今天在一本书上面看到了一个封装数据库操作的辅助类，封装了获得数据库的连接、关闭数据库的连接、执行SQLINSERT/UPDATE/DELETE/SELECT 语句的方法。
代码如下：
|
/** * */ package com.sotaof.struts.db.utils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.text.DateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.logging.Logger; import javax.naming.InitialContext; import javax.sql.DataSource; /** * @Title: DbUtils.java * @Package com.sotaof.struts.db.utils * @Description: TODO * @author A18ccms A18ccms_gmail_com * @date 2013-2-4 下午06:30:36 * @version V1.0 */ public class DbUtils { private static Logger logger = Logger.getLogger("DbUtils"); public static int execute(String sql, List<Object> paramList) throws Exception { if(sql == null || sql.trim().equals("")) { logger.info("parameter is valid!"); } Connection conn = null; PreparedStatement pstmt = null; int result = 0; try { conn = DbUtils.getConnection(); pstmt = DbUtils.getPreparedStatement(conn, sql); setPreparedStatementParam(pstmt, paramList); if(pstmt == null) { return -1; } result = pstmt.executeUpdate(); } catch(Exception e) { logger.info(e.getMessage()); throw new Exception(e); } finally { closeStatement(pstmt); closeConn(conn); } return result; } |
|
public static Connection getConnection() throws Exception { InitialContext cxt = new InitialContext(); if(cxt == null) { throw new Exception("no context!"); } DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/orcl"); if(ds == null) { throw new Exception("Data source not found!"); } return ds.getConnection(); } public static PreparedStatement getPreparedStatement(Connection conn, String sql) throws Exception { if(conn == null || sql == null || sql.trim().equals("")) { return null; } PreparedStatement pstmt = conn.prepareStatement(sql.trim()); return pstmt; } public static void setPreparedStatementParam(PreparedStatement pstmt, List<Object> paramList) throws Exception { if(pstmt == null || paramList == null || paramList.isEmpty()) { return; } DateFormat df = DateFormat.getDateTimeInstance(); for(int i = 0; i < paramList.size(); i++) { if(paramList.get(i) instanceof Integer) { int paramValue = ((Integer) paramList.get(i)).intValue(); pstmt.setInt(i + 1, paramValue); } else if(paramList.get(i) instanceof Float) { float paramValue = ((Float) paramList.get(i)).floatValue(); pstmt.setFloat(i + 1, paramValue); } else if(paramList.get(i) instanceof Double) { double paramValue = ((Double) paramList.get(i)).doubleValue(); pstmt.setDouble(i + 1, paramValue); } else if(paramList.get(i) instanceof Date) { pstmt.setString(i + 1, df.format((Date)paramList.get(i))); } else if(paramList.get(i) instanceof Long) { long paramValue = ((Long)paramList.get(i)).longValue(); pstmt.setLong(i + 1, paramValue); } else if(paramList.get(i) instanceof String) { pstmt.setString(i + 1, (String)paramList.get(i)); } } return; } private static void closeConn(Connection conn) { if(conn == null) { return; } try { conn.close(); } catch(SQLException e) { logger.info(e.getMessage()); } } private static void closeStatement(Statement stmt) { if(stmt == null) { return; } try { stmt.close(); } catch(SQLException e) { logger.info(e.getMessage()); } } private static void closeResultSet(ResultSet rs) { if(rs == null) { return; } try { rs.close(); } catch(SQLException e) { logger.info(e.getMessage()); } } private static ResultSet getResultSet(PreparedStatement pstmt) throws Exception { if(pstmt == null) { return null; } ResultSet rs = pstmt.executeQuery(); return rs; } public static List<Map<String,String>> getQueryList(String sql, List<Object> paramList) throws Exception { if(sql == null || sql.trim().equals("")) { logger.info("parameter is valid!"); return null; } Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; List<Map<String,String>> queryList = null; try { conn = DbUtils.getConnection(); pstmt = DbUtils.getPreparedStatement(conn, sql); setPreparedStatementParam(pstmt, paramList); if(pstmt == null) { return null; } rs = DbUtils.getResultSet(pstmt); queryList = DbUtils.getQueryList(rs); } catch(Exception e) { logger.info(e.getMessage()); throw new Exception(); } finally { closeResultSet(rs); closeStatement(pstmt); closeConn(conn); } return queryList; } private static List<Map<String,String>> getQueryList(ResultSet rs) throws Exception { if(rs == null) { return null; } ResultSetMetaData rsMetaData = rs.getMetaData(); int columnCount = rsMetaData.getColumnCount(); List<Map<String,String>> dataList = new ArrayList<Map<String,String>>(); while(rs.next()) { Map<String,String> dataMap = new HashMap<String,String>(); for(int i = 0; i < columnCount; i++) { dataMap.put(rsMetaData.getColumnName(i+1), rs.getString(i+1)); } dataList.add(dataMap); } return dataList; } } |
不过我认为这种方法虽然封装性比较好，也比较好管理，但是当出现异常时，对于错误的查找非常的麻烦，所以我个人很少使用这样的方法，不过如果这样的数据库麻烦，那么就用Hibernate框架吧(如果你的数据库够强大的话)。
最新内容请见作者的GitHub页：http://qaseven.github.io/