Concept of In Memory Database and how to see if my data is being populated in HSQL DB?

I am using HSQL in memory database for test purpose of my application and using SQL Server as main database, now when am doing test then HSQL Database is being populated with same data that I have in my SQL Server, now I am trying to test particular service which is retrieving data from Database(it would query MS Server if directly service is run or it will query HSQL Database if called from test)

I am able to see data from MS Server when I run the query bu开发者_StackOverflow中文版t HSQL Db does not return any data if am running same query on it. My hunch here is that HSQL DB is not being populated with the data, is there a way where in I can go and look in what tables I have in HSQL DB and how data is being populated in HSQLDB, i want to see data and i do not have any gui client to see how HSQL Database is populated and what data goes in what table. Do we have a good client for the same and how can I connect to HSQL Database using it and also how can I actually see things happening under HSQL Cover rather than just assuming that HSQL is being populated properly with what we have in SQL Server?

Any suggestions would go long way?

Here I add a detailed steps of

How To Running a memory-only HSQLDB in server mode?

1) Download the latest version hsqldb-2.2.5.zip

2) unzip it,open the bin folder

3) modify the runServer.bat like cd ..\data @java -classpath ../lib/hsqldb.jar org.hsqldb.server.Server -database.0 mem:aname -dbname.0 aliasdb and click the runServer.bat to start server

4) run the runManager.bat, select Server type, enter the url jdbc:hsqldb:hsql://localhost/aliasdb,connect, create some test table and test data

5) run the runManager.bat again to start another client,select Server type, enter the url jdbc:hsqldb:hsql://localhost/aliasdb, connect, you will find the data you created.

The best way to use HSQLDB for development is running a Server instance (which could store data purely in memory). While the server is on, you can connect from multiple clients, including GUI, to test and browse the data.

The URL form jdbc:hsqldb:hsql://localhost is used to access the server, while the server itself is using jdbc:hsqldb:mem:test as its internal memory database. The server must be started first with a command like this:

java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 mem:test

You can use Derby In-memory-database and H2 in-memory-database. They are good.but H2 has a better performance. H2 has a web console GUI and Derby has eclipse plugin GUI. You can try it. I have used them in the project. H2 Database has a tag in SO and its auther will support you in SO. I think fredit is right for using server mode instead of embed mode. But his url doesn't seem to be right. At least it should use mem. For Derby, the url is jdbc:derby://myhost:1527/memory:myDB;create=true. This is the exmple url for H2 jdbc:h2:tcp://localhost/mem:db1.

You need to use server mode, so client can connect to it from network. One thing you need take care is ,in memory mode, since it's in memory, when your java process is over,the JVM is not there, the data in DB is also lost. But for testing,it becomes an advantage. You don't need to clear the test data.

H2 has a very good feature for test purpose.It has SQL support which runs compatibility SQL for IBM DB2, Apache Derby, HSQLDB, MS SQL Server, MySQL, Oracle, and PostgreSQL. Which means even you use special SQL in MS SQL Server can get the same result in H2. I believe you will like it.

the links

• H2
• Derby

I know HSQL has in memory mode, but i haven't used it.

Good luck.

UPDATE

I believe if you instead write query in your test case, it will return results. I have done this a lot in my test case. Even in embed mode as long as you create your JDBC connection in your test case. If you feel this is inconvenient, you can even dump the data output to files.But you must load the data into the HSQL in your test case, you can use BeforeClass to do it.If you cannot do this,You have to use server mode.

The hsql seems to be a bit different in JDBC url for server memory mode

for details refer to http://hsqldb.org/doc/guide/guide.html#N108D2 Connections

You have to deal with some server configs.

@Rachel, I Finally find a link to help you set up server mode with in memory mode. Look at this. It's a bit complex.

"Running a memory-only HSQLDB in server mode"

• http://www.ramuenke.de/archives/34

• How to run a HSQLDB server in memory-only mode

NOTE

The second link didn't tell in detail how to connect correctly. So try the first one.