Tutorials to .com

Tutorials to .com » Jsp » Database » Optimize JDBC Programming

Optimize JDBC Programming

Print View , by: iSee ,Total views: 10 ,Word Count: 1830 ,Date: Sun, 19 Apr 2009 Time: 4:07 AM

JDBC programming to optimize this is in accordance with MS SQL SERVER 2000 JDBC DRIVER HELP, and reference data from other. ms of this paper is to help you lose style, sample code, it can ..... interested to download http://download.microsoft.com/download/SQLSVR2000/jdbc/2000/NT45XP/EN-US/setup . exe. Due to my limited, inappropriate text criticism please.
1. To minimize the database metadata methods
Is a ResultSet object, DatabaseMetaData object JDBC methods other than relatively slow, so flat-fan method to reduce the use of the performance of the system. In the proceedings should be the result set cache information, such as the getTypeInfo () return the result set into the Vector or Hashtable, so that procedures can greatly improve efficiency.
2. To avoid the method call model
In the method call should be avoided as far as possible into as null parameters, although in some cases to carry out successfully, but a heavy financial burden on the DB Server. In fact, in many cases the necessary parameters are known. For example:
/ / Here the code to omit the unauthorized capture (the same below).
DatabaseMetaData md =...;
ResultSet rs = md.getTables (null, null, "authors", null); / / access to MS SQL SERVER pubs database information in the authors table.
Should be written:
ResultSet rs = md.getTables ( "northwind", "dbo", "authors", new String [] ( "TABLE"));
This will enable more effective and reliable procedures.
3. The use of dummy table query to obtain information related to the characteristics of
A dumb query (Dummy Query, Inquiry into dumb I do not know whether it is appropriate, is willing to explore with you) will not produce a record of the result set, such as: select * from tableName where 1 = 0, because the conditions are never established, DB Server will not the implementation of this statement. As a result, firms do not have a record, the dumb query can greatly improve the efficiency of the implementation of the procedures. For example, a table we have to understand the relevant columns of information, the above statement select * from tableName than this statement to be much more efficient, which the database server to retrieve all rows and return a record set, while the former does not need. In response to this problem, JDBC can have the following two methods:
case 1: the use of getColumns () method
/ / getColumns () is a DatabaseMetaData method, the relevant information please refer to the document JDK1.3
ResultSet rs = md.getColumns ( "pubs", "dbo", "authors ",...);// return a result set record
while (rs.next ())// result sets obtained through the rolling out of
System.out.println (rs.getString (4));
case 2: using the getMetaData () method
Statement stmt = conn.createStatement ();
/ / Database server will never be the implementation of this query
ResultSet rs = stmt.executeQuery ( "select * from authors where 1 = 0");
ResultSetMetaData rsmd = rs.getMetaData ();
int colCount = rsmd.getColumnCount ();// get number of rows
for (int col = 1; col <= colCount; col + +)
System.out.println (rsmd.getColumnName (col));
/ /! Out here after the order is a select list order in which they appear, not necessarily listed in order of the corresponding table
Through the above analysis, the second method should be our choice.
4. On the stored procedure call
Since all of the JDBC driver always as string SQL statement sent to the database server, database server, after parsing, the type of authentication parameters, and then converted into the right parameters of the implementation of data types again. For example, such a stored procedure:
CallableStatement cstmt = conn.prepareCall ( "(call getCustomerName (123 )}");
/ / Get the client id specified name, input parameters, id is a positive integer
ResultSet rs = cstmt.executeQuery ();
Here we think that 123 is a positive integer, the actual "call getCustomerName (123)" as the entire string is sent to the database server, database server, after analysis of the precipitation from the "123" will convert it to another integer type value for the to the implementation of stored procedure parameters. Obviously, such a very low efficiency, because we still have things to server known to judge, this is undoubtedly imposing an extra burden on the server. As the optimization of our common way to call stored procedure should be:
CallableStatement cstmt = conn.prepareCall ( "call getCustomerName (?)");
cstmt.setLong (1123); / / the value and type of information encoded Send
ResultSet rs = cstmt.executeQuery ();
/ / do something
5. The proper use of Statement and PreparedStatement objects and execute methods
Statement is the only implementation of a query designed to optimize, PreparedStatement object is two or more times for the same query and design. PreparedStatement object the first time the implementation of a query prepared to spend a certain price, but the benefits of it for future inquiry is accelerated; because the SQL statement has been compiled and put in cache, you can always re-use; want to change the query result set by different methods setXXX Just change the host variable (?) on the list of values.
PreparedStatement and CallableStatement are due to the subclass Statement, they have the execute (String sql), executeQuery (String sql), executeUpdate (String sql), executeBatch () method.
execute (String sql) method returns a boolean value, it is complex to execute arbitrary sql statements, can produce multiple result sets. Return results if there is true, If you do not have a result set or update only a few will remember to return false. The result of it can set getResultSet () and getMoreResults () obtained by counting update getUpdateCount () access. Obviously execute (String sql) method to the use of complicated, so if only a simple query or update operations use the executeQuery (String sql) and executeUpdate (String sql) method. executeUpdate (String sql) to perform INSERT, UPDATE, DELETE statements, and DDL and DML commands (At this point the return value is 0).
If you need to update more, just after these updates packaged together with an order submitted to the database once to deal with all the requests, submitted by more than one by one to be much more efficient. For example:
/ / Save the current model submitted
boolean commitState = conn.getAutoCommit ();
/ / Turn off auto-commit mode
conn.setAutoCommit (false);
Statement stmt = conn.createStatement ();
/ / One by one by adding
stmt.addBatch ( "INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch ( "INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch ( "INSERT INTO emp_dept VALUES (1000, 260)");
/ / First submission
int [] updateCounts = stmt.executeBatch ();
so that the entry into force of conn.commit ();// update
conn.setAutoCommit (commitState); / / revert to the original submission mode
PreparedStatement object and use the basic CallalbeStatement with the same Statement, please refer to JDBC2.1API.
6. The right to use the cursor
JDBC2.1 core API provides three result set types: forward-only, scroll-insensitive and scroll-sensitive.
forward-only: only the former type. If you only need to scroll to the order of the results of focus on all the line, only to move the cursor to provide very high performance; However, it can not be a direct line from the first scroll to the last line, nor from the first line of the last line of rolled.
scroll-insensitive: do not scroll-sensitive. For higher-level applications to deal with, do not scroll-sensitive result set is an ideal choice, it supports the forward and backward rolling of the recordset. Scroll-sensitive result set is not the first request is from the database server to satisfy the conditions of access to all the line, and then it is stored in the client, that is included in the data is a static view of the client; Although relatively quickly after the operation, However, the database server to deal with the request of the first very slow, especially when the return is larger than the data. Therefore, if only to return to his record we should not use the cursor, use only move on to meet the demand; the other hand, if the return of many of the records, the cursor does not recommend the use of this, because these data are stored in memory , the large amount of data so that memory will soon be exhausted. Some scroll insensitive cursor is the realization of the data cache to the database server in a temporary table in order to avoid a disproportionate amount of memory resources.
scroll-sensitive: scroll-sensitive, sometimes called keyset-driven cursor. It is in your database records on line to meet the conditions made a logo, like the primary key line, when you rolling when the result set, only the data will have a logo to return to. Because each request must have a network connection, so speed is very slow.
7. Only to return to the needs of the rows or columns
last Saturday listening to the core of Oracle's analysis, I find out is to query or update the table, the database is in fact low-level operation of the disk file read or write, and I / O operations the greater the amount of data more time-consuming, floppy disk to read and write speed for everyone to see. In order to avoid unnecessary data transmission, please exercise caution when using the select * from ... such a statement, if only there is no need to return to an all out, especially when you do not need the column containing large data types (such as BINARY, BLOB, CLOB) or a larger amount of data will affect the system performance.
8. The use of connection pooling
Connection pooling for database access performance is improved significantly because of the creation and destruction of a connection is very expensive price. Connection pooling to achieve a shared database connection, a connection object can be repeatedly used a number of users. Managed by the container connection pool like a leasing companies, who use to him on a return to use after me, and then letting the next use, so that each request must be removed from the new building , and used again after it threw.
On the connection pool technology more complicated, but you can also write their own connection pool object, if you read "JAVA2 high-level programming."

[Reference]
1.MS SQL SERVER 2000 JDBC DRIVER HELP
2. "JAVA2 high-level programming"
3. "JAVA2 core technology II"
4. "J2EE experts to build enterprise-class system solutions"
5. "JSP high-level programming"
6.SUN JDBC2.1 API


Jsp And Database Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.