Elastic-Job(三)：集成MyBatis和H2

作者: 会点代码的大叔 | 来源:发表于2017-09-22 15:26 被阅读471次

感觉写了好多，还没到正题....下一篇应该就可以到Elastic-Job了。

一、引入jar包：

<!-- H2内存数据库 -->
<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>1.4.196</version>
</dependency>

<!-- 数据源，使用的是alibaba的Druid -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.1.3</version>
</dependency>

<dependency>
   <groupId>org.aspectj</groupId>
   <artifactId>aspectjweaver</artifactId>
   <version>1.9.0.BETA-6</version>
   <type>pom</type>
</dependency>

<!-- spring与数据库访问集成（非Hibernate） -->
<dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-jdbc</artifactId>
  <version>4.2.4.RELEASE</version>
</dependency>

<!-- 添加mybatis的核心包 -->
<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.2.8</version>
</dependency>

<!-- 添加mybatis与Spring整合的核心包 -->
<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis-spring</artifactId>         
  <version>1.2.2</version>
</dependency>

<!--添加aspectjweaver包，spring-jdbc已经包含spring-tx -->
<dependency>
  <groupId>org.aspectj</groupId>
  <artifactId>aspectjweaver</artifactId>
  <version>1.8.5</version>
</dependency>

<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <version>1.16.18</version>
</dependency>

二、增加数据源配置:

1.新建目录

首先在/src/main/resources目录下新建目录：properties、spring-database（用于存放数据源的相关配置）、sql（用于存放初始化脚本）。

2.applicationContext.xml文件中增加配置：

<!-- 加载配置文件 -->
<context:property-placeholder location="classpath:properties/*.properties" />
<!-- 引入spring-开头的所有配置文件 -->                      
<import resource="classpath:spring-*/*.xml"/>

3.数据源配置

/src/main/resources/spring-database增加数据源的配置文件application-db.xml。

4.事务配置

application-db.xml中增加事务配置。

5.集成MyBatis

我工程的Mybatis的配置文件是放在com.spring.lw.dao包下面。

步骤3、4、5操作完，application-db.xml文件的全部配置：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xsi:schemaLocation="http://www.springframework.org/schema/beans
                       http://www.springframework.org/schema/beans/spring-beans.xsd
                       http://www.springframework.org/schema/tx
                       http://www.springframework.org/schema/tx/spring-tx.xsd
                       http://www.springframework.org/schema/aop
                       http://www.springframework.org/schema/aop/spring-aop.xsd
                       http://www.springframework.org/schema/context
                       http://www.springframework.org/schema/context/spring-context.xsd
                       http://www.springframework.org/schema/jdbc
                       http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">


    <!-- 配置数据源，使用的是alibaba的Druid(德鲁伊)数据源 -->
    <bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="url" value="${jdbc_url}" />
        <property name="username" value="${jdbc_username}" />
        <property name="password" value="${jdbc_password}" />
        <!-- 初始化连接大小 -->
        <property name="initialSize" value="0" />
        <!-- 连接池最大使用连接数量 -->
        <property name="maxActive" value="20" />
        <!-- 连接池最大空闲 -->
        <property name="maxIdle" value="20" />
        <!-- 连接池最小空闲 -->
        <property name="minIdle" value="0" />
        <!-- 获取连接最大等待时间 -->
        <property name="maxWait" value="60000" />
        <!-- 
        <property name="poolPreparedStatements" value="true" /> 
        <property name="maxPoolPreparedStatementPerConnectionSize" value="33" /> 
        -->
        <property name="validationQuery" value="${validationQuery}" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />
        <property name="testWhileIdle" value="true" />
        <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="25200000" />
        <!-- 打开removeAbandoned功能 -->
        <property name="removeAbandoned" value="true" />
        <!-- 1800秒，也就是30分钟 -->
        <property name="removeAbandonedTimeout" value="1800" />
        <!-- 关闭abanded连接时输出错误日志 -->
        <property name="logAbandoned" value="true" />
        <!-- 监控数据库 -->
        <!-- <property name="filters" value="stat" /> -->
        <property name="filters" value="mergeStat" />
    </bean>

    <!-- 配置Spring的事务管理器 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!-- 注解方式配置事物 -->
    <!-- <tx:annotation-driven transaction-manager="transactionManager" /> -->
    
    <!-- 拦截器方式配置事物 -->
    <tx:advice id="transactionAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="insert*" propagation="REQUIRED" />
            <tx:method name="update*" propagation="REQUIRED" />
            <tx:method name="delete*" propagation="REQUIRED" />
            <tx:method name="get*" propagation="SUPPORTS" />
            <tx:method name="*" propagation="SUPPORTS" />
        </tx:attributes>
    </tx:advice>
    <aop:config>
        <aop:pointcut id="transactionPointcut" expression="execution(* com.spring.lw.service..*Impl.*(..))" />
        <aop:advisor pointcut-ref="transactionPointcut" advice-ref="transactionAdvice" />
    </aop:config>
    
    <!-- 配置sqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="mapperLocations" value="classpath:com/spring/lw/mapper/*.xml"/>
    </bean>
    
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.spring.lw.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>
    
    <jdbc:embedded-database id="dataSource" type="H2">  
        <jdbc:script location="classpath:/sql/schema.sql" />  
        <jdbc:script location="classpath:/sql/data.sql" />  
    </jdbc:embedded-database>  
</beans>

