Nano Framework之添加H2数据库并使用JDBC进行持久层开发

    本文基于前几篇为基础进行编写，如有需要请参考前几篇文章内容。
    下面我们进入今天的主题。

1、添加H2数据库依赖
1.1、修改pom.xml

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.189</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.0.15</version>
</dependency>

1.2、添加监听类

import java.sql.SQLException;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import org.h2.tools.Server;
import org.nanoframework.commons.support.logging.Logger;
import org.nanoframework.commons.support.logging.LoggerFactory;

public class H2DBServerListener implements ServletContextListener {

    private Logger LOG = LoggerFactory.getLogger(H2DBServerListener.class);
    private Server server;

    @Override
    public void contextInitialized(ServletContextEvent sce) {
        try {  
            LOG.info("正在启动H2数据库...");
            server = Server.createTcpServer().start(); 
            LOG.info("H2数据库启动完成");
        } catch (SQLException e) {  
            throw new RuntimeException("启动H2数据库出错：" + e.getMessage(), e);  
        }  
    }

    @Override
    public void contextDestroyed(ServletContextEvent sce) {
        if(server != null) {
            server.stop();
            server = null;
        }
    }

}

1.3、修改web.xml，添加listener和servlet

<listener>
    <listener-class>org.nanoframework.examples.first.webapp.listener.H2DBServerListener</listener-class>
</listener>
<!-- ... -->

