利用JDBC操作MySql数据库 Dao模式

一、DAO模式的介绍和意义

  • 什么是DAO?

DAO即Data Access Object, 是用来访问数据信息的类. 包含了对数据的 CRUD(即create,read,update,delete) 操作. 而不包含任何业务相关的信息.

DAO其实就是对一些常用的 JDBC 方法的封装(就像之前写的JDBC工具类, 也有不同的地方).

  • DAO的意义?

为了实现代码功能的模块化, 更有利于代码的维护与升级.

  • DAO要实现哪些数据相关操作的方法?

若是要处理一些事务的操作, 下列方法还应该在最前面添加一个数据库连接Connection参数

二、DAO模式的JDBC程序设计

本程序涉及的类有四个

  • 演示类：StudentDemo

  • 学生类：Student

  • 学生DAO类：StudentDAO

  • 数据库工具类：JDBCUtils  

文件结构如图：
 

1.JDBCUtils 类

该类是数据库访问工具类，主要提供了创建连接对象和释放资源的静态方法,代码如下：
 1 package utils;
 2 import java.sql.*;
 3 
 4 /**该类是数据库访问工具类，
 5 主要提供了创建连接对象和释放资源的静态方法
 6  * @author 陈杰*/
 7 public class JDBCUtils {
 8     public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
 9     public static final String URL = "jdbc:mysql://localhost:3306/exp08?characterEncoding=utf8&useSSL=false&serverTimezone=PRC";
10     public static final String USER = "root";
11     public static final String PASSWORD = "111111";
12 
13     /**
14      * 加载驱动
15      */
16     public static Connection getConnection() throws SQLException, ClassNotFoundException {
17         /* 注册驱动 */
18         Class.forName(DRIVER);
19         /* 建立连接 */
20         return DriverManager.getConnection(URL, USER, PASSWORD);
21     }
22 
23     public static void release(Statement stmt, Connection conn) {
24         if (stmt != null) {
25             try {
26                 stmt.close();
27             } catch (Exception e) {
28                 e.printStackTrace();
29             } finally {
30                 stmt = null;
31             }
32         }
33         if (conn != null) {
34             try {
35                 conn.close();
36             } catch (Exception e) {
37                 e.printStackTrace();
38             } finally {
39                 conn = null;
40             }
41         }
42     }
43 
44     public static void release(ResultSet rs, Statement stmt, Connection conn) {
45         if (rs != null) {
46             try {
47                 rs.close();
48             } catch (Exception e) {
49                 e.printStackTrace();
50             } finally {
51                 rs = null;
52             }
53         }
54         release(stmt, conn);
55     }
56 }
View Code
 
接下来就是咱们的主角了 StudentDao类
 

2.StudentDAO 类

