バックグラウンド・アドバンスド・クエリーの実装(基礎版)

1高度なクエリーの基礎版

 ：
        1. ：XxxQuery/XxxQueryObject.(Xxx )；

          ProductQueryObject( )， 

        2. IproductDAO ：

          List<Product> query(ProductQueryObject qo);

        3. ProductDAOImpl 

        4. 。

高度なクエリー・テーブル・オブジェクトの作成

public class ProductQueryObject {
    private String productName;// 
    private BigDecimal minSalePrice;// 
    private BigDecimal maxSalePrice;// 
    //getter/setter
    public String getProductName() {
        return productName;
    }
    public BigDecimal getMinSalePrice() {
        return minSalePrice;
    }
    public BigDecimal getMaxSalePrice() {
        return maxSalePrice;
    }
    public void setProductName(String productName) {
        this.productName = productName;
    }
    public void setMinSalePrice(BigDecimal minSalePrice) {
        this.minSalePrice = minSalePrice;
    }
    public void setMaxSalePrice(BigDecimal maxSalePrice) {
        this.maxSalePrice = maxSalePrice;
    }
}

IproductDAOでの高度なクエリー方法の定義

public interface IProductDAO {
    List list();

    // 
    List query (ProductQueryObject qo);
}

ProductDAOImplでの高度なクエリー・メソッドの実装

public class ProductDAOImpl implements IProductDAO {

    public List query(ProductQueryObject qo) {
        SqlSession session = MyBatisUtil.INSTANCE .openSession();   
        //-------------------------------------------------------
        // SQL
        StringBuilder sql = new StringBuilder(" WHERE 1=1 ");
        Map sqlMap = new HashMap<>();

        // 
        if(StringUtil.hasLength(qo.getProductName())){// 
            sql.append(" AND productName LIKE CONCAT('%',#{name},'%')");
            sqlMap.put("name", qo.getProductName());
        }
        // 
        if(qo.getMinSalePrice()!=null){
            sql.append(" AND salePrice >= #{minSalePrice}");
            sqlMap.put("minSalePrice",qo.getMinSalePrice());
        }
        // 
        if(qo.getMaxSalePrice()!=null){
            sql.append(" AND salePrice <= #{maxSalePrice}");
            sqlMap.put("maxSalePrice", qo.getMaxSalePrice());
        }
        //-------------------------------------------------------
        try {
            sqlMap.put("querySql", sql.toString());
            return session.selectList("cn.itsource.shopping.mapper.ProductMapper.advanceQuery", sqlMap);
        } finally {
            session.close();
        }
    }
}

バックグラウンドテスト

    @Test
    public void testQuery() {
        ProductQueryObject qo = new ProductQueryObject();
        qo.setProductName("iphone8s");
        qo.setMinSalePrice(new BigDecimal("6000"));
        qo.setMaxSalePrice(new BigDecimal("9000"));
        //------------------------------------
        List list = dao.query(null);
        for (Product p : list) {
            System.out.println(p);`
        }
    }

xmlマッピングファイル

 
<mapper namespace="cn.itsource.shopping.mapper.ProductMapper">

    
    <select id="list" resultType="Product">
        SELECT * FROM product
    select>

    
    <select id="advanceQuery" parameterType="map" resultType="Product">
        SELECT * FROM product ${querySql}
    select>
mapper>

MyBataisのXMLプロファイル

<configuration>
    
    <properties resource="db.properties" />
    
    <typeAliases>
        <typeAlias type="cn.itsource.shopping.domain.Product" alias="Product" />
    typeAliases>
    
    <environments default="development">
        
        <environment id="development">
            
            <transactionManager type="JDBC" />
            
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${usename}" />
                <property name="password" value="${password}" />
            dataSource>
        environment>
    environments>
    
    <mappers>
        <mapper resource="cn\itsource\shopping\domain\ProductMapper.xml" />
    mappers>
configuration>

db.propertiesプロファイル

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcdemo?useSSL=false
usename=root
password=111111

MyBatisUtilツールクラス

public enum MyBatisUtil {
    INSTANCE;

    private static SqlSessionFactory sessionFactory = null;

    static {
        try {
            sessionFactory = new SqlSessionFactoryBuilder().build(Resources
                    .getResourceAsStream("MyBatis-config.xml"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public SqlSession openSession() {
        return sessionFactory.openSession();
    }
}

StringUtilツールクラス

public class StringUtil {
    public static boolean hasLength(String str) {
        return str != null && !"".equals(str.trim());
    }
}

商品対象類

public class Product {
    private Long id;
    private String productName;
    private String brand;
    private String supplier;
    private BigDecimal salePrice;
    private BigDecimal costPrice;
    private Double cutoff;
    private Long dir_id;// 
    public Long getId() {
        return id;
    }
    public String getProductName() {
        return productName;
    }
    public void setProductName(String productName) {
        this.productName = productName;
    }
    public String getBrand() {
        return brand;
    }
    public String getSupplier() {
        return supplier;
    }
    public BigDecimal getSalePrice() {
        return salePrice;
    }
    public BigDecimal getCostPrice() {
        return costPrice;
    }
    public Double getCutoff() {
        return cutoff;
    }
    public Long getDir_id() {
        return dir_id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public void setBrand(String brand) {
        this.brand = brand;
    }
    public void setSupplier(String supplier) {
        this.supplier = supplier;
    }
    public void setSalePrice(BigDecimal salePrice) {
        this.salePrice = salePrice;
    }
    public void setCostPrice(BigDecimal costPrice) {
        this.costPrice = costPrice;
    }
    public void setCutoff(Double cutoff) {
        this.cutoff = cutoff;
    }
    public void setDir_id(Long dir_id) {
        this.dir_id = dir_id;
    }
    public String toString() {
        return "Product [id=" + id + ", productName=" + productName + ", brand=" + brand + ", suppliet=" + supplier
                + ", salePrice=" + salePrice + ", costPrice=" + costPrice + ", cutoff=" + cutoff + ", dir_id=" + dir_id
                + "]";
    }
}