Mybatis系列（三）简单示例

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_time
		FROM 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_time
		FROM 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_user
		SET
		user_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 WHERE
		user_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;
	}

	@Override
	public 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;
	}

	@Override
	public 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;

	@Before
	public void setUp() throws Exception {
		try {
			reader = Resources.getResourceAsReader("mybatis.xml");
		} catch (IOException e) {
			e.printStackTrace();
		}
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
	}

	@After
	public 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>
	 * 
	 */
	@Test
	public 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();
		}
	}

	/**
	 * 测试模糊查询
	 */
	@Test
	public 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();
		}
	}

	/**
	 * 测试写入数据
	 */
	@Test
	public 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();
		}
	}

	/**
	 * 测试更新数据
	 */
	@Test
	public 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();
		}
	}

	/**
	 * 测试单条删除
	 */
	@Test
	public void testDeleteById() {
		SqlSession session = sqlSessionFactory.openSession();
		try {
			session.delete("com.emerson.learning.mapping.user.deleteById", 3);
			session.commit();
		} finally {
			session.close();
		}
	}
	
	/**
	 * 测试批量删除
	 */
	@Test
	public 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表进行测试。这里使用到了Mybatis接口式编程的特性，在后续的《Mybatis系列之接口式编程》会进一步阐述，这里就不多说了。

 

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;

	@Before
	public void setUp() throws Exception {
		try {
			reader = Resources.getResourceAsReader("mybatis.xml");
		} catch (IOException e) {
			e.printStackTrace();
		}
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
	}

	@After
	public 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>
	 * 
	 */
	@Test
	public 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();
		}
	}

	@Test
	public 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();
		}
	}
}

 

总结

至此，对Mybatis的单表测试代码部分已全部完成。对于上述案例进行如下总结：

 

• Mybatis大大减少了码农的编码工作，提高了工和效率
• 让程序猿可以把更多精力放在业务逻辑处理上，与数据库打交道的工作交由Mybatis完成
• 对于业务表的映射关系尽量使用外部文件进行配置
• 对于变动极小的基础数据表的映射可以考虑使用注解方式完成

 

最后，附上项目目录结构图。