Databases and Java

I am starting out writing java code and interacting with databases for my "nextbigthing" project. Can someone direct me towards the best way to deal with adding/updating tables/records to databases? Here is my problem. There is too much repitition when it comes to DB code in java. I have to create the tables first (I use mysql). I then create classes in Java for each table. Then I create a AddRow, DeleteRow, UpdateRow and Search* depending on my ne开发者_运维技巧ed. For every table, every need creating this huge ass sql statement and the classes all seems like a huge waste of my time. There has to be a better, easier, more efficient way of doing things. Is there something out there that I do not know that will let me just tell Java what the table is and it automatically generate the queries and execute them for me? Its simple SQL that can be auto generated if it knows the column names and DB table inter dependencies. Seems like a very reasonable thing to have.

Check out Hibernate - a standard Java ORM solution.

User hibernate for mapping your classes to Database.

Set its hbm2ddl.auto to update to avoid writing DDL yourself. But note that this is not the most optimal way to take it to production.

Consider using Hibernate: https://www.hibernate.org/ It can create java classes with regular CRUD methods from existing database schema.

Of course there is a much better way !

You really want to learn some bits of Java EE, and in particular JPA for database access.

For a complete crash course on Java EE, check out the Sun the Java EE 5 tutorial. http://java.sun.com/javaee/5/docs/tutorial/doc/

Part 4 - Enterprise Beans Part 5 - Persistence (JPA)

Then you want to try Hibernate (for instance) which has an implementation of JPA.

This is for Java 5 or later. If you are still in Java 2, you might want to try Hibernate or iBatis.

You can also try iBatis, if you want control over SQL. Else JPA is good. You can also try using Seam Framework. It has good reverse-engineering tools.

There is also torque (http://db.apache.org/torque/) which I personally prefer because it's simpler, and does exactly what I need.

With torque I can define a database with mysql(Well I use Postgresql, but Mysql is supported too) and Torque can then query the database and then generate java classes for each table in the database. With Torque you can then query the database and get back Java objects of the correct type.

It supports where clauses (Either with a Criteria object or you can write the sql yourself) and joins.

It also support foreign keys, so if you got a User table and a House table, where a user can own 0 or more houses, there will be a getHouses() method on the user object which will give you the list of House objects the user own.

To get a first look at the kind of code you can write, take a look at http://db.apache.org/torque/releases/torque-3.3/tutorial/step5.html which contains examples which show how to load/save/query data with torque. (All the classes used in this example are auto-generated based on the database definition).

Or, if Hibernate is too much, try Spring JDBC. It eliminates a lot of boilerplate code for you.

iBatis is another good choice, intermediate between Spring JDBC and Hibernate.

It's just a matter of using the right tools. Use an IDE with tools to autogenerate the one and other.

If you're using Eclipse for Java EE and decide to head to JPA, then I can recommend to take benefit of the builtin Dali plugin. There's a nice PDF tutorial out at Eclipse.org.

If you're using Eclipse for Java EE and decide to head to "good ol" Hibernate, then I can recommend to take benefit of the Hibernatetools plugin. There's good reference guide out at Hibernate.org.

Both tools are capable of reverse-engineering from a SQL table to fullworthy Javabeans/entities and/or mapping files. It really takes most of boilerplate pains away. The DAO pattern is slightly superflous when grabbing JPA. In case of Hibernate you can consider to use a Generic DAO.