文章目录
- 01-多表关系设计回顾(掌握)
- 02-关联查询环境搭建(掌握)
- 03-关联查询之一对一查询(掌握)
- 04-关联查询之一对多查询(掌握)
- 05-分步查询之一对一(掌握)
- 06-分步查询之一对多(掌握)
- 07-mybatis延迟加载概述(掌握)
- 08-延迟加载之一对一(掌握)
- 09-延迟加载之一对多(掌握)
- 10-mybatis缓存概述(掌握)
- 11-一级缓存的验证(掌握)
- 12-一级缓存的清空(掌握)
- 13-二级缓存的验证(掌握)
- 14-逆向工程的概念及机制(掌握)
- 15-逆向工程操作(掌握)
- 16-QBC查询(掌握)
- 17-mybatis注解开发(了解)
- 18-mybatis注解操作CRUD(掌握)
- 19-复杂关系映射注解说明(了解)
- 20-注解实现一对一查询(了解)
- 21-注解实现一对多查询(了解)
01-多表关系设计回顾(掌握)
- ①一对一
- 表A中的一条记录对应表B中的一条记录,表B中的一条记录对应表A中的一条记录
- 比如:公司和注册地址
- 一对一查询
- ②一对多
- 表A中的一条记录对应表B中的多条记录,表B中的一条记录对应表A中的一条记录
- 比如:部门和员工
- 一对多查询,一对一查询
- ③多对多
- 表A中的一条记录对应表B中的多条记录,表B中的一条记录对应表A中的多条记录
- 比如:学生和课程
- 一对多查询
02-关联查询环境搭建(掌握)
-
环境搭建
CREATE TABLE tb_order( order_id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_no BIGINT , order_state VARCHAR(40) ); CREATE TABLE tb_customer( customer_id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(60), address VARCHAR(60) ); ALTER TABLE tb_order ADD FOREIGN KEY(customer_no) REFERENCES tb_customer(customer_id); INSERT INTO tb_customer(name,address) VALUES('张三','武汉市1'); INSERT INTO tb_customer(name,address) VALUES('李四','武汉市2'); INSERT INTO tb_customer(name,address) VALUES('王五','武汉市3'); INSERT INTO tb_order(customer_no,order_state ) VALUES(1,'完成'); INSERT INTO tb_order(customer_no,order_state ) VALUES(2,'完成'); INSERT INTO tb_order(customer_no,order_state ) VALUES(2,'完成'); INSERT INTO tb_order(customer_no,order_state ) VALUES(2,'未完成');
-
代码实现
public class Customer { private Integer customerId; private String name; private String address; private List<Order> orderList; }
public class Order { private Integer orderId; private String orderState; private Customer customer; }
03-关联查询之一对一查询(掌握)
-
需求
- 根据id查询订单信息,关联查询用户信息
-
代码实现
Order selectOrderById(Integer orderId) throws Exception; Order selectOrderById2(Integer orderId) throws Exception; Order selectOrderById3(Integer orderId) throws Exception;
<select id="selectOrderById" resultType="order"> select * from t_order where order_id = #{orderId} </select> <select id="selectOrderById2" resultType="order"> select * from t_order inner join t_customer on t_order.customer_no = t_customer.customer_id where t_order.order_id = #{orderId} </select> <resultMap id="orderMap" type="order"> <id property="orderId" column="order_id" javaType="int"></id> <result property="orderState" column="order_state" javaType="string"></result> <association property="customer" javaType="customer"> <id property="customerId" column="customer_id" javaType="int"></id> <result property="name" column="name" javaType="string"></result> <result property="address" column="address" javaType="string"></result> </association> </resultMap> <select id="selectOrderById3" resultMap="orderMap"> select * from t_order inner join t_customer on t_order.customer_no = t_customer.customer_id where t_order.order_id = #{orderId} </select>
04-关联查询之一对多查询(掌握)
-
需求
- 根据id查询用户信息,关联查询订单信息
-
代码实现
Customer selectCustomerById(Integer customerId) throws Exception; Customer selectCustomerById2(Integer customerId) throws Exception;
<select id="selectCustomerById" resultType="customer"> select * from t_customer left outer join t_order on t_customer.customer_id = t_order.customer_no where t_customer.customer_id = #{customerId} </select> <resultMap id="customerMap" type="customer"> <id property="customerId" column="customer_id" javaType="int"></id> <result property="name" column="name" javaType="string"></result> <result property="address" column="address" javaType="string"></result> <collection property="orderList" ofType="order"> <id property="orderId" column="order_id" javaType="int"></id> <result property="orderState" column="order_state" javaType="string"></result> </collection> </resultMap> <select id="selectCustomerById2" resultMap="customerMap"> select * from t_customer left outer join t_order on t_customer.customer_id = t_order.customer_no where t_customer.customer_id = #{customerId} </select>
05-分步查询之一对一(掌握)
-
分步查询
- 将一次多表查询拆分成多次单表查询
-
需求
- 根据id查询用户信息,分步查询订单信息
-
一对一关联查询
select * from t_order inner join t_customer on t_order.customer_no = t_customer.customer_id where t_order.order_id = 1
-
一对一分步查询
-- ①查询订单信息(customer_no) select * from t_order where t_order.order_id = 1; -- ②根据customer_no查询用户信息 select * from t_customer where t_customer.customer_id = 1;
-
①查询订单信息(customer_no)
<resultMap id="orderMap" type="order"> <id property="orderId" column="order_id" javaType="int"></id> <result property="orderState" column="order_state" javaType="string"></result> <!--调用第二次单表查询--> <association property="customer" select="com.panghu.dao.CustomerDao.selectCustomerById" column="customer_no" ></association> </resultMap> <select id="selectOrderById" resultMap="orderMap" > select * from t_order where t_order.order_id = #{orderId} </select>
-
②根据customer_no查询用户信息
<select id="selectCustomerById" resultType="customer"> select * from t_customer where t_customer.customer_id = #{customerId} </select>
06-分步查询之一对多(掌握)
-
需求
- 根据name查询用户信息,分步查询订单信息
-
一对多关联查询
select * from t_customer left outer join t_order on t_customer.customer_id = t_order.customer_no where t_customer.name = '张三'
-
一对多分步查询
-- ①查询用户信息(customer_id) select * from t_customer where t_customer.name = '张三'; -- ②查询订单信息 select * from t_order where t_order.customer_no = 1;
-
①查询用户信息(customer_id)
<resultMap id="customerMap" type="customer"> <id property="customerId" column="customer_id" javaType="int"></id> <result property="name" column="name" javaType="string"></result> <result property="address" column="address" javaType="string"></result> <!--调用第二次单表查询--> <collection property="orderList" ofType="order" select="com.panghu.dao.OrderDao.selectOrderListByCustomerId" column="customer_id" > </collection> </resultMap> <select id="selectCustomerByName" resultMap="customerMap"> select * from t_customer where t_customer.name = #{name} </select>
-
②查询订单信息
<select id="selectOrderListByCustomerId" resultType="order"> select * from t_order where t_order.customer_no = #{customerId} </select>
07-mybatis延迟加载概述(掌握)
- 概述
- 也叫懒加载 , 对于实体类关联的属性需要使用时才查询。
- 好处
- 对于实体类关联的属性需要使用时才查询。
- 适用场景
- 一对一查询不需要做延迟加载,一对多需要做延迟加载
08-延迟加载之一对一(掌握)
-
开发步骤
- ①编写mybatis-config.xml
- lazyLoadingEnabled=true
- ②编写mapper映射文件(可选)
- 设置fetchType
- ③一对一分步查询
- 参考"05-分步查询之一对一"
- ①编写mybatis-config.xml
-
①编写mybatis-config.xml
<settings> <setting name="lazyLoadingEnabled" value="true"/> </settings>
-
②编写mapper映射文件(可选)
<resultMap id="orderMap" type="order"> <id property="orderId" column="order_id" javaType="int"></id> <result property="orderState" column="order_state" javaType="string"></result> <!--调用第二次单表查询--> <association property="customer" select="com.panghu.dao.CustomerDao.selectCustomerById" column="customer_no" fetchType="lazy" ></association> </resultMap> <select id="selectOrderById" resultMap="orderMap" > select * from t_order where t_order.order_id = #{orderId} </select>
09-延迟加载之一对多(掌握)
-
开发步骤
- ①编写mybatis-config.xml
- lazyLoadingEnabled=true
- ②编写mapper映射文件(可选)
- 设置fetchType
- ③一对多分步查询
- 参考"06-分步查询之一对多"
- ①编写mybatis-config.xml
-
①编写mybatis-config.xml
-
②编写mapper映射文件(可选)
<resultMap id="customerMap" type="customer"> <id property="customerId" column="customer_id" javaType="int"></id> <result property="name" column="name" javaType="string"></result> <result property="address" column="address" javaType="string"></result> <!--调用第二次单表查询--> <collection property="orderList" ofType="order" select="com.panghu.dao.OrderDao.selectOrderListByCustomerId" column="customer_id" fetchType="lazy" > </collection> </resultMap> <select id="selectCustomerByName" resultMap="customerMap"> select * from t_customer where t_customer.name = #{name} </select>
10-mybatis缓存概述(掌握)
- 概述
- 减少和数据库的交互次数,提高执行效率
- 分类
- 先查询二级缓存,因为二级缓存中可能会有其他程序已经查出来的数据,可以拿来直接使用。
- 如果二级缓存没有命中,再查询一级缓存 如果一级缓存也没有命中,则查询数据库
- SqlSession关闭之前,一级缓存中的数据会写入二级缓存
11-一级缓存的验证(掌握)
-
代码实现
@Test public void selectUser2ById() throws Exception { SqlSession sqlSession = sqlSessionFactory.openSession(); User2Dao user2Dao = sqlSession.getMapper(User2Dao.class); //第一次查询,将结果存储到一级缓存 User2 user2 = user2Dao.selectUser2ById(1); System.out.println("user2 = " + user2); //第二次查询,将结果从一级缓存中取出 user2 = user2Dao.selectUser2ById(1); System.out.println("user2 = " + user2); sqlSession.close(); }
12-一级缓存的清空(掌握)
-
概述
- 如果 sqlSession去执行commit操作(执行插入、更新、删除),清空 SqlSession中的一级缓存,这 样做的目的为了让缓存中存储的是最新的信息
-
代码实现
@Test public void selectUser2ById2() throws Exception { SqlSession sqlSession = sqlSessionFactory.openSession(); User2Dao user2Dao = sqlSession.getMapper(User2Dao.class); User2 user2 = user2Dao.selectUser2ById(1); System.out.println("user2 = " + user2); //执行DML操作,清空一级缓存 //user2Dao.addUser2(new User2(null,"d","d",28)); sqlSession.clearCache(); user2 = user2Dao.selectUser2ById(1); System.out.println("user2 = " + user2); sqlSession.commit(); sqlSession.close(); }
13-二级缓存的验证(掌握)
-
概述
- 二级缓存是 mapper 映射级别的缓存,多个 SqlSession 去操作同一个 Mapper 映射的 sql语句,多 个SqlSession 可以共用二级缓存,二级缓存是跨 SqlSession 的。
-
开发步骤
- ①编写mybatis-config.xml
- cacheEnabled=true
- ②编写mapper映射文件
- 添加标签
- 设置useCache属性(可选)
- ③实体类实现Serializable接口
- ④代码测试
- ①编写mybatis-config.xml
-
①编写mybatis-config.xml
<settings> <setting name="cacheEnabled" value="true"/> </settings>
-
②编写mapper映射文件
<mapper namespace="com.atguigu.dao.User2Dao"> <cache></cache> <select id="selectUser2ById" resultType="user2" useCache="true"> select * from t_user2 where user_id = #{userId} </select> <insert id="addUser2"> insert into t_user2 values(null,#{userName},#{userPwd},#{age}) </insert> </mapper>
-
③实体类实现Serializable接口
public class User2 implements Serializable { private Integer userId; private String userName; private String userPwd; private Integer age; }
-
④代码测试
@Test public void selectUser2ById3() throws Exception { SqlSession sqlSession = sqlSessionFactory.openSession(); User2Dao user2Dao = sqlSession.getMapper(User2Dao.class); User2 user2 = user2Dao.selectUser2ById(1); System.out.println("user2 = " + user2); sqlSession.close();//将一级缓存中的数据存放到二级缓存中 sqlSession = sqlSessionFactory.openSession(); user2Dao = sqlSession.getMapper(User2Dao.class); //使用二级缓存 0.5 user2 = user2Dao.selectUser2ById(1); System.out.println("user2 = " + user2); //0.666666 user2 = user2Dao.selectUser2ById(1); System.out.println("user2 = " + user2); sqlSession.close(); }
14-逆向工程的概念及机制(掌握)
- 正向工程
- 先创建Java实体类,由框架负责根据实体类生成数据库表。Hibernate是支持正向工程的。
- 逆向工程
- 先创建数据库表,由框架负责根据数据库表,反向生成如下资源:java实体类,mapper接口,mapper映射文件
15-逆向工程操作(掌握)
-
开发步骤
- ①新建项目 : mybatis-generator-project
- ②编写pom.xml
- ③编写generatorConfig.xml
- 逆向工程配置
- ④执行逆向工程
-
②编写pom.xml
<dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.7</version> </dependency> </dependencies> <!-- 控制Maven在构建过程中相关配置 --> <build> <!-- 构建过程中用到的插件 --> <plugins> <!-- 具体插件,逆向工程的操作是以构建过程中插件形式出现的 --> <plugin> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-maven-plugin</artifactId> <version>1.3.0</version> <!-- 插件的依赖 --> <dependencies> <!-- 逆向工程的核心依赖 --> <dependency> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-core</artifactId> <version>1.3.2</version> </dependency> <!-- 数据库连接池 --> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.2</version> </dependency> <!-- MySQL驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.8</version> </dependency> </dependencies> </plugin> </plugins> </build>
-
③编写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> <!-- targetRuntime: 执行生成的逆向工程的版本 MyBatis3Simple: 生成基本的CRUD(清新简洁版) MyBatis3: 生成带条件的CRUD(奢华尊享版) --> <context id="DB2Tables" targetRuntime="MyBatis3"> <!-- 数据库的连接信息 --> <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://127.0.0.1:3306/test" userId="root" password="root"> </jdbcConnection> <!-- javaBean的生成策略--> <javaModelGenerator targetPackage="com.panghu.mybatis.entity" targetProject=".\src\main\java"> <property name="enableSubPackages" value="true"/> <property name="trimStrings" value="true"/> </javaModelGenerator> <!-- SQL映射文件的生成策略 --> <sqlMapGenerator targetPackage="com.panghu.mybatis.mapper" targetProject=".\src\main\java"> <property name="enableSubPackages" value="true"/> </sqlMapGenerator> <!-- Mapper接口的生成策略 --> <javaClientGenerator type="XMLMAPPER" targetPackage="com.panghu.mybatis.mapper" targetProject=".\src\main\java"> <property name="enableSubPackages" value="true"/> </javaClientGenerator> <!-- 逆向分析的表 --> <!-- tableName设置为*号,可以对应所有表,此时不写domainObjectName --> <!-- domainObjectName属性指定生成出来的实体类的类名 --> <table tableName="tb_user" domainObjectName="User"/> </context> </generatorConfiguration>
-
④执行逆向工程
16-QBC查询(掌握)
-
概述
- QBC查询最大的特点就是将SQL语句中的WHERE子句进行了组件化的封装,让我们可以通过调用 Criteria对象的方法自由的拼装查询条件。
- QBC:Query By Criteria
-
代码实现
public class CustomerMapperTest { private SqlSessionFactory sqlSessionFactory; @Before public void init() throws IOException { sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml")); } //insert into t_customer (customer_id, name, address ) values (?, ?, ? ) @Test public void insert() { SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class); Customer record = new Customer(); record.setCustomerId(null); record.setName(null); record.setAddress("武汉市"); customerMapper.insert(record); sqlSession.commit(); sqlSession.close(); } @Test public void insertSelective() { SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class); Customer record = new Customer(); record.setCustomerId(null); record.setName(null); record.setAddress("武汉市"); customerMapper.insertSelective(record); sqlSession.commit(); sqlSession.close(); } @Test public void deleteByPrimaryKey() { SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class); customerMapper.deleteByPrimaryKey(6L); sqlSession.commit(); sqlSession.close(); } @Test public void deleteByExample() { SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class); CustomerExample example = new CustomerExample(); CustomerExample.Criteria criteria = example.createCriteria(); criteria.andAddressEqualTo("武汉市"); criteria.andNameEqualTo("王八"); customerMapper.deleteByExample(example); sqlSession.commit(); sqlSession.close(); } @Test public void updateByPrimaryKey() { SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class); Customer record = new Customer(); record.setCustomerId(1L); record.setName(null); record.setAddress("wuhan"); customerMapper.updateByPrimaryKey(record); sqlSession.commit(); sqlSession.close(); } @Test public void updateByPrimaryKeySelective() { SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class); Customer record = new Customer(); record.setCustomerId(1L); record.setName(null); record.setAddress("wuhan"); customerMapper.updateByPrimaryKeySelective(record); sqlSession.commit(); sqlSession.close(); } @Test public void updateByExample() { SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class); Customer record = new Customer(); record.setCustomerId(1L); record.setName(null); record.setAddress("孝感"); CustomerExample example = new CustomerExample(); CustomerExample.Criteria criteria = example.createCriteria(); criteria.andCustomerIdEqualTo(record.getCustomerId()); customerMapper.updateByExample(record , example); sqlSession.commit(); sqlSession.close(); } @Test public void updateByExampleSelective() { SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class); Customer record = new Customer(); record.setCustomerId(1L); record.setName(null); record.setAddress("孝感"); CustomerExample example = new CustomerExample(); CustomerExample.Criteria criteria = example.createCriteria(); criteria.andCustomerIdEqualTo(record.getCustomerId()); customerMapper.updateByExampleSelective(record , example); sqlSession.commit(); sqlSession.close(); } @Test public void selectByPrimaryKey() { SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class); Customer customer = customerMapper.selectByPrimaryKey(1L); System.out.println("customer = " + customer); sqlSession.close(); } @Test public void selectByExample() { SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class); CustomerExample example = new CustomerExample(); CustomerExample.Criteria criteria = example.createCriteria(); List<Long> ids = new ArrayList<>(); ids.add(1L); ids.add(2L); ids.add(3L); criteria.andCustomerIdIn(ids); CustomerExample.Criteria criteria2 = example.or(); criteria2.andNameEqualTo("王八"); customerMapper.selectByExample(example); sqlSession.close(); } @Test public void countByExample() { SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class); CustomerExample example = new CustomerExample(); CustomerExample.Criteria criteria = example.createCriteria(); List<Long> ids = new ArrayList<>(); ids.add(1L); ids.add(2L); ids.add(3L); criteria.andCustomerIdIn(ids); CustomerExample.Criteria criteria2 = example.or(); criteria2.andNameEqualTo("王八"); int count = customerMapper.countByExample(example); System.out.println("count = " + count); sqlSession.close(); } }
17-mybatis注解开发(了解)
- 概述
- 这几年来注解开发越来越流行,MyBatis也可以使用注解开发方式,这样就可以减少编写Mapper映 射文件了。
- 常用注解
- @Insert @Delete @Update @Select @Results @One @Many
18-mybatis注解操作CRUD(掌握)
-
代码实现
public interface UserDao { @Insert("insert into t_user values(null,#{userName},#{userPwd},#{money},#{address})") int addUser(User inputUser) throws Exception; @Delete("delete from t_user where userId = #{userId}") int deleteUserById(Integer userId) throws Exception; @Update("update t_user set userName = #{userName},userPwd = #{userPwd},money = #{money} ,address = #{address} where userId=#{userId}") int updateUser(User inputUser) throws Exception; @Select("select * from t_user where userId = #{userId}") User selectUserById(Integer userId) throws Exception; }
19-复杂关系映射注解说明(了解)
- @Results 注解
- 相当于resultMap标签
- @Result 注解
- 相当于id标签和result标签
- @One注解
- 相当于association标签,一对一查询
- @Many注解
- 相当于collection标签,一对多查询
20-注解实现一对一查询(了解)
-
代码实现
@Results({ @Result(id = true,property = "orderId",column = "order_id",javaType = Integer.class), @Result(id = false ,property = "orderState",column = "order_state",javaType = String.class), @Result( id = false, property = "customer", one = @One( //调用第二次单表查询 select = "com.atguigu.dao.CustomerDao.selectCustomerById" ), column = "customer_no" ) }) @Select("select * from t_order where order_id = #{orderId}") Order selectOrderById(Integer orderId) throws Exception;
@Select("select * from t_customer where customer_id = #{customerId}") Customer selectCustomerById(Integer customerId) throws Exception;
21-注解实现一对多查询(了解)
-
代码实现
//②①第一次单表查询 @Results({ @Result(id = true, property = "customerId", column = "customer_id", javaType = Long.class), @Result(id = false, property = "name", column = "name", javaType = String.class), @Result(id = false, property = "address", column = "address", javaType = String.class), //调用第二次单表查询 @Result( column = "customer_id", property = "orderList", many = @Many( select = "com.panghu.dao.OrderDao.selectOrderListByCustomerId", fetchType = FetchType.EAGER ) ) }) @Select("select * from t_customer where name = #{name}") Customer selectCustomerByName(String name) throws Exception;
@Select("select * from t_order where customer_no = #{customerId}") List<Order> selectOrderListByCustomerId(Integer customerId) throws Exception;