Java-JDBC-ResultSet及查询记录对象(JavaBean)封装
目录
内容
1、ResultSet
ResultSet结果集对象,用于封装查询结果。
-
boolean next():游标向下移动一行,判断当前行是否是最后一行(末尾,是否还有数据),有则返回true;否则返回false;
-
getXxx(参数):获取数据
- Xxx:代码数据类型
- 参数:
- int :代表列的编号,从1开始
- String:代码列的名称
-
使用步骤
- 游标向下移动一行
- 判断是否有数据
- 获取数据
-
示例:遍历表account并打印
-
代码1-1:
package cn.gaogzhen.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCDemo4 { public static void main(String[] args) { Connection conn = null; Statement statement = null; ResultSet ret = null; // 2. 注册驱动 try { Class.forName("com.mysql.cj.jdbc.Driver"); // 3. 获取连接对象Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db2?serverTimezone=Asia/Shanghai", "root", "root"); // 4. sql语句 String sql = "select * from account"; // 5. 获取执行sql语句的对象statement statement = conn.createStatement(); // 6. 执行sql操作,获取结果 ret = statement.executeQuery(sql); // 7. 解析结果 System.out.println("ID\t姓名\t余额"); while(ret.next()) { int id = ret.getInt("id"); String name = ret.getString("name"); double balance = ret.getDouble("balance"); System.out.println(id + "\t" + name + "\t" + balance); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { // 8. 释放资源 if (ret != null) { try { ret.close(); } catch (SQLException e) { e.printStackTrace(); } } if (statement != null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
测试结果:
ID 姓名 余额
1 张三 500.0
2 李四 1000.0
2、JavaBean
JavaBean 为Java的标准类,即属性为私有,并且提供相应的get和set方法的类。
3、表 -> 对象集合
- 表名:对应类名
- 一条记录:对应一个类对象
- 列名:对应属性名
- 列类型:对应属性类型
- 数据集:对应集合
4、查询表emp数据将其封装为对象,并装入集合返回
-
表emp图示4-1:
-
JavaBean Emp类代码4-1:
package cn.gaogzhen.domain; import java.util.Date; /** * 员工类 * @author gaogzhen * */ 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 Emp(int id, String ename, int job_id, int mgr, Date joindate, double salary, double bonus, int dept_id) { super(); this.id = id; this.ename = ename; this.job_id = job_id; this.mgr = mgr; this.joindate = joindate; this.salary = salary; this.bonus = bonus; this.dept_id = 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 double getBonus() { return bonus; } public void setBonus(double bonus) { this.bonus = bonus; } public int getDept_id() { return dept_id; } public void setDept_id(int dept_id) { this.dept_id = dept_id; } @Override public String toString() { return "Emp [id=" + id + ", name=" + ename + ", job_id=" + job_id + ", mgr=" + mgr + ", joindate=" + joindate + ", salary=" + salary + ", bonus=" + bonus + ", dept_id=" + dept_id + "]"; } }
-
JDBCDemo05类代码4-2:
package cn.gaogzhen.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Date; import java.util.List; import cn.gaogzhen.domain.Emp; public class JDBCDemo5 { public static void main(String[] args) { List<Emp> list = new JDBCDemo5().findAll(); System.out.println(list); } public List<Emp> findAll() { Connection conn = null; Statement statement = null; ResultSet ret = null; List<Emp> list = new ArrayList<>(); // 2. 注册驱动 try { Class.forName("com.mysql.cj.jdbc.Driver"); // 3. 获取连接对象Connection conn = DriverManager.getConnection("jdbc:mysql:///db1?serverTimezone=Asia/Shanghai", "root", "root"); // 4. sql语句 String sql = "select * from emp"; // 5. 获取执行sql语句的对象statement statement = conn.createStatement(); // 6. 执行sql操作,获取结果 ret = statement.executeQuery(sql); // 7. 解析结果 while(ret.next()) { int id = ret.getInt("id"); String ename = ret.getString("ename"); int job_id = ret.getInt("job_id"); int mgr = ret.getInt("mgr"); Date joindate = ret.getDate("joindate"); double salary = ret.getDouble("salary"); double bonus = ret.getDouble("bonus"); int dept_id = ret.getInt("dept_id"); list.add(new Emp(id, ename, job_id, mgr, joindate, salary, bonus, dept_id)); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { // 8. 释放资源 if (ret != null) { try { ret.close(); } catch (SQLException e) { e.printStackTrace(); } } if (statement != null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } return list; } }
-
测试结果:
[Emp [id=1001, name=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=8000.0, bonus=0.0, dept_id=20], Emp [id=1002, name=卢俊义, job_id=3, mgr=1006, joindate=2001-02-20, salary=16000.0, bonus=3000.0, dept_id=30], Emp [id=1003, name=林冲, job_id=3, mgr=1006, joindate=2001-02-22, salary=12500.0, bonus=5000.0, dept_id=30], Emp [id=1004, name=唐僧, job_id=2, mgr=1009, joindate=2001-04-02, salary=29750.0, bonus=0.0, dept_id=20], Emp [id=1005, name=李逵, job_id=4, mgr=1006, joindate=2001-09-28, salary=12500.0, bonus=14000.0, dept_id=30], Emp [id=1006, name=宋江, job_id=2, mgr=1009, joindate=2001-05-01, salary=28500.0, bonus=0.0, dept_id=30], Emp [id=1007, name=刘备, job_id=2, mgr=1009, joindate=2001-09-01, salary=24500.0, bonus=0.0, dept_id=10], Emp [id=1008, name=猪八戒, job_id=4, mgr=1004, joindate=2007-04-19, salary=30000.0, bonus=0.0, dept_id=20], Emp [id=1009, name=罗贯中, job_id=1, mgr=0, joindate=2001-11-17, salary=50000.0, bonus=0.0, dept_id=10], Emp [id=1010, name=吴用, job_id=3, mgr=1006, joindate=2001-09-08, salary=15000.0, bonus=0.0, dept_id=30], Emp [id=1011, name=沙僧, job_id=4, mgr=1004, joindate=2007-05-23, salary=11000.0, bonus=0.0, dept_id=20], Emp [id=1012, name=李逵, job_id=4, mgr=1006, joindate=2001-12-03, salary=9500.0, bonus=0.0, dept_id=30], Emp [id=1013, name=小白龙, job_id=4, mgr=1004, joindate=2001-12-03, salary=30000.0, bonus=0.0, dept_id=20], Emp [id=1014, name=关羽, job_id=4, mgr=1007, joindate=2002-01-23, salary=13000.0, bonus=0.0, dept_id=10]]
后记 :
本项目为参考某马视频开发,相关视频及配套资料可自行度娘或者联系本人。上面为自己编写的开发文档,持续更新。欢迎交流,本人QQ:806797785
前端项目源代码地址:https://gitee.com/gaogzhen/vue-leyou
后端JAVA源代码地址:https://gitee.com/gaogzhen/JAVA