## Extraction of JDBC tool classes: JDBCUtils
* :: Purpose: to simplify writing
* :: Analysis.
1. Register the driver and also extract
2. Extract a method to get a connected object
* :: Requirements: do not want to pass parameters (cumbersome), but also need to ensure the generality of the tool class
* :: Resolution: configuration file
jdbc.properties
url=
user=
password=
3. Extract a method to release resources
Create jDBc.properties in the SRC directory
url=jdbc:mysql:///db3
user=root
password=123456
driver=com.mysql.cj.jdbc.Driver
JDBCUtils.java
package cn.itcast.utils;
import jdk.swing.interop.SwingInterOpUtils;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.net.URI;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
// JDBC Tool
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
// The file is read only once to get these values. Using static code blocks
static {
// Read the resource file, get the value.
try {
// Create the Properties collection class.
Properties pro = new Properties();
// The way to get the files in the src path - >ClassLoader class loader
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
System.out.println(path);
// 2. Load the file.
pro.load(new FileReader(path));
// 3. Get the data and assign a value.
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
// 4. Registration drive
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
// Get connection, return connection object
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
// Release of resources
public static void close(Statement stmt, Connection conn){
if(stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
// Release of resources
public static void close(ResultSet rs, Statement stmt, Connection conn){
if(rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
jdbcdemo.java
// Demo JDBC tool class
// Query all emp objects.
public List<Emp> findAll() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
List<Emp> list = null;
try {
conn = JDBCUtils.getConnection();
// 3. Defining sql
String sql = "select * from emp";
// 4. Get the object to execute sql.
stmt = conn.createStatement();
// 5. execute sql
rs = stmt.executeQuery(sql);
// 6. traverse the result set, encapsulate the object, load the set
Emp emp = null;
list = new ArrayList<Emp>();
while(rs.next()){
// Access to data
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 bounds = rs.getDouble("bounds");
int dept_id = rs.getInt("dept_id");
// Create an emp object and assign it a value.
emp = new Emp();
emp.setEname(ename);
emp.setJob_id(job_id);
emp.setMgr(mgr);
emp.setJoindate(joindate);
emp.setSalary(salary);
emp.setBounds(bounds);
emp.setDept_id(dept_id);
// load a collection
list.add(emp);
}
}catch (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.close(rs,stmt,conn);
}
return list;
}