将sql的查询结果封装为JavaBean
假如有一个表emp
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`id` int(11) NOT NULL,
`ename` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
`job_id` int(11) NULL DEFAULT NULL,
`mgr` int(11) NULL DEFAULT NULL,
`joindate` date NULL DEFAULT NULL,
`salary` decimal(7, 2) NULL DEFAULT NULL,
`bonus` decimal(7, 2) NULL DEFAULT NULL,
`dept_id` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `emp_jobid_ref_job_id_fk`(`job_id`) USING BTREE,
INDEX `emp_deptid_ref_dept_id_fk`(`dept_id`) USING BTREE,
CONSTRAINT `emp_deptid_ref_dept_id_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `emp_jobid_ref_job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;
向表中插入14条数据
INSERT INTO `emp` VALUES (1001, '孙悟空', 4, 1004, '2000-12-17', 8000.00, NULL, 20);
INSERT INTO `emp` VALUES (1002, '卢俊义', 3, 1006, '2001-02-20', 16000.00, 3000.00, 30);
INSERT INTO `emp` VALUES (1003, '林冲', 3, 1006, '2001-02-22', 12500.00, 5000.00, 30);
INSERT INTO `emp` VALUES (1004, '唐僧', 2, 1009, '2001-04-02', 29750.00, NULL, 20);
INSERT INTO `emp` VALUES (1005, '李逵', 4, 1006, '2001-09-28', 12500.00, 14000.00, 30);
INSERT INTO `emp` VALUES (1006, '宋江', 2, 1009, '2001-05-01', 28500.00, NULL, 30);
INSERT INTO `emp` VALUES (1007, '刘备', 2, 1009, '2001-09-01', 24500.00, NULL, 10);
INSERT INTO `emp` VALUES (1008, '猪八戒', 4, 1004, '2007-04-19', 30000.00, NULL, 20);
INSERT INTO `emp` VALUES (1009, '罗贯中', 1, NULL, '2001-11-17', 50000.00, NULL, 10);
INSERT INTO `emp` VALUES (1010, '吴用', 3, 1006, '2001-09-08', 15000.00, 0.00, 30);
INSERT INTO `emp` VALUES (1011, '沙僧', 4, 1004, '2007-05-23', 11000.00, NULL, 20);
INSERT INTO `emp` VALUES (1012, '李逵', 4, 1006, '2001-12-03', 9500.00, NULL, 30);
INSERT INTO `emp` VALUES (1013, '小白龙', 4, 1004, '2001-12-03', 30000.00, NULL, 20);
INSERT INTO `emp` VALUES (1014, '关羽', 4, 1007, '2002-01-23', 13000.00, NULL, 10);
那么我们在java中创建一个Emp的JavaBean
package demo.jdbcTest;
import java.util.Date;
/**
* 封装Emp表数据的JavaBean
*/
public class Emp {
private int id;
private String ename;
private int job_id;
private int mgr;
private Date joindate;
private double salary;
private double bonus;
private int dept_id;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public int getJob_id() {
return job_id;
}
public void setJob_id(int job_id) {
this.job_id = job_id;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public Date getJoindate() {
return joindate;
}
public void setJoindate(Date joindate) {
this.joindate = joindate;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public int getDept_id() {
return dept_id;
}
public void setDept_id(int dept_id) {
this.dept_id = dept_id;
}
public double getBonus() {
return bonus;
}
public void setBonus(double bonus) {
this.bonus = bonus;
}
@Override
public String toString() {
return "Emp{" +
"id=" + id +
", ename='" + ename + ''' +
", job_id=" + job_id +
", mgr=" + mgr +
", joindate=" + joindate +
", salary=" + salary +
", bonus=" + bonus +
", dept_id=" + dept_id +
'}';
}
}
我们使用java查询emp表,并且将 查询结果封装为Emp对象的数组 ArrayList<Emp>,数组中每个元素为一个Emp对象
package demo.jdbcTest;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JdbcQueryToBean {
/**
* * 定义一个方法,查询emp表的数据将其封装为对象,然后装载集合,返回。
*/
public static void main(String[] args) {
List<Emp> list = new JdbcQueryToBean().findAll();
System.out.println(list);
System.out.println(list.size());
}
/**
* 查询所有emp对象
* @return List<Emp> list
*/
public List<Emp> findAll() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
List<Emp> list = null;
try {
//1.注册驱动
//2.获取连接
conn = JDBCUtils.getConnection();
//3.定义sql
String sql = "select * from emp";
//4.获取执行sql的对象
stmt = conn.createStatement();
//5.执行sql
rs = stmt.executeQuery(sql);
//6.遍历结果集,封装对象,装载集合
Emp emp = null;
list = new ArrayList<Emp>();
while (rs.next()) {
//获取数据
int id = rs.getInt("id");
String ename = rs.getString("ename");
int job_id = rs.getInt("job_id");
int mgr = rs.getInt("mgr");
Date joindate = rs.getDate("joindate");
double salary = rs.getDouble("salary");
double bonus = rs.getDouble("bonus");
int dept_id = rs.getInt("dept_id");
// 创建emp对象,并赋值
emp = new Emp();
emp.setId(id);
emp.setEname(ename);
emp.setJob_id(job_id);
emp.setMgr(mgr);
emp.setJoindate(joindate);
emp.setSalary(salary);
emp.setBonus(bonus);
emp.setDept_id(dept_id);
//装载集合
list.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(rs,stmt,conn);
}
return list;
}
}
输出结果如下:
[Emp{id=1001, ename='孙悟空', job_id=4, mgr=1004, joindate=2000-12-17, salary=8000.0, bonus=0.0, dept_id=20},
Emp{id=1002, ename='卢俊义', job_id=3, mgr=1006, joindate=2001-02-20, salary=16000.0, bonus=3000.0, dept_id=30},
Emp{id=1003, ename='林冲', job_id=3, mgr=1006, joindate=2001-02-22, salary=12500.0, bonus=5000.0, dept_id=30},
Emp{id=1004, ename='唐僧', job_id=2, mgr=1009, joindate=2001-04-02, salary=29750.0, bonus=0.0, dept_id=20},
Emp{id=1005, ename='李逵', job_id=4, mgr=1006, joindate=2001-09-28, salary=12500.0, bonus=14000.0, dept_id=30},
Emp{id=1006, ename='宋江', job_id=2, mgr=1009, joindate=2001-05-01, salary=28500.0, bonus=0.0, dept_id=30},
Emp{id=1007, ename='刘备', job_id=2, mgr=1009, joindate=2001-09-01, salary=24500.0, bonus=0.0, dept_id=10},
Emp{id=1008, ename='猪八戒', job_id=4, mgr=1004, joindate=2007-04-19, salary=30000.0, bonus=0.0, dept_id=20},
Emp{id=1009, ename='罗贯中', job_id=1, mgr=0, joindate=2001-11-17, salary=50000.0, bonus=0.0, dept_id=10},
Emp{id=1010, ename='吴用', job_id=3, mgr=1006, joindate=2001-09-08, salary=15000.0, bonus=0.0, dept_id=30},
Emp{id=1011, ename='沙僧', job_id=4, mgr=1004, joindate=2007-05-23, salary=11000.0, bonus=0.0, dept_id=20},
Emp{id=1012, ename='李逵', job_id=4, mgr=1006, joindate=2001-12-03, salary=9500.0, bonus=0.0, dept_id=30},
Emp{id=1013, ename='小白龙', job_id=4, mgr=1004, joindate=2001-12-03, salary=30000.0, bonus=0.0, dept_id=20},
Emp{id=1014, ename='关羽', job_id=4, mgr=1007, joindate=2002-01-23, salary=13000.0, bonus=0.0, dept_id=10}]
14
![91ecf35fb87214ccd1fb18f4207e408b.png](https://i-blog.csdnimg.cn/blog_migrate/409fc8fdc8f63486b5130da644d7ba4d.jpeg)