Content
Why do we need to connect to the databases?
- To get test data – If we connect to the database, we can directly fetch the test data from the database and then work on them in the 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 for a test script, the test data can be updated using an update query.
You can also check: String Handling in Java
Connecting to MySQL Database in Java
Database automation in MySQL database involves the following steps-
- Loading the required MySQL 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 your pom.xml file in case you are using a 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();
}
}
}
For a complete step by step Selenium tutorial, you can check our free tutorial series – Selenium with Java Tutorial.