How to Connect to the Database Using JDBC API

Java Database Connectivity

JDBC is the standard Java API required for database-independent connectivity between the Java programming language and many databases. This application program interface (API) lets users encode access request statements in a Structured Query Language (SQL). They are then passed to the program that manages the database. It involves opening a connection, creating a SQL Database, executing SQL queries, and arriving at the output.

Why use JDBC

Before JDBC, ODBC API was the database API to connect and execute query with the database. 
But, ODBC API uses ODBC driver which is written in C language (i.e. platform dependent and unsecured). 
That is why Java has defined its own API (JDBC API) that uses JDBC drivers (written in Java language).

What is API

API (Application programming interface) is a document that contains description of all the features of a product or software. 
It represents classes and interfaces that software programs can follow to communicate with each other.
 An API can be created for applications, libraries, operating systems, etc


Types of Drivers:
1)JDBC-ODBC bridge driver
2)Native-API driver (partially java driver)
3)Network Protocol driver (fully java driver)
4)Thin driver (fully java driver)

Steps to create a JDBC Application

To create a JDBC application, follow the steps below:

1. Import the packages: Include the packages that contain the JDBC classes required for database programming.
2. Register the JDBC driver: Initialize a driver to open a communication channel with the database. Register to the database with the command:
   Class.forName(“com.mysql.jdbc.Driver”); // class.forName load the Driver class
3. Open a connection: After the driver registration, use the getConnection() method to create a Connection object, representing a physical connection with the database.
4. Execute a query: Use an object of type ‘Statement’ to build and submit a SQL statement to the database.
5. Extract data from the result set: Use the appropriate getXXX() method.
6. Clean up the environment: Close all database resources that rely on JVM garbage collection

How to Connect to the Database and get the data
     
1)load the driver 
//loading the driver
   Class.forName("oracle.jdbc.driver.OracleDriver");           
2)connect to the database
Connection con= DriverManager.getConnection(url,un,pwd);
String url =jdbc:oracle:thin:@localhost:1521:orcl
3)create a statement
Statement st=con.createStatement();
4)fetch the results
ResultSet res=st.executeQuery("select * from EMP");

class methodname interface
Class forName("drivertype") NA

DriverManager getConnection(url,un,pwd) Connection
 NA createStatement() Statement/PreparedStatement/CallableStatement
 NA executeQuery("statements") ResultSet
 Some of the benefits of PreparedStatement over Statement are: PreparedStatement helps us in preventing 
 SQL injection attacks because it automatically escapes the special characters. 
 PreparedStatement allows us to execute dynamic queries with parameter inputs.
 CallableStatement is used to execute stored procedures(big sql programs)
 ---------------------------------------------------------------------------------------------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * Simple Java Program to connect Oracle database by using Oracle JDBC thin driver
 * Make sure you have Oracle JDBC thin driver in your classpath before running this program
 * @author
 */
public class OracleJdbcExample {

    public static void main(String args[]) throws SQLException {
        //URL of Oracle database server
        String url = "jdbc:oracle:thin:@localhost:1632:DEVROOT32"; 
      
        //properties for creating connection to Oracle database
        Properties props = new Properties();
        props.setProperty("user", "scott");
        props.setProperty("password", "tiger");
     try{ 
  //1)load the driver
   Class.forName("oracle.jdbc.driver.OracleDriver");
  
        //creating connection to Oracle database using JDBC
       2) Connection conn = DriverManager.getConnection(url,props);

        String sql ="select sysdate as current_day from dual";

        //creating PreparedStatement object to execute query
       3) PreparedStatement preStatement = conn.prepareStatement(sql);
//fetch the result set.
      4) ResultSet result = preStatement.executeQuery();
      
        while(result.next()){
            System.out.println("Current Date from Oracle : " +         result.getString("current_day"));
        }
        System.out.println("done");
      }catch(SQLException e){
  e.printStackTrace();
  }finally{
  conn.close();
  result.close();
    }

}

=================================================

Database testing using Selenium

Step 1: Create a database in command prompt and insert the tables.
Step 2: Establish a connection to the database using JDBC.
Step 3: Execute the MySQL queries and process records present in the database.
Step 4: Integrate TestNG with JDBC to perform Database Testing.

Have a look at the script below:

