JDBC扩展
实体类和ORM
- 在使用JDBC操作数据库时,会发现数据是零散的,明明在数据库中是一行完整的数据,到了Java中变成一个个变量,不利于维护和管理,Java是面向对象的,一个表对应一个类,一行数据就对应Java中的一个对象,每个列对应对象的属性,所以要将数据以实体类的方式存储
- ORM(Object Relational Mapping)对象到关系数据库的映射,把面向对象的概念跟数据库中表的概念对应起来,以面向对象的角度操作数据库数据,即一张表对应一个类,一行数据对应一个对象,一个列对应一个属性
package com.lotus.advance.pojo;
public class Employee {
private Integer empId;
private String empName;
private Double empSalary;
private Integer empAge;
public Employee(Integer empId, String empName, Double empSalary, Integer empAge) {
this.empId = empId;
this.empName = empName;
this.empSalary = empSalary;
this.empAge = empAge;
}
public Employee() {
}
public Integer getEmpId() {
return empId;
}
public void setEmpId(Integer empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public Double getEmpSalary() {
return empSalary;
}
public void setEmpSalary(Double empSalary) {
this.empSalary = empSalary;
}
public Integer getEmpAge() {
return empAge;
}
public void setEmpAge(Integer empAge) {
this.empAge = empAge;
}
@Override
public String toString() {
return "Employee{" +
"empId=" + empId +
", empName='" + empName + '\'' +
", empSalary=" + empSalary +
", empAge=" + empAge +
'}';
}
}
package com.lotus.advance;
import com.lotus.advance.pojo.Employee;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCAdvanced {
private Connection conn;
private PreparedStatement statement;
private ResultSet rs;
@Before
public void init() throws SQLException {
String url = "jdbc:mysql://192.168.29.201:3306/jdbc";
String username = "root";
String password = "123456";
conn = DriverManager.getConnection(url, username, password);
}
@Test
public void testORM() throws SQLException {
String statement_sql = "select * from jdbc.t_emp where emp_id=?";
statement = conn.prepareStatement(statement_sql);
statement.setInt(1,2);
rs = statement.executeQuery();
Employee employee = null;
while (rs.next()) {
employee = new Employee();
int emp_id = rs.getInt("emp_id");
String emp_name = rs.getString("emp_name");
double emp_salary = rs.getDouble("emp_salary");
int emp_age = rs.getInt("emp_age");
employee.setEmpAge(emp_age);
employee.setEmpName(emp_name);
employee.setEmpId(emp_id);
employee.setEmpSalary(emp_salary);
System.out.println(employee);
}
}
@Test
public void testORMList() throws SQLException {
String statement_sql = "select * from jdbc.t_emp where emp_age>?";
statement = conn.prepareStatement(statement_sql);
statement.setInt(1,20);
rs = statement.executeQuery();
List<Employee> list = new ArrayList<>();
Employee employee = null;
while (rs.next()) {
employee = new Employee();
int emp_id = rs.getInt("emp_id");
String emp_name = rs.getString("emp_name");
double emp_salary = rs.getDouble("emp_salary");
int emp_age = rs.getInt("emp_age");
employee.setEmpAge(emp_age);
employee.setEmpName(emp_name);
employee.setEmpId(emp_id);
employee.setEmpSalary(emp_salary);
list.add(employee);
}
for (Employee emp : list) {
System.out.println(emp);
}
}
@After
public void destory() throws SQLException {
if (rs != null) {
rs.close();
}
if (statement != null) {
statement.close();
}
if (conn != null) {
conn.close();
}
}
}
主键回显
- 在数据库中,执行新增操作时,主键设置为自动增长,可以在表中直观的看到,但是在Java程序中,执行完新增后,只能得到受影响行数,无法得知当前新增数据的主键值,在Java程序中获取数据中插入新数据后的主键值,并赋值给Java对象,此操作为主键回显。
@Test
public void testReturnPK() throws SQLException {
String sql = "insert into t_emp(emp_name,emp_salary,emp_age) values (?,?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
Employee jack = new Employee(null, "jack", 123.45, 34);
preparedStatement.setString(1,jack.getEmpName());
preparedStatement.setDouble(2,jack.getEmpSalary());
preparedStatement.setInt(3,jack.getEmpAge());
int result = preparedStatement.executeUpdate();
if (result > 0) {
System.out.println("Success");
ResultSet keys = null;
try {
keys = preparedStatement.getGeneratedKeys();
if (keys.next()) {
int empId = keys.getInt(1);
jack.setEmpId(empId);
}
System.out.println(jack);
} finally {
if (keys != null) {
keys.close();
}
}
} else
{
System.out.println("Failure");
}
}
批量操作
- 插入多条数据时,一条一条发送数据库执行,效率低下
- 通过批量操作,可提升操作效率
public void testMoreInsert() throws SQLException {
String url = "jdbc:mysql://192.168.29.201:3306/jdbc?rewriteBatchedStatements=true";
String username = "root";
String password = "Lotus!1120";
conn = DriverManager.getConnection(url, username, password);
String sql = "insert into t_emp(emp_name,emp_salary,emp_age) values (?,?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 0; i < 10000; i++) {
preparedStatement.setString(1,"marry" + i);
preparedStatement.setDouble(2,100.0 + i);
preparedStatement.setInt(3,20 + i);
preparedStatement.addBatch();
}
preparedStatement.executeBatch();
long end = System.currentTimeMillis();
System.out.println("Time:" + (end - start));
}