模仿spring的jdbcTemplate
看着spring的jdbc模板,以前写过一个,贴上,回忆下过去~
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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;
}
}
这里用了属性文件来配置数据库字符串。
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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);
}
}