Database Testing with PostgreSQL and MySQL in Java[Eclipse] Using the JDBC Library

In this blog we will write a code on how to connect to a Postgres database and MySQL Database using JDBC, and run basic SQL queries in our Java application.

What Is JDBC?

JDBC stands for Java Database Connectivity. It is a set of APIs that we can use to connect and execute queries on any compatible database, like PostgreSQL, MySQL.

Let’s look at how data is retrieved from a database from most applications:

1. We create a connection to the database

2. We send a query statement (something like SELECT * FROM film)

3. The database returns the results

4. We use the results in our application

PostgreSQL in Java

Installing the Database Driver

When using JDBC, we need to install the driver of the database we’re using. In this case, that would be the PostgreSQL JDBC driver, which we can add as a dependency to our pom.xml file:

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.5.4</version>
</dependency>

How connection between Data base and Java Application works ?

Java code flow to execute the query in Data base

Select Query

Sample Java code to connect to database and execute query through Eclipse

SELECT query is used to display the data , retrieved from the table and print the output in console window

Project Structure :

Java code for SELECT query to retrieve data from database

In this code , we have to give credentials for DriverManager.getConnection(url, user, password) to connect to database

After the connection we have to execute the query , once the query is executed and output is printed and we are closing the connection.

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

public class postgreSQL_Connection {
	 private final static String url = "jdbc:postgresql://localhost:5302/dvdrental";
	 private final static String user = "postgres";
	 private final static String password = "reka";

    public static void main(String[] args) throws SQLException {
	   	   
	    // CONNECT TO POSTGRESSQL DATABASE	    
    	 Connection conn = null;
	        try {
	            conn = DriverManager.getConnection(url, user, password);

	            if (conn != null) {
	                System.out.println("Connected to the PostgreSQL server successfully.");
	            } else {
	                System.out.println("Failed to make connection!");
	            }

	        } catch (SQLException e) {
	            System.out.println(e.getMessage());
	        }
	        
      //  CREATE STATEMENT and EXECUTE STATEMENT
	        Statement st = conn.createStatement();
	        ResultSet rs = st.executeQuery("SELECT first_name  FROM customer WHERE first_name LIKE 'A%'");
			  while (rs.next()) {
			      System.out.print("FirstName which starts with A : ");
			      System.out.println(rs.getString("first_name"));
			  }
      // CLOSE CONEECTION
			  rs.close();
			  st.close();
	    }
}

ResultSet output printed in console ,

It displays the First Name of the customers which starts will letter A

Update Query for updating data, work a bit differently than SELECT queries. In this case, we don’t get any results back.

executeUpdate() is used for INSERT, UPDATE and DELETE queries

package postgreSQLdemo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class postgreSQL_Connection {
	 private final static String url = "jdbc:postgresql://localhost:5302/dvdrental";
	 private final static String user = "postgres";
	 private final static String password = "reka";

    public static void main(String[] args) throws SQLException {
	   	   
	    // CONNECT TO POSTGRESSQL DATABASE	    
    	 Connection conn = null;
	        try {
	            conn = DriverManager.getConnection(url, user, password);

	            if (conn != null) {
	                System.out.println("Connected to the PostgreSQL server successfully.");
	            } else {
	                System.out.println("Failed to make connection!");
	            }

	        } catch (SQLException e) {
	            System.out.println(e.getMessage());
	        }
 PreparedStatement updateStmt =
	                conn.prepareStatement("UPDATE NEWMOVIES SET RENTAL_RATE=5555 WHERE RELEASE_YEAR>2022;");
	        updateStmt.executeUpdate();  
    }
}
	        

Console output

Cross checking whether the update is updated in the PostgreSQL

MySQL in Java

Similarly connect with MySQL database and do Database testing

You need to download mysql-connector-java-8.0.30

module-info.java

Most of the classes and methods that we’ll be using are a part of the java.sql and javax.sql packages, which come included as a part of Java SE.

module mySQL {
	requires java.sql;
}

Code to connect to MySQL database

SELECT Query

SELECT query to retrieve data from database and print the output in console window

In this code , we have to give credentials for DriverManager.getConnection(url, user, password) to connect to database

After the connection we have to execute the query , once the query is executed and output is printed and we are closing the connection.

MySqlCommands.java

package mysqldemos;
import java.sql.Connection;  // Package from java.sql 
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/* 1.CREATE CONNECTION
 * 2.CREATE STATEMENT
 * 3.EXECUTE STATEMENT
 * 4.STORE DATA IN RESULT SET
 * 5.CLOSE CONNECTION
 */
public class MySqlSelectCommands {

	public static void main(String[] args) throws SQLException {
		
		//1.CREATE CONNECTION 
		  Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","root");
		  
		//2. Create statement/query
		   Statement stmt=con.createStatement();
		   String s="select sno,sname,marks from student";
		   	   
	   
		//3. execute statement  4. STORE RESULT SET DATA
		  ResultSet rs=stmt.executeQuery(s);
		  while (rs.next())
		  {int no=rs.getInt("sno");
		  String name=rs.getString("sname");
		  int point=rs.getInt("marks");
		  System.out.println(no+" "+name+" "+point);
		  }
		  
		//5. close connection
		  con.close();
		  
		//6. CONFIRMATION THAT IT EXECUTED
		  System.out.println("query executed"); 
	}
}

ResultSet output printed in console

INSERT query for inserting data, work a bit differently than SELECT queries. In this case, we don’t get any results back. Similarly you can execute queries for UPDATE and DELETE .

package mysqldemos;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MySqlSelectCommands {

	public static void main(String[] args) throws SQLException {
		
		//1.CREATE CONNECTION 
		  Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","root");
		  
		//2. Create statement/query
		   Statement stmt=con.createStatement();
String s="INSERT INTO STUDENT VALUES(200,'NinjaSurvivors',80)";
//String s="update student set sname='david' where sno =103";
//String s="delete from student where sno=103";	
	
		//3. Execute statement
 		   stmt.execute(s);
		  
		//5. Close connection
		   con.close();
		  
		//6. CONFIRMATION THAT IT EXECUTED
		  System.out.println("query executed"); 
	}
}

Console output

Cross checking whether the Insert query has successfully executed in the MySQL

Conclusion:

I hope, This article will help you to understand How to do Data base Testing on MySQL and PostgreSQL through Java Eclipse.

You must have got an idea on the topics explained in this blog. Lets explore more and learn New Topics.

Happy Learning