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 need. 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.
7 Answers
Comments
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.
2 Comments
Consider using Hibernate: https://www.hibernate.org/ It can create java classes with regular CRUD methods from existing database schema.
Comments
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.
Comments
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.
Comments
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).
Comments
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.
