@Override
public Set<EmpVO> getEmpsByDeptno(Integer deptno) {
Set<EmpVO> set = new LinkedHashSet<EmpVO>();
EmpVO empVO = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, userid, passwd);
pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT);
pstmt.setInt(1, deptno);
rs = pstmt.executeQuery();
while (rs.next()) {
empVO = new EmpVO();
empVO.setEmpno(rs.getInt("empno"));
empVO.setEname(rs.getString("ename"));
empVO.setJob(rs.getString("job"));
empVO.setHiredate(rs.getDate("hiredate"));
empVO.setSal(rs.getDouble("sal"));
empVO.setComm(rs.getDouble("comm"));
empVO.setDeptno(rs.getInt("deptno"));
set.add(empVO); // Store the row in the vector
}
// Handle any driver errors
} catch (ClassNotFoundException e) {
throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
// Handle any SQL errors
} catch (SQLException se) {
throw new RuntimeException("A database error occured. " + se.getMessage());
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace(System.err);
}
}
}
return set;
}
@WebMethod
public List<Asset> getFasilitasAset(int idAset) throws SQLException {
String query =
"SELECT * FROM "
+ FASILITAS_ASET_TABLE
+ " JOIN "
+ ASSET_TABLE
+ " ON "
+ FASILITAS_ASET_TABLE
+ ".id_fasilitas="
+ ASSET_TABLE
+ ".id"
+ " WHERE id_aset = "
+ idAset;
System.out.println(query);
ResultSet rs = executeQuery(query);
List<Asset> assets = null;
while (rs.next()) {
if (rs.isFirst()) assets = new ArrayList<Asset>();
Asset asset = new Asset();
asset.setId(rs.getInt("id"));
asset.setNama(rs.getString("nama"));
asset.setKategori(rs.getString("kategori"));
asset.setKategori(rs.getString("jenis"));
asset.setTanggalMasuk(rs.getDate("tanggal_masuk"));
asset.setKondisi(AssetCondition.getInstance(rs.getString("kondisi")));
asset.setPemilik(rs.getString("pemilik"));
asset.setVendor(getVendorAset(rs.getInt("id_vendor")));
asset.setHarga(rs.getString("harga"));
asset.setPublicAsset(rs.getBoolean("is_public"));
assets.add(asset);
}
return assets;
}
public ArrayList<airschedule> getAirschedule() {
connect();
ArrayList<airschedule> list = new ArrayList<airschedule>();
try {
pstmt = con.prepareStatement(SQL);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
airschedule s = new airschedule();
s.setSchedule_id(rs.getInt("schedule_id"));
s.setDeparture_date(rs.getDate("departure_date"));
s.setArrival_date(rs.getDate("arrival_date"));
s.setDeparture_time(rs.getTime("departure_time"));
s.setArrival_time(rs.getTime("arrival_time"));
s.setAirline_name(rs.getString("airline_name"));
result++;
list.add(s);
}
this.setResult(result);
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
disconnect();
}
return list;
}
@Test
public void testUpsertDateValues() throws Exception {
long ts = nextTimestamp();
Date now = new Date(System.currentTimeMillis());
ensureTableCreated(getUrl(), TestUtil.PTSDB_NAME, null, ts - 2);
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String dateString = "1999-01-01 02:00:00";
PreparedStatement upsertStmt =
conn.prepareStatement(
"upsert into ptsdb(inst,host,date) values('aaa','bbb',to_date('" + dateString + "'))");
int rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
upsertStmt =
conn.prepareStatement(
"upsert into ptsdb(inst,host,date) values('ccc','ddd',current_date())");
rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
conn.commit();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String select = "SELECT date,current_date() FROM ptsdb";
ResultSet rs = conn.createStatement().executeQuery(select);
Date then = new Date(System.currentTimeMillis());
assertTrue(rs.next());
Date date = DateUtil.parseDate(dateString);
assertEquals(date, rs.getDate(1));
assertTrue(rs.next());
assertTrue(rs.getDate(1).after(now) && rs.getDate(1).before(then));
assertFalse(rs.next());
}
@Override
public Project get(int id) {
Project result = null;
try (Connection connection = dbDataSourceProvider.getConnection()) {
try (PreparedStatement statement = connection.prepareStatement(sqlProvider.get4Load())) {
statement.setInt(1, id);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
result =
new Project(
resultSet.getString("name"),
resultSet.getInt("goal"),
resultSet.getDate("deadline_date"));
result.setShortDescription(resultSet.getString("description"));
result.setBalance(resultSet.getInt("balance"));
result.setDemoLink(resultSet.getString("demo_link"));
result.setCreateDate(resultSet.getDate("create_date"));
result.setId(id);
}
}
connection.commit();
} catch (SQLException e) {
throw new RuntimeException(e);
}
return result;
}
private EventRequestBean getFromResultSet(ResultSet resultSet) throws SQLException {
EventRequestBean eventRequest;
String id = resultSet.getString(1);
eventRequest = new EventRequestBean(id);
String name = resultSet.getString(2);
eventRequest.setName(name);
int clientId = resultSet.getInt(3);
eventRequest.setClientId(clientId);
String type = resultSet.getString(4);
eventRequest.setType(type);
Date from = resultSet.getDate(5);
eventRequest.setFrom(from);
Date to = resultSet.getDate(6);
eventRequest.setTo(to);
String budget = resultSet.getString(7);
eventRequest.setBudget(budget);
String status = resultSet.getString(8);
eventRequest.setStatus(status);
Map<String, String> preferences = eventRequest.getPreferences();
preferences.put("details_photo_video", resultSet.getNString(9));
preferences.put("details_music", resultSet.getNString(10));
preferences.put("details_poster", resultSet.getNString(11));
preferences.put("details_decoration", resultSet.getNString(12));
preferences.put("details_network", resultSet.getNString(13));
preferences.put("details_food_beverages", resultSet.getNString(14));
preferences.put("details_waiter", resultSet.getNString(15));
preferences.put("feedback_budget", resultSet.getNString(16));
return eventRequest;
}
private void verifyDateResultSet(ResultSet rs, Date date, int rowCount) throws SQLException {
for (int i = 0; i < rowCount; i++) {
assertTrue(rs.next());
if (tgtPH()) assertEquals(date, rs.getDate(1));
else if (tgtSQ() || tgtTR()) assertEquals(date.toString(), rs.getDate(1).toString());
}
assertFalse(rs.next());
}
public JobProtheus retornaJob(Connection conn, JobLote lote) throws SQLException {
ResultSet rs = null;
PreparedStatement stmt = null;
JobProtheus job = null;
String sql = "select job , B1_DESC, operacao , produto, ";
sql += " dt_release, job_start_date , qtd_release, setor, qtd_transf ";
sql += " from job left join " + DB_PROTHEUS.trim() + ".dbo.SB1000 SB1 ";
sql += " on(produto collate SQL_Latin1_General_CP1_CI_AS = B1_COD) ";
sql += " where job = ? and operacao = ? and SB1.D_E_L_E_T_ = '' ";
sql += " order by dt_release, produto ";
stmt = conn.prepareStatement(sql);
stmt.setString(1, lote.getJob().trim());
stmt.setInt(2, lote.getOperNum());
rs = stmt.executeQuery();
while (rs.next()) {
String nJob = rs.getString("job");
int operacao = rs.getInt("operacao");
String produto = rs.getString("produto");
Date dataEmissao = rs.getDate("dt_release");
Date dataPrevisaoInicio = rs.getDate("job_start_date");
String descricaoProduto = rs.getString("B1_DESC");
double quantidade = rs.getDouble("qtd_release");
String wc = rs.getString("setor");
String emissao = dataEmissao != null ? getDateToString(dataEmissao) : "";
String previsaoInicio = dataPrevisaoInicio != null ? getDateToString(dataPrevisaoInicio) : "";
job = new JobProtheus();
job.setStatus("");
job.setJob(nJob);
job.setOperacao(operacao);
job.setProduto(produto.trim());
job.setDataEmissao(emissao);
job.setQuantidadeLiberada(quantidade);
job.setDescricaoProduto(descricaoProduto);
job.setCentroTrabalho(wc);
double qtdTotal = retornaQtdTotal(conn, job); // calcula o total transferido para o job
job.setQuantidadeCompleta(qtdTotal);
job.setDataPrivisaoInicio(previsaoInicio);
job.setQuantidadeFaltando(quantidade - qtdTotal); // seta o valor qtd faltando
}
return job; // retorna lista de jobs
}
/**
* Zeige die Daten aller Spiele an, die am ersten Spieltag aller drei Ligen nach 17 Uhr begonnen
* haben.
*
* @param db Hostadresse der Datenbank
* @param user Benutzername für Datenbank
* @param password Passwort für Datenbank
* @throws SQLException Exception für Datenbankzugriffe benötigt
* @throws ClassNotFoundException Exception für Postgres-Verbindung benötigt
* @see #connect(String, String, String)
* @see #disconnect()
*/
public static void select3(String db, String user, String password)
throws SQLException, ClassNotFoundException {
connect(db, user, password);
System.out.println("Spiele am ersten Spieltag nach 17 Uhr:");
ResultSet rs =
stmt.executeQuery("SELECT * FROM bl.spiel WHERE spieltag = 1 AND uhrzeit > '17:00:00';");
while (rs.next()) {
int spiel_id = rs.getInt("spiel_id");
int spieltag = rs.getInt("spieltag");
Date datum = rs.getDate("datum");
Time uhrzeit = rs.getTime("uhrzeit");
int heim = rs.getInt("heim");
int gast = rs.getInt("gast");
int tore_heim = rs.getInt("tore_heim");
int tore_gast = rs.getInt("tore_gast");
System.out.print("ID: " + spiel_id + "; \t");
System.out.print("Spieltag: " + spieltag + ";\t");
System.out.print("Datum: " + datum + ";\t");
System.out.print("Uhrzeit: " + uhrzeit + ";\t");
System.out.print("Heim: " + heim + ";\t");
System.out.print("Gast: " + gast + ";\t");
System.out.print("Tore Heim: " + tore_heim + ";\t");
System.out.print("Tore Gast: " + tore_gast + ";");
System.out.println();
}
rs.close();
System.out.println();
disconnect();
}
@Override
public List<MstGasto> read() {
Connection cn;
PreparedStatement pst;
ResultSet rs;
String sql;
List<MstGasto> lst = new ArrayList();
try {
Class.forName(bd.getDriver());
cn = DriverManager.getConnection(bd.getUrl(), bd.getUser(), bd.getPasswd());
sql = "select * from mst_tipo_gastos order by corr_gasto";
pst = cn.prepareStatement(sql);
rs = pst.executeQuery();
while (rs.next()) {
lst.add(
new MstGasto(
rs.getInt("cod_residencial"),
rs.getInt("corr_gasto"),
rs.getString("desc_gasto"),
rs.getString("cod_cta_conta"),
rs.getDouble("valor_gasto"),
rs.getDate("fecha_creacion"),
rs.getString("cod_usuario"),
rs.getString("activo")));
}
rs.close();
pst.close();
cn.close();
} catch (SQLException e) {
log.severe(e.toString());
} catch (Exception e) {
log.severe(e.toString());
}
return lst;
}
private Vector getNextRow(ResultSet rs, ResultSetMetaData rsmd) throws SQLException {
Vector currentRow = new Vector();
for (int i = 1; i <= rsmd.getColumnCount(); ++i)
switch (rsmd.getColumnType(i)) {
case Types.VARCHAR:
currentRow.addElement(rs.getString(i));
break;
case Types.INTEGER:
currentRow.addElement(new Long(rs.getLong(i)));
break;
case Types.DATE:
currentRow.addElement(rs.getDate(i));
break;
case Types.FLOAT:
currentRow.addElement(rs.getFloat(i));
break;
case Types.CHAR:
currentRow.addElement(rs.getString(i));
break;
case Types.REAL:
currentRow.addElement(rs.getFloat(i));
break;
default:
System.out.println("Type was: " + rsmd.getColumnTypeName(i));
}
return currentRow;
}
private ServicePrice constructPriceEntity(ResultSet rs) throws SQLException {
ServicePrice servicePrice = new ServicePrice();
servicePrice.setPrice(rs.getBigDecimal("price"));
servicePrice.setServiceId(rs.getInt("service_id"));
servicePrice.setDate(rs.getDate("date").toLocalDate());
return servicePrice;
}
public static Card getCard(Connection con, String card_id) throws Exception {
Card cardObj = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
sql =
"select valid,a.card_id,a.regdate, a.card_no, a.member_id, a.card_class, a.op_type, a.moneyPaid, paidOfPro, con_no, class_name, markup "
+ " from t_card a "
+ " where a.card_id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, card_id);
rs = pstmt.executeQuery();
if (rs.next()) {
cardObj = new Card();
cardObj.card_id = rs.getString("card_id");
cardObj.regdate = rs.getDate("regdate");
cardObj.card_no = rs.getString("card_no");
cardObj.member_id = rs.getString("member_id");
cardObj.card_class = rs.getString("card_class");
cardObj.op_type = rs.getInt("op_type");
cardObj.setMoneypaid(JUtil.convertNull(rs.getString("moneyPaid")));
cardObj.paidofpro = JUtil.convertNull(rs.getString("paidOfPro"));
cardObj.con_no = JUtil.convertNull(rs.getString("con_no"));
cardObj.class_name = JUtil.convertNull(rs.getString("class_name"));
cardObj.markup = JUtil.convertNull(rs.getString("markup"));
cardObj.valid = rs.getInt("valid");
}
rs.close();
pstmt.close();
return cardObj;
}
@Override
public List<Ordine> listaOrdini() throws PersistenceException { // ok
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
List<Ordine> ordini = null;
Ordine ordine = null;
try {
String str =
"select ordini.codice as codice,stato,data,cliente,id "
+ "from ordini left outer join clienti on ordini.cliente=clienti.codice";
statement = connection.prepareStatement(str);
ResultSet result = statement.executeQuery();
ordini = new ArrayList<Ordine>();
while (result.next()) {
ordine = new Ordine();
ClienteDAOImpl cliente = new ClienteDAOImpl();
ordine.setCliente(cliente.getClienteById(result.getInt("cliente")));
ordine.setCodiceOrdine(result.getString("codice"));
ordine.setData(new java.util.Date(result.getDate("data").getDate()));
ordine.setStato(result.getString("stato"));
ordine.setId(result.getInt("id"));
ordini.add(ordine);
}
} 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 ordini;
}
@Override
public ArrayList<Emp> getList() {
ArrayList<Emp> emps = new ArrayList<Emp>(100);
if (conn == null) {
System.err.println("No connect!!");
return emps;
}
try {
Statement st = conn.createStatement();
ResultSet rs =
st.executeQuery(
"" + "SELECT e.id,e.lname,e.fname,e.mname,e.d_hire,e.d_fire " + "FROM DDT_EMP AS e");
while (rs.next()) {
Emp emp = new Emp();
emp.setId(rs.getInt("id"));
emp.setLastName(rs.getString("lname"));
emp.setFirstName(rs.getString("fname"));
emp.setMiddleName(rs.getString("mname"));
emp.setHireDate(rs.getDate("d_hire"));
emp.setFireDate(rs.getDate("d_fire"));
emps.add(emp);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return emps;
}
public static void fillTable(String aQuery, JTable aTable, Connector aConnector)
throws SQLException {
((DefaultTableModel) aTable.getModel()).setRowCount(0);
((DefaultTableModel) aTable.getModel()).setColumnCount(0);
ResultSet results = aConnector.sendQuery(aQuery);
ResultSetMetaData metadata = results.getMetaData();
cols = metadata.getColumnCount();
rowData = new Object[cols];
colNames = new String[cols];
for (int i = 0; i < cols; i++) {
colNames[i] = metadata.getColumnName(i + 1);
}
for (int i = 0; i < cols; i++) {
((DefaultTableModel) aTable.getModel()).addColumn(colNames[i]);
}
while (results.next()) {
for (int i = 0; i < cols; i++) {
if (metadata.getColumnName(i + 1).equalsIgnoreCase("DatePosted")) {
rowData[i] = StrVal.SqlStringToString(results.getDate(i + 1).toString());
rowData[i] += " || ";
rowData[i] += results.getTime(i + 1).toString();
} else rowData[i] = results.getString(i + 1);
}
((DefaultTableModel) aTable.getModel()).addRow(rowData);
}
}
@Override
public Emp get(Integer objectId) {
if (conn == null) {
System.err.println("No connect!!");
return null;
}
Emp emp = null;
try {
final String sql =
"SELECT " + "id,lname,fname,mname,d_hire,d_fire " + "FROM DDT_EMP" + " WHERE id = ?";
PreparedStatement st = conn.prepareStatement(sql);
st.setInt(1, objectId.intValue());
ResultSet rs = st.executeQuery();
while (rs.next()) {
emp = new Emp();
emp.setId(rs.getInt("id"));
emp.setLastName(rs.getString("lname"));
emp.setFirstName(rs.getString("fname"));
emp.setMiddleName(rs.getString("mname"));
emp.setHireDate(rs.getDate("d_hire"));
emp.setFireDate(rs.getDate("d_fire"));
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return emp;
}
private boolean validate(int type, String columnName, ResultSet resultSet0, ResultSet resultSet1)
throws SQLException {
switch (type) {
case Types.DATE:
return resultSet0.getDate(columnName).equals(resultSet1.getDate(columnName));
case Types.BOOLEAN:
return resultSet0.getBoolean(columnName) == resultSet1.getBoolean(columnName);
case Types.BIGINT:
return resultSet0.getLong(columnName) == resultSet1.getLong(columnName);
case Types.INTEGER:
return resultSet0.getInt(columnName) == resultSet1.getInt(columnName);
case Types.VARCHAR:
default:
return resultSet0.getString(columnName).equals(resultSet1.getString(columnName));
}
}
@Override
public List<Ordine> getOrdiniPerCliente(Cliente cliente) throws PersistenceException { // ok
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
Ordine o = null;
List<Ordine> lo = new LinkedList<Ordine>();
try {
String str = "select codice,data,stato,id from ordini where cliente=?";
statement = connection.prepareStatement(str);
statement.setInt(1, cliente.getId());
ResultSet result = statement.executeQuery();
while (result.next()) {
o = new Ordine();
o.setCliente(cliente);
o.setCodiceOrdine(result.getString("codice"));
o.setData(result.getDate("data"));
o.setStato(result.getString("stato"));
o.setId(result.getInt("id"));
lo.add(o);
// da completare per restituire ordine completo di righe
}
} 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 lo;
}
/**
* Method to build a vector from a ResultSet
*
* @param <code>ResultSet rsResult</code> the result from a query
* @return Vector of objects from the ResultSet
* @exception Throws Exception on error
*/
protected Vector buildObjectVector(ResultSet rsResult) throws Exception {
// vector for return data
Vector vReturn = null;
try {
// init the vector
vReturn = new Vector(super.VECT_INIT_SIZE, super.VECT_GROW_SIZE);
// loop through the entire result set
while (rsResult.next()) {
// create a new one
StageUsacForm objTmp = new StageUsacForm();
// set the attributes
// ADDING A NEW FIELD "ROWID"
objTmp.ROWID = rsResult.getString("ROWID");
objTmp.HDR_SPIN = rsResult.getLong("HDR_SPIN");
objTmp.SPIN_NM = rsResult.getString("SPIN_NM");
objTmp.RCPNT_EMAIL = rsResult.getString("RCPNT_EMAIL");
objTmp.USAC_EMAIL = rsResult.getString("USAC_EMAIL");
objTmp.RFRNC_NMBR = rsResult.getString("RFRNC_NMBR");
objTmp.RCRD_CNT = rsResult.getLong("RCRD_CNT");
objTmp.TOT_PAYMENT = rsResult.getDouble("TOT_PAYMENT");
objTmp.USAC_PRCS_DAT = rsResult.getDate("USAC_PRCS_DAT");
objTmp.RTRCT_FLAG = rsResult.getString("RTRCT_FLAG");
objTmp.DTL_SPIN = rsResult.getLong("DTL_SPIN");
objTmp.FRN = rsResult.getLong("FRN");
objTmp.SDC_INV_NO = rsResult.getString("SDC_INV_NO");
objTmp.AMT_PAID = rsResult.getDouble("AMT_PAID");
objTmp.DSBRSMNT_TXT = rsResult.getString("DSBRSMNT_TXT");
objTmp.EMAIL_DATE = rsResult.getDate("EMAIL_DATE");
objTmp.FILENAME = rsResult.getString("FILENAME");
objTmp.PROCESS_DATE = rsResult.getDate("PROCESS_DATE");
objTmp.STATUS = rsResult.getLong("STATUS");
// add it to the vector
vReturn.addElement(objTmp);
}
return vReturn;
} catch (Exception e) {
throw new Exception("BuildObjectVector()\n" + e.getMessage());
}
}
private String handleDate(ResultSet rs, int columnIndex) throws SQLException {
java.sql.Date date = rs.getDate(columnIndex);
String value = null;
if (date != null) {
SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
value = dateFormat.format(date);
}
return value;
}
@SuppressWarnings("deprecation")
@Test
public void date() throws SQLException {
connectWithJulianDayModeActivated();
Date d1 = new Date(System.currentTimeMillis());
stat.execute("create table t (c1);");
PreparedStatement prep = conn.prepareStatement("insert into t values(?);");
prep.setDate(1, d1);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select c1 from t;");
assertTrue(rs.next());
assertEquals(d1.getYear(), rs.getDate(1).getYear());
assertEquals(d1.getMonth(), rs.getDate(1).getMonth());
assertEquals(d1.getDay(), rs.getDate(1).getDay());
rs.close();
}
/**
* aggregation - group by
*
* @throws Exception
*/
@Test
public void testDateGroupBy() throws Exception {
printTestDescription();
Date date = new Date(1);
initATableValues(date);
try {
ResultSet rs;
String query = null;
if (tgtPH())
query =
"SELECT a_date, count(1) FROM atable WHERE organization_id='"
+ tenantId
+ "' group by a_date";
else if (tgtSQ() || tgtTR())
query =
"SELECT a_date, count(1) FROM atable WHERE organization_id='"
+ tenantId
+ "' group by a_date order by 1";
rs = getResultSet(conn, query);
/* 3 rows in expected result:
* 1969-12-31 3
* 1970-01-01 3
* 1970-01-02 3
* */
assertTrue(rs.next());
if (tgtPH()) assertEquals(date, rs.getDate(1));
else if (tgtSQ() || tgtTR()) assertEquals(date.toString(), rs.getDate(1).toString());
assertEquals(3, rs.getInt(2));
// the following assertions fails
assertTrue(rs.next());
assertEquals(3, rs.getInt(2));
assertTrue(rs.next());
assertEquals(3, rs.getInt(2));
assertFalse(rs.next());
} finally {
}
}
@Test
public void date1() throws SQLException {
Date d1 = new Date(987654321);
stat.execute("create table t (c1);");
PreparedStatement prep = conn.prepareStatement("insert into t values(?);");
prep.setDate(1, d1);
prep.executeUpdate();
prep.setDate(1, null);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select c1 from t;");
assertTrue(rs.next());
assertEquals(d1.getTime(), rs.getLong(1));
assertTrue(rs.getDate(1).equals(d1));
assertTrue(rs.next());
assertEquals(null, rs.getDate(1));
rs.close();
}
// ---------------------------------------------------------------//
public ArrayList<Project> selectAllProjects() throws SQLException {
Connection conn = null;
PreparedStatement prepStmt = null;
ArrayList<Project> rtrn = null;
try {
conn = select();
String sql =
"SELECT pr.*,cst.CUST_NAME FROM PROJECTS pr,CUSTOMERS cst WHERE cst.CUST_ID=pr.CUST_ID";
prepStmt = conn.prepareStatement(sql);
ResultSet rs = prepStmt.executeQuery();
rtrn = new ArrayList<Project>();
while (rs.next()) {
Project proj =
new Project(
rs.getInt("PROJ_ID"),
rs.getString("PROJ_NAME"),
rs.getInt("CUST_ID"),
rs.getString("PROJ_TYPE"),
rs.getDate("PROJ_FROM"),
rs.getDate("PROJ_TO"),
rs.getDate("PROJ_DEADLINE"),
(rs.getString("PROJ_ACTIVE")).equals("Y"),
rs.getFloat("PROJ_BUDGET"),
rs.getString("PROJ_DESCRIPTION"),
rs.getTimestamp("INSERTED_AT"),
rs.getString("INSERTED_BY"),
rs.getTimestamp("MODIFIED_AT"),
rs.getString("MODIFIED_BY"),
rs.getInt("ROWVERSION"),
rs.getInt("PAR_PROJ_ID"),
rs.getString("CUST_NAME"));
rtrn.add(proj);
}
rs.close();
} finally {
if (prepStmt != null) conn.close();
if (conn != null) conn.close();
}
return rtrn;
}
public long getDate(int n) {
try {
ResultSet rs = getResultSet();
if (rs == null) return 0;
Date ts = rs.getDate(n);
return ts.getTime();
} catch (Exception e) {
Debug.warning(e);
}
return 0;
}
public ArrayList<Listing> getAllListing() throws SQLException {
start();
rs = stmt.executeQuery("SELECT * FROM listingInfo");
ArrayList<Listing> rtrn = new ArrayList<Listing>();
while (rs.next()) {
Listing temp = new Listing();
temp.setUserName(rs.getString(2));
temp.setRacquetID(rs.getInt(3));
temp.setPrice(rs.getDouble(4));
temp.setNewOrOld(rs.getString(5));
temp.setDateListed(rs.getDate(6));
temp.setDateSold(rs.getDate(7));
// temp.setSellerRating(rs.getInt(8));
temp.setDescription(rs.getString(9));
temp.setPicURL(rs.getString(10));
rtrn.add(temp);
}
close();
return rtrn;
}
/**
* An welchem Tag fand das erste Spiel der Saison statt?
*
* @param db Hostadresse der Datenbank
* @param user Benutzername für Datenbank
* @param password Passwort für Datenbank
* @throws SQLException Exception für Datenbankzugriffe benötigt
* @throws ClassNotFoundException Exception für Postgres-Verbindung benötigt
* @see #connect(String, String, String)
* @see #disconnect()
*/
public static void select1(String db, String user, String password)
throws SQLException, ClassNotFoundException {
connect(db, user, password);
System.out.println("Das erste Spiel der Saison:");
ResultSet rs = stmt.executeQuery("SELECT datum FROM bl.spiel ORDER BY datum ASC LIMIT 1;");
while (rs.next()) {
Date datum = rs.getDate("datum");
System.out.println("Das erste Spiel der Saison fand statt am " + datum);
}
rs.close();
System.out.println();
disconnect();
}
@Test
public void testAjoutCreneau() throws SQLException, IOException, ClassNotFoundException {
Connection connection = BddConnecteur.getConnection();
BddUtilisateur.ajout("*****@*****.**", "labri");
BddLabo.ajout(1, "Labri");
BddAtelier.ajoutAtelier(
1,
"A la poursuite d'ennemis invisibles",
"Sciences de la vie ",
"Campus Carreire (Hôpital Pellegrin)",
"Labo MFP",
"",
"",
"",
"");
// Insertion
int idAtelier = 1;
String jour = "2015-12-23";
String heure = "13:00";
int capacite = 20;
BddCreneau.ajoutCreneau(1, jour, heure, capacite);
// Vérification
String sql = "SELECT * FROM Creneau WHERE idAtelier='" + idAtelier + "'";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
int count = 0;
Date jourRecup;
Time heureRecup;
int capaciteRecup = 0;
while (rs.next()) {
jourRecup = rs.getDate("jour");
heureRecup = rs.getTime("heure");
capaciteRecup = rs.getInt("capacite");
count++;
}
assertEquals(count, 1);
// assertEquals(jourRecup, jour);
// assertEquals(heureRecup, heure);
assertEquals(capaciteRecup, capacite);
// Fermeture
rs.close();
statement.close();
connection.close();
}
// ---------------------------------------------------------------//
public ArrayList<Task> selectTaskforProjID(int projID) throws SQLException {
Connection conn = null;
PreparedStatement prepStmt = null;
ArrayList<Task> rtrn = null;
try {
conn = select();
String sql = "SELECT * FROM TASKS WHERE PROJ_ID=?";
prepStmt = conn.prepareStatement(sql);
prepStmt.setInt(1, projID);
ResultSet rs = prepStmt.executeQuery();
rtrn = new ArrayList<Task>();
while (rs.next()) {
Task task =
new Task(
rs.getInt("TASK_ID"),
rs.getInt("PROJ_ID"),
rs.getString("TASK_DESCRIPTION"),
rs.getString("TASK_NOTES"),
rs.getDate("TASK_DEADLINE"),
rs.getDate("TASK_FROM"),
rs.getDate("TASK_TO"),
rs.getString("TASK_ACTIVE").equals("Y"),
rs.getString("TASK_TYPE"),
rs.getString("TASK_USER_NOTES"),
rs.getInt("ROWVERSION"),
rs.getTimestamp("INSERTED_AT"),
rs.getTimestamp("MODIFIED_AT"),
rs.getString("INSERTED_BY"),
rs.getString("MODIFIED_BY"));
rtrn.add(task);
}
rs.close();
} finally {
if (prepStmt != null) conn.close();
if (conn != null) conn.close();
}
return rtrn;
}
