封装数据库操作

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; 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 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; conn.close(); catch(SQLException e) logger.info(e.getMessage()); private static void closeStatement(Statement stmt) if(stmt == null) return; stmt.close(); catch(SQLException e) logger.info(e.getMessage()); private static void closeResultSet(ResultSet rs) if(rs == null) return; 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; 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/
C#之数据库操作类 平时在进行C#开发时，需要对数据库进行操作，下面介绍几种常见的操作数据库的方法： 一、操作类DataAccess using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; using DevExpress.XtraEditors; using System.Windows.Forms; //自己写的解密数据库链接dll，可