1 封装
新建类 DBConnUtil ,新建database.properties 文件储存链接信息如下所示
jdbcDriver=com.mysql.jdbc.Driver jdbcUrl=jdbc\:mysql\://localhost\:3306/homework jdbcUser=root jdbcPasswd=123
public class DBConnUtil { private static String jdbcDriver = ""; //定义连接信息 private static String jdbcUrl = ""; private static String jdbcUser = ""; private static String jdbcPasswd = ""; static{ InputStream is = null; try { is = DBConnUtil.class.getClassLoader().getResourceAsStream("database.properties"); //加载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"); } catch (IOException e) { e.printStackTrace(); } finally { if(is != null){ try { is.close(); // 关闭is } catch (IOException e) { e.printStackTrace(); } } } } public static Connection getConn(){ // 建立连接方法 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){ //关闭连接(用于增删改) if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(st != null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void closeAll(ResultSet rs ,PreparedStatement ps,Connection conn){ // 关闭连接(用于查) if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(ps != null){ try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
2 测试增删改差
2.1 插入数据
public boolean ChaRu1(User user){ boolean flag=true; Connection conn=null; Statement st=null; String sql="insert into user (name,pwd) values('"+user.getName()+"','"+user.getPwd()+"')"; conn=DBConnUtil.getConn(); // getConn()方法是静态的,直接用类调用建立连接。 try { st=conn.createStatement(); int i=st.executeUpdate(sql); if(i==0){ flag=false; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ DBConnUtil.closeAll(null, st, conn); //关闭连接,由于插入操作不涉及ResultSet类,故其对象rs无需关闭,用null代替。 } return flag; }
2.2 修改数据
public boolean XiuGai2(User user){ boolean flag=true; Connection conn =null; Statement st=null; String sql="update user set pwd='"+user.getPwd()+"' where name='"+user.getName()+"'"; conn=DBConnUtil.getConn(); try { st=conn.createStatement(); st.executeUpdate(sql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ DBConnUtil.closeAll(null, st, conn); } return flag; }
2.3 删除数据
public boolean ShanChu2(int id){ boolean flag=true; Connection conn=null; Statement st=null; String sql="delete from user where id="+id; conn=DBConnUtil.getConn(); try { st=conn.createStatement(); int i=st.executeUpdate(sql); if(i==0){ flag=false; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ DBConnUtil.closeAll(null, st, conn); } return flag; }
2.4删除数据
public List<User> ChanKan2(){ List<User> list= new ArrayList<User>(); Connection conn=null; Statement st= null; ResultSet rs=null; String sql="select * from user"; conn=DBConnUtil.getConn(); try { st=conn.createStatement(); rs=st.executeQuery(sql); while(rs.next()){ User user=new User(); user.setName(rs.getString("name")); list.add(user); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ DBConnUtil.closeAll(rs, st, conn); } return list; }