模仿spring的jdbcTemplate

看着spring的jdbc模板，以前写过一个，贴上，回忆下过去~

package org.xiziyin.shop.dal.dao.jdbcimpl;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.log4j.Logger;

public class JdbcBaseDAO {
    private static Logger log = Logger.getLogger(JdbcBaseDAO.class);
    private static Connection conn;
    private static PreparedStatement stat;
    private static ResultSet rs;
    private static Properties prop;
    
    /**
     * test interface
     */
    private static String propPath;

    /**
     * create a active connection
     * 
     * @author <a href="mailto:czy88840616@163.com">czy</a>
     * @since 2009-11-29 上午12:47:25
     *
     * @return
     * @throws SQLException 
     */
    public static boolean createConnection() throws SQLException{
        try {
            if (prop == null) {
                if (!loadProperties(propPath)) {
                    return false;
                }
            }
            String username = prop.getProperty(JdbcConstants.SHOP_DATABASE_USERNAME);
            String password = prop.getProperty(JdbcConstants.SHOP_DATABASE_PASSWORD);
            String url = prop.getProperty(JdbcConstants.SHOP_URL);
            String driver = prop.getProperty(JdbcConstants.SHOP_DRIVER);
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);

            return true;
        } catch (ClassNotFoundException e) {
            log.error("Class.forName error", e);
        }

        return false;
    }
    
    /**
     * load mysql properties from jdbc.properties file
     * 
     * @author <a href="mailto:czy88840616@163.com">czy</a>
     * @since 2009-11-29 上午12:47:43
     *
     * @param path
     * @return
     */
    private static boolean loadProperties(String path) {
        prop = new Properties();
        InputStream in = null;
        if(path == null) {
            path = JdbcConstants.SHOP_JDBC_PROPERTIES_PATH;
        }
        
        log.info("current root：" + System.getProperty("user.dir"));
        
        in = JdbcBaseDAO.class.getResourceAsStream(path);
        
        try {
            if (in != null) {
                prop.load(in);
                log.info("find properties：classpath" + path);
                in.close();
                return true;
            }
        } catch (IOException e) {
            log.error(path + " is IO Exception", e);
        }
        
        return false;
    }


    /**
     * execute DML sql
     * 
     * @author <a href="mailto:czy88840616@163.com">czy</a>
     * @since 2009-11-29 下午03:27:13
     *
     * @param sql
     * @param param
     * @param returnGeneratedKey
     * @param rh
     * @return
     * @throws SQLException
     */
    protected static boolean executeCommand(String sql, Object[] param, boolean returnGeneratedKey, ResultHandler rh)
            throws SQLException {
        boolean activeResult = createConnection();
        if (activeResult) {
            if(returnGeneratedKey) {
                stat = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            } else {
                stat = conn.prepareStatement(sql);
            }
            
            setParam(param);
            int executeResult = stat.executeUpdate();
            if(returnGeneratedKey) {
                rs = stat.getGeneratedKeys();
                rh.operateResult(rs);
            }
            closeActiveConn();
            return executeResult > 0;
        } else {
            log.error("connection is not active");
        }

        return false;
    }

    /**
     * execute query(select) sql
     * 
     * @author <a href="mailto:czy88840616@163.com">czy</a>
     * @since 2009-11-29 上午12:48:26
     *
     * @param sql
     * @param param
     * @param rh
     * @return
     * @throws SQLException
     */
    protected static boolean queryCommand(String sql, Object[] param, ResultHandler rh) throws SQLException {
        boolean activeResult = createConnection();
        if (activeResult) {
            stat = conn.prepareStatement(sql);
            setParam(param);
            rs = stat.executeQuery();
            rh.operateResult(rs);
            closeActiveConn();
            return true;
        } else {
            log.error("connection is not active");
        }
        return false;
    }

    /**
     * set parameters to prepareStatment
     * 
     * @author <a href="mailto:czy88840616@163.com">czy</a>
     * @since 2009-11-29 上午12:48:40
     *
     * @param param
     * @throws SQLException
     */
    private static void setParam(Object[] param) throws SQLException {
        for (int i = 0; i < param.length; i++) {
            stat.setObject(i + 1, param[i]);
        }

    }
    
    /**
     * 关闭连接
     * 
     * @author <a href="mailto:czy88840616@163.com">czy</a>
     * @since 2009-11-29 上午12:58:52
     *
     */
    private static void closeActiveConn() {
        try {
            if (rs != null)
                rs.close();
            if (stat != null)
                stat.close();
            if (conn != null)
                conn.close();        
        } catch (SQLException e) {
            log.error("close connection error", e);
        }        
    }

    public static void setPropPath(String propPath) {
        JdbcBaseDAO.propPath = propPath;
    }

}

这里用了属性文件来配置数据库字符串。

package org.xiziyin.shop.dal.dao.jdbcimpl;

import java.sql.ResultSet;
import java.sql.SQLException;

public interface ResultHandler {
    void operateResult(ResultSet rs) throws SQLException;
}

用来接收resultSet

package org.xiziyin.shop.dal.dao.jdbcimpl;

import java.sql.SQLException;

import org.testng.Assert;
import org.testng.annotations.Test;
import org.xiziyin.shop.BaseTest;


public class ConnectionTest extends BaseTest{

    @Test
    public void testConnection() throws SQLException {
        //JdbcBaseDAO.setPropPath("src/test/resources/jdbc.properties");
        boolean result = JdbcBaseDAO.createConnection();
        Assert.assertEquals(result, true);
    }

}