SQL语句中很多时候需要进行条件判断是否要进行某些字段的过滤，同时也经常会碰到in查询，Mybatis里的动态SQL就是为了应对这种问题。我们依然搭建一个项目下的module，配置好基本配置如Mybatis核心配置文件mybatis-config.xml；Pojo实体类(这里我们使用了一个城市数据表，建表SQL及数据见后）。本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.
//Mybatis核心配置文件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>
<properties resource="db.properties" />
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<package name="com.kermit.dao"/>
<package name="com.kermit.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<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>
<mapper resource="com/kermit/dao/CityMapper.xml" />
</mappers>
</configuration>
本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.Pojo实体类代码、接口类CityMapper、以及共用数据库连接类代码如下：本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.
//Pojo实体类
package com.kermit.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class City {
private int id;
private String name;
private int gdp;
private int people;
private String station;
}
//接口类CityMapper
package com.kermit.dao;
import com.kermit.pojo.City;
import java.util.List;
import java.util.Map;
public interface CityMapper {
//按要求查询城市
public List<City> findCityIf(Map map);
//查询城市 findCityChoose
public List<City> findCityChoose(Map map);
//in查询城市 selectForeach
public List<City> selectForeach(List list);
}
//Mybatis共用的数据库连接类
package com.kermit.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;
import java.io.InputStream;
public class MybatisMysql {
private static SqlSessionFactory sqlSessionFactory;
private static SqlSession sqlSession;
static{
try {
String resouces = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resouces);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession(true);
}
}
本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.接下来我们开发CityMapper.xml的mybatis 的SQL配置文件，这里使用了几种结合方式：本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.
1. where if结合，where 元素只会在子元素返回任何内容的情况下才插入 WHERE”子句。而且若子句的开头为 AND 或 OR，where 元素也会将它们去除。本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.
2. choose、when、otherwise 其有点像switch 语句，只要满足了一个条件就按此条件执行，而不会全部累加，都不满足就执行otherwise条件。本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.
3. Foreach 功能，适合in查询时的拼接。本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.
//CityMapper.xml的mybatis 的SQL配置文件，
<?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.kermit.dao.CityMapper">
<!--where if动态SQL功能-->
<select id="findCityIf" resultType="City" parameterType="map">
select * from city
<where>
<if test = "gdp != null">
and gdp > #{gdp}
</if>
<if test = "people != null">
and people > #{people}
</if>
<if test = "name != null">
and name = #{name}
</if>
</where>
</select>
<!--choose when otherwise动态SQL功能-->
<select id="findCityChoose" resultType="City" parameterType="map">
select * from city where 1
<choose>
<when test="gdp>0">
and gdp > #{gdp}
</when>
<when test="people>0">
and people > #{people}
</when>
<when test="station != null">
and station like #{station}
</when>
<otherwise>
and id>5
</otherwise>
</choose>
</select>
<select id="selectForeach" resultType="City" parameterType="list">
select * from city where id in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
</mapper>
本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.最后是我们的测试类代码：本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.
import com.kermit.dao.CityMapper;
import com.kermit.pojo.City;
import com.kermit.utils.MybatisMysql;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class DySqlTest {
public static SqlSession sqlSession;
public static CityMapper mapper;
static{
sqlSession = MybatisMysql.getSqlSession();
mapper = sqlSession.getMapper(CityMapper.class);
System.out.println(mapper);
}
@Test
public void TestSql1(){
//构造查询条件map
Map map = new HashMap();
//搜索GDP在20000亿以上的城市列表：北京、上海、深圳
map.put("gdp", 20000);
//搜索人口在1500万以上的城市列表：北京、上海
map.put("people", 1500);
//直接通过城市名称匹配
map.put("name", "北京");
List<City> cityList = mapper.findCityIf(map);
//显示查询的数据
for (City city : cityList) {
System.out.println(city);
}
sqlSession.close();
}
@Test
public void TestSql2(){
//构造查询条件map
Map map = new HashMap();
//搜索GDP在20000亿以上、单一条件匹配
//map.put("gdp", 20000);
//map.put("people", 1500);
map.put("station", "%北站%");
List<City> cityList = mapper.findCityChoose(map);
//显示查询的数据
for (City city : cityList) {
System.out.println(city);
}
sqlSession.close();
}
@Test
public void TestSql3(){
//构造查询条件map
List list = new ArrayList();
//in搜索城市ID
list.add(1);
list.add(3);
List<City> cityList = mapper.selectForeach(list);
//显示查询的数据
for (City city : cityList) {
System.out.println(city);
}
sqlSession.close();
}
}
本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.数据库表建表SQL及数据如下：本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL COMMENT '城市名称',
`gdp` int(10) unsigned NOT NULL COMMENT '2019年GDP数据',
`people` int(10) unsigned NOT NULL COMMENT '人口:万',
`station` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='市区数据表';
INSERT INTO `city` VALUES ('1', '北京', '35371', '2153', '北京站、北京东站、北京南站、北京西站、北京北站');
INSERT INTO `city` VALUES ('2', '上海', '38155', '2428', '上海站、上海南站、上海虹桥站、上海西站等');
INSERT INTO `city` VALUES ('3', '深圳', '26927', '1343', '深圳站、深圳北站、深圳东站、深圳西站、福田站');
INSERT INTO `city` VALUES ('4', '合肥', '9409', '818', '合肥站、合肥南站、合肥西站、合肥北城站');
INSERT INTO `city` VALUES ('5', '青岛', '11741', '949', '青岛站、青岛北站、红岛站、青岛西站、董家口站');
INSERT INTO `city` VALUES ('6', '郑州', '11589', '1035', '郑州站、郑州东站、郑州南站、郑州西站等');
INSERT INTO `city` VALUES ('7', '杭州', '15373', '1036', '杭州站、杭州东站、杭州南站、杭州西站等');
INSERT INTO `city` VALUES ('8', '西安', '9321', '1020', '西安站、西安北站、西安南站等');
本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.其它还有trim，用于去除前后多余的前缀、后缀字符；set用于更新操作时的协助处理；bind、script等。用得较少，可以参考Mybatis官网文档动态SQL支持：https://mybatis.org/mybatis-3/zh/dynamic-sql.html本文地址:http://www.04007.cn/article/898.html,未经许可,不得转载.
本文地址:http://www.04007.cn/article/898.html 未经许可,不得转载. 手机访问本页请扫描右下方二维码.
|
|
手机扫码直接打开本页面 |
