/**
* puts the value for the column aginst the column Name
*
* @param os
* @param rows
* @param oldResultSet
* @param index
* @param modifiedColumns
* @param columnName
* @throws SQLException
* @throws IOException
*/
public void writeUpdate(
Writer os,
ResultSet rows,
ResultSet oldResultSet,
int index,
HashMap modifiedColumns,
String columnName,
ArrayList encodedCols)
throws SQLException, IOException {
Object newObject = rows.getObject(index);
Object oldObject = oldResultSet.getObject(index);
if (newObject == null) {
write(os, "NULL", encodedCols, columnName);
if (oldObject != null) {
modifiedColumns.put(columnName, "NULL");
}
} else {
write(os, newObject, encodedCols, columnName);
if (oldObject != null) {
if (!(newObject.equals(oldObject))) {
modifiedColumns.put(columnName, newObject);
}
} else {
modifiedColumns.put(columnName, newObject);
}
}
}
/** private method which actually will do all of our work for the sample */
private void executeSample() {
String query = "select anEmployee from staff2";
try {
Statement stmt = _con.createStatement();
;
// Execute the query which will return an Employee object
// We will cast this using the Person interface. Note the
// Person interface class MUST be in your CLASSPATH. You
// Do not need Employee in your CLASSPATH.
ResultSet rs = stmt.executeQuery(query);
output("***Using interface class\n");
while (rs.next()) {
Person aPerson = (Person) rs.getObject(1);
displayMethods(aPerson.getClass());
output(
"The person is: "
+ aPerson.toString()
+ "\nFirst Name= "
+ aPerson.getFirstName()
+ "\nLast Name= "
+ aPerson.getLastName()
+ "\n");
}
// Now execute the same query, but this time we will use
// reflection to access the class. Again, only the interface
// Person is required in the CLASSPATH
rs = stmt.executeQuery(query);
output("***Using reflection\n");
Object theObj = null;
while (rs.next()) {
theObj = rs.getObject(1);
output("The person is: " + theObj.toString() + "\n");
Class theClass = theObj.getClass();
displayMethods(theClass);
Method m1 = theClass.getMethod("toString", new Class[0]);
Method m2 = theClass.getMethod("getFirstName", new Class[0]);
Method m3 = theClass.getMethod("getLastName", new Class[0]);
output(
"The person is: "
+ (Object) m1.invoke(theObj, new Object[0])
+ "\nFirst Name= "
+ (Object) m2.invoke(theObj, new Object[0])
+ "\nLast Name= "
+ (Object) m3.invoke(theObj, new Object[0])
+ "\n");
}
rs.close();
stmt.close();
} catch (SQLException sqe) {
displaySQLEx(sqe);
} catch (Exception e) {
error("Unexpected exception : " + e.toString() + "\n");
e.printStackTrace();
}
}
// execute and get results
private void execute(Connection conn, String text, Writer writer, boolean commaSeparator)
throws SQLException {
BufferedWriter buffer = new BufferedWriter(writer);
Statement stmt = conn.createStatement();
stmt.execute(text);
ResultSet rs = stmt.getResultSet();
ResultSetMetaData metadata = rs.getMetaData();
int nbCols = metadata.getColumnCount();
String[] labels = new String[nbCols];
int[] colwidths = new int[nbCols];
int[] colpos = new int[nbCols];
int linewidth = 1;
// read each occurrence
try {
while (rs.next()) {
for (int i = 0; i < nbCols; i++) {
Object value = rs.getObject(i + 1);
if (value != null) {
buffer.write(value.toString());
if (commaSeparator) buffer.write(",");
}
}
}
buffer.flush();
rs.close();
} catch (IOException ex) {
if (Debug.isDebug()) ex.printStackTrace();
// ok, exit from the loop
} catch (SQLException ex) {
if (Debug.isDebug()) ex.printStackTrace();
}
}
/*
* Execute query against a list of sensors
*
* */
public static String executeQuery(
String envelope, String query, String matchingSensors, String format) throws ParseException {
// String matchingSensors = getListOfSensorsAsString(envelope);
String reformattedQuery = reformatQuery(query, matchingSensors);
StringBuilder sb = new StringBuilder();
Connection connection = null;
try {
connection = Main.getDefaultStorage().getConnection();
Statement statement =
connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet results = statement.executeQuery(reformattedQuery);
ResultSetMetaData metaData; // Additional information about the results
int numCols, numRows; // How many rows and columns in the table
metaData = results.getMetaData(); // Get metadata on them
numCols = metaData.getColumnCount(); // How many columns?
results.last(); // Move to last row
numRows = results.getRow(); // How many rows?
String s;
// System.out.println("* Executing query *\n" + reformattedQuery + "\n***");
// headers
// sb.append("# Query: " + query + NEWLINE);
sb.append("# Query: " + reformattedQuery.replaceAll("\n", "\n# ") + NEWLINE);
sb.append("# ");
// System.out.println("ncols: " + numCols);
// System.out.println("nrows: " + numRows);
for (int col = 0; col < numCols; col++) {
sb.append(metaData.getColumnLabel(col + 1));
if (col < numCols - 1) sb.append(SEPARATOR);
}
sb.append(NEWLINE);
for (int row = 0; row < numRows; row++) {
results.absolute(row + 1); // Go to the specified row
for (int col = 0; col < numCols; col++) {
Object o = results.getObject(col + 1); // Get value of the column
// logger.warn(row + " , "+col+" : "+ o.toString());
if (o == null) s = "null";
else s = o.toString();
if (col < numCols - 1) sb.append(s).append(SEPARATOR);
else sb.append(s);
}
sb.append(NEWLINE);
}
} catch (SQLException e) {
sb.append("ERROR in execution of query: " + e.getMessage());
} finally {
Main.getDefaultStorage().close(connection);
}
return sb.toString();
}
public static <K, V> Map<K, V> resultSetToMap(final ResultSet resultSet) {
try {
final Map<K, V> map = new LinkedHashMap<K, V>();
while (resultSet.next()) {
K key = (K) resultSet.getObject(1);
V value = (V) resultSet.getObject(2);
map.put(key, value);
}
return map;
} catch (Exception e) {
throw new IllegalStateException("Unable to transform the resultSet into a Map", e);
}
}
protected Map<String, Object> getARow(
ResultSet resultSet,
boolean convertType,
List<String> colNames,
Map<String, Integer> fieldNameVsType) {
if (resultSet == null) return null;
Map<String, Object> result = new HashMap<>();
for (String colName : colNames) {
try {
if (!convertType) {
// Use underlying database's type information except for BigDecimal and BigInteger
// which cannot be serialized by JavaBin/XML. See SOLR-6165
Object value = resultSet.getObject(colName);
if (value instanceof BigDecimal || value instanceof BigInteger) {
result.put(colName, value.toString());
} else {
result.put(colName, value);
}
continue;
}
Integer type = fieldNameVsType.get(colName);
if (type == null) type = Types.VARCHAR;
switch (type) {
case Types.INTEGER:
result.put(colName, resultSet.getInt(colName));
break;
case Types.FLOAT:
result.put(colName, resultSet.getFloat(colName));
break;
case Types.BIGINT:
result.put(colName, resultSet.getLong(colName));
break;
case Types.DOUBLE:
result.put(colName, resultSet.getDouble(colName));
break;
case Types.DATE:
result.put(colName, resultSet.getTimestamp(colName));
break;
case Types.BOOLEAN:
result.put(colName, resultSet.getBoolean(colName));
break;
case Types.BLOB:
result.put(colName, resultSet.getBytes(colName));
break;
default:
result.put(colName, resultSet.getString(colName));
break;
}
} catch (SQLException e) {
logError("Error reading data ", e);
wrapAndThrow(SEVERE, e, "Error reading data from database");
}
}
return result;
}
/** @return {@code true} If next row was fetched successfully. */
private boolean fetchNext() {
if (data == null) return false;
try {
if (!data.next()) return false;
for (int c = 0; c < row.length; c++) row[c] = data.getObject(c + 1);
return true;
} catch (SQLException e) {
throw new IgniteException(e);
}
}
/** getObject. */
public void test() throws Exception {
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet result = statement.executeQuery();
int size = result.getMetaData().getColumnCount();
Vector rows = new Vector();
while (result.next()) {
Vector row = new Vector(size);
for (int column = 1; column <= size; column++) {
Object value = result.getObject(column);
value = ConversionManager.getDefaultManager().convertObject(value, ClassConstants.SQLDATE);
row.add(value);
}
rows.add(row);
}
result.close();
statement.close();
}
/**
* Processes a particular SQL Array object and interprets its value as a ParamValue object.
*
* @param sqlArray SQL Array element.
* @param paramValue Parameter value object initialized to contain an array of ParamValues.
* @return ParamValue object representing the SQL Array.
* @throws SQLException Throws an SQL Exception if the result set is not accessible.
*/
public static ParamValue processSQLArray(Array sqlArray, ParamValue paramValue)
throws SQLException {
ResultSet rs = sqlArray.getResultSet();
while (rs.next()) {
Object arrayEl = rs.getObject(2);
if (arrayEl instanceof Struct) {
paramValue.getArrayValue().add(new ParamValue((Struct) arrayEl));
} else if (arrayEl instanceof Array) {
paramValue
.getArrayValue()
.add(processSQLArray((Array) arrayEl, new ParamValue(ParamValue.PARAM_VALUE_ARRAY)));
} else {
paramValue.getArrayValue().add(new ParamValue(String.valueOf(arrayEl)));
}
}
rs.close();
return paramValue;
}
public List<Map<String, Object>> findAllRecords(String tableName) throws SQLException {
List<Map<String, Object>> records = new ArrayList<>();
String sql = "SELECT * FROM " + tableName;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData meta = rs.getMetaData();
int colCount = meta.getColumnCount();
while (rs.next()) {
Map<String, Object> record = new HashMap<>();
for (int i = 1; i <= colCount; i++) {
record.put(meta.getColumnName(i), rs.getObject(i));
}
records.add(record);
}
return records;
}
/*
* Searches for the sensors, which are contained within the specified envelope
* */
public static ArrayList<String> getListOfSensors(String envelope) throws ParseException {
String spatial_query =
"select location, name from sensors\n"
+ "where ST_CONTAINS(ST_GeomFromText('"
+ envelope
+ "'), location)";
ArrayList<String> sensors = new ArrayList<String>();
try {
Connection conn = connect(dburl, dbuser, dbpass);
Statement s2 = conn.createStatement();
ResultSet r2 = s2.executeQuery(spatial_query);
int count = 0;
while (r2.next()) {
PGgeometry geom = (PGgeometry) r2.getObject(1);
String name = r2.getString(2);
sensors.add(name);
logger.warn("Matching Geometry " + geom.toString() + " : " + name);
count++;
}
logger.warn("count = " + count);
s2.close();
conn.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
} catch (ClassNotFoundException e) {
logger.warn(e.getMessage(), e);
}
return sensors;
}
public void runReplicate() throws InterruptedException {
if (verbose) log.append("Started at " + Calendar.getInstance().getTime() + "\r\n");
Connection connectionFrom = null;
Connection connectionTo = null;
try {
connectionFrom = providerI.getConnection(dbFrom);
connectionTo = providerI.getConnection(dbTo);
PreparedStatement preparedStatementFrom = null;
PreparedStatement preparedStatementTo = null;
ResultSet resultSet = null;
for (Iterator<HashMap<String, String>> HashMapIterator = tables.iterator();
HashMapIterator.hasNext(); ) {
try {
HashMap<String, String> h = HashMapIterator.next();
String name = h.get("name");
String name2 = h.get("name2");
String where1 = h.get("where1");
String where2 = h.get("where2");
if (verbose)
log.append("Replicating " + dbFrom + "/" + name + " to " + dbTo + "/" + name2 + "\r\n");
preparedStatementFrom =
connectionFrom.prepareStatement("select * from " + name + " WHERE 1=1 " + where1);
boolean b = preparedStatementFrom.execute();
HashMap meta = new HashMap();
// 1. find all ids from DB1
ArrayList db1 = new ArrayList();
String nullMark = "____NULL";
if (b) {
resultSet = preparedStatementFrom.getResultSet();
ResultSetMetaData setMetaData = resultSet.getMetaData();
for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
meta.put(
setMetaData.getColumnLabel(i).toLowerCase(), setMetaData.getColumnClassName(i));
}
while (resultSet.next()) {
HashMap results = new HashMap();
for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
String columnName = setMetaData.getColumnLabel(i).toLowerCase();
Object o = resultSet.getObject(i);
if (o != null) {
results.put(columnName, o);
} else {
results.put(columnName + nullMark, nullMark);
}
}
db1.add(results);
}
resultSet.close();
} else {
log.append("Couldn't execute select from " + dbFrom + "/" + name + " /r/n");
return;
}
StringBuffer allIds = new StringBuffer();
HashMap records1 = new HashMap();
HashMap toInsert = new HashMap();
HashMap toUpdate = new HashMap();
for (Iterator iterator = db1.iterator(); iterator.hasNext(); ) {
HashMap record = (HashMap) iterator.next();
if (allIds.length() != 0) {
allIds.append(",");
}
allIds.append(record.get("id"));
records1.put(record.get("id"), record);
}
toInsert.putAll(records1);
// 2. find all ids to delete in DB2;
if (allIds.length() > 0) {
preparedStatementTo =
connectionTo.prepareStatement(
"delete from "
+ name2
+ " where id not in ("
+ allIds.toString()
+ ")"
+ where2);
if (verbose)
log.append(
"deleted from "
+ dbTo
+ "/"
+ name2
+ " "
+ preparedStatementTo.executeUpdate()
+ " records;\r\n");
} else {
if (verbose)
log.append(
"No records in "
+ dbFrom
+ "/"
+ name
+ ", nothing to delete in "
+ dbTo
+ "/"
+ name2
+ " ;\r\n");
}
// 3. find all ids from DB2;
preparedStatementTo =
connectionTo.prepareStatement("select * from " + name2 + " WHERE 1=1 " + where2);
b = preparedStatementTo.execute();
HashMap meta2 = new HashMap();
ArrayList db2 = new ArrayList();
if (b) {
resultSet = preparedStatementTo.getResultSet();
ResultSetMetaData setMetaData = resultSet.getMetaData();
for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
meta2.put(
setMetaData.getColumnLabel(i).toLowerCase(), setMetaData.getColumnClassName(i));
}
while (resultSet.next()) {
HashMap results = new HashMap();
for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
String columnName = setMetaData.getColumnLabel(i).toLowerCase();
Object o = resultSet.getObject(i);
if (o != null) {
results.put(columnName, o);
} else {
results.put(columnName + nullMark, nullMark);
}
}
db2.add(results);
}
} else {
log.append("Couldn't execute select from " + dbTo + "/" + name2 + " /r/n");
return;
}
// compare meta-data;
{
HashMap temp = new HashMap();
{
temp.putAll(meta);
Set set = meta2.keySet();
for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) {
Object o = iteratorSet.next();
if (meta.containsKey(o)) {
if (meta.get(o).equals(meta2.get(o))) {
temp.remove(o);
}
} else {
log.append("ERROR: Meta data not equals! \r\n");
log.append(o + "\t" + temp.get(o) + " not present in table " + name + "\r\n");
}
}
}
if (!temp.isEmpty()) {
log.append("ERROR: Meta data not equals! \r\n");
Set set = temp.keySet();
for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) {
Object o = iteratorSet.next();
log.append(o + "\t" + temp.get(o) + " != " + meta2.get(o) + "\r\n");
}
return;
}
}
for (Iterator iterator = db2.iterator(); iterator.hasNext(); ) {
HashMap db2Record = (HashMap) iterator.next();
if (toInsert.containsKey(db2Record.get("id"))) {
HashMap db1Record = (HashMap) toInsert.get(db2Record.get("id"));
boolean equal = true;
Set set = meta2.keySet();
for (Iterator iteratorSet = set.iterator(); equal && iteratorSet.hasNext(); ) {
String columnName2 = (String) iteratorSet.next();
if (db2Record.containsKey(columnName2 + nullMark)
|| db1Record.containsKey(columnName2 + nullMark)) {
equal =
db2Record.containsKey(columnName2 + nullMark)
&& db1Record.containsKey(columnName2 + nullMark);
} else {
// checking not-null;
equal = equalRecords(db1Record.get(columnName2), db2Record.get(columnName2));
}
}
if (!equal) {
toUpdate.put(db2Record.get("id"), toInsert.get(db2Record.get("id")));
}
toInsert.remove(db2Record.get("id"));
} else {
// this case shouldn't happen at all, since we've deleted all such records
}
}
log.append(
"Found "
+ toUpdate.size()
+ " to update, and "
+ toInsert.size()
+ " to insert.\r\n");
int totalUpdated = 0;
// 4. calculate all to update in DB2
if (!toUpdate.isEmpty()) {
Set set = toUpdate.keySet();
for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) {
StringBuffer sql = new StringBuffer();
Object id = iteratorSet.next();
HashMap r = (HashMap) toUpdate.get(id);
sql.append("UPDATE " + name2 + " SET ");
StringBuffer values = new StringBuffer();
Set en = meta2.keySet();
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
if (!o.equals("id")) {
if (values.length() != 0) {
values.append(",");
}
Object quote = dbQuotes.get(dbToType);
if (quote == null) {
quote = "";
}
values.append(quote).append(o).append(quote);
values.append(" = ? ");
}
}
values.append(" WHERE id = '" + r.get("id") + "';");
PreparedStatement statement =
connectionTo.prepareStatement(sql.toString() + values.toString());
en = meta2.keySet();
int i = 0;
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
if (!o.equals("id")) {
i++;
statement.setObject(i, r.get(o));
}
}
try {
totalUpdated += statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
log.append("Error occured: " + e + "\r\n");
}
}
}
if (verbose) log.append("Updated " + totalUpdated + " records.\r\n");
// 4. calculate all to insert to DB2
if (!toInsert.isEmpty()) {
StringBuffer header = new StringBuffer();
if (header.length() == 0) {
header.append(" INSERT INTO " + name2 + " (");
StringBuffer columns = new StringBuffer();
Set en = meta2.keySet();
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
if (columns.length() != 0) {
columns.append(",");
}
Object quote = dbQuotes.get(dbToType);
if (quote == null) {
quote = "";
}
columns.append(quote).append(o).append(quote);
}
header.append(columns.toString());
header.append(") VALUES ");
}
Set enumeration = toInsert.keySet();
for (Iterator iteratorSetX = enumeration.iterator(); iteratorSetX.hasNext(); ) {
Object id = iteratorSetX.next();
HashMap r = (HashMap) toInsert.get(id);
StringBuffer values = new StringBuffer();
if (values.length() != 0) {
values.append(",");
}
values.append("(");
StringBuffer columns = new StringBuffer();
Set en = meta2.keySet();
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
if (columns.length() != 0) {
columns.append(",");
}
columns.append(" ? ");
}
values.append(columns.toString());
values.append(");");
PreparedStatement statement =
connectionTo.prepareStatement(header.toString() + values.toString());
en = meta2.keySet();
int i = 0;
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
i++;
statement.setObject(i, r.get(o));
}
statement.execute();
}
}
if (verbose) log.append("Replication finished OK.\r\n");
} catch (Exception e) {
log.append("Some error occured: " + e + "\r\n");
log.append(e.getMessage() + "\r\n");
e.printStackTrace();
}
}
} catch (Exception e) {
log.append("Error with query = " + e);
log.append(e.getMessage());
return;
} finally {
try {
if (connectionFrom != null && !connectionFrom.isClosed()) {
connectionFrom.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connectionTo != null && !connectionTo.isClosed()) {
connectionTo.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
if (verbose) log.append("Ended at " + Calendar.getInstance().getTime());
}
/**
* returns the Object corresponding to the index passed from the resultSet
*
* @param row
* @param index
* @return
* @throws SQLException
*/
private Object getObject(ResultSet row, int index) throws SQLException {
return row.getObject(index);
}
/**
* 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) {
}
}
}
private String[] getFirstAlert() {
String[] keysForFirstAlert = new String[2];
ResultSet rs = null;
PreparedStatementWrapper pstat = null;
PreparedStatement pstatement = null;
try {
try {
/*try
{
//SELECT MIN(ONWERNAME)
pstat = agentName.rlAPI.fetchPreparedStatement(psForSelectMINOwnerName);
pstatement = pstat.getPreparedStatement();
rs = agentName.rlAPI.executeQuery(pstatement);
rs.next();
keysForFirstAlert[1] = (String)rs.getObject(1);
}
finally
{
try{
rs.close();
}catch(Exception e){}
http://ismp-build/php/download/1466f836f17657/AlarmTable.java
agentName.rlAPI.returnPreparedStatement(pstat);
}*/
// SELECT MIN(SOURCE) WHERE OWNERNAME=""//No I18N
pstat = agentName.rlAPI.fetchPreparedStatement(psForSelectMINSource);
pstatement = pstat.getPreparedStatement();
// pstatement.setString(1, keysForFirstAlert[1]);
rs = agentName.rlAPI.executeQuery(pstatement);
rs.next();
keysForFirstAlert[0] = (String) rs.getObject(1);
} finally {
try {
rs.close();
} catch (Exception e) {
}
agentName.rlAPI.returnPreparedStatement(pstat);
}
// SELECT MIN(ENTITY) WHERE OWNERNAME="" AND SOURCE=""//No I18N
pstat = agentName.rlAPI.fetchPreparedStatement(psForSelectMINEntity);
pstatement = pstat.getPreparedStatement();
pstatement.setString(1, keysForFirstAlert[0]);
// pstatement.setString(2, keysForFirstAlert[0]);
rs = agentName.rlAPI.executeQuery(pstatement);
rs.next();
keysForFirstAlert[1] = (String) rs.getObject(1);
} catch (Exception e) {
return null;
} finally {
try {
rs.close();
} catch (Exception e) {
}
agentName.rlAPI.returnPreparedStatement(pstat);
}
return keysForFirstAlert;
}
private String[] getNextAlert(String[] previousKeys) {
String[] keysForNextAlert = new String[2];
keysForNextAlert[0] = previousKeys[0];
keysForNextAlert[1] = previousKeys[1];
ResultSet rs = null;
PreparedStatementWrapper pstat = null;
PreparedStatement pstatement = null;
try {
// Next ENTITY
try {
// SELECT MIN(ENTITY) WHERE OWNERNAME="" AND SOURCE="" AND ENTITY>""//No I18N
pstat = agentName.rlAPI.fetchPreparedStatement(psForSelectNEXTEntity);
pstatement = pstat.getPreparedStatement();
pstatement.setString(1, previousKeys[0]);
pstatement.setString(2, previousKeys[1]);
// pstatement.setString(3, previousKeys[2]);
rs = agentName.rlAPI.executeQuery(pstatement);
rs.next();
keysForNextAlert[1] = (String) rs.getObject(1);
} finally {
try {
rs.close();
} catch (Exception e) {
}
agentName.rlAPI.returnPreparedStatement(pstat);
}
if (keysForNextAlert[1] == null) {
// Next SOURCE
try {
// SELECT MIN(SOURCE) WHERE OWNERNAME="" AND SOURCE>"";//No I18N
pstat = agentName.rlAPI.fetchPreparedStatement(psForSelectNEXTSource);
pstatement = pstat.getPreparedStatement();
pstatement.setString(1, previousKeys[0]);
// pstatement.setString(2, previousKeys[0]);
rs = agentName.rlAPI.executeQuery(pstatement);
rs.next();
keysForNextAlert[0] = (String) rs.getObject(1);
} finally {
try {
rs.close();
} catch (Exception e) {
}
agentName.rlAPI.returnPreparedStatement(pstat);
}
if (keysForNextAlert[0] == null) {
// Next OWNERNAME
/* try
{
//SELECT MIN(OWNERNAME) WHERE OWNERNAME>"";//No I18N
pstat = agentName.rlAPI.fetchPreparedStatement(psForSelectNEXTOwnerName);
pstatement = pstat.getPreparedStatement();
pstatement.setString(1, previousKeys[1]);
rs = agentName.rlAPI.executeQuery(pstatement);
rs.next();
keysForNextAlert[1] = (String)rs.getObject(1);
if(keysForNextAlert[1] == null)
return null;
}
finally
{
try{
rs.close();
}catch(Exception e){}
agentName.rlAPI.returnPreparedStatement(pstat);
}*/
try {
// SELECT MIN(SOURCE) WHERE OWNERNAME="";//No I18N
pstat = agentName.rlAPI.fetchPreparedStatement(psForSelectMINSource);
pstatement = pstat.getPreparedStatement();
pstatement.setString(1, keysForNextAlert[1]);
rs = agentName.rlAPI.executeQuery(pstatement);
rs.next();
keysForNextAlert[0] = (String) rs.getObject(1);
} finally {
try {
rs.close();
} catch (Exception e) {
}
agentName.rlAPI.returnPreparedStatement(pstat);
}
}
try {
// SELECT MIN(ENETITY) WHERE OWNERNAME="" AND SOURCE="";//No I18N
pstat = agentName.rlAPI.fetchPreparedStatement(psForSelectMINEntity);
pstatement = pstat.getPreparedStatement();
pstatement.setString(1, keysForNextAlert[0]);
rs = agentName.rlAPI.executeQuery(pstatement);
rs.next();
keysForNextAlert[1] = (String) rs.getObject(1);
} finally {
try {
rs.close();
} catch (Exception e) {
}
agentName.rlAPI.returnPreparedStatement(pstat);
}
}
} catch (Exception e) {
return null;
}
return keysForNextAlert;
}
private void initPK(ConnectionProvider cp, String shortTableName)
throws SQLException, DBException {
ResultSet rs;
IndexElement[] iearr = getIndexes();
if (iearr != null) {
for (int i = 0; i < iearr.length; i++)
if (iearr[i].isUnique()) {
UniqueKeyElementImpl ukei =
new UniqueKeyElementImpl(
iearr[i].getName().getName(),
false); // false = not primary key (primary flag is setted later)
UniqueKeyElement uke = new UniqueKeyElement(ukei, (TableElement) element, iearr[i]);
uke.setColumns(iearr[i].getColumns());
changeKeys(new UniqueKeyElement[] {uke}, DBElement.Impl.ADD);
}
UniqueKeyElement[] ukes = ((TableElement) element).getUniqueKeys();
rs =
cp.getDatabaseMetaData()
.getPrimaryKeys(cp.getConnection().getCatalog(), cp.getSchema(), shortTableName);
TreeMap cols = new TreeMap();
Object keySeq;
String colName;
if (rs != null) {
HashMap rset = new HashMap();
while (rs.next()) {
keySeq = rs.getObject("KEY_SEQ"); // NOI18N
colName = rs.getString("COLUMN_NAME").trim(); // NOI18N
cols.put(keySeq, colName); // NOI18N
}
rs.close();
}
boolean primary = false;
if (cols != null && cols.size() > 0) primary = true;
if (primary) {
if (ukes == null || ukes.length == 0) {
// issue 56492: no index defined for the primary key
// generate a UniqueKeyElement and an IndexElement for it
String indexName = "primary_key_index"; // NOI18N
int i = 1;
while (((TableElement) element).getIndex(DBIdentifier.create(indexName)) != null) {
indexName = indexName + i;
i++;
}
LinkedList idxs = new LinkedList();
for (Iterator it = cols.values().iterator(); it.hasNext(); ) {
// non-unique = false, thus the index is unique -- see initIndexes()
idxs.add(indexName + "." + it.next() + ".false"); // NOI18N
}
IndexElementImpl iei = new IndexElementImpl(this, indexName, true);
IndexElement ie = new IndexElement(iei, (TableElement) element);
iei.initColumns(idxs);
changeIndexes(new IndexElement[] {ie}, DBElement.Impl.ADD);
UniqueKeyElementImpl ukei = new UniqueKeyElementImpl(ie.getName().getName(), true);
UniqueKeyElement uke = new UniqueKeyElement(ukei, (TableElement) element, ie);
uke.setColumns(ie.getColumns());
changeKeys(new UniqueKeyElement[] {uke}, DBElement.Impl.ADD);
} else if (ukes.length == 1) ukes[0].setPrimaryKey(primary);
else {
ColumnElement[] ces;
Object[] o = cols.values().toArray();
boolean equals;
for (int i = 0; i < ukes.length; i++) {
ces = ukes[i].getColumns();
if (ces.length != o.length) continue;
else {
equals = true;
for (int j = 0; j < ces.length; j++)
if (!o[j].toString().equals(ces[j].getName().getName())) {
equals = false;
break;
}
if (equals) {
ukes[i].setPrimaryKey(primary);
break;
}
}
}
}
}
}
}
/*
* Builds sensors table from list of sensors currently loaded in the system
* */
public static boolean buildGeoIndex() {
boolean success = true;
sensors = new Vector<String>();
coordinates = new Vector<Point>();
getListOfSensors();
Properties properties = loadProperties();
if (properties != null) {
try {
dburl = properties.getProperty("dburl");
dbuser = properties.getProperty("dbuser");
dbpass = properties.getProperty("dbpass");
Connection conn = connect(dburl, dbuser, dbpass);
// ((org.postgresql.PGConnection) conn).addDataType("geometry", "org.postgis.PGgeometry");
// ((org.postgresql.PGConnection) conn).addDataType("box3d", "org.postgis.PGbox3d");
String st_create_table =
"DROP INDEX IF EXISTS gist_sensors;"
+ " DROP TABLE IF EXISTS sensors;"
+ " CREATE TABLE sensors ( \"name\" character(255) NOT NULL, \"location\" geometry NOT NULL );"
+ " CREATE INDEX gist_sensors ON sensors USING GIST ( location ); ";
logger.warn("Running query: " + st_create_table);
PreparedStatement prepareStatement = conn.prepareStatement(st_create_table);
prepareStatement.execute();
prepareStatement.close();
for (int i = 0; i < coordinates.size(); i++) {
String insert =
"insert into sensors values ( '"
+ sensors.get(i)
+ "', ST_MakePoint("
+ coordinates.get(i).getX()
+ " , "
+ coordinates.get(i).getY()
+ " , "
+ coordinates.get(i).getZ()
+ ") );";
PreparedStatement ps = conn.prepareStatement(insert);
ps.execute();
ps.close();
logger.warn(insert);
}
Statement s = conn.createStatement();
ResultSet r = s.executeQuery("select location, name from sensors");
while (r.next()) {
PGgeometry geom = (PGgeometry) r.getObject(1);
String name = r.getString(2);
logger.warn("Geometry " + geom.toString() + " : " + name);
}
s.close();
conn.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
success = false;
} catch (ClassNotFoundException e) {
logger.warn(e.getMessage(), e);
success = false;
}
} else {
logger.warn("Couldn't load properties files for PostGIS");
success = false;
}
return success;
}
private List<EventBean> execute(
PreparedStatement preparedStatement, Object[] lookupValuePerStream) {
if (ExecutionPathDebugLog.isDebugEnabled && log.isInfoEnabled()) {
log.info(".execute Executing prepared statement '" + preparedStatementText + "'");
}
// set parameters
SQLInputParameterContext inputParameterContext = null;
if (columnTypeConversionHook != null) {
inputParameterContext = new SQLInputParameterContext();
}
int count = 1;
for (int i = 0; i < lookupValuePerStream.length; i++) {
try {
Object parameter = lookupValuePerStream[i];
if (ExecutionPathDebugLog.isDebugEnabled && log.isInfoEnabled()) {
log.info(
".execute Setting parameter "
+ count
+ " to "
+ parameter
+ " typed "
+ ((parameter == null) ? "null" : parameter.getClass()));
}
if (columnTypeConversionHook != null) {
inputParameterContext.setParameterNumber(i + 1);
inputParameterContext.setParameterValue(parameter);
parameter = columnTypeConversionHook.getParameterValue(inputParameterContext);
}
setObject(preparedStatement, count, parameter);
} catch (SQLException ex) {
throw new EPException("Error setting parameter " + count, ex);
}
count++;
}
// execute
ResultSet resultSet;
if (enableJDBCLogging && jdbcPerfLog.isInfoEnabled()) {
long startTimeNS = System.nanoTime();
long startTimeMS = System.currentTimeMillis();
try {
resultSet = preparedStatement.executeQuery();
} catch (SQLException ex) {
throw new EPException("Error executing statement '" + preparedStatementText + '\'', ex);
}
long endTimeNS = System.nanoTime();
long endTimeMS = System.currentTimeMillis();
jdbcPerfLog.info(
"Statement '"
+ preparedStatementText
+ "' delta nanosec "
+ (endTimeNS - startTimeNS)
+ " delta msec "
+ (endTimeMS - startTimeMS));
} else {
try {
resultSet = preparedStatement.executeQuery();
} catch (SQLException ex) {
throw new EPException("Error executing statement '" + preparedStatementText + '\'', ex);
}
}
// generate events for result set
List<EventBean> rows = new LinkedList<EventBean>();
try {
SQLColumnValueContext valueContext = null;
if (columnTypeConversionHook != null) {
valueContext = new SQLColumnValueContext();
}
SQLOutputRowValueContext rowContext = null;
if (outputRowConversionHook != null) {
rowContext = new SQLOutputRowValueContext();
}
int rowNum = 0;
while (resultSet.next()) {
int colNum = 1;
Map<String, Object> row = new HashMap<String, Object>();
for (Map.Entry<String, DBOutputTypeDesc> entry : outputTypes.entrySet()) {
String columnName = entry.getKey();
Object value;
DatabaseTypeBinding binding = entry.getValue().getOptionalBinding();
if (binding != null) {
value = binding.getValue(resultSet, columnName);
} else {
value = resultSet.getObject(columnName);
}
if (columnTypeConversionHook != null) {
valueContext.setColumnName(columnName);
valueContext.setColumnNumber(colNum);
valueContext.setColumnValue(value);
valueContext.setResultSet(resultSet);
value = columnTypeConversionHook.getColumnValue(valueContext);
}
row.put(columnName, value);
colNum++;
}
EventBean eventBeanRow = null;
if (this.outputRowConversionHook == null) {
eventBeanRow = eventAdapterService.adapterForTypedMap(row, eventType);
} else {
rowContext.setValues(row);
rowContext.setRowNum(rowNum);
rowContext.setResultSet(resultSet);
Object rowData = outputRowConversionHook.getOutputRow(rowContext);
if (rowData != null) {
eventBeanRow =
eventAdapterService.adapterForTypedBean(rowData, (BeanEventType) eventType);
}
}
if (eventBeanRow != null) {
rows.add(eventBeanRow);
rowNum++;
}
}
} catch (SQLException ex) {
throw new EPException(
"Error reading results for statement '" + preparedStatementText + '\'', ex);
}
if (enableJDBCLogging && jdbcPerfLog.isInfoEnabled()) {
jdbcPerfLog.info("Statement '" + preparedStatementText + "' " + rows.size() + " rows");
}
try {
resultSet.close();
} catch (SQLException ex) {
throw new EPException("Error closing statement '" + preparedStatementText + '\'', ex);
}
return rows;
}
public void go(SystemEnvironment sysEnv) throws SDMSException {
Long sgId = null;
Long ZERO = new Long(0);
if (!sysEnv.cEnv.gid().contains(SDMSObject.adminGId)) {
SDMSPrivilege p = new SDMSPrivilege();
Vector v = SDMSMemberTable.idx_uId.getVector(sysEnv, sysEnv.cEnv.uid());
for (int i = 0; i < v.size(); i++) {
SDMSMember m = (SDMSMember) v.get(i);
try {
SDMSGrant gr =
SDMSGrantTable.idx_objectId_gId_getUnique(
sysEnv, new SDMSKey(ZERO, m.getGId(sysEnv)));
p.addPriv(sysEnv, gr.getPrivs(sysEnv).longValue());
} catch (NotFoundException nfe) {
}
}
try {
if (sysEnv.selectGroup != null) {
SDMSGroup sg = SDMSGroupTable.idx_name_getUnique(sysEnv, sysEnv.selectGroup);
sgId = sg.getId(sysEnv);
}
} catch (NotFoundException nfe) {
}
if (!(p.can(SDMSPrivilege.MANAGE_SEL) || (sgId != null && sysEnv.cEnv.gid().contains(sgId))))
throw new AccessViolationException(
new SDMSMessage(sysEnv, "03003081235", "Insufficient Privileges"));
}
int read = 0;
SDMSOutputContainer d_container = null;
if (cl_size > 0) {
clist = new int[cl_size];
ctype = new int[cl_size];
}
try {
Statement stmt = sysEnv.dbConnection.createStatement();
ResultSet rset = stmt.executeQuery(selectCmd);
ResultSetMetaData mdset = rset.getMetaData();
Vector desc = collist(mdset);
d_container = new SDMSOutputContainer(sysEnv, "Selected Values", desc);
while (rset.next()) {
Vector data = new Vector();
int j = 0;
for (int i = 1; i <= desc.size(); i++) {
Object o = rset.getObject(i);
if (cl_size > 0 && j < cl_size && i == clist[j]) {
o = convert(sysEnv, o, j);
j++;
}
data.addElement((rset.wasNull() ? null : o));
}
d_container.addData(sysEnv, data);
read++;
}
stmt.close();
sysEnv.dbConnection.commit();
} catch (SQLException sqle) {
try {
sysEnv.dbConnection.rollback();
} catch (SQLException sqle2) {
throw new RecoverableException(new SDMSMessage(sysEnv, "03310281524", "Connection lost"));
}
throw new CommonErrorException(
new SDMSMessage(sysEnv, "03204170024", "SQL Error : $1", sqle.toString()));
}
if (sv != null && sv.size() > 0) {
int sca[] = new int[sv.size()];
for (int i = 0; i < sv.size(); i++) {
sca[i] = ((Integer) sv.get(i)).intValue();
if (sca[i] >= d_container.columns)
throw new CommonErrorException(
new SDMSMessage(
sysEnv,
"03003081227",
"The sort column specified ($1) exceeds the number of columns in the output",
new Integer(sca[i])));
}
Collections.sort(d_container.dataset, d_container.getComparator(sysEnv, sca));
}
result.setOutputContainer(d_container);
result.setFeedback(
new SDMSMessage(sysEnv, "03204112153", "$1 Row(s) selected", new Integer(read)));
}