public void executeUpdate(String sql, Object... params) throws SQLException {
this.checkConnection();
SQLQuery query = new SQLQuery(sql, params);
query.execute();
}
/*
Gets the LoginID ignoring case.
*/
private Login findByIdOracleInsensitive(org.openiam.idm.srvc.auth.dto.LoginId id) {
log.debug("findByIdOracleInsensitive..");
String select =
" select /*+ INDEX(IDX_LOGIN_UPPER) */ "
+ " SERVICE_ID, LOGIN, MANAGED_SYS_ID, IDENTITY_TYPE, CANONICAL_NAME, USER_ID, PASSWORD, "
+ " PWD_EQUIVALENT_TOKEN, PWD_CHANGED, PWD_EXP, RESET_PWD, FIRST_TIME_LOGIN, IS_LOCKED, STATUS, "
+ " GRACE_PERIOD, CREATE_DATE, CREATED_BY, CURRENT_LOGIN_HOST, AUTH_FAIL_COUNT, LAST_AUTH_ATTEMPT, "
+ " LAST_LOGIN, IS_DEFAULT, PWD_CHANGE_COUNT, LAST_LOGIN_IP, PREV_LOGIN, PREV_LOGIN_IP, "
+ " PSWD_RESET_TOKEN, PSWD_RESET_TOKEN_EXP, LOGIN_ATTR_IN_TARGET_SYS "
+ " FROM LOGIN "
+ " WHERE SERVICE_ID = :serviceId AND UPPER(LOGIN) = :login AND MANAGED_SYS_ID = :managedSysId ";
log.debug("SQL to get Identity: " + select);
Session session = sessionFactory.getCurrentSession();
SQLQuery qry = session.createSQLQuery(select);
qry.addEntity(Login.class);
qry.setString("serviceId", id.getDomainId());
qry.setString("login", id.getLogin().toUpperCase());
qry.setString("managedSysId", id.getManagedSysId());
try {
return (Login) qry.uniqueResult();
} catch (Exception e) {
log.error(e.toString());
}
return null;
}
private IterableResult<Thumbnail> doFindMissing() throws SQLException {
Connection conn = source.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = null;
try {
rs = stmt.executeQuery(SQLQuery.getFindMissingThumbnails());
return new ThumbnailResult(rs, stmt);
} finally {
if (rs == null) {
SQLQuery.tryClose(stmt);
}
}
}
public float saldo(Conta conta, Date data) {
StringBuffer sql = new StringBuffer();
sql.append("select sum(l.valor * c.fator)");
sql.append(" from LANCAMENTO l,");
sql.append(" CATEGORIA c");
sql.append(" where l.categoria = c.codigo");
sql.append(" and l.conta = :conta");
sql.append(" and l.data <= :data");
SQLQuery query = this.session.createSQLQuery(sql.toString());
query.setParameter("conta", conta.getConta());
query.setParameter("data", data);
BigDecimal saldo = (BigDecimal) query.uniqueResult();
if (saldo != null) {
return saldo.floatValue();
}
return 0f;
}
private void doDeleteUnrelated() throws SQLException {
Connection conn = source.getConnection();
Statement stmt = conn.createStatement();
try {
stmt.executeUpdate(
"delete from THUMBNAILS where DIGOBJEKTID not in (select ID from DIGOBJEKT)");
} finally {
SQLQuery.tryClose(stmt);
}
}
private void overrideSQLQuery(DBMS dbms, SQLQuery sqlQuery) {
HashMap<String, SQLQuery> map = null;
if (defaultQLFilesMap.containsKey(dbms)) {
map = specificSQLQueriesMap.get(dbms);
} else {
map = new HashMap<String, SQLQuery>();
specificSQLQueriesMap.put(dbms.getName(), map);
}
map.put(sqlQuery.getId(), sqlQuery);
}
public void insert(String table, String[] fields, List<Object[]> rows) throws SQLException {
this.checkConnection();
String[] fieldPlaceholders = new String[fields.length];
Arrays.fill(fieldPlaceholders, "?");
String sql =
"INSERT INTO `"
+ table
+ "` (`"
+ StringUtils.implode(fields, "`, `")
+ "`) VALUES ("
+ StringUtils.implode(fieldPlaceholders, ", ")
+ ");";
SQLQuery query = new SQLQuery(sql);
for (Object[] params : rows) {
query.bindParams(params);
query.execute();
}
}
private void doInsert(BigDecimal digiObjId, String mimeType, byte[] contents)
throws SQLException {
Connection conn = source.getConnection();
PreparedStatement pstmt =
conn.prepareStatement(
"insert into THUMBNAILS (DIGOBJEKTID, MIME, CONTENTS) values (?, ?, ?)");
try {
pstmt.setBigDecimal(1, digiObjId);
pstmt.setString(2, mimeType);
pstmt.setBytes(3, contents);
pstmt.executeUpdate();
} finally {
SQLQuery.tryClose(pstmt);
}
}
private void cacheSQLQuery(SQLQuery sqlQuery) {
defaultSQLQueriesMap.put(sqlQuery.getId(), sqlQuery);
}
protected void optimizeFilter(SQLQuery query) {
optimizeExpression(query.getFilter());
}
@Override
public void sqlTrace(SQLTraceRecord record) {
if (record != null) {
switch (record.getMethodName()) {
// these calls capture a query string
case "nativeSQL":
case "prepareCall":
case "prepareStatement":
case "addBatch":
{
// acquire the SQL
SQLQuery query = currentQuery.get();
if (query == null) {
query = new SQLQuery();
currentQuery.set(query);
}
if (record.getParams() != null && record.getParams().length > 0)
query.addSQL((String) record.getParams()[0]);
break;
}
case "execute":
case "executeQuery":
case "executeUpdate":
{
// acquire the SQL
SQLQuery query = currentQuery.get();
if (query == null) {
query = new SQLQuery();
currentQuery.set(query);
} // these can all run the SQL and contain SQL
long executionTime = record.getExecutionTime();
// see if we have more SQL
if (record.getParams() != null && record.getParams().length > 0) {
// gather the SQL
query.addSQL((String) record.getParams()[0]);
}
// check the execution time
if (executionTime > threshold) {
StringBuilder messageBuilder =
new StringBuilder("SQL Query Exceeded Threshold Time: ");
messageBuilder
.append(threshold)
.append("(ms): Time Taken: ")
.append(executionTime)
.append("(ms)\n")
.append("Query was ")
.append(query.getSQL());
logger.log(
Level.WARNING,
messageBuilder.toString(),
new Exception("Stack Trace shows code path to SQL"));
}
// clean the thread local
currentQuery.set(null);
break;
}
}
}
}
