Mocking MySQL Server with Java

Question

Since I do not own the databases, some details may be unjustified, but I will include all:

As part of a project at my university, we are creating a website that uses JSPs, servlets and uses a MySQL server as a backend.

I am responsible for creating tables in the database and creating Java classes to interact with it. However, we can only connect to the MySQL server from within the university, while all of us (7 people) work mainly at home.

I am creating a QueryHandler interface that has a method that takes a string (representing a query) and returns a ResultSet . My question is this: how to create a class that implements this interface, which will simulate a database and allow others to use different DBHandler and not know the difference and let me test different queries without connecting to the actual MySQL database?

EDIT . I'm not sure about the differences between SQL databases, but obviously all the queries that I run in MySQL should be executed in the layout.

Answer 1

Why not just install your own MySQL database for testing? It runs on Windows, Mac, and Linux, and it is not too resource intensive. I installed it on my laptop for local testing.

Answer 2

Your API seems to be wrong. You must not return ResultSets to customers. Thus, you are forcing your customers to rely on a relational database forever. Your level of data access should hide all the details of how your data is really structured and stored.

Instead of returning a ResultSet, consider returning a list or that the client can provide a stream that your data access component can write.

This will make unit tests trivial for API clients and allow you to change the storage mechanisms of your choice.

Answer 3

Try derby . This is a free server that you can use for testing if you do not mind changing drivers when you return to SqlServer. You may be limited in the form of queries that you can run. I am not sure if SqlServer has special syntax outside standard SQL.

Answer 4

How about using HSQLDB for standalone tests? It will not behave exactly like a MySQL database, but it is a fast SQL memory that satisfies most of your needs.

Answer 5

The best way in my experience is to have multiple instances and / or database instances. Usually you have one for each user to do your development against checking / verifying the health of the running application, one for automatic assembly to run unit tests and, ideally, for each user for whom they run their unit tests. And, of course, instances / schemes for demonstrations, integration testing. In addition to the practical side, the ability to do this ensures that deploying / updating the application / database will also be almost flawless.

Assuming you have a DAO level, the only code that needs access to a real database at the unit test level is the DAO implementation, the business level should use a mock DAO implementation.