List of usage examples for java.sql ResultSetMetaData getColumnLabel
String getColumnLabel(int column) throws SQLException;
From source file:com.zimbra.cs.db.JdbcClient.java
private void runSql(Connection conn, String sql) { Matcher m = PAT_SELECT.matcher(sql); if (m.find()) { // Run query and display results try {/*from ww w . jav a 2 s.c o m*/ Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); ResultSetMetaData md = rs.getMetaData(); int colCount = md.getColumnCount(); List<Object[]> firstRows = new ArrayList<Object[]>(); int rowCount = 0; // Set initial column widths based on column labels int[] colWidths = new int[colCount]; if (mShowColumnNames) { for (int i = 0; i < colCount; i++) { String name = md.getColumnLabel(i + 1); if (name.length() > colWidths[i]) { colWidths[i] = name.length(); } } } // Read first 1000 rows first to calculate column widths for printing while (rowCount < 1000 && rs.next()) { Object[] row = getCurrentRow(rs); for (int i = 0; i < colCount; i++) { Object o = row[i]; int width = (o == null) ? NULL.length() : (o.toString()).length(); if (width > colWidths[i]) { colWidths[i] = width; } } firstRows.add(row); rowCount++; } // Print first rows if (!mBatch && mShowColumnNames) { // Skip if we're in batch mode. If not displaying column names, don't // print the first divider. printDivider(colWidths); } if (mShowColumnNames) { String[] colNames = new String[colCount]; for (int i = 0; i < colCount; i++) { colNames[i] = md.getColumnLabel(i + 1); } printRow(colNames, colWidths); } if (!mBatch) { printDivider(colWidths); } for (Object[] row : firstRows) { printRow(row, colWidths); } // Print any remaining rows while (rs.next()) { Object[] row = getCurrentRow(rs); printRow(row, colWidths); } if (!mBatch) { printDivider(colWidths); } rs.close(); stmt.close(); } catch (SQLException e) { e.printStackTrace(); System.err.println(e.getMessage()); } } else { // Run statement try { Statement stmt = conn.createStatement(); int numRows = stmt.executeUpdate(sql); stmt.close(); System.out.println("Updated " + numRows + " rows"); } catch (SQLException e) { System.err.println(e.getMessage()); } } }
From source file:org.josso.gateway.identity.service.store.db.IdentityDAO.java
/** * Builds an array of credentials based on a ResultSet * Column names are used to build a credential. *///from w ww. j av a 2 s. c o m protected Credential[] fetchCredentials(ResultSet rs) throws SQLException, IOException, SSOAuthenticationException { List creds = new ArrayList(); while (rs.next()) { ResultSetMetaData md = rs.getMetaData(); // Each column is a credential, the column name is used as credential name ... for (int i = 1; i <= md.getColumnCount(); i++) { String cName = md.getColumnLabel(i); Object credentialObject = rs.getObject(i); String credentialValue = null; // if the attribute value is an array, cast it to byte[] and then convert to // String using proper encoding if (credentialObject.getClass().isArray()) { try { // Try to create a UTF-8 String, we use java.nio to handle errors in a better way. // If the byte[] cannot be converted to UTF-8, we're using the credentialObject as is. byte[] credentialData = (byte[]) credentialObject; ByteBuffer in = ByteBuffer.allocate(credentialData.length); in.put(credentialData); in.flip(); Charset charset = Charset.forName("UTF-8"); CharsetDecoder decoder = charset.newDecoder(); CharBuffer charBuffer = decoder.decode(in); credentialValue = charBuffer.toString(); } catch (CharacterCodingException e) { if (logger.isDebugEnabled()) logger.debug("Can't convert credential value to String using UTF-8"); } } else if (credentialObject instanceof String) { // The credential value must be a String ... credentialValue = (String) credentialObject; } Credential c = null; if (credentialValue != null) { c = _cp.newCredential(cName, credentialValue); } else { c = _cp.newCredential(cName, credentialObject); } if (c != null) { creds.add(c); } } } return (Credential[]) creds.toArray(new Credential[creds.size()]); }
From source file:com.gdcn.modules.db.jdbc.processor.CamelBeanProcessor.java
/** * The positions in the returned array represent column numbers. The * values stored at each position represent the index in the * <code>PropertyDescriptor[]</code> for the bean property that matches * the column name. If no bean property was found for a column, the * position is set to <code>PROPERTY_NOT_FOUND</code>. * * @param rsmd The <code>ResultSetMetaData</code> containing column * information./*www .j a va 2 s. c om*/ * * @param props The bean property descriptors. * * @throws SQLException if a database access error occurs * * @return An int[] with column index to property index mappings. The 0th * element is meaningless because JDBC column indexing starts at 1. */ protected int[] mapColumnsToProperties(ResultSetMetaData rsmd, PropertyDescriptor[] props) throws SQLException { int cols = rsmd.getColumnCount(); int[] columnToProperty = new int[cols + 1]; Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND); for (int col = 1; col <= cols; col++) { String columnName = rsmd.getColumnLabel(col); if (null == columnName || 0 == columnName.length()) { columnName = rsmd.getColumnName(col); } columnName = columnName.toLowerCase(); String propertyName = columnToPropertyOverrides.get(columnName); if (propertyName == null) { propertyName = EncodeUtils.underline2camel(columnName);//? } for (int i = 0; i < props.length; i++) { String prop = props[i].getName(); if (propertyName.equalsIgnoreCase(prop)) { columnToProperty[col] = i; break; } } } return columnToProperty; }
From source file:jeeves.resources.dbms.Dbms.java
private Element buildResponse(ResultSet rs, Hashtable<String, String> formats) throws SQLException { ResultSetMetaData md = rs.getMetaData(); int colNum = md.getColumnCount(); // --- retrieve name and type of fields Vector<String> vHeaders = new Vector<String>(); Vector<Integer> vTypes = new Vector<Integer>(); for (int i = 0; i < colNum; i++) { vHeaders.add(md.getColumnLabel(i + 1).toLowerCase()); vTypes.add(new Integer(md.getColumnType(i + 1))); }//from w w w . j a v a2 s. com // --- build the jdom tree Element root = new Element(Jeeves.Elem.RESPONSE); while (rs.next()) { Element record = new Element(Jeeves.Elem.RECORD); for (int i = 0; i < colNum; i++) { String name = vHeaders.get(i).toString(); int type = ((Integer) vTypes.get(i)).intValue(); record.addContent(buildElement(rs, i, name, type, formats)); } root.addContent(record); } return root; }
From source file:org.sonar.db.AbstractDbTester.java
@CheckForNull private Integer getColumnIndex(ResultSet res, String column) { try {//from w ww .j av a2s . c o m ResultSetMetaData meta = res.getMetaData(); int numCol = meta.getColumnCount(); for (int i = 1; i < numCol + 1; i++) { if (meta.getColumnLabel(i).toLowerCase().equals(column.toLowerCase())) { return i; } } return null; } catch (Exception e) { throw new IllegalStateException("Fail to get column index"); } }
From source file:servlet.CinemaControl.java
protected void doListMSsOfCinemas(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = response.getWriter(); HttpSession session = request.getSession(); ServletContext sc = getServletContext(); String db_driver = sc.getInitParameter("db_driver"), db_url = sc.getInitParameter("db_url"), db_user = sc.getInitParameter("db_user"), db_password = sc.getInitParameter("db_password"); String db_q_cinemas = "SELECT * FROM Cinema;"; String db_q_houses = "SELECT * FROM House WHERE cinemaID = ?;"; String db_q_movieSessions = "SELECT * FROM MovieSession WHERE houseID = ?;"; String db_q_movies = "SELECT * FROM Movie WHERE movieID = ?;"; String db_q1_0 = "SELECT categoryName" + " FROM category c, movie_category mc, movie m" + " WHERE c.categoryID = mc.categoryID" + " AND m.movieID = mc.movieID" + " AND m.movieID = ?;"; String db_q1_1 = "SELECT actorName" + " FROM actor a, movie_actor ma, movie m" + " WHERE a.actorID = ma.actorID" + " AND ma.movieID = m.movieID" + " AND m.movieID = ?;"; String db_q1_2 = "SELECT image FROM movie m, movieImage mi" + " WHERE m.movieID = mi.movieID" + " AND m.movieID = ?;"; String db_q1_3 = "SELECT video" + " FROM movie m, movieVideo mv" + " WHERE m.movieID = mv.movieID" + " AND m.movieID = ?;"; try {//from w w w. j a v a 2s .co m JSONObject jso0 = new JSONObject(); JSONArray jsa0 = new JSONArray(); jso0.put("cinemas", jsa0); Class.forName(db_driver); Connection conn = DriverManager.getConnection(db_url, db_user, db_password); Statement statmt1 = conn.createStatement(); ResultSet rs1 = statmt1.executeQuery(db_q_cinemas); ResultSetMetaData rsmd1 = rs1.getMetaData(); int numOfColumns1 = rsmd1.getColumnCount(); while (rs1.next()) { JSONObject jso1 = new JSONObject(); jsa0.put(jso1); for (int i = 1; i <= numOfColumns1; i++) { jso1.put(rsmd1.getColumnLabel(i), rs1.getString(i)); } int cinemaID = Integer.parseInt(jso1.getString("cinemaID")); JSONArray jsa1 = new JSONArray(); jso1.put("houses", jsa1); PreparedStatement statmt2 = conn.prepareStatement(db_q_houses); statmt2.setInt(1, cinemaID); if (statmt2.execute()) { ResultSet rs2 = statmt2.getResultSet(); ResultSetMetaData rsmd2 = rs2.getMetaData(); int numOfColumns2 = rsmd2.getColumnCount(); while (rs2.next()) { JSONObject jso2 = new JSONObject(); jsa1.put(jso2); for (int j = 1; j <= numOfColumns2; j++) { jso2.put(rsmd2.getColumnLabel(j), rs2.getString(j)); } int houseID = Integer.parseInt(jso2.getString("houseID")); JSONArray jsa2 = new JSONArray(); jso2.put("movieSessions", jsa2); PreparedStatement statmt3 = conn.prepareStatement(db_q_movieSessions); statmt3.setInt(1, houseID); if (statmt3.execute()) { ResultSet rs3 = statmt3.getResultSet(); ResultSetMetaData rsmd3 = rs3.getMetaData(); int numOfColumns3 = rsmd3.getColumnCount(); while (rs3.next()) { JSONObject jso3 = new JSONObject(); jsa2.put(jso3); for (int k = 1; k <= numOfColumns3; k++) { jso3.put(rsmd3.getColumnLabel(k), rs3.getString(k)); } String playtime = jso3.getString("playtime"); jso3.put("date", playtime.substring(0, 10)); jso3.put("time", playtime.subSequence(11, 16)); int movieID = Integer.parseInt(jso3.getString("movieID")); JSONArray jsa3 = new JSONArray(); jso3.put("movies", jsa3); PreparedStatement statmt4 = conn.prepareStatement(db_q_movies); statmt4.setInt(1, movieID); if (statmt4.execute()) { ResultSet rs4 = statmt4.getResultSet(); ResultSetMetaData rsmd4 = rs4.getMetaData(); int numOfColumns4 = rsmd4.getColumnCount(); while (rs4.next()) { JSONObject jso4 = new JSONObject(); jsa3.put(jso4); for (int l = 1; l <= numOfColumns4; l++) { jso4.put(rsmd4.getColumnLabel(l), rs4.getString(l)); } //add categories JSONArray jsa1_0 = new JSONArray(); jso4.put("categories", jsa1_0); PreparedStatement statmt1_0 = conn.prepareStatement(db_q1_0); statmt1_0.setInt(1, movieID); if (statmt1_0.execute()) { ResultSet rs1_0 = statmt1_0.getResultSet(); while (rs1_0.next()) { jsa1_0.put(rs1_0.getString(1)); } } //add actors JSONArray jsa1_1 = new JSONArray(); jso4.put("actors", jsa1_1); PreparedStatement statmt1_1 = conn.prepareStatement(db_q1_1); statmt1_1.setInt(1, movieID); if (statmt1_1.execute()) { ResultSet rs1_1 = statmt1_1.getResultSet(); while (rs1_1.next()) { jsa1_1.put(rs1_1.getString(1)); } } //add images JSONArray jsa1_2 = new JSONArray(); jso4.put("movieImages", jsa1_2); PreparedStatement statmt1_2 = conn.prepareStatement(db_q1_2); statmt1_2.setInt(1, movieID); if (statmt1_2.execute()) { ResultSet rs1_2 = statmt1_2.getResultSet(); while (rs1_2.next()) { jsa1_2.put(rs1_2.getString(1)); } } //add videos JSONArray jsa1_3 = new JSONArray(); jso4.put("movieVideos", jsa1_3); PreparedStatement statmt1_3 = conn.prepareStatement(db_q1_3); statmt1_3.setInt(1, movieID); if (statmt1_3.execute()) { ResultSet rs1_3 = statmt1_3.getResultSet(); while (rs1_3.next()) { jsa1_3.put(rs1_3.getString(1)); } } } } } } } } } out.println(jso0.toString()); } catch (ClassNotFoundException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } catch (JSONException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:org.apache.kylin.query.adhoc.PushDownRunnerJdbcImpl.java
@Override public void executeQuery(String query, List<List<String>> results, List<SelectedColumnMeta> columnMetas) throws Exception { Statement statement = null;//from ww w . j a v a 2 s . c o m Connection connection = this.getConnection(); ResultSet resultSet = null; //extract column metadata ResultSetMetaData metaData = null; int columnCount = 0; try { statement = connection.createStatement(); resultSet = statement.executeQuery(query); extractResults(resultSet, results); metaData = resultSet.getMetaData(); columnCount = metaData.getColumnCount(); // fill in selected column meta for (int i = 1; i <= columnCount; ++i) { columnMetas.add(new SelectedColumnMeta(metaData.isAutoIncrement(i), metaData.isCaseSensitive(i), false, metaData.isCurrency(i), metaData.isNullable(i), false, metaData.getColumnDisplaySize(i), metaData.getColumnLabel(i), metaData.getColumnName(i), null, null, null, metaData.getPrecision(i), metaData.getScale(i), metaData.getColumnType(i), metaData.getColumnTypeName(i), metaData.isReadOnly(i), false, false)); } } catch (SQLException sqlException) { throw sqlException; } finally { DBUtils.closeQuietly(resultSet); DBUtils.closeQuietly(statement); closeConnection(connection); } }
From source file:org.dspace.storage.rdbms.DatabaseManager.java
/** * Return the names of all the columns of the ResultSet. * //from w ww . j av a2s . c o m * @param meta * The ResultSetMetaData * @return The names of all the columns of the given table, as a List. Each * element of the list is a String. * @exception SQLException * If a database error occurs */ static List<String> getColumnNames(ResultSetMetaData meta) throws SQLException { List<String> results = new ArrayList<String>(); int columns = meta.getColumnCount(); for (int i = 0; i < columns; i++) { results.add(meta.getColumnLabel(i + 1)); } return results; }
From source file:org.sonar.db.AbstractDbTester.java
private Set<String> getColumnNames(ResultSet res) { try {/* w ww .j a v a 2s . co m*/ Set<String> columnNames = new HashSet<>(); ResultSetMetaData meta = res.getMetaData(); int numCol = meta.getColumnCount(); for (int i = 1; i < numCol + 1; i++) { columnNames.add(meta.getColumnLabel(i).toLowerCase()); } return columnNames; } catch (Exception e) { throw new IllegalStateException("Fail to get column names"); } }
From source file:com.gmail.sretof.db.jdbc.processor.CamelBeanProcessor.java
/** * The positions in the returned array represent column numbers. The values * stored at each position represent the index in the * <code>PropertyDescriptor[]</code> for the bean property that matches the * column name. If no bean property was found for a column, the position is * set to <code>PROPERTY_NOT_FOUND</code>. * //from ww w . ja v a 2 s . co m * @param rsmd * The <code>ResultSetMetaData</code> containing column * information. * * @param props * The bean property descriptors. * * @throws SQLException * if a database access error occurs * * @return An int[] with column index to property index mappings. The 0th * element is meaningless because JDBC column indexing starts at 1. */ protected int[] mapColumnsToProperties(ResultSetMetaData rsmd, PropertyDescriptor[] props) throws SQLException { int cols = rsmd.getColumnCount(); int[] columnToProperty = new int[cols + 1]; Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND); for (int col = 1; col <= cols; col++) { String columnName = rsmd.getColumnLabel(col); if (null == columnName || 0 == columnName.length()) { columnName = rsmd.getColumnName(col); } columnName = columnName.toLowerCase(); String propertyName = columnToPropertyOverrides.get(columnName); if (propertyName == null) { propertyName = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, columnName);// ? } for (int i = 0; i < props.length; i++) { String prop = props[i].getName(); if (propertyName.equalsIgnoreCase(prop)) { columnToProperty[col] = i; break; } } } return columnToProperty; }