mybatis中数据分页以及高级查询 和一些简单的小笔记
高级查询+数据分页
文件目录：
核心代码
mybatis.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 resource="db.properties"></properties>
<typeAliases>
<typeAlias type="pagesearch.domain.user" alias="use"></typeAlias>
</typeAliases>
<environments default="default">
<environment id="default">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${db.driverClassName}"></property>
<property name="url" value="${db.url}"></property>
<property name="username" value="${db.username}"></property>
<property name="password" value="${db.password}"></property>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="pagesearch/domain/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="pagesearch.domain.usermapper">
<sql id="base">
<trim prefix="WHERE" prefixOverrides="AND|OR">
<if test="keyword!=null">
AND username LIKE CONCAT('%',#{keyword},'%')
</if>
<if test="bigage!=null">
AND age < #{bigage} //<为'<'
</if>
<if test="smallage!=null">
AND age > #{smallage} //>为'>'
</if>
</trim>
</sql>
<select id="count" resultType="long">
SELECT count(*) FROM user
<include refid="base"></include>
</select>
<select id="list" parameterType="pagesearch.domain.queryobject" resultType="use">
SELECT *FROM user
<include refid="base"></include>
LIMIT #{start},#{size}
</select>
</mapper>
mybatis工具类
package pagesearch.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
public class mybatisutils {
private SqlSessionFactory sf=null;
private static mybatisutils instance=new mybatisutils();
public mybatisutils() {
try {
sf=new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession opensession(){
return instance.sf.openSession();
}
}
user类
package pagesearch.domain;
public class user {
private Long id;
private String username;
private String password;
private Integer age;
public Long getId() {
return id;
}
public void setId(Long 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;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "user{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", age=" + age +
'}';
}
}
queryobject类
package pagesearch.domain;
public class queryobject {
private Long page;
private Long size;
private String keyword;
private Integer smallage;
private Integer bigage;
public Long getPage() {
return page;
}
public void setPage(Long page) {
this.page = page;
}
public Long getSize() {
return size;
}
public void setSize(Long size) {
this.size = size;
}
public String getKeyword() {
return keyword;
}
public void setKeyword(String keyword) {
this.keyword = keyword;
}
public Integer getSmallage() {
return smallage;
}
public void setSmallage(Integer smallage) {
this.smallage = smallage;
}
public Integer getBigage() {
return bigage;
}
public void setBigage(Integer bigage) {
this.bigage = bigage;
}
public Long getStart(){
return (this.page-1)*this.size;
}
}
pageresult类
package pagesearch.domain;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
public class pageresult {
private Long total;
private List rows;
public static final pageresult EMPTY=new pageresult(0L, Collections.emptyList());
public Long getTotal() {
return total;
}
public void setTotal(Long total) {
this.total = total;
}
public List getRows() {
return rows;
}
public void setRows(List rows) {
this.rows = rows;
}
public pageresult(Long total, List rows) {
this.total = total;
this.rows = rows;
}
}
usermapper接口
package pagesearch.domain;
import java.util.List;
public interface usermapper {
Long count(queryobject qo);
List<user>list(queryobject go);
}
Iuser接口
package pagesearch.domain;
public interface Iuser {
public pageresult pagesearch(queryobject qo);
}
Iuserimpl接口
package pagesearch.domain;
import org.apache.ibatis.session.SqlSession;
import pagesearch.utils.mybatisutils;
import java.util.List;
public class Iuserimpl implements Iuser{
@Override
public pageresult pagesearch(queryobject qo) {
SqlSession session= mybatisutils.opensession();
usermapper mapper=session.getMapper(usermapper.class);
try {
Long count=mapper.count(qo);
if(count>0){
List<user>list=mapper.list(qo);
return new pageresult(count,list);
}else {
return pageresult.EMPTY;
}
}catch (Exception e){
e.printStackTrace();
}finally {
session.close();
}
return null;
}
}
test测试类
package pagesearch.domain;
import org.apache.ibatis.session.SqlSession;
import pagesearch.utils.mybatisutils;
import java.util.List;
public class Iuserimpl implements Iuser{
@Override
public pageresult pagesearch(queryobject qo) {
SqlSession session= mybatisutils.opensession();
usermapper mapper=session.getMapper(usermapper.class);
try {
Long count=mapper.count(qo);
if(count>0){
List<user>list=mapper.list(qo);
return new pageresult(count,list);
}else {
return pageresult.EMPTY;
}
}catch (Exception e){
e.printStackTrace();
}finally {
session.close();
}
return null;
}
}
数据库
不断更改数据，显示的结果为
一下笔记转载于https://www.cnblogs.com/teach/p/5685545.html
#和$符号的差别
mybatis中#和$符号的区别
mybatis做为一个轻量级ORM框架在许多项目中使用，因其简单的入门受到了广大开发者的热爱。在近期项目中再做一个相关的开发，碰到了#、$符号这样的问题，之前没怎么注意过，通过学习之后，有了点感悟，分享如下，
#{}
使用#{}意味着使用的预编译的语句，即在使用jdbc时的preparedStatement，sql语句中如果存在参数则会使用?作占位符，我们知道这种方式可以防止sql注入，并且在使用#{}时形成的sql语句，已经带有引号，例，select * from table1 where id=#{id} 在调用这个语句时我们可以通过后台看到打印出的sql为：select * from table1 where id='2' 加入传的值为2.也就是说在组成sql语句的时候把参数默认为字符串。
${}
使用${}时的sql不会当做字符串处理，是什么就是什么，如上边的语句：select * from table1 where id=${id} 在调用这个语句时控制台打印的为：select * from table1 where id=2 ，假设传的参数值为2
从上边的介绍可以看出这两种方式的区别，我们最好是能用#{}则用它，因为它可以防止sql注入，且是预编译的，在需要原样输出时才使用${}，如，
select * from ${tableName} order by ${id} 这里需要传入表名和按照哪个列进行排序 ，加入传入table1、id 则语句为：select * from table1 order by id
如果是使用#{} 则变成了select * from 'table1' order by 'id' 我们知道这样就不对了。
另，在使用以下的配置时，必须使用#{}
<select id="selectMessageByIdI" parameterType="int" resultType="Message">
select * from message where id=#{id};
</select>
在parameterType是int时，sql语句中必须是#{}。