/**
* Method to apply any restrictions to the created ResultSet.
*
* @param ps The PreparedStatement
* @param query The query
* @param applyTimeout Whether to apply the query timeout (if any) direct to the PreparedStatement
* @throws SQLException Thrown when an error occurs applying the constraints
*/
public static void prepareStatementForExecution(
PreparedStatement ps, Query query, boolean applyTimeout) throws SQLException {
NucleusContext nucleusCtx = query.getExecutionContext().getNucleusContext();
RDBMSStoreManager storeMgr = (RDBMSStoreManager) query.getStoreManager();
PersistenceConfiguration conf = nucleusCtx.getPersistenceConfiguration();
if (applyTimeout) {
Integer timeout = query.getDatastoreReadTimeoutMillis();
if (timeout != null && timeout > 0) {
ps.setQueryTimeout(timeout / 1000);
}
}
// Apply any fetch size
int fetchSize = 0;
if (query.getFetchPlan().getFetchSize() > 0) {
// FetchPlan has a size set so use that
fetchSize = query.getFetchPlan().getFetchSize();
}
if (storeMgr.getDatastoreAdapter().supportsQueryFetchSize(fetchSize)) {
ps.setFetchSize(fetchSize);
}
// Apply any fetch direction
String fetchDir =
conf.getStringProperty(RDBMSPropertyNames.PROPERTY_RDBMS_QUERY_FETCH_DIRECTION);
Object fetchDirExt =
query.getExtension(RDBMSPropertyNames.PROPERTY_RDBMS_QUERY_FETCH_DIRECTION);
if (fetchDirExt != null) {
fetchDir = (String) fetchDirExt;
if (!fetchDir.equals("forward")
&& !fetchDir.equals("reverse")
&& !fetchDir.equals("unknown")) {
throw new NucleusUserException(LOCALISER.msg("052512"));
}
}
if (fetchDir.equals("reverse")) {
ps.setFetchDirection(ResultSet.FETCH_REVERSE);
} else if (fetchDir.equals("unknown")) {
ps.setFetchDirection(ResultSet.FETCH_UNKNOWN);
}
// Add a limit on the number of rows to include the maximum we may need
long toExclNo = query.getRangeToExcl();
if (toExclNo != 0 && toExclNo != Long.MAX_VALUE) {
if (toExclNo > Integer.MAX_VALUE) {
// setMaxRows takes an int as input so limit to the correct range
ps.setMaxRows(Integer.MAX_VALUE);
} else {
ps.setMaxRows((int) toExclNo);
}
}
}
/**
* Load the cache entry for the given URI from the database.
*
* @param uri the URI of the cached resource for which to return the cache entry
* @return an instance of KiWiCacheEntry representing the caching metadata for the given resource,
* or null in case there is no entry for this resource
* @throws SQLException
*/
public KiWiCacheEntry getCacheEntry(String uri) throws SQLException {
KiWiCacheEntry cached = entryResourceCache.get(uri);
// lookup element in cache first, so we can avoid reconstructing it if it is already there
if (cached != null) {
return cached;
}
PreparedStatement query = connection.getPreparedStatement("load.entry_by_uri");
query.setString(1, uri);
query.setMaxRows(1);
// run the database query and if it yields a result, construct a new node; the method call will
// take care of
// caching the constructed node for future calls
ResultSet result = query.executeQuery();
try {
if (result.next()) {
return constructCacheEntry(result);
} else {
return null;
}
} finally {
result.close();
}
}
public void addListener(DistributionRequest listener) {
synchronized (listeners) {
if (!listeners.contains(listener)) {
logger.warn("Adding a listener to Distributer:" + listener.toString());
boolean needsAnd =
SQLValidator.removeSingleQuotes(SQLValidator.removeQuotes(listener.getQuery()))
.indexOf(" where ")
> 0;
String query = SQLValidator.addPkField(listener.getQuery());
if (needsAnd) query += " AND ";
else query += " WHERE ";
query += " timed > " + listener.getStartTime() + " and pk > ? order by timed asc ";
PreparedStatement prepareStatement = null;
try {
prepareStatement =
getPersistantConnection(listener.getVSensorConfig())
.prepareStatement(
query); // prepareStatement =
// StorageManager.getInstance().getConnection().prepareStatement(query);
prepareStatement.setMaxRows(1000); // Limit the number of rows loaded in memory.
} catch (Exception e) {
throw new RuntimeException(e);
}
preparedStatements.put(listener, prepareStatement);
listeners.add(listener);
addListenerToCandidates(listener);
} else {
logger.warn(
"Adding a listener to Distributer failed, duplicated listener! " + listener.toString());
}
}
}
public void setMaxRows(int max) throws SQLException {
Profiler profiler = _profilerPoint.start();
try {
_preparedStatement.setMaxRows(max);
} finally {
profiler.finish();
}
}
@Test
public void setmaxrows() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select 1 union select 2;");
prep.setMaxRows(1);
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertFalse(rs.next());
prep.close();
}
private void testMaxRowsChange(Connection conn) throws SQLException {
PreparedStatement prep = conn.prepareStatement("SELECT * FROM SYSTEM_RANGE(1, 100)");
ResultSet rs;
for (int j = 1; j < 20; j++) {
prep.setMaxRows(j);
rs = prep.executeQuery();
for (int i = 0; i < j; i++) {
assertTrue(rs.next());
}
assertFalse(rs.next());
}
}
public Authentificator(
String DBMSHost,
int DBMSPort,
String DBMSUser,
String DBMSPassword,
String DBName,
String tableName)
throws SQLException {
String connectionURL = String.format(CONNECTION_URL_TEMPLATE, DBMSHost, DBMSPort, DBName);
DBConnection = DriverManager.getConnection(connectionURL, DBMSUser, DBMSPassword);
userGetStatement = DBConnection.prepareStatement(String.format(USER_GET_SQL, tableName));
userGetStatement.setMaxRows(1);
}
private boolean isValid(final Connection connection) {
if (connection == null) {
return false;
}
if (status != PwmService.STATUS.OPEN) {
return false;
}
try {
final Method getFreeSpaceMethod = File.class.getMethod("isValid");
final Object rawResult = getFreeSpaceMethod.invoke(connection, 10);
return (Boolean) rawResult;
} catch (NoSuchMethodException e) {
/* no error, pre java 1.6 doesn't have this method */
} catch (Exception e) {
LOGGER.debug(
"error checking for isValid for " + connection.toString() + ",: " + e.getMessage());
}
final StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM ")
.append(DatabaseTable.PWM_META.toString())
.append(" WHERE " + KEY_COLUMN + " = ?");
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
statement = connection.prepareStatement(sb.toString());
statement.setString(1, KEY_ENGINE_START_PREFIX + instanceID);
statement.setMaxRows(1);
resultSet = statement.executeQuery();
if (resultSet.next()) {
resultSet.getString(VALUE_COLUMN);
}
} catch (SQLException e) {
final ErrorInformation errorInformation =
new ErrorInformation(
PwmError.ERROR_DB_UNAVAILABLE, "isValid operation failed: " + e.getMessage());
lastError = errorInformation;
LOGGER.error(errorInformation.toDebugStr());
return false;
} finally {
close(statement);
close(resultSet);
}
return true;
}
public CourseInstance getCourseInstance(int instanceId) {
CourseInstance courseInstance = null;
PreparedStatement selectStatement;
try {
String query =
"SELECT * FROM COURSE, COURSEINSTANCE WHERE INSTANCEID = ? AND COURSE.COURSEID = COURSEINSTANCE.COURSEID";
selectStatement = ConnectionUtil.getOracleConnection().prepareStatement(query);
selectStatement.setMaxRows(1);
selectStatement.setInt(1, instanceId);
ResultSet rs = selectStatement.executeQuery();
List<CourseInstance> instances = resultSetToList(rs);
if (instances.size() > 0) courseInstance = instances.stream().findFirst().get();
} catch (SQLException e) {
e.printStackTrace();
}
return courseInstance;
}
public List<Agent> queryAllAgents(Page page) {
List<Agent> agent_list = null;
try {
conn = JDBC_Conn.getConnection();
String sql = "select * from agents natural join position";
stmt =
conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt.setMaxRows(page.getEndIndex());
rs = stmt.executeQuery();
if (page.getBeginIndex() > 0) {
rs.absolute(page.getBeginIndex());
}
while (rs.next()) {
if (agent_list == null) agent_list = new ArrayList<Agent>();
Agent agent = new Agent();
agent.setAgent_id(rs.getString("agent_id"));
agent.setPhone_number(rs.getString("phone_number"));
agent.setEmail(rs.getString("email"));
agent.setFirst_nm(CommUtils.initCap(rs.getString("first_nm")));
agent.setLast_nm(CommUtils.initCap(rs.getString("last_nm")));
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date dob = sdf.parse(rs.getString("dob"));
agent.setDob(dob);
agent.setZip(rs.getString("zip"));
agent.setGender(rs.getString("gender"));
agent.setPosition(rs.getString("position"));
agent_list.add(agent);
}
} catch (Exception e) {
e.printStackTrace();
throw new DAOException();
} finally {
JDBC_Conn.releaseConnection(conn, stmt, rs);
}
return agent_list;
}
@Override
public String get(final DatabaseTable table, final String key) throws DatabaseException {
if (traceLogging) {
LOGGER.trace("attempting get operation for table=" + table + ", key=" + key);
}
preOperationCheck();
final StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM ").append(table.toString()).append(" WHERE " + KEY_COLUMN + " = ?");
PreparedStatement statement = null;
ResultSet resultSet = null;
String returnValue = null;
try {
statement = connection.prepareStatement(sb.toString());
statement.setString(1, key);
statement.setMaxRows(1);
resultSet = statement.executeQuery();
if (resultSet.next()) {
returnValue = resultSet.getString(VALUE_COLUMN);
}
} catch (SQLException e) {
final ErrorInformation errorInformation =
new ErrorInformation(
PwmError.ERROR_DB_UNAVAILABLE, "get operation failed: " + e.getMessage());
lastError = errorInformation;
throw new DatabaseException(errorInformation);
} finally {
close(statement);
close(resultSet);
}
if (traceLogging) {
final LinkedHashMap<String, Object> debugOutput = new LinkedHashMap<>();
debugOutput.put("table", table);
debugOutput.put("key", key);
debugOutput.put("result", returnValue);
LOGGER.trace(
"get operation result: " + JsonUtil.serializeMap(debugOutput, JsonUtil.Flag.PrettyPrint));
}
updateStats(true, false);
return returnValue;
}
/** Returns all rows from the producto table that match the specified arbitrary SQL statement */
public Producto[] findByDynamicSelect(String sql, Object[] sqlParams)
throws ProductoDaoException {
// declare variables
final boolean isConnSupplied = (userConn != null);
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// get the user-specified connection or get a connection from the ResourceManager
conn = isConnSupplied ? userConn : ResourceManager.getConnection();
// construct the SQL statement
final String SQL = sql;
if (logger.isDebugEnabled()) {
logger.debug("Executing " + SQL);
}
// prepare statement
stmt = conn.prepareStatement(SQL);
stmt.setMaxRows(maxRows);
// bind parameters
for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
stmt.setObject(i + 1, sqlParams[i]);
}
rs = stmt.executeQuery();
// fetch the results
return fetchMultiResults(rs);
} catch (Exception _e) {
logger.error("Exception: " + _e.getMessage(), _e);
throw new ProductoDaoException("Exception: " + _e.getMessage(), _e);
} finally {
ResourceManager.close(rs);
ResourceManager.close(stmt);
if (!isConnSupplied) {
ResourceManager.close(conn);
}
}
}
public Course getCourseByCode(String code) {
Course course = null;
PreparedStatement selectStatement;
try {
String query = "SELECT * FROM COURSE WHERE COURSECODE = ?";
selectStatement = ConnectionUtil.getOracleConnection().prepareStatement(query);
selectStatement.setMaxRows(1);
selectStatement.setString(1, code);
ResultSet rs = selectStatement.executeQuery();
while (rs.next())
course =
CourseBuilder.createCourseBuilder()
.setId(rs.getInt("COURSEID"))
.setCode(rs.getString("COURSECODE"))
.setTitle(rs.getString("TITLE"))
.buildCourse();
} catch (SQLException e) {
e.printStackTrace();
}
return course;
}
/**
* Returns all rows from the carrito_compras table that match the specified arbitrary SQL
* statement
*/
public CarritoCompras[] findByDynamicWhere(String sql, Object[] sqlParams)
throws CarritoComprasDaoException {
// declare variables
final boolean isConnSupplied = (userConn != null);
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// get the user-specified connection or get a connection from the ResourceManager
conn = isConnSupplied ? userConn : ResourceManager.getConnection();
// construct the SQL statement
final String SQL = SQL_SELECT + " WHERE " + sql;
System.out.println("Executing " + SQL);
// prepare statement
stmt = conn.prepareStatement(SQL);
stmt.setMaxRows(maxRows);
// bind parameters
for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
stmt.setObject(i + 1, sqlParams[i]);
}
rs = stmt.executeQuery();
// fetch the results
return fetchMultiResults(rs);
} catch (Exception _e) {
_e.printStackTrace();
throw new CarritoComprasDaoException("Exception: " + _e.getMessage(), _e);
} finally {
ResourceManager.close(rs);
ResourceManager.close(stmt);
if (!isConnSupplied) {
ResourceManager.close(conn);
}
}
}
/**
* 初始化真正的PreparedStatement，对当前对象的操作全部都设置到真正的PreparedStatement
*
* @throws SQLException
*/
private void prepare() throws SQLException {
DALFactory dalFactory = DALFactory.getDefault();
List<Object> values = dalParameters.getValues();
Map<String, Object> context = new HashMap<String, Object>();
SQLStruct sqlStruct = dalFactory.getSqlAnalyzer().parse(sql, context);
SQLInfo sqlInfo = null;
if (sqlStruct.isCanParse()) {
sqlInfo =
dalFactory
.getSqlAnalyzer()
.analyse(sql, sqlStruct, values.toArray(new Object[values.size()]), context);
}
this.parsePartition(sqlStruct, sqlInfo);
this.initRealPreparedStatement();
if (this.maxFieldSize != 0) {
ps.setMaxFieldSize(maxFieldSize);
}
if (this.maxRows != 0) {
ps.setMaxRows(maxRows);
}
if (!this.escapeProcessing) {
ps.setEscapeProcessing(escapeProcessing);
}
if (this.queryTimeout != 0) {
ps.setQueryTimeout(queryTimeout);
}
if (this.cursorName != null) {
ps.setCursorName(cursorName);
}
if (this.fetchDirection != 0) {
ps.setFetchDirection(fetchDirection);
}
if (this.fetchSize != 0) {
ps.setFetchSize(fetchSize);
}
if (!this.poolable) {
ps.setPoolable(poolable);
}
this.dalParameters.initRealPreparedStatement(ps);
}
private void checkStatementExecute(Connection connection, boolean prepare, int maxRowCount)
throws SQLException {
final String sql =
"select * from (\n" + " values (1, 'a'), (null, 'b'), (3, 'c')) as t (c1, c2)";
final Statement statement;
final ResultSet resultSet;
final ParameterMetaData parameterMetaData;
if (prepare) {
final PreparedStatement ps = connection.prepareStatement(sql);
statement = ps;
ps.setMaxRows(maxRowCount);
parameterMetaData = ps.getParameterMetaData();
assertTrue(ps.execute());
resultSet = ps.getResultSet();
} else {
statement = connection.createStatement();
statement.setMaxRows(maxRowCount);
parameterMetaData = null;
assertTrue(statement.execute(sql));
resultSet = statement.getResultSet();
}
if (parameterMetaData != null) {
assertThat(parameterMetaData.getParameterCount(), equalTo(0));
}
final ResultSetMetaData metaData = resultSet.getMetaData();
assertEquals(2, metaData.getColumnCount());
assertEquals("C1", metaData.getColumnName(1));
assertEquals("C2", metaData.getColumnName(2));
for (int i = 0; i < maxRowCount || (maxRowCount == 0 && i < 3); i++) {
assertTrue(resultSet.next());
}
assertFalse(resultSet.next());
resultSet.close();
statement.close();
connection.close();
}
private List<String> getCategoryNameStartWith(
Connection pConn, int pStoreId, String pName, int pMaxRows) throws Exception {
List<String> list = new ArrayList<String>();
String reqName = pName.toUpperCase().replaceAll("'", "''");
String sql =
"SELECT DISTINCT JD_CATEGORY1 FROM DW_CATEGORY_DIM WHERE STORE_DIM_ID = ? AND UPPER(JD_CATEGORY1) LIKE '"
+ reqName
+ "%' ORDER BY JD_CATEGORY1";
PreparedStatement pstmt = pConn.prepareStatement(sql);
pstmt.setInt(1, pStoreId);
pstmt.setMaxRows(pMaxRows);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
list.add(rs.getString(1));
}
return list;
}
@SuppressWarnings("unchecked")
public List<Job> dequeueImmediate(String nodeId, long maxtime, int maxjobs)
throws DatabaseException {
ArrayList<Job> ret = new ArrayList<Job>(maxjobs);
Connection con = null;
PreparedStatement ps = null;
try {
con = getConnection();
ps = con.prepareStatement(SCHEDULE_IMMEDIATE);
ps.setString(1, nodeId);
ps.setLong(2, maxtime);
ps.setMaxRows(maxjobs);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Scheduler.JobDetails details = new Scheduler.JobDetails();
details.instanceId = asLong(rs.getObject("instanceId"));
details.mexId = (String) rs.getObject("mexId");
details.processId = (String) rs.getObject("processId");
details.type = (String) rs.getObject("type");
details.channel = (String) rs.getObject("channel");
details.correlatorId = (String) rs.getObject("correlatorId");
details.correlationKeySet = (String) rs.getObject("correlationKeySet");
details.retryCount = asInteger(rs.getObject("retryCount"));
details.inMem = asBoolean(rs.getInt("inMem"));
if (rs.getObject("detailsExt") != null) {
try {
ObjectInputStream is = new ObjectInputStream(rs.getBinaryStream("detailsExt"));
details.detailsExt = (Map<String, Object>) is.readObject();
is.close();
} catch (Exception e) {
throw new DatabaseException("Error deserializing job detailsExt", e);
}
}
{
// For compatibility reasons, we check whether there are entries inside
// jobDetailsExt blob, which correspond to extracted entries. If so, we
// use them.
Map<String, Object> detailsExt = details.getDetailsExt();
if (detailsExt.get("type") != null) {
details.type = (String) detailsExt.get("type");
}
if (detailsExt.get("iid") != null) {
details.instanceId = (Long) detailsExt.get("iid");
}
if (detailsExt.get("pid") != null && detailsExt.get("pid") instanceof String) {
details.processId = (String) detailsExt.get("pid");
}
if (detailsExt.get("inmem") != null) {
details.inMem = (Boolean) detailsExt.get("inmem");
}
if (detailsExt.get("ckey") != null) {
details.correlationKeySet = (String) detailsExt.get("ckey");
}
if (detailsExt.get("channel") != null) {
details.channel = (String) detailsExt.get("channel");
}
if (detailsExt.get("mexid") != null) {
details.mexId = (String) detailsExt.get("mexid");
}
if (detailsExt.get("correlatorId") != null) {
details.correlatorId = (String) detailsExt.get("correlatorId");
}
if (detailsExt.get("retryCount") != null) {
details.retryCount = Integer.parseInt((String) detailsExt.get("retryCount"));
}
}
Job job =
new Job(
rs.getLong("ts"),
rs.getString("jobid"),
asBoolean(rs.getInt("transacted")),
details);
ret.add(job);
}
rs.close();
ps.close();
} catch (SQLException se) {
throw new DatabaseException(se);
} finally {
close(ps);
close(con);
}
return ret;
}
@Override
public void setMaxRows(int max) throws SQLException {
stmt.setMaxRows(max);
}
/**
* Executes the query request.
*
* @throws SQLException if a database exception occurs
* @throws IOException
* @throws SAXException
* @throws ParserConfigurationException
*/
public void execute()
throws SQLException, IdentityException, NamingException, ParserConfigurationException,
SAXException, IOException {
// intitalize
PreparedStatement st = null;
PreparedStatement stCount = null;
MmdQueryCriteria criteria = getQueryCriteria();
MmdRecords records = getQueryResult().getRecords();
PageCursor pageCursor = getQueryResult().getPageCursor();
criteria.getDateRange().check();
pageCursor.setTotalRecordCount(0);
adminDao = new ImsMetadataAdminDao(getRequestContext());
tblImsUser = getRequestContext().getCatalogConfiguration().getUserTableName();
Users editablePublishers = Publisher.buildSelectablePublishers(getRequestContext(), false);
for (User u : editablePublishers.values()) {
if (u.getName().length() > 0) {
hmEditablePublishers.put(u.getName().toLowerCase(), u.getKey());
}
}
User tmpUser = new User();
tmpUser.setDistinguishedName("*");
getRequestContext().newIdentityAdapter().readUserGroups(tmpUser);
allGroups = tmpUser.getGroups();
isGptAdministrator = new RoleMap(getRequestContext().getUser()).get("gptAdministrator");
// determine if we are in ArcIMS metadata server proxy mode
try {
// establish the connection
ManagedConnection mc = returnConnection();
Connection con = mc.getJdbcConnection();
// start the SQL expression
StringBuilder sbSql = new StringBuilder();
StringBuilder sbCount = new StringBuilder();
StringBuilder sbFrom = new StringBuilder();
StringBuilder sbWhere = new StringBuilder();
sbSql.append("SELECT A.TITLE,A.DOCUUID,A.SITEUUID,C.USERNAME");
sbSql.append(",A.APPROVALSTATUS,A.PUBMETHOD,A.UPDATEDATE,A.ACL");
sbSql.append(",A.ID,A.HOST_URL,A.FREQUENCY,A.SEND_NOTIFICATION,A.PROTOCOL");
sbSql.append(",A.FINDABLE,A.SEARCHABLE,A.SYNCHRONIZABLE");
sbCount.append("SELECT COUNT(*)");
// append from clause
sbFrom.append(" FROM ").append(tblImsUser).append(" C");
sbFrom.append(",").append(getResourceTableName()).append(" A");
sbSql.append(sbFrom);
sbCount.append(sbFrom);
// build the where clause
if (sbWhere.length() > 0) {
sbWhere.append(" AND");
}
sbWhere.append(" (A.OWNER = C.USERID)");
Map<String, Object> args = criteria.appendWherePhrase("A", sbWhere, getPublisher());
// append the where clause expressions
if (sbWhere.length() > 0) {
sbSql.append(" WHERE ").append(sbWhere.toString());
sbCount.append(" WHERE ").append(sbWhere.toString());
}
// append the order by clause
String sSortColumn = criteria.getSortOption().getColumnKey();
String sSortDir = criteria.getSortOption().getDirection().toString();
if (sSortColumn.equalsIgnoreCase("title")) {
sSortColumn = "UPPER(A.TITLE)";
} else if (sSortColumn.equalsIgnoreCase("uuid")) {
sSortColumn = "A.DOCUUID";
} else if (sSortColumn.equalsIgnoreCase("owner")) {
sSortColumn = "UPPER(C.USERNAME)";
} else if (sSortColumn.equalsIgnoreCase("status")) {
sSortColumn = "A.APPROVALSTATUS";
} else if (sSortColumn.equalsIgnoreCase("method")) {
sSortColumn = "A.PUBMETHOD";
} else if (sSortColumn.equalsIgnoreCase("acl")) {
sSortColumn = "A.ACL";
} else if (sSortColumn.equalsIgnoreCase("updatedate")) {
sSortColumn = "A.UPDATEDATE";
} else {
sSortColumn = "A.UPDATEDATE";
sSortDir = "DESC";
criteria.getSortOption().setColumnKey("updatedate");
criteria.getSortOption().setDirection("desc");
}
sbSql.append(" ORDER BY ");
sbSql.append(sSortColumn).append(" ").append(sSortDir.toUpperCase());
if (!sSortColumn.equalsIgnoreCase("A.UPDATEDATE")) {
sbSql.append(", A.UPDATEDATE DESC");
}
// prepare the statements
st = con.prepareStatement(sbSql.toString());
stCount = con.prepareStatement(sbCount.toString());
int n = 1;
criteria.applyArgs(st, n, args);
criteria.applyArgs(stCount, n, args);
// query the count
logExpression(sbCount.toString());
ResultSet rsCount = stCount.executeQuery();
if (rsCount.next()) {
pageCursor.setTotalRecordCount(rsCount.getInt(1));
}
stCount.close();
stCount = null;
// query records if a count was found
pageCursor.checkCurrentPage();
if (pageCursor.getTotalRecordCount() > 0) {
// set the start record and the number of records to retrieve
int nCurPage = pageCursor.getCurrentPage();
int nRecsPerPage = getQueryResult().getPageCursor().getRecordsPerPage();
int nStartRecord = ((nCurPage - 1) * nRecsPerPage) + 1;
int nMaxRecsToRetrieve = nCurPage * nRecsPerPage;
st.setMaxRows(nMaxRecsToRetrieve);
// determine publisher names associated with editable records
// execute the query
logExpression(sbSql.toString());
ResultSet rs = st.executeQuery();
// build the record set
int nCounter = 0;
while (rs.next()) {
n = 1;
nCounter++;
if (nCounter >= nStartRecord) {
MmdRecord record = new MmdRecord();
records.add(record);
readRecord(rs, record);
// break if we hit the max value for the cursor
if (records.size() >= nRecsPerPage) {
break;
}
}
}
TreeMap<String, MmdRecord> recordsMap =
new TreeMap<String, MmdRecord>(String.CASE_INSENSITIVE_ORDER);
StringBuilder keys = new StringBuilder();
for (MmdRecord r : records) {
if (r.getProtocol() == null) continue;
recordsMap.put(r.getUuid(), r);
if (keys.length() > 0) {
keys.append(",");
}
keys.append("'").append(r.getUuid().toUpperCase()).append("'");
}
readJobStatus(con, recordsMap, keys.toString());
readLastHarvestDate(con, recordsMap, keys.toString());
}
} finally {
closeStatement(st);
closeStatement(stCount);
}
}
public synchronized void execute() {
NetLogger netLog = NetLogger.getTlogger();
netlogger.info(netLog.start("maddash.CheckSchedulerJob.execute"));
int schedJobCount = 0;
int totalJobCount = 0;
// query database
Connection conn = null;
try {
/* garbage collect before hitting database. memory spikes occur
* here as we pull in new check data so this maximizes memory
* available prior to spike and should help prevent heap errors */
System.gc();
MaDDashGlobals globals = MaDDashGlobals.getInstance();
conn = globals.getDataSource().getConnection();
long time = System.currentTimeMillis() / 1000;
netlogger.debug(netLog.start("maddash.CheckSchedulerJob.execute.queryDb"));
PreparedStatement selStmt =
conn.prepareStatement(
"SELECT c.id, c.gridName, "
+ "c.rowName, c.colName, t.checkType, t.checkParams, t.checkInterval, "
+ "t.retryInterval, t.retryAttempts, t.timeout, c.statusMessage FROM checkTemplates AS t, "
+ "checks AS c WHERE c.active = 1 AND t.id = c.checkTemplateId AND "
+ "c.nextCheckTime <= ? AND c.checkStatus != ? ORDER BY c.nextCheckTime ASC FETCH FIRST ? ROWS ONLY");
selStmt.setLong(1, time);
selStmt.setInt(2, CheckConstants.RESULT_MAINTENANCE);
selStmt.setInt(3, globals.getJobBatchSize());
selStmt.setMaxRows(globals.getJobBatchSize());
ResultSet checksToRun = selStmt.executeQuery();
netlogger.debug(netLog.end("maddash.CheckSchedulerJob.execute.queryDb"));
while (checksToRun.next()) {
totalJobCount++;
if (globals.isCheckScheduled(checksToRun.getInt(1))) {
continue;
}
String jobKey = UUID.randomUUID().toString();
String triggerName = "runCheckTrigger-" + jobKey;
String jobName = "runCheckJob-" + jobKey;
SimpleTrigger trigger = new SimpleTrigger(triggerName, null, new Date(), null, 0, 0L);
JobDetail jobDetail = new JobDetail(jobName, "RUN_CHECKS", RunCheckJob.class);
JobDataMap dataMap = new JobDataMap();
dataMap.put("checkId", checksToRun.getInt(1));
dataMap.put("gridName", checksToRun.getString(2));
dataMap.put("rowName", checksToRun.getString(3));
dataMap.put("colName", checksToRun.getString(4));
dataMap.put("checkType", globals.getCheckTypeClassMap().get(checksToRun.getString(5)));
dataMap.put("checkParams", checksToRun.getString(6));
dataMap.put("checkInterval", checksToRun.getInt(7));
dataMap.put("retryInterval", checksToRun.getInt(8));
dataMap.put("retryAttempts", checksToRun.getInt(9));
dataMap.put("timeout", checksToRun.getInt(10));
dataMap.put("statusMessage", checksToRun.getString(11));
dataMap.put("rowVars", DimensionUtil.getParams(checksToRun.getString(3), conn));
dataMap.put("colVars", DimensionUtil.getParams(checksToRun.getString(4), conn));
jobDetail.setJobDataMap(dataMap);
globals.updateScheduledChecks(checksToRun.getInt(1), true);
globals.getScheduler().scheduleJob(jobDetail, trigger);
schedJobCount++;
}
conn.close();
netlogger.info(netLog.end("maddash.CheckSchedulerJob.execute"));
log.debug("Scheduled " + schedJobCount + "/" + totalJobCount + " new jobs");
} catch (Exception e) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e2) {
}
}
netlogger.info(netLog.error("maddash.CheckSchedulerJob.execute", e.getMessage()));
log.error("Error scheduling job " + e.getMessage());
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}
}
protected void setMaxRowsToFetch(PreparedStatement statement, int maxRowsToFetch)
throws SQLException {
statement.setMaxRows(maxRowsToFetch);
}
public void setMaxRows(int max) throws SQLException {
this.maxRows = max;
if (ps != null) {
ps.setMaxRows(max);
}
}
public ArrayList<Album> getLastAlbums(Integer nbAlbum) throws ListAlbumException {
ArrayList<Album> arrayAlbum = new ArrayList<Album>();
try {
Context ctx = new InitialContext();
DataSource source = (DataSource) ctx.lookup("jdbc/MusicStore");
connexion = source.getConnection();
String requeteSQL =
"SELECT Album.idAlbum, Album.titre, Album.image, Artiste.nom, Album.Prix, "
+ " CASE WHEN Promotion_Artiste.idArtiste = Artiste.idArtiste "
+ " THEN true "
+ " ELSE false "
+ " END, "
+ " CASE WHEN Promotion_Artiste.idArtiste = Artiste.idArtiste "
+ " THEN (Album.Prix - (Album.Prix * Promotion.prcremise * 0.01))"
+ " END"
+ " FROM Album, Artiste_Album, Artiste, Promotion, Promotion_Artiste "
+ " WHERE Artiste_Album.idAlbum = Album.idAlbum AND Artiste_Album.idArtiste = Artiste.idArtiste "
+ " AND Promotion_Artiste.idPromotion = Promotion.idPromotion AND Promotion.datedeb <= current_date AND Promotion.datefin >= current_date"
+ " ORDER BY album.idalbum DESC";
PreparedStatement prepStat = connexion.prepareStatement(requeteSQL);
if (nbAlbum != 0) {
prepStat.setMaxRows(nbAlbum);
}
ResultSet donnees = prepStat.executeQuery();
while (donnees.next()) {
Album album = new Album();
album.setIdAlbum(donnees.getInt(1));
album.setTitre(donnees.getString(2));
album.setImage(donnees.getString(3));
album.setArtiste(donnees.getString(4));
album.setPrix(donnees.getDouble(5));
album.setPromo(donnees.getBoolean(6));
album.setPrixPromo(donnees.getDouble(7));
arrayAlbum.add(album);
}
if (arrayAlbum.isEmpty() == true) // Envoi erreur si aucune album
{
throw new ListAlbumException("emptyListAlbum");
}
} catch (SQLException e) {
throw new ListAlbumException("sqlException");
} catch (NamingException e) {
throw new ListAlbumException("errorNaming");
} finally {
try {
connexion.close();
} catch (SQLException e) {
throw new ListAlbumException("sqlConnexionError");
}
}
return arrayAlbum;
}
public List<Agent> queryAgentByCondition(Agent agent, Page page) {
List<Agent> agent_list = null;
int count = 0;
try {
conn = JDBC_Conn.getConnection();
String sql = "select * from agents natural join position where ";
if (agent.getFirst_nm() != null && !"".equals(agent.getFirst_nm())) {
sql += " first_nm='" + agent.getFirst_nm() + "'" + " and ";
count++;
}
if (agent.getLast_nm() != null && !"".equals(agent.getLast_nm())) {
sql += " last_nm='" + agent.getLast_nm() + "'" + " and ";
count++;
}
if (agent.getGender() != null && !"".equals(agent.getGender())) {
sql += " gender='" + agent.getGender() + "'" + " and ";
count++;
}
if (agent.getZip() != null && !"".equals(agent.getZip())) {
sql += " zip='" + agent.getZip() + "'" + " and ";
count++;
}
if (agent.getEmail() != null && !"".equals(agent.getEmail())) {
sql += " email='" + agent.getEmail() + "'" + " and ";
count++;
}
if (agent.getPhone_number() != null && !"".equals(agent.getPhone_number())) {
sql += " phone_number='" + agent.getPhone_number() + "'" + " and ";
count++;
}
if (agent.getPosition() != null && !"".equals(agent.getPosition())) {
sql += " position='" + agent.getPosition() + "'" + " and ";
count++;
}
if (agent.getDob() != null) {
SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
String dob_str = sdf.format(agent.getDob());
sql += " dob= " + "to_date('" + dob_str + "'," + "'MM/dd/yyyy')" + " and ";
count++;
}
if (count == 0) return queryAllAgents(page);
int last_index = sql.lastIndexOf("and");
sql = sql.substring(0, last_index);
stmt =
conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt.setMaxRows(page.getEndIndex());
rs = stmt.executeQuery();
if (page.getBeginIndex() > 0) {
rs.absolute(page.getBeginIndex());
}
while (rs.next()) {
if (agent_list == null) agent_list = new ArrayList<Agent>();
Agent agent1 = new Agent();
agent1.setAgent_id(rs.getString("agent_id"));
agent1.setPhone_number(rs.getString("phone_number"));
agent1.setEmail(rs.getString("email"));
agent1.setFirst_nm(CommUtils.initCap(rs.getString("first_nm")));
agent1.setLast_nm(CommUtils.initCap(rs.getString("last_nm")));
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date dob = sdf.parse(rs.getString("dob"));
agent1.setDob(dob);
agent1.setZip(rs.getString("zip"));
agent1.setGender(rs.getString("gender"));
agent1.setPosition(rs.getString("position"));
agent_list.add(agent1);
}
} catch (Exception e) {
e.printStackTrace();
throw new DAOException();
} finally {
JDBC_Conn.releaseConnection(conn, stmt, rs);
}
return agent_list;
}
public void setMaxRows(int max) throws SQLException {
preparedStatement.setMaxRows(max);
}
public void setMaxRows(int max) throws SQLException {
delegate.setMaxRows(max);
}