Java application developers come across JDBC (Java Database Connectivity) when it comes to connecting to a database. There is a JDBC driver, which is used by a Java application to connect to a database, and a JDBC API, which is related to programming.
JDBC lets a developer connect to several databases. It is essentially the interface for connecting to relational databases from Java framework, and it also enables developers to issue database queries, update and receive results. JDBC can be used to execute DML, DDL and Stored Procedures as well.
Before starting with JDBC, a developer needs a Java JDK copy. It there’s not one available, you can access the JDK/SDK free of cost at Sun’s Java site, or it may be present with any available IDEs, such as NetBeans and Eclipse. Once the JDK is available, the next thing required is the correct JDBC driver for the database, which in most cases will be provided by the database vendor.
While doing all this, Java developers can increase the efficiency of using JDBC to establish a database connection and avoid potential errors, which would improve overall performance. This can be done through the following ways:
Turn off auto commit
This is one of the tips that can lead to substantial gains in performance in the JDBC batch update. SQL queries should be executed with auto commit mode turned off. This is because when the auto commit mode is disabled, the SQL Statement can be grouped in one transaction while in an active auto commit mode, every SQL statement has to execute its own transaction and committed as soon as the process ends. So it pays to run queries with a disabled auto commit mode.
Connect to a modern database
This is another JDBC tip that is gaining popularity. The rationale behind this is that modern databases can successfully integrate with the enterprise development landscape and provide latest JDBC drivers. A Type 4 JDBC driver enables a Java application developer to integrate with either MySQL or a MariaDB connector. Developers are recommended to look for open source Java connectors that are licensed (licensed does not always mean they come with a price tag).
Opt for Bind variables over String concatenation
PreparedStatement should be used in Java for better performance, but this increase in performance is only possible if the developer utilizes bind variables donated by ‘place holders’ or ‘?’, as these enable database to run same queries, but with different parameters. It is also a great way to enhance protection against SQL injections.
Developers should close any resource in finally block as soon as they are working on that. For example, JDBC classes and JDBC Connection are resource intensive and should be shut down in finally block to ensure releases of connection in case of SQLException. Developers using Java 7 or higher can use ARM (Automatic Resource Management) Block to configure resources to close automatically.
Save network traffic bytes
Make sure that the database packet size is set to maximum and the driver is matching the packet size. For larger results sets, the particular configuration brings down the total packets sent/received between the server and the driver.