文章目录
- 1. typeHandlers标签
- 2. plugins标签
- 通过PageHelper的API获取分页的信息
1. typeHandlers标签
可以重写类型处理器,或创建类型处理器来处理不支持/非标准的类型。选择性地将它映射到一个JDBC类型:如Java中的Date
类型,将其存放到数据库时存成一个1970年至今的毫秒数,取出来的时候转换成java的Date
,即java的Date
与数据库的varchar
毫秒值之间的转换.具体做法是:
- 实现
org.apache.ibatis.type.TypeHandler
接口,或继承很便利的类org.apache.ibatis.type.BaseTypeHandler
- 覆盖4个未实现的方法。
setNonNullParameter
为java程序设置数据到数据库的回调方法,getNullableResult
为查询时mysql的字符串类型转换成java的Type类型的方法 - 在MyBatis核心配置文件中注册
- 测试转换是否正确
在数据库的user
表中新建一个字段birthday
,类型选择bigint
:
写一个插入语句,在XML文件和Java文件中:
<?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.example.demo.dao.UserMapper">
<insert id="save" parameterType="user">
insert into user values(#{id}, #{username}, #{password}, #{birthday})
</insert>
</mapper>
package com.example.demo.dao;
import com.example.demo.domain.User;
public interface UserMapper {
void save(User user);
}
User类中定义一个birthday属性,更新其Set、Get方法,更新toString方法:
package com.example.demo.domain;
import java.util.Date;
public class User {
int id;
String username;
String password;
Date birthday;
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
'}';
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
写测试代码:
package com.example.demo.service;
import com.example.demo.dao.UserMapper;
import com.example.demo.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
public class ServiceDemo {
public static void main(String[] args) throws IOException {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 新建user
User user = new User();
user.setUsername("yang");
user.setPassword("12345");
user.setBirthday(new Date());
// 执行保存操作
userMapper.save(user);
sqlSession.commit();
}
}
但是这时候会报错Data truncated for column 'birthday'
,则我们就需要
第一步:定义类型处理器,覆盖方法
package com.example.demo.handler;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class DateHandler extends BaseTypeHandler<Date> {
// 将java类型 转换成 数据库需要的类型
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
long time = date.getTime();
preparedStatement.setLong(i, time);;
}
// 将数据库中类型 转换成 java类型
// String参数:要转换的字段名称
// ResultSet,查询结果集
@Override
public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
// 获取结果集中需要的数据(long)并转换成Date类型返回
long slong = resultSet.getLong(s);
Date date = new java.sql.Date(slong);
return date;
}
// 将数据库中类型 转换成 java类型
@Override
public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
long slong = resultSet.getLong(i);
Date date = new java.sql.Date(slong);
return date;
}
// 将数据库中类型 转换成 java类型
@Override
public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
Long along = callableStatement.getLong(i);
Date date = new java.sql.Date(along);
return date;
}
}
第二步:在SqlMapConfig中使用<typeHandlers>
注册类型处理器
其中handler
中写的是类名
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 通过properties标签加载外部properties文件 -->
<properties resource="jdbc.properties"></properties>
<!-- 自定义别名 -->
<typeAliases>
<typeAlias type="com.example.demo.domain.User" alias="user"></typeAlias>
</typeAliases>
<!-- 注册类型处理器 -->
<typeHandlers>
<typeHandler handler="com.example.demo.handler.DateHandler"></typeHandler>
</typeHandlers>
<!-- 数据源环境 -->
<environments default="development">
<environment id="development">
<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="mapper/UserMapper.xml"></mapper>
</mappers>
</configuration>
第三步:插入和查询测试
插入测试代码:
package com.example.demo.service;
import com.example.demo.dao.UserMapper;
import com.example.demo.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
public class ServiceDemo {
public static void main(String[] args) throws IOException {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 新建user
User user = new User();
user.setUsername("yang");
user.setPassword("12345");
user.setBirthday(new Date());
// 执行保存操作
userMapper.save(user);
sqlSession.commit();
sqlSession.close();
}
}
可以看到已经成功保存了:
执行查询测试:
package com.example.demo.service;
import com.example.demo.dao.UserMapper;
import com.example.demo.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class ServiceDemo {
public static void main(String[] args) throws IOException {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 查询
User user = userMapper.findById(2);
System.out.println(user.getBirthday());
sqlSession.commit();
sqlSession.close();
}
}
也可以成功查询:
2. plugins标签
MyBatis可以使用第三方的插件来对功能进行拓展,分页助手PageHelper
是将分页的复杂操作进行封装,使用简单的方式即可获得分页相关数据。操作步骤如下:
- 导入通用PageHelper坐标
- 在MyBatis核心配置文件中配置PageHelper插件
- 测试分页数据获取
第一步:导入坐标
<!-- 分页助手-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.3.2</version>
</dependency>
<!-- 解析-->
<dependency>
<groupId>com.github.jsqlparser </groupId>
<artifactId>jsqlparser </artifactId>
<version>4.6</version>
</dependency>
</dependencies>
第二步:配置插件
需要注意的是,<plugins>
应该在typeHandlers
的后面、<environmnets>
的前面,根据给的提示来放置
<!-- 配置分页助手插件 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
注:PageHelper
的5.0以上的版本应该写:
<!-- 配置分页助手插件 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor"/>
</plugins>
第三步:测试分页数据获取
写一个查询所有用户的方法:
配置文件:
<select id="findAll" resultType="user">
select * from user
</select>
接口:
List<User> findAll();
测试:
package com.example.demo.service;
import com.example.demo.dao.UserMapper;
import com.example.demo.domain.User;
import com.github.pagehelper.PageHelper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class ServiceDemo {
public static void main(String[] args) throws IOException {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.findAll();
for (User user: userList){
System.out.println(user);
}
sqlSession.commit();
sqlSession.close();
}
}
在配置了分页相关的插件后,只需要简单地设置一下分页的相关参数,其中第一个参数pageNum
为当前页,第二个参数pageSize
为每页显示的条数:
package com.example.demo.service;
import com.example.demo.dao.UserMapper;
import com.example.demo.domain.User;
import com.github.pagehelper.PageHelper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class ServiceDemo {
public static void main(String[] args) throws IOException {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 设置分页相关参数,当前页,及每页显示的条数
PageHelper.startPage(1, 2);
List<User> userList = userMapper.findAll();
for (User user: userList){
System.out.println(user);
}
sqlSession.commit();
sqlSession.close();
}
}
数据库中一共三条数据:
当参数为(1,2)时,结果为:
当参数为(2,2)时,结果为:
通过PageHelper的API获取分页的信息
虽然能够分页了,但是我们还可以通过API获取一些分页的信息,这样子在写网页的时候就可以通过这些信息来判断是否显示“上一页”、“下一页”、“首页”、“尾页”按钮
当前页数:pageInfo.getPageNum()
每页显示条数:pageInfo.getPageSize()
总条数:pageInfo.getTotal()
总页数:pageInfo.getPages()
上一页:pageInfo.getPrePage()
下一页:pageinfo.getNextPgae()
是否是第一个(bool):pageInfo.isIsFirstPage()
是否是最后一个(bool):pageInfo.isIsLastPage()
package com.example.demo.service;
import com.example.demo.dao.UserMapper;
import com.example.demo.domain.User;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class ServiceDemo {
public static void main(String[] args) throws IOException {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 设置分页相关参数,当前页,及每页显示的条数
PageHelper.startPage(1, 2);
List<User> userList = userMapper.findAll();
for (User user: userList){
System.out.println(user);
}
// 获得与分页相关的参数
PageInfo<User> pageInfo = new PageInfo<User>(userList);
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.commit();
sqlSession.close();
}
}
结果如下: