1. JDBC

1. concept
   • Java DataBase Connectivity
   • A set of interfaces defined by Sun that operate on all relational databases, implemented by various database vendors, and then provided database driver JAR packages. When a programmer uses this interface to manipulate a database, the real code that executes is the implementation class in the database driver JAR package.
2. Using the step
   1. Import the database driver JAR package
   2. Registration drive
   3. Gets the database connection object
   4. Defining SQL statements
   5. Gets the object that executes the SQL statement
   6. Execute the SQL and receive the returned results
   7. The processing results
   8. Release resources
3. The main object
   1. DriveManagement: Drives managed objects
      • Register driver: tells the program which database driver JAR package to use
        • Class.forName("com.mysql.jdbc.Driver");
          • There is actually a static block of code in the com.mysql.jdbc.driver class that calls the registerDriver() method of Drivemanmanagement

            static {
                try {
                    DriverManager.registerDriver(new Driver());
                } catch (SQLException var1) {
                    throw new RuntimeException("Can't register driver!"); }}Copy the code

        • Get database connection:public static Connection getConnection(String url, String user, String password)
          • parameter
            • Url: indicates the connection path
              • Format:JDBC :mysql:// IP address (domain name): port number/database name
              • If you are connecting to a local mysql server and the default mysql service port is 3306, you can abbreviate the URL to:JDBC :mysql:/// Database name
            • User: indicates the user name
            • Password: password
   2. Connection: Database connection object
      • Gets the object that executes the SQL statement
        • To obtain the Statement:Statement createStatement()
        • To obtain a PreparedStatement:PreparedStatement prepareStatement(String sql)
      • Regulatory affairs
        • Start transaction (start transaction before SQL execution) :void setAutoCommit(boolean var1)Call this method with the argument false to start the transaction
        • Commit transaction (when all SQL has executed commit transaction) :void commit()
        • Rollback transaction (rollback transaction in catch) :void rollback()
   3. Statement: The object that executes a static SQL statement
      • boolean execute(String sql): Can execute any SQL statement
      • int executeUpdate(String sql): Executes DML (INSERT, UPDATE, delete) statements and DDL(CREATE, ALTER, drop) statements
        • The return value is the number of affected rows. You can determine whether the SQL statement is successfully executed based on the return value. If the return value is greater than 0, the execution is successful. Otherwise, the execution fails.
      • ResultSet executeQuery(String sql): Executes the DQL (SELECT) statement
   4. ResultSet: result set object that encapsulates query results
      • Boolean next(): After executing this method, move the cursor down one line (the default cursor position is in the header), then check whether the current row has data, if so, return true; If there is no data, return false
      • Xxx getXxx(parameter): obtains data
        • Xxx: data type
        • parameter
          • Int: column number, starting from 1
          • String: column name
   5. PreparedStatement: An object that executes a precompiled SQL statement
      1. SQL injection problem: When concatenating SQL, some SPECIAL SQL keywords are used to join strings
         • Enter the user name and password: a’ or ‘a’ = ‘a’
         • When executing SQL statements using Statement,select * from user where username = 'najhuf' and password = 'a' or 'a' = 'a'
         • This will result in a successful login, creating a security problem
      2. SQL injection problems can be solved by executing SQL statements using PreparedStatement objects
      3. Using the step
         1. Is the parameter used when defining SQL statements? As a placeholder. Such as:String sql = "select * from user where username = ? and password = ?"
         2. Get the object that executes the precompiled SQL statement:connection.preparedStatement(sql)
         3. For? The assignment
            • Preparedstatement. setXxx(parameter 1, parameter 2)
              • Parameter 1:? The location number of, starting with 1
              • Parameter 2:? The value of the
         4. Execute SQL statement
            • preparedStatement.executeUpdate()
            • preparedStatement.executeQuery()