List of usage examples for java.sql ResultSetMetaData getColumnCount
int getColumnCount() throws SQLException;
ResultSet object. From source file:com.modelmetrics.cloudconverter.forceutil.DataUpsertExecutor.java
public void executeWithResultSet(MigrationContext migrationContext) throws Exception { log.debug("starting data transfer (upsert)..."); dao.setSalesforceSession(migrationContext.getSalesforceSession()); Collection<Sproxy> toUpsert = new ArrayList<Sproxy>(); ResultSet rs = migrationContext.getResultSet(); ResultSetMetaData rsmd = migrationContext.getResultSetMetaData(); if (rs == null) { log.info("result set is null"); }/* w ww.j a va2 s .c om*/ while (rs.next()) { Sproxy current = sproxyBuilder.buildEmpty(migrationContext.getCustomObject().getFullName()); for (int i = 0; i < rsmd.getColumnCount(); i++) { current.setValue(migrationContext.getFieldMap().get(rsmd.getColumnName(i + 1)), rs.getObject(i + 1)); } toUpsert.add(current); if (toUpsert.size() == MAX_SPROXY_BATCH_SIZE) { dao.upsert(migrationContext.getExternalIdForUpsert(), toUpsert); toUpsert = new ArrayList<Sproxy>(); } } log.debug("starting the upsert..." + migrationContext.getExternalIdForUpsert()); dao.upsert(migrationContext.getExternalIdForUpsert(), toUpsert); log.debug("insert complete..."); }
From source file:com.neu.edu.hw5p4.controller.CsvController.java
@Override public ModelAndView handleRequest(HttpServletRequest hsr, HttpServletResponse hsr1) throws Exception { //throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. HttpSession session = hsr.getSession(); String action = hsr.getParameter("action"); String fileName;//from w w w .j a v a 2s . co m int k = 1; int rows = 0; ModelAndView mv = new ModelAndView(); int content; int pageNumberDisplay; if (action.equals("showDetails")) { int paginationButtons; if (hsr.getParameter("entryPage").equals("true")) { fileName = hsr.getParameter("fileName"); session.setAttribute("fileName", fileName); System.out.println("session out file name" + session.getAttribute("fileName")); try { Class.forName("org.relique.jdbc.csv.CsvDriver"); Connection conn = DriverManager.getConnection("jdbc:relique:csv:D:"); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM " + fileName); int totalRows = 0; while (rs.next()) { totalRows += 1; } content = Integer.parseInt(hsr.getParameter("defaultContent")); System.out.println("---------------" + hsr.getParameter("defaultContent")); pageNumberDisplay = content * 100; rs = stmt.executeQuery("SELECT * FROM " + fileName + " LIMIT 100 OFFSET " + pageNumberDisplay); ArrayList<SalesOrder> salesOrderArrayList = new ArrayList<>(); while (rs.next()) { SalesOrder s = new SalesOrder(); s.setAccountnumber(rs.getString("accountnumber")); s.setBilltoaddressid(rs.getString("billtoaddressid")); s.setComment(rs.getString("comment")); s.setCreditcardapprovalcode(rs.getString("creditcardapprovalcode")); s.setCreditcardid(rs.getString("creditcardid")); s.setCurrencyrateid(rs.getString("currencyrateid")); s.setCustomerid(rs.getString("customerid")); s.setDuedate(rs.getString("duedate")); s.setFreight(rs.getString("freight")); s.setModifieddate(rs.getString("modifieddate")); s.setOnlineorderflag(rs.getString("onlineorderflag")); s.setOrderdate(rs.getString("orderdate")); s.setPurchaseordernumber(rs.getString("purchaseordernumber")); s.setRevisionnumber(rs.getString("revisionnumber")); s.setSalesorderid(rs.getString("salesorderid")); s.setSalesordernumber(rs.getString("salesordernumber")); s.setSalespersonid(rs.getString("salespersonid")); s.setShipdate(rs.getString("shipdate")); s.setShipmethodid(rs.getString("shipmethodid")); s.setShiptoaddressid(rs.getString("shiptoaddressid")); s.setStatus(rs.getString("status")); s.setSubtotal(rs.getString("subtotal")); s.setTaxamt(rs.getString("taxamt")); s.setTerritoryid(rs.getString("territoryid")); s.setTotaldue(rs.getString("totaldue")); salesOrderArrayList.add(s); rows = rows + 1; } session.setAttribute("salesOrderList", salesOrderArrayList); String pageNumber = "a"; session.setAttribute("paginationHelp", content); hsr.setAttribute("pageNumber", pageNumber); mv.setViewName("index");//viewDetails conn.close(); System.out.println("RowsCount" + totalRows); if (totalRows % 100 == 0) { paginationButtons = totalRows / 100; session.setAttribute("paginationButtons", paginationButtons); } else { paginationButtons = Math.floorDiv(totalRows, 100) + 1; session.setAttribute("paginationButtons", paginationButtons); } } catch (Exception e) { e.printStackTrace(); } } else { fileName = (String) session.getAttribute("fileName"); System.out.println("file name " + fileName); System.out.println("---------------" + hsr.getParameter("defaultContent")); content = Integer.parseInt(hsr.getParameter("defaultContent")); System.out.println("---------------" + hsr.getParameter("defaultContent")); //content=Integer.parseInt(hsr.getParameter("content")); pageNumberDisplay = content * 100; try { Class.forName("org.relique.jdbc.csv.CsvDriver"); Connection conn = DriverManager.getConnection("jdbc:relique:csv:D:"); Statement stmt = conn.createStatement(); ResultSet rs = stmt .executeQuery("SELECT * FROM " + fileName + " LIMIT 100 OFFSET " + pageNumberDisplay); ResultSetMetaData rsmd = rs.getMetaData(); int colums = rsmd.getColumnCount(); ArrayList<SalesOrder> salesOrderArrayList = new ArrayList<>(); while (rs.next()) { SalesOrder s = new SalesOrder(); s.setAccountnumber(rs.getString("accountnumber")); s.setBilltoaddressid(rs.getString("billtoaddressid")); s.setComment(rs.getString("comment")); s.setCreditcardapprovalcode(rs.getString("creditcardapprovalcode")); s.setCreditcardid(rs.getString("creditcardid")); s.setCurrencyrateid(rs.getString("currencyrateid")); s.setCustomerid(rs.getString("customerid")); s.setDuedate(rs.getString("duedate")); s.setFreight(rs.getString("freight")); s.setModifieddate(rs.getString("modifieddate")); s.setOnlineorderflag(rs.getString("onlineorderflag")); s.setOrderdate(rs.getString("orderdate")); s.setPurchaseordernumber(rs.getString("purchaseordernumber")); s.setRevisionnumber(rs.getString("revisionnumber")); s.setSalesorderid(rs.getString("salesorderid")); s.setSalesordernumber(rs.getString("salesordernumber")); s.setSalespersonid(rs.getString("salespersonid")); s.setShipdate(rs.getString("shipdate")); s.setShipmethodid(rs.getString("shipmethodid")); s.setShiptoaddressid(rs.getString("shiptoaddressid")); s.setStatus(rs.getString("status")); s.setSubtotal(rs.getString("subtotal")); s.setTaxamt(rs.getString("taxamt")); s.setTerritoryid(rs.getString("territoryid")); s.setTotaldue(rs.getString("totaldue")); salesOrderArrayList.add(s); rows = rows + 1; } session.setAttribute("salesOrderList", salesOrderArrayList); session.setAttribute("columCount", colums); String pageNumber = "a"; session.setAttribute("paginationHelp", content); hsr.setAttribute("pageNumber", pageNumber); mv.setViewName("index");//viewDetails conn.close(); } catch (Exception e) { e.printStackTrace(); } } } SalesOrder s = new SalesOrder(); if (action.equals("addToDB")) { String pageNumber = "b"; hsr.setAttribute("pageNumber", pageNumber); System.out.println("account number " + hsr.getParameter("accountnumber")); s.setAccountnumber(hsr.getParameter("accountnumber")); System.out.println("Account number from bean=" + s.getAccountnumber()); s.setBilltoaddressid(hsr.getParameter("billtoaddressid")); s.setComment(hsr.getParameter("comment")); s.setCreditcardapprovalcode(hsr.getParameter("creditcardapprovalcode")); s.setCreditcardid(hsr.getParameter("creditcardid")); s.setCurrencyrateid(hsr.getParameter("currencyrateid")); s.setCustomerid(hsr.getParameter("customerid")); s.setDuedate(hsr.getParameter("duedate")); s.setFreight(hsr.getParameter("freight")); s.setModifieddate(hsr.getParameter("modifieddate")); s.setOnlineorderflag(hsr.getParameter("onlineorderflag")); s.setOrderdate(hsr.getParameter("orderdate")); s.setPurchaseordernumber(hsr.getParameter("purchaseordernumber")); s.setRevisionnumber(hsr.getParameter("revisionnumber")); s.setSalesorderid(hsr.getParameter("salesorderid")); s.setSalesordernumber(hsr.getParameter("salesordernumber")); s.setSalespersonid(hsr.getParameter("salespersonid")); s.setShipdate(hsr.getParameter("shipdate")); s.setShipmethodid(hsr.getParameter("shipmethodid")); s.setShiptoaddressid(hsr.getParameter("shiptoaddressid")); s.setStatus(hsr.getParameter("status")); s.setSubtotal(hsr.getParameter("subtotal")); s.setTaxamt(hsr.getParameter("taxamt")); s.setTerritoryid(hsr.getParameter("territoryid")); s.setTotaldue(hsr.getParameter("totaldue")); uploadDAO.uploadDatatoDB(s); // } // hsr.setAttribute("rowsAdded", salesOrderArrayList.size()); mv.setViewName("index");//dbUpdated } return mv; }
From source file:com.iih5.smartorm.model.DbExecutor.java
/** * Model/*from w ww.java2 s. c om*/ * @param sql * @param paras * @param model * @param <T> * @return * @ */ <T> List<T> queryList(String sql, Object[] paras, final Class<T> model) { final Set<String> columnMeta = new HashSet<String>(); return jdbc.query(sql, paras, new RowMapper<T>() { public T mapRow(ResultSet rs, int rowNum) throws SQLException { try { if (columnMeta.size() == 0) { for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) { String column = rs.getMetaData().getColumnLabel(i + 1); columnMeta.add(column); } } Model mModel = (Model) model.newInstance(); Field[] fields = mModel.getClass().getFields(); if (0 < fields.length) { for (Field f : fields) { if (columnMeta.contains(f.getName())) { f.set(mModel, rs.getObject(f.getName())); } } } else { ResultSetMetaData rad = rs.getMetaData(); int columnCount = rad.getColumnCount(); Map<String, Object> attrs = mModel.getAttrs(); for (int i = 1; i <= columnCount; i++) { Object value = rs.getObject(i); attrs.put(rad.getColumnName(i), value); } } return (T) mModel; } catch (Exception e) { e.printStackTrace(); } return null; } }); }
From source file:com.cloudera.recordbreaker.analyzer.DataQuery.java
public List<List<Object>> query(DataDescriptor desc1, DataDescriptor desc2, String projectionClause, String selectionClause) throws SQLException, IOException { String tablename1 = grabTable(desc1); String tablename2 = null;// w w w .j av a 2s. c o m if (desc2 != null) { tablename2 = grabTable(desc2); } // // Build the SQL query against the table // if (projectionClause == null || projectionClause.trim().length() == 0) { projectionClause = "*"; } if (selectionClause == null) { selectionClause = ""; } if (tablename2 == null) { projectionClause = projectionClause.replaceAll("DATA", tablename1); selectionClause = selectionClause.replaceAll("DATA", tablename1); } projectionClause = projectionClause.trim(); selectionClause = selectionClause.trim(); String query; if (tablename2 == null) { query = "SELECT " + projectionClause + " FROM " + tablename1; } else { query = "SELECT " + projectionClause + " FROM " + tablename1 + " DATA1" + ", " + tablename2 + " DATA2"; } if (selectionClause.length() > 0) { query = query + " WHERE " + selectionClause; } // // Try to run it first with the impala connection. // If that fails, try hive. // List<List<Object>> result = new ArrayList<List<Object>>(); Statement stmt = impalaCon.createStatement(); LOG.info("Processing: " + query); try { ResultSet res = null; try { res = stmt.executeQuery(query); LOG.info("Ran Impala query: " + query); } catch (Exception iex) { iex.printStackTrace(); // Fail back to Hive! stmt.close(); stmt = hiveCon.createStatement(); res = stmt.executeQuery(query); LOG.info("Ran Hive query: " + query); } // OK now do the real work ResultSetMetaData rsmd = res.getMetaData(); List<Object> metatuple = new ArrayList<Object>(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { metatuple.add(rsmd.getColumnLabel(i)); } result.add(metatuple); while (res.next()) { List<Object> tuple = new ArrayList<Object>(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { tuple.add(res.getObject(i)); } result.add(tuple); } return result; } finally { stmt.close(); } }
From source file:com.waveerp.DbInformation.java
public String loadDataEntries() { // Added by Jammi Dee 05/03/2012 registrySystem rss = new registrySystem(); url = rss.readRegistry("NA", "NA", "NA", "DBURL"); dbName = rss.readRegistry("NA", "NA", "NA", "DBDATABASE"); driver = rss.readRegistry("NA", "NA", "NA", "DBDRIVER"); user = rss.readRegistry("NA", "NA", "NA", "DBUSER"); password = rss.readRegistry("NA", "NA", "NA", "DBPASSWORD"); // Override Wave ERP database dbName = "information_schema"; // Added by Jammi Dee 05/03/2012 // Call the encryption management system desEncryption de = new desEncryption(); de.Encrypter("", ""); try {//from w w w .java 2 s .c om Class.forName(getDriver()); con = DriverManager.getConnection(url + dbName, user, password); ps = con.createStatement(); ps1 = con.createStatement(); rs = ps.executeQuery(querystring); rs1 = ps1.executeQuery( "select column_name, concat( column_name,':','[', data_type, column_key ,']' ), table_name from columns where table_schema = 'DBWAVEERP';"); ///////////////////////////////////////////// // Get the number of columns here. I need // this to add dynaminism to my table loader ///////////////////////////////////////////// ResultSetMetaData rsmd = rs.getMetaData(); setColCount(rsmd.getColumnCount()); /////////////////////////////////////////// // Load the column types to an array // Never access it directly, java simply // returns NULL, whew I don't know why /////////////////////////////////////////// String[] colTypes = new String[colCount]; for (int j = 0; j <= getColCount() - 1; j++) { colTypes[j] = rsmd.getColumnTypeName(j + 1); } /** * Initialize the working arrays here for the process * Added by Jammi Dee 06/06/2012 */ while (rs.next()) { String id = rs.getString(1); } rs.last(); int rowCount = rs.getRow(); while (rs1.next()) { String id = rs1.getString(1); } rs1.last(); int rowCount1 = rs1.getRow(); rowCount = rowCount + rowCount1; nodeid = new String[rowCount]; nodedesc = new String[rowCount]; nodeparent = new String[rowCount]; int ipoint = 0; rs.beforeFirst(); rs1.beforeFirst(); while (rs.next()) { nodeid[ipoint] = rs.getString(1); nodedesc[ipoint] = rs.getString(2); nodeparent[ipoint] = rs.getString(3); // Increment the pointer ipoint = ipoint + 1; } while (rs1.next()) { nodeid[ipoint] = rs1.getString(1); nodedesc[ipoint] = rs1.getString(2); nodeparent[ipoint] = rs1.getString(3); // Increment the pointer ipoint = ipoint + 1; } ps.close(); con.close(); } catch (Exception e) { System.out.println(e.getMessage()); e.printStackTrace(); return "FAILED"; } return "SUCCESS"; }
From source file:edu.education.ucsb.muster.MusterServlet.java
private String getOutputAsJson(String database, String query, long limit) throws SQLException { // The output string StringBuffer out = new StringBuffer(); // Cache StringBuffer length as needed int len;/* w w w. ja v a2s . c om*/ // Database operations DatabaseDefinition db = conf.getDatabase(database); // //register the driver registerDriver(db.driver, db.url); // // Connect to the database Connection connection = DriverManager.getConnection(db.url, db.username, db.password); // // Perform the query PreparedStatement statement = connection.prepareStatement(query); statement.execute(); ResultSet results = statement.getResultSet(); // Get and write the column names ResultSetMetaData meta = results.getMetaData(); int columnCount = meta.getColumnCount(); LinkedList<String> columns = new LinkedList<String>(); for (int i = 1; i < columnCount + 1; i++) { // We're only dealing with JSON, so the column names should be // JavaScript-friendly. columns.add(StringEscapeUtils.escapeJavaScript(meta.getColumnName(i))); } out.append("{\n \"columns\" : [ "); // Add column names in JSON format for (String column : columns) { out.append('"' + column + "\", "); } // remove the trailing ", " and add a line break and close the array len = out.length(); out.delete(len - 2, len); out.append(" ],\n"); // Add column values out.append(" \"results\" : [ \n"); for (int i = 0; i < limit && results.next(); i++) { out.append(rowAsJson(results, columns)); } // remove the trailing ", " len = out.length(); out.delete(len - 2, len); out.append("\n ]\n"); out.append("}"); return out.toString(); }
From source file:moe.yuna.palinuridae.core.BaseDao.java
public List<Map<String, Object>> select(final Selector selector) throws DBUtilException { return getJdbcTemplate().query(getDialect().select(selector), (stat) -> { for (int i = 0; i < selector.getValues().size(); i++) { stat.setObject(i + 1, selector.getValues().get(i)); }//from w ww . j av a 2 s . c o m }, (DBCallable<List<Map<String, Object>>>) (rs) -> { ResultSetMetaData md = rs.getMetaData(); List<Map<String, Object>> rlist = new ArrayList<>(); while (rs.next()) { Map<String, Object> map = new HashMap<String, Object>(); for (int i = 1; i <= md.getColumnCount(); i++) { // map.put(md.getColumnName(i), rs.getObject(i)); map.put(md.getColumnLabel(i), rs.getObject(i)); } rlist.add(map); } return rlist; }); }
From source file:moe.yuna.palinuridae.core.BaseDao.java
/** * @param sql// w ww. jav a2 s .c om * @param list * @return * @throws DBUtilException */ public List<Map<String, Object>> select(final String sql, final List<Object> list) throws DBUtilException { return getJdbcTemplate().query(sql, (stat) -> { for (int i = 0; i < list.size(); i++) { stat.setObject(i + 1, list.get(i)); } }, (DBCallable<List<Map<String, Object>>>) (rs) -> { ResultSetMetaData md = rs.getMetaData(); List<Map<String, Object>> rlist = new ArrayList<>(); while (rs.next()) { Map<String, Object> map = new HashMap<String, Object>(); for (int i = 1; i <= md.getColumnCount(); i++) { // map.put(md.getColumnName(i), rs.getObject(i)); map.put(md.getColumnLabel(i), rs.getObject(i)); } rlist.add(map); } return rlist; }); }
From source file:com.diversityarrays.dal.server.SqlDialog.java
public int doSqlQuery(ResultSet rs, List<String> headings, List<String[]> rows) throws SQLException { int nColumns = -1; while (rs.next()) { if (nColumns < 0) { ResultSetMetaData rsmd = rs.getMetaData(); nColumns = rsmd.getColumnCount(); for (int i = 1; i <= nColumns; ++i) { String hdg = rsmd.getColumnLabel(i); headings.add(hdg);/* ww w. ja v a2 s. com*/ } } String[] values = new String[nColumns]; rows.add(values); for (int i = 1; i <= nColumns; ++i) { values[i - 1] = rs.getString(i); } } return nColumns; }
From source file:com.cloudera.sqoop.manager.TestSqlManager.java
@Test public void testReadTable() { ResultSet results = null;/* ww w . ja v a 2 s . c om*/ try { results = manager.readTable(HsqldbTestServer.getTableName(), HsqldbTestServer.getFieldNames()); assertNotNull("ResultSet from readTable() is null!", results); ResultSetMetaData metaData = results.getMetaData(); assertNotNull("ResultSetMetadata is null in readTable()", metaData); // ensure that we get the correct number of columns back assertEquals("Number of returned columns was unexpected!", metaData.getColumnCount(), HsqldbTestServer.getFieldNames().length); // should get back 4 rows. They are: // 1 2 // 3 4 // 5 6 // 7 8 // .. so while order isn't guaranteed, we should get back 16 on the left // and 20 on the right. int sumCol1 = 0, sumCol2 = 0, rowCount = 0; while (results.next()) { rowCount++; sumCol1 += results.getInt(1); sumCol2 += results.getInt(2); } assertEquals("Expected 4 rows back", EXPECTED_NUM_ROWS, rowCount); assertEquals("Expected left sum of 16", EXPECTED_COL1_SUM, sumCol1); assertEquals("Expected right sum of 20", EXPECTED_COL2_SUM, sumCol2); } catch (SQLException sqlException) { fail("SQL Exception: " + sqlException.toString()); } finally { if (null != results) { try { results.close(); } catch (SQLException sqlE) { fail("SQL Exception in ResultSet.close(): " + sqlE.toString()); } } manager.release(); } }