JDBC In the end what is it? ?

JDBC(Java Database Connectivity),java Database connection .JDBC yes Java A bridge between and database , It's a specification, not an implementation ,
For execution SQL Declarative Java API. 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, Various types of databases have corresponding implementations , The code in this article is for MySQL Database implementation .

JDBC working principle

JDBC It provides a unified access mode for a variety of relational databases , Access as a vendor specific database API An advanced abstraction of , It mainly contains some general interface classes .

JDBC Access database hierarchy :

JDBC advantage :

Java Language access database operation is completely oriented to abstract interface programming

The development of database applications is not limited to the specific database manufacturers API

The portability of the program is greatly enhanced

Prerequisites for database programming

programing language , as Java,C,C++,Python etc .

database , as Oracle,MySQL,SQL Server 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 based
Java operation MySQL That is, the driver package is required . alike , To be based on Java operation Oracle Database needs Oracle Database driver package for ojdbc.

JDBC Programming steps

  Load the corresponding database JDBC Drive and initialize

Prepare database driver package , And add it to the dependency of the project ;
Import dedicated jar package ( Different databases need jar Different package ); be careful : visit MySQL The database needs to use third-party classes , These third-party classes , All compressed in one .jar In your file .

JDBC Usage specifications of common interfaces and classes
stay Java JDBC The operation of database is used in programming JDK Self contained API Unified processing , It is usually completely decoupled from the driver class of a specific database . So master Java JDBC API
( be located java.sql Bao Xia ) Can grasp Java Database programming .
1. Database connection (Connection)
Connection The interface implementation class is provided by the database , obtain Connection Objects usually have two ways : One is through DriverManager
( Driver management class ) Static method acquisition : // 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
Because it is necessary to establish a connection with the database when adding, deleting, modifying and querying the database , Therefore, you can write the connection establishment as a tool method in the project , You can call it directly when you use it : /** * Get a connection to the database *
@return Connection to a database */ public static Connection getConnection(){ Connection conn =
null; try { // Initialize driver class com.mysql.jdbc.Driver
Class.forName("com.mysql.jdbc.Driver"); conn =
DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/exam?characterEncoding=UTF-8","root",
"admin"); // This class is in
mysql-connector-java-5.0.8-bin.jar in , If you forget the guide package of the first step , Will throw ClassNotFoundException }
catch (ClassNotFoundException e) { e.printStackTrace(); }catch (SQLException e)
{ e.printStackTrace(); } return conn; } One is through DataSource( data source ) Object acquisition . It will be used in practical application
DataSource object MysqlDataSource db = new MysqlDataSource();
db.setServerName("localhost"); db.setPort(3306); db.setUser("root");
db.setPassword("123456"); db.setDatabaseName("04_03");// Database name
db.setUseSSL(false); db.setCharacterEncoding("utf-8");
db.setServerTimezone("Asia/Shanghai");// Fixed parameters , The case should be consistent
The difference between the above two methods is :

1. DriverManager Class Connection connect , It cannot be reused , Every time a resource is released after use , adopt connection.close()
All physical connections are closed .

2. DataSource
Provide connection pool support . When the connection pool is initialized, a certain number of database connections will be created , These connections can be reused , After each use of database connection , Release resource call connection.close()
All will Conncetion Connection object recycling .

Statement object ( Create operation command )

Statement After interface creation , Can execute SQL sentence , Complete the addition, deletion, modification and query of the database . among , Addition, deletion and modification only need to be changed SQL The content of the statement can be completed , However, the query is slightly complex .
Statement The main object is to SQL Statement to the database .JDBC API There are three main types in Statement object .
The most commonly used in actual development is preparedStatement object , The following is a summary of it :

stay Statement How to use string splicing in , This way has syntactic complexity , Easy to make mistakes and other shortcomings , The details are introduced in the following comparison . String splicing SQL Statements are very cumbersome , There are many mixed use of single quotation marks and double quotation marks in the middle , Error prone .
Statement s = conn.createStatement(); // prepare sql sentence // be careful : Use single quotation marks for strings ' String sql =
"insert into t_courses values(null,"+"' mathematics ')";
// stay statement How to use string splicing in , There are many problems in this way s.execute(sql);
System.out.println(" Insert statement executed successfully ");

