JDBC database operation summary _ MySQL

Summary of JDBC database use operations bitsCN.com

JDBC is a group of APIs that can execute SQL statements.

Traditional database operations require programmers to master the APIs of different databases, which is extremely inconvenient.

Therefore, java defines the JDBC standard interface and class, providing a unified way for programmers to operate databases.

The JDBC operation method is relatively simple and consists of five processes:

1. Register the database driver with DriverManager through the JDBC class library provided by the database vendor

2. use the getConnection () method provided by DriverManager to connect to the database

3. create an SQL statement object using the createStatement method of the database connection object

4. execute the SQL statement and return the result set to ResultSet.

5. read the results using the while loop

6. disable database resources

The following describes how to operate a Mysql database.

Preparations

First, we need to create a database and a simple table.

Mysql> create database person;
Query OK, 1 row affected (0.00 sec)

Mysql> use person;
Database changed
Mysql> create table student (
-> Id int,
-> Name varchar (20 ),
-> Birth year
->) Default charset = utf8;
Query OK, 0 rows affected (0.10 sec)

Insert several pieces of data into it.

Mysql> insert into student values
-> (1, 'Zhang San', 1990 ),
-> (2, 'Li Si', 1991 ),
-> (3, 'Wang Wu', 1992 );
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

A simple table is created.

Mysql> select * from student;
+ ------ + -------- + ------- +
| Id | name | birth |
+ ------ + -------- + ------- +
| 1 | Zhang San | 1990 |
| 2 | Li Si | 1991 |
| 3 | Wang Wu | 1992 |
+ ------ + -------- + ------- +
Rows in set (0.00 sec)

Next, go to the mysql official website to download the database connector package.

This package contains a document listing the basic usage instructions. For more information, see

Our operation is also based on the content in this document, and then the most important thing is to import this jar package

For ease of operation, use eclipse to import

Right-click the project and choose component path> Add external archive, as shown in the following figure.

Now we are using java to operate mysql databases.

JDBC operation Example 1: The simplest query operation

Import java. SQL .*;

Public class Demo {
// Throws all exceptions for the moment to compact the code
Public static void main (String [] args) throws Exception {
// Register the database driver
Class. forName ("com. mysql. jdbc. Driver ");
// Establish a database connection
// Parameter 1: jdbc: mysql // Address: Port/database, parameter 2: user name, parameter 3: password
Connection conn = DriverManager. getConnection
("Jdbc: mysql: // localhost: 3306/person", "root", "admin ");
// Create an SQL statement
Statement st = conn. createStatement ();
// Execute the statement and return the result
ResultSet rt = st.exe cuteQuery ("show tables ");
// Cyclically retrieve the result
While (rt. next ()){
// Obtain fields
System. out. println (rt. getString ("Tables_in_person "));
}
// Close the resource.
Rt. close ();
St. close ();
Conn. close ();
}
}

Running result: student

In this way, you can run the show tables statement to query the number of tables in the current database.

The rt. getString () method is used to obtain the field.

The method for disabling resources is the opposite.

However, the above operation method is not flexible and not rigorous.

Instance 2: Optimized query operations

Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. ResultSet;
Import java. SQL. SQLException;
Import java. SQL. Statement;

Public class Demo {
Public static void main (String [] args ){
String url = "jdbc: mysql: // localhost: 3306/person ";
String user = "root ";
String pwd = "admin ";
String SQL = "select * from student ";

Connection conn = null;
Statement st = null;
ResultSet rs = null;
Try {
Class. forName ("com. mysql. jdbc. Driver ");
Conn = DriverManager. getConnection (url, user, pwd );
St = conn. createStatement ();
// Execute the query statement. you can also execute () to execute any SQL statement.
Rs = st.exe cuteQuery (SQL );
While (rs. next ()){
System. out. println (rs. getObject (1) + "" +
Rs. getObject (2) + "" + rs. getInt ("birth "));
}
// Capture exceptions separately
} Catch (ClassNotFoundException e ){
E. printStackTrace ();
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
Try {
// Determine whether a resource exists
If (rs! = Null ){
Rs. close ();
// If the displayed setting is null, gc recycle is prompted.
Rs = null;
}
If (st! = Null ){
St. close ();
St = null;
}
If (conn! = Null ){
Conn. close ();
Conn = null;
}
} Catch (SQLException e ){
E. printStackTrace ();
}
}
}
}

Running result:

Here the exception is captured separately, and all the relevant strings are defined using variables.

Note that the getInt () method in the data is retrieved in the next loop. you must know the type and field to retrieve the data.

If you do not know, you can use getObject (1) to retrieve the first column, getObject (2) to retrieve the second column, and so on.

Instance 3: insert custom variables to the database

Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. SQLException;
Import java. SQL. Statement;

Public class Demo {
Public static void main (String [] args ){
// Parameter check
If (args. length! = 3 ){
System. out. println ("The parameter format is incorrect ");
System. exit (0 );
}
String id = args [0];
String name = args [1];
String birth = args [2];
String SQL = "insert into student values (" + id + ", '" + name +
"'," + "'" + Birth + "')";
System. out. println (SQL );

String url = "jdbc: mysql: // localhost: 3306/person ";
String user = "root ";
String pwd = "admin ";

Connection conn = null;
Statement st = null;
Try {
Class. forName ("com. mysql. jdbc. Driver ");
Conn = DriverManager. getConnection (url, user, pwd );
St = conn. createStatement ();
// Note that the excuteUpdate () method is executed.
St.exe cuteUpdate (SQL );
// Capture exceptions separately
} Catch (ClassNotFoundException e ){
E. printStackTrace ();
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
Try {
If (st! = Null ){
St. close ();
St = null;
}
If (conn! = Null ){
Conn. close ();
Conn = null;
}
} Catch (SQLException e ){
E. printStackTrace ();
}
}
}
}

