【WEEK7】 【DAY5】JDBC—PreparedStatement Object【English Version】
发布时间
阅读量：
阅读量
2024.4.12 Friday
Following 【WEEK7】 【DAY4】JDBC—Statement Object【English Version】
Contents
-
10.3.PreparedStatement Object
-
- 10.3.1.PreparedStatement can prevent SQL injection, more efficient than statement
- 10.3.2. Insertion
- 10.3.3. Deletion
- 10.3.4. Update
- 10.3.5. Query
- 10.3.6. Preventing SQL Injection
-
- 10.3.6.1. Under normal circumstances
- 10.3.6.2. Result
- 10.3.6.3. SQL Injection Failed
- 10.3.6.4. Result
-
10.4. Using IDEA to Connect to the Database
-
- 10.4.1. As shown below
10.3.PreparedStatement Object
10.3.1.PreparedStatement can prevent SQL injection, more efficient than statement
10.3.2. Insertion
package lesson.three;
import lesson.two.utils.JdbcUtils;
import java.sql.*;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
//Difference from a regular statement: uses question marks as placeholders
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)";
st = conn.prepareStatement(sql); //Pre-compile SQL: generate SQL but do not execute
//Manually assign values to parameters
//The syntax of set... corresponds to each position of the input function parameters and the parameters you wish to set
st.setInt(1,4); //id
st.setString(2,"lqf");
st.setString(3,"987654");
st.setString(4,"27046873@qq.com");
st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
//new Date().getTime() means: the time calculated by the computer needs to be converted to MySQL time
//sql.Date is database time, util.Date is Java's
//Because the source code of setDate has the time parameter as the database type: void setDate(int parameterIndex, java.sql.Date x)
//So, we need to use new Date().getTime() to get the timestamp (in this version, using “new java.util.Date().getTime()” does not cause an error)
//Execute
int i = st.executeUpdate();
if(i > 0){
System.out.println("Insertion successful");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,null);
}
}
}
- Result
10.3.3. Deletion
package lesson.three;
import lesson.two.utils.JdbcUtils;
import java.sql.*;
public class TestDelete {
public static void main (String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
//Difference from a regular statement: uses question marks as placeholders
String sql = "DELETE FROM users WHERE id = ?";
st = conn.prepareStatement(sql); //Pre-compile SQL: generate SQL but do not execute
//Manually assign values to parameters
st.setInt(1,4); //id
//Execute
int i = st.executeUpdate();
if(i > 0){
System.out.println("Deletion successful");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,null);
}
}
}
- Result
*After completing the code, if there’s no option to run: generally means the system didn’t detect the main function -> The most basic error is a spelling mistake in the main function, check this first before searching online for other possibilities.
10.3.4. Update
package lesson.three;
import lesson.two.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static to main(String[] args){
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
//Difference from a regular statement: uses question marks as placeholders
String sql = "UPDATE users SET `NAME` = ? WHERE id = ?";
st = conn.prepareStatement(sql); //Pre-compile SQL: generate SQL but do not execute
//Manually assign values to parameters
st.setString(1,"阿布巴卡");
st.setInt(2,1); //id
//Execute
int i = st.executeUpdate();
if(i > 0){
System.out.println("Update successful");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,null);
}
}
}
- Result
Here’s the translation:
10.3.5. Query
package lesson.three;
import lesson.two.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//Write SQL
String sql = "SELECT * FROM users WHERE id = ?";
st = conn.prepareStatement(sql); //Pre-compile SQL: generate SQL but do not execute
//Pass parameters
st.setInt(1,1); //id
//Execute
rs = st.executeQuery();
if(rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
- Result
10.3.6. Preventing SQL Injection
10.3.6.1. Under normal circumstances
package lesson.three;
import lesson.two.utils.JdbcUtils;
import java.sql.*;
public class prevent_SQL_injection {
public static void main(String[] args){
login("Abubakar","123456"); //Under normal circumstances
// login("''or '1=1 ","'or '1=1 "); //sql injection
}
//Login service
public static void login(String username, String password){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//SQL
String sql = "SELECT * FROM users WHERE `NAME`=? AND `PASSWORD`=?";
st = conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
//The result set returned after the query is saved in rs
rs = st.executeQuery(); //The sql in the brackets should be deleted, otherwise it throws an error, but the reason is unknown
//Print
while (rs.next()) {
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("email"));
System.out.println(rs.getString("password"));
System.out.println("===========================");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.3.6.2. Result
10.3.6.3. SQL Injection Failed
Only modify the login statement
login("''or '1=1 ","'or '1=1 "); //sql injection
10.3.6.4. Result
(No results found)
10.4. Using IDEA to Connect to the Database
10.4.1. As shown below
In theory, it’s fine as long as it’s successful, but in practice, it’s almost never needed.
Create a table under the p37jdbc database and insert data through IDEA:
-- P44
-- Create user table
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(40),
money FLOAT
);
-- Insert test data
INSERT INTO account(`NAME`, money) VALUES ('A', 1000);
INSERT INTO account(`NAME`, money) VALUES ('B', 1000);
INSERT INTO account(`NAME`, money) VALUES ('C', 1000);
全部评论 (0)
还没有任何评论哟~