Spring NamedParameterJdbcTemplate命名参数查询条件封装， NamedParameterJdbcTemplate查询封装...

最新推荐文章于 2021-09-24 09:43:22 发布

weixin_33795093

最新推荐文章于 2021-09-24 09:43:22 发布

阅读量135

点赞数

文章标签： java 数据库

原文链接：http://www.cnblogs.com/fanshuyao/p/7234113.html

版权

Spring NamedParameterJdbcTemplate命名参数查询条件封装，

NamedParameterJdbcTemplate查询封装

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

©Copyright 蕃薯耀 2017年7月25日

http://www.cnblogs.com/fanshuyao/

由于在项目中想用Spring的NamedParameterJdbcTemplate，但又想写的sql查询简单一点，少一些，所以封装了Sql命名查询。使用此工具，主要是让where条件的查询及排序和分页简单点，现在使用的是mysql。

一、SqlBuffer查询对象封装（现在只能select语句的封装）

Java代码

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>
* <li>如果whereType为IN，该值为List类型</li>
* <li>如果whereType为IN，该值为List类型，如：Arrays.asList("aa", "bb")</li>
* @param placeholder 命名参数
* <li>如果为Null，则使用表的字段名称作为命名参数</li>
* @param whereType WhereType枚举，如like，>=
* @return SqlBuffer
*/
public SqlBuffer and(String columnName, Object columnValue, String placeholder, WhereType whereType){
if(StrUtils.isBlank(columnName)){
return this;
}
return add(ConnectType.AND, columnName, columnValue, placeholder, whereType);
}
/**
* or条件连接
* @param columnName 表的字段名称
* @param columnValue 查询字段名称对应的值
* <li>如果whereType为Null或者NotNull，该值可以为空</li>
* <li>如果whereType为IN，该值为List类型，如：Arrays.asList("aa", "bb")</li>
* @param placeholder 命名参数
* <li>如果为Null，则使用表的字段名称作为命名参数</li>
* @param whereType WhereType枚举，如like，>=
* @return SqlBuffer
*/
public SqlBuffer or(String columnName, Object columnValue, String placeholder, WhereType whereType){
if(StrUtils.isBlank(columnName)){
return this;
}
return add(ConnectType.OR, columnName, columnValue, placeholder, whereType);
}
/**
* 命名参数查询，使用columnName作为命名参数
* @param connectType ConnectType枚举
* @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 add(ConnectType connectType, String columnName, Object columnValue, WhereType whereType){
return add(connectType, columnName, columnValue, null, whereType);
}
/**
* 命名参数查询
* @param connectType ConnectType枚举
* @param columnName 表的字段名称
* @param columnValue 查询字段名称对应的值
* <li>如果whereType为Null或者NotNull，该值可以为空</li>
* <li>如果whereType为IN，该值为List类型，如：Arrays.asList("aa", "bb")</li>
* @param placeholder 命名参数
* <li>如果为Null，则使用表的字段名称作为命名参数</li>
* @param whereType WhereType枚举，如like，>=
* @return SqlBuffer
*/
public SqlBuffer add(ConnectType connectType, String columnName, Object columnValue, String placeholder, WhereType whereType){
if(StrUtils.isBlank(columnName)){
return this;
}
this.whereSql.append(SPACE).append(connectType.getValue());
if(this.isNewStart){
this.whereSql.append(SPACE).append(LEFT_BRACKET);
this.isNewStart = false;
}
this.whereSql.append(SPACE).append(columnName);
//组装参数
if(StrUtils.isBlank(placeholder)){//命名参数为空，则使用字段名
placeholder = columnName;
}
if(WhereType.LIKE == whereType){
//LIKE '%' :username '%' ,:username 两边一定要有空格，如果没有空格，是查询不到数据的
this.whereSql.append(SPACE).append(whereType.getValue())
.append(SPACE).append(SINGLE_QUOTE).append(PERCENT_SIGN).append(SINGLE_QUOTE).append(SPACE)
.append(COLON).append(placeholder)
.append(SPACE).append(SINGLE_QUOTE).append(PERCENT_SIGN).append(SINGLE_QUOTE).append(SPACE);
this.getParamsMap().put(placeholder, columnValue);
// 这种方法是在值里放%，如 "%" + username + "%"
/*this.whereSql.append(SPACE).append(whereType.getValue())
.append(SPACE)
.append(COLON).append(placeholder);*/
}else if(WhereType.LIKE_LEFT == whereType){
this.whereSql.append(SPACE).append(whereType.getValue())
.append(SPACE).append(SINGLE_QUOTE).append(PERCENT_SIGN).append(SINGLE_QUOTE).append(SPACE)
.append(COLON).append(placeholder).append(SPACE);
this.getParamsMap().put(placeholder, columnValue);
}else if(WhereType.LIKE_RIGHT == whereType){
this.whereSql.append(SPACE).append(whereType.getValue())
.append(SPACE).append(COLON).append(placeholder)
.append(SPACE).append(SINGLE_QUOTE).append(PERCENT_SIGN).append(SINGLE_QUOTE).append(SPACE);
this.getParamsMap().put(placeholder, columnValue);
}else if(WhereType.NULL == whereType || WhereType.NOT_NULL == whereType){
this.whereSql.append(SPACE).append(whereType.getValue());
// 因为is null或is not null不需要参数值，所以参数Map不增加值
}else if(WhereType.IN == whereType){
// in (:userids)
this.whereSql.append(SPACE).append(whereType.getValue())
.append(SPACE).append(LEFT_BRACKET)
.append(COLON).append(placeholder)
.append(RIGHT_BRACKET);
this.getParamsMap().put(placeholder, columnValue);
}else{
this.whereSql.append(SPACE).append(whereType.getValue())
.append(SPACE).append(COLON).append(placeholder);
this.getParamsMap().put(placeholder, columnValue);
}
return this;
}
/**
* 把之前的查询条件用括号包括
* @return
*/
public SqlBuffer endBefore(){
if(hadWhereSql()){
this.whereSql.insert(0, SPACE + LEFT_BRACKET).append(RIGHT_BRACKET);
}else{
String beginFlag = this.whereSql.substring(0, 4);
if(beginFlag.toUpperCase().indexOf(ConnectType.AND.getValue()) > -1){
this.whereSql.insert(4, SPACE + LEFT_BRACKET).append(RIGHT_BRACKET);
}else{
this.whereSql.insert(3, SPACE + LEFT_BRACKET).append(RIGHT_BRACKET);
}
}
return this;
}
/**
* 为后面的查询条件加上左括号，结束后使用newEnd()加上右括号
* @return
*/
public SqlBuffer newStart(){
this.isNewStart = true;
this.isEndStart = true;
return this;
}
/**
* 为后面的查询条件加上右括号，先使用newStart()
* @return
*/
public SqlBuffer newEnd(){
if(this.isEndStart){
this.whereSql.append(SPACE).append(RIGHT_BRACKET);
}
this.isEndStart = false;
return this;
}
/**
* 排序
* @param columnName 表的列名称
* @param orderByType OrderByType枚举
* @return
*/
public SqlBuffer orderBy(String columnName, OrderByType orderByType){
if(hadOrderBySql()){
this.orderBySql.append(SPACE).append(COMMA).append(columnName).append(SPACE).append(orderByType.getValue());
}else{
this.orderBySql.append(ORDER_BY_SQL).append(SPACE).append(columnName).append(SPACE).append(orderByType.getValue());
this.orderByIndex = 1;
}
return this;
}
/**
* 升序排序
* @param columnName 表的列名称
* @return
*/
public SqlBuffer orderBy(String columnName){
return orderBy(columnName, OrderByType.ASC);
}
/**
* 分页查询条件
* @param offSize
* @param pageSize
* @return
*/
public SqlBuffer limit(int offSize, int pageSize){
if(offSize < 0){
offSize = 0;
}
if(pageSize < 0){
pageSize = 10;
}
this.offSize = offSize;
this.pageSize = pageSize;
if(hadLimitSql()){
this.limitSql.delete(0, limitSql.length())
.append(LIMIT_SQL).append(SPACE)
.append(offSize).append(COMMA).append(pageSize);
}else{
this.limitSql.append(LIMIT_SQL).append(SPACE)
.append(offSize).append(COMMA).append(pageSize);
this.limitIndex = 1;
}
return this;
}
/**
* 分页查询条件
* @param pageSize
* @return
*/
public SqlBuffer limit(int pageSize){
return limit(0, pageSize);
}
/**
* 获取SqlBuffer拼接的sql语句
* @return
*/
public String getSql(){
StringBuffer result = new StringBuffer("");
result.append(baseSql).append(SPACE);
if(!hadWhereSql()){
if(this.whereSql.length() > 0){
result.append(WHERE_INIT + SPACE);
}
}else{
result.append(WHERE_SQL + SPACE);
}
result.append(whereSql).append(SPACE)
.append(orderBySql).append(SPACE)
.append(limitSql).append(SPACE);
return result.toString();
}
/**
* 获取SqlBuffer拼接的countSql语句
* @return
*/
public String getCountSql(){
StringBuffer result = new StringBuffer("");
result.append(baseSql).append(SPACE);
if(!hadWhereSql()){
if(this.whereSql.length() > 0){
result.append(WHERE_INIT + SPACE);
}
}else{
result.append(WHERE_SQL + SPACE);
}
result.append(whereSql).append(SPACE);
return "select count(*) from ("+result.toString()+") _temp_count";
//return result.toString();
}
/**
* 获取SqlBuffer拼接的sql语句
* @return
*/
public String toString(){
return this.getSql();
}
@SuppressWarnings("unused")
private StringBuffer getBaseSql() {
return baseSql;
}
@SuppressWarnings("unused")
private void setBaseSql(StringBuffer baseSql) {
this.baseSql = baseSql;
}
@SuppressWarnings("unused")
private StringBuffer getWhereSql() {
return whereSql;
}
@SuppressWarnings("unused")
private void setWhereSql(StringBuffer whereSql) {
this.whereSql = whereSql;
}
@SuppressWarnings("unused")
private StringBuffer getOrderBySql() {
return orderBySql;
}
@SuppressWarnings("unused")
private void setOrderBySql(StringBuffer orderBySql) {
this.orderBySql = orderBySql;
}
@SuppressWarnings("unused")
private StringBuffer getLimitSql() {
return limitSql;
}
@SuppressWarnings("unused")
private void setLimitSql(StringBuffer limitSql) {
this.limitSql = limitSql;
}
@SuppressWarnings("unused")
private int getWhereIndex() {
return whereIndex;
}
@SuppressWarnings("unused")
private void setWhereIndex(int whereIndex) {
this.whereIndex = whereIndex;
}
public int getOrderByIndex() {
return orderByIndex;
}
public void setOrderByIndex(int orderByIndex) {
this.orderByIndex = orderByIndex;
}
@SuppressWarnings("unused")
private int getLimitIndex() {
return limitIndex;
}
@SuppressWarnings("unused")
private void setLimitIndex(int limitIndex) {
this.limitIndex = limitIndex;
}
public Map<String, Object> getParamsMap() {
return paramsMap;
}
public void setParamsMap(Map<String, Object> paramsMap) {
this.paramsMap = paramsMap;
}
public int getOffSize() {
return offSize;
}
public void setOffSize(int offSize) {
this.offSize = offSize;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
}

