util之 oracle SQLUtil,java JDBC 实现sql语句execute执行插入、更新、删除dml操作,查询queryList数据库单列List数据,查询queryObject单记录对象数据及数据关闭close重载实现等
本文摘自
http://www.xwood.net/_site_domain_/_root/5870/5874/t_c277906.html
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import javax.sql.DataSource; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; public final class SQLUtil { private static Log logger = LogFactory.getLog(SQLUtil.class); /** * 执行插入、更新、删除操作 * * @param dataSource * @param sql * @param params * @throws SQLException */ public static int execute(DataSource dataSource, String sql, Object... params) throws SQLException { logger.debug("start execute sql:" + sql); long startTime = System.currentTimeMillis(); Connection conn = null; Statement stat = null; int updateCount = 0; try { conn = dataSource.getConnection(); if (params == null || params.length == 0) { stat = conn.createStatement(); stat.execute(sql); } else { stat = conn.prepareStatement(sql); PreparedStatement pstat = (PreparedStatement) stat; int parameterIndex = 1; for (Object param : params) { pstat.setObject(parameterIndex, param); parameterIndex++; } pstat.execute(); } updateCount = stat.getUpdateCount(); logger.debug("end execute sql:" + sql + " UpdateCount:" + updateCount + " " + (System.currentTimeMillis() - startTime) + "ms"); } finally { close(stat); close(conn); } return updateCount; } /** * 查询数据库单列List数据 * * @param dataSource * @param sql * @param params * @return * @throws SQLException */ public static List<Object> queryList(DataSource dataSource, String sql, Object... params) throws SQLException { logger.debug("start execute sql:" + sql); long startTime = System.currentTimeMillis(); Connection conn = null; Statement stat = null; List<Object> resultList = new ArrayList<Object>(); try { conn = dataSource.getConnection(); stat = conn.createStatement(); if (params == null || params.length == 0) { stat = conn.createStatement(); ResultSet rs = stat.executeQuery(sql); int i = 1; while (rs.next()) { resultList.add(rs.getObject(i)); i++; } return resultList; } else { stat = conn.prepareStatement(sql); PreparedStatement pstat = (PreparedStatement) stat; int parameterIndex = 1; for (Object param : params) { pstat.setObject(parameterIndex, param); parameterIndex++; } ResultSet rs = pstat.executeQuery(); int i = 1; while (rs.next()) { resultList.add(rs.getObject(i)); i++; } } logger.debug("end query sql:" + sql + " Result:" + resultList + " " + (System.currentTimeMillis() - startTime) + "ms"); } finally { stat.close(); conn.close(); } return resultList; } public static Object queryObject(DataSource dataSource, String sql, Object... params) throws SQLException { List<Object> resultList = queryList(dataSource, sql, params); if (resultList == null || resultList.size() == 0) return null; return resultList.get(0); } public static void close(Connection con) { if (con != null) try { con.close(); } catch (SQLException ex) { logger.debug("Could not close JDBC Connection", ex); } catch (Throwable ex) { logger.debug("Unexpected exception on closing JDBC Connection", ex); } } public static void close(Statement stmt) { if (stmt != null) try { stmt.close(); } catch (SQLException ex) { logger.trace("Could not close JDBC Statement", ex); } catch (Throwable ex) { logger.trace("Unexpected exception on closing JDBC Statement", ex); } } public static void close(ResultSet rs) { if (rs != null) try { rs.close(); } catch (SQLException ex) { logger.trace("Could not close JDBC ResultSet", ex); } catch (Throwable ex) { logger.trace("Unexpected exception on closing JDBC ResultSet", ex); } } }