Running result:

The independent variables need to be set for running here, right-click in the window --> Running mode --> running configuration

Then write 4 susan 1993 in the independent variable. I didn't write any Chinese characters because of garbled characters.

It should be noted that executing the inserted SQL statement is more difficult to write. it is better to print out the SQL statement to check

Example 4: PreparedStatement application

From the Demo above, we can see that the SQL operation is quite inconvenient when inserting data.

The PreparedStatement object can be used to simplify the creation of SQL statements.

Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. PreparedStatement;
Import java. SQL. SQLException;

Public class Demo {

Public static void main (String [] args ){
If (args. length! = 3 ){
System. out. println ("The parameter format is incorrect ");
System. exit (0 );
}
String id = args [0];
String name = args [1];
String birth = args [2];

String url = "jdbc: mysql: // localhost: 3306/person ";
String user = "root ";
String pwd = "admin ";

Connection conn = null;
// Declare PreparedStatement object reference
PreparedStatement pst = null;
Try {
Class. forName ("com. mysql. jdbc. Driver ");
Conn = DriverManager. getConnection (url, user, pwd );
// Use? Replace variable
Pst = conn. prepareStatement ("insert into student values (?,?,?) ");
// Set the variable for the location of the specified parameter
Pst. setString (1, id );
Pst. setString (2, name );
Pst. setString (3, birth );
Pst.exe cuteUpdate ();
} Catch (ClassNotFoundException e ){
E. printStackTrace ();
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
Try {
If (pst! = Null ){
Pst. close ();
Pst = null;
}
If (conn! = Null ){
Conn. close ();
Conn = null;
}
} Catch (SQLException e ){
E. printStackTrace ();
}
}
}
}

Running result:

Instance 5: Batch processing

Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. SQLException;
Import java. SQL. Statement;

Public class Demo {

Public static void main (String [] args ){

String url = "jdbc: mysql: // localhost: 3306/person ";
String user = "root ";
String pwd = "admin ";

Connection conn = null;
Statement st = null;
Try {
Class. forName ("com. mysql. jdbc. Driver ");
Conn = DriverManager. getConnection (url, user, pwd );
St = conn. createStatement ();
// Add batch processing
St. addBatch ("insert into student values (6, 'Jerry ', '000000 ')");
St. addBatch ("insert into student values (7, 'Greg ', '123 ')");
St. addBatch ("insert into student values (8, 'ry', '123 ')");
// Execute batch processing
St.exe cuteBatch ();
} Catch (ClassNotFoundException e ){
E. printStackTrace ();
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
Try {
If (st! = Null ){
St. close ();
St = null;
}
If (conn! = Null ){
Conn. close ();
Conn = null;
}
} Catch (SQLException e ){
E. printStackTrace ();
}
}
}
}

Running result:

Batch processing is relatively simple. you only need to create a Statement object and add batch processing one by one.

Finally, use the executeBatch () method for batch processing.

In addition, the PreparedStatement object can also be processed in batches.

PreparedStatement ps = conn. prepareStatement ("insert into student values (?,?,?) ");
Ps. setInt (1, 8 );
Ps. setString (2, "GG ");
Ps. setString (3, "1996 ");
Ps. addBatch ();
Ps.exe cuteBatch ();

Instance 6: Transaction processing

Transaction processing requires SQL to update the database in units and ensure consistency.

For example, in the bank's transfer business, after one party transfers the funds, the other party adds

If an exception occurs, all operations will be rolled back.

Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. SQLException;
Import java. SQL. Statement;

Public class Demo {

Public static void main (String [] args ){

String url = "jdbc: mysql: // localhost: 3306/person ";
String user = "root ";
String pwd = "admin ";

Connection conn = null;
Statement st = null;
Try {
Class. forName ("com. mysql. jdbc. Driver ");
Conn = DriverManager. getConnection (url, user, pwd );
// Cancel automatic submission
Conn. setAutoCommit (false );
St = conn. createStatement ();
St. addBatch ("insert into student values (6, 'Jerry ', '000000 ')");
St. addBatch ("insert into student values (7, 'Greg ', '123 ')");
St. addBatch ("insert into student values (8, 'ry', '123 ')");
St.exe cuteBatch ();
// Set automatic submission after submission
Conn. commit ();
Conn. setAutoCommit (true );
} Catch (ClassNotFoundException e ){
E. printStackTrace ();

} Catch (SQLException e ){
E. printStackTrace ();

If (conn! = Null ){
Try {
// If an exception occurs, roll back the operation and set automatic submission
Conn. rollback ();
Conn. setAutoCommit (true );
} Catch (SQLException e1 ){
E1.printStackTrace ();
}
}
} Finally {
Try {
If (st! = Null ){
St. close ();
St = null;
}
If (conn! = Null ){
Conn. close ();
Conn = null;
}
} Catch (SQLException e ){
E. printStackTrace ();
}
}
}
}

Running result:

BitsCN.com