{"moduleinfo":{"card_count":[{"count_phone":1,"count":1}],"search_count":[{"count_phone":4,"count":4}]},"card":[{"des":"阿里云数据库专家保驾护航，为用户的数据库应用系统进行性能和风险评估，参与配合进行数据压测演练，提供数据库优化方面专业建议，在业务高峰期与用户共同保障数据库系统平稳运行。","link1":"https://www.aliyun.com/service/optimization/database","link":"https://www.aliyun.com/service/chiefexpert/database","icon":"https://img.alicdn.com/tfs/TB1a5ZfonnI8KJjy0FfXXcdoVXa-100-100.png","btn2":"数据库紧急救援服务","tip":"还有更多专家帮助您解决云上业务问题：立即查看","btn1":"云上数据库优化服务","link2":"https://www.aliyun.com/service/databaserescue","title":"数据库专家服务"}],"search":[{"txt":"安全首席专家","link":"https://www.aliyun.com/service/chiefexpert/security"},{"txt":"中间件首席专家","link":"https://www.aliyun.com/service/chiefexpert/middleware"},{"txt":"系统与平台首席专家","link":"https://www.aliyun.com/service/chiefexpert/platform"},{"txt":"存储首席专家","link":"https://www.aliyun.com/service/chiefexpert/storage"}],"countinfo":{"search":{"length_pc":0,"length":0},"card":{"length_pc":0,"length":0}},"simplifiedDisplay":"newEdition","newCard":[{"link":"https://www.aliyun.com/product/rds/mysql","icon":"rds_mysql1","contentLink":"https://www.aliyun.com/product/rds/mysql","title":"云数据库RDS MySQL版","des":"云数据库RDS MySQL版 MySQL 是全球最受欢迎的开源数据库，阿里云MySQL版 通过深度的内核优化和独享实例提供稳定极致的数据库性能，同时灵活的部署架构及产品形态，可满足不同场景下的数据库需求。","btn1":"了解详情","link1":"https://www.aliyun.com/product/rds/mysql","btn2":"管理控制台","link2":"https://rdsnext.console.aliyun.com/#/rdsList/cn-hangzhou/basic/","btn3":"在线咨询","link3":"https://www.aliyun.com/core/online-consult?spm=5176.8789780.7y9jhqsfz.115.47ea55caeL2Mfu&from=OVtXEBmlH8","infoGroup":[{"infoName":"最新活动","infoContent":{"firstContentName":"MySQL 1年仅19.9元","firstContentLink":"https://www.aliyun.com/database/dbfirstbuy#J_9036464270","lastContentName":"ECS+MySQL 0.7元/日","lastContentLink":"https://www.aliyun.com/1111/enterprise"}},{"infoName":"快速入门","infoContent":{"firstContentName":"选型指南","firstContentLink":"https://help.aliyun.com/document_detail/164594.html?spm=a2c4g.11186623.6.551.56b67384ispvvk","lastContentName":"上手指南","lastContentLink":"https://help.aliyun.com/document_detail/26124.html?spm=a2c4g.11174283.2.16.51795b83pbkzav"}},{"infoName":"最佳实践","infoContent":{"firstContentName":"X-Engine的应用场景","firstContentLink":"https://help.aliyun.com/document_detail/148402.html?spm=a2c4g.11186623.2.14.3f592685zjuUxu#concept-2364901","lastContentName":"读写分离功能","lastContentLink":"https://help.aliyun.com/document_detail/96073.html?spm=a2c4g.11174283.2.53.42db5b83VvZvMJ"}},{"infoName":"产品推荐","infoContent":{"firstContentName":"云数据库PolarDB","lastContentName":"云原生数据仓库MySQL","firstContentLink":"https://www.aliyun.com/product/polardb","lastContentLink":"https://www.aliyun.com/product/ads"}}]}],"visual":{"textColor":"dark","topbg":""}}
{"$env":{"JSON":{}},"$page":{"env":"production"},"$context":{"moduleinfo":{"card_count":[{"count_phone":1,"count":1}],"search_count":[{"count_phone":4,"count":4}]},"card":[{"des":"阿里云数据库专家保驾护航，为用户的数据库应用系统进行性能和风险评估，参与配合进行数据压测演练，提供数据库优化方面专业建议，在业务高峰期与用户共同保障数据库系统平稳运行。","link1":"https://www.aliyun.com/service/optimization/database","link":"https://www.aliyun.com/service/chiefexpert/database","icon":"https://img.alicdn.com/tfs/TB1a5ZfonnI8KJjy0FfXXcdoVXa-100-100.png","btn2":"数据库紧急救援服务","tip":"还有更多专家帮助您解决云上业务问题：立即查看","btn1":"云上数据库优化服务","link2":"https://www.aliyun.com/service/databaserescue","title":"数据库专家服务"}],"search":[{"txt":"安全首席专家","link":"https://www.aliyun.com/service/chiefexpert/security"},{"txt":"中间件首席专家","link":"https://www.aliyun.com/service/chiefexpert/middleware"},{"txt":"系统与平台首席专家","link":"https://www.aliyun.com/service/chiefexpert/platform"},{"txt":"存储首席专家","link":"https://www.aliyun.com/service/chiefexpert/storage"}],"countinfo":{"search":{"length_pc":0,"length":0},"card":{"length_pc":0,"length":0}},"simplifiedDisplay":"newEdition","newCard":[{"link":"https://www.aliyun.com/product/rds/mysql","icon":"rds_mysql1","contentLink":"https://www.aliyun.com/product/rds/mysql","title":"云数据库RDS MySQL版","des":"云数据库RDS MySQL版 MySQL 是全球最受欢迎的开源数据库，阿里云MySQL版 通过深度的内核优化和独享实例提供稳定极致的数据库性能，同时灵活的部署架构及产品形态，可满足不同场景下的数据库需求。","btn1":"了解详情","link1":"https://www.aliyun.com/product/rds/mysql","btn2":"管理控制台","link2":"https://rdsnext.console.aliyun.com/#/rdsList/cn-hangzhou/basic/","btn3":"在线咨询","link3":"https://www.aliyun.com/core/online-consult?spm=5176.8789780.7y9jhqsfz.115.47ea55caeL2Mfu&from=OVtXEBmlH8","infoGroup":[{"infoName":"最新活动","infoContent":{"firstContentName":"MySQL 1年仅19.9元","firstContentLink":"https://www.aliyun.com/database/dbfirstbuy#J_9036464270","lastContentName":"ECS+MySQL 0.7元/日","lastContentLink":"https://www.aliyun.com/1111/enterprise"}},{"infoName":"快速入门","infoContent":{"firstContentName":"选型指南","firstContentLink":"https://help.aliyun.com/document_detail/164594.html?spm=a2c4g.11186623.6.551.56b67384ispvvk","lastContentName":"上手指南","lastContentLink":"https://help.aliyun.com/document_detail/26124.html?spm=a2c4g.11174283.2.16.51795b83pbkzav"}},{"infoName":"最佳实践","infoContent":{"firstContentName":"X-Engine的应用场景","firstContentLink":"https://help.aliyun.com/document_detail/148402.html?spm=a2c4g.11186623.2.14.3f592685zjuUxu#concept-2364901","lastContentName":"读写分离功能","lastContentLink":"https://help.aliyun.com/document_detail/96073.html?spm=a2c4g.11174283.2.53.42db5b83VvZvMJ"}},{"infoName":"产品推荐","infoContent":{"firstContentName":"云数据库PolarDB","lastContentName":"云原生数据仓库MySQL","firstContentLink":"https://www.aliyun.com/product/polardb","lastContentLink":"https://www.aliyun.com/product/ads"}}]}],"visual":{"textColor":"dark","topbg":""}}}

