JavaWeb5.3【JDBC：案例1】

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 部门表<br>CREATE TABLE dept (
    id INT PRIMARY KEY,
    -- 部门id
    dname VARCHAR (50),
    -- 部门名称
    loc VARCHAR (50)
    -- 部门所在地
);
 
-- 添加4个部门
INSERT INTO
    dept (id, dname, loc)
VALUES
    (10, '教研部', '北京'),
    (20, '学工部', '上海'),
    (30, '销售部', '广州'),
    (40, '财务部', '深圳');
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 职务表
CREATE TABLE job (
    id INT PRIMARY KEY,
    jname VARCHAR (20),
    -- 职务名称
    description VARCHAR (50)
  -- 职务描述
);
 
-- 添加4个职务
INSERT INTO
    job (id, jname, description)
VALUES
    (
        1,
        '董事长',
        '管理整个公司，接单'
    ),
    (
        2,
        '经理',
        '管理部门员工'
    ),
    (
        3,
        '销售员',
        '向客人推销产品'
    ),
    (
        4,
        '文员',
        '使用办公软件'
    );
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 员工表
CREATE TABLE emp (
    id INT PRIMARY KEY,
    -- 员工id
    ename VARCHAR (50),
    -- 员工姓名
    job_id INT,
    -- 职务id
    mgr INT,
    -- 上级领导
    joindate DATE,
    -- 入职日期
    salary DECIMAL (7, 2),
    -- 工资
    bonus DECIMAL (7, 2),
    -- 奖金
    dept_id INT,
    -- 所在部门编号
    CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
    CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员工
INSERT INTO
    emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id)
