I. concept
1. In order to enable the program to operate the database and the tables in the database, each database will provide a set of drivers to connect and operate the database, and the drivers of each database are different. For example, MySQL database is driven by mysql, Oracle database is driven by oracle, so if the program we write wants to change the database on any day, It's inconvenient because all the code that connects to the database has to be rewritten. SUN Company for simplification. Unified database operation, defined a set of java operating database standards or specifications, this specification is JDBC.
2.JDBC is called Java Data Base Connectivity (java Database Connectivity), which is mainly composed of interfaces. In our development process, as long as the implementation of its corresponding interface can be very connected.
3. When we develop JDBC applications, we also need to import the corresponding database driver jar packages, which are written by the database company itself.
2. Prerequisite preparation for writing JDBC applications (programs that need to connect to databases)
1. First of all, we need to determine which database instance is connected. For example, in mysql, we can create a library first, then create a new table in the library, insert some data into the table. Here, I provide a section to create a database in MySQL database, as well as the sql statements of tables and data. This is also the database and tables that are operated after connecting the database.
create database test ; /*Create a database called Test*/ use test; /*Use the database or switch to it*/ create table book ( id int primary key auto_increment, /*Column: id, type: int, starting from 0, automatically added, Note: Primary key*/ name varchar(40) NOT NULL, /*Column: name, type: varchar, note: not empty*/ author varchar(40)NOT NULL, /*Column: author, type: varchar, note: not empty*/ prices double NOT NULL /*Column: prices, type: double, note: not empty*/ ); /*Create a new table called book*/ /*Insert data from four famous works*/ insert into book(id,name,author,prices) values (null,'Journey to the West','Wu Cheng en',25.00); insert into book(id,name,author,prices) values (null,'Water Margin','Shi Nai an',30.00); insert into book(id,name,author,prices) values (null,'The Dream of Red Mansion','Cao Xueqin',35.00); insert into book(id,name,author,prices) values (null,'Romance of the Three Kingdoms','Luo Guanzhong',40.00);
2. Create a new java project, and then import mysql driver jar package, that is, add it to the library where the program runs. Specific driver jar package can be found in the installation directory of the database, or download the corresponding database driver jar package on the Internet by ourselves.
Step Analysis of Connecting Database Operation
(1) Registered database driver
Although we just imported the driver jar package of mysql database when we built the new Java project, JBDC didn't know that there was a driver package. At this time, we need to give the driver package to JBDC to manage. We can register the driver in JDBC by using the registerDriver driver method provided by the Driver Manager tool class under the java.sql package. Actually, the registerDriver method needs a Driver object, and the Driver class itself is an interface provided by JDBC. This interface has been implemented in our driver, so we only need to write the following code to implement the registerDriver function.
import java.sql.DriverManager; //What you need to import is the interface class package DriverManager.registerDriver(new Driver());
(2) Getting (Creating) Connections to Databases
After registering the database driver, we did not connect to the database. In the past, no matter in the CMD window, when we operate the database through the visual database management tool, we need to connect to the database server first, and the java program is no exception. The java program here is equivalent to the client. Only by connecting to the database service first, can we access the data. Library operation
All interaction between client and database is accomplished through connection object, the common method of this object:
createStatement(): Create a state object that sends sql to the database.
prepareStatement(sql): Creates a pre-compiled SQL that is sent to the database
Here we can create a database connection object by using the getConnection(url,user,password) method in the DriverManager tool class, which requires three parameters to be passed in:
User: User name of database
Password: User Password
URLs: Database server addresses, different database URLs are written differently. Here I provide three main database URLs:
Oracle: jdbc:oracle:thin:@localhost:1521:sid
SqlServe: jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=sid
MySql: jdbc:mysql://localhost:3306/sid
Abbreviated form of Mysql url address: jdbc: mysql://sid
Note: The following sid is the instance name of the database (the database name used)
import java.sql.Connection; //The interface class package is imported Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root"); //Here we use a name called mysql Database, username and password are root
(3) Creating Transporter Objects
We have created a connection to the database and connected to the database, but if we want to operate the database, we need to use sql statements, and how do we use sql statements to operate the database in java programs? Here we need a transmitter object to transfer sql statements to the database for execution. As mentioned above, there is a createStatement() method in the Connection class that creates a transporter object
import java.sql.Statement; //The interface class package is imported Statement stat = conn.createStatement();
(4) Transfer sql statements to the database using the transmitter object to perform operations, and return the results with the result set
There are many ways to transfer SQL statements on java.sql.Statement: the most frequently used are
ExcuteQuery (String sql): Used to send queries to data.
ExcuteUpdate (String sql): Used to send insert, update, or delete statements to a database
execute(String sql): Used to send arbitrary SQL statements to a database
import java.sql.ResultSet; //Interface class packages that need to be imported ResultSet rs = stat.executeQuery("select * from book"); //Transfer a query statement to query book All tuple data in the table
(5) Traverse the result set and get the query object
ResultSet in Jdbc program is used to represent the execution result of Sql statement. Resultset encapsulates execution results in a form-like manner. The ResultSet object maintains a cursor pointing to a row of table data. Initially, before the first row, the cursor calls the ResultSet.next() method, which enables the cursor to point to a specific row of data and invokes the method to obtain the data of that row.
Since ResultSet is used to encapsulate execution results, the object provides get methods for obtaining data:
Get data of a specified type, such as:
getString(int index)
getString(String columnName)
ResultSet also provides a way to scroll the result set:
next(): Move to the next line
Previous(): Move to the previous line
Abslute (int row): Move to the specified row
beforeFirst(): The front of the mobile resultSet.
After Last (): Move to the end of the resultSet.
while(rs.next()) { String name = rs.getString("name"); System.out.println(name); }
(6) Close the connection (first created then closed)
After running the Jdbc program, remember to release the objects created by the program to interact with the database during running. These objects are usually ResultSet, Statement and Connection objects.
Connection object, in particular, is a very rare resource, which must be released immediately after being used up. If Connection can not be shut down timely and correctly, it will easily lead to system downtime. The principle of using Connection is to create it as late as possible and release it as early as possible.
rs.close();
stat.close();
conn.close();
Initial complete source code
1 package jdbcDemo; 2 /**************************** 3 * First Edition Connecting Database Program 4 **************************/ 5 import java.sql.Connection; 6 import java.sql.DriverManager; 7 import java.sql.ResultSet; 8 import java.sql.SQLException; 9 import java.sql.Statement; 10 //Cannot import java.sql Medium Driver Interface, import driver jar The class that implements the interface in the package, which is the only way to register the corresponding database driver 11 import com.mysql.jdbc.Driver; 12 public class JDBCTest { 13 public static void main(String[] args) throws SQLException { 14 //1.Registered database driver 15 DriverManager.registerDriver(new Driver()); 16 //2.Get the connection to the database 17 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "root"); 18 //3.Creating Transporter Objects 19 Statement stat = conn.createStatement(); 20 //4.Using Transmitter Object Transfer sql Statement to perform operations in the database and return the results with the result set 21 ResultSet rs = stat.executeQuery("select * from book"); 22 //5.Traverse the result set and get the query results 23 while(rs.next()) { 24 String name = rs.getString("name"); 25 System.out.println(name); 26 } 27 //6.Close the connection (turn on and off first) 28 rs.close(); 29 stat.close(); 30 conn.close(); 31 } 32 }
Data Table View and Running Results:
IV. Problems in initial database connection procedures
1 - The improper method of registering database driver leads to two registrations and the low universality of the program.
Looking at the source code of the Driver class, we can see the following code. From line 7, we can see that mysql registered itself once in the implementation of the Driver class, and we registered again in the program, resulting in two registrations.
When we register the driver, we need to import the Driver class already implemented in the mysql driver jar package, so that the program is bound to the specific database and the universality of the program is reduced. If we want to switch the database, we have to change the source code.
1 public class Driver extends NonRegisteringDriver implements java.sql.Driver { 2 // 3 // Register ourselves with the DriverManager 4 // 5 static { 6 try { 7 java.sql.DriverManager.registerDriver(new Driver()); 8 } catch (SQLException E) { 9 throw new RuntimeException("Can't register driver!"); 10 } 11 }
Repair methods:
Class.forname() method is used to load the Driver class already implemented in mysql into the program. Because the Driver class uses static code blocks to implement the interface, and the static code blocks can only be executed once when the class is loaded, that is to say, it ensures that the database driver will only be registered once without importing the class packages in the mysql driver.
Class.forName("com.mysql.jdbc.Driver");
2 -- Ignore exceptions that might be thrown in the program (the biggest problem)
When we execute a program, many of its method calls throw exceptions. If it throws exceptions and does not handle them accordingly (catch exceptions), the program will interrupt execution, the Statement object and the Connection object will not be closed, and we know that the Connection object, which is a very rare resource, must be released immediately after use, if Connection Action can't be shut down timely and correctly, which can easily lead to system downtime. So we need to ensure that no matter which step of the program has an exception that causes program interruption, the code of connection closure will be executed. At this time, we will think of the final block of code in exception handling. We can throw the exception up, but first try and then catch the exception, and finally execute the final generation. Code block
After the modification, we found that each close() prompted an exception to be handled, and then we tried / catch each exception directly.
Modified source code:
1 package jdbcDemo; 2 /**************************** 3 * Modified version of connection database program 4 **************************/ 5 import java.sql.Connection; 6 import java.sql.DriverManager; 7 import java.sql.ResultSet; 8 import java.sql.SQLException; 9 import java.sql.Statement; 10 11 public class JDBCTest { 12 public static void main(String[] args) { 13 Connection conn = null; 14 Statement stat = null; 15 ResultSet rs = null; 16 try { 17 //1.Registered database driver 18 Class.forName("com.mysql.jdbc.Driver"); 19 //2.Get the connection to the database 20 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "root"); 21 //3.Creating Transporter Objects 22 stat = conn.createStatement(); 23 //4.Using Transmitter Object Transfer sql Statement to perform operations in the database and return the results with the result set 24 rs = stat.executeQuery("select * from book"); 25 //5.Traverse the result set and get the query results 26 while(rs.next()) { 27 String name = rs.getString("name"); 28 System.out.println(name); 29 } 30 }catch(Exception e) { 31 e.printStackTrace(); 32 }finally { 33 //6.Close the connection (turn on and off first) 34 try { 35 rs.close(); 36 } catch (SQLException e) { 37 e.printStackTrace(); 38 } 39 try { 40 stat.close(); 41 } catch (SQLException e) { 42 e.printStackTrace(); 43 } 44 try { 45 conn.close(); 46 } catch (SQLException e) { 47 e.printStackTrace(); 48 } 49 } 50 } 51 }
5. Anomalies Neglected in Modified Programs
Abnormal problems
1. Since we declare the references of three objects at the beginning of the program and assign them to null, if an exception is thrown when the program executes the step of registering the database, then catch executes the final code block after the exception. It is found that the references to ResultSet objects, Connection objects and Statement objects are all null values. Call this pair of references. The method on the image throws a null pointer exception
2. There are also exceptions to the close () method. If we don't do the corresponding exception handling, those objects can't be shut down properly.
Solutions
1 - In order to prevent null pointer exceptions, we can first determine which objects are referred to as null, if not null, then execute exception handling code.
2 - Add a final static code block after each close() exception processing, and set the reference value of each corresponding object to null. The principle is: if the program executes to close() method and throws an exception, the final code block executes and the application value of the object is set to null. Because the object has no reference to it, it becomes a garbage object, and JVM garbage returns. The receiver reclaims the object resource and the object is closed.
The final source code after exception handling:
1 package jdbcDemo; 2 /**************************** 3 * Connecting database program without exception 4 **************************/ 5 import java.sql.Connection; 6 import java.sql.DriverManager; 7 import java.sql.ResultSet; 8 import java.sql.SQLException; 9 import java.sql.Statement; 10 11 public class JDBCTest { 12 public static void main(String[] args) { 13 Connection conn = null; 14 Statement stat = null; 15 ResultSet rs = null; 16 try { 17 //1.Registered database driver 18 Class.forName("com.mysql.jdbc.Driver"); 19 //2.Get the connection to the database 20 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "root"); 21 //3.Creating Transporter Objects 22 stat = conn.createStatement(); 23 //4.Using Transmitter Object Transfer sql Statement to perform operations in the database and return the results with the result set 24 rs = stat.executeQuery("select * from book"); 25 //5.Traverse the result set and get the query results 26 while(rs.next()) { 27 String name = rs.getString("name"); 28 System.out.println(name); 29 } 30 }catch(Exception e) { 31 e.printStackTrace(); 32 }finally { 33 //6.Close the connection (turn on and off first) 34 if(rs != null) { 35 try { 36 rs.close(); 37 } catch (SQLException e) { 38 e.printStackTrace(); 39 } finally { 40 rs = null; 41 } 42 } 43 if(stat != null) { 44 try { 45 stat.close(); 46 } catch (SQLException e) { 47 e.printStackTrace(); 48 }finally { 49 stat = null; 50 } 51 } 52 if(conn != null) { 53 try { 54 conn.close(); 55 } catch (SQLException e) { 56 e.printStackTrace(); 57 }finally { 58 conn = null; 59 } 60 } 61 } //--finally 62 } //--main 63 }//--class
Conclusion:
Here I just handle all the exceptions, but the universality of the program is not very high, because the Driver class name, URL, user and password used to connect the database are written in the program, we can actually write in a text file, through reading the file to get the unique connection parameters of each database.
In addition, in the actual development process, the program code connecting to the database is usually written in a tool class. When we want to operate on the data in the database, we just need to call the tool class, instead of writing so much code every time.
Next, I will update how to save the connection information of the database in the text file, and then read the file to realize the operation of connecting the database. At the same time, I will modify the program into a tool class for connecting the database.