public static void main(String[] args) { try { Connection con = DBopsMySql.setuptradesConnection(); con.setAutoCommit(false); String ul = "AUD"; PreparedStatement pstmt = DBopsMySql.getExpirysForUpdate(con, ul, 20090300, 20100700); PreparedStatement upDateStmt = DBopsMySql.updateBeginEndDatesForExpiry(con); ResultSet res = pstmt.executeQuery(); res.next(); // To get a lastexpiry for loop, so should be one extra early expiry int lastexp = res.getInt("expiry"); while (res.next()) { int exp = res.getInt("expiry"); String bdate = DateOps.dbShortFormatString(lastexp - 5); String edate = DateOps.dbShortFormatString(exp - 6); upDateStmt.setString(1, bdate); if (!res.isLast()) { upDateStmt.setString(2, edate); } upDateStmt.setString(3, ul); upDateStmt.setInt(4, exp); upDateStmt.addBatch(); lastexp = exp; } int[] updateCounts = upDateStmt.executeBatch(); upDateStmt.close(); con.close(); } catch (SQLException ex) { MsgBox.err2(ex); } }
public String getCustomers(String lastName) { StringBuffer json = new StringBuffer(); try { entries = stmt.executeQuery( "SELECT Person_ID,Vorname, Name, Strasse, Ort, Privattelefon FROM person WHERE '" + lastName + "' LIKE Vorname OR '" + lastName + "' LIKE Name "); json.append("["); while (entries.next()) { json.append("{"); json.append("\"id\":" + entries.getString("Person_ID") + ","); json.append("\"firstname\":\"" + entries.getString("Vorname") + "\","); json.append("\"lastname\":\"" + entries.getString("Name") + "\","); json.append("\"street\":\"" + entries.getString("Strasse") + "\","); json.append("\"city\":\"" + entries.getString("Ort") + "\","); json.append("\"tel\":\"" + entries.getString("Privattelefon") + "\""); if (entries.isLast()) { json.append("}"); } else { json.append("},"); } } json.append("]"); } catch (SQLException e) { e.printStackTrace(); } return json.toString(); }
public String getCustomers(Date date) { StringBuffer json = new StringBuffer(); try { entries = stmt.executeQuery( "SELECT Person_ID, Vorname,Name,Ankunft,Abreise FROM buchung, person WHERE '" + date + "' >= Ankunft And '" + date + "' <= Abreise And Person_ID = Person_IDFS"); json.append("["); while (entries.next()) { json.append("{"); json.append("\"id\":" + entries.getString("Person_ID") + ","); json.append("\"firstname\":\"" + entries.getString("Vorname") + "\","); json.append("\"lastname\":\"" + entries.getString("Name") + "\","); json.append("\"arrival\":\"" + entries.getString("Ankunft") + "\","); json.append("\"departure\":\"" + entries.getString("Abreise") + "\""); if (entries.isLast()) { json.append("}"); } else { json.append("},"); } } json.append("]"); } catch (SQLException e) { e.printStackTrace(); } return json.toString(); }
public String getCutomerDetails(int id) { StringBuilder json = new StringBuilder(); ResultSet additionalSet; ResultSet additionalSet2; try { entries = stmt.executeQuery( "SELECT Person_ID,Vorname, Name, Strasse, Ort, Privattelefon FROM person WHERE Person_ID = " + id); additionalSet = stmt2.executeQuery( "SELECT Buchung_ID, Ankunft,Abreise FROM buchung WHERE Person_IDFS = " + id); json.append("["); while (entries.next()) { json.append("{"); json.append("\"id\":\"" + entries.getString("Person_ID") + "\","); json.append("\"firstname\":\"" + entries.getString("Vorname") + "\","); json.append("\"lastname\":\"" + entries.getString("Name") + "\","); json.append("\"street\":\"" + entries.getString("Strasse") + "\","); json.append("\"city\":\"" + entries.getString("Ort") + "\","); json.append("\"tel\":\"" + entries.getString("Privattelefon") + "\","); json.append("\"bookings\": ["); while (additionalSet.next()) { json.append("{"); json.append("\"arrival\":\"" + additionalSet.getString("Ankunft") + "\","); json.append("\"departure\":\"" + additionalSet.getString("Abreise") + "\","); json.append("\"consumptions\": ["); additionalSet2 = stmt3.executeQuery( "SELECT Beschreibung, Anzahl, Preis, Rabatt, Datum FROM position, leistung WHERE Leistung_IDFS = Leistung_ID AND Buchung_IDFS = " + additionalSet.getString("Buchung_ID") + " ORDER BY Datum"); while (additionalSet2.next()) { json.append("{"); json.append( "\"description\":\"" + additionalSet2.getString("Beschreibung").replace("\r", "") + "\","); json.append("\"amount\":\"" + additionalSet2.getString("Anzahl") + "\","); json.append("\"price\":\"" + additionalSet2.getString("Preis") + "\","); json.append("\"discount\":\"" + additionalSet2.getString("Rabatt") + "\","); json.append("\"date\":\"" + additionalSet2.getString("Datum") + "\""); if (additionalSet2.isLast()) { json.append("}"); } else { json.append("},"); } } json.append("]"); if (additionalSet.isLast()) { json.append("}"); } else { json.append("},"); } } json.append("]"); json.append("}"); } json.append("]"); } catch (SQLException e) { e.printStackTrace(); } System.out.print(isJSONValid(json.toString())); return json.toString(); }