import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SeleniumDatabaseTesting {
// Connection object
static Connection con = null;
// Statement object
private static Statement stmt;
// Constant for Database URL
public static String DB_URL = "jdbc:mysql://localhost/Testdata";
//Database Username
public static String DB_USER = "your_user";
// Database Password
public static String DB_PASSWORD = "your_password";

@BeforeTest
public void setUp() throws Exception {
try{
// Database connection
String dbClass = "com.mysql.cj.jdbc.Driver";
Class.forName(dbClass).newInstance();
// Get connection to DB
Connection con = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
// Statement object to send the SQL statement to the Database
stmt = con.createStatement();
}
catch (Exception e)
{
e.printStackTrace();
}
}

@Test
public void test() {
try{
String query = "select * from testingdata";
// Get the contents of userinfo table from DB
ResultSet res = stmt.executeQuery(query);
// Print the result untill all the records are printed
// res.next() returns true if there is any next record else returns false
while (res.next())
{
System.out.print(res.getString(1));
System.out.print(" " + res.getString(2));
System.out.print(" " + res.getString(3));
System.out.println(" " + res.getString(4));
}
}
catch(Exception e)
{
e.printStackTrace();
}
}

@AfterTest
public void tearDown() throws Exception {
// Close DB connection
if (con != null) {
con.close();
}
}
}

Database testing using Browserstack Automate

Now, let’s explore how to perform database testing with the help of the Browserstack Automate platform.

The code pattern remains almost the same, except that a couple of desired capabilities and the hub URL needs to be added as shown below:

