spring与mybatis的整合小案例

一，环境准备

1.mysql数据库，

2.eclipse（已配置好maven 我的博客有介绍https://mp.csdn.net/postedit/86511535），

3.准备的数据库脚本 

CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;

USE `test`;
CREATE TABLE account
(
accountid INT PRIMARY KEY AUTO_INCREMENT,
balance NUMERIC(10,2)
);
CREATE TABLE oprecord
(
id INT PRIMARY KEY AUTO_INCREMENT,
accountid INT,
opmoney NUMERIC(10,2),
optime DATETIME
);
select * from account;

二，spring-mybatis整合

1.新建maven项目   groupId : com.spring  artifactId:spring-mybatis

包结构整体如下

在pom.xml中添加spring 依赖包 ，mybatis依赖包，mybatis-spring整合包，mysql（oracle）驱动包，JUnit单元测试包，log4j包等，配置内容如下

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.spring</groupId>
	<artifactId>spring-mybatis</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<!-- 统一管理所依赖的jar包 -->
	<properties>
		<spring.version>5.0.8.RELEASE</spring.version>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>
	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>
		<!-- spring相关包 -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aspects</artifactId>
			<version>${spring.version}</version>
			<scope>compile</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
			<scope>compile</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${spring.version}</version>
			<scope>test</scope>
		</dependency>
		<!-- mybatis相关包 -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.4.6</version>
			<scope>compile</scope>
		</dependency>
		<!-- spring-mybatis的整合包 -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>1.3.2</version>
			<scope>compile</scope>
		</dependency>
		<!-- dbcp2数据源连接池相关包 -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-dbcp2</artifactId>
			<version>2.5.0</version>
			<scope>compile</scope>
		</dependency>
		<!-- mysql驱动包 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.8</version>
			<scope>compile</scope>
		</dependency>
		<!-- log4j日志相关包 -->
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
			<scope>compile</scope>
		</dependency>
	</dependencies>
</project>

2.创建连接数据库的配置文件 db.properties  ，内容如下，这里需要修改成你自己的数据库实例名称和密码

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/smbms?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
mysql.username=root
mysql.password=a

3.创建mybatis-config.xml 配置文件，配置mibatis的全局属性

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<settings>
		<!-- 输出日志配置 -->
		<setting name="logImpl" value="LOG4J" />
		<!-- 这个配置使全局的映射器启用或者禁用二级缓存 -->
		<setting name="cacheEnabled" value="true" />
		<!-- 全局启用或者禁用延迟加载。当禁用时，所有关联对象都会及时加载 。 -->
		<setting name="lazyLoadingEnabled" value="true" />
		<!-- 指定mybatis 如何自动映射列到属性： PARTIAL 只会自动映射简单，没有嵌套的结果。 FULL 会自动映射任意复杂的结果（嵌套的或者其他情况） -->
		<setting name="autoMappingBehavior" value="FULL" />
		<!-- 设置超时时间，它决定驱动等待一个数据库相应的时间 单位：s -->
		<setting name="defaultStatementTimeout" value="25" />
		<!--是否开启自动驼峰命名规则，即从经典数据库列名A-COLUMN 到经典java属性名 aColumn的类似映射。 默认为false -->
		<setting name="mapUnderscoreToCamelCase" value="true" />
	</settings>
	<!--类型别名是为Java类型设置一个短的名字，可以方便我们引用每个类 -->
	<!-- 设置别名 -->
	<typeAliases>
		<package name="com.sam.pojo" />
		<!-- <typeAlias type="com.pojo.Student" alias="Student" /> -->
	</typeAliases>
	<!-- 与数据库相关的配置移植到spring框架进行管理，这里删掉原来的配置 -->
	<!-- 注册sql映射文件 -->
	<mappers>
		<!-- 绑定配置文件的方法 -->
		<!-- <mapper resource="com/smbms/dao/UserMapper.xml" /> -->
		<!-- 绑定接口的方法 -->
		<!-- <mapper class="com.smbms.dao.UserMapper"></mapper> -->
		<!-- 指定包下面所有的类绑定为类名 -->
		 <package name="com.sam.dao" />
	</mappers>
</configuration>

4.创建 applicationContext.xml配置文件，配置spring的属性 内容如下

