@Test
public void testTypeMismatchToDateFunctionBind() throws Exception {
printTestDescription();
initATableValues();
try {
String query = null;
if (tgtPH())
query =
"SELECT a_date FROM atable WHERE organization_id='"
+ tenantId
+ "' and a_date < TO_DATE(?)";
else if (tgtSQ() || tgtTR())
query = "SELECT a_date FROM atable WHERE organization_id='" + tenantId + "' and a_date < ?";
PreparedStatement statement = conn.prepareStatement(query);
statement.setDate(1, new Date(2));
statement.executeQuery();
if (tgtPH()) fail();
} catch (SQLException e) {
if (tgtPH())
assertTrue(
e.getMessage().contains("Type mismatch. expected: [VARCHAR] but was: DATE at TO_DATE"));
} finally {
}
}
@Override
public int registraOrdine(Ordine ordine) throws PersistenceException { // ok
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
int i;
if (this.getOrdineByCodice(ordine.getCodiceOrdine()) != null)
throw new PersistenceException("Ordine gia presente");
try {
String str = "insert into ordini (id,codice,data,stato,cliente) values (?,?,?,?,?)";
statement = connection.prepareStatement(str);
IdBroker id = new IdBrokerPostgres();
i = id.getId();
statement.setInt(1, i);
statement.setString(
2, new ClienteDAOImpl().getClienteById(ordine.getCliente().getId()).getNome() + i);
statement.setDate(3, new java.sql.Date(ordine.getData().getTime()));
statement.setString(4, ordine.getStato());
statement.setInt(5, ordine.getCliente().getId());
statement.executeUpdate();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
} finally {
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
}
}
return i;
}
public static void setDate(PreparedStatement stm, int i, Date date, boolean isNull)
throws SQLException {
if (isNull) {
stm.setNull(i, Types.DATE);
} else {
stm.setDate(i, new java.sql.Date(date.getTime()));
}
}
public void atualizar(Oriundo oriundo) throws SQLException {
Connection con =
DriverManager.getConnection(
new conexao().url,
new conexao().config.getString("usuario"),
new conexao().config.getString("senha"));
PreparedStatement ps = null;
String sqlAtualizar =
"UPDATE oriundo SET descricao=?, data_cadastro=?, dia_fechamento=?, dia_pag=? WHERE codigo=?";
try {
ps = con.prepareStatement(sqlAtualizar);
ps.setString(1, oriundo.getDescricao());
ps.setDate(2, oriundo.getData_cadastro());
ps.setInt(3, oriundo.getDia_fechamento());
ps.setInt(4, oriundo.getDia_pag());
ps.setInt(5, oriundo.getCodigo());
ps.executeUpdate();
JOptionPane.showMessageDialog(
null, "Atualizado Com Sucesso: ", "Mensagem do Sistema - Atualizar", 1);
} catch (NumberFormatException e) {
JOptionPane.showMessageDialog(
null, "NumberFormaterExeption Erro: " + e.getMessage(), "ClasseDAO Func.Atualizar", 0);
e.printStackTrace();
} catch (NullPointerException e) {
JOptionPane.showMessageDialog(
null, "NullPointerException Erro: " + e.getMessage(), "ClasseDAO Func. Atualizar", 0);
e.printStackTrace();
} catch (SQLException e) {
JOptionPane.showMessageDialog(
null, "SQLException Erro: " + e.getMessage(), "ClasseDAO Func. Atualizar", 0);
e.printStackTrace();
} catch (Exception e) {
JOptionPane.showMessageDialog(
null, "Exception Erro: " + e.getMessage(), "ClasseDAO Func. Atualizar", 0);
e.printStackTrace();
} finally {
ps.close();
con.close();
}
}
public void salvar(Oriundo oriundo) throws SQLException {
Connection con =
DriverManager.getConnection(
new conexao().url,
new conexao().config.getString("usuario"),
new conexao().config.getString("senha"));
PreparedStatement ps = null;
String sqlSalvar =
"INSERT INTO oriundo (descricao, data_cadastro, dia_fechamento, dia_pag ) VALUES (?, ?, ?, ?)";
try {
ps = con.prepareStatement(sqlSalvar);
ps.setString(1, oriundo.getDescricao());
ps.setDate(2, oriundo.getData_cadastro());
ps.setInt(3, oriundo.getDia_fechamento());
ps.setInt(4, oriundo.getDia_pag());
ps.executeUpdate();
// JOptionPane.showMessageDialog(null, "Inserido Com Sucesso: ", "Mensagem do Sistema -
// Salvar", 1);
} catch (NumberFormatException e) {
JOptionPane.showMessageDialog(
null, "NumberFormaterExeption Erro: " + e.getMessage(), "ClasseDAO Func.Salvar", 0);
e.printStackTrace();
} catch (NullPointerException e) {
JOptionPane.showMessageDialog(
null, "NullPointerException Erro: " + e.getMessage(), "ClasseDAO Func. Salvar", 0);
e.printStackTrace();
} catch (SQLException e) {
JOptionPane.showMessageDialog(
null, "SQLException Erro: " + e.getMessage(), "ClasseDAO Func. Salvar", 0);
e.printStackTrace();
} catch (Exception e) {
JOptionPane.showMessageDialog(
null, "Exception Erro: " + e.getMessage(), "ClasseDAO Func. Salvar", 0);
e.printStackTrace();
} finally {
ps.close();
con.close();
}
}
/**
* Inserta el registro dado por la entidad vData.
*
* <p><b> insert into
* TRATiempoTraslado(iCveTramite,iCveModalidad,iCveOficinaOrigen,iCveOficinaDestino,dtIniVigencia,iNumDiasTraslado,lDiasNaturalesTraslado)
* values (?,?,?,?,?,?,?) </b>
*
* <p><b> Campos Llave:
* iCveTramite,iCveModalidad,iCveOficinaOrigen,iCveOficinaDestino,dtIniVigencia, </b>
*
* @param vData TVDinRep - VO Dinámico que contiene a la entidad a Insertar.
* @param cnNested Connection - Conexión anidada que permite que el método se encuentre dentro de
* una transacción mayor.
* @throws DAOException - Excepción de tipo DAO
* @return TVDinRep - VO Dinámico que contiene a la entidad a Insertada, así como a la llave de
* esta entidad.
*/
public TVDinRep insert(TVDinRep vData, Connection cnNested) throws DAOException {
DbConnection dbConn = null;
Connection conn = cnNested;
PreparedStatement lPStmt = null;
boolean lSuccess = true;
String cMsg = "";
try {
if (cnNested == null) {
dbConn = new DbConnection(dataSourceName);
conn = dbConn.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(2);
}
String lSQL =
"insert into TRATiempoTraslado(iCveTramite,iCveModalidad,iCveOficinaOrigen,iCveOficinaDestino,dtIniVigencia,iNumDiasTraslado,lDiasNaturalesTraslado) values (?,?,?,?,?,?,?)";
// AGREGAR AL ULTIMO ...
vData.addPK(vData.getString("iCveTramite"));
vData.addPK(vData.getString("iCveModalidad"));
vData.addPK(vData.getString("iCveOficinaOrigen"));
vData.addPK(vData.getString("iCveOficinaDestino"));
vData.addPK(vData.getString("dtIniVigencia"));
// ...
lPStmt = conn.prepareStatement(lSQL);
lPStmt.setInt(1, vData.getInt("iCveTramite"));
lPStmt.setInt(2, vData.getInt("iCveModalidad"));
lPStmt.setInt(3, vData.getInt("iCveOficinaOrigen"));
lPStmt.setInt(4, vData.getInt("iCveOficinaDestino"));
lPStmt.setDate(5, vData.getDate("dtIniVigencia"));
lPStmt.setInt(6, vData.getInt("iNumDiasTraslado"));
lPStmt.setInt(7, vData.getInt("lDiasNaturalesTraslado"));
lPStmt.executeUpdate();
if (cnNested == null) {
conn.commit();
}
} catch (SQLException sqle) {
lSuccess = false;
cMsg = "" + sqle.getErrorCode();
} catch (Exception ex) {
warn("insert", ex);
if (cnNested == null) {
try {
conn.rollback();
} catch (Exception e) {
fatal("insert.rollback", e);
}
}
lSuccess = false;
} finally {
try {
if (lPStmt != null) {
lPStmt.close();
}
if (cnNested == null) {
if (conn != null) {
conn.close();
}
dbConn.closeConnection();
}
} catch (Exception ex2) {
warn("insert.close", ex2);
}
if (lSuccess == false) throw new DAOException(cMsg);
return vData;
}
}
/**
* Actualiza al registro con las modificaciones enviadas sobre la entidad (vData).
*
* <p><b> update TRATiempoTraslado set iNumDiasTraslado=?, lDiasNaturalesTraslado=? where
* iCveTramite = ? AND iCveModalidad = ? AND iCveOficinaOrigen = ? AND iCveOficinaDestino = ? AND
* dtIniVigencia = ? </b>
*
* <p><b> Campos Llave:
* iCveTramite,iCveModalidad,iCveOficinaOrigen,iCveOficinaDestino,dtIniVigencia, </b>
*
* @param vData TVDinRep - VO Dinámico que contiene a la entidad a Insertar.
* @param cnNested Connection - Conexión anidada que permite que el método se encuentre dentro de
* una transacción mayor.
* @throws DAOException - Excepción de tipo DAO
* @return TVDinRep - Entidad Modificada.
*/
public TVDinRep update(TVDinRep vData, Connection cnNested) throws DAOException {
DbConnection dbConn = null;
Connection conn = cnNested;
PreparedStatement lPStmt = null;
boolean lSuccess = true;
try {
if (cnNested == null) {
dbConn = new DbConnection(dataSourceName);
conn = dbConn.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(2);
}
String lSQL =
"update TRATiempoTraslado set iNumDiasTraslado=?, lDiasNaturalesTraslado=? where iCveTramite = ? AND iCveModalidad = ? AND iCveOficinaOrigen = ? AND iCveOficinaDestino = ? AND dtIniVigencia = ? ";
vData.addPK(vData.getString("iCveTramite"));
vData.addPK(vData.getString("iCveModalidad"));
vData.addPK(vData.getString("iCveOficinaOrigen"));
vData.addPK(vData.getString("iCveOficinaDestino"));
vData.addPK(vData.getString("dtIniVigencia"));
lPStmt = conn.prepareStatement(lSQL);
lPStmt.setInt(1, vData.getInt("iNumDiasTraslado"));
lPStmt.setInt(2, vData.getInt("lDiasNaturalesTraslado"));
lPStmt.setInt(3, vData.getInt("iCveTramite"));
lPStmt.setInt(4, vData.getInt("iCveModalidad"));
lPStmt.setInt(5, vData.getInt("iCveOficinaOrigen"));
lPStmt.setInt(6, vData.getInt("iCveOficinaDestino"));
lPStmt.setDate(7, vData.getDate("dtIniVigencia"));
lPStmt.executeUpdate();
if (cnNested == null) {
conn.commit();
}
} catch (Exception ex) {
warn("update", ex);
if (cnNested == null) {
try {
conn.rollback();
} catch (Exception e) {
fatal("update.rollback", e);
}
}
lSuccess = false;
} finally {
try {
if (lPStmt != null) {
lPStmt.close();
}
if (cnNested == null) {
if (conn != null) {
conn.close();
}
dbConn.closeConnection();
}
} catch (Exception ex2) {
warn("update.close", ex2);
}
if (lSuccess == false) throw new DAOException("");
return vData;
}
}
/**
* Elimina al registro a través de la entidad dada por vData.
*
* <p><b> delete from TRATiempoTraslado where iCveTramite = ? AND iCveModalidad = ? AND
* iCveOficinaOrigen = ? AND iCveOficinaDestino = ? AND dtIniVigencia = ? </b>
*
* <p><b> Campos Llave:
* iCveTramite,iCveModalidad,iCveOficinaOrigen,iCveOficinaDestino,dtIniVigencia, </b>
*
* @param vData TVDinRep - VO Dinámico que contiene a la entidad a Insertar.
* @param cnNested Connection - Conexión anidada que permite que el método se encuentre dentro de
* una transacción mayor.
* @throws DAOException - Excepción de tipo DAO
* @return boolean - En caso de ser o no eliminado el registro.
*/
public boolean delete(TVDinRep vData, Connection cnNested) throws DAOException {
DbConnection dbConn = null;
Connection conn = cnNested;
PreparedStatement lPStmt = null;
boolean lSuccess = true;
String cMsg = "";
try {
if (cnNested == null) {
dbConn = new DbConnection(dataSourceName);
conn = dbConn.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(2);
}
// Ajustar Where de acuerdo a requerimientos...
String lSQL =
"delete from TRATiempoTraslado where iCveTramite = ? AND iCveModalidad = ? AND iCveOficinaOrigen = ? AND iCveOficinaDestino = ? AND dtIniVigencia = ? ";
// ...
lPStmt = conn.prepareStatement(lSQL);
lPStmt.setInt(1, vData.getInt("iCveTramite"));
lPStmt.setInt(2, vData.getInt("iCveModalidad"));
lPStmt.setInt(3, vData.getInt("iCveOficinaOrigen"));
lPStmt.setInt(4, vData.getInt("iCveOficinaDestino"));
lPStmt.setDate(5, vData.getDate("dtIniVigencia"));
lPStmt.executeUpdate();
if (cnNested == null) {
conn.commit();
}
} catch (SQLException sqle) {
lSuccess = false;
cMsg = "" + sqle.getErrorCode();
} catch (Exception ex) {
warn("delete", ex);
if (cnNested == null) {
try {
conn.rollback();
} catch (Exception e) {
fatal("delete.rollback", e);
}
}
lSuccess = false;
} finally {
try {
if (lPStmt != null) {
lPStmt.close();
}
if (cnNested == null) {
if (conn != null) {
conn.close();
}
dbConn.closeConnection();
}
} catch (Exception ex2) {
warn("delete.close", ex2);
}
if (lSuccess == false) throw new DAOException(cMsg);
return lSuccess;
}
}
/**
* Method called by the Form panel to update existing data.
*
* @param oldPersistentObject original value object, previous to the changes
* @param persistentObject value object to save
* @return an ErrorResponse value object in case of errors, VOResponse if the operation is
* successfully completed
*/
public Response updateRecord(ValueObject oldPersistentObject, ValueObject persistentObject)
throws Exception {
PreparedStatement stmt = null;
try {
stmt =
conn.prepareStatement(
"update DEMO4 set TEXT=?,DECNUM=?,CURRNUM=?,THEDATE=?,COMBO=?,CHECK_BOX=?,RADIO=?,CODE=?,TA=?,FORMATTED_TEXT=?,URI=?,LINK_LABEL=?,YEAR=?,FILENAME=? where TEXT=?");
DetailTestVO vo = (DetailTestVO) persistentObject;
DetailTestVO oldVO = (DetailTestVO) oldPersistentObject;
stmt.setObject(
6, vo.getCheckValue() == null || !vo.getCheckValue().booleanValue() ? "N" : "Y");
stmt.setString(5, vo.getCombo().getCode());
stmt.setBigDecimal(3, vo.getCurrencyValue());
stmt.setDate(4, vo.getDateValue());
stmt.setBigDecimal(2, vo.getNumericValue());
stmt.setObject(
7,
vo.getRadioButtonValue() == null || !vo.getRadioButtonValue().booleanValue() ? "N" : "Y");
stmt.setString(1, vo.getStringValue());
stmt.setString(8, vo.getLookupValue());
stmt.setString(9, vo.getTaValue());
stmt.setString(10, vo.getFormattedTextValue());
stmt.setString(11, vo.getUri());
stmt.setString(12, vo.getLinkLabel());
stmt.setBigDecimal(13, vo.getYear());
stmt.setString(14, vo.getFilename());
stmt.setString(15, oldVO.getStringValue());
stmt.execute();
stmt.close();
stmt = conn.prepareStatement("delete from DEMO4_LIST_VALUES where TEXT=?");
stmt.setString(1, pk);
stmt.execute();
if (vo.getListValues() != null) {
stmt.close();
stmt = conn.prepareStatement("insert into DEMO4_LIST_VALUES(TEXT,CODE) values(?,?)");
for (int i = 0; i < vo.getListValues().size(); i++) {
stmt.setString(1, pk);
stmt.setString(2, vo.getListValues().get(i).toString());
stmt.execute();
}
}
try {
if (vo.getFilename() != null && vo.getFile() != null) {
File f = new File(vo.getFilename());
BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(f));
out.write(vo.getFile());
out.close();
}
} catch (Exception ex) {
ex.printStackTrace();
}
// this instruction is no more needed: the grid has been linked to the Form (see Form.linkGrid
// method...)
// gridFrame.reloadData();
return new VOResponse(vo);
} catch (SQLException ex) {
ex.printStackTrace();
return new ErrorResponse(ex.getMessage());
} finally {
try {
stmt.close();
conn.commit();
} catch (SQLException ex1) {
}
}
}
/** Business logic to execute. */
public final Response executeCommand(
Object inputPar,
UserSessionParameters userSessionPars,
HttpServletRequest request,
HttpServletResponse response,
HttpSession userSession,
ServletContext context) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = ConnectionManager.getConnection(context);
// fires the GenericEvent.CONNECTION_CREATED event...
EventsManager.getInstance()
.processEvent(
new GenericEvent(
this,
getRequestName(),
GenericEvent.CONNECTION_CREATED,
(JAIOUserSessionParameters) userSessionPars,
request,
response,
userSession,
context,
conn,
inputPar,
null));
Response responseVO =
bean.insertItem(
conn,
(JournalHeaderVO) inputPar,
userSessionPars,
request,
response,
userSession,
context);
if (responseVO.isError()) {
conn.rollback();
return responseVO;
}
if (inputPar instanceof JournalHeaderWithVatVO) {
JournalHeaderWithVatVO vo = (JournalHeaderWithVatVO) inputPar;
// insert vat rows in the specified vat register...
Response regRes =
vatRegisterAction.insertVatRows(
conn, vo.getVats(), userSessionPars, request, response, userSession, context);
if (regRes.isError()) {
conn.rollback();
return regRes;
}
// retrieve payment instalments...
Response payRes =
payAction.executeCommand(
new LookupValidationParams(vo.getPaymentCodeREG10(), new HashMap()),
userSessionPars,
request,
response,
userSession,
context);
if (payRes.isError()) {
conn.rollback();
return payRes;
}
PaymentVO payVO = (PaymentVO) ((VOListResponse) payRes).getRows().get(0);
GridParams gridParams = new GridParams();
gridParams
.getOtherGridParams()
.put(ApplicationConsts.PAYMENT_CODE_REG10, vo.getPaymentCodeREG10());
payRes =
paysAction.executeCommand(
gridParams, userSessionPars, request, response, userSession, context);
if (payRes.isError()) {
conn.rollback();
return payRes;
}
java.util.List rows = ((VOListResponse) payRes).getRows();
// create expirations in DOC19 ONLY if:
// - there are more than one instalment OR
// - there is only one instalment and this instalment has more than 0 instalment days
if (rows.size() > 1
|| (rows.size() == 1
&& ((PaymentInstalmentVO) rows.get(0)).getInstalmentDaysREG17().intValue() > 0)) {
// retrieve internationalization settings (Resources object)...
ServerResourcesFactory factory =
(ServerResourcesFactory) context.getAttribute(Controller.RESOURCES_FACTORY);
Resources resources = factory.getResources(userSessionPars.getLanguageId());
PaymentInstalmentVO inVO = null;
pstmt =
conn.prepareStatement(
"insert into DOC19_EXPIRATIONS(COMPANY_CODE_SYS01,DOC_TYPE,DOC_YEAR,DOC_NUMBER,DOC_SEQUENCE,PROGRESSIVE,DOC_DATE,EXPIRATION_DATE,NAME_1,NAME_2,VALUE,PAYED,DESCRIPTION,CUSTOMER_SUPPLIER_CODE,PROGRESSIVE_REG04,CURRENCY_CODE_REG03) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
long startTime = vo.getItemDateACC05().getTime(); // item date...
if (payVO.getStartDayREG10().equals(ApplicationConsts.START_DAY_END_MONTH)) {
Calendar cal = Calendar.getInstance();
if (cal.get(cal.MONTH) == 10
|| cal.get(cal.MONTH) == 3
|| cal.get(cal.MONTH) == 5
|| cal.get(cal.MONTH) == 8) cal.set(cal.DAY_OF_MONTH, 30);
else if (cal.get(cal.MONTH) == 1) {
if (cal.get(cal.YEAR) % 4 == 0) cal.set(cal.DAY_OF_MONTH, 29);
else cal.set(cal.DAY_OF_MONTH, 28);
} else cal.set(cal.DAY_OF_MONTH, 31);
startTime = cal.getTime().getTime();
}
BigDecimal amount = null;
for (int i = 0; i < rows.size(); i++) {
inVO = (PaymentInstalmentVO) rows.get(i);
pstmt.setString(1, vo.getCompanyCodeSys01ACC05());
pstmt.setString(2, vo.getDocTypeDOC19());
pstmt.setBigDecimal(3, vo.getItemYearACC05());
pstmt.setBigDecimal(4, null);
pstmt.setBigDecimal(5, vo.getDocSequenceDOC19());
pstmt.setBigDecimal(
6,
ProgressiveUtils.getConsecutiveProgressive(
"DOC19_EXPIRATIONS", "PROGRESSIVE", conn));
pstmt.setDate(7, vo.getItemDateACC05());
pstmt.setDate(
8,
new java.sql.Date(
startTime
+ inVO.getInstalmentDaysREG17().longValue()
* 86400
* 1000)); // expiration date
pstmt.setString(9, vo.getName_1REG04());
pstmt.setString(10, vo.getName_2REG04());
amount =
vo.getTotalValue()
.multiply(inVO.getPercentageREG17())
.divide(new BigDecimal(100), BigDecimal.ROUND_HALF_UP)
.setScale(vo.getTotalValue().scale(), BigDecimal.ROUND_HALF_UP); // value
pstmt.setBigDecimal(11, amount);
pstmt.setString(12, "N");
if (vo.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE))
pstmt.setString(
13,
resources.getResource("sale generic document")
+ " "
+ vo.getDocSequenceDOC19()
+ "/"
+ vo.getItemYearACC05()
+ " - "
+ resources.getResource("valueREG01")
+ " "
+ resources.getResource("rateNumberREG17")
+ " "
+ (i + 1)
+ " - "
+ inVO.getPaymentTypeDescriptionSYS10()); // description
else
pstmt.setString(
13,
resources.getResource("purchase generic document")
+ " "
+ vo.getDocSequenceDOC19()
+ "/"
+ vo.getItemYearACC05()
+ " - "
+ resources.getResource("valueREG01")
+ " "
+ resources.getResource("rateNumberREG17")
+ " "
+ (i + 1)
+ " - "
+ inVO.getPaymentTypeDescriptionSYS10()); // description
pstmt.setString(14, vo.getCustomerCodeSAL07());
pstmt.setBigDecimal(15, vo.getProgressiveREG04());
pstmt.setString(16, vo.getCurrencyCodeREG01());
pstmt.execute();
}
pstmt.close();
}
// create an item registration for proceeds, according to expiration settings (e.g. retail
// selling):
// there must be only one instalment and this instalment has 0 instalment days
if (rows.size() == 1
&& ((PaymentInstalmentVO) rows.get(0)).getInstalmentDaysREG17().intValue() == 0) {
// retrieve internationalization settings (Resources object)...
ServerResourcesFactory factory =
(ServerResourcesFactory) context.getAttribute(Controller.RESOURCES_FACTORY);
Resources resources = factory.getResources(userSessionPars.getLanguageId());
HashMap map = new HashMap();
map.put(ApplicationConsts.COMPANY_CODE_SYS01, vo.getCompanyCodeSys01ACC05());
map.put(ApplicationConsts.PARAM_CODE, ApplicationConsts.CASE_ACCOUNT);
Response res =
userParamAction.executeCommand(
map, userSessionPars, request, response, userSession, context);
if (res.isError()) {
conn.rollback();
return res;
}
String caseAccountCode = ((VOResponse) res).getVo().toString();
JournalHeaderVO jhVO = new JournalHeaderVO();
jhVO.setCompanyCodeSys01ACC05(vo.getCompanyCodeSys01ACC05());
if (vo.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
jhVO.setDescriptionACC05(
resources.getResource("sale generic document")
+ " "
+ vo.getDocSequenceDOC19()
+ "/"
+ vo.getItemYearACC05()
+ " - "
+ resources.getResource("customer")
+ " "
+ vo.getName_1REG04()
+ " "
+ (vo.getName_2REG04() == null ? "" : vo.getName_2REG04()));
jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_INVOICE_PROCEEDS);
} else {
jhVO.setDescriptionACC05(
resources.getResource("purchase generic document")
+ " "
+ vo.getDocSequenceDOC19()
+ "/"
+ vo.getItemYearACC05()
+ " - "
+ resources.getResource("supplier")
+ " "
+ vo.getName_1REG04()
+ " "
+ (vo.getName_2REG04() == null ? "" : vo.getName_2REG04()));
jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_PURCHASE_INVOICE_PAYED);
}
jhVO.setItemDateACC05(new java.sql.Date(System.currentTimeMillis()));
jhVO.setItemYearACC05(new BigDecimal(Calendar.getInstance().get(Calendar.YEAR)));
JournalRowVO jrVO = new JournalRowVO();
jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
if (vo.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
jrVO.setAccountCodeAcc02ACC06(vo.getCreditAccountCodeAcc02SAL07());
jrVO.setAccountCodeACC06(vo.getCustomerCodeSAL07());
jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_CUSTOMER);
jrVO.setCreditAmountACC06(vo.getTotalValue());
} else {
jrVO.setAccountCodeAcc02ACC06(vo.getDebitAccountCodeAcc02PUR01());
jrVO.setAccountCodeACC06(vo.getSupplierCodePUR01());
jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_SUPPLIER);
jrVO.setDebitAmountACC06(vo.getTotalValue());
}
jrVO.setDescriptionACC06("");
jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
jhVO.addJournalRow(jrVO);
jrVO = new JournalRowVO();
jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
jrVO.setAccountCodeAcc02ACC06(caseAccountCode);
jrVO.setAccountCodeACC06(caseAccountCode);
jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_ACCOUNT);
if (vo.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
jrVO.setDebitAmountACC06(vo.getTotalValue());
} else {
jrVO.setCreditAmountACC06(vo.getTotalValue());
}
jrVO.setDescriptionACC06("");
jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
jhVO.addJournalRow(jrVO);
Response proceedsRes =
bean.insertItem(conn, jhVO, userSessionPars, request, response, userSession, context);
if (proceedsRes.isError()) {
conn.rollback();
return proceedsRes;
}
}
}
Response answer = responseVO;
// fires the GenericEvent.BEFORE_COMMIT event...
EventsManager.getInstance()
.processEvent(
new GenericEvent(
this,
getRequestName(),
GenericEvent.BEFORE_COMMIT,
(JAIOUserSessionParameters) userSessionPars,
request,
response,
userSession,
context,
conn,
inputPar,
answer));
conn.commit();
// fires the GenericEvent.AFTER_COMMIT event...
EventsManager.getInstance()
.processEvent(
new GenericEvent(
this,
getRequestName(),
GenericEvent.AFTER_COMMIT,
(JAIOUserSessionParameters) userSessionPars,
request,
response,
userSession,
context,
conn,
inputPar,
answer));
return answer;
} catch (Throwable ex) {
Logger.error(
userSessionPars.getUsername(),
this.getClass().getName(),
"executeCommand",
"Error while inserting a new item in the journal",
ex);
try {
conn.rollback();
} catch (Exception ex3) {
}
return new ErrorResponse(ex.getMessage());
} finally {
try {
pstmt.close();
} catch (Exception ex2) {
}
try {
ConnectionManager.releaseConnection(conn, context);
} catch (Exception ex1) {
}
}
}
/**
* Inserta el registro dado por la entidad vData.
*
* <p><b> insert into
* GRLRegistroPNC(iEjercicio,iConsecutivoPNC,dtRegistro,iCveUsuRegistro,iCveProducto,lResuelto,dtResolucion,iCveOficinaAsignado,iCveDeptoAsignado)
* values (?,?,?,?,?,?,?,?,?) </b>
*
* <p><b> Campos Llave: iEjercicio,iConsecutivoPNC, </b>
*
* @param vData TVDinRep - VO Dinámico que contiene a la entidad a Insertar.
* @param cnNested Connection - Conexión anidada que permite que el método se encuentre dentro de
* una transacción mayor.
* @throws DAOException - Excepción de tipo DAO
* @return TVDinRep - VO Dinámico que contiene a la entidad a Insertada, así como a la llave de
* esta entidad.
*/
public TVDinRep insert(TVDinRep vData, Connection cnNested) throws DAOException {
DbConnection dbConn = null;
Connection conn = cnNested;
PreparedStatement lPStmt = null;
boolean lSuccess = true;
TFechas dtFechaActual = new TFechas();
try {
if (cnNested == null) {
dbConn = new DbConnection(dataSourceName);
conn = dbConn.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(2);
}
String lSQL =
"insert into GRLRegistroPNC(iEjercicio,iConsecutivoPNC,dtRegistro,iCveUsuRegistro,iCveProducto,lResuelto,dtResolucion,iCveOficinaAsignado,iCveDeptoAsignado,iCveOficina,iCveDepartamento,iCveProceso) values (?,?,?,?,?,?,?,?,?,?,?,?)";
// AGREGAR AL ULTIMO ...
Vector vcData =
findByCustom(
"",
"select MAX(iConsecutivoPNC) AS iConsecutivoPNC from GRLRegistroPNC WHERE iEjercicio = "
+ vData.getInt("iEjercicio"));
if (vcData.size() > 0) {
TVDinRep vUltimo = (TVDinRep) vcData.get(0);
vData.put("iConsecutivoPNC", vUltimo.getInt("iConsecutivoPNC") + 1);
} else vData.put("iConsecutivoPNC", 1);
vData.addPK(vData.getString("iConsecutivoPNC"));
lPStmt = conn.prepareStatement(lSQL);
lPStmt.setInt(1, dtFechaActual.getIntYear(dtFechaActual.TodaySQL()));
lPStmt.setInt(2, vData.getInt("iConsecutivoPNC"));
iConsecutivo = vData.getInt("iConsecutivoPNC");
lPStmt.setDate(3, tFecha.TodaySQL());
lPStmt.setInt(4, vData.getInt("iCveUsuario"));
lPStmt.setInt(5, vData.getInt("iCveProducto"));
lPStmt.setInt(6, vData.getInt("lResuelto"));
if (vData.getDate("dtResolucion") == null) lPStmt.setNull(7, Types.DATE);
else lPStmt.setDate(7, vData.getDate("dtResolucion"));
lPStmt.setInt(8, vData.getInt("iCveOficinaUsrAsg"));
lPStmt.setInt(9, vData.getInt("iCveDeptoUsrAsg"));
lPStmt.setInt(10, vData.getInt("iCveOficinaUsr"));
lPStmt.setInt(11, vData.getInt("iCveDeptoUsr"));
lPStmt.setInt(12, vData.getInt("iCveProceso"));
lPStmt.executeUpdate();
lPStmt.close();
try {
TDGRLRegCausaPNC1 dGRLRegCausaPNC = new TDGRLRegCausaPNC1();
dGRLRegCausaPNC.insert(vData, conn, iConsecutivo);
} catch (Exception exCausa) {
exCausa.printStackTrace();
lSuccess = false;
throw new Exception("Error Causa");
}
if (cnNested == null && lSuccess) conn.commit();
} catch (Exception ex) {
warn("insert", ex);
if (cnNested == null) {
try {
conn.rollback();
} catch (Exception e) {
fatal("insert.rollback", e);
}
}
lSuccess = false;
} finally {
try {
if (lPStmt != null) lPStmt.close();
if (cnNested == null) {
if (conn != null) conn.close();
dbConn.closeConnection();
}
} catch (Exception ex2) {
warn("insert.close", ex2);
}
if (lSuccess == false) throw new DAOException("");
return vData;
}
}
/**
* Actualiza el campo de Producto No Conforme de la persona que recibe la notificación.
*
* <p><b> update GRLRegistroPNC set cRecibeNotif=? where iEjercicio = ? AND iConsecutivoPNC = ?
* </b>
*
* <p><b> Campos Llave: iEjercicio,iConsecutivoPNC, </b>
*
* @param vData TVDinRep - VO Dinámico que contiene a la entidad a Insertar.
* @param cnNested Connection - Conexión anidada que permite que el método se encuentre dentro de
* una transacción mayor.
* @throws DAOException - Excepción de tipo DAO
* @return TVDinRep - Entidad Modificada.
*/
public TVDinRep updateRecibe(TVDinRep vData, Connection cnNested) throws DAOException {
DbConnection dbConn = null;
Connection conn = cnNested;
PreparedStatement lPStmt = null;
int iConsecutivoPNC = 0, iEjercicioPNC = 0;
boolean lSuccess = true;
try {
if (cnNested == null) {
dbConn = new DbConnection(dataSourceName);
conn = dbConn.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(2);
}
Vector vcPNC =
findByCustom(
"",
"SELECT "
+ "iEjercicioPNC,ICONSECUTIVOPNC "
+ "FROM TRAREGPNCETAPA "
+ "where IEJERCICIO = "
+ vData.getInt("iEjercicio")
+ " and INUMSOLICITUD = "
+ vData.getInt("iNumSolicitud")
+ " order by iorden desc");
if (vcPNC.size() > 0) {
TVDinRep vRegPNC = (TVDinRep) vcPNC.get(0);
iConsecutivoPNC = vRegPNC.getInt("ICONSECUTIVOPNC");
iEjercicioPNC = vRegPNC.getInt("iEjercicioPNC");
}
String lSQL =
"update GRLRegistroPNC set cRecibeNotif=?,dtNotificacion=? where iEjercicio = ? AND iConsecutivoPNC = ? ";
lPStmt = conn.prepareStatement(lSQL);
lPStmt.setString(1, vData.getString("cRecibeNotif"));
lPStmt.setDate(2, vData.getDate("dtNotificacion"));
lPStmt.setInt(3, iEjercicioPNC);
lPStmt.setInt(4, iConsecutivoPNC);
if (cnNested == null) {
conn.commit();
}
/*if(cnNested == null){
conn.commit();
TDVerificacion tdVer = new TDVerificacion();
tdVer.upNotificacion(vData, cnNested);
}*/
} catch (Exception ex) {
warn("update", ex);
if (cnNested == null) {
try {
conn.rollback();
} catch (Exception e) {
fatal("update.rollback", e);
}
}
lSuccess = false;
} finally {
try {
if (lPStmt != null) {
lPStmt.close();
}
if (cnNested == null) {
if (conn != null) {
conn.close();
}
dbConn.closeConnection();
}
} catch (Exception ex2) {
warn("update.close", ex2);
}
if (lSuccess == false) throw new DAOException("");
return vData;
}
}
/**
* Actualiza al registro con las modificaciones enviadas sobre la entidad (vData).
*
* <p><b> update GRLRegistroPNC set dtRegistro=?, iCveUsuRegistro=?, iCveProducto=?, lResuelto=?,
* dtResolucion=?, iCveOficinaAsignado=?, iCveDeptoAsignado=? where iEjercicio = ? AND
* iConsecutivoPNC = ? </b>
*
* <p><b> Campos Llave: iEjercicio,iConsecutivoPNC, </b>
*
* @param vData TVDinRep - VO Dinámico que contiene a la entidad a Insertar.
* @param cnNested Connection - Conexión anidada que permite que el método se encuentre dentro de
* una transacción mayor.
* @throws DAOException - Excepción de tipo DAO
* @return TVDinRep - Entidad Modificada.
*/
public TVDinRep update(TVDinRep vData, Connection cnNested) throws DAOException {
DbConnection dbConn = null;
Connection conn = cnNested;
PreparedStatement lPStmt = null;
boolean lSuccess = true;
try {
if (cnNested == null) {
dbConn = new DbConnection(dataSourceName);
conn = dbConn.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(2);
}
String lSQL =
"update GRLRegistroPNC set dtRegistro=?, iCveUsuRegistro=?, iCveProducto=?, lResuelto=?, dtResolucion=?, iCveOficinaAsignado=?, iCveDeptoAsignado=? where iEjercicio = ? AND iConsecutivoPNC = ? ";
vData.addPK(vData.getString("iEjercicio"));
vData.addPK(vData.getString("iConsecutivoPNC"));
lPStmt = conn.prepareStatement(lSQL);
lPStmt.setDate(1, vData.getDate("dtRegistro"));
lPStmt.setInt(2, vData.getInt("iCveUsuRegistro"));
lPStmt.setInt(3, vData.getInt("iCveProducto"));
lPStmt.setInt(4, vData.getInt("lResuelto"));
lPStmt.setDate(5, vData.getDate("dtResolucion"));
lPStmt.setInt(6, vData.getInt("iCveOficinaAsignado"));
lPStmt.setInt(7, vData.getInt("iCveDeptoAsignado"));
lPStmt.setInt(8, vData.getInt("iEjercicio"));
lPStmt.setInt(9, vData.getInt("iConsecutivoPNC"));
lPStmt.executeUpdate();
if (cnNested == null) {
conn.commit();
}
} catch (Exception ex) {
warn("update", ex);
if (cnNested == null) {
try {
conn.rollback();
} catch (Exception e) {
fatal("update.rollback", e);
}
}
lSuccess = false;
} finally {
try {
if (lPStmt != null) {
lPStmt.close();
}
if (cnNested == null) {
if (conn != null) {
conn.close();
}
dbConn.closeConnection();
}
} catch (Exception ex2) {
warn("update.close", ex2);
}
if (lSuccess == false) throw new DAOException("");
return vData;
}
}
/**
* Inserta el registro dado por la entidad vData.
*
* <p><b> insert into
* GRLRegistroPNC(iEjercicio,iConsecutivoPNC,dtRegistro,iCveUsuRegistro,iCveProducto,lResuelto,dtResolucion,iCveOficinaAsignado,iCveDeptoAsignado)
* values (?,?,?,?,?,?,?,?,?) </b>
*
* <p><b> Campos Llave: iEjercicio,iConsecutivoPNC, </b>
*
* @param vData TVDinRep - VO Dinámico que contiene a la entidad a Insertar.
* @param cnNested Connection - Conexión anidada que permite que el método se encuentre dentro de
* una transacción mayor.
* @throws DAOException - Excepción de tipo DAO
* @return TVDinRep - VO Dinámico que contiene a la entidad a Insertada, así como a la llave de
* esta entidad.
*/
public TVDinRep insertExiste(TVDinRep vData, Connection cnNested) throws DAOException {
DbConnection dbConn = null;
Connection conn = cnNested;
PreparedStatement lPStmt = null;
boolean lSuccess = true;
boolean lAgregarAPNC = false;
boolean lInsertaCausa = false;
TFechas dtFechaActual = new TFechas();
int iNumSolicitud, iCveTramite, iCveModalidad = 0;
int iConsecutivoPNC = 0;
int iEjercicio = 0;
try {
// LEL26092006
Vector vcDataA =
findByCustom(
"",
"SELECT "
+ "TRAREGPNCETAPA.IEJERCICIOPNC, "
+ "TRAREGPNCETAPA.INUMSOLICITUD, "
+ "TRAREGPNCETAPA.ICONSECUTIVOPNC, "
+ "TRAREGPNCETAPA.ICVETRAMITE, "
+ "TRAREGPNCETAPA.ICVEMODALIDAD "
+ "FROM TRAREGPNCETAPA "
+ "where TRAREGPNCETAPA.IEJERCICIO = "
+ vData.getInt("iEjercicio")
+ " and TRAREGPNCETAPA.INUMSOLICITUD = "
+ vData.getInt("iNumSolicitud")
+ " ORDER BY ICONSECUTIVOPNC DESC ");
TVDinRep vSoli;
if (vcDataA.size() > 0) {
vSoli = (TVDinRep) vcDataA.get(0);
iEjercicio = vSoli.getInt("IEJERCICIOPNC");
iNumSolicitud = vSoli.getInt("INUMSOLICITUD");
iConsecutivoPNC = vSoli.getInt("ICONSECUTIVOPNC");
iCveTramite = vSoli.getInt("ICVETRAMITE");
iCveModalidad = vSoli.getInt("ICVEMODALIDAD");
vcDataA = null;
vcDataA =
findByCustom(
"",
"SELECT "
+ "DTNOTIFICACION FROM TRAREGREQXTRAM "
+ "WHERE IEJERCICIO = "
+ iEjercicio
+ " AND INUMSOLICITUD = "
+ iNumSolicitud
+ " AND ICVETRAMITE = "
+ iCveTramite
+ " AND ICVEMODALIDAD = "
+ iCveModalidad
+ " AND LTIENEPNC = 1");
lAgregarAPNC = true;
for (int i = 0; i < vcDataA.size() && lAgregarAPNC == false; i++) {
vSoli = (TVDinRep) vcDataA.get(i);
if (vSoli.getDate("DTNOTIFICACION") == null) lAgregarAPNC = true;
else lAgregarAPNC = false;
}
}
if (lAgregarAPNC) {
TVDinRep vResuelto;
Vector vcDataR =
findByCustom(
"",
"SELECT lResuelto "
+ "FROM GRLREGISTROPNC WHERE "
+ "IEJERCICIO = "
+ iEjercicio
+ " AND ICONSECUTIVOPNC = "
+ iConsecutivoPNC);
if (vcDataR.size() > 0) {
vResuelto = (TVDinRep) vcDataR.get(0);
if (vResuelto.getInt("lResuelto") != 0) lAgregarAPNC = false;
}
}
// FinLEL26092006
if (cnNested == null) {
dbConn = new DbConnection(dataSourceName);
conn = dbConn.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(2);
}
String lSQL =
"insert into GRLRegistroPNC(iEjercicio,iConsecutivoPNC,dtRegistro,iCveUsuRegistro,iCveProducto,lResuelto,dtResolucion,iCveOficinaAsignado,iCveDeptoAsignado,iCveOficina,iCveDepartamento,iCveProceso) values (?,?,?,?,?,?,?,?,?,?,?,?)";
// AGREGAR AL ULTIMO ...
if (lAgregarAPNC == false) {
Vector vcData =
findByCustom(
"",
"select MAX(iConsecutivoPNC) AS iConsecutivoPNC from GRLRegistroPNC WHERE iEjercicio = "
+ vData.getInt("iEjercicio"));
if (vcData.size() > 0) {
TVDinRep vUltimo = (TVDinRep) vcData.get(0);
vData.put("iConsecutivoPNC", vUltimo.getInt("iConsecutivoPNC") + 1);
} else vData.put("iConsecutivoPNC", 1);
} else {
vData.put("iConsecutivoPNC", iConsecutivoPNC);
}
vData.addPK(vData.getString("iConsecutivoPNC"));
lPStmt = conn.prepareStatement(lSQL);
lPStmt.setInt(1, dtFechaActual.getIntYear(dtFechaActual.TodaySQL()));
lPStmt.setInt(2, vData.getInt("iConsecutivoPNC"));
iConsecutivo = vData.getInt("iConsecutivoPNC");
lPStmt.setDate(3, tFecha.TodaySQL());
lPStmt.setInt(4, vData.getInt("iCveUsuario"));
lPStmt.setInt(5, vData.getInt("iCveProducto"));
lPStmt.setInt(6, vData.getInt("lResuelto"));
if (vData.getDate("dtResolucion") == null) lPStmt.setNull(7, Types.DATE);
else lPStmt.setDate(7, vData.getDate("dtResolucion"));
lPStmt.setInt(8, vData.getInt("iCveOficinaUsrAsg"));
lPStmt.setInt(9, vData.getInt("iCveDeptoUsrAsg"));
lPStmt.setInt(10, vData.getInt("iCveOficinaUsr"));
lPStmt.setInt(11, vData.getInt("iCveDeptoUsr"));
lPStmt.setInt(12, vData.getInt("iCveProceso"));
if (lAgregarAPNC == false) lPStmt.executeUpdate();
lPStmt.close();
try {
TDGRLRegCausaPNC1 dGRLRegCausaPNC = new TDGRLRegCausaPNC1();
// TDGRLRegCausaPNC dGRLRegCausaPNC = new TDGRLRegCausaPNC();
if (lAgregarAPNC == false) {
dGRLRegCausaPNC.insert(vData, conn, iConsecutivo);
// dGRLRegCausaPNC.insert(vData,conn);
} else {
dGRLRegCausaPNC.insertA(vData, conn, iConsecutivo);
// dGRLRegCausaPNC.insertA(vData,conn);
}
} catch (Exception exCausa) {
exCausa.printStackTrace();
lSuccess = false;
}
if (cnNested == null && lSuccess) conn.commit();
} catch (Exception ex) {
warn("insert", ex);
if (cnNested == null) {
try {
conn.rollback();
} catch (Exception e) {
fatal("insert.rollback", e);
}
}
lSuccess = false;
} finally {
try {
if (lPStmt != null) lPStmt.close();
if (cnNested == null) {
if (conn != null) conn.close();
dbConn.closeConnection();
}
} catch (Exception ex2) {
warn("insert.close", ex2);
}
if (lSuccess == false) throw new DAOException("");
return vData;
}
}
public static void main(String[] args) {
Statement statement = null;
Connection conn = null;
ResultSet rsMin = null;
PreparedStatement psInsert = null;
LinkedList<Statement> allStatements = new LinkedList<Statement>();
try {
// Instantiate the driver
Class.forName(JDBC_DRIVER);
} catch (ClassNotFoundException cnfe) {
System.out.println(
"Can't instantiate driver class; check you have drives and classpath configured correctly?");
cnfe.printStackTrace();
System.exit(-1); // No driver? Need to fix before anything else will work. So quit the program
}
try {
conn = DriverManager.getConnection(DB_CONNECTION_URL, USER, PASSWORD);
statement = conn.createStatement();
allStatements.add(statement);
System.out.println("Average Weather Database Program");
// Create a table in the database. Stores today's date, and the min and max temperatures
// recorded.
String createTableSQL = "CREATE TABLE temp (day date, mintemp double, maxtemp double)";
String deleteTableSQL = "DROP TABLE temp";
try {
statement.executeUpdate(createTableSQL);
System.out.println("Created temp table");
} catch (SQLException sqle) {
// Seems the table already exists. Delete it and recreate it
if (sqle.getSQLState()
.startsWith("42")) { // Error code for table already existing start with XO
System.out.println("Temp table appears to exist already, delete and recreate");
statement.executeUpdate(deleteTableSQL);
statement.executeUpdate(createTableSQL);
} else {
// Something else went wrong. If we can't create the table, no point attempting
// to run the rest of the code. Throw the exception again to be handled at the end of the
// program.
System.out.println("Got stuck in catch else " + sqle.getSQLState() + " is sql state");
throw sqle;
}
}
// Add some test data
String prepStatInsert = "INSERT INTO temp VALUES ( ?, ?, ? )";
psInsert = conn.prepareStatement(prepStatInsert);
allStatements.add(psInsert);
psInsert.setDate(1, Date.valueOf("2014-04-01"));
psInsert.setDouble(2, 44.2);
psInsert.setDouble(3, 58.7);
psInsert.executeUpdate();
psInsert.setDate(1, Date.valueOf("2014-04-02"));
psInsert.setDouble(2, 34.6);
psInsert.setDouble(3, 55.1);
psInsert.executeUpdate();
psInsert.setDate(1, Date.valueOf("2014-04-03"));
psInsert.setDouble(2, 43.9);
psInsert.setNull(
3, Types.DOUBLE); // Forgot to record the max temperature for this date so set it to null.
psInsert.executeUpdate();
psInsert.setDate(1, Date.valueOf("2014-04-04"));
psInsert.setDouble(2, 43.8);
psInsert.setDouble(3, 47.2);
psInsert.executeUpdate();
System.out.println("Added test data to database");
// Let's calculate the average minimum and average maximum temperature for all the days.
// Add up all the maximum temperatures and divide by number of days to get average max
// temperature.
// Add up all the minimum temperatures and divide by number of days to get average min
// temperature.
double averageMaxTemp = 0;
double averageMinTemp = 0;
String getAvgsSQL = "SELECT AVG(mintemp) AS rsMin, AVG(maxtemp) AS rsMax FROM temp";
rsMin = statement.executeQuery(getAvgsSQL);
while (rsMin.next()) { // there is only one
averageMinTemp = rsMin.getDouble("rsMin");
System.out.println("Average min is " + averageMinTemp);
averageMaxTemp = rsMin.getDouble("rsMax");
System.out.println("Average max is " + averageMaxTemp);
}
System.out.println(
"Average maximum temperature = "
+ averageMaxTemp
+ " , average minimum temperature = "
+ averageMinTemp);
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
// A finally block runs whether an exception is thrown or not. Close resources and tidy up
// whether this code worked or not.
try {
if (rsMin != null) {
rsMin.close(); // Close result set
System.out.println("ResultSet closed");
}
} catch (SQLException se) {
se.printStackTrace();
}
// Close all of the statements. Stored a reference to each statement in allStatements so we
// can loop over all of them and close them all.
for (Statement s : allStatements) {
if (s != null) {
try {
s.close();
System.out.println("Statement closed");
} catch (SQLException se) {
System.out.println("Error closing statement");
se.printStackTrace();
}
}
}
try {
if (conn != null) {
conn.close(); // Close connection to database
System.out.println("Database connection closed");
}
} catch (SQLException se) {
se.printStackTrace();
}
}
System.out.println("End of program");
}
public void testSetDate() throws Exception {
for (int i = 0; i < PREPARE_THRESHOLD; i++) {
con.createStatement().execute("delete from testtimezone");
PreparedStatement insertTimestamp =
con.prepareStatement("INSERT INTO testtimezone(seq,tstz,ts,d) VALUES (?,?,?,?)");
int seq = 1;
Date dJVM, dUTC, dGMT03, dGMT05, dGMT13 = null;
// +0100 (JVM default)
dJVM = new Date(1104534000000L); // 2005-01-01 00:00:00 +0100
insertTimestamp.setInt(1, seq++);
insertTimestamp.setDate(2, dJVM); // 2005-01-01 00:00:00 +0100
insertTimestamp.setDate(3, dJVM); // 2005-01-01 00:00:00
insertTimestamp.setDate(4, dJVM); // 2005-01-01
insertTimestamp.executeUpdate();
// UTC
dUTC = new Date(1104537600000L); // 2005-01-01 00:00:00 +0000
insertTimestamp.setInt(1, seq++);
insertTimestamp.setDate(2, dUTC, cUTC); // 2005-01-01 00:00:00 +0000
insertTimestamp.setDate(3, dUTC, cUTC); // 2005-01-01 00:00:00
insertTimestamp.setDate(4, dUTC, cUTC); // 2005-01-01
insertTimestamp.executeUpdate();
// +0300
dGMT03 = new Date(1104526800000L); // 2005-01-01 00:00:00 +0300
insertTimestamp.setInt(1, seq++);
insertTimestamp.setDate(2, dGMT03, cGMT03); // 2005-01-01 00:00:00 +0300
insertTimestamp.setDate(3, dGMT03, cGMT03); // 2005-01-01 00:00:00
insertTimestamp.setDate(4, dGMT03, cGMT03); // 2005-01-01
insertTimestamp.executeUpdate();
// -0500
dGMT05 = new Date(1104555600000L); // 2005-01-01 00:00:00 -0500
insertTimestamp.setInt(1, seq++);
insertTimestamp.setDate(2, dGMT05, cGMT05); // 2005-01-01 00:00:00 -0500
insertTimestamp.setDate(3, dGMT05, cGMT05); // 2005-01-01 00:00:00
insertTimestamp.setDate(4, dGMT05, cGMT05); // 2005-01-01
insertTimestamp.executeUpdate();
if (min73) {
// +1300
dGMT13 = new Date(1104490800000L); // 2005-01-01 00:00:00 +1300
insertTimestamp.setInt(1, seq++);
insertTimestamp.setDate(2, dGMT13, cGMT13); // 2005-01-01 00:00:00 +1300
insertTimestamp.setDate(3, dGMT13, cGMT13); // 2005-01-01 00:00:00
insertTimestamp.setDate(4, dGMT13, cGMT13); // 2005-01-01
insertTimestamp.executeUpdate();
}
insertTimestamp.close();
// check that insert went correctly by parsing the raw contents in UTC
checkDatabaseContents(
"SELECT seq::text,tstz::text,ts::text,d::text from testtimezone ORDER BY seq",
new String[][] {
new String[] {"1", "2004-12-31 23:00:00+00", "2005-01-01 00:00:00", "2005-01-01"},
new String[] {"2", "2005-01-01 00:00:00+00", "2005-01-01 00:00:00", "2005-01-01"},
new String[] {"3", "2004-12-31 21:00:00+00", "2005-01-01 00:00:00", "2005-01-01"},
new String[] {"4", "2005-01-01 05:00:00+00", "2005-01-01 00:00:00", "2005-01-01"},
new String[] {"5", "2004-12-31 11:00:00+00", "2005-01-01 00:00:00", "2005-01-01"}
});
//
// check results
//
seq = 1;
PreparedStatement ps =
con.prepareStatement("SELECT seq,tstz,ts,d FROM testtimezone ORDER BY seq");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(dJVM, rs.getDate(2));
assertEquals(dJVM, rs.getDate(3));
assertEquals(dJVM, rs.getDate(4));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(dUTC, rs.getDate(2, cUTC));
assertEquals(dUTC, rs.getDate(3, cUTC));
assertEquals(dUTC, rs.getDate(4, cUTC));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(dGMT03, rs.getDate(2, cGMT03));
assertEquals(dGMT03, rs.getDate(3, cGMT03));
assertEquals(dGMT03, rs.getDate(4, cGMT03));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(dGMT05, rs.getDate(2, cGMT05));
assertEquals(dGMT05, rs.getDate(3, cGMT05));
assertEquals(dGMT05, rs.getDate(4, cGMT05));
if (min73) {
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(dGMT13, rs.getDate(2, cGMT13));
assertEquals(dGMT13, rs.getDate(3, cGMT13));
assertEquals(dGMT13, rs.getDate(4, cGMT13));
}
assertTrue(!rs.next());
ps.close();
}
}
