Objectives of this section
* Database driver
* JDBC Concept and function of
* master JDBC Operating principle of
* master JDBC Several common interfaces and classes in
* Master the database based application development process
<>1. Prerequisites for database programming
* programing language , as Java,C,C++,Python etc.
* programing language , as Java,C,C++,Python etc.
*
Database driver package ： Different databases , Different database driver packages are provided for different programming languages , as ：MySQL Provided Java Driver package for mysql-connector-java, Need to be based on Java operation MySQL The driver package is required . alike , To be based on Java operation Oracle Database requires Oracle Database driver package for ojdbc
<>2. Java Database programming for ：JDBC
JDBC, Namely Java Database Connectivity,java Database connection . Is a SQL Declarative Java API, It is
Java Database connection specification in . this API from java.sql.*,javax.sql.* Package consists of some classes and interfaces , It is Java
Developers operate the database to provide a standard API, Provides unified access to multiple relational databases .
<>3. JDBC working principle
JDBC Provides unified access to multiple relational databases , Access as a vendor specific database API A high-level abstraction of , It mainly contains some general interface classes
JDBC Access database hierarchy :
JDBC advantage ：
* Java Language access database operation completely oriented abstract interface programming
* The development of database applications is not limited to those of specific database manufacturers API
* The portability of the program is greatly enhanced
<>4. JDBC use
<>4.1 JDBC Development case
*
Prepare database driver package , And added to the project's dependencies ：
*
Establish database connection
// load JDBC Driver ： reflex , This calls initialization com.mysql.jdbc.Driver class , Load this class into JVM method
area , And execute the static method block of this class , Static properties . Class.forName("com.mysql.jdbc.Driver"); // Create database connection
Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/
test? user=root&password=root&useUnicode=true&characterEncoding=UTF-8");
//MySQL Data connected URL The parameter format is as follows ： jdbc:mysql:// server address : port / Database name ? Parameter name = Parameter value
* Create operation command （Statement） Statement statement = connection.createStatement();
* implement SQL sentence ResultSet resultSet= statement.executeQuery( "select id, sn, name,
qq_mail, classes_id from student");
* Process result set while (resultSet.next()) { int id = resultSet.getInt("id"); String sn
= resultSet.getString("sn"); String name = resultSet.getString("name"); int
classesId= resultSet.getInt("classes_id"); System.out.println(String.format(
"Student: id=%d, sn=%s, name=%s, classesId=%s", id, sn, name, classesId)); }
* Free resources （ Close result set , command , connect ） // Close result set if (resultSet != null) { try { resultSet.close();
} catch (SQLException e) { e.printStackTrace(); } } // close command if (statement != null
) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } }
// Close connection command if (connection != null) { try { connection.close(); } catch (
SQLException e) { e.printStackTrace(); } }
<>4.2 JDBC Summary of use steps
1. Create database connection Connection
2. Create operation command Statement
3. Execute with operation command SQL
4. Process result set ResultSet
5. Free resources
<>5. JDBC Common interfaces and classes
<>5.1 JDBC API
stay Java JDBC All database operations in programming use JDK Self contained API Unified processing , It is usually completely decoupled from the driver classes of a specific database . So master Java JDBC API（ be located
java.sql Under the package ） Can grasp Java Database programming .
<>5.2 Database connection Connection
Connection Interface implementation classes are provided by the database , obtain Connection Objects usually have two ways ：
* One is through DriverManager（ Drive management class ） Static method acquisition of ： // load JDBC Driver Class.forName(
"com.mysql.jdbc.Driver"); // Create database connection Connection connection = DriverManager.
getConnection(url);
* One is through DataSource（ data source ） Object acquisition . Will be used in practical application DataSource object . DataSource ds = new
MysqlDataSource(); ((MysqlDataSource) ds).setUrl(
"jdbc:mysql://localhost:3306/test"); ((MysqlDataSource) ds).setUser("root"); ((
MysqlDataSource) ds).setPassword("root"); Connection connection = ds.
getConnection();
* The difference between the above two methods is ：
*
DriverManager Class Connection connect , Can not be reused , Each time the resource is released after use , adopt connection.close() Both physical connections are closed .
*
DataSource Provides support for connection pooling . Connection pool will create a certain number of database connections during initialization , These connections are reusable , Database connection after each use , Release resource call connection.close() All will Connection Connection object recycling .Datasource The obtained database connection does not need to close the physical connection , Reset only , Reinitialize and return to the connection pool
<>5.3 Statement object
Statement The object is mainly to SQL Statement to the database .JDBC API There are three main types of Statement object .
The most commonly used in actual development is PreparedStatement object , The following is a summary of them ：
* Mainly master two kinds of execution SQL Method of ： executeQuery() Method that returns a single result set , Commonly used for select sentence
* executeUpdate() Method return value is an integer , Indicates the number of rows affected , Commonly used for update,insert,delete sentence
<>5.4 ResultSet object
ResultSet Object, which is called the result set , It represents compliance SQL All rows of statement conditions , And it passes a set getXXX Method provides access to the data in these rows .
ResultSet The data in is arranged row by row , Multiple fields per row , And has a record pointer , The data row indicated by the pointer is called the current data row , We can only operate the current data row . If we want to get a record , Is about to use ResultSet of next() method
, If we want to get ResultSet All records in , Should be used while loop .
<>6. Application cases
Technical knowledge points ：
* JDBC API of CRUD
* JDBC API Transaction control for
Functional requirements ：
* Student form
* automation 2019 level 5 Chengyaojin, a new student in the class
* Change the student's class to Chinese Department 2019 level 3 class
* Query all Chinese departments 2019 level 3 Classmates in class
* Delete the student named chengyaojin
* Transcript
* Add grade of Xu Xian ： english 80 branch ,Java65 branch , Computer Principles 76 branch , language 59 branch
* Revise Xu Xian's grade ： english 81 branch
* Delete Xu Xian's Chinese score
* Query Chinese Department 2019 level 3 Class grades
<>7. Summary of key contents
JDBC Use steps ：
1. Create database connection Connection
* DriverManager establish
* DataSource obtain
2. Create operation command Statement
* PreparedStatement
3. Execute with operation command SQL
// Query operation preparedStatement.executeQuery(); // newly added , modify , Delete operation preparedStatement.
executeUpdate();
4. Process result set ResultSet
while (resultSet.next()) { int xxx = resultSet.getInt("xxx"); String yyy=
resultSet.getString("yyy"); ... }
5. Free resources
try { if(resultSet != null){ resultSet.close(); } if(preparedStatement != null)
{ preparedStatement.close(); } if(connection != null){ connection.close(); } }
catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(" Database error "
); }
Interview Q & A ：
1. How to connect the database ? What are the differences
2. database Statement and PreparedStatement What's the difference? ?
<>8. Homework after class
Library management system
* Add the borrowing record of Diao Chan ： Bookofsongs , from 2019 year 9 month 25 day 17:50 reach 2019 year 10 month 25 day 17:50
* Query book borrowing information under computer classification
* Modifying books 《 In depth understanding Java virtual machine 》 The price of is 61.20
* delete id The largest borrowing record
<>9. Code practice
<>TestJDBC.java
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import javax.sql.
DataSource; import java.sql.Connection; import java.sql.PreparedStatement;
import java.sql.SQLException; import java.util.Scanner; public class TestJDBC {
public static void main(String[] args) throws SQLException { Scanner scanner =
new Scanner(System.in); // 1. Create a good data source DataSource dataSource = new MysqlDataSource
(); // Set the address where the database is located ((MysqlDataSource) dataSource).setURL(
"jdbc:mysql://127.0.0.1:3306/java102?characterEncoding=utf8&useSSL=false"); //
Set the user name to log in to the database ((MysqlDataSource) dataSource).setUser("root"); // This is the password for setting the login database ((
MysqlDataSource) dataSource).setPassword("940194"); // 2. Let the code establish a connection with the database server ~~
It is equivalent to reaching the rookie post station Connection connection = dataSource.getConnection(); // 2.5
Let the user input the data to be inserted through the console . System.out.println(" Please enter student ID : "); int id = scanner.nextInt();
System.out.println(" Please enter your name : "); String name = scanner.next(); // 3. Operational database .
Take inserting data as an example . // The key is to construct a SQL sentence ~ // stay JDBC Constructed in SQL, No need to bring it ; // ;
It is only used to distinguish different statements on the command line . Now it's working directly in code ~~ // String sql = "insert into student values(?
?)"; String sql = "insert into student (sn,name) values(? ?)"; // Here light is a
String Type sql Not yet , I need this String Package into one " Statement object " PreparedStatement statement =
connection.prepareStatement(sql); // Perform a replacement operation . statement.setInt(1, id); statement.
setString(2, name); System.out.println("statement: " + statement); // 4. implement SQL
, Equivalent to code scanning // SQL Inside if it is insert, update, delete, All use executeUpdate method . // SQL
Inside if it is select, Then use executeQuery method . // The return value indicates this operation , Affected A few lines . It is equivalent to entering in the console sql after ,
Number obtained ~ int ret = statement.executeUpdate(); System.out.println(ret); // 5. here
SQL Has been executed . Then you need to free up resources . statement.close(); connection.close(); } }
<>TestJDBCDelete.java
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import javax.sql.
DataSource; import java.sql.Connection; import java.sql.PreparedStatement;
import java.sql.SQLException; import java.util.Scanner; public class
TestJDBCDelete { public static void main(String[] args) throws SQLException {
// Delete records in the database ~ // Let the user enter a id, according to id To delete . // 1. create data source DataSource dataSource = new
MysqlDataSource(); ((MysqlDataSource)dataSource).setURL(
"jdbc:mysql://127.0.0.1:3306/java102?characterEncoding=utf8&useSSL=false"); ((
MysqlDataSource)dataSource).setUser("root"); ((MysqlDataSource)dataSource).
setPassword("940194"); // 2. Establish connection Connection connection = dataSource.
getConnection(); // 3. User input id Scanner scanner = new Scanner(System.in); System.
out.printf(" Please enter a to delete id: "); int id = scanner.nextInt(); // 4. Assembly sql sentence
String sql= "delete from student where id = ?"; PreparedStatement statement =
connection.prepareStatement(sql); statement.setInt(1, id); // 5. implement sql int ret
= statement.executeUpdate(); System.out.println("ret = " + ret); // 6. Reclaim and release resources
statement.close(); connection.close(); } }
<>TestJDBCUpdate.java
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import javax.sql.
DataSource; import java.sql.Connection; import java.sql.PreparedStatement;
import java.sql.SQLException; import java.util.Scanner; public class
TestJDBCUpdate { public static void main(String[] args) throws SQLException {
// according to id To change the student name . Let user input To modify id, And the corresponding modified name . // 1. create data source DataSource dataSource
= new MysqlDataSource(); ((MysqlDataSource)dataSource).setURL(
"jdbc:mysql://127.0.0.1:3306/java102?characterEncoding=utf8&useSSL=false"); ((
MysqlDataSource)dataSource).setUser("root"); ((MysqlDataSource)dataSource).
setPassword("940194"); // 2. Establish connection with database Connection connection = dataSource.
getConnection(); // 3. Input information Scanner scanner = new Scanner(System.in); System.out
.println(" Please enter the student to modify id: "); int id = scanner.nextInt(); System.out.println(
" Please enter the student name to be modified : "); String name = scanner.next(); // 4. Assembly sql String sql =
"update student set name = ? where id = ?"; PreparedStatement statement =
connection.prepareStatement(sql); statement.setString(1, name); statement.setInt
(2, id); System.out.println("statement: " + statement); // 5. implement sql int ret =
statement.executeUpdate(); System.out.println("ret = " + ret); // 6. Recycling resources
statement.close(); connection.close(); } }
<>TestJDBCSelect.java
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import javax.sql.
DataSource; import java.sql.Connection; import java.sql.PreparedStatement;
import java.sql.SQLException; import java.util.Scanner; public class
TestJDBCUpdate { public static void main(String[] args) throws SQLException {
// according to id To change the student name . Let user input To modify id, And the corresponding modified name . // 1. create data source DataSource dataSource
= new MysqlDataSource(); ((MysqlDataSource)dataSource).setURL(
"jdbc:mysql://127.0.0.1:3306/java102?characterEncoding=utf8&useSSL=false"); ((
MysqlDataSource)dataSource).setUser("root"); ((MysqlDataSource)dataSource).
setPassword("940194"); // 2. Establish connection with database Connection connection = dataSource.
getConnection(); // 3. Assembly sql String sql = "select * from student";
PreparedStatement statement= connection.prepareStatement(sql); // 4. implement sql,
For query operations , You need to use executeQuery() // The query operation did not return a int, It's a temporary table // use ResultSet Represents this table
ResultSet resultSet= statement.executeQuery();
//5. Traversal result set （ Returned temporary table ）, Get each row first , Then get several columns of this row //next Method to obtain a row of records , Move the cursor back one line at the same time
// If the traversal reaches the target result , Here next Just go back false Yes while (resultSet.next()) { // Get the columns in the current row
int id = resultSet.getInt("id"); String name = resultSet.getString("name");
System.out.println("id: "+ id +"name: " +name); } // 6. Recycling resources resultSet.close();
statement.close(); connection.close(); } }
Technology