To establish a connection with a database, you need to load the database specific driver by calling the forName() method of the class Class. For e.g. JDBC – ODBC Bridge driver is used to communicate with DBMS / RDBMS such as MS SQL, MS Access. You can load this driver like shown below.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
The Java.sql package contains classes and interfaces that helps in connecting to a database, sending SQL statements to the database and processing query results.
The Connection Object
The Connection object represents a connection with the database. You may have several Connection objects in your application that connects one or more database. The getConnection() method from DriverManager class is used to establish a connection with the database. This method attempts to locate the driver that can connect to the database represented by the JDBC URL passed to the getConnection() method. The JDBC URL is a string that provides a way of identifying the database. A JDBC URL consists of three parts.
<protocol>:<subprotocol>:<subname>
<protocol> in a JDBC URL is always jdbc.
<subprotocol> is the name of the database connectivity mechanism. If the mechanism for retrieving the data is ODBC – JDBC bridge, then the subprotocol should be odbc.
<subname> is used to identify database. This is also known as data source name. This contains information about the location of the database server, database name, user name and password to be used to connect the database server and the log file name to be used to store lengthy queries and driver and statistics.
The Statement Object
You can use the Statement object to send simple queries to the database. The Statement object allows you to execute simple queries. It has the following methods that can be used for querying.
- The executeQuery() method exectes a select query and returns a ResultSet object.
- The executeUpadate() executes an SQL Insert, Update or Delete statement and returns an int value specifying the number of rows affected by the query or 0 if the query does not return any value.
A sample program that connects to a Database using JDBC.
import java.sql.*;
public class QueryApp
{
public static void main(String a[])
{
try {
Class.forName("sun.jdbc.JdbcOdbcDriver"); // Loads the driver
Connection con = DriverManager.getConnection("jdbc:odbc:MyDsn","UserName","password"); //establishes the connection to a database.
Statement stat=con.createStatement(); // Returns a statement object
stat.executeQuery("Select * from tablename");
} catch(Exception e) {
System.out.println("Error"+e);
}
}
}
The ResultSet Object
The ResultSet object provides you methods to access data from a table. Executing a SQL statement usually generates a ResultSet object. The ResultSet object maintains a cursor pointing to its current row of data. Initially a cursor is positioned before the first row. The next() method moves the cursor the next row. You can retrieve data from the ResultSet rows by calling the getXXX(int coln) method where XXX refers to the datatype of column name such as String, an Integer or a Float. And coln refers to column number in the result set. The following table illustrates some of the widely used getXXX() methods.
Method | Description |
String getString(int column_number) | Returns the value in the specified column of the current row in the ResultSet object as a Srting object. This method is used with char(n) and varchar(n) data types of SQL. |
int getInt(int column_number) | Returns the value in the specified column of the current row in the ResultSet object as a integer. This method is used with smallint, tinyint and int data types of SQL. |
float getFloat(int column_number) | Returns the value in the specified column of the current row in the ResultSet object as a float type. This method is used with float and money data types of SQL. |
Date getDate(int column_number) | Returns the value in the specified column of the current row in the ResultSet object java.sql.Date object. This method is used with datetime data types of SQL. |
Sample Program
import java.sql.*;
public class QueryApp
{
public static void main(String a[])
{
try {
Class.forName("sun.jdbc.JdbcOdbcDriver"); // Loads the driver
Connection con = DriverManager.getConnection("jdbc:odbc:MyDsn","UserName","password"); //establishes the connection to a database.
Statement stat=con.createStatement(); // Returns a statement object
ResultSet rs=stat.executeQuery("Select * from tablename");
While(rs.next()){
System.out.println(rs.getString(2));
}
} catch(Exception e) {
System.out.println("Error"+e);
}
}
}
