MyBatis学习（六）

发表于 更新于 分类于 ， ， 阅读次数：

MyBatis查询语句加强

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
package com.xiaoguan.mybatis.mapper;

import com.xiaoguan.mybatis.pojo.Car;
import org.apache.ibatis.annotations.MapKey;

import java.util.List;
import java.util.Map;

public interface CarMapper {
    Long selectTotal();
    List<Car> selectAllByTuoFeng();
    List<Car> selectAllByMapping();
    @MapKey("id")
    Map<Long,Map<String,Object>> selectAllRetrunTwoMap();
    List<Map<String,Object>> selectAllMap();
    Map<String, Object> selectByIdMap(Long id);
    List<Car> selectById2(Long id);
    Car selectByBrandLike(String brand);
    List<Car> selectAll();
    Car selectById(Long id);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
package com.xiaoguan.mybatis.pojo;

import java.util.Objects;

/**
 * 封装汽车相关信息的普通java类
 */
public class Car {
    //建议使用包装类
    private Long id;
    private String carNum;
    private  String brand;
    private Double guidePrice;
    private String produceTime;
    private String carType;

    public Car() {
    }

    public Car(Long id, String carNum, String brand, Double guidePrice, String produceTime, String carType) {
        this.id = id;
        this.carNum = carNum;
        this.brand = brand;
        this.guidePrice = guidePrice;
        this.produceTime = produceTime;
        this.carType = carType;
    }

    @Override
    public String toString() {
        return "Car{" +
                "id=" + id +
                ", carNum='" + carNum + '\'' +
                ", brand='" + brand + '\'' +
                ", guidePrice=" + guidePrice +
                ", produceTime='" + produceTime + '\'' +
                ", carType='" + carType + '\'' +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Car car = (Car) o;
        return Objects.equals(getId(), car.getId()) && Objects.equals(getCarNum(), car.getCarNum()) && Objects.equals(getBrand(), car.getBrand()) && Objects.equals(getGuidePrice(), car.getGuidePrice()) && Objects.equals(getProduceTime(), car.getProduceTime()) && Objects.equals(getCarType(), car.getCarType());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getId(), getCarNum(), getBrand(), getGuidePrice(), getProduceTime(), getCarType());
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCarNum() {
        return carNum;
    }

