封装数据库连接、关闭方法和封装数据库增、删、改、查方法,实现Java项目连接数据库,实现基础的JDBC操作。
创建jdbc_test数据库,在此数据库中创建student表,设置id属性、name属性、age属性,分别存放学生id信息、名称和年龄,id属性设置为int类型,为自增长,name属性设置为varhcar(25),age属性设置为int类型。
jdbc_test
student
id
name
age
int
varhcar(25)
将druid-1.1.10.jar和mysql-connector-java-5.1.48-bin.jar导入项目中
druid-1.1.10.jar
mysql-connector-java-5.1.48-bin.jar
driverClassName = com.mysql.jdbc.Driver url = jdbc:mysql://localhost:3306/jdbc_test username = root password = root maxActive = 20 maxWait = 10000
JDBCUtils.class主要应用于数据库的连接、关闭,方便相关增删改操作的调用,提高代码的复用性。
JDBCUtils.class
public class JDBCUtils { //2.创建DateSource实现类对象(推荐懒汉式) private static DataSource ds; //1.私有化构造器,不让外界通过新建构造器进行访问 private JDBCUtils() {} //3.给DataSource进行初始化 static { try { Properties pro= new Properties(); pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties")); ds = DruidDataSourceFactory.createDataSource(pro); } catch (Exception e) { throw new RuntimeException("DataSource初始化错误"); } } //4.提供获取链接对象公共的访问方式 public static Connection getConnection () throws SQLException { if (ds != null) { return ds.getConnection(); } throw new RuntimeException("DataSource初始化错误"); } //5.封装对象关闭方法 public static void close (Connection con) { if (con != null) { try { con.close(); } catch (SQLException e) { throw new RuntimeException("Connection连接对象关闭异常"); } } } //6.封装增删改连接对象的方法 public static void close (Statement st ,Connection con) { if (st != null) { try { st.close(); } catch (SQLException e) { throw new RuntimeException("StatementSQL发送对象关闭异常"); } } close(con); } public static void close (ResultSet rs ,Statement st ,Connection con) { if (rs != null) { try { rs.close(); } catch (SQLException e) { throw new RuntimeException("ResultSet结果集对象关闭异常"); } } close(st,con); } }
public class Student { private int id; private String name; private int age; public Student() { } //生成一个除id外的其他两个的构造器,id在数据库中为自增 public Student(String name, int age) { this.name = name; this.age = age; } public Student(int id, String name, int age) { this.id = id; this.name = name; this.age = age; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } //重写toString()方法,方便打印输出查看 @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + '}'; } }
public interface StudentBasicDAO { //提供添加学生对象的方法 void addStudent (Student s); //提供删除学生对象的方法 void deleteStudent (int sid); //提供修改学生对象的方法 void updateStudent(Student s); //提供查询所有学生的方法 List<Student> getAllStudent(); //提供通过指定ID查询学生的方法 Student getStudent(int sid); }
public class StudentBasicDAOImpl implements StudentBasicDAO { //重写父类接口中的增加学生的方法 @Override public void addStudent(Student s) { Connection con = null; PreparedStatement ps = null; try { //获取数据库连接对象 con = JDBCUtils.getConnection(); //添加新增的SQL语句,数据库中的id为自增故而不进行插入 String sql = "insert into student values(null, ?, ?)"; //获取SQL语句的发送对象 ps = con.prepareStatement(sql); //确定?问号处的数据 ps.setString(1, s.getName()); ps.setInt(2, s.getAge()); //发送SQL语句 ps.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(); }finally { JDBCUtils.close(ps , con); } } //重写接口中删除学生的方法 @Override public void deleteStudent(int sid) { Connection con = null; PreparedStatement ps = null; try { con = JDBCUtils.getConnection(); String sql = "delete from student where id = ?"; ps = con.prepareStatement(sql); ps.setInt(1, sid); ps.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(); }finally { JDBCUtils.close(ps , con); } } //重写接口中修改学生信息方法 @Override public void updateStudent(Student s) { Connection con = null; PreparedStatement ps = null; try { con = JDBCUtils.getConnection(); String sql = "update student set name = ? ,age = ? where id = ?"; ps = con.prepareStatement(sql); ps.setString(1, s.getName()); ps.setInt(2, s.getAge()); ps.setInt(3, s.getId()); ps.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(); }finally { JDBCUtils.close(ps , con); } } @Override public List<Student> getAllStudent() { Connection con = null; PreparedStatement ps = null; ArrayList<Student> list = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "select * from student"; list = new ArrayList<>(); ps = con.prepareStatement(sql); rs = ps.executeQuery(); while(rs.next()){ int id = rs.getInt(1); String name = rs.getString(2); int age = rs.getInt(3); Student stu = new Student(id, name, age); list.add(stu); } } catch (SQLException e) { throw new RuntimeException(); }finally { JDBCUtils.close(rs, ps, con); } //如果没有异常将返回student对象的集合 return list; } //重写接口中获取指定id号学生记录信息 @Override public Student getStudent(int sid) { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; Student s = null; try { //获取数据库连接 con = JDBCUtils.getConnection(); //获取sql语句 String sql = "select * from student where id = ?"; //获取发送对象 ps = con.prepareStatement(sql); //处理?处信息 ps.setInt(1, sid); //发送sql语句信息 rs = ps.executeQuery(); //解析结果集 //游标需要移动才能获取经过的值 rs.next(); int id = rs.getInt(1); String name = rs.getString(2); int age = rs.getInt(3); s = new Student(id, name, age); } catch (SQLException e) { throw new RuntimeException(); }finall{ //调用工具类方法,关闭与数据库的连接 JDBCUtils.close(rs, ps, con); } return s; } }
public class JDBCDemo { //为了使静态方法中可以调用dao,添加static关键字 static StudentBasicDAOImpl dao = new StudentBasicDAOImpl(); public static void main(String[] args) throws Exception { //Connection con = JDBCUtils.getConnection(); //System.out.println(con); //往数据库中添加sudent对象 addStudent(); //删除id为10的学生记录 deleteStudent(); //修改id号为12的学生信息,name修改为海绵宝宝,age为12 updateStudent(); //获取所有的学生对象 getAllStudent(); //获取指定学生对象 getStudent(); } //增加学生记录 private static void addStudent() { dao.addStudent(new Student("派大星",4)); dao.addStudent(new Student("海绵宝宝",4)); dao.addStudent(new Student("章鱼哥",4)); } //删除学生记录 private static void deleteStudent(){ //删除id号为4的学生记录 dao.deleteStudent(4); } //修改指定学生记录的信息 private static void updateStudent(){ Student s = dao.getStudent(1); s.setAge(22); dao.updateStudent(s); } //获取指定学生对象的方法 private static void getStudent() { Student student = dao.getStudent(3); System.out.println("id号为3的学生信息如下:"); System.out.println(student); } //获取所有学生对象的方法 private static void getAllStudent() { List<Student> allStudent = dao.getAllStudent(); System.out.println("所有学生信息如下:"); //直接输出 System.out.println(allStudent); //遍历输出 ListIterator<Student> sli = allStudent.listIterator(); while (sli.hasNext()){ System.out.println(sli.next()); } } }
原文链接:https://www.cnblogs.com/iris-/p/13544079.html