Three interfaces that can be used to operate a database
There are three interfaces in the java.sql package that define different ways to call a database:
Statement: An object that executes a static SQL Statement and returns the result it generates. ②PrepatedStatement: The SQL statement is precompiled and stored in this object, which can be used to execute the statement efficiently multiple times.
③CallableStatement: used to execute SQL stored procedures
SQL > alter table Statement
-
This object is created by calling the createStatement() method of the Connection object. This object is used to execute static SQL statements and returns the results of the execution.
-
The Statement interface defines the following methods to execute SQL statements:
int excuteUpdate(StringINSERT, UPDATE, DELETE ResultSet executeQuery(StringSQL) : Perform the query operation SELECTCopy the code-
However, there are drawbacks to manipulating tables using Statement:
Problem 1: There are spread-string operations, cumbersome
Problem two: SQL injection problems exist
-
SQL injection is to use some systems to inject invalid SQL statements or commands into user input data without sufficient checks
SELECT name, password FROM user WHERE name='a' OR 1 = ' AND password = ' OR '1' ='1'Thus using the SQL engine of the system to complete the practice of malicious behavior. For Java, to protect against SQL injection, simply replace a Statement with a PreparedStatement(which is an extension of a Statement).
The code shown
Statement test class: simple mock login operations
public class StatementTest {
// The disadvantages of using Statement are that you need to spell SQL statements and there are problems with SQL injection
public static void main(String[] args){
Scanner scanner = new Scanner(System.in);
System.out.print("Username:");
String username = scanner.nextLine();
System.out.print("Password:");
String password = scanner.nextLine();
String sql = "select name,password from user where name = '"+username+
"' AND password ='"+password+"'";
// System.out.println(username);
// "select * from sc where sno = '"+sno+"'"
User user = get(sql);
if(user! =null){
System.out.println("Successful landing.");
}else{
System.out.println("User name does not exist or password is incorrect"); }}// Use Statement to query tables
public static User get(String sql){
User t = null;
InputStream is =null;
Connection conn =null;
Statement statement =null;
ResultSet rs =null;
try {
is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driverClass = properties.getProperty("driverClass");
//1. Register driver
Class.forName(driverClass);
// get the connection
conn = DriverManager.getConnection(url, user, password);
// System.out.println(conn);
//3. Obtain the statement object
statement = conn.createStatement();
//4, execute statement
rs = statement.executeQuery(sql);
// System.out.println(rs);
//5
if (rs.next()) {
t = new User();
// t.setname(rs.getString("name"));
// t.setPassword(rs.getString("password"));
t.setname(rs.getString(1));
t.setPassword(rs.getString(2)); System.out.println(t); }}catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(rs! =null)
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(statement! =null)
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(conn ! =null)
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(is ! =null)
is.close();
} catch(IOException e) { e.printStackTrace(); }}returnt; }}Copy the codeThe User class is used to store the information obtained from the query
public class User {
private String name;
private String password;
public User() {
}
public User(String name, String password) {
super(a);this.name = name;
this.password = password;
}
@Override
public String toString() {
return "name [name=" + name + ", password=" + password + "]";
}
public String getname() {
return name;
}
public void setname(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password; }}Copy the code3. Replace statement with PreparedStatement
1. PreparedStatement introduction
- A preparedStatement object can be obtained by calling the preparedStatement(String SQL) method of the Connection object
- The PreparedStatement interface is a subinterface of Statement and represents a precompiled SQL Statement
- The parameters in the SQL statement represented by the PreparedStatement object are marked with question marks (?). The setXxx() method takes two parameters. The first parameter is the index (starting from 1) of the parameter in the SQL statement to be set, and the second parameter is the value of the parameter in the SQL statement to be set
PreparedStatement vs Statement
-
Readability and maintainability of code.
-
PreparedStatement maximizes performance:
-
DBServer provides performance optimizations for precompiled statements. Because a precompiled statement can be called repeatedly, the statement is cached in the executing code compiled by the DBServer compiler, so the next call does not need to be compiled as long as the same precompiled statement is passed the parameters directly into the compiled statement executing code.
-
In a statement, even though the operation is the same, the data content is different. Therefore, the entire statement itself cannot match, so it does not have the meaning of caching statements. The fact is that no database caches the code executed after compiling ordinary statements. This will compile the incoming statement each time it is executed.
-
(Syntax checking, semantic checking, translation into binary commands, caching)
-
-
A PreparedStatement prevents SQL injection
3, Java and SQL corresponding data type conversion table
4. Use PreparedStatement to implement the add, delete, change and check operation
JDBCUtiles class: this class holds code snippets that are common for connecting to the database
public class JDBCUtils {
/** * get the connection */
public static Connection getConnection() throws Exception {
//1. Load the configuration file
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
//2. Read configuration information
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//3. Load the driver
Class.forName(driverClass);
//4. Get the connection
Connection conn = DriverManager.getConnection(url,user,password);
return conn;
}
/** * Close the resource */
public static void closeResource(Connection conn, Statement statement){
try {
if(statement ! =null)
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(conn ! =null)
conn.close();
} catch(SQLException throwables) { throwables.printStackTrace(); }}}Copy the codePreparedStatementTest class: implements add, delete, and modify operations
public class PreparedStatementTest {
public static void main(String[] args) {
Scanner scan = new Scanner(System.in);
System.out.print("Please enter user name :");
String username = scan.nextLine();
System.out.print("Please enter your password :");
String password = scan.nextLine();
Insert(username,password);
}
public static void Insert(String username,String userpassword){
Connection connection = null;
PreparedStatement ps = null;
try {
connection =JDBCUtils.getConnection();
String sql = "insert into user values(? ,?) ";
ps = connection.prepareStatement(sql);
ps.setString(1, username);
ps.setString(2, userpassword);
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally{ JDBCUtils.closeResource(connection,ps); }}}Copy the code