HSQLDB is the leading SQL relational database engine written in Java. It has a JDBC driver and supports a rich subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003 enhancements. It offers a small (less than 100k in one version for applets), fast database engine which offers both in-memory and disk-based tables and supports embedded and server modes.
Being slow when the data files get to be large fixed it. I admit last time I tried it was a year ago, but a select count on one column in a table with 10,000 records could take a few minutes.
It worked great for small stuff, but seemed to totally choak when the database grew. Plus startup times became pretty annoying. Once the tables became big enough even Oracle would be able to start quicker.
Just to be clear I think it is an first rate product for the small stuff, just inquireing from a “I got a lot of data to work with.” point of view.
OOo 2 uses integrates HSQLDB. It’s one of the many options OOo has for a database back-end.
Isn’t Java too slow for this kind of stuff? Why make a database engine in Java?
Someone????
“Leading” sounds like marketing drivel. Where’s the beef? How does it stack up against Cloudscape et al?
to be able to have access on mac, linux, windows, solaris…
It’s actually just about the fastest pure Java database available when using purely in-memory tables. If your requirements are to do basic SQL on large amounts of data that is still small enough to be kept in RAM (< 2GB) it can perform quite well.
In many of my tests, it could perform the query and return a ResultSet more quickly than a network based database could get the query started!
Having said that, once you start using disk-based (CACHED) tables, it slows down dramatically. Also, it still has a global lock in a few cases that can make it unsuitable for some multithreaded applications.
But for what it is good for, it’s by far the best product on the market.
You guys may want to check out http://www.hxsql.com/. Seems to be based on HSQLDB but overcomes some of the limitations. Is closed source though.
Check out the disk engine comparisons here. http://cluedup.com/hxsql/speedh.html
Seems to match what you were saying about tables on disk.
HSQLDB is a very good product. if it is used in the correct place.
One other thing is, it is very good for unit testing RDBMS related projects. Especially with an ORM solution. you create your DB in memory, make your test and erase it.
For speed, i am not sure how is it, bu i use it with in memory and cached table modes witohut a problem. There is a performance comparison (benchmarks are evil) here. i suggest reading the comments as well.
http://jamie.ideasasylum.com/notebook/index.php?id=4
It would be nice to see MySQL tested in embedded mode since it has the capability now.
Is it a good or bad sign that the “leading SQL relational database engine written in Java” is unknown to pretty much everyone I asked?
Isn’t Java too slow for this kind of stuff?
No. Database code is mostly bound by disk access, and an in-memory database will be fast as long as the data structures are well designed enough. Anyway, Java is usually JIT compiled, which makes it faster than you think.
Why make a database engine in Java?
Well there can be advantages in certain cases to embed your database engine in your application, such as lower overhead, easy portability and such. This is why people use databases like SQLite. If your application is written in Java, such a database should also be written in Java because of the awkwardness of Java’s interfaces to native code.
Here is another benchmark project for several java databases and ORM solutions:
http://www.polepos.org
but before reading their results a little warning, the author seems to be have a tendancy to be biased about object databases. (possibly have an affiliation with db4o, or a supporter of it).
Fairness of comparing object databases (db4o, prevayler etc) with RDBMS is questionable due to the lack of SQL query mechanisms. Anyway, you can see how fast HSQLDB is there too.
Is it a good or bad sign that the “leading SQL relational database engine written in Java” is unknown to pretty much everyone I asked?
If they are in the Java field I would point my finger at them and laugh. If they are outside the Java field that is understandable.
I’d be using a database but can’t find a good GUI for any of the OSS databases. At least nothing that even remotely compares to the ease of use of MS Access.
Until there are some good GUI’s the OSS databases will be restricted to servers.
Where are the database tools for desktop use?
As long as Microsoft rules the desktop it will rule everything else.
Access is a reporting tool, not a DB tool. Look at something like TOAD, that’s a db tool. Access lets you create forms, reports, and use scripts to make date entry and reporting applications.
Open source has yet to produce many tools as good as access ( as limited as access is).
Access can connect to many dbs, not just using the built in JET db engine. Oracle, DB2, MSSQL, I think anything that has an odbc driver.
Well, what kind of GUI are you talking about? Yes there is a GUI for it, but it is rather trivial. I would suggest using SQuirrel SQL Client – http://squirrel-sql.sourceforge.net . You can view table structure, run sql queries and all that stuff with all jdbc drivers.
<p />
As for the comment about ‘M$ ruling the desktop’, I take it that you haven’t done any real enterprise application development; Access really doesn’t come into play in those areas.
<p />
But, again, try that client, it works great. I haven’t used version 2 yet, but I have been using 1.x for years.
There is a built-in but very simple gui in Hsqldb. However, for Access like interface, You will have to wait Open Office 2 (will be out some time soon). it uses Hsqldb for its database tool.
There are plenty Java universal database GUI’s available. They connect to multiple databases, and they are really handy in multi database situations.. My favorite is AquaSoft’s data studio.
http://www.aquafold.com/
And dbvisualizer.
http://www.dbvis.com/products/dbvis/
but MS doesnt rule anything else
When I tried using HSQDL in disk-based mode. It would give me out of memory errors when doing SELECT statements on a VIEW which was made up of two tables with 120,000 and 15,000 rows each. Mckoi DB handles these tables (Same data and view) and even much larger tables without any problems in my experience, but it doesn’t seem to be as actively developed as HSQLDB