catalogue

1. Introduction to JDBC

2. Common API components

3. JDBC usage

3.1 introducing MySQL driver

3.2 jdbc connection database programming

Programming steps

4. Common API usage

4.1 Connection interface: the object of database connection

4.2 Statement interface: used to execute SQL statements

4.3 ResultSet(): returns the result set

Question: the difference between PreparedStatement and Statement

1. Introduction to JDBC

JDBC (Java Data Base Connection), a set of API interfaces for operating the database provided in Java, is used to connect the database with Java language for data operation.

2. Common API components

• DriverManager: this class is a driver management class, which manages a series of database drivers and is used to establish connections with databases;
• Connection: the interface has all the methods to contact the database and represents the context object communicating with the database;
• Statement: the database to which the object will submit SQL;
• Resultset: the result set of SQL query statement is returned to the user through resultset;
• SQLException: this class is any error in the interaction with the database.

3. JDBC usage

3.1 introducing MySQL driver

Introducing MySQL driver dependency through maven

 <!--MySQL Driver dependency-->
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.39</version>
      </dependency>

3.2 jdbc connection database programming

public class JDBC {
    public static void main(String[] args) {
        try {
            //Load database driver MySQL - > com.mysql.jdbc.driver
            Class.forName("com.mysql.jdbc.Driver");

            //Connect to database
            Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jing", "root", "123456");
            System.out.println("Connection succeeded");

            //Get Statement results
            Statement statement = connection.createStatement();
            //Modify data
            String sql1 = "update student set Sname='susu' where SID=5 ";
            int i = statement.executeUpdate(sql1);

            //query data base
            String sql = "select * from student";
            ResultSet resultSet = statement.executeQuery(sql);

            //End processing
            while (resultSet.next()) {
                String sname = resultSet.getString("Sname");
                System.out.println(sname);
            }

            //close resource
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            System.out.println("connection failed");
            e.printStackTrace();
        }
    }
}

DriverManager Manage Connections
        When connecting to the database, the getConnection(String url,String user, String password) method returns the Connection type, where the parameters represent:

• url:jdbc:mysql://127.0.0.1:3306/jing
• < top level protocol jdbc: secondary protocol database: / / local IP address: database port / database name >
• User: user name
• Password: password

Programming steps

1. Introduce MySQL connector Java dependency package
2. Introducing MySQL driver
3. DriverManager connects to the database to get the Connection object
4. Obtain Statement object through Connection for SQL operation
5. If it is a query operation, process the result set: ResultSet
6. close resource

4. Common API usage

4.1 Connection interface: the object of database connection

Get Statement object:

PreparedStatement prepareStatement(String sql): get PreparedStatement object

Statement createStatement(): get statement object

CallableStatement prepareCall(String sql): get CallableStatement object

Called as connection.

Transaction processing:

void commit() commits the transaction

4.2 Statement interface: used to execute SQL statements

boolean execute(String sql): submit the SQL statement, return the boolean type, and submit the change operation (insert, delete, modify)

int executeUpdate(String sql): submit and execute DML language. The return value is int. the return result indicates the number of rows affecting database data

ResultSet executeQuery(String sql): execute the query operation, and the returned results are in the ResultSet

4.3 ResultSet(): returns the result set

boolean next(): judge whether there is still data. Each call obtains a row of records corresponding to the database

Question: the difference between PreparedStatement and Statement

             //Get Statement results
            Statement statement = connection.createStatement();

            //Modify data
            String sql1 = "update student set Sname='susu' where SID=5 ";
            int i = statement.executeUpdate(sql1);

            //Compare PreparedStatement
            String sql2="update student set Sname = ?  where SID = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setString(1,"susu");
            preparedStatement.setString(2,"5");

            preparedStatement.execute();

        PreparedStatement is processed by precompiling mechanism, SQL and parameters are passed respectively, and the position of parameters on SQL is through placeholder '?' Pay special attention to that the starting position of preparedStatement.setString is 1.

The above is a summary of the knowledge points in the learning process. If there are errors or questions, please correct them and exchange them~~