List of usage examples for java.sql ResultSetMetaData getColumnLabel
String getColumnLabel(int column) throws SQLException;
From source file:gui.Trabajadores.java
public void cargaEdad() { try {/*ww w . j av a 2 s . c om*/ Database con = new Database(); DefaultTableModel modelo = new DefaultTableModel(); this.edad.setModel(modelo); Connection conn = con.conectar(); String sentencia = "select Num_Empleado,Fecha_Baja,Costo_Finiquito, Indemnizacion, Prima_Antiguedad, Gratificacion, Total_Pagado_Separacion, Motivo_Baja, Volver_Contratar, Pendiente from bajas_temp;"; PreparedStatement pst = conn.prepareStatement(sentencia); rs = pst.executeQuery(); ResultSetMetaData rsMd = rs.getMetaData(); //La cantidad de columnas que tiene la consulta int cantidadColumnas = rsMd.getColumnCount(); //Establecer como cabezeras el nombre de las colimnas for (int i = 1; i <= cantidadColumnas; i++) { modelo.addColumn(rsMd.getColumnLabel(i)); } //Creando las filas para el JTable while (rs.next()) { Object[] fila = new Object[cantidadColumnas]; for (int i = 0; i < cantidadColumnas; i++) { fila[i] = rs.getObject(i + 1); } modelo.addRow(fila); } con.CerrarConexion(); } catch (Exception ioe) { ioe.printStackTrace(); } }
From source file:gui.Trabajadores.java
public void cargaTurnoSin() { try {//from www. j av a2s . co m Database con = new Database(); DefaultTableModel modelo = new DefaultTableModel(); this.turnosin.setModel(modelo); Connection conn = con.conectar(); String sentencia = "select Num_Empleado,Fecha_Baja,Costo_Finiquito, Indemnizacion, Prima_Antiguedad, Gratificacion, Total_Pagado_Separacion, Motivo_Baja, Volver_Contratar, Pendiente from bajas_temp;"; PreparedStatement pst = conn.prepareStatement(sentencia); rs = pst.executeQuery(); ResultSetMetaData rsMd = rs.getMetaData(); //La cantidad de columnas que tiene la consulta int cantidadColumnas = rsMd.getColumnCount(); //Establecer como cabezeras el nombre de las colimnas for (int i = 1; i <= cantidadColumnas; i++) { modelo.addColumn(rsMd.getColumnLabel(i)); } //Creando las filas para el JTable while (rs.next()) { Object[] fila = new Object[cantidadColumnas]; for (int i = 0; i < cantidadColumnas; i++) { fila[i] = rs.getObject(i + 1); } modelo.addRow(fila); } con.CerrarConexion(); } catch (Exception ioe) { ioe.printStackTrace(); } }
From source file:gui.Trabajadores.java
public void cargaturnosControl() { try {// ww w. j av a 2 s .co m Database con = new Database(); DefaultTableModel modelo = new DefaultTableModel(); this.turnoCont.setModel(modelo); Connection conn = con.conectar(); String sentencia = "select Num_Empleado,Fecha_Baja,Costo_Finiquito, Indemnizacion, Prima_Antiguedad, Gratificacion, Total_Pagado_Separacion, Motivo_Baja, Volver_Contratar, Pendiente from bajas_temp;"; PreparedStatement pst = conn.prepareStatement(sentencia); rs = pst.executeQuery(); ResultSetMetaData rsMd = rs.getMetaData(); //La cantidad de columnas que tiene la consulta int cantidadColumnas = rsMd.getColumnCount(); //Establecer como cabezeras el nombre de las colimnas for (int i = 1; i <= cantidadColumnas; i++) { modelo.addColumn(rsMd.getColumnLabel(i)); } //Creando las filas para el JTable while (rs.next()) { Object[] fila = new Object[cantidadColumnas]; for (int i = 0; i < cantidadColumnas; i++) { fila[i] = rs.getObject(i + 1); } modelo.addRow(fila); } con.CerrarConexion(); } catch (Exception ioe) { ioe.printStackTrace(); } }
From source file:com.glaf.core.jdbc.QueryHelper.java
/** * @param conn//ww w .j a va 2s.c o m * ? * @param sqlExecutor * * @return */ @SuppressWarnings("unchecked") public List<Map<String, Object>> getResultList(Connection conn, SqlExecutor sqlExecutor) { if (!DBUtils.isLegalQuerySql(sqlExecutor.getSql())) { throw new RuntimeException(" SQL statement illegal "); } List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>(); PreparedStatement psmt = null; ResultSet rs = null; ResultSetMetaData rsmd = null; try { psmt = conn.prepareStatement(sqlExecutor.getSql()); if (sqlExecutor.getParameter() != null) { List<Object> values = (List<Object>) sqlExecutor.getParameter(); JdbcUtils.fillStatement(psmt, values); } rs = psmt.executeQuery(); if (conf.getBoolean("useMyBatisResultHandler", false)) { resultList = this.getResults(rs); } else { rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>(); for (int index = 1; index <= count; index++) { int sqlType = rsmd.getColumnType(index); ColumnDefinition column = new ColumnDefinition(); column.setIndex(index); column.setColumnName(rsmd.getColumnName(index)); column.setColumnLabel(rsmd.getColumnLabel(index)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(index)); column.setScale(rsmd.getScale(index)); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase())); columns.add(column); } int startIndex = 1; while (rs.next() && startIndex <= 50000) { int index = 0; startIndex++; Map<String, Object> rowMap = new HashMap<String, Object>(); Iterator<ColumnDefinition> iterator = columns.iterator(); while (iterator.hasNext()) { ColumnDefinition column = iterator.next(); String columnLabel = column.getColumnLabel(); String columnName = column.getColumnName(); if (StringUtils.isEmpty(columnName)) { columnName = column.getColumnLabel(); } columnName = columnName.toLowerCase(); String javaType = column.getJavaType(); index = index + 1; if ("String".equals(javaType)) { String value = rs.getString(column.getIndex()); if (value != null) { value = value.trim(); rowMap.put(columnName, value); rowMap.put(columnLabel, value); } } else if ("Integer".equals(javaType)) { try { Integer value = rs.getInt(column.getIndex()); rowMap.put(columnName, value); rowMap.put(columnLabel, value); } catch (Exception e) { String str = rs.getString(column.getIndex()); logger.error("integer:" + str); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); rowMap.put(columnName, num.intValue()); rowMap.put(columnLabel, rowMap.get(columnName)); logger.debug("?:" + num.intValue()); } } else if ("Long".equals(javaType)) { try { Long value = rs.getLong(column.getIndex()); rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } catch (Exception e) { String str = rs.getString(column.getIndex()); logger.error("long:" + str); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); rowMap.put(columnName, num.longValue()); rowMap.put(columnLabel, num.longValue()); logger.debug("?:" + num.longValue()); } } else if ("Double".equals(javaType)) { try { Double d = rs.getDouble(column.getIndex()); rowMap.put(columnName, d); rowMap.put(columnLabel, d); } catch (Exception e) { String str = rs.getString(column.getIndex()); logger.error("double:" + str); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); rowMap.put(columnName, num.doubleValue()); rowMap.put(columnLabel, num.doubleValue()); logger.debug("?:" + num.doubleValue()); } } else if ("Boolean".equals(javaType)) { rowMap.put(columnName, rs.getBoolean(column.getIndex())); rowMap.put(columnLabel, rowMap.get(columnName)); } else if ("Date".equals(javaType)) { rowMap.put(columnName, rs.getTimestamp(column.getIndex())); rowMap.put(columnLabel, rowMap.get(columnName)); } else if ("Blob".equals(javaType)) { // ignore } else { Object value = rs.getObject(column.getIndex()); if (value != null) { if (value instanceof String) { value = (String) value.toString().trim(); } rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } } } rowMap.put("startIndex", startIndex); resultList.add(rowMap); } } logger.debug(">resultList size=" + resultList.size()); return resultList; } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test public void testIntervalTypes() throws Exception { Statement stmt = con.createStatement(); // Since interval types not currently supported as table columns, need to create them // as expressions. ResultSet res = stmt/*from ww w. j a v a 2s . c o m*/ .executeQuery("select case when c17 is null then null else interval '1' year end as col1," + " c17 - c17 as col2 from " + dataTypeTableName + " order by col1"); ResultSetMetaData meta = res.getMetaData(); assertEquals("col1", meta.getColumnLabel(1)); assertEquals(java.sql.Types.OTHER, meta.getColumnType(1)); assertEquals("interval_year_month", meta.getColumnTypeName(1)); assertEquals(11, meta.getColumnDisplaySize(1)); assertEquals(11, meta.getPrecision(1)); assertEquals(0, meta.getScale(1)); assertEquals(HiveIntervalYearMonth.class.getName(), meta.getColumnClassName(1)); assertEquals("col2", meta.getColumnLabel(2)); assertEquals(java.sql.Types.OTHER, meta.getColumnType(2)); assertEquals("interval_day_time", meta.getColumnTypeName(2)); assertEquals(29, meta.getColumnDisplaySize(2)); assertEquals(29, meta.getPrecision(2)); assertEquals(0, meta.getScale(2)); assertEquals(HiveIntervalDayTime.class.getName(), meta.getColumnClassName(2)); // row 1 - results should be null assertTrue(res.next()); // skip the last (partitioning) column since it is always non-null for (int i = 1; i < meta.getColumnCount(); i++) { assertNull("Column " + i + " should be null", res.getObject(i)); } // row 2 - results should be null assertTrue(res.next()); for (int i = 1; i < meta.getColumnCount(); i++) { assertNull("Column " + i + " should be null", res.getObject(i)); } // row 3 assertTrue(res.next()); assertEquals("1-0", res.getString(1)); assertEquals(1, ((HiveIntervalYearMonth) res.getObject(1)).getYears()); assertEquals("0 00:00:00.000000000", res.getString(2)); assertEquals(0, ((HiveIntervalDayTime) res.getObject(2)).getDays()); }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
/** * validate schema generated by "set" command * @throws SQLException/* w w w . j a va 2 s .co m*/ */ @Test public void testSetCommand() throws SQLException { // execute set command String sql = "set -v"; Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery(sql); // Validate resultset columns ResultSetMetaData md = res.getMetaData(); assertEquals(1, md.getColumnCount()); assertEquals(SET_COLUMN_NAME, md.getColumnLabel(1)); //check if there is data in the resultset assertTrue("Nothing returned by set -v", res.next()); res.close(); stmt.close(); }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
/** * Verify selecting using builtin UDFs//from w ww .j av a 2 s . c o m * @throws SQLException */ @Test public void testBuiltInUDFCol() throws SQLException { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery("select c12, bin(c12) from " + dataTypeTableName + " where c1=1"); ResultSetMetaData md = res.getMetaData(); assertEquals(md.getColumnCount(), 2); // only one result column assertEquals(md.getColumnLabel(2), "c1"); // verify the system generated column name assertTrue(res.next()); assertEquals(res.getLong(1), 1); assertEquals(res.getString(2), "1"); res.close(); }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
/** * Verify selecting named expression columns * @throws SQLException//from w w w .j a v a 2s . c o m */ @Test public void testExprCol() throws SQLException { Statement stmt = con.createStatement(); ResultSet res = stmt .executeQuery("select c1+1 as col1, length(c4) as len from " + dataTypeTableName + " where c1=1"); ResultSetMetaData md = res.getMetaData(); assertEquals(md.getColumnCount(), 2); // only one result column assertEquals(md.getColumnLabel(1), "col1"); // verify the column name assertEquals(md.getColumnLabel(2), "len"); // verify the column name assertTrue(res.next()); assertEquals(res.getInt(1), 2); assertEquals(res.getInt(2), 1); res.close(); }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
/** * verify 'explain ...' resultset/* w ww .j a v a 2 s .c om*/ * @throws SQLException */ @Test public void testExplainStmt() throws SQLException { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery("explain select c1, c2, c3, c4, c5 as a, c6, c7, c8, c9, c10, c11, c12, " + "c1*2, sentences(null, null, null) as b, c23 from " + dataTypeTableName + " limit 1"); ResultSetMetaData md = res.getMetaData(); // only one result column assertEquals(md.getColumnCount(), 1); // verify the column name assertEquals(md.getColumnLabel(1), EXPL_COLUMN_NAME); //verify that there is data in the resultset assertTrue("Nothing returned explain", res.next()); }
From source file:org.jfree.data.jdbc.JDBCXYDataset.java
/** * ExecuteQuery will attempt execute the query passed to it against the * provided database connection. If connection is null then no action is * taken.//from w w w . j a v a 2s .com * * The results from the query are extracted and cached locally, thus * applying an upper limit on how many rows can be retrieved successfully. * * @param query the query to be executed. * @param con the connection the query is to be executed against. * * @throws SQLException if there is a problem executing the query. */ public void executeQuery(Connection con, String query) throws SQLException { if (con == null) { throw new SQLException("There is no database to execute the query."); } ResultSet resultSet = null; Statement statement = null; try { statement = con.createStatement(); resultSet = statement.executeQuery(query); ResultSetMetaData metaData = resultSet.getMetaData(); int numberOfColumns = metaData.getColumnCount(); int numberOfValidColumns = 0; int[] columnTypes = new int[numberOfColumns]; for (int column = 0; column < numberOfColumns; column++) { try { int type = metaData.getColumnType(column + 1); switch (type) { case Types.NUMERIC: case Types.REAL: case Types.INTEGER: case Types.DOUBLE: case Types.FLOAT: case Types.DECIMAL: case Types.BIT: case Types.DATE: case Types.TIME: case Types.TIMESTAMP: case Types.BIGINT: case Types.SMALLINT: ++numberOfValidColumns; columnTypes[column] = type; break; default: columnTypes[column] = Types.NULL; break; } } catch (SQLException e) { columnTypes[column] = Types.NULL; throw e; } } if (numberOfValidColumns <= 1) { throw new SQLException("Not enough valid columns where generated by query."); } /// First column is X data this.columnNames = new String[numberOfValidColumns - 1]; /// Get the column names and cache them. int currentColumn = 0; for (int column = 1; column < numberOfColumns; column++) { if (columnTypes[column] != Types.NULL) { this.columnNames[currentColumn] = metaData.getColumnLabel(column + 1); ++currentColumn; } } // Might need to add, to free memory from any previous result sets if (this.rows != null) { for (int column = 0; column < this.rows.size(); column++) { ArrayList row = (ArrayList) this.rows.get(column); row.clear(); } this.rows.clear(); } // Are we working with a time series. switch (columnTypes[0]) { case Types.DATE: case Types.TIME: case Types.TIMESTAMP: this.isTimeSeries = true; break; default: this.isTimeSeries = false; break; } // Get all rows. // rows = new ArrayList(); while (resultSet.next()) { ArrayList newRow = new ArrayList(); for (int column = 0; column < numberOfColumns; column++) { Object xObject = resultSet.getObject(column + 1); switch (columnTypes[column]) { case Types.NUMERIC: case Types.REAL: case Types.INTEGER: case Types.DOUBLE: case Types.FLOAT: case Types.DECIMAL: case Types.BIGINT: case Types.SMALLINT: newRow.add(xObject); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: newRow.add(new Long(((Date) xObject).getTime())); break; case Types.NULL: break; default: System.err.println("Unknown data"); columnTypes[column] = Types.NULL; break; } } this.rows.add(newRow); } /// a kludge to make everything work when no rows returned if (this.rows.size() == 0) { ArrayList newRow = new ArrayList(); for (int column = 0; column < numberOfColumns; column++) { if (columnTypes[column] != Types.NULL) { newRow.add(new Integer(0)); } } this.rows.add(newRow); } /// Determine max and min values. if (this.rows.size() < 1) { this.maxValue = 0.0; this.minValue = 0.0; } else { ArrayList row = (ArrayList) this.rows.get(0); this.maxValue = Double.NEGATIVE_INFINITY; this.minValue = Double.POSITIVE_INFINITY; for (int rowNum = 0; rowNum < this.rows.size(); ++rowNum) { row = (ArrayList) this.rows.get(rowNum); for (int column = 1; column < numberOfColumns; column++) { Object testValue = row.get(column); if (testValue != null) { double test = ((Number) testValue).doubleValue(); if (test < this.minValue) { this.minValue = test; } if (test > this.maxValue) { this.maxValue = test; } } } } } fireDatasetChanged(new DatasetChangeInfo()); //TODO: fill in real change info } finally { if (resultSet != null) { try { resultSet.close(); } catch (Exception e) { // TODO: is this a good idea? } } if (statement != null) { try { statement.close(); } catch (Exception e) { // TODO: is this a good idea? } } } }