【SSM框架】MyBatis笔记 --- 动态sql讲义+实战;map在动态sql中的使用;列名与类中成员变量名不同的两种解决方案
讲义:
- 动态sql可以定义代码片断,可以进行逻辑判断,可以进行循环处理(批量处理),使条件判断更为简单。
一、动态sql核心标签:
1、<sql>:当多种类型的查询语句的查询字段或者查询条件相同时,可以将其定义为常量,方便调用。
2、<include>:用来引用<sql>定义的代码片断。
<!--定义代码片断-->
<sql id="allColumns">
id,username,birthday,sex,address
</sql>
<!--引用定义好的代码片断-->
<select id="getAll" resultType="users" >
select <include refid="allColumns"></include>
from users
</select>
3、<if>:进行条件判断。
test 属性:if 执行条件(条件判断的取值可以是实体类的成员变量,可以是map的key,可以是@Param注解的名称)。
4、<where>:
特性:标签可以自动的将第一个条件前面的逻辑运算符 (or ,and) 去掉,比如 id 查询条件前面是有“and”关键字的,但是在打印出来的 SQL 中却没有。
<select id="getByCondition" parameterType="users" resultType="users">
select <include refid="allColumns"></include>
from users
<where>
<if test="userName != null and userName != ''">
and username like concat('%',#{userName},'%')
</if>
<if test="birthday != null">
and birthday = #{birthday}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="address != null and address != ''">
and address like concat('%',#{address},'%')
</if>
</where>
</select>
5、<set>:使用见下面的栗子。切记,至少更新一列(负责抛出异常)。
需求:使用 if+set 标签进行update操作时,哪个字段中有值才去更新,如果某项为 null 则不进行更新,而是保持数据库原值。
栗子:
<update id="updateBySet" parameterType="users">
update users
<set>
<if test="userName != null and userName != ''">
username = #{userName},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
<if test="sex != null and sex != ''">
sex = #{sex},
</if>
<if test="address != null and address != ''">
address =#{address} ,
</if>
</set>
where id = #{id}
</update>
6、<foreach>:用来进行循环遍历,完成循环条件查询,批量删除,批量增加,批量更新。
1)collection 属性:用来指定入参的类型,如果是List集合,则为list,如果是Map集合,则为map,如果是数组,则为array。
2)item 属性 :循环体中的具体对象。支持属性的点路径访问,如 item.age,item.info.details;在list和数组中是其中的对象,在map中是value。
3)index 属性 :在list和数组中,index是元素的序号,在map中,index是元素的key,该参数可不写。
4)separator 属性:多个值或对象或语句之间的分隔符。
5)open 属性 :表示该语句以什么开始。
6)close 属性 :表示该语句以什么结束。
注意:要使用批量更新,必须在jdbc.properties属性文件中的url中添加&allowMultiQueries=true,才允许多行操作。
二、通过指定下标来进行传参:
可以不使用对象的属性名进行参数值绑定,使用下标值。 mybatis-3.3 版本和之前的版本使用#{0},#{1}方式, 从 mybatis3.4 开始使用#{arg0},#{arg1}的方式。
三、map在动态sql中的使用:
- 如果入参超过一个以上,使用map封装查询条件,更有语义,查询条件更明确。
1、入参是map:
因为当传递的数据有多个,不适合使用指定下标或指定名称的方式来进行传参,又加上参数不一定与对象的成员变量一致,考虑使用map集合来进行传递,map使用的是键值对的方式.当在sql语句中使用的时候#{键名},${键名},{ }的是键的名称。
2、返回值是map:
返回值是map的适用场景,如果的数据不能使用对象来进行封装,可能查询的数据来自多张表中的某些列,这种情况下,使用map,但是map的返回方式破坏了对象的封装,返回来的数据是一个一个单独的数据, 彼此之间不相关,map使用表中的列名或别名作为键名(key)进行返回数据。
四、列名与类中成员变量名称不一致:
解决方案一:
使用列的别名,别名与类中的成员变量名一样,即可完成注入。
解决方案二:
使用<resultMap>标签进行映射。
property 属性:为成员变量名
column 属性:为列的别名
一堆栗子:
一、module 目录结构:
二、pom.xml:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.burning</groupId>
<artifactId>mybatis_003_dynamicsql</artifactId>
<version>1.0</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!--添加mybatis依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
<!--添加mysql依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
<scope>compile</scope>
</dependency>
</dependencies>
<build>
<!--指定资源文件位置-->
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
</resources>
</build>
</project>
三、jdbc.properties:
jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm?useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=888
四、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>
<!--读取jdbc.properties属性-->
<properties resource="jdbc.properties"></properties>
<!--设置日志输出-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!--注册实体类别名-->
<typeAliases>
<package name="org.burning.entity"/>
</typeAliases>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--注册mapper.xml文件-->
<mappers>
<!--优化mapper.xml文件注册-->
<!--绝对路径注册-->
<!--<mapper url="/////"></mapper>-->
<!--非动态代理方式下的注册-->
<!--<mapper resource="StudentMapper.xml"></mapper>-->
<!--单个注册-->
<!--<mapper class="org.burning.mapper.UsersMapper"></mapper>-->
<!--批量注册-->
<package name="org.burning.mapper"/>
</mappers>
</configuration>
五、建表语句:
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin
CREATE TABLE `books` (
`book_id` int NOT NULL AUTO_INCREMENT,
`book_name` varchar(45) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`book_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin
六、User.java:
package org.burning.entity;
import java.util.Date;
public class User {
private Integer id;
private String userName;
private Date birthday;
private String sex;
private String address;
public User() {
}
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 User(String userName, Date birthday, String sex, String address) {
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 + '\'' +
'}';
}
}
七、Book.java:
package org.burning.entity;
public class Book {
private Integer id;
private String name;
public Book() {
}
public Book(Integer id, String name) {
this.id = id;
this.name = name;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
@Override
public String toString() {
return "Book{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
八、UsersMapper.java:
package org.burning.mapper;
import org.apache.ibatis.annotations.Param;
import org.burning.entity.User;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* 数据访问层的接口,规定的数据库中可进行的各种操作
*/
public interface UsersMapper {
//查询用户全部信息
List<User> getAll();
//按指定的条件进行多条件查询
List<User> selectByCondition(User user);
//有选择的更新
int updateBySet(User user);
//查询多个指定id的用户信息
List<User> selectByIds(Integer[] arr);
//批量删除
int deleteBatch(Integer[] arr);
//批量增加
int insertBatch(List<User> users);
//查询生日在两个日期间的所有学生信息
List<User> selectByTwoBirthday(Date begin,Date end);
//入参是map
List<User> selectByMap(Map map);
//返回值是一行的map
Map returnMap(Integer id);
//返回多行的map
List<Map> returnMaps();
}
九、UsersMapper.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="org.burning.mapper.UsersMapper">
<!--定义代码片段-->
<sql id="allcolumns">
id,username,birthday,sex,address
</sql>
<!--查询users中所有的学生信息-->
<select id="getAll" resultType="user">
select <include refid="allcolumns"></include>
from users
</select>
<!--动态sql实现:
根据多个字段进行查询操作(可以通过判断user对象的实例变量是否“有意义”而进行sql的拼接)
【无意义值指的是:比如userName是字符串类型,而它为null或者为空字符串,那它就是无意义的】
-->
<select id="selectByCondition" parameterType="User" resultType="User">
select <include refid="allcolumns"></include>
from users
<where>
<if test="userName != null and userName != ''">
and username like concat('%',#{userName},'%')
</if>
<if test="birthday != null">
and birthday = #{birthday}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="address != null and address != ''">
and address like concat('%',#{address},'%')
</if>
</where>
</select>
<!--通过动态sql实现:
根据入参user对象的实例变量是否有“意义”,而进行相应的更新处理
-->
<update id="updateBySet" parameterType="user">
update users
<set>
<if test="userName != null and userName != ''">
userName = #{userName},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
<if test="sex != null and sex != ''">
sex = #{sex},
</if>
<if test="address != null and address != ''">
address = #{address},
</if>
</set>
where id=#{id}
</update>
<!--通过动态sql实现:(当入参是一个以上的时候,不需要写parameterType)
根据入参的id数组,来进行相应id多条信息查询
-->
<select id="selectByIds" resultType="user">
select <include refid="allcolumns"></include>
from users
where id in
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
<!--通过动态sql实现:
批量删除
-->
<delete id="deleteBatch">
delete from users
where id in
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
<!--通过动态sql实现:
批量增加
-->
<insert id="insertBatch">
insert into users (username,birthday,sex,address)
values
<foreach collection="list" item="u" separator=",">
(#{u.userName},#{u.birthday},#{u.sex},#{u.address})
</foreach>
</insert>
<!--通过指定参数位置,来获取入参值的栗子:
-->
<select id="selectByTwoBirthday" resultType="user">
select <include refid="allcolumns"></include>
from users
where birthday between #{arg0} and #{arg1}
</select>
<!--通过入参为Map类型,来进行多个数据的传递
-->
<select id="selectByMap" resultType="user">
select <include refid="allcolumns"></include>
from users
where birthday between #{birthdayBegin} and #{birthdayEnd}
</select>
<!--将查出来的数据封进Map里,字段名(可以使用别名)就是key,列值就是value
-->
<select id="returnMap" parameterType="int" resultType="map">
select id,username as name,address
from users
where id=#{id}
</select>
<!--将查出来的数据,封进Map里,同为一行的数据为一个Map
最后返回一个装着很多Map的List集合
-->
<select id="returnMaps" resultType="map">
select username as name,address
from users
</select>
</mapper>
十、UserTest.java:
package org.burning;
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.burning.entity.Book;
import org.burning.entity.User;
import org.burning.mapper.UsersMapper;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
public class UsersTest {
SqlSession sqlSession;
//动态代理对象
UsersMapper usersMapper;
//日期的格式化刷子
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
@Before
public void openSqlSession() throws IOException {
//读取核心配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
//创建工厂对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//取出sqlSession
sqlSession = factory.openSession();
//取出动态代理对象,完成接口方法的调用,实则是调用xml文件中相应的标签的功能
usersMapper = sqlSession.getMapper(UsersMapper.class);
}
@After
public void closeSqlsession() {
sqlSession.close();
}
@Test
public void testGetAll() {
List<User> users = usersMapper.getAll();
users.forEach(user -> System.out.println(user));
}
@Test
public void testSelectByCondition() throws ParseException {
User u = new User();
u.setSex("1");
u.setUserName("小");
u.setAddress("河");
u.setBirthday(sdf.parse("1999-02-22"));
List<User> users = usersMapper.selectByCondition(u);
users.forEach(user -> System.out.println(user));
}
@Test
public void testUpdateBySet() throws ParseException {
User u = new User();
u.setId(3);
u.setUserName("小明的新名字");
u.setBirthday(sdf.parse("1999-02-22"));
int num = usersMapper.updateBySet(u);
System.out.println(num);
sqlSession.commit();
}
@Test
public void testSelectByIds() {
Integer[] array = {1,4,5};
List<User> users = usersMapper.selectByIds(array);
users.forEach(user -> System.out.println(user));
}
@Test
public void testDeleteBatch() {
Integer[] array = {11,12,13};
int num = usersMapper.deleteBatch(array);
System.out.println(num);
sqlSession.commit();
}
@Test
public void testBatch() throws ParseException {
User user1 = new User("王1",sdf.parse("2020-01-01"),"2","大锤岛分岛A");
User user2 = new User("王2",sdf.parse("2020-01-02"),"2","大锤岛分岛B");
User user3 = new User("王3",sdf.parse("2020-01-03"),"2","大锤岛分岛C");
List<User> users = new ArrayList<>();
users.add(user1);
users.add(user2);
users.add(user3);
int num = usersMapper.insertBatch(users);
System.out.println(num);
sqlSession.commit();
}
@Test
public void testSelectByTwoBirthday() throws ParseException {
List<User> users = usersMapper.selectByTwoBirthday(
sdf.parse("1900-12-12"),
sdf.parse("3000-01-01")
);
users.forEach(user -> System.out.println(user));
}
@Test
public void testSelectByMap() throws ParseException {
Map userMap = new HashMap();
Date begin = sdf.parse("1900-12-12");
Date end = sdf.parse("3000-01-01");
userMap.put("birthdayBegin",begin);
userMap.put("birthdayEnd",end);
List<User> users = usersMapper.selectByMap(userMap);
users.forEach(user -> System.out.println(user));
}
@Test
public void testReturnMap() {
Map map = usersMapper.returnMap(1);
System.out.println(map);
}
@Test
public void testReturnMaps() {
List<Map> mapList = usersMapper.returnMaps();
mapList.forEach(map -> System.out.println(map));
}
}
十一、BooksMapper.java:
package org.burning.mapper;
import org.burning.entity.Book;
import java.util.List;
public interface BooksMapper {
//查询全部图书(别名)
List<Book> selectBooks();
//查询全部图书(resultMap)
List<Book> selectBooksPro();
}
十二、BooksMapper.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="org.burning.mapper.BooksMapper">
<!--使用resultMap手工完成映射-->
<resultMap id="bookMap" type="book">
<!--主键绑定-->
<id property="id" column="book_id"></id>
<!--非主键绑定-->
<result property="name" column="book_name"></result>
</resultMap>
<!--通过起别名的方案解决字段名和成员变量名不一致的问题-->
<select id="selectBooks" resultType="book">
select book_id id,book_name name
from books
</select>
<!--通过resultMap方案解决字段名和成员变量名不一致的问题-->
<select id="selectBooksPro" resultMap="bookMap">
select book_id,book_name
from books
</select>
</mapper>
十三、BookTest.java:
package org.burning;
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.burning.entity.Book;
import org.burning.mapper.BooksMapper;
import org.burning.mapper.UsersMapper;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.security.spec.PSSParameterSpec;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
public class BooksTest {
SqlSession sqlSession;
//动态代理对象
BooksMapper booksMapper;
//日期的格式化刷子
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
@Before
public void openSqlSession() throws IOException {
//读取核心配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
//创建工厂对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//取出sqlSession
sqlSession = factory.openSession();
//取出动态代理对象,完成接口方法的调用,实则是调用xml文件中相应的标签的功能
booksMapper = sqlSession.getMapper(BooksMapper.class);
}
@After
public void closeSqlsession() {
sqlSession.close();
}
@Test
public void testSelectBooks(){
List<Book> bookList = booksMapper.selectBooks();
bookList.forEach(book -> System.out.println(book));
}
@Test
public void testSelectBooksPro(){
List<Book> bookList = booksMapper.selectBooksPro();
bookList.forEach(book -> System.out.println(book));
}
}