PreparedStatement It is also used to execute sql And creation of statements Statement The difference is , Need according to sql Statement creation PreparedStatement. besides , You can also set parameters , Specify the appropriate value , instead of Statement Use string splicing like that .
/** * Add course * @param courseName Course name */ public void addCourse(String
courseName){ String sql = "insert into t_course(course_name) values(?)";
// This statement is for each IN The parameter retains a question mark (“?”) As placeholder Connection conn = null; // Connect to the database
PreparedStatement pstmt = null; // establish statement try{ conn =
DbUtil.getConnection(); pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, courseName); // Assign values to placeholders pstmt.executeUpdate(); // implement
}catch(SQLException e){ e.printStackTrace(); } finally{ DbUtil.close(pstmt);
DbUtil.close(conn); // Must be closed } }

use PreparedStatement Time , His SQL Statements no longer use string splicing , It's a placeholder .“?” It serves as a placeholder here . In addition to avoiding statement In addition to the tedious task of splicing strings , It can also improve performance . every time SQL The statements are the same ,java Class will not compile again , This can significantly improve performance .
String sql = "update t_course set course_name =? where course_id=?";
It needs to be used later PreparedStatement Interface created pstmt of set Method to assign a value to the placeholder . Be careful , The parameter index here is from 1 Started .

Statement and PreparedStatement Similarities and differences, advantages and disadvantages of

with : Both are used to hold SQL Declarative

different :PreparedStatement Need according to SQL Statement to create , It can set parameters , Specify the appropriate value , Not like Statement That's how you use string splicing .

PreparedStatement Advantages of :

1, It uses parameter settings , Good readability , Hard to remember . stay statement String splicing in , Poor readability and maintainability .

2, It has a precompiled mechanism , Performance ratio statement Faster .

3, It can effectively prevent SQL Injection attack .

Two kinds of execution SQL Method of :

executeQuery() Method returns the of a single result set after execution , Usually used for select sentence .

executeUpdate() The return value of the method is an integer , Indicates the number of rows affected , Usually used for update,insert,delete sentence .

ResultSet object ( Processing result set )

ResultSet Objects are called result sets , It represents compliance SQL All lines of the statement condition , And it passes a set getXXX Method provision Provides access to the data in these rows .
ResultSet The data in the is arranged row by row , Each row has multiple fields , And there is a record pointer , The data row indicated by the pointer is called the current data row , We can only manipulate the current data row . If we want to get a record , Just use ResultSet
of next() method , If we want to ResultSet All records in the , It should be used while loop . Its execution mechanism is similar to that in a collection iterator iterator .

Release 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();   } }

stay JDBC In the process of coding, we created Connection,ResultSet Other resources , These resources must be closed after use . Follow the principle from inside to outside in the process of closing . The closing operation is used in the adding and modifying operation , To make the code simple , Increase its reusability , Write these closed operations as a method and put them together into a tool class .
/** * Encapsulates three closing methods * @param pstmt */ public static void close(PreparedStatement
pstmt){ if(pstmt != null){ // Avoid null pointer exceptions try{ pstmt.close(); }catch(SQLException
e){ e.printStackTrace(); } } } public static void close(Connection conn){
if(conn != null){ try { conn.close(); } catch (SQLException e) { // TODO:
handle exception e.printStackTrace(); } } } public static void close(ResultSet
rs){ if (rs != null) { try { rs.close(); } catch (SQLException e) { // TODO:
handle exception e.printStackTrace(); } } }

Summary of key contents

JDBC Use steps :

1. Create database connection Connection

        DriverManager establish

        DataSource obtain

2. Create operation command Statement

        PreparedStatement

3. Use the operation command to execute SQL
// Query operation preparedStatement.executeQuery(); // newly added , modify , Delete operation
preparedStatement.executeUpdate(); 4. Processing result set ResultSet while (resultSet.next()) {
   int xxx = resultSet.getInt("xxx");    String yyy=
resultSet.getString("yyy"); ... } 5. Release 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 "); }
execute and executeUpdate Differences between

Same point : Both can perform the increase , delete , Modification and other operations .

difference :

1,execute You can execute query statements , Then pass getResult Take out the results .executeUpdate Cannot execute query statement .

2,execute return Boolean type ,true Indicates that the execution is a query statement ,false Indicates the of execution insert,delete,update etc .executeUpdate The return value of is int, Indicates how many pieces of data are affected .

Interview questions

What are the methods of database connection ? What's the difference

database Statement and PreparedStatement What's the difference? ?

 

Technology
©2019-2020 Toolsou All rights reserved,
C++ of string of compare usage Python Study notes ( one )evo Tool usage problems ——Degenerate covariance rank, Umeyama alignment is not possibleRISC-V_GD32VF103-TIMER0 timer interrupt java Array subscript variable _Java Basic grammar : array be based on stm32 Control four-wheel trolley motor drive ( one ) be based on redis Design of liking function Software engineering career planning mysql Query random data by conditions _MySQL Random query of several qualified records centos7 install RabbitMq