Getting started with Java Database Connectivity (JDBC)

Many a times in testing, we face a situation where we need to test the data into database. Database testing usually includes verifying the field length validation, checking the soft delete of a record, checking the business logic i.e. whether the data is correctly getting manipulated on the basis of the business rules etc. Well before we actually dig deep into the concept of database testing. let’s kick start with a Java API which is commonly used to interact with data sources.




When testing database using Selenium, we connect our JAVA program to Database via JDBC (Java Database Connectivity). We can easily perform CRUD (Create/Read/Update/Remove) operations on Database with the help of JDBC. Basically we perform 3 activities with the help of JDBC as below:

1. Get Connection to Database.
2. Execute CRUD queries on the database.
3. Get ResultSet as result of query after executing the query to database.

Now let’s try a very basic read operation with database. First we will try on command line then we will try with Java Program.

Command Line: I hope you have installed MySQL. If you haven’t installed MySQL, then first install MySQL.My SQL can be easily downloaded from http://dev.mysql.com/downloads/ .

Let’s connect to MySQL server and type “show databases;” to see databases list. Then type “use test;” to select test database. And type “show tables;” to list down all the tables available in test database.

Now let’s see with the help of following example how this works does. Following is a sample database and table it consists of:

The test database consists of a ‘users’ table only. Type select * from users, this will list down all user data. ‘users’ table basically consists of following 3 columns:

1. Name
2. City
3. Sex

It basically has two records:

Java Program:The first and foremost thing to remember while working with JDBC is that, there are different set of connectors for connecting to different databases. These connectors are available as jars and they need to be imported to your project library before starting. Since we are working with MySQL, so therefore we have used MySQL  connector jar. The one which I have imported to my project is “mysql-connector-java-5.0.8-bin.jar”.

Now, let us have a look at the basic steps that are required for the JDBC connectivity:

Now let us see how we accomplish these steps with the help of java code. I have marked the five lines in the following code in red so that they can be mapped tothe step diagram given above.
//importing sql package
import java.sql.*;
public class DatabaseConnection {
	public static void main(String[] args) {
		/*Connection is an interface which helps you establish                           connection with database.*/
		Connection conn = null;
		//Defining the SQL URL
		String url = "jdbc:mysql://localhost:3306/";
		//Defining the database name
		String dbName = "test";
		//Defining the driver that is being used
		String driver = "com.mysql.jdbc.Driver";
		//Defining username and password
		String userName = "root";
		String password = "root";
		try {
			//Loading the driver and creating its instance
			Class.forName(driver).newInstance();
			//Establishing the connection with the database
			conn = DriverManager.getConnection(url+dbName, userName,                        password);
			/*createStatement() method creates a Statement object                           for sending SQL to the database.
			 *It executes the SQL and returns the result it produces
			 */	
			Statement stmt = conn.createStatement();
                        /*executeQuery() method executes the SQL statement which                         returns a single ResultSet type object.*/
			ResultSet rs = stmt.executeQuery("Select * from users");
			/*next() returns true if next row is present otherwise                           it returns false. */
			while(rs.next()){
			//printing tge result
			System.out.println(rs.getString("name"));
			}
		} catch(Exception e){
			System.out.println("Exception Encountered");
		}
	}
}

The output of the above code is:
Krishna
Ram
In the next blog we will learn more about various classes in JDBC API, till then Happy Learning!