Java-JDBC-查询获取数据库数据
- JDBC对数据库的操作也叫做CURD: 它代表创建(Create)、更新(Update)、读取(Retrieve)和删除(Delete)操作
- 在JDBC中,使用查询语句获取到的是一个
resultSet
数据集
- 这个数据集可以想象为一个表格,表头就是
SQL
语句查询的字段,每一行就是查询出来的每一条数据 resultSet
数据集提供了一系列的get
方法,可以获取数据集中的数据resultSet
数据集也有一个next()
方法,这个方法实现的效果如果有下一行,就返回true
并且游标往下移动一行,如果没有下一行数据,就返回false
如何使用JDBC完成数据库查询操作
- 在使用JDBC进行查询的时候,一般都先写一个实体类,这个实体类是为了完成将每一行数据封装为一个对象而创建,实体类中的每一个属性都对应
SQL
查询出来的每一个字段(实体类的具体注意事项见代码注释) - 在包装实体类的时候,属性推荐使用包装类进行封装,避免空值异常
package com.shanlei.entity;
import java.io.Serializable;
import java.util.Date;
public class Emp implements Serializable {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Integer deptno;
@Override
public String toString() {
return "emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", hiredate=" + hiredate +
", sal=" + sal +
", comm=" + comm +
", deptno=" + deptno +
'}';
}
public Emp() {
}
public Emp(int empno, String ename, String job, int mgr, Date hiredate, double sal, double comm, int deptno) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
public double getComm() {
return comm;
}
public void setComm(double comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
}
package com.shanlei.test01;
import com.shanlei.entity.Emp;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class Test04 {
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String url = "jdbc:mysql://localhost/mytestdb?useSSL=false&useUnicode=ture&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
private static String user = "root", password = "123456";
public static void main(String[] args) {
List<Emp> emps = testQuery();
for (Emp emp : emps) {
System.out.println(emp);
}
}
public static List<Emp> testQuery(){
Connection connection = null;
PreparedStatement preparedstatement = null;
ResultSet resultSet = null;
List<Emp> emps = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);
String sql = "select * from emp";
preparedstatement = connection.prepareStatement(sql);
resultSet = preparedstatement.executeQuery();
emps = new ArrayList<Emp>();
while(resultSet.next()){
int empno = resultSet.getInt("empno");
String ename = resultSet.getString("ename");
String job = resultSet.getString("job");
int mgr = resultSet.getInt("mgr");
Date hiredate = resultSet.getDate("hiredate");
double sal = resultSet.getDouble("sal");
double comm = resultSet.getDouble("comm");
int deptno = resultSet.getInt("deptno");
emps.add(new Emp(empno, ename, job, mgr, hiredate, sal ,comm, deptno));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(null != resultSet){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != preparedstatement){
try {
preparedstatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != connection){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return emps;
}
}