框架篇(一):动态sql、数组传参

一、环境搭建 

1.tomat:Apache Tomcat® - Welcome!

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

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

4.源码、数据库：

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

二、简介

接上篇动态sql，改变参数为数组或集合

三、项目基础搭建及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"/>
  <!-- foreach：遍历
  	相关属性：
  		collection:指定输入对象中的集合属性，属性的值有三种 list array map-key
  		item:每次遍历生产的对象
  		open:开始遍历时，进行拼接字符串
  		close:结束时拼接的字符串
  		separator:分隔符
   -->
  <select id="array" resultType="emp">
  	select * from emp
  	<where>
  		<!-- SELECT * FROM emp WHERE 1=1 AND (empno=1 OR empno=2 OR empno=3) -->
  		<foreach collection="array" item="empno" open="and (" close=")" separator="or">
  			empno=#{empno}
  		</foreach>
  	</where>
  	
  </select>
  
  <select id="Listemps" resultType="emp">
	
	select * from emp where 1=1
	
	<!-- select * from emp where 1=1 and (empno=1 or empno=2 or empno=3) -->
	<!-- select * from emp where 1=1 and empno in(1,2,3) -->
	
	
		<foreach collection="list" item="empno" open="and empno in(" close=")" separator=",">
	 	 #{empno}
	
		</foreach>
	</select>
	
  
</mapper>

EmpMapper.java

package cn.lexed.dao;

import java.util.List;

import cn.lexed.pojo.Emp;

public interface EmpMapper {
	
	//数组传参
	public List<Emp> array(Integer[] empnos);
	
	public List<Emp> Listemps(List<Integer> empnos);
	
}

2.pojo

package cn.lexed.pojo;

public class Emp{
	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.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 test5() {
		//1.获取sqlsession对象
		SqlSession s=MybatisUtils.createSqlSession();
		Integer[] arr={1,2,3};
		List<Emp> list=s.getMapper(EmpMapper.class).array(arr);
		for(Emp e: list){
			System.out.println(e);
		}
		s.close();
		
	}
	
	@org.junit.Test
	public void test6() {
		//1.获取sqlsession对象
		SqlSession s=MybatisUtils.createSqlSession();
		List<Integer> e=new ArrayList<>();
		e.add(1);
		e.add(2);
		List<Emp> list=s.getMapper(EmpMapper.class).Listemps(e);
		for(Emp ee:list){
			System.out.println(ee);
		}
	}
	


}

4.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>
	
	<!-- 起别名 -->
	<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

五、结果