Java开发网 - JDBC revisit

Java开发网

您没有登录

» Java开发网 » 技术文章库

打印话题 寄给朋友 订阅主题

作者

JDBC revisit

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

作者

Re:JDBC revisit [Re:floater]

floater

Java Jedi

总版主

发贴: 3233
积分: 421

于 2003-09-21 11:47

Recently, Why introduced us this nice framework:
http://www.cjsdn.com/post/view?bid=21&id=48838

I took a look at the source code and gave out some comments in the above post.

In this post, let's see how to fix the problems I mentioned. Basically, we are taking a refactoring approach, the bottom line is that if the code is too long(I use the rule: every class file shouldn't go beyond 500 lines, and I haven't seen exceptions in application development).

1. The connection pool should be out, in a seperate package. What we pass into here should be just a connection because that's all it's related. In fact, the connection pool and datasource should have its own package, independent of everything else. So we pass the Connection object to the constructor.
2. Transaction handling is also connection related, so we leave that out in a seperate class.
3. The error handling is kind of wicky. It has a bunch of functions to check what the root cause is. Comm'n, I don't want to go through each call to know exactly what's wrong, just tell me what it is, I am a lazy guy. Besides, if we know the error, we won't be able to do anything in the code(with if statements). So we change it to a simple msg.
4. Continue in error handling, we really don't need subclasses to translate any specific db error code. We could, instead, use a properties file to map the error codes to messages. This saves users from programming. So we add a file name to the constructor.
5. If we take a bird view of the main class, it has a big chunk of code for SQL parameter settings. We could move them out of here. There is another big chunk of code dealing with converting from primitive typs to objects, so let's move this piece out too.
6. The SQLResult class is mixed with implemention interface with user interface, so we should seperate them too(input/output).

Having done these, we have something like this file tree:
(ignore the files with names starting with Test..., they are for testing purpose).

why edited on 2003-10-26 10:33

"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

作者

Re:JDBC revisit [Re:floater]

floater

Java Jedi

总版主

发贴: 3233
积分: 421

于 2003-09-21 12:10

Now let's go through the files and look at some of the details.

1. SQLParameters class does nothing but setting the parameters, used later for PreparedStatements(SQL) and CallableStatements(stored procedures). We use two List interfaces to store parameters, convert primitive types to the corresponding objects. List interface gives us the flexibility to change the underlying storage without breaking the class. Since we want to expose these methods to users, our SQLRunner should extend from this class(not using a composition).
2. StmntParamSetting class will actually take the List and set them to the Statement, so this class does the convertion from JDBC types to SQL types. It's only for this purpose. If JDBC adds new types, like Blob/Clob in JDBC 2, then we just modify in here. In fact, we didn't do all the convertions, we leave out 3 Stream setters in JDBC. But we could always modify in here later on.
3. The sqlresult package contains the implementation for SQLResult and StoredProcResult, which are on the top of package for easier access for users(so you will see a round trip in package UML diagram, a valid reason to make a round trip).
4. The DatabaseException is inherited from RuntimeException so we don't need to catch them(if we do, probably we can't do much about them anyway).
5. The TransactionHandler is for transactions. The normal usage is:


   TransactionHandler trans = new TransactionHandler(conn);
    trans.start();
    //do sql operations
   trans.commit();

No interaction with everything else.
6. Now the finale, SQLRunner class. The simple usage is:


    SQLRunner sqlRunner = new SQLRunner(conn);
    sqlRunner.setSQLCommand("selection * from mytest2 where id = ?");
    sqlRunner.addParam(22);
    SQLResult sr = sqlRunner.runSQLCommand();
    System.out.println(sr);

You can do this too:


    sqlRunner.addParam(156);
    SQLResult sr = sqlRunner.runSQLCommand("select * from aces_oper where id = ?");
    System.out.println(sr);

But it's unnatural to set parameters first for code readers(maybe we should take out this option).
The public interface is as follows:

"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

作者

Re:JDBC revisit [Re:floater]

floater

Java Jedi

总版主

发贴: 3233
积分: 421

于 2003-09-21 12:22

The source code is attached, but haven't got time to fully test it(so use it in your risk).

From the original SQLExecutor package, we simplify some(exceptions), modify some(seperate/isolate dependencies), the purpose is to write something maintainable:
1. Seperate transaction/connection pool, so we could replace them with something better later.
2. Use List interface, so we could change from ArrayList to something else later. Use sqlrunner.sqlresult.QueryResultGenerator so we know what we need to generat the result. So these interfaces act as firewall in case we have a fire in one piece of the code and it can't populate to elsewhere.
3. Users of these package should care only the top classes in sqlrunner, so there is no need for them to dig into subpackages(implementation details).

These are the considerations that a mature developers should consider and should do.

A better version down further.

Is there a way to delete this file?

{ there's a checkbox "Delete old attachment sqlrunner.rar" at the bottom -- why }

sqlrunner.rar (13.63k)

why edited on 2003-10-26 17:20

"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

作者

Re:JDBC revisit [Re:floater]

mazalet

如果英俊是一种罪，那

CJSDN高级会员

发贴: 430
积分: 60

于 2003-09-21 12:43

仔细研究中。

如果你更热爱金钱而非自由，更习惯于被奴役的安宁而畏惧令人充满活力的争取自由的抗争，那么，请你静静地走开。我们不会乞求你的建议或是帮助。伏下身去讨好那喂养你的人吧。但愿身上的锁链不会给你造成太多的痛苦，但愿未来的人们不会记起你曾经是我们的国人。”
---------

作者	Re:JDBC revisit [Re:floater]
Johnny I will rock you 发贴: 87 积分: 10	于 2003-09-22 09:44 老大，第二段source里面resultset和statement好像没关么？

作者	Re:JDBC revisit [Re:floater]
floater Java Jedi 总版主发贴: 3233 积分: 421	于 2003-09-22 10:03 Where do you mean? Could you post the code here? "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

作者

Re:JDBC revisit [Re:floater]

Johnny

I will rock you

发贴: 87
积分: 10

于 2003-09-24 09:40

here, hehe:


            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;
            }

作者	Re:JDBC revisit [Re:floater]
floater Java Jedi 总版主发贴: 3233 积分: 421	于 2003-09-24 10:06 Here is the connection: (5th line from the top) ResultSet rs = ps.executeQuery(); "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

作者	Re:JDBC revisit [Re:floater]
Johnny I will rock you 发贴: 87 积分: 10	于 2003-09-25 10:50 ??what's you mean? I mean you never closed the rs and ps.

作者	Re:JDBC revisit [Re:floater]
floater Java Jedi 总版主发贴: 3233 积分: 421	于 2003-09-25 11:15 Sorry, I misunderstood 关. Yea, I didn't. You are right, it would be better to close them, otherwise just leave them to the gc. "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

作者	Re:JDBC revisit [Re:floater]
floater Java Jedi 总版主发贴: 3233 积分: 421	于 2003-09-28 04:49 Here is a better version: I tested this on MySQL and Sybase for SQL statements. No stored procedure testing yet. sqlrunner.rar (13.82k) "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

作者

Re:JDBC revisit [Re:floater]

floater

Java Jedi

总版主

发贴: 3233
积分: 421

于 2003-09-28 04:54

This is a simple but logic-complete document to this framework.

You need to have JDBC knowledge and general J2SE knowledge, nothing else.

Does anyone know how to add line numbers to the code in a word document?(only a portion of a page, not the whole page). Thanks if you like me know.

design.doc (87.5k)

"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

已读帖子

新的帖子

被删除的帖子