floater
Java Jedi
总版主
发贴: 3233
积分: 421
|
于 2003-07-01 02:50
JDBC is the java database connection API, part of core J2EE. It utilizes JDBC drivers to talk to databases and send data from databases back to users(callers). A simple example is as follows:
/** * This is a typical usage of jdbc. The only varying portion is the try block. * How to seperate the invariance from the variance??? */ import java.sql.*;
public class JDBCTemplate { public static void main(String[] args) { String _driver = "com.sybase.jdbc2.jdbc.SybDriver"; String _server = "jdbc:sybase:Tds:192.168.25.38:1009/dweb01"; String _login = "web"; String _pswd = "temp123"; Connection conn = null; PreparedStatement ps = null; ResultSet rs= null; int ret = -1; try { //1 setting to get the connection. Class.forName(_driver); conn = DriverManager.getConnection(_server, _login, _pswd); //2 input, varying String sql = "select * from chng_rqst where idn = ?"; //3 = 1 + 2 to get the ps. ps = conn.prepareStatement(sql); //4 varying: setting different parameters ps.setInt(1, 3); //5 result: depends on query or update. rs = ps.executeQuery(); if (rs.next()) ret = rs.getInt(3); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (ps != null) ps.close(); if (rs != null) rs.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
Ignore the comments for the time being. Let's take a close look on the code here. Consider the case that you need to pass in 10 different sql statements(query or update). A naive approach is to repeat most of the above code 10 times. A better solution is to isolate the changing portion. The changing area is within the try block, everywhere else is remaining the same pretty much. So let's now concentrate on the try block. Let's move the code in the try block to a function and let users override that function to suit their need. In this way, we could isolate the changes from users and thus users could reuse our code. In order to do this, we have to think about the parameters passed in and out. Inside the try block, users' input are in step 2-5 and and thus the input is Connection. The output is a little bit tricky. Ideally, we should simply return a ResultSet back to users and let them do whatever they want. Unfortunately, this is not working. When we close the connection and PreparedStatement, the associated ResultSet becomes NULL(If you don't believe it, try it and see). So we have to return a generic Object. Here is the modified version
/** * This class is using template(callback) to hide the tedious code for jdbc exception catching. */ import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.DriverManager; import java.sql.SQLException;
public abstract class SQLOperation { public abstract Object sqlQuerySetup(Connection conn) throws SQLException;
public Object sqlQueryExecute() { String _driver = "com.sybase.jdbc2.jdbc.SybDriver"; String _server = "jdbc:sybase:Tds:192.168.25.38:1009/dweb01"; String _login = "web"; String _pswd = "temp123"; Connection conn = null; Object ret = null; try { Class.forName(_driver); conn = DriverManager.getConnection(_server, _login, _pswd); ///////////////////// This part is from users of this class ret = sqlQuerySetup(conn); ///////////////////// } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (conn != null) conn.close(); } catch(SQLException e) { e.printStackTrace(); } return ret; } }
//testing and usage public static void main(String[] args) { SQLOperation sqlo = new SQLOperation() { public Object sqlQuerySetup(Connection conn) throws SQLException { String sql = "select * from chng_rqst where idn = ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, 5); ResultSet rs = ps.executeQuery(); String aa = null; if (rs.next()) { aa = rs.getString(3); } System.out.println("result=" + aa); return aa; } }; String aa = (String)sqlo.sqlQueryExecute(); System.out.println("return=" + aa); } }
Now you could see most of the code in sqlQuerySetup(Connection) is user dependent. The code in sqlQueryExecute() remains the same for all different sql calls. We make the class abstract so users can't create this class without overriding the method.(Well, a empty implementation does nothing anyway) In the main() we create this inner class to implement that method, it follows normal convention and reasoning for inner classes.
Recap: 1. What if you want to return an array or a list? Put your row or list in one of the Collection object, Vector, etc, and then return that Collection Object. 2. What if you want to execute Update(rather than query)? The result of Updates is an integer, wrap it and return it, or create a new class just for update. 3. To go one step further, take a look at the main(), i.e., the usage of the new class. It still depends on the JDBC API, we could further extract that away so users could just input sql statement as a string, and set various parameters, without the knowledge of JDBC. In fact, there is such a framework in the Expert One-on-One, which provides a generic interface, without users knowing JDBC. To go even further, we could use JDO or DAO to hide the details of persistance layer by create an interface, like create(), load(), update() etc, so users don't need to know how we implement the persistance(We may use jdbc, o/r mapping, jdo, etc).
Note: 1. I just use sybase jdbc driver, you may use whatever driver you want. 2. I hardcoded the properties in those 4 string variables, normally you should load from a properties file(or xml, whatsoever). 3. This is a typical case where "you don't call me, I'll call you", the callback method, or template pattern, whatever. (Because you can't return a ResultSet).
floater edited on 2003-07-01 03:34
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." - Martin Fowler, Refactoring - Improving the Design of Existing Code
|