Mybatis系列之简单示例

经过前两篇文章《Mybatis系列之入门》和《Mybatis系列之配置》的介绍，我们对Mybatis有了一定的了解，下面就接合一个实例学习案例，来巩固一下我们前面学习的知识。

环境搭建

IDE: STS(Spring Tools Suite)

Java Version: 1.6

Spring Version:3.1.1

Mybatis Version: 3.3.0

Database: Mysql 5.1.36

JUnit Version: 4.7

数据库

在Mysql中创建数据库mybatis，并在其中创建两张数据表，DDL语句如下：

CREATE TABLE `sys_user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) NOT NULL,
  `user_password` varchar(32) NOT NULL,
  `nick_name` varchar(64) NOT NULL,
  `email` varchar(128) DEFAULT NULL,
  `user_type_id` smallint(4) NOT NULL,
  `is_valid` tinyint(1) NOT NULL DEFAULT '1',
  `created_time` datetime NOT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_login_time` datetime DEFAULT NULL,
  `online` tinyint(4) NOT NULL DEFAULT '0',
  `language` varchar(6) NOT NULL DEFAULT 'zh-cn',
  `psd_changed_date` datetime DEFAULT NULL,
  `mphone` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

CREATE TABLE `communicator` (  `communicator_id` int(10) NOT NULL AUTO_INCREMENT,  `communicator_name` varchar(64) NOT NULL,  `phone` varchar(32) DEFAULT NULL,  `fax` varchar(32) DEFAULT NULL,  `email` varchar(100) DEFAULT NULL,  `user_id` int(10) DEFAULT NULL,  `report_to` int(10) DEFAULT '0',  `is_valid` tinyint(4) NOT NULL DEFAULT '1',  `created_time` datetime DEFAULT NULL,  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`communicator_id`),  UNIQUE KEY `communicator_name` (`communicator_name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Mybatis 配置

<?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><properties resource="mybatis-mysql.properties"><property name="driver-mysql" value="com.mysql.jdbc.Driver"></property><property name="url" value="jdbc:mysql://127.0.0.1:3306/etao" ></property><property name="username" value="root"></property><property name="password" value="cope9020"></property></properties><settings>                <!-- 将数据库字段命名规则A_COLUMN转换为Java使用的驼峰式命名规则aCloumn --><setting name="mapUnderscoreToCamelCase" value="true" /></settings><typeAliases><typeAlias alias="User" type="com.emerson.learning.pojo.User" /></typeAliases><environments default="development"><environment id="development"><transactionManager type="JDBC" /><dataSource type="POOLED"><property name="driver" value="${driver-mysql}" /><property name="url" value="${url}" /><property name="username" value="${username}" /><property name="password" value="${password}" /></dataSource></environment><environment id="product"><transactionManager type="JDBC" /><dataSource type="POOLED"><property name="driver" value="com.mysql.jdbc.Driver" /><property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis" /><property name="username" value="root" /><property name="password" value="cope9020" /></dataSource></environment></environments><mappers><mapper resource="com/emerson/learning/mapping/User.xml" /><mapper class="com.emerson.learning.dao.ICommunicatorDao" /></mappers></configuration>

数据表映射文件

为了体验Mybatis配置的灵活性和多样性，这里个案例中使用了不同的数据映射方式。

数据表User，使用外部XML文件进行映射，而另一张数据表Communicator则使用了注解映射。

这里仅用于测试，因为在实际使用中，注解还是写在Java代码上的，所有当有变化时，还是需要重新编译程序的，这一点与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.emerson.learning.mapping.user"><!-- 根据传入的Id值，到数据库中查询记录 --><select id="getByID" parameterType="int" resultType="User">SELECT user_id, user_name, user_password, nick_name, email, is_valid, created_time, updated_timeFROM sys_user WHERE user_id = #{id}</select><!-- 按用户名进行模糊查询 -->    <select id="queryByName" parameterType="User" resultType="User">SELECT user_id, user_name, user_password, nick_name, email, is_valid, created_time, updated_timeFROM sys_user<where><if test="userName != null">user_name like '%' #{userName} '%'</if></where></select><!-- 创建新用户，并写入到数据表中 -->    <!-- 写入新记录并返回主键值，注意，这里的KeyProperty应该是Java类里的属性名称，而非数据表中的字段名 --><insert id="insertUser" parameterType="User" useGeneratedKeys="true"keyProperty="userId">INSERT INTO sys_user(user_name, user_password, nick_name,user_type_id,is_valid, created_time)VALUES(#{userName},#{userPassword}, #{nickName}, #{userTypeId}, #{isValid},#{createdTime})</insert><!-- 更新用户信息，并写回到数据表中 -->    <update id="udpateUser" parameterType="User">UPDATE sys_userSETuser_name = #{userName}, user_password = #{userPassword}, nick_name =#{nickName}, user_type_id = #{userTypeId}, is_valid, = #{isValid}WHERE user_id = #{id}</update><!-- 根据传入的Id值，删除单条记录 -->    <delete id="deleteById" parameterType="int">DELETE FROM sys_user WHEREuser_id = #{id}</delete><!-- 根据传入的Id值列表，删除多条记录 -->    <delete id="deleteBatch" parameterType="java.util.List">DELETE FROM sys_user WHERE user_id in<foreach collection="list" item="item" index="index" open="("close=")" separator=",">#{item}</foreach></delete></mapper>

package com.emerson.learning.dao;import java.util.List;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import com.emerson.learning.pojo.Communicator;public interface ICommunicatorDao {@Select("SELECT * FROM communicator WHERE communicator_id=#{id}")public Communicator getById(@Param(value = "id") int id);@Select("SELECT * FROM vw_communicator ORDER BY communicator_name")public List<Communicator> getAll();}

Java POJO类

创建用于封装数据表记录的值对象，有的朋友喜欢叫entity，有的叫domain，有的叫pojo，叫什么无所谓了，根据自己的喜好来吧：）

package com.emerson.learning.pojo;import java.sql.Timestamp;public class User {/** *  */private int userId;/** *  */private String userPassword;/** *  */private String userName;/** *  */private String nickName;/** *  */private int userTypeId;/** *  */private String email;/** *  */private int isValid;/** *  */private Timestamp createdTime;/** *  */private Timestamp updatedTime;public int getIsValid() {return isValid;}public void setIsValid(int isValid) {this.isValid = isValid;}public Timestamp getCreatedTime() {return createdTime;}public void setCreatedTime(Timestamp createdTime) {this.createdTime = createdTime;}public Timestamp getUpdatedTime() {return updatedTime;}public void setUpdatedTime(Timestamp updatedTime) {this.updatedTime = updatedTime;}public int getUserId() {return userId;}public void setUserId(int userId) {this.userId = userId;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName;}public String getNickName() {return nickName;}public void setNickName(String nickName) {this.nickName = nickName;}public String getEmail() {return email;}public void setEmail(String eamil) {this.email = eamil;}public String getUserPassword() {return userPassword;}public void setUserPassword(String userPassword) {this.userPassword = userPassword;}public User() {this.createdTime = new Timestamp(System.currentTimeMillis());}public int getUserTypeId() {return userTypeId;}public void setUserTypeId(int userTypeId) {this.userTypeId = userTypeId;}@Overridepublic String toString() {return "User [userId=" + userId + ", userName=" + userName + ", nickName=" + nickName + ", eamil=" + email+ ", isValid=" + isValid + ", createdTime=" + createdTime + ", updatedTime=" + updatedTime + "]";}}

package com.emerson.learning.pojo;import java.sql.Timestamp;public class Communicator {/** *  */private long communicatorId;/** *  */private String communicatorName;/** *  */private String phone;/** *  */private String fax;/** *  */private String email;/** *  */private int reportTo;/** *  */private String reportToName;/** *  */private int isValid;/** *  */private Timestamp createdTime;/** *  */private Timestamp updatedTime;public long getCommunicatorId() {return communicatorId;}public void setCommunicatorId(long communicatorId) {this.communicatorId = communicatorId;}public String getCommunicatorName() {return communicatorName;}public void setCommunicatorName(String communicatorName) {this.communicatorName = communicatorName;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public String getFax() {return fax;}public void setFax(String fax) {this.fax = fax;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public int getReportTo() {return reportTo;}public void setReportTo(int reportTo) {this.reportTo = reportTo;}public String getReportToName() {return reportToName;}public void setReportToName(String reportToName) {this.reportToName = reportToName;}public int getIsValid() {return isValid;}public void setIsValid(int isValid) {this.isValid = isValid;}public Timestamp getCreatedTime() {return createdTime;}public void setCreatedTime(Timestamp createdTime) {this.createdTime = createdTime;}public Timestamp getUpdatedTime() {return updatedTime;}public void setUpdatedTime(Timestamp updatedTime) {this.updatedTime = updatedTime;}@Overridepublic String toString() {return "Communicator [communicatorId=" + communicatorId + ", communicatorName=" + communicatorName + ", phone="+ phone + ", fax=" + fax + ", email=" + email + ", reportTo=" + reportTo + ", reportToName="+ reportToName + ", isValid=" + isValid + ", createdTime=" + createdTime + ", updatedTime="+ updatedTime + "]";}}

测试

一切基础工作做好后，就可以着手编写测试代码了。这里使用了JUnit测试框架，如果有朋友不了解，请自行补充学习。

我们先对使用外部XML文件进行映射的User表进行测试，测试代码如下。

package com.emerson.learning.mapping;import java.io.IOException;import java.io.Reader;import java.util.ArrayList;import java.util.List;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.junit.After;import org.junit.Before;import org.junit.Test;import com.emerson.learning.pojo.User;public class UserMappingTest {private Reader reader;private SqlSessionFactory sqlSessionFactory;@Beforepublic void setUp() throws Exception {try {reader = Resources.getResourceAsReader("mybatis.xml");} catch (IOException e) {e.printStackTrace();}sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);}@Afterpublic void tearDown() throws Exception {}/** * 下面使用的是直接从映射配置文件中读取相应的SQL语句并执行 返回的是Object对象，需要对其进行强类型转换后才可使用 *  * 该实现方便需要为每个实体类编写一个映射文件 * <mapper namespace="com.emerson.learning.mapping.user">  *     <select id="getByID" parameterType="int" resultType="User">  *         SELECT user_id, user_name, nick_name, email FROM sys_user WHERE user_id = #{id} *     </select> * </mapper> *  * 并注册到Mybatis配置文件中的mappers节中 * <mappers>  *     <mapper resource="com/emerson/learning/mapping/User.xml" /> * </mappers> *  */@Testpublic void testGetById() {SqlSession session = sqlSessionFactory.openSession();try {User user;Object obj = session.selectOne("com.emerson.learning.mapping.user.getByID", 1);if (null == obj) {System.out.println("the result is null.");} else {user = (User) obj;System.out.println(user.getUserName());System.out.println(user.getNickName());System.out.println(user);}} finally {session.close();}}/** * 测试模糊查询 */@Testpublic void testQueryByName() {SqlSession session = sqlSessionFactory.openSession();try {User user = new User();user.setUserName("mao");List<User> list = session.selectList("com.emerson.learning.mapping.user.queryByName", user);System.out.println(list.size());for(User u: list) {System.out.println(u);}} finally {session.close();}}/** * 测试写入数据 */@Testpublic void testInsertUser() {SqlSession session = sqlSessionFactory.openSession();try {User user = new User();user.setEmail("chris.mao.zb@###.com");user.setNickName("Mybatis Tester");user.setUserName("cmzb");user.setIsValid(1);user.setUserPassword("5f4dcc3b5aa765d61d8327deb882cf99");session.insert("com.emerson.learning.mapping.user.insertUser", user);System.out.println("New Id is " + user.getUserId()); //打印出新增记录的Id值session.commit(); //这里一定要使用commit，否则事务不会被提交，数据操作不会反映到数据表中} finally {session.close();}}/** * 测试更新数据 */@Testpublic void testUpdateUser() {SqlSession session = sqlSessionFactory.openSession();try {User user = session.selectOne("com.emerson.learning.mapping.user.getByID", 2);user.setEmail("chris.mao.zb@＃＃＃.com");user.setNickName("ChrisMao");user.setIsValid(0);session.update("com.emerson.learning.mapping.user.updatetUser", user);session.commit();//这里一个要commit，否则更新是不会写入到数据表中的} finally {session.close();}}/** * 测试单条删除 */@Testpublic void testDeleteById() {SqlSession session = sqlSessionFactory.openSession();try {session.delete("com.emerson.learning.mapping.user.deleteById", 3);session.commit();} finally {session.close();}}/** * 测试批量删除 */@Testpublic void testDeleteBatch() {SqlSession session = sqlSessionFactory.openSession();try {List<Integer> idList = new ArrayList<Integer>();idList.add(1);idList.add(2);session.delete("com.emerson.learning.mapping.user.deleteBatch", idList);session.commit();} finally {session.close();}}}

下在是对使用注解映射的数据表Communicator表进行测试。

package com.emerson.learning.dao;import java.io.IOException;import java.io.Reader;import java.util.List;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.junit.After;import org.junit.Before;import org.junit.Test;import com.emerson.learning.pojo.Communicator;public class CommunicatorDaoTest {private Reader reader;private SqlSessionFactory sqlSessionFactory;@Beforepublic void setUp() throws Exception {try {reader = Resources.getResourceAsReader("mybatis.xml");} catch (IOException e) {e.printStackTrace();}sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);}@Afterpublic void tearDown() throws Exception {}/** * 下面使用了接口和注解的方式来执行同样的代码 定义一个操作数据实体表的接口，并使用注解方式把SQL查询语句与接口方法进行绑定 public * interface ICommunicatorDao { @Select( * "SELECT * FROM communicator WHERE communicator_id=#{id}") public * Communicator getById(@Param(value = "id") int id); } *  * 然后将接口注册到Mybatis的配置文件中即可 * <mappers> <mapper class="com.emerson.learning.inter.ICommunicatorDao" /> * </mappers> *  * 当数据表很多的时候，需要写很多的映射关系，这样也比较麻烦，Mybatis3.2.1提供了 一种更加简洁的方法，可以一次指定多个映射接口 * <mappers> <package namee="com.emerson.learning.inter" /> </mappers> *  */@Testpublic void testGetById() {SqlSession session = sqlSessionFactory.openSession();try {ICommunicatorDao cp = session.getMapper(ICommunicatorDao.class);Communicator c = cp.getById(1);if (null == c) {System.out.println("the result is null.");} else {System.out.println(c);}} finally {session.close();}}@Testpublic void testGetAll() {SqlSession session = sqlSessionFactory.openSession();try {ICommunicatorDao cp = session.getMapper(ICommunicatorDao.class);List<Communicator> list = cp.getAll();for (Communicator c : list) {System.out.println(c);}} finally {session.close();}}}