WhichdDatabase would prove efficient?

Question

I have to develop a web application in Java, a pretty big one with loads of data to store and manipulate. I am also planning to use Hibernate and Spring.

There are many databases available now like MSSQL Server, MySQL Server, Oracle, db2, etc.. Which do you recommend?

Features I look for would be

• Java - database connection should be efficient and easy to implement

• good user interface for Database Server Management

• data storage capacity

• support

• and obviously the cost factor.

All valuable suggestions would be appreciated. Thanks in advance :-)

Edited: The web application will be hosted on a Linux Server.

Answer 1

All of the databases you mention scale very well as long as the databases are properly designed (normalized for OLTP and denormalized for OLAP).

All other things being equal, if cost is a large factor, then MySQL would probably come out cheaper than the others.

Answer 2

There was a similar question asked yesterday that might be worth checking out:

http://stackoverflow.com/questions/1603088/what-database-to-use-for-big-data-storage-and-manipulation

Answer 3

Everything works with Java, and you can get support for anything if you pay for it.

My personal experience:

1. Are your servers Windows machines? If not, MSSQL is out. Otherwise MSSQL has pretty decent GUIs. I haven't tried working with it and Java -- the Visual Studio support for developing with it is very good.

2. I haven't found Oracle to be worth the cost. I worked at a place that paid for support and still it took them forever to acknowledge data-munging bugs in the JDBC driver (still not fixed, last I checked).

3. My MySql usage is simple -- if you need complex functionality, check to make sure that it's supported

My 2 cents: stick with MySql or Postgres, simple SQL and try not to make any stored procedures or use proprietary SQL. If you find that you outgrow it, it will be a trivial port.

The more you try to take advantage of the proprietary features, the more you will be locked in.

Answer 4

I would prefer MS SQL Server for speed, support etc Most windows hosting provide MS SQL support at a nominal fee. here is a free GUI SQl Manger for it http://sqlmanager.net/en/products/mssql/manager/download SQl Server management studio is also there :)

Answer 5

Java, Hibernate, and Spring will work with almost any database. Tools like Squirrel simplify DB management across DBs as well.

The key point is cost. I recommend PostgreSql. It is outstanding: capable of huge volume, strong list of features, has a solid user community, and is free/open-source.

Answer 6

In addition to MySQL, Postgresql, and MS SQL Server, You might also want to look at Apache Derby (aka IBM Cloudscape, aka JavaDB). It's very portable: The database code is just a jar file; the database files can also be copied from one platform to another with no problems. It supports SQL99 and SQL-2003 standards, in memory databases and can be used either as an embedded database or network database. It doesn't come with it's own user interface, you would have choose from one of a number of JDBC SQL front-ends such as Squirrel SQL, Aqua Data Studio or SQL Developer (among others). More references can be found at IBM's web site.

I also agree with other posters here, Oracle is not worth the cost or headache. One notable headache is that it's JDBC drivers return non-standard objects for dates, so your Java code will need to contain Oracle specific hacks.

Answer 7

You can Check Firebird

• java - database connection should be efficient and easy to implement : Jaybird Compiere is a good example

• good user interface for Database Server Management : IBExpert Database Worbench for Windows FlameRobin everywhere else

• data storage capacity : you can check this blog

• support IBPhoenix IBSurgeon IBExpert and free support list here

• and obviously the cost factor : free

Answer 8

I suggest you

• Get DBVisulizer, which is the best tool I've encountered for developing with databases
• Prototype using an embedded DB like Derby or H2
• Load test using one of the above databases to determine if its sufficient for your production needs
• Use MySQL or Postgres for production if they aren't. Depending on your Linux distribution, they will probably be as simple to install as 'apt-get install mysql' or something similar
• Use the Hibernate tools to generate schema after you've created your object model and tweak that, rather than trying to start schema first, unless you're very experienced at schema creation.