框架篇(一):动态sql、分页查询

一、环境搭建 

1.tomat:Apache Tomcat® - Welcome!

2.小皮(mysql):Windows版phpstudy下载 - 小皮面板(phpstudy) (xp.cn)

3.SQLyog:SQLyog - Download (softonic.com)

4.源码、数据库：

5.源码、jar包、数据库

链接：https://pan.baidu.com/s/1XkU5lJSqTD26zlDuqs5-jA 
提取码：4567 
--来自百度网盘超级会员V2的分享

二、简介

分页查询

三、项目基础搭建及jar包导入

四、代码

1.dao层

EmpMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="cn.lexed.dao.EmpMapper">
<cache eviction="LRU" flushInterval="100000" size="1024" readOnly="true"/>
  
    <select id="getEmpById" parameterType="emp" resultType="emp">
  	select * from emp
  	<trim prefix="where" prefixOverrides="and | or">
  		<if test="ename!=null and ename!=''">
  			 and ename=#{ename}
  		</if>
  		<if test="job!=null and job!=''">
  			and job=#{job}
  		</if>
  	</trim>
  	
  </select>
	
	<select id="getEmps" resultType="emp">
		select * from emp
	</select>
  
</mapper>

 EmpMapper.java

package cn.lexed.dao;

import java.util.List;

import cn.lexed.pojo.Emp;

public interface EmpMapper {

    public Emp getEmpById(Emp emp);//查
	
	public List<Emp> getEmps();
}

2.pojo

注意：使用Serializable接口

package cn.lexed.pojo;

import java.io.Serializable;

public class Emp implements Serializable{
	private Integer empno;
	private String ename;
	private String job;
	private Integer mgr;
	
	public Emp() {
		super();
	}

	public Emp(Integer empno, String ename, String job, Integer mgr) {
		super();
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.mgr = mgr;
	}

	public Integer getEmpno() {
		return empno;
	}

	public void setEmpno(Integer empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public Integer getMgr() {
		return mgr;
	}

	public void setMgr(Integer mgr) {
		this.mgr = mgr;
	}

	@Override
	public String toString() {
		return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + "]";
	}
	
	
}

3.utils

package cn.lexed.utils;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MybatisUtils {
	
	      private static SqlSessionFactory factory;
	      
	      static{  //在静态代码块里，factory只会被创建一次
	    	  try{
	    		 InputStream is=Resources.getResourceAsStream("config.xml");
	    		 factory=new SqlSessionFactoryBuilder().build(is);
	    	  }catch(Exception e){
	    		  e.printStackTrace();
	    	  }
	    	  
	      }
	      
	      //获取SqlSession
	      public static SqlSession createSqlSession(){
	    	  
	    	  return  factory.openSession(true); //true为自动提交   
	      }
	      
	      
	      
	      public static void closeSqlSession(SqlSession sqlSession){
	      	  if(null!=sqlSession){
	    		  sqlSession.close();
	    	  }
	      }
}

resources

config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "mybatis-3-config.dtd" >
<configuration>
	<!-- 引入外部文件 -->
	<properties resource="db.properties"></properties>
	
	<!-- 开启复杂的自动映射 -->
	<!-- <settings>
	<setting name="autoMappingBehavior" value="FULL"/>
	</settings> -->
	
	<settings>
	<!-- 开启二级缓存 -->
	<setting name="cacheEnabled" value="true"></setting>

	</settings>
	
	<!-- 起别名 -->
	<typeAliases>
		<package name="cn.lexed.pojo"/>
	</typeAliases>
	
	<!-- 配置分页插件 -->
	<plugins>
		<plugin interceptor="com.github.pagehelper.PageInterceptor">
			<!-- 设置数据库的类型 Oracla mysql ... -->
			<property name="helperDialect" value="mysql"/>
		</plugin>
	</plugins>
	
	<!-- JDBC配置 -->
	<environments default="env">
		<environment id="env">
	
		<!-- 事务管理器 -->
		<transactionManager type="JDBC"></transactionManager>
		<!-- 数据源 -->
		<dataSource type="POOLED">
			<property name="driver" value="${driver}"/>
			<property name="url" value="${url}"/>
			<property name="username" value="${username}"/>
			<property name="password" value="${password}"/>
		</dataSource>
		</environment>
	</environments>
	<mappers>
		<!-- 在配置文件中，关联包下得接口类 -->
		<package name="cn.lexed.dao"/>
	</mappers>
</configuration>

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/dbs
username=root
password=root

五、结果  test

package cn.lexed.test;

import static org.junit.Assert.*;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

import cn.lexed.dao.EmpMapper;
import cn.lexed.pojo.Emp;
import cn.lexed.utils.MybatisUtils;

public class Test {

    //测试速度
    @org.junit.Test
	public void test() {
		//1.获取sqlsession对象
		SqlSession s=MybatisUtils.createSqlSession();
		long startTime1=System.currentTimeMillis();
		Emp emp=new Emp();
		emp.setEname("张三丰");
		emp.setJob("算命的");
		Emp e=s.getMapper(EmpMapper.class).getEmpById(emp);
		System.out.println(e);
		
		System.out.println("第一次查询的时间"+(System.currentTimeMillis()-startTime1));
		long startTime2=System.currentTimeMillis();
		Emp emp2=new Emp();
		emp2.setEname("张三丰");
		emp2.setJob("算命的");
		Emp e2=s.getMapper(EmpMapper.class).getEmpById(emp2);
		System.out.println(e2);
		
		System.out.println("第二次查询的时间"+(System.currentTimeMillis()-startTime2));
		
		
	}

	@org.junit.Test
	public void test7() {
		//1.获取sqlsession对象
		SqlSession s=MybatisUtils.createSqlSession();
		
		//分页
		Page<Object> page=PageHelper.startPage(2, 3);
		
		List<Emp> e=s.getMapper(EmpMapper.class).getEmps();
		
		System.out.println("当前页码:"+page.getPageNum());
		System.out.println("总记录数:"+page.getTotal());
		System.out.println("总页码:"+page.getPages());
		System.out.println("每页记录数"+page.getPageSize());
		System.out.println(page);
		
		//使用PageInfo：可以连续显示多少页
		PageInfo<Emp> info=new PageInfo<Emp>(e,5);
		for(Emp ee:e){
			System.out.println(ee);
		}
		System.out.println(info);
		
		/*
		 * 	当前页码
		 * 	总记录数
		 * 	总页码
		 * 	每页记录数
		 * 
		 * */
	
		
	}

}