How to database testing using frameworks.
Computer applications are more complex these days with technologies like Android and also with lots of Smartphone apps. The more complex the front ends, the more intricate the back ends become.
So it is all the more important to learn about DB testing and be able to validate Databases effectively to ensure security and quality databases.
Sometimes a QA automation engineer needs to work with a database. You need to run queries, call stored procedures, and write some data. The standard for working with databases in java is JDBC.
Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity. It is part of the Java Standard Edition platform, from Oracle Corporation. It provides methods to query and update data in a database and is oriented toward relational databases. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the Java virtual machine (JVM) host environment.
Program code written using JDBC looks like this:
/**
* The class JDBS example.
*/
public class JDBCExample {
/**
* The constant LOGGER.
*/
private static final Logger LOGGER = Logger.getLogger("java.util.logger");
/**
* The entry point of application.
*
* @param args the input arguments
*/
public static void main(String[] args) {
Connection con = null;
Statement stmt = null;
ResultSet resultSet = null;
try {
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/words",
"words", "words");
stmt = con.createStatement();
resultSet = stmt.executeQuery("select * from word");
while (resultSet.next()) {
LOGGER.info("word id: " + resultSet.getLong(1) +
" spelling: " + resultSet.getString(2) +
" part of speech: " + resultSet.getString(3));
}
} catch (SQLException exception) {
LOGGER.info("The method SQL is down" + exception);
} catch (ClassNotFoundException ex) {
LOGGER.info("The method SQL is down" + ex);
} finally {
try {
resultSet.close();
} catch (Exception e) {
}
try {
stmt.close();
} catch (Exception e) {
}
try {
con.close();
} catch (Exception e) {
}
}
}
}Writing such code is not correct, especially in a test framework that should be lightweight. What can be done about it? You need to use libraries that will make your life much easier.
- JDBI is built on top of JDBC. If your database has a JDBC driver, you can use JDBI with it. JDBI improves JDBC’s rough interface, providing a more natural Java database interface that is easy to bind to your domain data types. Unlike an ORM, we do not aim to provide a complete object-relational mapping framework — instead of that hidden complexity, we provide building blocks that allow you to construct the mapping between relations and objects as appropriate for your application.
The implementation of this library is similar to Groovy SQL. Using the JBDI library, you can write the following code:
/**
* The class JDBI example.
*/
public class JDBIExample {
/**
* The entry point of application.
*
* @param args the input arguments
*/
public static void main(String[] args) {
DataSource dataSource = JdbcConnectionPool.create("jdbc:h2:mem:test",
"username",
"password");
DBI dbi = new DBI(dataSource);
Handle handle = dbi.open();
handle.execute("create table something (id int primary key, name varchar(100))");
handle.execute("insert into something (id, name) values (?, ?)", 1, "Brian");
String name = handle.createQuery("select name from something where id = :id")
.bind("id", 1)
.map(StringMapper.FIRST)
.first();
assertThat(name, equalTo("Brian"));
handle.close();
}
}The code becomes more readable. Moreover, you can map queries to objects, which will make life even easier.
2. jOOQ a fluent API for typesafe SQL query construction and execution.
/**
* The type Jooq example.
*/
public class JOOQExample {
/**
* The entry point of application.
*
* @param args the input arguments
*/
public static void main(String[] args) {
select(KeyColumnUsage.CONSTRAINT_NAME, KeyColumnUsage.TABLE_NAME, KeyColumnUsage.COLUMN_NAME)
.
from(KEY_COLUMN_USAGE).
join(TABLE_CONSTRAINTS)
.
on(KeyColumnUsage.TABLE_SCHEMA.equal(TableConstraints.TABLE_SCHEMA))
.
and(KeyColumnUsage.TABLE_NAME.equal(TableConstraints.TABLE_NAME))
.
and(KeyColumnUsage.CONSTRAINT_NAME.equal(TableConstraints.CONSTRAINT_NAME))
.
where(TableConstraints.CONSTRAINT_TYPE.equal(constraintType))
.
and(KeyColumnUsage.TABLE_SCHEMA.equal(getSchemaName()))
.
orderBy(KeyColumnUsage.TABLE_NAME.ascending(), KeyColumnUsage.ORDINAL_POSITION.ascending()).
fetch();
}
}The library is quite powerful and can do a lot of things. But the code turns out to be not quite readable and poorly debugged.
3. QueryDSL is a framework that enables the construction of type-safe SQL-like queries for multiple backends including JPA, MongoDB, and SQL in Java.
Very powerful library. Can do a lot of useful things. The code that can be written looks like this:
/**
* The class Query dsl example.
*/
public class QueryDslExample {
/**
* The entry point of application.
*
* @param args the input arguments
*/
public static void main(String[] args) {
List persons = queryFactory.selectFrom(person)
.where(
person.firstName.eq("John"),
person.lastName.eq("Doe"))
.fetch();
}
}The library is too difficult to learn. The documentation is quite confusing.
4. Sql2o is a small java library, with the purpose of making database interaction easy. When fetching data from the database, the ResultSet will automatically be filled into your POJO objects. Kind of like an ORM, but without the SQL generation capabilities.
A fairly good library that allows you to do things quickly and easily. You can write code like this:
/**
* The type Sql two example.
*/
public class SqlTwoExample {
/**
* The type Task.
*/
public class Task {
private int id;
private String category;
private Date dueDate;
// getters and setters here
}
/**
* The Sql 2 o.
*/
Sql2o sql2o = new Sql2o(DB_URL, USER, PASS);
/**
* The Sql.
*/
String sql =
"SELECT id, category, duedate " +
"FROM tasks " +
"WHERE category = :category";
/**
* The Con.
*/
Connection con = sql2o.open();
{
List tasks = con.createQuery(sql)
.addParameter("category", "foo")
.executeAndFetch(Task.class);
}
}Everything is quite simple and convenient, with good documentation with clear and understandable examples.
5. Groovy’s SQL features are implemented in the elegant GroovySql API. Using closures and iterators, Groovy Sql neatly transfers JDBC resource management from you, the developer, to the Groovy environment. This way, JDBC programming is less cumbersome, and you can focus on queries and their results.
/**
* The type Groovy sql example.
*/
public class GroovySqlExample {
/**
* Main.
*/
public static void main(String[] args) {
sql = Sql.newInstance("jdbc:mysql://localhost:3306/words", "words",
"words", "org.gjt.mm.mysql.Driver")
sql.eachRow("select * from word") {
row |
println row.word_id + " " + row.spelling + " " + row.part_of_speech
}
}
}Using just a few lines, I created the function code without performing Connection closures, ResultSet closures, or any of the usual heavy-handed JDBC programming routines.
With all these features, factors, and processes to test on a database, there is an increasing demand for the testers to be technically proficient in the key Database concepts. Despite some of the negative beliefs that testing a database creates new bottlenecks and is a lot of additional expenditure — this is a realm of testing that is attracting significant attention and demand.
