Author: Nobody
Source: CSDN
Original text: https://blog.csdn.net/h1025372645/article/details/89191301
JDBC
Java DataBase Connectivity,java database connection, in order to reduce the difficulty of operating data, java provides jdbc, according to the characteristics of java object-oriented, many operations are encapsulated.
JDBC provides a lot of interfaces, and then different database vendors to implement this interface, how to achieve the bottom level, different databases are different, different database vendors need to provide interface implementation classes (Driver class, Driver driver, Driver)
We connect to different databases, we only need to use different drivers.
J: Java: Provides specifications (interfaces) for accessing databases.
DBC: The implementation of the interface, which the vendor implements.
JDBC is a java api for executing SQL statements.
version number
1.1.1 Major. Minor. Build
Major: Project by Architecture, Massive Change
Minor: When new features are available
Build: Compiled version
JDBC development
Java programs need to import jar packages using third-party providers
The relevant jar packages for mysql can be found by searching the following web sites
After downloading the jar package, you can create a lib folder, add the jar package and add dependencies under the same level directory of src.
code implementation
Implementing the use of JDBC through a simple case
import java.sql.*;
public class Demo02 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1 Registration Driver
Class.forName("com.mysql.jdbc.Driver");
// 2 Establishing Connections
String url = "jdbc:mysql://localhost:3306/mydb01";
String usernName = xxx; // Login to the database account
String password = xxxx; // password for login to database
Connection conn = DriverManager.getConnection(url, usernName, password);
// 3 Get the object that executes the sQL statement
Statement statement = conn.createStatement();
// 4 Get the results returned by the database
String sql = "delete from emp where empno = " +"7499";
String sqlUpdate = "update emp set sal = "+10000+" where empno = " +"7369";
String sqlInsert = "INSERT INTO emp VALUES(2018,\"boss\",\"king\",NULL,\"2018-8-
8\",15000,10000,10);";
//5 Processing data sets
int i = statement.executeUpdate(sql);
int s = statement.executeUpdate(sqlUpdate);
int ins = statement.executeUpdate(sqlInsert);
System.out.println(i + "Bank affected----delete");
System.out.println(s + "Bank affected----To update");
System.out.println(ins + "Bank affected----insert");
//6 Close the connection
statement.close();
conn.close();
}
}
Order of using JDBC
(1) Registered database driver
(2) Connecting with database
(3) Get the object to execute the SQL statement
(4) Get the results returned by the database
(5) Processing data sets (logical code)
(6) Release resources and close connections
Frequently used classes
Connection
You can create a connect object through a configuration file
Statement
Get the Statement object that operates the database through the connect object.
Through it, the operation of adding, deleting and modifying the database can be realized.
executeQuery(): Check, return data sets
executeUpdate(): Add, delete, change, return int data, number of rows affected
ResultSet
Data sets, understandably, are collections.
Take out the data:
By subscription: from 1
By field name: The fields following select in an SQL statement may be the same as or different from a database.
JDBC optimization
Usual development and project on-line after the use of the database is different, not the same
That's what we said. The development environment is different.
The development environment is different, the database used is different, so the above three elements of the database configuration need to be modified.
This kind of modification is manual operation, there are mistakes in manual operation, and the. java file after modification, also need to be recompiled, which may also lead to errors.
Assuming that the project is online, the following four steps are required:
Test Environment - > Modify Configuration - > Recompile - > Production Environment
If you want to avoid the above mistakes, you need to bypass the middle two steps.
The solution is to add the configuration file, store the configuration information to be modified in the configuration file, and read the information from the configuration file every time.
The location of the configuration file is fixed and will not be recompiled, which reduces the risk.
Configuration files can also be read and written with IO streams in java and through a proprietary class Properties
IO reads configuration files
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class IoReadProp {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
// 1 Registration Driver
Class.forName("com.mysql.jdbc.Driver");
String[] para = read();
//2 Establishing Connections
String url = para[0];
String usernName = para[1];
String password = para[2];
Connection conn = DriverManager.getConnection(url,usernName,password);
//3 Get the object that executes the sQL statement
Statement statement = conn.createStatement();
//4 Get the results returned by the database
String sql = "select * from emp";
ResultSet resultSet = statement.executeQuery(sql);
//5 Processing data sets
try {
while (resultSet.next()){
//Parameter 1, field name 2, field subscript in. getXXX method
int empno = resultSet.getInt("empno");
String ename = resultSet.getString("ename");
String job = resultSet.getString(3);
Date date = resultSet.getDate(5);
System.out.println("empno: "+empno+", ename:"+ename+", job:"+job+", date:"+date);
}
}
catch (Exception e){
e.printStackTrace();
}
finally {
//6 Close the connection
resultSet.close();
statement.close();
conn.close();
}
}
public static String [] read()throws IOException {
FileReader fr = new FileReader( "E:\\javalearning\\src\\jdbc\\jdbc.properties" );
//Create a write buffer
BufferedReader bufferedReader = new BufferedReader( fr );
String [] str = new String[3];
for(int i =0 ;i < 3;i++){
str[i] = bufferedReader.readLine().split("=")[1].replace(";","").trim();
}
bufferedReader.close();
fr.close();
return str;
}
}
Properties reads configuration files
import java.io.;
import java.sql.;
import java.util.Iterator;
import java.util.Properties;
public class PropReadProp {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
// 1 Registration Driver
Class.forName("com.mysql.jdbc.Driver");
// User arrays store database information
String[] para = new String[3];
//Read configuration files
int i = 0;
Properties prop = new Properties();
FileInputStream fileInputStream = new FileInputStream("E:\\javalearning\\src\\jdbc\\jdbc.properties");
InputStream in = new BufferedInputStream(fileInputStream);
prop.load(in);
Iterator<String> it = prop.stringPropertyNames().iterator();
while (it.hasNext()) {
para[i] = prop.getProperty(it.next());
i++;
}
in.close();
//2 Establishing Connections
String url = para[0];
String usernName = para[1];
String password = para[2];
Connection conn = DriverManager.getConnection(url, usernName, password);
//3 Get the object that executes the sQL statement
Statement statement = conn.createStatement();
//4 Get the results returned by the database
String sql = "select * from emp";
ResultSet resultSet = statement.executeQuery(sql);
//5 Processing data sets
try {
while (resultSet.next()) {
//Parameter 1, field name 2, field subscript in. getXXX method
int empno = resultSet.getInt("empno");
String ename = resultSet.getString("ename");
String job = resultSet.getString(3);
Date date = resultSet.getDate(5);
System.out.println("empno: " + empno + ", ename:" + ename + ", job:" + job + ", date:" + date);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//6 Close the connection
resultSet.close();
statement.close();
conn.close();
}
}
}
Hierarchical DAO
Data Access Object Data Access Object is an object-oriented database interface
A package will be created: dao, where classes are used to manipulate databases.
Usually, with a few tables, there are several DAO s
Layered Entity, bean, pojo
Entities, that is, a class with only attributes and corresponding set.get methods
Often a table is an entity, the attributes of the entity and the fields of the table are not related, the names are generally the same, and the types are corresponding.
Using reverse engineering, entities are derived from tables.
Utils Tool Class
Instead of us, we operate a series of connection closures, etc.
Case study:
The catalogue structure is as follows.
The EmpDAO code is as follows:
package jdbc.dao;
import jdbc.entity.Emp;
import jdbc.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class EmpDAO {
/**
* Obtain employee information based on employee id
*/
public Emp getEmpById(Integer id){
Connection connection =null;
Statement statement = null;
ResultSet rs = null;
Emp emp = null;
try {
connection = JDBCUtils.getConnection();
statement = connection.createStatement();
rs = statement.executeQuery("select * from emp where empno='"+id+"'");
while (rs.next()){
emp = new Emp();
int empno = rs.getInt("empno");
emp.setEmpno(empno);
String ename = rs.getString("ename");
emp.setEname(ename);
String job = rs.getString(3);
emp.setJob(job);
String hiredate = rs.getString(5);
emp.setHiredate(hiredate);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
JDBCUtils.close(connection,statement,rs);
}
return emp;
}
public Emp getEmpById(String id){
Connection connection =null;
Statement statement = null;
ResultSet rs = null;
Emp emp = null;
try {
connection = JDBCUtils.getConnection();
statement = connection.createStatement();
rs = statement.executeQuery("select * from emp where empno="+id);
while (rs.next()){
emp = new Emp();
int empno = rs.getInt("empno");
emp.setEmpno(empno);
String ename = rs.getString("ename");
emp.setEname(ename);
String job = rs.getString(3);
emp.setJob(job);
String hiredate = rs.getString(5);
emp.setHiredate(hiredate);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
JDBCUtils.close(connection,statement,rs);
}
return emp;
}
}
The Emp code in entity is as follows
package jdbc.entity;
public class Emp {
// Relevant attributes in emp table
private Integer empno;
private String ename;
private String job;
private String mgr;
private String hiredate ;
private double sal;
private double comm;
private Integer deptno;
public Integer getEmpno() {
return empno;
}
public void setEmpno(Integer 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 String getMgr() {
return mgr;
}
public void setMgr(String mgr) {
this.mgr = mgr;
}
public String getHiredate() {
return hiredate;
}
public void setHiredate(String 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 Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer deptno) {
this.deptno = deptno;
}
//Default output method
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr='" + mgr + '\'' +
", hiredate='" + hiredate + '\'' +
", sal=" + sal +
", comm=" + comm +
", deptno=" + deptno +
'}';
}
}
The JDBCUtils code in utils is as follows
package jdbc.utils;
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Iterator;
import java.util.Properties;
public class JDBCUtils {
private static final String URL ;
private static final String USERNAME ;
private static final String PASSWORD ;
static{
String [] parp = null;
try {
parp = PropRead();
} catch (IOException e) {
e.printStackTrace();
}
URL = parp[0];
USERNAME = parp[1];
PASSWORD=parp[2];
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* Create connection
*/
public static Connection getConnection() throws SQLException {
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
return conn;
}
public static void close(Connection co , Statement state, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(state !=null){
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(co !=null){
try {
co.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection co , Statement state){
if(state !=null){
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(co !=null){
try {
co.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Read configuration files
* @return
* @throws IOException
*/
public static String [] PropRead()throws IOException {
String[] para = new String[3];
int i = 0;
Properties prop = new Properties();
FileInputStream fileInputStream = new FileInputStream("E:\\javalearning\\src\\jdbc\\jdbc.properties");
InputStream in = new BufferedInputStream(fileInputStream);
prop.load(in);
Iterator<String> it = prop.stringPropertyNames().iterator();
while (it.hasNext()) {
para[i] = prop.getProperty(it.next());
i++;
}
in.close();
return para;
}
}
The test code is as follows:
package jdbc;
import jdbc.dao.EmpDAO;
import jdbc.entity.Emp;
import java.util.Scanner;
public class Main {
public static void main(String[] args) {
EmpDAO empDAO = new EmpDAO();
System.out.println("Please enter ID");
Scanner scanner = new Scanner( System.in );
String value = scanner.nextLine();
Emp emp = empDAO.getEmpById (value);
Emp emp1 = empDAO.getEmpById (7900);
System.out.println(emp);
System.out.println(emp1);
}
}
This simplifies the implementation of a hierarchical case using JDBC
SQL Injection Attack
Based on the above case, we can enter an employee's id to find the employee.
But there is a problem, how do you specify the user's input, let's see a phenomenon below.
I don't have 123456789 in my database, so why would there be results?
Because our sql statements are generated by string splicing, when you input data containing sql keywords, they will be treated as sql statements to execute.
Note: This is dangerous!
Unfriendly users can modify or even delete your database according to this vulnerability!
Solution: PreparedStatement class
PreparedStatement is a subclass of state
Solution Principle:
Instead of splicing, the SQL statements are preprocessed. That is to say, anything entered by the user can only be used as a value, without parsing special characters.
After modification, type the code as follows:
public Emp getEmpById2(String id){
Connection connection =null;
PreparedStatement statement = null;
ResultSet rs = null;
Emp emp = null;
try {
connection = JDBCUtils.getConnection();
String sql = "select * from emp where empno=?";
statement = connection.prepareStatement(sql);
statement.setString(1,id);
rs = statement.executeQuery();
while (rs.next()){
emp = new Emp();
int empno = rs.getInt("empno");
emp.setEmpno(empno);
String ename = rs.getString("ename");
emp.setEname(ename);
String job = rs.getString(3);
emp.setJob(job);
String hiredate = rs.getString(5);
emp.setHiredate(hiredate);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
JDBCUtils.close(connection,statement,rs);
}
return emp;
}
Test again: The results are shown in Figure 1
Summary: This does not mean that Statement is not available or that SQL splicing is not possible.
But if the value passed through the front end needs to be put directly into the SQL statement, you need to pay attention to it.