Mocking a MySQL server with Java

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 th开发者_如何学Goe 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.

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.

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.

Try derby. It's a free server you can use to test against, if you don't mind having to change drivers when you go back to SqlServer. You might be limited in the kind of queries you can run though. I'm not sure if SqlServer has any special syntax outside of standard SQL.

How about using a HSQLDB for offline tests? It wont behave exactly like a MySQL DB but is a fast in memory SQL DB that should satisfy most of your needs.

The best way in my experience is multiple database instances and or schemas. Normally you'd have one for each user to do their development against/sanity checking the running application, one for an automated build for running unit tests and ideally one for each user to run their unit tests against. And of course instances/schemas for demos, integration testing. Apart from the practial side, being able to do this ensures deploying/upgrading the app/database will be pretty near faultless too.

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