关于数据库查询

如果查询条件是一列等于某一列+字符串或者几个字符串连接，可以使用concat(‘字符串’,‘字符串’);

列如查询首都是国家的名称加上"城市"有哪些国家？
SELECT name
FROM world
WHERE capital = concat(name,' City')

sql语句动态拼接多个查询条件查询数据库

问题描述：

公司开发中自己负责的模块业务需要根据用户选择内容去查询数据库，其中会计期间是必须的条件，用户不选择则默认为当前日期。用户只需要根据需要去输入就可以进行相关查询。不选择不拼接。
直接上图上代码！！！


后台打印sql为：

拼接会计期间 :  select * from jyqrsj where  f_kjqj = ‘201801’

拼接单位1select * from jyqrsj where  f_kjqj = ‘201801’ and((f_ysdw like ‘100010161016’))

拼接单位2select * from jyqrsj where  f_kjqj = ‘201801’ and((f_ysdw like ‘100010161016’))  and ((f_yfdw like ‘10001010’))

拼接抵消代码select * from jyqrsj where  f_kjqj = ‘201801’ and((f_ysdw like ‘100010161016’))  and ((f_yfdw like ‘10001010’))  and f_dxcode = ‘10009002’

拼接签认类型select * from jyqrsj where  f_kjqj = ‘201801’ and((f_ysdw like ‘100010161016’))  and ((f_yfdw like ‘10001010’))  and f_dxcode = ‘10009002’  and F_LXBH in(1002,1003,1004,1005,1006)

