模仿spring的jdbcTemplate

 

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

 

JdbcBaseDAO

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

}

 

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

 

ResultHandler

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

}

 

转载于:https://www.cnblogs.com/xiziyin/archive/2010/01/10/1643737.html