Spring NamedParameterJdbcTemplate命名参数查询条件封装,
NamedParameterJdbcTemplate查询封装
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
©Copyright 蕃薯耀 2017年7月25日
http://www.cnblogs.com/fanshuyao/
由于在项目中想用Spring的NamedParameterJdbcTemplate,但又想写的sql查询简单一点,少一些,所以封装了Sql命名查询。使用此工具,主要是让where条件的查询及排序和分页简单点,现在使用的是mysql。
一、SqlBuffer查询对象封装(现在只能select语句的封装)
- import java.util.HashMap;
- import java.util.Map;
- public class SqlBuffer {
- /**
- * 空格
- */
- private final String SPACE = " ";
- /**
- * 冒号
- */
- private final String COLON = ":";
- /**
- * 逗号,
- */
- private final String COMMA = ",";
- /**
- * where关键字
- */
- private final String WHERE_SQL = "WHERE";
- /**
- * order by 关键字
- */
- private final String ORDER_BY_SQL = "ORDER BY";
- /**
- * limit关键字
- */
- private final String LIMIT_SQL = "LIMIT";
- /**
- * where 1=1条件
- */
- private final String WHERE_INIT = WHERE_SQL + " 1=1 ";
- private final String LEFT_BRACKET = "(";
- private final String RIGHT_BRACKET = ")";
- /**
- * 百分号%
- */
- private final String PERCENT_SIGN = "%";
- /**
- * 单引号 '
- */
- private final String SINGLE_QUOTE = "'";
- private StringBuffer baseSql = new StringBuffer("");
- private StringBuffer whereSql = new StringBuffer("");
- private StringBuffer orderBySql = new StringBuffer("");
- private StringBuffer limitSql = new StringBuffer("");
- public Map<String, Object> paramsMap = new HashMap<String, Object>();
- private int whereIndex = -1;
- private int orderByIndex = -1;
- private int limitIndex = -1;
- private int offSize;
- private int pageSize;
- private boolean isNewStart = false;
- private boolean isEndStart = false;
- public SqlBuffer(){}
- public SqlBuffer(String sql){
- init(sql);
- }
- private int getWhereIndex(String sql){
- if(sql != null){
- return sql.indexOf(WHERE_SQL);
- }
- return -1;
- }
- private int getOrderByIndex(String sql){
- if(sql != null){
- return sql.indexOf(ORDER_BY_SQL);
- }
- return -1;
- }
- private int getLimitIndex(String sql){
- if(sql != null){
- return sql.indexOf(LIMIT_SQL);
- }
- return -1;
- }
- /**
- * 判断有没有where查询语句,有则返回true
- * @return
- */
- private boolean hadWhereSql(){
- if(this.whereIndex > -1){
- return true;
- }
- return false;
- }
- /**
- * 判断有没有order by查询语句,有则返回true
- * @return
- */
- private boolean hadOrderBySql(){
- if(this.orderByIndex > -1){
- return true;
- }
- return false;
- }
- /**
- * 判断有没有limit查询语句,有则返回true
- * @return
- */
- private boolean hadLimitSql(){
- if(this.limitIndex > -1){
- return true;
- }
- return false;
- }
- /**
- * 初始化操作
- * @param sql
- */
- private void init(String sql){
- if(sql != null){
- String sqlUpper = sql.trim().toUpperCase();
- this.whereIndex = getWhereIndex(sqlUpper);
- this.orderByIndex = getOrderByIndex(sqlUpper);
- this.limitIndex = getLimitIndex(sqlUpper);
- if(!hadWhereSql() && !hadOrderBySql() && !hadLimitSql()){
- baseSql.append(sql);
- }else{
- if(hadWhereSql()){
- baseSql.append(sql.substring(0, whereIndex));
- }else if(!hadWhereSql() && hadOrderBySql()){
- if(hadOrderBySql()){
- baseSql.append(sql.substring(0, orderByIndex));
- }
- }else if(!hadWhereSql() && !hadOrderBySql() && hadLimitSql()){
- baseSql.append(sql.substring(0, limitIndex));
- }
- }
- int whereStartIndex = whereIndex + WHERE_SQL.length();//where条件不需要加where
- if(hadWhereSql() && hadOrderBySql() && hadLimitSql()){
- whereSql.append(sql.substring(whereStartIndex, orderByIndex));
- orderBySql.append(sql.substring(orderByIndex, limitIndex));
- limitSql.append(sql.substring(limitIndex));
- }else if(hadWhereSql() && hadOrderBySql() && !hadLimitSql()){
- whereSql.append(sql.substring(whereStartIndex, orderByIndex));
- orderBySql.append(sql.substring(orderByIndex));
- }else if(hadWhereSql() && !hadOrderBySql() && hadLimitSql()){
- whereSql.append(sql.substring(whereStartIndex, limitIndex));
- limitSql.append(sql.substring(limitIndex));
- }else if(hadWhereSql() && !hadOrderBySql() && !hadLimitSql()){
- whereSql.append(sql.substring(whereStartIndex));
- }else if(!hadWhereSql() && hadOrderBySql() && hadLimitSql()){
- orderBySql.append(sql.substring(orderByIndex, limitIndex));
- limitSql.append(sql.substring(limitIndex));
- }else if(!hadWhereSql() && hadOrderBySql() && !hadLimitSql()){
- orderBySql.append(sql.substring(orderByIndex));
- }else if(!hadWhereSql() && !hadOrderBySql() && hadLimitSql()){
- limitSql.append(sql.substring(limitIndex));
- }
- }
- }
- /**
- * and条件连接
- * @param columnName 表的字段名称
- * @param columnValue 查询字段名称对应的值
- * <li>如果whereType为Null或者NotNull,该值可以为空</li>
- * <li>如果whereType为IN,该值为List类型,如:Arrays.asList("aa", "bb")</li>
- * @param whereType WhereType枚举,如like,>=
- * @return SqlBuffer
- */
- public SqlBuffer and(String columnName, Object columnValue, WhereType whereType){
- return add(ConnectType.AND, columnName, columnValue, null, whereType);
- }
- /**
- * or条件连接,使用columnName作为命名参数
- * @param columnName 表的字段名称
- * @param columnValue 查询字段名称对应的值
- * <li>如果whereType为Null或者NotNull,该值可以为空</li>
- * <li>如果whereType为IN,该值为List类型,如:Arrays.asList("aa", "bb")</li>
- * @param whereType WhereType枚举,如like,>=
- * @return SqlBuffer
- */
- public SqlBuffer or(String columnName, Object columnValue, WhereType whereType){
- return add(ConnectType.OR, columnName, columnValue, null, whereType);
- }
- /**
- * 加上表字段不为空的判断
- * <li>如columnName is not null</li>
- * @param connectType ConnectType枚举
- * @param columnName 表的字段名称
- * @return SqlBuffer
- */
- public SqlBuffer isNotNull(ConnectType connectType, String columnName){
- return add(connectType, columnName, null, null, WhereType.NOT_NULL);
- }
- /**
- * 加上表字段不为空的判断,使用And连接
- * <li>如columnName is not null</li>
- * @param columnName 表的字段名称
- * @return SqlBuffer
- */
- public SqlBuffer isNotNull(String columnName){
- return add(ConnectType.AND, columnName, null, null, WhereType.NOT_NULL);
- }
- /**
- * 加上表字段为空的判断
- * <li>如columnName is null</li>
- * @param connectType ConnectType枚举
- * @param columnName 表的字段名称
- * @return SqlBuffer
- */
- public SqlBuffer isNull(ConnectType connectType, String columnName){
- return add(connectType, columnName, null, null, WhereType.NULL);
- }
- /**
- * 加上表字段为空的判断,使用And连接
- * <li>如columnName is not null</li>
- * @param columnName 表的字段名称
- * @return SqlBuffer
- */
- public SqlBuffer isNull(String columnName){
- return add(ConnectType.AND, columnName, null, null, WhereType.NULL);
- }
- /**
- * and条件连接,使用columnName作为命名参数
- * @param columnName 表的字段名称
- * @param columnValue 查询字段名称对应的值
- * <li>如果whereType为Null或者NotNull,该值可以为空</li>