MySQL Automation

Why do we need database automation?

  • To get test data - If we automate the database, we can directly fetch the test data from database and then work on them in test automation script
  • To verify result - In automation we can verify the front end result with backend entry in the database
  • To delete test data created - In automation it is good practice to delete the test data created, using database automation, we directly fire the delete query to delete the test data created
  • To update certain data - As per the need of test script, the test data can be updated using update query

Connecting to MySQL Database in Java

Database automation in mySQL database involves the following steps-

  • Loading the required JDBC Driver class, which in our case is com.mysql.jdbc.Driver-

  • Creating a connection to the database-
    Connection conn = DriverManager.getConnection("DatabaseURL","UserName", "Password");

  • Executing SQL queries-
    Statement st = conn.createStatement();
    String Sql = "select * from [tableName] where <condition>";
    ResultSet rs = st.executeQuery(Sql);

  • Fetching data from result set-
    while ( {

Sample code to connect to a MySQL database

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

public class connectingToMySQLDBExample {

    private static String userName;
    private static String password;
    private static String dbURL;
    private static Connection connection;
    public static void main(String[] args) {
	try {
		userName = "username";
		password = "password";
		dbURL = "jdbc:mysql://";
		try {
                catch (ClassNotFoundException e) {
			System.out.println("MySQL JDBC driver not found.");
		try {
			connection = DriverManager.getConnection(dbURL, userName, password);
			Statement st = connection.createStatement();
			String sqlStr = "select * from testTable";
			ResultSet rs = st.executeQuery(sqlStr);
			while ( {
			} catch (SQLException e) {
				System.out.println("Connection to MySQL db failed");
		} catch (Exception e) {