该类是从面向对象角度对Student进行插入、删除、编辑、查询所有学生和检索指定学号学生操作的封装。其主要代码如下：
  1 package dao;
  2 
  3 import utils.JDBCUtils;
  4 import vo.Student;
  5 
  6 import java.sql.*;
  7 import java.util.ArrayList;
  8 
  9 /**
 10  * @author 陈杰
 11  */
 12 public class StudentDao {
 13 
 14     /**
 15      * 将Student类型对象student保存到数据库中，并返回操作是否正常
 16      */
 17     public static boolean insert(Student student) {
 18         Connection conn = null;
 19         PreparedStatement stmt = null;
 20         try {
 21             conn = JDBCUtils.getConnection();
 22             String sql = "insert into student values (?,?,?,?,?,?);";
 23             stmt = conn.prepareStatement(sql);
 24             stmt.setInt(1, student.getNum());
 25             stmt.setString(2, student.getName());
 26             stmt.setString(3, student.getSex());
 27             stmt.setInt(4, student.getBirthday());
 28             stmt.setString(5, student.getDept());
 29             stmt.setString(6, student.getAddress());
 30             int i = stmt.executeUpdate();
 31             return i == 1;
 32         } catch (SQLException | ClassNotFoundException e) {
 33             e.printStackTrace();
 34         } finally {
 35             JDBCUtils.release(stmt, conn);
 36         }
 37         return false;
 38     }
 39 
 40     /**
 41      * 从数据库中删除指定学号num的学生,并返回操作是否正常
 42      */
 43     public static boolean delete(int num) {
 44         Connection conn = null;
 45         PreparedStatement stmt = null;
 46         try {
 47             conn = JDBCUtils.getConnection();
 48             String sql = "delete from student where num=?";
 49             stmt = conn.prepareStatement(sql);
 50             stmt.setInt(1, num);
 51             int i = stmt.executeUpdate();
 52             return i == 1;
 53 
 54         } catch (SQLException | ClassNotFoundException e) {
 55             e.printStackTrace();
 56         } finally {
 57             JDBCUtils.release(stmt, conn);
 58         }
 59         return false;
 60     }
 61 
 62     /**
 63      * 从数据库中编辑指定学号num的学生,并返回操作是否正常
 64      */
 65     public static boolean edit(Student student) {
 66         Connection conn = null;
 67         PreparedStatement stmt = null;
 68         int i;
 69         try {
 70             conn = JDBCUtils.getConnection();
 71             String sql = "update student SET name=?,sex=?,birthday=?,dept=?,address=? where num=?;";
 72             stmt = conn.prepareStatement(sql);
 73             stmt.setString(1, student.getName());
 74             stmt.setString(2, student.getSex());
 75             stmt.setInt(3, student.getBirthday());
 76             stmt.setString(4, student.getDept());
 77             stmt.setString(5, student.getAddress());
 78             stmt.setInt(6,student.getNum());
 79             i=stmt.executeUpdate();
 80             return i == 1;
 81         } catch (SQLException | ClassNotFoundException e) {
 82             e.printStackTrace();
 83         } finally {
 84             JDBCUtils.release(stmt, conn);
 85         }
 86         return false;
 87     }
 88 
 89 
 90     /**
 91      * 从数据库中获取所有学生，将学生以Student形式封装，加入ArrayList中
 92      */
 93     public static ArrayList<Student> getAllStudents() {
 94         ArrayList<Student> studentList = new ArrayList<>();
 95         Connection conn = null;
 96         PreparedStatement stmt = null;
 97         ResultSet rs = null;
 98         try {
 99             conn = JDBCUtils.getConnection();
100             String sql = "select * from student;";
101             stmt = conn.prepareStatement(sql);
102             rs = stmt.executeQuery();
103             while (rs.next()) {
104                 int num = rs.getInt("num");
105                 String name = rs.getString("name");
106                 String sex = rs.getString("sex");
107                 int birthday = rs.getInt("birthday");
108                 String dept = rs.getString("dept");
109                 String address = rs.getString("address");
110                 Student student = new Student(num, name, sex, birthday, dept, address);
111                 studentList.add(student);
112             }
113         } catch (SQLException | ClassNotFoundException throwable) {
114             throwable.printStackTrace();
115         } finally {
116             JDBCUtils.release(rs, stmt, conn);
117         }
118         return studentList;
119     }
120 
121     /**
122      * 从数据库中查询学号num的学生,并以student对象形式返回
123      */
124     public static Student getStudentByNum(int num) {
125         Connection conn = null;
126         PreparedStatement stmt = null;
127         ResultSet rs = null;
128         Student student;
129         try {
130             conn = JDBCUtils.getConnection();
131             String sql = "select num, name, sex, birthday, dept, address from student where num =?;";
132             stmt =conn.prepareStatement(sql);
133             stmt.setInt(1, num);
134             rs=stmt.executeQuery();
135             student=new Student();
136             while (rs.next()){
137                 student.setNum(rs.getInt(1));
138                 student.setName(rs.getString(2));
139                 student.setSex(rs.getString(3));
140                 student.setBirthday(rs.getInt(4));
141                 student.setDept(rs.getString(5));
142                 student.setAddress(rs.getString(6));
143            }
144             return student;
145         }catch (SQLException | ClassNotFoundException e) {
146             e.printStackTrace();
147         } finally {
148             JDBCUtils.release(rs,stmt, conn);
149         }
150         return null;
151     }
152 }
View Code

3.Student 类

 1 package vo;
 2 
 3 public class Student {
 4     int num;
 5     String name;
 6     String sex;
 7     int birthday;
 8     String dept;
 9     String address;
10  public Student() {}
11 
12     public Student(int num, String name, String sex, int birthday, String dept, String address) {
13         this.num = num;
14         this.name = name;
15         this.sex = sex;
16         this.birthday = birthday;
17         this.dept = dept;
18         this.address = address;
19     }
20 
21     public int getNum() {
22         return num;
23     }
24 
25     public void setNum(int num) {
26         this.num = num;
27     }
28 
29     public String getName() {
30         return name;
31     }
32 
33     public void setName(String name) {
34         this.name = name;
35     }
36 
37     public String getSex() {
38         return sex;
39     }
40 
41     public void setSex(String sex) {
42         this.sex = sex;
43     }
44 
45     public int getBirthday() {
46         return birthday;
47     }
48 
49     public void setBirthday(int birthday) {
50         this.birthday = birthday;
51     }
52 
53     public String getDept() {
54         return dept;
55     }
56 
57     public void setDept(String dept) {
58         this.dept = dept;
59     }
60 
61     public String getAddress() {
62         return address;
63     }
64 
65     public void setAddress(String address) {
66         this.address = address;
67     }
68 
69     @Override
70     public String toString() {
71         return "Student{" +
72                 "num='"+num+'\''+
73                 "name='" + name + '\'' +
74                 ", birthday='" + birthday+'\'' +
75                 ", sex='" + sex + '\'' +
76                 ",dept='"+dept+'\''+
77                 ",address='"+address+'\''+
78                 '}';
79     }
80 
81 }
View Code

4.StudentDemo 类

