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 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 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;
}
/**
* INTERNAL: Extract the direct values from the specified field value. Return them in a vector.
* The field value better be an Array.
*/
public Vector buildDirectValuesFromFieldValue(Object fieldValue) throws DatabaseException {
if (fieldValue == null) {
return null;
}
return Helper.vectorFromArray((Object[]) fieldValue);
}
/**
* INTERNAL: Build the appropriate field value for the specified set of direct values. The
* database better be expecting an ARRAY.
*/
public Object buildFieldValueFromDirectValues(
Vector directValues, String elementDataTypeName, AbstractSession session)
throws DatabaseException {
Object[] fields = Helper.arrayFromVector(directValues);
try {
((DatabaseAccessor) session.getAccessor()).incrementCallCount(session);
java.sql.Connection connection = ((DatabaseAccessor) session.getAccessor()).getConnection();
return session.getPlatform().createArray(elementDataTypeName, fields, session, connection);
} catch (java.sql.SQLException ex) {
throw DatabaseException.sqlException(ex, session, false);
} finally {
((DatabaseAccessor) session.getAccessor()).decrementCallCount();
}
}
private Connection openDB(final DBConfiguration dbConfiguration) throws DatabaseException {
final String connectionURL = dbConfiguration.getConnectionString();
final String jdbcClassName = dbConfiguration.getDriverClassname();
try {
final byte[] jdbcDriverBytes = dbConfiguration.getJdbcDriver();
if (jdbcDriverBytes != null) {
LOGGER.debug("loading JDBC database driver stored in configuration");
final JarClassLoader jarClassLoader = new JarClassLoader();
jarClassLoader.add(new ByteArrayInputStream(jdbcDriverBytes));
final JclObjectFactory jclObjectFactory = JclObjectFactory.getInstance();
// Create object of loaded class
driver = (Driver) jclObjectFactory.create(jarClassLoader, jdbcClassName);
LOGGER.debug(
"successfully loaded JDBC database driver '"
+ jdbcClassName
+ "' from application configuration");
}
} catch (Throwable e) {
final String errorMsg =
"error registering JDBC database driver stored in configuration: " + e.getMessage();
final ErrorInformation errorInformation =
new ErrorInformation(PwmError.ERROR_DB_UNAVAILABLE, errorMsg);
LOGGER.error(errorMsg, e);
throw new DatabaseException(errorInformation);
}
if (driver == null) {
try {
LOGGER.debug("loading JDBC database driver from classpath: " + jdbcClassName);
driver = (Driver) Class.forName(jdbcClassName).newInstance();
LOGGER.debug("successfully loaded JDBC database driver from classpath: " + jdbcClassName);
} catch (Throwable e) {
final String errorMsg =
e.getClass().getName()
+ " error loading JDBC database driver from classpath: "
+ e.getMessage();
final ErrorInformation errorInformation =
new ErrorInformation(PwmError.ERROR_DB_UNAVAILABLE, errorMsg);
throw new DatabaseException(errorInformation);
}
}
try {
LOGGER.debug("opening connection to database " + connectionURL);
final Properties connectionProperties = new Properties();
if (dbConfiguration.getUsername() != null && !dbConfiguration.getUsername().isEmpty()) {
connectionProperties.setProperty("user", dbConfiguration.getUsername());
}
if (dbConfiguration.getPassword() != null) {
connectionProperties.setProperty(
"password", dbConfiguration.getPassword().getStringValue());
}
final Connection connection = driver.connect(connectionURL, connectionProperties);
final Map<PwmAboutProperty, String> debugProps = getConnectionDebugProperties(connection);
;
LOGGER.debug(
"successfully opened connection to database "
+ connectionURL
+ ", properties: "
+ JsonUtil.serializeMap(debugProps));
connection.setAutoCommit(true);
return connection;
} catch (Throwable e) {
final String errorMsg =
"error connecting to database: " + Helper.readHostileExceptionMessage(e);
final ErrorInformation errorInformation =
new ErrorInformation(PwmError.ERROR_DB_UNAVAILABLE, errorMsg);
if (e instanceof IOException) {
LOGGER.error(errorInformation);
} else {
LOGGER.error(errorMsg, e);
}
throw new DatabaseException(errorInformation);
}
}
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;
}