Analysis of JDBC usage steps and main objects
1, What is JDBC
JDBC (Java Database Connectivity, referred to as JDBC) is the Java database connection (JDBC) API. It is an industry standard for database independent connection between Java programming language and various databases, SQL databases and other tabular data sources (such as spreadsheets or flat files). The JDBC API provides a call level API for SQL based database access.
2, Why use JDBC
JDBC technology allows you to use the Java programming language to develop "write once, run anywhere" functions for applications that need to access enterprise data. With drivers that support JDBC technology, you can even connect all corporate data in a heterogeneous environment.
JDBC is an application program interface used in Java language to regulate how client programs access (relational) databases. In other words, JDBC only provides standard specifications and does not provide specific implementation. The specific implementation is completed by each database manufacturer, that is, each database manufacturer implements the corresponding database driver according to JDBC specifications (that is, the class that implements the java.sql.Driver interface). When using different databases, users only need to use the driver classes provided by different manufacturers without changing the business code.
3, Use of JDBC
3.1. Complete examples of JDBC usage:
package JDBC;
import java.sql.*;
public class MainTest {
public static final String Driver="com.mysql.jdbc.Driver";
public static final String URL = "jdbc:mysql://localhost:3306/mybatis-study?useSSL=false";
public static final String USER = "root";
public static final String PASSWORD = "root";
public static void main(String[] args) throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
//1. Load driver
Class.forName("com.mysql.jdbc.Driver");
//2. Get database connection
conn = DriverManager.getConnection(URL, USER, PASSWORD);
conn.setAutoCommit(false); //The default value in JDBC is true, and transactions are automatically committed
//3. Get the object that can execute SQL (Statement\PreparedStatement object:)
stmt = conn.createStatement();
//4. Execute SQL and get the returned results
rs = stmt.executeQuery("SELECT user_name, age FROM tb_user");
conn.commit();
while (rs.next()) {
System.out.println(rs.getString("user_name") + " Age:" + rs.getInt("age"));
}
} catch (Exception e) {
e.printStackTrace();
conn.rollback();
} finally {
//5. Free resources and close connections
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
3.2 steps for using JDBC
1. Load driver
-
Three ways of driving loading
-
The first method is Class.forName("com.mysql.jdbc.Driver"), which dynamically loads the driver class through the JVM. It is common and recommended
-
We know that the purpose of Class.forName() method is to dynamically load classes. When loading Driver classes in this way, we only use Class.forName() Method allows the JVM to find and load the Driver class without other operations, but the JDBC specification stipulates that any Driver class must register itself with the DriverManger class, but there is no registration operation here. Why can it be used directly?
In fact, when the JVM loads the specified class, if there is a static code block in the class, the JVM will execute this part of the code. The static code is bound to the class. Successful class loading means that the static code block has been executed and will not go again in the future. That is, in the driver class (for example, com.mysql.jdbc.Driver) There must be a similar static code block to complete the registration operation. After checking the source code, the following code blocks are found:
static { try { java.sql.DriverManager.registerDriver(new Driver()); } catch (SQLException E) { throw new RuntimeException("Can't register driver!"); } }After JDBC 4.0, in fact, we don't need to call Class.forName to load the driver. We just need to put the jar package of the driver into the class loading path of the project, and the driver will be loaded automatically.
This automatic loading technology is called SPI, and the database driver manufacturers have also updated it. You can look at the META-INF/services directory in the jar package. There is a java.sql.Driver file, which contains the full path name of the driver. For example, the content in MySQL connector:com.mysql.jdbc.Driver com.mysql.fabric.jdbc.FabricMySQLDriver
-
So how does SPI technology load the database driver and at what stage?
static { loadInitialDrivers(); println("JDBC DriverManager initialized"); } ...... private static void loadInitialDrivers() { String drivers; try { drivers = AccessController.doPrivileged(new PrivilegedAction<String>() { public String run() { return System.getProperty("jdbc.drivers"); } }); } catch (Exception ex) { drivers = null; } AccessController.doPrivileged(new PrivilegedAction<Void>() { public Void run() { ServiceLoader<Driver> loadedDrivers = ServiceLoader.load(Driver.class); Iterator<Driver> driversIterator = loadedDrivers.iterator(); try{ while(driversIterator.hasNext()) { driversIterator.next(); } } catch(Throwable t) { // Do nothing } return null; } }); println("DriverManager.initialize: jdbc.drivers = " + drivers); if (drivers == null || drivers.equals("")) { return; } String[] driversList = drivers.split(":"); println("number of Drivers:" + driversList.length); for (String aDriver : driversList) { try { println("DriverManager.Initialize: loading " + aDriver); Class.forName(aDriver, true, ClassLoader.getSystemClassLoader()); } catch (Exception ex) { println("DriverManager.Initialize: load failed: " + ex); } } }Looking at the DriverManager class source code, we can see that the loadInitialDrivers method is invoked in the static code block, and the ServiceLoader.load(Driver.class) method is invoked in the loadInitialDrivers method. This method loads all classes in the META-INF/services/java.sql.Driver part to JVM memory, and completes the automatic loading of the drive.
This is the advantage of SPI, which can automatically load classes into JVM memory. This technology also accounts for a lot in Alibaba's dubbo framework. Interested friends can see dubbo's code or Baidu's dubbo extension mechanism. The detailed SPI mechanism is not in-depth.
-
-
The second method is DriverManager.register(new com.mysql.jdbc.Driver()), which uses DriverManager to register drivers. The corresponding lib needs to be imported during compilation. It is not recommended
- It should be noted that when analyzing the first method, we see that there are static code blocks in the driver class. In the static code block, the driver will be registered with the DriverManager once, that is to say, this method will be registered once when the new Driver is used, and then the outer layer will be registered again. Therefore, registering the driver twice will not affect the use, but it is not necessary. That is Since the static code block will be executed during the new object, it means that the direct new com.mysql.jdbc.Driver() can also achieve the effect of registering the driver.
-
The third method: System.setProperty("jdbc.driver", "com.mysql.jdbc.Driver"), multiple drivers can be loaded through system property settings
- Although it can be compiled without JDBC driver, the method parameter setting is relatively complex
The feature of this method is that it can set multiple drivers. When connecting, JDBC will search in order until it finds the first driver that can successfully connect to the specified URL.
Multiple drivers are separated by colons, such as
System.setProperty("jdbc.drivers","XXXDriver:XXXDriver:XXXDriver"); - Although it can be compiled without JDBC driver, the method parameter setting is relatively complex
-
2. Get database connection
-
As can be seen from the previous JDBC use example, the core code for obtaining database connection is the DriverManager.getConnection() method. How does JDBC distinguish multiple drivers if there are multiple drivers in the project?
- This requires the source code of DriverManager.getConnection(). The source code is as follows:
@CallerSensitive public static Connection getConnection(String url, String user, String password) throws SQLException { java.util.Properties info = new java.util.Properties(); if (user != null) { info.put("user", user); } if (password != null) { info.put("password", password); } return (getConnection(url, info, Reflection.getCallerClass())); } ...... private static Connection getConnection( String url, java.util.Properties info, Class<?> caller) throws SQLException { ClassLoader callerCL = caller != null ? caller.getClassLoader() : null; synchronized(DriverManager.class) { if (callerCL == null) { callerCL = Thread.currentThread().getContextClassLoader(); } } if(url == null) { throw new SQLException("The url cannot be null", "08001"); } println("DriverManager.getConnection(\"" + url + "\")"); SQLException reason = null; for(DriverInfo aDriver : registeredDrivers) { if(isDriverAllowed(aDriver.driver, callerCL)) { try { println(" trying " + aDriver.driver.getClass().getName()); Connection con = aDriver.driver.connect(url, info); if (con != null) { println("getConnection returning " + aDriver.driver.getClass().getName()); return (con); } } catch (SQLException ex) { if (reason == null) { reason = ex; } } } else { println(" skipping: " + aDriver.getClass().getName()); } } if (reason != null) { println("getConnection failed: " + reason); throw reason; } println("getConnection: no suitable driver found for "+ url); throw new SQLException("No suitable driver found for "+ url, "08001"); } - From the source code, we can see that the DriverManager.getConnection method will traverse all the loaded driver instances (registeredDrivers) to create a connection (call Driver.connect()). When a driver successfully creates a connection, it will return the connection (return (con)), and no other driver instances will be called at the same time.
- How does JDBC determine whether the driver has successfully established a connection? Its core lies in the connect() method in each driver implementation class. Take com.mysql.jdbc.Driver as an example, and its core source code is located at com.mysql.jdbc.NonRegisteringDriver, as follows:
public Connection connect(String url, Properties info) throws SQLException { if (url == null) { throw SQLError.createSQLException(Messages.getString("NonRegisteringDriver.1"), "08001", (ExceptionInterceptor)null); } else if (StringUtils.startsWithIgnoreCase(url, "jdbc:mysql:loadbalance://")) { return this.connectLoadBalanced(url, info); } else if (StringUtils.startsWithIgnoreCase(url, "jdbc:mysql:replication://")) { return this.connectReplicationConnection(url, info); } else { Properties props = null; if ((props = this.parseURL(url, info)) == null) { return null; } else if (!"1".equals(props.getProperty("NUM_HOSTS"))) { return this.connectFailover(url, info); } else { try { com.mysql.jdbc.Connection newConn = ConnectionImpl.getInstance(this.host(props), this.port(props), props, this.database(props), url); return newConn; } catch (SQLException var6) { throw var6; } catch (Exception var7) { SQLException sqlEx = SQLError.createSQLException(Messages.getString("NonRegisteringDriver.17") + var7.toString() + Messages.getString("NonRegisteringDriver.18"), "08001", (ExceptionInterceptor)null); sqlEx.initCause(var7); throw sqlEx; } } } } ...... public boolean acceptsURL(String url) throws SQLException { if (url == null) { throw SQLError.createSQLException(Messages.getString("NonRegisteringDriver.1"), "08001", (ExceptionInterceptor)null); } else { return this.parseURL(url, (Properties)null) != null; } } ...... public Properties parseURL(String url, Properties defaults) throws SQLException { Properties urlProps = defaults != null ? new Properties(defaults) : new Properties(); if (url == null) { return null; } else if (!StringUtils.startsWithIgnoreCase(url, "jdbc:mysql://") && !StringUtils.startsWithIgnoreCase(url, "jdbc:mysql:mxj://") && !StringUtils.startsWithIgnoreCase(url, "jdbc:mysql:loadbalance://") && !StringUtils.startsWithIgnoreCase(url, "jdbc:mysql:replication://")) { return null; } else { ...... return urlProps; } } - When connecting to the database, the core is to judge whether the incoming URL conforms to its own processing rules (parseURL method). If so, it will establish a connection with db.
For example, the URL rule of Mysql: JDBC: Mysql: / /: / < service_name > and so on
URL rules defined by Oracle: JDBC: Oracle: thin: @ / /: / < service_name >, etc - That is, the java.sql.Driver interface specifies that the manufacturer implements the interface and defines its own URL protocol. The Driver interface implemented by manufacturers judges whether the URL conforms to their own protocol through acceptsurl (URL) (generally, the parseURL method is called internally). If it conforms to their own protocol, this Driver can be used for database connection operation.
- Through the above source code analysis, we can see that there are two ways to obtain database connection:
1.//Find the appropriate driver to connect through the DriverManager Connection connection = DriverManager.getConnection(url, props);
//Take the appropriate Driver directly from the DriverManager, and then connect it by calling the connect() method of the Driver Connection connection = DriverManager.getDriver(url).connect(url, props);
- This requires the source code of DriverManager.getConnection(). The source code is as follows:
3. Get the object that can execute SQL (Statement\PreparedStatement object)
-
The main function of the first two steps is to use the driver to connect to the database. After the connection between the project and the database is completed, how do you send SQL statements to the database and execute them?
We have to mention the Statement object. The main function of the Statement object is to execute static SQL statements and return the generated results based on the established database Connection. From the JDBC use example at the beginning, we can see that the Statement object is associated with the Connection object (created through the conn.createStatement() method), This is why the Statement object is an object that executes static SQL statements and returns the generated results based on the established database Connection.
Note: by default, only one ResultSet object can be opened at the same time for each Statement object. Therefore, if the reading of one ResultSet object is intertwined with the reading of another ResultSet object, each ResultSet object must be generated by a different Statement object. If there is an open ResultSet object in the current Statement object, all execution methods in the Statement interface will implicitly close the open ResultSet object. What do you mean
-
Three kinds of statements in JDBC are used as package containers for executing SQL statements on a given connection: Statement, PreparedStatement (inheriting Statement) and CallableStatement (inheriting PreparedStatement).
-
1. Statement for general queries
- Used for general queries. This object is used to execute static SQL statements and return execution results. The SQL statements here must be complete.
- For a large number of batch processing, it is recommended to use Statement, because the precompiled space of PreparedStatement is limited, and there may be exceptions when the amount of data is large
-
2. PreparedStatement is used to execute parameterized queries
- It is used to execute parameterized queries. SQL statements are precompiled and stored in the PreparedStatement object. This statement is compiled only once during the first execution and then saved in the cache. Parameterized queries using PreparedStatement can prevent most SQL injections;
- The SQL statement has been precompiled before the program runs. When the parameters are dynamically passed to the PreprareStatement at run time, even if there are sensitive characters in the parameters, the database will process them as the attribute value of a field
- Always use (?) as a placeholder, and set the placeholder? The two sides of the are automatically enclosed in quotation marks ('), so PreparedStatement can only be used to set dynamic parameters for parameters that can be enclosed in quotation marks ('), such as parameter values? Placeholder, cannot be used for table name, field name, etc
-
3. CallableStatement is used for stored procedures
- Some DBMS treat each statement in the stored procedure as an independent statement; Others treat the whole process as a compound statement. This difference becomes important when auto commit is enabled because it affects when the commit method is called. In the former case, each statement is submitted separately; In the latter case, all statements are committed at the same time.
-
-
What is SQL injection, why does Statement have the risk of SQL injection, and how does PreparedStatement prevent SQL injection?
-
SQL injection means that the attacker adds additional SQL statements at the end of the original query statements of the application, or passes in additional SQL statements through parameters, so that the application can execute these additional SQL statements normally when the application does not judge or filter the legitimacy of the user input data, So as to realize illegal operation without the knowledge of the administrator.
-
Why does Statement have the risk of SQL injection? The key point is that the SQL Statement executed by the Statement object must be complete. Why do you say so? See the following example:
Statement st = con.CreateStatement(); //Create a Statement object String name = "'Zhang San'"; //parameter String sq = "delete from table1 where name="+name; //Splice the complete SQL statement delete from table1 where name = 'Zhang San' st.execute(sq); //Execute SQL
Since the SQL Statement executed by the Statement object must be complete, all the parameters in the SQL Statement must be spliced completely before being passed into the Statement object for execution. If the passed in parameters are tampered with during the splicing process, the spliced SQL Statement will also be tampered with, for example, the following example:
Statement st = con.CreateStatement(); //Create a Statement object String name = "'Zhang San' or 1=1"; //parameter String sq = "delete from table1 where name="+name; //Splice the complete SQL statement delete from table1 where name = 'Zhang San' or 1=1 st.execute(sq); //Execute SQL
At this time, when the database executes this SQL statement, all data in the table will be deleted, which is SQL injection.
-
How does the PreparedStatement object prevent SQL injection? The key is that the PreparedStatement executes parametric queries, that is, the SQL statements it executes are precompiled, and placeholders are used in the statements to specify the structure of the SQL statements. You can set the value of "?", but you cannot change the structure of the SQL statement. See the following example:
String sq = "delete from table1 where name=?";//Build sql statements to? As a placeholder, the value of this position is to be set PreparedStatement ps = con.prepareStatement(sq); //When creating a PreparedStatement, an sql statement is passed in to realize precompiling ps.setString(1,"Zhang San"); //Set the value of the placeholder of the sql statement. Note that the position of the first parameter is 1, not 0 ps.execute(); //Execute PreparedStatement delete from table1 where name = 'three sheets'
In the introduction at the beginning of this section, it has been said that PreparedStatement will be a placeholder? Quotation marks (') are automatically added around the, that is to say, all incoming parameters will be wrapped in quotation marks',, so that the database will only treat the inner examples in quotation marks as parameters during execution, such as the following example:
String sq = "delete from table1 where name=? or id=?"//Build sql statements to? As a placeholder, the value of this position is to be set PreparedStatement ps = con.prepareStatement(sq); //When creating a PreparedStatement, an sql statement is passed in to realize precompiling ps.setString(1,"Zhang San or 1=1"); //Set the value of the placeholder of the sql statement. Note that the position of the first parameter is 1, not 0 ps.execute(); //Execute PreparedStatement delete from table1 where name = 'Zhang San or 1=1'
At this time, when the database executes this sql, only the data with name 'Zhang San or 1=1' will be deleted, and all the data will not be deleted.
-
Then someone might say that if I pass a (') in the parameter, where is the SQL statement? The (') automatically added on both sides is truncated, and I can inject SQL. Is that so? Let's look at the following example:
String sq = "delete from table1 where name=?"//Build sql statements to? As a placeholder, the value of this position is to be set PreparedStatement ps = con.prepareStatement(sq); //When creating a PreparedStatement, an sql statement is passed in to realize precompiling ps.setString(1,"Zhang San' or '1'='1"); //Set the value of the placeholder of the sql statement. Note that the position of the first parameter is 1, not 0 ps.execute();//Execute this PreparedStatement
At this time, we may expect the SQL statement to be:
delete from table1 where name='Zhang San' or '1'='1'
Is this true? Obviously not. Print PreparedStatement.toString(). You can see that the real sql is:
delete from table1 where name='Zhang San\' or \'1\'=\'1'
That is, the prepareStatement object escapes the single quotation mark in the parameter with \ backslash, so as to prevent sql injection.
-
To sum up: PrepareStatement prevents SQL injection by adding quotation marks on both sides of the parameter and escaping the quotation marks in the parameter (as long as there is a single quotation mark in the string entered by the user, the single quotation mark will be escaped in the setString() function of the mysql database manufacturer). This effectively shields the SQL injection problems caused by illegal SQL and database keywords.
-
Therefore, in actual development, PreparedStatement is generally used to access the database. It can not only prevent sql injection, but also precompile (the whole sql statement needs to be recompiled without changing parameters once, with high efficiency). In addition, the result set obtained by executing the query statement is offline, and the result set can still be accessed after the connection is closed.
-
4. Execute SQL and get the returned results
-
The Statement interface provides three methods for executing SQL statements: ResultSet executeQuery(), int executeUpdate(), and boolean execute(); And a method for batch processing SQL: int[] executeBatch()
- The method executeQuery is used for statements that produce a single result set, such as a SELECT statement
- The executeUpdate method is used to execute INSERT, UPDATE, or DELETE statements and SQL DDL (data definition language) statements, such as CREATE TABLE and DROP TABLE. The effect of an INSERT, UPDATE, or DELETE statement is to modify one or more columns in zero or more rows in the table. The return value of executeUpdate is an integer indicating the number of rows affected (that is, the UPDATE count). For statements that do not operate on rows, such as CREATE TABLE or DROP TABLE, the return value of executeUpdate is always zero.
- The method execute is used to execute a statement that returns multiple result sets, multiple update counts, or a combination of both. Statement's execute can test whether the executed SQL is executing a query or updating. If true is returned, it means that the SQL execution will return a ResultSet indicating the query result. At this time, getResultSet() can be used to obtain the ResultSet object. If false is returned, it means that the SQL execution will return the number of updates or no results. At this time, getUpdateCount() can be used to obtain the number of updates. If you can't know whether to query or update in advance, you can use execute.
- When you need to send a batch of SQL statements to the database for execution, you should avoid sending execution to the database one by one. Batch processing is used to improve execution efficiency. Batch processing has two ways
- Statement
//Add sql to batch Statement statement = connection.createStatement(); statement.addBatch(sql1); statement.addBatch(sql2); //Execute batch statement.executeBatch(); //Empty batch sql statement.clearBatch();
- PreparedStatement
Why execute the SQL in the current PreparedStatement object and clear it every 100 entries? This is because the precompiled space of PreparedStatement mentioned earlier is limited. If the amount of data is too large, memory overflow will inevitably occur. Therefore, it is recommended to use Statement for a large number of batch processing.PreparedStatement preparedStatement = connection.prepareStatement(sql); for (int i = 1; i <= 205; i++) { preparedStatement.setInt(1, i); preparedStatement.setString(2, "TEST"+i); //Add to batch preparedStatement.addBatch(); if (i % 100 ==0) {//Every 100 entries, execute the sql in the current object and clear it //Execute batch preparedStatement.executeBatch(); //Clear batch processing [if the amount of data is too large, all data will be stored in batch processing, and the memory will overflow] preparedStatement.clearBatch(); } } //The rest are batch processed again preparedStatement.executeBatch(); //Re empty preparedStatement.clearBatch();
- Statement
-
ResultSet usage
-
ResultSet is an object returned from query results in data. It can be said that ResultSet is an object that stores query results. However, ResultSet not only has the function of storage, but also has the function of manipulating data, which may complete the update of data, etc.
-
Four resultsets
- The most basic ResultSet
- The function of this ResultSet is to complete the storage function of query results, and it can only be read once, not scrolling back and forth. This result set is created as follows:
Statement st = conn.CreateStatement ResultSet rs = Statement.excuteQuery(sqlStr);
- Because this result set does not support the scrolling read function, if you obtain such a result set, you can only use the next() method in it to read the data one by one.
- The function of this ResultSet is to complete the storage function of query results, and it can only be read once, not scrolling back and forth. This result set is created as follows:
- Scrollable ResultSet type
- This type supports scrolling back and forth to get records next (), previous(), and return to the first line first(). It also supports the line absolute (int n) in the ResultSet to go and the line relative(int n) relative to the current line. To realize such a ResultSet, the following method is used when creating a Statement:
Statement st = conn.createStatement(int resultSetType, int resultSetConcurrency) ResultSet rs = st.executeQuery(sqlStr)
-
1. resultSetType is the type that sets the ResultSet object, scrollable or non scrollable. The values are as follows:
ResultSet.TYPE_FORWARD_ONLY can only scroll forward,
ResultSet.TYPE_ SCROLL_ Intrinsic and Result.TYPE_SCROLL_SENSITIVE scrolls forward and backward arbitrarily. The difference between the two is that the former is not sensitive to modification, while the latter is sensitive to modification. -
2. Resultsetconcurrency is the value that can be modified by setting the ResultSet object. The values are as follows:
ResultSet.CONCUR_READ_ONLY is a parameter set to read-only type.
ResultSet.CONCUR_UPDATABLE is set as a parameter of modifiable type.
-
- This type supports scrolling back and forth to get records next (), previous(), and return to the first line first(). It also supports the line absolute (int n) in the ResultSet to go and the line relative(int n) relative to the current line. To realize such a ResultSet, the following method is used when creating a Statement:
- Updatable ResultSet
- Such a ResultSet object can modify the tables in the database, but I know that the ResultSet is only equivalent to the view of the tables in the database. Therefore, from time to time, all resultsets can be updated as long as updatable is set. The SQL sentence of the ResultSet that can be updated must have the following properties:
- a. Only a single table is referenced.
- b. Does not contain a join or group by clause.
- c. Those columns should contain primary keywords.
With the above conditions, the updatable ResultSet can modify the data. The creation method of the updatable result set is as follows:
Statement st = conn.createstatement(Result.TYPE_SCROLL_INSENSITIVE,Result.CONCUR_UPDATABLE)
- The result of the execution of such a Statement is an updatable result set. The update is to move the cursor of ResultSet to the row you want to update, then call updateXXX(). This method has two parameters. The first is the column to be updated, which can be column name or sequence number. The second is the data to be updated. This data type should be the same as XXX. Every time the update of a row is completed, updateRow() is called to complete the write to the database, and before the cursor of the resultset leaves the modified row, otherwise the modification will not be submitted.
- The updateXXX method can also be used to complete the insertion operation. But first of all, there are two methods:
- moveToInsertRow() moves the ResultSet to the insert row. This insert row is a special row in the table. There is no need to specify a specific row. As long as this method is called, the system will automatically move to that row.
- moveToCurrentRow() this is to move the ResultSet to a row in memory, usually the current row. If the insert operation is not used, this method has no effect. If the insert operation is used, this method is used to return to the line before the insert operation and leave the inserted line. Of course, it can also leave the inserted line through methods such as next(),previous().
- To complete the insertion of the database, first call moveToInsertRow() to move to the insert row and then call updateXXX to complete the update of the column data. After updating, it will be written to the database as well as the update operation. However, insertRow() is used here. It is also necessary to ensure that ResultSet does not leave the insertion column before the execution of the method, otherwise the insertion will not be executed. And updates to inserted rows will be lost.
- Updatable ResultSet
- Under normal circumstances, if a query is executed using a Statement and another query is executed, the result set of the first query will be closed, that is, the result set corresponding to all Statement queries is one. If you call the commit() method of Connection, the result set will also be closed. Holdability refers to whether the ResultSet is closed or not when the result is submitted. Both JDBC 2.0 and 1.0 provide that the ResultSet will be closed after submission. However, in JDBC 3.0, we can set whether the ResultSet is closed. To complete the creation of such a ResultSet object, the method is as follows:
Statement st=conn.createStatement(int resultSetType,int resultSetConcurrency,int resultSetHoldability) ResultSet rs = st.excuteQuery(sqlStr);
-
The first two parameters have the same meaning as the first two parameters. resultSetHoldability indicates whether the result set is opened after the result set is submitted. The values are as follows:
ResultSet.HOLD_CURSORS_OVER_COMMIT:It means that it will not be closed when the modification is submitted ResultSet. ResultSet.CLOSE_CURSORS_AT_COMMIT: Indicates that when the modification is submitted, it is closed ResultSet.
-
- The most basic ResultSet
-
Note: all methods executing the Statement will close the currently open result set (if any) of the called Statement object. This means that the processing of the current ResultSet object needs to be completed before re executing the Statement object.
5. Free resources and close connections
-
Connection, Statement and ResulSet? These three objects are defined inside the method, so these three objects disappear after the method is executed. Why close them separately?
- This connection is a connection to the database server. Although your method ends, the resource still exists and the database connection is not released
-
Why do you want to close the ResultSet first, then the PreparedStatement, and finally the Connection directly after JDBC access to the database?
- It seems that as long as the connection is turned off, the system can run normally. In query or other operations, if only the connection is closed without closing the ResultSet and Statement, will it affect the system performance. Or other adverse effects.
- If you do not use the Connection pool, there is no problem. Once the Connection is closed, the database physical Connection will be released, and all relevant Java resources can be recycled by GC.
- However, if you use a Connection pool, please note that Connection closing is not a physical closing, but a return of the Connection pool. Therefore, both PreparedStatement and ResultSet are held and actually occupy the cursor resources of the relevant database. In this case, as long as it runs for a long time, an error of "cursor exceeds the maximum allowed in the database" will be reported, The program cannot access the database normally.
- Because you open it in order, it's like a stack, last in, first out
- When opening: connection - > Preparedstatement - > resultset
- When closing: resultset - > Preparedstatement - > connection
-
What happens if resources are not released in order?
If you close the connection directly, that is to say, other are also closed. If you operate on other statement s, an exception will appear: "operation not allowed after resultset closed".
Expand reading
Thinking questions
- Execution order of static code block, construction code block, constructor and ordinary code block
- Difference between newInstance() method and new keyword
- Difference between Class.forName and Classloder.loaderClass
- SPI service mechanism