JdbcTemplateUtil -JdbcTemplateUtil工具类
/*
* Created on Apr 23, 2010
* JdbcTemplateUtil.java
*
* Copyright 2004 Hintsoft, LTD. All rights reserved.
* HINTSOFT PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
*
*
* $Id: JdbcTemplateUtil.java,v 1.8 2011/12/16 07:46:27 hcc Exp $
* $Author: hcc $ ( mailto:huangcc@hintsoft.com.cn )
* $Revision: 1.8 $
*
*/
package base.util;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.persistence.Column;
import javax.persistence.Id;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.LinkedCaseInsensitiveMap;
// import auth.po.User;
/**
* @author huangcc
*
*/
public class JdbcTemplateUtil<T> {
private Logger logger = LoggerFactory.getLogger(this.getClass());
public String getObjString(Object obj) {
T t = (T) obj;
return t.toString();
}
public void excuteInsertByList(JdbcTemplate jt, String tableName, final boolean seqId, final List list,
final Class cla) throws Exception {
Object obj = null;
if (list != null && list.size() > 0) {
obj = list.get(0);
} else {
return;
}
T t = (T) obj;
Field[] fieldArr = cla.getDeclaredFields();
String colum = "";
String columVal = "";
List<String> clumList = new ArrayList<String>();
for (Field field : fieldArr) {
String fieldName = field.getName();
if (field.getAnnotation(Column.class) != null) {
String beanid = field.getAnnotation(Column.class).name();
if (!StringUtils.isEmpty(beanid)) {
String meName = "get" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1, fieldName.length());
Method m = cla.getDeclaredMethod(meName);
Object o = m.invoke(t);
if (seqId) {
if (fieldName.equalsIgnoreCase("ID")) {
continue;
}
}
clumList.add(beanid);
}
}
}
if (clumList.size() == 0) {
return;
}
for (String key : clumList) {
colum += key + ",";
columVal += "?,";
}
if (colum.endsWith(",")) {
colum = colum.substring(0, colum.length() - 1);
}
if (columVal.endsWith(",")) {
columVal = columVal.substring(0, columVal.length() - 1);
}
String sql = "insert into " + tableName + "(" + colum.toString() + ")values(" + columVal + ")";
// logger.info(sql);
BatchPreparedStatementSetter batchPreparedStatementSetter = null;
batchPreparedStatementSetter = new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int index) throws SQLException {
T t = (T) list.get(index);
Field[] fieldArr = cla.getDeclaredFields();
int i = 1;
List param = new ArrayList();
for (Field field : fieldArr) {
String fieldName = field.getName();
if (field.getAnnotation(Column.class) != null) {
String beanid = field.getAnnotation(Column.class).name();
if (!StringUtils.isEmpty(beanid)) {
String meName = "get" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1, fieldName.length());
if (seqId) {
if (fieldName.equalsIgnoreCase("ID")) {
continue;
}
}
Method m;
try {
m = cla.getDeclaredMethod(meName);
Object o = m.invoke(t);
param.add(o);
if (o != null) {
ps.setObject(i++, o);
} else {
ps.setObject(i++, null, java.sql.Types.NULL);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
// ps.setInt(i++, xsDailyStat.getLastCountSum());
// ps.setInt(i++, xsDailyStat.getAgentId());
// ps.setString(i++, xsDailyStat.getAreaCode());
// ps.setInt(i++, xsDailyStat.getTypeId());
// ps.setInt(i++, dat);
}
public int getBatchSize() {
return list.size();
}
};
try {
jt.batchUpdate(sql, batchPreparedStatementSetter);
} catch (Exception e) {
e.printStackTrace();
}
}
public int excuteInsert(JdbcTemplate jt, String tableName, boolean seqId, Object obj, Class cla) throws Exception {
T t = (T) obj;
Field[] fieldArr = cla.getDeclaredFields();
String colum = "";
String columVal = "";
Map<String, Object> map = new HashMap<String, Object>();
for (Field field : fieldArr) {
String fieldName = field.getName();
if (field.getAnnotation(Column.class) != null) {
String beanid = field.getAnnotation(Column.class).name();
if (!StringUtils.isEmpty(beanid)) {
String meName = "get" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1, fieldName.length());
Method m = cla.getDeclaredMethod(meName);
Object o = m.invoke(t);
if (seqId) {
if (fieldName.equalsIgnoreCase("ID")) {
continue;
}
}
map.put(beanid, o);
}
}
}
List param = new ArrayList();
if (map.size() == 0) {
return 0;
}
for (String key : map.keySet()) {
colum += key + ",";
columVal += "?,";
param.add(map.get(key));
}
if (colum.endsWith(",")) {
colum = colum.substring(0, colum.length() - 1);
}
if (columVal.endsWith(",")) {
columVal = columVal.substring(0, columVal.length() - 1);
}
String sql = "insert into " + tableName + "(" + colum.toString() + ")values(" + columVal + ")";
// logger.info(sql);
return jt.update(sql, param.toArray());
}
public static void main(String[] args) throws Exception {
Date d = DateUtil.parseDateString("2011-11-10", "yyyy-MM-dd");
Date now = DateUtil.getYesterdayEnd(new Date());
long l = DateUtil.getBetweenDiffDay(d, now, true);
for (int i = 0; i < l; i++) {
String ddd = DateUtil.formatDate(DateUtil.getAppointDateByOffset(d, 0, (i + 1), 2), "yyyy-MM-dd");
System.out.println(ddd);
}
}
public List<T> queryForList(JdbcTemplate jt, String sql, Class cla) {
// logger.info(sql);
List list = jt.queryForList(sql);
JdbcTemplateTool jdbcTemplateTool = new JdbcTemplateTool<T>();
return jdbcTemplateTool.getObjectList(list, cla);
}
public List<T> queryForList(JdbcTemplate jt, Object[] params, String sql, Class cla) {
// logger.info(sql);
List list = jt.queryForList(sql, params);
JdbcTemplateTool jdbcTemplateTool = new JdbcTemplateTool<T>();
return jdbcTemplateTool.getObjectList(list, cla);
}
public String getObjSqlWhere(List paraList, Object obj, Class cla, String prefix) {
JdbcTemplateTool jdbcTemplateTool = new JdbcTemplateTool<T>();
return jdbcTemplateTool.getObjSqlWhere(paraList, obj, cla, prefix);
}
// public static void main(String[] args){
// Menu menu=new Menu();
// menu.setId(1);
// menu.setName("menu");
// JdbcTemplateUtil<Menu> jdbcTemplateTool=new JdbcTemplateUtil<Menu>();
// System.out.println(jdbcTemplateTool.getObjString(menu));
//
// }
}
class JdbcTemplateTool<T> {
private Logger logger = LoggerFactory.getLogger(this.getClass());
public String getObjSqlWhere(List paraList, Object obj, Class cla, String prefix) {
StringBuffer where = new StringBuffer();
if (paraList == null) {
paraList = new ArrayList();
}
if (obj == null) {
return "";
}
// logger.info(obj.toString());
try {
T t = (T) obj;
Field[] field = cla.getDeclaredFields();
for (Field field2 : field) {
String fieldName = field2.getName();
if (field2.getAnnotation(Column.class) != null) {
String beanid = field2.getAnnotation(Column.class).name();
if (!StringUtils.isEmpty(beanid)) {
String meName = "get" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1, fieldName.length());
Method m = cla.getDeclaredMethod(meName);
Object o = m.invoke(t);
if (o != null) {
// System.out.println(o.getClass().getSimpleName());
boolean flag = false;
if (o.getClass().getSimpleName().equals("Integer")) {
if (field2.getAnnotation(Id.class) != null) {
if (((Integer) o).intValue() != 0) {
flag = true;
}
} else {
flag = true;
}
} else if (o.getClass().getSimpleName().equals("String")) {
if (!StringUtils.isEmpty((String) o)) {
flag = true;
}
} else if (o.getClass().getSimpleName().equals("Long")) {
if (field2.getAnnotation(Id.class) != null) {
if (((Long) o).longValue() != 0) {
flag = true;
}
} else {
flag = true;
}
}
if (flag) {
where.append(" AND " + prefix + "." + beanid + "=? ");
paraList.add(o);
}
}
}
}
}
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return where.toString();
}
public List getObjectList(List<LinkedCaseInsensitiveMap> l, Class cla) {
List<T> resList = new ArrayList<T>();
try {
for (LinkedCaseInsensitiveMap map : l) {
T tt = (T) cla.newInstance();
Field[] field = cla.getDeclaredFields();
for (Field field2 : field) {
String fieldName = field2.getName();
if (field2.getAnnotation(Column.class) != null) {
String beanid = field2.getAnnotation(Column.class).name();
if (map.containsKey(beanid)) {
String meName = "set" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1, fieldName.length());
Method m = cla.getDeclaredMethod(meName, field2.getType());
// System.out.println(beanid);
m.invoke(tt, map.get(beanid));
// System.out.println(u.getId()+"");
// return;
}
}
}
resList.add(tt);
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return resList;
}
}
---excuteInsertByList (JdbcTemplate jt, String tableName, final boolean seqId, final List list, final Class cla)使用说明：
public void run() {
logger.info("消息中心,当日报警统计开始。");
long l1 = System.currentTimeMillis();
try {
String sql = "select * from bmc.ALARM_RECORD where ALARM_AT >='"
+ DateUtil.formatDate(DateUtil.getToday(date, 1), "yyyy-MM-dd HH:mm:ss") + "' and ALARM_AT<='"
+ DateUtil.formatDate(DateUtil.getToday(date, 2), "yyyy-MM-dd HH:mm:ss") + "'";
JdbcTemplateUtil<AlarmRecord> jtu = new JdbcTemplateUtil<AlarmRecord>();
List<AlarmRecord> alarmList = jtu.queryForList(this.jt_bmc, sql, AlarmRecord.class);
// 得到当日报警记录
Map<String, MsgCenterReport> msgMap = new HashMap<String, MsgCenterReport>();
for (AlarmRecord alarm : alarmList) {
if (StringUtils.isNotEmpty(alarm.getMonitor().getPoliceCode())) {// 该场所所属派出所
if (msgMap.containsKey(alarm.getMonitor().getPoliceCode())) {
MsgCenterReport centerReport = msgMap.get(alarm.getMonitor().getPoliceCode());
String statVal = centerReport.getStatValue();
centerReport.setStatValue((new Integer(statVal) + 1) + "");
} else {
MsgCenterReport centerReport = new MsgCenterReport();
centerReport.setAreaCode(alarm.getAreaCode());
centerReport.setPoliceCode(alarm.getMonitor().getPoliceCode());
centerReport.setReportDate(DateUtil.formatDate(date, "yyyy-MM-dd"));
centerReport.setServiceType(1);
centerReport.setType(MsgFinalParamUtil.getMsgType(MsgFinalParamUtil.msg_type_cur_alarm));
centerReport.setStatValue("1");
msgMap.put(alarm.getMonitor().getPoliceCode(), centerReport);
}
}
if (msgMap.containsKey(alarm.getAreaCode())) {
MsgCenterReport centerReport = msgMap.get(alarm.getAreaCode());
String statVal = centerReport.getStatValue();
centerReport.setStatValue((new Integer(statVal) + 1) + "");
} else {
MsgCenterReport centerReport = new MsgCenterReport();
centerReport.setAreaCode(alarm.getAreaCode());
centerReport.setReportDate(DateUtil.formatDate(date, "yyyy-MM-dd"));
centerReport.setServiceType(1);
centerReport.setType(MsgFinalParamUtil.getMsgType(MsgFinalParamUtil.msg_type_cur_alarm));
centerReport.setStatValue("1");
msgMap.put(alarm.getAreaCode(), centerReport);
}
}
logger.info("消息中心,删除当日的报警记录总表数据.");
String deleteMsgCenterSql = "delete from STAT.MSG_CENTER_REPORT where type="
+ MsgFinalParamUtil.getMsgType(MsgFinalParamUtil.msg_type_cur_alarm) + " and REPORT_DATE='"
+ DateUtil.formatDate(date, "yyyy-MM-dd") + "'";
this.jt_stat.execute(deleteMsgCenterSql);
List<MsgCenterReport> msgCenterReportList = new ArrayList<MsgCenterReport>();
msgCenterReportList.addAll(msgMap.values());
logger.info("消息中心，报警记录统计总表完成！共" + msgCenterReportList.size() + "条记录!");
for (MsgCenterReport msg : msgCenterReportList) {
logger.info("消息中心，区域编号：" + msg.getAreaCode() + ".派出所编号：" + msg.getPoliceCode() + ".有"
+ msg.getStatValue() + "条报警记录信息.");
}
try {
JdbcTemplateUtil<MsgCenterReport> jtuStat = new JdbcTemplateUtil<MsgCenterReport>();
jtuStat.excuteInsertByList(this.jt_stat, "STAT.MSG_CENTER_REPORT", true, msgCenterReportList,
MsgCenterReport.class);
logger.info("消息中心，总表报警记录入库完成！");
} catch (Exception e) {
logger.error("消息中心，总表报警记录入库失败！", e);
}
} catch (Exception e) {
logger.error("消息中心,报警记录统计失败！", e);
}
long l2 = System.currentTimeMillis();
logger.info("消息中心,报警记录统计完成！用时：" + (l2 - l1) + "毫秒.");
}
------excuteInsert (JdbcTemplate jt, String tableName, boolean seqId, Object obj, Class cla)使用说明：
public void saveCase(Case cases) throws Exception {
try {
JdbcTemplateUtil<Case> jt = new JdbcTemplateUtil<Case>();
jt.excuteInsert(baseService.getJt(DaoUtil.dao_bmc_f), "BMC.CASE", true, cases, Case.class);
} catch (Exception e) {
logger.error("保存审计失败！", e);
}
}
// 保存审计
@RemoteMethod
public int saveCase(String caseCode, String caseName, String areaCode, String level, String description,
String openFlag, String customCount, String customTime) {
try {
if (StringUtils.isNotEmpty(caseName)) {
List<Case> caseList = this.caseService.getByCaseName(caseName);
if (caseList != null && caseList.size() > 0) {
return 3;
}
}
User user = (User) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
Case cases = new Case();
cases.setCode(caseCode);
cases.setName(caseName);
cases.setStatus(new Integer(0));
cases.setLevel(StringUtils.stringToInt(level));
cases.setDescription(description);
cases.setOpenFlag(StringUtils.stringToInt(openFlag));
cases.setCreator(user.getUserName());
cases.setAreaCode(areaCode);
if(user.getLoginType().equals("2")){
cases.setPoliceCode(user.getPoliceCode());
}
cases.setCreateAt(new Date());
if (StringUtils.isNumber(customCount)) {
cases.setCustomCount(new Integer(customCount));
}
if (StringUtils.isNumber(customTime)) {
cases.setCustomTime(new Integer(customTime));
}
this.caseService.saveCase(cases);
} catch (Exception e) {
logger.error(e.getMessage(), e);
return 1;
}
return 0;
}
------queryForList(JdbcTemplate jt, String sql, Class cla) 使用说明：
public List<AttendanceUserConfig> attTimesGonfigList(String inId) {
if (StringUtils.isEmpty(inId)) {
inId = "-1";
}
String sql = "SELECT * FROM FMC.ATTENDANCE_USER_CONFIG WHERE id not in(" + inId + ")";
JdbcTemplateUtil<AttendanceUserConfig> aucJtu = new JdbcTemplateUtil<AttendanceUserConfig>();
List<AttendanceUserConfig> attTimesConfigList = aucJtu.queryForList(this.getJt(DaoUtil.dao_fmc_f), sql,
AttendanceUserConfig.class);
return attTimesConfigList;
}
------queryForList(JdbcTemplate jt, Object[] params, String sql, Class cla) 使用说明：
//通过ID 得到一条数据
public FjInvalidTm getFjInvalidTmById(long id){
List list=new ArrayList();
list.add(id);
String sql="select * from MC.FJ_INVALID_TM where ID =?";
JdbcTemplateUtil<FjInvalidTm> jdbcTemplateUtil = new JdbcTemplateUtil<FjInvalidTm>();
List<FjInvalidTm> list2=jdbcTemplateUtil.queryForList(baseService.getJt(DaoUtil.dao_mc), list.toArray(), sql, FjInvalidTm.class);
if(list2!=null && list2.size()>0){
return list2.get(0);
}else {
return null;
}
}
public SafeInfo isExistNum(String addCertifiCateNum) throws Exception {
List addParam = new ArrayList();
addParam.add(addCertifiCateNum);
SafeInfo safeInfo = null;
String sql = "SELECT * FROM MC.SAFE_INFO WHERE CERTIFICATE_NUM =?";
JdbcTemplateUtil<SafeInfo> jdbcTemplateUtil = new JdbcTemplateUtil<SafeInfo>();
List<SafeInfo> list = jdbcTemplateUtil.queryForList(baseService.getJt(DaoUtil.dao_mc), addParam.toArray(), sql,
SafeInfo.class);
if (list != null || list.size() > 0) {
for (int i = 0; i < list.size(); i++) {
safeInfo = list.get(0);
}
}
return safeInfo;
}