MyBatis CRUD Operations

1.Maven Dependency

<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>org.fool.mybatis</groupId>
	<artifactId>MyBatis</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<mybatis-version>3.2.1</mybatis-version>
		<mysql-connector-version>5.1.24</mysql-connector-version>
		<junit-version>4.11</junit-version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>${mybatis-version}</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>${mysql-connector-version}</version>
		</dependency>

		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>${junit-version}</version>
			<scope>test</scope>
		</dependency>
	</dependencies>
</project>

 

2.Project Directory

 

3.src/main/resources

mybatis-config.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="jdbc.properties" />

	<typeAliases>
		<package name="org.fool.mybatis.model" />
	</typeAliases>

	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driverClassName}" />
				<property name="url" value="${jdbc.url}" />
				<property name="username" value="${jdbc.username}" />
				<property name="password" value="${jdbc.password}" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<!-- Using classpath relative resources -->
		<mapper resource="mapper/UserMapper.xml" />
		<!-- Using mapper interface classes -->
		<mapper class="org.fool.mybatis.mapper.UserMapper" />
	</mappers>
</configuration>

 注意：这里使用UserMapper.xml和UserMapper接口，只是为了说明一下，有两种实现方式，想使用哪种，全看个人喜好。

 

jdbc.properties

jdbc.driverClassName = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8 
jdbc.username = root
jdbc.password = 123456

 

schema.sql

drop table if exists t_user ;

create table t_user
(
	uid integer not null auto_increment,
	username varchar(20) not null,
	password varchar(20) not null,
	nickname varchar(20) not null,
	email varchar(30) not null,
	constraint user_uid_pk primary key(uid)
);

insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "[e-mail]");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "[e-mail]");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "[e-mail]");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "[e-mail]");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "[e-mail]");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "[e-mail]");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "[e-mail]");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "[e-mail]");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "[e-mail]");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "[e-mail]");

commit;

 

UserMapper.xml

