【Mybatis-Plus】02 Spring整合,基本CRUD
创建非骨架普通Maven工程:
引入Spring & MybatisPlus的依赖坐标及其它持久层依赖:
<properties> <spring.version>5.2.8.RELEASE</spring.version> </properties> <dependencies> <!-- Junit --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13</version> <scope>test</scope> </dependency> <!-- Lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> </dependency> <!-- Jdbc --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.20</version> </dependency> <!-- Mybatis-Plus --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus</artifactId> <version>3.3.2</version> </dependency> <!-- log --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.12</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.7.30</version> </dependency> <!-- Spring --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-expression</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>${spring.version}</version> </dependency> <!-- AspectJ --> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjweaver</artifactId> <version>1.9.6</version> </dependency> </dependencies>
创建一个实体类:
package cn.echo42.pojo; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.io.Serializable; /** * @author DaiZhiZhou * @file MP-Spring * @create 2020-08-05 22:36 */ @Data @AllArgsConstructor @NoArgsConstructor @TableName("sys_user") public class User implements Serializable { private static final long serialVersionUID = 8921929240003713006L; @TableId("user_id") private Integer user_id; private String user_name; // @TableField("user_name") private String user_password; // @TableField("user_password") private Integer user_status; // @TableField("user_status") private Integer user_is_del; // @TableField("user_is_del") }
创建UserMapper接口并继承Maybatis-Plus的BaseMapper接口,同时泛型注入我们User实体类
package cn.echo42.mapper; import cn.echo42.pojo.User; import com.baomidou.mybatisplus.core.mapper.BaseMapper; /** * @author DaiZhiZhou * @file MP-Spring * @create 2020-08-05 22:42 */ public interface UserMapper extends BaseMapper<User> { }
简单的CRUD可以不需要UserMapper.xml,Maybatis-Plus已经在BaseMapper中帮助我们实现了
有这个传统就默认创建把:
<?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="cn.echo42.mapper.UserMapper"> </mapper>
连接数据库参数配置文件[ db.properties ]:
jdbc.driverClassName = com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/oa?serverTimezone=GMT
jdbc.username = root
jdbc.password = 123456
日志配置文件[ log4j.properties ]:
# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# MyBatis logging configuration...
log4j.logger.org.mybatis.example.BlogMapper=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
编写SpringXML的容器配置:
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd " > <!-- dao层的配置:核心是要产生 Mapper代理类对象 1.引入数据库配置信息 2.数据源配置 3.SqlSessionFactory 4.产生Mapper接口的代理类对象 --> <!-- 1.引入数据库配置信息 --> <context:property-placeholder location="classpath:db.properties" system-properties-mode="FALLBACK" /> <!--2.数据源配置 --> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="${jdbc.driverClassName}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> </bean> <!-- 3.SqlSessionFactory --> <bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="globalConfig" ref="globalConfig" /> <!-- 加载xxMapper.xml --> <property name="mapperLocations"> <array> <value>classpath:mapper/*Mapper.xml</value> </array> </property> <!-- 配置分页插件 --> <property name="plugins"> <array> <bean class="com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor"> </bean> </array> </property> </bean> <!-- 声明全局配置 --> <bean id="globalConfig" class="com.baomidou.mybatisplus.core.config.GlobalConfig"> <!-- 指定主键自动增长类型 --> <property name="dbConfig" ref="dbConfig" /> </bean> <bean id="dbConfig" class="com.baomidou.mybatisplus.core.config.GlobalConfig.DbConfig"> <property name="idType" value="AUTO" /> </bean> <!-- 4.产生Mapper接口的代理类对象 --> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <!-- 需要生成代理类对象的mapper接口包 --> <property name="basePackage" value="cn.echo42.mapper" /> <!-- sqlSessionFactory 的name 用于为代理类中生成SqlSession --> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" /> </bean> </beans>
中央容器XML导入Dao配置:
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd " > <import resource="classpath:bean-xml/dao.xml" /> </beans>
简单的插入测试:
添加操作:
import cn.echo42.mapper.UserMapper; import cn.echo42.pojo.User; import org.junit.Test; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; /** * @author DaiZhiZhou * @file MP-Spring * @create 2020-08-05 22:54 */ public class MybatisPlusTest { @Test public void SpringIntegrationMP_QuickStart() { ApplicationContext applicationContext = new ClassPathXmlApplicationContext("classpath:bean-xml/central.xml"); UserMapper userMapper = applicationContext.getBean(UserMapper.class); int insert = userMapper.insert( new User( null, "阿伟001", "133778", 1, 0 ) ); } }
日志打印结果:
DEBUG [main] - Creating a new SqlSession DEBUG [main] - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@8519cb4] was not registered for synchronization because synchronization is not active DEBUG [main] - Fetching JDBC Connection from DataSource DEBUG [main] - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/oa?serverTimezone=GMT] DEBUG [main] - JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@495b0487] will not be managed by Spring DEBUG [main] - ==> Preparing: INSERT INTO sys_user ( user_name, user_password, user_status, user_is_del ) VALUES ( ?, ?, ?, ? ) DEBUG [main] - ==> Parameters: 阿伟001(String), 133778(String), 1(Integer), 0(Integer) DEBUG [main] - <== Updates: 1 DEBUG [main] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@8519cb4] Process finished with exit code 0
为了更方便的测试,引入Spring-Test坐标,UserMapper自动装配,全注解测试。
Spring-Test坐标:
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>${spring.version}</version> </dependency>
测试类:
import cn.echo42.mapper.UserMapper; import cn.echo42.pojo.User; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; /** * @author DaiZhiZhou * @file MP-Spring * @create 2020-08-05 23:07 */ @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = "bean-xml/central.xml") public class MybatisPlusAnnotationTest { @Autowired UserMapper userMapper; }
这里有个坑,注意xml路径声明要写classpath:,不然Spring读取不到文件了
@ContextConfiguration(locations = "classpath:bean-xml/central.xml")
修改操作:
@Test public void mpUpdate() { UpdateWrapper<User> updateWrapper = new UpdateWrapper<User>(); updateWrapper.eq("user_id", 6); // WHERE user_id = 6 // updateWrapper.between("id", 10, 20); WHERE id BETWEEN 10 AND 20 int update = userMapper.update(new User( null, "杰哥001", "133778", 1, 0 ), updateWrapper); }
他这里的Wrapper对象和SpringDataJPA的条件规范的条件对象极其相似
通过这个对象来注入我们的条件,这个筛选条件的方法几乎一样
修改测试结果:
DEBUG [main] - Creating a new SqlSession DEBUG [main] - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1787f2a0] was not registered for synchronization because synchronization is not active DEBUG [main] - Fetching JDBC Connection from DataSource DEBUG [main] - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/oa?serverTimezone=GMT] DEBUG [main] - JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@784b990c] will not be managed by Spring DEBUG [main] - ==> Preparing: UPDATE sys_user SET user_name=?, user_password=?, user_status=?, user_is_del=? WHERE (user_id = ?) DEBUG [main] - ==> Parameters: 杰哥001(String), 133778(String), 1(Integer), 0(Integer), 6(Integer) DEBUG [main] - <== Updates: 1 DEBUG [main] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1787f2a0]
删除操作:
@Test public void mpDelete() { UpdateWrapper<User> updateWrapper = new UpdateWrapper<User>(); updateWrapper.eq("user_id", 6); // WHERE user_id = 6 // updateWrapper.between("id", 10, 20); WHERE id BETWEEN 10 AND 20 int delete = userMapper.delete(updateWrapper); }
同样的,因为删除只需要筛选条件就够了
日志结果打印:
DEBUG [main] - Creating a new SqlSession DEBUG [main] - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1787f2a0] was not registered for synchronization because synchronization is not active DEBUG [main] - Fetching JDBC Connection from DataSource DEBUG [main] - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/oa?serverTimezone=GMT] DEBUG [main] - JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@42f33b5d] will not be managed by Spring DEBUG [main] - ==> Preparing: DELETE FROM sys_user WHERE (user_id = ?) DEBUG [main] - ==> Parameters: 6(Integer) DEBUG [main] - <== Updates: 1 DEBUG [main] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1787f2a0]
查询操作:
参数是要求一个序列化接口类型的对象
已知的基本类型的包装类都基本符合此类型,所以小是基本使用的Integer,大到Decimal也可以
@Test public void mpQuery() { User user = (User)this.userMapper.selectById(3); System.out.println(user); }
因为之前的驼峰转义导致实体类属性命名得更改了:
package cn.echo42.pojo; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.io.Serializable; /** * @author DaiZhiZhou * @file MP-Spring * @create 2020-08-05 22:36 */ @Data @AllArgsConstructor @NoArgsConstructor @TableName("sys_user") public class User implements Serializable { private static final long serialVersionUID = 8921929240003713006L; @TableId("user_id") private Integer userId; // private Integer user_id; private String userName; // private String user_name; @TableField("user_name") private String userPassword; // private String user_password; @TableField("user_password") private Integer userStatus; // private Integer user_status; @TableField("user_status") private Integer userIsDel; // private Integer user_is_del; @TableField("user_is_del") }
查询结果:
DEBUG [main] - Creating a new SqlSession DEBUG [main] - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7d61eb55] was not registered for synchronization because synchronization is not active DEBUG [main] - Fetching JDBC Connection from DataSource DEBUG [main] - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/oa?serverTimezone=GMT] DEBUG [main] - JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@58ffcbd7] will not be managed by Spring DEBUG [main] - ==> Preparing: SELECT user_id,user_name,user_password,user_status,user_is_del FROM sys_user WHERE user_id=? DEBUG [main] - ==> Parameters: 3(Integer) DEBUG [main] - <== Total: 1 DEBUG [main] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7d61eb55] User(userId=3, userName=user02, userPassword=123456, userStatus=1, userIsDel=0)
查询操作2:
通过一个集合对象,装载一系列ID元素注入到这个方法中查询:
@Test public void mpQuery2() { List<Serializable> list = new ArrayList<Serializable>(); list.add(2); list.add(4); List<User> userList = userMapper.selectBatchIds(list); for (User user : userList) { System.out.println(user); } }
查询结果:
DEBUG [main] - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/oa?serverTimezone=GMT] DEBUG [main] - JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2e9fda69] will not be managed by Spring DEBUG [main] - ==> Preparing: SELECT user_id,user_name,user_password,user_status,user_is_del FROM sys_user WHERE user_id IN ( ? , ? ) DEBUG [main] - ==> Parameters: 2(Integer), 4(Integer) DEBUG [main] - <== Total: 2 DEBUG [main] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6c7a164b] User(userId=2, userName=user01, userPassword=123456, userStatus=1, userIsDel=0) User(userId=4, userName=user03, userPassword=123456, userStatus=1, userIsDel=0)
查询操作3:
使用Map集合封装筛选条件,Key即字段名称,Value即值
赋值多个条件,表示条件是连接的AND
@Test public void mpQuery3() { Map<String, Object> columnMapping = new HashMap<String, Object>(); columnMapping.put("user_is_del", 0); columnMapping.put("user_password", "123456"); List<User> userList = userMapper.selectByMap(columnMapping); for (User user : userList) { System.out.println(user); } }
查询结果:
DEBUG [main] - Creating a new SqlSession DEBUG [main] - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6c7a164b] was not registered for synchronization because synchronization is not active DEBUG [main] - Fetching JDBC Connection from DataSource DEBUG [main] - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/oa?serverTimezone=GMT] DEBUG [main] - JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@c5ee75e] will not be managed by Spring DEBUG [main] - ==> Preparing: SELECT user_id,user_name,user_password,user_status,user_is_del FROM sys_user WHERE user_password = ? AND user_is_del = ? DEBUG [main] - ==> Parameters: 123456(String), 0(Integer) DEBUG [main] - <== Total: 3 DEBUG [main] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6c7a164b] User(userId=2, userName=user01, userPassword=123456, userStatus=1, userIsDel=0) User(userId=3, userName=user02, userPassword=123456, userStatus=1, userIsDel=0) User(userId=4, userName=user03, userPassword=123456, userStatus=1, userIsDel=0)
查询操作4:
和上面的增删改一样使用封装条件对象执行:
不过selectCount是用以查询记录数量的
@Test public void mpQuery4() { QueryWrapper<User> userWrapper = new QueryWrapper<User>(); // 假装这是从控制器传递过来的字符串参数 String fromUrlParam = "user"; userWrapper.like(fromUrlParam != null , "user_name", fromUrlParam); // 用来查询符合筛选条件的记录数量 Integer integer = userMapper.selectCount(userWrapper); System.out.println("return rows : " + integer ); }
查询结果:
DEBUG [main] - Creating a new SqlSession DEBUG [main] - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@13d9cbf5] was not registered for synchronization because synchronization is not active DEBUG [main] - Fetching JDBC Connection from DataSource DEBUG [main] - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/oa?serverTimezone=GMT] DEBUG [main] - JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@246f8b8b] will not be managed by Spring DEBUG [main] - ==> Preparing: SELECT COUNT( 1 ) FROM sys_user WHERE (user_name LIKE ?) DEBUG [main] - ==> Parameters: %user%(String) DEBUG [main] - <== Total: 1 DEBUG [main] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@13d9cbf5] return rows : 3
查询操作5:
用于分页操作的查询,Mybatis已经帮助我们封装好了页面对象
从这个页面对象中获取信息即可
@Test public void mpQuery5() { IPage<User> page = new Page<User>(1, 5); userMapper.selectPage(page, null); long total = page.getTotal(); System.out.println("TotalRecords : " + total); List<User> list = page.getRecords(); for (User user : list) { System.out.println(user); } }
查询结果:
DEBUG [main] - Creating a new SqlSession DEBUG [main] - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@47c81abf] was not registered for synchronization because synchronization is not active DEBUG [main] - Fetching JDBC Connection from DataSource DEBUG [main] - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/oa?serverTimezone=GMT] DEBUG [main] - JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6c25e6c4] will not be managed by Spring DEBUG [main] - JsqlParserCountOptimize sql=SELECT user_id,user_name,user_password,user_status,user_is_del FROM sys_user DEBUG [main] - ==> Preparing: SELECT COUNT(1) FROM sys_user DEBUG [main] - ==> Parameters: DEBUG [main] - ==> Preparing: SELECT user_id,user_name,user_password,user_status,user_is_del FROM sys_user LIMIT ?,? DEBUG [main] - ==> Parameters: 0(Long), 5(Long) DEBUG [main] - <== Total: 5 DEBUG [main] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@47c81abf] TotalRecords : 5 User(userId=1, userName=admin, userPassword=admin, userStatus=1, userIsDel=0) User(userId=2, userName=user01, userPassword=123456, userStatus=1, userIsDel=0) User(userId=3, userName=user02, userPassword=123456, userStatus=1, userIsDel=0) User(userId=4, userName=user03, userPassword=123456, userStatus=1, userIsDel=0) User(userId=5, userName=admin02, userPassword=admin02, userStatus=1, userIsDel=0)