<servlet>
    <servlet-name>H2Console</servlet-name>
    <servlet-class>org.h2.server.web.WebServlet</servlet-class>
    <init-param>
        <param-name>webAllowOthers</param-name>
        <param-value></param-value>
    </init-param>
    <init-param>
        <param-name>trace</param-name>
        <param-value></param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
    <servlet-name>H2Console</servlet-name>
    <url-pattern>/console/*</url-pattern>
</servlet-mapping>

<!-- 修改Dispatcher-Servlet的启动参数load-on-startup为2，优先启动数据库 -->
<servlet>
    <servlet-name>Dispatcher-Servlet</servlet-name>
    <servlet-class>org.nanoframework.web.server.servlet.DispatcherServlet</servlet-class>
    <init-param>
        <param-name>context</param-name>
        <param-value>/context.properties</param-value>
    </init-param>
    <load-on-startup>2</load-on-startup>
</servlet>

<servlet-mapping>
    <servlet-name>Dispatcher-Servlet</servlet-name>
    <url-pattern>/dispatcher/*</url-pattern>
</servlet-mapping>

1.4、启动服务并访问 http://ip:port/first-webapp/console，使用以下信息登录

• 连接串: jdbc:h2:~/test
• 用户名: test
• 密码: test

1.5、新建测试表

create table t_nano_test (
id int primary key,
name varchar(255)
)

2、开发基于JDBC的服务
2.1、context.properties中添加jdbc支持

mapper.package.jdbc=/examples-jdbc.properties

2.2、在src/main/resources下添加数据源属性文件examples-jdbc.properties(模板)

JDBC.environment.id=examples

JDBC.pool.type=DRUID
JDBC.driver=org.h2.Driver
JDBC.url=jdbc:h2:~/test
JDBC.username=test
JDBC.password=test
JDBC.autoCommit=false

################################################################
################   DRUID连接池配置    ############################
################################################################
# 初始化连接数量
druid.initialSize=1

# 最大并发连接数
druid.maxActive=5

# 最大空闲连接数
druid.maxIdle=5

# 最小空闲连接数
druid.minIdle=1

# 配置获取连接等待超时的时间
druid.maxWait=30000

# 超过时间限制是否回收 
druid.removeAbandoned=true

# 超过时间限制多长 
druid.removeAbandonedTimeout=180

# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 
druid.timeBetweenEvictionRunsMillis=10000

# 配置一个连接在池中最小生存的时间，单位是毫秒
druid.minEvictableIdleTimeMillis=60000

# 用来检测连接是否有效的sql，要求是一个查询语句
druid.validationQuery=select 1

# 申请连接的时候检测
druid.testWhileIdle=true

# 申请连接时执行validationQuery检测连接是否有效，配置为true会降低性能
druid.testOnBorrow=false

# 归还连接时执行validationQuery检测连接是否有效，配置为true会降低性能
druid.testOnReturn=false

# 打开PSCache，并且指定每个连接上PSCache的大小
druid.poolPreparedStatements=true

druid.maxPoolPreparedStatementPerConnectionSize=20

# 属性类型是字符串，通过别名的方式配置扩展插件，
# 常用的插件有： 
#   监控统计用的filter:stat 
#   日志用的filter:log4j  
#   防御SQL注入的filter:wall
druid.filters=stat

2.3、添加Domain

import org.nanoframework.commons.entity.BaseEntity;

public class Test extends BaseEntity {
    private Integer id;
    private String name;

    public Test() {}
    public Test(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

}

2.4、添加Dao接口与实现

import org.nanoframework.examples.first.webapp.dao.impl.JdbcExamplesDaoImpl;
import org.nanoframework.examples.first.webapp.domain.Test;
import com.google.inject.ImplementedBy;
...

@ImplementedBy(JdbcExamplesDaoImpl.class)
public interface JdbcExamplesDao {
    long insert(Test test) throws SQLException;
    List<Test> select() throws SQLException;
    Test select(int id) throws SQLException;
}

import static org.nanoframework.orm.jdbc.binding.GlobalJdbcManager.get;
import org.nanoframework.examples.first.webapp.constant.DataSource;
import org.nanoframework.examples.first.webapp.dao.JdbcExamplesDao;
import org.nanoframework.examples.first.webapp.domain.Test;
import org.nanoframework.orm.jdbc.jstl.Result;
...

public class JdbcExamplesDaoImpl implements JdbcExamplesDao {

    private final String insert = "INSERT INTO T_NANO_TEST(ID, NAME) VALUES (?, ?) ";
    private final String select = "SELECT ID, NAME FROM T_NANO_TEST ";
    private final String selectById = "SELECT ID, NAME FROM T_NANO_TEST WHERE ID = ? ";

    @Override
    public long insert(Test test) throws SQLException {
        List<Object> values = new ArrayList<>();
        values.add(test.getId());
        values.add(test.getName());
        return get(DataSource.EXAMPLES).executeUpdate(insert);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Test> select() throws SQLException {
        Result result = get(DataSource.EXAMPLES).executeQuery(select);
        if(result.getRowCount() > 0) {
            List<Test> tests = new ArrayList<>();
            Arrays.asList(result.getRows()).forEach(row -> tests.add(Test._getMapToBean(row, Test.class)));
        } 

        return Collections.emptyList();
    }

    @SuppressWarnings("unchecked")
    @Override
    public Test select(int id) throws SQLException {
        List<Object> values = new ArrayList<>();
        values.add(id);
        Result result = get(DataSource.EXAMPLES).executeQuery(selectById, values);
        if(result.getRowCount() > 0) {
            return Test._getMapToBean(result.getRows()[0], Test.class);
        }

        return null;
    }

}

2.5、添加Component接口与实现

@Component
@ImplementedBy(JdbcExamplesComponentImpl.class)
@RequestMapping("/jdbc")
public interface JdbcExamplesComponent {
    @RequestMapping("/persist")
    Object persist(@RequestParam(name = "id") Integer id, @RequestParam(name = "name") String name);

    @RequestMapping("/find/all")
    Object findAll();

    @RequestMapping("/find/{id}")
    Object findById(@PathVariable("id") Integer id);
}

public class JdbcExamplesComponentImpl implements JdbcExamplesComponent {
    private Logger LOG = LoggerFactory.getLogger(JdbcExamplesComponentImpl.class);

    @Inject
    private JdbcExamplesDao examplsDao;

    @JdbcTransactional(envId = DataSource.EXAMPLES)
    @Override
    public Object persist(Integer id, String name) {
        Test test = new Test(id, name);
        try { 
            long changed = examplsDao.insert(test);
            if(changed > 0)
                return ResultMap.create(200, "写入数据库成功", "SUCCESS");
            else 
                return ResultMap.create(200, "写入数据库失败", "ERROR");
        } catch(Exception e) {
            LOG.error("写入数据库异常: " + e.getMessage(), e);
            return ResultMap.create(500, "写入数据库异常: " + e.getMessage(), e.getClass().getName());
        }
    }

    @Override
    public Object findAll() {
        try {
            List<Test> testList = examplsDao.select();
            Map<String, Object> map = ResultMap.create(200, "OK", "SUCCESS")._getBeanToMap();
            map.put("records", testList.size());
            map.put("rows", testList);
            return map;
        } catch(Exception e) {
            LOG.error("查询数据异常: " + e.getMessage(), e);
            return ResultMap.create(500, "查询数据异常: " + e.getMessage() , e.getClass().getName());
        }
    }

    @Override
    public Object findById(Integer id) {
        try {
            Test test = examplsDao.select(id);
            Map<String, Object> map = ResultMap.create(200, "OK", "SUCCESS")._getBeanToMap();
            map.put("data", test);
            return map;
        } catch(Exception e) {
            LOG.error("查询数据异常: " + e.getMessage(), e);
            return ResultMap.create(500, "查询数据异常: " + e.getMessage() , e.getClass().getName());
        }
    }

}

3、启动服务后进行以下操作

• http://ip:port/first-webapp/jdbc/persist?id=1&name=test
• http://ip:port/first-webapp/jdbc/find/all
• http://ip:port/first-webapp/jdbc/find/1

4、至此，最基本的JDBC示例开发就完成了