Detailed explanation of JDBC connection database in Java

1、 Concept

1. In order to enable the program to operate the database and operate 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 uses MySQL driver and Oracle database uses Oracle driver. In this way, if the program we write wants to change the database one day, That would be very inconvenient, because all the code connecting to the database should be written again. Sun company in order to simplify. Unified database operation defines a set of standards or specifications for Java database operation, which is JDBC.

2. The full name of JDBC is java data base connectivity, which is mainly composed of interfaces. In the development process, we can connect as long as we implement its corresponding interfaces

3. When developing JDBC applications, we also need to import the corresponding database driver jar packages, which are written by the database company itself.

2、 Preparation for writing JDBC applications (programs that need to connect to the database)

1. First, determine which database instance is connected. For example, in mysql, we can first create a library, then create a new table in the library, and insert some data into the table. Here I provide a SQL statement to create a library in MySQL database and the tables and data. This is also the library and tables to operate after connecting to the database.

2. Create a new Java project, and then import the driver jar package of MySQL, that is, add it to the library where the program runs. The specific driver jar package can be found in the database installation directory, or download the corresponding database driver jar package on the Internet

3、 Step analysis of database connection operation

(1) Register database driver

Although we just imported the driver jar package of MySQL database when creating a new Java project, jbdc does not know that there is a driver package here. At this time, we need to hand over the driver package to jbdc for management. We can use Java The registerdriver (driver driver) method provided by the drivermanager tool class under the SQL package is used to register the data driver in JDBC. The registerdriver (driver) method requires a driver object, and the driver class itself is an interface provided by JDBC, which has been implemented in our driver, Therefore, we only need to write the following code to realize the function of registering database driver

(2) Get (create) a connection to the database

After registering the database driver, we did not connect to the database. In the past, when we operated the database through the visual database management tool in the CMD window, we needed to connect to the database server first, and the Java program connected to the database is no exception. The Java program here is equivalent to the client, only by connecting to the database service first, To operate on the database

All interactions between the client and the database are completed through the connection object. The common methods of this object are as follows:

Createstatement(): creates a statement object that sends SQL to the database.

Preparestatement (SQL): creates a to send precompiled SQL to the database

Here, we can create a database connection object through the getconnection (URL, user, password) method in the drivermanager tool class. This method needs to pass in three parameters:

User: user name of the database

Password: user password

URL: database server address. Different databases have different URL writing methods. Here I provide three main database URL writing methods:

Oracle writing method: JDBC: Oracle: thin: @ localhost: 1521: sid

Sqlserve writing method: JDBC: Microsoft: sqlserver://localhost:1433 ; DatabaseName=sid

MySQL writing method: JDBC: MySQL: / / localhost: 3306 / SID

Short form of URL address of MySQL: JDBC: MySQL: / / / SID

Note: the following sid is the instance name of the database (the database name used)

(3) Create transport object

We have created the database connection above and connected to the database. However, if we want to operate the database, we need to use SQL statements. How can we use SQL statements in Java programs to operate the database? Here, we need a transmitter object to transmit SQL statements to the database for execution. As mentioned above, there is a createstatement () method in the connection class to create a transmitter object

(4) Use the transmitter object to transfer SQL statements to the database to perform operations, and return the results with the result set

java. sql. There are many methods to transfer SQL statements in the statement: the most used is

ExecuteQuery (string SQL): used to send query statements to data.

Executeupdate (string SQL): used to send insert, update or delete statements to the database

Execute (string SQL): used to send any SQL statement to the database

(5) Traverse the result set and get the query object

The resultset in the JDBC program is used to represent the execution results of SQL statements. Resultset encapsulates execution results in a table like manner. The ResultSet object maintains a cursor pointing to the table data row. At the beginning, the cursor calls ResultSet. before the first row. The next () method can make the cursor point to a specific data row and call the method to obtain the data of the row.

