MySQL Automation in Java

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. You can download the jar from here and add it to your classpath or add it as maven dependency in you pom.xml file in case you are using maven project.
    Class.forName("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 (rs.next()) {
    	System.out.println(rs.getString(<requiredField>));
    }
    

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://artoftesting.com/testDB";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		}
                catch (ClassNotFoundException e) {
			System.out.println("MySQL JDBC driver not found.");
			e.printStackTrace();
		}
		try {
			connection = DriverManager.getConnection(dbURL, userName, password);
			Statement st = connection.createStatement();
			String sqlStr = "select * from testTable";
			ResultSet rs = st.executeQuery(sqlStr);
			while (rs.next()) {
				System.out.println(rs.getString("name"));
			}
				
			} catch (SQLException e) {
				System.out.println("Connection to MySQL db failed");
			 e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}