二、定义的枚举

1、ConnectType

Java代码

public enum ConnectType {
AND{
@Override
public String getValue() {
return "AND";
}
},
OR{
@Override
public String getValue() {
return "OR";
}
};
public abstract String getValue();
}

2、OrderByType

Java代码

public enum OrderByType {
DESC{
@Override
public String getValue() {
return "DESC";
}
},
ASC{
@Override
public String getValue() {
return "ASC";
}
};
public abstract String getValue();
}

3、WhereType

Java代码

public enum WhereType {
EQUALS{
@Override
public String getValue() {
return "=";
}
},
NOT_EQUALS{
@Override
public String getValue() {
return "!=";
}
},
LIKE{
@Override
public String getValue() {
return "LIKE";
}
},
LIKE_LEFT{
@Override
public String getValue() {
return "LIKE";
}
},
LIKE_RIGHT{
@Override
public String getValue() {
return "LIKE";
}
},
GREATER_THAN{
@Override
public String getValue() {
return ">";
}
},
GREATER_THAN_EQUALS{
@Override
public String getValue() {
return ">=";
}
},
LESS_THAN{
@Override
public String getValue() {
return "<";
}
},
LESS_THAN_EQUALS{
@Override
public String getValue() {
return "<=";
}
},
NULL{
@Override
public String getValue() {
return "is null";
}
},
NOT_NULL{
@Override
public String getValue() {
return "is not null";
}
},
IN{
@Override
public String getValue() {
return "in";
}
};
public abstract String getValue();
}

