自分で設計したJdbcTemplate
自分で書いたJdbcテンプレートは、Template、Strategy、Call Backなどを使用して、パラメータを自動的に設定でき、ResultSetからJTableおよびListへの自動マッピングが可能です.重複コードを大幅に減らし、クラシックを比較します.
PreparedStatementCallbackはコールバック用のインタフェースです
PreparedStatementCallbackはコールバック用のインタフェースです
public interface PreparedStatementCallback {
public Object doInPreparedStatement(PreparedStatement pstmt) throws SQLException,ClassNotFoundException;
}
public class JdbcTemplate {
private static final Log logger = LogFactory.getLog(JdbcTemplate.class);
/**
* execute template
* @param action
* @param sql
* @param arrayParams
* @param mapParams
* @return
* @throws PersistenceException
*/
private Object execute(PreparedStatementCallback action, String sql,
Object[] arrayParams,HashMap<Integer, Object> mapParams) throws PersistenceException{
Connection conn = null;
PreparedStatement pstmt = null;
Object result = null;
try {
conn = DataSourceFactory.getConnection();
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
// call back
result = action.doInPreparedStatement(pstmt);
logger.debug(pstmt.toString());
conn.commit();
} catch (ClassNotFoundException e) {
transactionRollback(conn);
throw new PersistenceException(e);
} catch (SQLException e) {
transactionRollback(conn);
throw new PersistenceException(e);
} finally {
this.closeStatement(pstmt);
this.closeConnection(conn);
}
return result;
}
/**
* hook method
* @param sql
* @param arrayParams
* @param mapParams
* @return
* @throws PersistenceException
*/
private Object execute(final String sql,final Object[] arrayParams,
final HashMap<Integer, Object> mapParams) throws PersistenceException{
PreparedStatementCallback callBack = new PreparedStatementCallback() {
@Override
public Object doInPreparedStatement(PreparedStatement pstmt)
throws SQLException, ClassNotFoundException {
if(arrayParams!=null)
setParameter(pstmt, arrayParams);
if(mapParams!=null)
setParameter(pstmt, mapParams);
int count = pstmt.executeUpdate();
return new Boolean(count > 0);
}
};
if(arrayParams!=null)
return execute(callBack,sql,arrayParams,null);
else if(mapParams!=null)
return execute(callBack,sql,null,mapParams);
else
return execute(callBack,sql,null,null);
}
/**
* hook method
* @param sql
* @param arrayParams
* @param mapParams
* @return
* @throws PersistenceException
*/
private Object executeQuery(final String sql,final Object[] arrayParams,final HashMap<Integer, Object> mapParams) throws PersistenceException {
PreparedStatementCallback callBack = new PreparedStatementCallback() {
@Override
public Object doInPreparedStatement(PreparedStatement pstmt)
throws SQLException, ClassNotFoundException {
if(arrayParams!=null)
setParameter(pstmt, arrayParams);
if(mapParams!=null)
setParameter(pstmt, mapParams);
ResultSet rs = pstmt.executeQuery();
return convertResultSetToList(rs);
}
};
if(arrayParams!=null)
return execute(callBack,sql,arrayParams,null);
else if(mapParams!=null)
return execute(callBack,sql,null,mapParams);
else
return execute(callBack,sql,null,null);
}
public Object fillDataToTable(final String sql,final Object[] arrayParams) throws PersistenceException {
PreparedStatementCallback callBack = new PreparedStatementCallback() {
@Override
public Object doInPreparedStatement(PreparedStatement pstmt)
throws SQLException, ClassNotFoundException {
if(arrayParams!=null)
setParameter(pstmt, arrayParams);
ResultSet rs = pstmt.executeQuery();
return convertResultSetToTable(rs);
}
};
return execute(callBack,sql,arrayParams,null);
}
public Object execute(String sql,Object[] arrayParams) throws PersistenceException{
return execute(sql,arrayParams,null);
}
public Object execute(String sql,HashMap<Integer, Object> mapParams) throws PersistenceException{
return execute(sql,null,mapParams);
}
public Object execute(String sql) throws PersistenceException{
return execute(sql,null,null);
}
public Object executeQuery(final String sql,
final Object[] arrayParams) throws PersistenceException {
return executeQuery(sql,arrayParams,null);
}
public Object executeQuery(final String sql,
final HashMap<Integer, Object> mapParams) throws PersistenceException {
return executeQuery(sql,null,mapParams);
}
public Object executeQuery(final String sql) throws PersistenceException {
return executeQuery(sql,null,null);
}
/**
* setParameter
* @param sql
* @param parameters HashMap<Integer, Object>
* @return
* @throws PersistenceException
*/
private PreparedStatement setParameter(PreparedStatement pstmt,
HashMap<Integer, Object> parameters) throws ClassNotFoundException,
SQLException {
if (parameters!=null) {
if (parameters.size()>=0) {
for (int i = 1; i <= parameters.size(); i++) {
try {
if (parameters.get(i).getClass() == Class
.forName("java.lang.String")) {
pstmt.setString(i, parameters.get(i).toString());
}
if (parameters.get(i).getClass() == Class
.forName("java.sql.Date")) {
pstmt.setDate(i, java.sql.Date.valueOf(parameters
.get(i).toString()));
}
if (parameters.get(i).getClass() == Class
.forName("java.lang.Boolean")) {
pstmt.setBoolean(i, (Boolean) (parameters.get(i)));
}
if (parameters.get(i).getClass() == Class
.forName("java.lang.Integer")) {
pstmt.setInt(i, (Integer) parameters.get(i));
}
if (parameters.get(i).getClass() == Class
.forName("java.lang.Float")) {
pstmt.setFloat(i, (Float) parameters.get(i));
}
if (parameters.get(i).getClass() == Class
.forName("java.lang.Double")) {
pstmt.setDouble(i, (Double) parameters.get(i));
}
} catch (ClassNotFoundException ex) {
throw ex;
} catch (SQLException ex) {
throw ex;
}
}
}
}
return pstmt;
}
/**
* setParameter
* @param sql
* @param parameters Object[]
* @return
* @throws PersistenceException
*/
private PreparedStatement setParameter(PreparedStatement pstmt,
Object[] parameters) throws ClassNotFoundException,
SQLException {
if (parameters!=null) {
if (parameters.length>=0) {
for (int i = 0; i < parameters.length; i++) {
try {
if (parameters[i].getClass() == Class
.forName("java.lang.String")) {
pstmt.setString(i+1, parameters[i].toString());
}
if (parameters[i].getClass() == Class
.forName("java.sql.Date")) {
pstmt.setDate(i+1, java.sql.Date.valueOf(parameters[i].toString()));
}
if (parameters[i].getClass() == Class
.forName("java.lang.Boolean")) {
pstmt.setBoolean(i+1, (Boolean) (parameters[i]));
}
if (parameters[i].getClass() == Class
.forName("java.lang.Integer")) {
pstmt.setInt(i+1, (Integer) parameters[i]);
}
if (parameters[i].getClass() == Class
.forName("java.lang.Float")) {
pstmt.setFloat(i+1, (Float) parameters[i]);
}
if (parameters[i].getClass() == Class
.forName("java.lang.Double")) {
pstmt.setDouble(i+1, (Double) parameters[i]);
}
} catch (ClassNotFoundException ex) {
throw ex;
} catch (SQLException ex) {
throw ex;
}
}
}
}
return pstmt;
}
private List<HashMap<Object,Object>> convertResultSetToList(
ResultSet rs) throws SQLException {
ResultSetMetaData rsmd = rs.getMetaData();
List<HashMap<Object, Object>> tempList = new ArrayList<HashMap<Object, Object>>();
HashMap<Object, Object> tempHash = null;
try{
while (rs.next()) {
tempHash = new HashMap<Object, Object>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
tempHash.put(rsmd.getColumnName(i + 1).toUpperCase(), rs
.getString(rsmd.getColumnName(i + 1)));
}
tempList.add(tempHash);
}
}catch(SQLException e){
throw e;
}
finally{
this.closeResultSet(rs);
}
return tempList;
}
private TableModel convertResultSetToTable(ResultSet rs) throws SQLException {
DefaultTableModel tableModel = null;
int columnCount = 0;
ResultSetMetaData rsmd = null;
try {
tableModel = new DefaultTableModel();
tableModel.setRowCount(0);
rsmd = rs.getMetaData();
columnCount = rsmd.getColumnCount();
String[] arr = new String[columnCount];
for(int i=0;i<columnCount;i++){
tableModel.addColumn(rsmd.getColumnName(i+1));
}
while (rs.next()) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
arr[i] = rs.getString(rsmd.getColumnName(i + 1));
}
tableModel.addRow(arr);
}
} catch (SQLException e) {
throw e;
} finally{
this.closeResultSet(rs);
}
return tableModel;
}
private void transactionRollback(Connection conn) {
if (conn != null) {
try {
conn.rollback();
} catch (SQLException e) {
logger.debug("Could not roll back transaction", e);
}
}
}
private void closeStatement(Statement stmt) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
logger.debug("Could not close JDBC Statement", e);
}
}
}
private void closeConnection(Connection conn){
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
logger.debug("Could not close JDBC Statement", e);
}
}
}
private void closeResultSet(ResultSet rs){
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
logger.debug("Could not close ResultSet", e);
}
}
}
}