6.数据源参数配置

/src/main/resources/properties下面增加dbconfig.properties，我们使用的是内存数据库：

driverClassName=org.h2.Driver
validationQuery=SELECT 1
jdbc:h2:mem:DavisDB;MODE=MySql;DB_CLOSE_DELAY=-1
jdbc_username=sa
jdbc_password=sa

三.编写代码

1.增加VO：

package com.spring.lw.vo;

//不用写get/set方法了，回头介绍这个
import lombok.Data;

@Data
public class User {
    private int id;
    
    private String username;
    
    private String password;
    
    public String toString(){
        return "id = " + id + " , username = " + username + ", password = " + password ;
    }
}

2.编写DAO的接口和实现类、Service的接口和实现类

Dao接口：

package com.spring.lw.dao;

import com.spring.lw.vo.User;

public interface IUserDao {
    User getUser(int id);

    int insertUser(User user);
    
    int updateUser(User user);
    
    int deleteUser(int id);
}

Dao实现类：

package com.spring.lw.dao.impl;

import com.spring.lw.dao.IUserDao;
import com.spring.lw.vo.User;

public class UserDaoImpl implements IUserDao {
    @Override
    public User getUser(int id) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public int insertUser(User user) {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public int updateUser(User user) {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public int deleteUser(int id) {
        // TODO Auto-generated method stub
        return 0;
    }
}

Service接口：

package com.spring.lw.service;

import com.spring.lw.vo.User;

public interface IUserService {
    User getUser(int id);

    int insertUser(User user);
    
    int updateUser(User user);
    
    int deleteUser(int id);
}

Servie实现类：

package com.spring.lw.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.spring.lw.dao.IUserDao;
import com.spring.lw.service.IUserService;
import com.spring.lw.vo.User;

@Service("userServiceImpl")
public class UserServiceImpl implements IUserService {

    @Autowired
    private IUserDao userDao;
    
    @Override
    public User getUser(int id) {
        // TODO Auto-generated method stub
        return userDao.getUser(id);
    }

    @Override
    public int insertUser(User user) {
        // TODO Auto-generated method stub
        return userDao.insertUser(user);
    }

    @Override
    public int updateUser(User user) {
        // TODO Auto-generated method stub
        return userDao.updateUser(user);
    }

    @Override
    public int deleteUser(int id) {
        // TODO Auto-generated method stub
        return userDao.deleteUser(id);
    }

}

3.Controller

新的接口路径,http://localhost:8088/Davis/getUser/{userId}

package com.spring.lw.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.spring.lw.service.IUserService;
import com.spring.lw.vo.User;

@Controller
public class RestAPIController {
    @Autowired
    private IUserService userService ;
    
    @RequestMapping("/hello")
    public @ResponseBody String test() {
        return "Spring MVC...";
    }
    
    @RequestMapping("/getUser/{userId}")
    public @ResponseBody String getUser(@PathVariable String userId){
        User user = userService.getUser(Integer.parseInt(userId));
        
        return user == null ? "Can't find..." : user.toString();
    }
}

4.MyBatis配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.spring.lw.dao.IUserDao">
    <select id="getUser" parameterType="int" resultType="com.spring.lw.vo.User">
        SELECT * FROM USER WHERE ID=#{id}
    </select>

    <insert id="insertUser" parameterType="com.spring.lw.vo.User">
        insert into user(id, username, password) 
        values(#{id,jdbcType=INTEGER},#{username,jdbcType=VARCHAR},
        #{password,jdbcType=VARCHAR});
    </insert>

    <update id="updateUser" parameterType="com.spring.lw.vo.User">
        update user
        <set>
            <if test="username!=null">
                username=#{username,jdbcType=VARCHAR},
            </if>
            <if test="password!=null">
                password=#{password,jdbcType=VARCHAR},
            </if>
        </set>
        where id=#{id,jdbcType=INTEGER}
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from user where id=#{id}
    </delete>
</mapper>

四.集成H2

1.在/src/main/resources/sql下面新建初始化脚本：

schema.sql
CREATE TABLE USER (ID int(11) NOT NULL , USERNAME varchar(20) , PASSWORD varchar(10));

data.sql
INSERT INTO USER (ID , USERNAME , PASSWORD) VALUES ('1', 'admin', 'admin');
INSERT INTO USER (ID , USERNAME , PASSWORD) VALUES ('2', 'zhangsan', '12345');
INSERT INTO USER (ID , USERNAME , PASSWORD) VALUES ('3', 'lisi', '11111');
INSERT INTO USER (ID , USERNAME , PASSWORD) VALUES ('4', 'wangwu', '22222');
INSERT INTO USER (ID , USERNAME , PASSWORD) VALUES ('5', 'zhaoliu', '33333');

2.在application-db.xml中增加配置，让工程启动的时候执行初始化脚本。

<!-- 配置sqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="mapperLocations" value="classpath:com/spring/lw/mapper/*.xml"/>
</bean>

如果Eclips提示xml错误的话，增加：
xmlns:jdbc="http://www.springframework.org/schema/jdbc"

http://www.springframework.org/schema/jdbc
http://www.springframework.org/schema/jdbc/spring-jdbc.xsd

上面完整的配置文件已经包含。

3.启动工程，在浏览器里面输入：

http://10.136.19.170:8088/Davis/getUser/1
可以查询到User的信息
http://10.136.19.170:8088/Davis/getUser/1
Can't find...