Java database connectivity (JDBC) is the JavaSoft specification of a standard application programming interface (API) that allows Java programs to access database management systems. The JDBC API consists of a set of interfaces and classes written in the Java programming language. Using these standard interfaces and classes, programmers can write applications that connect to databases, send queries written in structured query language (SQL), and process the results. JDBC is oriented towards relational databases.
Though it is not standard anymore to use JDBC directly into your application because we got many more robust APIs to do this job for us e.g. hibernate and MyBatis. But, if you are still struck in there due to specific requirements or simply you are learning it, then below suggestions will help you in writing more fast and efficient code.
Sections in this post: Use Object Pooling Consider MetaData Performance Choose Commit Mode carefully Save Some Bytes On Network Traffic
Let’s directly jump into discussion.
Use Object Pooling Almost Always
Object pooling can happen at two sides:
1) Connection Pooling: Database connections are often expensive to create because of the overhead of establishing a network connection and initializing a database connection session in the back end database. In turn, connection session initialization often requires time consuming processing to perform user authentication, establish transactional contexts and establish other aspects of the session that are required for subsequent database usage.
Additionally, the database’s ongoing management of all of its connection sessions can impose a major limiting factor on the scalability of your application. Valuable database resources such as locks, memory, cursors, transaction logs, statement handles and temporary tables all tend to increase based on the number of concurrent connection sessions.
Enabling Connection pooling allows the pool manager to keep connections in a “pool” after they are closed. The next time a connection is needed, if the connection options requested match one in the pool then that connection is returned instead of incurring the overhead of establishing another actual socket connection to the server.
2) Statement Pooling: Starting from JDBC 3.0, JDBC standards define a statement-caching interface. Setting the MaxPooledStatements connection option enables statement pooling. Enabling statement pooling allows the driver to re-use Prepared Statement objects. When Prepared Statements are closed they are returned to the pool instead of being freed and the next Prepared Statement with the same SQL statement is retrieved from the pool rather than being instantiated and prepared against the server.
Statement caching can do the following:
- Prevent the overhead of repeated cursor creation
- Prevent repeated statement parsing and creation
- Reuse data structures in the client
Please ensure your driver support this feature, and is enabled by default or not. A sample code can be something like this if you are doing it programatically.
Properties p = new Properties(); p.setProperty("user", "root"); p.setProperty("password", "password"); p.setProperty("MaxPooledStatements", "200"); connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/JDBCDemo", p);
Read more about statement pooling here: http://docs.oracle.com/cd/B28359_01/java.111/b31224/stmtcach.htm
Consider MetaData Performance Also
If you are dealing with metadata into your code then it is another important area to look after. Here the first tip is to use as many parameters (or filters) to fetch metadata as much you can specify. For example, don’t call getTables like this:
DatabaseMetaData dbmd = connection.getMetaData(); ResultSet rs = dbmd.getTables(null,null,null,null);
Specifying at least the schema will avoid returning information on all tables for every schema when the request is sent to the server:
DatabaseMetaData dbmd = connection.getMetaData(); ResultSet rs = dbmd.getTables(null,"testDB",null,null);
Secondly, remember that most JDBC drivers populate the ResultSetMetaData object at fetch time when the needed data is returned in select queries. Use this information instead of getting data from DatabaseMetaData which is addionational request and avoidable in most cases.
selectStmt = connection.createStatement(); ResultSet rs = selectStmt.executeQuery("SELECT ID,FIRST_NAME,LAST_NAME,STAT_CD FROM EMPLOYEE WHERE ID <= 10"); ResultSetMetaData rsmd = rs.getMetaData(); rsmd.getColumnCount(); rsmd.getColumnName(0); rsmd.getColumnType(0); rsmd.getColumnTypeName(0); rsmd.getColumnDisplaySize(0); rsmd.getPrecision(0); rsmd.getScale(0);
Choose Commit Mode carefully
When writing a JDBC application, make sure you consider how often you are committing transactions. Every commit causes the driver to send packet requests over the socket. Additionally, the database performs the actual commit which usually entails disk I/O on the server. Consider removing auto-commit mode for your application and using manual commit instead to better control commit logic.
Code to use is:
Save Some Bytes On Network Traffic
To reduce network traffic, following suggestions can be looked and adapted in appropriate for your application.
- Use addBatch() instead of using Prepared Statement to insert data when working with high volume clients. This sends multiple insert requests in a single network packet and save some bytes for you.
- Do not use “select * from table”. Instead specify column names which are actually needed. I will suggest that make it a practice because many times we are doing it without realizing it’s negative impacts. Just imagine if you do this in a table where you are storing BLOBs also. You fetch such heavy objects from database and do not use it. What a waste.
- Ensure that your database is set to the maximum packet size and that the driver matches that packet size. For fetching larger result sets, this reduces the number of total packets sent/received between the driver and server.
That’s all for this post. Post a comment if you have some doubts.
Happy Learning !!