MyBatis
mybatis-3.4.6
jdk1.8+
一、mybatis入门
1.依赖jar包
- pom.xml引入依赖
<dependencies>
<!-- mybatis依赖及需要的依赖start -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<version>3.2.9</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>ognl</groupId>
<artifactId>ognl</artifactId>
<version>3.2.8</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.25</version>
</dependency>
<!-- mybatis依赖及需要的依赖 end -->
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.7</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.9.1</version>
<!-- 2.10及以上版本新增了对java9的特性支持,不兼容 -->
</dependency>
</dependencies>
2. MyBatis 配置文件
- 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">
<!-- 配置文件包含mybatis核心设置 -->
<configuration>
<!-- 引入外部资源文件 -->
<properties resource="db.properties"></properties>
<settings>
<!-- 指定mybatis所用日志的具体实现 -->
<setting name="logImpl" value="LOG4J"/>
<!-- 设置驼峰匹配 -->
<setting name="mapUnderscoreToCamelCase" value="true" />
</settings>
<!-- 设置包扫描(别名) -->
<typeAliases>
<package name="com.zhougl.web.beans" />
</typeAliases>
<!-- 配置环境:可以配置多个环境,default:配置某一个环境的唯一标识,表示默认使用哪个环境 -->
<environments default="development">
<environment id="development">
<!-- 指定事务管理类型,type="JDBC"指直接简单实用了JDBC提交和回滚设置 -->
<transactionManager type="JDBC" />
<!-- dataSource指配置数据源,type="POOLED"是JDBC连接对象的数据源连接池的实现 -->
<dataSource type="POOLED">
<!-- 配置连接信息 -->
<property name="driver" value="${jdbc.driverClass}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<!-- 配置持久化类映射文件:用来配置sql语句和结果集类型等 -->
<mappers>
<mapper resource="com/zhougl/web/dao/mapper/PhoneUserMapper.xml" />
</mappers>
</configuration>
3. Mapper映射文件
- PhoneUserMapper.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">
<!-- namespace习惯上设置成包名+sql映射文件(PhoneUserMapper.xml去除后缀) -->
<mapper namespace="com.zhougl.web.dao.mapper.PhoneUserMapper">
<resultMap id="BaseResultMap" type="com.zhougl.web.beans.PhoneUser">
<id column="ID" jdbcType="VARCHAR" property="id" />
<result column="NAME" jdbcType="VARCHAR" property="name" />
<result column="BIRTH_DAY" jdbcType="TIMESTAMP" property="birthDay" />
<result column="ID_NO" jdbcType="VARCHAR" property="idNo" />
<result column="MOBILE_TEL" jdbcType="VARCHAR" property="mobileTel" />
<result column="E_MAIL" jdbcType="VARCHAR" property="eMail" />
<result column="PASSWD" jdbcType="VARCHAR" property="passwd" />
<result column="CREATE_DATE" jdbcType="TIMESTAMP" property="createDate" />
</resultMap>
<insert id="insertPhoneUser" parameterType="com.zhougl.web.beans.PhoneUser">
<selectKey keyProperty="id" resultType="java.lang.String" order="BEFORE">
select SQ_PHONE_USER_ID.nextval as id from dual
</selectKey>
insert into PHONE_USER (ID, NAME, BIRTH_DAY,
ID_NO, MOBILE_TEL, E_MAIL,
PASSWD, CREATE_DATE)
values (#{id,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, #{birthDay,jdbcType=TIMESTAMP},
#{idNo,jdbcType=VARCHAR}, #{mobileTel,jdbcType=VARCHAR}, #{eMail,jdbcType=VARCHAR},
#{passwd,jdbcType=VARCHAR}, #{createDate,jdbcType=TIMESTAMP})
</insert>
<select id="selectPhoneUserById" parameterType="String" resultMap="BaseResultMap">
select * from PHONE_USER where ID=#{id,jdbcType=VARCHAR}
</select>
</mapper>
4.MyBatis完成数据操作代码
public class BasicMyBaitisTest {
public static void main(String[] args) throws IOException {
//读取mybatis-config.xml配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//初始化mybatis.创建SQLSessionFactory实例
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//创建session实例
SqlSession session = sqlSessionFactory.openSession();
//创建phoneUser 对象
PhoneUser user = new PhoneUser("test", "吴总", new Date(1992, 9, 26), "54232819800130824X", "15555555858", "wuzong@163.com", "123456", new Date());
//插入数据
//session.insert("com.zhougl.web.dao.mapper.PhoneUserMapper.insertPhoneUser", user);
PhoneUser userOne = session.selectOne("com.zhougl.web.dao.mapper.PhoneUserMapper.selectPhoneUserById", "1");
System.out.println(userOne.toString());
//提交事务
session.commit();
//关闭session
session.close();
}
}
//打印结果
PhoneUser [id=1, name=测试, birthDay=Tue Oct 23 00:00:00 CST 1984, idNo=220724198410236590, mobileTel=199123425678, eMail=ces@qq.com, passwd=1, createDate=Thu Nov 22 00:00:00 CST 2018]
5. log4j日志文件配置
#全局日志配置
#log4j.rootLogger=ERROR,stdout
log4j.rootLogger=DEBUG,Console
#MyBatis日志配置
#log4j.logger.com.zhougl.mapper.UserMapper=DEBUG
#控制台输出
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
6.mybatis-generator插件使用(快速生成代码)
- generatorConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!-- 引入外部资源文件 -->
<properties resource="db.properties"></properties>
<!--
出现错误:Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
解决办法:将本地的MAVEN仓库中的mysql驱动引入进来
-->
<classPathEntry location="D:\Oracle\ojdbc6.jar"/>
<context id="oraclegenerator" targetRuntime="MyBatis3">
<!-- 生成的Java文件的编码 -->
<property name="javaFileEncoding" value="UTF-8"/>
<!-- 格式化java代码 -->
<property name="javaFormatter" value="org.mybatis.generator.api.dom.DefaultJavaFormatter"/>
<!-- 格式化XML代码 -->
<property name="xmlFormatter" value="org.mybatis.generator.api.dom.DefaultXmlFormatter"/>
<!--不生成注释-->
<commentGenerator>
<property name="suppressAllComments" value="true" />
</commentGenerator>
<!-- 配置数据库连接 -->
<jdbcConnection driverClass="${jdbc.driverClass}"
connectionURL="${jdbc.url}"
userId="${jdbc.username}"
password="${jdbc.password}" />
<!-- java类型处理器
用于处理DB中的类型到Java中的类型,默认使用JavaTypeResolverDefaultImpl;
注意一点,默认会先尝试使用Integer,Long,Short等来对应DECIMAL和 NUMERIC数据类型;
-->
<javaTypeResolver type="org.mybatis.generator.internal.types.JavaTypeResolverDefaultImpl">
<!--
true:使用BigDecimal对应DECIMAL和 NUMERIC数据类型
false:默认,
scale>0;length>18:使用BigDecimal;
scale=0;length[10,18]:使用Long;
scale=0;length[5,9]:使用Integer;
scale=0;length<5:使用Short;
-->
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!-- 指定javaBean生成的位置 -->
<javaModelGenerator targetPackage="com.zhougl.web.beans" targetProject="src/main/java" >
<!-- 在targetPackage的基础上,根据数据库的schema再生成一层package,最终生成的类放在这个package下,默认为false -->
<property name="enableSubPackages" value="true" />
<!-- 设置是否在getter方法中,对String类型字段调用trim()方法 -->
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!--指定sql映射文件生成的位置 -->
<sqlMapGenerator targetPackage="com.zhougl.web.dao.mapper" targetProject="src/main/java" >
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false" />
</sqlMapGenerator>
<!-- 指定dao接口生成的位置,mapper接口 -->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.zhougl.web.dao" targetProject="src/main/java" >
<property name="enableSubPackages" value="false" />
</javaClientGenerator>
<!-- table表生成对应的DoaminObject -->
<!-- 指定表 emp 指定schema 配合enableSubPackages="false" 会让schema作为包的后缀
这里不指定schema,逆向工程会查询sysuser都有哪些schema,对每个schema生成对象-->
<table tableName="PHONE_USER" domainObjectName="PhoneUser"></table>
<!-- <table schema="TJ" tableName="tbl_dept" domainObjectName="Department"></table> -->
</context>
</generatorConfiguration>
二、mybatis基础
1. typeAliases类型设置别名
mybatis-config.xml文件中设置:
<!-- 配置类型别名 User 可以使用在任何使用com.zhougl.web.beans.PhoneUser的地方-->
<typeAliases>
<typeAlias alias="User" type="com.zhougl.web.beans.PhoneUser"/>
</typeAliases>
<!-- 设置包扫描(别名) 制定一个包名,每一个在该包中的java Bean ,没有注解的情况下会使用Bean的首字母小写的非限定类名作为别名,有注解则为主键值@Alias("User")-->
<typeAliases>
<package name="com.zhougl.web.beans" />
</typeAliases>
@Alias("User")
public class PhoneUser {
}
2.mapper映射器
<!-- 配置持久化类映射文件:用来配置sql语句和结果集类型等 -->
<!-- 使用类路径查找资源文件 -->
<mappers>
<mapper resource="com/zhougl/web/dao/mapper/PhoneUserMapper.xml" />
</mappers>
<!-- 使用本地文件 -->
<mappers>
<mapper url="file:///D:\OxygenWorkspace\maven-mybatis\src\main\java\com\zhougl\web\dao\mapper\PhoneUserMapper.xml"/>
</mappers>
<!-- 使用包名 非注解模式的话xml配置文件必须也处于同一级 package 下,且与Mapper类同名-->
<mappers>
<package name="com.zhougl.web.dao.mapper"/>
</mappers>
-
使用接口类
-
mybatis-config.xml
<!-- 使用接口类 --> <mappers> <mapper class="com.zhougl.web.dao.PhoneDepartmentMapper"/> </mappers>
-
接口与映射文件位置
映射文件命名空间
namespace="com.zhougl.web.dao.PhoneDepartmentMapper"
必须与接口类全限定名一直测试代码
PhoneDepartment phoneDepartment = session.selectOne("com.zhougl.web.dao.PhoneDepartmentMapper.selectByPrimaryKey", "1"); //结果 PhoneDepartment [id=1, departmentName=企划部, departmentCode=D01]
-
3.<selectKey>生成主键
<insert id="insert" parameterType="com.zhougl.web.beans.PhoneDepartment">
<selectKey keyProperty="id" resultType="String" order="BEFORE">
select SQ_PHONE_DEPARTMENT_ID.nextval as id from dual
</selectKey>
insert into PHONE_DEPARTMENT (ID, DEPARTMENT_NAME, DEPARTMENT_CODE
)
values (#{id,jdbcType=VARCHAR}, #{departmentName,jdbcType=VARCHAR}, #{departmentCode,jdbcType=VARCHAR}
)
</insert>
4. sql代码段
<!-- 引用 sql 片段,如果refid 指定的不在本文件中,那么需要在前面加上 namespace -->
<!-- 属性代码段可以被包含在其他语句里面 -->
<sql id="sometable">${prefix}Table</sql>
<sql id="someinclude">from <include refid="${include_target}"/></sql>
<!-- 属性值可以用于包含的refid属性或包含的代码段里面的属性 -->
<select id="select" resultType="map">
select field1,field2,field3
<include refid="someinclude">
<property name="prefix" value="Some" />
<property name="include_target" value="sometable"/>
</include>
</select>
5.SqlSessionFactoryUtil工厂类封装方法
public class SqlSessionFactoryUtil {
private static SqlSessionFactory sqlSessionFactory = null;
//创建初始化SqlSessionFactory对象
static {
try {
//读取mybatis-config.xml配置文件
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
//获取SqlSession对象的静态方法
public static SqlSession getSession() {
return sqlSessionFactory.openSession();
}
//获取SqlSessionFactory的静态方法
public static SqlSessionFactory getSqlSessionFactory() {
return sqlSessionFactory;
}
}
6. <association>一对一查询
<association>元素的解释:
- column 表示当前查询数据库表的列名DEPARTMENT_ID
- property 表示返回类型PhoneUserIdAndDepartment属性名department
- javaType 表示该属性对于的类型名称
- select 表示执行的查询语句,将查询到的数据封装到property所代表的类型对象当中
6.1 基本应用
6.1.1 java bean
- WebClass.java
public class WebClass {
private BigDecimal id;
private String code;
private String name;
//班级与学生是一对多的关系
private List<Student> students;
}
- Student.java
public class Student {
private BigDecimal id;
private String name;
private String sex;
private Short age;
//学生与班级是多对一的关系
private WebClass webClass;
}
6.1.1 映射文件
<mapper namespace="com.zhougl.web.dao.StudentDao">
<resultMap id="StudentMap" type="com.zhougl.web.beans.Student">
<id column="id" jdbcType="DECIMAL" property="id" />
<result column="studentName" jdbcType="VARCHAR" property="name" />
<result column="SEX" jdbcType="VARCHAR" property="sex" />
<result column="AGE" jdbcType="DECIMAL" property="age" />
<!-- 一对多 -->
<association property="webClass" column="class_id"
javaType="com.zhougl.web.beans.WebClass"
select="selectClassById"/>
</resultMap>
<select id="selectClassById" parameterType="int" resultType="com.zhougl.web.beans.WebClass">
select * from WEB_CLASS where ID = #{id,jdbcType=DECIMAL}
</select>
<select id="selectStudent" parameterType="int" resultMap="StudentMap">
select * from STUDENT
</select>
</mapper>
6.1.2 测试代码
public static void main(String[] args) {
SqlSession session = SqlSessionFactoryUtil.getSession();
List<Student> students = session.selectList("com.zhougl.web.dao.StudentDao.selectStudent");
students.forEach(student -> System.out.println(student));
session.commit();
session.close();
}
- 结果
==> Preparing: select * from STUDENT
==> Parameters:
====> Preparing: select * from WEB_CLASS where ID = ?
====> Parameters: 1(Integer)
<==== Total: 1
====> Preparing: select * from WEB_CLASS where ID = ?
====> Parameters: 2(Integer)
<==== Total: 1
<== Total: 4
Student [id=1, name=null, sex=女, age=22, webClass=WebClass [id=1, code=C001, name=大乱斗指导班]]
Student [id=2, name=null, sex=女, age=24, webClass=WebClass [id=2, code=C002, name=无限流战斗班]]
Student [id=3, name=null, sex=男, age=28, webClass=WebClass [id=1, code=C001, name=大乱斗指导班]]
Student [id=4, name=null, sex=女, age=26, webClass=WebClass [id=2, code=C002, name=无限流战斗班]]
6.2 一对一映射(接口)
6.2.1 java bean
- card.java
public class Card {
private Integer id;
private String code;
//省略构造器和set/get方法
}
- Person.java
public class Person {
private Integer id;
private String name;
private String sex;
private Short age;
private Card card;
//省略构造器和set/get方法
}
6.2.2 映射文件
- CardMapper.xml
<mapper namespace="com.zhougl.web.dao.mapper.CardMapper">
<sql id="Base_Column_List">
ID, CODE
</sql>
<select id="selectCardById" parameterType="integer" resultType="com.zhougl.web.beans.Card">
select
<include refid="Base_Column_List" />
from CARD
where ID = #{id,jdbcType=DECIMAL}
</select>
</mapper>
- PersonMapper.xml
<mapper namespace="com.zhougl.web.dao.mapper.PersonMapper">
<resultMap id="personMap" type="com.zhougl.web.beans.Person">
<id column="ID" jdbcType="DECIMAL" property="id" />
<result column="NAME" jdbcType="VARCHAR" property="name" />
<result column="SEX" jdbcType="VARCHAR" property="sex" />
<result column="AGE" jdbcType="DECIMAL" property="age" />
<!-- 一对一关联映射 -->
<association property="card" column="CARD_ID"
javaType="com.zhougl.web.beans.Card"
select="com.zhougl.web.dao.mapper.CardMapper.selectCardById" />
</resultMap>
<sql id="Base_Column_List">
ID, NAME, SEX, AGE, CARD_ID
</sql>
<select id="selectPersonById" parameterType="integer"
resultMap="personMap">
select
<include refid="Base_Column_List" />
from PERSON
where ID = #{id,jdbcType=DECIMAL}
</select>
</mapper>
6.2.3 接口
- PersonMapper.java,与PersonMapper.xml在同一个文件夹,接口的类名必须与映射文件namespace一致
public interface PersonMapper {
/**
* 方法名与参数必须和xml文件中<select...>的id属性和parameterType属性一致
* @param id
* @return Person
*/
Person selectPersonById(Integer id);
}
6.2.4 测试类
public class OneToOneTest {
public static void main(String[] args) {
SqlSession session = SqlSessionFactoryUtil.getSession();
//获得mapper接口代理对象
PersonMapper mapper = session.getMapper(PersonMapper.class);
//直接调用接口方法,查询数据
Person person = mapper.selectPersonById(1);
System.out.println(person);
System.out.println(person.getCard());
session.commit();
session.close();
}
}
7 <collection>一对多查询
<collection>元素的解释:
- column 表示当前查询数据库表的列名DEPARTMENT_ID
- property 表示返回类型PhoneUserIdAndDepartment属性名department
- javaType 表示该属性对于的类型名称,本例是一个ArrayList集合
- select 表示执行的查询语句,将查询到的数据封装到property所代表的类型对象当中
- ofType 表示集合当中的类型
7.1 基本应用
7.1.1 java bean
- 同 6.1.1
7.1.2 映射文件
<mapper namespace="com.zhougl.web.dao.WebClassDao">
<resultMap id="WebClassMap"
type="com.zhougl.web.beans.WebClass">
<id column="ID" jdbcType="DECIMAL" property="id" />
<result column="CODE" jdbcType="VARCHAR" property="code" />
<result column="NAME" jdbcType="VARCHAR" property="name" />
<!-- 一对多 -->
<collection property="students" column="id"
javaType="ArrayList" ofType="com.zhougl.web.beans.Student"
select="selectStudentByClassId"
fetchType="lazy" />
</resultMap>
<select id="selectStudentByClassId" parameterType="int" resultType="com.zhougl.web.beans.Student">
select * from STUDENT where class_id = #{classId,jdbcType=DECIMAL}
</select>
<select id="selectClass" parameterType="int" resultMap="WebClassMap">
select * from WEB_CLASS
</select>
</mapper>
7.1.3 测试代码
SqlSession session = SqlSessionFactoryUtil.getSession();
List<WebClass> classes = session.selectList("com.zhougl.web.dao.WebClassDao.selectClass");
classes.forEach(classe ->{
System.out.println(classe);
List<Student> students = classe.getStudents();
students.forEach(student -> System.out.println(student));
});
session.commit();
session.close();
- 结果
==> Preparing: select * from WEB_CLASS
==> Parameters:
<== Total: 2
==> Preparing: select * from STUDENT where class_id = ?
==> Parameters: 2(Integer)
<== Total: 2
WebClass [id=2, code=C002, name=无限流战斗班]
Student [id=2, name=王怡, sex=女, age=24]
Student [id=4, name=王多燕, sex=女, age=26]
==> Preparing: select * from STUDENT where class_id = ?
==> Parameters: 1(Integer)
<== Total: 2
WebClass [id=1, code=C001, name=大乱斗指导班]
Student [id=1, name=王一倩, sex=女, age=22]
Student [id=3, name=王二赞, sex=男, age=28]
7.2 一对多映射
7.2.1 mybatis配置
- mybatis-config.xml添加如下配置
<settings>
<!-- 延迟加载的全局开关 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- true 使带有延迟加载的属性对象立即加载 ,false-按需加载-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
7.2.2 java bean
- 同 6.1.1
7.2.3 mapper映射文件
- WebClassMapper.xml
<mapper namespace="com.zhougl.web.dao.mapper.WebClassMapper">
<resultMap id="WebClassMap"
type="com.zhougl.web.beans.WebClass">
<id column="ID" jdbcType="DECIMAL" property="id" />
<result column="CODE" jdbcType="VARCHAR" property="code" />
<result column="NAME" jdbcType="VARCHAR" property="name" />
<!-- 一对多 -->
<collection property="students" column="id"
javaType="ArrayList" ofType="com.zhougl.web.beans.Student"
select="com.zhougl.web.dao.mapper.StudentMapper.selectStudentByClassId"
fetchType="lazy" >
<id column="ID" jdbcType="DECIMAL" property="id" />
<result column="NAME" jdbcType="VARCHAR" property="name" />
<result column="SEX" jdbcType="VARCHAR" property="sex" />
<result column="AGE" jdbcType="DECIMAL" property="age" />
</collection>
</resultMap>
<sql id="Base_Column_List">
ID, CODE, NAME
</sql>
<select id="selectWebClassById"
parameterType="int" resultMap="WebClassMap">
select
<include refid="Base_Column_List" />
from WEB_CLASS
where ID = #{id,jdbcType=DECIMAL}
</select>
</mapper>
7.2.4 mapper接口
- WebClassMapper.xml
public interface WebClassMapper {
WebClass selectWebClassById(int i);
}
7.2.5 测试类
public class OneToManyTest {
public static void main(String[] args) {
SqlSession session = SqlSessionFactoryUtil.getSession();
OneToManyTest test = new OneToManyTest();
//测试一对多
test.testOneToMany(session);
//测试多对一
//test.testManyToOne(session);
session.commit();
session.close();
}
//测试一对多,查询班级(一)级联查询学生(多)
public void testOneToMany(SqlSession session) {
WebClassMapper mapper = session.getMapper(WebClassMapper.class);
WebClass webClass = mapper.selectWebClassById(1);
System.out.println(webClass.getId()+" "+webClass.getCode()+" "+webClass.getName());
System.out.println(webClass.toString());
List<Student> students = webClass.getStudents();
for (Student student : students) {
System.out.println(student.toString());
}
}
}
==> Preparing: select ID, CODE, NAME from WEB_CLASS where ID = ?
==> Parameters: 1(Integer)
<== Total: 1
1 C001 大乱斗指导班
==> Preparing: select ID as studentId, NAME as studentName, SEX, AGE, CLASS_ID from STUDENT where CLASS_ID = ?
==> Parameters: 1(Integer)
<== Total: 4
WebClass [id=1, code=C001, name=大乱斗指导班]
Student [id=1, name=王一倩, sex=女, age=22]
Student [id=2, name=王怡, sex=女, age=24]
Student [id=3, name=王二赞, sex=男, age=28]
Student [id=4, name=王多燕, sex=女, age=26]
7.3 多对一映射
7.3.1 java bean
- 同 6.1.1
7.3.2 mapper配置
- StudentMapper.xml
<mapper namespace="com.zhougl.web.dao.mapper.StudentMapper">
<resultMap id="StudentMap" type="com.zhougl.web.beans.Student">
<id column="studentId" jdbcType="DECIMAL" property="id" />
<result column="studentName" jdbcType="VARCHAR" property="name" />
<result column="SEX" jdbcType="VARCHAR" property="sex" />
<result column="AGE" jdbcType="DECIMAL" property="age" />
<!-- 多对一 -->
<association property="webClass" javaType="com.zhougl.web.beans.WebClass">
<id column="ID" jdbcType="DECIMAL" property="id" />
<result column="CODE" jdbcType="VARCHAR" property="code" />
<result column="NAME" jdbcType="VARCHAR" property="name" />
</association>
</resultMap>
<sql id="Base_Column_List">
ID as studentId, NAME as studentName, SEX, AGE, CLASS_ID
</sql>
<sql id="student_List">
${student}.ID as studentId, ${student}.NAME as studentName, ${student}.SEX, ${student}.AGE, ${student}.CLASS_ID
</sql>
<sql id="Web_Class_Column_List">
${webClass}.ID , ${webClass}.CODE, ${webClass}.NAME
</sql>
<!-- 多表连接 -->
<!-- 查出来的列同名时需要使用别名区分 -->
<select id="selectStudentById" parameterType="int" resultMap="StudentMap">
select
<include refid="student_List" >
<property name="student" value="s"/>
</include>,
<include refid="Web_Class_Column_List" >
<property name="webClass" value="c"/>
</include>
from STUDENT s,WEB_CLASS c
where s.class_id=c.id and s.ID = #{id,jdbcType=DECIMAL}
</select>
<select id="selectStudentByClassId" parameterType="int" resultMap="StudentMap">
select
<include refid="Base_Column_List" />
from STUDENT
where CLASS_ID = #{classId,jdbcType=DECIMAL}
</select>
</mapper>
7.3.3 mapper接口
- StudentMapper.java
public interface StudentMapper {
Student selectStudentById(int id);
List<Student> selectStudentByClassId(int classId);
}
7.3.4 测试类
public class OneToManyTest {
//测试多对一,查询学生(多)级联查询班级(一)
public void testManyToOne(SqlSession session) {
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.selectStudentById(1);
System.out.println(student);
System.out.println(student.getWebClass().toString());
}
- 结果
==> Preparing: select s.ID as studentId, s.NAME as studentName, s.SEX, s.AGE, s.CLASS_ID , c.ID , c.CODE, c.NAME from STUDENT s,WEB_CLASS c where s.class_id=c.id and s.ID = ?
==> Parameters: 1(Integer)
<== Total: 1
Student [id=1, name=王一倩, sex=女, age=22]
WebClass [id=1, code=C001, name=大乱斗指导班]
7.4 多对多映射
7.4.1 java bean
public class WebOrder {
private BigDecimal id;
private String code;
private BigDecimal total;
private BigDecimal userId;
//订单和用户是多对一关系
private WebUser user;
//订单和商品是多对多关系
private List<WebArticle> articles;
}
public class WebUser {
private BigDecimal id;
private String username;
private String loginname;
private String password;
private String phone;
private String address;
//用户和订单是一对多关系
private List<WebOrder> orders;
}
public class WebArticle {
private BigDecimal id;
private String name;
private BigDecimal price;
private String remark;
}
7.4.2 mapper配置
- WebOrderMapper.xml
<mapper namespace="com.zhougl.web.dao.mapper.WebOrderMapper">
<resultMap id="BaseResultMap" type="com.zhougl.web.beans.WebOrder">
<id column="oId" jdbcType="DECIMAL" property="id" />
<result column="CODE" jdbcType="VARCHAR" property="code" />
<result column="TOTAL" jdbcType="DECIMAL" property="total" />
<!-- 多对一关联 -->
<association property="user" javaType="com.zhougl.web.beans.WebUser">
<id column="ID" jdbcType="DECIMAL" property="id" />
<result column="USERNAME" jdbcType="VARCHAR" property="username" />
<result column="LOGINNAME" jdbcType="VARCHAR" property="loginname" />
<result column="PASSWORD" jdbcType="VARCHAR" property="password" />
<result column="PHONE" jdbcType="VARCHAR" property="phone" />
<result column="ADDRESS" jdbcType="VARCHAR" property="address" />
</association>
<!-- 多对多关联 -->
<collection property="articles" javaType="ArrayList"
column="oId" ofType="com.zhougl.web.beans.WebArticle"
select="com.zhougl.web.dao.mapper.WebArticleMapper.selectArticleByOrderId"
fetchType="lazy">
<id column="ID" jdbcType="DECIMAL" property="id" />
<result column="NAME" jdbcType="VARCHAR" property="name" />
<result column="PRICE" jdbcType="DECIMAL" property="price" />
<result column="REMARK" jdbcType="VARCHAR" property="remark" />
</collection>
</resultMap>
<!-- 有同名列,需要使用别名 -->
<select id="selectOrderById" parameterType="int" resultMap="BaseResultMap">
select
o.ID as oId, o.CODE, o.TOTAL, u.*
from WEB_ORDER o,WEB_USER u
where o.user_id = u.id and o.ID = #{id,jdbcType=DECIMAL}
</select>
<select id="selectOrderByUserId" parameterType="int" resultType="com.zhougl.web.beans.WebOrder">
select * from WEB_ORDER where user_id = #{userId,jdbcType=DECIMAL}
</select>
</mapper>
<mapper namespace="com.zhougl.web.dao.mapper.WebUserMapper">
<resultMap id="BaseResultMap" type="com.zhougl.web.beans.WebUser">
<id column="ID" jdbcType="DECIMAL" property="id" />
<result column="USERNAME" jdbcType="VARCHAR" property="username" />
<result column="LOGINNAME" jdbcType="VARCHAR" property="loginname" />
<result column="PASSWORD" jdbcType="VARCHAR" property="password" />
<result column="PHONE" jdbcType="VARCHAR" property="phone" />
<result column="ADDRESS" jdbcType="VARCHAR" property="address" />
<!-- 一对多关联 -->
<collection property="orders" javaType="ArrayList"
ofType="com.zhougl.web.beans.WebOrder"
column="id" select="com.zhougl.web.dao.mapper.WebOrderMapper.selectOrderByUserId"
fetchType="lazy">
<id column="ID" jdbcType="DECIMAL" property="id" />
<result column="CODE" jdbcType="VARCHAR" property="code" />
<result column="TOTAL" jdbcType="DECIMAL" property="total" />
</collection>
</resultMap>
<sql id="Base_Column_List">
ID, USERNAME, LOGINNAME, PASSWORD, PHONE, ADDRESS
</sql>
<select id="selectUserById" parameterType="int" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from WEB_USER
where ID = #{id,jdbcType=DECIMAL}
</select>
</mapper>
<mapper namespace="com.zhougl.web.dao.mapper.WebArticleMapper">
<resultMap id="BaseResultMap" type="com.zhougl.web.beans.WebArticle">
<id column="ID" jdbcType="DECIMAL" property="id" />
<result column="NAME" jdbcType="VARCHAR" property="name" />
<result column="PRICE" jdbcType="DECIMAL" property="price" />
<result column="REMARK" jdbcType="VARCHAR" property="remark" />
</resultMap>
<sql id="Base_Column_List">
ID, NAME, PRICE, REMARK
</sql>
<select id="selectArticleByOrderId" parameterType="int" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from WEB_ARTICLE where id in(
select article_id from WEB_ITEM where order_id =#{orderId,jdbcType=DECIMAL}
)
</select>
</mapper>
7.4.3 mapper接口
public interface WebOrderMapper {
List<WebOrder> selectOrderByUserId(int userId);
WebOrder selectOrderById(int id);
}
public interface WebUserMapper {
WebUser selectUserById(int id);
}
7.4.4 测试类
public class ManyToManyTest {
public static void main(String[] args) {
SqlSession session = SqlSessionFactoryUtil.getSession();
ManyToManyTest test = new ManyToManyTest();
//test.testOneToMany(session);
test.testManyToMany(session);
session.commit();
session.close();
}
public void testOneToMany(SqlSession session) {
WebUserMapper userMapper = session.getMapper(WebUserMapper.class);
WebUser user = userMapper.selectUserById(1);
System.out.println(user.getUsername()+" "+user.getLoginname()+" "+user.getPhone()+" "+user.getAddress());
List<WebOrder> orders = user.getOrders();
for (WebOrder webOrder : orders) {
System.out.println(webOrder.toString());
}
}
public void testManyToMany(SqlSession session) {
WebOrderMapper orderMapper = session.getMapper(WebOrderMapper.class);
WebOrder order = orderMapper.selectOrderById(1);
System.out.println(order.getCode()+" "+order.getTotal());
WebUser user = order.getUser();
System.out.println(user.toString());
List<WebArticle> articles = order.getArticles();
for (WebArticle webArticle : articles) {
System.out.println(webArticle.toString());
}
}
}
8.动态SQL
mybatis才有ognl表达式来完成动态SQL。
常用元素;
if
-
choose
when
otherwise
where
set
foreach
bind
8.1 if
- 可选条件 如果不为空则加入限制条件
<select id="selectEmployeeByIdLike" resultType="com.zhougl.web.beans.WebEmployee">
select * from WEB_EMPLOYEE where state = 'active'
<!-- 可选条件 如果不为空则加入限制条件 -->
<if test="id != null">
and id= #{id}
</if>
</select>
8.2 choose
- 选择其中的一项
<select id="selectEmployeeChooce" parameterType="hashMap" resultType="com.zhougl.web.beans.WebEmployee">
select * from WEB_EMPLOYEE where state = 'active'
<!-- 条件判断 -->
<choose>
<when test="id !=null"> and id= #{id} </when>
<when test="loginname != null and password != null"> and loginname = #{loginname} and password = #{password}</when>
<otherwise> and sex = '男' </otherwise>
</choose>
</select>
8.3 where
- where元素之一在一个以上的if条件有至的情况下才会去插入where子句,若最后内容是 and 或 or 开头,where元素会将他们去除
<!-- where元素之一在一个以上的if条件有至的情况下才会去插入where子句,若最后内容是 and 或 or 开头,where元素会将他们去除 -->
<select id="selectEmployeeWhere" resultType="com.zhougl.web.beans.WebEmployee">
select * from WEB_EMPLOYEE
<where>
<if test="state != null"> state = #{state}</if>
<if test="id != null"> and id = #{id}</if>
<if test="loginname != null and password != null">
and loginname = #{loginname} and password = #{password}
</if>
</where>
</select>
8.5 foreach
- 元素用来遍历集合
- collection:指定输入对象中的集合属性
- item:每次遍历生成的对象
- open:开始遍历时的拼接字符串
- close:结束时拼接的字符串
- separator:遍历对象之间需要拼接的字符串
<select id="selectEmployeeForeach" resultType="com.zhougl.web.beans.WebEmployee">
<!-- collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
select * from WEB_EMPLOYEE where ID in (1,2,3)
-->
select * from WEB_EMPLOYEE
where ID in
<!-- foreach 指定一个集合,声明元素体内的集合项和索引变量,也袁旭指定开闭匹配的字符串以及迭代中间放置分隔符 -->
<foreach collection="list" index="index" item="item" open="(" separator="," close=")" >
#{item}
</foreach>
</select>
8.6 bind
-
预定义变量
<select id="selectEmployeeLikeName" parameterType="int" resultMap="BaseResultMap">
<bind name="pattern" value="'%'+ _parameter.getName() +'%'"/>
select * from WEB_EMPLOYEE
where name like #{pattern}
</select>
8.7 trim
替换和添加
prefix:前缀
prefixoverride:去掉第一个and或者是or
suffix:后缀
suffixoverride:去掉最后一个逗号(也可以是其他的标记,就像是上面前缀中的and一样)
<select id="selectEmployeeTrim" resultMap="BaseResultMap">
SELECT * FROM WEB_EMPLOYEE
<!-- prefix:前缀
prefixoverride:去掉第一个and或者是or
suffix:后缀
suffixoverride:去掉最后一个逗号(也可以是其他的标记,就像是上面前缀中的and一样) -->
<!--这个语句中是把AND换成where,下面的写法基本与where是等效的-->
<trim prefix="where" prefixOverrides="and">
<if test="state != null">and state = #{state}</if>
<if test="id != null"> and id = #{id}</if>
<if test="loginname != null and password != null">
and loginname = #{loginname} and password = #{password}
</if>
</trim>
</select>
8.8 set
- set 元素会动态前置set关键字,同时会消除无关的逗号
<update id="updateEmployeeSet" parameterType="com.zhougl.web.beans.WebEmployee">
update WEB_EMPLOYEE
<!-- set 元素会动态前置set关键字,同时会消除无关的逗号 -->
<set>
<if test="loginname != null">LOGINNAME = #{loginname,jdbcType=VARCHAR},</if>
<if test="password != null">PASSWORD = #{password,jdbcType=VARCHAR},</if>
<if test="name != null">NAME = #{name,jdbcType=VARCHAR},</if>
<if test="sex != null">SEX = #{sex,jdbcType=VARCHAR},</if>
<if test="age != null">AGE = #{age,jdbcType=DECIMAL},</if>
<if test="phone != null">PHONE = #{phone,jdbcType=VARCHAR},</if>
<if test="sal != null">SAL = #{sal,jdbcType=DECIMAL},</if>
<if test="state != null">STATE = #{state,jdbcType=VARCHAR}</if>
</set>
where id=#{id,jdbcType=DECIMAL}
</update>
9.事务管理及缓存机制
9.1 事务管理
9.1.1 事务特性
- 原子性。事务是应用中最小执行单位。
- 一致性。事务执行的结果,必须使数据库从一种一致状态,变到另一种一致性状态。
- 隔离性。事务执行互不干扰,不能互相影响。
- 持续性。事务一提交,对数据所做的任何改变都要保存到物理数据库中。
9.1.2 Transaction接口
-
Transaction接口
- JdbcTransaction实现类
- ManagedTransaction实现类
-
TransactionFactory接口
- JdbcTransactionFactory实现类
- ManagedTransactionFactory实现类
使用JDBC事务管理机制。利用
java.sql.Connection
完成对事务的提交(commit())、回滚(rollback())、和关闭(close())等。使用MANAGED事务管理机制。mybatis自身不会去实现事务管理,让容器如weblogic、JBoss等来实现对事务的管理。
9.1.3 事务的配置创建
- mybatis-config.xml
<environment id="development">
<!-- 指定事务管理类型,type="JDBC"指直接简单实用了JDBC提交和回滚设置。type="MANAGED"指让容器实现对事务的管理 -->
<transactionManager type="JDBC" />
</environment>
9.2 缓存机制
9.2.1 一级缓存(SqlSession级别)
一级缓存的作用域是SqlSession范围的。同一个SqlSession中执行两次相同的SQL语句,第一次执行会将查询的数据写到缓存(内存),第二次查询时会从缓存中获取数据。如果SqlSession执行DML操作(insert、update、delete),并提交到数据库,mybatis会清空SqlSession中的一级缓存,保证缓存中是最新信息,避免出现脏读现象。
<u>mybatis默认开启一级缓存。</u>
9.2.2 二级缓存(Mapper级别)
二级缓存是mapper级别的,多个SqlSession可以使用同一个mapper的SQL语句去操作数据库,得到的数据会存在二级缓存。
9.2.2.1 开启二级缓存配置
- mybatis-config.xml
<!-- 开启二级缓存 -->
<setting name="cacheEnabled" value="true"/>
9.2.2.2 mapper中开启二级缓存
- mapper.xml
<!-- 开启当前mapper的namespace下的二级缓存 -->
<cache eviction="LRU" flushInterval="60000" size="512" readOnly="true"/>
-
cashe元素用来开启当前mapper的namespace下的二级缓存,属性设置:
- flushInterval。刷新间隔。默认不设置,缓存仅仅调用语句时刷新。
- size。缓存数目。默认值1024。
- readOnly。只读。默认为false。只读的缓存会给所有调用者返回缓存对象的相同实例,这些对象不能被修改。
-
eviction。收回策略。默认为LRU。
- LRU。最近最少使用的策略。移出最长时间不被使用的对象。
- FIFO。先进先出策略。按对象进入缓存的顺序来移除。
- SOFT。软引用策略。移除基于垃圾回收器状态和软引用规则的对象。
- WEAK。弱引用策略。更积极地移除基于垃圾收集器和弱引用规则的对象。
- 使用二级缓存时,查询结果映射的Java对象必须实现
java.io.Serializable
接口。
三、注解配置
- select。映射查询SQL语句
- selectProvider。select语句的动态SQL映射。
- insert。映射插入的SQL语句
- insertProvider。insert语句的动态SQL映射。
- update。映射更新的SQL语句。
- updateProvider。
- delete。映射删除的SQL语句。
- deleteProvider。
-
result。在列和属性直接的单独结果映射。属性包括:id、column、property、javaType、jdbcTpye、
type Handler
、one、many。id是一个布尔值,表示十分被用于主键映射。one属性是单独的联系,与<association>相似。many属性是对集合而言的,与<collection>相似。 - results。多结果映射(result)列表。
-
options。提供配置选项的附加值。
@Options(userGenerateKeys=true,keProperty="id")
- one。复杂类型的单独属性映射。必须制定select属性。表示已映射SQL语句的完全限定名。
- many。复杂类型的集合属性映射。必须制定select属性。表示已映射SQL语句的完全限定名。
- param。用于映射器方法来给每个参数取一个名字。否则,多参数将会以它们的顺序位置和SQL语句中的表达式进行映射。
-
SelectKey。用于生产主键。
- 属性
statement="select max(empNo)+1 as myNo from emp"
:表示定义的子查询语句-
before=true
:表示在之前执行,booler类型的,所以为true -
keyColumn="myNo"
:表示查询所返回的类名 -
resultType=int.class
:表示返回值得类型 -
keyProperty="empNo"
:表示将该查询的属性设置到某个列中,此处设置到empNo中
-
- 属性
1. CRUD基本注解
1.1 接口定义
- WebUserDao.java
public interface WebUserDao {
/*
* statement="select max(empNo)+1 as myNo from emp":表示定义的子查询语句
* before=true:表示在之前执行,booler类型的,所以为true
* keyColumn="myNo":表示查询所返回的类名
* resultType=int.class:表示返回值得类型
* keyProperty="empNo" :表示将该查询的属性设置到某个列中,此处设置到empNo中
*/
@Insert("insert into web_user (id, username,loginname,password, phone, address) values (#{id},#{username},#{loginname},#{password},#{phone},#{address})")
@SelectKey(statement="select SQ_PHONE_DEPARTMENT_ID.nextval as id from dual",before=true,keyColumn="id",resultType=int.class,keyProperty="id" )
int saveUser(WebUser user);
@Select("select * from web_user where id = #{id}")
@Results({
@Result(column="id",property="id"),
@Result(column="username",property="username"),
@Result(column="loginname",property="loginname"),
@Result(column="password",property="password"),
@Result(column="phone",property="phone"),
@Result(column="address",property="address"),
})
WebUser selectUserById(int id);
@Update("update web_user set phone=#{phone}, password=#{password},address=#{address} where id=#{id}")
int modifyUser(WebUser user);
@Delete("delete from web_user where id=${id}")
int deleteUser(@Param("id") int id);
}
1.2 测试
- CrudAnnotationTest.java
public class CrudAnnotationTest {
public static void main(String[] args) {
SqlSession session = SqlSessionFactoryUtil.getSession();
WebUserDao userDao = session.getMapper(WebUserDao.class);
CrudAnnotationTest test = new CrudAnnotationTest();
//test.testInsert(userDao);
//test.testUpdate(userDao);
//test.testDelete(userDao);
test.testSelect(userDao);
session.commit();
session.close();
}
public void testInsert(WebUserDao userDao) {
WebUser user = new WebUser();
user.setUsername("杨涵");
user.setLoginname("Yanhan");
user.setPassword("123456");
user.setAddress("石各庄");
userDao.saveUser(user);
System.out.println(user.getId());
}
public void testSelect(WebUserDao userDao) {
WebUser user = userDao.selectUserById(8);
System.out.println(user.toString());
}
public void testUpdate(WebUserDao userDao) {
WebUser user = new WebUser();
user.setPhone("16677778888");
user.setPassword("666666");
user.setAddress("石家庄");
user.setId(7);
int num = userDao.modifyUser(user);
System.out.println(num);
}
public void testDelete(WebUserDao userDao) {
int num = userDao.deleteUser(7);
System.out.println(num);
}
}
1.3 插入空值异常处理
解决插入空值产生的异常 Error setting null for parameter #5 with JdbcType OTHER .
说明:postgreSQL,MySQL,SQLSERVER都支持JdbcType.NULL类型,Oracle是不支持,适配的时候也因为这个问题导致mybatis报错。
解决方法:
-
MyBatis-config.xml
中设置当JDBC类型为空值时,要指定的值得,默认为OTHER,我们指定为NULL就好了(注意是大写的NULL)。
<!-- 设置但JDBC类型为空时,某些驱动程序 要指定值,default:OTHER -->
<setting name="jdbcTypeForNull" value="NULL"/>
- 在每个数据变量那里设置相应的jdbcType
<select id="selectUserById" parameterType="int" resultMap="BaseResultMap">
select * from WEB_USER
where ID = #{id,jdbcType=DECIMAL}
</select>
2. 一对一、一对多、多对多映射
2.1 一对一
2.1.1 java bean
- 同(二) 6.2.1
2.1.2 接口
- PersonDao.java
public interface PersonDao {
@Select("select * from person where id = #{id}")
@Results({
@Result(column="ID" ,property="id"),
@Result(column="NAME",property="name"),
@Result(column="SEX" ,property="sex"),
@Result(column="AGE",property="age"),
@Result(column="card_id",property="card",//表示person的card属性对于表的card_id列
one=@One(
select="com.zhougl.web.annotation.dao.CardDao.selectCardById",
fetchType=FetchType.EAGER))
})
Person selectPersonById(Integer id);
}
- CardDao.java
public interface CardDao {
@Select("select * from Card where id = #{id}")
Card selectCardById(Integer id);
}
2.1.3 测试
public void testOneToOne(SqlSession session) {
PersonDao dao = session.getMapper(PersonDao.class);
Person person =dao.selectPersonById(1);
System.out.println(person.toString());
System.out.println(person.getCard().toString());
}
2.2 一对多
2.2.1 java bean
- 同(二) 6.1.1
2.2.2 接口
- WebClassDao.java
public interface WebClassDao {
@Select("select * from WEB_CLASS where ID = #{id}")
@Results({
@Result(column="ID",property="id" ),
@Result(column="CODE",property="code"),
@Result(column="NAME",property="name"),
@Result(column="id",property="students",
many=@Many(
select="com.zhougl.web.annotation.dao.StudentDao.selectStudentByClassId",
fetchType=FetchType.LAZY))
})
WebClass selectWebClassById(int id);
}
- StudentDao.java
public interface StudentDao {
//Student selectStudentById(int id);
@Select("select * from STUDENT where CLASS_ID = #{classId}")
@Results({
@Result(column="Id",property="id"),
@Result(column="name",property="name"),
@Result(column="SEX",property="sex"),
@Result(column="AGE",property="age")
})
List<Student> selectStudentByClassId(int classId);
}
2.2.3 测试
public void testOneToMany(SqlSession session) {
WebClassDao dao = session.getMapper(WebClassDao.class);
WebClass webClass = dao.selectWebClassById(1);
System.out.println(webClass.toString());
webClass.getStudents().forEach(student ->System.out.println(student.toString()));
}
2.3 多对多
2.3.1 java bean
- 同(二)7.4.1
2.3.2 接口
- WebUserDao.java
public interface WebUserDao {
WebUser selectUserById(int id);
@Update("update web_user set phone=#{phone}, password=#{password},address=#{address} where id=#{id}")
int modifyUser(WebUser user);
}
- WebArticleDao.java
public interface WebArticleDao {
@Select("select * from WEB_ARTICLE where id in( select article_id from WEB_ITEM where order_id =#{orderId} )")
List<WebArticle> selectArticleByOrderId(int orderId);
}
- WebOrderDao.java
public interface WebOrderDao {
@Select("select * from WEB_ORDER where id = #{id}")
@Results({
@Result(column="id",property="id"),
@Result(column="CODE",property="code"),
@Result(column="TOTAL",property="total"),
@Result(column="user_id",property="user",
one=@One(select="com.zhougl.web.annotation.dao.WebUserDao.selectUserById",fetchType=FetchType.EAGER)),
@Result(column="id",property="articles" ,
many=@Many(select="com.zhougl.web.annotation.dao.WebArticleDao.selectArticleByOrderId",fetchType=FetchType.LAZY)),
})
WebOrder selectOrderById(int id);
}
2.3.3 测试
public void testManyToMany(SqlSession session) {
WebOrderDao dao = session.getMapper(WebOrderDao.class);
WebOrder order = dao.selectOrderById(1);
System.out.println(order.toString());
System.out.println(order.getUser().toString());
order.getArticles().forEach(article ->System.out.println(article.toString()));
}
//结果
WebOrder [id=1, code=201811300001, total=3666.65, userId=null]
WebUser [id=1, username=梁二燕, loginname=yangzi, password=123456, phone=15555556666, address=武汉]
WebArticle [id=1, name=花鸟画, price=1999.99, remark=梁老师著作]
WebArticle [id=2, name=肖像画, price=1666.66, remark=梁老师著作]
3. Provider注解动态SQL
3.1 @InsertProvider
3.1.1 构建动态SQL类
public class EmployeeDynaSqlProvider {
public String insertEmployee(WebEmployee employee){
return new SQL() {
{
INSERT_INTO("web_employee");
if(employee.getId()!=null) {
VALUES("id", "#{id}");
}
if(employee.getLoginname()!=null) {
VALUES("loginname", "#{loginname}");
}
if(employee.getLoginname()!=null) {
VALUES("password", "#{password}");
}
if(employee.getLoginname()!=null) {
VALUES("name", "#{name}");
}
if(employee.getLoginname()!=null) {
VALUES("sex", "#{sex}");
}
if(employee.getLoginname()!=null) {
VALUES("age", "#{age}");
}
if(employee.getLoginname()!=null) {
VALUES("phone", "#{phone}");
}
if(employee.getLoginname()!=null) {
VALUES("sal", "#{sal}");
}
if(employee.getLoginname()!=null) {
VALUES("state", "#{state}");
}
}
}.toString();
}
}
3.1.2 接口
public interface WebEmployeeDao {
@InsertProvider(type=EmployeeDynaSqlProvider.class,method="insertEmployee")
@SelectKey(statement="select max(id)+1 as id from web_employee",before=true,keyColumn="id",resultType=int.class,keyProperty="id" )
int insertEmployee(WebEmployee employee);
}
3.1.3 测试类
public class DynamicSQLTest {
public static void main(String[] args) {
SqlSession session = SqlSessionFactoryUtil.getSession();
WebEmployeeDao dao = session.getMapper(WebEmployeeDao.class);
DynamicSQLTest test = new DynamicSQLTest();
test.testInsertEmployee(dao);
//test.testUpdateEmployee(dao);
//test.testDeleteEmployee(dao);
//test.testSelectWhithMap(dao);
session.commit();
session.close();
}
public void testInsertEmployee(WebEmployeeDao dao) {
WebEmployee employee = new WebEmployee();
employee.setName("菠萝赛东");
employee.setLoginname("boluosaidong");
employee.setPassword("123456");
employee.setState("active");
int num = dao.insertEmployee(employee);
System.out.println(num);
}
- 结果
==> Preparing: select max(id)+1 as id from web_employee
==> Parameters:
<== Total: 1
==> Preparing: INSERT INTO web_employee (id, loginname, password, name, sex, age, phone, sal, state) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
==> Parameters: 6(Integer), boluosaidong(String), 123456(String), 菠萝赛东(String), null, null, null, null, active(String)
<== Updates: 1
WebEmployee [id=6, loginname=boluosaidong, password=123456, name=菠萝赛东, sex=null, age=null, phone=null, sal=null, state=active]
3.1 @SelectProvider
3.1.1 构建动态SQL类
public class EmployeeDynaSqlProvider {
public String selectWhithMap(Map<String, Object> map) {
return new SQL(){
{
SELECT("*");
FROM("web_employee");
//多个参数时
//SELECT * FROM web_employee WHERE ( id = ? AND loginname = ? and password = ? AND state = ? )
if(map.get("id")!=null) {
WHERE(" id = #{id} ");
}
if(map.get("loginname")!=null && map.get("password")!=null) {
WHERE(" loginname = #{loginname} and password = #{password} ");
}
if(map.get("name")!=null) {
WHERE(" name = #{name} ");
}
if(map.get("phone")!=null) {
WHERE(" phone = #{phone} ");
}
if(map.get("state")!=null) {
WHERE(" state = #{state} ");
}
}
}.toString();
}
}
3.1.2 接口
public interface WebEmployeeDao {
@SelectProvider(type=EmployeeDynaSqlProvider.class,method="selectWhithMap")
List<WebEmployee> selectEmployeeWithMap(HashMap<String, Object> map);
}
3.1.3 测试类
public class DynamicSQLTest {
public void testSelectWhithMap(WebEmployeeDao dao) {
HashMap<String, Object> map = new HashMap<String, Object>();
//map.put("id", 1);
//map.put("loginname", "rose");
//map.put("password", "123456");
map.put("state", "active");
//map.put("state", "inactive");
List<WebEmployee> employees = dao.selectEmployeeWithMap(map);
employees.forEach(WebEmployee -> System.out.println(WebEmployee.toString()));
}
}
- 结果
==> Preparing: SELECT * FROM web_employee WHERE ( state = ? )
==> Parameters: active(String)
<== Total: 5
WebEmployee [id=1, loginname=jack, password=123456, name=苏文, sex=男, age=26, phone=15566665555, sal=9800, state=active]
WebEmployee [id=2, loginname=rose, password=123456, name=苏问问, sex=女, age=24, phone=16655556666, sal=6800, state=active]
WebEmployee [id=3, loginname=tom, password=123456, name=陈大山, sex=男, age=29, phone=17766665555, sal=8800, state=active]
WebEmployee [id=4, loginname=alice, password=123456, name=苏苏豆豆, sex=女, age=25, phone=19955556666, sal=5800, state=active]
WebEmployee [id=6, loginname=boluosaidong, password=123456, name=菠萝赛东, sex=null, age=null, phone=null, sal=null, state=active]
3.1 @UpdateProvider
3.1.1 构建动态SQL类
public class EmployeeDynaSqlProvider {
public String updateEmployee(WebEmployee employee) {
return new SQL() {
{
UPDATE("web_employee");
if(employee.getLoginname()!=null) {
SET("loginname = #{loginname}");
}
if(employee.getLoginname()!=null) {
SET("password = #{password}");
}
if(employee.getLoginname()!=null) {
SET("name = #{name}");
}
if(employee.getLoginname()!=null) {
SET("sex = #{sex}");
}
if(employee.getLoginname()!=null) {
SET("age = #{age}");
}
if(employee.getLoginname()!=null) {
SET("phone = #{phone}");
}
if(employee.getLoginname()!=null) {
SET("sal = #{sal}");
}
if(employee.getLoginname()!=null) {
SET("state = #{state}");
}
WHERE(" id = #{id} ");
}
}.toString();
}
}
3.1.2 接口
public interface WebEmployeeDao {
@UpdateProvider(type=EmployeeDynaSqlProvider.class,method="updateEmployee")
int updateEmployee(WebEmployee employee);
}
3.1.3 测试类
public class DynamicSQLTest {
public void testUpdateEmployee(WebEmployeeDao dao) {
WebEmployee employee = new WebEmployee();
employee.setId(6);
employee.setName("菠萝赛西");
employee.setLoginname("boluosaiXi");
employee.setPassword("789012");
employee.setState("active");
int num = dao.updateEmployee(employee);
System.out.println(num);
}
}
- 结果
==> Preparing: UPDATE web_employee SET loginname = ?, password = ?, name = ?, sex = ?, age = ?, phone = ?, sal = ?, state = ? WHERE ( id = ? )
==> Parameters: boluosaiXi(String), 789012(String), 菠萝赛西(String), null, null, null, null, active(String), 6(Integer)
<== Updates: 1
WebEmployee [id=6, loginname=boluosaiXi, password=789012, name=菠萝赛西, sex=null, age=null, phone=null, sal=null, state=active]
3.1 @DeleteProvider
3.1.1 构建动态SQL类
public class EmployeeDynaSqlProvider {
public String deleteEmployee(WebEmployee employee) {
return new SQL() {
{
DELETE_FROM("web_employee");
if(employee.getId()!=null) {
WHERE(" id = #{id} ");
}
if(employee.getLoginname()!=null) {
WHERE("loginname", "#{loginname}");
}
if(employee.getLoginname()!=null) {
WHERE("password", "#{password}");
}
if(employee.getLoginname()!=null) {
WHERE("name", "#{name}");
}
if(employee.getLoginname()!=null) {
WHERE("sex", "#{sex}");
}
if(employee.getLoginname()!=null) {
WHERE("age", "#{age}");
}
if(employee.getLoginname()!=null) {
WHERE("phone", "#{phone}");
}
if(employee.getLoginname()!=null) {
WHERE("sal", "#{sal}");
}
if(employee.getLoginname()!=null) {
WHERE("state", "#{state}");
}
}
}.toString();
}
}
3.1.2 接口
public interface WebEmployeeDao {
@DeleteProvider(type=EmployeeDynaSqlProvider.class,method="deleteEmployee")
int deleteEmployee(WebEmployee employee);
}
3.1.3 测试类
public class DynamicSQLTest {
public void testDeleteEmployee(WebEmployeeDao dao) {
WebEmployee employee = new WebEmployee();
employee.setId(6);
int num = dao.deleteEmployee(employee);
System.out.println(num);
}
}
- 结果
==> Preparing: DELETE FROM web_employee WHERE ( id = ? )
==> Parameters: 6(Integer)
<== Updates: 1