Database testing using java

Database testing with Java

Automated from the UI is good, but it’s better if we can verify and comparing the value on the web UI with the actual value from the database, that helps us to make sure it’s correct and integrity. This article will show you how to read and verify database values with Java code.

I will assume that you have successfully installed XAMPP so we can create the new database, table for testing this code.

1. Database preparation

Go to phpMyAdmin (http://localhost/phpmyadmin/) and create the new database (“TESTDB”) with the below SQL statement:

We also need to create the table (“CUSTOMERS”) and insert some values to that table.

Re-check your values with this SQL statement: SELECT * FROM CUSTOMERS;

This is the full SQL statement that you can use:

INSERT INTO CUSTOMERS ( ID , NAME , AGE , ADDRESS , SALARY ) VALUES ( 1 , ‘Ramesh’ , 32 , ‘Ahmedabad’ , 2000.00 ) ;

INSERT INTO CUSTOMERS ( ID , NAME , AGE , ADDRESS , SALARY ) VALUES ( 2 , ‘Khilan’ , 25 , ‘Delhi’ , 1500.00 ) ;

INSERT INTO CUSTOMERS ( ID , NAME , AGE , ADDRESS , SALARY ) VALUES ( 3 , ‘kaushik’ , 23 , ‘Kota’ , 2000.00 ) ;

INSERT INTO CUSTOMERS ( ID , NAME , AGE , ADDRESS , SALARY ) VALUES ( 4 , ‘Chaitali’ , 25 , ‘Mumbai’ , 6500.00 ) ;

INSERT INTO CUSTOMERS ( ID , NAME , AGE , ADDRESS , SALARY ) VALUES ( 5 , ‘Hardik’ , 27 , ‘Bhopal’ , 8500.00 ) ;

INSERT INTO CUSTOMERS ( ID , NAME , AGE , ADDRESS , SALARY ) VALUES ( 6 , ‘Komal’ , 22 , ‘MP’ , 4500.00 ) ;

The results

2. Working with Java code

First, we need to download the “mysql-connector-java” jar at https://dev.mysql.com/downloads/connector/j/5.1.html

Select and download “Platform Independent (Architecture Independent), ZIP Archive”. When the website asks for login, you just need to click on “No thanks, just start my download” if you just want to download it without login.

Unzip this file and import “mysql-connector-java-5.1.43-bin.jar” to your project, the file version should be difference based on the time you download, so do not worry if you see the other number of jar version.

Ok, that’s good for now, we need to create a new Class and use this code, here’s my full code and explanation below:

Источник

§Testing with databases

While it is possible to write functional tests that test database access code by starting up a full application including the database, starting up a full application is not often desirable, due to the complexity of having many more components started and running just to test one small part of your application.

Play provides a number of utilities for helping to test database access code that allow it to be tested with a database but in isolation from the rest of your app. These utilities can easily be used with either ScalaTest or specs2, and can make your database tests much closer to lightweight and fast running unit tests than heavy weight and slow functional tests.

§Using a database

To test with a database backend, you only need:

libraryDependencies += javaJdbc % Test

To connect to a database, at a minimum, you just need database driver name and the url of the database, using the Database static factory methods. For example, to connect to MySQL, you might use the following:

Database database = Databases.createFrom("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test");

This will create a database connection pool for the MySQL test database running on localhost , with the name default . The name of the database is only used internally by Play, for example, by other features such as evolutions, to load resources associated with that database.

You may want to specify other configuration for the database, including a custom name, or configuration properties such as usernames, passwords and the various connection pool configuration items that Play supports, by supplying a custom name parameter and/or a custom config parameter:

Database database = Databases.createFrom( "mydatabase", "com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test", ImmutableMap.of( "username", "test", "password", "secret"));

After using a database, since the database is typically backed by a connection pool that holds open connections and may also have running threads, you need to shut it down. This is done by calling the shutdown method:

These methods are particularly useful if you use them in combination with JUnit’s @Before and @After annotations, for example:

Database database; @Before public void createDatabase() < database = Databases.createFrom("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test"); >@After public void shutdownDatabase()

Tip: You can use this to externalize your test database configuration, using environment variables or system properties to configure what database to use and how to connect to it. This allows for maximum flexibility for developers to have their own environments set up the way they please, as well as for CI systems that provide particular environments that may differ to development.

§Using an in-memory database

Some people prefer not to require infrastructure such as databases to be installed in order to run tests. Play provides simple helpers to create an H2 in-memory database for these purposes:

Database database = Databases.inMemory();

The in-memory database can be configured by supplying a custom name, custom URL arguments, and custom connection pool configuration. The following shows supplying the MODE argument to tell H2 to emulate MySQL , as well as configuring the connection pool to log all statements:

Database database = Databases.inMemory( "mydatabase", ImmutableMap.of("MODE", "MYSQL"), ImmutableMap.of("logStatements", true));

As with the generic database factory, ensure you always shut the in-memory database connection pool down:

§Applying evolutions

When running tests, you will typically want your database schema managed for your database. If you’re already using evolutions, it will often make sense to reuse the same evolutions that you use in development and production in your tests. You may also want to create custom evolutions just for testing. Play provides some convenient helpers to apply and manage evolutions without having to run a whole Play application.

To apply evolutions, you can use applyEvolutions from the Evolutions static class:

Evolutions.applyEvolutions(database);

This will load the evolutions from the classpath in the evolutions/ directory, and apply them.

After a test has run, you may want to reset the database to its original state. If you have implemented your evolutions down scripts in such a way that they will drop all the database tables, you can do this simply by calling the cleanupEvolutions method:

Evolutions.cleanupEvolutions(database);

§Custom evolutions

In some situations you may want to run some custom evolutions in your tests. Custom evolutions can be used by using a custom EvolutionsReader . The easiest way to do this is using the static factory methods on Evolutions , for example forDefault creates an evolutions reader for a simple list of Evolution scripts for the default database. For example:

Evolutions.applyEvolutions( database, Evolutions.forDefault( new Evolution( 1, "create table test (id bigint not null, name varchar(255));", "drop table test;")));

Cleaning up custom evolutions is done in the same way as cleaning up regular evolutions, using the cleanupEvolutions method:

Evolutions.cleanupEvolutions(database);

Note though that you don’t need to pass the custom evolutions reader here, this is because the state of the evolutions is stored in the database, including the down scripts which will be used to tear down the database.

Sometimes it will be impractical to put your custom evolution scripts in code. If this is the case, you can put them in the test resources directory, using the fromClassLoader factory method:

Evolutions.applyEvolutions( database, Evolutions.fromClassLoader(getClass().getClassLoader(), "testdatabase/"));

This will load evolutions, in the same structure and format as is done for development and production, from testdatabase/evolutions//.sql .

§Integrating with JUnit

Typically you will have many tests that you want to run with the same evolutions, so it will make sense to extract the evolutions setup code into before and after methods, along with the database setup and tear down code. Here is what a complete test might look like:

import static org.junit.Assert.*; import java.sql.Connection; import org.junit.*; import play.db.Database; import play.db.Databases; import play.db.evolutions.*; public class DatabaseTest < Database database; @Before public void setupDatabase() < database = Databases.inMemory(); Evolutions.applyEvolutions( database, Evolutions.forDefault( new Evolution( 1, "create table test (id bigint not null, name varchar(255));", "drop table test;"))); >@After public void shutdownDatabase() < Evolutions.cleanupEvolutions(database); database.shutdown(); >@Test public void testDatabase() throws Exception < Connection connection = database.getConnection(); connection.prepareStatement("insert into test values (10, 'testing')").execute(); assertTrue( connection.prepareStatement("select * from test where >>

Found an error in this documentation? The source code for this page can be found here. After reading the documentation guidelines, please feel free to contribute a pull request. Have questions or advice to share? Go to our community forums to start a conversation with the community.

Community support

Donate to our Collective

Источник