三、用到的工具类：StrUtils

Java代码

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import java.util.UUID;
//import cn.imovie.common.utils.RegUtils;
public class StrUtils {
/**
* 随机获取数字和大写英文字母组合的字符串
*
* @param size
* 返回的字符串的位数，如果小于1，则默认是6
* @return String
* @since 2015-09-25
*/
public static String getRandomLetterAndDigital(int size) {
String str = "23456789ABCDEFGHJKLMNPQRSTUVWXYZ";// 去掉容易混淆字符：0与O,1与I
StringBuffer sb = new StringBuffer();
if (size < 1) {
size = 6;
}
for (int i = 0; i < size; i++) {
int ran = (int) (Math.random() * str.length());
sb.append(str.charAt(ran));
}
return sb.toString().trim();
}
/**
* 随机获取大/小写英文字母组合的字符串
*
* @param size
* 返回的字符串的位数，如果小于1，则默认是6
* @return String
* @since 2015-09-25
*/
public static String getLetter(int size) {
String str = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
StringBuffer sb = new StringBuffer();
if (size < 1) {
size = 6;
}
for (int i = 0; i < size; i++) {
int ran = (int) (Math.random() * str.length());
sb.append(str.charAt(ran));
}
return sb.toString().trim();
}
/**
* 随机获取大写英文字母组合的字符串
*
* @param size
* 返回的字符串的位数，如果小于1，则默认是6
* @return String
* @since 2015-09-25
*/
public static String getUpperLetter(int size) {
String str = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
StringBuffer sb = new StringBuffer();
if (size < 1) {
size = 6;
}
for (int i = 0; i < size; i++) {
int ran = (int) (Math.random() * str.length());
sb.append(str.charAt(ran));
}
return sb.toString().trim();
}
/**
* 随机获取数字字符串
*
* @param size
* 返回的字符串的位数
* @return String
* @since 2015-09-25
*/
public static String getRandomDigital(int size) {
String str = "1234567890";
StringBuffer sb = new StringBuffer();
if (size < 1) {
return null;
} else {
for (int i = 0; i < size; i++) {
int ran = (int) (Math.random() * 10);
sb.append(str.charAt(ran));
}
return sb.toString().trim();
}
}
/**
* 获取随机数字，同getRandomDigital
*
* @param size
* @return
*/
public static String getNumber(int size) {
return getRandomDigital(size);
}
/**
* 生成年月日时分秒毫秒（20120905050602000）
*
* @return
* @since 2015-09-25
*/
public static String getYYYYMMDDHHmmssmilliSecond() {
StringBuffer str = new StringBuffer("");
String strMonth = "";
String strDate = "";
String strHour = "";
String strMinute = "";
String strSecond = "";
String strMilliSecond = "";
Calendar cal = Calendar.getInstance();
int year = cal.get(Calendar.YEAR);
int month = cal.get(Calendar.MONTH) + 1;
int date = cal.get(Calendar.DATE);
int hour = cal.get(Calendar.HOUR_OF_DAY);
int minute = cal.get(Calendar.MINUTE);
int second = cal.get(Calendar.SECOND);
int milliSecond = cal.get(Calendar.MILLISECOND);
if (month < 10) {
strMonth = "0" + month;
} else {
strMonth = String.valueOf(month);
}
if (date < 10) {
strDate = "0" + date;
} else {
strDate = String.valueOf(date);
}
if (hour < 10) {
strHour = "0" + hour;
} else {
strHour = String.valueOf(hour);
}
if (minute < 10) {
strMinute = "0" + minute;
} else {
strMinute = String.valueOf(minute);
}
if (second < 10) {
strSecond = "0" + second;
} else {
strSecond = String.valueOf(second);
}
if (milliSecond < 10) {
strMilliSecond = "00" + milliSecond;
} else if (milliSecond < 100) {
strMilliSecond = "0" + milliSecond;
} else {
strMilliSecond = String.valueOf(milliSecond);
}
return str.append(String.valueOf(year).toString().trim()).append(strMonth.trim()).append(strDate.trim())
.append(strHour.trim()).append(strMinute.trim()).append(strSecond.trim()).append(strMilliSecond.trim())
.toString();
}
/**
* 生成年月日（20120905050602000）
*
* @return
* @since 2015-09-25
*/
public static String getYYYYMMDD() {
StringBuffer str = new StringBuffer("");
String strMonth = "";
String strDate = "";
Calendar cal = Calendar.getInstance();
int year = cal.get(Calendar.YEAR);
int month = cal.get(Calendar.MONTH) + 1;
int date = cal.get(Calendar.DATE);
if (month < 10) {
strMonth = "0" + month;
} else {
strMonth = String.valueOf(month);
}
if (date < 10) {
strDate = "0" + date;
} else {
strDate = String.valueOf(date);
}
return str.append(String.valueOf(year).toString().trim()).append(strMonth.trim()).append(strDate.trim())
.toString();
}
/**
* 获取uuid，有横杠（36位）
*
* @return
* @since 2015-10-14
*/
public static String getUUID() {
return UUID.randomUUID().toString();
}
/**
* 获取uuid，无横杠(32位)
*
* @return
* @author lqyao
* @since 2015-10-14
*/
public static String getUUIDNumberOnly() {
return UUID.randomUUID().toString().replaceAll("-", "");
}
/**
* 移除字符串最后一个字符
*
* @return
* @since 2015-10-14
*/
public static String removeLastCode(String str) {
if (str == null || str.length() < 1) {
return str;
}
return str.substring(0, str.length() - 1);
}
/**
* 第一个字符变大写
*
* @param str
* @return
*/
public static String firstCodeToUpperCase(String str) {
if (isBlank(str)) {
return str;
}
String strTrim = str.trim();
return String.valueOf(strTrim.charAt(0)).toUpperCase() + strTrim.substring(1, strTrim.length());
}
/**
* 获取字符串最后一个字符
*
* @return
* @since 2016-01-13
*/
public static String getLastCode(String str) {
if (str == null || str.length() < 1) {
return "";
}
return str.substring(str.length() - 1);
}
/**
* 获取第一个id
*
* @param str
* 字符串
* @return id
*/
public static String getFirstId(String str, String spiltCode) {
if (spiltCode == null) {
spiltCode = ",";
}
if (!StrUtils.isEmpty(str)) {
if (str.indexOf(spiltCode) > -1) {
return str.substring(0, str.indexOf(spiltCode)).trim();
}
}
return str;
}
/**
* 去相同部分
*
* @param originalStr
* 原字符串
* @param deleteStr
* 需要去掉的字符串
* @return string
* @author lqy
*/
public static String removeSamePart(String originalStr, String deleteStr) {
if (originalStr != null && deleteStr != null) {
originalStr = originalStr.replaceAll("\\(", "（");
originalStr = originalStr.replaceAll("\\)", "）");
originalStr = originalStr.replaceAll(" |　", "");
deleteStr = deleteStr.replaceAll("\\(", "（");
deleteStr = deleteStr.replaceAll("\\)", "）");
deleteStr = deleteStr.replaceAll(" |　", "");
if (originalStr.indexOf(deleteStr) > -1) {
originalStr = originalStr.replaceAll(deleteStr, "");
}
}
return originalStr;
}
/**
* 拆分字符串获取数组
*
* @param str
* 字符串
* @param spiltCode
* 拆分符号
* @return String[]
*/
public static String[] getArrayAfterSpilt(String str, String spiltCode) {
if (str == null || str.trim().equals("")) {
return null;
} else {
if (spiltCode == null || spiltCode.trim().equals("")) {
spiltCode = ",";
}
return str.split(spiltCode);
}
}
/**
* 拆分字符串获取Ids
*
* @param idsString
* id字符串
* @param spiltCode
* 拆分符号
* @return ids
*/
public static int[] getIdsAfterSpilt(String idsString, String spiltCode) {
List<Integer> idList = new ArrayList<Integer>();
if (idsString == null || idsString.trim().equals("")) {
return null;
} else {
if (spiltCode == null || spiltCode.trim().equals("")) {
spiltCode = ",";
}
String[] idArray = idsString.split(spiltCode);
if (idArray != null && idArray.length > 0) {
for (String string : idArray) {
if (string != null && !string.trim().equals("")) {
idList.add(Integer.parseInt(string.trim()));
}
}
}
}
if (idList != null && idList.size() > 0) {
int[] ids = new int[idList.size()];
for (int j = 0; j < idList.size(); j++) {
ids[j] = idList.get(j);
}
return ids;
}
return null;
}
/**
*
* @param obj
* @return obj == null;
*/
public static boolean isNull(Object obj) {
return obj == null;
}
/**
* 判断list是否为Null
*
* @param list
* @return
*/
public static <T> boolean isNullList(List<T> list) {
return (list == null);
}
/**
* 判断list是否为空
*
* @param list
* @return (list == null) || (list.size() < 1)
*/
public static <T> boolean isEmptyList(List<T> list) {
return (list == null) || (list.size() < 1);
}
/**
* 判断Map是否为Null
*
* @param map
* @return
*/
public static <K, V> boolean isNullMap(Map<K, V> map) {
return (map == null);
}
/**
* 判断Map是否为空
*
* @param map
* @return
*/
public static <K, V> boolean isEmptyMap(Map<K, V> map) {
return (map == null || map.size() < 1);
}
/**
* 判断数组是否为Null
*
* @param obj
* @return
*/
public static boolean isNullArray(Object[] obj) {
return (obj == null);
}
/**
* 判断数组是否为空
*
* @param obj
* @return
*/
public static boolean isEmptyArray(Object[] obj) {
return (obj == null || obj.length < 1);
}
/**
*
* Checks if a String is empty ("") or null.
*
*
* <pre>
* StringUtils.isEmpty(null) = true
* StringUtils.isEmpty("") = true
* StringUtils.isEmpty(" ") = false
* StringUtils.isEmpty("bob") = false
* StringUtils.isEmpty(" bob ") = false
* </pre>
*
*
* NOTE: This method changed in Lang version 2.0. It no longer trims the
* String. That functionality is available in isBlank().
*
*
* @param str
* the String to check, may be null
* @return <code>true</code> if the String is empty or null
*/
public static boolean isEmpty(String str) {
return str == null || str.length() == 0;
}
/**
*
* Checks if a String is whitespace, empty ("") or null.
*
*
* <pre>
* StringUtils.isBlank(null) = true
* StringUtils.isBlank("") = true
* StringUtils.isBlank(" ") = true
* StringUtils.isBlank("bob") = false
* StringUtils.isBlank(" bob ") = false
* </pre>
*
* @param str
* the String to check, may be null
* @return <code>true</code> if the String is null, empty or whitespace
* @since 2.0
*/
public static boolean isBlank(String str) {
int strLen;
if (str == null || (strLen = str.length()) == 0) {
return true;
}
for (int i = 0; i < strLen; i++) {
if ((Character.isWhitespace(str.charAt(i)) == false)) {
return false;
}
}
return true;
}
/**
*
* Checks if the String contains only whitespace.
*
*
*
* <code>null</code> will return <code>false</code>. An empty String ("")
* will return <code>true</code>.
*
*
* <pre>
* StringUtils.isWhitespace(null) = false
* StringUtils.isWhitespace("") = true
* StringUtils.isWhitespace(" ") = true
* StringUtils.isWhitespace("abc") = false
* StringUtils.isWhitespace("ab2c") = false
* StringUtils.isWhitespace("ab-c") = false
* </pre>
*
* @param str
* the String to check, may be null
* @return <code>true</code> if only contains whitespace, and is non-null
* @since 2.0
*/
public static boolean isWhitespace(String str) {
if (str == null) {
return false;
}
int sz = str.length();
for (int i = 0; i < sz; i++) {
if ((Character.isWhitespace(str.charAt(i)) == false)) {
return false;
}
}
return true;
}
/**
* 变成中文括号
*
* @param str
* @return
*/
public static String bracketToChinese(String str) {
if (isBlank(str)) {
return str;
}
String strTrim = str.trim();
strTrim = strTrim.replaceAll("\\(", "（").replaceAll("\\)", "）");
return strTrim;
}
/**
* 变成英文括号
*
* @param str
* @return
*/
public static String bracketToEnglish(String str) {
if (isBlank(str)) {
return str;
}
String strTrim = str.trim();
strTrim = strTrim.replaceAll("（", "(").replaceAll("）", ")");
return strTrim;
}
/**
* 替换字符串
*
* @param str
* @param sourceStr，如果是特殊字符，如英文()、[]等，要使用\\(
* @param targetStr
* @return
*/
public static String replaceStr(String str, String sourceStr, String targetStr) {
if (isBlank(str)) {
return str;
}
String strTrim = str.trim();
strTrim = strTrim.replaceAll(sourceStr, targetStr);
return strTrim;
}
}