VALUES
    (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
    (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
    (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
    (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
    (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
    (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
    (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
    (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
    (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
    (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
    (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
    (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
    (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
    (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 工资等级表
CREATE TABLE salarygrade (
    grade INT PRIMARY KEY,
    -- 级别
    losalary INT,
    -- 最低工资
    hisalary INT -- 最高工资
);
 
-- 添加5个工资等级
INSERT INTO
    salarygrade (grade, losalary, hisalary)
VALUES
    (1, 7000, 12000),
    (2, 12010, 14000),
    (3, 14010, 20000),
    (4, 20010, 30000),
    (5, 30010, 99990);
复制代码
 1 package com.yub4by.domain;
 2 
 3 import java.util.Date;
 4 
 5 /**
 6  * 封装Emp表数据的JavaBean
 7  */
 8 public class Emp {
 9     private int id; //int
10     private String ename; //varchar
11     private int job_id; //职位编号，外键
12     private int mgr; //管理这个员工的上级编号
13     private Date joindate; //date
14     private double salary; //工资 decimal
15     private double bonus; //奖金
16     private int dept_id; //部门编号，外键
17 
18     public int getId() {
19         return id;
20     }
21 
22     public void setId(int id) {
23         this.id = id;
24     }
25 
26     public String getEname() {
27         return ename;
28     }
29 
30     public void setEname(String ename) {
31         this.ename = ename;
32     }
33 
34     public int getJob_id() {
35         return job_id;
36     }
37 
38     public void setJob_id(int job_id) {
39         this.job_id = job_id;
40     }
41 
42     public int getMgr() {
43         return mgr;
44     }
45 
46     public void setMgr(int mgr) {
47         this.mgr = mgr;
48     }
49 
50     public Date getJoindate() {
51         return joindate;
52     }
53 
54     public void setJoindate(Date joindate) {
55         this.joindate = joindate;
56     }
57 
58     public double getSalary() {
59         return salary;
60     }
61 
62     public void setSalary(double salary) {
63         this.salary = salary;
64     }
65 
66 
67     public int getDept_id() {
68         return dept_id;
69     }
70 
71     public void setDept_id(int dept_id) {
72         this.dept_id = dept_id;
73     }
74 
75 
76     public double getBonus() {
77         return bonus;
78     }
79 
80     public void setBonus(double bonus) {
81         this.bonus = bonus;
82     }
83 
84     @Override
85     public String toString() {
86         return "Emp{" +
87                 "id=" + id +
88                 ", ename='" + ename + '\'' +
89                 ", job_id=" + job_id +
90                 ", mgr=" + mgr +
91                 ", joindate=" + joindate +
92                 ", salary=" + salary +
93                 ", bonus=" + bonus +
94                 ", dept_id=" + dept_id +
95                 '}';
96     }
97 }
复制代码
复制代码
  1 package com.yub4by.jdbc;
  2 
  3 import com.yub4by.domain.Emp;
  4 
  5 import java.sql.*;
  6 import java.util.ArrayList;
  7 import java.util.List;
  8 
  9 /**
 10  * 练习：
 11  *             * 定义一个方法，查询emp表的数据将其封装为对象，然后装载集合，返回。
 12  *                 1. 定义Emp类
 13  *                 2. 定义方法 public List<Emp> findAll(){}
 14  *                 3. 实现方法 select * from emp;
 15  */
 16 public class JDBCDemo7 {
 17 
 18 
 19     public static void main(String[] args) {
 20         List<Emp> empList = new JDBCDemo7().findAll();
 21         System.out.println(empList);
 22         System.out.println(empList.size()); //14
 23     }
 24 
 25 
 26     public List<Emp> findAll(){
 27 
 28         Connection conn = null;
 29         Statement stmt = null;
 30         ResultSet rs = null;
 31         List<Emp> list = null;
 32 
 33         try {
 34             //1 注册驱动
 35             Class.forName("com.mysql.jdbc.Driver");
 36             //2 获取数据库连接对象
 37             conn = DriverManager.getConnection("jdbc:mysql:///hm_db2", "root", "root");
 38             //3 获取执行sql语句的对象
 39             stmt = conn.createStatement();
 40 
 41             //4 定义sql
 42             String sql = "select * from emp";
 43             //5 执行sql
 44             rs = stmt.executeQuery(sql);
 45 
 46             //6 遍历结果集，封装为对象，装载到集合
 47             Emp emp = null;
 48             list = new ArrayList<Emp>();
 49             while (rs.next()){
 50                 //获取数据
 51                 int id = rs.getInt("id");
 52                 String ename = rs.getString("ename");
 53                 int job_id = rs.getInt("job_id");
 54                 int mgr = rs.getInt("mgr");
 55                 Date joindate = rs.getDate("joindate");
 56                 double salary = rs.getDouble("salary");
 57                 double bonus = rs.getDouble("bonus");
 58                 int dept_id = rs.getInt("dept_id");
 59 
 60                 // 创建emp对象,并赋值
 61                 emp = new Emp();
 62                 emp.setId(id);
 63                 emp.setEname(ename);
 64                 emp.setJob_id(job_id);
 65                 emp.setMgr(mgr);
 66                 emp.setJoindate(joindate);
 67                 emp.setSalary(salary);
 68                 emp.setBonus(bonus);
 69                 emp.setDept_id(dept_id);
 70 
 71                 //装载集合
 72                 list.add(emp);
 73             }
 74         } catch (ClassNotFoundException | SQLException e) {
 75             e.printStackTrace();
 76         }finally {
 77             if (rs != null){
 78                 try {
 79                     rs.close();
 80                 } catch (SQLException e) {
 81                     e.printStackTrace();
 82                 }
 83             }
 84             if (stmt != null){
 85                 try {
 86                     stmt.close();
 87                 } catch (SQLException e) {
 88                     e.printStackTrace();
 89                 }
 90             }
 91             if (conn != null){
 92                 try {
 93                     conn.close();
 94                 } catch (SQLException e) {
 95                     e.printStackTrace();
 96                 }
 97             }
 98         }
 99 
100         return list;
101     }
102 
103 
104 }
复制代码
复制代码
 1 package com.yub4by.jdbc;
 2 
 3 import com.yub4by.domain.Emp;
 4 import com.yub4by.util.JDBCUtils;
 5 
 6 import java.sql.*;
 7 import java.util.ArrayList;
 8 import java.util.List;
 9 
10 /**
11  * JDBCUtils改进
12  * 练习：
13  *             * 定义一个方法，查询emp表的数据将其封装为对象，然后装载集合，返回。
14  *                 1. 定义Emp类
15  *                 2. 定义方法 public List<Emp> findAll(){}
16  *                 3. 实现方法 select * from emp;
17  */
18 public class JDBCDemo8 {
19 
20     public static void main(String[] args) {
21         List<Emp> empList = new JDBCDemo8().findAll();
22         System.out.println(empList);
23         System.out.println(empList.size()); //14
24     }
25 
26 
27     public List<Emp> findAll(){
28 
29         Connection conn = null;
30         Statement stmt = null;
31         ResultSet rs = null;
32         List<Emp> list = null;
33 
34         try {
35             /*//1 注册驱动
36             Class.forName("com.mysql.jdbc.Driver");
37             //2 获取数据库连接对象
38             conn = DriverManager.getConnection("jdbc:mysql:///hm_db2", "root", "root");*/
39             conn = JDBCUtils.getConnection();
40 
41             //3 获取执行sql语句的对象
42             stmt = conn.createStatement();
43 
44             //4 定义sql
45             String sql = "select * from emp";
46             //5 执行sql
47             rs = stmt.executeQuery(sql);
48 
49             //6 遍历结果集，封装为对象，装载到集合
50             Emp emp = null;
51             list = new ArrayList<Emp>();
52             while (rs.next()){
53                 //获取数据
54                 int id = rs.getInt("id");
55                 String ename = rs.getString("ename");
56                 int job_id = rs.getInt("job_id");
57                 int mgr = rs.getInt("mgr");
58                 Date joindate = rs.getDate("joindate");
59                 double salary = rs.getDouble("salary");
60                 double bonus = rs.getDouble("bonus");
61                 int dept_id = rs.getInt("dept_id");
62 
63                 // 创建emp对象,并赋值
64                 emp = new Emp();
65                 emp.setId(id);
66                 emp.setEname(ename);
67                 emp.setJob_id(job_id);
68                 emp.setMgr(mgr);
69                 emp.setJoindate(joindate);
70                 emp.setSalary(salary);
71                 emp.setBonus(bonus);
72                 emp.setDept_id(dept_id);
73 
74                 //装载集合
75                 list.add(emp);
76             }
77         } catch (SQLException e) {
78             e.printStackTrace();
79         }finally {
80             JDBCUtils.close(rs, stmt, conn);
81         }
82 
83         return list;
84     }
85 
86 
87 }
复制代码

 

　　

　　

　　

　　

posted @   yub4by  阅读(36)  评论(0)  编辑  收藏  举报
编辑推荐：
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！
点击右上角即可分享
微信分享提示