Q:

How to structure programs that interact with databases?

I've just finished a book on MySQL and I'm in the infantile stages of learning to couple that with Java. I know that the technology I need to learn is called JDBC and that it essentially allows you to execute programmatically created SQL statements. I plan on making a database centered records keeping and management program as a long term project. I'm not quite sure how to fit in a program structure to interact with the database.
Why is the typical way this is done, in terms of high level organization?
How do I go about organizing classes to put together SQL statements and work with them and trade info back and forth with the program and handle results?
What are some do's and more importantly, what are some don'ts?

A:

A typical "MVC for the web" program might look something like this:
RDBMS <--> ORM <--> DAL/SL <--> Controller <--> ViewModel <--> View

RDBMS - Your database, usually something like SQL Server, Oracle or Postgresql.
ORM - An Object-Relational Mapper, like Hibernate.  The ORM converts tables to class objects, and vice versa.
DAL/SL - Data Access layer/Service Layer/Repository.  This is a layer of abstraction between your DAL and your User Interface that provides data retrieval services, business logic, and so forth.
ViewModel - The View Model maps data between your domain objects and a user interface.  It can also contain user-interface logic.
View - The User Interface.
That ought to keep you busy for awhile.

