JDBC封装

封装

public class JdbcUtil {
	static {//加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//打开连接
	public static Connection openConnection() {
		String url = "jdbc:mysql://localhost:3306/lanou2?useSSL=true";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url,"root","123456");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	//关闭连接
	public static void close(ResultSet rs ,Statement stmt,Connection conn) {
		try {
			if(rs != null) {
				rs.close();
			}
			if(stmt != null) {
				stmt.close();
			}
			if(conn != null) {
				conn.close();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

调用：

@Override
	public List<Student> find(Student student) {
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    //存储返回值
	    List<Student> list = new ArrayList<>();
	    //存储参数
	    List<Object> pList = new ArrayList<>();
	    try {
	      conn = JdbcUtil.openConnection();
	      //动态sql拼接
	      StringBuffer sql = new StringBuffer("select * from student where 1=1 ");
	      
	      if(student != null) {
	    	  if(student.getSno() != null && !student.getSno().trim().equals("")) {
	    		  sql.append(" and sno = ? ");
	    		  pList.add(student.getSno());
	    	  }
	    	  if(student.getSname() != null  && !student.getSname().trim().equals("")) {
	    		  sql.append(" and sname like ? ");
	    		  pList.add("%"+student.getSname()+"%");
	    	  }
	    	  if(student.getSsex() != null  && !student.getSsex().trim().equals("")) {
	    		  sql.append(" and ssex = ? ");
	    		  pList.add(student.getSsex());
	    	  }
	    	  if(student.getSclass() != null  && !student.getSclass().trim().equals("")) {
	    		  sql.append(" and class = ? ");
	    		  pList.add(student.getSclass());
	    	  }
	      }
      
	      stmt = conn.prepareStatement(sql.toString());
	      //赋值
	      int i = 1;
	      for (Object object : pList) {
	        stmt.setObject(i++, object);
	      }
	      rs = stmt.executeQuery();
	      while(rs.next()) {
	        Student s = new Student(rs.getString("sno"), 
	            rs.getString("sname"), 
	            rs.getString("ssex"), 
	            rs.getTimestamp("sbirthday"), 
	            rs.getString("class"));
	        list.add(s);
	      }
	    }catch(Exception e) {
	      e.printStackTrace();
	    }finally {
	      JdbcUtil.close(rs, stmt, conn);
	    }
	    return list;
	}