package db; import java.sql.*; import java.util.ArrayList; import java.util.HashMap; import java.util.List;
public class DbConnection { private Connection conn = null; private static DbConnection Db=null; private String connStr = "com.mysql.jdbc.Driver"; private List<HashMap<Object,Object>> listData= new ArrayList<HashMap<Object,Object>>(); private PreparedStatement pts; private ResultSet rs; private DbConnection(){ } public static DbConnection getDbConnection(){ if(Db==null){ Db = new DbConnection(); } return Db; } public Connection getMySqlConn() throws Exception{ try { Class.forName(connStr); conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root"); } catch (Exception e) { throw new Exception("链接mysql数据库失败"); } return conn; } public void closeConn() throws Exception{ try { conn.close(); } catch (SQLException e) { throw new Exception("关闭连接失败"); } }
//封装数据进入list数据结构避免在其他地方关闭连接错误 public List<HashMap<Object,Object>> executeQuery(String sql,Object[] args) throws Exception{ try { pts = conn.prepareStatement(sql);
//设置sql语句的参数所传参数的数组长度必须和sql语句中的?好个数相等 if(args!=null){ for (int i=0;i<args.length;i++) { pts.setObject(i, args[i]); } } rs = pts.executeQuery(); while(rs.next()){ HashMap<Object,Object> map = new HashMap<Object, Object>(); ResultSetMetaData metaData = rs.getMetaData(); for(int i=0;i<metaData.getColumnCount();i++){ map.put(metaData.getColumnName(i), rs.getObject(i)); } listData.add(map); } } catch (SQLException e) { throw new Exception("查询数据失败"); }finally{ closeConn(); } return listData; } public int executeUpdate(String sql,Object[] args) throws Exception{ int count = 0; try { pts = conn.prepareStatement(sql);
//同上面的查询 if(args!=null){ for (int i=0;i<args.length;i++) { pts.setObject(i, args[i]); } } count = pts.executeUpdate(); } catch (SQLException e) { throw new Exception("更新数据失败"); }finally{ closeConn(); } return count; } }