private void appendLimitBuffer(StringBuffer sqlBuf) {
if (this.getPageDto() != null) {
if (!ClassTool.isNullObj(sqlBuf)) {
/*
sqlBuf = new StringBuffer();
sqlBuf.insert(0, " select * from (select rownum r, t.* from ( ");
sqlBuf.append(" ) t where rownum < ");
sqlBuf.append(Integer.parseInt(this.getPageDto().getStart()) + Integer.parseInt(this.getPageDto().getLimit()));
sqlBuf.append(" ) where r > =");
sqlBuf.append(this.getPageDto().getStart());
*/
String pageSql =
DBTool.getCurrPageSql(
sqlBuf.toString(),
Integer.parseInt(this.getPageDto().getStart()),
Integer.parseInt(this.getPageDto().getLimit()));
sqlBuf.delete(0, sqlBuf.length());
sqlBuf.append(pageSql);
}
/*
sqlBuf.append(" limit ");
sqlBuf.append(this.getPageDto().getStart());
sqlBuf.append(", ");
sqlBuf.append(this.getPageDto().getLimit());
*/
}
}
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 void appendOrderByBuffer(StringBuffer sqlBuf, Class objClass) throws Exception {
if (ClassTool.isNullObj(this.orderByBuf)) {
this.orderByBuf = new StringBuffer();
}
this.orderByBuf.trimToSize();
if (this.orderByBuf.length() > 0 || this.getPageDto() != null) {
if (this.orderByBuf.length() > 0) {
sqlBuf.append(" order by ");
sqlBuf.append(this.orderByBuf);
}
if (this.getPageDto() != null) {
DBTool.parseSortAndFilter(this.getPageDto());
if (!StringTool.isEmpty(this.getPageDto().getSort())) {
if (this.orderByBuf.length() == 0) {
sqlBuf.append(" order by ");
} else if (this.orderByBuf.length() > 0) {
sqlBuf.append(", ");
}
String sort =
ClassTool.getDBColumnNameByObjFieldName(objClass, this.getPageDto().getSort());
if (StringTool.isEmpty(sort)) {
sort = StringTool.translateToDBColumn(this.getPageDto().getSort());
}
sqlBuf.append(sort);
sqlBuf.append(" ");
sqlBuf.append(this.getPageDto().getDir());
}
}
}
}
private void appendWhereBuffer(StringBuffer sqlBuf) throws Exception {
if (ClassTool.isNullObj(this.whereBuf)) {
this.whereBuf = new StringBuffer();
}
this.whereBuf.trimToSize();
if (this.whereBuf.length() > 0 || this.getPageDto() != null) {
if (this.getPageDto() != null) {
DBTool.parseSortAndFilter(this.getPageDto());
if (!StringTool.isEmpty(this.getPageDto().getFilter())) {
this.getPageDto()
.setFilterOperator(
StringTool.isEmpty(
this.getPageDto().getFilterOperator(), GeneralConstants.EQUAL_MARK));
if (this.whereBuf.length() > 0) {
whereBuf.append(" and ");
}
whereBuf.append(this.getPageDto().getFilter());
whereBuf.append(" ");
if ("like".equalsIgnoreCase(this.getPageDto().getFilterOperator())) {
if (StringTool.isEmpty(this.getPageDto().getFilterValue().trim())) {
this.getPageDto().setFilterOperator(GeneralConstants.EQUAL_MARK);
}
}
whereBuf.append(this.getPageDto().getFilterOperator());
if ("like".equalsIgnoreCase(this.getPageDto().getFilterOperator())
|| "not like".equalsIgnoreCase(this.getPageDto().getFilterOperator())) {
whereBuf.append(" '%");
whereBuf.append(this.getPageDto().getFilterValue().trim());
whereBuf.append("%' and ");
whereBuf.append(this.getPageDto().getFilter());
whereBuf.append(" != ' ' ");
} else {
whereBuf.append(" '");
whereBuf.append(this.getPageDto().getFilterValue().trim());
whereBuf.append("'");
if (StringTool.isEmpty(this.getPageDto().getFilterValue().trim())) {
if (GeneralConstants.EQUAL_MARK.equals(this.getPageDto().getFilterOperator())) {
whereBuf.append(" or ");
whereBuf.append(this.getPageDto().getFilter());
whereBuf.append(" IS NULL ");
} else if (GeneralConstants.NOT_EQUAL_MARK.equals(
this.getPageDto().getFilterOperator())) {
whereBuf.append(" and ");
whereBuf.append(this.getPageDto().getFilter());
whereBuf.append(" IS NOT NULL ");
}
}
}
}
}
if (this.whereBuf.length() > 0) {
sqlBuf.append(" where ");
sqlBuf.append(this.whereBuf);
}
}
}
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;
}
}
}
protected List<Object> selectDtoList(Class objClass, DataSource ds) throws Exception {
return this.selectDtoList(objClass, DBTool.getSelectSqlFromObjectClass(objClass), ds);
}
protected List<Object> selectDtoList(Class objClass) throws Exception {
return this.selectDtoList(
objClass,
DBTool.getSelectSqlFromObjectClass(objClass),
DBManager.getInstance().getDataSource());
}
protected void deleteDto(Class objClass) throws Exception {
this.updateOrDeleteDto(DBTool.getDeleteSqlFromObjectClass(objClass));
}
protected void updateDto(Object dto) throws Exception {
this.updateOrDeleteDto(DBTool.getUpdateSqlFromObjectClass(dto));
}