项目的BaseService封装了jdbcTemplate

前置知识：
JdbcTemplate 使用总结
JdbcTemplate详解

主角:BaseService类
引入了一些类（下面一一贴出来）

import com.njry.controller.BaseController;
import com.njry.model.User;
import com.njry.utils.common.StringUtils;
import com.njry.utils.db.BatchSql;
import com.njry.utils.db.DBSpringUtils;

package com.njry.service;

import com.njry.controller.BaseController;
import com.njry.model.User;
import com.njry.utils.common.StringUtils;
import com.njry.utils.db.BatchSql;
import com.njry.utils.db.DBSpringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 服务组件基类
 * @author chang
 * @createDate Mar 8, 2013
 * @description
 */
public class BaseService extends BaseController {
@Autowired
public DBSpringUtils db;
@Autowired
public JdbcTemplate jdbcTemplate;

public String getNextSeqValue(String seqName) {
Map<String, Object> map = null;
String sql = "select " + seqName + ".NEXTVAL SEQ from dual";
String nextVal = "";
try {
map = jdbcTemplate.queryForMap(sql);
nextVal = StringUtils.get(map, "SEQ");
} catch (Exception e) {
logger.error(sql, e);
}
return nextVal;
}

public List<Map<String, Object>> getAttachmentList(String linkBusiId){
String sql = "select a.attachment_id file_id," +
" a.file_name file_name," +
" round(a.attachment_size/(1024*1024),2) file_size " +
" from t_attachment a" +
" where link_busi_id=? ";
return db.queryForList(sql, new Object[]{linkBusiId});
}

public void deleteAttachment(String linkBusiId){
String sql = "select a.attachment_id, a.attachment_path, a.file_root" +
" from t_attachment a" +
" where link_busi_id=? ";
List<Map<String, Object>> files = db.queryForList(sql, new Object[]{linkBusiId});
for (Map<String, Object> map: files) {
String attachmentId = StringUtils.get(map, "attachment_id");
String attachmentPath = StringUtils.get(map, "attachment_path");
String fileRoot = StringUtils.get(map, "file_root");
File file = new File(fileRoot + attachmentPath);
if(file.exists()) {
file.delete();
}
sql = "delete from t_attachment where attachment_id=?";
jdbcTemplate.update(sql, new Object[]{attachmentId});
}
}

/**
 * 获取数据字典配置
 * @param group_id 字典组编号
 * @return
 */
public List<Map<String, Object>> getDictItemList(String group_id){
return this.getDictItemList(group_id, "");
}

/**
 * 获取数据字典配置
 * @param group_id 字典组编号
 * @param special 特殊条件 如：and item_id not in(99)
 * @return
 */
public List<Map<String, Object>> getDictItemList(String group_id, String special){
String sql = "select a.item_id,a.item_name,a.item_value,a.item_order,a.group_id," +
" a.item_order,a.remark from t_dictionary_item a " +
" where a.status =1 and upper(a.group_id)=upper(?) ";
if(!"".equals(special)){
sql += special;
}
sql += "order by a.item_order,a.item_id " ;
return db.queryForList(sql, new Object[]{group_id});
}

/**
 * 插入大字段内容
 * @param batchSql
 * @param clob
 * @param instId
 */
public void addClob(BatchSql batchSql, List<String> clob, String instId, String table){
String sql = "";
sql = "delete from t_clob_content a where a.inst_id = ? ";
batchSql.addBatch(sql, new Object[]{instId});
for (int i=0;i<clob.size();i++) {
sql = "insert into t_clob_content(clob_id, clob_content, show_order, inst_id, create_time, remark) " +
  " values(SEQ_COMMON_ID.NEXTVAL, ?, ?, ?, sysdate, ? ) ";
batchSql.addBatch(sql, new Object[]{clob.get(i), i+1, instId, table});
}
db.doInTransaction(batchSql);
}

/**
 * 获取大字段内容
 * @param instId
 * @return
 */
public String getClobContent(String instId){
String sql = "select clob_content from t_clob_content a where inst_id=? order by show_order";
List<Map<String, Object>> clob = db.queryForList(sql, new Object[]{instId});
String content = "";
for(int i=0; i<clob.size(); i++) {
content += clob.get(i).get("CLOB_CONTENT");
}
return content;
}

/**
 * 获取地市信息列表
 * @param request
 * @return
 */
public List<Map<String, Object>> getRegionList(HttpServletRequest request){
List<Map<String, Object>> newRegionList = new ArrayList<Map<String, Object>>();
User user = this.getUser(request);
if (user.getMaxRoleLevel() == 0) {
//全省视野
return getDictItemList("COMMON.REGION_ID");
} else {
for (Map<String, Object> map : getDictItemList("COMMON.REGION_ID")) {
if (user.getRegionId().equals(StringUtils.get(map, "ITEM_ID"))) {
newRegionList.add(map);
}
}
}
return newRegionList;
}

/**
 * 获取角色集合
 * @return
 */
public List<Map<String, Object>> getRoleList(){
String sql = "select a.role_id, a.role_name from t_role a where a.status = 1 order by a.role_level, a.role_order";
return db.queryForList(sql);
}

/**
 * 获取公共权限控制语句
 * @param request
 * @param params sql参数
 * @param joinColumn 拼接字段
 * @return
 */
public String getCommonDataSql(HttpServletRequest request, List<String> params, String joinColumn){
User user = this.getUser(request);
int maxUserRoleLevel = user.getMaxRoleLevel();
String sql = " and exists (select 1 from t_organization m where m.org_id="+joinColumn;
if(maxUserRoleLevel < 1) {//省

} else if (maxUserRoleLevel == 1){//市
sql += " and m.region_id=?";
params.add(user.getRegionId());
} else if (maxUserRoleLevel > 1){//县及以下
sql += " and m.region_id=? and m.yxdy_id=?";
params.add(user.getRegionId());
params.add(user.getYxdyId());
}
sql += ")";
return sql;
}
}

