private void setParameter(PreparedStatement stmt, List<Object> paramList) throws SQLException {
for (int i = 0; i < paramList.size(); i++) {
Object param = paramList.get(i);
if (param instanceof Arrays) {
List<Object> list = Arrays.asList(param);
for (Object obj : list) {
if (obj instanceof String) {
stmt.setString(i + 1, (String) paramList.get(i));
} else if (obj instanceof Integer) {
stmt.setInt(i + 1, (Integer) paramList.get(i));
} else if (obj instanceof Double) {
stmt.setDouble(i + 1, (Double) paramList.get(i));
} else if (param instanceof Long) {
stmt.setLong(i + 1, (Long) paramList.get(i));
} else if (param instanceof Float) {
stmt.setFloat(i + 1, (Float) paramList.get(i));
} else if (param instanceof Short) {
stmt.setShort(i + 1, (Short) paramList.get(i));
} else if (param instanceof Byte) {
stmt.setByte(i + 1, (Byte) paramList.get(i));
} else if (param instanceof Boolean) {
stmt.setBoolean(i + 1, (Boolean) paramList.get(i));
} else if (param instanceof Date) {
stmt.setDate(i + 1, (Date) paramList.get(i));
} else if (param instanceof Timestamp) {
stmt.setTimestamp(i + 1, (Timestamp) paramList.get(i));
} else if (param instanceof Object) {
stmt.setObject(i + 1, (Object) paramList.get(i));
} else if (param instanceof Arrays) {
stmt.setObject(i + 1, (Object) paramList.get(i));
}
}
}
if (param instanceof String) {
stmt.setString(i + 1, (String) paramList.get(i));
} else if (param instanceof Integer) {
stmt.setInt(i + 1, (Integer) paramList.get(i));
} else if (param instanceof Double) {
stmt.setDouble(i + 1, (Double) paramList.get(i));
} else if (param instanceof Long) {
stmt.setLong(i + 1, (Long) paramList.get(i));
} else if (param instanceof Float) {
stmt.setFloat(i + 1, (Float) paramList.get(i));
} else if (param instanceof Short) {
stmt.setShort(i + 1, (Short) paramList.get(i));
} else if (param instanceof Byte) {
stmt.setByte(i + 1, (Byte) paramList.get(i));
} else if (param instanceof Boolean) {
stmt.setBoolean(i + 1, (Boolean) paramList.get(i));
} else if (param instanceof Date) {
stmt.setDate(i + 1, (Date) paramList.get(i));
} else if (param instanceof Timestamp) {
stmt.setTimestamp(i + 1, (Timestamp) paramList.get(i));
} else if (param instanceof Object) {
stmt.setObject(i + 1, (Object) paramList.get(i));
} else if (param instanceof Arrays) {
stmt.setObject(i + 1, (Object) paramList.get(i));
}
}
}
public boolean insertHealthStatus(String personnelID, HealthStatus hs) {
boolean check = true;
PreparedStatement ps;
if (dbc.connect()) {
try {
String sqlQuery = "INSERT INTO " + HEALTH_STATUS + " VALUES(?,?,?,?,?,?,?,?,?)";
ps = dbc.getConnection().prepareStatement(sqlQuery);
ps.setString(1, personnelID);
if (hs.getHealDate() == null) {
ps.setNull(2, java.sql.Types.DATE);
} else {
ps.setDate(2, hs.getHealDate());
}
ps.setNString(3, hs.getHospital());
ps.setShort(4, hs.getHeight());
ps.setShort(5, hs.getWeight());
ps.setString(6, hs.getSight());
ps.setShort(7, hs.getBloodID());
ps.setShort(8, hs.getHealthStatusID());
ps.setNString(9, hs.getNote());
if (ps.executeUpdate() < 1) {
check = false;
}
} catch (SQLException ex) {
Logger.getLogger(PersonnelDAO.class.getName()).log(Level.SEVERE, null, ex);
}
}
return check;
}
public boolean insertSkillInfo(String personnelID, Skill sk) {
boolean check = true;
PreparedStatement ps;
if (dbc.connect()) {
try {
String sqlQuery = "INSERT INTO " + SKILL_INFO + " VALUES(?,?,?,?,?)";
ps = dbc.getConnection().prepareStatement(sqlQuery);
ps.setString(1, personnelID);
ps.setShort(2, sk.getNameID());
ps.setShort(3, sk.getClassificationID());
ps.setNString(4, sk.getNote());
if (sk.getEffectiveDate() == null) {
ps.setNull(5, java.sql.Types.DATE);
} else {
ps.setDate(5, sk.getEffectiveDate());
}
if (ps.executeUpdate() < 1) {
check = false;
}
} catch (SQLException ex) {
Logger.getLogger(PersonnelDAO.class.getName()).log(Level.SEVERE, null, ex);
}
}
return check;
}
public CFSecurityISOCountryLanguageBuff lockBuff(
CFSecurityAuthorization Authorization, CFSecurityISOCountryLanguagePKey PKey) {
final String S_ProcName = "lockBuff";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
short ISOCountryId = PKey.getRequiredISOCountryId();
short ISOLanguageId = PKey.getRequiredISOLanguageId();
String sql =
"SELECT * FROM "
+ schema.getLowerDbSchemaName()
+ ".sp_lock_iso_cntrylng( ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " )";
if (stmtLockBuffByPKey == null) {
stmtLockBuffByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtLockBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtLockBuffByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtLockBuffByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtLockBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtLockBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtLockBuffByPKey.setShort(argIdx++, ISOCountryId);
stmtLockBuffByPKey.setShort(argIdx++, ISOLanguageId);
resultSet = stmtLockBuffByPKey.executeQuery();
if (resultSet.next()) {
CFSecurityISOCountryLanguageBuff buff = unpackISOCountryLanguageResultSetToBuff(resultSet);
if (resultSet.next()) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response");
}
return (buff);
} else {
return (null);
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void excuteBatch() throws BusinessException {
Connection conn = null;
PreparedStatement psmt = null;
synchronized (vector) {
try {
conn = DataSourceUtils.getConnection(SessionFactoryUtils.getDataSource(sessionFactory));
conn.setAutoCommit(false);
psmt = conn.prepareStatement(SqlFactory.getInstance().insertPolicyDevice());
for (PolicyDevice pd : vector) {
psmt.setString(1, (String) new UUIDHexGenerator().generate(null, null));
psmt.setString(2, pd.getPolicyId());
psmt.setString(3, pd.getDeviceId());
psmt.setShort(4, pd.getType());
psmt.setShort(5, pd.getStatus());
psmt.addBatch();
}
psmt.executeBatch();
conn.commit();
vector.clear();
} catch (SQLException e) {
e.printStackTrace();
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
throw new BusinessException(
ErrorCode.DATABASE_ACCESS_ERROR, "Could not get database connnection");
} catch (Exception e) {
e.printStackTrace();
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
throw new BusinessException(ErrorCode.ERROR, e.getMessage());
} finally {
try {
if (psmt != null) {
psmt.close();
psmt = null;
}
if (conn != null) {
DataSourceUtils.releaseConnection(
conn, SessionFactoryUtils.getDataSource(sessionFactory));
}
} catch (Exception e) {
e.printStackTrace();
throw new BusinessException(
ErrorCode.DATABASE_ACCESS_ERROR, "Could not close database connnection");
}
}
}
}
public CFSecurityISOCountryCurrencyBuff readBuff(
CFSecurityAuthorization Authorization, CFSecurityISOCountryCurrencyPKey PKey) {
final String S_ProcName = "readBuff";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
short ISOCountryId = PKey.getRequiredISOCountryId();
short ISOCurrencyId = PKey.getRequiredISOCurrencyId();
final String sql =
"CALL sp_read_iso_cntryccy( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " )";
if (stmtReadBuffByPKey == null) {
stmtReadBuffByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtReadBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtReadBuffByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtReadBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtReadBuffByPKey.setShort(argIdx++, ISOCountryId);
stmtReadBuffByPKey.setShort(argIdx++, ISOCurrencyId);
resultSet = stmtReadBuffByPKey.executeQuery();
if (resultSet.next()) {
CFSecurityISOCountryCurrencyBuff buff = unpackISOCountryCurrencyResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
return (buff);
} else {
return (null);
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void deleteISOTimezoneByOffsetIdx(
CFSecurityAuthorization Authorization, short argTZHourOffset, short argTZMinOffset) {
final String S_ProcName = "deleteISOTimezoneByOffsetIdx";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
String sql =
"SELECT "
+ schema.getLowerDbSchemaName()
+ ".sp_delete_isotz_by_offsetidx( ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " ) as DeletedFlag";
if (stmtDeleteByOffsetIdx == null) {
stmtDeleteByOffsetIdx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByOffsetIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByOffsetIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByOffsetIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByOffsetIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByOffsetIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByOffsetIdx.setShort(argIdx++, argTZHourOffset);
stmtDeleteByOffsetIdx.setShort(argIdx++, argTZMinOffset);
resultSet = stmtDeleteByOffsetIdx.executeQuery();
if (resultSet.next()) {
boolean deleteFlag = resultSet.getBoolean(1);
if (resultSet.next()) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response");
}
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected 1 record result set to be returned by delete, not 0 rows");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void deleteISOTimezoneByOffsetIdx(
CFSecurityAuthorization Authorization, short argTZHourOffset, short argTZMinOffset) {
final String S_ProcName = "deleteISOTimezoneByOffsetIdx";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
String sql = "exec sp_delete_isotz_by_offsetidx ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?";
if (stmtDeleteByOffsetIdx == null) {
stmtDeleteByOffsetIdx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByOffsetIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByOffsetIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByOffsetIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByOffsetIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByOffsetIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByOffsetIdx.setShort(argIdx++, argTZHourOffset);
stmtDeleteByOffsetIdx.setShort(argIdx++, argTZMinOffset);
Object stuff = null;
boolean moreResults = stmtDeleteByOffsetIdx.execute();
while (stuff == null) {
try {
moreResults = stmtDeleteByOffsetIdx.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
stuff = stmtDeleteByOffsetIdx.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtDeleteByOffsetIdx.getUpdateCount()) {
break;
}
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public CFSecurityISOTimezoneBuff[] readBuffByOffsetIdx(
CFSecurityAuthorization Authorization, short TZHourOffset, short TZMinOffset) {
final String S_ProcName = "readBuffByOffsetIdx";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
String sql =
"SELECT * FROM "
+ schema.getLowerDbSchemaName()
+ ".sp_read_isotz_by_offsetidx( ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " )";
if (stmtReadBuffByOffsetIdx == null) {
stmtReadBuffByOffsetIdx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtReadBuffByOffsetIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByOffsetIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtReadBuffByOffsetIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtReadBuffByOffsetIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByOffsetIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtReadBuffByOffsetIdx.setShort(argIdx++, TZHourOffset);
stmtReadBuffByOffsetIdx.setShort(argIdx++, TZMinOffset);
resultSet = stmtReadBuffByOffsetIdx.executeQuery();
List<CFSecurityISOTimezoneBuff> buffList = new LinkedList<CFSecurityISOTimezoneBuff>();
while (resultSet.next()) {
CFSecurityISOTimezoneBuff buff = unpackISOTimezoneResultSetToBuff(resultSet);
buffList.add(buff);
}
int idx = 0;
CFSecurityISOTimezoneBuff[] retBuff = new CFSecurityISOTimezoneBuff[buffList.size()];
Iterator<CFSecurityISOTimezoneBuff> iter = buffList.iterator();
while (iter.hasNext()) {
retBuff[idx++] = iter.next();
}
return (retBuff);
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public CFBamISOCountryCurrencyBuff readBuffByIdIdx(
CFBamAuthorization Authorization, short ISOCountryId, short ISOCurrencyId) {
final String S_ProcName = "readBuffByIdIdx";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
String sql =
"SELECT * FROM "
+ schema.getLowerDbSchemaName()
+ ".sp_read_iso_cntryccy_by_ididx( ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " )";
if (stmtReadBuffByIdIdx == null) {
stmtReadBuffByIdIdx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtReadBuffByIdIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByIdIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtReadBuffByIdIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtReadBuffByIdIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByIdIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtReadBuffByIdIdx.setShort(argIdx++, ISOCountryId);
stmtReadBuffByIdIdx.setShort(argIdx++, ISOCurrencyId);
resultSet = stmtReadBuffByIdIdx.executeQuery();
if (resultSet.next()) {
CFBamISOCountryCurrencyBuff buff = unpackISOCountryCurrencyResultSetToBuff(resultSet);
if (resultSet.next()) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response");
}
return (buff);
} else {
return (null);
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void deleteISOCountryCurrencyByIdIdx(
CFSecurityAuthorization Authorization, short argISOCountryId, short argISOCurrencyId) {
final String S_ProcName = "deleteISOCountryCurrencyByIdIdx";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
final String sql =
"CALL sp_delete_iso_cntryccy_by_ididx( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " )";
if (stmtDeleteByIdIdx == null) {
stmtDeleteByIdIdx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByIdIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByIdIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByIdIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByIdIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByIdIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByIdIdx.setShort(argIdx++, argISOCountryId);
stmtDeleteByIdIdx.setShort(argIdx++, argISOCurrencyId);
resultSet = stmtDeleteByIdIdx.executeQuery();
if (resultSet.next()) {
int deleteFlag = resultSet.getInt(1);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected 1 record result set to be returned by delete, not 0 rows");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void deleteISOCountryLanguage(
CFSecurityAuthorization Authorization, CFSecurityISOCountryLanguageBuff Buff) {
final String S_ProcName = "deleteISOCountryLanguage";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
short ISOCountryId = Buff.getRequiredISOCountryId();
short ISOLanguageId = Buff.getRequiredISOLanguageId();
String sql =
"call "
+ schema.getLowerDbSchemaName()
+ ".sp_delete_iso_cntrylng( ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " )";
if (stmtDeleteByPKey == null) {
stmtDeleteByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByPKey.setShort(argIdx++, ISOCountryId);
stmtDeleteByPKey.setShort(argIdx++, ISOLanguageId);
stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
;
stmtDeleteByPKey.executeUpdate();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void deleteISOCountryCurrency(
CFSecurityAuthorization Authorization, CFSecurityISOCountryCurrencyBuff Buff) {
final String S_ProcName = "deleteISOCountryCurrency";
try {
Connection cnx = schema.getCnx();
short ISOCountryId = Buff.getRequiredISOCountryId();
short ISOCurrencyId = Buff.getRequiredISOCurrencyId();
String sql =
"exec sp_delete_iso_cntryccy ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?";
if (stmtDeleteByPKey == null) {
stmtDeleteByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByPKey.setShort(argIdx++, ISOCountryId);
stmtDeleteByPKey.setShort(argIdx++, ISOCurrencyId);
stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
;
Object stuff = null;
boolean moreResults = stmtDeleteByPKey.execute();
while (stuff == null) {
try {
moreResults = stmtDeleteByPKey.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
stuff = stmtDeleteByPKey.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtDeleteByPKey.getUpdateCount()) {
break;
}
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
}
/**
* Inserts the given feature into BLOB table and returns the generated primary key.
*
* @param stmt
* @param feature
* @return primary key of the feature
* @throws SQLException
* @throws FeatureStoreException
*/
private int insertFeatureBlob(PreparedStatement stmt, Feature feature)
throws SQLException, FeatureStoreException {
LOG.debug("Inserting feature with id '" + feature.getId() + "' (BLOB)");
if (fs.getSchema().getFeatureType(feature.getName()) == null) {
throw new FeatureStoreException(
"Cannot insert feature '"
+ feature.getName()
+ "': feature type is not served by this feature store.");
}
ICRS crs = blobMapping.getCRS();
stmt.setString(1, feature.getId());
stmt.setShort(2, fs.getFtId(feature.getName()));
byte[] bytes = encodeFeatureBlob(feature, crs);
stmt.setBytes(3, bytes);
LOG.debug("Feature blob size: " + bytes.length);
Geometry bboxGeom = getFeatureEnvelopeAsGeometry(feature);
blobGeomConverter.setParticle(stmt, bboxGeom, 4);
stmt.execute();
int internalId = -1;
// ResultSet rs = null;
// try {
// // TODO only supported for PostgreSQL >= 8.2
// rs = stmt.getGeneratedKeys();
// rs.next();
// internalId = rs.getInt( 1 );
// } finally {
// if ( rs != null ) {
// rs.close();
// }
// }
return internalId;
}
public boolean insertFamilyDetails(String personnelID, FamilyDetails fd) {
boolean check = true;
PreparedStatement ps;
if (dbc.connect()) {
try {
String sqlQuery = "INSERT INTO " + FAMILY + " VALUES(?,?,?,?,?,?,?)";
ps = dbc.getConnection().prepareStatement(sqlQuery);
ps.setString(1, personnelID);
ps.setShort(2, fd.getRelationID());
ps.setNString(3, fd.getPerName());
if (fd.getPerBirth() == null) {
ps.setNull(4, java.sql.Types.DATE);
} else {
ps.setDate(4, fd.getPerBirth());
}
ps.setNString(5, fd.getPerJob());
ps.setNString(6, fd.getPerAdd());
ps.setString(7, fd.getPerPhone());
if (ps.executeUpdate() < 1) {
check = false;
}
} catch (SQLException ex) {
Logger.getLogger(PersonnelDAO.class.getName()).log(Level.SEVERE, null, ex);
}
}
return check;
}
public static void addNewVehicle(String reg, String make, String model, short yom) {
// add vehicle to database
try {
// create a mysql database connection
Connection conn = Utills.openDb();
// Item table mysql insert statement
String query = " insert into vehicle (reg_num, make, model, yom)" + " values (?, ?, ?, ?)";
// Item table mysql insert preparedstatement
PreparedStatement preparedStmt = (PreparedStatement) conn.prepareStatement(query);
preparedStmt.setString(1, reg);
preparedStmt.setString(2, make);
preparedStmt.setString(3, model);
preparedStmt.setShort(4, yom);
System.out.print(preparedStmt);
// execute the preparedstatement
preparedStmt.execute();
conn.close();
} catch (Exception e) {
System.err.println("Got an exception!");
System.err.println(e.getMessage());
}
}
public boolean updateContractList(Contract con) {
boolean check = true;
PreparedStatement ps;
if (dbc.connect()) {
try {
String sqlQuery = "INSERT INTO " + getCONTRACT_CB() + " VALUES(?,?,?,?,?,?)";
ps = dbc.getConnection().prepareStatement(sqlQuery);
ps.setNString(1, con.getName());
ps.setShort(2, con.getMonth());
if (con.isBHXH()) {
ps.setBoolean(3, con.isBHXH());
ps.setFloat(4, con.getPercent());
} else {
ps.setBoolean(3, con.isBHXH());
ps.setNull(4, java.sql.Types.FLOAT);
}
ps.setBytes(5, con.getTemplate());
ps.setString(6, con.getExtension());
if (ps.executeUpdate() < 1) {
check = false;
}
} catch (SQLException ex) {
Logger.getLogger(PersonnelDAO.class.getName()).log(Level.SEVERE, null, ex);
}
}
return check;
}
public boolean insertDocumentDetails(String personnelID, DocumentsDetails doc) {
boolean check = true;
PreparedStatement ps;
if (dbc.connect()) {
try {
String sqlQuery = "INSERT INTO " + DOC_INFO + " VALUES(?,?,?,?,?,?)";
ps = dbc.getConnection().prepareStatement(sqlQuery);
ps.setString(1, personnelID);
ps.setNString(2, doc.getName());
if (doc.getSaveDate() == null) {
ps.setNull(3, java.sql.Types.DATE);
} else {
ps.setDate(3, doc.getSaveDate());
}
ps.setBytes(4, doc.getFile());
ps.setString(5, doc.getExtension());
ps.setShort(6, doc.getKindID());
if (ps.executeUpdate() < 1) {
check = false;
}
} catch (SQLException ex) {
Logger.getLogger(PersonnelDAO.class.getName()).log(Level.SEVERE, null, ex);
}
}
return check;
}
public void setShort(int idx, short sht) throws SQLException {
try {
addMementoEntry("setShort", new Class[] {Short.TYPE}, idx, sht);
wrapped.setShort(idx, sht);
} catch (SQLException e) {
throw new UcanaccessSQLException(e);
}
}
public void setShort(int parameterIndex, short x) throws SQLException {
checkOpen();
try {
_stmt.setShort(parameterIndex, x);
} catch (SQLException e) {
handleException(e);
}
}
public void deleteAuditAction(
CFSecurityAuthorization Authorization, CFSecurityAuditActionBuff Buff) {
final String S_ProcName = "deleteAuditAction";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
ICFSecuritySchema.AuditActionEnum AuditActionId = Buff.getRequiredAuditActionId();
final String sql =
"CALL sp_delete_auditaction( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " )";
if (stmtDeleteByPKey == null) {
stmtDeleteByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByPKey.setShort(argIdx++, (short) AuditActionId.ordinal());
stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
;
resultSet = stmtDeleteByPKey.executeQuery();
if (resultSet.next()) {
int deleteFlag = resultSet.getInt(1);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected 1 record result set to be returned by delete, not 0 rows");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void setShort(int parameterIndex, short x) throws SQLException {
Profiler profiler = _profilerPoint.start();
try {
_preparedStatement.setShort(parameterIndex, x);
} finally {
profiler.finish();
}
}
public void setShort(int parameterIndex, short x) throws SQLException {
String methodCall = "setShort(" + parameterIndex + ", " + x + ")";
argTraceSet(parameterIndex, "(short)", new Short(x));
try {
realPreparedStatement.setShort(parameterIndex, x);
} catch (SQLException s) {
reportException(methodCall, s);
throw s;
}
reportReturn(methodCall);
}
protected long addLayoutSet(
long layoutSetId,
long companyId,
long groupId,
long layoutSetPrototypeId,
UserGroupTemplateInfo userGroupTemplateInfo)
throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getConnection();
StringBundler sb = new StringBundler(5);
sb.append("insert into LayoutSet (layoutSetId, groupId, ");
sb.append("companyId, privateLayout, logo, logoId, themeId, ");
sb.append("colorSchemeId, wapThemeId, wapColorSchemeId, css, ");
sb.append("pageCount, settings_, layoutSetPrototypeId) values ");
sb.append("(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
String sql = sb.toString();
ps = con.prepareStatement(sql);
ps.setLong(1, layoutSetId);
ps.setLong(2, groupId);
ps.setLong(3, companyId);
ps.setBoolean(4, userGroupTemplateInfo.isPrivateLayout());
ps.setShort(5, userGroupTemplateInfo.getLogo());
ps.setLong(6, userGroupTemplateInfo.getLogoId());
ps.setString(7, userGroupTemplateInfo.getThemeId());
ps.setString(8, userGroupTemplateInfo.getColorSchemeId());
ps.setString(9, userGroupTemplateInfo.getWapThemeId());
ps.setString(10, userGroupTemplateInfo.getWapColorSchemeId());
ps.setString(11, userGroupTemplateInfo.getCss());
ps.setLong(12, userGroupTemplateInfo.getPageCount());
ps.setString(13, userGroupTemplateInfo.getSettings());
ps.setLong(14, layoutSetPrototypeId);
ps.execute();
return layoutSetId;
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
private void updateUnsignedTable(Connection conn, double data) throws Exception {
PreparedStatement stmt =
conn.prepareStatement("UPSERT INTO " + unsignedTableName + " VALUES (?, ?, ?, ?, ?, ?, ?)");
stmt.setString(1, KEY);
Double d = Double.valueOf(data);
stmt.setDouble(2, d.doubleValue());
stmt.setFloat(3, d.floatValue());
stmt.setInt(4, d.intValue());
stmt.setLong(5, d.longValue());
stmt.setShort(6, d.shortValue());
stmt.setByte(7, d.byteValue());
stmt.executeUpdate();
conn.commit();
}
/**
* The setup creates a Connection to the database, and also creates a table with one row. Then it
* creates an updatable ResultSet which is positioned on the row.
*
* @exception Exception any exception will cause test to fail with error.
*/
public void setUp() throws Exception {
Connection con = getConnection();
try {
con.setAutoCommit(false);
Statement stmt = con.createStatement();
String createTableString =
"CREATE TABLE "
+ TABLE_NAME
+ " ("
+ "F01 SMALLINT,"
+ "F02 INTEGER,"
+ "F03 BIGINT,"
+ "F04 REAL,"
+ "F05 FLOAT,"
+ "F06 DOUBLE,"
+ "F07 DECIMAL,"
+ "F08 NUMERIC,"
+ "F09 CHAR(100),"
+ "F10 VARCHAR(256) )";
println(createTableString);
stmt.executeUpdate(createTableString);
PreparedStatement ps =
con.prepareStatement("insert into " + TABLE_NAME + " values(?,?,?,?,?,?,?,?,?,?)");
ps.setShort(1, (short) 1);
ps.setInt(2, 1);
ps.setLong(3, 1L);
ps.setFloat(4, 1.0f);
ps.setDouble(5, 1.0);
ps.setDouble(6, 1.0);
// Use setString instead of setBigDecimal to
// allow most of the test cases to run under J2ME
ps.setString(7, "1");
ps.setString(8, "1");
ps.setString(9, "1");
ps.setString(10, "1");
ps.executeUpdate();
ps.close();
stmt.close();
} catch (SQLException e) {
con.rollback();
throw e;
}
}
public CFSecurityAuditActionBuff readBuffByIdIdx(
CFSecurityAuthorization Authorization, ICFSecuritySchema.AuditActionEnum AuditActionId) {
final String S_ProcName = "readBuffByIdIdx";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
final String sql = "CALL sp_read_auditaction_by_ididx( ?, ?, ?, ?, ?" + ", " + "?" + " )";
if (stmtReadBuffByIdIdx == null) {
stmtReadBuffByIdIdx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtReadBuffByIdIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByIdIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtReadBuffByIdIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtReadBuffByIdIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByIdIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtReadBuffByIdIdx.setShort(argIdx++, (short) AuditActionId.ordinal());
resultSet = stmtReadBuffByIdIdx.executeQuery();
if (resultSet.next()) {
CFSecurityAuditActionBuff buff = unpackAuditActionResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
return (buff);
} else {
return (null);
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public boolean insertEducationInfo(String personnelID, Education edu) {
boolean check = true;
PreparedStatement ps;
if (dbc.connect()) {
try {
String sqlQuery = "INSERT INTO " + EDUCATION + " VALUES(?,?,?,?,?,?,?)";
ps = dbc.getConnection().prepareStatement(sqlQuery);
ps.setString(1, personnelID);
ps.setShort(2, edu.getKindID());
ps.setShort(3, edu.getGraduationID());
ps.setNString(4, edu.getCourseName());
ps.setNString(5, edu.getSchoolName());
ps.setShort(6, edu.getFromYear());
ps.setShort(7, edu.getToYear());
if (ps.executeUpdate() < 1) {
check = false;
}
} catch (SQLException ex) {
Logger.getLogger(PersonnelDAO.class.getName()).log(Level.SEVERE, null, ex);
}
}
return check;
}
@Test
public void colNameAccess() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select ? as col1, ? as col2, ? as bingo;");
prep.setNull(1, 0);
prep.setFloat(2, Float.MIN_VALUE);
prep.setShort(3, Short.MIN_VALUE);
prep.executeQuery();
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertNull(rs.getString("col1"));
assertTrue(rs.wasNull());
assertEquals(Float.MIN_VALUE, rs.getFloat("col2"));
assertEquals(Short.MIN_VALUE, rs.getShort("bingo"));
rs.close();
prep.close();
}
// create/split item stack
private ItemStack getSplitItemStack(
int itemRowId, int itemId, short itemDamage, int qty, String enchStr) {
ItemStack stack = new ItemStack(itemId, qty, itemDamage);
int maxSize = stack.getMaxStackSize();
// split stack
if (qty > maxSize) {
Connection conn = WebAuctionPlus.dataQueries.getConnection();
PreparedStatement st = null;
while (qty > maxSize) {
try {
if (WebAuctionPlus.isDebug())
WebAuctionPlus.log.info(
"WA Query: getSplitItemStack qty:"
+ Integer.toString(qty)
+ " max:"
+ Integer.toString(maxSize));
st =
conn.prepareStatement(
"INSERT INTO `"
+ WebAuctionPlus.dataQueries.dbPrefix()
+ "Items` ( "
+ "`playerName`, `itemId`, `itemDamage`, `qty`, `enchantments` )VALUES( ?, ?, ?, ?, ? )");
st.setString(1, playerName);
st.setInt(2, itemId);
st.setShort(3, itemDamage);
st.setInt(4, maxSize);
st.setString(5, enchStr);
st.executeUpdate();
} catch (SQLException e) {
WebAuctionPlus.log.warning(
WebAuctionPlus.logPrefix + "Unable to insert new item to inventory!");
e.printStackTrace();
return null;
} finally {
WebAuctionPlus.dataQueries.closeResources(st, null);
}
qty -= maxSize;
}
stack.setAmount(qty);
WebAuctionPlus.dataQueries.closeResources(conn);
}
// add enchantments
if (enchStr != null && !enchStr.isEmpty())
DataQueries.decodeEnchantments(Bukkit.getPlayer(playerName), stack, enchStr);
return stack;
}