<?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:p="http://www.springframework.org/schema/p"
	xmlns:c="http://www.springframework.org/schema/c" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop
       	http://www.springframework.org/schema/aop/spring-aop.xsd
       	">
	<!-- 配制组件扫描 -->
	<context:component-scan base-package="com.sam"></context:component-scan>
	<!-- 引入数据库参数配置文件 -->
	<context:property-placeholder location="classpath:db.properties" />
	<!-- 配置数据源 -->
	<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
		<property name="driverClassName" value="${mysql.driver}"></property>
		<property name="url" value="${mysql.url}"></property>
		<property name="username" value="${mysql.username}"></property>
		<property name="password" value="${mysql.password}"></property>
		<!-- 初始化连接数 -->
		<property name="initialSize" value="50"></property>
		<!-- 最大连接数 -->
		<property name="maxOpenPreparedStatements" value="200"></property>
		<!-- 最小空闲 -->
		<property name="minIdle" value="10"></property>
		<!-- 最大空闲 -->
		<property name="maxIdle" value="30"></property>
		<!-- 等待超时时间（毫秒） -->
		<property name="maxWaitMillis" value="2000"></property>
	</bean>
	<!-- 配制与数据库交互的会话工厂 -->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<!-- 注入数据源 -->
		<property name="dataSource" ref="dataSource"></property>
		<!-- 引入mybatis配置文件 -->
		<property name="configLocation" value="classpath:mybatis-config.xml"></property>
	</bean>
	<!-- 配置数据映射器 -->
	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<!-- 为映射器接口文件设置基本的包路径 -->
		<property name="basePackage" value="com.sam.dao"></property>
	</bean>
</beans>

5.两个实体类代码如下

 

package com.sam.pojo;

import java.io.Serializable;
import java.math.BigDecimal;

public class Account implements Serializable {

	private static final long serialVersionUID = -685981091919057914L;

	private Integer accountid;

	private BigDecimal balance;

	public Integer getAccountid() {
		return accountid;
	}

	public void setAccountid(Integer accountid) {
		this.accountid = accountid;
	}

	public BigDecimal getBalance() {
		return balance;
	}

	public void setBalance(BigDecimal balance) {
		this.balance = balance;
	}
}

package com.sam.pojo;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

public class Oprecord implements Serializable {

	private static final long serialVersionUID = -7420958525797234193L;

	private Integer id;

	private Integer accountid;

	private BigDecimal opmoney;

	private Date optime;

	public Integer getId() {
		return id;
	}

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

	public Integer getAccountid() {
		return accountid;
	}

	public void setAccountid(Integer accountid) {
		this.accountid = accountid;
	}

	public BigDecimal getOpmoney() {
		return opmoney;
	}

	public void setOpmoney(BigDecimal opmoney) {
		this.opmoney = opmoney;
	}

	public Date getOptime() {
		return optime;
	}

	public void setOptime(Date optime) {
		this.optime = optime;
	}
}

6.数据层接口以及映射文件代码如下

package com.sam.dao;

import com.sam.pojo.Account;

public interface AccountMapper {
    int deleteByPrimaryKey(Integer accountid);

    int insert(Account record);

    int insertSelective(Account record);

    Account selectByPrimaryKey(Integer accountid);

    int updateByPrimaryKeySelective(Account record);

    int updateByPrimaryKey(Account record);
}

package com.sam.dao;

import com.sam.pojo.Oprecord;

public interface OprecordMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(Oprecord record);

    int insertSelective(Oprecord record);

    Oprecord selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(Oprecord record);

    int updateByPrimaryKey(Oprecord record);
}

