Summary of JDBC
What is a JDBC
- Java Data Base Connectivity (JDBC) is a standard specification for Java to access databases. Is a Java API for executing SQL statements that provides uniform access to multiple relational databases and consists of a set of classes and interfaces written in the Java language.
JDBC theory
-
JDBC is the interface, the driver is the implementation of the interface, without the driver will not be able to complete the database connection, thus can not operate the database! Each database vendor needs to provide its own driver to connect to its own database, which means that drivers are generally provided by the database generator vendor.
-
Summary: JDBC is defined by Sun company a set of operation of all relational database rules (interface), and database manufacturers need to achieve this interface, provide a database driver JAR package, we can use this interface programming, the real implementation of the code is the corresponding driver package implementation class.
JDBC development
API usage: 1. Register the driver
- The JDBC specification defines the Driver interface: java.sql.Driver
- The MySql Driver package provides the implementation class com.mysql.jdbc.driver
How to load the registered driver | describe |
---|---|
Class.forname (database driven implementation Class) | Load and register the database Driver provided by MySql “com.mysql.jdbc.driver”. |
- Code sample
public class JDBCDemo01 {
public static void main(String[] args) throws ClassNotFoundException {
//1. Register the driver
// The forName method initializes the class
Class.forName("com.mysql.jdbc.Driver"); }}Copy the code
- Note: starting with JDBC3, the version that is currently in common use. Can be used directly without registering the driver. The sentence class.forname can be omitted.
API usage: 2. Get a connection
- The Connection interface, representing a Connection object, is implemented by the vendor of the database
- Using static methods of the DriverManager class, getConnection gets the connection to the database
Static method to get a connection | instructions |
---|---|
Connection getConnection(String url, String user, String password) | Retrieve the database connection object by connection string and username and password |
- 1) Description of three connection parameters of getConnection method
The connection parameters | instructions |
---|---|
user | Login User name |
password | The login password |
url | MySql URL format jdbc:mysql://localhost:3306/db4 |
- 2) A detailed description of urls
jdbc:mysql://localhost:3306/db4? characterEncoding=UTF-8
- JDBC specifies the URL format byThree partsEach part is separated by a colon.
- The first part is the JDBC protocol, which is fixed;
- The second part is the subprotocol, which is the name of the database, connecting to the mysql database, and the second part is of course mysql;
- The third part is specified by the database vendor. We need to understand the requirements of each database vendor. The third part of mysql consists of the IP address of the database server (localhost), port number (3306), and database name to be used.
3. Obtain the statement execution platform
- The SQL statement execution object is obtained through the createStatement method of Connection
Methods in the Connection interface | instructions |
---|---|
Statement createStatement() | Create SQL statement execution objects |
- Statement: Represents a Statement object that sends SQL statements to the server, executes static SQL statements, and returns the results it generates.
Common methods of the Statement class | instructions |
---|---|
int executeUpdate(String sql); | Execute the INSERT UPDATE delete statement. Returns an int representing the number of rows affected |
ResultSet executeQuery(String sql); | Execute the SELECT statement to return a ResultSet object |
API usage: 4. Process result sets
- The result set is processed only when a query operation is performed
The ResultSet interface
- Function: encapsulate the result set of database query, traverse the result set, take out each record.
ResultSet Interface method | instructions |
---|---|
boolean next() | 1) Cursor down one line 2) Return Boolean, true if there is a next record, false otherwise |
xxx getXxx( String or int) | 1) By column name, the argument is of type String. Returns different types 2) Pass the column number, the argument is an integer, starting at 1. Returns different types |
5. Release resources
1) Objects to be released are as follows: ResultSet, Statement, and Connection 2) Release principles: Open objects first and close them later. ResultSet ==> Statement ==> Connection 3) Which code block to place in: finally block
- As with IO streams, everything needs to be turned off after use! The sequence of closing is first on and then off, the first obtained and then closed, and the first obtained and then closed
Step to summarize
- Get driver (can be omitted)
- Get connected
- Obtaining the Statement object
- Processing result sets (only at query time)
- Release resources
JDBC implementation add, delete, change and check
JDBC tools
- When do you create your own utility classes?
- If a feature is used frequently, we recommend making it a utility class that can be reused in different places.
- The “get database connection” operation, which will be present in all future add, delete, change, and search functions, can encapsulate the utility class JDBCUtils. Provides a way to get connection objects to achieve code reuse.
- What a utility class contains
-
- Several strings can be defined as constants: username, password, URL, driver class
-
- Get the database connection: getConnection()
-
- Close all open resources:
-
DML operations
Insert records
- Solve the problem of inserting Chinese garbled characters
// jdbc:mysql://localhost:3306/db4? characterEncoding=UTF-8
// characterEncoding= utf-8 specifies the characterEncoding and decoding format.
Copy the code
- Code sample
/** * Insert data *@throws SQLException
*/
@Test
public void testInsert(a) throws SQLException {
//1. Use the tool class to obtain the connection
Connection connection = JDBCUtils.getConnection();
/ / 2. Get the Statement
Statement statement = connection.createStatement();
/ / 2.1 to write Sql
String sql = Insert into Jdbc_user values(null,' jdbc_user ','123','2020/1/1')";
/ / 2.2 Sql execution
int i = statement.executeUpdate(sql);
System.out.println(i);
/ / 3. Close the flow
JDBCUtils.close(connection,statement);
}
Copy the code
Update record
- Change the user name according to the ID
/** * Change the name of user 1 to guangkun */
@Test
public void testUpdate(a) throws SQLException {
Connection connection = JDBCUtils.getConnection();
Statement statement = connection.createStatement();
String sql = "Update jdbc_user set username = ' 'where id = 1";
statement.executeUpdate(sql);
JDBCUtils.close(connection,statement);
}
Copy the code
Delete records
- Delete records 3 and 4
/** * Delete records with ids 3 and 4 *@throws SQLException
*/
@Test
public void testDelete(a) throws SQLException {
Connection connection = JDBCUtils.getConnection();
Statement statement = connection.createStatement();
statement.executeUpdate("delete from jdbc_user where id in(3,4)");
JDBCUtils.close(connection,statement);
}
Copy the code
DQL operation
Query a record whose name is Zhang Baiwan
public class TestJDBC02 {
public static void main(String[] args) throws SQLException {
//1. Obtain the connection object
Connection connection = JDBCUtils.getConnection();
2. Obtain the Statement object
Statement statement = connection.createStatement();
String sql = "SELECT * FROM jdbc_user WHERE username = 'jdbc_user ';;
ResultSet resultSet = statement.executeQuery(sql);
//3. Process the result set
while(resultSet.next()){
// Get field information by column name
int id = resultSet.getInt("id");
String username = resultSet.getString("username");
String password = resultSet.getString("password");
String birthday = resultSet.getString("birthday");
System.out.println(id+""+username+"" + password +"" + birthday);
}
//4. Release resourcesJDBCUtils.close(connection,statement,resultSet); }}Copy the code
SQL Injection Problems
Sql Injection Demo
1) Insert two data into jdbc_user
Insert 2 pieces of data
INSERT INTO jdbc_user VALUES(NULL.'jack'.'123456'.'2020/2/24');
INSERT INTO jdbc_user VALUES(NULL.'tom'.'123456'.'2020/2/24');
Copy the code
2) SQL injection demonstration
# SQL injection demo
-- Enter an incorrect password
SELECT * FROM jdbc_user WHERE username = 'tom' AND PASSWORD = '123' OR '1' = '1';
Copy the code
If this is a login operation, then the user has logged in successfully. Obviously this is not what we want to see
Problem analysis
What is SQL injection?
- We concatenate the password entered by the user with the SQL statement. The user’s input becomes part of the SQL statement syntax and changes the true meaning of the original SQL. This problem is called SQL injection.
How is injection implemented
- Concatenate string at user input
select * from jdbc_user where username = 'abc' and password = 'abc' or '1'='1'
-- name=' ABC 'and password=' ABC
Select * from user where true=true; All records were queried
Copy the code
How to solve
- To solve SQL injection, you cannot simply concatenate the password entered by the user with our SQL statement.
Preprocessing object
PreparedStatement Describes the interface
- PreparedStatement is a subinterface of the Statement interface that inherits all methods from the parent interface. It is a pre-compiled SQL statement object.
- Precompiled: Indicates that SQL statements are precompiled and stored in a PreparedStatement object. You can then use this object to efficiently execute the statement multiple times.
PreparedStatement characteristics
- Because of the pre-compiled function, improve the efficiency of SQL execution.
- Can effectively prevent SQL injection problems, higher security
Get the PreparedStatement object
- Create a PreparedStatement object using Connection
Methods in the Connection interface | instructions |
---|---|
PreparedStatement prepareStatement(String sql) | Specifies a precompiled SQL statement that uses placeholders? Create a statement object |
PreparedStatement Common method of an interface
Commonly used method | instructions |
---|---|
int executeUpdate(); | Execute the INSERT UPDATE delete statement |
ResultSet executeQuery(); | Execute the SELECT statement. Returns the result set object Resulet |
Use the steps in PreparedStatement
1) Write SQL statement, unknown content use? Sites:
"SELECT * FROM jdbc_user WHERE username=? AND password=?" ;
2) Get the PreparedStatement object 3) set the actual parameters: setXxx(placeholder position, real value) 4) execute the parameterized SQL statement 5) close the resource
SetXxx overloaded method | instructions |
---|---|
void setDouble(int parameterIndex, double x) | Sets the specified parameter to the given Java double value. |
void setInt(int parameterIndex, int x) | Sets the specified parameter to the given Java int value. |
void setString(int parameterIndex, String x) | Sets the specified parameter to the given Java String value. |
void setObject(int parameterIndex, Object x) | Sets the value of the specified argument using the given object. |
What is the difference between a Statement and a PreparedStatement?
- Statement is used to execute static SQL statements. To execute static SQL statements, you must specify a prepared SQL Statement.
- A PrepareStatement is a pre-compiled SQL statement object that can contain dynamic parameters such as? , can be “? “. Dynamically set parameter values.
- Preparestatements can reduce compilation times and improve database performance.
JDBC Control transactions
- Previously we used MySQL commands to manipulate transactions. Next we use JDBC to handle transactions for bank transfers.
Transaction dependent API
- We use the methods in Connection for transaction management
methods | instructions |
---|---|
void setAutoCommit(boolean autoCommit) | The parameter is true or false. If this parameter is set to false, automatic commit is disabled and transactions are enabled |
void commit() | Commit the transaction |
void rollback() | Roll back the transaction |
Development steps
- Get connected
- Open the transaction
- When the PreparedStatement is obtained, perform two update operations
- Normally the transaction is committed
- An exception occurred to rollback the transaction
- Finally close the resource