import com.njry.controller.BaseController;

package com.njry.controller;

import com.njry.model.User;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.session.Session;
import org.apache.shiro.subject.Subject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.MessageSource;
import org.springframework.core.env.Environment;

import javax.servlet.http.HttpServletRequest;

/**
 * @desc: 基础类
 * @author: 
 * @date: 
 */
public class BaseController {
public Logger logger = LoggerFactory.getLogger(getClass());
@Autowired
public Environment env;
@Autowired
public MessageSource messageSource;
@Value("${sys.encryptKey}")
public String encryptKey;

public User getUser(HttpServletRequest request){
Subject subject = SecurityUtils.getSubject();
Session session= subject.getSession();
return (User) session.getAttribute("user");
}

public String getUserId(HttpServletRequest request){
return getUser(request).getUserId();
}
}

import com.njry.model.User;
实体类没必要贴

import com.njry.utils.common.StringUtils;

package com.njry.utils.common;

import cn.hutool.core.util.StrUtil;
import cn.hutool.crypto.SecureUtil;

import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.text.DecimalFormat;
import java.util.*;

public class StringUtils {

/**
 * 字符数组转字符串
 * @param strArray
 * @param splitFlag
     * @return
     */
public static String arrayToString(String[] strArray, String splitFlag) {
String tmpString = "";
if (strArray.length == 0) {
tmpString = "";
} else {
for (int i = 0; i < strArray.length; i++) {
tmpString = tmpString + strArray[i];
if (i < strArray.length - 1) {
tmpString = tmpString + splitFlag;
}
}
}
tmpString = tmpString.trim();
return tmpString;
}

/**
 * 字符list转字符
 * @param list
 * @param splitFlag
     * @return
     */
public static String listToString(List<String> list, String splitFlag) {
String tmpString = "";
if (list.size() == 0) {
tmpString = "";
} else {
for (String str : list) {
tmpString = tmpString + splitFlag + str;
}
tmpString = tmpString.substring(1);
}
tmpString = tmpString.trim();
return tmpString;
}

public static String listToString(List<Map<String, Object>> list, String key, String split) {
String str = "";
for (Map<String, Object> map : list) {
str += get(map, key) + split;
}
if (!"".equals(str))
str = str.substring(0, str.lastIndexOf(split));
return str;
}

public static String listToStringWithDistinct(List list, String key, String split) {
String str = "";
List<String> lst = new ArrayList<String>();
for (int i = 0; i < list.size(); i++) {
Map map = (Map) list.get(i);
String value = get(map, key);
if(!lst.contains(value) && !value.equals("")) {
lst.add(value);
str += value + split;
}
}
if (!"".equals(str))
str = str.substring(0, str.lastIndexOf(split));
else
str = "''";
return str;
}

/**
 * 字符串反转
 * @param strReverse
 * @return
     */
public static String reverse(String strReverse) {
if (strReverse == null) {
return strReverse;
} else {
StringBuffer tmpString = new StringBuffer(strReverse);

tmpString = tmpString.reverse();

return tmpString.toString();
}
}

/**
 * 对象转字符
 * @param value
 * @return
     */
public static String notEmpty(Object value) {
if (value == null) {
value = "";
}
return String.valueOf(value);
}

/**
 * 获取map的键值
 * @param map
 * @param keyName
     * @return
     */
public static String get(Map map, String keyName) {
return notEmpty(map.get(keyName));
}

/**
 * 获取map的键int值
 * @param map
 * @param keyName
 * @return
 */
public static int getInt(Map map, String keyName) {
String str = notEmpty(map.get(keyName));
if(StrUtil.isBlank(str)) {
str = "0";
}
return Integer.parseInt(str);
}

/**
 * 替换参数字符串
 * @param sql
 * @param params
 */
public static String getSql(String sql, Object[] params) {
int i = 0;
StringTokenizer st = new StringTokenizer(sql, "?", true);
StringBuffer bf= new StringBuffer("");
while (st.hasMoreTokens()) {
String temp = st.nextToken();
if(temp.equals("?")) {
bf.append("'"+String.valueOf(params[i])+"'");
i++;
} else {
bf.append(temp);
}
}

return bf.toString();
}

/**
 * 获取不定参数的sql语句
 * @param sql
 * @param list
 * @return
 */
public static String getSql(String sql, List<String> list) {
return getSql(sql, list.toArray());
}

/**
 * 字符转整型
 * @param s
 * @return
     */
public static int toInt(String s){
try {
return Integer.parseInt(s);
} catch (Exception e) {
return 0;
}
}

/**
 * 字符转long
 * @param s
 * @return
 */
public static long toLong(String s){
try {
return Long.parseLong(s);
} catch (Exception e) {
return 0;
}
}

/**
 * 字符转浮点
 * @param s
 * @return
 */
public static float toFloat(String s){
try {
return Float.parseFloat(s);
} catch (Exception e) {
return 0;
}
}

/**
 * 字符转double
 * @param s
 * @return
 */
public static double toDouble(String s){
try {
return Double.parseDouble(s);
} catch (Exception e) {
return 0;
}
}

/**
 * 浮点转字符串
 * @param f
 * @param pattern "00.00"  输出"00.12"
 * @return
 */
public static String float2String(float f, String pattern) {
DecimalFormat df = new DecimalFormat(pattern);//输出"00.12"
String s = df.format(f);
return s;
}

public String[] split(String strSplit, String splitFlag) {
if (strSplit == null || splitFlag == null) {
String[] tmpSplit = new String[1];
tmpSplit[0] = strSplit;
return tmpSplit;
}

StringTokenizer st = new StringTokenizer(strSplit,
splitFlag);

int size = st.countTokens();
String[] tmpSplit = new String[size];

for (int i = 0; i < size; i++) {
tmpSplit[i] = st.nextToken();
}
return tmpSplit;
}

/**
 * md5加密
 * @param source
 * @return
 */
public static String md5(String source) {
String result = "";
try {
result = md5(source.getBytes("UTF-8"));
} catch (UnsupportedEncodingException e) {
return "";
}
return result;
}  

/**
 * md5加密
 * @param source
 * @return
 */
public static String md5(byte[] source) {  
StringBuffer sb = new StringBuffer(32);
try {
MessageDigest md = MessageDigest.getInstance("MD5");  
byte[] array = md.digest(source);  
for (int i = 0; i < array.length; i++) {
sb.append(Integer.toHexString((array[i] & 0xFF) | 0x100).substring(1, 3));  
}  
} catch (Exception e) {  
return "";  
}  
return sb.toString();  
}

/**
 * 获取UUID
 * @return
     */
public static String getUUID(){
UUID uuid = UUID.randomUUID();
String str = uuid.toString().replaceAll("-", "");
return str;
}

/**
 * 将类似123,456修改为'123','456'的形式
 * @param str 源字符
 * @param split 分隔符
 * @return
 */
public static String rebuildStr(String str, String split){
String[] array = str.split(split);
String newStrng = "";
for (int i = 0; i < array.length; i++) {
newStrng += ",'"+array[i]+"'";
}
if (!newStrng.equals("")) {
newStrng = newStrng.substring(1);
}
return newStrng;
}

/**
 * 断目标字符串是否包含特定字符串
 * @param container
 * @param regx
 * @return
 */
public static boolean isContains(String container, String regx[]) {
boolean result = false;
for (int i = 0; i < regx.length; i++) {
if (container.indexOf(regx[i]) != -1) {
return true;
}
}
return result;
}

public static String toInSql(String inParams, List<String> paramList, String splitChart){
String[] paramArray = inParams.split(splitChart);
String inSql = "";
for (int i = 0; i < paramArray.length; i++) {
inSql += ",?";
paramList.add(paramArray[i]);
}
if (!inSql.equals("")) {
inSql = inSql.substring(1);
}
return inSql;
}

/**
 * 替换字符串中的特殊字符
 * @param value
 * @return
 */
public static String cleanAllXSS(String value) {
String[] filterChars = {"'", "\"", "%", "(", "<", ">", "/", ")", ";"};
String[] replaceChars = {"‘", "“", "％", "（", "＜", "＞", "／", "）", "；"};

value = value.replaceAll("(?i)<\\s*script.*<\\s*/\\s*script\\s*>", "");
value = value.replaceAll("(?i)onclick", "");
value = value.replaceAll("(?i)ondblclick", "");
value = value.replaceAll("(?i)onerror", "");
value = value.replaceAll("(?i)onblur", "");
value = value.replaceAll("(?i)onfocus", "");
value = value.replaceAll("(?i)onkeydown", "");
value = value.replaceAll("(?i)onkeypress", "");
value = value.replaceAll("(?i)onkeyup", "");
value = value.replaceAll("(?i)onmousedown", "");
value = value.replaceAll("(?i)onmousemove", "");
value = value.replaceAll("(?i)onmouseout", "");
value = value.replaceAll("(?i)onmouseover", "");
value = value.replaceAll("(?i)onmouseup", "");
value = value.replaceAll("(?i)alert", "");
value = value.replaceAll("(?i)prompt", "");
value = value.replaceAll("(?i)confirm", "");
value = value.replaceAll("(?i)select", "");
value = value.replaceAll("(?i)input", "");
value = value.replaceAll("(?i)button", "");
value = value.replaceAll("(?i)textarea", "");
value = value.replaceAll("(?i)iframe", "");
value = value.replaceAll("(?i)marquee", "");
value = value.replaceAll("(?i)eval\\((.*)\\)", "");
value = value.replaceAll("(?i)<\\s*iframe\\s+.*>", "");
value = value.replaceAll("(?i)<\\s*frame\\s+.*>", "");
value = value.replaceAll("(?i)<\\s*input.*>", "");
value = value.replaceAll("(?i)<\\s*select.*<\\s*/\\s*select\\s*>", "");
value = value.replaceAll("(?i)<\\s*img\\s+.*>", "");

for (int i = 0; i < filterChars.length; i++) {
value = value.replace(filterChars[i], replaceChars[i]);
}

return value;
}

public static String htmlEncode(String source) {
if (source == null) {
return "";
}
String html = "";
StringBuffer buffer = new StringBuffer();
for (int i = 0; i < source.length(); i++) {
char c = source.charAt(i);
switch (c) {
case '<':
buffer.append("&lt;");
break;
case '>':
buffer.append("&gt;");
break;
case '&':
buffer.append("&amp;");
break;
case '"':
buffer.append("&quot;");
break;
case '\'':
buffer.append("&#x27;");
break;
case 10:
case 13:
break;
default:
buffer.append(c);
}
}
html = buffer.toString();
return html;
}

public static String getRandomStr(int length){
String base = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ123456789";
Random random = new Random();
StringBuffer sb = new StringBuffer();
for (int i = 0; i < length; i++) {
int number = random.nextInt(base.length());
sb.append(base.charAt(number));
}
return sb.toString();
}

/**
 * 分割大字符串
 * @param str
 * @return
 */
public static List<String> splitLongStr(String str) {
List<String> list = new ArrayList<String>();
char[] ch = str.toCharArray();
int l = 0;
int start = 0;
for (int i = 0; i < ch.length; i++) {
char c = ch[i];
if (isChinese(c)) {
l = l + 2;
} else {
l = l +1;
}
if (l >= 3996) {
String tempStr = str.substring(start, i);
list.add(tempStr);
start = i;
l = 0;
} else if (i == ch.length - 1) {
String tempStr = str.substring(start);
list.add(tempStr);
}
}
if (list.size()<=0) {
list.add(str);
}
return list;
}

/**
 * 根据Unicode编码完美的判断中文汉字和符号
 * @param c
 * @return
 */
public static boolean isChinese(char c) {
Character.UnicodeBlock ub = Character.UnicodeBlock.of(c);
if (ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS
|| ub == Character.UnicodeBlock.CJK_COMPATIBILITY_IDEOGRAPHS
|| ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_A
|| ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_B
|| ub == Character.UnicodeBlock.CJK_SYMBOLS_AND_PUNCTUATION
|| ub == Character.UnicodeBlock.HALFWIDTH_AND_FULLWIDTH_FORMS
|| ub == Character.UnicodeBlock.GENERAL_PUNCTUATION) {
return true;
}
return false;
}

/**
 * 查找指定字符串是否包含指定字符串列表中的任意一个字符串
 * @param strs    指定字符串（包含分隔符的字符串）
 * @param delims  strs中的分隔符
 * @param testStr 待检查的字符串
 * @return true：是  false：否
 */
public static boolean containsAny(String strs, String delims, String testStr) {
if (strs == null || testStr == null) {
return false;
}

if(StrUtil.isBlank(delims)) {
return strs.equals(testStr);
}

for (String tmp : strs.split(delims)) {
if (StrUtil.equals(tmp, testStr)) {
return true;
}
}
return false;
}

/**
 * 获取uuid
 * @param needLine 格式：true 带横杠  false 不带横杠, 默认 不带
 * @param upperCase 大小写：true 大写  false 小写, 默认 小写
 * @return
 */
public static String getUUId(boolean needLine, boolean upperCase){
String uuid = UUID.randomUUID().toString();
if(!needLine){
uuid = uuid.replace("-", "");
}
if(upperCase){
uuid = uuid.toUpperCase();
}
return uuid;
}

public static void main(String[] args) {
String s = StringUtils.reverse("hello world");
System.out.println("s:"+s);
byte[] key = SecureUtil.generateKey("DES", StrUtil.bytes("njry$2IqD8", "UTF-8")).getEncoded();
String passwd = SecureUtil.des(key).encryptBase64("njjj#2021");
System.out.println("passwd:"+passwd);
passwd = SecureUtil.des(key).decryptStr(passwd);
System.out.println("passwd:"+passwd);
}

}