select * from jyqrsj where  f_kjqj = ‘201801’ and((f_ysdw like ‘100010161016’))  and ((f_yfdw like ‘10001010’))  and f_dxcode = ‘10009002’  and F_LXBH in(1002,1003,1004,1005,1006)
条件全选情况下没有问题。接下来进行条件拼接查询。还是直接上图！！


后台查询结果为：


哈哈，上代码吧！
	TQueryData queryData = new TQueryData();
	ResultSet rs = null;
	// 进行sql 语句的动态拼接查询
	String sql = "select * from jyqrsj";// 原始的数据查询
	Object kjqj = param.getProperty("KJQJ");// 校验日期
	Object dwbh1 = param.getProperty("DWBH1");// 单位编号1
	Object dwbh2 = param.getProperty("DWBH2");// 单位编号2
	Object dxdwbh = param.getProperty("DXDWBH");// 抵消单位编号
	Object qrlx = param.getProperty("QRLX");// 签认类型
	int count = 0;
	// 必选项不为空时进行where条件拼接
	if (!(dwbh1.toString().equals("")) || !(dwbh2.toString().equals(""))
			|| !(dxdwbh.toString().equals(""))
			|| !(qrlx.toString().equals(""))) {
		sql = sql + " where ";
		sql = sql + " f_kjqj = '" + kjqj + "'";
		System.err.println("拼接会计期间 :  " + sql);

		if (dwbh1.toString().equals(""))
			sql = sql + "";
		else {
			if (count > 0)
				sql = sql + "and";
			sql = sql + " and((f_ysdw like '" + dwbh1 + "')) ";
			count++;

			if (dwbh2.toString().equals(""))
				sql = sql + "";
			else {
				if (count > 0)
					sql = sql + " and ((f_yfdw like '" + dwbh2 + "')) ";
				count++;
			}

			if (dxdwbh.toString().equals(""))
				sql = sql + "";
			else {
				if (count > 0)
					sql = sql + " and f_dxcode = '" + dxdwbh + "' ";
				count++;
			}

			if (qrlx.toString().equals(""))
				sql = sql + "";
			else {
				if (count > 0)
					sql = sql + " and F_LXBH in(" + qrlx + ")";
				count++;
			}
		}
	}
	rs = conn.createStatement().executeQuery(sql);

需要注意的是：

水无长形，有些逻辑需要根据自身去改变。例如这个拼接查询条件思想大致为：
根据数据库表中字段对象，定义一个变量count用来计数。将前台拼接条件做if 的 各个条件的非空判断，所有拼接条件有一个满足条件就进行查询基础表。然后 拼接sql = sql + “where”; 使用where关键字去拼接后面的条件。这个地方我的需求不一样就做了变动。
注意大括号的使用，如下图 红框处需要注意，已经标注出来。在大括号和count++上踩了坑。

假如我有5个输入框，代表5个查询条件，怎样才能实现根据用户自由输入一个或多个条件的不同而得到不同的查询结果呢？？
public List getCstServiceByPage(int page, int everypage,
CstService ser, String d1, String d2, String status) {
String hql = "select s,c.custName from CstService s,CstCustomer c where s.cstCustomer.custNo=c.custNo and s.svrStatus='"
+ status + "'";
if (ser.getSvrCustName() != null && !ser.getSvrCustName().equals("")) {
hql += " and s.svrCustName like '%" + ser.getSvrCustName() + "%'";
}
if (ser.getSvrTitle() != null && !ser.getSvrTitle().equals("")) {
hql += " and s.svrTitle='" + ser.getSvrTitle() + "'";
}
if (ser.getSvrType() != null && !ser.getSvrType().equals("")) {
if (ser.getSvrType().equals("全部")) {
hql = hql;
} else {
hql += " and s.svrType='" + ser.getSvrType() + "'";
}
}
if (!d1.equals("") && d1 != null && !d2.equals("") && d2 != null) {
hql += " and s.svrCreateDate between to_date('" + d1
+ "','yyyy-mm-dd') and to_date('" + d2 + "','yyyy-mm-dd')";
}
List list = HibernateBaseDao.bypage(hql, page, everypage);
Iterator iter = list.iterator();
List list2 = new ArrayList();
while (iter.hasNext()) {
Object[] obj = (Object[]) iter.next();
CstService service = (CstService) obj[0];
String custName = obj[1].toString();
CstCustomer c = new CstCustomer();
c.setCustName(custName);
service.setCstCustomer(c);
list2.add(service);
}
return list2;
}
穿个对象进去