四、Dao使用例子：

Java代码

@Override
public <E> List<E> queryForList(String sql, Map<String, Object> paramsMap, Class<E> clazz) {
return namedParameterJdbcTemplate.query(sql,paramsMap,new BeanPropertyRowMapper(clazz));
}
@Override
public List<Map<String, Object>> queryForList(SqlBuffer sqlBuffer) {
return namedParameterJdbcTemplate.queryForList(sqlBuffer.getSql(), sqlBuffer.getParamsMap());
}
@SuppressWarnings("rawtypes")
@Override
public List<?> queryForList(SqlBuffer sqlBuffer, Class<?> clazz) {
return namedParameterJdbcTemplate.query(sqlBuffer.getSql(), sqlBuffer.getParamsMap(),
new BeanPropertyRowMapper(clazz));
}
@Override
public int queryForInt(SqlBuffer sqlBuffer) {
return namedParameterJdbcTemplate.queryForObject(sqlBuffer.getCountSql(), sqlBuffer.getParamsMap(), int.class);
}
@Override
public String queryForString(SqlBuffer sqlBuffer) {
return namedParameterJdbcTemplate.queryForObject(sqlBuffer.getCountSql(), sqlBuffer.getParamsMap(),
String.class);
}
@Override
public int getCount(SqlBuffer sqlBuffer){
return namedParameterJdbcTemplate.queryForObject(sqlBuffer.getCountSql(), sqlBuffer.getParamsMap(), int.class);
}

