Learn JDBC Programming Basic Steps
After learning what is JDBC, let us see how to write and learn JDBC program.
The whole Learn JDBC Program can be divided into 8 basic steps.
- Import the java.sql package
- Load the driver
- Establish the connection with the database using the driver
- Create a "Statement" object
- Send a SQL command using Statement object
- Retrieve records or data from the database
- Customize the records received as per client requirement
- When the job is over close the database connection
Let us discuss the above steps elaborately as they are requied in every JDBC program.
Learn JDBC : 1. Import the java.sql package
Import the java.sql package, as follows, as it contains all the classes and interfaces required to write a Java database connectivity code like DriverManager, Connection and Statement etc.
import java.sql.*;
Learn JDBC : 2. Load the driver
It is discussed earlier in JDBC Driver Types what a driver is. The driver used here is the bridge driver (known as Type-1) driver – "JdbcOdbcDriver".
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
JdbcOdbcDriver is a Java class available in the package "odbc". odbc a subpackage of "jdbc". And again jdbc is a subpackage of "sun" package. The forName() method of java.lang.Class loads the driver. Following is the class signature of forName() method.
public static java.lang.Class forName(java.lang.String) throws java.lang.ClassNotFoundException;
The string parameter passed is the name of the driver along with the address and the return type is Class. Anyhow, this return type Class object is not used anywhere in our coding. So, do not worry about the return type of forName() method. Anyhow if you are interested right now, the Class object is used in Creating Java Object with forName() and Retrieve fields with Reflection API.
The forName() method dynamically loads the driver JdbcOdbcDriver (remember, it is a Java class) into the RAM and makes it avaialble to DriverManager to connect to database. If the driver is not available or misspelled (Many people do not follow the case of the letters. Writing lowercase to uppercase or vice versa is also liable for exception), the method throws ClassNotFoundException.
As an alternative, the above statement can be replaced as follows.
DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
Learn JDBC : 3. Establish the connection with the database using the driver
Connection con = DriverManager.getConnection("jdbc:odbc:snrao", "scott", "tiger");
getConnection() is a static method of DriverManager class which takes three string parameters.
jdbc:odbc:snrao : This first parameter describes the protocol used. Here, odbc is the protocol which is again a subprotocol of jdbc (note: in the previous statement, jdbc and odbc are package names, do not get confused). snrao is the DSN (Data Source Name). We learn later how to create a DSN.
scott : It is the user name of the database. Here. Oracle is used.
tiger : It is the password to open the database.
If all the parameters are passed very correctly, this method returns an object of Connection interface. Connection interface object, in the above statement is con. con represents the actual connection between Java and Database. On this object, all the remining code runs. If the parameters passed are incorrect, the method throws a checked exception SQLException.
Learn JDBC : 4. Create a "Statement" object
Once the Connection is established, the next job is to create Statement object.
Statement stmt = con.createStatement();
The createStatement() method of Connection interface returns an object Statment interface, here it is stmt. The Statement object is used to move between Java and database (like a transport service agent, Java to databse and database to Java). Infact, this object carries SQL command to database and gets back the records or data returned by the databse to the Java program. This is shown in the next step.
Following is the method signature as defined in Connection interface.
public abstract java.sql.Statement createStatement() throws java.sql.SQLException;
Learn JDBC : 5. Send a SQL command using Statement object
Learn JDBC : 6. Retrieve records or data from the database
ResultSet res = stmt.executeQuery("select * from Employee");
The executeQuery() method carries the string parameter (nothing but a select command) from Java to database. The database executes the select command and returns all the records. The records returned by the executeQuery() are absorbed into ResultSet object res. Now res contains all the records of Employee table. The return type of executeQuery() method is an object of ResultSet interface. Following is the method signature as defined in Statement interface.
public abstract java.sql.ResultSet executeQuery(java.lang.String) throws java.sql.SQLException;
There exists two commonly used methods in Statement interface – executeQuery(String) and executeUpdate(String). executeQuery() is used for select statements only and for all remaining (like creating table, inserting records etc.) executeUpdate() is used. Following is the method signature.
public abstract int executeUpdate(java.lang.String) throws java.sql.SQLException;
executeUpdate() takes a string parameter and is nothing but any SQL command (except select) which you actually type at SQL prompt. The return type is an int data byte that depicts the number of records affected by the SQL update command. Return type int is shown in JDBC – Delete Record Example.
int numberOfRecordsAffected = stmt.executeUpdate(“insert into Employee values(100, ‘SNRao’, 5500.50)”);
In the above statement, the value numberOfRecordsAffected is 1 as only one record is inserted.
int numberOfRecordsAffected = stmt.executeUpdate(“delete from Employee where empid=100”);
In the above statement, the value numberOfRecordsAffected is that of how many records are deleted in the database satisfying the condition empid=100.
Learn JDBC : 7. Customize the records received as per client requirement
Suppose the client would like to print all the records. Following is the way.
We know in the previous step, ResultSet object contains all the records returned by the database.
while(res.next())
{
int id = res.getInt(1);
String str = res.getString(2);
double salary = res.getDouble(3);
System.out.println(id + ", " + str + ", " + salary + ", ");
}
The above loop prints all the records of the Employee table. The working of the above while loop is explained with internal details figuratively in Select Records Example.
The parameter 1, 2 and 3 are the field numbers in the Employee table (represented as int datatype). Many getXXX() methods exist in the ResultSet interface and few are given below. How the loop works is discussed in the later program.
- public abstract java.lang.String getString(int) throws java.sql.SQLException;
- public abstract boolean getBoolean(int) throws java.sql.SQLException;
- public abstract byte getByte(int) throws java.sql.SQLException;
- public abstract short getShort(int) throws java.sql.SQLException;
- public abstract int getInt(int) throws java.sql.SQLException;
- public abstract long getLong(int) throws java.sql.SQLException;
- public abstract float getFloat(int) throws java.sql.SQLException;
- public abstract double getDouble(int) throws java.sql.SQLException;
Learn JDBC : 8. When the job is over close the database connection
When all the work with the database is finished, close the database connection (like you close in I/O streams). Once closed, the connected between Java and database is destroyed. The objects are garbage collected and the memory occupied by the objects is freed.
if(res != null)
res.close();
if(stmt != null)
stmt.close();
if(con != null)
con.close();
The best place for closing statements is finally block.
What is DSN and creating DSN is given in Creating Data Source Name (DSN) JDBC.
