Java tutorial
/* * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you under the Apache License, Version 2.0 (the * "License"); you may not use this file except in compliance * with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.glaf.core.jdbc; import java.math.BigDecimal; import java.math.BigInteger; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Timestamp; import java.sql.Types; import java.text.NumberFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import org.apache.commons.lang.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.ibatis.io.Resources; import org.apache.ibatis.type.TypeHandler; import org.apache.ibatis.type.TypeHandlerRegistry; import com.glaf.core.base.ResultModel; import com.glaf.core.base.RowModel; import com.glaf.core.config.BaseConfiguration; import com.glaf.core.config.Configuration; import com.glaf.core.config.DBConfiguration; import com.glaf.core.dialect.Dialect; import com.glaf.core.domain.ColumnDefinition; import com.glaf.core.entity.SqlExecutor; import com.glaf.core.util.CaseInsensitiveHashMap; import com.glaf.core.util.DBUtils; import com.glaf.core.util.DateUtils; import com.glaf.core.util.FieldType; import com.glaf.core.util.JdbcUtils; import com.glaf.core.util.StringTools; public class QueryHelper { protected static final Log logger = LogFactory.getLog(QueryHelper.class); protected static Configuration conf = BaseConfiguration.create(); protected static TypeHandlerRegistry typeHandlerRegistry = new TypeHandlerRegistry(); public QueryHelper() { } @SuppressWarnings("unchecked") public List<ColumnDefinition> getColumnDefinitions(String systemName, String sql, Map<String, Object> params) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, params); Connection conn = null; PreparedStatement psmt = null; ResultSet rs = null; ResultSetMetaData rsmd = null; try { conn = DBConnectionFactory.getConnection(systemName); psmt = conn.prepareStatement(sqlExecutor.getSql()); if (sqlExecutor.getParameter() != null) { List<Object> values = (List<Object>) sqlExecutor.getParameter(); JdbcUtils.fillStatement(psmt, values); } rs = psmt.executeQuery(); rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>(); for (int index = 1; index <= count; index++) { int sqlType = rsmd.getColumnType(index); ColumnDefinition column = new ColumnDefinition(); column.setIndex(index); column.setColumnName(rsmd.getColumnName(index)); column.setColumnLabel(rsmd.getColumnLabel(index)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(index)); column.setScale(rsmd.getScale(index)); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase())); columns.add(column); } return columns; } catch (Exception ex) { throw new RuntimeException(ex); } finally { JdbcUtils.close(rs); JdbcUtils.close(psmt); JdbcUtils.close(conn); } } @SuppressWarnings("unchecked") public List<ColumnDefinition> getColumns(Connection conn, String sql, Map<String, Object> paramMap) { if (!DBUtils.isLegalQuerySql(sql)) { throw new RuntimeException(" SQL statement illegal "); } List<ColumnDefinition> columns = new java.util.ArrayList<ColumnDefinition>(); PreparedStatement psmt = null; ResultSetMetaData rsmd = null; ResultSet rs = null; try { List<Object> values = null; if (paramMap != null) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); sql = sqlExecutor.getSql(); values = (List<Object>) sqlExecutor.getParameter(); } logger.debug("sql:\n" + sql); logger.debug("values:" + values); psmt = conn.prepareStatement(sql); if (values != null && !values.isEmpty()) { JdbcUtils.fillStatement(psmt, values); } rs = psmt.executeQuery(); rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); for (int i = 1; i <= count; i++) { int sqlType = rsmd.getColumnType(i); ColumnDefinition column = new ColumnDefinition(); column.setColumnLabel(rsmd.getColumnLabel(i)); column.setColumnName(rsmd.getColumnName(i)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(i)); column.setScale(rsmd.getScale(i)); column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase())); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } if (!columns.contains(column)) { columns.add(column); } logger.debug(column.getColumnName() + " sqlType:" + sqlType + " precision:" + column.getPrecision() + " scale:" + column.getScale()); } } catch (Exception ex) { throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } return columns; } @SuppressWarnings("unchecked") public int getCount(Connection conn, String sql, Map<String, Object> params) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, params); PreparedStatement psmt = null; ResultSet rs = null; try { psmt = conn.prepareStatement(sqlExecutor.getSql()); if (sqlExecutor.getParameter() != null) { List<Object> values = (List<Object>) sqlExecutor.getParameter(); JdbcUtils.fillStatement(psmt, values); } rs = psmt.executeQuery(); if (rs.next()) { return rs.getInt(1); } return 0; } catch (Exception ex) { throw new RuntimeException(ex); } finally { JdbcUtils.close(rs); JdbcUtils.close(psmt); } } /** * @param conn * ? * @param sqlExecutor * * @return */ @SuppressWarnings("unchecked") public List<Map<String, Object>> getResultList(Connection conn, SqlExecutor sqlExecutor) { if (!DBUtils.isLegalQuerySql(sqlExecutor.getSql())) { throw new RuntimeException(" SQL statement illegal "); } List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>(); PreparedStatement psmt = null; ResultSet rs = null; ResultSetMetaData rsmd = null; try { psmt = conn.prepareStatement(sqlExecutor.getSql()); if (sqlExecutor.getParameter() != null) { List<Object> values = (List<Object>) sqlExecutor.getParameter(); JdbcUtils.fillStatement(psmt, values); } rs = psmt.executeQuery(); if (conf.getBoolean("useMyBatisResultHandler", false)) { resultList = this.getResults(rs); } else { rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>(); for (int index = 1; index <= count; index++) { int sqlType = rsmd.getColumnType(index); ColumnDefinition column = new ColumnDefinition(); column.setIndex(index); column.setColumnName(rsmd.getColumnName(index)); column.setColumnLabel(rsmd.getColumnLabel(index)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(index)); column.setScale(rsmd.getScale(index)); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase())); columns.add(column); } int startIndex = 1; while (rs.next() && startIndex <= 50000) { int index = 0; startIndex++; Map<String, Object> rowMap = new HashMap<String, Object>(); Iterator<ColumnDefinition> iterator = columns.iterator(); while (iterator.hasNext()) { ColumnDefinition column = iterator.next(); String columnLabel = column.getColumnLabel(); String columnName = column.getColumnName(); if (StringUtils.isEmpty(columnName)) { columnName = column.getColumnLabel(); } columnName = columnName.toLowerCase(); String javaType = column.getJavaType(); index = index + 1; if ("String".equals(javaType)) { String value = rs.getString(column.getIndex()); if (value != null) { value = value.trim(); rowMap.put(columnName, value); rowMap.put(columnLabel, value); } } else if ("Integer".equals(javaType)) { try { Integer value = rs.getInt(column.getIndex()); rowMap.put(columnName, value); rowMap.put(columnLabel, value); } catch (Exception e) { String str = rs.getString(column.getIndex()); logger.error("integer:" + str); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); rowMap.put(columnName, num.intValue()); rowMap.put(columnLabel, rowMap.get(columnName)); logger.debug("?:" + num.intValue()); } } else if ("Long".equals(javaType)) { try { Long value = rs.getLong(column.getIndex()); rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } catch (Exception e) { String str = rs.getString(column.getIndex()); logger.error("long:" + str); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); rowMap.put(columnName, num.longValue()); rowMap.put(columnLabel, num.longValue()); logger.debug("?:" + num.longValue()); } } else if ("Double".equals(javaType)) { try { Double d = rs.getDouble(column.getIndex()); rowMap.put(columnName, d); rowMap.put(columnLabel, d); } catch (Exception e) { String str = rs.getString(column.getIndex()); logger.error("double:" + str); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); rowMap.put(columnName, num.doubleValue()); rowMap.put(columnLabel, num.doubleValue()); logger.debug("?:" + num.doubleValue()); } } else if ("Boolean".equals(javaType)) { rowMap.put(columnName, rs.getBoolean(column.getIndex())); rowMap.put(columnLabel, rowMap.get(columnName)); } else if ("Date".equals(javaType)) { rowMap.put(columnName, rs.getTimestamp(column.getIndex())); rowMap.put(columnLabel, rowMap.get(columnName)); } else if ("Blob".equals(javaType)) { // ignore } else { Object value = rs.getObject(column.getIndex()); if (value != null) { if (value instanceof String) { value = (String) value.toString().trim(); } rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } } } rowMap.put("startIndex", startIndex); resultList.add(rowMap); } } logger.debug(">resultList size=" + resultList.size()); return resultList; } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } } /** * @param conn * ? * @param sqlExecutor * ? * @param start * 0 * @param pageSize * ? * @return */ @SuppressWarnings("unchecked") public List<Map<String, Object>> getResultList(Connection conn, SqlExecutor sqlExecutor, int start, int pageSize) { if (!DBUtils.isLegalQuerySql(sqlExecutor.getSql())) { throw new RuntimeException(" SQL statement illegal "); } List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>(); String sql = sqlExecutor.getSql(); PreparedStatement psmt = null; ResultSet rs = null; ResultSetMetaData rsmd = null; boolean supportsPhysicalPage = false; try { Dialect dialect = DBConfiguration.getDatabaseDialect(conn); if (dialect != null && dialect.supportsPhysicalPage()) { supportsPhysicalPage = true; sql = dialect.getLimitString(sql, start, pageSize); logger.debug("sql=" + sqlExecutor.getSql()); logger.debug(">>sql=" + sql); } psmt = conn.prepareStatement(sql); if (sqlExecutor.getParameter() != null) { List<Object> values = (List<Object>) sqlExecutor.getParameter(); JdbcUtils.fillStatement(psmt, values); logger.debug(">>values=" + values); } rs = psmt.executeQuery(); if (conf.getBoolean("useMyBatisResultHandler", false)) { resultList = this.getResults(rs); } else { rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>(); for (int i = 1; i <= count; i++) { int sqlType = rsmd.getColumnType(i); ColumnDefinition column = new ColumnDefinition(); column.setIndex(i); column.setColumnName(rsmd.getColumnName(i)); column.setColumnLabel(rsmd.getColumnLabel(i)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(i)); column.setScale(rsmd.getScale(i)); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase())); columns.add(column); } if (!supportsPhysicalPage) { logger.debug("---------------------skipRows:" + start); this.skipRows(rs, start, pageSize); } logger.debug("---------------------columns:" + columns.size()); logger.debug("---------------------start:" + start); logger.debug("---------------------pageSize:" + pageSize); // int index = 0; while (rs.next()) { // index++; // logger.debug("---------------------row index:" + index); Map<String, Object> rowMap = new HashMap<String, Object>(); Iterator<ColumnDefinition> iterator = columns.iterator(); while (iterator.hasNext()) { ColumnDefinition column = iterator.next(); String columnLabel = column.getColumnLabel(); String columnName = column.getColumnName(); if (StringUtils.isEmpty(columnName)) { columnName = column.getColumnLabel(); } columnName = columnName.toLowerCase(); String javaType = column.getJavaType(); if ("String".equals(javaType)) { String value = rs.getString(column.getIndex()); if (value != null) { value = value.trim(); rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } } else if ("Integer".equals(javaType)) { try { Integer value = rs.getInt(column.getIndex()); rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } catch (Exception e) { String str = rs.getString(column.getIndex()); logger.error("integer:" + str); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); rowMap.put(columnName, num.intValue()); rowMap.put(columnLabel, rowMap.get(columnName)); logger.debug("?:" + num.intValue()); } } else if ("Long".equals(javaType)) { try { Long value = rs.getLong(column.getIndex()); rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } catch (Exception e) { String str = rs.getString(column.getIndex()); logger.error("long:" + str); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); rowMap.put(columnName, num.longValue()); rowMap.put(columnLabel, rowMap.get(columnName)); logger.debug("?:" + num.longValue()); } } else if ("Double".equals(javaType)) { try { Double d = rs.getDouble(column.getIndex()); rowMap.put(columnName, d); rowMap.put(columnLabel, rowMap.get(columnName)); } catch (Exception e) { String str = rs.getString(column.getIndex()); logger.error("double:" + str); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); rowMap.put(columnName, num.doubleValue()); rowMap.put(columnLabel, rowMap.get(columnName)); logger.debug("?:" + num.doubleValue()); } } else if ("Boolean".equals(javaType)) { rowMap.put(columnName, rs.getBoolean(column.getIndex())); rowMap.put(columnLabel, rowMap.get(columnName)); } else if ("Date".equals(javaType)) { rowMap.put(columnName, rs.getTimestamp(column.getIndex())); rowMap.put(columnLabel, rowMap.get(columnName)); } else if ("Blob".equals(javaType)) { } else { Object value = rs.getObject(column.getIndex()); if (value != null) { if (value instanceof String) { value = (String) value.toString().trim(); } rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } } } resultList.add(rowMap); } } logger.debug(">resultList size = " + resultList.size()); return resultList; } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } } public List<Map<String, Object>> getResultList(Connection conn, String sql, Map<String, Object> paramMap) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); return this.getResultList(conn, sqlExecutor); } /** * @param conn * ? * @param start * 0 * @param pageSize * ? * @param sql * ? * @param paramMap * ? * @return */ @SuppressWarnings("unchecked") public ResultModel getResultList(Connection conn, String sql, Map<String, Object> paramMap, int start, int pageSize) { if (!DBUtils.isLegalQuerySql(sql)) { throw new RuntimeException(" SQL statement illegal "); } ResultModel resultModel = new ResultModel(); boolean supportsPhysicalPage = false; PreparedStatement psmt = null; ResultSetMetaData rsmd = null; ResultSet rs = null; Dialect dialect = null; try { dialect = DBConfiguration.getDatabaseDialect(conn); if (dialect != null && dialect.supportsPhysicalPage()) { logger.debug("sql=" + sql); supportsPhysicalPage = dialect.supportsPhysicalPage(); sql = dialect.getLimitString(sql, start, pageSize); logger.debug(">>sql=" + sql); } List<Object> values = null; if (paramMap != null) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); sql = sqlExecutor.getSql(); values = (List<Object>) sqlExecutor.getParameter(); } logger.debug("sql:\n" + sql); logger.debug("values:" + values); psmt = conn.prepareStatement(sql); if (values != null && !values.isEmpty()) { JdbcUtils.fillStatement(psmt, values); } List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>(); rs = psmt.executeQuery(); rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); for (int i = 1; i <= count; i++) { int sqlType = rsmd.getColumnType(i); ColumnDefinition column = new ColumnDefinition(); column.setIndex(i); column.setColumnName(rsmd.getColumnName(i)); column.setColumnLabel(rsmd.getColumnLabel(i)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(i)); column.setScale(rsmd.getScale(i)); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } column.setName(StringTools.lower(StringTools.camelStyle(column.getColumnLabel()))); columns.add(column); } resultModel.setHeaders(columns); if (!supportsPhysicalPage) { this.skipRows(rs, start); } int k = 0; while (rs.next() && k++ < pageSize) { int index = 0; RowModel rowModel = new RowModel(); Iterator<ColumnDefinition> iterator = columns.iterator(); while (iterator.hasNext()) { ColumnDefinition column = iterator.next(); ColumnDefinition c = new ColumnDefinition(); c.setColumnName(column.getColumnName()); c.setColumnLabel(column.getColumnLabel()); c.setName(column.getName()); c.setJavaType(column.getJavaType()); c.setPrecision(column.getPrecision()); c.setScale(column.getScale()); String javaType = column.getJavaType(); index = index + 1; if ("String".equals(javaType)) { String value = rs.getString(column.getIndex()); c.setValue(value); } else if ("Integer".equals(javaType)) { try { Integer value = rs.getInt(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.intValue()); } } else if ("Long".equals(javaType)) { try { Long value = rs.getLong(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.longValue()); } } else if ("Double".equals(javaType)) { try { Double value = rs.getDouble(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.doubleValue()); } } else if ("Boolean".equals(javaType)) { Boolean value = rs.getBoolean(column.getIndex()); c.setValue(value); } else if ("Date".equals(javaType)) { Timestamp value = rs.getTimestamp(column.getIndex()); c.setValue(value); } else { c.setValue(rs.getObject(column.getIndex())); } rowModel.addColumn(c); } resultModel.addRow(rowModel); } } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } return resultModel; } public List<Map<String, Object>> getResultList(SqlExecutor sqlExecutor, int start, int pageSize) { Connection conn = null; try { conn = DBConnectionFactory.getConnection(); return this.getResultList(conn, sqlExecutor, start, pageSize); } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(conn); } } /** * ?SQL?? * * @param sql * @param paramMap * @return */ public List<Map<String, Object>> getResultList(String sql, Map<String, Object> paramMap) { Connection conn = null; try { conn = DBConnectionFactory.getConnection(); return this.getResultList(conn, sql, paramMap); } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(conn); } } /** * ?SQL??? * * @param sql * @param start * 0 * @param pageSize * @param paramMap * @return */ public List<Map<String, Object>> getResultList(String sql, Map<String, Object> paramMap, int start, int pageSize) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); Connection conn = null; try { conn = DBConnectionFactory.getConnection(); return this.getResultList(conn, sqlExecutor, start, pageSize); } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(conn); } } /** * ?SQL?? * * @param systemName * @param sqlExecutor * @return */ public List<Map<String, Object>> getResultList(String systemName, SqlExecutor sqlExecutor) { Connection conn = null; try { conn = DBConnectionFactory.getConnection(systemName); return this.getResultList(conn, sqlExecutor); } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(conn); } } /** * ????SQL?? * * @param systemName * @param sql * @param paramMap * @return */ public List<Map<String, Object>> getResultList(String systemName, String sql, Map<String, Object> paramMap) { Connection conn = null; try { conn = DBConnectionFactory.getConnection(systemName); return this.getResultList(conn, sql, paramMap); } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(conn); } } /** * ????SQL??? * * @param systemName * @param sql * @param firstResult * 0 * @param maxResults * @param paramMap * @return */ public List<Map<String, Object>> getResultList(String systemName, String sql, Map<String, Object> paramMap, int start, int pageSize) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); Connection conn = null; try { conn = DBConnectionFactory.getConnection(systemName); return this.getResultList(conn, sqlExecutor, start, pageSize); } catch (Exception ex) { throw new RuntimeException(ex); } finally { JdbcUtils.close(conn); } } /** * ??? * * @param conn * JDBC * @param start * ?0 * @param pageSize * ? * @param tableName * ? * @param paramMap * ?? * @return */ public ResultModel getResultModel(Connection conn, String tableName, Map<String, Object> paramMap, int start, int pageSize) { ResultModel resultModel = null; String sql = "select count(*) from " + tableName; int total = this.getTotal(conn, sql, paramMap); if (total > 0) { sql = "select * from " + tableName; resultModel = this.getResultList(conn, sql, paramMap, start, pageSize); resultModel.setStart(start); resultModel.setPageSize(pageSize); resultModel.setTotal(total); } return resultModel; } public List<Map<String, Object>> getResults(ResultSet rs) { logger.debug("--------------use mybatis results----------------"); try { List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); List<String> columns = new ArrayList<String>(); List<TypeHandler<?>> typeHandlers = new ArrayList<TypeHandler<?>>(); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 0, n = rsmd.getColumnCount(); i < n; i++) { columns.add(rsmd.getColumnLabel(i + 1)); try { Class<?> type = Resources.classForName(rsmd.getColumnClassName(i + 1)); TypeHandler<?> typeHandler = typeHandlerRegistry.getTypeHandler(type); if (typeHandler == null) { typeHandler = typeHandlerRegistry.getTypeHandler(Object.class); } typeHandlers.add(typeHandler); } catch (Exception ex) { ex.printStackTrace(); typeHandlers.add(typeHandlerRegistry.getTypeHandler(Object.class)); } } while (rs.next()) { Map<String, Object> row = new HashMap<String, Object>(); for (int i = 0, n = columns.size(); i < n; i++) { String name = columns.get(i); TypeHandler<?> handler = typeHandlers.get(i); Object value = handler.getResult(rs, name); row.put(name, value); if (value != null && value instanceof java.util.Date) { java.util.Date date = (java.util.Date) value; row.put(name + "_date", DateUtils.getDate(date)); row.put(name + "_datetime", DateUtils.getDateTime(date)); } } list.add(row); } return list; } catch (SQLException ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { try { if (rs != null) { rs.close(); rs = null; } } catch (SQLException e) { } } } @SuppressWarnings("unchecked") public int getTotal(Connection conn, SqlExecutor sqlExecutor) { int total = 0; PreparedStatement psmt = null; ResultSet rs = null; try { psmt = conn.prepareStatement(sqlExecutor.getSql()); if (sqlExecutor.getParameter() != null) { List<Object> values = (List<Object>) sqlExecutor.getParameter(); JdbcUtils.fillStatement(psmt, values); } rs = psmt.executeQuery(); if (rs.next()) { Object object = rs.getObject(1); if (object instanceof Integer) { Integer iCount = (Integer) object; total = iCount.intValue(); } else if (object instanceof Long) { Long iCount = (Long) object; total = iCount.intValue(); } else if (object instanceof BigDecimal) { BigDecimal bg = (BigDecimal) object; total = bg.intValue(); } else if (object instanceof BigInteger) { BigInteger bi = (BigInteger) object; total = bi.intValue(); } else { String x = object.toString(); if (StringUtils.isNotEmpty(x)) { total = Integer.parseInt(x); } } } } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } return total; } @SuppressWarnings("unchecked") public int getTotal(Connection conn, String sql, Map<String, Object> paramMap) { if (!DBUtils.isLegalQuerySql(sql)) { throw new RuntimeException(" SQL statement illegal "); } int total = -1; PreparedStatement psmt = null; ResultSet rs = null; try { List<Object> values = null; if (paramMap != null) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); sql = sqlExecutor.getSql(); values = (List<Object>) sqlExecutor.getParameter(); } sql = DBUtils.removeOrders(sql); logger.debug("sql:\n" + sql); logger.debug("values:" + values); psmt = conn.prepareStatement(sql); if (values != null && !values.isEmpty()) { JdbcUtils.fillStatement(psmt, values); } rs = psmt.executeQuery(); if (rs.next()) { Object object = rs.getObject(1); if (object != null) { if (object instanceof Integer) { Integer iCount = (Integer) object; total = iCount.intValue(); } else if (object instanceof Long) { Long iCount = (Long) object; total = iCount.intValue(); } else if (object instanceof BigDecimal) { BigDecimal bg = (BigDecimal) object; total = bg.intValue(); } else if (object instanceof BigInteger) { BigInteger bi = (BigInteger) object; total = bi.intValue(); } else { String x = object.toString(); if (StringUtils.isNotEmpty(x)) { total = Integer.parseInt(x); } } } } } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } return total; } public int getTotal(SqlExecutor sqlExecutor) { Connection conn = null; try { conn = DBConnectionFactory.getConnection(); return this.getTotal(conn, sqlExecutor); } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(conn); } } public int getTotal(String systemName, SqlExecutor sqlExecutor) { Connection conn = null; try { conn = DBConnectionFactory.getConnection(systemName); return this.getTotal(conn, sqlExecutor); } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(conn); } } @SuppressWarnings("unchecked") public int getTotalRecords(Connection conn, String sql, Map<String, Object> paramMap) { if (!DBUtils.isLegalQuerySql(sql)) { throw new RuntimeException(" SQL statement illegal "); } int total = 0; PreparedStatement psmt = null; ResultSet rs = null; try { List<Object> values = null; if (paramMap != null) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); sql = sqlExecutor.getSql(); values = (List<Object>) sqlExecutor.getParameter(); } sql = DBUtils.removeOrders(sql); logger.debug("sql:\n" + sql); logger.debug("values:" + values); psmt = conn.prepareStatement(sql); if (values != null && !values.isEmpty()) { JdbcUtils.fillStatement(psmt, values); } rs = psmt.executeQuery(); while (rs.next()) { total = total + 1; } } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } return total; } @SuppressWarnings("unchecked") public int getTotalRecords(String systemName, String sql, Map<String, Object> paramMap) { if (!DBUtils.isLegalQuerySql(sql)) { throw new RuntimeException(" SQL statement illegal "); } int total = 0; Connection conn = null; PreparedStatement psmt = null; ResultSet rs = null; try { conn = DBConnectionFactory.getConnection(systemName); List<Object> values = null; if (paramMap != null) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); sql = sqlExecutor.getSql(); values = (List<Object>) sqlExecutor.getParameter(); } sql = DBUtils.removeOrders(sql); logger.debug("sql:\n" + sql); logger.debug("values:" + values); psmt = conn.prepareStatement(sql); if (values != null && !values.isEmpty()) { JdbcUtils.fillStatement(psmt, values); } rs = psmt.executeQuery(); while (rs.next()) { total = total + 1; } } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(rs); JdbcUtils.close(psmt); JdbcUtils.close(conn); } return total; } public Map<String, Object> selectOne(Connection conn, SqlExecutor sqlExecutor) { List<Map<String, Object>> results = getResultList(conn, sqlExecutor); if (results != null && results.size() > 0) { return results.get(0); } return null; } /** * ?SQL?? * * @param sql * @param paramMap * @return */ public Map<String, Object> selectOne(String sql, Map<String, Object> paramMap) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); Connection conn = null; try { conn = DBConnectionFactory.getConnection(); return this.selectOne(conn, sqlExecutor); } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(conn); } } public Map<String, Object> selectOne(String systemName, SqlExecutor sqlExecutor) { Connection conn = null; try { conn = DBConnectionFactory.getConnection(systemName); List<Map<String, Object>> results = getResultList(conn, sqlExecutor); if (results != null && results.size() > 0) { return results.get(0); } return null; } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(conn); } } /** * ?SQL?? * * @param sql * @param paramMap * @return */ public Map<String, Object> selectOne(String systemName, String sql, Map<String, Object> paramMap) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); Connection conn = null; try { conn = DBConnectionFactory.getConnection(systemName); return this.selectOne(conn, sqlExecutor); } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(conn); } } protected void skipRows(ResultSet rs, int start) throws SQLException { if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) { if (start != 0) { logger.debug("rs absolute " + start); rs.absolute(start); } } else { for (int i = 0; i < start; i++) { rs.next(); } } } protected void skipRows(ResultSet rs, int firstResult, int maxResults) throws SQLException { if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) { if (firstResult != 0) { rs.absolute(firstResult); } } else { for (int i = 0; i < firstResult; i++) { rs.next(); } } } public Map<String, Object> toMap(ResultSet rs) throws SQLException { Map<String, Object> result = new CaseInsensitiveHashMap(); ResultSetMetaData rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); for (int i = 1; i <= count; i++) { String columnName = rsmd.getColumnLabel(i); if (StringUtils.isEmpty(columnName)) { columnName = rsmd.getColumnName(i); } Object object = rs.getObject(i); columnName = columnName.toLowerCase(); String name = StringTools.camelStyle(columnName); result.put(name, object); result.put(columnName, object); } return result; } }