private String getMonth(String month) {
String query;
String i_month = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select to_number(to_char(to_date(?,'Month'),'MM')) from dual";
USFEnv.getLog().writeDebug("Dinvjrnl:Get Month - Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, month);
rs = pstmt.executeQuery();
if (rs.next()) {
i_month = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Month Conversion Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return i_month;
}
public Vector getUsacFiles(String invoice) {
Vector fileList = new Vector();
String query = "";
query =
query
+ " SELECT distinct filename, ' Date:'||to_char(usac_prcs_dat,'MM/DD/YYYY'),trunc(usac_prcs_dat) usac_prcs_dat ";
query = query + " FROM stage_usac_form ";
query = query + " WHERE rtrim(ltrim(sdc_inv_no)) = ?";
query = query + " ORDER BY usac_prcs_dat ";
USFEnv.getLog().writeDebug("getUsacFiles() Query :" + query, this, null);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = cConn.prepareStatement(query);
pstmt.setString(1, invoice);
rs = pstmt.executeQuery();
while (rs.next()) {
fileList.addElement(rs.getString(1));
fileList.addElement(rs.getString(2));
}
// rs.close();
// stmt.close();
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (Exception e) {
USFEnv.getLog().writeCrit("Unable to close ResultSet and statement", this, e);
}
USFEnv.getLog().writeCrit("getUsacFiles() Failed Query:" + query, this, ex);
}
/* try
{
if( rs != null)
rs.close();
}
catch(Exception e)
{
USFEnv.getLog().writeCrit("Unable to close ResultSet",this,null);
}
try
{
if( pstmt != null)
pstmt.close();
}
catch(Exception e)
{
USFEnv.getLog().writeCrit("Unable to close Prepared Statement",this,null);
}*/
return fileList;
}
/** EJECUTA UNA CONSULTA Y GENERA LA TABLA HTML */
protected String getHTML(String SQL) throws Exception {
StringBuffer html = new StringBuffer();
html.append("<TABLE border='1'>");
html.append("<TR>");
if (conectar()) {
ResultSet rs = ejecutarSQL(SQL);
ResultSetMetaData rsm = rs.getMetaData();
html.append("<TR>");
for (int i = 1; i <= rsm.getColumnCount(); i++) {
html.append("<TH>" + rsm.getColumnName(i) + "</TH>");
}
html.append("</TR>");
while (rs.next()) {
html.append("<TR>");
for (int i = 1; i <= rsm.getColumnCount(); i++) {
html.append("<TD>" + rs.getString(i) + ".</TD>");
}
html.append("</TR>");
}
desconectar();
}
html.append("</TR>");
html.append("</TABLE>");
return html.toString();
} // Fin getHTML
public static void main(String[] args)
throws SQLException, ClassNotFoundException, JSONException {
PostgresDb db = new PostgresDb();
// db.setUrl("jdbc:postgresql://localhost:5432/powaaim");
try {
Class.forName("org.postgresql.Driver");
c = DriverManager.getConnection(db.getUrl(), db.getUser(), db.getPass());
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
st = c.createStatement();
rs = st.executeQuery("SELECT id, name, externalshopid from shop where name='David_3'");
System.out.println("id" + " " + "name" + " " + "externalshopid");
c.close();
obj = new JSONObject();
while (rs.next()) {
int id = rs.getInt("id");
String externaId = rs.getString("externalshopid");
String name = rs.getString("name");
// System.out.println(id+ " " + " "+ name + " " +" "+ externaId);
System.out.println();
obj.put("id", id);
obj.put("name", name);
obj.put("externalshopid", externaId);
System.out.print(obj);
}
}
/**
* This method queries the database to get the details related to the bp_id passed.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getBpdet(String bpid) {
String query;
Vector bpdet = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select rtrim(bs_id_fk||bp_rgn),bp_month from blg_prd where bp_id = ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, bpid);
rs = pstmt.executeQuery();
while (rs.next()) {
bpdet.addElement(rs.getString(1));
bpdet.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: BP_ID details not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return bpdet;
}
/**
* This method queries the database to get the list of the Billing Systems.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getYears(String year) {
String query;
Vector years = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select yr||'-'||(yr+1),yr from fung_yr where yr > ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
rs = pstmt.executeQuery();
while (rs.next()) {
years.addElement(rs.getString(1));
years.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Years List not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return years;
}
/**
* This method queries the database to get the sequence for the BP_ID.
*
* @exception SQLException, if query fails
* @author
*/
public String getBpid() {
String query;
String bpid = "";
Statement stmt = null;
ResultSet rs = null;
query = "select bp_id_seq.nextval from dual ";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
bpid = rs.getString(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Sequence Value for BP_ID not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return bpid;
}
/**
* This method queries the database to validate the passed in FRN for the selected Funding Year
*
* @exception SQLException, if query fails
* @author
*/
public boolean validateFRN(String frn, String year) {
String query;
boolean validfrn = false;
Statement stmt = null;
ResultSet rs = null;
query = "select wo_id from wrk_ordr where wrk_ordr_no='" + frn + "' and yr_fk=" + year;
USFEnv.getLog().writeCrit("RhccDinvview: FRN query " + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
validfrn = true;
if (rs != null) rs.close();
if (stmt != null) stmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("RhccDinvview: FRN not valid for the Year ", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return false;
}
/**
* This method queries the database to get the name of the Billing System.
*
* @exception SQLException, if query fails
* @author
*/
public String getBlgsysnm(String blgsys) {
String query;
String blgsysnm = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select bs_nm from blg_sys where bs_id = ?";
USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Name Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, blgsys);
rs = pstmt.executeQuery();
if (rs.next()) {
blgsysnm = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Billing System Name not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepare statement", this, e);
}
}
return blgsysnm;
}
/**
* This method queries the database to get the Invoice Number associated with the passes Invid
*
* @exception SQLException, if query fails
* @author
*/
public long getInvID(String inv_no) {
String query;
long invID = 0;
Statement stmt = null;
ResultSet rs = null;
query = "select inv_id from inv where inv_no=" + inv_no;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
invID = rs.getLong(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvview: The Invoice ID not retreived", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return (invID);
}
/**
* This method queries the database to get the Invoice Number associated with the passes Invid
*
* @exception SQLException, if query fails
* @author
*/
public String getInvno(String invid) {
String query;
String invno = "";
Statement stmt = null;
ResultSet rs = null;
query = "select inv_no from rhcc_inv where rhcc_inv_id=" + invid;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
invno = rs.getString(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("RhccDinvview: The Invoice Number not retreived", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return invno;
}
/**
* This method queries the database to get the SPIN associated with the passes Invid
*
* @exception SQLException, if query fails
* @author
*/
public String getSPINname(String invid) {
String query;
String spinnm = "";
Statement stmt = null;
ResultSet rs = null;
// query = "select slc_nm from usw_co,inv where uc_id=uc_id_fk and inv_id="+invid;
query =
"select srv_provr_nm from srv_provr,rhcc_inv where spin = rhcc_inv.spin_fk and rhcc_inv_id="
+ invid;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
spinnm = rs.getString(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("RhccDinvview: The SPIN Name not retreived", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return spinnm;
}
//
// Examine BLOBs and CLOBs.
//
private void vetLargeObjects(
Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
throws Exception {
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE t (id INT PRIMARY KEY, " + "b BLOB(10), c CLOB(10))");
stmt.execute(
"INSERT INTO t (id, b, c) VALUES (1, "
+ "CAST ("
+ TestUtil.stringToHexLiteral("101010001101")
+ "AS BLOB(10)), CAST ('hello' AS CLOB(10)))");
ResultSet rs = stmt.executeQuery("SELECT id, b, c FROM t");
rs.next();
Blob blob = rs.getBlob(2);
Clob clob = rs.getClob(3);
vetObject(blob, unsupportedList, notUnderstoodList);
vetObject(clob, unsupportedList, notUnderstoodList);
stmt.close();
conn.rollback();
}
/**
* This method queries the database to check if the passed start date for the Journal Month starts
* exactly after the Previous end date.
*
* @exception SQLException, if query fails
* @author
*/
public boolean check(String strtdat, String blgsys, String year, String month, String rgn) {
if (month.length() > ((new Integer("2")).intValue())) {
month = getMonth(month);
}
String query;
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select 'true' from (select bp_end_dat,bp_year from blg_prd ";
query = query + "where bp_month= decode(to_number(?),1,12,to_number(?)-1) and bs_id_fk = ?";
if ((rgn != null) && !(rgn.equals(""))) {
query = query + " and bp_rgn = ?";
} else {
query = query + " and bp_rgn is null";
}
query = query + " and bp_year=decode(to_number(?),1,to_number(?)-1,to_number(?) ) ) A ";
query = query + " where to_date(?,'MM/DD/YYYY')-A.bp_end_dat=1";
USFEnv.getLog().writeDebug("Dinvjrnl:check Date- Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, month);
pstmt.setString(2, month);
pstmt.setString(3, blgsys);
int m = 4;
if ((rgn != null) && !(rgn.equals(""))) {
pstmt.setString(m, rgn);
m = m + 1;
}
pstmt.setString(m, month);
pstmt.setString(m + 1, year);
pstmt.setString(m + 2, year);
pstmt.setString(m + 3, strtdat);
rs = pstmt.executeQuery();
if (rs.next()) {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Date Check Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return false;
}
/**
* This method queries the database to get the Journal Dates for the passed in Funding Year
*
* @exception SQLException, if query fails
* @author
*/
public Vector getJrnldts(String year) {
String query;
Vector jrnllst = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query =
"select bp_id,decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord, ";
query = query + "rtrim(bs_nm||' '||bp_rgn) nm, bp_month,decode(bp_month,1,'January',";
query =
query
+ "2,'February',3,'March',4,'April',5,'May',6,'June',7,'July',8,'August',9,'September',";
query =
query
+ "10,'October',11,'November',12,'December'), to_char(bp_strt_dat,'MM/DD/YYYY'),to_char(bp_end_dat,'MM/DD/YYYY') ";
query = query + "from blg_prd,blg_sys ";
query = query + "where bs_id=bs_id_fk and ( bp_strt_dat >= ";
query = query + "(select strt_dat from fung_yr where yr = ?) and ";
query = query + "bp_end_dat <= (select end_dat from fung_yr where yr = ?) ";
query = query + "or ( bp_year =to_number(?) and bp_month=7) ) ";
query = query + "order by ord,nm,bp_strt_dat ";
USFEnv.getLog().writeDebug("Dinvjrnl: JRNL Dates Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
pstmt.setString(2, year);
pstmt.setString(3, year);
rs = pstmt.executeQuery();
while (rs.next()) {
Dinvjrnl jrnldts = new Dinvjrnl(null);
jrnldts.strBpid = rs.getString(1);
jrnldts.strBlgsys = rs.getString(3);
jrnldts.strBlgmnth = rs.getString(5);
jrnldts.strBlgstrtdt = rs.getString(6);
jrnldts.strBlgenddt = rs.getString(7);
jrnllst.addElement(jrnldts);
jrnldts = null;
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: JRNL Dates not retreived for the Year ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
USFEnv.getLog()
.writeDebug("Dinvjrnl: Journal Dates Vector size :" + jrnllst.size(), this, null);
return jrnllst;
}
/**
* Checks if a lock is left behind in the DB.
*
* @param con
* @param conID
* @return true if a lock is found.
*/
public static boolean checkLocks(Connection connection, long conID) {
try {
Statement stmt = connection.createStatement();
ResultSet res = stmt.executeQuery("exec sa_locks " + conID);
while (res.next()) {
return true;
}
} catch (SQLException ex) {
logger.error("Unable to retrieve connection ID", ex);
}
return false;
}
public String doSave() {
String back = "";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String qq =
"insert into monitors values(0,?," + "?,?,?,?,?," + "?,?,?,?,?," + "?,?,'Active',?,'y')";
editable = "y";
con = Helper.getConnection();
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
} else {
try {
pstmt = con.prepareStatement(qq);
if (debug) {
logger.debug(qq);
}
back = fillPStatement(pstmt);
if (back.equals("")) {
pstmt.executeUpdate();
//
// get the id of the new record
//
qq = "select LAST_INSERT_ID() ";
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
rs = pstmt.executeQuery();
if (rs.next()) {
id = rs.getString(1);
}
message = "Saved Successfully";
}
} catch (Exception ex) {
back += ex;
logger.error(ex);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
}
return back;
}
private void isEdit(Boolean isEdit) {
if (isEdit) {
try {
Connection con = FrameLogin.getConnect();
String sql = "SELECT * FROM Persons WHERE personId = " + Id;
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs.first()) {
String firstname = rs.getString("FirstName");
String lastname = rs.getString("LastName");
String cellphone = rs.getString("CellPhoneNo");
String homephone = rs.getString("HomePhoneNo");
String gradyear = rs.getString("Graduation Year");
String Gender = rs.getString("Gender");
firstName.setText(firstname);
lastName.setText(lastname);
cellPhone.setText(cellphone);
homePhone.setText(homephone);
gradYear.setText(gradyear);
gender.setSelectedItem(Gender);
jLabel7.setVisible(false);
studentId.setVisible(false);
} else {
MessageBox.infoBox("Error: ID not found", "Error");
}
} catch (Exception e) {
MessageBox.infoBox(e.toString(), "Error in isEdit");
}
}
FrameLogin.closeConnect();
}
boolean closeAndRemoveResultSets(Set rsSet) {
boolean okay = true;
synchronized (rsSet) {
for (Iterator ii = rsSet.iterator(); ii.hasNext(); ) {
ResultSet rs = (ResultSet) ii.next();
try {
rs.close();
} catch (SQLException e) {
if (Debug.DEBUG)
logger.log(MLevel.WARNING, "An exception occurred while cleaning up a ResultSet.", e);
// e.printStackTrace();
okay = false;
} finally {
ii.remove();
}
}
}
return okay;
}
/**
* This method queries the database to get the sequence for the BP_ID.
*
* @exception SQLException, if query fails
* @author
*/
public boolean checkDuplicate(String blgsys, String year, String month, String rgn) {
String query;
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select 'true' from blg_prd where bs_id_fk=" + blgsys + " and bp_year=" + year;
query = query + " and bp_month=" + month;
if ((rgn != null) && !(rgn.equals(""))) {
query = query + " and bp_rgn='" + rgn + "'";
} else {
query = query + " and bp_rgn is null";
}
USFEnv.getLog().writeDebug("Dinvjrnl:check Duplicate- Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, blgsys);
pstmt.setString(2, year);
pstmt.setString(3, month);
if ((rgn != null) && !(rgn.equals(""))) {
pstmt.setString(4, rgn);
}
rs = pstmt.executeQuery();
if (rs.next()) {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Date Comparison Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return false;
}
/**
* This method queries the database to get the Invoice Numbers list for a particular FRN in a
* Funding Year
*
* @exception SQLException, if query fails
* @author
*/
public Vector getFrninvnos(String frn, String year) {
String query;
Vector invnos = new Vector();
Statement stmt = null;
ResultSet rs = null;
USFEnv.getLog().writeWarn("Inside the block-->>RhccDinview Inview", this, null);
query =
" select distinct rhcc_inv_id_fk,rhcc_inv.inv_no||' - '||to_char(rhcc_inv.inv_dat,'Mon/DD/YYYY') ";
query = query + " from wrk_ordr,wrk_ordr_dets,wo_det_hsties,rhcc_inv,fung_yr ";
query = query + " where wo_id=wo_id_fk and wod_id=wod_id_fk and ";
query = query + " wdh_stat='P' and ";
query = query + " inv_stat is not null and ";
query = query + " rhcc_inv_id_fk=rhcc_inv_id and ";
query = query + " wrk_ordr_no='" + frn + "' and ";
query = query + " wrk_ordr.yr_fk=fung_yr.yr and ";
query = query + " wrk_ordr.yr_fk=" + year;
USFEnv.getLog().writeCrit("RhccDinvview: The Invoice Number List Query:" + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
invnos.addElement(rs.getString(1));
invnos.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog()
.writeCrit("RhccDinvview: The Invoice Numbers List for FRN not retreived", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return invnos;
}
protected ArrayList<String> getSQL(String SQL) throws Exception {
StringBuffer html = new StringBuffer();
ArrayList<String> v = new ArrayList<String>();
if (conectar()) {
ResultSet rs = ejecutarSQL(SQL);
ResultSetMetaData rsm = rs.getMetaData();
while (rs.next()) {
String r = "";
for (int i = 1; i <= rsm.getColumnCount(); i++) {
r += rs.getString(i) + "-";
}
v.add(r);
}
desconectar();
}
return v;
} // Fin getHTML
private boolean isLoggedIn(int id) {
boolean isLoggedIn = false;
Connection con = getConnect();
int personId = getPersonId(id);
try {
String SQL = "SELECT * FROM LogInOut WHERE personId = " + personId + " AND TimeOut IS NULL";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(SQL);
if (rs != null && rs.next()) {
rs.last();
isLoggedIn = true;
}
} catch (Exception err) {
MessageBox.infoBox(err.toString(), "Error from isLoggedIn");
System.out.println(err);
}
closeConnect();
return isLoggedIn;
// SELECT * FROM LogInOut WHERE personId = 1 AND TimeOut IS NULL
}
public static int getPersonId(int id) {
Connection con = getConnect();
ResultSet rs = null;
int personId = 0;
try {
String SQL = "SELECT PersonId FROM 2761DB.Persons WHERE SchoolId = " + id;
Statement stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
if (rs.next()) {
rs.first();
personId = rs.getInt("PersonId");
} else {
AddUserQuery addUserQuery = new AddUserQuery();
addUserQuery.setVisible(true);
}
} catch (Exception err) {
MessageBox.infoBox(err.toString(), "Error in getPersonId");
}
closeConnect();
return personId;
}
/**
* Preconditions: 1. flag must be one of TMSTARTRSCAN, TMENDRSCAN, TMNOFLAGS or TMSTARTTRSCAN |
* TMENDRSCAN 2. if flag isn't TMSTARTRSCAN or TMSTARTRSCAN | TMENDRSCAN, a recovery scan must be
* in progress
*
* <p>Postconditions: 1. list of prepared xids is returned
*/
public Xid[] recover(int flag) throws XAException {
// Check preconditions
if (flag != TMSTARTRSCAN
&& flag != TMENDRSCAN
&& flag != TMNOFLAGS
&& flag != (TMSTARTRSCAN | TMENDRSCAN))
throw new PGXAException(GT.tr("Invalid flag"), XAException.XAER_INVAL);
// We don't check for precondition 2, because we would have to add some additional state in
// this object to keep track of recovery scans.
// All clear. We return all the xids in the first TMSTARTRSCAN call, and always return
// an empty array otherwise.
if ((flag & TMSTARTRSCAN) == 0) return new Xid[0];
else {
try {
Statement stmt = conn.createStatement();
try {
// If this connection is simultaneously used for a transaction,
// this query gets executed inside that transaction. It's OK,
// except if the transaction is in abort-only state and the
// backed refuses to process new queries. Hopefully not a problem
// in practise.
ResultSet rs = stmt.executeQuery("SELECT gid FROM pg_prepared_xacts");
LinkedList l = new LinkedList();
while (rs.next()) {
Xid recoveredXid = RecoveredXid.stringToXid(rs.getString(1));
if (recoveredXid != null) l.add(recoveredXid);
}
rs.close();
return (Xid[]) l.toArray(new Xid[l.size()]);
} finally {
stmt.close();
}
} catch (SQLException ex) {
throw new PGXAException(GT.tr("Error during recover"), ex, XAException.XAER_RMERR);
}
}
}
protected void executeQuery(Statement stmt, String q) throws SQLException {
q = q.replace("$PREFIX", getPrefix());
LOG.info(" Executing " + q);
ResultSet rs = stmt.executeQuery(q);
StringBuilder header = new StringBuilder();
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
if (i > 1) {
header.append("|");
}
header.append(rs.getMetaData().getColumnName(i));
}
LOG.info(header);
int seq = 0;
while (true) {
boolean valid = rs.next();
if (!valid) break;
seq++;
StringBuilder line = new StringBuilder();
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
if (i > 1) {
line.append("|");
}
line.append(rs.getString(i));
}
LOG.info(seq + ":" + line);
}
}
public static boolean isIdFound(int id) {
Connection con = getConnect();
boolean isFound = false;
try {
String SQL =
"SELECT PersonId, FirstName, LastName, SchoolId FROM 2761DB.Persons WHERE SchoolId = "
+ id;
Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(SQL);
if (rs.first()) {
isFound = true;
} else {
AddUserQuery addUser = new AddUserQuery();
addUser.setVisible(true);
}
} catch (SQLException err) {
System.out.println(err);
MessageBox.infoBox(err.toString(), "Error");
}
closeConnect();
return isFound;
}
public static LinkedList<User> getAll() {
String query = "SELECT username, password, email, number, isIT FROM tomcat_user";
LinkedList<User> items = new LinkedList<>();
try (Connection connection = Config.getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery(query); ) {
while (result.next()) {
items.add(
new User(
result.getString("username"),
result.getString("password"),
result.getString("email"),
result.getString("number"),
query));
}
} catch (SQLException e) {
System.err.println("Couldn't get items from database.");
}
return items;
}
/** EJECUTA UNA CONSULTA Y GENERA XML */
protected String getXML() throws Exception {
StringBuffer xml = new StringBuffer();
xml.append("<registros>");
if (conectar()) {
ResultSet rs = ejecutarSQL(this.SQL);
while (rs.next()) {
ResultSetMetaData rsm = rs.getMetaData();
xml.append("<registro>");
for (int i = 1; i <= rsm.getColumnCount(); i++) {
xml.append("<" + rsm.getColumnName(i) + ">");
xml.append(rs.getString(i));
xml.append("</" + rsm.getColumnName(i) + ">");
}
xml.append("</registro>");
}
desconectar();
}
xml.append("</registros>");
return xml.toString();
} // Fin getXML
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;
}