mybatis示例集合
https://www.cnblogs.com/curen/p/5942583.html
本文适合对mybatis有简单的入门了解的读者。转载请注明出处！
环境：
Mysql5.6、navicat for mysql 11、jdk8、maven3.3.9、mybatis3.2.8
MySQL表
CREATE TABLE `sale` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sale_name` varchar(255) DEFAULT NULL, `sale_age` int(11) DEFAULT NULL, `user_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=gbk;
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `age` int(11) NOT NULL, `name` varchar(255) NOT NULL, `user_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=gbk;
maven依赖配置(pom.xml)
1 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 2 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> 3 <modelVersion>4.0.0</modelVersion> 4 <groupId>com</groupId> 5 <artifactId>batistest</artifactId> 6 <packaging>war</packaging> 7 <version>0.0.1-SNAPSHOT</version> 8 <name>batistest Maven Webapp</name> 9 <url>http://maven.apache.org</url> 10 <dependencies> 11 <dependency> 12 <groupId>junit</groupId> 13 <artifactId>junit</artifactId> 14 <version>3.8.1</version> 15 <scope>test</scope> 16 </dependency> 17 <dependency> 18 <groupId>org.mybatis</groupId> 19 <artifactId>mybatis</artifactId> 20 <version>3.2.8</version> 21 </dependency> 22 <dependency> 23 <groupId>mysql</groupId> 24 <artifactId>mysql-connector-java</artifactId> 25 <version>5.1.38</version> 26 </dependency> 27 <dependency> 28 <groupId>log4j</groupId> 29 <artifactId>log4j</artifactId> 30 <version>1.2.17</version> 31 </dependency> 32 </dependencies> 33 <build> 34 <finalName>batistest</finalName> 35 </build> 36 </project>
mybatis配置文件（conf.xml）
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> 3 <configuration> 4 <environments default="development"> 5 <environment id="development"> 6 <transactionManager type="JDBC" /> 7 <!-- 配置数据库连接信息 --> 8 <dataSource type="POOLED"> 9 <property name="driver" value="com.mysql.jdbc.Driver" /> 10 <property name="url" value="jdbc:mysql://localhost:3308/rfid" /> 11 <property name="username" value="admin" /> 12 <property name="password" value="dps2010_admin" /> 13 </dataSource> 14 </environment> 15 </environments> 16 17 <!-- 注册映射文件 --> 18 <mappers> 19 <mapper resource="demo/mybatis/base/mapper/User.xml" /> 20 <mapper resource="demo/mybatis/base/mapper/IUser.xml" /> 21 <mapper resource="demo/mybatis/base/mapper/IRelate.xml" /> 22 </mappers> 23 </configuration>
mybatis映射文件(demo/mybatis/base/mapper/User.xml)
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3 <!-- 唯一命名空间，通常定义规则为：所在包名+文件名(映射文件) -->
4 <mapper namespace="demo.mybatis.base.mapper.User">
5
6 <!--
7 缓存启用
8
9 缓存更新算法
10 LRU － 最近最少使用的：移除最长时间不被使用的对象
11 FIFO － 先进先出：按对象进入缓存的时间顺序来移除
12 SOFT － 软引用：移除基于垃圾回收器状态和软引用规则的对象
13 WEAK － 弱引用：更积极地移除基于垃圾回收器状态和弱引用规则的对象
14
15 size:最大缓存对象或集合
16 flushInterval:缓存刷新间隔,60秒
17 readOnly:缓存是否为只读
18 -->
19 <cache eviction="FIFO" size="512" flushInterval="60000" readOnly="false"></cache>
20
21
22 <!-- SQL引用 -->
23 <sql id="column">id,age,name</sql>
24
25 <!--
26 查询标签
27 2、id为标签的唯一标识
28 3、parameterType为参数类型
29 4、resultType为返回结果类型
30
31 注意:include的refid为要引用的SQL标签的ID
32 -->
33 <select id="queryUser" parameterType="int" resultType="demo.modul.User" >
34 select <include refid="column"></include> from user where id=#{id}
35 </select>
36
37 <!--
38 以下演示基本的：插入标签、更新标签、删除标签
39 1、parameterType 参数类型,可以为基本数据类型，也可以为定义的对象
40 2、useGeneratedKeys 是否为自动生成主键
41 3、keyProperty 数据库主键对应的实体类的属性名
42
43 注意：若数据库不支持主键，则可以使用selectKey生成主键
44 -->
45 <insert id="insertUser" parameterType="demo.modul.User" useGeneratedKeys="true" keyProperty="id">
46 insert into user(name,age) values(#{name},#{age})
47 </insert>
48
49
50 <update id="updateUser" parameterType="demo.modul.User" >
51 update user set age=#{age} where name=#{name}
52 </update>
53
54 <delete id="deleteUser" parameterType="int">
55 delete from user where id=#{id}
56 </delete>
57
58
59
60 <!--
61 MyBatis动态元素演示1： if条件语句
62 -->
63 <select id="testIf" parameterType="demo.modul.User" resultType="demo.modul.User">
64 select <include refid="column"></include> from user
65 <where>
66 <if test="name!=null"> name like '%' #{name} '%'</if>
67 </where>
68 </select>
69
70 <!--
71 MyBatis动态元素演示2： choose分支结构
72 -->
73 <select id="testChoose" parameterType="demo.modul.User" resultType="demo.modul.User">
74 select <include refid="column"></include> from user WHERE 1=1
75 <choose>
76 <when test="name!=null"> AND name like '%' #{name} '%'</when>
77 <when test="age!=null"> AND age>#{age}</when>
78 <otherwise>AND id=9</otherwise>
79 </choose> limit 0,1
80 </select>
81
82 <!--
83 MyBatis动态元素演示3： foreach遍历
84 -->
85 <delete id="testForeach" parameterType="java.util.List" >
86 delete from user where id in
87 <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
88 #{item}
89 </foreach>
90 </delete>
91
92 <!--
93 MyBatis动态元素演示4： where
94 注意：
95 1、当第一个if条件不成立时，mybatis会自动去除第一个条件的AND连接符
96 2、只有有任何一条条件成立，则自动添加WHERE关键字
97 -->
98 <select id="testWhere" parameterType="demo.modul.User" resultType="demo.modul.User">
99 select <include refid="column"></include> from user
100 <where>
101 <if test="name!=null">name like '%' #{name} '%'</if>
102 <if test="age!=null">AND age >10</if>
103 </where>
104 </select>
105
106 <!--
107 MyBatis动态元素演示5： set
108 -->
109 <update id="testSet" parameterType="demo.modul.User">
110 update user
111 <set>
112 <if test="name!=null">name = #{name},</if>
113 <if test="age!=null">age = #{age}</if>
114 </set>
115 where id=#{id}
116 </update>
117
118
119 </mapper>
demo/mybatis/base/mapper/IUser.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="demo.mybatis.base.IUser">
<!-- SQL引用 -->
<sql id="column">id,age,name</sql>
<!--
查询标签
2、id为标签的唯一标识
3、parameterType为参数类型
4、resultType为返回结果类型
注意:include的refid为要引用的SQL标签的ID
-->
<select id="getUser" parameterType="int" resultType="demo.modul.User" >
select <include refid="column"></include> from user where id=#{id}
</select>
<select id="getAllUser" parameterType="int" resultType="demo.modul.User" >
select <include refid="column"></include> from user where age>#{age}
</select>
</mapper>
demo/mybatis/base/mapper/IRelate.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="demo.mybatis.base.IRelate">
<!--
演示1：嵌套查询、多对一的关系
注意：此方式存在N+1性能的问题，意思就是每行记录对应的记录都会生产一个子查询
类似于这样的SQL: SELECT table1.id,table1.name,(select sum(table2.age) from table2 where table2.name = table1.name) from table1
-->
<resultMap type="demo.modul.Sale" id="userMap">
<id property="id" column="id"/>
<result property="name" column="sale_name"/>
<result property="age" column="sale_age"/>
<association property="user" column="user_id" select="queryOneUser" javaType="demo.modul.User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
</association>
</resultMap>
<select id="queryOneUser" parameterType="int" resultType="demo.modul.User" >
select id,name,age from user where user_id = #{user_id}
</select>
<select id="querySale" parameterType="int" resultMap="userMap">
select id,sale_name,sale_age,user_id from sale where id = 1
</select>
<!--
演示2：嵌套结果、多对一的关系
注意：解决N+1的SQL查询性能问题
-->
<resultMap type="demo.modul.Sale" id="userMap1">
<id property="id" column="sale_id"/>
<result property="name" column="sale_name"/>
<result property="age" column="sale_age"/>
<association property="user" resultMap="userResult" />
</resultMap>
<resultMap type="demo.modul.User" id="userResult" >
<result property="name" column="user_name"/>
<result property="age" column="user_age"/>
</resultMap>
<select id="querySale1" parameterType="int" resultMap="userMap1">
SELECT sale.id as sale_id,sale_age,sale_name,age as user_age,`name` as user_name FROM sale LEFT JOIN `user` on sale.user_id=user.id WHERE sale.id= #{id}
</select>
<!--
演示3：嵌套查询、多对多的关系
注意：存在N+1的SQL查询性能问题
-->
<resultMap type="demo.modul.User" id="userQuery">
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="userId" column="user_id"/>
<collection property="sale" column="user_id" javaType="ArrayList" ofType="demo.modul.Sale" select="querySaleList">
<result property="name" column="name"/>
<result property="age" column="age"/>
</collection>
</resultMap>
<select id="querySaleList" resultType="demo.modul.Sale">
SELECT sale_name as name,sale_age as age FROM sale WHERE user_id=#{user_id}
</select>
<select id="queryUser" parameterType="int" resultMap="userQuery">
SELECT name,age,user_id FROM user WHERE user_id=#{user_id}
</select>
<!--
演示3：嵌套结果、多对多的关系
注意：不存在N+1的SQL查询性能问题
-->
<resultMap type="demo.modul.User" id="userQuery1">
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="userId" column="user_id"/>
<collection property="sale" ofType="demo.modul.Sale" columnPrefix="c_" >
<result property="name" column="sale_name"/>
<result property="age" column="sale_age"/>
</collection>
</resultMap>
<select id="queryUser1" parameterType="int" resultMap="userQuery1">
SELECT user.name,user.age,user.user_id,sale.sale_name as c_sale_name,sale.sale_age as c_sale_age FROM user left join sale on user.user_id = sale.user_id WHERE user.user_id=#{user_id}
</select>
</mapper>
JAVA接口定义
demo.mybatis.base.IUser
package demo.mybatis.base;
import java.util.List;
import demo.modul.User;
public interface IUser {
//获取某个用户
public User getUser(int id);
//获取多个用户
public List<User> getAllUser(int age);
}
demo.mybatis.base.IRelate
package demo.mybatis.base;
import demo.modul.Sale;
import demo.modul.User;
public interface IRelate {
//嵌套查询、多对一
public Sale querySale(int id);
//嵌套结果、多对多
public Sale querySale1(int id);
//嵌套查询、一对多
public User queryUser(int id);
//嵌套结果、一对多
public User queryUser1(int id);
}
javabean
demo.modul.User
package demo.modul;
import java.util.List;
public class User {
private int id;
private String name;
private int age;
private List<Sale> sale;
private int userId;
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 List<Sale> getSale() {
return sale;
}
public void setSale(List<Sale> sale) {
this.sale = sale;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
}
demo.modul.Sale
package demo.modul;
public class Sale {
private int id;
private int age;
private String name;
private User user;
private int userId;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
}
测试：
package demo.modul;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import demo.mybatis.base.IRelate;
import demo.mybatis.base.IUser;
public class test {
public static void main(String[] args) {
//mybatis的配置文件
String resource = "conf.xml";
//使用类加载器加载mybatis的配置文件（它也加载关联的映射文件）
InputStream is = test.class.getClassLoader().getResourceAsStream(resource);
//构建SqlSession的工厂
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
//创建能执行映射文件中sql的SqlSession
SqlSession session = sessionFactory.openSession();
//执行SQL
getRelateUserQuery1(session);
}
/****************************** 增删改查测试 *********************************/
//查询用户
private static void queryUser(SqlSession session)
{
//配置文件 命名空间+标签ID
String statement = "demo.mybatis.base.mapper.User.queryUser";
//执行查询，并根据配置文件返回结果到对象
User user = session.selectOne(statement,2);
//数据输出
System.out.println("ID："+user.getId());
System.out.println("姓名："+user.getName());
System.out.println("年龄："+user.getAge());
}
//插入用户
private static void insertUser(SqlSession session)
{
//参数设置
User u = new User();
u.setAge(15);
u.setName("测试插入");
//配置文件对应的命名空间+标签ID
String statement = "demo.mybatis.base.mapper.User.insertUser";
//解析SQL并替换参数
int cnt = session.insert(statement, u);
//提交SQL
session.commit();
//返回结果
System.out.println("受影响行数:"+cnt);
}
//删除用户
private static void deleteUser(SqlSession session)
{
//命名空间+标签ID
String statement = "demo.mybatis.base.mapper.User.deleteUser";
//解析并执行SQL
int cnt = session.update(statement, 6);
//提交SQL
session.commit();
//结果
System.out.println("受影响行数");
}
//更新用户
private static void updateUser(SqlSession session)
{
//设置参数
User u = new User();
u.setName("测试插入");
u.setAge(26);
//命名空间+标签ID
String statement = "demo.mybatis.base.mapper.User.updateUser";
//解析SQL并替换参数
int cnt = session.insert(statement,u);
//提交参数SQL
session.commit();
//返回结果
System.out.println("受影响行数:"+cnt);
}
/********************* 条件测试 **************************/
//测试IF条件
private static void testIf(SqlSession session)
{
User condition = new User();
condition.setAge(2);
condition.setName("楷");
String statement = "demo.mybatis.base.mapper.User.testIf";
User u = session.selectOne(statement, condition);
System.out.println("年龄:"+u.getAge()+" 姓名:"+u.getName()+" ID:"+u.getId());
}
//测试choose分支结构
private static void testChoose(SqlSession session)
{
User condition = new User();
condition.setName("楷");
String statement = "demo.mybatis.base.mapper.User.testChoose";
User u = session.selectOne(statement, condition);
System.out.println("年龄:"+u.getAge()+" 姓名:"+u.getName()+" ID:"+u.getId());
}
//测试foreach遍历
private static void testForeach(SqlSession session)
{
List<Integer> list = new ArrayList<Integer>();
list.add(1);
list.add(2);
list.add(3);
list.add(4);
//命名空间+标签ID
String statement = "demo.mybatis.base.mapper.User.testForeach";
//解析并执行SQL
int cnt = session.update(statement, list);
//提交SQL
session.commit();
//结果
System.out.println("受影响行数"+cnt);
}
//测试where条件
private static void testWhere(SqlSession session)
{
User condition = new User();
condition.setName("楷");
String statement = "demo.mybatis.base.mapper.User.testWhere";
User u = session.selectOne(statement, condition);
System.out.println("年龄:"+u.getAge()+" 姓名:"+u.getName()+" ID:"+u.getId());
}
//测试set条件
private static void testSet(SqlSession session)
{
User condition = new User();
condition.setName("楷");
condition.setId(1);
String statement = "demo.mybatis.base.mapper.User.testSet";
int cnt = session.update(statement, condition);
System.out.println("受影响的行数:"+cnt);
}
/***接口式映射测试***/
//查询某个用户
private static void getUser(SqlSession session)
{
try {
System.out.println(IUser.class);
IUser cp = session.getMapper(IUser.class);
User u = cp.getUser(2);
if (null == u) {
System.out.println("没有符合条件的记录");
} else {
System.out.println("年龄"+u.getAge());
System.out.println("姓名"+u.getName());
System.out.println("ID"+u.getId());
}
} finally {
session.close();
}
}
// 查询多个用户用户
private static void getAllUser(SqlSession session) {
try {
IUser cp = session.getMapper(IUser.class);
List<User> users = cp.getAllUser(2);
if (users.size()==0) {
System.out.println("没有符合条件的记录");
} else {
for(User u:users)
{
System.out.println("年龄:"+u.getAge()+" 姓名:"+u.getName()+" ID:"+u.getId());
}
}
} finally {
session.close();
}
}
/************************关联查询*****************************/
//关联查询测试，嵌套查询，多对一关系，存在N+1的SQL性能问题
private static void getRelateQuery(SqlSession session)
{
IRelate relate = session.getMapper(IRelate.class);
Sale sale = relate.querySale(1);
System.out.println("业务姓名:"+sale.getName()+" 业务年龄:"+sale.getAge()+" 业务id:"+sale.getId()+ " 客户姓名:"+sale.getUser().getName()+" 客户年龄:"+sale.getUser().getAge());
}
//关联查询测试，嵌套结果，多对一关系 ，解决N+1的SQL性能问题
private static void getRelateQuery1(SqlSession session)
{
IRelate relate = session.getMapper(IRelate.class);
Sale sale = relate.querySale1(1);
System.out.println("业务姓名:"+sale.getName()+" 业务年龄:"+sale.getAge()+" 业务id:"+sale.getId()+ " 客户姓名:"+sale.getUser().getName()+" 客户年龄:"+sale.getUser().getAge());
}
//关联查询集合，嵌套查询，多对多关系，存在N+1的性能问题
private static void getRelateUserQuery(SqlSession session)
{
IRelate relate = session.getMapper(IRelate.class);
User user = relate.queryUser(5);
System.out.println("姓名:"+user.getName()+" 年龄:"+user.getAge()+" 用户ID:"+user.getUserId());
for(Sale s:user.getSale())
{
System.out.println("销售名称:"+s.getName()+" 销售年龄:"+s.getAge());
}
}
//关联查询集合，嵌套结果，多对多关系，解决N+1的性能问题
private static void getRelateUserQuery1(SqlSession session)
{
IRelate relate = session.getMapper(IRelate.class);
User user = relate.queryUser1(5);
System.out.println("姓名:"+user.getName()+" 年龄:"+user.getAge()+" 用户ID:"+user.getUserId());
for(Sale s:user.getSale())
{
System.out.println("销售名称:"+s.getName()+" 销售年龄:"+s.getAge());
}
}
}
项目结构：
浙公网安备 33010602011771号