Mybatis框架入门
Mybatis 框架
一、什么是 Mybatis
二、Mybatis 与 Hibernate 区别
两个都是持久层框架，操作数据库，但是两者还是有区别的
hibernate: 它是一个标准的 orm 框架, 比较重量级, 学习成本高.
优点: 高度封装, 使用起来不用写 sql, 开发的时候, 会减低开发周期.
缺点:sql 语句无法优化
应用场景:oa(办公自动化系统), erp(企业的流程系统) 等, 还有一些政府项目,
总的来说, 在用于量不大, 并发量小的时候使用.
mybatis: 它不是一个 orm 框架, 它是对 jdbc 的轻量级封装, 学习成本低, 比较简单
优点: 学习成本低, sql 语句可以优化, 执行效率高, 速度快
缺点: 编码量较大, 会拖慢开发周期
应用场景: 互联网项目, 比如电商,P2p 等
总的来说是用户量较大, 并发高的项目。
三、体验原始的 jdbc 开发
1、导入 jar 包
此时操作数据库，需要引入数据库驱动，这里我使用的是 mysql 驱动
2、编写 jdbc 程序
public static void main(String[] args) { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null;</span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> { </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">加载数据库驱动</span> Class.forName("com.mysql.jdbc.Driver"<span style="color: rgba(0, 0, 0, 1)">); </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">通过驱动管理类获取数据库链接</span> connection = DriverManager.getConnection("jdbc:mysql://192.168.174.130:3306/SSM", "root", "root"<span style="color: rgba(0, 0, 0, 1)">); </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">定义sql语句 ?表示占位符</span> String sql = "select * from myUser where username = ?"<span style="color: rgba(0, 0, 0, 1)">; </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">获取预处理statement</span> preparedStatement =<span style="color: rgba(0, 0, 0, 1)"> connection.prepareStatement(sql); </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值</span> preparedStatement.setString(1, "王五"<span style="color: rgba(0, 0, 0, 1)">); </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">向数据库发出sql执行查询，查询出结果集</span> resultSet =<span style="color: rgba(0, 0, 0, 1)"> preparedStatement.executeQuery(); </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">遍历查询结果集</span> <span style="color: rgba(0, 0, 255, 1)">while</span><span style="color: rgba(0, 0, 0, 1)">(resultSet.next()){ System.out.println(resultSet.getString(</span>"id")+" "+resultSet.getString("username"<span style="color: rgba(0, 0, 0, 1)">)); } } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (Exception e) { e.printStackTrace(); }</span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)">{ </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">释放资源</span> <span style="color: rgba(0, 0, 255, 1)">if</span>(resultSet!=<span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">){ </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> { resultSet.close(); } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (SQLException e) { </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> TODO Auto-generated catch block</span>e.printStackTrace();
}
}
if(preparedStatement!=null){
try {
preparedStatement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}} }</span></pre>
缺点：频繁创建释放资源降低性能; 代码耦合性强，不易维护; 传参与所获结果集编码不够灵活 (存在硬编码)
综上：mybatis 解决了以上问题
1、在SqlMapConfig.xml中配置数据链接池，使用连接池管理数据库链接, 大大减少了不断创建释放资源。
2、将Sql语句配置在XXXXmapper.xml文件中与java代码分离。
3、Mybatis自动将java对象映射至sql语句，通过statement中的parameterType定义输入参数的类型。解决了条件查询中笨重问题
四、快速部署环境
1、下载 jar 包
mybatis 官网： http://www.mybatis.org/mybatis-3/
jar 包下载地址： https://github.com/mybatis/mybatis-3/releases
2、新建工程，导入相关 jar 包
3、在工程 (不是 src) 下建立一个源码包, 用于存放配置文件
4、在源码中配置一个日志文件，用于打印日志
# Global logging configuration log4j.rootLogger=DEBUG, stdout # Console output... log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
5、配置核心配置文件
配置文件中主要是配置连接数据库和事务管理的内容，文件名可以自定义，默认 SqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "https://www.mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 和 spring 整合后 environments 配置将废除 --> <environments default="development"> <environment id="development"> <!-- 使用 jdbc 事务管理 --> <transactionManager type="JDBC" /> <!-- 数据库连接池 --> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://192.168.174.130:3306/SSM" /> <!-- 注意：这里必须是 username, 一定要规范 --> <property name="username" value="root" /> <property name="password" value="root" /> </dataSource> </environment> </environments> </configuration>
6、配置 sql 映射文件, 文件名自定义，这里默认为 User.xml,
与 hibernate 想区别的是：hibernate 是通过操作映射文件对象来操作数据库，与 sql 无太大关系;mybatis 的映射文件是用来写 sql 语句的
注意：此文件放在源码包下
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3.0-mapper.dtd"> <!-- namespace 命名空间，做 sql 隔离，防止命名重复 --> <mapper namespace="test"></mapper>
7、在核心配置文件中引入映射文件
<mappers>
<mapper resource="User.xml"/> </mappers>
此时环境差不多就部署好了
五、需求开发
1、根据 id 查询一个客户
1）建立客户表
2）定义 pojo 类
package com.clj.pojo;import java.util.Date;
import java.util.List;public class User {
private int id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
private List<Orders> ordersList;</span><span style="color: rgba(0, 0, 255, 1)">public</span> List<Orders><span style="color: rgba(0, 0, 0, 1)"> getOrdersList() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> ordersList; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setOrdersList(List<Orders><span style="color: rgba(0, 0, 0, 1)"> ordersList) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.ordersList =<span style="color: rgba(0, 0, 0, 1)"> ordersList; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> getId() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> id; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setId(<span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> id) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.id =<span style="color: rgba(0, 0, 0, 1)"> id; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getUsername() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> username; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setUsername(String username) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.username =<span style="color: rgba(0, 0, 0, 1)"> username; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getSex() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> sex; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setSex(String sex) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.sex =<span style="color: rgba(0, 0, 0, 1)"> sex; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Date getBirthday() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> birthday; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setBirthday(Date birthday) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.birthday =<span style="color: rgba(0, 0, 0, 1)"> birthday; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getAddress() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> address; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setAddress(String address) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.address =<span style="color: rgba(0, 0, 0, 1)"> address; } @Override </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String toString() { </span><span style="color: rgba(0, 0, 255, 1)">return</span> "User [id=" + id + ", username=" + username + ", sex=" +<span style="color: rgba(0, 0, 0, 1)"> sex </span>+ ", birthday=" + birthday + ", address=" + address + "]"<span style="color: rgba(0, 0, 0, 1)">; }}
3）在 User.xml 文件中配置 sql 语句
注意：当传入参数类型时原始型时，占位符中的括号中的值可以随意设置，但最好可读性较强（占位符能自动进行java类型和jdbc类型转换，可以有效防止sql注入。）
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3.0-mapper.dtd"> <!-- namespace 命名空间，做 sql 隔离 --> <mapper namespace="test"> <!-- id:sql 语句唯一标识 parameterType: 指定传入参数类型 (对应 javaBean 类型，写原始型会自动包装为包装类) resultType：返回结果类型 #{}: 占位符号，起到占位作用，如果传入的是原始型，那么括号中的变量名称可以随意定义 --> <select id="findUserById" parameterType="int" resultType="com.clj.pojo.User"> select * from user where id=#{id} </select> </mapper>
3）测试
值得注意的是，这里调用 sql 的写法
@Test public void testFindUserById() throws Exception{ String resource="SqlMapConfig.xml"; InputStream inputStream=Resources.getResourceAsStream(resource); //创建工厂 SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputStream); //通过工厂创建会话 SqlSession openSession=factory.openSession(); //第一个参数：所调用的 sql 语句：namespace+‘.’+SqlID //第二个参数：传入的参数 User user=openSession.selectOne("test.findUserById",1); System.out.println(user); openSession.close();}
这里调用的是 selectOne 方法，旨在查询一条指定的数据，如果用它查询多条数据，会报异常 (查询多条要用 selectList)
2、根据用户名查询客户
1）在 User.xml 文件中配置 sql 语句
注意：当传入的参数是非引用型时，拼接符要用 "value"( 拼接符不进行jdbc类型转换， ${}可以接收简单类型值或pojo属性值，如果parameterType传输单个简单类型值，${}括号中只能是value。)
这里返回值为 List 集合，配置为该集合的泛型
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3.0-mapper.dtd"> <!-- namespace 命名空间，做 sql 隔离 --> <mapper namespace="test"> <!-- 如果返回结果为集合，可以调用 selectList 方法。这个方法返回结果就有个集合 ， 所以映射文件应该配置成集合的泛型 ${} 拼接符：字符串原样拼接，如果传入的参数是基本类型，括号中的值必须为“value” 注意：拼接符有 sql 注入的风险，所以慎用 (= 号用占位符，like 用拼接符)--> <select id="findUserByUserName" parameterType="java.lang.String" resultType="com.clj.pojo.User"> <!--select * from user where username like #{name} --> select * from user where username like '%${value}%' </select> </mapper>
2）测试
@Test public void testFindUSerByUserName() throws Exception{ String resource="SqlMapConfig.xml"; InputStream inputstream=Resources.getResourceAsStream(resource); SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputstream); SqlSession openSession=factory.openSession(); //List<User> list=openSession.selectList("test.findUserByUserName","% 王 %"); List<User> list=openSession.selectList("test.findUserByUserName","王"); System.out.println(list); openSession.close();}
3、插入数据
1）配置 sql
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3.0-mapper.dtd"> <!-- namespace 命名空间，做 sql 隔离 --> <mapper namespace="test"> <!-- #{}: 如果传入的是 pojo 类型，那么 #{} 中变量的名称必须是 pojo 中对应的属性 如果要返回数据库自增主键，可以使用 select LAST_INSERT_ID() --> <insert id="insertUser" parameterType="com.clj.pojo.User"> <!-- 执行 LAST_INSERT_ID，返回自增的主键 keyProperty：将返回的主键放入传入参数的 ID 中保存 order: 相当于 insert 语句的执行顺序，在 insert 前执行时 before，之后是 after resultType:keyProperty 中属性的类型， --> <selectKey keyProperty="id" order="AFTER" resultType="int"> select LAST_INSERT_ID() </selectKey> insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address}) </insert> </mapper>
注意：这里添加selectKey实现将主键返回，因为是先插入数据，才能获得主键，所以其属性值 order="AFTER"
如果是 uuid(随机字符串)，属性值 order="Before"
<insert id="insertUser" parameterType="cn.itcast.mybatis.po.User"> <selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id"> select uuid() </selectKey> insert into user(id,username,birthday,sex,address) values(#{id},#{username},#{birthday},#{sex},#{address}) </insert>
2) 测试
@Test public void testInsertUser() throws Exception{ String resource="SqlMapConfig.xml"; InputStream inputstream=Resources.getResourceAsStream(resource); SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputstream); SqlSession openSession=factory.openSession(); User user=new User(); user.setUsername("赵四"); user.setBirthday(new Date()); user.setSex("1"); user.setAddress("长沙市"); System.out.println("======"+user.getId()); openSession.insert("test.insertUser",user); //提交事务 (mybatis 会自动提交事务，但是不知道何时手动提交事务) openSession.commit(); System.out.println("========"+user.getId()); openSession.close();}
4、删除更新数据
1）配置 sql 语句
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3.0-mapper.dtd"> <!-- namespace 命名空间，做 sql 隔离 --> <mapper namespace="test"> <delete id="delUserById" parameterType="int"> delete from user where id=#{id} </delete> <update id="updateUserById" parameterType="com.clj.pojo.User"> update user set username=#{username} where id=#{id} </update> </mapper>
2）测试
@Test public void testDelUserById() throws Exception{ String resource="SqlMapConfig.xml"; InputStream inputstream=Resources.getResourceAsStream(resource); SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputstream); SqlSession openSession=factory.openSession(); openSession.delete("test.delUserById",29); openSession.commit(); openSession.close();} @Test public void testUpdateUserById()throws Exception{ String resource="SqlMapConfig.xml"; InputStream inputstream=Resources.getResourceAsStream(resource); SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputstream); SqlSession openSession=factory.openSession(); User user=new User(); user.setId(28); user.setUsername("佳先森"); openSession.update("test.updateUserById",user); openSession.commit(); openSession.close();}
扩展：将连接数据库中的属性封装到数据库配置文件中
1、在源码包中配置 db.properties
jdbc.driver=com.mysql.jdbc.Driver\t jdbc.url=jdbc:mysql://192.168.174.130:3306/SSM jdbc.username=root jdbc.password=root
2、修改核心配置文件
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "https://www.mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 引入链接数据库配置文件 --> <properties resource="db.properties"/> <!-- 和 spring 整合后 environments 配置将废除 --> <environments default="development"> <environment id="development"> <!-- 使用 jdbc 事务管理 --> <transactionManager type="JDBC" /> <!-- 数据库连接池 --> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}" /> <property name="url" value="${jdbc.url}" /> <!-- 注意：这里必须是 username, 一定要规范 --> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> </dataSource> </environment> </environments> <mappers><mapper resource="User.xml"/> </mappers> </configuration>
六、mybatis 支持别名
在核心配置文件中第一个类的别名，后面可以直接引用别名，无需配置全路径
<typeAliases> <!-- 定义单个 pojo 类别名 type: 类的全路径名称 alias：别名 --> <typeAlias type="com.clj.pojo.User" alias="user"/> <!-- 使用包扫描的方式批量定义别名, 定义后别名等于类名 ，不区分大小写 ，但最好按照驼峰命名法书写 --> <package name="com.clj.pojo"/> </typeAliases>
七、mybatis 引入映射文件写法
有两种写法：一种是单个引入，另一种是包扫描的方式
<mappers>
<!-- 两种引入方式
方式一：单个引入
<mapper resource="User.xml"/>--></span>
<span style="color: rgba(51, 153, 102, 1)"><!-- 使用class属性引入接口的全路径名称：
使用规则：1.接口名称和映射文件名称除扩展名之外要完全相同
2.接口和映射文件要放在同一个目录下
<mapper class="com.clj.UserMapper.UserMapper"/>--></span>
<span style="color: rgba(51, 153, 102, 1)"><!-- 方式二：使用包扫描的方式批量引入Mapper
使用包扫描规则和上个一样
--></span>
<<span style="color: rgba(0, 0, 255, 1)">package</span> name="com.clj.UserMapper"/>
</mappers></pre>
八、MyBatis 之 Dao 层的开发方式
MyBatis 之 Dao 层的开发方式有两种：原始 Dao 开发和 Mapper 接口开发
1、Dao 开发方式一：原始 Dao 开发
1）配置 user.xml 中的 sql 语句
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3.0-mapper.dtd"> <!-- namespace 命名空间，做 sql 隔离 --> <mapper namespace="test"> <select id="findUserById" parameterType="int" resultType="com.clj.pojo.User"> select * from user where id=#{id} </select> <select id="findUserByUserName" parameterType="java.lang.String" resultType="com.clj.pojo.User">
select * from user where username like '%${value}%'
</select>
</mapper>
2）定义接口和实现类
package com.clj.dao;import java.util.List;
import com.clj.pojo.User;
public interface UserDao {
public User findUserById(Integer id);
public List<User> findUserByUserName(String username);
}
package com.clj.dao;import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;import com.clj.pojo.User;
public class UserDaoImpl implements UserDao{
private SqlSessionFactory sqlSessionFactory;
//通过构造方法注入
public UserDaoImpl(SqlSessionFactory sqlSessionFactory) {
super();
this.sqlSessionFactory = sqlSessionFactory;
}
@Override
public User findUserById(Integer id) {
//SqlSession 是线程不安全的，所以他的最佳使用的范围在方法体内
SqlSession opeanSession=sqlSessionFactory.openSession();
User user=opeanSession.selectOne("test.findUserById",id);
return user;
}
@Override
public List<User> findUserByUserName(String username) {
SqlSession opeanSession=sqlSessionFactory.openSession();
List<User> list=opeanSession.selectList("test.findUserByUserName",username);
return list;
}}
3）在核心配置文件 SqlMapConfig.xml 中引入 user.xml
<mappers>
<mapper resource="User.xml"/> </mappers>
4）定义测试类进行测试
package com.clj.Test;import java.io.InputStream;
import java.util.List;import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;import com.clj.dao.UserDao;
import com.clj.dao.UserDaoImpl;
import com.clj.pojo.User;public class UserDaoTest {
private SqlSessionFactory factory;</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">@Before作用在测试方法之前执行这个方法</span>@Before
public void setUp() throws Exception{
String resource="SqlMapConfig.xml";
InputStream inputstream=Resources.getResourceAsStream(resource);
factory=new SqlSessionFactoryBuilder().build(inputstream);
SqlSession openSession=factory.openSession();
}
@Test
public void testFindUserById() throws Exception{
//将初始化的工厂注入到实现类中
UserDao userDao=new UserDaoImpl(factory);
User user=userDao.findUserById(1);
System.out.println(user);
}
@Test
public void testFindUserByUserName() throws Exception{
UserDao userDao=new UserDaoImpl(factory);
List<User> list=userDao.findUserByUserName("王");
System.out.println(list);
}
}
总结：原始 Dao 还是存在一些不好的因素：1. 代码有重复 2.sqlSession 调用 sql 时需要指定 id 值，存在硬编码
2、Dao 开发方式二：Mapper 动态代理的方式
Mapper 动态代理的方式需要严格遵守一些规范
需求一：通过主键查找用户; 通过用户名查找用户; 插入用户数据
1）定义接口
package com.clj.UserMapper;import java.util.List;
import com.clj.pojo.CustomerOrders;
import com.clj.pojo.Orders;
import com.clj.pojo.QueryVo;
import com.clj.pojo.User;//与之关乎的配置文件要在同一个目录包下
public interface UserMapper {
public User findUserById(Integer id);
//注意：这里是模糊查询，配置文件虽然写的是 User，实际返回的是 List 集合
//动态代理形式中，如果返回结果集 List，那么 mybatis 会在生成实现类的时候会自动调用 selectList 方法
public List<User> findUserByUserName(String userName);
public void insertUser(User user);
}
2）定义其映射文件，最好是在同目录下
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3.0-mapper.dtd"> <!--mapper 接口代理实现编写轨则 1. 映射文件中 namespace 要等于接口的全路径名 2. 映射文件中 sql 语句 id 要等于接口的方法名称 3. 映射文件中传入参数类型要等于接口方法的传入参数类型 4. 映射文件中返回结果集类型等于接口方法的返回值类型 --> <mapper namespace="com.clj.UserMapper.UserMapper"> <select id="findUserById" parameterType="int" resultType="com.clj.pojo.User"> select * from user where id=#{id} </select> <select id="findUserByUserName" parameterType="java.lang.String" resultType="com.clj.pojo.User"> <!--select * from user where username like #{name} --> select * from user where username like '%${value}%' </select> <!-- 这里返回值使用的别名 --> <insert id="insertUser" parameterType="user"> <selectKey keyProperty="id" order="AFTER" resultType="int"> select LAST_INSERT_ID() </selectKey> insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address}) </insert> </mapper>
两文件的目录关系
3）在核心配置文件中引入 Mapper 的配置文件
<mappers>
<!-- 两种引入方式
方式一：单个引入
<mapper resource="User.xml"/>--></span>
<span style="color: rgba(51, 153, 102, 1)"> <!-- 方式二：使用包扫描的方式批量引入Mapper
使用包扫描规则和上个一样
--></span>
<<span style="color: rgba(0, 0, 255, 1)">package</span> name="com.clj.UserMapper"/>
</mappers></pre>
·4）测试类
package com.clj.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;import com.clj.UserMapper.UserMapper;
import com.clj.pojo.CustomerOrders;
import com.clj.pojo.Orders;
import com.clj.pojo.QueryVo;
import com.clj.pojo.User;public class UserMapperTest {
private static SqlSessionFactory factory;</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">@Before作用在测试方法之前执行这个方法</span>@Before
public void setUp() throws Exception{
String resource="SqlMapConfig.xml";
InputStream inputstream=Resources.getResourceAsStream(resource);
factory=new SqlSessionFactoryBuilder().build(inputstream);
SqlSession openSession=factory.openSession();
}
@Test
public void testFindUserById() throws Exception{
SqlSession openSession=factory.openSession();
//通过 getMapper 方法实列化接口
UserMapper mapper=openSession.getMapper(UserMapper.class);
User user=mapper.findUserById(1);
System.out.println(user);
}
}
另外：删除用户和更新用户 sql 配置文件方式为，具体步骤如上
<delete id="delUserById" parameterType="int"> delete from user where id=#{id} </delete> <update id="updateUserById" parameterType="com.clj.pojo.User"> update user set username=#{username} where id=#{id} </update>
需求二：根据用户名查询，利用高级查询 Vo 类
1）创建 Vo 类封装用户属性，并提供 set/get 方法
package com.clj.pojo;import java.util.List;
//用于高级查询
public class QueryVo {
private User user;
private List<Integer> ids;</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> User getUser() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> user; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setUser(User user) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.user =<span style="color: rgba(0, 0, 0, 1)"> user; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> List<Integer><span style="color: rgba(0, 0, 0, 1)"> getIds() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> ids; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setIds(List<Integer><span style="color: rgba(0, 0, 0, 1)"> ids) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.ids =<span style="color: rgba(0, 0, 0, 1)"> ids; }}
2）在 UserMapper.xml 文件中配置查询语句
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3.0-mapper.dtd"> <!--mapper 接口代理实现编写轨则 1. 映射文件中 namespace 要等于接口的全路径名 2. 映射文件中 sql 语句 id 要等于接口的方法名称 3. 映射文件中传入参数类型要等于接口方法的传入参数类型 4. 映射文件中返回结果集类型等于接口方法的返回值类型 --> <mapper namespace="com.clj.UserMapper.UserMapper"> <select id="findUserbyVo" parameterType="com.clj.pojo.QueryVo" resultType="com.clj.pojo.User"> select * from user where username like '%${user.username}%' and sex=#{user.sex} </select> </mapper>
3）定义接口
public List<User> findUserbyVo(QueryVo vo);
4）测试类
public class UserMapperTest { private static SqlSessionFactory factory;</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">@Before作用在测试方法之前执行这个方法</span>@Before
public void setUp() throws Exception{
String resource="SqlMapConfig.xml";
InputStream inputstream=Resources.getResourceAsStream(resource);
factory=new SqlSessionFactoryBuilder().build(inputstream);
SqlSession openSession=factory.openSession();
}
@Test
public void testFindUserByVo() throws Exception{
SqlSession openSession=factory.openSession();
//通过 getMapper 方法实列化接口
UserMapper mapper=openSession.getMapper(UserMapper.class);
QueryVo vo=new QueryVo();
User user=new User();
user.setUsername("张");
user.setSex("1");
vo.setUser(user);
List<User> list=mapper.findUserbyVo(vo);
System.out.println(list);
openSession.close();
}
}
需求三：查询数据总数，利用 count(*)
1) 配置 sql
<!-- 只有返回结果为一行一列的时候，那么返回值类型时可以指定类型为基本类型 -->
<select id="findUserCount" resultType="int">
select count(*) from user
</select>
2) 配置接口
public Integer findUserCount();
3）测试
@Test public void testFindUserCount()throws Exception{ SqlSession opneSession=factory.openSession(); //通过 getMapper 方法实例化接口 UserMapper mapper=opneSession.getMapper(UserMapper.class); Integer count=mapper.findUserCount(); System.out.println("===="+count);}
需求四：动态增加查询条件
1）配置 sql
方式一：局部配置
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3.0-mapper.dtd"> <!--mapper 接口代理实现编写轨则 1. 映射文件中 namespace 要等于接口的全路径名 2. 映射文件中 sql 语句 id 要等于接口的方法名称 3. 映射文件中传入参数类型要等于接口方法的传入参数类型 4. 映射文件中返回结果集类型等于接口方法的返回值类型 --> <mapper namespace="com.clj.UserMapper.UserMapper"> <select id="findUserByUserNameAndSex" parameterType="com.clj.pojo.User" resultType="com.clj.pojo.User"> select * from user <!-- where 标签作用： 会自动向 sql 语句中添加 where 关键字 会去掉第一个条件的 and 关键字 --> <where> <if test="username!=null and username !=''"> and username like '%${username}%' </if> <if test="sex !=null and sex!=''"> and sex=#{sex} </if> </where> </select> </mapper>
方式二：全局配置
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3.0-mapper.dtd"> <!--mapper 接口代理实现编写轨则 1. 映射文件中 namespace 要等于接口的全路径名 2. 映射文件中 sql 语句 id 要等于接口的方法名称 3. 映射文件中传入参数类型要等于接口方法的传入参数类型 4. 映射文件中返回结果集类型等于接口方法的返回值类型 --> <mapper namespace="com.clj.UserMapper.UserMapper"> <!-- 封装 sql 条件，封装后可以重用 id: 此 sql 的唯一标识符 --> <sql id="user_where"> <where> <if test="username!=null and username !=''"> and username like '%${username}%' </if> <if test="sex !=null and sex!=''"> and sex=#{sex} </if> </where></sql><select id="findUserByUserNameAndSex" parameterType="com.clj.pojo.User" resultType="com.clj.pojo.User">
select * from user
<!-- where 标签作用：
会自动向 sql 语句中添加 where 关键字
会去掉第一个条件的 and 关键字
<where>
<if test="username!=null and username !=''">
and username like '%${username}%'
</if>
<if test="sex !=null and sex!=''">
and sex=#{sex}
</if>
</where>-->
<!-- 调用全局条件 -->
<include refid="user_where"/>
</select>
</mapper>
2）接口
public List<User> findUserByUserNameAndSex(User user);
3）测试
@Test public void testFindUserbyUserNameAndSex() throws Exception{ SqlSession opneSession=factory.openSession(); UserMapper mapper=opneSession.getMapper(UserMapper.class); User user=new User(); user.setUsername("张"); user.setSex("1"); List<User> list=mapper.findUserByUserNameAndSex(user); System.out.println(list); }
需求五：查询主键在某个范围内（动态添加条件查询之foreach标签）
1）sql 配置文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3.0-mapper.dtd"> <!--mapper 接口代理实现编写轨则 1. 映射文件中 namespace 要等于接口的全路径名 2. 映射文件中 sql 语句 id 要等于接口的方法名称 3. 映射文件中传入参数类型要等于接口方法的传入参数类型 4. 映射文件中返回结果集类型等于接口方法的返回值类型 --> <mapper namespace="com.clj.UserMapper.UserMapper"> <select id="findUserByIds" parameterType="com.clj.pojo.QueryVo" resultType="com.clj.pojo.User"> <!-- select * from user where id in (1,16,28,22) --> select * from user <where> <!-- 这里的 id 指定是 vo 中的集合属性 --> <if test="ids!=null"> <!-- foreach: 循环传入的集合参数 collection：传入的集合的变量名称 item: 每次循环将循环的数据放入这个变量中 open: 循环开始拼接的字符串 close: 循环结束拼接的字符串 separator: 循环中拼接的分隔符 --> <foreach collection="ids" item="id" open="id in (" close=")" separator=","> #{id} </foreach> </if> </where> </select> </mapper>
2）接口
public List<User> findUserByIds(QueryVo vo);
3）测试
public class UserMapperTest { private static SqlSessionFactory factory;</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">@Before作用在测试方法之前执行这个方法</span>@Before
public void setUp() throws Exception{
String resource="SqlMapConfig.xml";
InputStream inputstream=Resources.getResourceAsStream(resource);
factory=new SqlSessionFactoryBuilder().build(inputstream);
SqlSession openSession=factory.openSession();
}
@Test
public void testFindUserbyIds() throws Exception{
SqlSession opneSession=factory.openSession();
UserMapper mapper=opneSession.getMapper(UserMapper.class);
QueryVo vo=new QueryVo();
List<Integer> ids=new ArrayList<Integer>();
ids.add(1);
ids.add(16);
ids.add(28);
ids.add(22);
List<User> list=mapper.findUserByIds(vo);System.out.println(list);
}
}
需求六：关联查询之一对一查询
环境：一个客户对应多个订单，而一个订单对应一个客户
方法一：使用 resultType，定义订单信息 po 类，此 po 类中包括了订单信息和用户信息
1）预测 sql 语句写法：SELECT orders.*,user.username,userss.address FROM orders,user WHERE orders.user_id = user.id
2）定义订单类
package com.clj.pojo;import java.util.Date;
public class Orders {
private Integer id;</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> Integer userId; </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String number; </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> Date createtime; </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String note; </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Integer getId() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> id; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setId(Integer id) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.id =<span style="color: rgba(0, 0, 0, 1)"> id; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Integer getUserId() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> userId; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setUserId(Integer userId) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.userId =<span style="color: rgba(0, 0, 0, 1)"> userId; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getNumber() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> number; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setNumber(String number) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.number = number == <span style="color: rgba(0, 0, 255, 1)">null</span> ? <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)"> : number.trim(); } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Date getCreatetime() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> createtime; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setCreatetime(Date createtime) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.createtime =<span style="color: rgba(0, 0, 0, 1)"> createtime; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getNote() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> note; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setNote(String note) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.note = note == <span style="color: rgba(0, 0, 255, 1)">null</span> ? <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)"> : note.trim(); }}
3）定义封装客户和订单的 pojo 类，此类包含上边预测的 sql 语句字段
package com.clj.pojo;import java.util.Date;
//一对一：select a.*,b.id uid,username,birthday,sex,address
//from order a,user b
//where a.user_id=b.id
//注意：不能用实体 User 代替他属性
//缺点：java 是单继承
public class CustomerOrders extends Orders{
private int uid;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
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 getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}}
注意：OrdersCustom 类继承 Orders 类后 OrdersCustom 类包括了 Orders 类的所有字段，只需要定义用户的信息字段即可。(注意：这里不能用 user 来取代这些属性)
4）配置 sql 语句
1）在 order 类中定义 user 属性
package com.clj.pojo;import java.util.Date;
public class Orders {
private Integer id;</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> Integer userId; </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String number; </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> Date createtime; </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String note; </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> User user; </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> User getUser() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> user; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setUser(User user) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.user =<span style="color: rgba(0, 0, 0, 1)"> user; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Integer getId() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> id; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setId(Integer id) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.id =<span style="color: rgba(0, 0, 0, 1)"> id; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Integer getUserId() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> userId; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setUserId(Integer userId) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.userId =<span style="color: rgba(0, 0, 0, 1)"> userId; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getNumber() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> number; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setNumber(String number) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.number = number == <span style="color: rgba(0, 0, 255, 1)">null</span> ? <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)"> : number.trim(); } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Date getCreatetime() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> createtime; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setCreatetime(Date createtime) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.createtime =<span style="color: rgba(0, 0, 0, 1)"> createtime; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getNote() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> note; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setNote(String note) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.note = note == <span style="color: rgba(0, 0, 255, 1)">null</span> ? <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)"> : note.trim(); }}
2）配置 sql 语句
<!-- 关于一对一查询 --> <!-- 方式一：自动映射 : 利用类中的属性对应表中的字段 需要提供封装 user 和 order 属性的类 --> <select id="findOrderAndUser1" resultType="com.clj.pojo.CustomerOrders"> select a.*,b.id uid,username,birthday,sex,address from orders a,user b where a.user_id=b.id </select>
5）配置接口
public List<CustomerOrders> findOrderAndUser1();
6）定义测试类
@Test public void testFindORdersAndUser() throws Exception{ SqlSession opneSession=factory.openSession(); UserMapper mapper=opneSession.getMapper(UserMapper.class); List<CustomerOrders> list=mapper.findOrderAndUser1(); System.out.println(list); }
总结：此方法虽然简单，定义专门的 pojo 类作为输出类型，其中定义了 sql 查询结果集所有的字段。但是缺点是 pojo 类只能单继承
方式二：使用 resultMap，定义专门的 resultMap 用于映射一对一查询结果
1）配置 sql 注意此时不需要 CustomerOrders 类
<!-- 一对一：手动映射: 需要在 order 类中封装 User 这个属性 --> <!-- id：resultMap 的唯一标识 type: 将查询出的数据放入指定的对象中 注意：手动映射需要指定数据表中的字段名与 java 中 pojo 类中的属性名称对应的关系 --> <resultMap type="com.clj.pojo.Orders" id="orderAndUserResultMap"> <!--id 标签指定主键字段对应关系 column: 列，数据库中的字段名称 property：属性，java 中 pojo 中的属性名称 --> <id column="id" property="id"/> <!-- result：指定 非主键对应的关系 --> <result column="user_id" property="userId"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/> <!-- association：表示进行关联查询单条记录
这个标签指定单个对象的对应关系 property: 表示关联查询的结果存储在 cn.itcast.mybatis.po.Orders 的 user 属性中 javaType：user 属性中类型的路径 --> <association property="user" javaType="com.clj.pojo.User">
<!-- 查询结果的 user_id 列对应关联对象的 id 属性，这里是 <id /> 表示 user_id 是关联查询对象的唯一标识 --> <id column="uid" property="id"/>
<!-- 查询结果的 username 列对应关联对象的 username 属性 --> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="birthday" property="birthday"/> <result column="address" property="address"/> </association> </resultMap> <select id="findOrderAndUser2" resultMap="orderAndUserResultMap"> select a.*,b.id uid,username,birthday,sex,address from orders a,user b where a.user_id=b.id </select>
2）配置接口
public List<Orders> findOrderAndUser2();
3）测试类
@Test public void findOrderAndUser2() throws Exception{ SqlSession opneSession=factory.openSession(); UserMapper mapper=opneSession.getMapper(UserMapper.class); List<Orders> list=mapper.findOrderAndUser2(); for(Orders order:list){System.out.println(order.getUserId()+"\t"+order.getCreatetime());} }
需求七：一对多查询
1）预测 sql 语句
SELECT u.*, o.id oid,o.number,o.createtime,o.note FROM `user` u LEFT JOIN orders o ON u.id = o.user_id
2）在 user pojo 类中加上 List 集合属性
package com.clj.pojo;import java.util.Date;
import java.util.List;public class User {
private int id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
private List<Orders> ordersList;</span><span style="color: rgba(0, 0, 255, 1)">public</span> List<Orders><span style="color: rgba(0, 0, 0, 1)"> getOrdersList() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> ordersList; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setOrdersList(List<Orders><span style="color: rgba(0, 0, 0, 1)"> ordersList) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.ordersList =<span style="color: rgba(0, 0, 0, 1)"> ordersList; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> getId() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> id; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setId(<span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> id) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.id =<span style="color: rgba(0, 0, 0, 1)"> id; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getUsername() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> username; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setUsername(String username) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.username =<span style="color: rgba(0, 0, 0, 1)"> username; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getSex() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> sex; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setSex(String sex) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.sex =<span style="color: rgba(0, 0, 0, 1)"> sex; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Date getBirthday() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> birthday; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setBirthday(Date birthday) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.birthday =<span style="color: rgba(0, 0, 0, 1)"> birthday; } </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getAddress() { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> address; } </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setAddress(String address) { </span><span style="color: rgba(0, 0, 255, 1)">this</span>.address =<span style="color: rgba(0, 0, 0, 1)"> address; } @Override </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String toString() { </span><span style="color: rgba(0, 0, 255, 1)">return</span> "User [id=" + id + ", username=" + username + ", sex=" +<span style="color: rgba(0, 0, 0, 1)"> sex </span>+ ", birthday=" + birthday + ", address=" + address + "]"<span style="color: rgba(0, 0, 0, 1)">; }}
3）配置 sql
<!-- 一对多 -->
<resultMap type="com.clj.pojo.User" id="userAndOrderMap">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
<result column="address" property="address"/>
<!-- 指定对应的集合对象关系映射
property: 将数据放入 User 对象中的 orderList 属性中
ofType：指定 orderList 属性泛型类型
-->
<collection property="ordersList" ofType="com.clj.pojo.Orders">
<id column="oid" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!-- 注意：这里不需要配置 user 属性，他是用于一对一时配置的属性，与这里无关 -->
</collection>
</resultMap>
<select id="findUserAndOrders" resultMap="userAndOrderMap">
select a.*,b.id oid,user_id,number,createtime
from user a,order b where a.id=b.user_id
</select>
4）定义接口
public List<User> findUserAndOrders();
5）测试类
@Test public void findUserAndOrders() throws Exception{ SqlSession opneSession=factory.openSession(); UserMapper mapper=opneSession.getMapper(UserMapper.class); List<User> list=mapper.findUserAndOrders(); System.out.println(list);}</span></pre>
九、Mybatis 与 Spring 整合
1、整合思路
SqlSessionFactory 对象应该放到 spring 容器中作为单例存在。
传统 dao 的开发方式中，应该从 spring 容器中获得 sqlsession 对象。
Mapper 代理形式中，应该从 spring 容器中直接获得 mapper 的代理对象。
数据库的连接以及数据库连接池事务管理都交给 spring 容器来完成。
2、环境部署
1）导入所需 jar 包
在导入 spring 包和 mybaits 包之外还要 jar 两者的整合包
至于 maven 构建项目，其 pom 文件的约束为：
注意：至于 jdbc 可以用 c3p0，也可以用 dbcp，但是无论这两个都需要用到 spring-jdbc 的 jar 包，否则会报错误
Caused by: org.springframework.beans.PropertyBatchUpdateException; nested PropertyAccessExceptions (1) are: PropertyAccessException 1: org.springframework.beans.MethodInvocationException: Property 'dataSource' threw exception; nested exception is java.lang.NoClassDefFoundError: org/springframework/jdbc/datasource/TransactionAwareDataSourceProxy at org.springframework.beans.AbstractPropertyAccessor.setPropertyValues(AbstractPropertyAccessor.java:121)at org.springframework.beans.AbstractPropertyAccessor.setPropertyValues(AbstractPropertyAccessor.java:75) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1570) ... 41 more
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.17.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">1.2</span>.<span style="color: rgba(128, 0, 128, 1)">17</span></version>
</dependency>
<!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/mysql/mysql-connector-java --></span>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">5.0</span>.<span style="color: rgba(128, 0, 128, 1)">8</span></version>
</dependency>
<!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/org.mybatis/mybatis-spring --></span>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">1.3</span>.<span style="color: rgba(128, 0, 128, 1)">1</span></version>
</dependency>
<!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/org.springframework/spring-beans --></span>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">4.3</span>.<span style="color: rgba(128, 0, 128, 1)">17</span>.RELEASE</version>
</dependency>
<!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/org.springframework/spring-context --></span>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">4.3</span>.<span style="color: rgba(128, 0, 128, 1)">17</span>.RELEASE</version>
</dependency>
<!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/org.springframework/spring-core --></span>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">4.3</span>.<span style="color: rgba(128, 0, 128, 1)">17</span>.RELEASE</version>
</dependency>
<!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/org.springframework/spring-expression --></span>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">4.3</span>.<span style="color: rgba(128, 0, 128, 1)">17</span>.RELEASE</version>
</dependency>
<!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/org.aopalliance/com.springsource.org.aopalliance --></span>
<dependency>
<groupId>aopalliance</groupId>
<artifactId>aopalliance</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">1.0</span></version>
</dependency>
<!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/org.aspectj/org.aspectj.weaver --></span>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">1.7</span>.<span style="color: rgba(128, 0, 128, 1)">4</span></version>
</dependency>
<!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/org.springframework/spring-aspects --></span>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">4.3</span>.<span style="color: rgba(128, 0, 128, 1)">17</span>.RELEASE</version>
</dependency>
<!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/org.springframework/spring-tx --></span>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">4.3</span>.<span style="color: rgba(128, 0, 128, 1)">17</span>.RELEASE</version>
</dependency>
<!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/junit/junit --></span>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">4.12</span></version>
<scope>test</scope>
</dependency>
<!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/org.springframework/spring-test --></span>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">4.3</span>.<span style="color: rgba(128, 0, 128, 1)">17</span>.RELEASE</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version><span style="color: rgba(128, 0, 128, 1)">0.9</span>.<span style="color: rgba(128, 0, 128, 1)">1.2</span></version>
</dependency></pre>
2）在工程项目下 (非 src) 创建一个源码包，用来存放配置文件
配置连接数据库驱动配置文件 db.properties
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://192.168.174.130:3306/SSM jdbc.username=root jdbc.password=root
1)) 配置日志配置文件 log4j.properties
### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=c\:mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
### set log levels - for more verbose logging change 'info' to 'debug' ###
log4j.rootLogger=info, stdout
配置 spring 的配置文件 applicationContext.xml
1))) 配置数据库驱动，加载 db.properties 文件
<!-- 加载配置文件 -->
<context:property-placeholder location="classpath:db.properties" />
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.password}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
2))) 将 sqlSessionfactory 的创建交给 spring 管理
<!-- 整合后会话工厂归 spring 来管理 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 指定 mybatis 核心配置文件 -->
<property name="configLocation" value="classpath:SqlMapConfig.xml"></property>
<!-- 指定会话工厂使用的数据源 -->
<property name="dataSource" ref="dataSource"/>
</bean>
全部代码如下
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd"><!-- 加载配置文件 --> <context:property-placeholder location="classpath:db.properties" /> <!-- 数据库连接池 --></pre><bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean><!-- 整合后会话工厂归 spring 来管理 --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <!-- 指定 mybatis 核心配置文件 --> <property name="configLocation" value="classpath:SqlMapConfig.xml"></property> <!-- 指定会话工厂使用的数据源 --> <property name="dataSource" ref="dataSource"/> </bean> </beans>
其中 org.mybatis.spring.SqlSessionFactoryBean 的路径配置如下图
2)) 配置 mybatis 数据 sql 映射文件
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "https://www.mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <typeAliases> <package name="com.clj.pojo"/> </typeAliases><mappers> <mapper resource="User.xml"/> <<span style="color: rgba(0, 0, 255, 1)">package</span> name="com.clj.UserMapper"/> </mappers></configuration>
3、整合之 Dao 层的开发
1）开发方式一之传统 dao 开发
1)) 思路：
想为接口 + 实现类来完成。需要 dao 实现类需要继承 SqlsessionDaoSupport 类
2)) 接口定义和 javaBean
这里定义了根据 id 查询和根据用户名查询两个方法package com.clj.dao;
import java.util.List;import com.clj.pojo.User;
public interface UserDao {
public User findUserById(Integer id);
public List<User> findUserByUserName(String username);
public List<UserBean> findUserByUserNameAndMoney(UserBean userBean);
public List<UserBean> findUserById(QueryVo vo);
}
@Component public class UserBean { private Integer id; private String username; private String age; private Integer money; //set/get 方法 }
@Component public class QueryVo { private List<Integer> ids; }
3)) 实现类定义
实现类实现 SqlSessionDaoSupport
package com.clj.dao;import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.support.SqlSessionDaoSupport;import com.clj.pojo.User;
//此时 dao 层要继承 SqlSessionDaoSupport
public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao{
@Override
public User findUserById(Integer id) {
//SqlSession 是线程不安全的，所以他的最佳使用的范围在方法体内
SqlSession opeanSession=this.getSqlSession();
User user=opeanSession.selectOne("test.findUserById",id);
//opeanSession.close();
return user;} @Override </span><span style="color: rgba(0, 0, 255, 1)">public</span> List<User><span style="color: rgba(0, 0, 0, 1)"> findUserByUserName(String username) { SqlSession opeanSession</span>=<span style="color: rgba(0, 0, 255, 1)">this</span><span style="color: rgba(0, 0, 0, 1)">.getSqlSession(); List</span><User> list=opeanSession.selectList("test.findUserByUserName"<span style="color: rgba(0, 0, 0, 1)">,username); </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> list; }</span></pre>public List<UserBean> findUserByUserNameAndMoney(UserBean userBean) {
SqlSession sqlSession=this.getSqlSession();
List<UserBean> user=sqlSession.selectList("test.findUserByUserNameAndMoney",userBean);
return user;
}@Override
public List<UserBean> findUserById(QueryVo vo) {
SqlSession sqlSession=this.getSqlSession();
List<UserBean> user=sqlSession.selectList("test.findUserById",vo);
return user;
}}
4))注入 dao 层于 sprign 配置文件 (applicationContext.xml)
这里为 dao 层注入了 sqlSessionFactory
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd"><!-- 加载配置文件 --> <context:property-placeholder location="classpath:db.properties" /> <!-- 数据库连接池 --> <bean id="dataSource" <span style="color: rgba(0, 0, 255, 1)">class</span>="org.apache.commons.dbcp.BasicDataSource"<span style="color: rgba(0, 0, 0, 1)"> destroy</span>-method="close"> <property name="driverClassName" value="${jdbc.driver}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> <property name="maxActive" value="10" /> <property name="maxIdle" value="5" /> </bean><br> <context:component-scan base-package="com.aliance.entity"/> <!-- 整合后会话工厂归spring来管理 --> <bean id="sqlSessionFactory" <span style="color: rgba(0, 0, 255, 1)">class</span>="org.mybatis.spring.SqlSessionFactoryBean"> <!-- 指定mybatis核心配置文件 --> <property name="configLocation" value="classpath:SqlMapConfig.xml"></property> <!-- 指定会话工厂使用的数据源 --> <property name="dataSource" ref="dataSource"/> </bean> <!--配置原生Dao实现 --><bean id="userDao" class="com.clj.dao.UserDaoImpl">
<property name="sqlSessionFactory" ref="sqlSessionFactory"/>
</bean>
</beans>
5))SqlMap.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> <typeAliases> <typeAlias type="com.aliance.entity.UserBean" alias="userBean"/></typeAliases> <mappers> <mapper resource="User.xml"/> </mappers></configuration>
6))User.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="test"> <sql id="testWhere"> <where> <if test="username!=null||username!=''"> and username like '%${username}%' </if> <if test="age!=null"> and age> #{age} </if></where> </sql> <select id="findUserByUserNameAndMoney" parameterType="userBean" resultType="userBean"><span style="color: rgba(0, 0, 0, 1)"> select </span>*<span style="color: rgba(0, 0, 0, 1)"> from userinfo </span><include refid="testWhere"/> </select> <select id="findUserById" parameterType="com.aliance.entity.QueryVo" resultType="userBean"><span style="color: rgba(0, 0, 0, 1)"> select </span>*<span style="color: rgba(0, 0, 0, 1)"> from userinfo </span><where> <<span style="color: rgba(0, 0, 255, 1)">if</span> test="ids!=null"> <foreach collection="ids" item="id" open="and id in (" close=")" separator=","><span style="color: rgba(0, 0, 0, 1)"> #{id} </span></foreach> </<span style="color: rgba(0, 0, 255, 1)">if</span>> </where> </select></mapper>
6)) 测试类
方式一：
package com.clj.Test;import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;import com.clj.dao.UserDao;
import com.clj.pojo.User;public class UserDaoTest {
private ApplicationContext applicationContext;
@Before
public void setUp() throws Exception{
//这里可加 classpath, 也可不加
String configLocation="classpath:applicationContext.xml";
applicationContext=new ClassPathXmlApplicationContext(configLocation);
}
@Test
public void testFindUserById() throws Exception{
//获取 UserDao 对象，getBean 中的字符串是在 applcationContext.xml 中神明的
UserDao userDao=(UserDao)applicationContext.getBean("userDao");
User user=userDao.findUserById(1);
System.out.println(user);
}
}
方式二：
@RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration("classpath:ApplicationContext.xml") public class TestDemo1 { @Resource private ApplicationContext application; @Resource private UserBean userBean; @Resource private UserDao userDao; @Resource private QueryVo vo; @Test public void run2(){ userDao=(UserDao) application.getBean("userDao"); userBean.setUsername("李"); userBean.setAge("11"); List<UserBean> list=userDao.findUserByUserNameAndMoney(userBean); System.out.println(list); } @Test public void run3(){ userDao=(UserDao) application.getBean("userDao"); List<Integer> list=new ArrayList(); list.add(1); list.add(3); list.add(6); vo.setIds(list); List<UserBean> temp=userDao.findUserById(vo); System.out.println(temp);}}
注意：这里由于 sqlSessionFactory 归 spring 管理，所以其关闭也是有 spring 管理，如果在测试中手动关闭 session，会报错
2) 开发方式二：Mapper 代理形式开发 dao
1)) 新建一个 mapper 包，用来配置 mapper 的接口和配置文件
mapper 接口：这里和整合前 mybatis 中动态 mapper 代理接口代码一致
package com.clj.UserMapper;import java.util.List;
import com.clj.pojo.CustomerOrders;
import com.clj.pojo.Orders;
import com.clj.pojo.QueryVo;
import com.clj.pojo.User;//与之关乎的配置文件要在同一个目录包下
public interface UserMapper {
public User findUserById(Integer id);
//注意：这里是模糊查询，配置文件虽然写的是 User，实际返回的是 List 集合
//动态代理形式中，如果返回结果集 List，那么 mybatis 会在生成实现类的时候会自动调用 selectList 方法
public List<User> findUserByUserName(String userName);
public void insertUser(User user);
public List<User> findUserbyVo(QueryVo vo);
public Integer findUserCount();
public List<User> findUserByUserNameAndSex(User user);
public List<User> findUserByIds(QueryVo vo);
public List<CustomerOrders> findOrderAndUser1();
public List<Orders> findOrderAndUser2();
public List<User> findUserAndOrders();
}
2)) 配置 sql 映射文件：这里和整合前 mybatis 中动态 mapper 代理接口代码一致
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3.0-mapper.dtd"> <!--mapper 接口代理实现编写轨则 1. 映射文件中 namespace 要等于接口的全路径名 2. 映射文件中 sql 语句 id 要等于接口的方法名称 3. 映射文件中传入参数类型要等于接口方法的传入参数类型 4. 映射文件中返回结果集类型等于接口方法的返回值类型 --> <mapper namespace="com.clj.UserMapper.UserMapper"> <!-- 封装 sql 条件，封装后可以重用 id: 此 sql 的唯一标识符 --> <sql id="user_where"> <where> <if test="username!=null and username !=''"> and username like '%${username}%' </if> <if test="sex !=null and sex!=''"> and sex=#{sex} </if> </where></sql> <select id="findUserById" parameterType="int" resultType="com.clj.pojo.User"><span style="color: rgba(0, 0, 0, 1)"> select </span>* from user where id=<span style="color: rgba(0, 0, 0, 1)">#{id} </span></select> <select id="findUserByUserName" parameterType="java.lang.String" resultType="com.clj.pojo.User"> <!--select * from user where username like #{name} --><span style="color: rgba(0, 0, 0, 1)"> select </span>* from user where username like '%${value}%' </select> <!-- 这里返回值使用的别名 --> <insert id="insertUser" parameterType="user"> <selectKey keyProperty="id" order="AFTER" resultType="int"><span style="color: rgba(0, 0, 0, 1)"> select LAST_INSERT_ID() </span></selectKey><span style="color: rgba(0, 0, 0, 1)"> insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address}) </span></insert> <delete id="delUserById" parameterType="int"><span style="color: rgba(0, 0, 0, 1)"> delete from user where id</span>=<span style="color: rgba(0, 0, 0, 1)">#{id} </span></delete> <update id="updateUserById" parameterType="com.clj.pojo.User"><span style="color: rgba(0, 0, 0, 1)"> update user set username</span>=#{username} where id=<span style="color: rgba(0, 0, 0, 1)">#{id} </span></update> <select id="findUserbyVo" parameterType="com.clj.pojo.QueryVo" resultType="com.clj.pojo.User"><span style="color: rgba(0, 0, 0, 1)"> select </span>* from user where username like '%${user.username}%' and sex=<span style="color: rgba(0, 0, 0, 1)">#{user.sex} </span></select> <!-- 只有返回结果为一行一列的时候，那么返回值类型时可以指定类型为基本类型 --> <select id="findUserCount" resultType="int"><span style="color: rgba(0, 0, 0, 1)"> select count(</span>*<span style="color: rgba(0, 0, 0, 1)">) from user </span></select> <select id="findUserByUserNameAndSex" parameterType="com.clj.pojo.User" resultType="com.clj.pojo.User"><span style="color: rgba(0, 0, 0, 1)"> select </span>*<span style="color: rgba(0, 0, 0, 1)"> from user </span><span style="color: rgba(51, 153, 102, 1)"><!-- where标签作用： 会自动向sql语句中添加where关键字 会去掉第一个条件的and关键字 <where> <if test="username!=null and username !=''"> and username like '%${username}%' </if> <if test="sex !=null and sex!=''"> and sex=#{sex} </if> </where>--> <!-- 调用全局条件 --></span> <include refid="user_where"/> </select> <select id="findUserByIds" parameterType="com.clj.pojo.QueryVo" resultType="com.clj.pojo.User"> <!-- select * from user where id in (1,16,28,22) --><span style="color: rgba(0, 0, 0, 1)"> select </span>*<span style="color: rgba(0, 0, 0, 1)"> from user </span><where> <!-- 这里的id指定是vo中的集合属性 --> <<span style="color: rgba(0, 0, 255, 1)">if</span> test="ids!=null"> <span style="color: rgba(51, 153, 102, 1)"> <!-- foreach:循环传入的集合参数 collection：传入的集合的变量名称 item:每次循环将循环的数据放入这个变量中 open:循环开始拼接的字符串 close:循环结束拼接的字符串 separator:循环中拼接的分隔符 --></span> <foreach collection="ids" item="id" open="id in (" close=")" separator=","><span style="color: rgba(0, 0, 0, 1)"> #{id} </span></foreach> </<span style="color: rgba(0, 0, 255, 1)">if</span>> </where> </select> <!-- 关于一对一查询 --> <!--<span style="color: rgba(0, 0, 0, 1)"> 方式一：自动映射 :利用类中的属性对应表中的字段 需要提供封装user和order属性的类</span>--> <select id="findOrderAndUser1" resultType="com.clj.pojo.CustomerOrders"><span style="color: rgba(0, 0, 0, 1)"> select a.</span>*<span style="color: rgba(0, 0, 0, 1)">,b.id uid,username,birthday,sex,address from orders a,user b where a.user_id</span>=<span style="color: rgba(0, 0, 0, 1)">b.id </span></select> <!-- 一对以：手动映射:需要在order类中封装User这个属性 --><!--
id：resultMap 的唯一标识
type: 将查询出的数据放入指定的对象中
注意：手动映射需要指定数据表中的字段名与 java 中 pojo 类中的属性名称对应的关系
-->
<resultMap type="com.clj.pojo.Orders" id="orderAndUserResultMap">
<!--id 标签指定主键字段对应关系
column: 列，数据库中的字段名称
property：属性，java 中 pojo 中的属性名称
-->
<id column="id" property="id"/>
<!-- result：指定 非主键对应的关系 -->
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!-- 这个标签指定单个对象的对应关系
property: 指定将数据放入 Orders 中的 user 属性中
javaType：user 属性中类型的路径
-->
<association property="user" javaType="com.clj.pojo.User">
<id column="uid" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="findOrderAndUser2" resultMap="orderAndUserResultMap">
select a.,b.id uid,username,birthday,sex,address
from orders a,user b
where a.user_id=b.id
</select>
<!-- 一对多 -->
<resultMap type="com.clj.pojo.User" id="userAndOrderMap">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
<result column="address" property="address"/>
<!-- 指定对应的集合对象关系映射
property: 将数据放入 User 对象中的 orderList 属性中
ofType：指定 orderList 属性泛型类型
-->
<collection property="ordersList" ofType="com.clj.pojo.Orders">
<id column="oid" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!-- 注意：这里不需要配置 user 属性，他是用于一对一时配置的属性，与这里无关 -->
</collection>
</resultMap>
<select id="findUserAndOrders" resultMap="userAndOrderMap">
select a.,b.id oid,user_id,number,createtime
from user a,order b where a.id=b.user_id
</select>
</mapper>
3)) 在 applicationContext.xml 映射文件中注入 mapper 接口
方式一：手动配置
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd"><!-- 加载配置文件 --> <context:property-placeholder location="classpath:db.properties" /> <!-- 数据库连接池 --> <bean id="dataSource" <span style="color: rgba(0, 0, 255, 1)">class</span>="org.apache.commons.dbcp.BasicDataSource"<span style="color: rgba(0, 0, 0, 1)"> destroy</span>-method="close"> <property name="driverClassName" value="${jdbc.driver}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> <property name="maxActive" value="10" /> <property name="maxIdle" value="5" /> </bean> <!-- 整合后会话工厂归spring来管理 --> <bean id="sqlSessionFactory" <span style="color: rgba(0, 0, 255, 1)">class</span>="org.mybatis.spring.SqlSessionFactoryBean"> <!-- 指定mybatis核心配置文件 --> <property name="configLocation" value="classpath:SqlMapConfig.xml"></property> <!-- 指定会话工厂使用的数据源 --> <property name="dataSource" ref="dataSource"/> </bean> <!--配置原生Dao实现 --> <bean id="userDao" <span style="color: rgba(0, 0, 255, 1)">class</span>="com.clj.dao.UserDaoImpl"> <property name="sqlSessionFactory" ref="sqlSessionFactory"/> </bean> <!--<span style="color: rgba(0, 0, 0, 1)"> 动态代理方式：Mappper接口代理实现--> </span><span style="color: rgba(255, 0, 0, 1)"><bean id="userMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"> 配置Mapper接口的全路径名称,其中property name="mapperInterface"是根据主类来配置的 <property name="mapperInterface" value="com.clj.UserMapper.UserMapper"/> <property name="sqlSessionFactory" ref="sqlSessionFactory"/> </bean></span></beans>
public class MapperFactoryBean<T> extends SqlSessionDaoSupport implements FactoryBean<T> {private Class<T> mapperInterface;
}
其中 MapperScannerConfigurer 的路径为
方式二：采用包扫描的方式
<!--使用包扫描的方式批量引入 Mapper 扫描后引用时可以使用类名，首字母小写 --> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <!-- 指定要扫描的包的全路径名称 ，如果有多个，用逗号 ',' 隔开 --> <!-- 此时可以关掉 SqlMapConfig.xml 文件中对 Mapper 的扫描 --> <property name="basePackage" value="com.clj.UserMapper"/> </bean>
其中 MapperScannerConfigurer 的路径为
如果采用包扫描的方式，他与 mybatis 中的 sqlMapConfig.xml 文件中扫描有冲突，需要屏蔽 sqlMapConfig.xml 的代码
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "https://www.mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <typeAliases> <package name="com.clj.pojo"/> </typeAliases><mappers> <mapper resource="User.xml"/> <span style="color: rgba(128, 128, 0, 1)"> <!-- <package name="com.clj.UserMapper"/> --></span> </mappers></configuration>
4)) 测试类
package com.clj.Test;import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;import com.clj.UserMapper.UserMapper;
import com.clj.pojo.User;public class UserMapperTest {
private ApplicationContext applicationContext;
@Before
public void setUp() throws Exception{
//这里可加 classpath, 也可不加
String configLocation="classpath:applicationContext.xml";
applicationContext=new ClassPathXmlApplicationContext(configLocation);
}
@Test
public void testFindUserById()throws Exception{
// 方式一：手动配置：通过 bean 中 id 获取
UserMapper userMapper=(UserMapper) applicationContext.getBean("userMapper");
// 方式二：扫描方式：通过 mapper 接口名进行获取，注意，getBean() 中的值是通过类名第一个字母小写得到
User user=userMapper.findUserById(1);
System.out.println(user);}}
十、MyBatis 之逆向工程
1、逆向工程作用
自动生成 Pojo 类, 还可以自动生成 Mapper 接口和映射文件, 注意: 生成的方式是追加而不是覆盖, 所以不可以重复生成, 重复生成的文件有问题, 如果想重复生成将原来生 成的文件删除
2、快速部署环境
1）下载核心 jar 包 mybatis-generator-core-1.3.2
2）在项目工程 (非 src 下) 构建源码包，用来存放配置文件
3）创建 log4j.properties 文件 (只是打印日志，可以不配置)
# Global logging configuration log4j.rootLogger=DEBUG, stdout # Console output... log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
4）配置 generatorConfig.xml 文件
此文件用来配置 Mapper 中的信息 ( 注意几点：1. 添加要生成的数据库表 2.pojo文件所在包路径 3.mapper文件所在包路径)
<?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>
<context id="testTables" targetRuntime="MyBatis3">
<commentGenerator>
<!-- 是否去除自动生成的注释 true：是 ： false: 否 -->
<property name="suppressAllComments" value="true" />
</commentGenerator>
<!-- 数据库连接的信息：驱动类、连接地址、用户名、密码 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://192.168.174.130:3306/SSM"userId="root"
password="root">
</jdbcConnection>
<!-- <jdbcConnection driverClass="oracle.jdbc.OracleDriver"
connectionURL="jdbc:oracle:thin:@127.0.0.1:1521:yycg"
userId="yycg"
password="yycg">
</jdbcConnection> --><!--<span style="color: rgba(0, 0, 0, 1)"> 默认false，把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer，为 true时把JDBC DECIMAL 和 NUMERIC 类型解析为java.math.BigDecimal </span>--> <javaTypeResolver> <property name="forceBigDecimals" value="false" /> </javaTypeResolver> <!-- targetProject:生成PO类的位置 --> <javaModelGenerator targetPackage="cn.clj.pojo"<span style="color: rgba(0, 0, 0, 1)"> targetProject</span>=".\src"> <!-- enableSubPackages:是否让schema作为包的后缀 --> <property name="enableSubPackages" value="false" /> <!-- 从数据库返回的值被清理前后的空格 --> <property name="trimStrings" value="true" /> </javaModelGenerator> <!-- targetProject:mapper映射文件生成的位置 --> <sqlMapGenerator targetPackage="cn.clj.mapper"<span style="color: rgba(0, 0, 0, 1)"> targetProject</span>=".\src"> <!-- enableSubPackages:是否让schema作为包的后缀 --> <property name="enableSubPackages" value="false" /> </sqlMapGenerator> <!-- targetPackage：mapper接口生成的位置 --> <javaClientGenerator type="XMLMAPPER"<span style="color: rgba(0, 0, 0, 1)"> targetPackage</span>="cn.clj.mapper"<span style="color: rgba(0, 0, 0, 1)"> targetProject</span>=".\src"> <!-- enableSubPackages:是否让schema作为包的后缀 --> <property name="enableSubPackages" value="false" /> </javaClientGenerator> <!-- 指定数据库表 --> <table tableName="orders"></table> <table tableName="user"></table> <!-- <table schema="" tableName="sys_user"></table> <table schema="" tableName="sys_role"></table> <table schema="" tableName="sys_permission"></table> <table schema="" tableName="sys_user_role"></table> <table schema="" tableName="sys_role_permission"></table> --> <!--<span style="color: rgba(0, 0, 0, 1)"> 有些表的字段需要指定java类型 </span><table schema="" tableName=""> <columnOverride column="" javaType="" /> </table> --> </context></generatorConfiguration>
5）根据指定的路径创建文件包
这里要创建 cn.clj.mapper 与 cn.clj.pojo 包，逆向工程不会自动帮你键包，所有要提前创建
6）创建启动类
注意：这里的要配置 generatorConfig.xml 文件位置
package com.clj.Test;import java.io.File;
import java.util.ArrayList;
import java.util.List;import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;public class StartService {
public void generator() throws Exception{
List<String> warnings = new ArrayList<String>();
boolean overwrite = true;
File configFile = new File("config/generatorConfig.xml");
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(configFile);
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config,
callback, warnings);
myBatisGenerator.generate(null);
}
public static void main(String[] args) throws Exception {
try {
StartService startService = new StartService();
startService.generator();
} catch (Exception e) {
e.printStackTrace();
}
}
}