ProcedureSqlServer
来源：互联网 发布：网络飞镖机 编辑：程序博客网 时间：2024/05/16 19:35
package com.zxwl.base.dao;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.Date;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Set;import javax.sql.DataSource;import org.hibernate.HibernateException;import org.hibernate.Session;import org.springframework.orm.hibernate3.HibernateCallback;import org.springframework.orm.hibernate3.HibernateTemplate;import org.springframework.orm.hibernate3.SessionFactoryUtils;public class ProcedureSqlServer<T> implements IProcedure<T>{ protected EntityHelper<T> entityHelper; private HibernateTemplate hibernateTemplate; public ProcedureSqlServer(EntityHelper<T> entityHelper) { this.entityHelper = entityHelper; } public String formatFieldName(String fieldName) { String[] names = fieldName.split("_"); StringBuilder nameBuffer = new StringBuilder(); nameBuffer.append(names[0]); for (int i = 1; i < names.length; i++) { nameBuffer.append(names[i].substring(0, 1).toUpperCase()); nameBuffer.append(names[i].substring(1)); } return nameBuffer.toString(); } public void setHibernateTemplate(HibernateTemplate hibernateTemplate) { this.hibernateTemplate = hibernateTemplate; } public HibernateTemplate getHibernateTemplate() { return this.hibernateTemplate; } public int getDatabaseType(Class<?> type) { if (type == String.class) { return 12; } if (type == Integer.class) { return 4; } if (type == Short.class) { return 4; } if (type == Date.class) { return 91; } return 12; } public void registerParameter(CallableStatement cs, QueryParameter queryParameter) throws SQLException { Set<String> paraNames = queryParameter.findParameterNames(); for (String paraName : paraNames) { Parameter parameter = queryParameter.findParameter(paraName); if (parameter != null) { int paraType = getDatabaseType(parameter.findType()); Object value = parameter.getValue(); if (parameter.getDirction().equals("IN")) { cs.setObject(paraName, value, paraType); } else { cs.registerOutParameter(paraName, paraType); if (value != null) { cs.setObject(paraName, value, paraType); } } } } } public List<T> queryList(final String procedureName, final QueryParameter queryParameter) { return (List)getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection(); String queryString = ""; String queryParams = ""; int paramCount = 0; paramCount = queryParameter.size().intValue(); for (int i = 0; i < paramCount; i++) { queryParams = queryParams + "?,"; } if (!"".equals(queryParams)) { queryParams = queryParams.substring(0, queryParams.length() - 1); } queryString = "{call " + procedureName + "(" + queryParams + ")}"; CallableStatement cs = con.prepareCall(queryString); Set<String> paraNames = queryParameter.findParameterNames(); List resultList = null; ResultSet rs = null; try { ProcedureSqlServer.this.registerParameter(cs, queryParameter); rs = cs.executeQuery(); if (ProcedureSqlServer.this.entityHelper == null) { throw new Exception("entityHelper异常"); } resultList = ProcedureSqlServer.this.entityHelper.convert(rs); for (String paraName : paraNames) { Parameter parameter = queryParameter.findParameter(paraName); if (parameter != null) { if (parameter.getDirction().equals("OUT")) { parameter.setValue(cs.getObject(paraName)); } } } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { if (rs != null) { rs.close(); } if (cs != null) { cs.close(); } if (con != null) { con.close(); } } return resultList; } }); } public void execute(final String procedureName, final QueryParameter queryParameter) { getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection(); String queryString = ""; String queryParams = ""; int paramCount = 0; paramCount = queryParameter.size().intValue(); for (int i = 0; i < paramCount; i++) { queryParams = queryParams + "?,"; } if (!"".equals(queryParams)) { queryParams = queryParams.substring(0, queryParams.length() - 1); } queryString = "{call " + procedureName + "(" + queryParams + ")}"; CallableStatement cs = con.prepareCall(queryString); Set<String> paraNames = queryParameter.findParameterNames(); try { ProcedureSqlServer.this.registerParameter(cs, queryParameter); cs.execute(); for (String paraName : paraNames) { Parameter parameter = queryParameter.findParameter(paraName); if (parameter != null) { if (parameter.getDirction().equals("OUT")) { parameter.setValue(cs.getObject(paraName)); } } } } catch (Exception e) { e.printStackTrace(); } finally { if (cs != null) { cs.close(); } if (con != null) { con.close(); } } return null; } }); } public List<Map<String, String>> queryMapList(final String procedureName, final QueryParameter queryParameter) { return (List)getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection(); String queryString = ""; String queryParams = ""; int paramCount = 0; paramCount = queryParameter.size().intValue(); for (int i = 0; i < paramCount; i++) { queryParams = queryParams + "?,"; } if (!"".equals(queryParams)) { queryParams = queryParams.substring(0, queryParams.length() - 1); } queryString = "{call " + procedureName + "(" + queryParams + ")}"; CallableStatement cs = con.prepareCall(queryString); Set<String> paraNames = queryParameter.findParameterNames(); List resultList = null; ResultSet rs = null; try { ProcedureSqlServer.this.registerParameter(cs, queryParameter); rs = cs.executeQuery(); int colCount = rs.getMetaData().getColumnCount(); String[] colNameList = new String[colCount]; for (int i = 0; i < colCount; i++) { String fieldName = rs.getMetaData().getColumnName(i + 1); colNameList[i] = ProcedureSqlServer.this.formatFieldName(fieldName.toLowerCase()); } resultList = new ArrayList(); int i; while (rs.next()) { Map rowMap = new HashMap(); for (i = 0; i < colCount; i++) { Object val = rs.getObject(i + 1); if (val != null) { if ((val instanceof Date)) { val = rs.getTimestamp(i + 1); rowMap.put(colNameList[i], val.toString()); } else { rowMap.put(colNameList[i], val.toString()); } } else { rowMap.put(colNameList[i], null); } } resultList.add(rowMap); } for (String paraName : paraNames) { Parameter parameter = queryParameter.findParameter(paraName); if (parameter != null) { if (parameter.getDirction().equals("OUT")) { parameter.setValue(cs.getObject(paraName)); } } } } catch (Exception e) { e.printStackTrace(); } finally { if (rs != null) { rs.close(); } if (cs != null) { cs.close(); } if (con != null) { con.close(); } } return resultList; } }); }} 0 0
- ProcedureSqlServer
- activity四种启动模式（launchmodel）混合使用详解
- android 自定义控件
- Android RecyclerView开源框架（下拉刷新、底部加载更多）
- CentOS6跟换yum源
- Spring-org.springframework.beans.factory.BeanNotOfRequiredTypeException
- ProcedureSqlServer
- <%@ CODEPAGE=65001 %>
- 《算法》第一章——利用两个栈实现一个队列的push和pop操作
- 跨进程通信之AIDL
- 谈谈~~
- http://me.rice.edu/~akin/
- Android 属性动画(Property Animation)
- DTD与XSD的一个范例
- Memcached java client使用中的问题
