分页插件
1.pom.xml文件中添加分页插件
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.6</version>
</dependency>
2. 在mybatis配置文件中添加分页插件
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<property name="dialect" value="mysql"/>
</plugin>
<!--5.0版本pagehelper-->
<!-- <plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="helperDialect" value="mysql"/>
</plugin> -->
</plugins>
3. 测试类中使用PageHelper分页查询
PetsMapper petsMapper=session.getMapper(PetsMapper.class);
PageHelper.startPage(1, 3);
List<Pets> list=petsMapper.selectByExample(null);
PageInfo<Pets> info=new PageInfo<Pets>(list);
List<Pets> pets=info.getList();
for (Pets pets2 : list) {
System.out.println(pets2);
}
System.out.println("当前页="+info.getPageNum()+","+"每页条数="+info.getPageSize()+","+"总页数="+info.getPages()+","+"总条数="+info.getTotal());
注解开发
public interface PetsMapper {
@Select(value="select id,name,birth_date birthDate,type_id typeId, owner_id ownerId from pets")
public List<Pets> findAll();
@Select("select id,name,birth_date birthDate,type_id typeId, owner_id ownerId from pets where id=#{id}")
public Pets findById(int id);
@Insert("insert into pets(name,birth_date,type_id,owner_id) values(#{name},#{birthDate},#{typeId},#{ownerId})")
@SelectKey(keyColumn="id",keyProperty="id",resultType=Integer.class, before = false, statement = { "select last_insert_id()" })
public int insert(Pets p);
@Update(value="update pets set name=#{name},birth_date=#{birthDate} where id=#{id}")
public int update(Pets p);
@Delete("delete from pets where id=#{id}")
public int delete(int id) ;
}
mybatis自定义类型处理器
当字段类型和数据库类型不一致时,需要自定义类型转化器。
案例
编写一个普通类继承BaseTypeHandler抽象类,或者实现TypeHandler接口。重写三个方法:如下:
public class MyAddressTypeHandler extends BaseTypeHandler<Address> {
/**
* 新增 或者修改时 遇到Address 类型的字段 自动会调用该方法
*/
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Address parameter, JdbcType jdbcType)
throws SQLException {
//填充address这个字段的占位符
ps.setString(i, parameter.toString());//对象--->字符串
}
/**
* 根据列名查询
*/
@Override
public Address getNullableResult(ResultSet rs, String columnName) throws SQLException {
// TODO Auto-generated method stub
String a = rs.getString(columnName);
//山东省-青岛市-市北区-市北路-100(String)
Address address = null;
if (a!=null) {
String s [] = a.split("-");
address = new Address();
address.setProvinceName(s[0]);
address.setCityName(s[1]);
address.setDistinctName(s[2]);
address.setStreetName(s[3]);
address.setNo(Integer.parseInt(s[4].trim()));
}
return address;
}
/**
* 根据下标查询
*/
@Override
public Address getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String a = rs.getString(columnIndex);
//山东省-青岛市-市北区-市北路-100(String)
Address address = null;
if (a!=null) {
String s [] = a.split("-");
address = new Address();
address.setProvinceName(s[0]);
address.setCityName(s[1]);
address.setDistinctName(s[2]);
address.setStreetName(s[3]);
address.setNo(Integer.parseInt(s[4].trim()));
}
return address;
}
/**
* 存储过程
*/
@Override
public Address getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
// TODO Auto-generated method stub
return null;
}
在mybatis的配置文件中配置该处理器
<typeHandlers>
<!-- 配置自定义类型处理器
配置完成后:遇到address类型的字段 自动会调用MyAddressTypeHandler类处理该字段
-->
<typeHandler handler="com.itqf.handler.MyAddressTypeHandler"/>
</typeHandlers>
#{}和${}
- #{}
按照该字段真实的值的类型填充占位符。例如:String类型,调用ps.setString()填充。
- ${}
直接把值拼接到sql语句中,不拼接链接符号。 存在sql注入的问题 应用场景: 排序时,要排序的字段,排序规则,就可以使用${}
例:
select * from users order by #{order} #{sorter};
使用#{}方式,执行sql语句
select * from users order by 'id' 'desc';
select * from users order by ${order} ${sorter};
使用${}方式,执行sql语句
select * from users order by id desc;