JdbcTemplateUtil -JdbcTemplateUtil工具类

于 2013-09-24 13:30:45 发布
阅读量936 收藏
点赞数
分类专栏： java_framework
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/p0p0p0p01/article/details/11972025
版权

/*
 * 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;

 }

 

 

 

21世纪大懒虫
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值