I've been struggling to get a Java program to connect to MS SQL Server, and I'm starting to wonder if MySQL would be a better choice for my (learning) project.
Sun's tutorials refer to Java DB, but I've never heard of that in any other context, so it seems not the most useful database to learn about.
I appreciate any insight into the most natural way to connect Java to a commonly used database.
Perhaps you could describe the problems you've been having with connecting to MS SQL. Of course it's possible, so it's likely something small that you have or have not done that's preventing the connection from working.
There are many open source database servers with JDBC drivers. One that you might consider is HSQLDB which has a completely in-memory mode so you don't even have to think about setting up a server. This is probably a great way to learn the basics of SQL.
The kind or the name of Database won't affect your education process as you will work with JDBC.
I think you can go with any. Just set up it in the proper way on your machine and connect with appropriate connection string.
I recommend MySQL or Oracle. From Oracle you can get free database as well, however size of the database is limited.
Reasoning for this is that those are most used databases and it's good to get some visibility for those. Use JDBC and it's similar interface, but doing code on top of the database and there are other things you need to learn as well. Especially if you do any application which is bit more complex. Also getting hands dirty with basic operations with database is always good.
Microsoft even has documentation on its website. Google keywords were "jdbc sql server".
If you want to stay in Java, one option is Berkeley DB Java Edition. It's the same BDB software that was from Sleepycat, which had a sterling reputation in the embedded database market.
Pick one database that you can figure out how to install. Most are pretty easy and it will give you a good idea of what using a database is like. Once you have a working app, try porting it to another database. I would recommend using JavaDB for the first one and then one of the other db - Oracle, MySQL, Postgres, etc.
The biggest issue that I've had with using MSSql is when using the antiquated JDBC -> ODBC Bridge drivers; these do a half-assed Java wrapper around the ODBC calls, which are flaky and fragile. If you are using JDBC ODBC Bridge, replace them with the latest native JDBC drivers from microsoft- they work a million times better.
Java DB is Apache Derby rebranded and included in the JDK. It is ok, and it is much easier to install than natively running databases.
It is very important to use a good driver to the database! That might solve all your connection problems.
Also note that if you switch database, you most likely also need to change your SQL unless you use a layer like Hibernate or JPA.
The simplest way to connect to an external relational database (as opposed to an in memory database like berkeley db) is via JDBC. You should get familiar with JDBC before you progress on to any other storage methods. For most projects you will be fine using either mysql or postgresql. Don't worry about deciding on one over the other, for smaller projects the differences are not that relevant. As long as you are using jdbc you will be ble to switch between the two later if you really need to.
To get started I would download mysql and follow the jdbc tutorial on the sun site http://java.sun.com/docs/books/tutorial/jdbc/index.html ).
At a later date you may decide you need to use an object-relational-mapping tool, like hibernate, but I would not worry about that now either, just get familiar with jdbc and either mysql or postgresql.