How to implement database functionality effectively?

Question

I am developing a Java Desktop Application which uses MySQL database. The DB has 6 tables. Every table, as usual, should allow CRUD (Create, Read, Update and Delete) operations.

I have designed 6*4 = 24 JPanels, 4 JPanels for each tables. Each JPanel have Components to take user input and perform the CRUD operation for which it is designed. For instance, a JPanel3 is designed for Create operation for Table1.

Now I want to know the following:

Q1. Would it be better to write 24 functions, each performing a specific function for a specific table?

Q2. This kind of situation is very normal as every application generally has many tables. So, Are all those applications use this approach of writing each function for each operation for each table?

Q3. As it is a Swing Application and every CRUD operation need the database connection, so Would it be better to make a connection to the database when the user starts the application?

or

Would it be better to make the database connection at the time when user clicks on the "save" or "edit" or "delete" or "create" button?

Q4. Would it be better to refer the connection from an instance variable which is shared by all the 24 functions? or Would it be better to have every function its own Connection?

Any other suggestions are also welcomed.

Answer 1

What does the application do?

I hope you're using more descriptive names for your objects than "JPanel3" and "Table1"?

Answer 2

It would be great if you could join a project with experienced people. Your questions are understandable but... most project already have solutions for this.

I believe there are many more problems than the ones you describe, and you could work for months just to discover the questions ;-)

There are so many suggestions you might need that we can't even start.

Could you consider a framework such as Hibernate? Although it would be complex for you to learn, in the process (and the recommandations) you would learn a lot about the database layer problems and solutions.

---

But to answer some of your questions :

Q1 : no, writing 24 functions would be a lot of duplication.

Q2 : certainly not.

Q3 : A database connection typically times out. I suggest to ask for one at appropriate times..., for example in the cases you describe.

Q4 : obtaining a connection should be shared code.

Answer 3

See this article about the DAO pattern, and then see the Don't repeat the DAO! article so that you make a generic, reusable DAO.

In short - wrap your database access functionality in a single class and reuse that class from everywhere, thus effectively making your application not explicitly dependent on database operations - only on the DAO class (interface).

Answer 4

A1. No. The design should be guided by the requirements and the domain logic, not by technical considerations. Usually, it does not make sense to have all CRUD operations separately accessible to the user for each and every table. Write functions to perform those operations together that belong together.

A2. No. Nowadays, most applications use an Object/Relational mapper like Hibernate for this kind of thing. But still, there should be application logic on top that executes related operations together.

A3/4. Use a DB connection pool. O/R mappers generally do that automatically.

Answer 5

Q1: Could you have fewer JPanels and use a JComboBox to let the user pick wich table to operate on? That will probably save you some code.

Q2: In some way, yes. But see my answer for Q4.

Q3: Do the connection when the user clicks, and leave it open as short time as you need. Database connections takes resources.

Q4: It would be much better if you do all the Database-code and interacting in a single class, called Data Access Object, then is it much easier to change database from MySQL if you would like. See http://java.sun.com/developer/technicalArticles/J2SE/Desktop/javadb/

Answer 6

It would be better if you write no code. Yes! Just drag and drop. Data validation, reports and graphs. Create a complete database program without writing a single line of code. Common things such as new/edit/delete/search/update. Use JDeveloper.