mybatis条件查询+模糊查询根据list、map集合批量查询

最新推荐文章于 2024-06-07 09:20:47 发布

神经呀

最新推荐文章于 2024-06-07 09:20:47 发布

阅读量2.8k

点赞数 1

分类专栏： mybatis框架知识点

本文链接：https://blog.csdn.net/qq_43543543/article/details/104876126

版权

mybatis框架知识点专栏收录该内容

14 篇文章 0 订阅

订阅专栏

mybatis条件查询+模糊查询

package test;

import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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 com.abc.mybatis.domain.Emp;
import com.abc.mybatis.mapper.EmpDao;

public class TestQueryByCondition {

	public static void main(String[] args) throws Exception{
		// 初始化sqlSessionFactory
		InputStream is=Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(is);
		//初始化SqlSession
		SqlSession sqlSession=sessionFactory.openSession();
		
		EmpDao mapper=sqlSession.getMapper(com.abc.mybatis.mapper.EmpDao.class);
		
		Map<String, Object> map=new HashMap<>();
		map.put("ename", "莎士");
		//map.put("empno", 6666);
		//map.put("hiredate", value);
		List<Emp> emps=mapper.queryEmpByCondition(map);
		System.out.println(emps.toString());
		
		sqlSession.close();
	}

}

mybatis条件查询+模糊查询：mapper配置

<!-- map是java.util.Map 的简写，mybatis能够解析 -->
<select id="queryEmpByCondition" resultType="Emp" parameterType="map">
	select empno,ename,hiredate,sal from emp 
	<!-- where标签首先会在使用的地方输出一个where，mybatis还会智能的判断有无条件
	如果无条件，那么默认查出所有数据；空格问题，where会帮我们自动补上
	每个条件前面要有and
	 -->
	<where>
		<if test="ename!=null and ename!=''">
		and ename like '%${ename}%'
		<!-- ${ename}这个写法会导致sql注入，因为会拼接sql，如果里面含有or and等运算符，那么sql语句的意义就会变 -->
		<!-- and ename=#{ename}  -->
		</if>
		<if test="empno>0">
		and empno=#{empno} 
		</if>
		<if test="hiredate!=null">
		and hiredate=#{hiredate} 
		</if>
	</where>
</select>

根据list集合批量查询

package test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

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 com.abc.mybatis.domain.Emp;
import com.abc.mybatis.mapper.EmpDao;

public class TestQueryByList {
	public static void main(String[] args) throws IOException {
		// 初始化sqlSessionFactory
		InputStream is=Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(is);
		//初始化SqlSession
		SqlSession sqlSession=sessionFactory.openSession();
		
		EmpDao mapper=sqlSession.getMapper(com.abc.mybatis.mapper.EmpDao.class);
		
		List<Integer> list=new ArrayList<>();
		list.add(6666);
		list.add(7839);
		List<Emp> emps=mapper.queryByList(list);
		System.out.println(emps.toString());
		
	}
}

根据list集合批量查询：mapper配置文件

<select id="queryByList" resultType="Emp">
	select <include refid="baseColumn"></include> from emp
	where empno in
	<!-- collection="list"告诉maybatis我需要一个list类型的参数
	select * from emp where empno in(7688,7839) -->
	<foreach collection="list" open="(" item="id" separator="," close=")" >
		#{id}
	</foreach>
</select>

根据map集合批量查询

package test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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 com.abc.mybatis.domain.Emp;
import com.abc.mybatis.mapper.EmpDao;

public class TestQueryByMap {
	public static void main(String[] args) throws IOException {
		// 初始化sqlSessionFactory
		InputStream is=Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(is);
		//初始化SqlSession
		SqlSession sqlSession=sessionFactory.openSession();
		
		EmpDao mapper=sqlSession.getMapper(com.abc.mybatis.mapper.EmpDao.class);
		
		Map<String,Object> map=new HashMap<>();
		List<Integer> list=new ArrayList<>();
		list.add(6666);
		list.add(7839);
		
		map.put("list", list);
		map.put("ename", "KING");
		//查询名字等于KING，编号在list范围之内的人
		List<Emp> emps=mapper.queryByMap(map);
		System.out.println(emps.toString());
		
	}
}

根据map集合批量查询：mapper配置文件

<select id="queryByMap" resultType="Emp">
	select <include refid="baseColumn"></include> from emp
	where ename=#{ename}
	and empno in
	<foreach collection="list" open="(" item="id" separator="," close=")" >
		#{id}
	</foreach>
</select>

EmpDao接口

package com.abc.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.abc.mybatis.domain.Emp;

public interface EmpDao {
	
	/**批量查询：根据map集合,map集合中包含了list集合*/
	public List<Emp> queryByMap(Map<String,Object> map);
	
	/**批量查询：根据list集合查询*/
	public List<Emp> queryByList(List<Integer> empnos);
	
	public List<Emp> queryAll();
	public Emp queryEmp(Integer empno);
	public Integer updateEmp(Emp emp);
	
	/** 条件查询 */
	public List<Emp> queryEmpByCondition(Map map);

}

EmpMapper完整配置文件

