Java with database storage.

Question

I have to write a program that can take bookings, store them and then access them at a later time, the application has to be written in Java. Because of this i have been looking into various ways to use a database with Java.

I have been looking into using the JDBC with the mySQL driver database and also looking into the javaDB. What would you recommend that i do to create this program, Has anyone have any experience of writing a program that uses a DB in java and could give me any tips?

Thanks!

Answer 1

One really simple approach is to use Spring's JDBC classes, which provide a lot of convenience methods over raw JDBC and automatically manage the releasing of resources. Spring also provides a rich exception hierarchy, which allows specific actions to be taken based on specific errors (e.g. retry on database deadlock).

Also, unlike many other persistence layers Spring does not hide the JDBC implementation under the covers (it acts as a thin layer), allowing you to use raw JDBC if necessary.

Initialisation

// First create a DataSource corresponding to a single connection.
// Your production application could potentially use a connection pool.
// true == suppress close of underlying connection when close() is called.
DataSource ds = new SingleConnectionDataSource("com.MyDriver", "Database URL", "user", "password", true);

// Now create a SimpleJdbcTemplate passing in the DataSource.  This provides many
// useful utility methods.
SimpleJdbcTemplate tmpl = new SimpleJdbcTemplate(ds);

SQL Update (or Insert)

// Simple example of SQL update.  The resources are automatically release if an exception
// occurs.  SQLExceptions are translated into Spring's rich DataAccessException exception
// hierarchy, allowing different actions to be performed based on the specific type of
// error.
int nRows = tmpl.update("update Foo set Name = ? where Id = ?", "Hello", 5);
assert nRows == 1;

SQL Query

// Example of SQL query using ParameterizedRowMapper to translate each row of the
// ResultSet into a Person object.
tmpl.query("select * from Person", new ParameterizedRowMapper<Person>() {
  Person mapRow(ResultSet rs, int rowNum) {
    return new Person(rs.getString("FirstName"), rs.getString("LastName"));
  }
});

Answer 2

Check this examples: http://www.roseindia.net/jdbc/jdbc-mysql/. A lot of code samples about using JDBC with MySQL.

Answer 3

I can certainly recommend JavaDb (the database that comes with Java 6). It was original the Derby database from IBM and is very capable. It integrates well with a Java program (either running as a standalone process, or in the same VM as the client program). You won't have to worry about an additional non_Java install (which may be important, depending on how/where you're deploying).

If you want/need an ORM (object-relational mapping), Hibernate is the de facto Java standard these days (here's an introduction). However, if that's overkill for your project, at least check out Apache Commons DbUtils, which will reduce a lot of your boilerplate code.

Answer 4

Sun has two JDBC tutorials online that you might find useful, JDBC Introduction and JDBC Basics. These are part of a much larger series of Java Tutorials that have a lot of great information. They're usually the first place I look when I run into something I don't know how to do in Java.

Answer 5

I'm sure every Java developer has written an application that talks to a database using JDBC. Many people stop using raw JDBC fairly quickly either using something like Spring JDBC wrapper or a full blown ORM such as Hibernate. All these will make writing the database layer that little bit easier but I generally feel you should have a reasonable understanding of what is going on under the hood before diving into them.

So the in-process or dedicated database question? It depends. How many people will be accessing the application at once? (An in-process database would be useless if you need multiple people accessing the same database). How much control over the environment do you have? (it is a waste of time suggesting a dedicated database if you can't install it). Is the application web based or a desktop application? (A web based solution could use an in-process database and allow multiple users to access it). How important is the data? (A in-process database is less likely to have the same level of back up solutions as a dedicated database).

Answer 6

In your case, I'd use Java6 and Java DB (aka Derby). It's not that installing and using MySQL is complicated (its quite simple actually) but well, why would you do that if you already have a capable database?

Having that said, to get started with JavaDB, have a look at the Java DB Reference, there are plenty of technical articles there. Pay a special attention to Working with the Java DB (Derby) Database - NetBeans IDE 6.5 Tutorial.

For the data access itself, you could use the Java Persistence API (JPA) as in Creating a Custom Java Desktop Database Application. But I wouldn't do that for homework. Instead, I'd start with the basics i.e. with JDBC and do everything by hand. You mentioned it and I think its a good idea. Have a look at Tutorial: Java databasing with Derby, Java's own open source database, it might be very useful (I'm not saying the code shown there promotes all best practices but it's pretty simple and will get you started).

Don't pollute your mind with advanced topics such as connection pooling, don't use frameworks like Hibernate, JPA or even Spring (these frameworks know how to do things, you don't and the point is not to learn using frameworks, at least not right now). Keep it simple and sexy but do it by hand.