1、获取主键值
1.1使用useGeneratedKeys属性
1.2使用selectKey标签
修改UserMapper
2、分页
3、多表操作(重难点)
3.1一对一
需求:(电商)【一个用户对应一个购物车】,查询这个用户信息时同时关系他的购车信息。
1、新建Person与Car
CREATE TABLE `person` (
`id` int(11) NOT NULL,
`name` varchar(32) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`address` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `person` VALUES (1, '梦琪', '2019-05-08', '北京市');
INSERT INTO `person` VALUES (2, '忆柳', '2019-05-14', '天津市');
INSERT INTO `person` VALUES (3, '慕青', '2018-10-17', '上海市');
INSERT INTO `person` VALUES (4, '初夏', '2017-04-13', '重庆市');
INSERT INTO `person` VALUES (5, '新柔', '2018-12-29', '广州市');
DROP TABLE IF EXISTS `car`;
CREATE TABLE `car` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(20) DEFAULT NULL,
`pid` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `cp` (`pid`),
CONSTRAINT `cp` FOREIGN KEY (`pid`) REFERENCES `person` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `car` VALUES (1, '忆柳的购物车', 2);
INSERT INTO `car` VALUES (2, '梦琪的购物车', 1);
INSERT INTO `car` VALUES (3, '新柔的购物车', 5);
INSERT INTO `car` VALUES (4, '慕青的购物车', 3);
INSERT INTO `car` VALUES (5, '初夏的购物车', 4);
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
`oid` int(11) NOT NULL AUTO_INCREMENT,
`oname` varchar(255) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`oid`),
KEY `op` (`pid`),
CONSTRAINT `op` FOREIGN KEY (`pid`) REFERENCES `person` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `order` VALUES (1, '荣耀MagicBook 2019 14英寸轻薄窄边框', 2);
INSERT INTO `order` VALUES (2, '小米 (MI)Ruby 2019款 15.6英寸金属轻薄', 1);
INSERT INTO `order` VALUES (3, '戴尔灵越14 燃 14英寸英特尔酷睿i5轻薄窄边框', 3);
INSERT INTO `order` VALUES (4, '联想(Lenovo)小新14英寸 锐龙版R5', 4);
INSERT INTO `order` VALUES (5, '红辣椒7X 4+64GB 学生智能手机', 5);
INSERT INTO `order` VALUES (6, '荣耀10青春版 幻彩渐变', 1);
INSERT INTO `order` VALUES (7, 'OPPO K1 全面屏手机', 2);
INSERT INTO `order` VALUES (8, '卡梵蒂GAVADI 鳄鱼皮钱包', 5);
INSERT INTO `order` VALUES (9, '七匹狼钱包', 2);
INSERT INTO `order` VALUES (10, '金利来(Goldlion)男士钱包', 1);
#学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES (1, '梦琪');
INSERT INTO `student` VALUES (2, '初夏');
INSERT INTO `student` VALUES (3, '忆柳');
#课程表
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `course` VALUES (1, 'JAVA');
INSERT INTO `course` VALUES (2, 'HTML');
INSERT INTO `course` VALUES (3, 'DATABASE');
#中间表
DROP TABLE IF EXISTS `stu_cou`;
CREATE TABLE `stu_cou` (
`scid` int(11) NOT NULL AUTO_INCREMENT,
`cid` int(11) DEFAULT NULL,
`sid` int(11) DEFAULT NULL,
PRIMARY KEY (`scid`),
KEY `scs` (`sid`),
KEY `scc` (`cid`),
CONSTRAINT `scc` FOREIGN KEY (`cid`) REFERENCES `course` (`cid`),
CONSTRAINT `scs` FOREIGN KEY (`sid`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
INSERT INTO `stu_cou` VALUES (1, 1, 1);
INSERT INTO `stu_cou` VALUES (2, 1, 2);
INSERT INTO `stu_cou` VALUES (3, 1, 3);
INSERT INTO `stu_cou` VALUES (4, 2, 1);
INSERT INTO `stu_cou` VALUES (5, 2, 2);
INSERT INTO `stu_cou` VALUES (6, 2, 3);
INSERT INTO `stu_cou` VALUES (7, 3, 1);
3.1.1连表操作
1、确定sql
2、新建实体类并分析实体类之间的关系
【用户拥购物车】在pserson有一个car的属性
Person
public class Person {
private Integer id;
private String name;
private Date birthday;
private String address;
}
car
public class Car {
private Integer cid;
private String cname;
private Integer pid;
}
3、新建接口PersonMapper.java
public interface PersonMapper {
List<Person> listAll();
//模糊查询
List<Person> listByName(@Param("name") String name);
//一对一 联合查询
List<Person> list();
//一对一 分表查询
List<Person> list2();
//一对多 连接查询
Person getById(int id);
//一对多 分表查询
Person getById2(int id);
int update(Person person);
int add(Person person);
int delete(int id);
}
4、新建映射文件PersonMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace 命名空间:映射文件唯一标识符,相当于类名
作用:区别多个mapper StudentMapper DogMapper-->
<mapper namespace="com.wwz.mapper.PersonMapper">
<!-- <mapper namespace="UserMapper">-->
<!--开启二级缓存-->
<cache />
<!-- select标签是专门用来执行查询的sql
<select></select>称为:执行单元
id:是这个执行单元的唯一标识符 作用是区别多个执行单元
resultType:映射结果类型 就是说把sql查询到的记录晨映射成哪个类型(实体类)
完全限定名:包名+类名
标签体就写我们要执行sql
-->
<!--这个resultType是你sql语句执行结果的返回类型(要写全限定名)-->
<select id="listAll" resultType="com.wwz.pojo.Person">
select * from person
</select>
<!-- 一对一 联合查询 -->
<resultMap id="baseResultMap" type="com.wwz.pojo.Person">
<!--主键列用id标签表示-->
<id column="id" property="id" />
<result column="name" property="name" />
<result column="birthday" property="birthday" />
<result column="address" property="address" />
<!--association 专门用来做一对一的 property="car" 是person中的car属性
javaType="Car 是属性car类型-->
<association property="car" javaType="com.wwz.pojo.Car">
<id column="cid" property="cid" />
<result column="cname" property="cname" />
<result column="pid" property="pid" />
</association>
</resultMap>
<select id="list" resultMap="baseResultMap">
select * from person p inner join car c on p.id = c.pid
</select>
<!-- 一对一 分表查询 -->
<resultMap id="baseResultMap2" type="com.wwz.pojo.Person">
<!--主键列用id标签表示-->
<id column="id" property="id" />
<result column="name" property="name" />
<result column="birthday" property="birthday" />
<result column="address" property="address" />
<!-- select="com.wwz.mapper.CarMapper.get" 调用CarMapper下的get方法得到一个 car值,把car值赋给属性car
column="id" 把列id的值作为参数 -->
<association property="car" javaType="com.wwz.pojo.Car"
select="com.wwz.mapper.CarMapper.get" column="id" >
</association>
</resultMap>
<select id="list2" resultMap="baseResultMap2">
select * from person
</select>
<!-- 一对多 分表查询 -->
<resultMap id="baseResultMap3" type="com.wwz.pojo.Person">
<!--主键列用id标签表示-->
<id column="id" property="id" />
<result column="name" property="name" />
<result column="birthday" property="birthday" />
<result column="address" property="address" />
<!-- select="com.wwz.mapper.CarMapper.get" 调用CarMapper下的get方法得到一个 car值,把car值赋给属性car
column="id" 把列id的值作为参数 -->
<association property="car" javaType="com.wwz.pojo.Car"
select="com.wwz.mapper.CarMapper.get" column="id" >
</association>
</resultMap>
<select id="getById2" resultMap="baseResultMap3">
select * from `person` where id = #{id}
</select>
<!--多对多 连接查询-->
<!-- 模糊查询 -->
<select id="listByName" resultType="com.wwz.pojo.Person">
SELECT * FROM person
WHERE `NAME` LIKE '%${name}%';
</select>
<!-- useGeneratedKeys="true" 声明返回主键(开启), keyProperty="id"把返回主键的值封装到实体的id属性中(只适用于主键自增的数据库) -->
<insert id="add" parameterType="com.wwz.pojo.Person" useGeneratedKeys="true" keyProperty="id">
insert into person (id,name,birthday,address)
values (#{id},#{name},#{age},#{email})
</insert>
<update id="update" parameterType="com.wwz.pojo.Person">
update person set
id = #{id},
name = #{name },
age = #{age},
email = #{email}
where name = #{name }
</update>
<delete id="delete" parameterType="com.wwz.pojo.Person">
delete from person where id = #{id}
</delete>
</mapper>
5、测试
@Test
public void testUser3() throws IOException {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.list3();
//遍历集合
for (User user : users) {
System.out.println(user);
System.out.println(user.getCar());
}
}
6、总结
3.1.2单表操作(推荐)
连接表查(慢),所以我们使用单表查
3.2、一对多
需求:【一个用户对应多个订单】查询用户信息时关联订单信息
sql
3.2.1连表操作
1、确定sql
3.2.2单表操作
分别实现两张表的操作
3.3、同时实现一对一与一对多
3.4多对多
3.4.1、连表操作
1、确定sql
4、idea工具database使用
5、动态 SQL
5.3choose、when、otherwise
5.6、foreach(重点)