List of usage examples for java.sql ResultSetMetaData getColumnName
String getColumnName(int column) throws SQLException;
From source file:org.apache.hive.hplsql.Exec.java
/** * Trace values retrived from the database *///from w ww . j a v a2 s.c o m public void trace(ParserRuleContext ctx, Var var, ResultSet rs, ResultSetMetaData rm, int idx) throws SQLException { if (var.type != Var.Type.ROW) { trace(ctx, "COLUMN: " + rm.getColumnName(idx) + ", " + rm.getColumnTypeName(idx)); trace(ctx, "SET " + var.getName() + " = " + var.toString()); } else { Row row = (Row) var.value; int cnt = row.size(); for (int j = 1; j <= cnt; j++) { Var v = row.getValue(j - 1); trace(ctx, "COLUMN: " + rm.getColumnName(j) + ", " + rm.getColumnTypeName(j)); trace(ctx, "SET " + v.getName() + " = " + v.toString()); } } }
From source file:com.runwaysdk.dataaccess.database.general.PostgreSQL.java
/** * /*from www . j ava 2 s .c o m*/ * @see com.runwaysdk.dataaccess.AbstractDatabase#getColumnNames(java.lang.String) */ @Override public List<String> getColumnNames(String table) { Connection conx = Database.getConnection(); Statement statement = null; ResultSet resultSet = null; LinkedList<String> attributeList = new LinkedList<String>(); try { String sqlStmt = "SELECT * FROM " + table + " WHERE 1=0"; statement = conx.createStatement(); resultSet = statement.executeQuery(sqlStmt); ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) { attributeList.add((resultSetMetaData.getColumnName(i)).toLowerCase()); } conx.commit(); return attributeList; } catch (SQLException ex) { this.throwDatabaseException(ex); return null; } finally { try { if (resultSet != null) resultSet.close(); if (statement != null) statement.close(); Database.closeConnection(conx); } catch (SQLException e) { this.throwDatabaseException(e); } } }
From source file:com.glaf.core.jdbc.QueryHelper.java
/** * @param conn/*from ww w . j a v a 2 s.c o m*/ * ? * @param sqlExecutor * ? * @param start * 0 * @param pageSize * ? * @return */ @SuppressWarnings("unchecked") public List<Map<String, Object>> getResultList(Connection conn, SqlExecutor sqlExecutor, int start, int pageSize) { if (!DBUtils.isLegalQuerySql(sqlExecutor.getSql())) { throw new RuntimeException(" SQL statement illegal "); } List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>(); String sql = sqlExecutor.getSql(); PreparedStatement psmt = null; ResultSet rs = null; ResultSetMetaData rsmd = null; boolean supportsPhysicalPage = false; try { Dialect dialect = DBConfiguration.getDatabaseDialect(conn); if (dialect != null && dialect.supportsPhysicalPage()) { supportsPhysicalPage = true; sql = dialect.getLimitString(sql, start, pageSize); logger.debug("sql=" + sqlExecutor.getSql()); logger.debug(">>sql=" + sql); } psmt = conn.prepareStatement(sql); if (sqlExecutor.getParameter() != null) { List<Object> values = (List<Object>) sqlExecutor.getParameter(); JdbcUtils.fillStatement(psmt, values); logger.debug(">>values=" + 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 i = 1; i <= count; i++) { int sqlType = rsmd.getColumnType(i); ColumnDefinition column = new ColumnDefinition(); column.setIndex(i); column.setColumnName(rsmd.getColumnName(i)); column.setColumnLabel(rsmd.getColumnLabel(i)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(i)); column.setScale(rsmd.getScale(i)); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase())); columns.add(column); } if (!supportsPhysicalPage) { logger.debug("---------------------skipRows:" + start); this.skipRows(rs, start, pageSize); } logger.debug("---------------------columns:" + columns.size()); logger.debug("---------------------start:" + start); logger.debug("---------------------pageSize:" + pageSize); // int index = 0; while (rs.next()) { // index++; // logger.debug("---------------------row index:" + index); 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(); if ("String".equals(javaType)) { String value = rs.getString(column.getIndex()); if (value != null) { value = value.trim(); rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } } else if ("Integer".equals(javaType)) { try { Integer value = rs.getInt(column.getIndex()); rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } 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, rowMap.get(columnName)); logger.debug("?:" + num.longValue()); } } else if ("Double".equals(javaType)) { try { Double d = rs.getDouble(column.getIndex()); rowMap.put(columnName, d); rowMap.put(columnLabel, rowMap.get(columnName)); } 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, rowMap.get(columnName)); 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)) { } 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)); } } } 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:com.glaf.core.jdbc.QueryHelper.java
/** * @param conn/* ww w . ja va 2 s .c o m*/ * ? * @param start * 0 * @param pageSize * ? * @param sql * ? * @param paramMap * ? * @return */ @SuppressWarnings("unchecked") public ResultModel getResultList(Connection conn, String sql, Map<String, Object> paramMap, int start, int pageSize) { if (!DBUtils.isLegalQuerySql(sql)) { throw new RuntimeException(" SQL statement illegal "); } ResultModel resultModel = new ResultModel(); boolean supportsPhysicalPage = false; PreparedStatement psmt = null; ResultSetMetaData rsmd = null; ResultSet rs = null; Dialect dialect = null; try { dialect = DBConfiguration.getDatabaseDialect(conn); if (dialect != null && dialect.supportsPhysicalPage()) { logger.debug("sql=" + sql); supportsPhysicalPage = dialect.supportsPhysicalPage(); sql = dialect.getLimitString(sql, start, pageSize); logger.debug(">>sql=" + sql); } List<Object> values = null; if (paramMap != null) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); sql = sqlExecutor.getSql(); values = (List<Object>) sqlExecutor.getParameter(); } logger.debug("sql:\n" + sql); logger.debug("values:" + values); psmt = conn.prepareStatement(sql); if (values != null && !values.isEmpty()) { JdbcUtils.fillStatement(psmt, values); } List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>(); rs = psmt.executeQuery(); rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); for (int i = 1; i <= count; i++) { int sqlType = rsmd.getColumnType(i); ColumnDefinition column = new ColumnDefinition(); column.setIndex(i); column.setColumnName(rsmd.getColumnName(i)); column.setColumnLabel(rsmd.getColumnLabel(i)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(i)); column.setScale(rsmd.getScale(i)); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } column.setName(StringTools.lower(StringTools.camelStyle(column.getColumnLabel()))); columns.add(column); } resultModel.setHeaders(columns); if (!supportsPhysicalPage) { this.skipRows(rs, start); } int k = 0; while (rs.next() && k++ < pageSize) { int index = 0; RowModel rowModel = new RowModel(); Iterator<ColumnDefinition> iterator = columns.iterator(); while (iterator.hasNext()) { ColumnDefinition column = iterator.next(); ColumnDefinition c = new ColumnDefinition(); c.setColumnName(column.getColumnName()); c.setColumnLabel(column.getColumnLabel()); c.setName(column.getName()); c.setJavaType(column.getJavaType()); c.setPrecision(column.getPrecision()); c.setScale(column.getScale()); String javaType = column.getJavaType(); index = index + 1; if ("String".equals(javaType)) { String value = rs.getString(column.getIndex()); c.setValue(value); } else if ("Integer".equals(javaType)) { try { Integer value = rs.getInt(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.intValue()); } } else if ("Long".equals(javaType)) { try { Long value = rs.getLong(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.longValue()); } } else if ("Double".equals(javaType)) { try { Double value = rs.getDouble(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.doubleValue()); } } else if ("Boolean".equals(javaType)) { Boolean value = rs.getBoolean(column.getIndex()); c.setValue(value); } else if ("Date".equals(javaType)) { Timestamp value = rs.getTimestamp(column.getIndex()); c.setValue(value); } else { c.setValue(rs.getObject(column.getIndex())); } rowModel.addColumn(c); } resultModel.addRow(rowModel); } } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } return resultModel; }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test public void testResultSetMetaData() throws SQLException { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery("select c1, c2, c3, c4, c5 as a, c6, c7, c8, c9, c10, c11, c12, " + "c1*2, sentences(null, null, null) as b, c17, c18, c20, c21, c22, c23 from " + dataTypeTableName + " limit 1"); ResultSetMetaData meta = res.getMetaData(); ResultSet colRS = con.getMetaData().getColumns(null, null, dataTypeTableName.toLowerCase(), null); assertEquals(20, meta.getColumnCount()); assertTrue(colRS.next());//from w ww .j a v a 2 s .com assertEquals("c1", meta.getColumnName(1)); assertEquals(Types.INTEGER, meta.getColumnType(1)); assertEquals("int", meta.getColumnTypeName(1)); assertEquals(11, meta.getColumnDisplaySize(1)); assertEquals(10, meta.getPrecision(1)); assertEquals(0, meta.getScale(1)); assertEquals("c1", colRS.getString("COLUMN_NAME")); assertEquals(Types.INTEGER, colRS.getInt("DATA_TYPE")); assertEquals("int", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(1), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(1), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c2", meta.getColumnName(2)); assertEquals("boolean", meta.getColumnTypeName(2)); assertEquals(Types.BOOLEAN, meta.getColumnType(2)); assertEquals(1, meta.getColumnDisplaySize(2)); assertEquals(1, meta.getPrecision(2)); assertEquals(0, meta.getScale(2)); assertEquals("c2", colRS.getString("COLUMN_NAME")); assertEquals(Types.BOOLEAN, colRS.getInt("DATA_TYPE")); assertEquals("boolean", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getScale(2), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c3", meta.getColumnName(3)); assertEquals(Types.DOUBLE, meta.getColumnType(3)); assertEquals("double", meta.getColumnTypeName(3)); assertEquals(25, meta.getColumnDisplaySize(3)); assertEquals(15, meta.getPrecision(3)); assertEquals(15, meta.getScale(3)); assertEquals("c3", colRS.getString("COLUMN_NAME")); assertEquals(Types.DOUBLE, colRS.getInt("DATA_TYPE")); assertEquals("double", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(3), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(3), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c4", meta.getColumnName(4)); assertEquals(Types.VARCHAR, meta.getColumnType(4)); assertEquals("string", meta.getColumnTypeName(4)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(4)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(4)); assertEquals(0, meta.getScale(4)); assertEquals("c4", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("string", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(4), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(4), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("a", meta.getColumnName(5)); assertEquals(Types.ARRAY, meta.getColumnType(5)); assertEquals("array", meta.getColumnTypeName(5)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(5)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(5)); assertEquals(0, meta.getScale(5)); assertEquals("c5", colRS.getString("COLUMN_NAME")); assertEquals(Types.ARRAY, colRS.getInt("DATA_TYPE")); assertEquals("array<int>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c6", meta.getColumnName(6)); assertEquals(Types.JAVA_OBJECT, meta.getColumnType(6)); assertEquals("map", meta.getColumnTypeName(6)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(6)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(6)); assertEquals(0, meta.getScale(6)); assertEquals("c6", colRS.getString("COLUMN_NAME")); assertEquals(Types.JAVA_OBJECT, colRS.getInt("DATA_TYPE")); assertEquals("map<int,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c7", meta.getColumnName(7)); assertEquals(Types.JAVA_OBJECT, meta.getColumnType(7)); assertEquals("map", meta.getColumnTypeName(7)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(7)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(7)); assertEquals(0, meta.getScale(7)); assertEquals("c7", colRS.getString("COLUMN_NAME")); assertEquals(Types.JAVA_OBJECT, colRS.getInt("DATA_TYPE")); assertEquals("map<string,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c8", meta.getColumnName(8)); assertEquals(Types.STRUCT, meta.getColumnType(8)); assertEquals("struct", meta.getColumnTypeName(8)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(8)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(8)); assertEquals(0, meta.getScale(8)); assertEquals("c8", colRS.getString("COLUMN_NAME")); assertEquals(Types.STRUCT, colRS.getInt("DATA_TYPE")); assertEquals("struct<r:string,s:int,t:double>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c9", meta.getColumnName(9)); assertEquals(Types.TINYINT, meta.getColumnType(9)); assertEquals("tinyint", meta.getColumnTypeName(9)); assertEquals(4, meta.getColumnDisplaySize(9)); assertEquals(3, meta.getPrecision(9)); assertEquals(0, meta.getScale(9)); assertEquals("c9", colRS.getString("COLUMN_NAME")); assertEquals(Types.TINYINT, colRS.getInt("DATA_TYPE")); assertEquals("tinyint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(9), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(9), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c10", meta.getColumnName(10)); assertEquals(Types.SMALLINT, meta.getColumnType(10)); assertEquals("smallint", meta.getColumnTypeName(10)); assertEquals(6, meta.getColumnDisplaySize(10)); assertEquals(5, meta.getPrecision(10)); assertEquals(0, meta.getScale(10)); assertEquals("c10", colRS.getString("COLUMN_NAME")); assertEquals(Types.SMALLINT, colRS.getInt("DATA_TYPE")); assertEquals("smallint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(10), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(10), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c11", meta.getColumnName(11)); assertEquals(Types.FLOAT, meta.getColumnType(11)); assertEquals("float", meta.getColumnTypeName(11)); assertEquals(24, meta.getColumnDisplaySize(11)); assertEquals(7, meta.getPrecision(11)); assertEquals(7, meta.getScale(11)); assertEquals("c11", colRS.getString("COLUMN_NAME")); assertEquals(Types.FLOAT, colRS.getInt("DATA_TYPE")); assertEquals("float", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(11), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(11), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c12", meta.getColumnName(12)); assertEquals(Types.BIGINT, meta.getColumnType(12)); assertEquals("bigint", meta.getColumnTypeName(12)); assertEquals(20, meta.getColumnDisplaySize(12)); assertEquals(19, meta.getPrecision(12)); assertEquals(0, meta.getScale(12)); assertEquals("c12", colRS.getString("COLUMN_NAME")); assertEquals(Types.BIGINT, colRS.getInt("DATA_TYPE")); assertEquals("bigint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(12), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(12), colRS.getInt("DECIMAL_DIGITS")); assertEquals("_c12", meta.getColumnName(13)); assertEquals(Types.INTEGER, meta.getColumnType(13)); assertEquals("int", meta.getColumnTypeName(13)); assertEquals(11, meta.getColumnDisplaySize(13)); assertEquals(10, meta.getPrecision(13)); assertEquals(0, meta.getScale(13)); assertEquals("b", meta.getColumnName(14)); assertEquals(Types.ARRAY, meta.getColumnType(14)); assertEquals("array", meta.getColumnTypeName(14)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(14)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(14)); assertEquals(0, meta.getScale(14)); // Move the result of getColumns() forward to match the columns of the query assertTrue(colRS.next()); // c13 assertTrue(colRS.next()); // c14 assertTrue(colRS.next()); // c15 assertTrue(colRS.next()); // c16 assertTrue(colRS.next()); // c17 assertEquals("c17", meta.getColumnName(15)); assertEquals(Types.TIMESTAMP, meta.getColumnType(15)); assertEquals("timestamp", meta.getColumnTypeName(15)); assertEquals(29, meta.getColumnDisplaySize(15)); assertEquals(29, meta.getPrecision(15)); assertEquals(9, meta.getScale(15)); assertEquals("c17", colRS.getString("COLUMN_NAME")); assertEquals(Types.TIMESTAMP, colRS.getInt("DATA_TYPE")); assertEquals("timestamp", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(15), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(15), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c18", meta.getColumnName(16)); assertEquals(Types.DECIMAL, meta.getColumnType(16)); assertEquals("decimal", meta.getColumnTypeName(16)); assertEquals(18, meta.getColumnDisplaySize(16)); assertEquals(16, meta.getPrecision(16)); assertEquals(7, meta.getScale(16)); assertEquals("c18", colRS.getString("COLUMN_NAME")); assertEquals(Types.DECIMAL, colRS.getInt("DATA_TYPE")); assertEquals("decimal", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(16), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(16), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); // skip c19, since not selected by query assertTrue(colRS.next()); assertEquals("c20", meta.getColumnName(17)); assertEquals(Types.DATE, meta.getColumnType(17)); assertEquals("date", meta.getColumnTypeName(17)); assertEquals(10, meta.getColumnDisplaySize(17)); assertEquals(10, meta.getPrecision(17)); assertEquals(0, meta.getScale(17)); assertEquals("c20", colRS.getString("COLUMN_NAME")); assertEquals(Types.DATE, colRS.getInt("DATA_TYPE")); assertEquals("date", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(17), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(17), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c21", meta.getColumnName(18)); assertEquals(Types.VARCHAR, meta.getColumnType(18)); assertEquals("varchar", meta.getColumnTypeName(18)); // varchar columns should have correct display size/precision assertEquals(20, meta.getColumnDisplaySize(18)); assertEquals(20, meta.getPrecision(18)); assertEquals(0, meta.getScale(18)); assertEquals("c21", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("varchar", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(18), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(18), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c22", meta.getColumnName(19)); assertEquals(Types.CHAR, meta.getColumnType(19)); assertEquals("char", meta.getColumnTypeName(19)); // char columns should have correct display size/precision assertEquals(15, meta.getColumnDisplaySize(19)); assertEquals(15, meta.getPrecision(19)); assertEquals(0, meta.getScale(19)); assertEquals("c23", meta.getColumnName(20)); assertEquals(Types.BINARY, meta.getColumnType(20)); assertEquals("binary", meta.getColumnTypeName(20)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(20)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(20)); assertEquals(0, meta.getScale(20)); assertEquals("c22", colRS.getString("COLUMN_NAME")); assertEquals(Types.CHAR, colRS.getInt("DATA_TYPE")); assertEquals("char", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(19), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(19), colRS.getInt("DECIMAL_DIGITS")); for (int i = 1; i <= meta.getColumnCount(); i++) { assertFalse(meta.isAutoIncrement(i)); assertFalse(meta.isCurrency(i)); assertEquals(ResultSetMetaData.columnNullable, meta.isNullable(i)); } }
From source file:ProcessRequest.java
public JSONArray parseQueryResults(ResultSet rs, String table) throws SQLException, JSONException { JSONArray resultJSONArray = new JSONArray(); ResultSetMetaData rsmd = rs.getMetaData(); int columns = rsmd.getColumnCount(); while (rs.next()) { JSONObject result = new JSONObject(); JSONObject resultMeta = new JSONObject(); resultMeta.put("table", table); result.put("metadata", resultMeta); for (int i = 1; i <= columns; i++) { //out.println("<td>"+rs.getString(i)+"</td>"); int type = rsmd.getColumnType(i); //result.put(rsmd.getColumnName(i), rs.get) switch (type) { case Types.BIT: result.put(rsmd.getColumnName(i), rs.getBoolean(i)); break; case Types.TINYINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.SMALLINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.INTEGER: //System.out.println(rsmd.getColumnName(i) + " type: "+type); result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.BIGINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.FLOAT: result.put(rsmd.getColumnName(i), rs.getFloat(i)); break; case Types.REAL: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.DOUBLE: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.NUMERIC: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.DECIMAL: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.CHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.VARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.LONGVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.DATE: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; }/* w w w . java 2s . c o m*/ case Types.TIME: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; } case Types.TIMESTAMP: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; } case Types.BINARY: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.VARBINARY: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.LONGVARBINARY: result.put(rsmd.getColumnName(i), rs.getLong(i)); break; case Types.NULL: result.put(rsmd.getColumnName(i), ""); break; case Types.BOOLEAN: result.put(rsmd.getColumnName(i), rs.getBoolean(i)); break; case Types.ROWID: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.NCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.NVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.LONGNVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.SQLXML: case Types.NCLOB: case Types.DATALINK: case Types.REF: case Types.OTHER: case Types.JAVA_OBJECT: case Types.DISTINCT: case Types.STRUCT: case Types.ARRAY: case Types.BLOB: case Types.CLOB: default: result.put(rsmd.getColumnName(i), rs.getString(i)); break; } } //if(table.equals("Ticket")) //System.out.println(result.toString(5)); resultJSONArray.put(result); } return resultJSONArray; }
From source file:com.commander4j.thread.AutoLabellerThread.java
public void run() { logger.debug("AutoLabeller Thread running"); setSessionID(JUnique.getUniqueID()); JDBUser user = new JDBUser(getHostID(), getSessionID()); user.setUserId("interface"); user.setPassword("interface"); user.setLoginPassword("interface"); Common.userList.addUser(getSessionID(), user); Common.sd.setData(getSessionID(), "silentExceptions", "Yes", true); Boolean dbconnected = false;// w w w . jav a 2 s.c o m if (Common.hostList.getHost(hostID).isConnected(sessionID) == false) { dbconnected = Common.hostList.getHost(hostID).connect(sessionID, hostID); } else { dbconnected = true; } if (dbconnected) { JDBViewAutoLabellerPrinter alp = new JDBViewAutoLabellerPrinter(getHostID(), getSessionID()); LinkedList<JDBViewAutoLabellerPrinter> autolabellerList = new LinkedList<JDBViewAutoLabellerPrinter>(); int noOfMessages = 0; while (true) { JWait.milliSec(500); if (allDone) { if (dbconnected) { Common.hostList.getHost(hostID).disconnect(getSessionID()); } return; } autolabellerList.clear(); autolabellerList = alp.getModifiedPrinterLines(); noOfMessages = autolabellerList.size(); if (noOfMessages > 0) { for (int x = 0; x < noOfMessages; x++) { JWait.milliSec(100); JDBViewAutoLabellerPrinter autolabview = autolabellerList.get(x); messageProcessedOK = true; messageError = ""; if (autolabview.getPrinterObj().isEnabled()) { logger.debug("Line =" + autolabview.getAutoLabellerObj().getLine()); logger.debug("Line Description =" + autolabview.getAutoLabellerObj().getDescription()); logger.debug("Printer ID =" + autolabview.getPrinterObj().getPrinterID()); logger.debug("Printer Enabled =" + autolabview.getPrinterObj().isEnabled()); logger.debug("Export Path =" + autolabview.getPrinterObj().getExportRealPath()); logger.debug("Export Enabled =" + autolabview.getPrinterObj().isExportEnabled()); logger.debug("Export Format =" + autolabview.getPrinterObj().getExportFormat()); logger.debug("Direct Print =" + autolabview.getPrinterObj().isDirectPrintEnabled()); logger.debug("Printer Type =" + autolabview.getPrinterObj().getPrinterType()); logger.debug("Printer IP =" + autolabview.getPrinterObj().getIPAddress()); logger.debug("Printer Port =" + autolabview.getPrinterObj().getPort()); logger.debug("Process Order =" + autolabview.getLabelDataObj().getProcessOrder()); logger.debug("Material =" + autolabview.getLabelDataObj().getMaterial()); logger.debug("Module ID =" + autolabview.getModuleObj().getModuleId()); logger.debug("Module Type =" + autolabview.getModuleObj().getType()); if (autolabview.getPrinterObj().isExportEnabled()) { String exportPath = JUtility.replaceNullStringwithBlank( JUtility.formatPath(autolabview.getPrinterObj().getExportRealPath())); if (exportPath.equals("") == false) { if (exportPath.substring(exportPath.length() - 1) .equals(File.separator) == false) { exportPath = exportPath + File.separator; } } else { exportPath = Common.interface_output_path + "Auto Labeller" + File.separator; } String exportFilename = exportPath + JUtility.removePathSeparators(autolabview.getAutoLabellerObj().getLine()) + "_" + JUtility.removePathSeparators(autolabview.getPrinterObj().getPrinterID()) + "." + autolabview.getPrinterObj().getExportFormat(); String exportFilenameTemp = exportFilename + ".out"; logger.debug("Export Filename =" + exportFilename); /* ================CSV================ */ if (autolabview.getPrinterObj().getExportFormat().equals("CSV")) { try { PreparedStatement stmt = null; ResultSet rs; String labelType = autolabview.getLabelDataObj().getLabelType(); stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID()) .prepareStatement( Common.hostList.getHost(getHostID()).getSqlstatements() .getSQL("DBVIEW_AUTO_LABELLER_PRINTER.getProperties" + "_" + labelType)); stmt.setString(1, autolabview.getAutoLabellerObj().getLine()); stmt.setString(2, autolabview.getPrinterObj().getPrinterID()); stmt.setFetchSize(50); rs = stmt.executeQuery(); logger.debug("Writing CSV"); CSVWriter writer = new CSVWriter(new FileWriter(exportFilenameTemp), CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_QUOTE_CHARACTER, CSVWriter.DEFAULT_ESCAPE_CHARACTER, CSVWriter.DEFAULT_LINE_END); writer.writeAll(rs, true); rs.close(); stmt.close(); writer.close(); File fromFile = new File(exportFilenameTemp); File toFile = new File(exportFilename); FileUtils.deleteQuietly(toFile); FileUtils.moveFile(fromFile, toFile); fromFile = null; toFile = null; } catch (Exception e) { messageProcessedOK = false; messageError = e.getMessage(); } } /* ================XML================ */ if (autolabview.getPrinterObj().getExportFormat().equals("XML")) { try { PreparedStatement stmt = null; ResultSet rs; stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID()) .prepareStatement(Common.hostList.getHost(getHostID()) .getSqlstatements() .getSQL("DBVIEW_AUTO_LABELLER_PRINTER.getProperties")); stmt.setString(1, autolabview.getAutoLabellerObj().getLine()); stmt.setString(2, autolabview.getPrinterObj().getPrinterID()); stmt.setFetchSize(50); rs = stmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); int colCount = rsmd.getColumnCount(); DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); Document document = builder.newDocument(); Element message = (Element) document.createElement("message"); Element hostUniqueID = addElement(document, "hostRef", Common.hostList.getHost(getHostID()).getUniqueID()); message.appendChild(hostUniqueID); Element messageRef = addElement(document, "messageRef", autolabview.getAutoLabellerObj().getUniqueID()); message.appendChild(messageRef); Element messageType = addElement(document, "interfaceType", "Auto Labeller Data"); message.appendChild(messageType); Element messageInformation = addElement(document, "messageInformation", "Unique ID=" + autolabview.getAutoLabellerObj().getUniqueID()); message.appendChild(messageInformation); Element messageDirection = addElement(document, "interfaceDirection", "Output"); message.appendChild(messageDirection); Element messageDate = addElement(document, "messageDate", JUtility.getISOTimeStampStringFormat(JUtility.getSQLDateTime())); message.appendChild(messageDate); if (rs.first()) { Element labelData = (Element) document.createElement("LabelData"); Element row = document.createElement("Row"); labelData.appendChild(row); for (int i = 1; i <= colCount; i++) { String columnName = rsmd.getColumnName(i); Object value = rs.getObject(i); Element node = document.createElement(columnName); node.appendChild(document.createTextNode(value.toString())); row.appendChild(node); } message.appendChild(labelData); document.appendChild(message); JXMLDocument xmld = new JXMLDocument(); xmld.setDocument(document); // =============================== DOMImplementationLS DOMiLS = null; FileOutputStream FOS = null; // testing the support for DOM // Load and Save if ((document.getFeature("Core", "3.0") != null) && (document.getFeature("LS", "3.0") != null)) { DOMiLS = (DOMImplementationLS) (document.getImplementation()) .getFeature("LS", "3.0"); // get a LSOutput object LSOutput LSO = DOMiLS.createLSOutput(); FOS = new FileOutputStream(exportFilename); LSO.setByteStream((OutputStream) FOS); // get a LSSerializer object LSSerializer LSS = DOMiLS.createLSSerializer(); // do the serialization LSS.write(document, LSO); FOS.close(); } // =============================== } rs.close(); stmt.close(); } catch (Exception e) { messageError = e.getMessage(); } } if (autolabview.getPrinterObj().getExportFormat().equals("LQF")) { } } if (autolabview.getPrinterObj().isDirectPrintEnabled()) { } } if (messageProcessedOK == true) { autolabview.getAutoLabellerObj().setModified(false); autolabview.getAutoLabellerObj().update(); } else { logger.debug(messageError); } autolabview = null; } } } } }
From source file:edu.ku.brc.specify.conversion.ConvertVerifier.java
/** * @param oldNewIdStr//from w w w . ja v a 2 s . com * @param newColInx * @param oldColInx * @return * @throws SQLException */ private StatusType compareDates(final String oldNewIdStr, final int newColInx, final int oldColInx) throws SQLException { PartialDateConv datePair = new PartialDateConv(); Object newObj = newDBRS.getObject(newColInx); Object oldObj = oldDBRS.getObject(oldColInx); ResultSetMetaData newRsmd = newDBRS.getMetaData(); ResultSetMetaData oldRsmd = oldDBRS.getMetaData(); String newColName = newRsmd.getColumnName(newColInx); String oldColName = oldRsmd.getColumnName(oldColInx); if (newObj == null) { String clsName = newRsmd.getColumnClassName(newColInx); if (compareTo6DBs) { if (!clsName.equals("java.sql.Date") || oldObj != null) { String msg = "New Value was null and shouldn't have been for Key Value New Field [" + newColName + "] [" + oldObj + "]"; log.error(msg); tblWriter.logErrors(newColName, msg); return StatusType.NEW_VAL_NULL; } } else if (oldObj != null) { if (oldObj instanceof Number && ((Number) oldObj).intValue() == 0) { return StatusType.COMPARE_OK; } else if (!clsName.equals("java.sql.Date") || (!(oldObj instanceof String) && ((Number) oldObj).intValue() != 0)) { String msg = "New Value was null and shouldn't have been for Key Value New Field [" + newColName + "] [" + oldObj + "]"; log.error(msg); tblWriter.logErrors(newColName, msg); return StatusType.NEW_VAL_NULL; } } else { return StatusType.COMPARE_OK; } } StringBuilder errSB = new StringBuilder(); //System.out.println(newObj.getClass().getName()+" "+oldObj.getClass().getName()); if (newObj instanceof java.sql.Date) { boolean isPartialDate = false; Byte partialDateType = null; if (StringUtils.contains(newRsmd.getColumnName(newColInx + 1), "DatePrecision")) { partialDateType = newDBRS.getByte(newColInx); isPartialDate = true; } if (compareTo6DBs) { Object dateObj = oldDBRS.getObject(oldColInx); boolean isPartialDate2 = false; Byte partialDateType2 = null; if (StringUtils.contains(oldRsmd.getColumnName(oldColInx + 1), "DatePrecision")) { partialDateType2 = newDBRS.getByte(oldColInx); isPartialDate2 = true; } else { log.error("Next isn't DatePrecision and can't be!"); tblWriter.logErrors(oldNewIdStr, errSB.toString()); } if (!newObj.equals(dateObj) || (isPartialDate2 && !partialDateType2.equals(partialDateType))) { errSB.insert(0, oldColName + " "); errSB.append("["); errSB.append(datePair); errSB.append("]["); errSB.append(dateFormatter.format((Date) newObj)); errSB.append("] oldDate["); errSB.append(dateFormatter.format((Date) dateObj)); errSB.append("]"); log.error(errSB.toString()); tblWriter.logErrors(oldNewIdStr, errSB.toString()); return StatusType.BAD_DATE; } } else { int oldIntDate = oldDBRS.getInt(oldColInx); if (oldIntDate == 0) { return StatusType.NO_OLD_REC; } BasicSQLUtils.getPartialDate(oldIntDate, datePair, false); if (partialDateType != null) { if (Byte.parseByte(datePair.getPartial()) != partialDateType.byteValue()) { errSB.append("Partial Dates Type do not match. Old[" + datePair.getPartial() + "] New [" + partialDateType.byteValue() + "]"); // error partial dates don't match } } Calendar cal = Calendar.getInstance(); cal.setTime((Date) newObj); int year = Integer.parseInt(datePair.getDateStr().substring(0, 4)); int mon = Integer.parseInt(datePair.getDateStr().substring(5, 7)); int day = Integer.parseInt(datePair.getDateStr().substring(8, 10)); if (mon > 0) mon--; boolean isYearOK = true; int yr = cal.get(Calendar.YEAR); if (year != yr) { errSB.append("Year mismatch Old[" + year + "] New [" + yr + "] "); isYearOK = false; } if (mon != cal.get(Calendar.MONTH)) { errSB.append("Month mismatch Old[" + mon + "] New [" + cal.get(Calendar.MONTH) + "] "); } if (day != cal.get(Calendar.DAY_OF_MONTH)) { errSB.append("Day mismatch Old[" + day + "] New [" + cal.get(Calendar.DAY_OF_MONTH) + "] "); } if (errSB.length() > 0 && (!isYearOK || !isPartialDate)) { errSB.insert(0, oldColName + " "); errSB.append("["); errSB.append(datePair); errSB.append("]["); errSB.append(dateFormatter.format((Date) newObj)); errSB.append("]"); log.error(errSB.toString()); tblWriter.logErrors(oldNewIdStr, errSB.toString()); return StatusType.BAD_DATE; } } } return StatusType.COMPARE_OK; }
From source file:mil.army.usace.data.nativequery.rdbms.NativeRdbmsQuery.java
public <T> List<T> getRecordsAsSimpleCollection(ReturnType returnType, String sql, Class[] returnTypes, Object[] params) {// w w w. java 2 s. co m if (returnTypes.length == 0) returnTypes = null; PreparedStatement st = null; ResultSet rs = null; ResultSetMetaData rsMetaData = null; try { List<T> records = new ArrayList<>(); st = conn.prepareStatement(sql); setParams(st, params); rs = st.executeQuery(); if (returnType == ReturnType.RECORDSET || returnType == ReturnType.RECORDSETMAP) rsMetaData = rs.getMetaData(); while (rs.next()) { switch (returnType) { case ARRAYLIST: if (returnTypes != null) records.add((T) convertType(rs.getObject(1), returnTypes[0])); else records.add((T) rs.getObject(1)); break; case ARRAYLIST_TUPLE: if (returnTypes != null) records.add((T) new Tuple(convertType(rs.getObject(1), returnTypes[0]), convertType(rs.getObject(2), returnTypes[1]))); else records.add((T) new Tuple(rs.getObject(1), rs.getObject(2))); break; case ARRAYLIST_TUPLE3: if (returnTypes != null) records.add((T) new Tuple3(convertType(rs.getObject(1), returnTypes[0]), convertType(rs.getObject(2), returnTypes[1]), convertType(rs.getObject(3), returnTypes[2]))); else records.add((T) new Tuple3(rs.getObject(1), rs.getObject(2), rs.getObject(3))); break; case RECORDSET: ArrayList record = new ArrayList(); for (int i = 0; i < rsMetaData.getColumnCount(); i++) { record.add((returnTypes == null) ? rs.getObject(i + 1) : convertType(rs.getObject(i + 1), returnTypes[i])); } ((ArrayList) records).add(record); break; case RECORDSETMAP: HashMap<String, Object> recordmap = new HashMap<>(); for (int i = 0; i < rsMetaData.getColumnCount(); i++) { recordmap.put(rsMetaData.getColumnName(i + 1), (returnTypes == null) ? rs.getObject(i + 1) : convertType(rs.getObject(i + 1), returnTypes[i])); } ((ArrayList) records).add(recordmap); break; } } return records; } catch (Exception ex) { throw new NativeQueryException(sql, null, ex); } finally { closeOnFinally(rs, st); } }
From source file:com.flexive.core.storage.GenericDivisionImporter.java
/** * Import data from a zip archive to a database table * * @param stmt statement to use * @param zip zip archive containing the zip entry * @param ze zip entry within the archive * @param xpath xpath containing the entries to import * @param table name of the table * @param executeInsertPhase execute the insert phase? * @param executeUpdatePhase execute the update phase? * @param updateColumns columns that should be set to <code>null</code> in a first pass (insert) * and updated to the provided values in a second pass (update), * columns that should be used in the where clause have to be prefixed * with "KEY:", to assign a default value use the expression "columnname:default value", * if the default value is "@", it will be a negative counter starting at 0, decreasing. * If the default value starts with "%", it will be set to the column following the "%" * character in the first pass * @throws Exception on errors/*from w w w.j a v a2 s . c o m*/ */ protected void importTable(Statement stmt, final ZipFile zip, final ZipEntry ze, final String xpath, final String table, final boolean executeInsertPhase, final boolean executeUpdatePhase, final String... updateColumns) throws Exception { //analyze the table final ResultSet rs = stmt.executeQuery("SELECT * FROM " + table + " WHERE 1=2"); StringBuilder sbInsert = new StringBuilder(500); StringBuilder sbUpdate = updateColumns.length > 0 ? new StringBuilder(500) : null; if (rs == null) throw new IllegalArgumentException("Can not analyze table [" + table + "]!"); sbInsert.append("INSERT INTO ").append(table).append(" ("); final ResultSetMetaData md = rs.getMetaData(); final Map<String, ColumnInfo> updateClauseColumns = updateColumns.length > 0 ? new HashMap<String, ColumnInfo>(md.getColumnCount()) : null; final Map<String, ColumnInfo> updateSetColumns = updateColumns.length > 0 ? new LinkedHashMap<String, ColumnInfo>(md.getColumnCount()) : null; final Map<String, String> presetColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null; //preset to a referenced column (%column syntax) final Map<String, String> presetRefColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null; final Map<String, Integer> counters = updateColumns.length > 0 ? new HashMap<String, Integer>(10) : null; final Map<String, ColumnInfo> insertColumns = new HashMap<String, ColumnInfo>( md.getColumnCount() + (counters != null ? counters.size() : 0)); int insertIndex = 1; int updateSetIndex = 1; int updateClauseIndex = 1; boolean first = true; for (int i = 0; i < md.getColumnCount(); i++) { final String currCol = md.getColumnName(i + 1).toLowerCase(); if (updateColumns.length > 0) { boolean abort = false; for (String col : updateColumns) { if (col.indexOf(':') > 0 && !col.startsWith("KEY:")) { String value = col.substring(col.indexOf(':') + 1); col = col.substring(0, col.indexOf(':')); if ("@".equals(value)) { if (currCol.equalsIgnoreCase(col)) { counters.put(col, 0); insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); sbInsert.append(',').append(currCol); } } else if (value.startsWith("%")) { if (currCol.equalsIgnoreCase(col)) { presetRefColumns.put(col, value.substring(1)); insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); sbInsert.append(',').append(currCol); // System.out.println("==> adding presetRefColumn "+col+" with value of "+value.substring(1)); } } else if (!presetColumns.containsKey(col)) presetColumns.put(col, value); } if (currCol.equalsIgnoreCase(col)) { abort = true; updateSetColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), updateSetIndex++)); break; } } if (abort) continue; } if (first) { first = false; } else sbInsert.append(','); sbInsert.append(currCol); insertColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); } if (updateColumns.length > 0 && executeUpdatePhase) { sbUpdate.append("UPDATE ").append(table).append(" SET "); int counter = 0; for (String updateColumn : updateSetColumns.keySet()) { if (counter++ > 0) sbUpdate.append(','); sbUpdate.append(updateColumn).append("=?"); } sbUpdate.append(" WHERE "); boolean hasKeyColumn = false; for (String col : updateColumns) { if (!col.startsWith("KEY:")) continue; hasKeyColumn = true; String keyCol = col.substring(4); for (int i = 0; i < md.getColumnCount(); i++) { if (!md.getColumnName(i + 1).equalsIgnoreCase(keyCol)) continue; updateClauseColumns.put(keyCol, new ColumnInfo(md.getColumnType(i + 1), updateClauseIndex++)); sbUpdate.append(keyCol).append("=? AND "); break; } } if (!hasKeyColumn) throw new IllegalArgumentException("Update columns require a KEY!"); sbUpdate.delete(sbUpdate.length() - 5, sbUpdate.length()); //remove trailing " AND " //"shift" clause indices for (String col : updateClauseColumns.keySet()) { GenericDivisionImporter.ColumnInfo ci = updateClauseColumns.get(col); ci.index += (updateSetIndex - 1); } } if (presetColumns != null) { for (String key : presetColumns.keySet()) sbInsert.append(',').append(key); } sbInsert.append(")VALUES("); for (int i = 0; i < insertColumns.size(); i++) { if (i > 0) sbInsert.append(','); sbInsert.append('?'); } if (presetColumns != null) { for (String key : presetColumns.keySet()) sbInsert.append(',').append(presetColumns.get(key)); } sbInsert.append(')'); if (DBG) { LOG.info("Insert statement:\n" + sbInsert.toString()); if (updateColumns.length > 0) LOG.info("Update statement:\n" + sbUpdate.toString()); } //build a map containing all nodes that require attributes //this allows for matching simple xpath queries like "flatstorages/storage[@name='FX_FLAT_STORAGE']/data" final Map<String, List<String>> queryAttributes = new HashMap<String, List<String>>(5); for (String pElem : xpath.split("/")) { if (!(pElem.indexOf('@') > 0 && pElem.indexOf('[') > 0)) continue; List<String> att = new ArrayList<String>(5); for (String pAtt : pElem.split("@")) { if (!(pAtt.indexOf('=') > 0)) continue; att.add(pAtt.substring(0, pAtt.indexOf('='))); } queryAttributes.put(pElem.substring(0, pElem.indexOf('[')), att); } final PreparedStatement psInsert = stmt.getConnection().prepareStatement(sbInsert.toString()); final PreparedStatement psUpdate = updateColumns.length > 0 && executeUpdatePhase ? stmt.getConnection().prepareStatement(sbUpdate.toString()) : null; try { final SAXParser parser = SAXParserFactory.newInstance().newSAXParser(); final DefaultHandler handler = new DefaultHandler() { private String currentElement = null; private Map<String, String> data = new HashMap<String, String>(10); private StringBuilder sbData = new StringBuilder(10000); boolean inTag = false; boolean inElement = false; int counter; List<String> path = new ArrayList<String>(10); StringBuilder currPath = new StringBuilder(100); boolean insertMode = true; /** * {@inheritDoc} */ @Override public void startDocument() throws SAXException { counter = 0; inTag = false; inElement = false; path.clear(); currPath.setLength(0); sbData.setLength(0); data.clear(); currentElement = null; } /** * {@inheritDoc} */ @Override public void processingInstruction(String target, String data) throws SAXException { if (target != null && target.startsWith("fx_")) { if (target.equals("fx_mode")) insertMode = "insert".equals(data); } else super.processingInstruction(target, data); } /** * {@inheritDoc} */ @Override public void endDocument() throws SAXException { if (insertMode) LOG.info("Imported [" + counter + "] entries into [" + table + "] for xpath [" + xpath + "]"); else LOG.info("Updated [" + counter + "] entries in [" + table + "] for xpath [" + xpath + "]"); } /** * {@inheritDoc} */ @Override public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException { pushPath(qName, attributes); if (currPath.toString().equals(xpath)) { inTag = true; data.clear(); for (int i = 0; i < attributes.getLength(); i++) { String name = attributes.getLocalName(i); if (StringUtils.isEmpty(name)) name = attributes.getQName(i); data.put(name, attributes.getValue(i)); } } else { currentElement = qName; } inElement = true; sbData.setLength(0); } /** * Push a path element from the stack * * @param qName element name to push * @param att attributes */ private void pushPath(String qName, Attributes att) { if (att.getLength() > 0 && queryAttributes.containsKey(qName)) { String curr = qName + "["; boolean first = true; final List<String> attList = queryAttributes.get(qName); for (int i = 0; i < att.getLength(); i++) { if (!attList.contains(att.getQName(i))) continue; if (first) first = false; else curr += ','; curr += "@" + att.getQName(i) + "='" + att.getValue(i) + "'"; } curr += ']'; path.add(curr); } else path.add(qName); buildPath(); } /** * Pop the top path element from the stack */ private void popPath() { path.remove(path.size() - 1); buildPath(); } /** * Rebuild the current path */ private synchronized void buildPath() { currPath.setLength(0); for (String s : path) currPath.append(s).append('/'); if (currPath.length() > 1) currPath.delete(currPath.length() - 1, currPath.length()); // System.out.println("currPath: " + currPath); } /** * {@inheritDoc} */ @Override public void endElement(String uri, String localName, String qName) throws SAXException { if (currPath.toString().equals(xpath)) { if (DBG) LOG.info("Insert [" + xpath + "]: [" + data + "]"); inTag = false; try { if (insertMode) { if (executeInsertPhase) { processColumnSet(insertColumns, psInsert); counter += psInsert.executeUpdate(); } } else { if (executeUpdatePhase) { if (processColumnSet(updateSetColumns, psUpdate)) { processColumnSet(updateClauseColumns, psUpdate); counter += psUpdate.executeUpdate(); } } } } catch (SQLException e) { throw new SAXException(e); } catch (ParseException e) { throw new SAXException(e); } } else { if (inTag) { data.put(currentElement, sbData.toString()); } currentElement = null; } popPath(); inElement = false; sbData.setLength(0); } /** * Process a column set * * @param columns the columns to process * @param ps prepared statement to use * @return if data other than <code>null</code> has been set * @throws SQLException on errors * @throws ParseException on date/time conversion errors */ private boolean processColumnSet(Map<String, ColumnInfo> columns, PreparedStatement ps) throws SQLException, ParseException { boolean dataSet = false; for (String col : columns.keySet()) { ColumnInfo ci = columns.get(col); String value = data.get(col); if (insertMode && counters != null && counters.get(col) != null) { final int newVal = counters.get(col) - 1; value = String.valueOf(newVal); counters.put(col, newVal); // System.out.println("new value for " + col + ": " + newVal); } if (insertMode && presetRefColumns != null && presetRefColumns.get(col) != null) { value = data.get(presetRefColumns.get(col)); // System.out.println("Set presetRefColumn for "+col+" to ["+value+"] from column ["+presetRefColumns.get(col)+"]"); } if (value == null) ps.setNull(ci.index, ci.columnType); else { dataSet = true; switch (ci.columnType) { case Types.BIGINT: case Types.NUMERIC: if (DBG) LOG.info("BigInt " + ci.index + "->" + new BigDecimal(value)); ps.setBigDecimal(ci.index, new BigDecimal(value)); break; case java.sql.Types.DOUBLE: if (DBG) LOG.info("Double " + ci.index + "->" + Double.parseDouble(value)); ps.setDouble(ci.index, Double.parseDouble(value)); break; case java.sql.Types.FLOAT: case java.sql.Types.REAL: if (DBG) LOG.info("Float " + ci.index + "->" + Float.parseFloat(value)); ps.setFloat(ci.index, Float.parseFloat(value)); break; case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: if (DBG) LOG.info("Timestamp/Date " + ci.index + "->" + FxFormatUtils.getDateTimeFormat().parse(value)); ps.setTimestamp(ci.index, new Timestamp(FxFormatUtils.getDateTimeFormat().parse(value).getTime())); break; case Types.TINYINT: case Types.SMALLINT: if (DBG) LOG.info("Integer " + ci.index + "->" + Integer.valueOf(value)); ps.setInt(ci.index, Integer.valueOf(value)); break; case Types.INTEGER: case Types.DECIMAL: try { if (DBG) LOG.info("Long " + ci.index + "->" + Long.valueOf(value)); ps.setLong(ci.index, Long.valueOf(value)); } catch (NumberFormatException e) { //Fallback (temporary) for H2 if the reported long is a big decimal (tree...) ps.setBigDecimal(ci.index, new BigDecimal(value)); } break; case Types.BIT: case Types.CHAR: case Types.BOOLEAN: if (DBG) LOG.info("Boolean " + ci.index + "->" + value); if ("1".equals(value) || "true".equals(value)) ps.setBoolean(ci.index, true); else ps.setBoolean(ci.index, false); break; case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BLOB: case Types.BINARY: ZipEntry bin = zip.getEntry(value); if (bin == null) { LOG.error("Failed to lookup binary [" + value + "]!"); ps.setNull(ci.index, ci.columnType); break; } try { ps.setBinaryStream(ci.index, zip.getInputStream(bin), (int) bin.getSize()); } catch (IOException e) { LOG.error("IOException importing binary [" + value + "]: " + e.getMessage(), e); } break; case Types.CLOB: case Types.LONGVARCHAR: case Types.VARCHAR: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: if (DBG) LOG.info("String " + ci.index + "->" + value); ps.setString(ci.index, value); break; default: LOG.warn("Unhandled type [" + ci.columnType + "] for column [" + col + "]"); } } } return dataSet; } /** * {@inheritDoc} */ @Override public void characters(char[] ch, int start, int length) throws SAXException { if (inElement) sbData.append(ch, start, length); } }; handler.processingInstruction("fx_mode", "insert"); parser.parse(zip.getInputStream(ze), handler); if (updateColumns.length > 0 && executeUpdatePhase) { handler.processingInstruction("fx_mode", "update"); parser.parse(zip.getInputStream(ze), handler); } } finally { Database.closeObjects(GenericDivisionImporter.class, psInsert, psUpdate); } }