查询数据库时直接封住成List&Map&String,String&&格式
来源：it-home
最近研究项目查询底层使用的dao时发现了一个比较好用的封装类，在查询数据库时，能够将查询出来的字段直接封装成key-value模式，即，key是字段名，value是字段对应的值，当然是一个map代表一行数据/对象，多个对象在一个list中，代码如下： import java.lang.reflect.Mimport java.sql.CallableSimport java.sql.Cimport java.sql.PreparedSimport java.sql.ResultSimport java.sql.ResultSetMetaDimport java.sql.SQLEimport java.sql.Simport java.sql.Timport java.sql.Timport java.util.ArrayLimport java.util.Cimport java.util.HashMimport java.util.Limport org.apache.log4j.Limport org.hibernate.HibernateEimport org.hibernate.Simport org.springframework.orm.hibernate3.HibernateCimport org.springframework.orm.hibernate3.SessionFactoryUimport org.springframework.stereotype.Rimport mon.utils.DateTimeUimport mon.utils.SystemCimport com.minxinloan.dao.JdbcD/** * Created with IntelliJ IDEA. * User: Administrator * Date: 13-10-21 * Time: 下午1:24 * To change this template use File | Settings | File Templates. */public class JdbcDaoImpl extends BaseDaoImpl implements JdbcDao{
private final static Logger log = Logger.getLogger(JdbcDaoImpl.class);
* get data from the ResultSet which is save in the given colnum and type
* @param rs
* @param colNum
* @param type
* @throws SQLException
*/ private Object getValue(final ResultSet rs, int colNum, int type)
throws SQLException {
switch (type) {
case Types.ARRAY:
case Types.BLOB://
case Types.CLOB:
case Types.DISTINCT:
case Types.LONGVARBINARY:
case Types.VARBINARY:
case Types.BINARY:
case Types.REF:
case Types.STRUCT:
default: {
Object value = rs.getObject(colNum);
if (rs.wasNull() || (value == null))
* change a ResultSet to ArrayList if the data in ResultSet is null then
* save it as a default string
* @param resultSet
* @param strDefalut
* @param transType
"MAP"-every record translate to HashMap,"LIST"-every record translate to ArrayList
* @throws SQLException
*/ public ArrayList FromResultToArrayList(ResultSet resultSet,String strDefalut,String transType) {
ArrayList dataList = new ArrayList();
if (resultSet == null)
return dataL
// get the strut of the ResultSet
ResultSetMetaData resultmd = resultSet.getMetaData();
if (resultmd == null)
return dataL
// get the total count of column
int colCount = resultmd.getColumnCount();
// deal with all data one by one
while (resultSet.next()) {
Object row =
if ("MAP".equalsIgnoreCase(transType)){
row = new HashMap();
row = new ArrayList(colCount);
for (int i = 1; i &= colC i++) {
// get type of column
= resultmd.getColumnType(i);
String colName = resultmd.getColumnName(i);
// get data
if ("MAP".equalsIgnoreCase(transType)){
((HashMap)row).put(colName.toUpperCase(), ObjectToString(getValue(resultSet, i, type),
strDefalut));
((ArrayList)row).add(ObjectToString(getValue(resultSet, i, type),
strDefalut));
dataList.add(row);
} catch (SQLException e) {
log.error(e);
return dataL }
static public Object convertClobColumn(Object value) throws Exception {
if (value != null) {
if (value.getClass().getName().toLowerCase().contains("clob")) {
StringBuilder sb = new StringBuilder();
long clenl = (Long) new Object() {
Method getMethod(Object obj) throws Exception {
Method method =
method = obj.getClass().getMethod("length", null);
}.getMethod(value).invoke(value, null);
int bufl = 4096;
bufl = bufl & clenl ? bufl : (int)
int rcount = 0;
for (long l = 1; l &= l += bufl) {
rcount = (int) (clenl - l + 1);
rcount = rcount & bufl ? rcount :
sb.append(new Object() {
Method getMethod(Object obj) throws Exception {
Method method =
method = obj.getClass().getMethod("getSubString",
long.class, int.class);
}.getMethod(value).invoke(value, l, rcount));
value = sb.toString();
* change the object to String type
* @param obj
* @param strDefalut
*/ private String ObjectToString(Object obj, String strDefalut) {
if (obj == null)
return strD
String strResult = strD
// date type java.sql.Date
if (obj instanceof java.sql.Date) {
strResult = DateTimeUtils.DateToString((java.sql.Date) obj,
SystemConstant.CURR_JAVA_DATE_FORMATTER, strDefalut);
// date type java.sql.T
} else if (obj instanceof Timestamp) {
strResult = DateTimeUtils.TimestampToString(
(java.sql.Timestamp) obj,
SystemConstant.CURR_JAVA_DATETIME_FORMATTER, strDefalut);
// numeral type
} else if (obj instanceof java.math.BigDecimal) {
strResult = ((java.math.BigDecimal) obj).toString();
}else if(obj.getClass().getName().toLowerCase().contains("clob")){
obj=convertClobColumn(obj);
}catch(Exception e){e.printStackTrace();}
strResult=obj==null?"":obj.toString();
strResult = obj.toString();
if (strResult != null)
strResult = strResult.trim();
return strR } private String ObjectToString(Object obj) {
return ObjectToString(obj, ""); } /**
* 从数据库获取查询结果
* @param sql
* @param encapType
* @see queryData(final String sql,final String encapType,final List params)
*/ public List queryData(final String sql,final String encapType){
return queryData(sql,encapType,null);
* 从数据库获取查询结果
* @param sql
* @param encapType: 查询结果封装方式MAP/LIST
* @param params: 参数列表
*/ public List queryData(final String sql,final String encapType,final List params){
return (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException {
PreparedStatement pst =
ResultSet rs =
ArrayList resultList =
Connection conn =
conn = session.connection();
pst = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
if(params!=null){
for (int i = 0; i&params.size(); i++){
Object obj = params.get(i);
if (obj instanceof Integer){
pst.setInt(i+1, ((Integer)obj).intValue());
}else if (obj instanceof Long){
pst.setLong(i+1, ((Long)obj).longValue());
}else if (obj instanceof Double){
pst.setDouble(i+1, ((Double)obj).doubleValue());
}else if (obj instanceof Float){
pst.setFloat(i+1, ((Float)obj).floatValue());
}else if (obj instanceof java.util.Date){
pst.setDate(i+1, (java.sql.Date)obj);
}else if (obj instanceof java.sql.Date){
pst.setDate(i+1, (java.sql.Date)obj);
}else if (obj instanceof String){
pst.setString(i+1, (String)obj);
pst.setObject(i+1, obj);
rs = pst.executeQuery();
resultList = FromResultToArrayList(rs,SystemConstant.DEFAULTNNULLVAL,encapType);
} catch (SQLException e) {
log.error(e.getMessage(),e);
log.error("sql："+sql+"————————encapType:"+encapType+"————————params:"+params);
} finally {
closeResultSet(rs);
closeStatement(pst);
return resultL
public List queryData(String sql){
return queryData(sql,"LIST"); }
public List queryDataOfMapEncap(String sql){
return queryData(sql,"MAP"); } /**
* 获得查询的结果总数
* @param conn
* @param originalSql
*/ public int getTotalCount(final String sql){
Integer iResult =
(Integer) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException {
Connection conn =
int iRes = 0;
ResultSet rs =
PreparedStatement pst =
String newSQL = "SELECT COUNT(*) FROM (" + sql + ") a";
conn = session.connection();
pst = conn.prepareStatement(newSQL,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = pst.executeQuery();
if (rs.next())
iRes = rs.getInt(1);
} catch (SQLException e) {
log.error(e);
} finally {
closeResultSet(rs);
closeStatement(pst);
return new Integer(iRes);
return iResult.intValue(); } protected void closeResultSet(ResultSet result) {
if (result != null) {
result.close();
} catch (Exception e) {
result = } protected void closeStatement(Statement st) {
if (st != null) {
st.close();
} catch (Exception e) {
protected void closeConnection(Connection conn) {
if (conn != null) {
conn.close();
} catch (Exception e) {
public int execute(final String sql){
Integer iResult =
(Integer) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException {
int returnVal = 0;
PreparedStatement statement =
Connection conn = session.connection();
statement = conn.prepareStatement(sql);
returnVal = statement.executeUpdate();
return new Integer(returnVal);
closeStatement(statement);
return iResult.intValue();
public int updateAndCommit(final String sql){
Integer iResult =
(Integer) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException {
int returnVal = 0;
PreparedStatement statement =
Connection conn = session.connection();
statement = conn.prepareStatement(sql);
returnVal = statement.executeUpdate();
return new Integer(returnVal);
closeStatement(statement);
return iResult.intValue();
public String executeProc(final String procName){
String iResult =
(String) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException {
String returnVal = "";
CallableStatement statement =
Connection conn = session.connection();
statement = conn.prepareCall(procName);
statement.registerOutParameter(1, Types.VARCHAR);
statement.execute();
returnVal = statement.getString(1);
return returnV
closeStatement(statement);
* @throws Exception
public Connection getConnection() throws Exception{
return SessionFactoryUtils.getDataSource(getSessionFactory()).getConnection();
public String getCommaSQL(String sql){
String result=
List list=queryData(sql);
if(list.size()&0){
List tmp=(List)list.get(0);
result=tmp.get(0).toString();
} @Override public void saveOrUpdateAll(List pos) {
// TODO Auto-generated method stub
getHibernateTemplate().saveOrUpdateAll(pos); }
* 批量插入的方法
* @param sql
* @param argss
* @throws Exception
*/ @Override
public int[] batchExecuteSql(final String sql, final Collection&Object[]& argss) throws Exception {
return (int[]) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)throws HibernateException, SQLException {
int[] rvl =
PreparedStatement stmt =
Connection conn = session.connection();
stmt = conn.prepareStatement(sql);
stmt.clearBatch();
for (Object[] args : argss) {
if (args != null && args.length & 0) {
for(int i = 0; i & args. ++i){
stmt.setObject(i + 1, args[i]);
stmt.addBatch();
rvl = stmt.executeBatch();
} catch (SQLException e) {
} finally {
if (stmt != null)
stmt.close();
}); }复制代码
这里使用的hibernate+jdbc一起的。
免责声明：本站部分内容、图片、文字、视频等来自于互联网，仅供大家学习与交流。相关内容如涉嫌侵犯您的知识产权或其他合法权益，请向本站发送有效通知，我们会及时处理。反馈邮箱&&&&。
学生服务号
在线咨询，奖学金返现，名师点评，等你来互动