jdbc操作数据库插入、更新、删除、查找

mysql数据库： public boolean insertFrom(Person per){      boolean flag = false;      Connection conn = null;      conn = JdbcCon.jdbcConMysql();      PreparedStatement ps = null;      String sql = "insert into person(name,birthday,sex) values(?,?,?)";      try { ps = conn.prepareStatement(sql); ps.setString(1, per.getName()); ps.setDate(2, per.getBirthday()); ps.setInt(3, per.getSex()); int i = ps.executeUpdate(); if(i != 0){ flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { JdbcCon.closeAll(null, ps, conn); }      return flag;     }     public boolean updateFrom(int id,int sex,String name){      boolean flag = false;      Connection conn = null;      conn = JdbcCon.jdbcConMysql();      String sql = "update person set sex=?,name=? where id=?";      PreparedStatement ps = null;      try { ps = conn.prepareStatement(sql); ps.setInt(1, sex); ps.setString(2, name); ps.setInt(3, id); int i = ps.executeUpdate(); if(i != 0){ flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { JdbcCon.closeAll(null, ps, conn); }      return flag;     }     public boolean deleteFrom(int id){      boolean flag = false;      Connection conn = null;      conn = JdbcCon.jdbcConMysql();      PreparedStatement ps = null;      String sql= "delete from person where id=?";      try { ps = conn.prepareStatement(sql); ps.setInt(1, id); int i = ps.executeUpdate(); if(i != 0){ flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { JdbcCon.closeAll(null, ps, conn); }      return flag;     }     public Person selectFrom(String name,int id){      Person per = null;      Connection conn = null;      conn = JdbcCon.jdbcConMysql();      String sql = "select * from person where id=? and name=?";      PreparedStatement ps = null;      ResultSet rs = null;      try { ps = conn.prepareStatement(sql); ps.setInt(1, id); ps.setString(2, name); rs = ps.executeQuery(); while(rs.next()){ per = new Person(); per.setBirthday(rs.getDate("birthday")); per.setId(rs.getInt("id")); per.setName(rs.getString("name")); per.setSex(rs.getInt("sex")); } } catch (SQLException e) { e.printStackTrace(); } finally { JdbcCon.closeAll(rs, ps, conn); } return per; } Oracle数据库： public boolean insertStu(Stu stu){ boolean flag = false; Connection conn = null; Statement st = null; StringBuilder sql = new StringBuilder(); sql.append("insert into stu(id,stu_num,stu_name) values(seq_stu_id.nextval,'"); sql.append(stu.getStu_num()); sql.append("','"); sql.append(stu.getStu_name()); sql.append("')"); conn = DBConn.getConn(); try { st = conn.createStatement(); int i = st.executeUpdate(sql.toString()); if(i != 0){ flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { DBConn.closeAll(null, st, conn); } return flag; } public static void main(String[] args) { Stu stu = new Stu(); stu.setStu_name("aaabbbbccc"); stu.setStu_num("6543211"); StuDao dao = new StuDao(); System.out.println(dao.insertStu(stu)); } 封装 public class JdbcCon { private static String jdbcDriver = ""; private static String jdbcUrl = ""; private static String jdbcUser = ""; private static String jdbcpasswd = ""; static { InputStream is = null; try { is = JdbcCon.class.getClassLoader().getResourceAsStream("database.properties"); Properties p = new Properties(); p.load(is); jdbcDriver = p.getProperty("jdbcDriver"); jdbcUrl = p.getProperty("jdbcUrl"); jdbcUser = p.getProperty("jdbcUser"); jdbcpasswd = p.getProperty("jdbcpasswd"); // System.out.println(p.getProperty("jdbcDriver")); // System.out.println(p.getProperty("jdbcUrl")); // System.out.println(p.getProperty("jdbcUser")); // System.out.println(p.getProperty("jdbcpasswd")); } catch (IOException e) { e.printStackTrace(); } finally { try { if(is != null){ is.close(); } } catch (IOException e) { e.printStackTrace(); } } }     public static Connection jdbcConMysql(){      Connection conn = null;      try { Class.forName(jdbcDriver); conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcpasswd); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn;     }     public static void closeAll(ResultSet rs,Statement st,Connection conn){      try { if(rs != null){ rs.close(); } } catch (SQLException e) { e.printStackTrace(); }      try { if(st != null){ st.close(); } } catch (SQLException e) { e.printStackTrace(); }      try { if(conn != null){ conn.close(); } } catch (SQLException e) { e.printStackTrace(); }     }     public static void closeAll(ResultSet rs,PreparedStatement ps,Connection conn){      try { if(rs != null){ rs.close(); } } catch (SQLException e) { e.printStackTrace(); }      try { if(ps != null){ ps.close(); } } catch (SQLException e) { e.printStackTrace(); }      try { if(conn != null){ conn.close(); } } catch (SQLException e) { e.printStackTrace(); }     } } properties写在src目录下，JdbcCon.class.getClassLoader().getResourceAsStream()默认在src目录下找properties文件 mysql数据库链接参数： #以下是mysql数据库的配置信息 #jdbcDriver=com.mysql.jdbc.Driver #jdbcUrl=jdbc:mysql://localhost:3306/change #jdbcUser=root #jdbcPasswd=476645144 oracle数据库链接参数： #以下是oracle数据库的配置信息 #jdbcDriver=oracle.jdbc.driver.OracleDriver #jdbcUrl=jdbc:oracle:thin:@localhost:1521:ORCL #jdbcUser=scott #jdbcPasswd=tiger 批处理四种方式比较。 public boolean regUser(User user){ boolean flag = false; Connection conn = null; PreparedStatement ps = null; String sql = "insert into user(email,passwd,nickname,sex) values(?,?,?,?)"; conn = DBConn.getConn(); try { ps = conn.prepareStatement(sql); ps.setString(1, user.getEmail()); ps.setString(2, user.getPasswd()); ps.setString(3, user.getNickname()); ps.setInt(4, user.getSex()); int i = ps.executeUpdate(); if(i != 0){ flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { DBConn.closeAll(null, ps, conn); } return flag; } public boolean insertUsersByCom(List<User> list){ boolean flag = true; Connection conn = null; Statement st = null; conn = DBConn.getConn(); try { st = conn.createStatement(); for (User user : list) { StringBuilder sql = new StringBuilder(); sql.append("insert into user(email,passwd,sex,nickname) values('"); sql.append(user.getEmail()); sql.append("','"); sql.append(user.getPasswd()); sql.append("',"); sql.append(user.getSex()); sql.append(",'"); sql.append(user.getNickname()); sql.append("')"); System.out.println(sql.toString()); int i = st.executeUpdate(sql.toString()); } } catch (SQLException e) { e.printStackTrace(); } finally { DBConn.closeAll(null, st, conn); } return flag; } public boolean insertUsersByPre(List<User> list){ boolean flag = true; Connection conn = null; PreparedStatement ps = null; String sql = "insert into user(email,passwd,nickname,sex) values(?,?,?,?)"; conn = DBConn.getConn(); try { ps = conn.prepareStatement(sql); for (User user : list) { ps.setString(1, user.getEmail()); ps.setString(2, user.getPasswd()); ps.setString(3, user.getNickname()); ps.setInt(4, user.getSex()); int i = ps.executeUpdate(); } } catch (SQLException e) { e.printStackTrace(); } finally { DBConn.closeAll(null, ps, conn); } return flag; } public boolean insertUsersByStandard(List<User> list){ boolean flag = true; Connection conn = null; PreparedStatement ps = null; String sql = "insert into user(email,passwd,nickname,sex) values(?,?,?,?)"; conn = DBConn.getConn(); try { ps = conn.prepareStatement(sql); for (User user : list) { ps.setString(1, user.getEmail()); ps.setString(2, user.getPasswd()); ps.setString(3, user.getNickname()); ps.setInt(4, user.getSex()); //真正的批处理 在这里并不执行 而是将当前的这条命令 加入到批处理的队列中 ps.addBatch(); } ps.executeBatch();//执行批处理命令 } catch (SQLException e) { e.printStackTrace(); } finally { DBConn.closeAll(null, ps, conn); } return flag; } public static void main(String[] args) { List<User> list = new ArrayList<User>(); for (int i = 0; i < 10; i++) { User user = new User(); user.setEmail("xiaowang" + i + "@126.com"); user.setNickname("wang" + i); user.setPasswd("8787878"); user.setSex(0); list.add(user); } // UserDao dao = new UserDao(); //第一种  调用regUser(User user)插入10条记录  获取连接和statement各10次  sql语句编译和执行各10次  关闭10次 // for (User user : list) { // dao.regUser(user); // } //第二种 调用insertUsersByCom(List<User> list)  获取连接和statement各一次 sql语句编译和执行各10次 关闭一次 // dao.insertUsersByCom(list); //第三种 调用insertUsersByPre(List<User> list)  获取连接和statement各一次 sql语句编译一次  执行10次 关闭一次 // dao.insertUsersByPre(list); //第四种 调用insertUsersByStandard(List<User> list) 获取连接和statment sql语句编译执行 关闭都仅有一次 UserDao.getInstance().insertUsersByStandard(list); } 数据库的事务，把conn的自动提交模式改为手动提交，当两条sql语句都成功完成时才提交，否则都回滚。 public boolean transferMoney(String from,String to,double money){ boolean flag = false; String sql1 = "update bank set money=money-? where bankcard=?"; String sql2 = "update bank set money=money+? where bankcard=?"; Connection conn = null; PreparedStatement ps = null;- conn = DBConn.getConn(); try {  conn.setAutoCommit(false);//将自动提交模式改为手动提交(autoCommit - 为 true 表示启用自动提交模式；为 false 表示禁用自动提交模式) ps = conn.prepareStatement(sql1); ps.setDouble(1, money); ps.setString(2, from); int i1 = ps.executeUpdate(); ps = conn.prepareStatement(sql2); ps.setDouble(1, money); ps.setString(2, to); int i2 = ps.executeUpdate(); if(i1 != 0 && i2 != 0){ flag = true; conn.commit();//手动提交 }else{ conn.rollback();//回滚 } } catch (SQLException e) { e.printStackTrace(); try { conn.rollback();//发生一场也回滚 } catch (SQLException e1) { e1.printStackTrace(); } } finally { DBConn.closeAll(null, ps, conn); } return flag; } 单例模式    只能new一次，节省资源省开销，通常在dao包中使用 private NewsDao(){ } private static NewsDao dao = null; public static NewsDao getInstance(){ if(dao == null){ dao = new NewsDao(); } return dao; } /** * @param value:指的是用户输入的邮箱或者是昵称的信息 * @param type :指的是该次验证的字段是哪个字段  如果0代表验证的邮箱 1代表验证的昵称 * @return * @function:验证用户输入的email或者是昵称是否被占用 */ public static boolean isOnly(String value,int type){ boolean flag = true; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; StringBuilder sql = new StringBuilder(); sql.append("select id from user where "); sql.append(type==0?" email=? ":" nickname=?"); System.out.println(sql.toString()); conn = DBConn.getConn(); try { ps = conn.prepareStatement(sql.toString()); ps.setString(1, value); rs = ps.executeQuery(); while (rs.next()) { flag = false; } } catch (SQLException e) { e.printStackTrace(); } finally { DBConn.closeAll(rs, ps, conn); } return flag; } /** * @return * @function:分页查询最新注册用户的信息  */ public List<User> getUsersByPag(int startRow,int size){ List<User> list = new ArrayList<User>(); Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; String sql = "select * from user order by id desc limit ?,?"; User user = null; conn = DBConn.getConn(); try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()) { user = new User(); user.setEmail(rs.getString("email")); user.setAddtime(rs.getDate("addtime")); user.setId(rs.getInt("id")); user.setNickname(rs.getString("nickname")); user.setPasswd(rs.getString("passwd")); user.setSex(rs.getInt("sex")); list.add(user); } } catch (SQLException e) { e.printStackTrace(); } finally { DBConn.closeAll(rs, ps, conn); } return list; }