Mybatis小试CRUD

初次使用mybatis，对contacts表做一个增删改查。

contacts表结构：

+---------+------------------+
| Field   | Type             |
+---------+------------------+
| id      | int(10) unsigned |
| name    | varchar(45)      |
| address | varchar(45)      |
| gender  | char(1)          |
| dob     | datetime         |
| email   | varchar(45)      |
| mobile  | varchar(15)      |
| phone   | varchar(15)      |
+---------+------------------+

eclipse工程结构：

官方文档上直接copy了mybatis主配置文件，稍作修改，保存为configuration.xml 如下

<?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="db.properties">
	</properties>
	
	<typeAliases>
		<typeAlias type="com.mybatistest.bean.Contact" alias="Contact"/>
	</typeAliases>
	
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${driver}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="com/mybatistest/bean/ContactMapper.xml" />
	</mappers>
</configuration>

生成entity类 Contact.java  ，并建立对应的ContactMapper.xml文件，如下：

<?xml version="1.0" encoding="UTF-8" ?>     
<!DOCTYPE mapper     
PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"     
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">     
     
<mapper namespace="com.mybatistest.mapper.ContactMapper">
    <select id="selectList" resultType="Contact">     
        select * from contacts     
    </select> 
    <select id="selectByName" parameterType="String" resultType="Contact">
    	select * from contacts where name like concat('%',#{name},'%')
    </select>
    <select id="findContact" parameterType="int" resultType="Contact">
    	select * from contacts where id=#{id}
    </select>  
    
    <update id="updateContact" parameterType="Contact">
    	update contacts set name=#{name},address=#{address},gender=#{gender},
    	dob=#{dob},email=#{email},mobile=#{mobile},phone=#{phone} where id=#{id}
    </update>  
    <insert id="insertContact" 
    	parameterType="Contact" 
    	useGeneratedKeys="true"
    	keyProperty="id">
    	insert into contacts (name,address,gender,dob,email,mobile,phone) 
    	values (#{name},#{address},#{gender},#{dob},#{email},#{mobile},#{phone})
    </insert>
    <delete id="deleteContact" parameterType="int">
    	delete from contacts where id=#{id}
    </delete>
</mapper>

第一次写like查询的时候是用的 like '%#{name}%'，结果查不到，网上查过以后，有人给的建议是contact('%',#{name},'%')，于是拿来用了。

同时，还要生成一个ContactMapper的接口，如下：

public interface ContactMapper {
	List<Contact> selectList();
	List<Contact> selectByName(String name);
	Contact findContact(int id);
	int updateContact(Contact contact);
	int insertContact(Contact contact);
	int deleteContact(int id);
}

下面是CRUD操作的实现

public class ContactDaoImpl implements ContactDao {
	private SqlSession session; //
	private Transaction transaction; //
	private ContactMapper getContactMapper(){
		session = DBUtil.getSqlSession();
		//get mapper
		ContactMapper mapper = session.getMapper(ContactMapper.class);
		return mapper;
	}
	
	private void closeSession(){
		if(session!=null){
			session.close();
		}
	}
	//query all
	public List<Contact> query(){
		ContactMapper mapper = getContactMapper();
		List<Contact> list = mapper.selectList();
		return list;
	}
	
	//query by name
	public List<Contact> query(String name) {
		ContactMapper mapper = getContactMapper();
		List<Contact> list = mapper.selectByName(name);
		return list;
	}

	//find one by id
	public Contact find(int id){
		ContactMapper mapper = getContactMapper();
		return mapper.findContact(id);
	}
	
	//update contact
	public int update(Contact contact){
		ContactMapper mapper = getContactMapper();
		transaction = DBUtil.getTransaction(session); //transactio begin
		int rows = mapper.updateContact(contact);
		try {
			transaction.commit(); //commit
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeSession();
		}
		return rows; //return effected rows
	}
	
	//insert contact to db
	public int insert(Contact contact){
		ContactMapper mapper = getContactMapper();
		transaction = DBUtil.getTransaction(session);
		int rows = mapper.insertContact(contact);
		try {
			transaction.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeSession();
		}
		return rows;
	}
	
	//delete contact by id
	public int delete(int id){
		ContactMapper mapper = getContactMapper();
		transaction = DBUtil.getTransaction(session);
		int rows = mapper.deleteContact(id);
		try {
			transaction.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeSession();
		}
		return rows;
	}
}

之后重构，生成接口ContactDao。

下面是工具类DBUtil.java

public class DBUtil {
	private static SqlSessionFactory sessfactory = null;
	private static TransactionFactory transFactory = null;
	static{
		Reader reader = null;
		try {
			reader = Resources.getResourceAsReader("configuration.xml");
		} catch (IOException e) {
			e.printStackTrace();
		}
		sessfactory = new SqlSessionFactoryBuilder().build(reader);
		transFactory = new JdbcTransactionFactory();
	}
	
	
	public static Transaction getTransaction(SqlSession session){
		return transFactory.newTransaction(session.getConnection());
	}
	
	public static SqlSession getSqlSession(){
		return sessfactory.openSession();
	}
}

写完实现了，接着可以写简单单元测试了。。

public class ContactDaoTest {
	@Test
	public void testSelectList(){
		ContactDao dao = new ContactDaoImpl();
		List<Contact> list = dao.query();
		Assert.assertEquals(3, list.size());
		for(Contact c : list){
			System.out.println("name:"+c.getName()+",dob:"+c.getDob()+",email:"+c.getEmail());
		}
	}
	@Test
	public void testSelectByName(){
		ContactDao dao = new ContactDaoImpl();
		List<Contact> list = dao.query("tangerine");
		Assert.assertEquals(1, list.size());
		for(Contact c : list){
			System.out.println("name:"+c.getName()+",dob:"+c.getDob()+",email:"+c.getEmail());
		}
	}
	@Test
	public void testFindContact(){
		ContactDao dao = new ContactDaoImpl();
		Contact contact = dao.find(9);
		Assert.assertNotNull(contact);
		Assert.assertEquals("ssss", contact.getName());
		System.out.println("name:"+contact.getName());
	}
	
	@Test
	public void testInsertContact(){
		Contact contact = new Contact();
		contact.setName("MyBatis Test");
		contact.setAddress("Newyork City,Down town");
		contact.setDob(new Date());
		contact.setGender("M");
		contact.setEmail("HelloBatis@gmail.com");
		ContactDao dao = new ContactDaoImpl();
		int rows = dao.insert(contact);
		System.out.println(rows+" rows effected!");
		Assert.assertEquals(1, rows);
	}
	
	@Test
	public void testUpdateContact(){
		Contact contact = new Contact();
		contact.setId(9);
		contact.setName("MyBatis Update");
		contact.setAddress("Shanghai,China");
		contact.setDob(new Date());
		contact.setGender("M");
		contact.setEmail("mybatisworld@gmail.com");
		ContactDao dao = new ContactDaoImpl();
		dao.update(contact);
		
	}
	
	@Test
	public void testDeleteContact(){
		ContactDao dao = new ContactDaoImpl();
		try{
			int rows = dao.delete(5);
			Assert.assertEquals(1, rows);
			Contact deleted = dao.find(5);
			Assert.assertNull(deleted);
		}catch(Exception ex){
			Assert.fail();
		}
	}

mybatis还是挺简单的，就是要记的规则多了， 技术学多了，脑袋里面全是规则。。。。