这是一个很普通的单列jdbc连接
public class DBHelper { private static String Driver; private static String Url; private static String UserName; private static String UserPwd; static { try { ClassLoader classLoader = DBHelper.class.getClassLoader(); InputStream is = classLoader.getResourceAsStream("db.properties"); Properties proprs = new Properties(); proprs.load(is); Driver = proprs.getProperty("driver"); Url = proprs.getProperty("url"); UserName = proprs.getProperty("username"); UserPwd = proprs.getProperty("password"); } catch (IOException e) { e.printStackTrace(); } } public static Connection getConnection(){ Connection conn = null; try { Class.forName(Driver); conn = DriverManager.getConnection(Url, UserName, UserPwd); } catch (Exception e) { e.printStackTrace(); } return conn; } }
这样dao层有大量重复的：
getConnection()
prepareStatement
public class ITb_UserImpl implements ITb_User { public Connection conn = null; public ResultSet rs = null; public PreparedStatement ps = null; //查询所有数据 @Override public List<Tb_User> queryAllDate() { List<Tb_User> lists = new ArrayList<Tb_User>(); conn = DBHelper.getConnection(); String sql = "select * from buser"; try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()) { Tb_User user =new Tb_User(); user.setId(rs.getInt("userid")); user.setUserName(rs.getString("username")); user.setUserPwd(rs.getString("userpwd")); user.setUserSex(rs.getBoolean("usersex")); user.setUserpPhone(rs.getString("userphone")); user.setBirhday(rs.getDate("birthday")); lists.add(user); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { DBHelper.release(conn,ps,rs); } return lists; } @Override //添加 public int insertData(Tb_User t) { conn = DBHelper.getConnection(); String sql = "INSERT INTO `buser` (`username`,`userpwd`,`usersex`,`userphone`,`birthday`) VALUE (?,?,?,?,?); "; int i = 0; try { ps = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS); ps.setString(1,t.getUserName()); ps.setString(2,t.getUserPwd()); ps.setBoolean(3,t.isUserSex()); ps.setString(4,t.getUserpPhone()); ps.setDate(5,new java.sql.Date(t.getBirhday().getTime())); ps.executeUpdate(); rs = ps.getGeneratedKeys(); if (rs.next()) { i = rs.getInt(1); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { DBHelper.release(conn,ps,rs); } return i; } @Override //修改 public int update(Tb_User t) { conn = DBHelper.getConnection(); int i = 0; String sql = "update buser set username=?,userpwd=?,usersex=?,userphone=? where userid=? "; try { ps = conn.prepareStatement(sql); ps.setString(1,t.getUserName()); ps.setString(2,t.getUserPwd()); ps.setBoolean(3,t.isUserSex()); ps.setString(4,t.getUserpPhone()); ps.setInt(5,t.getId()); i = ps.executeUpdate(); } catch (SQLException throwables) { throwables.printStackTrace(); } return i; } @Override //删除 public int delete(int id) { conn = DBHelper.getConnection(); int i = 0; String sql = "delete from buser where userid = ?"; try { ps = conn.prepareStatement(sql); ps.setInt(1,id); i = ps.executeUpdate(); if (i == 1) { return i; } } catch (SQLException throwables) { throwables.printStackTrace(); } return i; } @Override public Tb_User queryDataById(int id) { conn = DBHelper.getConnection(); String sql = "select * from buser where userid = ?"; Tb_User user = null; if (id >0) { try { ps = conn.prepareStatement(sql); ps.setInt(1, id); rs = ps.executeQuery(); if (rs.next()) { user = new Tb_User(); user.setId(rs.getInt("userid")); user.setUserName(rs.getString("username")); user.setUserPwd(rs.getString("userpwd")); user.setUserSex(rs.getBoolean("usersex")); user.setUserpPhone(rs.getString("userphone")); user.setBirhday(rs.getDate("birthday")); } } catch (SQLException throwables) { throwables.printStackTrace(); } } return user; } }
我们把增删改和查的操作封装成一个方法
public class DBHelper { private static String Driver; private static String Url; private static String UserName; private static String UserPwd; //静态代码块加载配置文件信息 static { try { //1.通过当前类获取类加载器 ClassLoader classLoader = DBHelper.class.getClassLoader(); //2.通过类加载器的方法获得一个输入流 InputStream is = classLoader.getResourceAsStream("db.properties"); //3.创建一个properties对象 Properties proprs = new Properties(); //4.加载输入流 proprs.load(is); //5.获取相关参数的值 Driver = proprs.getProperty("driver"); Url = proprs.getProperty("url"); UserName = proprs.getProperty("username"); UserPwd = proprs.getProperty("password"); } catch (IOException e) { e.printStackTrace(); } } //获取连接方法 public static Connection getConnection(){ Connection conn = null; try { Class.forName(Driver); conn = DriverManager.getConnection(Url, UserName, UserPwd); } catch (Exception e) { e.printStackTrace(); } return conn; } /** * @param sql sql语句（增删改） */ public static int excuteUpdate(String sql,Object...obj) { int row=0; Connection con=DBHelper.getConnection(); PreparedStatement pst=null; try { pst=con.prepareStatement(sql); if(obj!=null&&obj.length>0) { for(int i=0;i<obj.length;i++) { pst.setObject(i+1, obj[i]); } } row=pst.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally { dbClose(con, pst); } return row; } /** * @param sql sql查询语句 */ //查询用的方法 public static List<Map<String,Object>> executeQuery(String sql,Object...obj){ List<Map<String,Object>> list=new ArrayList<Map<String,Object>>(); Connection con=DBHelper.getConnection(); PreparedStatement pst=null; ResultSet rs=null; try { pst=con.prepareStatement(sql); if(obj!=null&&obj.length>0) { for(int i=0;i<obj.length;i++) { pst.setObject(i+1, obj[i]); } } rs=pst.executeQuery(); //把结果集转换为内存中一张虚拟表 ResultSetMetaData rsmd=rs.getMetaData(); //获取表中的列数 int colCount=rsmd.getColumnCount(); if(rs!=null) { while(rs.next()) { Map<String,Object> mso=new HashMap<String,Object>(); for(int i=1;i<=colCount;i++) { //rsmd.getColumnName(i);获取每列的列名 mso.put(rsmd.getColumnName(i), rs.getObject(i)); } list.add(mso); } } } catch (Exception e) { e.getStackTrace(); }finally { dbClose(con, pst, rs); } return list; } //释放资源方法 public static void dbClose(Connection conn, PreparedStatement pstmt){ if (pstmt != null){ try { pstmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } //释放资源方法 public static void dbClose(Connection conn, PreparedStatement pstmt, ResultSet rs){ if (rs != null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (pstmt != null){ try { pstmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
现在dao层就简洁很多
public class UserDaoImpl implements UserDao { @Override public long bookReadCount() { long count = 0; String sql = "select count(*) as count from s_user"; List<Map<String, Object>> lm = DBHelper.executeQuery(sql); if (lm.size() > 0) { count = (long) lm.get(0).get("count"); } return count; } @Override public List<User> userList(PageBean pageBean) { List<User> lu=new ArrayList<>(); List<Map<String, Object>> list=new ArrayList<Map<String,Object>>(); String sql="select * from s_user limit ?,?"; list=DBHelper.executeQuery(sql,(pageBean.getCurPage()-1)*pageBean.getMaxSize(),pageBean.getMaxSize()); if(list.size()>0) { for(Map<String,Object> map:list) { User u=new User(map); lu.add(u); } } return lu; } @Override public boolean findUser(String userName) { String sql="select * from s_user where userName=?"; List<Map<String,Object>> list=DBHelper.executeQuery(sql, userName); return list.size()>0?true:false; } @Override public User findUser(Integer id) { String sql="select * from s_user where userId=?"; User u=null; List<Map<String,Object>> list=DBHelper.executeQuery(sql, id); if(list.size()>0) { u=new User(list.get(0)); } return u; } @Override public boolean userAdd(User user) { String sql = "insert into s_user(userName,userPassWord,name,sex,age,tell,address,enabled) values(?,?,?,?,?,?,?,?)"; int i = DBHelper.excuteUpdate(sql, user.getUserName(), user.getUserPassWord(), user.getName(), user.getSex(), user.getAge() , user.getTell(), user.getAddress(), user.getEnabled()); return i>0?true:false; } @Override public boolean userUpdate(User user) { String sql="update s_user set userPassWord=?,name=?,sex=?,age=?,tell=?,address=?,enabled=? where userId =?"; int i=DBHelper.excuteUpdate(sql,user.getUserPassWord(),user.getName(),user.getSex(),user.getAge() ,user.getTell(),user.getAddress(),user.getEnabled(),user.getUserId()); return i>0?true:false; } @Override public boolean delUser(int id) { String sql = "delete from s_user where userid =?"; int i = DBHelper.excuteUpdate(sql, id); return i>0?true:false; } @Override public boolean batDelUser(String id) { String sql="delete from s_user where userId in ("+id+")"; int i=DBHelper.excuteUpdate(sql); return i>0?true:false; } @Override public User userLogin(User user) { User user1=null; String sql="select * from s_user where userName=? and userPassWord=?"; List<Map<String, Object>> list =DBHelper.executeQuery(sql, user.getUserName(),user.getUserPassWord()); if(list.size()>0) { Map<String, Object> map = list.get(0); user1=new User(map); } return user1; } }