JDBC-00-Notes

JDBC-series-notes

One, JDBC quick start

1. The concept of jdbc

  • JDBC (Java DataBase Connectivity, java database connection) is a Java API used to execute SQL statements, which can provide unified access to a variety of relational databases. It is composed of a set of classes and interfaces written in Java language.

2. The essence of jdbc

  • In fact, it is a set of specifications (interfaces) officially provided by java. Used to help developers quickly realize the connection of different relational databases!

3.jdbc quick start program

Import the jar package

Register the driver-Driver

Class.forName("com.mysql.jdbc.Driver");

Get connection-Connection

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db0", "root", "root");

Get the executor object-Statement

Statement stat = con.createStatement(); //存在注入攻击

Execute the sql statement and receive the returned result-ResultSet

String sql = "SELECT * FROM user";
ResultSet rs = stat.executeQuery(sql);

process result

while(rs.next()) {
    System.out.println(rs.getInt("id") + "\t" + rs.getString("name"));
}

Release resources

con.close();
stat.close();
rs.close();

Second, detailed explanation of each functional class of JDBC

1.DriverManager

DriverManager: drive management object

Register the driver (tell the program which database driver to use)

  • static void registerDriver(Driver driver): Register with the given driver DriverManager
  • Write code to use: Class.forName("com.mysql.jdbc.Driver");
  • By viewing the source code, it is found that there is a static code block in the com.mysql.jdbc.Driver class
static { //会随着类的加载而执行  也就是说执行Class.forName("com.mysql.jdbc.Driver"); 会注册驱动
	try {
		java.sql.DriverManager.registerDriver(new Driver());//注册驱动
	} catch (SQLException E) {
		throw new RuntimeException("Can't register driver!");
	}
}
  • Note: The driver jar package after mysql5 can omit the step of registering the driver. In the jar package, there is a java.sql.Driver configuration file with com.mysql.jdbc.Driver specified in the file

Get database connection (get the connection to the database and return the connection object)

  • static Connection getConnection(String url, String user, String password);
  • Return value: Connection database connection object
  • parameter
  • url: Specify the path of the connection. Syntax: jdbc:mysql://ip address (domain name): port number/database name [different database URLs are slightly different]
  • user: username
  • password: password

2.Connection

  • Connection: database connection object
  • Get the executor object
  • Get the general executor object: Statement createStatement();
  • Get the precompiled executor object: PreparedStatement prepareStatement(String sql);//Prevent SQL injection attacks
  • Management affairs
  • Open transaction: setAutoCommit(boolean autoCommit); If the parameter is false, the transaction is started. [The default is true]
  • Commit the transaction: commit();
  • Roll back the transaction: rollback();
  • Release resources
  • Release the database connection object immediately: void close();

3.Statement

  • Statement: the object that executes the sql statement
  • Execute DML statement: int executeUpdate(String sql);
  • Return value int: Returns the number of rows affected.
  • Parameter sql: insert, update, and delete statements can be executed.
  • Execute DQL statement: ResultSet executeQuery(String sql);
  • Return value ResultSet: encapsulate the result of the query.
  • Parameter sql: select statement can be executed.
  • Release resources
  • Release the executor object immediately: void close();

4.ResultSet

  • ResultSet: result set object
  • Determine whether there is data in the result set: boolean next();
  • Return true if there is data, and move the index down one row
  • No data returns false
  • Get the data in the result set: XXX getXxx("column name");
  • XXX represents the data type (to get a column of data, the data type of this column)
  • For example: String getString("name"); int getInt("age");
  • Release resources
  • Release the result set object immediately: void close();

Three, the learning content of the database

1. Demonstration of JDBC addition, deletion, modification and checking

2. JDBC tool class extraction

3. SQL Anti-Injection Attack (PreparedStatement)

4. Transaction management

5. Database connection pool

6, JDBC framework (JDBCTemplate)