Basic concepts of jdbc

Java database connection is a set of api interface provided by Java. It accesses different databases (mysql, oracle, sqlserver) in a consistent way

java.sql.*
java.sql.Driver driver (how to connect to the database)
java.sql.Connection connection (represents the connection channel between java program and database)
java.sql.Statement execute SQL statement
java.sql.PreparedStatement
java.sql.CallableStatement
The result set of java.sql.ResultSet represents the query results from the database
java.sql.DriverManager tool class to get Connection
java.sql.SQLException represents an exception during the execution of SQL

To program with jdbc

1) Load driver (this step can be omitted in the new version of jdbc)
2) Create Connection, create Connection object
3) Create statement object
4) Execute sql statement
5) Close release resources

 public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        1) Loading the driver (this step can be omitted in the new version of jdbc) only needs to be executed once
//        Class.forName("com.mysql.jdbc.Driver");// mysql 5.1
     Class.forName("com.mysql.cj.jdbc.Driver"); // mysql 8.8

//        2) Create Connection, create Connection object
     // jdbc:mysql: called connection protocol
     // localhost: ip address of mysql server
     // 3306: connection port number
     // test3: database name
     // serverTimezone=GMT%2B8 set the connection time zone to be consistent with the database server (added when connecting 8.0 mysql)
     String url = "jdbc:mysql://Localhost: 3306 / demo? Servertimezone = GMT% 2B8 & usessl = false "; / / connection string of database
     String username="root"; // Database user name
     String password="root"; // Password for database
     Connection conn = DriverManager.getConnection(url, username, password);

//        3) Create Statement object
     Statement stmt = conn.createStatement();

//        4) Execute sql statement (add, delete, modify or query)
//        stmt.executeUpdate(sql) / / used to execute insert, update, delete
//        stmt.executeQuery(sql) / / used to execute select
//        stmt.execute(sql)
     // The integer returned by the executeUpdate method, representing the number of record lines affected by the addition, deletion and modification
//        int i = stmt.executeUpdate("insert into dept(deptno,dname,loc) values(60, 'teaching department', 'Xi'an'), (70, 'marketing department', 'Xi'an')");
//        System.out.println("the number of affected rows is:" + i);

     // Result set object
     ResultSet rs = stmt.executeQuery("select deptno,dname,loc from dept");
     // The return value of rs.next() is a boolean indicating whether there is a next record
     while(rs.next()) {
//            Int deptno = rs.getint (1); / / parameter of getxxx method, which represents the column, starting from 1
//            String dname = rs.getString(2);
//            String loc = rs.getString(3);
         int deptno = rs.getInt("deptno");
         String dname = rs.getString("dname");
         String loc = rs.getString("loc");
         System.out.println(deptno + "\t" + dname + "\t" + loc);
     }


//        5) Close release resource, open resource first and close last
     rs.close();
     stmt.close();
     conn.close();
 }

Important interface

ResultSet

next() moves to the next record in the result set. If the next record returns true, it does not return false
getXXX(int column subscript) is used to get the data of a column in the result set, where XXX is the data type and the subscript starts from 1
getXXX(int column) is used to get the data of a column in the result set, where XXX is the data type

preparedStatement

The value in the SQL statement is required to use the? Placeholder to occupy the position, and then through a series of setXXX
Method. XXX is determined by the type of value. For example, call setString for string and setInt for integer
SetXXX? Subscript, value) subscript also starts from 1