MyBatis CRUD Operations

-★出爺;3

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&amp;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", "mybatis@orm.org");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "mybatis@orm.org");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "mybatis@orm.org");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "mybatis@orm.org");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "mybatis@orm.org");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "mybatis@orm.org");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "mybatis@orm.org");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "mybatis@orm.org");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "mybatis@orm.org");
insert into t_user(username, password, nickname, email)
values("mybatis", "mybatis", "orm", "mybatis@orm.org");
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("hibernate@orm.org");
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("mybatis@orm.org");
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("hibernate@orm.org");
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("mybatis@orm.org");
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的映射