List of usage examples for java.sql ResultSet getObject
Object getObject(String columnLabel) throws SQLException;
Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.
From source file:com.cisco.dvbu.ps.deploytool.services.RegressionManagerUtils.java
/** * Similar to the same method in original pubtest utility, but doesn't throw an exception if 0 rows are returned * and uses existing(established) JDBC connection corresponding to its published datasource name. * /*from w ww . jav a 2 s. co m*/ * @param item * * @return result - A string containing a formatted response with the rows and first row latency: <rows>:<firstRowLatency> */ public static String executeQuery(RegressionItem item, HashMap<String, Connection> cisConnections, String outputFile, String delimiter, String printOutputType) throws CompositeException { // Set the command and action name String command = "executeQuery"; String actionName = "REGRESSION_TEST"; int rows = 0; String result = null; Connection conn = null; Statement stmt = null; ResultSet rs = null; start = System.currentTimeMillis(); long firstRowLatency = 0L; // Don't execute if -noop (NO_OPERATION) has been set otherwise execute under normal operation. if (CommonUtils.isExecOperation()) { try { conn = getJdbcConnection(item.database, cisConnections); // don't need to check for null here. String URL = null; String userName = null; if (conn.getMetaData() != null) { if (conn.getMetaData().getURL() != null) URL = conn.getMetaData().getURL(); if (conn.getMetaData().getUserName() != null) userName = conn.getMetaData().getUserName(); } RegressionManagerUtils.printOutputStr(printOutputType, "debug", "RegressionManagerUtils.executeQuery(item, cisConnections, outputFile, delimiter, printOutputType). item.database=" + item.database + " cisConnections.URL=" + URL + " cisConnections.userName=" + userName + " outputFile=" + outputFile + " delimiter=" + delimiter + " printOutputType=" + printOutputType, ""); RegressionManagerUtils.printOutputStr(printOutputType, "debug", "DEBUG: connection to DB successful", ""); stmt = conn.createStatement(); stmt.execute(item.input.replaceAll("\n", " ")); rs = stmt.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData(); int columns = rsmd.getColumnCount(); RegressionManagerUtils.printOutputStr(printOutputType, "debug", "DEBUG: number metadata columns=" + columns, ""); // Get the column metadata boolean addSep = false; String content = ""; RegressionManagerUtils.printOutputStr(printOutputType, "debug", "DEBUG: Get column metadata.", ""); for (int i = 0; i < columns; i++) { if (addSep) { content += delimiter; } if (rsmd.getColumnName(i + 1) != null) content += rsmd.getColumnName(i + 1).toString(); else content += ""; addSep = true; } if (outputFile != null) CommonUtils.appendContentToFile(outputFile, content); RegressionManagerUtils.printOutputStr(printOutputType, "results", content, ""); // Read the values boolean firstRow = true; RegressionManagerUtils.printOutputStr(printOutputType, "debug", "DEBUG: Begin Query Loop.", ""); while (rs.next()) { if (firstRow) { firstRowLatency = System.currentTimeMillis() - start; firstRow = false; RegressionManagerUtils.printOutputStr(printOutputType, "debug", "DEBUG: Set first row latency time=" + firstRowLatency, ""); } addSep = false; content = ""; for (int i = 0; i < columns; i++) { if (addSep) { content += delimiter; } if (rs.getObject(i + 1) != null) content += rs.getObject(i + 1).toString(); else content += ""; addSep = true; } if (outputFile != null) CommonUtils.appendContentToFile(outputFile, content); RegressionManagerUtils.printOutputStr(printOutputType, "results", content, ""); rows++; } } catch (SQLException e) { RegressionManagerUtils.printOutputStr(printOutputType, "debug", "DEBUG: Exception caught in RegressionManagerUtils.executeQuery:", ""); RegressionManagerUtils.printOutputStr(printOutputType, "debug", e.getMessage(), ""); throw new CompositeException("executeQuery(): " + e.getMessage()); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } } catch (SQLException e) { rs = null; stmt = null; throw new CompositeException( "executeQuery(): unable to close ResultSet or Statement" + e.getMessage()); } } RegressionManagerUtils.printOutputStr(printOutputType, "results", "\nCompleted executeQuery()", ""); } else { logger.info("\n\nWARNING - NO_OPERATION: COMMAND [" + command + "], ACTION [" + actionName + "] WAS NOT PERFORMED.\n"); } // <rows>:<firstRowLatency> result = "" + rows + ":" + firstRowLatency; return result; /* Note: to process this result string on the client invocation side use the following pattern: * * String result = RegressionManagerUtils.executeQuery(item, cisConnections, outputFile, delim, printOutputType, "results"); String results[] = result.split(":"); if (results.length > 1) { rowCount = Integer.valueOf(results[0]); firstRowLatency.addAndGet(Long.parseLong(results[1])); } */ }
From source file:ips1ap101.lib.core.db.xdp.RecursoCachedRowSetDataProvider.java
protected Object execute(String procedure, Object argumento) { // throws SQLException Bitacora.trace(getClass(), "execute", procedure, argumento); if (StringUtils.isNotBlank(procedure) && argumento != null) { try {// w w w . j av a2s. com if (TLC.getAgenteSql().isStoredProcedure(procedure)) { Object[] args = new Object[] { argumento }; Object resultado = TLC.getAgenteSql().executeProcedure(procedure, args); if (resultado instanceof ResultSet) { ResultSet resultSet = (ResultSet) resultado; if (resultSet.next()) { return resultSet.getObject(1); } // } else if (resultado instanceof Number) { // return resultado; } return resultado; } } catch (SQLException ex) { String localizedMessage = DBUtils.getProperErrorMessage(ex.getLocalizedMessage()); TLC.getBitacora().error(localizedMessage); return ex; } } return null; }
From source file:GestoSAT.GestoSAT.java
public Map getStock() { try {/* w w w.jav a 2s . co m*/ Map stock = new HashMap(); Class.forName("com.mysql.jdbc.Driver"); Statement st = con.createStatement(); ResultSet res = st.executeQuery( "Select * From stock S INNER JOIN proveedores P ON S.id_proveedor = P.id_Proveedor"); Proveedor p; this.getProveedores(); while (res.next()) { p = (Proveedor) this.proveedor.get(res.getInt("P.id_Proveedor")); Stock s = new Stock(res.getString("S.Nombre"), res.getFloat("S.Unidades"), res.getFloat("S.Precio_venta"), (String) res.getObject("S.Descripcion"), res.getFloat("S.Precio_compra"), res.getFloat("S.num_Alertar"), p); s = p.setStock(res.getInt("s.id_Stock"), s); stock.putIfAbsent(res.getInt("S.id_Stock"), s); } return stock; } catch (Exception ex) { Logger.getLogger(GestoSAT.class.getName()).log(Level.SEVERE, null, ex); return new HashMap(); } }
From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java
public List<Transaction> getTrans(String where, HashMap param) { List lst = new ArrayList(); try {//from www.j a v a 2 s. c om String sql = "select cp.trans_id transId, cp.content_provider_id contentproviderid, to_char(cp.request_date, 'dd/MM/yyyy hh24:mi:ss') requestDate, " + "(case nvl(bp.error_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when '32' then \n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end)\n" + "when 'null' then\n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "when 'null' then\n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "else TO_CHAR(4)\n" + "end\n" + ")" + " transStatus, " + "cp.confirm_status confirmStatus, cp.billing_code billingCode, cp.order_id orderId, cp.amount, " + "cp.order_info orderInfo, cp.trans_type transType, bp.bank_code bankCode, bp.cp_code cpCode, bp.msisdn, p.cp_name cpName, bp.customer_name customerName, bp.error_code || bp.correct_code errorCode from trans_cp cp join trans_bankplus bp on cp.trans_cp_id = bp.trans_cp_id " + " join content_provider p on p.content_provider_id = cp.content_provider_id "; // sql += where; // sql += " order by cp.request_date desc"; // log.debug(sql); //Rebuild where param int numparam = StringUtils.countMatches(where, ":"); // log.info("NUM PARAM: " + numparam); ArrayList<Object> arrParam = new ArrayList<Object>(); arrParam.add(0, ""); for (int i = 0; i < numparam; i++) { for (Object object : param.keySet()) { String key = object.toString(); Object val = param.get(key); int index = where.indexOf(":"); int indexCheck = where.indexOf(":" + key); if (index == indexCheck) { if (val instanceof ArrayList) { ArrayList arr = (ArrayList) val; String add = ""; for (int j = 0; j < arr.size(); j++) { arrParam.add(arr.get(j)); add += ",?"; } add = add.substring(1); where = where.substring(0, index) + add + where.substring(index + (":" + key).length()); } else if (val instanceof Date) { Date d = (Date) val; String date = new SimpleDateFormat("dd/MM/yyyy HH-mm-ss").format(d); arrParam.add(date); where = where.substring(0, index) + "to_date(?,'dd/MM/yyyy hh24-mi-ss')" + where.substring(index + (":" + key).length()); } else { arrParam.add(val); where = where.substring(0, index) + "?" + where.substring(index + (":" + key).length()); } // arrParam.add(val); // where = where.substring(0, index) + "?" + where.substring(index + (":" + key).length()); break; } } } numparam = arrParam.size() - 1; sql += where; sql += " order by cp.request_date desc"; // log.debug(sql); log.info("WHERE CLAUSE: " + where); log.info("LIST PARAM VALUE: " + arrParam); Session sess = DAOFactory.getNewSession(); // SQLQuery query = sess.createSQLQuery(sql); Connection conn = sess.connection(); PreparedStatement pstm = null; ResultSet rs = null; try { pstm = conn.prepareStatement(sql); for (int i = 0; i < numparam; i++) { Object objVal = arrParam.get(i + 1); if (objVal instanceof ArrayList) { ArrayList arrlist = (ArrayList) objVal; java.sql.Array sqlArray = null; // oracle.jdbc.OracleConnection oracleConnection = conn.unwrap(OracleConnection.class); if (arrlist.get(0) instanceof String) { // sqlArray = oracleConnection.createArrayOf("VARCHAR", arrlist.toArray()); // sqlArray = conn.createArrayOf("VARCHAR", arrlist.toArray()); } else { // sqlArray = conn.createArrayOf("NUMERIC", arrlist.toArray()); // sqlArray = oracleConnection.createArrayOf("INTEGER", arrlist.toArray()); } pstm.setArray(i + 1, sqlArray); } else if (objVal instanceof String) { pstm.setString(i + 1, objVal.toString()); // java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime()); // log.info("set string: " + (i + 1) + " - " + objVal.toString()); } else if (objVal instanceof Date) { Date d = (Date) objVal; // String date = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(d); java.sql.Timestamp sqlDate = new java.sql.Timestamp(d.getTime()); // log.info("set date: " + (i + 1) + " - " + sqlDate); pstm.setTimestamp(i + 1, sqlDate); // pstm.setString(i + 1, date); } else { pstm.setLong(i + 1, Long.parseLong(objVal.toString())); // log.info("set long: " + (i + 1) + " - " + Long.parseLong(objVal.toString())); } } // log.info("PREP: " + pstm.toString()); rs = pstm.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); int numCol = rsmd.getColumnCount(); String[] arrCol = new String[numCol]; String coltemp = ""; for (int i = 0; i < numCol; i++) { arrCol[i] = rsmd.getColumnName(i + 1); coltemp += rsmd.getColumnName(i + 1) + "#"; } // log.info("CCCCCC:" + coltemp); while (rs.next()) { // log.info("AAAAAAAAAAAAA:" + rs.getString(1)); Transaction trans = new Transaction(); for (int i = 0; i < numCol; i++) { // String data = rs.getString(arrCol[i]); Object data = rs.getObject(arrCol[i]); if (data != null) { callSetFunction(trans, "set" + arrCol[i], data); } } lst.add(trans); } } catch (Exception ex) { log.error("", ex); } finally { // log.info("================>finally"); if (rs != null) { rs.close(); } if (pstm != null) { pstm.close(); } if (sess != null) { sess.close(); } } // query.setResultTransformer(Transformers.aliasToBean(Transaction.class)); // for (Object object : param.keySet()) { // String key = object.toString(); // Object val = param.get(key); // if (val instanceof ArrayList) { //For select in // query.setParameterList(key, (ArrayList) val); // } else { // query.setParameter(key, param.get(key)); // } // } // log.info(query.toString()); // lst = query.list(); // ScrollableResults resultset = query.scroll(ScrollMode.FORWARD_ONLY); // resultset.beforeFirst(); // while (resultset.next()) { // Object[] objres = resultset.get(); // log.info(objres); // } // resultset.close(); } catch (Exception ex) { log.error("getTrans: ", ex); } finally { // DAOFactory.commitCurrentSessions(); } return lst; }
From source file:com.draagon.meta.manager.db.driver.GenericSQLDriver.java
protected void parseField(Object o, MetaField f, ResultSet rs, int j) throws SQLException { switch (f.getType()) { case MetaField.BOOLEAN: { boolean bv = rs.getBoolean(j); if (rs.wasNull()) { f.setBoolean(o, null);/*w ww . j a v a 2 s .co m*/ } else { f.setBoolean(o, new Boolean(bv)); } } break; case MetaField.BYTE: { byte bv = rs.getByte(j); if (rs.wasNull()) { f.setByte(o, null); } else { f.setByte(o, new Byte(bv)); } } break; case MetaField.SHORT: { short sv = rs.getShort(j); if (rs.wasNull()) { f.setShort(o, null); } else { f.setShort(o, new Short(sv)); } } break; case MetaField.INT: { int iv = rs.getInt(j); if (rs.wasNull()) { f.setInt(o, null); } else { f.setInt(o, new Integer(iv)); } } break; case MetaField.DATE: { Timestamp tv = rs.getTimestamp(j); if (rs.wasNull()) { f.setDate(o, null); } else { f.setDate(o, new java.util.Date(tv.getTime())); } } break; case MetaField.LONG: { long lv = rs.getLong(j); if (rs.wasNull()) { f.setLong(o, null); } else { f.setLong(o, new Long(lv)); } } break; case MetaField.FLOAT: { float fv = rs.getFloat(j); if (rs.wasNull()) { f.setFloat(o, null); } else { f.setFloat(o, new Float(fv)); } } break; case MetaField.DOUBLE: { double dv = rs.getDouble(j); if (rs.wasNull()) { f.setDouble(o, null); } else { f.setDouble(o, new Double(dv)); } } break; case MetaField.STRING: f.setString(o, rs.getString(j)); break; case MetaField.OBJECT: f.setObject(o, rs.getObject(j)); break; } }
From source file:org.openmrs.module.sync.api.db.hibernate.HibernateSyncDAO.java
public void exportChildDB(String uuidForChild, OutputStream os) throws DAOException { PrintStream out = new PrintStream(os); Set<String> tablesToSkip = new HashSet<String>(); {// w w w . j a v a2 s . c o m tablesToSkip.add("hl7_in_archive"); tablesToSkip.add("hl7_in_queue"); tablesToSkip.add("hl7_in_error"); tablesToSkip.add("formentry_archive"); tablesToSkip.add("formentry_queue"); tablesToSkip.add("formentry_error"); tablesToSkip.add("sync_class"); tablesToSkip.add("sync_import"); tablesToSkip.add("sync_record"); tablesToSkip.add("sync_server"); tablesToSkip.add("sync_server_class"); tablesToSkip.add("sync_server_record"); // TODO: figure out which other tables to skip // tablesToSkip.add("obs"); // tablesToSkip.add("concept"); // tablesToSkip.add("patient"); } List<String> tablesToDump = new ArrayList<String>(); Session session = sessionFactory.getCurrentSession(); String schema = (String) session.createSQLQuery("SELECT schema()").uniqueResult(); log.warn("schema: " + schema); // Get all tables that we'll need to dump { Query query = session.createSQLQuery( "SELECT tabs.table_name FROM INFORMATION_SCHEMA.TABLES tabs WHERE tabs.table_schema = '" + schema + "'"); for (Object tn : query.list()) { String tableName = (String) tn; if (!tablesToSkip.contains(tableName.toLowerCase())) tablesToDump.add(tableName); } } log.warn("tables to dump: " + tablesToDump); String thisServerGuid = getGlobalProperty(SyncConstants.PROPERTY_SERVER_UUID); // Write the DDL Header as mysqldump does { out.println("-- ------------------------------------------------------"); out.println("-- Database dump to create an openmrs child server"); out.println("-- Schema: " + schema); out.println("-- Parent GUID: " + thisServerGuid); out.println("-- Parent version: " + OpenmrsConstants.OPENMRS_VERSION); out.println("-- ------------------------------------------------------"); out.println(""); out.println("/*!40101 SET CHARACTER_SET_CLIENT=utf8 */;"); out.println("/*!40101 SET NAMES utf8 */;"); out.println("/*!40103 SET TIME_ZONE='+00:00' */;"); out.println("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;"); out.println("/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;"); out.println("/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;"); out.println("/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;"); out.println("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;"); out.println("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;"); out.println("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;"); out.println("/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;"); out.println(""); } try { // JDBC way of doing this // Connection conn = // DriverManager.getConnection("jdbc:mysql://localhost/" + schema, // "test", "test"); Connection conn = sessionFactory.getCurrentSession().connection(); try { Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); // Get the create database statement ResultSet rs = st.executeQuery("SHOW CREATE DATABASE " + schema); for (String tableName : tablesToDump) { out.println(); out.println("--"); out.println("-- Table structure for table `" + tableName + "`"); out.println("--"); out.println("DROP TABLE IF EXISTS `" + tableName + "`;"); out.println("SET @saved_cs_client = @@character_set_client;"); out.println("SET character_set_client = utf8;"); rs = st.executeQuery("SHOW CREATE TABLE " + tableName); while (rs.next()) { out.println(rs.getString("Create Table") + ";"); } out.println("SET character_set_client = @saved_cs_client;"); out.println(); { out.println("-- Dumping data for table `" + tableName + "`"); out.println("LOCK TABLES `" + tableName + "` WRITE;"); out.println("/*!40000 ALTER TABLE `" + tableName + "` DISABLE KEYS */;"); boolean first = true; rs = st.executeQuery("select * from " + tableName); ResultSetMetaData md = rs.getMetaData(); int numColumns = md.getColumnCount(); int rowNum = 0; boolean insert = false; while (rs.next()) { if (rowNum == 0) { insert = true; out.print("INSERT INTO `" + tableName + "` VALUES "); } ++rowNum; if (first) { first = false; } else { out.print(", "); } if (rowNum % 20 == 0) { out.println(); } out.print("("); for (int i = 1; i <= numColumns; ++i) { if (i != 1) { out.print(","); } if (rs.getObject(i) == null) { out.print("NULL"); } else { switch (md.getColumnType(i)) { case Types.VARCHAR: case Types.CHAR: case Types.LONGVARCHAR: out.print("'"); out.print( rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'")); out.print("'"); break; case Types.BIGINT: case Types.DECIMAL: case Types.NUMERIC: out.print(rs.getBigDecimal(i)); break; case Types.BIT: out.print(rs.getBoolean(i)); break; case Types.INTEGER: case Types.SMALLINT: case Types.TINYINT: out.print(rs.getInt(i)); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: out.print(rs.getDouble(i)); break; case Types.BLOB: case Types.VARBINARY: case Types.LONGVARBINARY: Blob blob = rs.getBlob(i); out.print("'"); InputStream in = blob.getBinaryStream(); while (true) { int b = in.read(); if (b < 0) { break; } char c = (char) b; if (c == '\'') { out.print("\'"); } else { out.print(c); } } out.print("'"); break; case Types.CLOB: out.print("'"); out.print( rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'")); out.print("'"); break; case Types.DATE: out.print("'" + rs.getDate(i) + "'"); break; case Types.TIMESTAMP: out.print("'" + rs.getTimestamp(i) + "'"); break; default: throw new RuntimeException("TODO: handle type code " + md.getColumnType(i) + " (name " + md.getColumnTypeName(i) + ")"); } } } out.print(")"); } if (insert) { out.println(";"); insert = false; } out.println("/*!40000 ALTER TABLE `" + tableName + "` ENABLE KEYS */;"); out.println("UNLOCK TABLES;"); out.println(); } } } finally { conn.close(); } // Now we mark this as a child out.println("-- Now mark this as a child database"); if (uuidForChild == null) uuidForChild = SyncUtil.generateUuid(); out.println("update global_property set property_value = '" + uuidForChild + "' where property = '" + SyncConstants.PROPERTY_SERVER_UUID + "';"); // Write the footer of the DDL script { out.println("/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;"); out.println("/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;"); out.println("/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;"); out.println("/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;"); out.println("/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;"); out.println("/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;"); out.println("/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;"); out.println("/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;"); } out.flush(); out.close(); } catch (IOException ex) { log.error("IOException", ex); } catch (SQLException ex) { log.error("SQLException", ex); } }
From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
private JSONArray toJSONArray(ResultSet rs, JSONArray json) throws Exception { String temp = null;/* w ww . j av a 2s . c o m*/ try { // we will need the column names, this will save the table meta-data like column nmae. java.sql.ResultSetMetaData rsmd = rs.getMetaData(); //loop through the ResultSet while (rs.next()) { //figure out how many columns there are int numColumns = rsmd.getColumnCount(); //each row in the ResultSet will be converted to a JSON Object JSONObject obj = new JSONObject(); // loop through all the columns and place them into the JSON Object for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { obj.put(column_name, rs.getArray(column_name).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getBigDecimal(column_name).toBigInteger().toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, ((Boolean) rs.getBoolean(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { obj.put(column_name, rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { obj.put(column_name, ((Double) rs.getDouble(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, ((Float) rs.getFloat(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { // temp = rs.getString(column_name); //saving column data to temp variable // temp = ESAPI.encoder().canonicalize(temp); //decoding data to base state // temp = ESAPI.encoder().encodeForHTML(temp); //encoding to be browser safe // obj.put(column_name, temp); //putting data into JSON object // obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.TIME) { obj.put(column_name, TimeStampUtils.dateTimeToString(rs.getTime(column_name))); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, TimeStampUtils.timeStampToString(rs.getTimestamp(column_name))); } else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) { obj.put(column_name, rs.getBigDecimal(column_name).toString()); } else { obj.put(column_name, rs.getObject(column_name)); } } //end foreach json.add(obj); } //end while } catch (Exception e) { e.printStackTrace(); } return json; //return JSON array }
From source file:com.alibaba.wasp.jdbc.TestJdbcResultSet.java
@Test public void testDatetime() throws SQLException { trace("test DATETIME"); ResultSet rs; Object o;//from w w w.j av a 2 s . com // rs = stat.executeQuery("call date '99999-12-23'"); // rs.next(); // assertEquals("99999-12-23", rs.getString(1)); // rs = stat.executeQuery("call timestamp '99999-12-23 01:02:03.000'"); // rs.next(); // assertEquals("99999-12-23 01:02:03.0", rs.getString(1)); // rs = stat.executeQuery("call date '-99999-12-23'"); // rs.next(); // assertEquals("-99999-12-23", rs.getString(1)); // rs = stat.executeQuery("call timestamp '-99999-12-23 01:02:03.000'"); // rs.next(); // assertEquals("-99999-12-23 01:02:03.0", rs.getString(1)); stat = conn.createStatement(); // stat.execute("CREATE TABLE test(ID INT PRIMARY KEY,VALUE DATETIME)"); stat.execute( "INSERT INTO test (column1,column6,column2,column3) VALUES (1,'2011-11-11 0:0:0', 13, 'testDatetime')"); stat.execute( "INSERT INTO test (column1,column6,column2,column3) VALUES (2,'2002-02-02 02:02:02', 13, 'testDatetime')"); stat.execute( "INSERT INTO test (column1,column6,column2,column3) VALUES (3,'1800-01-01 0:0:0', 13, 'testDatetime')"); stat.execute( "INSERT INTO test (column1,column6,column2,column3) VALUES (4,'9999-12-31 23:59:59', 13, 'testDatetime')"); stat.execute( "INSERT INTO test (column1,column6,column2,column3) VALUES (5,'9999-12-31 23:59:59', 13, 'testDatetime')"); // stat.execute("INSERT INTO test (column1,column6,column2,column3) VALUES(5,NULL)"); rs = stat.executeQuery("SELECT column1,column6 FROM test where column3='testDatetime' ORDER BY column1"); // assertResultSetMeta(rs, 2, new String[] { "ID", "VALUE" }, new int[] { // Types.INTEGER, Types.TIMESTAMP }, new int[] { 10, 23 }, new int[] { 0, // 10 }); // rs = stat.executeQuery("SELECT * FROM test ORDER BY ID"); // assertResultSetMeta(rs, 2, new String[] { "ID", "VALUE" }, new int[] { // Types.INTEGER, Types.TIMESTAMP }, new int[] { 10, 23 }, new int[] { 0, // 10 }); rs.next(); java.sql.Date date; java.sql.Time time; Timestamp ts; date = rs.getDate(2); assertTrue(!rs.wasNull()); time = rs.getTime(2); assertTrue(!rs.wasNull()); ts = rs.getTimestamp(2); assertTrue(!rs.wasNull()); trace("Date: " + date.toString() + " Time:" + time.toString() + " Timestamp:" + ts.toString()); trace("Date ms: " + date.getTime() + " Time ms:" + time.getTime() + " Timestamp ms:" + ts.getTime()); trace("1970 ms: " + Timestamp.valueOf("1970-01-01 00:00:00.0").getTime()); assertEquals(Timestamp.valueOf("2011-11-11 00:00:00.0").getTime(), date.getTime()); assertEquals(Timestamp.valueOf("1970-01-01 00:00:00.0").getTime(), time.getTime()); assertEquals(Timestamp.valueOf("2011-11-11 00:00:00.0").getTime(), ts.getTime()); assertTrue(date.equals(java.sql.Date.valueOf("2011-11-11"))); assertTrue(time.equals(java.sql.Time.valueOf("00:00:00"))); assertTrue(ts.equals(Timestamp.valueOf("2011-11-11 00:00:00.0"))); assertFalse(rs.wasNull()); o = rs.getObject(2); trace(o.getClass().getName()); assertTrue(o instanceof Timestamp); assertTrue(((Timestamp) o).equals(Timestamp.valueOf("2011-11-11 00:00:00"))); assertFalse(rs.wasNull()); rs.next(); date = rs.getDate("COLUMN6"); assertTrue(!rs.wasNull()); time = rs.getTime("COLUMN6"); assertTrue(!rs.wasNull()); ts = rs.getTimestamp("COLUMN6"); assertTrue(!rs.wasNull()); trace("Date: " + date.toString() + " Time:" + time.toString() + " Timestamp:" + ts.toString()); assertEquals("2002-02-02", date.toString()); assertEquals("02:02:02", time.toString()); assertEquals("2002-02-02 02:02:02.0", ts.toString()); rs.next(); assertEquals("1800-01-01", rs.getDate("column6").toString()); assertEquals("00:00:00", rs.getTime("column6").toString()); assertEquals("1800-01-01 00:00:00.0", rs.getTimestamp("column6").toString()); rs.next(); assertEquals("9999-12-31", rs.getDate("Column6").toString()); assertEquals("23:59:59", rs.getTime("Column6").toString()); assertEquals("9999-12-31 23:59:59.0", rs.getTimestamp("Column6").toString()); // assertTrue(!rs.next()); }
From source file:com.enonic.vertical.engine.handlers.MenuHandler.java
private List<Element> findAdminMenuItemElements(Document doc, Connection conn, int menuKey, String[] groupKeys, String anonGroupKey, boolean adminRights) throws SQLException { // Find access rights HashMap<Integer, Integer> accessRights = findAdminMenuItemAccessRights(conn, menuKey, groupKeys, anonGroupKey, adminRights);//from w w w.ja v a 2 s .c o m // Composte the sql StringBuffer sql = new StringBuffer("SELECT "); sql.append(this.db.tMenuItem.mei_lKey.getName()).append(", "); sql.append(this.db.tMenuItem.mei_lParent.getName()).append(", "); sql.append(this.db.tMenuItem.mei_sName.getName()).append(", "); sql.append(this.db.tMenuItem.mei_sSubTitle.getName()).append(", "); sql.append(this.db.tMenuItem.mei_bHidden.getName()).append(", "); sql.append(this.db.tMenuItem.mei_lOrder.getName()).append(", "); sql.append(this.db.tMenuItem.mei_mid_lkey.getName()).append(", "); sql.append(this.db.tPageTemplate.pat_lType.getName()).append(", "); sql.append(this.db.tPageTemplate.pat_sName.getName()).append(", "); sql.append(this.db.tMenuItem.mei_sDisplayName.getName()); sql.append(" FROM ").append(this.db.tMenuItem.getName()); sql.append(" LEFT JOIN ").append(this.db.tPage.getName()); sql.append(" ON ").append(this.db.tMenuItem.mei_pag_lKey.getName()).append(" = ") .append(this.db.tPage.pag_lKey.getName()); sql.append(" LEFT JOIN ").append(this.db.tPageTemplate.getName()); sql.append(" ON ").append(this.db.tPage.pag_pat_lKey.getName()).append(" = ") .append(this.db.tPageTemplate.pat_lKey.getName()); sql.append(" WHERE ").append(this.db.tMenuItem.mei_men_lKey.getName()); sql.append(" = ").append(String.valueOf(menuKey)); // Execute the sql ArrayList<Element> list = new ArrayList<Element>(); HashMap<Integer, Element> elements = new HashMap<Integer, Element>(); HashMap<Integer, Integer> keyParentMap = new HashMap<Integer, Integer>(); PreparedStatement stmt = null; ResultSet result = null; try { stmt = conn.prepareStatement(sql.toString()); result = stmt.executeQuery(); while (result.next()) { Integer itemKey = result.getInt(1); Number parentKey = (Number) result.getObject(2); String name = result.getString(3); String alternativeName = result.getString(db.tMenuItem.mei_sSubTitle.getName()); boolean hidden = (result.getInt(5) == 1 || result.wasNull()); int order = result.getInt(6); MenuItemType menuItemType = MenuItemType.get(result.getInt(7)); Number value = (Number) result.getObject(8); PageTemplateType pageTemplateType = value != null ? PageTemplateType.get(value.intValue()) : null; String pageTemplateName = result.getString(db.tPageTemplate.pat_sName.getName()); String displayName = result.getString(db.tMenuItem.mei_sDisplayName.getName()); Element elem = doc.createElement("menuitem"); elem.setAttribute("key", itemKey.toString()); elem.setAttribute("name", StringUtil.getXMLSafeString(name)); if (alternativeName != null && alternativeName.length() > 0) { elem.setAttribute("alternativename", StringUtil.getXMLSafeString(alternativeName)); } if (StringUtils.isNotBlank(displayName)) { elem.setAttribute("displayname", StringUtil.getXMLSafeString(displayName)); } elem.setAttribute("order", String.valueOf(order)); elem.setAttribute("visible", String.valueOf(!hidden)); if (pageTemplateName != null) { elem.setAttribute("pagetemplatename", pageTemplateName); } appendMenuAccessRights(elem, accessRights.get(itemKey)); elem.setAttribute("path", getMenuItemPath(itemKey)); String type = null; if (pageTemplateType != null) { type = pageTemplateType.getName(); } else { if (menuItemType == MenuItemType.URL) { type = "url"; } else if (menuItemType == MenuItemType.CONTENT) { type = "content"; } else if (menuItemType == MenuItemType.LABEL) { type = "label"; } else if (menuItemType == MenuItemType.SECTION) { type = "section"; } else if (menuItemType == MenuItemType.SHORTCUT) { type = "shortcut"; } } elem.setAttribute("type", type); elements.put(itemKey, elem); if (parentKey != null) { keyParentMap.put(itemKey, parentKey.intValue()); } else { list.add(elem); } } } finally { close(result); close(stmt); } // Nest the elements for (Object o : keyParentMap.entrySet()) { Map.Entry entry = (Map.Entry) o; Integer itemKey = (Integer) entry.getKey(); Integer parentKey = (Integer) entry.getValue(); Element parentElem = elements.get(parentKey); Element itemElem = elements.get(itemKey); if ((parentElem != null) && (itemElem != null)) { parentElem.appendChild(itemElem); } } return list; }
From source file:com.belle.yitiansystem.merchant.service.impl.MerchantsService.java
/** * ?sql?/*from w w w.ja va 2 s .c o m*/ * * @throws SQLException * **/ private String getResouseBySql(String sql, List<Object> params) throws SQLException { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String str = ""; try { conn = getConnection(); pstmt = conn.prepareStatement(sql); if (params != null && params.size() > 0) { for (int j = 0; j < params.size(); j++) { pstmt.setObject(j + 1, params.get(j)); } } rs = pstmt.executeQuery(); while (rs.next()) { ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 0; i < rsmd.getColumnCount();) { ++i; str = (String) rs.getObject(i); } } } catch (Exception e) { // TODO: handle exception logger.error("sql?!" + sql, e); } finally { close(conn, pstmt, rs); } return str; }