/** {@inheritDoc} */
@Override
public void update(Connexion connexion, DTO dto)
throws DAOException, InvalidHibernateSessionException, InvalidDTOException,
InvalidDTOClassException {
if (connexion == null) {
throw new InvalidHibernateSessionException("La connexion ne peut être null");
}
if (dto == null) {
throw new InvalidDTOException("Le DTO ne peut être null");
}
if (!dto.getClass().equals(getDtoClass())) {
throw new InvalidDTOClassException(
"Le DTO doit être de la classe " + getDtoClass().getName());
}
PretDTO pretDTO = (PretDTO) dto;
try (PreparedStatement statementRead =
(connexion.getConnection().prepareStatement(PretDAO.UPDATE_REQUEST))) {
statementRead.setString(1, pretDTO.getMembreDTO().getIdMembre());
statementRead.setString(2, pretDTO.getLivreDTO().getIdLivre());
statementRead.setTimestamp(3, pretDTO.getDatePret());
statementRead.setTimestamp(4, pretDTO.getDateRetour());
statementRead.setString(5, pretDTO.getIdPret());
statementRead.executeUpdate();
} catch (SQLException sqlException) {
throw new DAOException(
Integer.toString(sqlException.getErrorCode()) + " " + sqlException.getMessage(),
sqlException);
}
}
public static void initialize(Properties properties) throws UnknownHostException {
nodeName = properties.getProperty("node.name");
interval = Long.parseLong(properties.getProperty("heartbeat"));
try (final Connection connection = ConnectionAllocator.getConnection()) {
final String updateSql =
"update `daemon-status` ds set ds.daemon = ?, ds.heartbeat = ? where ds.id = ?";
try (final PreparedStatement updateStatement = connection.prepareStatement(updateSql)) {
updateStatement.setString(1, "SMSSenderHeartbeat");
updateStatement.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
updateStatement.setString(3, nodeName);
if (updateStatement.executeUpdate() == 0) {
final String insertSql =
"insert into `daemon-status` (id, daemon, heartbeat) values (?, ?, ?)";
try (final PreparedStatement insertStatement = connection.prepareStatement(insertSql)) {
insertStatement.setString(1, nodeName);
insertStatement.setString(2, "SMSSenderHeartbeat");
insertStatement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
insertStatement.execute();
}
}
}
connection.commit();
} catch (Exception e) {
LOGGER.error("Cannot update heartbeat", e);
}
new HeartbeatDaemon();
}
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));
}
}
}
/**
* Description of the Method
*
* @param db Description of the Parameter
* @throws SQLException Description of the Exception
*/
public void insert(Connection db) throws SQLException {
id = DatabaseUtils.getNextSeq(db, "report_queue_queue_id_seq");
PreparedStatement pst =
db.prepareStatement(
"INSERT INTO report_queue "
+ "("
+ (id > -1 ? "queue_id, " : "")
+ "report_id, entered, enteredby, processed, "
+ "status, filename, filesize, enabled, output_type, email) "
+ "VALUES ("
+ (id > -1 ? "?, " : "")
+ "?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
int i = 0;
if (id > -1) {
pst.setInt(++i, id);
}
pst.setInt(++i, reportId);
pst.setTimestamp(++i, entered);
pst.setInt(++i, enteredBy);
pst.setTimestamp(++i, processed);
DatabaseUtils.setInt(pst, ++i, status);
pst.setString(++i, filename);
DatabaseUtils.setLong(pst, ++i, size);
pst.setBoolean(++i, enabled);
DatabaseUtils.setInt(pst, ++i, outputType);
pst.setBoolean(++i, email);
pst.execute();
pst.close();
id = DatabaseUtils.getCurrVal(db, "report_queue_queue_id_seq", id);
}
// ---------------------------------------------------------------//
public void addTasktoProject(int projectid, Task task, Connection conn) throws SQLException {
PreparedStatement prepStmt = null;
java.util.Date date = new java.util.Date();
Timestamp currentdate = new Timestamp(date.getTime());
conn = select();
String sql =
"INSERT INTO TASKS(TASK_ID,PROJ_ID,TASK_DESCRIPTION,TASK_NOTES,TASK_DEADLINE,TASK_FROM,TASK_TO,TASK_ACTIVE,TASK_TYPE,TASK_USER_NOTES,ROWVERSION,INSERTED_AT,INSERTED_BY,MODIFIED_AT,MODIFIED_BY)"
+ " VALUES(PROJ_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
prepStmt = conn.prepareStatement(sql);
prepStmt.setInt(1, projectid);
prepStmt.setString(2, task.getTask_DESC());
prepStmt.setString(3, task.getTask_NOTES());
prepStmt.setDate(4, task.getTask_DEADLINE());
prepStmt.setDate(5, task.getTask_STARDATE());
prepStmt.setDate(6, task.getTask_ENDDATE());
if (task.isTask_ACTIVE()) prepStmt.setString(7, "Y");
else prepStmt.setString(7, "N");
prepStmt.setString(8, task.getTask_Type());
prepStmt.setString(9, task.getTask_USERNOTES());
prepStmt.setInt(10, 1);
if (task.getTask_INSERTEDAT() != null) prepStmt.setTimestamp(11, currentdate);
else prepStmt.setDate(11, null);
prepStmt.setString(12, "Grigoris");
if (task.getTask_MODIFIEDAT() != null) prepStmt.setTimestamp(13, currentdate);
else prepStmt.setDate(13, null);
if (task.getTask_MODIFIEDAT() != null) prepStmt.setString(14, "Grigoris");
else prepStmt.setString(14, "Grigoris");
prepStmt.executeUpdate();
return;
}
/**
* Description of the Method
*
* @param db Description of the Parameter
* @return Description of the Return Value
* @throws SQLException Description of the Exception
*/
public int update(Connection db) throws SQLException {
int resultCount = 0;
PreparedStatement pst = null;
StringBuffer sql = new StringBuffer();
sql.append(
" UPDATE customer_product "
+ " SET description = ?, "
+ " status_id = ?, "
+ " status_date = ?, "
+ " modified = "
+ DatabaseUtils.getCurrentTimestamp(db)
+ ", "
+ " modifiedby = ?, "
+ " enabled = ? ");
sql.append("WHERE order_id = ? ");
sql.append("AND modified " + ((this.getModified() == null) ? "IS NULL " : "= ? "));
int i = 0;
pst = db.prepareStatement(sql.toString());
pst.setString(++i, this.getDescription());
DatabaseUtils.setInt(pst, ++i, this.getStatusId());
pst.setTimestamp(++i, this.getStatusDate());
pst.setInt(++i, this.getModifiedBy());
pst.setInt(++i, this.getId());
if (this.getModified() != null) {
pst.setTimestamp(++i, this.getModified());
}
pst.setBoolean(++i, this.getEnabled());
resultCount = pst.executeUpdate();
pst.close();
return resultCount;
}
public void adiciona(Utilizacao utilizacao) {
String sql =
"insert into utilizacao "
+ "(matriculaUtilizador,nomeUtilizador,idComputador,enderecoIPV4,dataHoraLogon, dataHoraLogoff)"
+ " values (?,?,?,?,?,?)";
try {
// prepared statement para inserção
PreparedStatement stmt = connection.prepareStatement(sql);
// seta os valores
stmt.setString(1, utilizacao.getMatriculaUtilizador());
stmt.setString(2, utilizacao.getNomeUtilizador());
stmt.setInt(3, utilizacao.getComputador().getIdComputador());
stmt.setString(4, utilizacao.getEnderecoIPV4());
if (utilizacao.getDataHoraLogon() == null) {
stmt.setTimestamp(5, null);
} else {
stmt.setTimestamp(5, new Timestamp(utilizacao.getDataHoraLogon().getTimeInMillis()));
}
if (utilizacao.getDataHoraLogoff() == null) {
stmt.setDate(6, null);
} else {
stmt.setTimestamp(6, new Timestamp(utilizacao.getDataHoraLogoff().getTimeInMillis()));
}
// executa
stmt.execute();
stmt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/** {@inheritDoc} */
@Override
public void add(Connexion connexion, DTO dto)
throws InvalidHibernateSessionException, InvalidDTOException, InvalidDTOClassException,
DAOException {
if (connexion == null) {
throw new InvalidHibernateSessionException("La connexion ne peut être null");
}
if (dto == null) {
throw new InvalidDTOException("Le DTO ne peut être null");
}
if (!dto.getClass().equals(getDtoClass())) {
throw new InvalidDTOClassException("Le DTO doit être un " + getDtoClass().getName());
}
final PretDTO pretDTO = (PretDTO) dto;
try (PreparedStatement addPreparedStatement =
connexion.getConnection().prepareStatement(PretDAO.ADD_REQUEST)) {
addPreparedStatement.setString(1, pretDTO.getMembreDTO().getIdMembre());
addPreparedStatement.setString(2, pretDTO.getLivreDTO().getIdLivre());
addPreparedStatement.setTimestamp(3, pretDTO.getDatePret());
addPreparedStatement.setTimestamp(4, pretDTO.getDateRetour());
addPreparedStatement.executeUpdate();
} catch (final SQLException sqlException) {
throw new DAOException(sqlException);
}
}
public void altera(Utilizacao utilizacao) {
String sql =
"update utilizacao set matriculaUtilizador=?, nomeUtilizador=?, idComputador=?,"
+ "enderecoIPV4=?, dataHoraLogon=?, dataHoraLogoff=? where codUtilizacao=?";
try {
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, utilizacao.getMatriculaUtilizador());
stmt.setString(2, utilizacao.getNomeUtilizador());
stmt.setInt(3, utilizacao.getComputador().getIdComputador());
stmt.setString(4, utilizacao.getEnderecoIPV4());
if (utilizacao.getDataHoraLogon() == null) {
stmt.setTimestamp(5, null);
} else {
stmt.setTimestamp(5, new Timestamp(utilizacao.getDataHoraLogon().getTimeInMillis()));
}
if (utilizacao.getDataHoraLogoff() == null) {
stmt.setDate(6, null);
} else {
stmt.setTimestamp(6, new Timestamp(utilizacao.getDataHoraLogoff().getTimeInMillis()));
}
stmt.setInt(7, utilizacao.getCodUtilizacao());
stmt.execute();
stmt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
protected void setUpdateStatementValues(ChargeSession session, PreparedStatement ps)
throws SQLException {
// cols: auth_status = ?, xid = ?, ended = ?, posted = ?
// sessionid_hi, sessionid_lo
ps.setString(1, session.getStatus() != null ? session.getStatus().toString() : null);
if (session.getTransactionId() == null) {
ps.setNull(2, Types.BIGINT);
} else {
ps.setLong(2, session.getTransactionId().longValue());
}
if (session.getEnded() != null) {
// store ts in UTC time zone
Calendar cal = calendarForDate(session.getEnded());
Timestamp ts = new Timestamp(cal.getTimeInMillis());
ps.setTimestamp(3, ts, cal);
} else {
ps.setNull(3, Types.TIMESTAMP);
}
if (session.getPosted() != null) {
// store ts in UTC time zone
Calendar cal = calendarForDate(session.getPosted());
Timestamp ts = new Timestamp(cal.getTimeInMillis());
ps.setTimestamp(4, ts, cal);
} else {
ps.setNull(4, Types.TIMESTAMP);
}
UUID pk = UUID.fromString(session.getSessionId());
ps.setLong(5, pk.getMostSignificantBits());
ps.setLong(6, pk.getLeastSignificantBits());
}
/**
* 修改用户
*
* @param user
*/
public void updateUser(User user) {
Connection conn = DbUtil.getConnection();
PreparedStatement ps = null;
try {
if (StringUtils.isNotBlank(user.getUserPassword())) {
ps =
conn.prepareStatement(
"update auth_user au SET au.department_id = ? ,au.user_name = ? ,au.user_password = ? ,au.user_real_name = ?,au.use_status = ?,au.create_time = ? WHERE au.id = ?");
ps.setInt(1, user.getDepartmentId());
ps.setString(2, user.getUserName());
ps.setString(3, Secure.encryptPwd(user.getUserPassword()));
ps.setString(4, user.getUserRealName());
ps.setInt(5, user.getUseStatus());
ps.setTimestamp(6, user.getCreateTime());
ps.setInt(7, user.getId());
ps.executeUpdate();
} else {
ps =
conn.prepareStatement(
"update auth_user au SET au.department_id = ? ,au.user_name = ? ,au.user_real_name = ?,au.use_status = ?,au.create_time = ? WHERE au.id = ?");
ps.setInt(1, user.getDepartmentId());
ps.setString(2, user.getUserName());
ps.setString(3, user.getUserRealName());
ps.setInt(4, user.getUseStatus());
ps.setTimestamp(5, user.getCreateTime());
ps.setInt(6, user.getId());
ps.executeUpdate();
}
} catch (Exception e) {
log.error("修改用户信息出错", e);
} finally {
DbUtil.closeConnection(null, ps, conn);
}
}
public int insert(ThesysShipFeeVO vo) throws SQLException {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
int r = 0;
try {
con = getConnection();
String sql =
" INSERT INTO LAPHONE_SHIP_FEE(SITE_ID, FEE_TYPE, COND_END, FEE_AMT, CRT_USR_ID, CRT_DATE, LM_USR_ID, LM_DATE) "
+ " VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
stmt = con.prepareStatement(sql);
int idx = 1;
stmt.setString(idx++, vo.getSiteId());
stmt.setInt(idx++, vo.getFeeType());
stmt.setInt(idx++, vo.getConditionEnd());
stmt.setInt(idx++, vo.getFeeAmount());
stmt.setString(idx++, vo.getCreater());
stmt.setTimestamp(idx++, convert(vo.getCreateDate()));
stmt.setString(idx++, vo.getLastUpdater());
stmt.setTimestamp(idx++, convert(vo.getLastUpdatedDate()));
r = stmt.executeUpdate();
} finally {
closeAll(con, stmt, rs);
}
return r;
}
public int getIdFromRehearsalRequest(Band band, RehearsalRequest rehearsalRequest) {
int id = -1;
try {
PreparedStatement stmt =
conn.prepareStatement(
"select id from rehearsal_requests where band_id = ? and start_time = ? and end_time = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1, band.getId());
stmt.setTimestamp(2, new java.sql.Timestamp(rehearsalRequest.getStartTime().getTime()));
stmt.setTimestamp(3, new java.sql.Timestamp(rehearsalRequest.getEndTime().getTime()));
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
id = rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return id;
}
public void fillStatement(PreparedStatement stmt, List params) throws SQLException {
if (params == null || params.size() == 0) {
return;
}
for (int i = 0; i < params.size(); i++) {
Object obj = params.get(i);
if (obj != null) {
if (obj instanceof java.sql.Date) {
java.sql.Date sqlDate = (java.sql.Date) obj;
stmt.setDate(i + 1, sqlDate);
} else if (obj instanceof java.sql.Time) {
java.sql.Time sqlTime = (java.sql.Time) obj;
stmt.setTime(i + 1, sqlTime);
} else if (obj instanceof java.sql.Timestamp) {
Timestamp datetime = (Timestamp) obj;
stmt.setTimestamp(i + 1, datetime);
} else if (obj instanceof java.util.Date) {
Timestamp datetime = DateTools.toTimestamp((java.util.Date) obj);
stmt.setTimestamp(i + 1, datetime);
} else {
stmt.setObject(i + 1, obj);
}
} else {
stmt.setString(i + 1, null);
}
}
}
public void addMusician(Musician m) {
try {
PreparedStatement stmt =
conn.prepareStatement(
"insert into musicians values(0, ?, ?, ?, ?, ?, ?)",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setString(1, m.getUsername());
stmt.setString(2, m.getPassword());
stmt.setString(3, m.getFirstName());
stmt.setString(4, m.getLastName());
if (m.getBirthdate() == null) {
stmt.setTimestamp(5, null);
} else {
stmt.setTimestamp(5, new java.sql.Timestamp(m.getBirthdate().getTime()));
}
if (m.getHabitation() == null) {
stmt.setNull(6, java.sql.Types.INTEGER);
} else {
stmt.setInt(6, m.getHabitation().getId());
}
stmt.executeUpdate();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
public void save(Connection conn) throws SQLException {
boolean is_new = isNew();
String query =
(is_new
? ("insert into "
+ tableName()
+ " ("
+ recordIDColumnName()
+ ",mod_count,date_entered,date_modified,name,status,product) values (?,?,?,?,?,?,?)")
: ("update "
+ tableName()
+ " set mod_count = ?, date_entered = ?, "
+ "date_modified = ?, name = ?, status = ?, product = ? where "
+ recordIDColumnName()
+ " = ?"));
int idx = 1;
PreparedStatement ps = conn.prepareStatement(query);
if (is_new) {
ps.setInt(idx++, getId().intValue());
}
int new_mod_count = mod_count.intValue() + 1;
ps.setInt(idx++, new_mod_count);
ps.setTimestamp(idx++, date_entered);
ps.setTimestamp(idx++, date_modified); // ---TODO: set to current time
ps.setString(idx++, getName());
ps.setString(idx++, getStatus());
ps.setInt(idx++, getProduct().getId().intValue());
if (!is_new) {
ps.setInt(idx++, getId().intValue());
}
ps.executeUpdate();
ps.close();
// Only increment the mod_count if there was no exception during the save.
setModCount(new Integer(new_mod_count));
}
/**
* Updates the batch progress level with the given batch progress. This will update only End Date
* Time field (end_date_time) for the given batch number and batch revision number.
*
* @param batchProgress The batch progress
* @return 1 if the record was inserted in the table 0 otherwise
* @throws CommDatabaseException Any database I/O exception
*/
public Integer updateBatchProgress(BatchProgress batchProgress, Connection connection)
throws CommDatabaseException {
PreparedStatement pstmt = null;
try {
connection.setAutoCommit(false);
pstmt = connection.prepareStatement(UPDATE_PROGRESS_LEVEL);
Long objendDateTime = batchProgress.getEndDatetime();
if (objendDateTime != null)
pstmt.setTimestamp(1, new Timestamp(batchProgress.getEndDatetime()));
else pstmt.setTimestamp(1, null);
pstmt.setObject(2, batchProgress.getBatchNo());
pstmt.setObject(3, batchProgress.getBatchRevNo());
pstmt.setInt(4, batchProgress.getIndicatorNo());
int iUpdate = pstmt.executeUpdate();
connection.commit();
return iUpdate;
} catch (SQLException sqe) {
throw new CommDatabaseException(sqe);
} finally {
try {
connection.setAutoCommit(true);
} catch (SQLException e) {
}
releaseResources(null, pstmt, null);
}
}
public ResultSet getForMetricId(
String metricId, java.sql.Timestamp startTime, java.sql.Timestamp endTime) {
String query =
SELECT
+ "from data_point this\n where\n this.\"metric_id\" = ?\n and this.\"timestamp\" >= ?\n and this.\"timestamp\" <= ?\n order by this.\"timestamp\"";
java.sql.PreparedStatement genorm_statement = null;
try {
genorm_statement = GenOrmDataSource.prepareStatement(query);
genorm_statement.setString(1, metricId);
genorm_statement.setTimestamp(2, startTime);
genorm_statement.setTimestamp(3, endTime);
s_logger.debug(genorm_statement.toString());
ResultSet rs = new SQLResultSet(genorm_statement.executeQuery(), query, genorm_statement);
return (rs);
} catch (java.sql.SQLException sqle) {
try {
if (genorm_statement != null) genorm_statement.close();
} catch (java.sql.SQLException sqle2) {
}
if (s_logger.isDebug()) sqle.printStackTrace();
throw new GenOrmException(sqle);
}
}
/**
* Inserts a new instance of the data element. The store method needs to guarantee that the data
* object is of type Committee.
*
* @param committee the instance to create. @ if there is a problem inserting.
*/
protected void insert(Committee committee) {
Connection conn = null;
PreparedStatement ps = null;
int id =
(committee.isRemote())
? committee.getEntryId().intValue()
: sequenceGenerator.getNextSequenceNumber("entry");
try {
conn = getDataSource().getConnection();
ps = conn.prepareStatement(INSERT_QUERY);
logService.debug("Preparing insert statement " + INSERT_QUERY);
ps.setString(1, committee.getPersonId());
ps.setInt(2, id);
committee.setEntryId(String.valueOf(id));
ps.setString(3, committee.getEntryName());
if (committee.getFromDate() == null) ps.setNull(4, Types.TIMESTAMP);
else ps.setTimestamp(4, new java.sql.Timestamp(committee.getFromDate().getTime()));
if (committee.getToDate() == null) ps.setNull(5, Types.TIMESTAMP);
else ps.setTimestamp(5, new java.sql.Timestamp(committee.getToDate().getTime()));
ps.setString(6, committee.getDescription());
int result = ps.executeUpdate();
logService.debug("There were " + result + " rows inserted.");
} catch (SQLException sqle) {
throw new DataAccessException(sqle);
} finally {
close(conn, ps, null);
}
}
// ---------------------------------------------------------------//
public void updateTasktoProject(Task task, Connection conn) throws SQLException {
PreparedStatement prepStmt = null;
java.util.Date date = new java.util.Date();
Timestamp currentdate = new Timestamp(date.getTime());
conn = select();
String sql =
"UPDATE TASKS SET TASK_DESCRIPTION=?,TASK_NOTES=?,TASK_DEADLINE=?,TASK_FROM=?,TASK_TO=?,TASK_ACTIVE=?,TASK_TYPE=?,TASK_USER_NOTES=?,ROWVERSION=+ROWVERSION+1,INSERTED_AT=?,INSERTED_BY=?,MODIFIED_AT=?,MODIFIED_BY=?"
+ " WHERE TASK_ID=?";
prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, task.getTask_DESC());
prepStmt.setString(2, task.getTask_NOTES());
prepStmt.setDate(3, task.getTask_DEADLINE());
prepStmt.setDate(4, task.getTask_STARDATE());
prepStmt.setDate(5, task.getTask_ENDDATE());
if (task.isTask_ACTIVE()) prepStmt.setString(6, "Y");
else prepStmt.setString(6, "N");
prepStmt.setString(7, task.getTask_Type());
prepStmt.setString(8, task.getTask_USERNOTES());
if (task.getTask_INSERTEDAT() != null) prepStmt.setTimestamp(9, currentdate);
else prepStmt.setDate(9, null);
prepStmt.setString(10, "Grigoris");
if (task.getTask_MODIFIEDAT() != null) prepStmt.setTimestamp(11, currentdate);
else prepStmt.setDate(11, null);
prepStmt.setString(12, "Grigoris");
prepStmt.setInt(13, task.getTask_ID());
prepStmt.executeUpdate();
return;
}
@Override
public boolean insert(List<Message> messages) {
boolean success = true;
try (PreparedStatement st = connection.prepareStatement(INSERT_MESSAGE); ) {
connection.setAutoCommit(false);
for (Message message : messages) {
st.setString(1, message.getNumber());
st.setString(2, message.getContents());
st.setTimestamp(3, new Timestamp(message.getSentDate().getTime()));
st.setTimestamp(4, new Timestamp(message.getReceivedDate().getTime()));
st.addBatch();
}
st.executeBatch();
connection.commit();
} catch (SQLException ex) {
success = false;
logger.error("Error inserting message", ex);
try {
connection.rollback();
} catch (SQLException ex1) {
logger.error("Error rolling back", ex1);
}
} finally {
try {
connection.setAutoCommit(true);
} catch (SQLException ex) {
logger.error("Error setting auto commit", ex);
}
}
return success;
}
/** Update plays in base */
@Override
public boolean updatePlays(String playName, Timestamp date) throws DAOException {
boolean isUpdate = false;
PreparedStatement psSelect = null;
PreparedStatement psUpdate = null;
ResultSet rs = null;
try {
psSelect = DBConnection.getConnection().prepareStatement(ConstantsQuery.CHECK_PLAYS);
psSelect.setString(1, playName);
psSelect.setTimestamp(2, date);
rs = psSelect.executeQuery();
psUpdate = DBConnection.getConnection().prepareStatement(ConstantsQuery.ADD_PLAYS);
psUpdate.setString(1, playName);
psUpdate.setTimestamp(2, date);
if (!rs.next()) {
psUpdate.executeUpdate();
}
return isUpdate;
} catch (SQLException e) {
throw new DAOException(Constants.ERROR);
} finally {
DBConnection.closeResultSet(rs);
DBConnection.closeStatement(psSelect, psUpdate);
}
}
/**
* Creates the group in the database using the given group instance. The group instance can
* include sub items such as sections and questions. Those sub items will be persisted as well.
* The operator parameter is used as the creation/modification user of the group and its subitems.
* The creation date and modification date will be the current date time when the group is
* created.
*
* @param group The group instance to be created in the database.
* @param order the sort order of this group.
* @param operator The creation user of this group.
* @param parentId The id of the scorecard that contains this.
* @throws IllegalArgumentException if any input is null or the operator is empty string.
* @throws PersistenceException if error occurred while accessing the database.
*/
public void createGroup(Group group, int order, String operator, long parentId)
throws PersistenceException {
if (group == null) {
throw new IllegalArgumentException("group cannot be null.");
}
if (operator == null) {
throw new IllegalArgumentException("operator cannot be null.");
}
if (operator.trim().length() == 0) {
throw new IllegalArgumentException("operator cannot be empty String.");
}
logger.log(
Level.INFO,
new LogMessage(
"Group",
null,
operator,
"create new Group with order:" + order + " and parentId:" + parentId));
// get next id
long groupId = DBUtils.nextId(IdGeneratorUtility.getGroupIdGenerator());
Timestamp time = new Timestamp(System.currentTimeMillis());
// create section persistence
InformixSectionPersistence sectionPersistence = new InformixSectionPersistence(connection);
PreparedStatement pstmt = null;
logger.log(Level.INFO, "insert record into scorecard_group with group_id:" + groupId);
try {
// create statement
pstmt = connection.prepareStatement(INSERT_SCORECARD_GROUP);
// set the variables
pstmt.setLong(1, groupId);
pstmt.setLong(2, parentId);
pstmt.setString(3, group.getName());
pstmt.setFloat(4, group.getWeight());
pstmt.setInt(5, order);
pstmt.setString(6, operator);
pstmt.setTimestamp(7, time);
pstmt.setString(8, operator);
pstmt.setTimestamp(9, time);
// create group and create the sections
pstmt.executeUpdate();
sectionPersistence.createSections(group.getAllSections(), operator, groupId);
} catch (SQLException ex) {
logger.log(
Level.ERROR,
new LogMessage("Group", new Long(groupId), operator, "Fail to create Group.", ex));
throw new PersistenceException("Error occur while creating the scorecard group.", ex);
} finally {
DBUtils.close(pstmt);
}
// set the group id.
group.setId(groupId);
}
/**
* Insert transportation routes
*
* @param transportationRouteList List of transportation routes to insert (id == null)
* @throws Exception
*/
public void insertTransportationRoutes(Collection<TransportationRoute> transportationRouteList)
throws Exception {
try {
String query =
String.format(
"INSERT INTO %s (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
table,
id,
name,
validFrom,
validUntil,
transportationID,
operator,
network,
extRef,
description,
descriptionFrom,
descriptionTo,
routeNo);
PreparedStatement ps = ConnectionManager.getInstance().prepareStatement(query);
for (TransportationRoute transportationRoute : transportationRouteList) {
if (transportationRoute.getId() != null || transportationRoute.getType() == null) {
continue;
}
transportationRoute.setId(UUID.randomUUID());
PGobject toInsertUUID = new PGobject();
toInsertUUID.setType("uuid");
toInsertUUID.setValue(String.valueOf(transportationRoute.getId()));
ps.setObject(1, toInsertUUID);
ps.setString(2, transportationRoute.getName());
ps.setTimestamp(3, transportationRoute.getValidFrom());
ps.setTimestamp(4, transportationRoute.getValidUntil());
ps.setObject(5, transportationRoute.getType().getId());
ps.setString(6, transportationRoute.getOperator());
ps.setString(7, transportationRoute.getNetwork());
ps.setString(8, transportationRoute.getExtRef());
ps.setString(9, transportationRoute.getDescription());
ps.setString(10, transportationRoute.getDescriptionFrom());
ps.setString(11, transportationRoute.getDescriptionTo());
ps.setString(12, transportationRoute.getRouteNo());
ps.addBatch();
}
ps.executeBatch();
ps.close();
ScheduleDAO scheduleDAO = new ScheduleDAO();
scheduleDAO.insertOrUpdateSchedules(transportationRouteList);
} catch (Exception e) {
ConnectionManager.getInstance().closeConnection(true);
throw new Exception("Transportation route insert failed: " + e.getMessage());
}
}
@Override
public int add(AbstractBean ab) {
LeadBean leadBean = (LeadBean) ab;
String query =
"insert into tbl_crm_lead values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
try {
preparedStatement = DBUtility.connection.prepareStatement(query);
preparedStatement.setInt(1, leadBean.getLeadId());
preparedStatement.setString(2, leadBean.getSubjectName());
preparedStatement.setString(3, leadBean.getLeadDescription());
preparedStatement.setString(4, leadBean.getContactName());
preparedStatement.setString(5, leadBean.getCompanyId());
preparedStatement.setString(6, leadBean.getValuation());
preparedStatement.setString(7, leadBean.getLeadState());
preparedStatement.setString(8, leadBean.getLeadClassId());
preparedStatement.setString(9, leadBean.getPartnerName());
preparedStatement.setString(10, leadBean.getPhone());
preparedStatement.setString(11, leadBean.getFax());
preparedStatement.setString(12, leadBean.getEmail());
preparedStatement.setString(13, leadBean.getWorkPhone());
preparedStatement.setString(14, leadBean.getMobile());
preparedStatement.setString(15, leadBean.getStreet1());
preparedStatement.setString(16, leadBean.getStreet2());
preparedStatement.setString(17, leadBean.getCity());
preparedStatement.setString(18, leadBean.getZip());
preparedStatement.setString(19, leadBean.getStateId());
preparedStatement.setString(20, leadBean.getCountryId());
preparedStatement.setString(21, leadBean.getType());
preparedStatement.setString(22, leadBean.getDateOpen());
preparedStatement.setString(23, leadBean.getDateClose());
preparedStatement.setString(24, leadBean.getExpectedDateClose());
preparedStatement.setString(25, leadBean.getStageId());
preparedStatement.setString(26, leadBean.getProbability());
preparedStatement.setString(27, leadBean.getChannelId());
preparedStatement.setString(28, leadBean.getSectionId());
preparedStatement.setString(29, leadBean.getCategoryId());
preparedStatement.setString(30, leadBean.getDayClose());
preparedStatement.setString(31, leadBean.getDayOpen());
preparedStatement.setString(32, leadBean.getReferredBy());
preparedStatement.setString(33, leadBean.getUserId());
preparedStatement.setString(34, leadBean.getPriorityId());
preparedStatement.setString(35, leadBean.getNextActionDate());
preparedStatement.setString(36, leadBean.getNextAction());
preparedStatement.setBoolean(37, leadBean.isStatus());
preparedStatement.setString(38, leadBean.getCreateBy());
preparedStatement.setTimestamp(39, new Timestamp(System.currentTimeMillis()));
preparedStatement.setString(40, leadBean.getWriteBy());
preparedStatement.setTimestamp(41, new Timestamp(System.currentTimeMillis()));
} catch (SQLException e) {
ZLog.err("VNC CRM for Zimbra", "Error in insert operation in LeadHelper", e);
}
operationStatus = dbu.insert(preparedStatement);
if (operationStatus == 1) {
return Notification.record_saved;
} else {
return Notification.record_not_saved;
}
}
/** 插入子单信息 */
private void insertItemSo(Connection con, JumpMQOrderItemVo soItemVo) {
PreparedStatement st = null;
String sql =
"insert into edw1_dev.REALTIME_METADATA_SO_ITEM (ID, ENDUSERID, ORDERID, PRODUCTID, MERCHANTID, ORDERITEMAMOUNT, ORDERITEMPRICE, ORDERITEMNUM, PARENTSOITEMID, ISITEMLEAF, DELIVERYFEEAMOUNT, PROMOTIONAMOUNT, COUPONAMOUNT, CREATETIME, RULE_TYPE, UPDT_TIME) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
try {
st = con.prepareStatement(sql);
st.setLong(1, soItemVo.getId());
st.setLong(2, soItemVo.getEndUserId());
st.setLong(3, soItemVo.getOrderId());
st.setLong(4, soItemVo.getProductId());
st.setLong(5, soItemVo.getMerchantId());
if (soItemVo.getOrderItemAmount() != null) {
st.setDouble(6, soItemVo.getOrderItemAmount().doubleValue());
} else {
st.setDouble(6, 0.0);
}
if (soItemVo.getOrderItemPrice() != null) {
st.setDouble(7, soItemVo.getOrderItemPrice().doubleValue());
} else {
st.setDouble(7, 0.0);
}
st.setInt(8, soItemVo.getOrderItemNum());
st.setLong(9, soItemVo.getParentSoItemId());
st.setInt(10, soItemVo.getIsItemLeaf());
if (soItemVo.getDeliveryFeeAmount() != null) {
st.setDouble(11, soItemVo.getDeliveryFeeAmount().doubleValue());
} else {
st.setDouble(11, 0.0);
}
if (soItemVo.getPromotionAmount() != null) {
st.setDouble(12, soItemVo.getPromotionAmount().doubleValue());
} else {
st.setDouble(12, 0.0);
}
if (soItemVo.getCouponAmount() != null) {
st.setDouble(13, soItemVo.getCouponAmount().doubleValue());
} else {
st.setDouble(13, 0.0);
}
if (soItemVo.getCreateTime() != null) {
st.setTimestamp(14, new Timestamp(soItemVo.getCreateTime().getTime()));
} else {
st.setTimestamp(14, new Timestamp(new Date().getTime()));
}
st.setInt(15, soItemVo.getRuleType());
st.setTimestamp(16, new Timestamp(new Date().getTime()));
st.executeQuery();
} catch (Exception e) {
logger.info("error: Metadata item insert error ! ");
} finally {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* This method creates the groups instances in the datastore. It has the same behavior as {@link
* #createGroup(Group, int, String, long)} except it executes insert at once.
*
* @param groups the groups to be created.
* @param operator the creation operator.
* @param parentId the id of the parent scorecard.
* @throws PersistenceException if error occurred while accessing the database.
*/
void createGroups(Group[] groups, String operator, long parentId) throws PersistenceException {
logger.log(
Level.INFO,
new LogMessage("Group", null, operator, "create new Groups with parentId:" + parentId));
// generate the ids
long[] groupIds =
DBUtils.generateIdsArray(groups.length, IdGeneratorUtility.getGroupIdGenerator());
Timestamp time = new Timestamp(System.currentTimeMillis());
// create sections persistence
InformixSectionPersistence sectionPersistence = new InformixSectionPersistence(connection);
PreparedStatement pstmt = null;
try {
pstmt = connection.prepareStatement(INSERT_SCORECARD_GROUP);
// set the id of the parent
pstmt.setLong(2, parentId);
// for each group - set the variables
for (int i = 0; i < groups.length; i++) {
pstmt.setLong(1, groupIds[i]);
pstmt.setString(3, groups[i].getName());
pstmt.setFloat(4, groups[i].getWeight());
pstmt.setInt(5, i);
pstmt.setString(6, operator);
pstmt.setTimestamp(7, time);
pstmt.setString(8, operator);
pstmt.setTimestamp(9, time);
// execute the update and creates sections of this group
pstmt.executeUpdate();
logger.log(
Level.INFO, "insert record into scorecard_group table with groupId:" + groupIds[i]);
sectionPersistence.createSections(groups[i].getAllSections(), operator, groupIds[i]);
}
} catch (SQLException ex) {
logger.log(
Level.INFO,
new LogMessage(
"Group",
null,
operator,
"Failed to create new Groups with parentId:" + parentId,
ex));
throw new PersistenceException("Error occur while creating the scorecard group.", ex);
} finally {
DBUtils.close(pstmt);
}
// set ids to groups
for (int i = 0; i < groups.length; i++) {
groups[i].setId(groupIds[i]);
}
}
/**
* Description of the Method
*
* @param db Description of the Parameter
* @return Description of the Return Value
* @throws SQLException Description of the Exception
*/
public boolean insert(Connection db) throws SQLException {
boolean result = false;
StringBuffer sql = new StringBuffer();
id = DatabaseUtils.getNextSeq(db, "customer_product_customer_product_id_seq");
sql.append(
"INSERT INTO customer_product (org_id, order_id, order_item_id, description, "
+ " status_id, status_date, ");
if (id > -1) {
sql.append("customer_product_id, ");
}
sql.append("entered, ");
sql.append("enteredby, ");
sql.append("modified, ");
sql.append("modifiedby, enabled) ");
sql.append("VALUES( ?, ?, ?, ?, ?, ?, ");
if (id > -1) {
sql.append("?, ");
}
if (entered != null) {
sql.append("?, ");
} else {
sql.append(DatabaseUtils.getCurrentTimestamp(db) + ", ");
}
sql.append("?, ");
if (modified != null) {
sql.append("?, ");
} else {
sql.append(DatabaseUtils.getCurrentTimestamp(db) + ", ");
}
sql.append("?, ? )");
int i = 0;
PreparedStatement pst = db.prepareStatement(sql.toString());
pst.setInt(++i, this.getOrgId());
DatabaseUtils.setInt(pst, ++i, this.getOrderId());
DatabaseUtils.setInt(pst, ++i, this.getOrderItemId());
pst.setString(++i, this.getDescription());
DatabaseUtils.setInt(pst, ++i, this.getStatusId());
pst.setTimestamp(++i, this.getStatusDate());
if (id > -1) {
pst.setInt(++i, id);
}
if (entered != null) {
pst.setTimestamp(++i, this.getEntered());
}
pst.setInt(++i, this.getEnteredBy());
if (modified != null) {
pst.setTimestamp(++i, this.getModified());
}
pst.setInt(++i, this.getModifiedBy());
pst.setBoolean(++i, this.getEnabled());
pst.execute();
pst.close();
id = DatabaseUtils.getCurrVal(db, "customer_product_customer_product_id_seq", id);
result = true;
return result;
}
public void alterarCampanha(Campanha campanha) throws SQLException {
PreparedStatement stmtAlterar = null;
try {
try {
connection = ConnectionFactory.getConnection();
} catch (ClassNotFoundException ex) {
}
stmtAlterar =
connection.prepareStatement(
"update campanha set titulo = ?, dtInicio = ?, dtFim = ?, "
+ "aPositivo = ?, aNegativo = ?, bPositivo = ?, bNegativo = ?, oPositivo = ?, oNegativo = ?, "
+ "abPositivo = ?, abNegativo = ?, tipo = ?, outros = ?, descricao = ?, sexo = ? , nomeImagem = ?, "
+ " idAtivo = ?, caminhoImagem = ?, legendaImagem = ? where idCampanha = ?");
stmtAlterar.setString(1, campanha.getTitulo());
stmtAlterar.setTimestamp(2, campanha.getDtInicio());
stmtAlterar.setTimestamp(3, campanha.getDtFim());
stmtAlterar.setBoolean(4, campanha.isaPositivo());
stmtAlterar.setBoolean(5, campanha.isaNegativo());
stmtAlterar.setBoolean(6, campanha.isbPositivo());
stmtAlterar.setBoolean(7, campanha.isbNegativo());
stmtAlterar.setBoolean(8, campanha.isoPositivo());
stmtAlterar.setBoolean(9, campanha.isoNegativo());
stmtAlterar.setBoolean(10, campanha.isAbPositivo());
stmtAlterar.setBoolean(11, campanha.isAbNegativo());
stmtAlterar.setInt(12, campanha.getTipo());
stmtAlterar.setString(13, campanha.getOutros());
stmtAlterar.setString(14, campanha.getDescricao());
stmtAlterar.setString(15, campanha.getSexo());
stmtAlterar.setString(16, campanha.getNomeImagem());
stmtAlterar.setBoolean(17, campanha.isAtivo());
stmtAlterar.setString(18, campanha.getCaminhoImagem());
stmtAlterar.setString(19, campanha.getLegendaImagem());
// stmtAlterar.setInt(20, campanha.getUsuario().getId());
stmtAlterar.setInt(20, campanha.getId());
stmtAlterar.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException("Erro ao alterar campanha. Origem: " + e.getMessage());
} finally {
try {
stmtAlterar.close();
} catch (SQLException ex) {
System.out.println("Erro ao fechar stmt. Ex:" + ex.getMessage());
}
;
try {
connection.close();
} catch (SQLException ex) {
System.out.println("Erro ao fechar conexão. Ex:" + ex.getMessage());
}
;
}
}
private void checkPrepareBindExecuteFetchDate(Connection connection) throws Exception {
final String sql0 = "select cast(? as varchar(20)) as c\n" + "from (values (1, 'a'))";
final String sql1 = "select ? + interval '2' day as c from (values (1, 'a'))";
final Date date = Date.valueOf("2015-04-08");
final long time = date.getTime();
PreparedStatement ps;
ParameterMetaData parameterMetaData;
ResultSet resultSet;
ps = connection.prepareStatement(sql0);
parameterMetaData = ps.getParameterMetaData();
assertThat(parameterMetaData.getParameterCount(), equalTo(1));
ps.setDate(1, date);
resultSet = ps.executeQuery();
assertThat(resultSet.next(), is(true));
assertThat(resultSet.getString(1), is("2015-04-08"));
ps.setTimestamp(1, new Timestamp(time));
resultSet = ps.executeQuery();
assertThat(resultSet.next(), is(true));
assertThat(resultSet.getString(1), is("2015-04-08 00:00:00.0"));
ps.setTime(1, new Time(time));
resultSet = ps.executeQuery();
assertThat(resultSet.next(), is(true));
assertThat(resultSet.getString(1), is("00:00:00"));
ps.close();
ps = connection.prepareStatement(sql1);
parameterMetaData = ps.getParameterMetaData();
assertThat(parameterMetaData.getParameterCount(), equalTo(1));
ps.setDate(1, date);
resultSet = ps.executeQuery();
assertTrue(resultSet.next());
assertThat(resultSet.getDate(1), equalTo(new Date(time + TimeUnit.DAYS.toMillis(2))));
assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
ps.setTimestamp(1, new Timestamp(time));
resultSet = ps.executeQuery();
assertTrue(resultSet.next());
assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
ps.setObject(1, new java.util.Date(time));
resultSet = ps.executeQuery();
assertTrue(resultSet.next());
assertThat(resultSet.getDate(1), equalTo(new Date(time + TimeUnit.DAYS.toMillis(2))));
assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
resultSet.close();
ps.close();
connection.close();
}