<?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="org.fool.mybatis.model.User">

	<insert id="add" parameterType="User">
		insert into t_user(username, password, nickname, email)
			values(#{username}, #{password}, #{nickname}, #{email})
	</insert>
	
	<update id="update" parameterType="User">
		update t_user set password = #{password}, nickname = #{nickname}, email = #{email}
			where uid = #{uid}
	</update>
	
	<delete id="delete" parameterType="int">
		delete from t_user where uid = #{uid}
	</delete>
	
	<select id="load" parameterType="int" resultType="User">
		select * from t_user where uid = #{uid}
	</select>
	
	<select id="list" resultType="User">
		select * from t_user
	</select>
	
	<select id="load_by_username" parameterType="string" resultType="User">
		select * from t_user where username = #{username}
	</select>
</mapper>

 

4.src/main/java

User.java

package org.fool.mybatis.model;

public class User
{
	private int uid;
	private String username;
	private String password;
	private String nickname;
	private String email;

	public int getUid()
	{
		return uid;
	}

	public void setUid(int uid)
	{
		this.uid = uid;
	}

	public String getUsername()
	{
		return username;
	}

	public void setUsername(String username)
	{
		this.username = username;
	}

	public String getPassword()
	{
		return password;
	}

	public void setPassword(String password)
	{
		this.password = password;
	}

	public String getNickname()
	{
		return nickname;
	}

	public void setNickname(String nickname)
	{
		this.nickname = nickname;
	}

	public String getEmail()
	{
		return email;
	}

	public void setEmail(String email)
	{
		this.email = email;
	}

	@Override
	public String toString()
	{
		return "User [uid=" + uid + ", username=" + username + ", password="
				+ password + ", nickname=" + nickname + ", email=" + email
				+ "]";
	}

}

 MyBatisUtil.java

package org.fool.mybatis.util;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisUtil
{
	private static SqlSessionFactory sessionFactory;

	private MyBatisUtil()
	{
	}

	static
	{
		try
		{
			InputStream is = Resources
					.getResourceAsStream("mybatis-config.xml");

			sessionFactory = new SqlSessionFactoryBuilder().build(is);
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
	}

	public static SqlSessionFactory getSqlSessionFactory()
	{
		return sessionFactory;
	}
}

 UserMapper.java

package org.fool.mybatis.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.fool.mybatis.model.User;

public interface UserMapper
{
	@Insert("insert into t_user(username, password, nickname, email) values(#{username}, #{password}, #{nickname}, #{email})")
	public void add(User user);

	@Update("update t_user set password = #{password}, nickname = #{nickname}, email = #{email} where uid = #{uid}")
	public void update(User user);

	@Delete("delete from t_user where uid = #{uid}")
	public void delete(int id);

	@Select("select * from t_user where uid = #{uid}")
	public User load(int id);

	@Select("select * from t_user")
	public List<User> list();
	
	@Select("select * from t_user where username = #{username}")
	public User loadByUsername(String username);
}

 

5.src/test/java

TestMyBatis.java

package org.fool.mybatis.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.fool.mybatis.model.User;
import org.fool.mybatis.util.MyBatisUtil;
import org.junit.Assert;
import org.junit.Test;

public class TestMyBatis
{

	@Test
	public void testAdd()
	{
		SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();

		User user = new User();
		user.setUsername("Hibernate");
		user.setPassword("123456");
		user.setNickname("Little Hi");
		user.setEmail("[e-mail]");

		session.insert(User.class.getName() + ".add", user);

		session.commit();

		session.close();
	}

	@Test
	public void testLoad()
	{
		SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();

		User user = session.selectOne(User.class.getName() + ".load", 11);

		System.out.println(user);

		session.close();
	}

	@Test
	public void testUpdate()
	{
		SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();

		User user = session.selectOne(User.class.getName() + ".load", 11);
		
		System.out.println(user);

		user.setPassword("mybatis");
		user.setNickname("orm");
		user.setEmail("[e-mail]");
		
		session.update(User.class.getName() + ".update", user);

		session.commit();

		System.out.println(user);

		session.close();
	}

	@Test
	public void testDelete()
	{
		SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();

		User user = session.selectOne(User.class.getName() + ".load", 11);

		System.out.println(user);

		session.delete(User.class.getName() + ".delete", user);

		session.commit();

		session.close();
	}

	@Test
	public void testList()
	{
		SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();

		List<User> users = session.selectList(User.class.getName() + ".list",
				null);

		Assert.assertEquals(10, users.size());

		session.close();
	}

	@Test
	public void testLoadByUsername()
	{
		SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();

		User user = session.selectOne(User.class.getName()
				+ ".load_by_username", "Hibernate");
		
		System.out.println(user);
		
		session.close();
	}
}

 TestMyBatisByMapper.java

package org.fool.mybatis.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.fool.mybatis.mapper.UserMapper;
import org.fool.mybatis.model.User;
import org.fool.mybatis.util.MyBatisUtil;
import org.junit.Assert;
import org.junit.Test;

public class TestMyBatisByMapper
{

	@Test
	public void testAdd()
	{
		SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();

		User user = new User();
		user.setUsername("Hibernate");
		user.setPassword("123456");
		user.setNickname("Little Hi");
		user.setEmail("[e-mail]");

		session.getMapper(UserMapper.class).add(user);

		session.commit();

		session.close();
	}

	@Test
	public void testLoad()
	{
		SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();

		User user = session.getMapper(UserMapper.class).load(11);

		System.out.println(user);

		session.close();
	}

	@Test
	public void testUpdate()
	{
		SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();

		User user = session.getMapper(UserMapper.class).load(11);

		System.out.println(user);

		user.setPassword("mybatis");
		user.setNickname("orm");
		user.setEmail("[e-mail]");

		session.getMapper(UserMapper.class).update(user);

		session.commit();

		System.out.println(user);

		session.close();
	}

	@Test
	public void testDelete()
	{
		SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();

		session.getMapper(UserMapper.class).delete(11);

		session.commit();

		session.close();
	}

	@Test
	public void testList()
	{
		SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();

		List<User> users = session.getMapper(UserMapper.class).list();

		Assert.assertEquals(10, users.size());

		session.close();
	}

	@Test
	public void testLoadByUsername()
	{
		SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();

		User user = session.getMapper(UserMapper.class).loadByUsername(
				"Hibernate");

		System.out.println(user);

		session.close();
	}
}

 

 6.Best in Practice

创建相应的MyBatisUtil完成对Session的获取

不使用annotation完成sql的映射