该类是从面向对象角度对Student进行插入、删除、编辑、查询所有学生和检索指定学号学生操作的封装。其主要代码如下：
  1 import dao.StudentDao;
  2 import vo.Student;
  3 
  4 import java.util.ArrayList;
  5 import java.util.Scanner;
  6 
  7 
  8 public class StudentDemo {
  9     /**
 10      * 主方法
 11      */
 12     public static void main(String[] args) {
 13         Scanner scanner = new Scanner(System.in);
 14         while (true) {
 15             /* 打印系统操作菜单 */
 16             showMenu();
 17             /* 获取用户要执行的命令编号 */
 18             String choice = scanner.nextLine();
 19             //noinspection AlibabaSwitchStatement
 20             switch (choice) {
 21                 /* 添加学生信息 */
 22                 case "1" -> addStudent();
 23                 /* 删除学生信息 */
 24                 case "2" -> deleteStudent();
 25                 /* 修改学生信息 */
 26                 case "3" ->        editStudent();
 27                 /* 查看所有学生信息 */
 28                 case "4" -> showAllStudents();
 29                 /* 根据学号查询学生信息 */
 30                 case "5" -> showStudentByNum();
 31                 /* 结束程序 */
 32                 case "0" -> {
 33                     System.out.println("程序运行结束。");
 34                     return;
 35                 }
 36                 default -> throw new IllegalStateException("Unexpected value: " + choice);
 37             }
 38         }
 39     }
 40 
 41 
 42     /**
 43      * 打印菜单
 44      */
 45     public static void showMenu() {
 46         System.out.println();
 47         System.out.println("*** 欢迎使用学生信息管理系统 ***");
 48         System.out.println("1.添加学生");
 49         System.out.println("2.删除学生");
 50         System.out.println("3.修改学生信息");
 51         System.out.println("4.查看所有学生");
 52         System.out.println("5.根据学号查询学生信息");
 53         System.out.println("0.退出程序");
 54         System.out.print("请选择要执行的操作：");
 55     }
 56 
 57     /**
 58      * 添加学生操作
 59      */
 60     private static void addStudent() {
 61         Scanner scanner = new Scanner(System.in);
 62         System.out.print("请输入学生学号：");
 63         int num = scanner.nextInt();
 64         scanner.nextLine();
 65         System.out.print("请输入学生姓名：");
 66         String name = scanner.nextLine();
 67         System.out.print("请输入学生性别：");
 68         String sex = scanner.nextLine();
 69         System.out.print("请输入学生出生年份：");
 70         int birthday = scanner.nextInt();
 71         scanner.nextLine();
 72         System.out.print("请输入学生院系：");
 73         String dept = scanner.nextLine();
 74         System.out.print("请输入学生联系地址：");
 75         String address = scanner.nextLine();
 76 
 77         Student student = new Student(num, name, sex, birthday, dept, address);
 78         if (StudentDao.insert(student)) {
 79             System.out.println("成功添加学生信息。");
 80         } else {
 81             System.out.println("添加学生信息失败。");
 82         }
 83     }
 84 
 85     /**
 86      * 删除学生信息
 87      */
 88     private static void deleteStudent() {
 89         Scanner scanner = new Scanner(System.in);
 90         System.out.print("请输入学生学号：");
 91         int num = scanner.nextInt();
 92         scanner.nextLine();
 93         if (StudentDao.delete(num)) {
 94             System.out.println("成功删除");
 95         } else {
 96             System.out.println("删除失败");
 97         }
 98     }
 99 
100     /**
101      * 修改学生信息
102      */
103     private static void editStudent() {
104         Scanner scanner = new Scanner(System.in);
105         System.out.print("请输入学生学号：");
106         int num = scanner.nextInt();
107         scanner.nextLine();
108         System.out.print("请输入学生姓名：");
109         String name = scanner.nextLine();
110         System.out.print("请输入学生性别：");
111         String sex = scanner.nextLine();
112         System.out.print("请输入学生出生年份：");
113         int birthday = scanner.nextInt();
114         scanner.nextLine();
115         System.out.print("请输入学生院系：");
116         String dept = scanner.nextLine();
117         System.out.print("请输入学生联系地址：");
118         String address = scanner.nextLine();
119         Student student = new Student(num, name, sex, birthday, dept, address);
120         if (StudentDao.edit(student)) {
121             System.out.println("成功修改学生信息。");
122         } else {
123             System.out.println("修改学生信息失败。");
124         }
125     }
126 
127     /**
128      * 查询所有学生操作
129      */
130     private static void showAllStudents() {
131         ArrayList<Student> studentList = StudentDao.getAllStudents();
132         System.out.println("所有学生信息如下：");
133         for (Student student : studentList) {
134             System.out.println(student.toString());
135         }
136     }
137 
138     /**
139      * 根据学号查询学生信息
140      */
141     private static void showStudentByNum() {
142         Scanner scanner = new Scanner(System.in);
143         System.out.print("请输入学生学号：");
144         int num = scanner.nextInt();
145         scanner.nextLine();
146         System.out.println("所查询学生信息如下：");
147         Student student = StudentDao.getStudentByNum(num);
148         if (student != null) {
149             System.out.println(student);
150         } else {
151             System.out.println("所查询学生不存在");
152         }
153 
154     }
155 
156 
157 }
View Code
 
<------当然这些代码都是以学生数据库进行的----->

三、数据库结构参考

 
posted @ 2022-04-29 21:46  JULYChanJ  阅读(181)  评论(0)    收藏  举报