Use of JDBC based on Java review
- 2020-04-01 01:51:08
- OfStack
Although we generally use ORM frameworks to replace traditional JDBC, such as Hibernate or iBatis, in the actual development process, JDBC is the foundation for data access in Java, and mastering it is helpful for us to understand Java's data manipulation process.
The full name for JDBC is Java Database Connectivity.
JDBC database operation process:
The & # 8226; Connect to database
The & # 8226; Send data requests, known as traditional CRUD instructions
The & # 8226; Returns the result set of the operation
Common JDBC objects include:
The & # 8226; ConnectionManager
The & # 8226; The Connection
The & # 8226; The Statement
The & # 8226; The CallableStatement
The & # 8226; PreparedStatement
The & # 8226; The ResultSet
The & # 8226; The SavePoint
A simple example
Let's take a look at a simple example that USES the Derby database that comes with the JDK to create a table, insert some records, and return them:
A simple JDBC The sample
private static void test1() throws SQLException
{
String driver = "org.apache.derby.jdbc.EmbeddedDriver";
String dbURL = "jdbc:derby:EmbeddedDB;create=true";
Connection con = null;
Statement st = null;
try
{
Class.forName(driver);
con = DriverManager.getConnection(dbURL);
st = con.createStatement();
st.execute("create table foo(ID INT NOT NULL, NAME VARCHAR(30))");
st.executeUpdate("insert into foo(ID,NAME) values(1, 'Zhang San')");
ResultSet rs = st.executeQuery("select ID,NAME from foo");
while(rs.next())
{
int id = rs.getInt("ID");
String name = rs.getString("NAME");
System.out.println("ID=" + id + "; NAME=" + name);
}
}
catch(Exception ex)
{
ex.printStackTrace();
}
finally
{
if (st != null) st.close();
if (con != null) con.close();
}
}
How do I establish a database connection
In the sample code above, the section that establishes a database connection is as follows:
String driver = "org.apache.derby.jdbc.EmbeddedDriver";
String dbURL = "jdbc:derby:EmbeddedDB;create=true";
Class.forName(driver);
con = DriverManager.getConnection(dbURL);
The process of establishing a database connection can be divided into two steps:
1) load the database driver, that is, the driver mentioned above and class.forname (dirver)
2) to locate the database connection string, namely dbURL and DriverManager. GetConnection (dbURL)
Different databases have different dirver and dbURL, but the way to load the driver and establish the connection is the same, that is, you just need to modify the above driver and dbURL values.
Automatically loads the database driver
If we use class.forname (...) every time we make a connection To manually load the database driver, this will be very troublesome, we can through the configuration file, to save the database driver information.
We can add the following file to the classpath, which is the compiled.class directory:
META-INFservicesjava.sql.Driver
The corresponding content is the full path of the JDBC driver, which is the value of the above driver variable:
org.apache.derby.jdbc.EmbeddedDriver
Next, we don't need to display class.forname (...) in the program. When our database changes, we only need to modify this file. For example, when our database changes from Derby to MySQL, we only need to change the above configuration to:
com.mysql.jdbc.Driver
However, it is important to note that the full path of the JDBC driver is configured and does not contain jar file information, so we still need to manually place the jar file containing the driver into the classpath of the application.
Basic operations in JDBC
For database operations, CRUD operations should be the most common operation, that is, we often say add, delete, look, change.
JDBC does this using a Statement and a ResultSet.
How do you implement CRUD
Here is an example of implementing CRUD:
JDBC Implement the basic CRUD The sample
private static void insertTest() throws SQLException
{
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
st.execute("insert into user(ID,NAME) values(1, 'Zhang San')");
st.execute("insert into user(ID,NAME) values(2, 'Li Si')");
st.execute("insert into user(ID,NAME) values(3, 'Wang Wu')");
System.out.println("=====insert test=====");
showUser(st);
st.close();
con.close();
}
private static void deleteTest() throws SQLException
{
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
st.execute("delete from user where ID=3");
System.out.println("=====delete test=====");
showUser(st);
st.close();
con.close();
}
private static void updateTest() throws SQLException
{
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
st.executeUpdate("update user set NAME='TEST' where ID=2");
System.out.println("=====update test=====");
showUser(st);
st.close();
con.close();
}
private static void showUser(Statement st) throws SQLException
{
ResultSet rs = st.executeQuery("select ID, NAME from user");
while(rs.next())
{
int id = rs.getInt("ID");
String name = rs.getString("NAME");
System.out.println("ID:" + id + "; NAME=" + name);
}
rs.close();
}
We call the above test methods in sequence:
insertTest();
deleteTest();
updateTest();
The implementation results are as follows:
=====insert test=====
ID:1; NAME=Zhang San
ID:2; NAME=Li Si
ID:3; NAME=Wang Wu
=====delete test=====
ID:1; NAME=Zhang San
ID:2; NAME=Li Si
=====update test=====
ID:1; NAME=Zhang San
ID:2; NAME=TEST
The showUser method in the above code prints out all the records in the user table.
How do I invoke a stored procedure
Stored procedures are a common technique used in database development to improve system performance by saving compilation time. Our example USES MySQL database.
How do I call a stored procedure with no parameters
Suppose we now have a simple stored procedure that simply returns all the records in the user table as follows:
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetUser`()
BEGIN
select ID,NAME from user;
END
We can use the CallableStatement to call the stored procedure:
Invoke stored procedure example 1
private static void execStoredProcedureTest() throws SQLException
{
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
CallableStatement cst = con.prepareCall("call GetUser()");
ResultSet rs = cst.executeQuery();
while(rs.next())
{
int id = rs.getInt("ID");
String name = rs.getString("NAME");
System.out.println("ID:" + id + "; NAME=" + name);
}
rs.close();
cst.close();
con.close();
}
Its execution results are as follows:
ID:1; NAME=Zhang San
ID:2; NAME=TEST
How do I call a stored procedure with parameters
The parameters in the stored procedure of MySQL are divided into three types: in/out/inout. We can regard in as input parameter and out as output parameter. JDBC sets the parameters of these two types in different ways:
1) in, JDBC is set in a manner similar to cst.set(1, 10)
2) out, JDBC use similar to CST. RegisterOutParameter (2, types.varchar); The way to set
Let's take a look at an example of an in parameter. Suppose we want to return user information with a specific ID. The stored procedure is as follows:
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetUserByID`(in id int)
BEGIN
set @sqlstr=concat('select * from user where ID=', id);
prepare psmt from @sqlstr;
execute psmt;
END
The Java calling code is as follows:
JDBC Calling stored procedure example 2
private static void execStoredProcedureTest2(int id) throws SQLException
{
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
CallableStatement cst = con.prepareCall("call GetUserByID(?)");
cst.setInt(1, id);
ResultSet rs = cst.executeQuery();
while(rs.next())
{
String name = rs.getString("NAME");
System.out.println("ID:" + id + "; NAME=" + name);
}
rs.close();
cst.close();
con.close();
}
We execute the following statement:
execStoredProcedureTest2(1);
The results are as follows:
ID:1; NAME=Zhang San
For arguments of type out, the invocation is similar and will not be repeated.
Gets the metadata information for the database and the result set
In JDBC, we can not only manipulate the data, but also obtain metadata information about the database and the result set, such as the name of the database, driver information, and table information. Column information for the result set, etc.
Gets the metadata information for the database
We can getMetaData information about the database through the connection.getmetadata method, which is of type DatabaseMetaData.
Gets the metadata information for the database
private static void test1() throws SQLException
{
String dbURL = "jdbc:mysql://localhost/mysql";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
DatabaseMetaData dbmd = con.getMetaData();
System.out.println(" Database: " + dbmd.getDatabaseProductName() + " " + dbmd.getDatabaseProductVersion());
System.out.println(" Driver: " + dbmd.getDriverName() + " " + dbmd.getDriverVersion());
ResultSet rs = dbmd.getTables(null, null, null, null);
System.out.println(String.format("|%-26s|%-9s|%-9s|%-9s|", " The name of the table "," Table categories "," Table type "," Table schema "));
while(rs.next())
{
System.out.println(String.format("|%-25s|%-10s|%-10s|%-10s|",
rs.getString("TABLE_NAME"),rs.getString("TABLE_CAT"),
rs.getString("TABLE_TYPE"), rs.getString("TABLE_SCHEM")));
}
}
The database we use here is the default database that comes with MySQL: MySQL, which records some information from the entire database server. The above code execution results are as follows:
Database: MySQL 5.5.28
Driver: MySQL-AB JDBC Driver mysql-connector-java-5.0.4 ( $Date: 2006-10-19 17:47:48 +0200 (Thu, 19 Oct 2006) $, $Revision: 5908 $ )
| The name of the table | Table categories | Table type | Table schema |
|columns_priv |mysql |TABLE |null |
|db |mysql |TABLE |null |
|event |mysql |TABLE |null |
|func |mysql |TABLE |null |
.
Due to the large number of tables in mysql, the above results are only partially truncated.
Gets the metadata information for the result set
We can getMetaData information about the resultset by using the resultset.getmetadata method, which is of type ResultSetMetaData.
Gets the metadata information for the result set
private static void test2() throws SQLException
{
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select ID, NAME from user");
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++)
{
System.out.println("Column Name:" + rsmd.getColumnName(i) + "; Column Type:" + rsmd.getColumnTypeName(i));
}
}
Its execution results are as follows:
Column Name:ID; Column Type:INTEGER UNSIGNED
Column Name:NAME; Column Type:VARCHAR
As you can see, it returns the name and type of each column in the class result set.
Operations based on a ResultSet
When we need to modify the database, we can do it with a ResultSet in addition to the Statement above.
Note that in this case, when we define the Statement, we need to add parameters.
The Statement constructor can contain three arguments:
The & # 8226; ResultSetType, whose values include: resultset.type_forward_only, resultset.type_scroll_insensitive, or resultset.scroll_sensitive. By default, the value of this parameter is resultset.type_forward_only.
The & # 8226; ResultSetConcurrency, whose values include: resultset.concur_read_only or resultset.concur_updatable, whose default value is resultset.concur_read_only.
The & # 8226; ResultSetHoldability, whose values include: resultset.hold_cursors_over_commit or resultset.close_cursors_at_commit.
In order for the ResultSet to operate on the data, we need:
The & # 8226; Set the resultSetType to resultset.type_scroll_sensitive.
The & # 8226; Set resultSetConcurrency to resultset.concur_updatable.
In the process of adjusting the data through a ResultSet, the following methods may be called:
The & # 8226; The resultset. Last ()
The & # 8226; The resultset. First ()
The & # 8226; The resultset. MoveToInsertRow ()
The & # 8226; The resultset. Absolute ()
The & # 8226; The resultset. Setxxx ()
The & # 8226; The resultset. The updateRow ()
The & # 8226; The resultset. InsertRow ()
The following is an example of adding, deleting and changing data through a ResultSet:
through ResultSet Add, delete and change the data
private static void getResultCount() throws SQLException
{
System.out.println("=====Result Count=====");
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
ResultSet rs = st.executeQuery("select * from user");
rs.last();
System.out.println(" The number of bars that return the result: "+ rs.getRow());
rs.first();
rs.close();
st.close();
con.close();
}
private static void insertDataToResultSet() throws SQLException
{
System.out.println("=====Insert=====");
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery("select ID,NAME from user");
rs.moveToInsertRow();
rs.updateInt(1, 4);
rs.updateString(2, "Xiao Ming");
rs.insertRow();
showUser(st);
rs.close();
st.close();
con.close();
}
private static void updateDataToResultSet() throws SQLException
{
System.out.println("=====Update=====");
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery("select * from user");
rs.last();
int count = rs.getRow();
rs.first();
rs.absolute(count);
rs.updateString(2, "Xiao Qiang");
rs.updateRow();
showUser(st);
rs.close();
st.close();
con.close();
}
private static void delDataFromResultSet() throws SQLException
{
System.out.println("=====Delete=====");
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
ResultSet rs = st.executeQuery("select * from user");
rs.last();
int count = rs.getRow();
rs.first();
rs.absolute(count);
rs.deleteRow();
showUser(st);
rs.close();
st.close();
con.close();
}
Invoke the above methods respectively:
getResultCount();
insertDataToResultSet();
updateDataToResultSet();
delDataFromResultSet();
The implementation results are as follows:
=====Result Count=====
The number of bars that return the result: 2
=====Insert=====
ID:1; NAME=Zhang San
ID:2; NAME=TEST
ID:4; NAME=Xiao Ming
=====Update=====
ID:1; NAME=Zhang San
ID:2; NAME=TEST
ID:4; NAME=Xiao Qiang
=====Delete=====
ID:1; NAME=Zhang San
ID:2; NAME=TEST
You can see that we inserted, updated, and deleted records with ID 4.
Preprocessing and batch processing
Preprocessing and batch processing are both ways to improve system performance, either by using the database's caching mechanism or by using the database to execute multiple statements at once.
pretreatment
After the Statement is received by the database server, it will usually parse the Statement, analyze whether there is any syntax error, and customize the optimal execution plan, which may reduce the performance of the system. The typical database server does this, designing a caching mechanism so that when the database receives an instruction, if it's already in the cache, it's not parsed, it's run.
The same instruction here means that the SQL statement is exactly the same, including case.
JDBC USES PreparedStatement for preprocessing:
Preprocessing example
private static void test1() throws SQLException
{
System.out.println("=====Insert a single record by PreparedStatement=====");
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
PreparedStatement pst = con.prepareStatement("insert into user(id,name) values(?,?)");
pst.setInt(1, 5);
pst.setString(2, "Lei Feng");
pst.executeUpdate();
showUser(pst);
pst.close();
con.close();
}
The implementation results are as follows:
=====Insert a single record by PreparedStatement=====
ID:1; NAME=Zhang San
ID:2; NAME=TEST
ID:5; NAME=Lei Feng
The batch
Batch processing improves performance by taking advantage of the database's ability to execute multiple statements at once to avoid the performance penalty of multiple connections.
Batch adds instructions using Statement's addBatch and executeBatch method to execute multiple instructions at once:
Batch example
private static void test2() throws SQLException
{
System.out.println("=====Insert multiple records by Statement & Batch=====");
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
st.addBatch("insert into user(id,name) values(6,'Xiao Zhang')");
st.addBatch("insert into user(id,name) values(7,'Xiao Liu')");
st.addBatch("insert into user(id,name) values(8,'Xiao Zhao')");
st.executeBatch();
showUser(st);
st.close();
con.close();
}
The implementation results are as follows:
=====Insert multiple records by Statement & Batch=====
ID:1; NAME=Zhang San
ID:2; NAME=TEST
ID:5; NAME=Lei Feng
ID:6; NAME=Xiao Zhang
ID:7; NAME=Xiao Liu
ID:8; NAME=Xiao Zhao
Preprocessing is combined with batch processing
We can combine preprocessing with batch processing and execute multiple statements at once by using the database's caching mechanism:
Example of a combination of preprocessing and batch processing
private static void test3() throws SQLException
{
System.out.println("=====Insert multiple records by PreparedStatement & Batch=====");
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
PreparedStatement pst = con.prepareStatement("insert into user(id,name) values(?,?)");
pst.setInt(1, 9);
pst.setString(2, "Xiao Zhang");
pst.addBatch();
pst.setInt(1, 10);
pst.setString(2, "Xiao Liu");
pst.addBatch();
pst.setInt(1, 11);
pst.setString(2, "Xiao Zhao");
pst.addBatch();
pst.executeBatch();
showUser(pst);
pst.close();
con.close();
}
The implementation results are as follows:
=====Insert multiple records by PreparedStatement & Batch=====
ID:1; NAME=Zhang San
ID:2; NAME=TEST
ID:5; NAME=Lei Feng
ID:9; NAME=Xiao Zhang
ID:10; NAME=Xiao Liu
ID:11; NAME=Xiao Zhao
Database transaction
Transactions are an unavoidable topic when it comes to database development. JDBC is automatically committed at every step by default. We can force the auto commit to be turned off by setting connection.setAutoCommit(false) and then commit and rollback transactions through connection.com MIT () and connection.rollback().
Simple database transactions
Here is an example of a simple database transaction:
A simple example of a database transaction
private static void transactionTest1() throws SQLException
{
System.out.println("=====Simple Transaction test=====");
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
try
{
con.setAutoCommit(false);
st.executeUpdate("insert into user(id,name) values(12, 'Xiao Li')");
con.commit();
}
catch(Exception ex)
{
ex.printStackTrace();
con.rollback();
}
finally
{
con.setAutoCommit(true);
showUser(st);
if (st != null) st.close();
if (con != null) con.close();
}
}
By executing the above method twice in a row, we can get the following results:
=====Simple Transaction test=====
ID:1; NAME=Zhang San
ID:2; NAME=TEST
ID:5; NAME=Lei Feng
ID:12; NAME=Xiao Li
=====Simple Transaction test=====
ID:1; NAME=Zhang San
ID:2; NAME=TEST
ID:5; NAME=Lei Feng
ID:12; NAME=Xiao Li
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '12' for key 'PRIMARY'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3170)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1316)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1235)
at sample.jdbc.mysql.ResultSetSample.transactionTest1(ResultSetSample.java:154)
at sample.jdbc.mysql.ResultSetSample.main(ResultSetSample.java:17)
As you can see, on the first invocation, the operation succeeds, the transaction commits, and a record is inserted into the user table; On the second invocation, a primary key collision exception occurs and the transaction is rolled back.
A transaction with SavePoint
When we have multiple processes in our transaction operations, we sometimes want some operations to be committed when they are complete to avoid a rollback of the entire transaction. JDBC USES SavePoint to do this.
with SavePoint Transaction example of
private static void transactionTest2() throws SQLException
{
System.out.println("=====Simple Transaction test=====");
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
Savepoint svpt = null;
try
{
con.setAutoCommit(false);
st.executeUpdate("insert into user(id,name) values(13, 'Xiao Li')");
st.executeUpdate("insert into user(id,name) values(14, 'Xiao Wang')");
svpt = con.setSavepoint("roll back to here");
st.executeUpdate("insert into user(id,name) values(15, 'Xiao Zhao')");
st.executeUpdate("insert into user(id,name) values(13, 'Xiao Li')");
con.commit();
}
catch(Exception ex)
{
ex.printStackTrace();
con.rollback(svpt);
}
finally
{
con.setAutoCommit(true);
showUser(st);
if (st != null) st.close();
if (con != null) con.close();
}
}
The implementation results are as follows:
=====Simple Transaction test=====
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '13' for key 'PRIMARY'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3170)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1316)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1235)
at sample.jdbc.mysql.ResultSetSample.transactionTest2(ResultSetSample.java:185)
at sample.jdbc.mysql.ResultSetSample.main(ResultSetSample.java:18)
ID:1; NAME=Zhang San
ID:2; NAME=TEST
ID:5; NAME=Lei Feng
ID:13; NAME=Xiao Li
ID:14; NAME=Xiao Wang
You can see that the final transaction reports a primary key conflict exception and the transaction rolls back, but still inserts records with ids 13 and 14 into the database.
In addition, after SavePoint is determined, the record with ID 15 is not inserted, it is rolled back by transaction.