javaweb之条件查询和多表联合查询_java条件查询怎么写

文章目录

• 条件查询
• • 工具类封装
  • 使用1
  • 使用2
  • 使用条件
  • • 必须提供基础SQL
    • 空值处理规则​​
    • ​​特殊字符转义
• 多表联合查询
• • 一对一
  • 一对多

条件查询

public List<Bill> findListBy(Map<String,Object> maps) throws Exception{ //where a.productName like concat(\'%\',?,\'%\') and totalPrice>=? and totalPrice<=? List<Bill> billList = new ArrayList<>(); String sql = \"select a.id,a.billCode,a.productName,a.productCount,\\n\" + \"a.productUnit,a.productDesc,a.totalPrice,a.providerId,b.proName \" + \" from smbms_bill a left join smbms_provider b on a.providerId=b.id \" + \"where 1=1 \"; List<Object> params = new ArrayList<>(); if(maps.get(\"pName\")!=null){ sql += \" and a.productName like concat(\'%\',?,\'%\')\"; params.add(maps.get(\"pName\")); } if(maps.get(\"begin\")!=null){ sql+= \"and totalPrice>=?\"; params.add(maps.get(\"begin\")); } System.out.println(\"maps.get(\\\"end\\\")====\"+maps.get(\"end\")); if(maps.get(\"end\")!=null){ sql+= \"and totalPrice<=?\"; params.add(maps.get(\"end\")); } ResultSet rs = query(sql,params); while(rs.next()){ Bill bill = new Bill(); bill.setId(rs.getInt(\"id\")); bill.setBillCode(rs.getString(\"billCode\")); bill.setProductName(rs.getString(\"productName\")); bill.setProductCount(rs.getInt(\"productCount\")); bill.setProductUnit(rs.getString(\"productUnit\")); bill.setProductDesc(rs.getString(\"productDesc\")); bill.setTotalPrice(rs.getBigDecimal(\"totalPrice\")); Provider provider = new Provider(); provider.setId(rs.getInt(\"providerId\")); provider.setProName(rs.getString(\"proName\")); bill.setProvider(provider); billList.add(bill); } return billList; }

工具类封装

import java.util.ArrayList;import java.util.List;public class DynamicSqlBuilder { private final StringBuilder sqlBuilder; private final List<Object> params; private boolean whereAdded = false; public DynamicSqlBuilder(String baseSql) { this.sqlBuilder = new StringBuilder(baseSql); this.params = new ArrayList<>(); } /** * 添加等于条件（=） * @param column 数据库字段名 * @param value 条件值 */ public DynamicSqlBuilder eq(String column, Object value) { addCondition(column, value, \" = ?\"); return this; } /** * 添加模糊查询条件（LIKE） * @param column 数据库字段名 * @param value 条件值 */ public DynamicSqlBuilder like(String column, String value) { if (value != null && !value.isEmpty()) { addCondition(column, \"%\" + value + \"%\", \" LIKE ?\"); } return this; } /** * 添加大于条件（>） * @param column 数据库字段名 * @param value 条件值 */ public DynamicSqlBuilder gt(String column, Object value) { addCondition(column, value, \" > ?\"); return this; } /** * 添加IN条件（IN） * @param column 数据库字段名 * @param values 值列表 */ public DynamicSqlBuilder in(String column, List<?> values) { if (values != null && !values.isEmpty()) { addWhereClause(); sqlBuilder.append(column).append(\" IN (\"); for (int i = 0; i < values.size(); i++) { sqlBuilder.append(i > 0 ? \",?\" : \"?\"); params.add(values.get(i)); } sqlBuilder.append(\")\"); } return this; } // 核心条件拼接逻辑 private void addCondition(String column, Object value, String operator) { if (value != null && !(value instanceof String && ((String) value).isEmpty())) { addWhereClause(); sqlBuilder.append(column).append(operator); params.add(value); } } // 动态添加WHERE关键字 private void addWhereClause() { if (!whereAdded) { sqlBuilder.append(\" WHERE \"); whereAdded = true; } else { sqlBuilder.append(\" AND \"); } } /** * 构建最终SQL和参数 * @return 包含SQL字符串和参数数组的对象 */ public SqlResult build() { return new SqlResult(sqlBuilder.toString(), params.toArray()); } // 返回结果封装类 public static class SqlResult { private final String sql; private final Object[] params; public SqlResult(String sql, Object[] params) { this.sql = sql; this.params = params; } public String getSql() { return sql; } public Object[] getParams() { return params; } }}

使用1

public class TestDemo { public static void main(String[] args) { // 1. 基础SQL（必须包含SELECT和FROM） String baseSql = \"SELECT * FROM users\"; // 2. 动态构建条件 DynamicSqlBuilder builder = new DynamicSqlBuilder(baseSql) .eq(\"status\", 1)// 状态为1 .like(\"name\", \"张\")  // 名字包含\"张\" .gt(\"age\", 18) // 年龄大于18 .in(\"department_id\", List.of(101, 203)); // 部门ID在列表中 // 3. 获取结果 DynamicSqlBuilder.SqlResult result = builder.build(); // 4. 输出结果 System.out.println(\"完整SQL: \" + result.getSql()); System.out.println(\"参数数组: \" + Arrays.toString(result.getParams())); }}

使用2

@Test public void test01(){ String name = \"张三\"; String pwd = null; String add = \"龙鳞路\"; DynamicSqlBuilder dynamicSqlBuilder = new DynamicSqlBuilder(\"SELECT * FROM users\"); dynamicSqlBuilder.eq(\"name\", name); dynamicSqlBuilder.eq(\"pwd\", pwd); dynamicSqlBuilder.eq(\"add\", add); DynamicSqlBuilder.SqlResult result = dynamicSqlBuilder.build(); System.out.println(result.getSql()); Object[] objects = result.getParams(); for (Object obj:objects){ System.out.println(obj); } }

完整SQL: SELECT * FROM users WHERE status = ? AND name LIKE ? AND age > ? AND department_id IN (?,?)参数数组: [1, \"%张%\", 18, 101, 203]

 核心特性​​智能条件拼接​​自动跳过null或空字符串条件 首次添加条件时自动插入WHERE关键字，后续条件用AND连接支持=、LIKE、>、IN等常用操作符​​防SQL注入设计​​所有值通过?占位符处理参数值独立存储在Object[]数组，符合预编译规范 ​​链式调用​​采用builder.eq(...).like(...)的流畅接口风格类似MyBatis-Plus的Wrapper写法 ​​IN语句优化​​自动展开List为(?,?,?)格式支持任意类型的集合参数

使用条件

必须提供基础SQL

// 正确示例new DynamicSqlBuilder(\"SELECT id,name FROM user\")// 错误示例（缺少SELECT/FROM）new DynamicSqlBuilder(\"user\")

空值处理规则​​

字符串：跳过null和\"\"
数字：跳过null
集合：跳过null和空集合

​​特殊字符转义

​​特殊字符转义​​如果参数包含%或_等特殊字符，需在调用前转义

.like(\"name\", \"100%\".replace(\"%\", \"\\\\%\"))

多表联合查询

一对一

public List<Bill> findListBy(Map<String,Object> maps) throws Exception{List<Bill> billList = new ArrayList<>();ResultSet rs = query(sql,params);while(rs.next()){ Bill bill = new Bill(); bill.setId(rs.getInt(\"id\")); bill.setBillCode(rs.getString(\"billCode\")); bill.setProductName(rs.getString(\"productName\")); bill.setProductCount(rs.getInt(\"productCount\")); bill.setProductUnit(rs.getString(\"productUnit\")); bill.setProductDesc(rs.getString(\"productDesc\")); bill.setTotalPrice(rs.getBigDecimal(\"totalPrice\")); Provider provider = new Provider(); provider.setId(rs.getInt(\"providerId\")); provider.setProName(rs.getString(\"proName\")); bill.setProvider(provider); billList.add(bill); } return billList;}

一对多

public class ProviderDaoImpl extends BaseDao implements ProviderDao, BaseInterface<Provider> { @Override public Provider findProviderById(Integer id) throws Exception{ String sql = \"select id,proName from smbms_provider where id=?\"; String billSql = \"select id,billCode from smbms_bill where prividerId=?\"; Provider provider = null; ResultSet rs = query(sql,null); while (rs.next()){ provider = new Provider(); provider.setId(rs.getInt(\"id\")); provider.setProName(rs.getString(\"proName\")); ResultSet resultSet = query(billSql,null); List<Bill> billList = new ArrayList<>(); while(resultSet.next()){ Bill bill = new Bill(); bill.setId(rs.getInt(\"id\")); bill.setBillCode(rs.getString(\"billCode\")); billList.add(bill); } provider.setList(billList); } return provider; }