List of usage examples for java.sql ResultSet getMetaData
ResultSetMetaData getMetaData() throws SQLException;
ResultSet object's columns. From source file:fll.web.developer.QueryHandler.java
@SuppressFBWarnings(value = {
"SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE" }, justification = "Executing query from user")
@Override//from w w w .ja v a 2 s. c o m
protected void processRequest(final HttpServletRequest request, final HttpServletResponse response,
final ServletContext application, final HttpSession session) throws IOException, ServletException {
final List<String> columnNames = new LinkedList<String>();
final List<Map<String, String>> data = new LinkedList<Map<String, String>>();
String error = null;
DataSource datasource = ApplicationAttributes.getDataSource(application);
Statement stmt = null;
ResultSet rs = null;
Connection connection = null;
try {
connection = datasource.getConnection();
final String query = request.getParameter(QUERY_PARAMETER);
stmt = connection.createStatement();
rs = stmt.executeQuery(query);
ResultSetMetaData meta = rs.getMetaData();
for (int columnNum = 1; columnNum <= meta.getColumnCount(); ++columnNum) {
columnNames.add(meta.getColumnName(columnNum).toLowerCase());
}
while (rs.next()) {
final Map<String, String> row = new HashMap<String, String>();
for (final String columnName : columnNames) {
final String value = rs.getString(columnName);
row.put(columnName, value);
}
data.add(row);
}
} catch (final SQLException e) {
error = e.getMessage();
LOGGER.error("Exception doing developer query", e);
} finally {
SQLFunctions.close(rs);
SQLFunctions.close(stmt);
SQLFunctions.close(connection);
}
response.setContentType("application/json");
response.setCharacterEncoding(Utilities.DEFAULT_CHARSET.name());
final ResultData result = new ResultData(columnNames, data, error);
final ObjectMapper jsonMapper = new ObjectMapper();
final Writer writer = response.getWriter();
jsonMapper.writeValue(writer, result);
}
From source file:teambootje.A3.java
/** * Creates new form A3// w w w. j a va 2 s . c o m */ public A3() { initComponents(); setLocationRelativeTo(null); setLayout(new BorderLayout()); //Create and set up the window. setTitle("SS Rotterdam Analyse || Analyse 3"); ImageIcon icon = new ImageIcon("img/bootje.jpg"); setIconImage(icon.getImage()); // back BTN JButton back = new JButton("Back"); add(back, BorderLayout.NORTH); back.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { dispose(); // throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. } }); // panel en Label JPanel ana = new JPanel(); add(ana, BorderLayout.CENTER); //tabel String sql = "SELECT locatie.land, locatie.stad, COUNT(posts.PID) AS Aantal FROM persoon, locatie, posts WHERE persoon.LID = locatie.LID AND persoon.AID = posts.AID GROUP BY locatie.land ORDER BY count(posts.PID)"; List<Object[]> list = new ArrayList<Object[]>(); ResultSet rs = null; try { rs = db.runSql(sql); while (rs.next()) { String land = rs.getString("locatie.land"); String stad = rs.getString("locatie.stad"); int aantal = rs.getInt("Aantal"); String[] row = new String[rs.getMetaData().getColumnCount()]; for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) { row[i - 1] = rs.getString(i); } list.add(row); //chart JButton chart = new JButton("Chart"); add(chart, BorderLayout.SOUTH); chart.addActionListener(new ActionListener() { String l1 = land; String s1 = stad; int a1 = aantal; @Override public void actionPerformed(ActionEvent e) { DefaultPieDataset pieDataset = new DefaultPieDataset(); pieDataset.setValue(s1, a1); JFreeChart chart = ChartFactory.createPieChart3D("Aantal Posts per locatie", pieDataset, true, true, true); PiePlot3D p = (PiePlot3D) chart.getPlot(); //p.setForegroundAlpha(TOP_ALIGNMENT); ChartFrame pie = new ChartFrame("Aantal Posts per locatie", chart); pie.setVisible(true); pie.setSize(500, 500); pie.setLocationRelativeTo(null); // throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. } }); } } catch (SQLException e) { JOptionPane.showMessageDialog(null, e); } Object[][] array = new Object[list.size()][]; Object columnNames[] = { "Land", "Stad", "Aantal" }; list.toArray(array); JTable table = new JTable(array, columnNames); JScrollPane scroll = new JScrollPane(table); scroll.setPreferredSize(new Dimension(400, 400)); ana.add(scroll); }
From source file:Database.Handler.java
@SuppressWarnings("unchecked") private List<T> mapRersultSetToObject(ResultSet rs, Class outputClass) { List<T> outputList = null; try {//from w w w. j a va 2 s . com if (rs != null) { if (outputClass.isAnnotationPresent(Entity.class)) { ResultSetMetaData rsmd = rs.getMetaData(); Field[] fields = outputClass.getDeclaredFields(); while (rs.next()) { T bean = (T) outputClass.newInstance(); //System.out.println("rsmd = "+rsmd.getColumnCount()); for (int i = 1; i <= rsmd.getColumnCount(); i++) { String columnName = rsmd.getColumnName(i); Object columnValue = rs.getObject(i); for (Field field : fields) { if (field.isAnnotationPresent(Column.class)) { Column column = field.getAnnotation(Column.class); if (column.name().equalsIgnoreCase(columnName) && columnValue != null) { //System.out.println(field.getName() + "=====>" + columnValue); BeanUtils.setProperty(bean, field.getName(), columnValue); break; } } } } if (outputList == null) { outputList = new ArrayList<T>(); } outputList.add(bean); } } else { // throw some error System.out.println("output class is not annotationPresented"); } } else { return null; } } catch (SQLException ex) { Logger.getLogger(Handler.class.getName()).log(Level.SEVERE, null, ex); } catch (SecurityException ex) { Logger.getLogger(Handler.class.getName()).log(Level.SEVERE, null, ex); } catch (InstantiationException ex) { Logger.getLogger(Handler.class.getName()).log(Level.SEVERE, null, ex); } catch (IllegalAccessException ex) { Logger.getLogger(Handler.class.getName()).log(Level.SEVERE, null, ex); } catch (InvocationTargetException ex) { Logger.getLogger(Handler.class.getName()).log(Level.SEVERE, null, ex); } return outputList; }
From source file:teambootje.A1.java
public A1() { initComponents();//from w w w .j a v a2s . c o m setLocationRelativeTo(null); setLayout(new BorderLayout()); setSize(500, 500); //Create and set up the window. setTitle("SS Rotterdam Analyse || Analyse 1"); ImageIcon icon = new ImageIcon("img/bootje.jpg"); setIconImage(icon.getImage()); // back BTN JButton back = new JButton("Back"); add(back, BorderLayout.NORTH); back.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { dispose(); //throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. } }); // panel JPanel ana = new JPanel(); add(ana, BorderLayout.CENTER); //tabel String nvt = "SELECT Geslacht, COUNT(*) AS Aantal FROM persoon GROUP BY geslacht"; String male = "SELECT Geslacht AS male, COUNT(*) AS Aantal_Male FROM persoon WHERE Geslacht = 'man'"; String Female = "SELECT Geslacht AS female, COUNT(*) AS Aantal_Female FROM persoon WHERE Geslacht = 'vrouw'"; List<Object[]> list = new ArrayList<Object[]>(); ResultSet rs = null; try { rs = db.runSql(nvt); while (rs.next()) { String geslacht = rs.getString("Geslacht"); int aantal = rs.getInt("Aantal"); String[] row = new String[rs.getMetaData().getColumnCount()]; for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) { row[i - 1] = rs.getString(i); } list.add(row); try { rs = db.runSql(male); while (rs.next()) { String man = rs.getString("male"); int am = rs.getInt("Aantal_Male"); String[] row1 = new String[rs.getMetaData().getColumnCount()]; for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) { row1[i - 1] = rs.getString(i); } try { rs = db.runSql(Female); while (rs.next()) { String vrouw = rs.getString("female"); int af = rs.getInt("Aantal_Female"); String[] row2 = new String[rs.getMetaData().getColumnCount()]; for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) { row2[i - 1] = rs.getString(i); } Object[][] array = new Object[list.size()][]; Object columnNames[] = { "Geslacht", "Aantal" }; list.toArray(array); JTable table = new JTable(array, columnNames); JScrollPane scroll = new JScrollPane(table); scroll.setPreferredSize(new Dimension(400, 400)); ana.add(scroll); //chart JButton chart = new JButton("Chart"); add(chart, BorderLayout.SOUTH); chart.addActionListener(new ActionListener() { String g1 = geslacht; String m = man; String v = vrouw; int a1 = aantal; int a2 = am; int a3 = af; @Override public void actionPerformed(ActionEvent e) { DefaultPieDataset pieDataset = new DefaultPieDataset(); pieDataset.setValue("Niet vrij gegeven", a1); pieDataset.setValue("Man", a2); pieDataset.setValue("vrouw", a3); JFreeChart chart = ChartFactory.createPieChart3D("Aantal mannen en vrouwen", pieDataset, true, true, true); PiePlot3D p = (PiePlot3D) chart.getPlot(); //p.setForegroundAlpha(TOP_ALIGNMENT); ChartFrame pie = new ChartFrame("Aantal mannen en vrouwen", chart); pie.setVisible(true); pie.setSize(500, 500); pie.setLocationRelativeTo(null); //throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. } }); } } catch (SQLException v) { JOptionPane.showMessageDialog(null, v); } } } catch (SQLException m) { JOptionPane.showMessageDialog(null, m); } } } catch (SQLException e) { JOptionPane.showMessageDialog(null, e); } }
From source file:com.jbrisbin.vpc.jobsched.sql.SqlMessageHandler.java
public SqlMessage handleMessage(final SqlMessage msg) throws Exception { log.debug("handling message: " + msg.toString()); DataSource ds = appCtx.getBean(msg.getDatasource(), DataSource.class); JdbcTemplate tmpl = new JdbcTemplate(ds); String sql = msg.getSql();//from w ww. j av a2 s . c om CallableStatementCreator stmtCreator = null; CallableStatementCallback<SqlMessage> callback = null; if (sql.startsWith("plugin:")) { // Use a plugin to get the sql String pluginName = (sql.contains("?") ? sql.substring(7, sql.indexOf('?')) : sql.substring(7)); final Plugin plugin = groovyPluginManager.getPlugin(pluginName); Map<String, Object> vars = new LinkedHashMap<String, Object>(); vars.put("message", msg); vars.put("datasource", ds); vars.put("listen", groovyClosureFactory.createListenClosure(msg)); vars.put("mapreduce", groovyClosureFactory.createMapReduceClosure(msg)); plugin.setContext(vars); // Execute this plugin plugin.run(); Object o = plugin.get("sql"); if (null != o && o instanceof Closure) { sql = ((Closure) o).call(msg).toString(); } else if (o instanceof String || o instanceof GString) { sql = o.toString(); } else { throw new IllegalStateException("Can't convert " + String.valueOf(o) + " to SQL statement."); } msg.setSql(sql); o = plugin.get("statementCreator"); if (null != o && o instanceof Closure) { stmtCreator = new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { Object obj = ((Closure) plugin.get("statementCreator")).call(new Object[] { con, msg }); log.debug("from plugin statementCreator: " + String.valueOf(obj)); return (CallableStatement) obj; } }; } else { throw new IllegalStateException("Can't convert " + String.valueOf(o) + " to CallableStatementCreator. Define a closure named 'statementCreator' in your plugin."); } o = plugin.get("callback"); if (null != o && o instanceof Closure) { callback = new CallableStatementCallback<SqlMessage>() { public SqlMessage doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { Object obj = ((Closure) plugin.get("callback")).call(new Object[] { cs, msg }); log.debug("from plugin callback: " + String.valueOf(obj)); return (SqlMessage) obj; } }; } else { throw new IllegalStateException("Can't convert " + String.valueOf(o) + " to CallableStatementCallback. Define a closure named 'callback' in your plugin."); } } else { stmtCreator = new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection connection) throws SQLException { CallableStatement stmt = connection.prepareCall(msg.getSql()); List<Object> params = msg.getParams(); if (null != params) { int index = 1; for (Object obj : params) { stmt.setObject(index++, obj); } } return stmt; } }; callback = new CallableStatementCallback<SqlMessage>() { public SqlMessage doInCallableStatement(CallableStatement callableStatement) throws SQLException, DataAccessException { if (null == msg.getResults().getData()) { msg.getResults().setData(new ArrayList<List<Object>>()); } if (callableStatement.execute()) { ResultSet results = callableStatement.getResultSet(); // Pull out column names ResultSetMetaData meta = results.getMetaData(); String[] columns = new String[meta.getColumnCount()]; for (int i = 1; i <= meta.getColumnCount(); i++) { columns[i - 1] = meta.getColumnName(i); } msg.getResults().getColumnNames().addAll(Arrays.asList(columns)); int total = 0; while (results.next()) { List<Object> row = new ArrayList<Object>(columns.length); for (int i = 1; i <= columns.length; i++) { row.add(results.getObject(i)); } msg.getResults().getData().add(row); total++; } msg.getResults().setTotalRows(total); } else { msg.getResults().getColumnNames().add("updateCount"); msg.getResults().setTotalRows(1); List<Object> updCnt = new ArrayList<Object>(1); updCnt.add(callableStatement.getUpdateCount()); msg.getResults().getData().add(updCnt); } return msg; } }; } try { tmpl.setExceptionTranslator(appCtx.getBean(SQLExceptionTranslator.class)); } catch (NoSuchBeanDefinitionException notfound) { // IGNORED } if (null != stmtCreator && null != callback) { try { tmpl.execute(stmtCreator, callback); } catch (Throwable t) { log.error(t.getMessage(), t); List<String> errors = new ArrayList<String>(); errors.add(t.getMessage()); Throwable cause = t.getCause(); if (null != cause) { do { errors.add(cause.getMessage()); } while (null != (cause = cause.getCause())); } msg.getResults().setErrors(errors); } } else { log.warn("CallableStatementCreator and/or CallableStatementCallback where empty. " + "Make sure your plugin provides these under 'statementCreator' and 'callback' respectively."); } return msg; }
From source file:net.orpiske.ssps.common.db.MultiRsHandler.java
@Override protected T handleRow(ResultSet rs) throws SQLException { T dto;//from w w w . j a va2 s .co m try { dto = clazz.newInstance(); } catch (InstantiationException e1) { throw new SQLException("Unable to instantiate DTO class: " + e1.getMessage(), e1); } catch (IllegalAccessException e1) { throw new SQLException("Illegal to instantiate DTO class: " + e1.getMessage(), e1); } ResultSetMetaData meta = rs.getMetaData(); for (int i = 1; i <= meta.getColumnCount(); i++) { Object value = rs.getObject(i); String name = meta.getColumnName(i); try { /* * We convert the column name to a more appropriate and java like name * because some columns are usually named as some_thing whereas Java * properties are named someThing. This call does this conversion. */ String javaProperty = NameConverter.sqlToProperty(name); PropertyUtils.setSimpleProperty(dto, javaProperty, value); } catch (Exception e) { throw new SQLException("Unable to set property " + name + " for bean" + dto.getClass(), e); } } return dto; }
From source file:be.bittich.dynaorm.repository.GenericDynaRepository.java
/** * Configuration//from w w w. j ava 2 s.co m */ private void configure() { runner = getQueryRunner(); dialect = getDialect(); // default tableName TableFromDB table = AnnotationProcessor.getAnnotationType(clazz, TableFromDB.class); String tableName = clazz.getSimpleName().toLowerCase(); if (table != null && !isEmpty(table.tableName())) { tableName = table.tableName(); } tableColumn = new TableColumn(tableName); rowProcessor = new DynaRowProcessor(tableColumn); try { ResultSet rs = runner.getDataSource().getConnection() .prepareStatement(dialect.requestForTableColumns(tableName)).executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); Integer nbColumns = metaData.getColumnCount(); for (int i = 1; i <= nbColumns; i++) { String name = metaData.getColumnName(i); int type = metaData.getColumnType(i); tableColumn.addColumn(name, type); } } catch (SQLException ex) { LOG.log(Level.SEVERE, null, ex); } }
From source file:com.cloudera.sqoop.manager.TestSqlManager.java
@Test public void testReadTable() { ResultSet results = null; try {//from www . j av a 2 s. com 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(); } }
From source file:org.tradex.jdbc.JDBCHelper.java
/** * Issues a named parameter query using numerical binds, starting at 0. * @param sql The SQL//from ww w . j a va2 s . c om * @param binds The bind values * @return an Object array of the results */ public Object[][] templateQuery(CharSequence sql, Object... binds) { NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(ds); final List<Object[]> results = template.query(sql.toString(), getBinds(sql.toString().trim().toUpperCase(), binds), new RowMapper<Object[]>() { int columnCount = -1; @Override public Object[] mapRow(ResultSet rs, int rowNum) throws SQLException { if (columnCount == -1) columnCount = rs.getMetaData().getColumnCount(); Object[] row = new Object[columnCount]; for (int i = 0; i < columnCount; i++) { row[i] = rs.getObject(i + 1); } return row; } }); Object[][] ret = new Object[results.size()][]; int cnt = 0; for (Object[] arr : results) { ret[cnt] = arr; cnt++; } return ret; }
From source file:RawSQLServlet.java
/** Do the SQL query */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String query = request.getParameter("sql"); response.setContentType("text/html"); PrintWriter out = response.getWriter(); if (query == null) { out.println("<b>Error: malformed query, contact administrator</b>"); return;//from www.j a v a 2 s . c om } // NB MUST also check for admin privs before proceding! try { // SQL out.println("<p>Your query: <b>" + query + "</b></p>"); stmt.execute(query); ResultSet rs = stmt.getResultSet(); if (rs == null) { // print updatecount out.println("<p>Result: updateCount = <b>" + stmt.getUpdateCount() + "</p>"); } else { // process resultset out.println("<br>Your response:"); ResultSetMetaData md = rs.getMetaData(); int count = md.getColumnCount(); out.println("<table border=1>"); out.print("<tr>"); for (int i = 1; i <= count; i++) { out.print("<th>"); out.print(md.getColumnName(i)); } out.println("</tr>"); while (rs.next()) { out.print("<tr>"); for (int i = 1; i <= count; i++) { out.print("<td>"); out.print(rs.getString(i)); } out.println("</tr>"); } } out.println("</table>"); // rs.close(); } catch (SQLException ex) { out.print("<B>" + getClass() + ": SQL Error:</B>\n" + ex); out.print("<pre>"); ex.printStackTrace(out); out.print("</pre>"); } }