MyBatis快速入门——第四章、mybatis动态sql_if_choose_when
测试sql
数据库名称【mytest】，编码类型【utf8】
代码语言：javascript复制DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`createDate` datetime(0) NOT NULL,
`modifyDate` datetime(0) NOT NULL,
`productName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`productTitle` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`productPrice` decimal(10, 2) NOT NULL,
`productCount` int(8) NOT NULL,
`productType` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`productColor` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`productWeight` double NULL DEFAULT NULL,
`productStatus` int(1) NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `product` VALUES ('b383581fd20211ec84b500e070bfdb54', '2022-05-12 22:49:18', '2022-05-12 22:49:18', '外星人M15', '高端外星人', 13499.00, 299, '外星人', 'black', 3300, 1);
INSERT INTO `product` VALUES ('b3839547d20211ec84b500e070bfdb54', '2022-05-12 22:49:18', '2022-05-12 22:49:18', 'ThinkBook', '联想', 4599.00, 159, '联想', 'gray', 2250, 1);
INSERT INTO `product` VALUES ('b383d49dd20211ec84b500e070bfdb54', '2022-05-12 22:49:18', '2022-05-12 22:49:18', '戴尔G15', '戴尔', 7499.00, 179, '戴尔', 'gray', 2270, 1);
INSERT INTO `product` VALUES ('b384180cd20211ec84b500e070bfdb54', '2022-05-12 22:49:18', '2022-05-12 22:49:18', 'RedmiBook Pro15', '小米', 4499.00, 699, '小米', 'black', 2500, 1);
INSERT INTO `product` VALUES ('b38457bed20211ec84b500e070bfdb54', '2022-05-12 22:49:18', '2022-05-12 22:49:18', '华硕a豆', '华硕', 3699.00, 799, '华硕', 'pink', 2100, 1);
INSERT INTO `product` VALUES ('f6715eb2d20111ec84b500e070bfdb54', '2022-05-12 22:44:01', '2022-05-12 22:44:01', '拯救者Y7700P', '2022新品拯救者', 7399.00, 199, '联想', 'gray', 2200, 1);mybatis-config.xml文件
代码语言：javascript复制<?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>
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<typeAliases>
<package name="com.item.model"/>
</typeAliases>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/mytest?characterEncoding=utf-8"/>
<property name="username" value="root"/>
<property name="password" value="12345678"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/item.mapper/ProductMapper.xml"></mapper>
</mappers>
</configuration>ProductMapper.xml文件
情况1、纯if判断
代码语言：javascript复制<?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.item.mapper.ProductMapper">
<select id="GetInfo" resultType="Product">
select * from product
<if test="productName!=null or productType!=null or productColor!=null ">
where 1=1
</if>
<!-- 模糊查询 -->
<if test="productName!=null">
and productName like "%${productName}%"
</if>
<!-- 类型筛选 -->
<if test="productType!=null">
and productType="${productType}"
</if>
<!-- 颜色筛选 -->
<if test="productColor!=null">
and productColor="${productColor}"
</if>
</select>
</mapper>情况2：choose when（if else）
代码语言：javascript复制<?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.item.mapper.ProductMapper">
<select id="GetInfo" resultType="Product">
select * from product
<if test="productName!=null or productType!=null or productColor!=null ">
where 1=1
</if>
<choose>
<when test="productName!=null">
and productName like "%${productName}%"
</when>
<when test="productType!=null">
and productType = "${productType}"
</when>
<when test="productType!=null">
and productColor = "${productColor}"
</when>
</choose>
</select>
</mapper>数据库工具类
代码语言：javascript复制package com.item.common;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.Reader;
public class JDBC {
public static SqlSessionFactory GetConn(){
Reader reader = null;
try {
reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
return factory;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}Product
代码语言：javascript复制package com.item.model;
import java.math.BigDecimal;
import java.util.Date;
public class Product {
private String id;
private Date createDate;
private Date modifyDate;
private String productName;
private String productTitle;
private BigDecimal productPrice;
private int productCount;
private String productType;
private String productColor;
private double productWeight;
private int productStatus;
@Override
public String toString() {
return "Product{"
"id='" id '''
", createDate=" createDate
", modifyDate=" modifyDate
", productName='" productName '''
", productTitle='" productTitle '''
", productPrice=" productPrice
", productCount=" productCount
", productType='" productType '''
", productColor='" productColor '''
", productWeight=" productWeight
", productStatus=" productStatus
'}';
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public Date getCreateDate() {
return createDate;
}
public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
public Date getModifyDate() {
return modifyDate;
}
public void setModifyDate(Date modifyDate) {
this.modifyDate = modifyDate;
}
public String getProductName() {
return productName;
}
public void setProductName(String productName) {
this.productName = productName;
}
public String getProductTitle() {
return productTitle;
}
public void setProductTitle(String productTitle) {
this.productTitle = productTitle;
}
public BigDecimal getProductPrice() {
return productPrice;
}
public void setProductPrice(BigDecimal productPrice) {
this.productPrice = productPrice;
}
public int getProductCount() {
return productCount;
}
public void setProductCount(int productCount) {
this.productCount = productCount;
}
public String getProductType() {
return productType;
}
public void setProductType(String productType) {
this.productType = productType;
}
public String getProductColor() {
return productColor;
}
public void setProductColor(String productColor) {
this.productColor = productColor;
}
public double getProductWeight() {
return productWeight;
}
public void setProductWeight(double productWeight) {
this.productWeight = productWeight;
}
public int getProductStatus() {
return productStatus;
}
public void setProductStatus(int productStatus) {
this.productStatus = productStatus;
}
}ProductMapper
代码语言：javascript复制package com.item.mapper;
import com.item.model.Product;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface ProductMapper {
List<Product> GetInfo(@Param("productName") String productName,
@Param("productType") String productType,
@Param("productColor") String productColor);
}ProduceDAO
代码语言：javascript复制package com.item.dao;
import com.item.common.JDBC;
import com.item.mapper.ProductMapper;
import com.item.model.Product;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import java.util.List;
public class ProduceDAO {
/**
* 各类查询
* @param productName (商品名称)
* @param productType (商品类型)
* @param productColor (商品颜色)
* @return
*/
public static List<Product> GetInfo(String productName,
String productType,
String productColor) {
SqlSessionFactory factory = JDBC.GetConn();
SqlSession session = factory.openSession();
ProductMapper db = session.getMapper(ProductMapper.class);
List<Product> list = db.GetInfo(productName, productType, productColor);
session.close();
return list;
}
}Action
代码语言：javascript复制package com.item.action;
import com.item.dao.ProduceDAO;
import com.item.model.Product;
import java.util.List;
public class Action {
public static void main(String[] args){
List<Product> list = ProduceDAO.GetInfo(null,"联想",null);
for (Product p : list) {
System.out.println("编号" p.getId());
System.out.println("创建时间" p.getCreateDate());
System.out.println("修改时间" p.getModifyDate());
System.out.println("产品名称" p.getProductName());
System.out.println("产品标题" p.getProductTitle());
System.out.println("产品价格" p.getProductPrice());
System.out.println("产品数量" p.getProductCount());
System.out.println("品牌类型" p.getProductType());
System.out.println("重量" p.getProductWeight());
System.out.println("状态" (p.getProductStatus()==1?"上架":"下架"));
}
}
}执行效果：
注意1=1用于肯定查询，不是所有的1=1都是注入攻击。
