Understand JDBC
First understand what a database connection driver is
Database driver: It is the key to connecting the application and the database. Our program will deal with the database through the database driver! ! !
In order to simplify developers’ (unified database) operations, SUN provides a specification (for Java database operations), commonly known as JDBC.
Then the implementation of these specifications is done by specific manufacturers.
For developers, we only need to master the operation of the JDBC interface!
Download and import database driver
1. Download
1. Download address: https://dev.mysql.com/downloads/
2. Click Connector/J and select the boxed option, which means it has nothing to do with the platform.
3. Download this compressed file
4. The pop-up interface selection is as follows:
5. Place the decompressed file in the specified location
2. Import IDRA
1. Create a lib directory under the new project
2. Copy the jar package we downloaded
3. Right-click the lib directory and click Add as Library
4. Click OK
5. We found that the jar package can be expanded, indicating that we have successfully imported the jar package into the project.
The first JDBC program (standard operating steps)
1. Operation steps
1. The imported connection in pom.xml is the jar package of the database, and the JDBC driver is loaded in the class;
2. Write the user’s account number, password and URL path;
3. The driver connects to the database and returns the connection object connection;
4. The connection object connection creates an object statement that executes SQL;
5. Execute the SQL object to call the query or update method (executeQuery) to execute the SQL statement and return a result set;
6. Release the connection
2. Test code (the code for the first and second steps is fixed, memorize it!!!)
Database Table:
IDEA code:
Import dependencies:
<!-- Import the jar package of the database connection driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
</dependencies>
The specific code is as follows:
import java.sql.*;
//My first JDBC program
public class jdbcFirst {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1. Load the driver. Remember this loading method. This is a fixed way of writing.
Class.forName("com.mysql.cj.jdbc.Driver");
//2. User information and URL, remember this path, it is also a fixed way of writing! ! ! ! ! ! ! ! ! !
/*useUnicode=true indicates support for Chinese encoding
* characterEncoding=utf8 means setting the character set encoding
* useSSL=true means using a secure connection
* "?" is used to connect parameters
* " & amp;" means and and
*URL represents unique positioning:
*MySQL formula: protocol://host address:port number (default 3306)/database name? Parameter 1 & amp; Parameter 2 & amp; Parameter 3;
* oralce formula: jdbc:oralce:thin@localhost:1521 (default 1521):sid
* */
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true & amp;characterEncoding=utf8 & amp;useSSL=true";
String username = "root";
String password = "root";
//3. Connect to the database and return the database object. Connection represents the database. You can also set automatic transaction submission, rollback and other operations at this level.
Connection connection = DriverManager.getConnection(url, username, password);
/*//Database level operations are common as follows:
connection.commit(); //Submit transaction
connection.setAutoCommit(true);//Set whether to automatically commit
connection.rollback(); //Rollback transaction*/
//4. Execute SQL object statement is the object used to execute SQL
Statement statement = connection.createStatement();
//5. Object for executing SQL. To execute SQL, there may be results. Check the returned results.
String sql1 = "SELECT * FROM users"; //Define query statement
//Note: Only the Query and update methods are called by the statement, because insert and delete are both classified under update, and executeUpdate() returns the number of affected rows.
//statement.execute();//All statements of addition, deletion, modification and query can be executed
ResultSet resultSet = statement.executeQuery(sql1); // resultSet is only available when querying. It returns a result set in the form of a linked list. The result set encapsulates all query results.
while (resultSet.next()) { //resultSet.next() return value is a Boolean type, if there is no data in the result set, it returns false
System.out.println("id = " + resultSet.getObject("id")); // getObject is used when the column data type is not known. resultSet can also call methods of specific types
System.out.println("name = " + resultSet.getObject("NAME"));
System.out.println("pwd = " + resultSet.getObject("PASSWORD"));
System.out.println("email = " + resultSet.getObject("email"));
System.out.println("birthday = " + resultSet.getObject("birthday"));
System.out.println("========================================== ===");
}
//6. Release the connection, which must be done (the connection takes up a lot of memory and resources must be released)
resultSet.close();
statement.close();
connection.close();
}
}
Extract JDBC into a tool class
1. Create a new package named utils
2. In the tool class, write the method of loading the driver into the static code block, and load it as the class is loaded. In this way, you only need to execute the method of loading the driver once.
Then write the function of connecting to the database and releasing resources into a method, which can be called directly in the future.
import com.mysql.cj.protocol.Resultset;
import javax.xml.stream.events.StartDocument;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
//Raise the scope of the variables we use
private static String driver;
private static String url;
private static String username;
private static String password;
static {
try {
// 1. Obtain his own reflection object through the tool class, then obtain the class loader of the reflection object, and call the class loader's method of obtaining resources.
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
//2. Create an object to read the configuration file
Properties properties = new Properties();
//3. Call the method to read the configuration file
properties.load(in);
//4. Get the value in the configuration file
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//Load the JDBC Driver. Because it is placed in a static code block, it only needs to be loaded once.
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
//Connect to database method
public static Connection getConnection() throws SQLException {
//Connect to the database
return DriverManager.getConnection(url, username, password);
}
//Release resource method
public static void release(Connection conn, Statement sta, ResultSet rs) throws SQLException {
if (rs != null) {
rs.close();
}
if (sta != null) {
sta.close();
}
if (conn != null) {
conn.close();
}
}
}
Use tools to complete additions, deletions, modifications and checks
In IDEA, the Update method is directly called for additions, deletions and modifications, and the excuteQuery method is called for queries.
Test table:
1. Addition, deletion and modification
public class testInsert {
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement sta = null;
ResultSet rs = null;
try {
//Get the connection, because we encapsulate JDBC into a tool class, here you can directly call the method of the tool class
conn = JdbcUtils.getConnection();
//Get the object to execute sql
sta = conn.createStatement();
//Write the required SQL statements
String sql = "INSERT INTO users (id,`NAME`,`PASSWORD`,`email`,birthday)" +
"VALUES (4,'Zhang San','1232456445','[email protected]','2023-01-23');";
//Use the Update method for all additions, deletions and changes, and return the number of affected rows of type int.
int i = sta.executeUpdate(sql);
if (i != 0){
System.out.println(i + "The data was inserted successfully!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//When the operation is completed, call the method to release the resources
JdbcUtils.release(conn,sta,rs);
}
}
}
2. Inquiry
public class testQuery {
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
sta = conn.createStatement();
String sql = "SELECT * FROM users";
rs = sta.executeQuery(sql);
while (rs.next()){
System.out.println("id:" + rs.getInt("id"));
System.out.println("NAME:" + rs.getString("NAME"));
System.out.println("PASSWORD:" + rs.getInt("PASSWORD"));
System.out.println("email:" + rs.getObject("email"));
System.out.println("birthday:" + rs.getObject("birthday"));
System.out.println("============================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
JdbcUtils.release(conn,sta,rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
SQL injection
SQL statements can be spliced (there are loopholes). If the business we write does not shield this kind of SQL injection problem, the program will be attacked.
The following demonstrates a piece of SQL injection code:
public class testLogin {
public static void main(String[] args) {
//Calling the login business in the main method, write a username h and password of "'or '1 = 1", "'or '1 = 1". This will make the where condition judgment always keep true
login("'or '1 = 1", "'or '1 = 1");
}
//Login business
public static void login(String username, String password) {
Connection conn = null;
Statement sta = null;
ResultSet rs = null;
try {
//Get the connection, because we encapsulate JDBC into a tool class, here you can directly call the method of the tool class
conn = JdbcUtils.getConnection();
//Get the object to execute sql
sta = conn.createStatement();
//Write the required SQL statements
String sql = "select * from users where `NAME` = '" + username + "'AND PASSWORD = '" + password + "'";
//Use the Update method for all additions, deletions and changes, and return the number of affected rows of type int.
rs = sta.executeQuery(sql);
while (rs.next()) {
System.out.println("id:" + rs.getInt("id"));
System.out.println("NAME:" + rs.getString("NAME"));
System.out.println("PASSWORD:" + rs.getInt("PASSWORD"));
System.out.println("email:" + rs.getObject("email"));
System.out.println("birthday:" + rs.getObject("birthday"));
System.out.println("============================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//When the operation is completed, call the method to release the resources
try {
JdbcUtils.release(conn, sta, rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
Output result:
Write a piece of spliced SQL to query all the information.
PreparedStatement object
This object can prevent SQL injection and will be more efficient
1. Code for adding, deleting and modifying PreparedStatement objects
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement sta = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//The question mark in value represents a placeholder. The advantage of using the prepareStatement method is that value can be added last.
String sql = "INSERT INTO users (id,`NAME`,`PASSWORD`,`email`,birthday) VALUES(?,?,?,?,?)";
//Different from statement
/*1. The prepareStatement object can be created directly by the database conn object, and the statement object also needs the conn object to call the createStatement() method to create it.
* 2. The prepareStatement() method needs to pass in a parameter. This parameter is a precompiled SQL statement. Generally speaking, the SQL is written first and is not executed temporarily.
* 3. Put the written SQL into the method in advance
* */
sta = conn.prepareStatement(sql);
sta.setInt(1, 4);
sta.setString(2, "10086");
sta.setString(3, "123456789");
sta.setString(4, "[email protected]");
//Note: The date here is the date that calls SQL first
//Then what is called is the date under until in java, and getTime is to get the current timestamp.
sta.setDate(5, new java.sql.Date(new Date().getTime()));
//The real execution method is different from the statement that calling this method requires passing sql into it, while prepareStatement does not need to pass in SQL statements.
int i = sta.executeUpdate();
if (i > 0 ){
System.out.println(i + "Line inserted!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
JdbcUtils.release(conn, sta, rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
2. Query code
public class TestQuery {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement sta = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//The question mark in value represents a placeholder. The advantage of using the prepareStatement method is that value can be added last.
String sql = "select * from users where id = ?";
//Different from statement
/*1. The prepareStatement object can be created directly by the database conn object, and the statement object also needs the conn object to call the createStatement() method to create it.
* 2. The prepareStatement() method needs to pass in a parameter. This parameter is a precompiled SQL statement. Generally speaking, the SQL is written first and is not executed temporarily.
* 3. Put the written SQL into the method in advance
* */
sta = conn.prepareStatement(sql);
sta.setInt(1,3);
//The real execution method is different from the statement that calling this method requires passing sql into it, while prepareStatement does not need to pass in SQL statements.
rs = sta.executeQuery();
if (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
JdbcUtils.release(conn, sta, rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
3. Reasons to prevent SQL injection
See code and comments below
public class testLogin {
public static void main(String[] args) {
//Calling the login business in the main method
//1. Normal parameters
login("10086","123456789");
//2. SQL injection parameters
login("'or '1 = 1", "'or '1 = 1");
}
//Login business
public static void login(String username, String password) {
Connection conn = null;
PreparedStatement sta = null;
ResultSet rs = null;
try {
//Get the connection, because we encapsulate JDBC into a tool class, here you can directly call the method of the tool class
conn = JdbcUtils.getConnection();
//Write the required SQL statements
String sql = "select * from users where `NAME` = ? and `PASSWORD` = ?";
//Get the object to execute sql and precompile it
//The essence of prepareStatement to prevent SQL injection is that it treats the parameters passed in as characters. If there is an escape character like "" in it, it will be escaped directly.
sta = conn.prepareStatement(sql);
//Assign value to value
sta.setString(1,username);
sta.setString(2,password);
rs = sta.executeQuery();
while (rs.next()) {
System.out.println("id:" + rs.getInt("id"));
System.out.println("NAME:" + rs.getString("NAME"));
System.out.println("PASSWORD:" + rs.getInt("PASSWORD"));
System.out.println("email:" + rs.getObject("email"));
System.out.println("birthday:" + rs.getObject("birthday"));
System.out.println("============================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//When the operation is completed, call the method to release the resources
try {
JdbcUtils.release(conn, sta, rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
Statement object
There is a problem of SQL injection when using statement objects, which is not safe. Generally, PreparedStatement objects are used!