Durango2008 wrote:
> Hi this is really not a Java specific question but more in regards to OO
> design in general. I have an application which has access to multiple
> tables in a database and a multitude of different SQL statements for
> manipulating the many tables and retrieving data from them. The part I
am
> struggling with is a good class design for the Java code which deals
with
> the DB.
> I would like to achieve the following:
> 1. Separating DB code from the main application code.
> 2. Reducing code redundancy
> 3. Managing SQL statements in a good way.
>
> For the most part I can deal with items 1 and 2, however the managing of
SQL
> statements is the part that gets me.
> There will be a huge number of different SQL statements needed for this
> project.
> However to create a means of dynamically creating SQL statements can
become
> a bit over-complicated.
Prepared statements are one technique to avoid creating large numbers of
almost-identical SQL statements. You can use prepared statements if you
expect to issue many queries such as
SELECT name,address FROM CUSTOMER WHERE customer_id = 123456
but for different values of customer_id. Instead of creating a new SQL
qstatement for each customer_id, you use a pattern like this:
// Initialisation code, probably in a class constructor
String query =
"SELECT name,address FROM CUSTOMER WHERE customer_id =?";
PreparedStatement pstmt = conn.prepareStatement(query);
...
// Set parameter #1 in prepared statement
int customer_id = 123456;
pstmt.setInt(1, customer_id);
// Execute the query using the specified parameter
ResultSet rs = pstmt.executeQuery();
// Now use ResultSet as usual
> I also don't like to see a class filled with hard-coded SQL strings
waiting
> to be called, but it looks like it's the most practical means.
The java.util.ResourceBundle class is one solution to this problem. You
put your SQL strings in a text file, separate from your source code,
with entries such as
sqlCustomerByID=SELECT name,address FROM CUSTOMER WHERE customer_id =?
and retrieve them with code like this:
ResourceBundle sqlResources =
ResourceBundle.getBundle("SQLQueries.txt");
String query = sqlResources.getString("sqlCustomerByID");
David Harper
Cambridge, England
|
