public String doDelete() { String back = "", qq = ""; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; con = Helper.getConnection(); if (con == null) { back = "Could not connect to DB"; addError(back); return back; } else { try { qq = "delete from monitors where id=?"; if (debug) { logger.debug(qq); } pstmt = con.prepareStatement(qq); pstmt.setString(1, id); pstmt.executeUpdate(); message = "Deleted Successfully"; } catch (Exception ex) { back += ex + ":" + qq; logger.error(back); addError(back); } finally { Helper.databaseDisconnect(con, pstmt, rs); } } return back; }
public String updateStatus(String new_status) { String back = ""; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; String str = ""; String qq = ""; con = Helper.getConnection(); if (con == null) { back = "Could not connect to DB"; addError(back); return back; } try { qq = "update monitors set status=? where id = ? "; if (debug) { logger.debug(qq); } pstmt = con.prepareStatement(qq); pstmt.setString(1, new_status); pstmt.setString(2, id); pstmt.executeUpdate(); } catch (Exception ex) { back += ex + ":" + qq; logger.error(back); addError(back); } finally { Helper.databaseDisconnect(con, pstmt, rs); } return back; }
public String doSelect() { String back = ""; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; String qq = "select external_id,device_id,name,asset_num," + " serial_num,screen_size,model,type," + "vertical_resolution,horizontal_resolution," + "manufacturer," + "date_format(received,'%m/%d/%Y')," + "expected_age,status,notes,editable " + " from monitors where id=?"; con = Helper.getConnection(); if (con == null) { back = "Could not connect to DB"; addError(back); return back; } else { try { if (debug) { logger.debug(qq); } pstmt = con.prepareStatement(qq); pstmt.setString(1, id); rs = pstmt.executeQuery(); if (rs.next()) { setExternal_id(rs.getString(1)); setDevice_id(rs.getString(2)); setName(rs.getString(3)); setAsset_num(rs.getString(4)); setSerial_num(rs.getString(5)); setScreen_size(rs.getString(6)); setModel(rs.getString(7)); setType(rs.getString(8)); setVertical_resolution(rs.getString(9)); setHorizontal_resolution(rs.getString(10)); setManufacturer(rs.getString(11)); setReceived(rs.getString(12)); setExpected_age(rs.getString(13)); setStatus(rs.getString(14)); setNotes(rs.getString(15)); setEditable(rs.getString(16)); } else { return "Record " + id + " Not found"; } } catch (Exception ex) { back += ex + ":" + qq; logger.error(back); addError(back); } finally { Helper.databaseDisconnect(con, pstmt, rs); } } return back; }
public String doPartialUpdate() { String back = ""; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; String str = ""; String qq = ""; con = Helper.getConnection(); if (con == null) { back = "Could not connect to DB"; addError(back); return back; } try { qq = "update monitors set " + "asset_num=?, screen_size=?,received=?, expected_age=?, " + "notes=? " + "where id=?"; if (debug) { logger.debug(qq); } pstmt = con.prepareStatement(qq); int jj = 1; if (asset_num.equals("")) pstmt.setNull(jj++, Types.VARCHAR); else pstmt.setString(jj++, asset_num); if (screen_size.equals("")) pstmt.setNull(jj++, Types.VARCHAR); else pstmt.setString(jj++, screen_size); if (received.equals("")) pstmt.setNull(jj++, Types.DATE); else pstmt.setDate(jj++, new java.sql.Date(dateFormat.parse(received).getTime())); pstmt.setString(jj++, "" + expected_age); if (notes.equals("")) pstmt.setNull(jj++, Types.VARCHAR); else pstmt.setString(jj++, notes); pstmt.setString(6, id); pstmt.executeUpdate(); } catch (Exception ex) { back += ex + ":" + qq; logger.error(back); addError(back); } finally { Helper.databaseDisconnect(con, pstmt, rs); } if (back.equals("")) { back = doSelect(); } return back; }
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; }
public String doUpdate() { String back = ""; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; String str = ""; String qq = ""; con = Helper.getConnection(); if (con == null) { back = "Could not connect to DB"; addError(back); return back; } try { qq = "update monitors set external_id=?,device_id=?," + "name=?,asset_num=?,serial_num=?,screen_size=?,model=?,type=?," + "vertical_resolution=?,horizontal_resolution=?,manufacturer=?," + "received=?,expected_age=?, " + "notes=? " + // we do not change status here "where id=?"; if (debug) { logger.debug(qq); } pstmt = con.prepareStatement(qq); back = fillPStatement(pstmt); if (back.equals("")) { pstmt.setString(15, id); // 17 - 2 (editable) pstmt.executeUpdate(); } } catch (Exception ex) { back += ex + ":" + qq; logger.error(back); addError(back); } finally { Helper.databaseDisconnect(con, pstmt, rs); } if (back.equals("")) { back = doSelect(); // to get status } return back; }
public String find() { String back = ""; PreparedStatement pstmt = null; ResultSet rs = null; Connection con = Helper.getConnection(); String qq = "select r.id,r.asset_id,r.asset_num,r.type, " + " date_format(date,'%m/%d/%Y'),r.location_id,r.weight,r.description " + " from recycled_items r "; String qw = ""; if (con == null) { back = "Could not connect to DB"; addError(back); return back; } try { if (!location_id.equals("")) { if (!qw.equals("")) qw += " and "; qw += " r.location_id = ? "; } if (!asset_id.equals("")) { if (!qw.equals("")) qw += " and "; qw += " r.asset_id = ? "; } if (!type.equals("")) { if (!qw.equals("")) qw += " and "; qw += " r.type = ? "; } if (!date_from.equals("")) { if (!qw.equals("")) qw += " and "; qw += " r.date >= str_to_date('" + date_from + "','%m/%d/%Y')"; } if (!date_to.equals("")) { if (!qw.equals("")) qw += " and "; qw += " r.date <= str_to_date('" + date_to + "','%m/%d/%Y')"; } if (!qw.equals("")) { qq = qq + " where " + qw; } qq = qq + " order by r.date DESC " + limit; if (debug) { logger.debug(qq); } pstmt = con.prepareStatement(qq); int jj = 1; if (!location_id.equals("")) { pstmt.setString(jj++, location_id); } if (!asset_id.equals("")) { pstmt.setString(jj++, asset_id); } if (!type.equals("")) { pstmt.setString(jj++, type); } rs = pstmt.executeQuery(); while (rs.next()) { if (recycledItems == null) recycledItems = new ArrayList<RecycledItem>(); RecycledItem one = new RecycledItem( debug, rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8)); recycledItems.add(one); } } catch (Exception ex) { back += ex + " : " + qq; logger.error(back); addError(back); } finally { Helper.databaseDisconnect(con, pstmt, rs); } return back; }