五、SqlBuffer 使用例子：

Java代码

SqlBuffer sqlBuffer6 = new SqlBuffer("SELECT ja.*,jaa.`attr_name`,jaa.`attr_value` FROM jc_advertising ja LEFT JOIN jc_advertising_attr jaa ON ja.`advertising_id`=jaa.`advertising_id`");
sqlBuffer6.and("jaa.attr_name", Arrays.asList("image_title","image_url"), WhereType.IN);
sqlBuffer6.and("ja.site_id", null, WhereType.NOT_NULL).isNull("ja.ad_code");
sqlBuffer6.and("ja.ad_name", "banner", WhereType.EQUALS);
List<Aaa> list = (List<Aaa>) testSevice.queryForList(sqlBuffer6, Aaa.class);
for (Aaa aaa : list) {
System.out.println("aaa.getAdvertisingId()="+aaa.getAdvertisingId());
System.out.println("aaa.getAdName()="+aaa.getAdName());

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

http://www.cnblogs.com/fanshuyao/

转载于:https://www.cnblogs.com/fanshuyao/p/7234113.html

weixin_33795093

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Spring NamedParameterJdbcTemplate命名参数查询条件封装， NamedParameterJdbcTemplate查询封装...

Spring NamedParameterJdbcTemplate命名参数查询条件封装，NamedParameterJdbcTemplate查询封装>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>&g...
复制链接

扫一扫