package testngtest;
import org.openqa.selenium.By;
import org.openqa.selenium.Platform;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.remote.DesiredCapabilities;
import org.openqa.selenium.remote.RemoteWebDriver;
import java.net.URL;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SeleniumDatabaseTesting {
public static final String USERNAME = "nehapramodvaidya";
public static final String AUTOMATE_KEY = Your_key";
public static final String URL = "https://" + USERNAME + ":" + AUTOMATE_KEY + "@hub-cloud.browserstack.com/wd/hub";
public static void main(String[] args) throws Exception {
DesiredCapabilities caps = new DesiredCapabilities();

caps.setCapability("os", "Windows");
caps.setCapability("os_version", "10");
caps.setCapability("browser", "Chrome");
caps.setCapability("browser_version", "80");

caps.setCapability("name", "nehapramodvaidya's First Test");}
// Connection object
static Connection con = null;
// Statement object
private static Statement stmt;
// Constant for Database URL
public static String DB_URL = "jdbc:mysql://localhost/onlinebanking";
//Database Username
public static String DB_USER = "Your_Database_Username";
// Database Password
public static String DB_PASSWORD = "Your_Database_Password";

@BeforeTest
public void setUp() throws Exception {
try{
// Database connection
String dbClass = "com.mysql.cj.jdbc.Driver";
Class.forName(dbClass).newInstance();
// Get connection to DB
Connection con = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
// Statement object to send the SQL statement to the Database
stmt = con.createStatement();
}
catch (Exception e)
{
e.printStackTrace();
}
}

@Test
public void test() {
try{
String query = "select * from user";
// Get the contents of userinfo table from DB
ResultSet res = stmt.executeQuery(query);
// Print the result untill all the records are printed
// res.next() returns true if there is any next record else returns false
while (res.next())
{
System.out.print(res.getString(1));
System.out.print(" " + res.getString(2));
System.out.print(" " + res.getString(3));
System.out.println(" " + res.getString(4));
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
}

==============================================

Below is the code for JDBC MySQl connection and reusable method which return 2 D array.

package bddcucumber.managers.dbmanager;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

/*

 * Author: Ramesh

 * Date: 23Jun2019

 */

public class DBConnectionManager {

private static DBConnectionManager instance;

private Connection conn;

private DBConnectionManager(String url) {

try {

Class.forName("com.mysql.cj.jdbc.Driver");

this.conn = DriverManager.getConnection(url);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public Connection getConnection() {

return this.conn;

}

public static DBConnectionManager getInstance(String url) throws SQLException {

if (instance==null) {

instance= new DBConnectionManager(url);

}else if (instance.getConnection().isClosed()) {

instance= new DBConnectionManager(url);

}

return instance;

}

}

----------------------------------------------------------------------------

package bddcucumber.managers.dbmanager;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import bddcucumber.managers.dbmanager.DBConnectionManager;

/*

 * Author: Akash

 * Date: 23Jun2019

 * Using free MY SQl database for sampling details for the same is as below

 */

/*

 * Database: db_autofrat

   Username: akashdktyagi

   Email: akashdktyagi@gmail.com

   The host name to access the server is db4free.net and 

   the port is 3306. You can use phpMyAdmin on our website to log in to the server.

https://www.db4free.net/

 */

public class DBManager {

//for testing only Should be removed

public static void main(String[] args) throws SQLException {

ArrayList<String> obj = new ArrayList<String>();

obj.add("Akash");

obj.add("Tyagi");

System.out.print(obj);

String q = "Select * from Products";

String [][] result  = FetchDataFromDB(q);

System.out.print(result);

}

public static String[][] FetchDataFromDB(String query) throws SQLException {

//String url = "jdbc:ucanaccess://E:\\_AkashStuff\\Automation\\EclipseWorkspace\\AutomationPoCCucumber\\src\\test\\resources\\dbtable\\mySQL.accdb";

String url = "jdbc:mysql://db4free.net:3306/db_autofrat?user=akashdktyagi&password=akashdktyagi";

DBConnectionManager DBInstance = DBConnectionManager.getInstance(url);

Connection conn = DBInstance.getConnection();

Statement stat = conn.createStatement();

ResultSet rs = stat.executeQuery(query);

//To get total number of Column returned

int clmCount = rs.getMetaData().getColumnCount();

//To get total number of rows returned

rs.last();// this will move the rs to last row

int rowcount = rs.getRow(); // this will give the index of last row

rs.beforeFirst(); // this will bring it back to first record

//Create a Object 

String[][] result = new String[rowcount][clmCount];

int i=0;

while(rs.next()) {

for(int j=0;j<clmCount;j++) {

result[i][j]=rs.getString(j+1);

}

i=i+1;

}

return result;

}

}

===========================================

 public void br_API_will_insert_a_record_into_tBookingRequestRejections_table_in_the_BR_Staging_DB_tables_The_record_will_contain_the_error_code_and_error_description() {

    try {

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection m_Connection = DriverManager.getConnection(DETAILS);
        Statement m_Statement = m_Connection.createStatement();         
        String query = "select top 10 * from tBookingRequest where request_id = " + idNumber; 

        ResultSet m_ResultSet = m_Statement.executeQuery(query);

        while (m_ResultSet.next()) {
            System.out.println("HERE: " + m_ResultSet.getString(1) + ", " + m_ResultSet.getString(2) + ", "
                    + m_ResultSet.getString(3));
        }
    }
    catch(Exception ex) {
        System.out.println(ex.toString());
    }

====================================================================

and then use the methods for the implementation of the steps.

package stepDefinitions;

import cucumber.api.java.en.Given;
import cucumber.api.java.en.Then;
import cucumber.api.java.en.When;
import util.DatabaseConnection;

public class TransactionGeneratorTest extends DatabaseConnection {

    @Given("^I am connected with the database$")
    public void i_am_connected_with_the_database() throws Throwable {
        createConnection();
    }

    @When("^I run the select query$")
    public void i_run_the_select_query() throws Throwable {
        createQuery();
        executeQuery();
    }   

    @Then("^I should see the result as \"([^\"]*)\"$")
    public void i_should_see_the_result_as(String name) throws Throwable {
        assertRecords(name);
    }
}

=====================================================================

package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Assert;

public class DatabaseConnection extends DriverFactory {

    ReadConfigFile config = new ReadConfigFile();

    public static String dbUrl;                 
    public static String username;  
    public static String password;
    public static String database_driver;
    Connection con;
    Statement stmt;
    String query;
    ResultSet rs;

    public DatabaseConnection() {
        super();
    }

    public DatabaseConnection createConnection() throws SQLException, ClassNotFoundException {
        con = DriverManager.getConnection(config.getUrl(),config.getUsername(),config.getPassword());
        Class.forName(config.getDatabaseDriver());  
        return new DatabaseConnection();
    }

    public DatabaseConnection createQuery() throws SQLException {
        query = "select * from test where no = 1;";
        stmt = con.createStatement();   
        return new DatabaseConnection();
    }

    public DatabaseConnection executeQuery() throws SQLException {
        rs = stmt.executeQuery(query);
        return new DatabaseConnection();
    }

    public DatabaseConnection assertRecords(String name) throws SQLException {
        while (rs.next()){
            String myName = rs.getString(2);                                                                                           
            Assert.assertEquals(myName,name);
        }
        return new DatabaseConnection();
    }

    public DatabaseConnection closeConnection() throws SQLException {
        con.close();
        return new DatabaseConnection();
    }
}