Advertisement

---

Top

< Prev

Next >

Java Database Connectivity(JDBC) API provide a set of classes and interfaces that allow a Java application to -

• Connect a Java application with a database.
• Execute Structured Programming Language(SQL) statements.
• Process the result of executing SQL statements.
• Finally, close the connection between Java application and the database.

Note :

These JDBC classes and interfaces are available in java.sql and javax.sql packages.

Some important JDBC classes and interface

JDBC classes and interface required to connect a Java application to a database are -

• Driver interface - A Java application can access a database through its driver. Driver interface represents the database driver that is used to establish the connectivity of a Java application with the database.


• DriverManager class - DriverManager class is used to load and register the driver of any particular database in order to connect a Java application to this database.


• Connection interface - It enables a Java application to connect to a database.


• Statement interface - It enables a Java application to execute Structured Query Language(SQL) statements in order to perform database operations.


• ResultSet interface - It represents a set of data stored in a table, which is the result of executing SQL statements to retrieve the data from the database.


• SQLException class - It represents an exception raised which interrupted the connection between the Java application and the database.

Steps to connect a Java application to a database

• Loading and registering the database driver using forName() method.
We can load and register a database driver in just one step to connect a Java application with a database using forName() method of class java.lang.Class. For example, to load and register a JDBC-ODBC Type-1 driver using forName() method -



Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");






• Connecting a Java Application with a database using DriverManager interface.
After the database driver is loaded and registered, in order to connect a Java application to a database, we must obtain a Connection object using DriverManager which has three overloaded methods, which gives us a Connection object, such as -




1. getConnection(String url)



String url  = "jdbc:odbc:MyDataSource";
Connection con = DriverManager.getConnection(url);


where a JDBC url contains three parts, such as - jdbc : <sub-protocol> : <info>


• jdbc is the name of the protocol used to access the database


• <sub-protocol> represents the sub-protocol used to access the database, in our example we have used JDBC-ODBC driver to access a database, hence this <sub-protocol> has a value odbc.


• info represents the Data Source Name which is the name used to refer to a database and it may also contain username and password to connect to the database. In our example, we have only used the name of our data source i.e. MyDataSource.





2. getConnection(String url, String username, String password)



String url  = "jdbc:odbc:MyDataSource";
Connection con = DriverManager.getConnection(url, "scott", "tiger");


scott is the our username to connect to Oracle database and tiger is our password.




3. getConnection(String url, Properties info)



String url  = "jdbc:odbc:MyDataSource";
Properties p = new Properties();
p.setProperty("username","scott");
p.setProperty("password","tiger");
Connection con = DriverManager.getConnection(url, p);


Here we have used Properties class to specify username and password.





Advertisement






Executing SQL statements using JDBC.

Using Connection object, we can create a Statement object through which you can execute Structured Query Language(SQL) statements in order to perform database operations through a Java application.


Statement interface has three methods that allow us to execute SQL statements -



1. executeQuery() - This method is used to retrieve data from database by executing the Select SQL statement. It returns the ResultSet object which is used to retrieve the information.



String url  = "jdbc:odbc:MyDataSource";
Connection con = DriverManager.getConnection(url, "scott", "tiger");
Statement stmt = con.createStatement();
ResultSet rs= stmt.executeQuery("SELECT * from MyTable);






2. executeUpdate() - This method is used to perform insert, update, delete operations by executing INSERT, UPDATE and DELETE SQL statements. It returns the number of rows affected by executing SQL statement.



String url  = "jdbc:odbc:MyDataSource";
Connection con = DriverManager.getConnection(url, "scott", "tiger");
Statement stmt = con.createStatement();
int count = stmt.executeQuery("INSERT into MyTable values ("1", "Maria", "Wisconsin"));






3. execute() - This method is used to execute any SQL operation. It returns a boolean value -
• true, if execute() method has returned a ResultSet object by executing SELECT SQL statement.
• false, if execute() method has returned a total count of rows affected by executing INSERT, DELETE, UPDATE SQL statement.



String url  = "jdbc:odbc:MyDataSource";
Connection con = DriverManager.getConnection(url, "scott", "tiger");
Statement stmt = con.createStatement();
boolean bool = stmt.execute("INSERT into MyTable values ("1", "Maria", "Wisconsin")); 


Over here, bool contains boolean value false as we have executed INSERT SQL statement in execute().






Retrieving the information after executing SQL statements using JDBC

ResultSet interface is used to retrieve the data returned after executing SELECT SQL statement.



String url  = "jdbc:odbc:MyDataSource";
Connection con = DriverManager.getConnection(url, "scott", "tiger");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * from MyTable where name = 'Maria' "); 
while(rs.next())
{
System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3));
}

Closing the connection between Java application and database using JDBC

By calling close() method of Connection interface, we can close the connection between Java application and the database.

con.close();

where con is a reference variable of type Connection

Please share this article -

< Prev

Next >

< Types of JDBC Drivers

JDBC CREATE Table >

Advertisement