JDBC connection database
Programming involving establishing a JDBC connection is quite simple. Here are four simple steps:
Importing JDBC package:
The import statement tells the Java compiler where to find the references in the code and place the class at the beginning of your source code.
Using the standard JDBC package, it allows selection, insertion, update and delete data from SQL tables, adding the following imports to your source code:
import java.sql.* ; // for standard JDBC programsimport java.math.* ; // for BigDecimal and BigInteger support
Register the JDBC driver:
Before using it, you must register your driver in the program. A registered driver is an implementation process in which the class file of the Oracle driver is loaded into memory so that it can be used as a JDBC interface.
You need to do this registration only once in your program. A driver can be registered in one of two ways.
Method (I) - Class.forName():
The most common method in registering a driver is to use Java's Class.forName() method to dynamically load the driver's class files into memory, and it will automatically register it. This approach is desirable because it allows the driver to register configuration for easy portability.
The following example uses Class.forName() to register an Oracle driver:
try { Class.forName("oracle.jdbc.driver.OracleDriver");}catch(ClassNotFoundException ex) { System.out.println("Error: unable to load driver class!"); System.exit(1);}You can use the getInstance() method to resolve incompatible JVMs, but two additional exceptions are written as follows:
try { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();}catch(ClassNotFoundException ex) { System.out.println("Error: unable to load driver class!"); System.exit(1);catch(IllegalAccessException ex) { System.out.println("Error: access problem while loading!"); System.exit(2);catch(InstantiationException ex) { System.out.println("Error: unable to instantiate driver!"); System.exit(3);} Method (II) - DriverManager.registerDriver():
The second way to use it to register a driver is to use the staticDriverManager.registerDriver() method.
It should, if you are using an incompatible JDK JVM, for example, Microsoft provides a method using registerDriver().
The following example uses registerDriver() to register the Oracle driver:
try { Driver myDriver = new oracle.jdbc.driver.OracleDriver(); DriverManager.registerDriver( myDriver );}catch(ClassNotFoundException ex) { System.out.println("Error: unable to load driver class!"); System.exit(1);} Database URL formulation:
When loading the driver, you can establish a connection in the program using the DriverManager.getConnection() method. For easy reference, let's list three overloaded DriverManager.getConnection() methods:
Here, each form requires a database URL. The URL of the database points to the database address.
Developing a database URL is most commonly used to establish connections.
The following table lists the popular JDBC driver names and database URLs.
RDBMSJDBC driver name URL format
MySQL com.mysql.jdbc.Driver jdbc:mysql://hostname/ databaseNameORACLE oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@hostname:port Number:databaseNameDB2 COM.ibm.db2.jdbc.net.DB2Driver jdbc:db2:hostname:port Number/databaseNameSybase com.sybase.jdbc.SybDriver jdbc:sybase:Tds:hostname:port Number/databaseName
All highlighted parts in URL format are static, and only the remaining parts need to be changed according to the database settings.
Create a connection object: Use the username and password of the database URL:
The following three forms of DriverManager.getConnection() method are used to create a connection object. The most common form of getConnection() requires passing a database URL, username and password:
The value of databaseName for the URL database: Assuming that you are using Oracle's thin driver, you need to specify a host: port.
Suppose there is a host TCP/IP address 192.0.0.1 and the host name and Oracle listener are configured on port 1521, the database name is EMP, and then the complete database URL is:
jdbc:oracle:thin:@amrood:1521:EMP
Now, the appropriate username and password and getConnection() method must be called to obtain a Connection object as shown below:
String URL = "jdbc:oracle:thin:@amrood:1521:EMP";String USER = "username";String PASS = "password"Connection conn = DriverManager.getConnection(URL, USER, PASS);
Use only one database URL:
The second form of DriverManager.getConnection() method only requires a database URL:
DriverManager.getConnection(String url);
However, in this case, the URL of the database, including the username and password, has the following general form:
jdbc:oracle:driver:username/password@database
So the above connection can be created as follows:
String URL = "jdbc:oracle:thin:username/password@amrood:1521:EMP";Connection conn = DriverManager.getConnection(URL);
Use the database URL and a Properties object:
The third form of DriverManager.getConnection() method requires a database URL and a Properties object:
DriverManager.getConnection(String url, Properties info);
Properties object, saves a set of keyword-value pairs. It is used to call the getConnection() method when the driver attribute is passed to the driver.
To make the same connection made through the previous example, use the following code:
import java.util.*;String URL = "jdbc:oracle:thin:@amrood:1521:EMP";Properties info = new Properties( );info.put( "user", "username" );info.put( "password", "password" );Connection conn = DriverManager.getConnection(URL, info);
Close JDBC connection:
At the end of the JDBC program, it explicitly requires that all connections to the database be closed to end each database session. However, if you forget, the Java garbage collector closes the connection and it clears the stale objects.
Relying on garbage collection, especially in database programming, is a very poor programming habit. It should always be in the habit of closing the close() method associated with the connection object.
To ensure that the connection is closed, it can be executed in finally blocks in the code. Finally blocks will be executed, whether or not occur or not.
To close the connection opened above, the close() method should be called as follows:
conn.close();
Explicitly close connections to DBMS to save resources.
Create a JDBC application:
There are six steps involved in building a JDBC application:
Sample code:
This example can be used as a template when you need to build a JDBC application.
This sample code is written based on the environment and database installation in the previous chapter.
Copy the following example FirstExample.java, compile and run as follows:
//STEP 1. Import required packagesimport java.sql.*;public class FirstExample { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/EMP"; // Database credentials static final String USER = "username"; static final String PASS = "password"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ //STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); //STEP 3: Open a connection System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); //STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql; sql = "SELECT id, first, last, age FROM Employees"; ResultSet rs = stmt.executeQuery(sql); //STEP 5: Extract data from result set while(rs.next()){ //Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); //Display values System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); } //STEP 6: Clean-up environment rs.close(); stmt.close(); conn.close(); }catch(SQLException se){ //Handle errors for JDBC se.printStackTrace(); }catch(Exception e){ //Handle errors for Class.forName e.printStackTrace(); } finally{ //finally block used to close resources try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ }// nothing we can do try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); }//end finally try }//end try System.out.println("Goodbye!"); }//end main}//end FirstExampleNow let’s compile the above example as follows:
C:>javac FirstExample.java
When running FirstExample, it produces the following results:
C:>java FirstExample
Connecting to database...Creating statement...ID: 100, Age: 18, First: Zara, Last: AliID: 101, Age: 25, First: Mahnaz, Last: FatmaID: 102, Age: 30, First: Zaid, Last: KhanID: 103, Age: 28, First: Sumit, Last: Mittal