<?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.sam.dao.AccountMapper" >
  <resultMap id="BaseResultMap" type="com.sam.pojo.Account" >
    <id column="accountid" property="accountid" jdbcType="INTEGER" />
    <result column="balance" property="balance" jdbcType="DECIMAL" />
  </resultMap>
  <sql id="Base_Column_List" >
    accountid, balance
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from account
    where accountid = #{accountid,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from account
    where accountid = #{accountid,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.sam.pojo.Account" >
    insert into account (accountid, balance)
    values (#{accountid,jdbcType=INTEGER}, #{balance,jdbcType=DECIMAL})
  </insert>
  <insert id="insertSelective" parameterType="com.sam.pojo.Account" >
    insert into account
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="accountid != null" >
        accountid,
      </if>
      <if test="balance != null" >
        balance,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="accountid != null" >
        #{accountid,jdbcType=INTEGER},
      </if>
      <if test="balance != null" >
        #{balance,jdbcType=DECIMAL},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.sam.pojo.Account" >
    update account
    <set >
      <if test="balance != null" >
        balance = #{balance,jdbcType=DECIMAL},
      </if>
    </set>
    where accountid = #{accountid,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.sam.pojo.Account" >
    update account
    set balance = #{balance,jdbcType=DECIMAL}
    where accountid = #{accountid,jdbcType=INTEGER}
  </update>
</mapper>

 

<?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.sam.dao.OprecordMapper" >
  <resultMap id="BaseResultMap" type="com.sam.pojo.Oprecord" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="accountid" property="accountid" jdbcType="INTEGER" />
    <result column="opmoney" property="opmoney" jdbcType="DECIMAL" />
    <result column="optime" property="optime" jdbcType="TIMESTAMP" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, accountid, opmoney, optime
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from oprecord
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from oprecord
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.sam.pojo.Oprecord" >
    insert into oprecord (id, accountid, opmoney, 
      optime)
    values (#{id,jdbcType=INTEGER}, #{accountid,jdbcType=INTEGER}, #{opmoney,jdbcType=DECIMAL}, 
      #{optime,jdbcType=TIMESTAMP})
  </insert>
  <insert id="insertSelective" parameterType="com.sam.pojo.Oprecord" >
    insert into oprecord
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="accountid != null" >
        accountid,
      </if>
      <if test="opmoney != null" >
        opmoney,
      </if>
      <if test="optime != null" >
        optime,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="accountid != null" >
        #{accountid,jdbcType=INTEGER},
      </if>
      <if test="opmoney != null" >
        #{opmoney,jdbcType=DECIMAL},
      </if>
      <if test="optime != null" >
        #{optime,jdbcType=TIMESTAMP},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.sam.pojo.Oprecord" >
    update oprecord
    <set >
      <if test="accountid != null" >
        accountid = #{accountid,jdbcType=INTEGER},
      </if>
      <if test="opmoney != null" >
        opmoney = #{opmoney,jdbcType=DECIMAL},
      </if>
      <if test="optime != null" >
        optime = #{optime,jdbcType=TIMESTAMP},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.sam.pojo.Oprecord" >
    update oprecord
    set accountid = #{accountid,jdbcType=INTEGER},
      opmoney = #{opmoney,jdbcType=DECIMAL},
      optime = #{optime,jdbcType=TIMESTAMP}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

7.业务层代码

package com.sam.service;

import com.sam.pojo.Account;

public interface BaseService {

	public void addAccount(Account account);
}

package com.sam.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.sam.dao.AccountMapper;
import com.sam.pojo.Account;
@Service
public class BaseServiceImpl implements BaseService {

	@Autowired
	private AccountMapper am;

	@Override
	public void addAccount(Account account) {
		am.insert(account);
	}

}

8.测试类代码

package com.sam.test;

import java.math.BigDecimal;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.sam.dao.AccountMapper;
import com.sam.pojo.Account;
import com.sam.service.BaseService;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:applicationContext.xml" })
public class SpringMybatisTest {

	@Autowired
	private BasicDataSource dataSource;
	@Autowired
	private SqlSessionFactoryBean factory;
	@Autowired
	private BaseService bs;

	@Test
	public void test1() {
		System.out.println(dataSource);
		System.out.println(factory);
	}

	@Test
	public void test2() throws Exception {
		SqlSessionFactory obj = factory.getObject();
		SqlSession session = obj.openSession();
		AccountMapper mapper = session.getMapper(AccountMapper.class);
		Account ac = new Account();
		ac.setBalance(new BigDecimal(1000000));
		int len = mapper.insert(ac);
		System.out.println(len);
	}

	@Test
	public void test3() throws Exception {
		Account ac = new Account();
		ac.setBalance(new BigDecimal(2000000));
		bs.addAccount(ac);
	}
}

三，运行结果

1.运行test1 ()  显示如下信息，则说明与数据库连接成功

2，运行test2() 和test3（） 进行添加操作

3.查看数据库的数据，OK，成功！