Build a micro service application with Vertx: a complete tutorial (part 3/5)

In this series of posts, I would teach you, how to create a complete reactive micro service application with Vert.x and Java, connect it with your data source, test and deploy on Heroku platform.

Table of contents

Create a DAO implementation

In this section of the tutorial, we would connect our service to the database. In order to do it, we need to implement our DAO interface into an actual data access object. We use DataSource to establish a database connection (this is a preferred way to work with database in Java).

public class BikeDAOImpl extends IBikeDAO{

	private MySqlDataSource dataSource;

	public BikeDAOImpl(MySqlDataSource dataSource){
		this.dataSource = dataSource;
	}

	/*overriden DAO methods*/
}

Prepare the test database

To work with a database, we first should obtain it. I encourage you to use a real database against of in-memory databases, like SQLite or Derby, as you could verify how your applications work during development stage. There are three main ways to get a real MySQL database:

  1. Install and configure MySQL server on localhost
  2. Use free database hosting, as db4free.net – this method I use in this tutorial.
  3. Rare situation, but if you have a hosting service with MySQL database and it permits external connections, you can use it. As far as I know, not all hosting providers permit such behaviour, but some do. Check with your provider about that.

Last two methods are preferred, as you could test work of your app with real remote database hosts. I would skip an installation process, as it vary based on your choice, and I refer you to an actual documentation, and instead I would move to an actual database schema. Don’t forget to populate it with some fake bike data just for test purposes.

Just some imaginary bikes, situated in different spots around Prague, CZ

Retrieve a single bike from database

As you remember from the last post, we started from getting a single bike based on its identification number (id). Let continue with this tradition 🙂

public Bike findById(String id){
	try (Connection connection = dataSource.getConnection()){
			//Create a PreparedStatement
			PreparedStatement ps = "SELECT bike_id, last_lon, last_lat, is_busy FROM bikes WHERE bike_id = ?";
			//pass ID to query
			ps.setString(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()){
				Bike bike = new Bike();
				....
				return bike;
			} else {
				//nothing found
				return null;
			}
		} catch (Exception ex){
			System.out.println(ex.getLocalizedMessage());
			/*Btw, return null when object is absent is not a best practice*/
			return null;
		}
}

Establish a connection and test

So far we have a DAOImplementation object, one access method and a [remote] database. Let test, that everything is up and running. Create a new test and verify that we can obtain connection and actually get a bike from a database.

public class BikeDAOImplTest {

	private final String DB_URL = ""; /*Include your database url here*/
    private BikeDAOImpl dao;

    @BeforeEach
    void setup(){
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setUrl(DB_URL);
        dao = new BikeDAOImpl(dataSource);
    }

    @Test
    void notNullTest(){
    	//check that dao exists
    	Assertions.assertNotNull(dao);
    }

    @Test
    void findBikeById(){
    	//specifiy a bike id according to your dataset
        String id = ....
        //execute
        Bike bike = dao.findById(id);
        //assert results
        Assertions.assertEquals(id, bike.getId());
        //...other assertions are welcome
    }
}

Run this test and check results. Because we tested against the real database, this type of tests is called the integration test. If you want to master core concepts of unit testing with JUnit5, I suggest you to check my tutorial on JUnit5. I also recommend you to go through my Vert-x JUnit 5 tutorial.

Next steps

Now you are ready to implement remaining methods and test them in a same manner. Due to the fact, that it is a purely JDBC topic, and we are not using Vertx SQL libraries, there is nothing that can be unfamiliar to you, if you have an experience with JDBC. If you are not fluent with JDBC API, feel free to review these good starter materials:

Conclusion

In this chapter we focused on how to connect our microservice app with a real remote MySQL database, and how to write an integration test. In next chapter we would write an AppEndpoint.

You can find a full source code for this tutorial on my github. Feel free to clone it:

git clone https://github.com/yuri-mednikov/bike-share