Database Testing - JDBC
Dec 18, 2021
We need to access Databases from outside(Intellij, Eclipse etc ) of SQL developer to test
We need to have something that will connect from Intellij to Database to take data or make test
Ready classes written in java, works for all database we just need driver for our type of database, code will not change.
What is JDBC (Java Database Connectivity ) ?
Java Database Connectivity (JDBC) is an API (Application Program Interface) or platform-independent interface which helps to connect java programs with various databases such as Oracle, My SQL, MS Access and SQL Server etc.
It has 2 packages ;
1.java.sql.*; Basic Database Communication
Interfaces : Connection - Statement - ResultSet - ResultSetMetaData - DatabaseMetaData etc
Classes : DriverManager etc
2.javax.sql.*; Advanced Database Communication
- Java Database Connectivity (JDBC) is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases
- It is similar action to connect Browser
JAVA —> Selenium —>Chrome Driver—> Chrome Browser
- We can say JDBC is a middle man that understands Java and Databases
JAVA —> JDBC —> OracleDriver —> ORACLE DB
- JDBC libraries already come with java standard edition(JAVA SE). For every database, there is a Driver that enables programmatic access to the database. Those drivers are developed by database manufacturers.
- JDBC provides DriverManager to connect various Databases then Java Applications can communicate with any type of Database
- DriverManager: This class comes from java.sql package with JDBC. It is responsible to establish the connection to the Databases with the help of the related driver
- JDBC Driver (Database Driver (Oracle Driver- MySql Driver etc ) ) : It acts as Bridge between Java Application and Database. It converts
- Using JDBC will help with code reusability, as code written for one database will work for another type of database, and all we need to do will change the driver/credentials to the database.
Maven Dependency
For Oracle - click
For MySQL - click
What is Connection String ?
In order to test Database using JAVA, First step we need to connect Database via Connection String. It includes URL - Username - Password
String dbURL ="jdbc:oracle:thin:@33.52.156.25:1521:xe"; // INSTEAD OF THIS URL USE YOUR IP FROM EC2 INSTANCE
String dbUsername = "xx";
String dbPassword = "xx";
How we can connect Database ?
NOTE :
This 3 steps are important and all comes from import java.sql.*;
Connection → Helps our java project connect to database
Statement → Helps to write and execute SQL query
ResultSet → A DataStructure where we can store the data that came from database
ResultSet.TYPE_SCROLL_INSENSITIVE —> to be able to scroll in the table
ResultSet.CONCUR_READ_ONLY. —> makes the object immutable.
ResultSet Methods
ResultSet use cursor/pointer to navigate through rows
As a default pointer shows location know as beforeFirst
In order to move cursor to the first row, we need to call next() method
With the help of next() method we can iterate/jump all rows
You can use getString(columnName) to get cell value
rs.next(); -- jump next row
String firstRowRegionName = rs.getString("REGION_NAME");
// OR
rs.next(); -- jump next row
String secondRowRegionName = rs.getString(2);
In order to access data in ResultSet object , we need to use cursor navigation methods
next() - move to next row and return true false according to if we have valid row
previous() - move to previous row and return true false according to if we have valid row
first() - move to first row from anywhere
last() - move to last row from anywhere
beforeFirst() - move to before first location from anywhere
afterLast() - move to after last location from anywhere
absolute(8) - move to any row by using row number , for example 8 in this case
In order to get the cell value at certain row , you can use column index or column name :
getString(ColumnName)
getString(ColumnIndex)
getInt(ColumName)
getInt(ColumnIndex)
getDouble(ColumName)
getDouble(ColumnIndex)
What is ResultSetMetaData ?
ResultSet Object can hold only data. It does not have any info about columnNames or count.
Rsmd is responsible for upper side of table.ResultSetMetaData can be used to get column information about ResultSet object
Database MetaData
