原始jdbc操作（查询数据）
原始jdbc操作的分析
原始jdbc开发存在的问题如下：
① 数据库连接创建、释放频繁造成系统资源浪费从而影响系统性能
② sql 语句在代码中硬编码，造成代码不易维护，实际应用 sql 变化的可能较大，sql 变动需要改变java代码。
③ 查询操作时，需要手动将结果集中的数据手动封装到实体中。插入操作时，需要手动将实体的数据设置到sql语句的占位
符位置
应对上述问题给出的解决方案：
① 使用数据库连接池初始化连接资源
② 将sql语句抽取到xml配置文件中
③ 使用反射、内省等底层技术，自动将实体与表进行属性与字段的自动映射
什么是Mybatis
基于java的持久层框架，它内部封装了
jdbc，使开发者只需要关注sql语句本身，而不需要花费精力
去处理加载驱动、创建连接、创建statement等繁杂的过程。
mybatis通过xml或注解的方式将要执行的各种 statement配
置起来，并通过java对象和statement中sql的动态参数进行
映射生成最终执行的sql语句。
最后mybatis框架执行sql并将结果映射为java对象并返回。采
用ORM思想解决了实体和数据库映射的问题，对jdbc 进行了
封装，屏蔽了jdbc api 底层访问细节，使我们不用与jdbc api
打交道，就可以完成对数据库的持久化操作
Mybatis入门案例
1，导入MyBatis的坐标和其他相关坐标
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.32</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
</dependencies>
2，创建user数据表
3，编写User实体
4，编写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 namespace="userMapper">
<!--查询操作-->
<select id="findAll" resultType="com.benjamin.domain.User">
select * from user
</select>
</mapper>
5，编写MyBatis核心文件
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>
<!--通过properties标签加载外部properties文件-->
<properties resource="jdbc.properties"></properties>
<!--数据源环境-->
<environments default="developement">
<environment id="developement">
<transactionManager type="JDBC"></transactionManager>
<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>
<mapper resource="com/benjamin/map/UserMapper.xml"></mapper>
</mappers>
</configuration>
jdbc.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=root
6，编写测试代码
@Test
//查询操作
public void test1() throws IOException {
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行操作 参数：namespace+id
List<User> userList = sqlSession.selectList("userMapper.findAll");
//打印数据
//[User{id=1, username='zhangsan', password='123'}, User{id=2, username='lisi', password='123'}, User{id=3, username='wangwu', password='123'}]
System.out.println(userList);
//释放资源
sqlSession.close();
}
插入操作注意问题
• 插入语句使用insert标签
• 在映射文件中使用parameterType属性指定要插入的数据类型
• Sql语句中使用#{实体属性名}方式引用实体中的属性值
• 插入操作使用的API是sqlSession.insert(“命名空间.id”,实体对象);
• 插入操作涉及数据库数据变化，所以要使用sqlSession对象显示的提交事务，
即sqlSession.commit()
<!--插入操作-->
<insert id="save" parameterType="com.benjamin.domain.User">
insert into user values(#{id},#{username},#{password})
</insert>
@Test
//插入user
public void test2() throws IOException {
User user = new User();
user.setId(4);
user.setPassword("123456");
user.setUsername("tom");
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行操作 参数：namespace+id
sqlSession.insert("userMapper.save",user);
//提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
修改操作注意问题
• 修改语句使用update标签
• 修改操作使用的API是sqlSession.update(“命名空间.id”,实体对象);
<!--修改操作-->
<update id="update" parameterType="com.benjamin.domain.User">
update user set username=#{username},password=#{password} where id=#{id}
</update>
@Test
//修改操作
public void test3() throws IOException {
//模拟user对象
User user = new User();
user.setId(4);
user.setUsername("lucy");
user.setPassword("123");
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行操作 参数：namespace+id
sqlSession.update("userMapper.update",user);
//mybatis执行更新操作 提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
删除操作注意问题
删除语句使用delete标签
• Sql语句中使用#{任意字符串}方式引用传递的单个参数
• 删除操作使用的API是sqlSession.delete(“命名空间.id”,Object);
<!--删除操作-->
<delete id="delete" parameterType="int">
delete from user where id=#{abc}
</delete>
@Test
//删除操作
public void test4() throws IOException {
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行操作 参数：namespace+id
sqlSession.delete("userMapper.delete",8);
//mybatis执行更新操作 提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
MyBatis的映射文件概述
MyBatis常用配置解析
environments标签
其中，事务管理器（transactionManager）类型有两种：
• JDBC：这个配置就是直接使用了JDBC 的提交和回滚设置，它依赖于从数据源得到的连接来管理事务作用域。
• MANAGED：这个配置几乎没做什么。它从来不提交或回滚一个连接，而是让容器来管理事务的整个生命周期（比如JEE
应用服务器的上下文）。 默认情况下它会关闭连接，然而一些容器并不希望这样，因此需要将 closeConnection 属性设置
为 false 来阻止它默认的关闭行为。
其中，数据源（dataSource）类型有三种：
• UNPOOLED：这个数据源的实现只是每次被请求时打开和关闭连接。
• POOLED：这种数据源的实现利用“池”的概念将 JDBC 连接对象组织起来。
• JNDI：这个数据源的实现是为了能在如 EJB 或应用服务器这类容器中使用，容器可以集中或在外部配置数据源，然后放置
一个 JNDI 上下文的引用。
配置文件解析
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>配置
<!--通过properties标签加载外部properties文件,例如使用${jdbc.driver}去读取外部properties文件的内容-->
<properties resource="jdbc.properties"></properties>属性
<!--自定义别名，在UserMapper.xml中引用-->
<typeAliases>
<typeAlias type="com.benjamin.domain.User" alias="user"></typeAlias>
</typeAliases>
<!--数据源环境-->
<environments default="developement">环境，当配置多个environment时，default=指定默认的环境名称
<environment id="developement">指定当前环境的名称
<transactionManager type="JDBC"></transactionManager>事务管理器，指定事务管理类型是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>
<!--加载映射文件-->
<!--mapper标签
该标签的作用是加载映射的，加载方式有如下几种：
使用相对路径的资源引用，例如：<mapper resource="org/mybatis/builder/AuthorMapper.xml"/>
使用完全限定资源定位符（URL），例如：<mapper url="file:///var/mappers/AuthorMapper.xml"/>
使用映射器接口实现类的完全限定类名，例如：<mapper class="org.mybatis.builder.AuthorMapper"/>
将包内的映射器接口实现全部注册为映射器，例如：<package name="org.mybatis.builder"/>-->
<mappers>
<mapper resource="com/benjamin/map/UserMapper.xml"></mapper>
</mappers>
</configuration>
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 namespace="userMapper">
<!--自定义别名： 根据id进行查询，resultType="user"引用的是sqlMapConfig.xml中定义的别名-->
<!--默认别名： parameterType="int"中的int是mybatis框架定义好的别名-->
<!--别名 数据类型: string String,long Long,int Integer,double Double,boolean Boolean-->
<select id="findById" resultType="user" parameterType="int">
select * from user where id=#{id}
</select>
<!--修改操作-->
<update id="update" parameterType="com.benjamin.domain.User">
update user set username=#{username},password=#{password} where id=#{id}
</update>
<!--插入操作-->
<insert id="save" parameterType="com.benjamin.domain.User">
insert into user values(#{id},#{username},#{password})
</insert>
</mapper>
MyBatis相应API
SqlSession工厂构建器SqlSessionFactoryBuilder
通过加载mybatis的核心文件的输入流的形式构建一个SqlSessionFactory对象
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
openSession() 会默认开启一个事务，但事务不会自动提交，也就意味着需要手动提交该事务，更新操作数据才会持久化到数据库中
openSession(boolean autoCommit) 参数为是否自动提交，如果设置为true，那么不需要手动提交事务
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
操作事务的方法
void commit()
void rollback()
Mybatis的Dao层实现
传统开发方式
1，编写dao层接口，和接口实现
2，service层去调用dao层实现类
代理开发方式
采用 Mybatis 的代理开发方式实现 DAO 层的开发，这种方式是企业的主流。
Mapper 接口开发方法只需要程序员编写Mapper 接口（相当于Dao 接口），由Mybatis 框架根据接口定义创建接
口的动态代理对象，代理对象的方法体同上边Dao接口实现类方法。
Mapper 接口开发需要遵循以下规范：
1、 Mapper.xml文件中的namespace与mapper接口的全限定名相同
2、 Mapper接口方法名和Mapper.xml中定义的每个statement的id相同
3、 Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql的parameterType的类型相同
4、 Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同
1，dao层只编写接口，接口的实现由mybatis进行
public interface UserMapper {
public List<User> findAll();
//2、 Mapper接口方法名findById和Mapper.xml中定义的每个statement的id相同
//3、 Mapper接口方法的输入参数类型int和mapper.xml中定义的每个sql的parameterType的类型相同
//4、 Mapper接口方法的输出参数类型User和mapper.xml中定义的每个sql的resultType的类型相同
public User findById(int id);
}
2，Service层代码
//UserMapper的实现交由mybatis
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
public class ServiceDemo {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sessionFactory.openSession();
//mybatis实现了UserMapper
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//find all
List<User> userList = mapper.findAll();
//[User{id=1, username='zhangsan', password='123'}, User{id=2, username='lisi', password='123'}, User{id=3, username='wangwu', password='123'}, User{id=4, username='tom', password='123456'}]
System.out.println(userList);
//select one
User user = mapper.findById(1);
//User{id=1, username='zhangsan', password='123'}
System.out.println(user);
}
}
3，配置文件需要遵循Mapper接口开发规范1，2，3，4
<?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">
<!--1、 Mapper.xml文件中的namespace与mapper接口的全限定名相同-->
<mapper namespace="com.benjamin.dao.UserMapper">
<!--查询操作-->
<select id="findAll" resultType="com.benjamin.domain.User">
select * from user
</select>
<!--根据id进行查询-->
<!--2、 Mapper接口方法名和Mapper.xml中定义的每个statement的id（findById）相同-->
<!--3、 Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql的parameterType的类型相同-->
<!--4、 Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同-->
<select id="findById" resultType="user" parameterType="int">
select * from user where id=#{id}
</select>
</mapper>
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>
properties省略
<!--自定义别名-->
<typeAliases>
<typeAlias type="com.benjamin.domain.User" alias="user"></typeAlias>
</typeAliases>
environments省略
<!--加载映射文件-->
<mappers>
<mapper resource="com/benjamin/map/UserMapper.xml"></mapper>
</mappers>
</configuration>
动态sql语句
动态 SQL 之if
我们根据实体类的不同取值，使用不同的 SQL语句来进行查询。比如在 id如果不为空时可以根据id查询，如果
username 不同空时还要加入用户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。
UserMapper.xml
<!--select * from user where id=#{id} and username=#{username} and password=#{password}-->
<select id="findByCondition" resultType="user" parameterType="user">
<include refid="selectUser"></include>
<where>
<if test="id!=0">
and id=#{id}
</if>
<if test="username!=null">
and username=#{username}
</if>
<if test="password!=null">
and password=#{password}
</if>
</where>
</select>
测试方法
@Test
//动态sql if
public void test4() throws IOException {
User user = new User();
user.setId(1);
user.setUsername("zhangsan");
user.setPassword("123");
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行操作 参数：namespace+id
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//select * from user where id=? and username=? and password=?
List<User> byCondition = mapper.findByCondition(user);
//[User{id=1, username='zhangsan', password='123'}]
System.out.println(byCondition);
//释放资源
sqlSession.close();
}
动态 SQL 之foreach
循环执行sql的拼接操作，例如：SELECT * FROM USER WHERE id IN (1,2,5)。
foreach标签的属性含义如下：
<foreach>标签用于遍历集合，它的属性：
collection：代表要遍历的集合元素，注意编写时不要写#{}
open：代表语句的开始部分
close：代表结束部分
item：代表遍历集合的每个元素，生成的变量名
sperator：代表分隔符
UserMapper.xml配置文件
<!--select * from user WHERE id in ( ? , ? , ? ) -->
<!--include引用被抽取的sql语句-->
<select id="findByIds" resultType="user" parameterType="list">
<include refid="selectUser"></include>
<where>
<foreach collection="list" item="id" open="id in (" close=")" separator=",">
#{id}
</foreach>
</where>
</select>
<!--sql片段抽取-->
<sql id="selectUser">
select * from user
</sql>
测试方法
@Test
//动态sql for each
public void test5() throws IOException {
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行操作 参数：namespace+id
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Integer> list = new ArrayList<Integer>();
list.add(1);
list.add(2);
list.add(3);
//select * from user WHERE id in ( ? , ? , ? )
List<User> byIds = mapper.findByIds(list);
System.out.println(byIds);
//释放资源
sqlSession.close();
}
SQL片段抽取
Sql 中可将重复的 sql 提取出来，使用时用 include 引用即可，最终达到 sql 重用的目的
UserMapper.xml
<!--select * from user WHERE id in ( ? , ? , ? ) -->
<!--include引用被抽取的sql语句-->
<select id="findByIds" resultType="user" parameterType="list">
<include refid="selectUser"></include>
<where>
<foreach collection="list" item="id" open="id in (" close=")" separator=",">
#{id}
</foreach>
</where>
</select>
<!--sql片段抽取-->
<sql id="selectUser">
select * from user
</sql>
MyBatis映射文件配置
<select>：查询
<insert>：插入
<update>：修改
<delete>：删除
<where>：where条件
<if>：if判断
<foreach>：循环
<sql>：sql片段抽取
typeHandlers标签
无论是 MyBatis 在预处理语句（PreparedStatement）中设置一个参数时，还是从结果集中取出一个值时， 都会用
类型处理器将获取的值以合适的方式转换成 Java 类型。下表描述了一些默认的类型处理器（截取部分）。
自定义类型转换器
你可以重写类型处理器或创建你自己的类型处理器来处理不支持的或非标准的类型。具体做法为：实现
org.apache.ibatis.type.TypeHandler 接口， 或继承一个很便利的类 org.apache.ibatis.type.BaseTypeHandler， 然
后可以选择性地将它映射到一个JDBC类型。例如需求：一个Java中的Date数据类型，我想将之存到数据库的时候存成一
个1970年至今的毫秒数，取出来时转换成java的Date，即java的Date与数据库的varchar毫秒值之间转换。
开发步骤：
① 定义转换类继承类BaseTypeHandler<T>
② 覆盖4个未实现的方法，其中setNonNullParameter为java程序设置数据到数据库的回调方法，getNullableResult为查询时 mysql的字符串类型转换成 java的Type类型的方法
③ 在MyBatis核心配置文件中进行注册
④ 测试转换是否正确
1，定义转换类继承类BaseTypeHandler
2，覆盖4个未实现的方法，其中setNonNullParameter为java程序设置数据到数据库的回调方法，getNullableResult为查询时 mysql的字符串类型转换成 java的Type类型的方法
public class DateTypeHandler extends BaseTypeHandler<Date> {
//将java类型 转换成 数据库需要的类型
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
long time = date.getTime();
preparedStatement.setLong(i,time);
}
//将数据库中类型 转换成 java类型
//s指数据库中要转换字段名称
public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
long aLong = resultSet.getLong(s);
Date date = new Date(aLong);
return date;
}
//将数据库中类型 转换成 java类型
public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
long aLong = resultSet.getLong(i);
Date date = new Date(aLong);
return date;
}
//将数据库中类型 转换成 java类型
public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
long aLong = callableStatement.getLong(i);
Date date = new Date(aLong);
return date;
}
}
3，在MyBatis核心配置文件中进行注册
sqlMapConfig.xml
省略properties标签，数据源环境，自定义别名
<!--自定义类型转换器-->
<typeHandlers>
<typeHandler handler="com.benjamin.handle.DateTypeHandler"></typeHandler>
</typeHandlers>
<!--加载映射文件-->
<mappers>
<mapper resource="com/benjamin/map/UserDateMapper.xml"></mapper>
</mappers>
UserDateMapper.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="com.benjamin.dao.UserDateMapper">
<!--插入操作-->
<insert id="save" parameterType="userdate">
insert into user values(#{id},#{username},#{password},#{birth})
</insert>
<!--查询操作-->
<select id="findById" parameterType="int" resultType="userdate">
select * from user where id=#{id}
</select>
<!--sql片段抽取-->
<sql id="selectUser">
select * from user
</sql>
</mapper>
4，测试转换
@Test
//自定义类型转换器,插入数据
public void test6() throws IOException {
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行操作 参数：namespace+id
UserDateMapper mapper = sqlSession.getMapper(UserDateMapper.class);
UserDate userDate = new UserDate(5,"wangwu","abc",new Date());
//insert into user values(?,?,?,?)
mapper.save(userDate);
//提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
@Test
//自定义类型转换器,读取数据
public void test7() throws IOException {
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行操作 参数：namespace+id
UserDateMapper mapper = sqlSession.getMapper(UserDateMapper.class);
//select * from user where id=?
UserDate byId = mapper.findById(5);
Date birth = byId.getBirth();
System.out.println(birth);//Sun Oct 16 10:52:27 CST 2022
//提交事务
//sqlSession.commit();
//释放资源
sqlSession.close();
}
plugins标签
MyBatis可以使用第三方的插件来对功能进行扩展，分页助手PageHelper是将分页的复杂操作进行封装，使用简单的方式即
可获得分页的相关数据
开发步骤：
① 导入通用PageHelper的坐标
② 在mybatis核心配置文件中配置PageHelper插件
③ 测试分页数据获取
1，导入通用PageHelper的坐标
<!--mybatis分页-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.7.5</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
2，在mybatis核心配置文件中配置PageHelper插件
sqlMapConfig.xml
<!--配置分页助手插件-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<!--配置分页方言-->
<property name="dialect" value="mysql"></property>
</plugin>
</plugins>
3，测试分页数据获取
@Test
//查询操作
public void test8() throws IOException {
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//设置分页相关参数 当前页+每页显示的条数
PageHelper.startPage(2,2);
//执行操作,获取mybatis底层实现的UserDateMapper的实现类
UserDateMapper mapper = sqlSession.getMapper(UserDateMapper.class);
List<UserDate> all = mapper.findAll();
//打印数据
//UserDate{id=3, username='wangwu', password='123', birth=Thu Jan 01 08:00:00 CST 1970}
//UserDate{id=4, username='tom', password='123456', birth=Thu Jan 01 08:00:00 CST 1970}
for (UserDate userDate : all) {
System.out.println(userDate);
}
//获得与分页相关参数
PageInfo<UserDate> pageInfo = new PageInfo<UserDate>(all);
System.out.println("当前页："+pageInfo.getPageNum());
System.out.println("每页显示条数："+pageInfo.getPageSize());
System.out.println("总条数："+pageInfo.getTotal());
System.out.println("总页数："+pageInfo.getPages());
System.out.println("上一页："+pageInfo.getPrePage());
System.out.println("下一页："+pageInfo.getNextPage());
System.out.println("是否是第一页："+pageInfo.isIsFirstPage());
System.out.println("是否是最后一页："+pageInfo.isIsLastPage());
//释放资源
sqlSession.close();
}
MyBatis核心配置文件常用标签
1、properties标签：该标签可以加载外部的properties文件
2、typeAliases标签：设置类型别名
3、environments标签：数据源环境配置标签
4、typeHandlers标签：配置自定义类型处理器
5、plugins标签：配置MyBatis的插件
Mybatis多表查询
一对一查询
1，创建Order和User实体
注意：如果实体类只定义了有参构造，会报argument type mismatch错误。因为mybatis底层是根据反射来定义，java中有参构造后会默认无视无参构造，而反射的Class.forName(“className”).newInstance();需要对应的类提供一个无参构造函数。不然就会报错
org.apache.ibatis.reflection.ReflectionException: Error instantiating class com.benjamin.domain.Order with invalid types (int,Date,double,UserDate) or values (1,Sun Oct 16 00:00:00 CST 2022,3000.0,1). Cause: java.lang.IllegalArgumentException: argument type mismatch
public class Order {
private int id;
private Date ordertime;
private double total;
//当前订单属于哪一个用户
private UserDate userdate;
}
public class UserDate {
private int id;
private String username;
private String password;
private Date birth;
}
2，创建OrderMapper接口
public interface OrderMapper {
public List<Order> findAll();
}
3，配置OrderMapper.xml
其中order对象的属性User有两种配置方法
一对一配置：使用resultMap和association标签做配置
<?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="com.benjamin.dao.OrderMapper">
<resultMap id="orderMap" type="order">
<!--手动指定字段与实体属性的映射关系
column: 数据表的字段名称
property：实体的属性名称
-->
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<!--方法1
<result column="uid" property="userdate.id"></result>
<result column="username" property="userdate.username"></result>
<result column="password" property="userdate.password"></result>
<result column="birth" property="userdate.birth"></result>-->
<!--
方法2
property: 当前实体(order)中的属性名称(private User user)
javaType: 当前实体(order)中的属性的类型(User)
-->
<association property="userdate" javaType="userdate">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birth" property="birth"></result>
</association>
</resultMap>
<!--查询操作-->
<select id="findAll" resultMap="orderMap">
SELECT *,o.id oid FROM orders o, user u WHERE o.uid = u.id
</select>
</mapper>
4，测试
@Test
//查询操作 1对1
public void test9() throws IOException {
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行操作
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Order> orders = mapper.findAll();
//打印数据
for (Order order : orders) {
//Order{id=1, ordertime=Sun Oct 16 00:00:00 CST 2022, total=3000.0, userdate=UserDate{id=1, username='zhangsan', password='123', birth=null}}
//Order{id=2, ordertime=Fri Oct 14 00:00:00 CST 2022, total=2000.0, userdate=UserDate{id=2, username='lisi', password='123', birth=null}}
System.out.println(order);
}
//释放资源
sqlSession.close();
}
一对多查询
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单
1，修改User实体
同样的坑，不要只在实体类中只加有参构造
public class UserDate {
private int id;
private String username;
private String password;
private Date birth;
private List<Order> orderList;
}
2，创建UserMapper接口
public interface UserDateMapper {
public List<UserDate> findAllUO();
3，配置UserMapper.xml
1对多，使用collection标签，将user实体List orderList属性中的order对象的属性一一与数据库的相应列关联映射
一对多配置：使用resultMap+collection标签做配置
<resultMap id="userMap" type="userdate">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birth" property="birth"></result>
<collection property="orderList" ofType="order">
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
</collection>
</resultMap>
<!--查询操作-->
<select id="findAllUO" resultMap="userMap">
SELECT *,o.id oid FROM user u,orders o WHERE o.uid = u.id;
</select>
4，测试
@Test
//查询操作 1对多 1个user对应多个订单
public void test10() throws IOException {
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行操作
UserDateMapper mapper = sqlSession.getMapper(UserDateMapper.class);
List<UserDate> allUO = mapper.findAllUO();
//打印数据
for (UserDate userDate : allUO) {
System.out.println(userDate);
}
//释放资源
sqlSession.close();
}
5，测试结果
UserDate{id=1, username='zhangsan', password='123', birth=null, orderList=[Order{id=1, ordertime=Sun Oct 16 00:00:00 CST 2022, total=3000.0, userdate=null}, Order{id=3, ordertime=Sun Oct 16 15:21:19 CST 2022, total=200.0, userdate=null}]}
UserDate{id=2, username='lisi', password='123', birth=null, orderList=[Order{id=2, ordertime=Fri Oct 14 00:00:00 CST 2022, total=2000.0, userdate=null}]}
多对多查询
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用。多对多查询的需求：查询用户同时查询出该用户的所有角色
1，创建Role实体，修改User实体
public class Role {
private int id;
private String roleName;
private String roleDesc;
}
public class UserDate {
private int id;
private String username;
private String password;
private Date birth;
private List<Order> orderList;//user对order为1对多，在多对对关系实现时，toString不加此字段
private List<Role> roleList;
}
2，添加UserMapper接口方法
public interface UserDateMapper {
public List<UserDate> findUR();
}
3，配置UserMapper.xml
依然和1对多时一样，使用collection标签来定义数据库字段与实体类字段的对应关系
多对多配置：使用resultMap+collection标签做配置
<resultMap id="urMap" type="userdate">
<id column="userid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birth" property="birth"></result>
<!--
property: 当前实体(order)中的属性名称(role)
javaType: 当前实体(order)中的属性的类型(Role)
column:数据库中列明
property：实体类中属性名
-->
<collection property="roleList" ofType="role">
<id column="roleId" property="id"></id>
<result column="roleName" property="roleName"></result>
<result column="roleDesc" property="roleDesc"></result>
</collection>
</resultMap>
<!--查询操作，多对多-->
<select id="findUR" resultMap="urMap">
SELECT * FROM USER, sys_user_role, sys_role WHERE user.`id`=sys_user_role.`userId` AND sys_role.`id`=sys_user_role.`roleId`
</select>
4，测试
@Test
//查询操作 多对多 多个user对应多个role
public void test11() throws IOException {
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行操作
UserDateMapper mapper = sqlSession.getMapper(UserDateMapper.class);
List<UserDate> allUO = mapper.findUR();
//打印数据
for (UserDate userDate : allUO) {
System.out.println(userDate);
}
//释放资源
sqlSession.close();
}
5，测试结果
UserDate{id=1, username='zhangsan', password='123', birth=null, roleList=[Role{id=1, roleName='院长', roleDesc='负责全面工作'}, Role{id=2, roleName='研究员', roleDesc='课程研发工作'}]}
UserDate{id=2, username='lisi', password='123', birth=null, roleList=[Role{id=2, roleName='研究员', roleDesc='课程研发工作'}, Role{id=3, roleName='讲师', roleDesc='授课工作'}]}
MyBatis的注解开发
@Insert：实现新增
@Update：实现更新
@Delete：实现删除
@Select：实现查询
@Result：实现结果集封装
@Results：可以与@Result 一起使用，封装多个结果集
@One：实现一对一结果集封装
@Many：实现一对多结果集封
MyBatis的简单增删改查，注解实现
1，定义UserAnnoMapper接口，在方法上添加增删改查的注解
public interface UserAnnoMapper {
@Insert("insert into user values(#{id},#{username},#{password},#{birth})")
public void save(UserDate user);
@Update("update user set username=#{username},password=#{password} where id=#{id}")
public void update(UserDate user);
@Delete("delete from user where id=#{id}")
public void delete(int id);
@Select("select * from user where id=#{id}")
public UserDate findById(int id);
@Select("select * from user")
public List<UserDate> findAll();
}
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>
<!--通过properties标签加载外部properties文件-->
<properties resource="jdbc.properties"></properties>
<!--数据源环境-->
<environments default="developement">
<environment id="developement">
<transactionManager type="JDBC"></transactionManager>
<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.benjamin.dao"></package>
</mappers>
</configuration>
3，测试类
public class MyBatisAnnoTest {
private UserAnnoMapper mapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(UserAnnoMapper.class);
}
@Test
public void testSave(){
UserDate user = new UserDate();
user.setUsername("jack");
user.setPassword("abc");
mapper.save(user);
}
@Test
public void testUpdate(){
UserDate user = new UserDate();
user.setId(5);
user.setUsername("lucy");
user.setPassword("123");
mapper.update(user);
}
@Test
public void testDelete(){
mapper.delete(5);
}
@Test
public void testFindById(){
UserDate user = mapper.findById(2);
//UserDate{id=2, username='lisi', password='123', birth=null, orderList=null}
System.out.println(user);
}
@Test
public void testFindAll(){
List<UserDate> all = mapper.findAll();
for (UserDate user : all) {
System.out.println(user);
}
}
}
MyBatis的注解实现复杂映射开发
实现复杂关系映射之前我们可以在映射文件中通过配置<resultMap>来实现，使用注解开发后，我们可以使用@Results注解
，@Result注解，@One注解，@Many注解组合完成复杂关系的配置
@Results
代替的是标签<resultMap>该注解中可以使用单个@Result注解，也可以使用@Result集
合。使用格式：@Results（{@Result（），@Result（）}）或@Results（@Result（））
@Resut
代替了<id>标签和<result>标签
@Result中属性介绍：
column：数据库的列名
property：需要装配的属性名
one：需要使用的@One 注解（@Result（one=@One）（）））
many：需要使用的@Many 注解（@Result（many=@many）（）））
@One （一对一）
代替了<assocation> 标签，是多表查询的关键，在注解中用来指定子查询返回单一对象。
@One注解属性介绍：
select: 指定用来多表查询的 sqlmapper
使用格式：@Result(column=" ",property="",one=@One(select=""))
@Many （多对一）
代替了<collection>标签, 是是多表查询的关键，在注解中用来指定子查询返回对象集合。
使用格式：@Result(property="",column="",many=@Many(select=""))
一对一查询
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
1，创建Order和User实体
public class UserDate {
private int id;
private String username;
private String password;
private Date birth;
}
public class Order {
private int id;
private Date ordertime;
private double total;
//当前订单属于哪一个用户
private UserDate userdate;
}
2，使用注解配置OrderMapper，UserAnnoMapper接口
OrderMapper
public interface OrderMapper {
//一对一，注解方法1
@Select("select *,o.id oid from orders o,user u where o.uid=u.id")
@Results({
@Result(column = "oid",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(column = "uid",property = "userdate.id"),
@Result(column = "username",property = "userdate.username"), @Result(column = "password",property = "userdate.password") }) public List<Order> findAllAnno();
//一对一，注解方法2
@Select("select * from orders")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(
property = "userdate", //要封装的属性名称
column = "uid", //根据哪个字段去查询user表的数据
javaType = UserDate.class, //要封装的实体类型
//select属性 代表查询哪个接口的方法获得数据
one = @One(select = "com.benjamin.dao.UserAnnoMapper.findById") ) }) public List<Order> findAllAnnoOTO();
}
一对一，注解方法2中的@One注解，用来指定子查询返回单一对象
UserAnnoMapper
public interface UserAnnoMapper {
@Select("select * from user where id=#{id}")
public UserDate findById(int id);
}
3，测试代码
public class MyBatisTest2 {
private OrderMapper mapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(OrderMapper.class);
}
@Test
public void testSave(){
List<Order> all = mapper.findAllAnno();
for (Order order : all) {
System.out.println(order);
}
}
@Test
public void testSave2(){
List<Order> all = mapper.findAllAnnoOTO();
for (Order order : all) {
System.out.println(order);
}
}
}
4，测试结果
Order{id=1, ordertime=Sun Oct 16 00:00:00 CST 2022, total=3000.0, userdate=UserDate{id=1, username='zhangsan', password='123', birth=null, orderList=null}}
Order{id=2, ordertime=Fri Oct 14 00:00:00 CST 2022, total=2000.0, userdate=UserDate{id=2, username='lisi', password='123', birth=null, orderList=null}}
Order{id=3, ordertime=Sun Oct 16 15:21:19 CST 2022, total=200.0, userdate=UserDate{id=1, username='zhangsan', password='123', birth=null, orderList=null}}
一对多查询
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
1，修改User实体
public class UserDate {
private int id;
private String username;
private String password;
private Date birth;
private List<Order> orderList;
}
public class Order {
private int id;
private Date ordertime;
private double total;
}
2，使用注解配置OrderMapper，UserAnnoMapper接口
UserAnnoMapper
public interface UserAnnoMapper {
@Select("select * from user")
@Results({
//column = "数据库字段",property = "实体类字段"
@Result(id = true, column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(column = "birth", property = "birth"),
@Result( property = "orderList",//要封装的属性名称 column = "id",//根据哪个字段去查询order表的数据 javaType = List.class, many = @Many(select = "com.benjamin.dao.OrderMapper.findById") )} ) public List<UserDate> findUserOrder();
}
OrderMapper
public interface OrderMapper {
@Select("select * from orders where uid=#{uid}")
public List<Order> findById();
}
3，测试代码
public class MyBatisTest2 {
private OrderMapper mapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(OrderMapper.class);
}
@Test
public void testSave2(){
List<Order> all = mapper.findAllAnnoOTO();
for (Order order : all) {
System.out.println(order);
}
}
}
4，测试结果
UserDate{id=1, username='zhangsan', password='123', birth=null, orderList=[Order{id=1, ordertime=Sun Oct 16 00:00:00 CST 2022, total=3000.0}, Order{id=3, ordertime=Sun Oct 16 15:21:19 CST 2022, total=200.0}]}
UserDate{id=2, username='lisi', password='123', birth=null, orderList=[Order{id=2, ordertime=Fri Oct 14 00:00:00 CST 2022, total=2000.0}]}
UserDate{id=3, username='wangwu', password='123', birth=null, orderList=[]}
UserDate{id=4, username='tom', password='123456', birth=null, orderList=[]}
多对多查询
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
1，创建Role实体，User实体
public class UserDate {
private int id;
private String username;
private String password;
private Date birth;
private List<Role> roleList;
}
public class Role {
private int id;
private String roleName;
private String roleDesc;
}
2，使用注解配置OrderMapper，UserAnnoMapper接口
UserAnnoMapper
public interface UserAnnoMapper {
@Select("select * from user")
@Results({
//column = "数据库字段",property = "实体类字段"
@Result(id = true, column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(column = "birth", property = "birth"),
@Result( property = "roleList",//要封装的属性名称 column = "id",//根据哪个字段去查询order表的数据 javaType = List.class, many = @Many(select = "com.benjamin.dao.RoleMapper.findByUId") )} ) public List<UserDate> findUserRole();
}
RoleMapper
public interface RoleMapper {
@Select("SELECT * FROM USER, sys_user_role, sys_role WHERE user.`id`=sys_user_role.`userId` AND sys_role.`id`=sys_user_role.`roleId` AND userId =#{userId}")
public List<Role> findByUId();
}
3，测试代码
public class MyBatisAnnoTest {
private UserAnnoMapper mapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(UserAnnoMapper.class);
}
@Test
public void testFindUserRole(){
List<UserDate> all = mapper.findUserRole();
for (UserDate user : all) {
System.out.println(user);
}
}
}
4，测试结果
UserDate{id=1, username='zhangsan', password='123', birth=null, roleList=[Role{id=1, roleName='院长', roleDesc='负责全面工作'}, Role{id=1, roleName='研究员', roleDesc='课程研发工作'}]}
UserDate{id=2, username='lisi', password='123', birth=null, roleList=[Role{id=2, roleName='研究员', roleDesc='课程研发工作'}, Role{id=2, roleName='讲师', roleDesc='授课工作'}]}
UserDate{id=3, username='wangwu', password='123', birth=null, roleList=[]}
UserDate{id=4, username='tom', password='123456', birth=null, roleList=[]}
@Select注解实现多表查询
Mybatis逆向过程只能单表查询，对于多表查询需要直接写sql，并且使用when标签条件条件语句
Mybatis-Plus @Select注解参数判空及SQL拼接
public interface EstimateOrderMapper extends Mapper<EstimateOrder> {
@Select({"<script>"+
"SELECT t1.OrderId,t1.DoorNo,t1.Score,t1.OrderTime,t1.Stat,t1.StartTime,t1.EndTime " +
"FROM estimate_order t1 " +
"INNER JOIN (SELECT DoorNo, MAX(OrderTime) AS max_time FROM estimate_order GROUP BY DoorNo) t2" +
" ON t1.DoorNo = t2.DoorNo AND t1.OrderTime = t2.max_time" +
"<when test='doorno!=null'>" +
"AND t1.doorno = #{doorno}"+
"</when>"+
"<when test='score!=null'>" +
"AND t1.score = #{score}"+
"</when>"+
"<when test='ordertime!=null'>" +
"AND t1.ordertime = #{ordertime}"+
"</when>"+
"<when test='stat!=null'>" +
"AND t1.stat = #{stat}"+
"</when>"+
"<when test='starttime!=null'>" +
"AND t1.starttime = #{starttime}"+
"</when>"+
"<when test='endtime!=null'>" +
"AND t1.endtime = #{endtime}"+
"</when>"+
"</script>"})
List<EstimateOrder> queryEveryDoorNoInfos(EstimateOrderVO estimateOrderVO);
@Select({"<script>"+
"SELECT t1.OrderId,t1.DoorNo,t1.Score,t1.OrderTime,t1.Stat,t1.StartTime,t1.EndTime " +
"FROM estimate_order t1 " +
"where 1=1" +
"<when test='doorno!=null'>" +
"and t1.doorno = #{doorno}"+
"</when>"+
"order by OrderTime DESC"+
"</script>"})
List<EstimateOrder> queryDoorNoHistoryInfos(EstimateOrderVO estimateOrderVO);
}
条件查询时，非空和null值判断
@Select("<script>select DeviceSn,DoorNo,DoorId,CarriageId,CarriageName,MetroName,MetroId,LineId,LineName,AreaId,AreaName from door_data_live as t3 where DoorNo is not null" +
"<if test = 'areaid!=null'> and t3.AreaId = #{areaid}</if>" +
"<if test = 'lineid!=null'> and t3.LineId = #{lineid}</if>" +
"<if test = 'metroid!=null'> and t3.MetroId = #{metroid}</if>" +
"<if test = 'carriageid!=null'> and t3.CarriageId = #{carriageid}</if>" +
"<if test = 'doorno!=null'> and t3.DoorNo like concat('%',#{doorno},'%')</if>" +
"<if test = 'sn!=null'> and t3.DeviceSn = #{sn}</if>" +
"<if test = 'lineids!=null'> and t3.LineId in " +
"<foreach item='sn' index='index' collection='lineids' open='(' separator=',' close=')'>" +
"#{sn}" +
"</foreach>" +
"</if>" +
//集合非空并且元素个数>0判断
"<if test = 'metroids!=null and metroids.length > 0'> and t3.MetroId in " +
"<foreach item='sn' index='index' collection='metroids' open='(' separator=',' close=')'>" +
"#{sn}" +
"</foreach>" +
"</if>" +
"<if test = 'carriageids!=null'> and t3.CarriageId in " +
"<foreach item='sn' index='index' collection='carriageids' open='(' separator=',' close=')'>" +
"#{sn}" +
"</foreach>" +
"</if>" +
"</script>")
List<BaseShortInfoVO> selectDoorNosByIds(@Param("areaid") Integer areaid, @Param("lineid") Integer lineid, @Param("metroid") Integer metroid, @Param("carriageid") Integer carriageid, @Param("doorno") String doorno, @Param("sn") String sn, @Param("lineids") List<Integer> lineids, @Param("metroids") List<Integer> metroids, @Param("carriageids") List<Integer> carriageids);
SSM框架整合
整合思路
实现步骤
1，将SqlSessionFactory配置到Spring容器中
applicationContext.xml
<!--加载jdbc.properties-->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!--配置数据源-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!--配置MyBatis的SqlSessionFactory,用于生成SqlSession,从而生成Mapper接口的实现类-->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<!--加载mybatis核心配置文件sqlMapConfig.xml-->
<property name="configLocation" value="classpath:sqlMapConfig.xml"/>
</bean>
2，扫描Mapper，让Spring容器产生Mapper实现类
applicationContext.xml
<!--配置Mapper扫描-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!--指定接口所在的包-->
<property name="basePackage" value="com.benjamin.mapper"/>
</bean>
截止目前sqlMapConfig.xml中properties，environments，mappers已经全部与spring整合
<?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标签加载外部properties文件-->
<properties resource="jdbc.properties"></properties> 交由spring管理
<!--自定义别名-->
<typeAliases>
<typeAlias type="com.benjamin.domain.User" alias="user"></typeAlias>
<typeAlias type="com.benjamin.domain.UserDate" alias="userdate"></typeAlias>
<typeAlias type="com.benjamin.domain.Order" alias="order"></typeAlias>
<typeAlias type="com.benjamin.domain.Role" alias="role"></typeAlias>
</typeAliases>
<!--自定义类型转换器-->
<!--<typeHandlers>
<typeHandler handler="com.benjamin.handle.DateTypeHandler"></typeHandler>
</typeHandlers>-->
<!--配置分页助手插件-->
<!--<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<!–配置分页方言–>
<property name="dialect" value="mysql"></property>
</plugin>
</plugins>-->
<!--数据源环境-->
<environments default="developement"> 交由spring管理
<environment id="developement">
<transactionManager type="JDBC"></transactionManager>
<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>
<!--加载映射文件xml实现方式-->
<!--<mappers> 交由spring管理
<mapper resource="com/benjamin/map/UserMapper.xml"></mapper>
<mapper resource="com/benjamin/map/UserDateMapper.xml"></mapper>
<mapper resource="com/benjamin/map/OrderMapper.xml"></mapper>
</mappers>-->
<!--加载映射关系，此配置用于注解开发-->
<mappers> 交由spring管理
<!--指定接口所在的包-->
<package name="com.benjamin.dao"></package>
</mappers>
</configuration>
3，配置声明式事务控制
applicationContext.xml
<!--配置声明式事务控制-->
<bean id="transacionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!--通知 事务的增强-->
<tx:advice id="txAdvice" transaction-manager="transacionManager">
<tx:attributes>
<tx:method name="*"/>
</tx:attributes>
</tx:advice>
<!--配置事务的aop织入-->
<aop:config>
<aop:pointcut id="txPointcut" expression="execution(* com.itheima.service.impl.*.*(..))"/>
<aop:advisor advice-ref="txAdvice" pointcut-ref="txPointcut"/>
</aop:config>
4，.修改Service实现类代码
@Service("accountService")
public class AccountServiceImpl implements AccountService {
@Autowired
private AccountMapper accountMapper;
public void save(Account account) {
accountMapper.save(account);//service层调用dao层（mapper的实现类）
}
public List<Account> findAll() {
return accountMapper.findAll();
}
}
Service层在整合前是如下的
@Service("accountService")
public class AccountServiceImpl implements AccountService {
public List<Account> findAll() {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sessionFactory.openSession();
//mybatis实现了UserMapper
AccountMapper mapper = sqlSession.getMapper(AccountMapper.class);
List<Account> accountList = mapper.findAll();
return accountList;
}
}
example实例解析
mybatis中关于example类详解mybatis的Example[Criteria]的使用
mybatis的逆向工程中会生成实例及实例对应的example，example用于添加条件，相当于where后面的部分
查询
主键查询
1,selectByPrimaryKey()根据主键查询
@Autowired
XxxMapper xxxMapper;
//相当于select * from user where id = 100
User user = xxxMapper.selectByPrimaryKey(100);
条件查询
2,添加条件查询selectByExample() 和 selectByExampleWithBLOGs()
Example example = new Example(EstimateOrder.class);
Example.Criteria criteria = example.createCriteria();//Criteria是Example类的一个静态内部类，用来存储查询条件语句
//where doorno=""
criteria.andEqualTo("doorno", estimateOrderVO.getDoorno());
//order by OrderTime DESC
example.setOrderByClause("OrderTime DESC");
//createtime >= starttime
criteria.andGreaterThanOrEqualTo("createtime", projectSearchVO.getStarttime());
//createtime <= endtime
criteria.andLessThanOrEqualTo("createtime", projectSearchVO.getEndtime());
List<EstimateOrder> estimateOrders = estimateOrderMapper.selectByExample(example);
注：在iBator逆向工程生成的文件XxxExample.java中包含一个static的内部类Criteria，Criteria中的方法是定义SQL 语句where后的查询条件。
in查询
3,添加in条件查询,where field in(a,b,c)
Example example = new Example(EstimateOrder.class);
Example.Criteria criteria = example.createCriteria();
List<String> projectName = new ArrayList();
lives.stream()
.forEach(item->projectName.add(item.getAreaname()+item.getLinename()));
List<String> collect = projectName.stream().distinct().collect(Collectors.toList());
criteria.andIn("projectname", collect);
like查询
Example example = new Example(BiPublishRecod.class);
example.setOrderByClause(" Id DESC");
Example.Criteria criteria = example.createCriteria();
if (!StringUtils.isBlank(biPublishSearchVO.getName())) {
criteria.andLike("pagename", "%" + biPublishSearchVO.getName() + "%");
}
!=不等于查询
Example doorCategoryExample = new Example(DoorCategory.class);
Example.Criteria doorCategorycriteria = doorCategoryExample.createCriteria();
doorCategorycriteria.andNotEqualTo("isdel", true);
插入
public Object saveEstimateOrderInfo(EstimateOrder estimateOrder) {
success = estimateOrderMapper.insert(estimateOrder);
//相当于：insert into user(ID,username,password,email) values ('dsfgsdfgdsfgds','admin','admin','wyw@126.com');
return success;
}
修改
public Object saveEstimateOrderInfo(EstimateOrder estimateOrder) {
Example example = new Example(EstimateOrder.class);
Example.Criteria criteria1 = example.createCriteria();
//条件过滤
criteria1.andEqualTo("orderid", estimateOrder.getOrderid());
//updateByExample()更新所有的字段，包括字段为null的也更新
//建议使用 updateByExampleSelective()更新想更新的字段
success = estimateOrderMapper.updateByExampleSelective(estimateOrder, example);
return success;
}
删除
public Object deleteEstimateOrderInfo(EstimateOrder estimateOrder) {
Example example = new Example(EstimateOrder.class);
Example.Criteria criteria1 = example.createCriteria();
//条件过滤
criteria1.andEqualTo("orderid", estimateOrder.getOrderid());
int delete = estimateOrderMapper.deleteByExample(example);
return delete;
}
mybatis拦截器实现分页
PageInterceptor拦截器类
package com.meteorological.interceptor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import com.meteorological.bean.BasePage;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.ReflectorFactory;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.factory.ObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
/*
* 此文件为mybatis SQL拦截器,用于做数据分页
*/
/**
* sql拦截 数据分页
*/
@Intercepts({
@Signature(method = "query", type = Executor.class, args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(method = "prepare", type = StatementHandler.class, args = {Connection.class, Integer.class})
})
public class PageInterceptor implements Interceptor {
private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
private static final ReflectorFactory DEFAULT_REFLECTOR_FACTORY = new DefaultReflectorFactory();
// private static final String dialect = "xugu";
private String pagePatten = "InPage"; // 需要进行分页操作的字符串正则表达式
public String getPageNamePattern() {
return pagePatten;
}
public void setPageNamePattern(String pagePatten) {
this.pagePatten = pagePatten;
}
private boolean isSelect(String sql) {
return !StringUtils.isEmpty(sql) && sql.toUpperCase().trim().startsWith("SELECT");
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
String selectId = mappedStatement.getId();
String[] split = selectId.split("\\.");
String methodName = split[split.length-1];
if (!methodName.contains(pagePatten)) return invocation.proceed();
BoundSql boundSql = statementHandler.getBoundSql();
if (isSelect(boundSql.getSql())) {
Object params = boundSql.getParameterObject();
if (params instanceof Map) {
return complexParamsHandler(invocation, boundSql, (Map<?, ?>) params);
} else if (params instanceof BasePage) { // 单个参数且为Page，则表示该操作需要进行分页处理
return simpleParamHandler(invocation, boundSql, (BasePage) params);
}
}
return invocation.proceed();
}
private Object simpleParamHandler(Invocation invocation, BoundSql boundSql, BasePage page) throws Throwable {
return pageHandlerExecutor(invocation, boundSql, page);
}
@Override
public Object plugin(Object target) {
// 当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
} else {
return target;
}
}
@Override
public void setProperties(Properties properties) {
Interceptor.super.setProperties(properties);
}
private Object complexParamsHandler(Invocation invocation, BoundSql boundSql, Map<?, ?> params) throws Throwable {
//判断参数中是否指定分页
if (containsPage(params)) {
return pageHandlerExecutor(invocation, boundSql, (BasePage) params.get("page"));
} else {
return invocation.proceed();
}
}
public String getCountSql(String sql) {
return "select count(0) from (" + sql + ") as total";
}
public String getPageSql(String sql, BasePage page) {
if(page.getPageTo()<=0){
page.setPageTo(1);
}
if(page.getMaxRowsPerPage()<=0){
page.setMaxRowsPerPage(20);
}
int startRow = (page.getPageTo()-1)*page.getMaxRowsPerPage();
if(startRow>=page.getTotalRecord()){
page.setPageTo(1);
startRow=0;
}
return sql+" limit "+startRow+", "+page.getMaxRowsPerPage();
}
private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,
Object parameterObject) throws SQLException {
ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
parameterHandler.setParameters(ps);
}
/**
* 获取数据总长度
* @param connection java.sql.Connection
* @param mappedStatement ibatis.mapping.MappedStatement
* @param boundSql ibatis.mapping.BoundSql
* @return 数据总长度
*/
private int getTotalSize(Connection connection, MappedStatement mappedStatement, BoundSql boundSql) {
String countSql = getCountSql(boundSql.getSql());
PreparedStatement countStmt;
ResultSet rs;
List<AutoCloseable> closeableList = new ArrayList<>();
try {
countStmt = connection.prepareStatement(countSql);
BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql,
boundSql.getParameterMappings(), boundSql.getParameterObject());
setParameters(countStmt, mappedStatement, countBS, boundSql.getParameterObject());
rs = countStmt.executeQuery();
if (rs.next()) {
return rs.getInt(1);
}
closeableList.add(countStmt);
closeableList.add(rs);
} catch (SQLException e) {
System.out.println(e.getErrorCode());
} finally {
for (AutoCloseable closeable : closeableList) {
try {
if (closeable != null)
closeable.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return 0;
}
/**
* 执行数据分页
* @param invocation ibatis.plugin.Invocation
* @param boundSql ibatis.mapping.BoundSql
* @param page 分页数据承载对象基类
* @return Object
* @throws Throwable invocation.proceed()
*/
private Object pageHandlerExecutor(Invocation invocation, BoundSql boundSql, BasePage page) throws Throwable {
Connection connection = (Connection) invocation.getArgs()[0];
MetaObject statementHandler = MetaObject.forObject(invocation.getTarget(), DEFAULT_OBJECT_FACTORY,
DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_REFLECTOR_FACTORY);
int maxSize = getTotalSize(connection, (MappedStatement) statementHandler.getValue("delegate.mappedStatement"),
boundSql);
page.setTotalRecord(maxSize);
String wrapperSql = getPageSql(boundSql.getSql(), page);
MetaObject boundSqlMeta = MetaObject.forObject(boundSql, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY,
DEFAULT_REFLECTOR_FACTORY);
boundSqlMeta.setValue("sql", wrapperSql);
return invocation.proceed();
}
private boolean containsPage(Map<?, ?> params) {
if(params==null){
return false;
}else if(!params.containsKey("page")){
return false;
}
Object page = params.get("page");
if(page==null){
return false;
}else return page instanceof BasePage;
}
}
PageExecutor类
package com.meteorological.interceptor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.cursor.Cursor;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.transaction.Transaction;
public class PageExecutor implements Executor {
private Executor executor;
private String pattern;
public PageExecutor(Executor executor, String pattern) {
this.executor = executor;
this.pattern = pattern;
}
@Override
public int update(MappedStatement mappedStatement, Object parameter) throws SQLException {
return executor.update(mappedStatement, parameter);
}
/**
* 获取总数
* @param ms
* @param parameter
* @return
*/
private int getCount(MappedStatement ms, Object parameter) {
BoundSql bsql = ms.getBoundSql(parameter);
String sql = bsql.getSql();
String countSql = getCountSql(sql);
Connection connection = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
connection = ms.getConfiguration().getEnvironment().getDataSource()
.getConnection();
stmt = connection.prepareStatement(countSql);
rs = stmt.executeQuery();
if (rs.next())
return rs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (connection != null && !connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return 0;
}
private String getCountSql(String sql) {
return " SELECT count(*) "
+ removeSelect(removeOrders(sql));
}
// 去除sql语句中select子句
private static String removeSelect(String hql) {
int beginPos = hql.toLowerCase().indexOf("from");
if (beginPos < 0) {
throw new IllegalArgumentException(" hql : " + hql + " must has a keyword 'from'");
}
return hql.substring(beginPos);
}
protected String removeOrders(String sql) {
Pattern p = Pattern.compile("ORDER\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
StringBuffer sb = new StringBuffer();
while (m.find()) {
m.appendReplacement(sb, "");
}
m.appendTail(sb);
return sb.toString();
}
/**
* 修改返回值类型
* @param rows
* @param ms
* @param parameter
* @param rowBounds
* @return
*/
private <E> List<E> pageResolver(List<E> rows, MappedStatement ms,
Object parameter, RowBounds rowBounds) {
String msid = ms.getId();
// 如果需要分页查询，修改返回类型为Page对象
if (msid.matches(pattern)) {
int count = getCount(ms, parameter);
int offset = rowBounds.getOffset();
int pagesize = rowBounds.getLimit();
return null;
}
return rows;
}
@Override
public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler,
CacheKey cacheKey, BoundSql boundSql) throws SQLException {
RowBounds rb = new RowBounds(rowBounds.getOffset(), rowBounds.getLimit());
List<E> rows = executor.query(ms, parameter, rowBounds, resultHandler,
cacheKey, boundSql);
return pageResolver(rows, ms, parameter, rb);
}
@Override
public <E> List<E> query(MappedStatement mappedStatement, Object parameter, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException {
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
return query(mappedStatement, parameter, rowBounds, resultHandler,
executor.createCacheKey(mappedStatement, parameter, rowBounds, boundSql),
boundSql);
}
@Override
public <E> Cursor<E> queryCursor(MappedStatement mappedStatement, Object o, RowBounds rowBounds) throws SQLException {
return null;
}
@Override
public List<BatchResult> flushStatements() throws SQLException {
return executor.flushStatements();
}
@Override
public void commit(boolean required) throws SQLException {
executor.commit(required);
}
@Override
public void rollback(boolean required) throws SQLException {
executor.rollback(required);
}
@Override
public CacheKey createCacheKey(MappedStatement mappedStatement, Object parameterObject, RowBounds rowBounds, BoundSql boundSql) {
return executor
.createCacheKey(mappedStatement, parameterObject, rowBounds, boundSql);
}
@Override
public boolean isCached(MappedStatement mappedStatement, CacheKey cacheKey) {
return executor.isCached(mappedStatement, cacheKey);
}
@Override
public void clearLocalCache() {
executor.clearLocalCache();
}
@Override
public void deferLoad(MappedStatement mappedStatement, MetaObject resultObject, String property, CacheKey key, Class<?> targetType) {
executor.deferLoad(mappedStatement, resultObject, property, key, targetType);
}
@Override
public Transaction getTransaction() {
return executor.getTransaction();
}
@Override
public void close(boolean forceRollback) {
executor.close(forceRollback);
}
@Override
public boolean isClosed() {
return executor.isClosed();
}
@Override
public void setExecutorWrapper(Executor executor) {
this.executor = executor;
}
}
mybatis-config.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日志 -->
<!--配置 MyBatis 输出 SQL 日志的方式为标准输出（STDOUT_LOGGING）方式。这样配置后，可以在控制台上看到 MyBatis 执行的 SQL 语句以及其他相关的日志信息-->
<setting name="logImpl" value="STDOUT_LOGGING" />
</settings>
<plugins>
<!--配置自定义插件，并指定其拦截器类。该插件用于实现分页功能-->
<plugin interceptor="com.meteorological.interceptor.PageInterceptor">
<!--Mapper 接口中定义的方法名中包含 “page” 字符串会被拦截-->
<property name="patten" value="^.*page.*$"/>
</plugin>
</plugins>
</configuration>
BasePage基类
import java.io.Serializable;
import java.util.*;
/**
* 分页数据承载对象基类
*/
public class BasePage implements Serializable {
protected static final long serialVersionUID = 5136213157391895517L;
/**
* 起始页码
*/
protected int pageTo = 1;
/**
* 每页显示的最大记录数
*/
protected int maxRowsPerPage = 10;
/**
* 总记录数
*/
protected int totalRecord;
/**
* 当前页记录
*/
protected List<?> data;
public int getPageTo() {
return pageTo;
}
public void setPageTo(int pageTo) {
this.pageTo = pageTo;
}
public int getMaxRowsPerPage() {
return maxRowsPerPage;
}
public void setMaxRowsPerPage(int maxRowsPerPage) {
this.maxRowsPerPage = maxRowsPerPage;
}
public int getTotalRecord() {
return totalRecord;
}
public void setTotalRecord(int totalRecord) {
this.totalRecord = totalRecord;
}
public List<?> getData() {
return data;
}
public BasePage setData(List<?> data) {
this.data = data;
return this;
}
}
Page继承BasePage
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.Data;
import lombok.EqualsAndHashCode;
import java.util.List;
/**
* 数据分页承载对象
*/
@EqualsAndHashCode(callSuper = true)
@JsonInclude(JsonInclude.Include.NON_NULL)
@Data
public class Page extends BasePage {
private static final long serialVersionUID = -6190845403265328029L;
private boolean isFirstPage = true;//是否是第一页
private boolean isLastPage = false;//是否是最后一页
private int pageCount = 0;//当前页总记录数
private int totalPage = 0;//总页数
private int prePage = 1;//上一页页码
private int nextPage = 1;//下一页页码
public Page() {
super();
}
public Page(int pageTo, int maxRowsPerPage) {
super();
setPageTo(pageTo);
setMaxRowsPerPage(maxRowsPerPage);
}
@Override
public Page setData(List<?> data){
super.setData(data);
if(data!=null && data.size()>0){
pageCount = data.size();
isFirstPage= this.pageTo == 1;
//总页数
totalPage = (int)Math.ceil(totalRecord/(double)maxRowsPerPage);
//是否是最后一页
isLastPage = pageTo == totalPage;
//是否是第一页
if(isFirstPage){
prePage = 1;
}else{
prePage = pageTo-1;
}
if(isLastPage){
nextPage = 1;
}else{
nextPage = pageTo+1;
}
}else{
isLastPage = true;
}
return this;
}
/**只要get方法**/
public boolean isFirstPage() {
return isFirstPage;
}
public boolean isLastPage() {
return isLastPage;
}
public int getPageCount() {
return pageCount;
}
public int getTotalPage() {
return totalPage;
}
public int getPrePage() {
return prePage;
}
public int getNextPage() {
return nextPage;
}
}
拦截器通过拦截mapper中方法名带有InPage的方法去修改原sql加分页
@Service
public class RadarImageService implements IRadarImageService {
private final IRadarImageMapper radarImageMapper;
@Autowired
public RadarImageService(IRadarImageMapper radarImageMapper) {
this.radarImageMapper = radarImageMapper;
}
@Override
public Page getRadarImageByTimeInPage(String station, String startTime, Integer pageTo) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHH");
try {
Page page = new Page();
page.setPageTo(pageTo);
List<RadarImageItem> list = radarImageMapper.findByTimeInPage(station, page, sdf.parse(startTime));
page.setData(list);
return page;
} catch (ParseException ex) {
ex.printStackTrace();
return null;
}
}
}
mapper中需要带上@Param(“page”) Page page
@Mapper
public interface IRadarImageMapper {
List<RadarImageItem> findByTimeInPage(String station, @Param("page") Page page, Date startTime);
}
xml配置文件
<select id="findByTimeInPage" resultMap="RadarImageItem">
SELECT Id, time, degree, no, station, path, filename, product FROM RADAR_IMG
<where>
time >= #{startTime}
AND
station = #{station}
</where>
ORDER BY time DESC, degree, no
</select>
PageHelper实现分页
查询数据库前，PageHelper拦截查询请求添加分页参数limit pageTo,pageSize,并通过select count(*) from table where …查询原不加limit sql执行后获取的总条数进而填充PageInfo
1，pom文件引入依赖
<!--分页助手启动器-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.10</version>
</dependency>
2，配置拦截器插件
通过 yml 配置文件进行配置
# PageHelper 分页插件配置
pagehelper:
helper-dialect: mysql
reasonable: true
support-methods-arguments: true
params: count=countsql
3，service层代码
public Object query(ProjectSearchVO projectSearchVO) {
Example example1 = new Example(DoorProblem.class);
Example.Criteria criteria1 = example1.createCriteria();
if(projectSearchVO!=null && projectSearchVO.getProjectid()!=null){
//条件过滤
criteria1.andEqualTo("projectid", projectSearchVO.getProjectid());
// 设置第几条记录开始，多少条记录为一页
PageHelper.startPage(projectSearchVO.getPageNumber() != null ? projectSearchVO.getPageNumber() : 1, projectSearchVO.getPageSize() != null ? projectSearchVO.getPageSize() : 10);
List<DoorProblem> doorProblems = DoorProblemMapper.selectByExample(example1);
//分页的详细信息PageInfo包括当前页，下一页，总页数，总条数等
PageInfo<DoorProblem> pageInfo = new PageInfo<>(doorProblems);
return pageInfo;
}else{
return null;
}
}
PageHelper分页DO转VO
LambdaQueryWrapper<ApplyOrderDO> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.lt(ApplyOrderDO::getCreateTime, endDate);
queryWrapper.gt(ApplyOrderDO::getCreateTime, startDate);
queryWrapper.orderByDesc(ApplyOrderDO::getCreateTime);
int pageInt = page != null && page > 0 ? page : 1;
int pageSizeInt = pageSize != null && pageSize > 0 ? pageSize : 10;
PageHelper.startPage(pageInt,pageSizeInt);
List<ApplyOrderDO> applyOrderDOS = applyOrderMapper.selectList(queryWrapper);
PageInfo<ApplyOrderDO> pageInfo = new PageInfo<>(applyOrderDOS);
PageInfo<ApplyOrderVO> newpageInfo = new PageInfo<>();
org.springframework.beans.BeanUtils.copyProperties(pageInfo, newpageInfo);
//return Result.success(applyOrderVOPage);
return Result.success(newpageInfo);
mybatisplus分页和DO转VO
service层
@Override
public Result<Object> getAllApplyOrdersByTimeRange(String timeRange, Integer page, Integer pageSize) {
String dateString = timeRange.substring(1, timeRange.length() - 1); // 去掉中括号
String[] dateArray = dateString.split(",");
SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
Date startDate;
Date endDate;
try {
startDate = format.parse(dateArray[0]);
endDate = format.parse(dateArray[1]);
} catch (ParseException e) {
return Result.error(CodeMsg.ORDER_TIME_FORMAT);
}
//admin
LambdaQueryWrapper<ApplyOrderDO> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.lt(ApplyOrderDO::getCreateTime, endDate);
queryWrapper.gt(ApplyOrderDO::getCreateTime, startDate);
queryWrapper.orderByDesc(ApplyOrderDO::getCreateTime);
int pageInt = page != null && page > 0 ? page : 1;
int pageSizeInt = pageSize != null && pageSize > 0 ? pageSize : 10;
Page<ApplyOrderDO> pageDo = new Page<>(pageInt, pageSizeInt);
Page<ApplyOrderDO> applyOrderDOPage = applyOrderMapper.selectPage(pageDo, queryWrapper);
// DO 转 VO （Page对象）
Page<ApplyOrderVO> applyOrderVOPage = ApplyOrderMapper.INSTANCE.toVOPage(applyOrderDOPage);
return Result.success(applyOrderVOPage);
}
ApplyOrderMapper用于DO与VO转换
import cma.sxqxgxw.order.VO.ApplyOrderVO;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.mapstruct.Mapper;
import org.mapstruct.Mapping;
import org.mapstruct.ReportingPolicy;
import org.mapstruct.factory.Mappers;
/*
* 本文件创建的位置要根据转换目标来定；比如是DO转VO，则写在DO的目录里，如果是DTO转DO，则写到DTO下
*/
/**
* 注意：unmappedTargetPolicy
* ERROR：任何未映射的目标属性都将使构建失败–这可以避免意外的未映射字段
* WARN：（默认）构建期间的警告消息
* IGNORE：无输出或错误
* 更新VO或DO类时，需要重新clean项目，因为需要重新生成do转vo的工具类
*/
@Mapper(unmappedTargetPolicy = ReportingPolicy.ERROR)
public interface ApplyOrderMapper {
ApplyOrderMapper INSTANCE = Mappers.getMapper(ApplyOrderMapper.class);
ApplyOrderVO toVO(ApplyOrderDO applyOrderDO);
@Mapping(target = "optimizeJoinOfCountSql", ignore = true)
Page<ApplyOrderVO> toVOPage(Page<ApplyOrderDO> page);
}
DO实体类
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.Size;
import java.util.List;
/**
* 离线数据申请单实体类
*/
@EqualsAndHashCode(callSuper = true)
@Data
@TableName("APPLY_ORDER_INFO")
public class ApplyOrderDO extends BaseDO {
/**
* 主键
*/
@TableId(value = "ID", type = IdType.ASSIGN_UUID)
private String id;
/**
* 申请单号
*/
@TableField(value = "ORDER_ID")
private String orderId;
/**
* 用户ID
*/
@TableField(value = "USER_ID")
@NotBlank
private String userId;
/**
* 用户姓名
*/
@TableField(value = "USER_NAME")
@NotBlank
private String userName;
/**
* 申请人联系电话
*/
@TableField(value = "USER_PHONE_NUM")
private String userPhoneNum;
/**
* 申请人所在单位
*/
@TableField(value = "USER_COMPANY")
private String userCompany;
/**
* 更新人ID
*/
@TableField(value = "UPDATE_USER_ID")
private String updateUserId;
/**
* 更新人姓名
*/
@TableField(value = "UPDATE_USER_NAME")
private String updateUserName;
/**
* 更新人联系电话
*/
@TableField(value = "UPDATE_DEAL_MOBILE")
private String updateDealMobile;
/**
* 申请单状态(0申请中、1已取消、2申请通过、3申请驳回)
*/
@TableField(value = "ORDER_STATUS")
private Integer orderStatus;
/**
* 流程附件描述或驳回原因
*/
@TableField(value = "REMARKS")
private String remarks;
/**
* 处理状态(未受理、处理中、已处理)
*/
@TableField(value = "DEAL_STATUS")
private Integer dealStatus;
/**
* Action: 订单所在流程节点(创建申请单->审核申请单->数据发放->结束)
*/
@TableField(value = "ORDER_PROCESS_NODE")
private Integer orderProcessNode;
@TableField(value = "USER_REAL_NAME")
private String userRealName;
@TableField(value = "USER_NACK_NAME")
private String userNackName;
@TableField(value = "MODIFIED_USER_REAL_NAME")
private String modifiedUserRealName;
@TableField(value = "MODIFIED_USER_NACK_NAME")
private String modifiedUserNackName;
}
VO实体类
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.Data;
import lombok.EqualsAndHashCode;
import java.util.Date;
/**
* 订单DTO
*/
@JsonInclude(JsonInclude.Include.NON_NULL)
@EqualsAndHashCode
@Data
public class ApplyOrderVO {
/**
* 主键
*/
//private String id;
/**
* 申请单号
*/
private String orderId;
/**
* 用户ID
*/
private String userId;
/**
* 用户姓名
*/
private String userName;
/**
* 申请人联系电话
*/
private String userPhoneNum;
/**
* 申请人所在单位
*/
private String userCompany;
/**
* 更新人ID
*/
private String updateUserId;
/**
* 更新人姓名
*/
private String updateUserName;
/**
* 更新人联系电话
*/
private String updateDealMobile;
/**
* 申请单状态(0申请中、1已取消、2申请通过、3申请驳回)
*/
private Integer orderStatus;
/**
* 流程附件描述或驳回原因
*/
private String remarks;
/**
* 创建时间
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date createTime;
/**
* 更新时间
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date updateTime;
/**
* 处理状态(未受理、处理中、已处理)
*/
private Integer dealStatus;
/**
* Action: 订单所在流程节点(创建申请单->审核申请单->数据发放->结束)
*/
private Integer orderProcessNode;
private String userRealName;
private String userNackName;
private String modifiedUserRealName;
private String modifiedUserNackName;
}
将集合传入分页对象中，假分页
public Object query(EstimateOrderVO estimateOrderVO) {
Example example = new Example(EstimateOrder.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo("doorno", estimateOrderVO.getDoorno());
example.setOrderByClause("OrderTime DESC");
List<EstimateOrder> estimateOrders = estimateOrderMapper.selectByExample(example);
int start = estimateOrderVO.getPageNumber()==null ? 1 : estimateOrderVO.getPageNumber();
int pageSize = estimateOrderVO.getPageSize()==null ? 10 : estimateOrderVO.getPageSize();
Page page = new Page(start,pageSize);
int total = estimateOrders.size();
page.setTotal(total);
int startIndex = (start - 1) * pageSize;
int endIndex = Math.min(startIndex + pageSize,total);
if(startIndex>endIndex){
page.addAll(new ArrayList());
PageInfo pageInfo = new PageInfo<>(page);
return pageInfo;
}else{
page.addAll(estimateOrders.subList(startIndex,endIndex));
PageInfo pageInfo = new PageInfo<>(page);
return pageInfo;
}
}
in后带的属性值太多，导致sql报错
解决方法1：临时表将in中数据存入表中，然后和sql联表查询
SELECT
AI.station_id_c,
AI.element_id,
AI.element_name,
AI.element_value,
AI.alarm_level,
AI.alarm_desc,
AI.date_time
FROM (
SELECT DISTINCT station_id_c FROM tmp_station -- 将要代替的查询结果集
) TS
JOIN ALARM_INFO AI
ON TS.station_id_c = AI.station_id_c
WHERE AI.modified_time > '2023-05-22 17:03:00'
ORDER BY AI.date_time DESC;
解决方法2：station_id_c in(a,b) or station_id_c in(c,d)
这种方式需要将查询条件modified_time > #{dt} AND ELEMENT_NAME = #{element}在每个or中都写一次。foreach标签中解析#{sta.stationIdC}有误，需要修改为’${sta.stationIdC}'。开始传的是List泛型为String一直解析不了，后来改为了List泛型为自定义对象
<select id="findAlarmInfosDescInPage" resultMap="alarmInfoItem">
SELECT
ALARM_INFO.station_id_c,
ALARM_INFO.element_id,
ALARM_INFO.element_name,
ALARM_INFO.element_value,
ALARM_INFO.alarm_level,
ALARM_INFO.alarm_desc,
ALARM_INFO.date_time
FROM ALARM_INFO
where
modified_time > #{dt} AND ELEMENT_NAME = #{element}
<if test="stations != null and stations.size() > 0">
and station_id_c in
<foreach collection="stations" item="sta" index ="index" open="(" close=")">
<if test="index > 0">
<choose>
<when test="(index % 500) == 499">
) or modified_time > #{dt} AND ELEMENT_NAME = #{element} AND station_id_c in (
</when>
<otherwise> , </otherwise>
</choose>
</if>
'${sta.stationIdC}'
</foreach>
</if>
ORDER BY ALARM_INFO.element_value desc , ALARM_INFO.date_time desc
</select>
反射获取自定义类属性名、属性值
1,mybatis逆向工程拼接sql
2,通过反射获取自定义类中所有字段
3,将反射获取到的属性名和属性值放入JSONObject对象中
4,将json对象放入集合中
public List<JSONObject> getAqiDayByTimeRangeAndRegion(String starttime,String endtime, String region,Integer limit){
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date dt1 = sdf.parse(starttime);
Date dt2 = sdf.parse(endtime);
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");
//1,mybatis逆向工程拼接sql
Example example = new Example(HbAirQltyDay.class);
Example.Criteria criteria = example.createCriteria();
criteria.andBetween("dateTime", sdf2.format(dt1),sdf2.format(dt2));
//榆林市,延安市,铜川市,咸阳市,渭南市,宝鸡市,西安市,商洛市,汉中市,安康市
String[] cities = region.split(",");
List<String> items = new ArrayList<>();
for (String city:cities){
items.add(city);
}
criteria.andIn("city",items);
example.setOrderByClause("date_time desc limit "+limit);
List<HbAirQltyDay> qxAqidataList = hbAirQltyDayMapper.selectByExample(example);
List<JSONObject> jsonObjects=new ArrayList<>();
for (HbAirQltyDay qxAqidata:qxAqidataList){
JSONObject jsonObject=new JSONObject();
//2,通过反射获取自定义类中所有字段
Field[] fields = qxAqidata.getClass().getDeclaredFields();
for (Field field : fields) {
//设置允许通过反射访问私有变量
field.setAccessible(true);
//获取字段的值
String value = field.get(qxAqidata).toString();
//获取字段属性名称
String name = field.getName().toUpperCase();
//3,将反射获取到的属性名和属性值放入JSONObject对象中
jsonObject.put(name,value);
}
if(jsonObject.containsKey("CITY")){
jsonObject.put("City",jsonObject.get("CITY"));
}
//4,将json对象放入集合中
jsonObjects.add(jsonObject);
}
return jsonObjects;
}catch (Exception e){
return new ArrayList<>();
}
}
map集合作为参数传入insert语句中
1，将JsonNode转换为Map
2，通过mq传给下游mq消费端去处理
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.sxqx.entity.MQMessage;
import com.sxqx.listener.IMessageReceiver;
import com.sxqx.service.cmadaas.surfacelive.ISurfaceLiveService;
import com.sxqx.service.cmadaas.upperair.IUpperAirService;
import com.sxqx.utils.dataConverter.JsonConverter;
import com.sxqx.utils.mq.RabbitMQ2MessageSender;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.amqp.rabbit.annotation.Queue;
import org.springframework.amqp.rabbit.annotation.RabbitHandler;
import org.springframework.amqp.rabbit.annotation.RabbitListener;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.Iterator;
import java.util.Map;
import java.util.Objects;
@Component
public class SyncTransferDsMessageReceiver implements IMessageReceiver {
Log log = LogFactory.getLog(SyncTransferDsMessageReceiver.class);
private final ISurfaceLiveService surfaceLiveService;
private final IUpperAirService upperAirService;
private final RabbitMQ2MessageSender rabbitMQ2MessageSender;
@Autowired
public SyncTransferDsMessageReceiver(ISurfaceLiveService surfaceLiveService,
IUpperAirService upperAirService,
RabbitMQ2MessageSender rabbitMQ2MessageSender) {
this.surfaceLiveService = surfaceLiveService;
this.upperAirService = upperAirService;
this.rabbitMQ2MessageSender = rabbitMQ2MessageSender;
}
@RabbitListener(queuesToDeclare = {
@Queue(name = "sxqxgxw_transfer_cmadaas_ds")
})
@RabbitHandler
@Override
public void onMessageReceived(String mqMessageString) {
//log.info("SyncTransferDsMessageReceiver start");
JsonNode jsonNode = JsonConverter.jsonString2JsonNode(mqMessageString);
JsonNode msg = jsonNode.findValue("msg");
JsonNode JsonNodeParams = msg.findValue("params");
Map<String, Object> params = JsonConverter.jsonNode2HashMap(JsonNodeParams);
if (params.size() > 0) {
String dataCode = params.get("dataCode").toString();
String elements = params.get("elements").toString();
String staIds = params.get("staIds").toString();
String times = params.get("times").toString();
Object responseByTimeAndStaID = null;
if (Objects.equals(dataCode,"SURF_GLB_MUL_HOR") ||
Objects.equals(dataCode,"SURF_CHN_MUL_HOR")) {
responseByTimeAndStaID = surfaceLiveService.getSurfEleByTimeAndStaID(dataCode, elements, times, staIds, null, null, null, null, null);
}else if(Objects.equals(dataCode,"UPAR_CHN_MUL_FTM")){
responseByTimeAndStaID = upperAirService.getUparEleByTimeAndStaID(dataCode, elements, times, staIds, null, null, null, null);
}
if(responseByTimeAndStaID!=null){
JsonNode jsonNodeDs = JsonConverter.jsonString2JsonObject(responseByTimeAndStaID.toString());
if (jsonNodeDs != null) {
JsonNode ds = jsonNodeDs.findValue("DS");
if(ds!=null && ds.size()>0){
// 拿到接口返回的数据集
Iterator<JsonNode> iterator = ds.elements();
while (iterator.hasNext()) {
JsonNode data = iterator.next();
// 将JsonNode转换为Map
ObjectMapper objectMapper = new ObjectMapper();
Map<String, Object> map = objectMapper.convertValue(data, Map.class);
if(map.containsKey("Datetime")){
Object datetime = map.get("Datetime");
map.remove("Datetime");
map.put("DATE_TIME",datetime);
}
if(map.containsKey("Station_levl")){
Object stationLevl = map.get("Station_levl");
map.remove("Station_levl");
map.put("STATION_LEVEL",stationLevl);
}
//插入数据
MQMessage mqMessage = new MQMessage();
JSONObject jsonObject = new JSONObject();
jsonObject.put("ds", map);
jsonObject.put("dataCode", dataCode);
mqMessage.setMsg(jsonObject);
rabbitMQ2MessageSender.send("sxqxgxw_cmadaas_ds_insert", mqMessage);
try {
Thread.sleep(50);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
}
}
}
}
}
将Map集合key，value作为表中字段和字段值插入数据库
@Component
public class SyncCmadaasDsMessageReceiver implements IMessageReceiver {
private final CmadaasDSMapper cmadaasDSMapper;
private final MQMessageSender mqMessageSender;
@Autowired
public SyncCmadaasDsMessageReceiver(CmadaasDSMapper cmadaasDSMapper,
MQMessageSender mqMessageSender) {
this.cmadaasDSMapper = cmadaasDSMapper;
this.mqMessageSender = mqMessageSender;
}
Log log = LogFactory.getLog(SyncSevpChnWefcRffcReceiver.class);
@RabbitListener(queuesToDeclare = {
@Queue(name = "sxqxgxw_cmadaas_ds_insert")
})
@RabbitHandler
@Override
public void onMessageReceived(String mqMessageString) {
JsonNode jsonNode = JsonConverter.jsonString2JsonNode(mqMessageString);
JsonNode msg = jsonNode.findValue("msg");
String dataCode = msg.findValue("dataCode").asText();
JsonNode ds = msg.findValue("ds");
if (Objects.equals(dataCode, "SURF_GLB_MUL_HOR") ||
Objects.equals(dataCode, "SURF_CHN_MUL_HOR") ||
Objects.equals(dataCode, "UPAR_CHN_MUL_FTM")) {
// 将JsonNode转换为Map
ObjectMapper objectMapper = new ObjectMapper();
Map<String, Object> map = objectMapper.convertValue(ds, Map.class);
if (map.containsKey("DATE_TIME")) {
Object dateTime = map.get("DATE_TIME");
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = null;
try {
date = dateFormat.parse(dateTime.toString());
} catch (ParseException e) {
e.printStackTrace();
}
map.replace("DATE_TIME", dateTime, date);
int cmadaasDSCountByTime = cmadaasDSMapper.findCmadaasDSCountByTime(dataCode, date);
int cmadaasDSCount = cmadaasDSMapper.findCmadaasDSCount(dataCode, map);
if (cmadaasDSCount == 0) {
map.put("CREATE_TIME", new Date());
map.put("DELETE_FLAG", 0);
cmadaasDSMapper.insertCmadaasDS(dataCode, map);
//清除DB时，按时间去逻辑删除，仅发送一条消息
if(cmadaasDSCountByTime==0){
// 清除DB记录
MQMessage mqMessage = new MQMessage();
JSONObject jsonObject = new JSONObject();
jsonObject.put("tableName", dataCode);
jsonObject.put("dateTime", dateTime);
mqMessage.setMsg(jsonObject);
mqMessageSender.send("queue.db_delay_7day_destroy", mqMessage);
}
}
}
}
}
}
mybatis mapper接口
@Repository
public interface CmadaasDSMapper {
int insertCmadaasDS(@Param("dataCode") String tableName,@Param("params")Map map);
int findCmadaasDSCount(@Param("dataCode") String tableName,@Param("params")Map map);
int findCmadaasDSCountByTime(@Param("dataCode") String tableName,@Param("dateTime")Date dateTime);
int deleteCmadaasDS(@Param("dataCode") String tableName,@Param("params")Map map);
int updateCmadaasDSDeleteFlag(@Param("dataCode") String tableName, @Param("deleteFlag") Integer deleteFlag,
@Param("deleteTime") Date deleteTime, @Param("dateTime")Date dateTime);
}
mybatis配置
<?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="com.sxqx.mapper.remote.xugugzb.cmadaasds.CmadaasDSMapper">
<insert id="insertCmadaasDS"
parameterType="java.util.Map"
>
INSERT INTO ${dataCode}
<foreach collection="params.keys" item="key" open="(" close=")" separator="," >
${key}
</foreach>
values
<foreach collection="params.keys" item="key" open="(" close=")" separator=",">
#{params[${key}]}
</foreach>
</insert>
<select id="findCmadaasDSCount" resultType="java.lang.Integer">
SELECT
COUNT(*)
FROM ${dataCode}
<where>
<foreach collection="params.keys" item="key" open="" close="" separator=" AND " >
${key} = #{params[${key}]}
</foreach>
</where>
</select>
<select id="findCmadaasDSCountByTime" resultType="java.lang.Integer">
SELECT
COUNT(*)
FROM ${dataCode}
where
DATE_TIME = #{dateTime}
</select>
<delete id="deleteCmadaasDS">
DELETE
FROM ${dataCode}
<where>
<foreach collection="params.keys" item="key" open="" close="" separator=" AND " >
${key} = #{params[${key}]}
</foreach>
</where>
</delete>
<update id="updateCmadaasDSDeleteFlag" parameterType="java.util.Map">
update ${dataCode}
set DELETE_FLAG= #{deleteFlag}, DELETE_TIME= #{deleteTime}
where
DATE_TIME = #{dateTime}
</update>
</mapper>
321
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
