-- 部门表
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, '财务部', '深圳');
-- 职务表
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,
'文员',
'使用办公软件'
);
-- 员工表
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);
-- 工资等级表
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 }