import com.njry.utils.db.BatchSql;

package com.njry.utils.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BatchSql {
private List<Map<String, Object>> sqlList = new ArrayList<Map<String, Object>>();

public void addBatch(String sql, Object[] objects) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("sql", sql);
map.put("objects", objects);
sqlList.add(map);
}

public void addBatch(String sql) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("sql", sql);
map.put("objects", new Object[] {});
sqlList.add(map);
}

public List getSqlList() {
return sqlList;
}

public void clearBatch() {
    this.sqlList.clear();
}
}

这个工具类涉及到一个事务：前置知识看链接
Spring Boot 中的 TransactionTemplate 是什么，如何使用
import com.njry.utils.db.DBSpringUtils;

package com.njry.utils.db;

import com.github.pagehelper.PageInfo;
import com.njry.utils.common.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

import java.util.*;

/**
 * 数据库操作类
 * @author chang
 * @createDate Aug 5, 2013
 * @description
 */
public class DBSpringUtils {
private static Logger logger = LoggerFactory.getLogger(DBSpringUtils.class);
public final static int DEFAULT_FETCHSIZE = 32;//默认的fetchsize
public JdbcTemplate jdbcTemplate;
public TransactionTemplate transactionTemplate;

public DBSpringUtils() {

}

public DBSpringUtils(JdbcTemplate jdbcTemplate, TransactionTemplate transactionTemplate) {
this.jdbcTemplate = jdbcTemplate;
this.transactionTemplate = transactionTemplate;
}

/**
 * 返回一条记录
 * @param sql 传入的sql语句: select *from table where user_id=?
 * @param objects
 * @return
 */
public Map<String, Object> queryForMap(String sql, Object[] objects) {
Map<String, Object> map = null;
try {
map = this.jdbcTemplate.queryForMap(sql, objects);
} catch (EmptyResultDataAccessException e) {

} catch (Exception e) {
logger.error(StringUtils.getSql(sql, objects), e);
}
if (map == null) {
map = new HashMap<String, Object>();
}
return map;
}

/**
     * 返回一条记录 支持占位符 
     * @param sql 传入的sql语句: select *from table where user_id=?
     * @param list 查询时条件不确定，将条件放入一个List<String>中
     * @return
     */
    public Map<String, Object> queryForMap(String sql, List<String> list) {
        return this.queryForMap(sql, list.toArray());
    }
    

public Map<String, Object> queryForMap(String sql) {
Map<String, Object> map = null;
try {
map = this.jdbcTemplate.queryForMap(sql);
} catch (EmptyResultDataAccessException e) {

} catch (Exception e) {
logger.error(sql, e);
}
if (map == null) {
map = new HashMap<String, Object>();
}
return map;
}

/**
 * 获取某个字段的值
 * @param sql
 * @param args
 * @return
 */
public String queryForString(String sql, Object[] args) {
try {
return StringUtils.notEmpty(this.jdbcTemplate.queryForObject(sql, args, String.class));
} catch (EmptyResultDataAccessException e) {
return "";
} catch (Exception e) {
logger.error(StringUtils.getSql(sql, args), e);
return "";
}
}

public String queryForString(String sql) {
try {
return StringUtils.notEmpty(this.jdbcTemplate.queryForObject(sql, null, String.class));
} catch (EmptyResultDataAccessException e) {
return "";
} catch (Exception e) {
logger.error(sql, e);
return "";
}
}

public String queryForString(String sql, List<String> list) {
try {
return StringUtils.notEmpty(this.jdbcTemplate.queryForObject(sql, list.toArray(), String.class));
} catch (EmptyResultDataAccessException e) {
return "";
} catch (Exception e) {
logger.error(StringUtils.getSql(sql, list.toArray()), e);
return "";
}
}

/**
 * 返回数据集
 * @param sql 传入的sql语句: select *from table where user_id=?
 * @param objects
 * @return
 */
public List<Map<String, Object>> queryForList(String sql, Object[] objects) {
return this.queryForList(sql, objects, DEFAULT_FETCHSIZE);
}

/**
 * 返回数据集
 * 查询时条件不确定，将条件放入一个List<String>中
 * @param sql
 * @param list
 * @return
 */
public List<Map<String, Object>> queryForList(String sql, List<String> list) {
return this.queryForList(sql, list.toArray());
}

/**
 * 查询条件不确定时返回数据集
 * @param sql sql_where拼接 sql="select * from table where name='"+v_name+"'";
 * @return
 */
public List<Map<String, Object>> queryForList(String sql) {
return this.queryForList(sql, DEFAULT_FETCHSIZE);
}

/**
 * 查询条件不确定时返回数据集
 * @param sql sql_where拼接 sql="select * from table where name='"+v_name+"'";
 * @param fetchSize 一次获取的数据条数
 * @return
 */
public List<Map<String, Object>> queryForList(String sql, int fetchSize) {
JdbcTemplate jdbc = this.jdbcTemplate;
jdbc.setFetchSize(fetchSize);
List<Map<String, Object>> list = null;
try {
list = jdbc.queryForList(sql);
} catch (Exception e) {
logger.error(sql, e);
}
if (list == null) {
list = new ArrayList<Map<String, Object>>();
}
return list;
}

/**
 * 返回数据集
 * @param sql 传入的sql语句: select *from table where user_id=?
 * @param objects
 * @param fetchSize
 * @return
 */
public List<Map<String, Object>> queryForList(String sql, Object[] objects, int fetchSize) {
JdbcTemplate jdbc = this.jdbcTemplate;
jdbc.setFetchSize(fetchSize);
List<Map<String, Object>> list = null;
try {
list = jdbc.queryForList(sql, objects);
} catch (Exception e) {
logger.error(StringUtils.getSql(sql, objects), e);
}
if (list == null) {
list = new ArrayList<Map<String, Object>>();
}
return list;
}

/**
 * 返回数据集
 * 查询时条件不确定，将条件放入一个List<String>中
 * @param sql
 * @param list
 * @param fetchSize
 * @return
 */
public List<Map<String, Object>> queryForList(String sql, List<String> list, int fetchSize) {
return this.queryForList(sql, list.toArray(), fetchSize);
}

/**
 * 返还记录数
 * @param sql 传入的sql语句 select count(*) from table where name=?
 * @param objects 参数值
 * @return -1:数据库异常
 */
public int queryForInt(String sql, Object[] objects) {
int exc = -1;
try {
exc = this.jdbcTemplate.queryForObject(sql, objects, Integer.class);
} catch (Exception e) {
exc = -1;
logger.error(StringUtils.getSql(sql, objects), e);
}
return exc;
}

/**
 * 返还记录数
 * @param sql 传入的sql语句 select count(*) from table where name=?
 * @param list 参数值
 * @return -1:数据库异常
 */
public int queryForInt(String sql, List<String> list) {
return this.queryForInt(sql, list.toArray());
}

/**
 * 返还记录数
 * @param sql 传入的sql语句直接拼接好
 * @return
 */
public int queryForInt(String sql) {
return this.jdbcTemplate.queryForObject(sql, Integer.class);
}

/**
 * 数据库分页
 * @param sql
 * @param params
 * @param orderSql
 * @param pageSize
 * @param pageNo
 * @return
 */
public PageInfo<Map<String, Object>> getForList(String sql, Object[] params, String orderSql,
String pageSize, String pageNo) {
pageNo = pageNo.equals("") ? "1" : pageNo;
pageSize = pageSize.equals("") ? "20" : pageSize;
int iPageSize = StringUtils.toInt(pageSize);
int iPageNo = StringUtils.toInt(pageNo);
int size = this.queryForInt("select count(1) from ("+sql+")", params);
int first = (iPageNo-1) * iPageSize + 1;
int last = iPageNo * iPageSize;
sql = "SELECT * FROM (SELECT AA.*, rownum rr  FROM ("
+ sql + " " + orderSql
+ " )AA  where rownum <= " +last+ " )BB where rr<=" + last + " and rr>=" + first;
List<Map<String, Object>> list = this.queryForList(sql, params);
PageInfo<Map<String, Object>> page = new PageInfo<Map<String, Object>>(list);
page.setPageNum(iPageNo);
page.setPageSize(iPageSize);
page.setPages(size);
page.setTotal(size);
return page;
}

public PageInfo<Map<String, Object>> getForList(String sql, String orderSql,
String pageSize, String pageNo) {
return getForList(sql, new Object[] {}, orderSql, pageSize, pageNo);
}

public PageInfo<Map<String, Object>> getForList(String sql, List<String> params, String orderSql,
String pageSize, String pageNo) {
return getForList(sql, params.toArray(), orderSql, pageSize, pageNo);
}

/**
 * 过程调用
 * @param name
 * @return
 */
public ProcUtils getProcUtils(String name) {
ProcUtils proc = null;
try {
proc = new ProcUtils(this.jdbcTemplate.getDataSource(), name);
} catch (Exception e) {
logger.error(name, e);
}
return proc;
}

/**
 * 编程式事务处理
 * @param batchSql
 * @return
 */
public int doInTransaction(final BatchSql batchSql) {
int exc = 1;
if (null == batchSql) {
exc = 0;
}
exc = transactionTemplate.execute(new TransactionCallback<Integer>() {
@Override
public Integer doInTransaction(TransactionStatus transactionStatus) {

try {
List<Map<String, Object>> sqlList = batchSql.getSqlList();
for (int i = 0; i < sqlList.size(); i++) {
Map<String, Object> sqlMap = sqlList.get(i);
String sql = (String) sqlMap.get("sql");
Object[] objects = (Object[]) sqlMap.get("objects");
jdbcTemplate.update(sql, objects);
}
return 1;
} catch (Exception e) {
e.printStackTrace();
transactionStatus.setRollbackOnly();
return 0;
}
}
});
return exc;
}

/**
 * 处理sql语句中in的占位符
 * @param collection 参数集合
 * @return
 */
public String rebuildInSql(Collection<String> collection, List<String> paramList){
String inSql = "";
for (int i = 0; i < collection.size(); i++) {
inSql += ",?";
}
if (!inSql.equals("")) {
inSql = inSql.substring(1);
}
paramList.addAll(collection);
return inSql;
}

/**
 * 处理sql语句中in的占位符
 * @param inParams in的参数
 * @param paramList 参数list
 * @return
 */
public String rebuildInSql(String inParams, List<String> paramList){
return this.rebuildInSql(inParams, paramList, ",");
}

/**
 * 处理sql语句中in的占位符
 * @param inParams in的参数
 * @param paramList 参数list
 * @param splitChart inParams的分隔符
 * @return
 */
public String rebuildInSql(String inParams, List<String> paramList, String splitChart){
String[] paramArray = inParams.split(splitChart);
String inSql = "";
for (int i = 0; i < paramArray.length; i++) {
inSql += ",?";
paramList.add(paramArray[i]);
}
if (!inSql.equals("")) {
inSql = inSql.substring(1);
}
return inSql;
}
/**
 * 返回相应sequence的下一个值
 * @param sequenceName sequence名称
 * @return 下个序列值
 */
public String getNextSequenceValue(String sequenceName) {
return this.queryForString("select " + sequenceName + ".NEXTVAL SEQ from dual");
}
/**
 * insert,update,delete 操作
 * @param sql 传入的语句 sql="insert into tables values(?,?)";
 * @param objects
 * @return 0:失败 1:成功
 */
public int update(String sql, Object[] objects) {
int exc = 1;
try {
jdbcTemplate.update(sql, objects);
} catch (Exception e) {
e.printStackTrace();
logger.error(StringUtils.getSql(sql, objects));
exc = 0;
}
return exc;
}

public int update(String sql) {
int exc = 1;
try {
jdbcTemplate.update(sql);
} catch (Exception e) {
e.printStackTrace();
logger.error(sql);
exc = 0;
}
return exc;
}
}

原文地址：https://blog.csdn.net/tellmewhoisi/article/details/140383576

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！