<?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.abc.mybatis.mapper.EmpDao">
	
	<!-- public List<Emp> queryByMap(Map<String,Object> map); -->
	<select id="queryByMap" resultType="Emp">
		select <include refid="baseColumn"></include> from emp
		where ename=#{ename}
		and empno in
		<foreach collection="list" open="(" item="id" separator="," close=")" >
			#{id}
		</foreach>
	</select>
	
	<!-- public List<Emp> queryByList(List<Integer> empnos); -->
	<select id="queryByList" resultType="Emp">
		select <include refid="baseColumn"></include> from emp
		where empno in
		<!-- collection="list"告诉maybatis我需要一个list类型的参数
		select * from emp where empno in(7688,7839) -->
		<foreach collection="list" open="(" item="id" separator="," close=")" >
			#{id}
		</foreach>
	</select>
	
	<!-- public List<Emp> queryEmpByCondition(Map map); -->
	<!-- map是java.util.Map 的简写，mybatis能够解析 -->
	<select id="queryEmpByCondition" resultType="Emp" parameterType="map">
		select empno,ename,hiredate,sal from emp 
		<!-- where标签首先会在使用的地方输出一个where，mybatis还会智能的判断有无条件
		如果无条件，那么默认查出所有数据；空格问题，where会帮我们自动补上
		每个条件前面要有and
		 -->
		<where>
			<if test="ename!=null and ename!=''">
			and ename like '%${ename}%'
			<!-- ${ename}这个写法会导致sql注入，因为会拼接sql，如果里面含有or and等运算符，那么sql语句的意义就会变 -->
			<!-- and ename=#{ename}  -->
			</if>
			<if test="empno>0">
			and empno=#{empno} 
			</if>
			<if test="hiredate!=null">
			and hiredate=#{hiredate} 
			</if>
		</where>
	</select>
		
	<!-- public List<Emp> queryAll(); -->
		<select id="queryAll" resultType="Emp">
			select <include refid="baseColumn"></include> from emp
		</select>
		
	<!-- public Emp queryEmpno(Integer empno); -->
		<select id="queryEmp" parameterType="Integer" resultType="Emp">
			select <include refid="baseColumn"></include> from emp where empno=#{empno}
		</select>
		
		<!-- sql语句有重复出现的块，可以提取出来，定义成sql片段，其它sql语句引用即可 -->
		<sql id="baseColumn">
		empno,ename,hiredate,sal
		</sql>
		
	<!-- public Integer updateEmp(Emp emp); -->
		<update id="updateEmp" parameterType="Emp">
			update emp
			<set>
				<if test="ename!=null and ename!=''">
				ename=#{ename},
				</if>
				<if test="sal>0">
				sal=#{sal},
				</if>
				<if test="hiredate!=null">
				hiredate=#{hiredate},
				</if>
			</set>
			where empno=#{empno}
		</update>
		
		
	</mapper>

mybatis-config.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>


<!-- – configuration 配置
• properties 属性
• settings 设置
• typeAliases 类型命名
• typeHandlers 类型处理器
• objectFactory 对象工厂
• plugins 插件
• Environments 环境
• mappers 映射器 -->

	<!-- 加载属性文件 -->
	<properties resource="jdbc_oracle.properties"></properties>

	<!-- 配置别名 -->
	<typeAliases>
		<package name="com.abc.mybatis.domain"/>
	</typeAliases>

	<!-- 对数据库做个映射 -->
	<environments default="armbank">
		<environment id="armbank">
			<transactionManager type="JDBC"></transactionManager>
			<dataSource type="pooled">
				<property name="url" value="${jdbc.url}"/>
				<property name="username" value="${jdbc.username}"/>
				<property name="driver" value="${jdbc.driver}"/>
				<property name="password" value="${jdbc.password}"/>
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
	<!-- 映射文件写好之后一定要告诉主配置文件，否则报错 -->
	<!-- 包扫描效率高 -->
		<mapper resource="EmpMapper.xml"></mapper>
	
	</mappers>

</configuration>

jdbc_oracle.properties文件

jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl
jdbc.username=scott
jdbc.password=tiger

Emp实体类

package com.abc.mybatis.domain;

import java.util.Date;

public class Emp {
	
	private Integer empno;
	private String ename;
	private Date hiredate;
	private Double sal;
	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 Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public Double getSal() {
		return sal;
	}
	public void setSal(Double sal) {
		this.sal = sal;
	}
	@Override
	public String toString() {
		return "Emp [empno=" + empno + ", ename=" + ename + ", hiredate=" + hiredate + ", sal=" + sal + "]\n";
	}
	public Emp(Integer empno, String ename, Date hiredate, Double sal) {
		super();
		this.empno = empno;
		this.ename = ename;
		this.hiredate = hiredate;
		this.sal = sal;
	}
	public Emp() {
		super();
		// TODO Auto-generated constructor stub
	}
}

神经呀

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
mybatis条件查询+模糊查询根据list、map集合批量查询

mybatis条件查询+模糊查询package test;import java.io.InputStream;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.ibatis.io.Resources;import org.apache.ibatis.sess...
复制链接

扫一扫