List of usage examples for java.sql ResultSetMetaData getColumnCount
int getColumnCount() throws SQLException;
ResultSet object. From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
public List<String> toStringList(ResultSet rs) throws Exception { List<String> list = new ArrayList<String>(); try {/* www .j a va 2 s .com*/ // we will need the column names, this will save the table meta-data like column nmae. java.sql.ResultSetMetaData rsmd = rs.getMetaData(); //loop through the ResultSet while (rs.next()) { //figure out how many columns there are int numColumns = rsmd.getColumnCount(); //each row in the ResultSet will be converted to a JSON Object StringBuilder builder = new StringBuilder(); // loop through all the columns and place them into the JSON Object for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { builder.append(rs.getArray(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { builder.append(rs.getBoolean(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { builder.append(rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { builder.append(rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { builder.append(rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { builder.append(rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { // temp = rs.getString(column_name); //saving column data to temp variable // temp = ESAPI.encoder().canonicalize(temp); //decoding data to base state // temp = ESAPI.encoder().encodeForHTML(temp); //encoding to be browser safe // obj.put(column_name, temp); //putting data into JSON object // builder.append(rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { builder.append(rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIME) { builder.append(rs.getTime(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { builder.append(rs.getTimestamp(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) { builder.append(rs.getBigDecimal(column_name)); } else { builder.append(rs.getObject(column_name)); } } //end foreach list.add(builder.toString()); } //end while } catch (Exception e) { e.printStackTrace(); } return list; //return String list }
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;/*from www .j a va2 s. co 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:com.xpfriend.fixture.cast.temp.Database.java
private DynaClass getDynaClass(ResultSet resultSet) throws SQLException { ResultSetMetaData md = resultSet.getMetaData(); int count = md.getColumnCount(); DynaProperty[] properties = new DynaProperty[count]; for (int i = 0; i < properties.length; i++) { int column = i + 1; Class<?> type = TypeConverter.getJavaType(md.getColumnType(column), md.getColumnTypeName(column), md.getPrecision(column), md.getScale(column)); String name = getColumnLabel(md, column); properties[i] = new DynaProperty(name, type); }//w w w.jav a2 s.c om return new BasicDynaClass(null, null, properties); }
From source file:org.dcm4chee.dashboard.ui.report.display.DisplayReportDiagramPanel.java
@Override public void onBeforeRender() { super.onBeforeRender(); Connection jdbcConnection = null; try {/*from w w w . j a v a2 s. c om*/ if (report == null) throw new Exception("No report given to render diagram"); jdbcConnection = DatabaseUtils.getDatabaseConnection(report.getDataSource()); ResultSet resultSet = DatabaseUtils.getResultSet(jdbcConnection, report.getStatement(), parameters); ResultSetMetaData metaData = resultSet.getMetaData(); JFreeChart chart = null; resultSet.beforeFirst(); // Line chart - 1 numeric value if (report.getDiagram() == 0) { if (metaData.getColumnCount() != 1) throw new Exception( new ResourceModel("dashboard.report.reportdiagram.image.render.error.1numvalues") .wrapOnAssignment(this).getObject()); DefaultCategoryDataset dataset = new DefaultCategoryDataset(); while (resultSet.next()) dataset.addValue(resultSet.getDouble(1), metaData.getColumnName(1), String.valueOf(resultSet.getRow())); chart = ChartFactory.createLineChart( new ResourceModel("dashboard.report.reportdiagram.image.label").wrapOnAssignment(this) .getObject(), new ResourceModel("dashboard.report.reportdiagram.image.row-label").wrapOnAssignment(this) .getObject(), metaData.getColumnName(1), dataset, PlotOrientation.VERTICAL, true, true, true); // XY Series chart - 2 numeric values } else if (report.getDiagram() == 1) { if (metaData.getColumnCount() != 2) throw new Exception( new ResourceModel("dashboard.report.reportdiagram.image.render.error.2numvalues") .wrapOnAssignment(this).getObject()); XYSeries series = new XYSeries(metaData.getColumnName(1) + " / " + metaData.getColumnName(2)); while (resultSet.next()) series.add(resultSet.getDouble(1), resultSet.getDouble(2)); chart = ChartFactory.createXYLineChart( new ResourceModel("dashboard.report.reportdiagram.image.label").wrapOnAssignment(this) .getObject(), metaData.getColumnName(1), metaData.getColumnName(2), new XYSeriesCollection(series), PlotOrientation.VERTICAL, true, true, true); // Category chart - 1 numeric value, 1 comparable value } else if (report.getDiagram() == 2) { if (metaData.getColumnCount() != 2) throw new Exception( new ResourceModel("dashboard.report.reportdiagram.image.render.error.2values") .wrapOnAssignment(this).getObject()); DefaultCategoryDataset dataset = new DefaultCategoryDataset(); while (resultSet.next()) dataset.setValue(resultSet.getDouble(1), metaData.getColumnName(1) + " / " + metaData.getColumnName(2), resultSet.getString(2)); chart = new JFreeChart( new ResourceModel("dashboard.report.reportdiagram.image.label").wrapOnAssignment(this) .getObject(), new CategoryPlot(dataset, new LabelAdaptingCategoryAxis(14, metaData.getColumnName(2)), new NumberAxis(metaData.getColumnName(1)), new CategoryStepRenderer(true))); // Pie chart - 1 numeric value, 1 comparable value (used as category) } else if ((report.getDiagram() == 3) || (report.getDiagram() == 4)) { if (metaData.getColumnCount() != 2) throw new Exception( new ResourceModel("dashboard.report.reportdiagram.image.render.error.2values") .wrapOnAssignment(this).getObject()); DefaultPieDataset dataset = new DefaultPieDataset(); while (resultSet.next()) dataset.setValue(resultSet.getString(2), resultSet.getDouble(1)); if (report.getDiagram() == 3) // Pie chart 2D chart = ChartFactory .createPieChart(new ResourceModel("dashboard.report.reportdiagram.image.label") .wrapOnAssignment(this).getObject(), dataset, true, true, true); else if (report.getDiagram() == 4) { // Pie chart 3D chart = ChartFactory .createPieChart3D(new ResourceModel("dashboard.report.reportdiagram.image.label") .wrapOnAssignment(this).getObject(), dataset, true, true, true); ((PiePlot3D) chart.getPlot()).setForegroundAlpha( Float.valueOf(new ResourceModel("dashboard.report.reportdiagram.image.alpha") .wrapOnAssignment(this).getObject())); } // Bar chart - 1 numeric value, 2 comparable values (used as category, series) } else if (report.getDiagram() == 5) { if ((metaData.getColumnCount() != 2) && (metaData.getColumnCount() != 3)) throw new Exception( new ResourceModel("dashboard.report.reportdiagram.image.render.error.3values") .wrapOnAssignment(this).getObject()); DefaultCategoryDataset dataset = new DefaultCategoryDataset(); while (resultSet.next()) dataset.setValue(resultSet.getDouble(1), resultSet.getString(2), resultSet.getString(metaData.getColumnCount())); chart = ChartFactory.createBarChart( new ResourceModel("dashboard.report.reportdiagram.image.label").wrapOnAssignment(this) .getObject(), metaData.getColumnName(2), metaData.getColumnName(1), dataset, PlotOrientation.VERTICAL, true, true, true); } int[] winSize = DashboardCfgDelegate.getInstance().getWindowSize("reportDiagramImage"); addOrReplace(new JFreeChartImage("diagram", chart, winSize[0], winSize[1])); final JFreeChart downloadableChart = chart; addOrReplace(new Link<Object>("diagram-download") { private static final long serialVersionUID = 1L; @Override public void onClick() { RequestCycle.get().setRequestTarget(new IRequestTarget() { public void respond(RequestCycle requestCycle) { WebResponse wr = (WebResponse) requestCycle.getResponse(); wr.setContentType("image/png"); wr.setHeader("content-disposition", "attachment;filename=diagram.png"); OutputStream os = wr.getOutputStream(); try { ImageIO.write(downloadableChart.createBufferedImage(800, 600), "png", os); os.close(); } catch (IOException e) { log.error(this.getClass().toString() + ": " + "respond: " + e.getMessage()); log.debug("Exception: ", e); } wr.close(); } @Override public void detach(RequestCycle arg0) { } }); } }.add(new Image("diagram-download-image", ImageManager.IMAGE_DASHBOARD_REPORT_DOWNLOAD) .add(new ImageSizeBehaviour()) .add(new TooltipBehaviour("dashboard.report.reportdiagram.", "image.downloadlink")))); addOrReplace(new Image("diagram-print-image", ImageManager.IMAGE_DASHBOARD_REPORT_PRINT) .add(new ImageSizeBehaviour()) .add(new TooltipBehaviour("dashboard.report.reportdiagram.", "image.printbutton"))); addOrReplace(new Label("error-message", "").setVisible(false)); addOrReplace(new Label("error-reason", "").setVisible(false)); } catch (Exception e) { log.error("Exception: " + e.getMessage()); addOrReplace(((DynamicDisplayPage) this.getPage()).new PlaceholderLink("diagram-download") .setVisible(false)); addOrReplace(new Image("diagram-print-image").setVisible(false)); addOrReplace(new Image("diagram").setVisible(false)); addOrReplace(new Label("error-message", new ResourceModel("dashboard.report.reportdiagram.statement.error").wrapOnAssignment(this) .getObject()) .add(new AttributeModifier("class", true, new Model<String>("message-error")))); addOrReplace(new Label("error-reason", e.getMessage()) .add(new AttributeModifier("class", true, new Model<String>("message-error")))); log.debug(getClass() + ": ", e); } finally { try { jdbcConnection.close(); } catch (Exception ignore) { } } }
From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
private List<LogMessageData> toLogMessageDataInternal(ResultSet rs, List<LogMessageData> logMessageData) { try {/*from www . ja v a 2s . com*/ // we will need the column names. java.sql.ResultSetMetaData rsmd = rs.getMetaData(); //loop through the ResultSet while (rs.next()) { //figure out how many columns there are int numColumns = rsmd.getColumnCount(); String tableName = rsmd.getTableName(1); LogMessageData obj = null; if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_01_NAME)) { obj = new LogMessageData_Partition_01(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_02_NAME)) { obj = new LogMessageData_Partition_02(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_03_NAME)) { obj = new LogMessageData_Partition_03(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_04_NAME)) { obj = new LogMessageData_Partition_04(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_05_NAME)) { obj = new LogMessageData_Partition_05(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_06_NAME)) { obj = new LogMessageData_Partition_06(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_07_NAME)) { obj = new LogMessageData_Partition_07(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_08_NAME)) { obj = new LogMessageData_Partition_08(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_09_NAME)) { obj = new LogMessageData_Partition_09(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_10_NAME)) { obj = new LogMessageData_Partition_10(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_11_NAME)) { obj = new LogMessageData_Partition_11(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_12_NAME)) { obj = new LogMessageData_Partition_12(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_13_NAME)) { obj = new LogMessageData_Partition_13(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_14_NAME)) { obj = new LogMessageData_Partition_14(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_15_NAME)) { obj = new LogMessageData_Partition_15(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_16_NAME)) { obj = new LogMessageData_Partition_16(); } else if (tableName.equals(DataBase.LOGMESSAGEDATA_PARTITION_17_NAME)) { obj = new LogMessageData_Partition_17(); } // loop through all the columns for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (column_name.equals("ID")) { obj.setId(rs.getBigDecimal(column_name).longValueExact()); } if (column_name.equals("CONTENT")) { obj.setContent(rs.getNString(column_name)); } if (column_name.equals("LABEL")) { obj.setLabel(rs.getNString(column_name)); } if (column_name.equals("MIMETYPE")) { obj.setMimeType(rs.getNString(column_name)); } if (column_name.equals("MODIFIED")) { obj.setModified(rs.getBoolean(column_name)); } if (column_name.equals("CONTENTSIZE")) { obj.setContentSize(rs.getBigDecimal(column_name).longValueExact()); } if (column_name.equals("SEARCHABLE")) { obj.setSearchable(rs.getBoolean(column_name)); } if (column_name.equals("UTCLOCALTIMESTAMP")) { obj.setUtcLocalTimeStamp(rs.getTimestamp(column_name)); } if (column_name.equals("UTCSERVERTIMESTAMP")) { obj.setUtcServerTimeStamp(rs.getTimestamp(column_name)); } // if (column_name.equals("LOGMESSAGE_ID")) { // obj.setUtcServerTimeStamp(rs.getTimestamp(column_name)); // } } //end foreach logMessageData.add(obj); } //end while } catch (Exception e) { e.printStackTrace(); } return logMessageData; }
From source file:jp.mathes.databaseWiki.db.postgres.PostgresBackend.java
@SuppressWarnings({ "rawtypes", "unchecked" })
@Override/*w w w . j a v a 2 s . com*/
public Document getDocument(final String user, final String password, final String db, final String table,
final String name, final boolean allowEmpty, final Map<String, String[]> defaultFieldValues)
throws BackendException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = this.connectToDB(user, password, db);
PostgresDocument doc = this.createEmptyDocument(conn, table, name, db);
String nameField = this.getNameField(conn, table, db);
st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
String queryString = String.format("select * from \"%s\".\"%s\" where \"%s\"='%s'",
this.getSchemaName(table, db), this.getPlainTableName(table), nameField, name);
this.logString(queryString, user);
rs = st.executeQuery(queryString);
if (this.getNumRows(rs) == 0) {
if (allowEmpty) {
if (defaultFieldValues != null) {
for (String key : defaultFieldValues.keySet()) {
Field field = doc.getAllFields().get(key);
if (field != null) {
if (field.getType() == FieldType.string || field.getType() == FieldType.text) {
field.setValue(defaultFieldValues.get(key)[0]);
} else if (field.getType() == FieldType.date) {
try {
field.setValue(new SimpleDateFormat("y-M-d")
.parse(defaultFieldValues.get(key)[0]));
} catch (ParseException e) {
throw new BackendException(e);
}
} else if (field.getType() == FieldType.dec) {
field.setValue(Integer.valueOf(defaultFieldValues.get(key)[0]));
} else if (field.getType() == FieldType.num) {
field.setValue(Double.valueOf(defaultFieldValues.get(key)[0]));
}
}
}
}
return doc;
} else {
throw new DocumentNotFoundException(String.format("Document '%s' not found in table '%s.%s'.",
name, this.getSchemaName(table, db), this.getPlainTableName(table)));
}
}
rs.next();
ResultSetMetaData md = rs.getMetaData();
for (int i = 1; i <= md.getColumnCount(); i++) {
Field field = doc.getAllFields().get(md.getColumnName(i));
if (field.getType() == FieldType.string || field.getType() == FieldType.text) {
field.setValue(rs.getString(i));
} else if (field.getType() == FieldType.date) {
field.setValue(rs.getDate(i));
} else if (field.getType() == FieldType.dec) {
field.setValue(rs.getInt(i));
} else if (field.getType() == FieldType.num) {
field.setValue(rs.getDouble(i));
}
}
return doc;
} catch (SQLException e) {
throw new BackendException(e);
} catch (ClassNotFoundException e) {
throw new BackendException(e);
} finally {
DbUtils.closeQuietly(rs);
DbUtils.closeQuietly(st);
DbUtils.closeQuietly(conn);
}
}
From source file:com.micromux.cassandra.jdbc.JdbcRegressionTest.java
/** * Create a column group and confirm that the {@code ResultSetMetaData} works. *//*from w w w . ja va 2 s . co m*/ @Test public void testResultSetMetaData() throws Exception { Statement stmt = con.createStatement(); // Create the target Column family String createCF = "CREATE COLUMNFAMILY t33 (k int PRIMARY KEY," + "c text " + ") ;"; stmt.execute(createCF); stmt.close(); con.close(); // open it up again to see the new CF con = DriverManager .getConnection(String.format("jdbc:cassandra://%s:%d/%s?%s", HOST, PORT, KEYSPACE, OPTIONS)); // paraphrase of the snippet from the ISSUE #33 provided test PreparedStatement statement = con.prepareStatement("update t33 set c=? where k=123"); statement.setString(1, "mark"); statement.executeUpdate(); ResultSet result = statement.executeQuery("SELECT k, c FROM t33;"); ResultSetMetaData metadata = result.getMetaData(); int colCount = metadata.getColumnCount(); System.out.println("Test Issue #33"); DatabaseMetaData md = con.getMetaData(); System.out.println(); System.out.println("--------------"); System.out.println("Driver Version : " + md.getDriverVersion()); System.out.println("DB Version : " + md.getDatabaseProductVersion()); System.out.println("Catalog term : " + md.getCatalogTerm()); System.out.println("Catalog : " + con.getCatalog()); System.out.println("Schema term : " + md.getSchemaTerm()); System.out.println("--------------"); while (result.next()) { metadata = result.getMetaData(); colCount = metadata.getColumnCount(); assertEquals("Total column count should match schema for t33", 2, metadata.getColumnCount()); System.out.printf("(%d) ", result.getRow()); for (int i = 1; i <= colCount; i++) { System.out.print(showColumn(i, result) + " "); switch (i) { case 1: assertEquals("First Column: k", "k", metadata.getColumnName(1)); assertEquals("First Column Type: int", Types.INTEGER, metadata.getColumnType(1)); break; case 2: assertEquals("Second Column: c", "c", metadata.getColumnName(2)); assertEquals("Second Column Type: text", Types.NVARCHAR, metadata.getColumnType(2)); break; } } System.out.println(); } }
From source file:CSVTools.CsvToolsApi.java
public void writeResultSetIntoCSVFile(CachedRowSet resultSet, String path) { PrintWriter csvWriter = null; try {/*from www. j av a 2 s . com*/ csvWriter = new PrintWriter(new File(path)); ResultSetMetaData meta = resultSet.getMetaData(); int numberOfColumns = meta.getColumnCount(); String dataHeaders = "\"" + meta.getColumnName(1) + "\""; for (int i = 2; i < numberOfColumns + 1; i++) { dataHeaders += ",\"" + meta.getColumnName(i) + "\""; } csvWriter.println(dataHeaders); resultSet.beforeFirst(); while (resultSet.next()) { String row = "\"" + resultSet.getString(1) + "\""; for (int i = 2; i < numberOfColumns + 1; i++) { row += ",\"" + resultSet.getString(i) + "\""; } csvWriter.println(row); } csvWriter.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } }
From source file:com.dbmojo.QueryExecutor.java
/** Execute a query i.e. NOT AN UPDATE. This method handles both * raw SQL and prepared statements./*w w w. jav a 2s . c o m*/ */ private HashMap executeQuery(Connection conn, boolean prepared, String query, String[] values) throws Exception { HashMap qObj = new HashMap(); ArrayList<ArrayList<String>> rowList = new ArrayList<ArrayList<String>>(); ArrayList<String> colList = new ArrayList<String>(); ArrayList<String> typeList = new ArrayList<String>(); ResultSet rset = null; PreparedStatement pstmt = null; Statement stmt = null; String rMessage = ""; try { if (prepared) { pstmt = conn.prepareStatement(query); setPreparedStatementValues(pstmt, values); rset = pstmt.executeQuery(); if (DebugLog.enabled) { DebugLog.add(this, "Prepared statement has been executed"); } } else { stmt = conn.createStatement(); rset = stmt.executeQuery(query); if (DebugLog.enabled) { DebugLog.add(this, "Statement has been executed"); } } final ResultSetMetaData rsetMetaData = rset.getMetaData(); final int numCols = rsetMetaData.getColumnCount(); boolean firstRow = true; //Loop through all the result ROWs while (rset.next()) { ArrayList<String> valList = new ArrayList<String>(); //JSONArray valArray = new JSONArray(); //Loop through all the result COLs for (int i = 1; i <= numCols; i++) { if (firstRow) { colList.add(rsetMetaData.getColumnName(i)); typeList.add(rsetMetaData.getColumnTypeName(i)); } valList.add(rset.getString(i)); } //Add each result row to a list of rows rowList.add(valList); firstRow = false; } if (DebugLog.enabled) { DebugLog.add(this, "Result set JSON created"); } } catch (Exception e) { //If something goes wrong then return the error as the message for the //result. Do not return any rows or column headers final String err = "Couldn't Execute Query: " + e.toString(); if (DebugLog.enabled) { DebugLog.add(this, err); } return Util.getError(err); } finally { //Cleanup up JDBC stuff if (rset != null) { rset.close(); if (DebugLog.enabled) { DebugLog.add(this, "Closing result set"); } } if (pstmt != null) { pstmt.close(); if (DebugLog.enabled) { DebugLog.add(this, "Closing prepared statement"); } } if (stmt != null) { stmt.close(); if (DebugLog.enabled) { DebugLog.add(this, "Closing statement"); } } } //Final JSON for this query is a JSON object //The rows attribute can be in either document or standard format qObj.put("types", typeList); qObj.put("cols", colList); qObj.put("rows", rowList); //No message necessary since everything went off without a hitch qObj.put("message", ""); //If we get this far then we know things are good qObj.put("status", "success"); return qObj; }
From source file:com.xpfriend.fixture.cast.temp.Database.java
private boolean hasIdentityColumnInternal(String tableName) { DbCommand command = createCommand("select * from " + tableName); try {//www. j a v a 2s .c o m ResultSet resultSet = command.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); for (int i = 1; i <= columnCount; i++) { if (metaData.isAutoIncrement(i)) { return true; } } return false; } catch (SQLException e) { throw new ConfigException(e); } finally { command.close(); } }