private void insertDto(Object dto, String idFieldName) throws Exception {
Connection conn = null;
Exception ex = null;
String sql = "";
try {
conn = DBManager.getInstance().getDataSource().getConnection();
/*
Statement stmt = conn.createStatement();
sql = DBTool.getInsertSqlFromObjectClass(dto);
stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
*/
sql = DBTool.getInsertSqlFromObjectClass(dto);
PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql, new String[] {"ID"});
pstmt.executeUpdate();
ResultSet rs = pstmt.getGeneratedKeys();
if (rs != null) {
if (rs.next()) {
int id = rs.getInt(1);
ClassTool.invokeSetMethod(dto, idFieldName, String.valueOf(id));
rs.close();
}
}
} catch (Exception e) {
LogTool.logError(e, this.getClass().getName());
LogTool.logText(sql, this.getClass().getName());
ex = e;
} finally {
DBManager.getInstance().closeDBConn(conn);
if (ex != null) throw ex;
}
}
private long selectDtoListCount(String originalSql, Connection conn) throws Exception {
long ret = 0;
Exception ex = null;
StringBuffer sqlBuf = new StringBuffer();
try {
sqlBuf.append(originalSql);
String whereClause = this.whereBuf.toString().trim();
if (whereClause.length() > 0) {
sqlBuf.append(" where ");
sqlBuf.append(whereClause);
}
String sql = sqlBuf.toString();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs != null) {
if (rs.next()) {
ret = rs.getLong(1);
}
}
} catch (Exception e) {
LogTool.logError(e, this.getClass().getName());
LogTool.logText(sqlBuf.toString(), this.getClass().getName());
ex = e;
} finally {
if (ex != null) throw ex;
return ret;
}
}
/*
* execute select sql and return hashtable, key==column name, value==column value
*/
protected List<Map<String, String>> executeSelectSql(String sql, DataSource ds) throws Exception {
List<Map<String, String>> ret = new ArrayList<Map<String, String>>();
Connection conn = null;
Exception ex = null;
StringBuffer sqlBuf = new StringBuffer();
try {
conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs != null) {
ResultSetMetaData rsMd = rs.getMetaData();
int columnCount = rsMd.getColumnCount();
while (rs.next()) {
Map<String, String> rowDataMap = new Hashtable<String, String>();
for (int i = 1; i < columnCount + 1; i++) {
String columnName = rsMd.getColumnName(i);
String columnTypeName = rsMd.getColumnTypeName(i);
String columnValue = "";
if (columnTypeName.toLowerCase().indexOf("int") != -1) {
columnValue = String.valueOf(rs.getInt(columnName));
} else if (columnTypeName.toLowerCase().indexOf("varchar") != -1
|| columnTypeName.toLowerCase().indexOf("text") != -1) {
columnValue = String.valueOf(rs.getString(columnName));
} else if (columnTypeName.toLowerCase().indexOf("float") != -1) {
columnValue = String.valueOf(rs.getFloat(columnName));
} else if (columnTypeName.toLowerCase().indexOf("double") != -1) {
columnValue = String.valueOf(rs.getDouble(columnName));
} else if (columnTypeName.toLowerCase().indexOf("date") != -1) {
columnValue = String.valueOf(rs.getDate(columnName));
} else if (columnTypeName.toLowerCase().indexOf("boolean") != -1) {
columnValue = String.valueOf(rs.getBoolean(columnName));
} else if (columnTypeName.toLowerCase().indexOf("decimal") != -1) {
columnValue = String.valueOf(rs.getBigDecimal(columnName));
}
rowDataMap.put(columnName, columnValue);
ret.add(rowDataMap);
}
}
}
} catch (Exception e) {
LogTool.logError(e, this.getClass().getName());
LogTool.logText(sqlBuf.toString(), this.getClass().getName());
ex = e;
} finally {
DBManager.getInstance().closeDBConn(conn);
if (ex != null) {
throw ex;
}
return ret;
}
}
/*
* execute update sql
*/
public void executeUpdateOrDeleteSql(String sql, DataSource ds) throws Exception {
Connection conn = null;
Exception ex = null;
try {
conn = ds.getConnection();
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
} catch (Exception e) {
LogTool.logError(e, this.getClass().getName());
LogTool.logText(sql, this.getClass().getName());
ex = e;
} finally {
DBManager.getInstance().closeDBConn(conn);
if (ex != null) throw ex;
}
}
protected int selectDtoCount() throws Exception {
int ret = 0;
this.appendWhereBuffer(this.sqlBuf);
Connection conn = null;
Exception ex = null;
try {
conn = DBManager.getInstance().getDataSource().getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(this.sqlBuf.toString());
if (rs.next()) {
ret = rs.getInt(1);
}
} catch (Exception e) {
LogTool.logError(e, this.getClass().getName());
LogTool.logText(this.sqlBuf.toString(), this.getClass().getName());
ex = e;
} finally {
DBManager.getInstance().closeDBConn(conn);
if (ex != null) throw ex;
return ret;
}
}
private List<Object> selectDtoList(Class objClass, String originalSql, DataSource ds)
throws Exception {
List<Object> ret = new ArrayList<Object>();
Connection conn = null;
Exception ex = null;
StringBuffer sqlBuf = new StringBuffer();
try {
sqlBuf.append(originalSql);
this.appendWhereBuffer(sqlBuf);
this.appendOrderByBuffer(sqlBuf, objClass);
this.appendLimitBuffer(sqlBuf);
String sql = sqlBuf.toString();
LogTool.debugText("sql = " + sql, this.getClass().getName());
conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs != null) {
while (rs.next()) {
if (ClassTool.isNullObj(objClass) || "list".equalsIgnoreCase(objClass.getName())) {
ret.add(DBTool.getResultSetColumnInList(rs));
} else {
ret.add(objClass.cast(ClassTool.extractValueFromResultSet(objClass, rs)));
}
}
}
} catch (Exception e) {
LogTool.logError(e, this.getClass().getName());
LogTool.logText(sqlBuf.toString(), this.getClass().getName());
ex = e;
} finally {
try {
if (ex != null) {
throw ex;
} else {
if (!ClassTool.isListEmpty(ret)) {
if (ret.get(0) != null) {
String selectCountSql = "";
if (!StringTool.isEmpty(originalSql)) {
selectCountSql = DBTool.getSelectCountSqlFromSelectSql(originalSql);
} else {
selectCountSql = DBTool.getSelectCountSqlFromObjectClass(objClass);
}
if (!StringTool.isEmpty(selectCountSql)) {
long totalRecordsCountInThisSearch = this.selectDtoListCount(selectCountSql, conn);
if (ClassTool.isNullObj(objClass) || "list".equalsIgnoreCase(objClass.getName())) {
if (ret.get(0) instanceof List) {
List tmpList = (List) ret.get(0);
if (!ClassTool.isListEmpty(tmpList)) {
BasePageDTO.class
.cast(tmpList.get(0))
.setTotalRecordsCountInThisSearch(totalRecordsCountInThisSearch);
}
}
} else {
BasePageDTO.class
.cast(ret.get(0))
.setTotalRecordsCountInThisSearch(totalRecordsCountInThisSearch);
}
}
}
}
}
} catch (Exception e) {
LogTool.logError(e, this.getClass().getName());
ex = e;
} finally {
DBManager.getInstance().closeDBConn(conn);
if (ex != null) {
throw ex;
}
return ret;
}
}
}