SSM = Spring+SpringMVC+MyBatis
SSH = Spring+Struts+Hibernate
1. 简介
1.1 JDBC开发存在哪些问题
- sql语句写到了daoimpl,dao中,不利于系统的维护(硬编码:sql写到了java代码中)。
- 解决:将sql语句放到一个单独的文件中xml
- PreparedStatement 的对象需要处理?,存在sql和java的硬编码问题。
- 解决:将sql和参数一起设置在xml中
- ResultSet 进行遍历,使用列名或者序号获取数据,不利于系统的维护。
- 解决:将查询到的结果直接映射为java对象
1.2 ORM 对象关系映射
1.2.1 ORM是什么
**ORM(Object-Relational Mapping,对象关系映射)**是一种编程技术,用于将对象模型和关系数据库之间进行映射。ORM工具可以自动将对象数据转换为关系型数据库的表结构,并提供简单的API用于对数据库进行操作,从而使得开发人员可以更方便地处理数据库相关的操作。
ORM的主要目的是解决面向对象编程语言(如Java、C#等)和关系数据库之间的差异问题。通过使用ORM工具,开发人员可以使用面向对象的方式来进行数据库操作,而不必直接编写SQL语句,降低了开发的难度和复杂度。
1.2.2 ORM功能和特性
- 对象关系映射:ORM工具能够自动将对象和关系数据库之间进行映射,提供了对象到表、属性到列的映射规则。
- 数据库操作接口:ORM工具提供了简化的API用于进行数据库的增删改查操作,开发人员可以使用面向对象的方式来操作数据库,而不必直接编写SQL语句。
- 数据库事务管理:ORM工具能够提供事务的管理支持,确保在一个事务中的操作要么全部成功,要么全部失败。
- 缓存支持:ORM工具通常具备缓存功能,可以提高数据的读取性能。
- 跨数据库平台支持:ORM工具通常支持多种数据库平台,可以轻松切换不同的数据库。
1.2.3 常见的ORM框架包括:
-
Hibernate:Hibernate是Java平台上最为广泛使用的ORM框架之一。它提供了强大的对象关系映射功能,可以将Java对象映射到关系数据库,并提供了丰富的查询和事务管理功能。
-
Entity Framework:Entity Framework是.NET平台上的ORM框架,用于将.NET对象映射到关系数据库。它支持多种数据库提供程序,如SQL Server、MySQL等,同时提供了LINQ查询语法和事务支持。
-
Django ORM:Django ORM是Python中流行的ORM框架,是Django Web框架的一部分。它提供了简单易用的API,用于将Python对象映射到关系数据库,并支持多种数据库后端。
-
SQLAlchemy:SQLAlchemy是Python中一个功能强大的ORM框架,支持多种数据库后端,并提供了灵活的查询和表达式语法,可用于构建高性能的数据库访问层。
-
Spring Data JPA:Spring Data JPA是基于Spring Framework的Java开发的ORM框架,它通过对JPA(Java Persistence API)的封装简化了数据访问层的开发,提供了方便的CRUD操作和查询功能。
-
MyBatis:它是Java平台上非常流行的持久层框架,用于将Java对象与关系数据库之间进行映射。MyBatis使用XML或注解配置来定义SQL映射关系,并提供了灵活的、可定制化的SQL查询和操作功能。
MyBatis相对于其他ORM框架的一个特点是它更加靠近SQL语句的编写,开发人员可以直接编写和优化SQL查询语句,从而更好地控制数据库访问的性能。同时,MyBatis也提供了一些基本的ORM功能,如对象到表之间的映射、查询参数绑定、事务管理等。
这些框架都提供了良好的抽象和封装,使得开发人员可以更专注于业务逻辑的实现,而不必过多关注数据库操作的细节。选择合适的ORM框架取决于开发语言和目标平台,以及个人或团队的偏好和项目需求。
1.3 什么是MyBatis
MyBatis 是一款优秀的半自动持久层框架,它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作**。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录**。
2. 底层
3. 入门
3.1 导jar包
下载jar包的地址
Maven Repository: org.mybatis » mybatis (mvnrepository.com)
3.2 XML配置
3.2.1 创建SqlMapConfig.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>
<!--***配置参数-->
<settings>
<!--打印sql日志-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--打开延时加载开关-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--将积极加载改为消极加载-->
<setting name="aggressiveLazyLoading" value="false"/>
<!--开启二级缓存-->
<setting name="cacheEnabled" value="true"/>
</settings>
<!--环境配置-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!--注册映射文件-->
<mappers>
<mapper resource="com/gk/entity/user.xml"/>
<mapper resource="com/gk/mapper/UserMapper.xml"/>
</mappers>
</configuration>
3.2.2 入门案例 CRUD User表
- User.java
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
- User.xml
${} 和 #{} 的区别:
这俩都是占位符
- #{} 是预编译处理,${}是字符串替换
- MyBatis在处理#{}时,会将sql中的#{}替换为?,调用 PreparedStatement 的 set 方法来赋值
- MyBatis在处理${}时,会将 ${}直接替换成变量的值,可能引起SQL注入问题
- 使用#{}可以防止SQL注入
<?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">
<!--
namespace 映射文件的命名空间,唯一
-->
<!--
public User findUserById(int id);
id : statement 方法名 要求唯一 与mapper中的方法名一致
parameterType : 方法的参数类型 (参数为引用类型时,写全类名。例:如果为Integer类型,写java.lang.Integer,如果为实体类写com.gk.entity.User)
resultType : 方法的返回值类型
-->
<select id="findById" parameterType="int" resultType="com.gk.entity.User">
select * from user where id = #{id}
</select>
<insert id="addUser" parameterType="com.gk.entity.User">
insert into user (username) values (#{username})
</insert>
<delete id="deleteById" parameterType="int">
delete from user where id = #{id}
</delete>
</mapper>
- UserTest.java
package com.gk.test;
import com.gk.entity.User;
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.Test;
import java.io.IOException;
import java.io.InputStream;
public class UserTest {
@Test
public void findById() throws Exception {
//MyBatis核心配置文件
String resource = "SqlMapConfig.xml";
//获取配置文件
InputStream inputStream = Resources.getResourceAsStream(resource);
//根据配置文件 创建会话工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//使用会话工厂来创建会话
SqlSession sqlSession = sqlSessionFactory.openSession();
//第一个参数:命名空间
//第二个参数:parameterType 实参
User user = sqlSession.selectOne("test.findById",1);
System.out.println(user);
sqlSession.close();
}
@Test
public void addUser() throws Exception{
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
sqlSession.insert("test.addUser",new User("高启强"));
//进行增删改要进行提交,否则修改不成功
sqlSession.commit();
System.out.println("ok");
sqlSession.close();
}
@Test
public void deleteById() throws Exception{
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
sqlSession.delete("test.deleteById",37);
sqlSession.commit();
System.out.println("ok");
sqlSession.close();
}
}
4. dao开发模式
- UserDao.java
package com.gk.dao;
import com.gk.entity.User;
public interface UserDao{
/**
* 根据id查询用户,查出的结果是一个,返回一个User
*/
public User findById(int id) throws Exception;
public void addUser(User user) throws Exception;
}
- UserDaoImpl.java 使用User.xml
package com.gk.dao.impl;
import com.gk.dao.UserDao;
import com.gk.entity.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
public class UserDaoImpl implements UserDao {
private SqlSessionFactory sqlSessionFactory;
public UserDaoImpl(SqlSessionFactory sqlSessionFactory) {
this.sqlSessionFactory = sqlSessionFactory;
}
@Override
public User findById(int id) throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
User user = sqlSession.selectOne("test.findById", id);
sqlSession.close();
return user;
}
@Override
public void addUser(User user) throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
sqlSession.insert("test.addUser",user);
sqlSession.commit();
sqlSession.close();
}
}
- UserDaoTest.java
package com.gk.test;
import com.gk.dao.impl.UserDaoImpl;
import com.gk.entity.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
public class UserDaoTest {
SqlSessionFactory factory = null;
UserDaoImpl userDaoImpl;
@Before
public void beforeMethod() throws Exception {
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
factory = new SqlSessionFactoryBuilder().build(inputStream);
userDaoImpl = new UserDaoImpl(factory);
}
@Test
public void findById() throws Exception {
User user = userDaoImpl.findById(1);
System.out.println(user);
}
@Test
public void add() throws Exception{
userDaoImpl.addUser(new User("高玉良"));
}
}
5. mapper代理模式的开发
规范:
-
在mapper.xml中namespace等于 mapper接口地址
<mapper namespace="com.liushao.mapper.UserMapper">
-
在mapper.xml中statementID要和 mapper接口中的方法名保持一致
<!--public List<User> findAll();--> <select id="findAll"
-
在mapper.xml中的resultType 要和 mapper.java中输出参数类型保持一致[返回值]
resultType="com.liushao.entity.User"
-
在mapper.xml中的parameterType 要和 mapper.java中输入参数类型保持一致[形参]
查询订单以及订单明细
查询主表:order
MyBatis
1.MyBatis简介
MyBatis 是一款优秀的持久层框架,它支持自定义 SQL、存储过程以及高级映射。
MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。
MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。
作用:
- 传统的jdbc开发存在以下问题:
- sql语句写在了java代码当中,不利于系统维护(硬编码)
- PreparedStatement 设置参数时,该对象需要手动设置参数,处理问号 ,存在sql和java的硬编码问题
- ResultSet ,进行遍历时使用列名或者序号获取数据 ,不利于系统的维护
- MyBatis可以解决以上问题:
- 将sql语句放到一个单独的xml中
- 将sql和参数设置在 xml中
- 将查询到的结果直接映射为 java对象
2.MyBatis底层原理
3.MyBatis入门案例
1.导入jar包
2.创建SqlMapConfig.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>
<!--环境配置-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis"/>
<property name="username" value="root"/>
<property name="password" value="weixu001014"/>
</dataSource>
</environment>
</environments>
<!--注册映射文件-->
<mappers>
<mapper resource="com/example/weixu/entity/User.xml"/>
<mapper resource="com/example/weixu/mapper/UserMapper.xml"/>
</mappers>
</configuration>
3.创建User.xml
User.xml:书写所要执行的sql语句
<?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="usertest">
<!--
sql标签的三个参数:
1.statement id:方法名 要求唯一
public User findUserById(int id);
2.parameterType: 参数
3.resultType:返回值类型
-->
<!--按照id查询-->
<select id="findUserById" parameterType="int" resultType="com.example.weixu.entity.User">
SELECT * FROM USER WHERE id=#{id}
</select>
<!--添加用户-->
<insert id="addUser" parameterType="com.example.weixu.entity.User">
INSERT INTO user(username)values(#{username})
</insert>
<!--删除用户-->
<delete id="deleteUser" parameterType="int">
DELETE from user where id = #{id}
</delete>
<!--查询所有用户-->
<select id="findAll" resultType="com.example.weixu.entity.User" >
SELECT * FROM `user`
</select>
</mapper>
User类
package com.example.weixu.entity;/**
* @User HASEE
* @Author WeiXu
* @Createtime 2023/10/12-12-8:33
* @PACKAGE_NAME com.example.weixu.pojo
*/
import java.io.Serializable;
import java.sql.Date;
/**
*@ClassName:User
*@author weixu
*@date 2023/10/12 8:33
*/
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
public User() {
}
public User(String username) {
this.username = username;
}
public User(Integer id, String username, Date birthday, String sex, String address) {
this.id = id;
this.username = username;
this.birthday = birthday;
this.sex = sex;
this.address = address;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
4.获取核心配置文件:SqlMapConfig.xml,创建会话工厂通过会话工厂创建会话,使用会话执行sql语句
/**
*@ClassName:UserTest
*@author weixu
*@date 2023/10/12 11:29
*/
public class UserTest {
@Test
public void findUserById() throws Exception {
//1.获取核心配置文件
String resource="SqlMapConfig.xml";
InputStream resourceAsStream = Resources.getResourceAsStream(resource);
//2.创建会话工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//3.创建会话
SqlSession sqlSession = sqlSessionFactory.openSession();
//4.执行sql语句
//第一个参数:命名空间.方法名
//第二个参数:实际参数
User user = sqlSession.selectOne("usertest.findUserById", 1);
System.out.println(user);
sqlSession.close();
}
@Test
public void addUser() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
int i = sqlSession.insert("usertest.addUser", new User("魏旭"));
if (i!=0)
{
System.out.println("插入成功!");
}
//增删改需要提交事务
sqlSession.commit();
sqlSession.close();
}
@Test
public void deleteUser() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
int i = sqlSession.delete("usertest.deleteUser", 37);
if (i!=0)
{
System.out.println("删除成功");
}
sqlSession.commit();
sqlSession.close();
}
/*查询所有用户*/
@Test
public void findAllUser() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
List<User> userList = sqlSession.selectList("usertest.findAll");
System.out.println(userList);
sqlSession.close();
}
}
4.MyBatis两种开发模式
4.1 dao开发模式
1.创建UserDao接口
public interface UserDao {
public List<User> findAll();
}
2.创建UserDaoImpl实现类
/**
*@ClassName:UserDaoImpl
*@author weixu
*@date 2023/10/12 18:23
*/
public class UserDaoImpl implements UserDao{
private SqlSessionFactory sqlSessionFactory;
public UserDaoImpl(SqlSessionFactory sqlSessionFactory) {
this.sqlSessionFactory = sqlSessionFactory;
}
@Override
public List<User> findAll() {
SqlSession sqlSession = sqlSessionFactory.openSession();
List<User> userList = sqlSession.selectList("usertest.findAll");
return userList;
}
}
3.Test测试:实现查询
/**
*@ClassName:UserDaoTest
*@author weixu
*@date 2023/10/12 18:36
*/
public class UserDaoTest {
private SqlSessionFactory sqlSessionFactory;
/*初始化SqlSession工厂类*/
@Before
public void SqlSessionFactory() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
}
@Test
public void findAll()
{
UserDao userDao = new UserDaoImpl(sqlSessionFactory);
for (User user : userDao.findAll()) {
System.out.println(user);
}
}
}
4.2 mapper代理模式的开发
相对于dao开发模式
规范:
1.在mapper.xml中namespace等于 mapper接口地址
<mapper namespace="com.liushao.mapper.UserMapper">
2.在mapper.xml中statementID要和 mapper接口中的方法名保持一致
<select id="findAll"
public List<User> findAll();
3.在mapper.xml中的resultType 要和 mapper.java中输出参数类型保持一致[返回值]
resultType="com.liushao.entity.User"
public List<User> findAll();
4.在mapper.xml中的parameterType 要和 mapper.java中输入参数类型保持一致[形参]
public List<User> findAll();
1.创建UserMapper.xml映射文件
- namespace命名空间:使用mapper代理映射的方式命名空间需要写UserMapper接口路径
- statement id:需要与UserMapper接口中的方法名相同
<?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代理映射的方式命名空间需要写UserMapper接口路径-->
<mapper namespace="com.example.weixu.mapper.UserMapper">
<!--statement id 需要与接口中方法名保持一致-->
<!--查询所有用户-->
<select id="findAll" resultType="com.example.weixu.entity.User">
SELECT * FROM user
</select>
<!--按照id查询-->
<select id="findById" parameterType="int" resultType="com.example.weixu.entity.User">
SELECT * FROM USER WHERE id=#{id}
</select>
<!--添加用户-->
<insert id="addUser" parameterType="com.example.weixu.entity.User">
INSERT INTO user(username)values(#{username})
</insert>
<!--删除用户-->
<delete id="deleteUser" parameterType="int">
DELETE from user where id = #{id}
</delete>
<!--运用查询帮助类查询用户列表-->
<!-- <select id="queryUser" parameterType="com.example.weixu.Vo.UserQueryVo" resultType="com.example.weixu.entity.UserCustom">-->
<!-- SELECT * from user where sex = #{userCustom.sex} and username like "%${userCustom.username}%"-->
<!-- </select>-->
<!--条件组合查询:动态sql-->
<select id="queryUser" parameterType="com.example.weixu.Vo.UserQueryVo" resultType="com.example.weixu.entity.UserCustom">
SELECT * FROM user
/*where标签相当于1=1*/
<where>
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
and sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and username like '%${userCustom.username}%'
</if>
</if>
</where>
</select>
<!--条件查询结果行数-->
<select id="queryCount" parameterType="com.example.weixu.Vo.UserQueryVo" resultType="int">
SELECT count(*) from user where sex = #{userCustom.sex} and username like "%${userCustom.username}%"
</select>
<!--根据id修改用户-->
<update id="updateUser" parameterType="com.example.weixu.Vo.UserUpdateVo">
UPDATE user set username = #{userCustom.username},sex = #{userCustom.sex} where id = #{userCustom.id}
</update>
<!--查询所有用户(列名与数据库不一致)-->
<select id="findAll2" resultMap="userResultMap">
SELECT id _id,username,birthday,sex,address FROM user
</select>
<resultMap id="userResultMap" type="com.example.weixu.entity.User">
<!--主键使用id标签-->
<id column="_id" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</resultMap>
</mapper>
2.创建UserMapper接口
/**
* @User HASEE
* @Author WeiXu
* @Createtime 2023/10/12-12-14:58
* @PACKAGE_NAME com.example.weixu.mapper
*/
public interface UserMapper {
/*查询所有用户*/
public List<User> findAll() throws Exception;
/*查询所有用户:列名是别名和实体类对象不对应的情况*/
public List<User> findAll2() throws Exception;
/*按照id查询用户*/
public User findById(int id) throws Exception;
/*删除用户*/
public int delById(Integer id);
/*添加用户*/
public int addUser(User user);
/*条件查询用户*/
public List<UserCustom> queryUser(UserQueryVo userQueryVo);
/*条件查询用户行数*/
public int queryCount(UserQueryVo userQueryVo);
/*修改用户*/
public int updateUser(UserUpdateVo userUpdateVo);
}
3.执行测试
UserMapperTest
- 通过sqlsession.getMapper(UserMapper.class)获取映射对象:mapper
- 通过mapper对象调用执行sql方法:List userList = mapper.findAll();
/**
*@ClassName:UserMapperTest
*@author weixu
*@date 2023/10/12 15:03
*/
public class UserMapperTest {
private SqlSessionFactory sqlSessionFactory;
/*初始化SqlSession工厂类*/
@Before
public void SqlSessionFactory() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
}
/*查询所有用户*/
@Test
public void findAll() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.findAll();
System.out.println(userList);
sqlSession.close();
}
/*查询所有用户*/
@Test
public void findAll2() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.findAll2();
System.out.println(userList);
sqlSession.close();
}
/*按照id查询用户*/
@Test
public void findById() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.findById(1);
System.out.println(user);
sqlSession.close();
}
/*根据id删除用户*/
@Test
public void delById()
{
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.delById(38);
if (i!=0)
{
System.out.println("删除成功!");
}
sqlSession.commit();
sqlSession.close();
}
/*添加用户*/
@Test
public void addUser()
{
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.addUser(new User("魏旭"));
if (i!=0)
{
System.out.println("添加成功!");
}
sqlSession.commit();
sqlSession.close();
}
/*条件查询用户*/
@Test
public void queryUser()
{
UserCustom userCustom = new UserCustom();
// userCustom.setSex("1");
// userCustom.setUsername("i");
UserQueryVo userQueryVo = new UserQueryVo();
userQueryVo.setUserCustom(userCustom);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<UserCustom> userList = mapper.queryUser(userQueryVo);
for (UserCustom custom:userList)
{
System.out.println(custom);
}
sqlSession.close();
}
/*条件查询行数*/
@Test
public void queryCount()
{
UserCustom userCustom = new UserCustom();
userCustom.setSex("1");
userCustom.setUsername("i");
UserQueryVo userQueryVo = new UserQueryVo();
userQueryVo.setUserCustom(userCustom);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int count = mapper.queryCount(userQueryVo);
System.out.println(count);
sqlSession.close();
}
/*根据id修改用户*/
@Test
public void updateUser()
{
UserCustom userCustom = new UserCustom();
userCustom.setId(38);
userCustom.setUsername("wei");
userCustom.setSex("2");
UserUpdateVo userUpdateVo = new UserUpdateVo(userCustom);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.updateUser(userUpdateVo);
if (i!=0)
{
System.out.println("更新成功");
}
sqlSession.commit();
sqlSession.close();
}
}
5.MyBatis输入映射及输出映射
5.1 输入映射
parameterType
相当于parameterStatement的 '?'占位符
<select parameterType="输入参数类型">
...........
...........
</select>
传入的参数用#{参数名}或者${参数名}来表示
<select id="queryUser" parameterType="com.example.weixu.Vo.UserQueryVo" resultType="com.example.weixu.entity.UserCustom">
<!--传入的参数用#{参数名}或者${参数名}来表示相当于jdbc中的 ?占位符-->
SELECT * FROM user
/*where标签相当于1=1*/
<where>
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
and sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and username like '%${userCustom.username}%'
</if>
</if>
</where>
</select>
parameterMap
<select parameterMap="该参数集合名字(自定义的)">
...........
...........
</select>
<parameterMap>
</parameterMap>
#{},${}
两者都是表示sql语句中传入的参数,不同的是,#{}是预编译处理相当于parameterStatement的 '?'占位符,而== 相当于字符串替换 = = , = = 把 {}相当于字符串替换==,==把 相当于字符串替换==,==把{}替换成变量的值==,因此==${}会产生sql注入问题==是不安全的,#{}更加安全。
5.2 输出映射
resultType和resultMap都是用来进行输出参数映射的,相对于resultMap,resultType更加简单便捷,因此推荐使用resultType,但是如果数据库列名与java对象的属性名不一致,或者sql语句中使用了别名的情况下必须使用resultMap来进行查询结果的映射。
resultType
<select resultType="输出参数类型">
...........
...........
</select>
resultMap
-
数据库主键列使用标签:
-
<!--column:表式数据库列名或者该列别名,如果名字不对将无法映射,查询结果映射到java对象值为null或0 property:java对象属性名--> <id column="id" property="id"/>
-
-
其他数据库列使用标签
-
<!--column:表式数据库列名或者该列别名,如果名字不对将无法映射,查询结果映射到java对象值为null或0 property:java对象属性名--> <result column="id" property="id"/>
-
<select id="findOrderUser" resultMap="orderanduser">
select * from orders,user where orders.user_id = user.id
</select>
<!--id:自定义输出参数集合的名字 type:返回参数的类型-->
<resultMap id="orderanduser" type="com.example.weixu.entity.Orders">
<id column="id1" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!--asssociation:针对于只是一个对象的情况下使用的标签
property:返回参数类型中该属性定义的属性名
javaType:该属性的类型-->
<association property="user" javaType="com.example.weixu.entity.User">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
resultMap是可继承的:
<resultMap id="orderAndDetResultMap" type="com.example.weixu.entity.Orders" extends="所要继承的resultMap的id(自定义的名字)">
<select id="findOrderAndDet" resultMap="orderAndDetResultMap">
SELECT orders.* ,
user.username,
user.address,
user.birthday,
user.sex,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.items_num
FROM
orders,orderdetail,user
where
orders.id = orderdetail.orders_id
and
user.id = orders.user_id
</select>
<resultMap id="orderAndDetResultMap" type="com.example.weixu.entity.Orders" extends="orderanduser">
<!--collection:针对于返回参数的属性有集合的情况下使用的标签:
property:返回参数类型中该属性定义的属性名
ofType:该属性的类型-->
<collection property="orderdetailList" ofType="com.example.weixu.entity.Orderdetail">
<id column="orderdetail_id" property="id"/>
<result column="orders_id" property="orderId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
</collection>
</resultMap>
asssociation标签
- 第一行为主键id
- 其他行为其他数据库列
<!--asssociation:针对于只是一个对象的情况下使用的标签
property:返回参数类型中该属性定义的属性名
javaType:该属性的类型-->
<association property="user" javaType="com.example.weixu.entity.User">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
collection标签
- 第一行为主键id
- 其他行为其他数据库列
<!--collection:针对于返回参数的属性是一个集合的情况下使用的标签:
property:返回参数类型中该属性定义的属性名
ofType:该属性的类型-->
<collection property="orderdetailList" ofType="com.example.weixu.entity.Orderdetail">
<id column="orderdetail_id" property="id"/>
<result column="orders_id" property="orderId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
</collection>
collection标签是可嵌套的:
<resultMap id="userOrderOrderdetailItems" type="com.example.weixu.entity.User">
<id property="id" column="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!--collection是可嵌套的-->
<collection property="ordersList" ofType="com.example.weixu.entity.Orders">
<id column="orders_id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<collection property="orderdetailList" ofType="com.example.weixu.entity.Orderdetail">
<id column="orderdetail_id" property="id"/>
<result column="orders_id" property="ordersId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<association property="items" javaType="com.example.weixu.entity.Items">
<id column="items_id" property="id"/>
<result column="name" property="name"/>
<result column="price" property="price"/>
<result column="detail" property="detail"/>
<result column="pic" property="pic"/>
<result column="createtime" property="createtime"/>
</association>
</collection>
</collection>
</resultMap>
6.MyBatis动态sql语句
**作用:**解决复杂的多条件查询,有的条件可能有值,有的条件可能没有值
6.1 where标签
相当于where 1=1 用来方便拼接sql语句
/*where标签相当于1=1*/
<where>
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
and sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and username like '%${userCustom.username}%'
</if>
</if>
</where>
6.2 if标签
通过条件判断,来动态修改sql语句
<if test="判断条件">所要拼接的sql语句</if>
实例
<!--条件组合查询:动态sql-->
<select id="queryUser" parameterType="com.example.weixu.Vo.UserQueryVo" resultType="com.example.weixu.entity.UserCustom">
SELECT * FROM user
/*where标签相当于1=1*/
<where>
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
and sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and username like '%${userCustom.username}%'
</if>
</if>
</where>
</select>
6.3 foreach标签
对于sql中重复的部分可以使用foreach标签来循环拼接sql语句(如查询多个id对应的用户信息)
<foreach collection="迭代的集合或数组的名称" item="每次迭代中表示当前元素的变量名称" open="在循环开始时要添加到生成的SQL语句的字符串" close="在循环结束时要添加到生成的SQL语句的字符串" separator="每个元素之间的分隔符,将其添加到生成的SQL语句中">
所要拼接的sql语句
</foreach>
实例:
<!--查询所有用户:多个id-->
<select id="findByIds" parameterType="com.example.weixu.entity.UserCustom" resultType="com.example.weixu.entity.UserCustom">
SELECT * FROM user
<!-- <where>-->
<!-- <foreach collection="ids" item="id" open="and (" close=")" separator="or">-->
<!-- id = #{id}-->
<!-- </foreach>-->
<!-- </where>-->
<!--另一种写法-->
<where>
<foreach collection="ids" item="id" open="and id in(" close=")" separator=",">
#{id}
</foreach>
</where>
</select>
6.4 sql片段
SQL片段是SQL查询中可重复使用的一部分,它可以在查询中多次引用,有点类似于SQL的宏或函数
定义sql片段:
<!--使用sql标签定义sql片段-->
<sql id="自定义的该sql片段的名字">
sql语句。。。。
</sql>
引用sql片段
使用include标签来引用
<select id="queryUser" parameterType="com.example.weixu.Vo.UserQueryVo" resultType="com.example.weixu.entity.UserCustom">
SELECT * FROM user
<include refid="所要引用的sql片段的名称"/>
</select>
实例:
<!--条件组合查询:动态sql-->
<select id="queryUser" parameterType="com.example.weixu.Vo.UserQueryVo" resultType="com.example.weixu.entity.UserCustom">
SELECT * FROM user
/*where标签相当于1=1*/
<!-- <where>-->
<!-- <if test="userCustom!=null">-->
<!-- <if test="userCustom.sex!=null and userCustom.sex!=''">-->
<!-- and sex=#{userCustom.sex}-->
<!-- </if>-->
<!-- <if test="userCustom.username!=null and userCustom.username!=''">-->
<!-- and username like '%${userCustom.username}%'-->
<!-- </if>-->
<!-- </if>-->
<!-- </where>-->
<include refid="querySql"/>
</select>
<!--sql片段-->
<sql id="querySql" >
<!--where标签相当于1=1-->
<where>
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
and sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and username like '%${userCustom.username}%'
</if>
</if>
</where>
</sql>
7.多表查询
7.1 一对一
一对一查询订单,关联查询创建订单的用户信息:
Orders类
/**
*@ClassName:orders
*@author weixu
*@date 2023/10/12 8:31
*/
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
private User user;
private List<Orderdetail> orderdetailList= new ArrayList<>();
public List<Orderdetail> getOrderdetailList() {
return orderdetailList;
}
public void setOrderdetailList(List<Orderdetail> orderdetailList) {
this.orderdetailList = orderdetailList;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Orders() {
}
public Orders(Integer id, Integer userId, String number, Date createtime, String note) {
this.id = id;
this.userId = userId;
this.number = number;
this.createtime = createtime;
this.note = note;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", userId=" + userId +
", number='" + number + '\'' +
", createtime=" + createtime +
", note='" + note + '\'' +
'}';
}
}
User类
package com.example.weixu.entity;/**
* @User HASEE
* @Author WeiXu
* @Createtime 2023/10/12-12-8:33
* @PACKAGE_NAME com.example.weixu.pojo
*/
import java.io.Serializable;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
/**
*@ClassName:User
*@author weixu
*@date 2023/10/12 8:33
*/
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Orders> ordersList = new ArrayList<>();
public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
public User() {
}
public User(String username) {
this.username = username;
}
public User(Integer id, String username, Date birthday, String sex, String address) {
this.id = id;
this.username = username;
this.birthday = birthday;
this.sex = sex;
this.address = address;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
UserMapper接口
/**
* @User HASEE
* @Author WeiXu
* @Createtime 2023/10/12-12-14:58
* @PACKAGE_NAME com.example.weixu.mapper
*/
public interface UserMapper {
/*查询所有用户*/
public List<User> findAll() throws Exception;
/*查询所有用户:列名是别名和实体类对象不对应的情况*/
public List<User> findAll2() throws Exception;
/*按照id查询用户*/
public User findById(int id) throws Exception;
/*删除用户*/
public int delById(Integer id);
/*添加用户*/
public int addUser(User user);
/*条件查询用户*/
public List<UserCustom> queryUser(UserQueryVo userQueryVo);
/*条件查询用户行数*/
public int queryCount(UserQueryVo userQueryVo);
/*修改用户*/
public int updateUser(UserUpdateVo userUpdateVo);
/*根据多个id查询所有用户*/
public List<User> findByIds(UserCustom userCustom);
/*一对一查询订单,关联查询创建订单的用户信息*/
public List<Orders> findOrderUser();
/*一对多 查询订单以及订单明细*/
public List<Orders> findOrderAndDet();
/*多对多 */
public List<User> findUserAndOrder();
}
UserMapper.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代理映射的方式命名空间需要写UserMapper接口路径-->
<mapper namespace="com.example.weixu.mapper.UserMapper">
<!--一对一:查询订单,关联查询创建订单的用户信息-->
<select id="findOrderUser" resultMap="orderanduser">
select * from orders,user where orders.user_id = user.id
</select>
<!--id:自定义输出参数集合的名字 type:返回参数的类型-->
<resultMap id="orderanduser" type="com.example.weixu.entity.Orders">
<!--column:表式数据库列名或者该列别名,如果名字不对将无法映射,查询结果映射到java对象值为null或0
property:java对象属性名-->
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!--asssociation:针对于只是一个对象的情况下使用的标签
property:返回参数类型中该属性定义的属性名
javaType:该属性的类型-->
<association property="user" javaType="com.example.weixu.entity.User">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
</mapper>
UserMapperTest
/**
*@ClassName:UserMapperTest
*@author weixu
*@date 2023/10/12 15:03
*/
public class UserMapperTest {
private SqlSessionFactory sqlSessionFactory;
/*初始化SqlSession工厂类*/
@Before
public void SqlSessionFactory() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
}
/*一对一查询:查询订单,关联查询创建订单的用户信息*/
@Test
public void findOrderUser()
{
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Orders> orderUser = mapper.findOrderUser();
for (Orders orders:orderUser)
{
System.out.println(orders);
}
}
}
7.2 一对多
一对多 查询订单以及订单明细:
一个订单有多条订单明细,所以在Orders类中定义List集合的成员变量来接收返回参数
Orders类
/**
*@ClassName:orders
*@author weixu
*@date 2023/10/12 8:31
*/
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
private User user;
private List<Orderdetail> orderdetailList= new ArrayList<>();
public List<Orderdetail> getOrderdetailList() {
return orderdetailList;
}
public void setOrderdetailList(List<Orderdetail> orderdetailList) {
this.orderdetailList = orderdetailList;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Orders() {
}
public Orders(Integer id, Integer userId, String number, Date createtime, String note) {
this.id = id;
this.userId = userId;
this.number = number;
this.createtime = createtime;
this.note = note;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", userId=" + userId +
", number='" + number + '\'' +
", createtime=" + createtime +
", note='" + note + '\'' +
'}';
}
}
Orderdetail类
/**
*@ClassName:orders
*@author weixu
*@date 2023/10/12 8:31
*/
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
private User user;
private List<Orderdetail> orderdetailList= new ArrayList<>();
public List<Orderdetail> getOrderdetailList() {
return orderdetailList;
}
public void setOrderdetailList(List<Orderdetail> orderdetailList) {
this.orderdetailList = orderdetailList;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Orders() {
}
public Orders(Integer id, Integer userId, String number, Date createtime, String note) {
this.id = id;
this.userId = userId;
this.number = number;
this.createtime = createtime;
this.note = note;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", userId=" + userId +
", number='" + number + '\'' +
", createtime=" + createtime +
", note='" + note + '\'' +
'}';
}
}
UserMapper接口
/**
* @User HASEE
* @Author WeiXu
* @Createtime 2023/10/12-12-14:58
* @PACKAGE_NAME com.example.weixu.mapper
*/
public interface UserMapper {
/*一对多 查询订单以及订单明细*/
public List<Orders> findOrderAndDet();
}
UserMapper.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代理映射的方式命名空间需要写UserMapper接口路径-->
<mapper namespace="com.example.weixu.mapper.UserMapper">
<!--一对一:查询订单,关联查询创建订单的用户信息-->
<select id="findOrderUser" resultMap="orderanduser">
select * from orders,user where orders.user_id = user.id
</select>
<!--id:自定义输出参数集合的名字 type:返回参数的类型-->
<resultMap id="orderanduser" type="com.example.weixu.entity.Orders">
<!--column:表式数据库列名或者该列别名,如果名字不对将无法映射,查询结果映射到java对象值为null或0
property:java对象属性名-->
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!--asssociation:针对于只是一个对象的情况下使用的标签
property:返回参数类型中该属性定义的属性名
javaType:该属性的类型-->
<association property="user" javaType="com.example.weixu.entity.User">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
<!--一对多 查询订单以及订单明细-->
<select id="findOrderAndDet" resultMap="orderAndDetResultMap">
SELECT orders.* ,
user.username,
user.address,
user.birthday,
user.sex,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.items_num
FROM
orders,orderdetail,user
where
orders.id = orderdetail.orders_id
and
user.id = orders.user_id
</select>
<resultMap id="orderAndDetResultMap" type="com.example.weixu.entity.Orders" extends="orderanduser">
<!--collection:针对于返回参数的属性有集合的情况下使用的标签:
property:返回参数类型中该属性定义的属性名
ofType:该属性的类型-->
<collection property="orderdetailList" ofType="com.example.weixu.entity.Orderdetail">
<id column="orderdetail_id" property="id"/>
<result column="orders_id" property="orderId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
</collection>
</resultMap>
</mapper>
UserMapperTest
/**
*@ClassName:UserMapperTest
*@author weixu
*@date 2023/10/12 15:03
*/
public class UserMapperTest {
private SqlSessionFactory sqlSessionFactory;
/*初始化SqlSession工厂类*/
@Before
public void SqlSessionFactory() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
}
/*一对多 查询订单以及订单明细*/
@Test
public void findOrderAndDet()
{
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Orders> orderAndDet = mapper.findOrderAndDet();
for (Orders orders:orderAndDet)
{
System.out.println(orders);
}
}
}
7.3 多对多
多对多,查询用户及用户购买的商品,涉及到四张表的关联查询:User、Orders、Orderdetail、Items
主表为User表,关联表为:Orders、Orderdetail、Items,因此返回结果类型为User类
- 由于一个用户有多个订单,所以在User类中定义List集合的成员变量来接收返回参数
- 一个订单有多条订单明细,所以在Orders类中定义List集合的成员变量来接收返回参数
- 一个订单明细对应一个商品条目,所以在Orderdetail类中定义Items类型的成员变量来接收返回参数
User类:
/**
*@ClassName:User
*@author weixu
*@date 2023/10/12 8:33
*/
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Orders> ordersList = new ArrayList<>();
public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
public User() {
}
public User(String username) {
this.username = username;
}
public User(Integer id, String username, Date birthday, String sex, String address) {
this.id = id;
this.username = username;
this.birthday = birthday;
this.sex = sex;
this.address = address;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
Orders类:
/**
*@ClassName:orders
*@author weixu
*@date 2023/10/12 8:31
*/
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
private User user;
private List<Orderdetail> orderdetailList= new ArrayList<>();
public List<Orderdetail> getOrderdetailList() {
return orderdetailList;
}
public void setOrderdetailList(List<Orderdetail> orderdetailList) {
this.orderdetailList = orderdetailList;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Orders() {
}
public Orders(Integer id, Integer userId, String number, Date createtime, String note) {
this.id = id;
this.userId = userId;
this.number = number;
this.createtime = createtime;
this.note = note;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", userId=" + userId +
", number='" + number + '\'' +
", createtime=" + createtime +
", note='" + note + '\'' +
'}';
}
}
Orderdetail类
/**
*@ClassName:orderdetail
*@author weixu
*@date 2023/10/12 8:29
*/
public class Orderdetail {
private Integer id;
private Integer ordersId;
private Integer itemsId;
private Integer itemsNum;
private Items items;
public Items getItems() {
return items;
}
public void setItems(Items items) {
this.items = items;
}
public Orderdetail() {
}
public Orderdetail(Integer id, Integer ordersId, Integer itemsId, Integer itemsNum) {
this.id = id;
this.ordersId = ordersId;
this.itemsId = itemsId;
this.itemsNum = itemsNum;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getOrdersId() {
return ordersId;
}
public void setOrdersId(Integer ordersId) {
this.ordersId = ordersId;
}
public Integer getItemsId() {
return itemsId;
}
public void setItemsId(Integer itemsId) {
this.itemsId = itemsId;
}
public Integer getItemsNum() {
return itemsNum;
}
public void setItemsNum(Integer itemsNum) {
this.itemsNum = itemsNum;
}
@Override
public String toString() {
return "orderdetail{" +
"id=" + id +
", ordersId=" + ordersId +
", itemsId=" + itemsId +
", itemsNum=" + itemsNum +
'}';
}
}
Items类
/**
*@ClassName:items
*@author weixu
*@date 2023/10/12 8:24
*/
public class Items {
private Integer id;
private String name;
private Float price;
private String detail;
private String pic;
private Date createtime;
public Items() {
}
public Items(Integer id, String name, Float price, String detail, String pic, Date createtime) {
this.id = id;
this.name = name;
this.price = price;
this.detail = detail;
this.pic = pic;
this.createtime = createtime;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Float getPrice() {
return price;
}
public void setPrice(Float price) {
this.price = price;
}
public String getDetail() {
return detail;
}
public void setDetail(String detail) {
this.detail = detail;
}
public String getPic() {
return pic;
}
public void setPic(String pic) {
this.pic = pic;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
@Override
public String toString() {
return "items{" +
"id=" + id +
", name='" + name + '\'' +
", price=" + price +
", detail='" + detail + '\'' +
", pic='" + pic + '\'' +
", createtime=" + createtime +
'}';
}
}
UserMapper接口
/**
* @User HASEE
* @Author WeiXu
* @Createtime 2023/10/12-12-14:58
* @PACKAGE_NAME com.example.weixu.mapper
*/
public interface UserMapper {
/*多对多 */
public List<User> findUserAndOrder();
}
UserMapper.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代理映射的方式命名空间需要写UserMapper接口路径-->
<mapper namespace="com.example.weixu.mapper.UserMapper">
<!--多对多:查询用户及用户购买的商品-->
<select id="findUserAndOrder" resultMap="userOrderOrderdetailItems">
select
user.*,
orders.id orders_id,
orders.number,
orders.createtime,
orders.note,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
items.id items_id,
items.name,
items.createtime,
items.detail,
items.pic,
items.price
from user,orders,orderdetail,items
where
user.id=orders.user_id
and
orders.id=orderdetail.orders_id
and
orderdetail.items_id=items.id
</select>
<resultMap id="userOrderOrderdetailItems" type="com.example.weixu.entity.User">
<id property="id" column="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!--collection是可嵌套的-->
<collection property="ordersList" ofType="com.example.weixu.entity.Orders">
<id column="orders_id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<collection property="orderdetailList" ofType="com.example.weixu.entity.Orderdetail">
<id column="orderdetail_id" property="id"/>
<result column="orders_id" property="ordersId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<association property="items" javaType="com.example.weixu.entity.Items">
<id column="items_id" property="id"/>
<result column="name" property="name"/>
<result column="price" property="price"/>
<result column="detail" property="detail"/>
<result column="pic" property="pic"/>
<result column="createtime" property="createtime"/>
</association>
</collection>
</collection>
</resultMap>
</mapper>
MyBatis
1.什么是MyBatis?
2.MyBatis的优点
3.MyBatis框架的缺点
4.MyBatis适用的场合
5.MyBatis 与 Hibernate 有哪些不同?
6.#{}和${}的区别是什么?
在 MyBatis 中,#{} 和 ${} 是用于替换 SQL 语句中的参数的两种不同的方式,它们有以下区别:
-
#{}是预编译处理,${}是字符串拼接:#{} 按照预编译处理的规则对参数进行占位符替换,将参数值放入 PreparedStatement 中,这种方式可以防止 SQL 注入攻击。而 ${} 则是直接将参数值替换到 SQL 语句中,这种方式存在 SQL 注入的风险。
-
#{}会将输入参数转义,${}不会转义:#{} 会对输入的参数进行转义以保证安全性,特殊字符会被转义成字符实体或特定的字符串表示形式,而 ${} 不会做此操作,如果参数中包含特殊字符可能会导致 SQL 注入等安全问题。
-
#{}更加安全,${}更加灵活:使用 #{} 可以有效的避免 SQL 注入等安全问题,但是它只能传递参数值,无法传递 SQL 关键字、表名等信息;而 ${} 可以传递 SQL 关键字、表名等信息,但是需要开发人员自行保证传入参数的安全性。
7.当实体类中的属性名和表中的字段名不一样 ,怎么办 ?
8.模糊查询 like 语句该怎么写?
9.MyBatis中涉及到的设计模式
MyBatis 是一个流行的持久层框架,它在设计和实现中运用了多种设计模式来提高代码的可维护性、灵活性和可扩展性。以下是 MyBatis 中涉及到的一些常见设计模式:
-
Builder 模式:在 MyBatis 中,SqlSessionFactoryBuilder 用来构建 SqlSessionFactory 对象,SqlSessionFactoryBuilder 使用了 Builder 模式来创建复杂对象。
-
工厂模式:MyBatis 使用工厂模式来创建 SqlSession 和 SqlSessionFactory 对象,通过 SqlSessionFactory 工厂来获取 SqlSession 实例。
-
装饰器模式:在 MyBatis 中,Executor 类是一个关键组件,它使用了装饰器模式来动态地添加额外的功能,比如缓存、日志记录等。
-
模板模式:MyBatis 中的 SqlSessionTemplate 就采用了模板模式,定义了一些操作数据库的基本方法,具体的实现交给子类去实现。
-
代理模式:在 MyBatis 中,Mapper 接口通过动态代理的方式生成实现类,实现了接口和 XML 配置文件的映射,简化了开发过程。
-
观察者模式:MyBatis 中的事件机制使用了观察者模式,允许开发人员注册监听器以便在特定事件发生时执行自定义逻辑。
这些设计模式使得 MyBatis 在实现数据访问功能时更加灵活和可扩展,同时也降低了模块之间的耦合度,使得代码更易于维护和扩展。