Mybatis下的动态sql
package com.msb.mapper;
import com.msb.pojo.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface EmpMapper {
/*实现一个查询所以员工信息的方法
* 方法名findAll
* return 全部员工信息封装的Emp对象的List集合
* */
List<Emp> findAll();
/*
* 根据员工编号和薪资下限去查询员工信息
*
* */
List<Emp> findSal(int deptno, double sal);
/*
* 根据字母进行模糊查询
*
* */
List<Emp> findEname (String name);
List<Emp> fByempno(int[] empno);
List<Emp> fByempno2(List<Integer> empno);
}
<?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.msb.mapper.EmpMapper">
<sql id="empID">empno,ename,job,mgr,hiredate,sal,comm,deptno</sql>
<sql id="BaseID">select <include refid="empID"></include>from emp</sql>
<select id="findAll" resultType="emp">
<include refid="BaseID"></include>
</select>
<select id="findSal" resultType="emp">
<include refid="BaseID"></include> where deptno = #{deptno} and sal >= #{sal}
</select>
<select id="findEname" resultType="emp">
<include refid="BaseID"></include> where ename like concat('%',#{name},'%')
</select>
<!--List<Emp> fByempno(int[] empno);
select * from emp where empno in(1,2,3)
collection="array"参数是数组int[],collection中名字指定为array;
open="(" 以什么开头
close=")"以什么结尾
separator=","用什么拼接
item="empno"中间变量名
-->
<select id="fByempno" resultType="emp">
<include refid="BaseID"></include>where empno in
<foreach collection="array" open="(" close=")" separator="," item="empno">
#{empno}
</foreach>
</select>
<!--List<Emp> fByempno2(List<Integer> empno);-->
<select id="fByempno2" resultType="emp" >
<include refid="BaseID"></include>where empno in
<foreach collection="list" open="(" close=")" item="empno" separator=",">
#{empno}
</foreach>
</select>
</mapper>
//调用测试方法
import com.msb.mapper.EmpMapper;
import com.msb.pojo.Dept;
import com.msb.pojo.Emp;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public class Test1 {
SqlSession sqlSession = null;
@Before
public void test1(){
//首先做一个对象SqlSessionFactoryBuilder建立一个绘话
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
//有一个文本输入的io流进行读取操作
InputStream stream = null;
try {
//这里的路径直接会定位到配置文件classes下面；所以这个文件在次目录下--编译和
//-图纸；对数据库文件进行读取，获取一个io流，由于配置文件在classes下面，直接写文件名即可
stream = Resources.getResourceAsStream("sqlMapConfig.xml");
} catch (IOException e) {
e.printStackTrace();
}
//build需要指向一个文件进行读取出来--工厂
SqlSessionFactory factory = ssfb.build(stream);
//需要用sqlSession去调用增删改查--工人去获取数据，打开这个绘话
sqlSession = factory.openSession();
}
@Test
public void tese2(){
List<Emp> list = sqlSession.selectList("findAll");
list.forEach(System.out::println);
}
@Test
public void tese3(){
/*EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = new Emp();
Emp emp1 = new Emp();
emp.setSal(1500);
emp1.setDeptno(20);
List<Emp> emps = mapper.findSal(20,1500);
emps.forEach(System.out::println);*/
Emp emp =new Emp();
emp.setDeptno(20);
emp.setSal(1500);
List<Emp> findSal = sqlSession.selectList("findSal", emp);
findSal.forEach(System.out::println);
}
@Test
public void tese4(){
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> emps = mapper.findEname("a");
emps.forEach(System.out::println);
}
//测试方法对select * from emp where empno in(1,2,3)；个数可变
//List<Emp> fByempno(int[] empno);这里放数组
@Test
public void tese6(){
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> emps = mapper.fByempno(new int[]{7369, 7499, 7521});
emps.forEach(System.out::println);
}
//List<Emp> fByempno2(List<Integer> empno);这里放集合
@Test
public void tese7(){
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
List<Integer>arr=new ArrayList<>();
Collections.addAll(arr, 7369, 7499, 7521);
List<Emp> emps = mapper.fByempno2(arr);
emps.forEach(System.out::println);
}
@After
public void test3(){
if (sqlSession!=null){
sqlSession.close();
}
}
}
<?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="jdbc.properties"></properties>
<!--这里是起的别名，前面是名，后面是路径-->
<!--<typeAliases>
<typeAlias alias="dept" type="com.msb.pojo.Dept"/>
</typeAliases>-->
<!--包别名，用到时候调用名字小写即可,就会扫描msb下的所以实体类，用的时候实体类名小写-->
<typeAliases>
<package name="com.msb"/>
</typeAliases>
<environments default="development">
<environment id="development">
<!-- 简单使用了 JDBC 的提交和回滚设置 -->
<transactionManager type="JDBC"/>
<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>
<!--加载mapper映射文件,通过包扫描加载核心文件-->
<mappers>
<package name="com.msb.mapper"/>
</mappers>
</configuration>
log4j.rootLogger=debug,stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.SimpleLayout
log4j.appender.logfile=org.apache.log4j.FileAppender
log4j.appender.logfile.File=d:/msb.log
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %l %F %p %m%n
jdbc_driver=com.mysql.cj.jdbc.Driver
jdbc_url=jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
jdbc_username=root
jdbc_password=root
实现类
package com.msb.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.sql.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Integer sal;
private Integer comm;
private Integer deptno;
}
最最重要的pop导入依赖,只需要导入dependencies标签的即可
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.msb</groupId>
<artifactId>Mybaries2</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<!--mysqlConnector-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<!--mybatis 核心jar包-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.3</version>
</dependency>
<!--junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.1</version>
<scope>test</scope>
</dependency>
<!--lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
</project>
浙公网安备 33010602011771号