Mocking a MySQL server with Java

Question

Since I'm not really proficient with databases, some details may be irrlevant, but I'll include everything:

As part of a project in my University, we're creating a website that uses JSP, servlets and uses a MySQL server as backend.

I'm in charge of setting up the tables on the DB, and creating the Java classes to interact with it. However, we can only connect to the MySQL server from inside the University, while we all (7 people) work mostly at home.

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

EDIT: I'm not so sure on the differences between SQL databases, but obviously all the queries I run on MySQL should run on the mock.

Answer 1

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

Answer 2

Your API appears to be flawed. You should not be returning ResultSets to clients. By doing so, you are forever forcing your clients to rely on a relational database backend. Your data access layer needs to hide all of the details of how your data is actually structured and stored.

Instead of returning a ResultSet, consider returning a List or allowing the client to supply a Stream that your data access component can write to.

This will make unit tests trivial for the clients of the API and will allow you to swap storage mechanisms at will.