@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;
}
@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;
}
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());
}
@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 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());
}
}
/**
* 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 {
}
}
/**
* Gets a value from a given column in a JDBC result set.
*
* @param i Ordinal of column (1-based, per JDBC)
*/
private Object value(int i) throws SQLException {
// MySQL returns timestamps shifted into local time. Using
// getTimestamp(int, Calendar) with a UTC calendar should prevent this,
// but does not. So we shift explicitly.
switch (types[i]) {
case Types.TIMESTAMP:
return shift(resultSet.getTimestamp(i + 1));
case Types.TIME:
return shift(resultSet.getTime(i + 1));
case Types.DATE:
return shift(resultSet.getDate(i + 1));
}
return primitives[i].jdbcGet(resultSet, i + 1);
}
public List<Oriundo> listar() throws SQLException {
List<Oriundo> resultado = new ArrayList<Oriundo>();
conexao propCon = new conexao();
Connection con =
DriverManager.getConnection(
new conexao().url,
propCon.config.getString("usuario"),
propCon.config.getString("senha"));
PreparedStatement ps = null;
ResultSet rs = null;
String sqlListar = "SELECT * FROM oriundo Order by codigo DESC";
Oriundo oriundo;
try {
ps = con.prepareStatement(sqlListar);
rs = ps.executeQuery();
// if(rs==null){
// return null;
// }
while (rs.next()) {
oriundo = new Oriundo();
oriundo.setCodigo(rs.getInt("codigo"));
oriundo.setDescricao(rs.getString("descricao"));
oriundo.setData_cadastro(rs.getDate("data_cadastro"));
oriundo.setDia_fechamento(rs.getInt("dia_fechamento"));
oriundo.setDia_pag(rs.getInt("dia_pag"));
resultado.add(oriundo);
}
} catch (NumberFormatException e) {
JOptionPane.showMessageDialog(
null, "NumberFormaterExeption Erro: " + e.getMessage(), "ClasseDAO Func.Localizar", 0);
e.printStackTrace();
} catch (NullPointerException e) {
JOptionPane.showMessageDialog(
null, "NullPointerException Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
e.printStackTrace();
} catch (SQLException e) {
JOptionPane.showMessageDialog(
null, "SQLException Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
e.printStackTrace();
} catch (Exception e) {
JOptionPane.showMessageDialog(
null, "Exception Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
e.printStackTrace();
} finally {
ps.close();
con.close();
}
return resultado;
}
public Oriundo localizar(Integer codigo) throws SQLException {
Connection con =
DriverManager.getConnection(
new conexao().url,
new conexao().config.getString("usuario"),
new conexao().config.getString("senha"));
PreparedStatement ps = null;
ResultSet rs = null;
String sqlLocalizar = "SELECT * FROM oriundo WHERE codigo=?";
Oriundo oriundo = new Oriundo();
try {
ps = con.prepareStatement(sqlLocalizar);
ps.setInt(1, codigo);
rs = ps.executeQuery();
// if(!rs.next()){
// return null;
// }
oriundo.setCodigo(rs.getInt("codigo"));
oriundo.setDescricao(rs.getString("descricao"));
oriundo.setData_cadastro(rs.getDate("data_cadastro"));
oriundo.setDia_fechamento(rs.getInt("dia_fechamento"));
oriundo.setDia_pag(rs.getInt("dia_pag"));
return oriundo;
} catch (NumberFormatException e) {
JOptionPane.showMessageDialog(
null, "NumberFormaterExeption Erro: " + e.getMessage(), "ClasseDAO Func.Localizar", 0);
e.printStackTrace();
} catch (NullPointerException e) {
JOptionPane.showMessageDialog(
null, "NullPointerException Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
e.printStackTrace();
} catch (SQLException e) {
JOptionPane.showMessageDialog(
null, "SQLException Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
e.printStackTrace();
} catch (Exception e) {
JOptionPane.showMessageDialog(
null, "Exception Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
e.printStackTrace();
} finally {
ps.close();
con.close();
}
return oriundo;
}
private Asset _getAset(int idAset) throws SQLException {
String query = "SELECT * FROM " + ASSET_TABLE + " WHERE id = " + idAset;
ResultSet rs = executeQuery(query);
Asset asset = null;
if (rs.next()) {
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"));
}
return asset;
}
public static List<Movie> getAllMovies() {
String query = "SELECT * FROM movie";
List<Movie> movies = new LinkedList<>();
try (Connection connection = Config.getConnection(); // step 1
Statement statement = connection.createStatement(); // step 2
ResultSet result = statement.executeQuery(query); ) { // step 3 and 4
while (result.next()) { // step 5
Movie movie = new Movie();
// you should be validating the following,
// this is just an example to get you started
movie.setName(result.getString(1));
movie.setYear(result.getDate(2).getYear());
movie.setUrl(result.getString(3));
movies.add(movie);
}
} catch (SQLException e) {
System.err.println(e.getMessage());
System.err.println(e.getStackTrace());
}
return movies;
}
@Override
public Ordine getOrdineByCodice(String codice) throws PersistenceException {
Ordine ordine = null;
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
try {
String query =
"select ordini.codice,ordini.id as id,data,stato,cliente,nome,partitaiva,indirizzo "
+ "from ordini LEFT OUTER JOIN clienti on cliente = clienti.id WHERE ordini.codice=?";
statement = connection.prepareStatement(query);
statement.setString(1, codice);
ResultSet result = statement.executeQuery();
if (result.next()) {
ordine = new Ordine();
ordine.setCodiceOrdine(codice);
ordine.setStato(result.getString("stato"));
ordine.setId(result.getInt("id"));
ordine.setData(new java.util.Date(result.getDate("data").getTime()));
Cliente cliente = new Cliente();
cliente.setId(result.getInt("cliente"));
cliente.setNome(result.getString("nome"));
cliente.setIndirizzo(result.getString("indirizzo"));
cliente.setPiva(result.getString("partitaiva"));
ordine.setCliente(cliente);
}
} 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 ordine;
}
@WebMethod
public List<Asset> getAsetByJenis(String jenis) throws SQLException {
String query = "SELECT * FROM " + ASSET_TABLE + " WHERE jenis= '" + jenis + "'";
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;
}
private void loadCompany(Connection con, String unit_code) throws Exception {
PreparedStatement pstmt = null;
ResultSet rs = null;
String qry = null;
JCompany obj = null;
qry =
" select unit_id, unit_code, unit_name, biz_lx, biz_ym, biz_fl, biz_yx, biz_ch, "
+ " company_fullName, company_addr,company_district,unit_created "
+ " from t_unit "
+ " where unit_code=? ";
pstmt = con.prepareStatement(qry);
pstmt.setString(1, unit_code.toUpperCase());
rs = pstmt.executeQuery();
if (rs.next()) {
Set<String> bizSet = null;
String[] bizTypes = null;
String szId, szCode, szName;
bizSet = new HashSet<String>();
szId = rs.getString("unit_id");
szCode = rs.getString("unit_code");
szName = JUtil.convertNull(rs.getString("unit_name"));
{
if (rs.getInt("biz_lx") == 1) bizSet.add("留学");
if (rs.getInt("biz_ym") == 1) bizSet.add("移民");
if (rs.getInt("biz_yx") == 1) bizSet.add("团组");
if (rs.getInt("biz_fl") == 1) bizSet.add("外语");
if (rs.getInt("biz_ch") == 1) bizSet.add("华文");
bizTypes = new String[bizSet.size()];
int i = 0;
for (String biz : bizSet) {
bizTypes[i++] = biz;
}
}
obj = new JCompany(szId, szCode, szName, bizTypes);
obj.setCompany_fullName(rs.getString("company_fullName"));
obj.setCompany_address(rs.getString("company_addr"));
obj.setCompany_district(rs.getString("company_district"));
obj.setUnit_created(rs.getDate("unit_created"));
obj.setBranch(getChildBranch(con, szCode));
}
rs.close();
pstmt.close();
if (obj == null) return;
/** ******** 放到 列表 中 ****************** */
String szId = obj.getCompany_id();
String szCode = obj.getCompany_code();
lsBizCompany.remove(szId);
if (!szCode.startsWith("XT_NB.")) lsBizCompany.add(szId);
lsOACompany.contains(szId);
lsOACompany.add(szId);
mapCompany.put(obj.getCompany_code(), obj);
int idx = lsCompany.size();
for (int i = 0; i < lsCompany.size(); i++) {
JCompany com = lsCompany.get(i);
if (com.getCompany_id().equals(szId)) {
idx = i;
lsCompany.remove(i);
break;
}
}
lsCompany.add(idx, obj);
}
public void setContent(String cat) {
cat = cat.trim();
selectAllCB.setVisible(false);
selectAllCB.setSelected(false);
deleteBut.setVisible(false);
restoreBut.setVisible(false);
refreshBut.setVisible(true);
Object columns[] = null;
int count = 0;
switch (cat) {
case "Inbox":
columns = new Object[] {"", "From", "Date", "Subject", "Content"};
count = Database.getCount("Inbox");
workingSet = db.getData("SELECT * FROM messages WHERE tag='inbox' ORDER BY msg_id desc");
;
break;
case "SentMail":
columns = new Object[] {"", "To", "Date", "Subject", "Content"};
count = Database.getCount("Sentmail");
workingSet = db.getData("SELECT * FROM messages WHERE tag='sentmail' ORDER BY msg_id desc");
break;
case "Draft":
columns = new Object[] {"", "To", "Date", "Subject", "Content"};
count = Database.getCount("Draft");
workingSet = db.getData("SELECT * FROM messages WHERE tag='draft' ORDER BY msg_id desc");
break;
case "Outbox":
columns = new Object[] {"", "To", "Date", "Subject", "Content"};
count = Database.getCount("Outbox");
workingSet = db.getData("SELECT * FROM messages WHERE tag='outbox' ORDER BY msg_id desc");
break;
case "Trash":
// restoreBut.setVisible(true);
columns = new Object[] {"", "To/From", "Date", "Subject", "Content"};
count = Database.getCount("Trash");
workingSet =
db.getData(
"SELECT * FROM messages,trash WHERE messages.tag='trash' and messages.msg_id=trash.msg_id ORDER BY deleted_at desc");
break;
default:
System.out.println("in default case");
}
if (count > 0) {
selectAllCB.setVisible(true);
rows = new Object[count][];
msgID = new int[count];
try {
workingSet.beforeFirst();
for (int i = 0; i < count && workingSet.next(); i++) {
msgID[i] = workingSet.getInt(1);
rows[i] =
new Object[] {
false,
workingSet.getString(2),
workingSet.getDate(3),
workingSet.getString(4),
workingSet.getString(5)
};
}
} catch (SQLException sqlExc) {
JOptionPane.showMessageDialog(null, sqlExc, "EXCEPTION", JOptionPane.ERROR_MESSAGE);
sqlExc.printStackTrace();
}
tableModel = new MyDefaultTableModel(rows, columns);
table = new JTable(tableModel);
table.getSelectionModel().addListSelectionListener(this);
table.addMouseListener(this);
table.getTableHeader().setOpaque(true);
table.getTableHeader().setReorderingAllowed(false);
// table.getTableHeader().setBackground(Color.blue);
table.getTableHeader().setForeground(Color.blue);
// table.setRowSelectionAllowed(false);
// table.setColumnSelectionAllowed(false);
table.setFont(new Font(Font.SANS_SERIF, Font.PLAIN, 14));
table.setRowHeight(20);
table.setFillsViewportHeight(true);
TableColumn column = null;
for (int i = 0; i < 5; i++) {
column = table.getColumnModel().getColumn(i);
if (i == 0) {
column.setPreferredWidth(6);
} else if (i == 3) {
column.setPreferredWidth(250);
} else if (i == 4) {
column.setPreferredWidth(450);
} else {
column.setPreferredWidth(40);
}
}
table.setAutoResizeMode(JTable.AUTO_RESIZE_SUBSEQUENT_COLUMNS);
remove(contentPan);
contentPan = new JScrollPane(table);
contentPan.setBackground(Color.orange);
contentPan.setOpaque(true);
contentPan.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
add(contentPan, "Center");
Home.home.homeFrame.setVisible(true);
} else {
JPanel centPan = new JPanel(new GridBagLayout());
centPan.setBackground(new Color(52, 86, 70));
JLabel label = new JLabel("No Messages In This Category");
label.setFont(new Font(Font.SANS_SERIF, Font.BOLD, 22));
label.setForeground(Color.orange);
centPan.add(label);
remove(contentPan);
contentPan = new JScrollPane(centPan);
contentPan.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
add(contentPan, "Center");
contentPan.repaint();
}
}
public void localTimestamps(String timeZone) throws Exception {
final String testDateFormat = "yyyy-MM-dd HH:mm:ss";
final List<String> datesToTest =
Arrays.asList(
"2015-09-03 12:00:00",
"2015-06-30 23:59:58",
"1997-06-30 23:59:59",
"1997-07-01 00:00:00",
"2012-06-30 23:59:59",
"2012-07-01 00:00:00",
"2015-06-30 23:59:59",
"2015-07-01 00:00:00",
"2005-12-31 23:59:59",
"2006-01-01 00:00:00",
"2008-12-31 23:59:59",
"2009-01-01 00:00:00",
"2015-06-30 23:59:60",
"2015-07-31 00:00:00",
"2015-07-31 00:00:01",
// On 2000-03-26 02:00:00 Moscow went to DST, thus local time became 03:00:00
"2000-03-26 01:59:59",
"2000-03-26 02:00:00",
"2000-03-26 02:00:01",
"2000-03-26 02:59:59",
"2000-03-26 03:00:00",
"2000-03-26 03:00:01",
"2000-03-26 03:59:59",
"2000-03-26 04:00:00",
"2000-03-26 04:00:01",
// On 2000-10-29 03:00:00 Moscow went to regular time, thus local time became 02:00:00
"2000-10-29 01:59:59",
"2000-10-29 02:00:00",
"2000-10-29 02:00:01",
"2000-10-29 02:59:59",
"2000-10-29 03:00:00",
"2000-10-29 03:00:01",
"2000-10-29 03:59:59",
"2000-10-29 04:00:00",
"2000-10-29 04:00:01");
con.createStatement().execute("delete from testtimezone");
Statement stmt = con.createStatement();
for (int i = 0; i < datesToTest.size(); i++) {
stmt.execute(
"insert into testtimezone (ts, seq) values ('" + datesToTest.get(i) + "', " + i + ")");
}
// Different timezone test should have different sql text, so we test both text and binary modes
PreparedStatement pstmt =
con.prepareStatement("SELECT ts FROM testtimezone order by seq /*" + timeZone + "*/");
Calendar expectedTimestamp = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat(testDateFormat);
for (int i = 0; i < PREPARE_THRESHOLD; i++) {
ResultSet rs = pstmt.executeQuery();
for (int j = 0; rs.next(); j++) {
String testDate = datesToTest.get(j);
Date getDate = rs.getDate(1);
Timestamp getTimestamp = rs.getTimestamp(1);
String getString = rs.getString(1);
Time getTime = rs.getTime(1);
expectedTimestamp.setTime(sdf.parse(testDate));
assertEquals(
"getTimestamp: "
+ testDate
+ ", transfer format: "
+ (i == 0 ? "text" : "binary")
+ ", timeZone: "
+ timeZone,
sdf.format(expectedTimestamp.getTimeInMillis()),
sdf.format(getTimestamp));
assertEquals(
"getString: "
+ testDate
+ ", transfer format: "
+ (i == 0 ? "text" : "binary")
+ ", timeZone: "
+ timeZone,
sdf.format(expectedTimestamp.getTimeInMillis()),
sdf.format(sdf.parse(getString)));
expectedTimestamp.set(Calendar.HOUR_OF_DAY, 0);
expectedTimestamp.set(Calendar.MINUTE, 0);
expectedTimestamp.set(Calendar.SECOND, 0);
assertEquals(
"getDate: "
+ testDate
+ ", transfer format: "
+ (i == 0 ? "text" : "binary")
+ ", timeZone: "
+ timeZone,
sdf.format(expectedTimestamp.getTimeInMillis()),
sdf.format(getDate));
expectedTimestamp.setTime(sdf.parse(testDate));
expectedTimestamp.set(Calendar.YEAR, 1970);
expectedTimestamp.set(Calendar.MONTH, 0);
expectedTimestamp.set(Calendar.DAY_OF_MONTH, 1);
assertEquals(
"getTime: "
+ testDate
+ ", transfer format: "
+ (i == 0 ? "text" : "binary")
+ ", timeZone: "
+ timeZone,
sdf.format(expectedTimestamp.getTimeInMillis()),
sdf.format(getTime));
}
rs.close();
}
}
/**
* This method must be overridden by the subclass to retrieve data and return the valorized value
* object.
*
* @param valueObjectClass value object class
* @return a VOResponse object if data loading is successfully completed, or an ErrorResponse
* object if an error occours
*/
public Response loadData(Class valueObjectClass) {
Statement stmt = null;
try {
// since this method could be invoked also when selecting another row on the linked grid,
// the pk attribute must be recalculated from the grid...
int row = gridFrame.getGrid().getSelectedRow();
if (row != -1) {
TestVO gridVO = (TestVO) gridFrame.getGrid().getVOListTableModel().getObjectForRow(row);
pk = gridVO.getStringValue();
}
stmt = conn.createStatement();
ResultSet rset =
stmt.executeQuery(
"select DEMO4.TEXT,DEMO4.DECNUM,DEMO4.CURRNUM,DEMO4.THEDATE,DEMO4.COMBO,DEMO4.CHECK_BOX,DEMO4.RADIO,DEMO4.CODE,"
+ "DEMO4_LOOKUP.DESCRCODE,DEMO4.TA,DEMO4.FORMATTED_TEXT,DEMO4.URI,DEMO4.LINK_LABEL,DEMO4.YEAR,DEMO4.FILENAME "
+ "from DEMO4,DEMO4_LOOKUP where TEXT='"
+ pk
+ "' and DEMO4.CODE=DEMO4_LOOKUP.CODE");
if (rset.next()) {
DetailTestVO vo = new DetailTestVO();
vo.setCheckValue(
rset.getObject(6) == null || !rset.getObject(6).equals("Y")
? Boolean.FALSE
: Boolean.TRUE);
vo.setCombo(new ComboVO());
vo.getCombo().setCode(rset.getString(5));
// this is a simplification: in a real situation combo v.o. will be retrieved from the
// database...
Domain d = ClientSettings.getInstance().getDomain("ORDERSTATE");
if (vo.getCombo().getCode().equals("O")) vo.getCombo().setDescription("opened");
else if (vo.getCombo().getCode().equals("S")) vo.getCombo().setDescription("sospended");
else if (vo.getCombo().getCode().equals("D")) vo.getCombo().setDescription("delivered");
else if (vo.getCombo().getCode().equals("C")) vo.getCombo().setDescription("closed");
vo.setCurrencyValue(rset.getBigDecimal(3));
vo.setDateValue(rset.getDate(4));
vo.setNumericValue(rset.getBigDecimal(2));
vo.setRadioButtonValue(
rset.getObject(7) == null || !rset.getObject(7).equals("Y")
? Boolean.FALSE
: Boolean.TRUE);
vo.setStringValue(rset.getString(1));
vo.setLookupValue(rset.getString(8));
vo.setDescrLookupValue(rset.getString(9));
vo.setTaValue(rset.getString(10));
vo.setFormattedTextValue(rset.getString(11));
vo.setUri(rset.getString(12));
vo.setLinkLabel(rset.getString(13));
vo.setYear(rset.getBigDecimal(14));
vo.setFilename(rset.getString(15));
vo.setTooltipURI(vo.getUri());
try {
if (vo.getFilename() != null) {
File f = new File(vo.getFilename());
BufferedInputStream in = new BufferedInputStream(new FileInputStream(f));
byte[] bytes = new byte[(int) f.length()];
in.read(bytes);
in.close();
vo.setFile(bytes);
}
} catch (Exception ex) {
ex.printStackTrace();
}
stmt.close();
stmt = conn.createStatement();
rset =
stmt.executeQuery(
"select DEMO4_LIST_VALUES.CODE from DEMO4_LIST_VALUES where TEXT='" + pk + "'");
ArrayList codes = new ArrayList();
while (rset.next()) {
codes.add(rset.getString(1));
}
vo.setListValues(codes);
return new VOResponse(vo);
} else return new ErrorResponse("No data found.");
} catch (SQLException ex) {
ex.printStackTrace();
return new ErrorResponse(ex.getMessage());
} finally {
try {
stmt.close();
} catch (Exception ex1) {
}
}
}
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();
}
}
protected ReportParameterValue[] getParamValuesFromDataSource(
ReportParameter param, Map<String, Object> parameters) throws ProviderException {
Connection conn = null;
PreparedStatement pStmt = null;
ResultSet rs = null;
try {
ReportDataSource dataSource = param.getDataSource();
conn = dataSourceProvider.getConnection(dataSource.getId());
if (parameters == null || parameters.isEmpty()) {
pStmt = conn.prepareStatement(param.getData());
} else {
}
rs = pStmt.executeQuery();
ResultSetMetaData rsMetaData = rs.getMetaData();
boolean multipleColumns = false;
if (rsMetaData.getColumnCount() > 1) multipleColumns = true;
ArrayList<ReportParameterValue> v = new ArrayList<ReportParameterValue>();
while (rs.next()) {
ReportParameterValue value = new ReportParameterValue();
if (param.getClassName().equals("java.lang.String")) {
value.setId(rs.getString(1));
} else if (param.getClassName().equals("java.lang.Double")) {
value.setId(new Double(rs.getDouble(1)));
} else if (param.getClassName().equals("java.lang.Integer")) {
value.setId(new Integer(rs.getInt(1)));
} else if (param.getClassName().equals("java.lang.Long")) {
value.setId(new Long(rs.getLong(1)));
} else if (param.getClassName().equals("java.math.BigDecimal")) {
value.setId(rs.getBigDecimal(1));
} else if (param.getClassName().equals("java.util.Date")) {
value.setId(rs.getDate(1));
} else if (param.getClassName().equals("java.sql.Date")) {
value.setId(rs.getDate(1));
} else if (param.getClassName().equals("java.sql.Timestamp")) {
value.setId(rs.getTimestamp(1));
}
if (multipleColumns) {
value.setDescription(rs.getString(2));
}
v.add(value);
}
rs.close();
ReportParameterValue[] values = new ReportParameterValue[v.size()];
v.toArray(values);
return values;
} catch (Exception e) {
throw new ProviderException("Error retreiving param values from database: " + e.getMessage());
} finally {
try {
if (pStmt != null) pStmt.close();
if (conn != null) conn.close();
} catch (Exception c) {
log.error("Error closing");
}
}
}
private void getChannelList() {
Connection con = null;
ChannelInfo ci;
this.channel_data.clear();
try {
con = pool.getConnection(timeout);
ResultSet rs;
try (Statement s = con.createStatement()) {
rs = s.executeQuery("SELECT * FROM `channels`");
PreparedStatement s2;
ResultSet rs2;
while (rs.next()) {
ci = new ChannelInfo(rs.getString("channel"));
ci.setDefaultOptions();
ci.setReactiveChatter(
rs.getInt("reactive_chatter_level"), rs.getInt("chatter_name_multiplier"));
ci.setRandomChatter(rs.getInt("random_chatter_level"));
ci.setTwitterTimers(
rs.getFloat("tweet_bucket_max"), rs.getFloat("tweet_bucket_charge_rate"));
ci.setWarAutoMuzzle(rs.getBoolean("auto_muzzle_wars"));
ci.velociraptorSightings = rs.getInt("velociraptor_sightings");
ci.activeVelociraptors = rs.getInt("active_velociraptors");
ci.deadVelociraptors = rs.getInt("dead_velociraptors");
ci.killedVelociraptors = rs.getInt("killed_velociraptors");
if (rs.getDate("last_sighting_date") != null) {
long time = rs.getDate("last_sighting_date").getTime();
ci.lastSighting = new java.util.Date(time);
}
s2 =
con.prepareStatement(
"SELECT `setting`, `value` FROM `channel_chatter_settings` WHERE `channel` = ?");
s2.setString(1, rs.getString("channel"));
s2.executeQuery();
rs2 = s2.getResultSet();
while (rs2.next()) {
ci.addChatterSetting(rs2.getString("setting"), rs2.getBoolean("value"));
}
s2.close();
rs2.close();
s2 =
con.prepareStatement(
"SELECT `setting`, `value` FROM `channel_command_settings` WHERE `channel` = ?");
s2.setString(1, rs.getString("channel"));
s2.executeQuery();
rs2 = s2.getResultSet();
while (rs2.next()) {
ci.addCommandSetting(rs2.getString("setting"), rs2.getBoolean("value"));
}
s2.close();
rs2.close();
s2 =
con.prepareStatement(
"SELECT `account` FROM `channel_twitter_feeds` WHERE `channel` = ?");
s2.setString(1, rs.getString("channel"));
s2.executeQuery();
rs2 = s2.getResultSet();
while (rs2.next()) {
ci.addTwitterAccount(rs2.getString("account"));
}
s2.close();
rs2.close();
this.channel_data.put(ci.channel, ci);
this.saveChannelSettings(ci);
}
}
rs.close();
} catch (SQLException ex) {
Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
public void testGetDate() throws Exception {
con.createStatement()
.executeUpdate(
"INSERT INTO testtimezone(tstz,ts,d) VALUES('2005-01-01 15:00:00 +0300', '2005-01-01 15:00:00', '2005-01-01')");
PreparedStatement ps = con.prepareStatement("SELECT tstz,ts,d from testtimezone");
for (int i = 0; i < PREPARE_THRESHOLD; i++) {
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
checkDatabaseContents(
"SELECT tstz::text,ts::text,d::text from testtimezone",
new String[] {"2005-01-01 12:00:00+00", "2005-01-01 15:00:00", "2005-01-01"});
Date d;
// timestamptz: 2005-01-01 15:00:00+03
d = rs.getDate(1); // 2005-01-01 13:00:00 +0100 -> 2005-01-01 00:00:00 +0100
assertEquals(1104534000000L, d.getTime());
d = rs.getDate(1, cUTC); // 2005-01-01 12:00:00 +0000 -> 2005-01-01 00:00:00 +0000
assertEquals(1104537600000L, d.getTime());
d = rs.getDate(1, cGMT03); // 2005-01-01 15:00:00 +0300 -> 2005-01-01 00:00:00 +0300
assertEquals(1104526800000L, d.getTime());
d = rs.getDate(1, cGMT05); // 2005-01-01 07:00:00 -0500 -> 2005-01-01 00:00:00 -0500
assertEquals(1104555600000L, d.getTime());
d = rs.getDate(1, cGMT13); // 2005-01-02 01:00:00 +1300 -> 2005-01-02 00:00:00 +1300
assertEquals(1104577200000L, d.getTime());
// timestamp: 2005-01-01 15:00:00
d = rs.getDate(2); // 2005-01-01 00:00:00 +0100
assertEquals(1104534000000L, d.getTime());
d = rs.getDate(2, cUTC); // 2005-01-01 00:00:00 +0000
assertEquals(1104537600000L, d.getTime());
d = rs.getDate(2, cGMT03); // 2005-01-01 00:00:00 +0300
assertEquals(1104526800000L, d.getTime());
d = rs.getDate(2, cGMT05); // 2005-01-01 00:00:00 -0500
assertEquals(1104555600000L, d.getTime());
d = rs.getDate(2, cGMT13); // 2005-01-01 00:00:00 +1300
assertEquals(1104490800000L, d.getTime());
// date: 2005-01-01
d = rs.getDate(3); // 2005-01-01 00:00:00 +0100
assertEquals(1104534000000L, d.getTime());
d = rs.getDate(3, cUTC); // 2005-01-01 00:00:00 +0000
assertEquals(1104537600000L, d.getTime());
d = rs.getDate(3, cGMT03); // 2005-01-01 00:00:00 +0300
assertEquals(1104526800000L, d.getTime());
d = rs.getDate(3, cGMT05); // 2005-01-01 00:00:00 -0500
assertEquals(1104555600000L, d.getTime());
d = rs.getDate(3, cGMT13); // 2005-01-01 00:00:00 +1300
assertEquals(1104490800000L, d.getTime());
assertTrue(!rs.next());
rs.close();
}
}
