protected static final List<String[]> getRows(DbSession session, DbSelect select)
throws WaarpDatabaseSqlException {
String action = select.toString();
DbRequest request = null;
try {
request = new DbRequest(session);
request.select(action, TIMEOUT_IN_SECOND);
List<String[]> list = new ArrayList<String[]>();
while (request.getNext()) {
ResultSet resultSet = request.getResultSet();
String[] result = new String[select.selected.size()];
for (int i = 1; i <= result.length; i++) {
result[i - 1] = resultSet.getString(i);
}
list.add(result);
}
return list;
} catch (WaarpDatabaseNoConnectionException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return null;
} catch (WaarpDatabaseSqlException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
throw e;
} catch (SQLException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return null;
} finally {
if (request != null) {
request.close();
}
}
}
protected static final List<String[]> getRows(
DbSession session, DbTable table, int start, int limit) {
String action = "SELECT * FROM " + table.name;
action = DbModelFactory.dbVitamModel.getLimitOffset(action, start, limit);
DbRequest request = null;
try {
request = new DbRequest(session);
request.select(action);
List<String[]> list = new ArrayList<String[]>();
while (request.getNext()) {
ResultSet resultSet = request.getResultSet();
String[] result = new String[table.nbFields()];
for (int i = 1; i <= result.length; i++) {
result[i - 1] = resultSet.getString(i);
}
list.add(result);
}
return list;
} catch (WaarpDatabaseNoConnectionException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return null;
} catch (WaarpDatabaseSqlException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return null;
} catch (SQLException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return null;
} finally {
if (request != null) {
request.close();
}
}
}
/**
* @param session
* @param table
* @return the number of rows within the table
*/
protected static final int getRowCount(DbSession session, DbTable table) {
String action = "SELECT COUNT(*) FROM " + table.name;
DbRequest request = null;
try {
request = new DbRequest(session);
request.select(action);
if (request.getNext()) {
ResultSet resultSet = request.getResultSet();
return resultSet.getInt(1);
}
return -1;
} catch (WaarpDatabaseNoConnectionException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return -1;
} catch (WaarpDatabaseSqlException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return -1;
} catch (SQLException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return -1;
} finally {
if (request != null) {
request.close();
}
}
}
/**
* @param session
* @param select
* @return the number of rows within the select
* @throws WaarpDatabaseSqlException
*/
protected static final int getRowCount(DbSession session, String select)
throws WaarpDatabaseSqlException {
if (select == null) {
return -1;
}
if (select.trim().length() == 0) {
return -1;
}
if (!select.toLowerCase().trim().startsWith("select")) {
return -1;
}
String action =
"SELECT COUNT(*) FROM ("
+ DbModelFactory.dbVitamModel.getLimitOffset(select, 0, LIMITCOUNTROW)
+ ")";
DbRequest request = null;
try {
request = new DbRequest(session);
request.select(action, TIMEOUT_IN_SECOND);
if (request.getNext()) {
ResultSet resultSet = request.getResultSet();
int result = resultSet.getInt(1);
if (result != LIMITCOUNTROW) {
return result;
}
}
return -1;
} catch (WaarpDatabaseNoConnectionException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return -1;
} catch (WaarpDatabaseSqlException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
throw e;
} catch (SQLException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return -1;
} finally {
if (request != null) {
request.close();
}
}
}
/**
* @param session
* @param select
* @return the number of rows within the select
* @throws WaarpDatabaseSqlException
*/
protected static final int getRowCount(DbSession session, DbSelect select)
throws WaarpDatabaseSqlException {
int start = select.offset;
int limit = select.limit;
select.offset = 0;
select.limit = LIMITCOUNTROW;
String action =
"SELECT COUNT(*) FROM ("
+ DbModelFactory.dbVitamModel.getLimitOffset(
select.toStringNoLimitNoOrder(), 0, LIMITCOUNTROW)
+ ")";
select.offset = start;
select.limit = limit;
DbRequest request = null;
try {
request = new DbRequest(session);
request.select(action, TIMEOUT_IN_SECOND);
if (request.getNext()) {
ResultSet resultSet = request.getResultSet();
int result = resultSet.getInt(1);
if (result != LIMITCOUNTROW) {
return result;
}
}
return -1;
} catch (WaarpDatabaseNoConnectionException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return -1;
} catch (WaarpDatabaseSqlException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
throw e;
} catch (SQLException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return -1;
} finally {
if (request != null) {
request.close();
}
}
}
/**
* @param session
* @param select
* @return a List of Array of Strings, the first rank being the field names, then the values. In
* case of Insert/Update/Delete, will be "QueryCount" then the count of lines touched.
* @throws WaarpDatabaseSqlException
*/
protected static final List<String[]> getRows(DbSession session, String select)
throws WaarpDatabaseSqlException {
String action = select;
if (select == null) {
return null;
}
boolean isSelect = select.toLowerCase().trim().startsWith("select");
if (action.trim().length() == 0) {
return null;
}
DbRequest request = null;
try {
request = new DbRequest(session);
if (isSelect) {
request.select(action, TIMEOUT_IN_SECOND);
List<String[]> list = new ArrayList<String[]>();
ResultSet global = request.getResultSet();
// Get result set meta data
ResultSetMetaData metadata = global.getMetaData();
int numColumns = metadata.getColumnCount();
String[] header = new String[numColumns];
// Get the column names; column indices start from 1
for (int i = 1; i < numColumns + 1; i++) {
String columnName = metadata.getColumnName(i);
// Get the name of the column's table name
String tableName = metadata.getTableName(i);
header[i - 1] = tableName + "." + columnName;
}
list.add(header);
while (request.getNext()) {
ResultSet resultSet = request.getResultSet();
String[] result = new String[numColumns];
for (int i = 1; i <= result.length; i++) {
result[i - 1] = resultSet.getString(i);
}
list.add(result);
}
return list;
} else {
// update, insert, delete
int count = request.query(action);
List<String[]> list = new ArrayList<String[]>();
String[] header = new String[] {"QueryCount"};
String[] value = new String[] {"" + count};
list.add(header);
list.add(value);
return list;
}
} catch (WaarpDatabaseNoConnectionException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return null;
} catch (WaarpDatabaseSqlException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
throw e;
} catch (SQLException e) {
logger.warn(StaticValues.LBL.error_error.get() + e);
return null;
} finally {
if (request != null) {
request.close();
}
}
}