List of usage examples for java.sql ResultSetMetaData getColumnCount
int getColumnCount() throws SQLException;
ResultSet object. From source file:com.mapd.utility.SQLImporter.java
private void checkMapDTable(ResultSetMetaData md) throws SQLException { createMapDConnection();/*ww w. j av a2s. com*/ String tName = cmd.getOptionValue("targetTable"); if (tableExists(tName)) { // check if we want to truncate if (cmd.hasOption("truncate")) { executeMapDCommand("Drop table " + tName); createMapDTable(md); } else { List<TColumnType> columnInfo = getColumnInfo(tName); // table exists lets check it has same number of columns if (md.getColumnCount() != columnInfo.size()) { LOGGER.error("Table sizes do not match - Mapd " + columnInfo.size() + " versus Select " + md.getColumnCount()); exit(1); } // table exists lets check it is same layout - check names will do for now for (int colNum = 1; colNum <= columnInfo.size(); colNum++) { if (!columnInfo.get(colNum - 1).col_name.equalsIgnoreCase(md.getColumnName(colNum))) { LOGGER.error("MapD Table does not have matching column in same order for column number" + colNum + " MapD column name is " + columnInfo.get(colNum - 1).col_name + " versus Select " + md.getColumnName(colNum)); exit(1); } } } } else { createMapDTable(md); } }
From source file:i5.las2peer.services.loadStoreGraphService.LoadStoreGraphService.java
/** * /*from w w w. j a v a 2s . c o m*/ * loadGraph * * @param id a String * * @return HttpResponse * */ @GET @Path("/{id}") @Produces(MediaType.APPLICATION_JSON) @Consumes(MediaType.TEXT_PLAIN) @ApiResponses(value = { @ApiResponse(code = HttpURLConnection.HTTP_NOT_FOUND, message = "graphNotFound"), @ApiResponse(code = HttpURLConnection.HTTP_OK, message = "graphLoaded"), @ApiResponse(code = HttpURLConnection.HTTP_INTERNAL_ERROR, message = "internalError") }) @ApiOperation(value = "loadGraph", notes = "") public HttpResponse loadGraph(@PathParam("id") String id) { String result = ""; String columnName = ""; String selectquery = ""; int columnCount = 0; Connection conn = null; PreparedStatement stmnt = null; ResultSet rs = null; ResultSetMetaData rsmd = null; try { // get connection from connection pool conn = dbm.getConnection(); selectquery = "SELECT * FROM graphs WHERE graphId = " + id + " ;"; // prepare statement stmnt = conn.prepareStatement(selectquery); // retrieve result set rs = stmnt.executeQuery(); rsmd = (ResultSetMetaData) rs.getMetaData(); columnCount = rsmd.getColumnCount(); // process result set if (rs.next()) { JSONObject ro = new JSONObject(); for (int i = 1; i <= columnCount; i++) { result = rs.getString(i); columnName = rsmd.getColumnName(i); // setup resulting JSON Object ro.put(columnName, result); } HttpResponse graphLoaded = new HttpResponse(ro.toJSONString(), HttpURLConnection.HTTP_OK); return graphLoaded; } else { // return HTTP Response on error String er = "No result for graph with id " + id; HttpResponse graphNotFound = new HttpResponse(er, HttpURLConnection.HTTP_NOT_FOUND); return graphNotFound; } } catch (Exception e) { String er = "Internal error: " + e.getMessage(); HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR); return internalError; } finally { // free resources if (rs != null) { try { rs.close(); } catch (Exception e) { Context.logError(this, e.getMessage()); String er = "Internal error: " + e.getMessage(); HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR); return internalError; } } if (stmnt != null) { try { stmnt.close(); } catch (Exception e) { Context.logError(this, e.getMessage()); String er = "Internal error: " + e.getMessage(); HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR); return internalError; } } if (conn != null) { try { conn.close(); } catch (Exception e) { Context.logError(this, e.getMessage()); String er = "Internal error: " + e.getMessage(); HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR); return internalError; } } } }
From source file:com.glaf.base.modules.todo.business.TodoJobBean.java
public List getTasks() { List todos = todoService.getSQLTodos(); List rows = new java.util.ArrayList(); if (todos != null && todos.size() > 0) { Iterator iterator = todos.iterator(); while (iterator.hasNext()) { Todo todo = (Todo) iterator.next(); if (StringUtils.isNotEmpty(todo.getSql())) { logger.info(todo.getId() + ":" + todo.getSql()); Map rowsMap = new java.util.HashMap(); java.sql.Connection conn = null; java.sql.PreparedStatement psmt = null; java.sql.ResultSet rs = null; try { conn = DBConnectionFactory.getConnection(); psmt = conn.prepareStatement(todo.getSql()); rs = psmt.executeQuery(); java.sql.ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { TodoInstance model = new TodoInstance(); model.setRowId(rs.getString(1)); model.setStartDate(rs.getDate(2)); if (rsmd.getColumnCount() == 3) { switch (Long.valueOf(todo.getId()).intValue()) { case 8005: case 7001: case 7002: case 7003: case 7004: case 17001: case 17010: case 18001: case 19001: case 20001: case 20084001: model.setDeptId(rs.getLong(3)); break; default: model.setActorId(rs.getString(3)); break; }//from ww w . j ava2 s . co m } rowsMap.put(model.getRowId(), model); } rs.close(); psmt.close(); rs = null; psmt = null; } catch (java.sql.SQLException ex) { logger.debug(todo.getId() + ":" + todo.getSql()); ex.printStackTrace(); } finally { JdbcUtils.close(rs); JdbcUtils.close(psmt); JdbcUtils.close(conn); } if (rowsMap.size() > 0) { Iterator iter = rowsMap.keySet().iterator(); while (iter.hasNext()) { String rowId = (String) iter.next(); TodoInstance model = (TodoInstance) rowsMap.get(rowId); Date startDate = model.getStartDate(); if (startDate == null) { startDate = new java.util.Date(); } model.setProvider("sql"); model.setLinkType(todo.getLinkType()); model.setAppId(todo.getAppId()); model.setModuleId(todo.getModuleId()); model.setTodoId(todo.getId()); model.setRoleId(todo.getRoleId()); model.setRoleCode(todo.getRoleCode()); model.setTitle(todo.getTitle()); model.setCreateDate(new Date(System.currentTimeMillis())); model.setStartDate(startDate); int limitDay = todo.getLimitDay(); int ahour = todo.getXa(); int bhour = todo.getXb(); Date limitWorkDate = workCalendarService.getWorkDate(startDate, limitDay); long time = limitWorkDate.getTime(); Date cautionDate = new Date(time - ahour * DateUtils.HOUR); Date pastDueDate = new Date(time + bhour * DateUtils.HOUR); model.setAlarmDate(cautionDate); model.setPastDueDate(pastDueDate); model.setRowId(rowId); model.setVersionNo(System.currentTimeMillis()); rows.add(model); } } } } } return rows; }
From source file:eionet.acl.PersistenceDB.java
private String[][] executeStringQuery(String sql) throws SQLException { Vector<String[]> rvec = new Vector<String[]>(); // Return value as Vector String[][] rval = {}; // Return value Connection con = null;/* ww w. jav a 2s . co m*/ Statement stmt = null; ResultSet rset = null; // Process the result set con = getConnection(); try { stmt = con.createStatement(); rset = stmt.executeQuery(sql); ResultSetMetaData md = rset.getMetaData(); //number of columns in the result set int colCnt = md.getColumnCount(); while (rset.next()) { String[] row = new String[colCnt]; // Row of the result set // Retrieve the columns of the result set for (int i = 0; i < colCnt; ++i) { row[i] = rset.getString(i + 1); } rvec.addElement(row); } } catch (SQLException e) { e.printStackTrace(System.out); throw new SQLException("Error occurred when processing result set: " + sql); } finally { close(con, stmt, null); } // Build return value if (rvec.size() > 0) { rval = new String[rvec.size()][]; for (int i = 0; i < rvec.size(); ++i) rval[i] = (String[]) rvec.elementAt(i); } // Success return rval; }
From source file:ResultSetIterator.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 w ww . java 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.getColumnName(col); for (int i = 0; i < props.length; i++) { if (columnName.equalsIgnoreCase(props[i].getName())) { columnToProperty[col] = i; break; } } } return columnToProperty; }
From source file:DbMetaServlet.java
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, java.io.IOException { String sql = "select * from aTable"; Connection conn = null;//from w ww . j a va2s . com Statement stmt = null; ResultSet rs = null; ResultSetMetaData rsm = null; response.setContentType("text/html"); java.io.PrintWriter out = response.getWriter(); out.println("<html><head><title>Discover a ResultSet</title></head><body>"); out.println("<h2>Here is Info about the returned ResultSet</h2>"); out.println("<table border='1'><tr>"); try { //Get a connection from the pool conn = pool.getConnection(); //Create a Statement with which to run some SQL stmt = conn.createStatement(); //Execute the SQL rs = stmt.executeQuery(sql); //Get a ResultSetMetaData object from the ResultSet rsm = rs.getMetaData(); int colCount = rsm.getColumnCount(); //print column names printMeta(rsm, "name", out, colCount); //print column index printMeta(rsm, "index", out, colCount); //print column type printMeta(rsm, "column type", out, colCount); //print column display size printMeta(rsm, "column display", out, colCount); } catch (Exception e) { throw new ServletException(e.getMessage()); } finally { try { stmt.close(); conn.close(); } catch (SQLException sqle) { } } out.println("</table></body></html>"); out.close(); }
From source file:com.mapd.utility.SQLImporter.java
void executeQuery() { Connection conn = null;/*from w ww .jav a 2 s . c o m*/ Statement stmt = null; long totalTime = 0; try { //Open a connection LOGGER.info("Connecting to database url :" + cmd.getOptionValue("jdbcConnect")); conn = DriverManager.getConnection(cmd.getOptionValue("jdbcConnect"), cmd.getOptionValue("sourceUser"), cmd.getOptionValue("sourcePasswd")); long startTime = System.currentTimeMillis(); //Execute a query stmt = conn.createStatement(); long timer; ResultSet rs = stmt.executeQuery(cmd.getOptionValue("sqlStmt")); //check if table already exists and is compatible in MapD with the query metadata ResultSetMetaData md = rs.getMetaData(); checkMapDTable(md); timer = System.currentTimeMillis(); long resultCount = 0; int bufferCount = 0; long total = 0; int bufferSize = Integer.valueOf(cmd.getOptionValue("bufferSize", "10000")); List<TStringRow> rows = new ArrayList(bufferSize); while (rs.next()) { TStringRow tsr = new TStringRow(); for (int i = 1; i <= md.getColumnCount(); i++) { // place string in rows array TStringValue tsv = new TStringValue(); tsv.str_val = rs.getString(i); if (rs.wasNull()) { tsv.is_null = true; } else { tsv.is_null = false; } tsr.addToCols(tsv); } rows.add(tsr); resultCount++; bufferCount++; if (bufferCount == bufferSize) { bufferCount = 0; //send the buffer to mapD client.load_table(session, cmd.getOptionValue("targetTable"), rows); rows.clear(); if (resultCount % 100000 == 0) { LOGGER.info("Imported " + resultCount + " records"); } } } if (bufferCount > 0) { //send the LAST buffer to mapD client.load_table(session, cmd.getOptionValue("targetTable"), rows); rows.clear(); bufferCount = 0; } LOGGER.info("result set count is " + resultCount + " read time is " + (System.currentTimeMillis() - timer) + "ms"); //Clean-up environment rs.close(); stmt.close(); totalTime = System.currentTimeMillis() - startTime; conn.close(); } catch (SQLException se) { LOGGER.error("SQLException - " + se.toString()); se.printStackTrace(); } catch (TMapDException ex) { LOGGER.error("TMapDException - " + ex.toString()); ex.printStackTrace(); } catch (TException ex) { LOGGER.error("TException failed - " + ex.toString()); ex.printStackTrace(); } finally { //finally block used to close resources try { if (stmt != null) { stmt.close(); } } catch (SQLException se2) { } // nothing we can do try { if (conn != null) { conn.close(); } } catch (SQLException se) { LOGGER.error("SQlException in close - " + se.toString()); se.printStackTrace(); } //end finally try } //end try }
From source file:QueryConnector.java
private void loadData(XSpreadsheet sheet, CellAddress startCell, Query query, boolean saveConnectionInfo) throws Exception { XCellRange cellRange = UnoRuntime.queryInterface(XCellRange.class, sheet); com.sun.star.lang.Locale locale = new com.sun.star.lang.Locale(); //effettuo la query sul db Class.forName(query.getDriverClass()); Connection con = DriverManager.getConnection(query.getURL(), query.getUsername(), query.getPassword()); Statement stmt = con.createStatement(); ResultSet result = stmt.executeQuery(query.getQuery()); ResultSetMetaData metaData = result.getMetaData(); int columnCount = metaData.getColumnCount(); //intestazione del risultato (nomi delle colonne) for (int i = 1, j = startCell.Column; i <= columnCount; i++, j++) { XCell curCellHeader = cellRange.getCellByPosition(j, startCell.Row); XTextRange currentCellHeaderText = UnoRuntime.queryInterface(XTextRange.class, curCellHeader); currentCellHeaderText.setString(metaData.getColumnLabel(i)); if (saveConnectionInfo) setCellUserProperty(curCellHeader, QUERY_PROPERTY, query.getName()); }/*w ww. j av a2s. c o m*/ //contenuto della query int rowCount = 1; int rowIdx = startCell.Row + 1; while (result.next()) { for (int i = 1, j = startCell.Column; i <= columnCount; i++, j++) { XCell curCellData = cellRange.getCellByPosition(j, rowIdx); XPropertySet cellDataProps = UnoRuntime.queryInterface(XPropertySet.class, curCellData); XTextRange currentCellDataText = UnoRuntime.queryInterface(XTextRange.class, curCellData); Object cellValue = result.getObject(i); if (cellValue instanceof java.sql.Date || cellValue instanceof java.sql.Time || cellValue instanceof java.util.Date) { cellDataProps.setPropertyValue("NumberFormat", this.numberFormats.queryKey("GG/MM/AAAA HH.MM.SS", locale, true)); curCellData.setValue(dateValue(DATE_FORMATTER.format((java.util.Date) cellValue))); } else if (cellValue instanceof Number) { cellDataProps.setPropertyValue("NumberFormat", this.numberFormats.queryKey("#", locale, true)); Number number = (Number) cellValue; curCellData.setValue(number.doubleValue()); } else if (cellValue == null) { currentCellDataText.setString(""); } else currentCellDataText.setString(cellValue.toString()); if (saveConnectionInfo) setCellUserProperty(curCellData, QUERY_PROPERTY, query.getName()); } rowCount++; rowIdx++; } result.close(); stmt.close(); con.close(); //aggiono le informazioni sul range dei dati selezionato nella query query.setPositionAndArea(startCell.Column, startCell.Row, columnCount, rowCount); //salvo le informazioni sul documento if (saveConnectionInfo) settings.saveQuery(query); }
From source file:com.googlecode.jdbcproc.daofactory.impl.block.service.ParametersSetterBlockServiceImpl.java
private Map<String, Integer> createTypes(JdbcTemplate jdbcTemplate, final String tableName) { return jdbcTemplate.execute(new StatementCallback<Map<String, Integer>>() { public Map<String, Integer> doInStatement(Statement stmt) throws SQLException, DataAccessException { ResultSet rs = stmt.executeQuery("select * from " + tableName); try { ResultSetMetaData meta = rs.getMetaData(); Map<String, Integer> types = new HashMap<String, Integer>(); int count = meta.getColumnCount(); for (int i = 1; i <= count; i++) { String name = meta.getColumnName(i); int type = meta.getColumnType(i); types.put(name, type); }//from w ww .j a v a 2s. co m return types; } finally { rs.close(); } } }); }
From source file:it.unibas.spicy.persistence.relational.DAORelational.java
public void loadInstance(int scenarioNo, AccessConfiguration accessConfiguration, IDataSourceProxy dataSource, DBFragmentDescription dataDescription, IConnectionFactory dataSourceDB, boolean source) throws DAOException, SQLException { Connection connection = dataSourceDB.getConnection(accessConfiguration); DatabaseMetaData databaseMetaData = null; String catalog = null;//from w w w . j a va2 s . co m String schemaName = accessConfiguration.getSchemaName(); Connection connectionPostgres = null; this.dataDescription = dataDescription; AccessConfiguration accessConfigurationPostgres = new AccessConfiguration(); accessConfigurationPostgres.setDriver(SpicyEngineConstants.ACCESS_CONFIGURATION_DRIVER); accessConfigurationPostgres .setUri(SpicyEngineConstants.ACCESS_CONFIGURATION_URI + SpicyEngineConstants.MAPPING_TASK_DB_NAME); accessConfigurationPostgres.setLogin(SpicyEngineConstants.ACCESS_CONFIGURATION_LOGIN); accessConfigurationPostgres.setPassword(SpicyEngineConstants.ACCESS_CONFIGURATION_PASS); connectionPostgres = dataSourceDB.getConnection(accessConfigurationPostgres); try { databaseMetaData = connection.getMetaData(); catalog = connection.getCatalog(); String[] tableTypes = new String[] { "TABLE" }; ResultSet tableResultSet = databaseMetaData.getTables(catalog, schemaName, null, tableTypes); Statement statement = connection.createStatement(); Statement statementPostgres = connectionPostgres.createStatement(); while (tableResultSet.next()) { String tableName = tableResultSet.getString("TABLE_NAME"); if (!this.dataDescription.checkLoadTable(tableName)) { continue; } String tablePath = tableName; if (!schemaName.equals("")) { tablePath = schemaName + ".\"" + tableName + "\""; } String newTablePath = tableName; if (source) { newTablePath = SpicyEngineConstants.SOURCE_SCHEMA_NAME + scenarioNo + ".\"" + tableName + "\""; } else { newTablePath = SpicyEngineConstants.TARGET_SCHEMA_NAME + scenarioNo + ".\"" + tableName + "\""; } ResultSet countResult = statement .executeQuery("SELECT COUNT(*) AS instancesCount FROM " + tablePath + ";"); int instancesCount = 1; while (countResult.next()) { instancesCount = countResult.getInt("instancesCount"); } ResultSet pKList = null; pKList = databaseMetaData.getPrimaryKeys(null, null, tableName); // ResultSet pKList = statement.executeQuery("SELECT c.column_name as keyname\n" + "FROM information_schema.key_column_usage AS c\n" + // "LEFT JOIN information_schema.table_constraints AS t\n" + // "ON t.constraint_name = c.constraint_name\n" + // "WHERE t.table_name = '" + tablePath + "' AND t.constraint_type = 'PRIMARY KEY';"); String pKListString = ""; while (pKList.next()) { pKListString += pKList.getString("COLUMN_NAME") + ","; } if (pKListString != "") pKListString = pKListString.substring(0, pKListString.length() - 1); int inCount = 0; String viewName = tableName + "_MIPMapView"; String orderByClause = ""; if (pKListString != "") orderByClause = " ORDER BY " + pKListString; statement.executeUpdate("DROP VIEW IF EXISTS " + viewName + ";"); statement.executeUpdate( "CREATE VIEW " + viewName + " AS SELECT * FROM " + tablePath + orderByClause + ";"); for (int i = 0; i <= ((instancesCount - 1) / BATCH_SIZE); i++) { ResultSet instancesSet = statement.executeQuery("SELECT * FROM " + viewName + " LIMIT " + BATCH_SIZE + " OFFSET " + (BATCH_SIZE * i) + ";"); ResultSetMetaData rsmd = instancesSet.getMetaData(); int columnsNumber = rsmd.getColumnCount(); String sql_insert_stmnt = ""; while (instancesSet.next()) { String tmp_sql_insert_stmnt = "("; for (int j = 1; j <= columnsNumber; j++) { String columnValue = instancesSet.getString(j); if (columnValue == null) { tmp_sql_insert_stmnt += " null,"; } else { if (isTextColumn(rsmd.getColumnTypeName(j))) { tmp_sql_insert_stmnt += "'" + columnValue.replaceAll("'", "''") + "',"; } else { tmp_sql_insert_stmnt += "" + columnValue + ","; } } } //take out the last ',' character tmp_sql_insert_stmnt = tmp_sql_insert_stmnt.substring(0, tmp_sql_insert_stmnt.length() - 1); tmp_sql_insert_stmnt += "),"; // if (!inserted.contains(tmp_sql_insert_stmnt)) { sql_insert_stmnt += tmp_sql_insert_stmnt; // inserted.add(tmp_sql_insert_stmnt); // } } if (!sql_insert_stmnt.equals("")) { //take out the last ',' character sql_insert_stmnt = sql_insert_stmnt.substring(0, sql_insert_stmnt.length() - 1); inCount += statementPostgres .executeUpdate("insert into " + newTablePath + " values " + sql_insert_stmnt + ";"); } } statement.executeUpdate("DROP VIEW IF EXISTS " + viewName + ";"); } dataSource.addAnnotation(SpicyEngineConstants.LOADED_INSTANCES_FLAG, true); } finally { if (connection != null) dataSourceDB.close(connection); if (connectionPostgres != null) dataSourceDB.close(connectionPostgres); } }