Continuing from the previous entry, I’ll discuss JDBC. Although there was a section on Servlets, the recommendations are more decision-levels such as deciding on a framework, mad caching and hiring a UI designer. So here I go…
Use DataSource Whenever Possible
I don’t fully agree with this yet, since I still don’t see the absolute benefit when using the DataSource technique. The book explains that using the old Class.forName() and DriverManager.getConnection() contains references to specific implementation classes.
I don’t agree because these information are Strings, which can be read from a config file and drivers can be loaded dynamically. Java programmers have always also used generic JDBC interface to deal with the underlying database (as I mentioned in a previous post).Therefore I see the same level of portability in using both methods, especially when a ConnectionManager is used to retrieve connections (encapsulation of connection logic).
Pool Database Connections
Enough said. If you don’t understand this ask me or Google.
Separate Application, Persistence and JDBC logic.
As per most apps, beans are used to model entity information and are already separate from JDBC logic. As a plus, using a DAO interface model can further isolate your persistence logic from the application, allowing your underlying persistence implementation to switch to non-JDBC.
This is already a common pattern in EJB, where entity beans rely on a DAO to perform stores, loads and finds.
Do not Rely on Built-in Key Generation
For portability sake, this is a must. Besides difficulty in locating the record just inserted, other databases might not have this feature.
The book also provide a sequencing method to generate unique keys, though I think it can be more easily implemented with a SELECT MAX (only for trivial apps) or a separate table storing the current sequence. The method presented offers better performance than my methods, so do an evaluation before deciding which to use.
Use PreparedStatement
I would say not only use it, but use it correctly. The purpose of using PREPARED statements is that it can be executed repeatedly efficiently. Therefore if you prepare a statement, execute it once, and prepare the same statement again next time and execute it once, you aren’t really using PreparedStatement correctly.
The preferred way to use PreparedStatements is have it prepared once, and executed many times. Be it a static query or with ? parameters, these “prepared” statements will offer better performance.
Reference Columns By Number
This is actually two problems in one. The first is that access to metadata information is expensive, and referencing column by name requires metadata. Most programmers access columns by name now due to the flexibility and convenience of not needing to remember column numbers as well as able to change the sequence in the SELECT without affecting the code.
However, just like column numbers can change, column names can change as well when the SQL is changed. The book says that rs.getLong(1) is as bad as rs.getLong("personID")
The “correct” solution is to externalize both the SQL and the sequence number so that the query can be changed without affecting the code
new PreparedStatement(PERSON_QUERY);
rs.getLong(PERSON_QUERY_PERSON_ID);
Close Statements and ResultSets
Despit JDBC specifications, some drivers do not clean up as cleanly as specified. To clean up completely, use the finally clause. In fact, this template should always be used.
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = ..;
stmt = conn.prepareStatement(..);
stmt.set..(..);
rs = stmt.executeQuery();
while (rs.next())
// ..
} catch (SQLException sqlE) {
// handle exception
} finally {
if (rs != null)
try { rs.close() } catch (SQLException sqlE) {}
if (stmt != null)
try { stmt.close() } catch (SQLException sqlE) {}
if (conn != null)
try { conn.close() } catch (SQLException sqlE) {}
}
Thread JDBC code in Swing Applications
In general I think this should be “Thread time-consuming task in GUI applications”. The threading, though it complicates the code, is necessary to provide a responsive GUI to the user. When time consuming tasks are performed in the GUI event thread, the event thread is unable to perform other tasks such as redrawing the UI or getting input from the user through the UI. This includes unable to cancel the event if the user chooses to.
At the same time, care should be taken when accessing GUI elements from threads. See the wiki for more information on this.
Do Not Use Primary Keys with Real-World Meaning
This tip effectively contradicts with the perfect data model of the primary key. The fact it is listed as a best practice in a published book demonstrates the vast difference between academic theory and industry practice. However for practical purposes, it is still better to issue a meaningless identifier to the data row.
You might argue some data like your IC number don’t change, but they might be entered incorrectly into the system in the first place (data entry, human/machine error) and needs to be modified.