MyBatis3
testcase for Mybatis:
数据库:
blog:
CREATE TABLE `blog` (
`blogid` int(11) NOT NULL AUTO_INCREMENT,
`blogtile` char(20) DEFAULT NULL,
`authorid` int(11) NOT NULL,
PRIMARY KEY (`blogid`),
KEY `fk_authorid` (`authorid`),
CONSTRAINT `fk_authorid` FOREIGN KEY (`authorid`) REFERENCES `author` (`authorid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
author:
CREATE TABLE `author` (
`authorid` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
PRIMARY KEY (`authorid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
person:
CREATE TABLE `person` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` char(100) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
post:
CREATE TABLE `post` (
`postid` int(11) NOT NULL AUTO_INCREMENT,
`postcomment` char(20) DEFAULT NULL,
`blogid` int(11) NOT NULL,
PRIMARY KEY (`postid`),
KEY `fk_blogid` (`blogid`),
CONSTRAINT `fk_blogid` FOREIGN KEY (`blogid`) REFERENCES `blog` (`blogid`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "mybatis-3-config.dtd" >
<configuration>
<!-- 关联属性文件,使用${}使用 -->
<properties resource="db.properties"/>
<!-- 定义类型别名 -->
<typeAliases>
<!-- <typeAlias type="" alias=""/> -->
<package name="main.bean"/>
</typeAliases>
<!-- 配置数据库环境信息 -->
<environments default="mysql">
<environment id="mysql">
<!--指定事务管理类型[JDBC|MANAGED] -->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置JDBC连接对象的资源 POOLED:数据源连接池实现 -->
<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="main/config/mapper/person-mapper.xml"/>
</mappers>
</configuration>
author.java
package main.bean;
public class author {
private int authorid;
private String name;
private String sex;
public author() {
super();
// TODO Auto-generated constructor stub
}
public author(int authorid, String name, String sex) {
super();
this.authorid = authorid;
this.name = name;
this.sex = sex;
}
public int getAuthorid() {
return authorid;
}
public void setAuthorid(int authorid) {
this.authorid = authorid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "author [authorid=" + authorid + ", name=" + name + ", sex=" + sex + "]";
}
}
package main.bean;
import java.util.List;
public class blog {
private int blogid;
private String blogtile;
private author aut;
private List<post> posts;
public blog() {
super();
// TODO Auto-generated constructor stub
}
public blog(int blogid, String blogtile, author aut, List<post> posts) {
super();
this.blogid = blogid;
this.blogtile = blogtile;
this.aut = aut;
this.posts = posts;
}
public int getBlogid() {
return blogid;
}
public void setBlogid(int blogid) {
this.blogid = blogid;
}
public String getBlogtile() {
return blogtile;
}
public void setBlogtile(String blogtile) {
this.blogtile = blogtile;
}
public author getAut() {
return aut;
}
public void setAut(author aut) {
this.aut = aut;
}
public List<post> getPosts() {
return posts;
}
public void setPosts(List<post> posts) {
this.posts = posts;
}
@Override
public String toString() {
return "blog [blogid=" + blogid + ", blogtile=" + blogtile + ", aut=" + aut + ", posts=" + posts + "]";
}
}
person.java
package main.bean;
public class person {
private int id;
private String name;
private int age;
private String sex;
public person() {
super();
// TODO Auto-generated constructor stub
}
public person(int id, String name, int age, String sex) {
super();
this.id = id;
this.name = name;
this.age = age;
this.sex = sex;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "person [id=" + id + ", name=" + name + ", age=" + age + ", sex=" + sex + "]";
}
}
post.java
package main.bean;
public class post {
private int postid;
private String postcomment;
private int blogid;
public post() {
super();
// TODO Auto-generated constructor stub
}
public post(int postid, String postcomment, int blogid) {
super();
this.postid = postid;
this.postcomment = postcomment;
this.blogid = blogid;
}
public int getPostid() {
return postid;
}
public void setPostid(int postid) {
this.postid = postid;
}
public String getPostcomment() {
return postcomment;
}
public void setPostcomment(String postcomment) {
this.postcomment = postcomment;
}
public int getBlogid() {
return blogid;
}
public void setBlogid(int blogid) {
this.blogid = blogid;
}
@Override
public String toString() {
return "post [postid=" + postid + ", postcomment=" + postcomment + ", blogid=" + blogid + "]";
}
}
person-mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="main.config.mapper.person-mapper">
<select id="selectPerson" parameterType="int" resultType="person">
SELECT * FROM person WHERE id = ${_parameter}
</select>
<insert id="insertPerson" parameterType="person">
INSERT INTO person(name, age, sex) VALUES(#{name}, #{age}, #{sex})
</insert>
<update id="updatePerson" parameterType="person">
UPDATE person SET
name = #{name},
age = #{age},
sex = #{sex}
WHERE id = #{id}
</update>
<delete id="deletePerson" parameterType="int">
DELETE FROM person WHERE id = #{_parameter}
</delete>
<!-- 一对一关系 -->
<select id="selectBlog" parameterType="int" resultMap="blogResult">
SELECT * FROM blog INNER JOIN author ON blog.authorid = author.authorid WHERE blog.blogid = #{_parameter}
</select>
<resultMap id="blogResult" type="blog">
<id property="blogid" column="blogid"/>
<result property="blogtile" column="blogtile"/>
<association property="aut" column="authorid" javaType="author" resultMap="authorResult"></association>
</resultMap>
<!-- author表和author的映射关系 -->
<resultMap id="authorResult" type="author">
<id property="authorid" column="authorid"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
</resultMap>
<!-- 一对多关系 -->
<select id="selectBlogAndPosts" parameterType="int" resultMap="blogResult2">
SELECT * FROM blog INNER JOIN post ON blog.blogid = post.blogid WHERE blog.blogid = #{_parameter}
</select>
<resultMap type="blog" id="blogResult2">
<id property="blogid" column="blogid"/>
<result property="blogtile" column="blogtile"/>
<collection property="posts" ofType="post" resultMap="postResult"/>
</resultMap>
<resultMap type="post" id="postResult">
<id property="postid" column="postid"/>
<result property="postcomment" column="postcomment"/>
<result property="blogid" column="blogid"/>
</resultMap>
<!-- 动态sql foreach -->
<select id="selectForeach" parameterType="list" resultMap="blogResult2">
SELECT * FROM blog INNER JOIN post ON blog.blogid = post.blogid
WHERE blog.blogid IN
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
</mapper>
Factory.java
package main.factory;
import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class Factory {
private static Factory factory;
private static SqlSessionFactory sqlSessionFactory;
private Factory(){
try{
String resource = "main/config/mybatis-config.xml";
Reader reader = Resources.getResourceAsReader(resource);
sqlSessionFactory = (SqlSessionFactory) new SqlSessionFactoryBuilder().build(reader);
}catch(IOException e){
System.out.println(e.getMessage());
}catch(Exception e){
System.out.println(e.getMessage());
}
}
public static SqlSessionFactory getFactory(){
if(factory == null){
factory = new Factory();
return factory.sqlSessionFactory;
}else
return factory.sqlSessionFactory;
}
}
test.java
package main.test;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
import main.bean.blog;
import main.bean.person;
import main.factory.Factory;
public class test {
@Test
public void testInsert(){
SqlSessionFactory factory = Factory.getFactory();
SqlSession session = null;
person p1 = new person(2, "lisi", 12, "w");
try{
//session = factory.openSession(true);
//默认是手动提交事务
session = factory.openSession();
int result = session.insert("main.config.mapper.person-mapper.insertPerson", p1);
//需要手动提交
session.commit();
System.out.println(result);
}catch(Exception e){
System.out.println(e.toString());
}finally{
session.close();
}
}
@Test
public void testSelect(){
SqlSessionFactory factory = Factory.getFactory();
SqlSession session = null;
try{
session = factory.openSession();
person p = session.selectOne("main.config.mapper.person-mapper.selectPerson", 2);
System.out.println(p.toString());
//虽然session的方法签名不需要catch异常
}catch(Exception e){
System.out.println(e.toString());
}finally{
session.close();
}
}
@Test
public void testUpdate(){
SqlSessionFactory factory = Factory.getFactory();
SqlSession session = null;
person p1 = new person(11, "houshaoli", 25, "m");
try{
session = factory.openSession(true);
int result = session.update("main.config.mapper.person-mapper.updatePerson", p1);
System.out.println(result);
}catch(Exception e){
System.out.println(e.toString());
}finally{
session.close();
}
}
@Test
public void testDelete(){
SqlSessionFactory factory = Factory.getFactory();
SqlSession session = null;
try{
session = factory.openSession(true);
int result = session.delete("main.config.mapper.person-mapper.deletePerson", 11);
System.out.println(result);
}catch(Exception e){
System.out.println(e.toString());
}finally{
session.close();
}
}
//一对一映射
@Test
public void testSelectOneToOne(){
SqlSessionFactory factory = Factory.getFactory();
SqlSession session = null;
try{
session = factory.openSession(true);
blog b1 = session.selectOne("main.config.mapper.person-mapper.selectBlog", 1);
System.out.println(b1.toString());
}catch(Exception e){
System.out.println(e.toString());
}finally{
session.close();
}
}
//一对多集合
@Test
public void testSelectOneToMutil(){
SqlSessionFactory factory = Factory.getFactory();
SqlSession session = null;
try{
session = factory.openSession(true);
blog b = session.selectOne("main.config.mapper.person-mapper.selectBlogAndPosts", 1);
System.out.println(b.toString());
}catch(Exception e){
System.out.println(e.toString());
}finally{
session.close();
}
}
//动态sql foreach实例
@Test
public void testSelectOneToMutil2(){
SqlSessionFactory factory = Factory.getFactory();
SqlSession session = null;
try{
session = factory.openSession(true);
List<String> list = new ArrayList<String>();
list.add("2");
list.add("1");
list.add("6");
List<blog> blist = session.selectList("main.config.mapper.person-mapper.selectForeach", list);
for(blog b: blist){
System.out.println(b.toString());
}
}catch(Exception e){
System.out.println(e.toString());
}finally{
session.close();
}
}
}