Since the resultset is used to encapsulate the execution results, the object provides get methods for obtaining data:

Gets data of the specified type, for example:

getString(int index)

getString(String columnName)

Resultset also provides a method to scroll the result set:

Next (): move to the next line

Previous(): move to the previous line

Absolute (int row): move to the specified row

Beforefirst(): move the front of the resultset.

Afterlast(): move to the back of the resultset.

(6) Close connection (created before closed)

After the JDBC program runs, remember to release the objects created by the program to interact with the database. These objects are usually resultset, statement and connection objects.

Especially the connection object, which is a very rare resource, must be released immediately after use. If the connection cannot be closed timely and correctly, it is very easy to cause system downtime. The use principle of connection is to create as late as possible and release as early as possible.

Initial complete source code

Datasheet view and run results:

4、 Problems in initial database connection program

1 -- improper registration database driving method leads to two registrations, and the universality of the program is low

We can see the following code in the source code of the driver class. From the code in line 7, we can see that MySQL has registered itself once in the implementation of the driver class, and we have registered it again in the program, resulting in two registrations

When registering the driver, we need to import the driver class already implemented in the MySQL driver jar package, so that the program will be bound with the specific database, and the universality of the program will be reduced. If we want to switch the database, we have to change the source code

Repair method:

Use class The forname () method loads the driver class implemented in MySQL into the program. Because the driver class uses a static code block when implementing the interface, and the static code block will only be executed once when the class is loaded, that is, it ensures that the database driver will only be registered once. At the same time, it does not need to import the class package in the MySQL driver, which improves the universality of the program

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

2 -- ignoring the exceptions that may be thrown in the program (the biggest problem)

When we execute a program, many of its method calls will throw exceptions. If it throws an exception, No corresponding treatment was made (catch exception) then the program will be interrupted, and the statement object and connection object will not be closed. We know that the connection object is a very rare resource and must be released immediately after use. If the connection cannot be closed timely and correctly, it is very easy to cause system downtime. Therefore, we need to ensure that no matter which step of the program has an exception When the program is interrupted and the connection is closed, the code will be executed. At this time, we will think of the finally code block in exception handling. We can throw the exception upward, but try first, then catch the exception, and finally execute the finally code block

After modification, we find that each close () prompts for an exception to be handled. At this time, we also directly try / catch each exception

Modified source code:

5、 Ignored exception in modified program

Abnormal problem

1. Since we declared the references of three objects at the beginning of the program and assigned null values to them, if the program throws an exception when executing the registration database, catch the exception and execute the finally code block. It is found that the references of resultset object, connection object and statement object are null values, Calling a method on this object throws a null pointer exception

2. The close () method also has exceptions. If we do not handle the corresponding exceptions, those objects cannot be closed normally

terms of settlement

1 --- in order to prevent null pointer exceptions, we can first judge whether the references of which objects are null. If not, execute exception handling code

2 --- after each close() exception is handled, a finally static code block is added to set the reference value of each corresponding object to null. The principle is: if the program executes the close() method and throws an exception, the finally code block executes and sets the application value of the object to null. Since the object has no reference to it, it becomes a garbage object, The JVM garbage collector will recycle the object resource, and the object will be closed

The final source code after exception handling:

Summary:

I only handle all exceptions here, but the generality of the program is not very high, because the driver class name, URL, user and password used to connect to the database are written in the program. In fact, we can write them in a text file to obtain the unique connection parameters of each database by reading the file.

In addition, in the actual development process, the program code connecting to the database is generally written in a tool class. When we want to operate on the data in the database, we only need to call this tool class instead of writing so much code every time

Next, I will update an article on how to save the database connection information in a text file, and then read this file to realize the operation of connecting to the database. At the same time, I will also modify this program as a tool class for connecting to the database

The above is the whole content of this article. I hope the content of this article can bring some help to your study or work. At the same time, I also hope to support a lot of programming tips!