JDBC Basics
First, access the database using JDBC basic steps:
A. loading JDBC driver
B. definition connection URL
C. establish a connection
D. create a Statement object
E. execute a query or update
F. The results dealing with
G. Close the connection
Second, loading JDBC driver:
1. In order to make the code as flexible as possible, we must avoid references to the class name for hard-coded (hard-coding), so we can document from the Properties of the method of loading drivers, you can also use the server configuration data source (DataSource) method to avoid hard-coded in the code
2. In the development process to ensure that set CLASSPATH to include the driver JAR file path. Service in the WEB
Device when deployed JAR file on to Web applications WEB-INF/lib directory. If multiple Web applications use the same database driver JAR file can be placed in the public directory servers using <% CATALINA_HOME%> \ common \ lib in
Third, the definition of connection URL:
Loading JDBC driver, you must specify the location of the database server. Point to the URL database used in the agreement are:
jdbc: sub-agreement, and loading the server host name, port, database name (or reference). Such as: Oracle connection URL:
jdbc: oracle: thin: @ 192.168.0.71:1521: UMV2
jdbc: oracle: using Oracle driver
thin: means to connect the server model used by
@ 192.168.0.71: server address
1521: Server listening port
UMV2: Database Name
Fourth, establish a connection:
1. A database connection (Connection) through its own getMetaData () to obtain its own information
2. By default, a database connection is automatically submitted to the mode (auto-commit), that is to say that whenever a SQL statement
Was changed after the implementation of the results will be submitted automatically if auto-commit mode has been shut down, then the method commit () call must be explicit to be submitted to change the outcome, otherwise all the results of database operations will not be saved
Fifth, create a Statement object:
At same time, each Statement object can only open a ResultSet object. Therefore, the same result if there were two sets of results in the cross-visit, then the two result sets must be different for the two to create the Statement object. If you open a new set of results when there is a set of results has been opened, then there is the outcome of this meeting has been the closure of implicit
Sixth, the implementation of query or update:
In the Statement object can perform the following steps:
A. query operation: executeQuery (SQL statements) B. maintenance operations: executeUpdate (SQL statement)
C. batch operations: executeBath ()
7, the results of treatment:
Bank of China 1.ResultSet the first column index is 1 rather than 0, access to the data in the ResultSet to the use of names, rather than the index
However, attention should be paid to the use of names as a query is case-sensitive.
2.JDBC1.0, we can only move forward in the ResultSet; in JDBC2.0, we can be to the ResultSet
Under the (next) or up (previous) move, the same can also be moved to a specific line (relative, absolute)
3. ResultSet default is non-renewable, and can only move forward. The following code shows how to create a rolling of sensitive ResultSet update
Statement stmt = con.createStatement (
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery ( "SELECT a, b FROM TABLE2");
/ / Rs will be scrollable, will not show changes made by others,
/ / And will be updatable
4.ResultSet and ResultSetMetaData methods provide no direct return to query the number of rows returned. However, in the JDBC
2.0, you can by calling last () method to position the cursor to the last line of the ResultSet, and then call getRow () side
Method to obtain the current line number. In JDBC1.0, make sure the number of rows is the only way to repeat the ResultSet call next () method,
Return false until it is to
Eight to close the connection:
In the database connection should be closed to ResultSet, Statement, Connection order
JDBC-PreparedStatement (prepared statement)
A, PreparedStatement (prepared statement) the creation of:
First of all, create a standard format in accordance with the parameters of language use in the actual parameters to the database before sending to the compiler. That statement with a question mark should be replaced by the value of the specific location. Prepared statement each time you use, only need to use the corresponding setXxx call, replace the statement of the parameters marked. Then the statement can be the same as conventional, the use of executeQuery or execute / executeUpdate modify data in the table below. For example:
Connection connection = DriverManager.getConnection (url, username, password);
/ / Create the parameter with a question mark statement
String template = "UPDATE music SET price =? WHERE";
PreparedStatement statement = connection.prepareStatement (template);
float newPrices [] = getNewPrices ();
int recordingIDs = getIDs ();
for (int i = 0; i <recordingIDs.length; i + +) (
/ / Used instead of setXxx?
statement.setFloat (1, newPrices [i]);
statement.setInt (2, recordingIDs [i]);
/ / Implementation of prepared statements
statement.execute ();)
Second, the benefits of the use of PreparedStatement:
1. Dependent on the server to support pre-compiled queries, as well as the driver to deal with the efficiency of the original query, to prepare statements on the advantages in terms of performance may vary widely.
2. Security is to prepare a statement of the characteristics, we recommend the adoption of HTML form to accept user input, and then update the database, we have to use prepared statements or stored procedures.
3. Prepared statement is also able to correctly handle embedded in the string in quotation marks, as well as dealing with non-character data (such as sending to the database after the target sequence)
JDBC-CallableStatement (callable statement)
First, the use of CallableStatement (callable statement) the advantages and disadvantages:
1. Advantages: syntax error at compile time to find out, rather than during operation; database stored procedure to run than conventional
SQL query is much faster; programmers only need to know the input and output parameters, do not understand the table structure. In addition, as a result of the database language to access the database a little bit of local functions (sequences, triggers, multiple cursor), so use it to write stored procedures than the Java programming language to use some simple.
2. Disadvantages: stored procedure business logic in the database server is running, rather than the client or Web server. The trend is the development of the industry as much as possible out of the business logic database and place them on JavaBean components (or in the large-scale systems, EnterPrise JavaBean components), in the framework of the Web in this manner is the main motive : database access and network I / O is often the performance bottleneck.
Second, the use of JAVA in CallableStatement in a database stored procedure call:
1. The process of definition of the database call
A. The process of free parameters: (call procedure_name)
B. input parameters, only the process of: (call procedure_name (?,?...)}
C. There is a process of output parameters: (? Call procedure_name)
D. Both input parameters and output parameters of the process (? = Call procedure_name (?,?...)}
In the course of the four kinds of forms to the attention of a number of process may return output parameters, and parameters of the index value of output parameters from the beginning. So in front of the last example, the first input parameter of the index value is 2 instead of 1.
2. CallableStatement for the process of preparation
String procedure = "(? = Call procedure_name (?,?))";
CallableStatement statement = connection.prepareCall (procedure);
3. To provide the value of the input parameters
In the implementation of stored procedure, we need to call and set up the type of item and the parameters corresponding to the setXxx, marked by the replacement of the input parameters
Statement.setString (2, "name");
4. The type of output parameters register
We must use a registered registerOutParameter output parameters for each JDBC type
Statement.registerOutParameter (n, type);
5. The implementation of the stored procedure
Statement.execute ();
6. Access the output parameter to return
GetXxx can visit by calling the output parameters of each corresponding
For example:
Connection connection = DriverManager.getConnection (url, username, password);
String procedure = "(? = Call myProc (?,?)}";
CallableStatement statement = connection.prepareCall (procedure);
statement.setString (2 ,×××);
statement.setFloat (3 ,×××);
statement.registerOutParameter (1, Types.INTEGER);
statement.execute ();
int row = statement.getInt (1);
JDBC-Transation (Transaction Processing)
A, Transation (Transaction Processing) concept:
Update the database, by default, changes are written to the database permanent. However, this default behavior can be closed through the preparation process. Automatically shut down in case of delivery, if a problem occurs in the updates, then every change to the database will be able to cancel (or back to back to the original value). If the update is successful, these changes can be submitted to the database permanent. This approach is also known as transaction management.
We need to ensure that either all the operations have taken place, or do not occur all of the operation. This is the principle of Authority.
Second, the use of JAVA in Transation (Service Management) to ensure the integrity of the database:
We use the try-catch-finally block to correctly deal with transaction management, first of all, record the current state of auto-commit. Then, in the try block, call setAutoCommit (false) and the implementation of a series of queries or updates. If a malfunction occurs in the catch block calls rollback; the success of the event, then try to call at the end of the block commit. Either way, both in the finally block to reset automatically submitted to the state. For example:
Connection connection = DriverManager.getConnection (url, username, password);
boolean autoCommit = connection.getAutoCommit ();
Statement statement;
try (
connection.setAutoCommit (false); / / Close the database automatically submitted
statement = connection.createStatement ();
statement.execute (...);
statement.execute (..);
...
connection.commit (); / / if all the statements were submitted by the successful implementation of Service
)
catch (SQLException sqle) (
connection.rollback (); / / If there are abnormal in all the affairs of the rollback
)
finally (
if (statement! = null) (statement.close ();)
connection.setAutoCommit (autoCommit); / / reset state automatically submitted
)
The code above, the connection from the DriverManager to obtain statements in the try / catch block outside. Unless such a successful connection, otherwise they would not call rollback. If the connection to obtain statements on the try / catch with fast, once an exception occurs after a successful connection, due to the role of rollback will disconnect the connection has been established. GetConnection method, however SQLException will be thrown out of this very unusual way to either re-thrown outside or in a separate try / catch block to capture.
JDBC common API
A, Connection Interface:
1.createStatement (): create a database connection
2.prepareStatement (String sql): create a Prepared Statement
3.prepareCall (String sql): call to create a statement
4.getAutoCommit (): access to the automatic mode
5.setAutoCommit (): Setting mode automatically submitted
6.commit (): submission of the implementation of the SQL statement
7.rollback (): roll back the SQL statement executed
8.getMetaData (): obtain a DatabaseMetaData object, the object contains the basic information database
9.close (): Close the database connection
10.isClose (): database connection to determine whether the time-out or show to close
Second, Statement interface:
1.execute (String sql): the implementation of SQL statements, if the return value is the result set is true, otherwise false
2.executeQuery (String sql): the implementation of SQL statements, the return value for the ResultSet
3.executeUpdate (String sql): the implementation of SQL statements, the return value is the number of rows affected by
4.addBatch (String sql): Target Statement to the current list of commands to add a new batch of SQL statements
5.clearBatch (): empty the current list of Statement object command
6.executeBatch (): the implementation of the current batch statement Statement object, the return value for each statement of the function affected by an array
7.getConnection (): return the Statement object to create the Connection object
8.getQueryTimeout (): waiting for the results to obtain the time
9.setQueryTimeout (): set the time to wait for the results
Third, ResultSet interface:
1.first () / beforeFirst (): the cursor moved to the first record in the ResultSet (front)
2.last () / afterLast (): the cursor moved to the end of a ResultSet records (back)
3.absolute (int column): the cursor moved to the first line in relation to the designated line, as opposed to negative, compared with a record of the last
4.relative (int rows): the cursor moved to the current line relative to the first few lines, is downward, negative for the upward
5.next (): Move the cursor line
6.previous (): Move the cursor line
7.insertRow (): and databases to the current ResultSet was to insert a row insert record
8.deleteRow (): will present the current row in the ResultSet and the corresponding database record to delete
9.updateRow (): Using ResultSet in the current record has been updated to update the corresponding database records
10.cancelUpdate (): Cancel the current ResultSet and database operations done
11.findColumn (String columnName): return the current ResultSet column name with the specified index of the corresponding
12.getRow (): return ResultSet The current line number
13.refreshRow (): update the current ResultSet all records
14.getMetaData (): return description of the ResultSet object ResultSetMetaData
15.isAfterLast (): whether or not to the end of
16.isBeforeFirst (): whether or not to begin
17.isFirst (): if the first record
18.isLast (): whether the last record
19.wasNull (): check out whether the value NULL value, if the type of column as the basic type, and the database value is 0, then
This check is important. NULL as a result of the database also returned to 0, so 0 value and the database can not distinguish between NULL. If the column is of type object, you can simply compare the return value with null
20.close (): close the current ResultSet
Fourth, ResultSetMetaData interface:
1.getColumnCount (): return the number of columns in ResultSet
2.getColumnName (): return column names in the database
3.getColumnType (): return the SQL type of column
4.isReadOnly (): that the data item is a read-only value
5.isNullable (): that the column can store NULL
Based on the JDBC database connection pool technology research and application
Java applications access the database of the basic principles of
In the Java language, JDBC (Java DataBase Connection) is a database application and a bridge, that is, Java language access to databases through JDBC technology. JDBC is an "open" program, which database application developers, database developers front tool provides a standard application programming interface so that developers can use pure Java language, a complete database application. JDBC provides two API, developers are oriented facing the bottom of the API and the JDBC driver API, primarily by the underlying JDBC driver and direct JDBC-ODBC bridge driver to connect with the database.
In general, Java applications that access the database of the process (as shown in Figure 1) is:
① load database driver;
② establish a database connection through JDBC;
③ access the database, the implementation of SQL statements;
④ disconnected database connection.
JDBC is a database access technology, has the advantage of easy-to-use. However, the use of this model Web application development, there are many problems: First of all, every Web request must establish a database connection. Establish a connection is a time-consuming activities, each had to spend time 0.05s ~ 1s, but also the distribution of system memory resources. This time for one or several database operations, perhaps can not feel how much the system costs. But when it comes to the current Web applications, in particular large-scale e-commerce website and at the same time there are hundreds or even thousands of people online is normal. In this case, the frequent operation of the database connection is bound to occupy a lot of system resources, the site must respond to the speed of decline in serious and even cause the collapse of the server. Is not alarmist talk, and this is constraining the development of certain e-commerce website technical bottlenecks. Secondly, for each database connection, use after every disconnect. Otherwise, if the program could not close the abnormal, it would lead to database system memory leak, and ultimately will have to restart the database. In addition, this development can not control the connection object is created the number of system resources will be distributed without regard to, such as too many connections, but also may lead to memory leaks, server crash.
Database Connection Pool (connection pool) of the working principle
1, the basic concepts and principles of
Database connection pooling basic idea is to establish a database connection "pool." Add in the buffer pool in advance a certain number of connections, when the need for a database connection, simply from the "pool" out of one, after using the add back. We can set the connection pool through the largest number of connections to prevent system and database connection endless. More importantly, we can manage the connection pooling mechanism to monitor the number of databases, use of the system development, test and provide a basis for performance tuning.
2, the server built-in connection pool
API in the JDBC connection pool did not provide the method. WEB large number of application servers such as BEA's WebLogic and IBM's WebSphere to provide a mechanism for the connection pool, but it must be a special category of third-party support the connection pool usage.
Connection pooling analysis of key issues
1, complicated by issues
Connection management services in order to have the greatest generality, we must consider multi-threaded environment, that is, complicated issues. This issue is relatively well resolved, because the Java language itself provides support for the concurrent management, the use of synchronized keyword to ensure that the threads are synchronized. For use directly in the category together with the synchronized keyword in front of methods, such as:
public synchronized Connection getConnection ()
2, multi-database server and multi-user
For large-scale enterprise applications often need to connect different databases (for example, to connect Oracle and Sybase). How to connect different database then? We adopt the strategy is: to design a single-case model in line with the connection pool management, connection pooling and management in the only instance when being read to create a resource file, in which storage resources to document the number of database url address (< poolName.url>), user name (<poolName.user>), password (<poolName.password>) and other information. If tx.url = 172.21.15.123:5000 / tx_it, tx.user = yang, tx.password = yang321. According to the information provided in the resource file, create a number of examples of types of connection pool, each an example of a specific database connection pool. Examples of connection pool management instance for each connection pool for a name, through a variety of different names to manage the connection pool.
For a database with multiple users using a different name and password to access the situation, you can file through the allocation of resources to deal with that document in terms of resources to set up a number with the same url address, but with different user name and password of the database connection information.
3, Transaction Processing
We know that the Service has atoms, and at this time require the operation of the database in line with the "ALL-ALL-NOTHING" principles, namely, a group of SQL statements for the whole or so, or not full.
In the Java language, Connection type provides its own support services, you can set the AutoCommit attribute Connection for false, and then explicitly call commit or rollback methods to achieve. However, for efficient reuse Connection, it is necessary to provide support mechanisms, the corresponding panel. Exclusive use of each Service to achieve a connection, this method can greatly reduce the complexity of transaction management.
4, the distribution of connection pool and release
Connection pool distribution and release of the performance of the system have a great impact. Reasonable distribution and release, can improve the connectivity of the complex moment, thereby reducing the cost of establishing a new connection, but also can speed up the user's access speed.
For connection pool management can be used free. That is, to have been created but not yet distributed by the creation of the connection time to a free storage pool. Whenever the user requests a connection, the system first checks whether there is idle idle connection pool. If there had set up the longest (by order of the storage containers to achieve) that allowed him to connect (the actual connection is first to determine the validity, if available on the allocation to users, such as the connection is not available just from the free pool deleted and re-detect whether there are idle pool connection); If you do not check the current connection pool are open, whether the connection pool by the maximum allowable number of connections (maxConn), if not met, we create a new connection, if we have achieved on the waiting for a certain period of time (timeout). If the waiting time has been released to connect to this connection will be allocated to waiting users, if the scheduled time to wait for longer than timeout, then return null value (null). System which has been allocated out of the connection is being used only counts when you are finished using the pool and then return to idle. Idle state for the connection can be opened from time to time special thread detection, it will take a certain degree of system overhead, but can guarantee a faster response speed. Can also be taken not to open specialized thread, but in the allocation of pre-detection method.
5, connection pool configuration and maintenance
Connection pool should be placed in the end the number of connections to make the best performance of the system? System can be taken to set the minimum number of connections (minConn) and the maximum number of connections (maxConn) to control the connection pool of connections. The smallest number of connections is the connection pool when starting the connections created. If you create too much, the system on the slow start, but after the system created in response to speed quickly; If you create too few, the system started fast, but slow to respond to them.
In the development, setting the smaller the minimum number of connections, it would develop faster, and actually used in the system when a larger set because it is the speed of access to customers will be faster. The largest number of connections is the connection pool to allow the maximum number of connections, the number of specific settings, depending on the traffic system through repeated testing, to find the best point.
How to ensure that the minimum connection pool connections it? There are two types of active and passive strategies. Dynamic that is at a certain period of time on the connection pool for testing, if we find that the number of connections is less than the minimum number of connections, then add the corresponding number of new connections, to ensure the normal operation of connection pooling. Idle connection is still not found the time to check again.
Realization of Connection Pool
1, connection pool model
Connection pool is connected to a database of all the "pool", the main achievement of the following features: ① from the connection pool can be used to obtain or create connection; ② use after the connection back to connection pool; ③ in the system is shut down before the break open and release all connections to connect the system resources occupied; ④ also able to handle invalid connection (the original registration for the connectivity can be used, for some reason is no longer available, such as overtime, communications issues), and to limit the total number of connection pool connections not less than a predetermined value and does not exceed a predetermined value.
Connection Pool Management is a connection pool outside the category of the category review (wrapper), in line with the single-case model, that is, the system can only have one connection pool management examples. Used for its main object of a number of connection pool management, has the following features: ① load and register the database specific JDBC driver; ② property documents in accordance with the given information, the creation of connection pool object; ③ In order to facilitate the management of a number of connection pool object, for each object from a connection pool name, the name of connection pool to achieve between their mapping examples; ④ track to connect customers to use in order to connect the needs of the closure of the release of resources. The connection pool management is to facilitate the introduction of a number of connection pool on the use and management, such as the system needs to connect different database, or connect the same database but the security issue, the need for different user using a different name and password.
2, connection pooling to achieve
Given the following types of connection pool and connection pool management and the main properties in order to achieve the basic interface:
public class DBConnectionPool implements TimerListener (
private int checkedOut; / / have been allocated out of the connections
private ArrayList freeConnections = new ArrayList ();// containers, leisure pool, in chronological order based on the creation of storage has been created
The connection has not yet been distributed
private int minConn; / / connect the pond to connect the smallest number of
private int maxConn; / / connect the pond to allow the maximum number of connections that exist
private String name; / / for the connection pool取个名字to facilitate management
private String password; / / connect database password when needed
private String url; / / create a connection to the address database
private String user; / / connect to database user name is required
public Timer timer; / / timer
public DBConnectionPool (String name, String URL, String user, Stringpassword, int maxConn)
public synchronized void freeConnection (Connection con) / / use the connection after the idle returned to the pool
public synchronized Connection getConnection (long timeout) / / get a connection, timeout is the waiting time
public synchronized void release () / / disconnect all connections, release system resources occupied by
private Connection newConnection () / / create a new database connection
public synchronized void TimerEvent () / / Timer event handler
)
public class DBConnectionManager (
static private DBConnectionManager instance; / / connection pool management of the only examples of
static private int clients; / / the number of customers
private ArrayList drivers = new ArrayList ();// containers, stored procedures for database-driven
private HashMap pools = new HashMap ();// to name / value form of connection pool to access the object's name and connection pool objects
private void loadDrivers (Properties props) / / load database driver
private void createPools (Properties props) / / According to information provided by the properties file, create one or more connection pool
private DBConnectionManager () / / private constructor, in which the initialization function called init ()
private void init () / / initialize connection pool management of the only examples from the private constructor call
static synchronized public DBConnectionManager getInstance () / / If the instance has been the only example of the creation of a direct return to this example; Otherwise, call the private constructor, to create connection pool management of the only examples of
public Connection getConnection (String name) / / from the name for the name of the connection pool object / / get a connection
public Connection getConnection (String name, long time) / / from the name for the name of the connection pool to obtain a connection object, time is the waiting time
public void freeConnection (String name, Connection con) / / release a connection name is a connection pool object name
public synchronized void release () / / release all resources
)
3, the use of connection pooling
Above, the connection pool to achieve development in how it applied to the system? Servlet as an example below the use of connection pooling.
Servlet life cycle is: when at the beginning of the establishment of servlet, call its initialization (init) method. After each user request led to the establishment of a call in front of the service methods of the examples of the thread. Finally, when the server decided to unload a servlet, it first calls the servlet's destroy method. According to the characteristics of servlet, we can initialize function in the connection pool management to generate the only instance (including the creation of one or more connection pool). Such as:
public void init () throws ServletException
(
/ / GetInstance ()? DBConnectionManager ()? Init ()
connMgr = DBConnectionManager.getInstance ();
)
Can then be passed in the service method name to use connection pooling connection pool, the implementation of database operations. Finally, in the destroy method to release occupied system resources, such as:
public void destroy () (
connMgr.release ();
super.destroy ();
)
First, access the database using JDBC basic steps:
A. loading JDBC driver
B. definition connection URL
C. establish a connection
D. create a Statement object
E. execute a query or update
F. The results dealing with
G. Close the connection
Second, loading JDBC driver:
1. In order to make the code as flexible as possible, we must avoid references to the class name for hard-coded (hard-coding), so we can document from the Properties of the method of loading drivers, you can also use the server configuration data source (DataSource) method to avoid hard-coded in the code
2. In the development process to ensure that set CLASSPATH to include the driver JAR file path. Service in the WEB
Device when deployed JAR file on to Web applications WEB-INF/lib directory. If multiple Web applications use the same database driver JAR file can be placed in the public directory servers using <% CATALINA_HOME%> \ common \ lib in
Third, the definition of connection URL:
Loading JDBC driver, you must specify the location of the database server. Point to the URL database used in the agreement are:
jdbc: sub-agreement, and loading the server host name, port, database name (or reference). Such as: Oracle connection URL:
jdbc: oracle: thin: @ 192.168.0.71:1521: UMV2
jdbc: oracle: using Oracle driver
thin: means to connect the server model used by
@ 192.168.0.71: server address
1521: Server listening port
UMV2: Database Name
Fourth, establish a connection:
1. A database connection (Connection) through its own getMetaData () to obtain its own information
2. By default, a database connection is automatically submitted to the mode (auto-commit), that is to say that whenever a SQL statement
Was changed after the implementation of the results will be submitted automatically if auto-commit mode has been shut down, then the method commit () call must be explicit to be submitted to change the outcome, otherwise all the results of database operations will not be saved
Fifth, create a Statement object:
At same time, each Statement object can only open a ResultSet object. Therefore, the same result if there were two sets of results in the cross-visit, then the two result sets must be different for the two to create the Statement object. If you open a new set of results when there is a set of results has been opened, then there is the outcome of this meeting has been the closure of implicit
Sixth, the implementation of query or update:
In the Statement object can perform the following steps:
A. query operation: executeQuery (SQL statements) B. maintenance operations: executeUpdate (SQL statement)
C. batch operations: executeBath ()
7, the results of treatment:
Bank of China 1.ResultSet the first column index is 1 rather than 0, access to the data in the ResultSet to the use of names, rather than the index
However, attention should be paid to the use of names as a query is case-sensitive.
2.JDBC1.0, we can only move forward in the ResultSet; in JDBC2.0, we can be to the ResultSet
Under the (next) or up (previous) move, the same can also be moved to a specific line (relative, absolute)
3. ResultSet default is non-renewable, and can only move forward. The following code shows how to create a rolling of sensitive ResultSet update
Statement stmt = con.createStatement (
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery ( "SELECT a, b FROM TABLE2");
/ / Rs will be scrollable, will not show changes made by others,
/ / And will be updatable
4.ResultSet and ResultSetMetaData methods provide no direct return to query the number of rows returned. However, in the JDBC
2.0, you can by calling last () method to position the cursor to the last line of the ResultSet, and then call getRow () side
Method to obtain the current line number. In JDBC1.0, make sure the number of rows is the only way to repeat the ResultSet call next () method,
Return false until it is to
Eight to close the connection:
In the database connection should be closed to ResultSet, Statement, Connection order
JDBC-PreparedStatement (prepared statement)
A, PreparedStatement (prepared statement) the creation of:
First of all, create a standard format in accordance with the parameters of language use in the actual parameters to the database before sending to the compiler. That statement with a question mark should be replaced by the value of the specific location. Prepared statement each time you use, only need to use the corresponding setXxx call, replace the statement of the parameters marked. Then the statement can be the same as conventional, the use of executeQuery or execute / executeUpdate modify data in the table below. For example:
Connection connection = DriverManager.getConnection (url, username, password);
/ / Create the parameter with a question mark statement
String template = "UPDATE music SET price =? WHERE";
PreparedStatement statement = connection.prepareStatement (template);
float newPrices [] = getNewPrices ();
int recordingIDs = getIDs ();
for (int i = 0; i <recordingIDs.length; i + +) (
/ / Used instead of setXxx?
statement.setFloat (1, newPrices [i]);
statement.setInt (2, recordingIDs [i]);
/ / Implementation of prepared statements
statement.execute ();)
Second, the benefits of the use of PreparedStatement:
1. Dependent on the server to support pre-compiled queries, as well as the driver to deal with the efficiency of the original query, to prepare statements on the advantages in terms of performance may vary widely.
2. Security is to prepare a statement of the characteristics, we recommend the adoption of HTML form to accept user input, and then update the database, we have to use prepared statements or stored procedures.
3. Prepared statement is also able to correctly handle embedded in the string in quotation marks, as well as dealing with non-character data (such as sending to the database after the target sequence)
JDBC-CallableStatement (callable statement)
First, the use of CallableStatement (callable statement) the advantages and disadvantages:
1. Advantages: syntax error at compile time to find out, rather than during operation; database stored procedure to run than conventional
SQL query is much faster; programmers only need to know the input and output parameters, do not understand the table structure. In addition, as a result of the database language to access the database a little bit of local functions (sequences, triggers, multiple cursor), so use it to write stored procedures than the Java programming language to use some simple.
2. Disadvantages: stored procedure business logic in the database server is running, rather than the client or Web server. The trend is the development of the industry as much as possible out of the business logic database and place them on JavaBean components (or in the large-scale systems, EnterPrise JavaBean components), in the framework of the Web in this manner is the main motive : database access and network I / O is often the performance bottleneck.
Second, the use of JAVA in CallableStatement in a database stored procedure call:
1. The process of definition of the database call
A. The process of free parameters: (call procedure_name)
B. input parameters, only the process of: (call procedure_name (?,?...)}
C. There is a process of output parameters: (? Call procedure_name)
D. Both input parameters and output parameters of the process (? = Call procedure_name (?,?...)}
In the course of the four kinds of forms to the attention of a number of process may return output parameters, and parameters of the index value of output parameters from the beginning. So in front of the last example, the first input parameter of the index value is 2 instead of 1.
2. CallableStatement for the process of preparation
String procedure = "(? = Call procedure_name (?,?))";
CallableStatement statement = connection.prepareCall (procedure);
3. To provide the value of the input parameters
In the implementation of stored procedure, we need to call and set up the type of item and the parameters corresponding to the setXxx, marked by the replacement of the input parameters
Statement.setString (2, "name");
4. The type of output parameters register
We must use a registered registerOutParameter output parameters for each JDBC type
Statement.registerOutParameter (n, type);
5. The implementation of the stored procedure
Statement.execute ();
6. Access the output parameter to return
GetXxx can visit by calling the output parameters of each corresponding
For example:
Connection connection = DriverManager.getConnection (url, username, password);
String procedure = "(? = Call myProc (?,?)}";
CallableStatement statement = connection.prepareCall (procedure);
statement.setString (2 ,×××);
statement.setFloat (3 ,×××);
statement.registerOutParameter (1, Types.INTEGER);
statement.execute ();
int row = statement.getInt (1);
JDBC-Transation (Transaction Processing)
A, Transation (Transaction Processing) concept:
Update the database, by default, changes are written to the database permanent. However, this default behavior can be closed through the preparation process. Automatically shut down in case of delivery, if a problem occurs in the updates, then every change to the database will be able to cancel (or back to back to the original value). If the update is successful, these changes can be submitted to the database permanent. This approach is also known as transaction management.
We need to ensure that either all the operations have taken place, or do not occur all of the operation. This is the principle of Authority.
Second, the use of JAVA in Transation (Service Management) to ensure the integrity of the database:
We use the try-catch-finally block to correctly deal with transaction management, first of all, record the current state of auto-commit. Then, in the try block, call setAutoCommit (false) and the implementation of a series of queries or updates. If a malfunction occurs in the catch block calls rollback; the success of the event, then try to call at the end of the block commit. Either way, both in the finally block to reset automatically submitted to the state. For example:
Connection connection = DriverManager.getConnection (url, username, password);
boolean autoCommit = connection.getAutoCommit ();
Statement statement;
try (
connection.setAutoCommit (false); / / Close the database automatically submitted
statement = connection.createStatement ();
statement.execute (...);
statement.execute (..);
...
connection.commit (); / / if all the statements were submitted by the successful implementation of Service
)
catch (SQLException sqle) (
connection.rollback (); / / If there are abnormal in all the affairs of the rollback
)
finally (
if (statement! = null) (statement.close ();)
connection.setAutoCommit (autoCommit); / / reset state automatically submitted
)
The code above, the connection from the DriverManager to obtain statements in the try / catch block outside. Unless such a successful connection, otherwise they would not call rollback. If the connection to obtain statements on the try / catch with fast, once an exception occurs after a successful connection, due to the role of rollback will disconnect the connection has been established. GetConnection method, however SQLException will be thrown out of this very unusual way to either re-thrown outside or in a separate try / catch block to capture.
JDBC common API
A, Connection Interface:
1.createStatement (): create a database connection
2.prepareStatement (String sql): create a Prepared Statement
3.prepareCall (String sql): call to create a statement
4.getAutoCommit (): access to the automatic mode
5.setAutoCommit (): Setting mode automatically submitted
6.commit (): submission of the implementation of the SQL statement
7.rollback (): roll back the SQL statement executed
8.getMetaData (): obtain a DatabaseMetaData object, the object contains the basic information database
9.close (): Close the database connection
10.isClose (): database connection to determine whether the time-out or show to close
Second, Statement interface:
1.execute (String sql): the implementation of SQL statements, if the return value is the result set is true, otherwise false
2.executeQuery (String sql): the implementation of SQL statements, the return value for the ResultSet
3.executeUpdate (String sql): the implementation of SQL statements, the return value is the number of rows affected by
4.addBatch (String sql): Target Statement to the current list of commands to add a new batch of SQL statements
5.clearBatch (): empty the current list of Statement object command
6.executeBatch (): the implementation of the current batch statement Statement object, the return value for each statement of the function affected by an array
7.getConnection (): return the Statement object to create the Connection object
8.getQueryTimeout (): waiting for the results to obtain the time
9.setQueryTimeout (): set the time to wait for the results
Third, ResultSet interface:
1.first () / beforeFirst (): the cursor moved to the first record in the ResultSet (front)
2.last () / afterLast (): the cursor moved to the end of a ResultSet records (back)
3.absolute (int column): the cursor moved to the first line in relation to the designated line, as opposed to negative, compared with a record of the last
4.relative (int rows): the cursor moved to the current line relative to the first few lines, is downward, negative for the upward
5.next (): Move the cursor line
6.previous (): Move the cursor line
7.insertRow (): and databases to the current ResultSet was to insert a row insert record
8.deleteRow (): will present the current row in the ResultSet and the corresponding database record to delete
9.updateRow (): Using ResultSet in the current record has been updated to update the corresponding database records
10.cancelUpdate (): Cancel the current ResultSet and database operations done
11.findColumn (String columnName): return the current ResultSet column name with the specified index of the corresponding
12.getRow (): return ResultSet The current line number
13.refreshRow (): update the current ResultSet all records
14.getMetaData (): return description of the ResultSet object ResultSetMetaData
15.isAfterLast (): whether or not to the end of
16.isBeforeFirst (): whether or not to begin
17.isFirst (): if the first record
18.isLast (): whether the last record
19.wasNull (): check out whether the value NULL value, if the type of column as the basic type, and the database value is 0, then
This check is important. NULL as a result of the database also returned to 0, so 0 value and the database can not distinguish between NULL. If the column is of type object, you can simply compare the return value with null
20.close (): close the current ResultSet
Fourth, ResultSetMetaData interface:
1.getColumnCount (): return the number of columns in ResultSet
2.getColumnName (): return column names in the database
3.getColumnType (): return the SQL type of column
4.isReadOnly (): that the data item is a read-only value
5.isNullable (): that the column can store NULL
Based on the JDBC database connection pool technology research and application
Java applications access the database of the basic principles of
In the Java language, JDBC (Java DataBase Connection) is a database application and a bridge, that is, Java language access to databases through JDBC technology. JDBC is an "open" program, which database application developers, database developers front tool provides a standard application programming interface so that developers can use pure Java language, a complete database application. JDBC provides two API, developers are oriented facing the bottom of the API and the JDBC driver API, primarily by the underlying JDBC driver and direct JDBC-ODBC bridge driver to connect with the database.
In general, Java applications that access the database of the process (as shown in Figure 1) is:
① load database driver;
② establish a database connection through JDBC;
③ access the database, the implementation of SQL statements;
④ disconnected database connection.
JDBC is a database access technology, has the advantage of easy-to-use. However, the use of this model Web application development, there are many problems: First of all, every Web request must establish a database connection. Establish a connection is a time-consuming activities, each had to spend time 0.05s ~ 1s, but also the distribution of system memory resources. This time for one or several database operations, perhaps can not feel how much the system costs. But when it comes to the current Web applications, in particular large-scale e-commerce website and at the same time there are hundreds or even thousands of people online is normal. In this case, the frequent operation of the database connection is bound to occupy a lot of system resources, the site must respond to the speed of decline in serious and even cause the collapse of the server. Is not alarmist talk, and this is constraining the development of certain e-commerce website technical bottlenecks. Secondly, for each database connection, use after every disconnect. Otherwise, if the program could not close the abnormal, it would lead to database system memory leak, and ultimately will have to restart the database. In addition, this development can not control the connection object is created the number of system resources will be distributed without regard to, such as too many connections, but also may lead to memory leaks, server crash.
Database Connection Pool (connection pool) of the working principle
1, the basic concepts and principles of
Database connection pooling basic idea is to establish a database connection "pool." Add in the buffer pool in advance a certain number of connections, when the need for a database connection, simply from the "pool" out of one, after using the add back. We can set the connection pool through the largest number of connections to prevent system and database connection endless. More importantly, we can manage the connection pooling mechanism to monitor the number of databases, use of the system development, test and provide a basis for performance tuning.
2, the server built-in connection pool
API in the JDBC connection pool did not provide the method. WEB large number of application servers such as BEA's WebLogic and IBM's WebSphere to provide a mechanism for the connection pool, but it must be a special category of third-party support the connection pool usage.
Connection pooling analysis of key issues
1, complicated by issues
Connection management services in order to have the greatest generality, we must consider multi-threaded environment, that is, complicated issues. This issue is relatively well resolved, because the Java language itself provides support for the concurrent management, the use of synchronized keyword to ensure that the threads are synchronized. For use directly in the category together with the synchronized keyword in front of methods, such as:
public synchronized Connection getConnection ()
2, multi-database server and multi-user
For large-scale enterprise applications often need to connect different databases (for example, to connect Oracle and Sybase). How to connect different database then? We adopt the strategy is: to design a single-case model in line with the connection pool management, connection pooling and management in the only instance when being read to create a resource file, in which storage resources to document the number of database url address (< poolName.url>), user name (<poolName.user>), password (<poolName.password>) and other information. If tx.url = 172.21.15.123:5000 / tx_it, tx.user = yang, tx.password = yang321. According to the information provided in the resource file, create a number of examples of types of connection pool, each an example of a specific database connection pool. Examples of connection pool management instance for each connection pool for a name, through a variety of different names to manage the connection pool.
For a database with multiple users using a different name and password to access the situation, you can file through the allocation of resources to deal with that document in terms of resources to set up a number with the same url address, but with different user name and password of the database connection information.
3, Transaction Processing
We know that the Service has atoms, and at this time require the operation of the database in line with the "ALL-ALL-NOTHING" principles, namely, a group of SQL statements for the whole or so, or not full.
In the Java language, Connection type provides its own support services, you can set the AutoCommit attribute Connection for false, and then explicitly call commit or rollback methods to achieve. However, for efficient reuse Connection, it is necessary to provide support mechanisms, the corresponding panel. Exclusive use of each Service to achieve a connection, this method can greatly reduce the complexity of transaction management.
4, the distribution of connection pool and release
Connection pool distribution and release of the performance of the system have a great impact. Reasonable distribution and release, can improve the connectivity of the complex moment, thereby reducing the cost of establishing a new connection, but also can speed up the user's access speed.
For connection pool management can be used free. That is, to have been created but not yet distributed by the creation of the connection time to a free storage pool. Whenever the user requests a connection, the system first checks whether there is idle idle connection pool. If there had set up the longest (by order of the storage containers to achieve) that allowed him to connect (the actual connection is first to determine the validity, if available on the allocation to users, such as the connection is not available just from the free pool deleted and re-detect whether there are idle pool connection); If you do not check the current connection pool are open, whether the connection pool by the maximum allowable number of connections (maxConn), if not met, we create a new connection, if we have achieved on the waiting for a certain period of time (timeout). If the waiting time has been released to connect to this connection will be allocated to waiting users, if the scheduled time to wait for longer than timeout, then return null value (null). System which has been allocated out of the connection is being used only counts when you are finished using the pool and then return to idle. Idle state for the connection can be opened from time to time special thread detection, it will take a certain degree of system overhead, but can guarantee a faster response speed. Can also be taken not to open specialized thread, but in the allocation of pre-detection method.
5, connection pool configuration and maintenance
Connection pool should be placed in the end the number of connections to make the best performance of the system? System can be taken to set the minimum number of connections (minConn) and the maximum number of connections (maxConn) to control the connection pool of connections. The smallest number of connections is the connection pool when starting the connections created. If you create too much, the system on the slow start, but after the system created in response to speed quickly; If you create too few, the system started fast, but slow to respond to them.
In the development, setting the smaller the minimum number of connections, it would develop faster, and actually used in the system when a larger set because it is the speed of access to customers will be faster. The largest number of connections is the connection pool to allow the maximum number of connections, the number of specific settings, depending on the traffic system through repeated testing, to find the best point.
How to ensure that the minimum connection pool connections it? There are two types of active and passive strategies. Dynamic that is at a certain period of time on the connection pool for testing, if we find that the number of connections is less than the minimum number of connections, then add the corresponding number of new connections, to ensure the normal operation of connection pooling. Idle connection is still not found the time to check again.
Realization of Connection Pool
1, connection pool model
Connection pool is connected to a database of all the "pool", the main achievement of the following features: ① from the connection pool can be used to obtain or create connection; ② use after the connection back to connection pool; ③ in the system is shut down before the break open and release all connections to connect the system resources occupied; ④ also able to handle invalid connection (the original registration for the connectivity can be used, for some reason is no longer available, such as overtime, communications issues), and to limit the total number of connection pool connections not less than a predetermined value and does not exceed a predetermined value.
Connection Pool Management is a connection pool outside the category of the category review (wrapper), in line with the single-case model, that is, the system can only have one connection pool management examples. Used for its main object of a number of connection pool management, has the following features: ① load and register the database specific JDBC driver; ② property documents in accordance with the given information, the creation of connection pool object; ③ In order to facilitate the management of a number of connection pool object, for each object from a connection pool name, the name of connection pool to achieve between their mapping examples; ④ track to connect customers to use in order to connect the needs of the closure of the release of resources. The connection pool management is to facilitate the introduction of a number of connection pool on the use and management, such as the system needs to connect different database, or connect the same database but the security issue, the need for different user using a different name and password.
2, connection pooling to achieve
Given the following types of connection pool and connection pool management and the main properties in order to achieve the basic interface:
public class DBConnectionPool implements TimerListener (
private int checkedOut; / / have been allocated out of the connections
private ArrayList freeConnections = new ArrayList ();// containers, leisure pool, in chronological order based on the creation of storage has been created
The connection has not yet been distributed
private int minConn; / / connect the pond to connect the smallest number of
private int maxConn; / / connect the pond to allow the maximum number of connections that exist
private String name; / / for the connection pool取个名字to facilitate management
private String password; / / connect database password when needed
private String url; / / create a connection to the address database
private String user; / / connect to database user name is required
public Timer timer; / / timer
public DBConnectionPool (String name, String URL, String user, Stringpassword, int maxConn)
public synchronized void freeConnection (Connection con) / / use the connection after the idle returned to the pool
public synchronized Connection getConnection (long timeout) / / get a connection, timeout is the waiting time
public synchronized void release () / / disconnect all connections, release system resources occupied by
private Connection newConnection () / / create a new database connection
public synchronized void TimerEvent () / / Timer event handler
)
public class DBConnectionManager (
static private DBConnectionManager instance; / / connection pool management of the only examples of
static private int clients; / / the number of customers
private ArrayList drivers = new ArrayList ();// containers, stored procedures for database-driven
private HashMap pools = new HashMap ();// to name / value form of connection pool to access the object's name and connection pool objects
private void loadDrivers (Properties props) / / load database driver
private void createPools (Properties props) / / According to information provided by the properties file, create one or more connection pool
private DBConnectionManager () / / private constructor, in which the initialization function called init ()
private void init () / / initialize connection pool management of the only examples from the private constructor call
static synchronized public DBConnectionManager getInstance () / / If the instance has been the only example of the creation of a direct return to this example; Otherwise, call the private constructor, to create connection pool management of the only examples of
public Connection getConnection (String name) / / from the name for the name of the connection pool object / / get a connection
public Connection getConnection (String name, long time) / / from the name for the name of the connection pool to obtain a connection object, time is the waiting time
public void freeConnection (String name, Connection con) / / release a connection name is a connection pool object name
public synchronized void release () / / release all resources
)
3, the use of connection pooling
Above, the connection pool to achieve development in how it applied to the system? Servlet as an example below the use of connection pooling.
Servlet life cycle is: when at the beginning of the establishment of servlet, call its initialization (init) method. After each user request led to the establishment of a call in front of the service methods of the examples of the thread. Finally, when the server decided to unload a servlet, it first calls the servlet's destroy method. According to the characteristics of servlet, we can initialize function in the connection pool management to generate the only instance (including the creation of one or more connection pool). Such as:
public void init () throws ServletException
(
/ / GetInstance ()? DBConnectionManager ()? Init ()
connMgr = DBConnectionManager.getInstance ();
)
Can then be passed in the service method name to use connection pooling connection pool, the implementation of database operations. Finally, in the destroy method to release occupied system resources, such as:
public void destroy () (
connMgr.release ();
super.destroy ();
)