    public void setCarNum(String carNum) {
        this.carNum = carNum;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public Double getGuidePrice() {
        return guidePrice;
    }

    public void setGuidePrice(Double guidePrice) {
        this.guidePrice = guidePrice;
    }

    public String getProduceTime() {
        return produceTime;
    }

    public void setProduceTime(String produceTime) {
        this.produceTime = produceTime;
    }

    public String getCarType() {
        return carType;
    }

    public void setCarType(String carType) {
        this.carType = carType;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package com.xiaoguan.mybatis.utils;

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 java.io.IOException;

/**
 * MyBatis工具类
 */
public class SqlSessionUtils {
    private static SqlSessionFactory sqlSessionFactory;
    private SqlSessionUtils() {}
    static {
        try {
           sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    private static ThreadLocal<SqlSession> local=new ThreadLocal<>();
    /**
     * 获取会话对象
     * @return 会话对象
     */
    public static SqlSession openSession(){
        SqlSession sqlSession = local.get();
        if (sqlSession==null) {
            sqlSession=sqlSessionFactory.openSession();
            local.set(sqlSession);
        }
        return sqlSession;

    }

    /**
     * 关闭sqlSession对象
     * @param sqlSession
     */
    public static void close(SqlSession sqlSession){
        if (sqlSession != null) {
            sqlSession.close();
            //移除sqlSession对象和当前线程绑定关系
            local.remove();
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?xml version="1.0" encoding="UTF-8"?>

<configuration debug="false">
    <!-- 控制台输出 -->
    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符-->
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
        </encoder>
    </appender>
    <!-- 按照每天生成日志文件 -->
    <!--mybatis log configure-->
    <logger name="com.apache.ibatis" level="TRACE"/>
    <logger name="java.sql.Connection" level="DEBUG"/>
    <logger name="java.sql.Statement" level="DEBUG"/>
    <logger name="java.sql.PreparedStatement" level="DEBUG"/>

    <!-- 日志输出级别,logback日志级别包括五个：TRACE < DEBUG < INFO < WARN < ERROR -->
    <root level="DEBUG">
        <appender-ref ref="STDOUT"/>
        <appender-ref ref="FILE"/>
    </root>

</configuration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?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>
    <properties resource="jdbc.properties"/>
    <settings>
        <!--开启驼峰映射-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
        <package name="com.xiaoguan.mybatis.pojo"/>
    </typeAliases>
    <environments default="mybatis">
        <environment id="mybatis">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.xiaoguan.mybatis.mapper"/>
    </mappers>
</configuration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
package com.xiaoguan.mybatis.test;

import com.xiaoguan.mybatis.mapper.CarMapper;
import com.xiaoguan.mybatis.pojo.Car;
import com.xiaoguan.mybatis.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;
import java.util.Map;

public class TestCarMapper {
    @Test
    public void testselectByBrandLike(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = mapper.selectByBrandLike("凯美瑞");
        System.out.println(car);
        sqlSession.close();
    }
    @Test
    public void testselectAll(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectAll();
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
    @Test
    public void testselectById(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = mapper.selectById(34L);
        System.out.println(car);
        sqlSession.close();
    }
    @Test
    public void testselectById2(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectById2(34L);
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
    @Test
    public void testselectByIdMap(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Map<String, Object> map = mapper.selectByIdMap(34L);
        System.out.println(map);
        sqlSession.close();
    }
    @Test
    public void testselectAllMap(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Map<String, Object>> maps = mapper.selectAllMap();
        maps.forEach(map -> System.out.println(map));
        sqlSession.close();
    }
    @Test
    public void testselectAllRetrunTwoMap(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Map<Long, Map<String, Object>> longMapMap = mapper.selectAllRetrunTwoMap();
        System.out.println(longMapMap);
        sqlSession.close();
    }
    @Test
    public void testselectAllByMapping() {
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectAllByMapping();
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
    @Test
    public void testselectAllByTuoFeng() {
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars =mapper.selectAllByTuoFeng();
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
    @Test
    public void testselectTotal() {
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Long aLong = mapper.selectTotal();
        System.out.println(aLong);
        sqlSession.close();
    }
}

动态SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.xiaoguan.mapper;

import com.xiaoguan.pojo.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface CarMapper {
    int deleteByIds2(@Param("ids") Long[] ids);
    int insertBatch(@Param("cars") List<Car> cars);
    int deleteByIds(@Param("ids") Long[] ids);
    List<Car> selectByChoose(@Param("brand") String brand,@Param("guidePrice") String guidePrice,@Param("carType") String carType);
    int updateCarBySet(Car car);
    int updateCarById(Car car);
    List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand,
                                              @Param("guidePrice") Double guidePrice,
                                              @Param("carType") String carType);
    List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand,
                                              @Param("guidePrice") Double guidePrice,
                                              @Param("carType") String carType);
    List<Car> selectByMultiCondition(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
package com.xiaoguan.pojo;

import java.util.Objects;

/**
 * 封装汽车相关信息的普通java类
 */
public class Car {
    //建议使用包装类
    private Long id;
    private String carNum;
    private  String brand;
    private Double guidePrice;
    private String produceTime;
    private String carType;

    public Car() {
    }

    public Car(Long id, String carNum, String brand, Double guidePrice, String produceTime, String carType) {
        this.id = id;
        this.carNum = carNum;
        this.brand = brand;
        this.guidePrice = guidePrice;
        this.produceTime = produceTime;
        this.carType = carType;
    }

    @Override
    public String toString() {
        return "Car{" +
                "id=" + id +
                ", carNum='" + carNum + '\'' +
                ", brand='" + brand + '\'' +
                ", guidePrice=" + guidePrice +
                ", produceTime='" + produceTime + '\'' +
                ", carType='" + carType + '\'' +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Car car = (Car) o;
        return Objects.equals(getId(), car.getId()) && Objects.equals(getCarNum(), car.getCarNum()) && Objects.equals(getBrand(), car.getBrand()) && Objects.equals(getGuidePrice(), car.getGuidePrice()) && Objects.equals(getProduceTime(), car.getProduceTime()) && Objects.equals(getCarType(), car.getCarType());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getId(), getCarNum(), getBrand(), getGuidePrice(), getProduceTime(), getCarType());
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCarNum() {
        return carNum;
    }

    public void setCarNum(String carNum) {
        this.carNum = carNum;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public Double getGuidePrice() {
        return guidePrice;
    }

    public void setGuidePrice(Double guidePrice) {
        this.guidePrice = guidePrice;
    }

    public String getProduceTime() {
        return produceTime;
    }

    public void setProduceTime(String produceTime) {
        this.produceTime = produceTime;
    }

    public String getCarType() {
        return carType;
    }

    public void setCarType(String carType) {
        this.carType = carType;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package com.xiaoguan.utils;

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 java.io.IOException;

/**
 * MyBatis工具类
 */
public class SqlSessionUtils {
    private static SqlSessionFactory sqlSessionFactory;
    private SqlSessionUtils() {}
    static {
        try {
           sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    private static ThreadLocal<SqlSession> local=new ThreadLocal<>();
    /**
     * 获取会话对象
     * @return 会话对象
     */
    public static SqlSession openSession(){
        SqlSession sqlSession = local.get();
        if (sqlSession==null) {
            sqlSession=sqlSessionFactory.openSession();
            local.set(sqlSession);
        }
        return sqlSession;

    }

    /**
     * 关闭sqlSession对象
     * @param sqlSession
     */
    public static void close(SqlSession sqlSession){
        if (sqlSession != null) {
            sqlSession.close();
            //移除sqlSession对象和当前线程绑定关系
            local.remove();
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?xml version="1.0" encoding="UTF-8"?>

<configuration debug="false">
    <!-- 控制台输出 -->
    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符-->
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
        </encoder>
    </appender>
    <!-- 按照每天生成日志文件 -->
    <!--mybatis log configure-->
    <logger name="com.apache.ibatis" level="TRACE"/>
    <logger name="java.sql.Connection" level="DEBUG"/>
    <logger name="java.sql.Statement" level="DEBUG"/>
    <logger name="java.sql.PreparedStatement" level="DEBUG"/>

    <!-- 日志输出级别,logback日志级别包括五个：TRACE < DEBUG < INFO < WARN < ERROR -->
    <root level="DEBUG">
        <appender-ref ref="STDOUT"/>
        <appender-ref ref="FILE"/>
    </root>

</configuration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?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>
    <properties resource="jdbc.properties"/>
    <settings>
        <!--开启驼峰映射-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
        <package name="com.xiaoguan.pojo"/>
    </typeAliases>
    <environments default="mybatis">
        <environment id="mybatis">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.xiaoguan.mapper"/>
    </mappers>
</configuration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
package com.xiaoguan.test;

import com.xiaoguan.mapper.CarMapper;
import com.xiaoguan.pojo.Car;
import com.xiaoguan.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

public class CarTest {
    @Test
    public void testdeleteByIds2(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Long[] ids= {37L,38L};
        int count=mapper.deleteByIds2(ids);
        System.out.println(count);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testInsertinsertBatch(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carLists=new ArrayList<>();
        for (int i = 0; i < 4; i++) {
            carLists.add(new Car(null, "1200", "帕萨特", 200.0, "2023-07-18", "燃油车"));
        }
        int count=mapper.insertBatch(carLists);
        System.out.println(count);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testdeleteByIds(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Long[] ids= {29L,31L};
        int count=mapper.deleteByIds(ids);
        System.out.println(count);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testselectByChoose(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car>cars=mapper.selectByChoose("",null,"燃油车    ");
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
    @Test
    public void testupdateCarBySet(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(34L, "8888888888", null, null, null, null);
        mapper.updateCarBySet(car);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testupdateCarById(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(34L, "88888888", "保时捷", 200.0, "2023-07-18", "燃油车");
        mapper.updateCarById(car);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public  void testselectByMultiConditionWithTrim(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars=mapper.selectByMultiConditionWithTrim("特斯拉",50.0,"");
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
    @Test
    public  void testselectByMultiConditionWithWhere(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars=mapper.selectByMultiConditionWithWhere("特斯拉",50.0,"");
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
    @Test
    public  void testselectByMultiCondition(){
        SqlSession sqlSession = SqlSessionUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars=mapper.selectByMultiCondition("特斯拉",null,"");
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
}