Example usage for java.sql ResultSetMetaData getColumnName

List of usage examples for java.sql ResultSetMetaData getColumnName

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSetMetaData
  6. getColumnName

Introduction

In this page you can find the example usage for java.sql ResultSetMetaData getColumnName.

Prototype

String getColumnName(int column) throws SQLException;

Source Link

Document

Get the designated column's name.

Usage

From source file:edu.ku.brc.specify.toycode.RegPivot.java

/**
 * @param newTblName//from  www.j a va 2 s .  c  om
 * @param stmt
 * @param pStmt
 * @param fillSQL
 * @param secInx
 * @param dbFieldTypes
 * @param dbFieldNames
 * @param inxToName
 * @return
 * @throws SQLException
 */
private int fillTrackTable(final String newTblName, final Statement stmt, final PreparedStatement pStmt,
        final String fillSQL, final int secInx, final Vector<Integer> dbFieldTypes,
        final Vector<String> dbFieldNames, final HashMap<Integer, String> inxToName) throws SQLException {
    System.out.println("Filling Track Table.");

    int instCnt = 0;

    System.out.println(fillSQL);

    ResultSet rs = stmt.executeQuery(fillSQL);
    ResultSetMetaData rsmd = rs.getMetaData();

    HashMap<String, Integer> nameToIndex = new HashMap<String, Integer>();
    for (int c = 1; c <= rsmd.getColumnCount(); c++) {
        nameToIndex.put(rsmd.getColumnName(c), c);
        System.out.println(c + " - " + rsmd.getColumnName(c));
    }

    boolean debug = false;

    String prevRegId = null;

    HashMap<String, HashMap<String, Object>> colHash = new HashMap<String, HashMap<String, Object>>();

    HashMap<String, Object> nameToVals = new HashMap<String, Object>();

    while (rs.next()) {
        String regId = rs.getString(1);
        if (prevRegId == null)
            prevRegId = regId;

        for (int i = 1; i < secInx; i++) {
            if (debug)
                System.out.println("Put: " + dbFieldNames.get(i - 1) + "  " + dbFieldTypes.get(i - 1) + "  = "
                        + rs.getObject(i));

            if (dbFieldTypes.get(i - 1) == java.sql.Types.TIMESTAMP) {
                try {
                    String ts = rs.getString(i);
                    if (StringUtils.isNotEmpty(ts) && ts.equals("0000-00-00 00:00:00")) {
                        continue;
                    }
                } catch (Exception ex) {
                    continue;
                }
            }
            nameToVals.put(dbFieldNames.get(i - 1), rs.getObject(i));
        }
        String name = rs.getString(secInx);
        name = StringUtils.replace(name, "(", "_");
        name = StringUtils.replace(name, ")", "_");

        if (name.equals("reg_type")) {
            String strVal = (String) rs.getObject(secInx + 2);
            name = strVal + "_number";

            nameToVals.put(name, regId);
            if (debug)
                System.out.println("Put: " + name + " = " + regId);
        } else {
            Integer intVal = (Integer) rs.getObject(secInx + 1);
            String strVal = (String) rs.getObject(secInx + 2);
            nameToVals.put(name, strVal != null ? strVal : intVal);
            if (debug)
                System.out.println("Put: " + name + " = " + intVal + " / " + strVal);
        }

        if (debug)
            System.out.println("-------------------------------------------");

        if (!prevRegId.equals(regId)) {
            String colNum = (String) nameToVals.get("Collection_number");

            if (StringUtils.isNotEmpty(colNum)) {
                copyHash(colNum, colHash, nameToVals);
            }
            prevRegId = regId;
            nameToVals.clear();
        }
    }

    writeHash(colHash, null, pStmt, dbFieldTypes, dbFieldNames, inxToName);

    String alterSQL = "ALTER TABLE " + newTblName + " ADD Lookup VARCHAR(64) AFTER IP";
    BasicSQLUtils.update(connection, alterSQL);

    alterSQL = "ALTER TABLE " + newTblName + " ADD Country VARCHAR(64) AFTER Lookup";
    BasicSQLUtils.update(connection, alterSQL);

    alterSQL = "ALTER TABLE " + newTblName + " ADD City VARCHAR(64) AFTER Country";
    BasicSQLUtils.update(connection, alterSQL);

    return instCnt;
}

From source file:org.dcm4chee.dashboard.ui.report.display.DisplayReportDiagramPanel.java

@Override
public void onBeforeRender() {
    super.onBeforeRender();

    Connection jdbcConnection = null;
    try {//from  ww w .ja va2  s .  c o m
        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:de.innovationgate.webgate.api.jdbc.custom.JDBCSource.java

/**
 * @param resultSet//w  ww.  j a va 2 s  .co  m
 * @param map
 */
private void pushRowData(ResultSet resultSet, Map map) throws SQLException {

    ResultSetMetaData rsMeta = resultSet.getMetaData();
    for (int idx = rsMeta.getColumnCount(); idx > 0; idx--) {
        String colName = rsMeta.getColumnName(idx);
        resultSet.updateObject(colName, map.get(colName.toLowerCase()));
    }

}

From source file:org.apache.zeppelin.jdbc.JDBCInterpreter.java

private String getResults(ResultSet resultSet, boolean isTableType) throws SQLException {
    ResultSetMetaData md = resultSet.getMetaData();
    StringBuilder msg;//from www . ja v  a2 s. c o m
    if (isTableType) {
        msg = new StringBuilder(TABLE_MAGIC_TAG);
    } else {
        msg = new StringBuilder();
    }

    for (int i = 1; i < md.getColumnCount() + 1; i++) {
        if (i > 1) {
            msg.append(TAB);
        }
        msg.append(replaceReservedChars(md.getColumnName(i)));
    }
    msg.append(NEWLINE);

    int displayRowCount = 0;
    while (resultSet.next() && displayRowCount < getMaxResult()) {
        for (int i = 1; i < md.getColumnCount() + 1; i++) {
            Object resultObject;
            String resultValue;
            resultObject = resultSet.getObject(i);
            if (resultObject == null) {
                resultValue = "null";
            } else {
                resultValue = resultSet.getString(i);
            }
            msg.append(replaceReservedChars(resultValue));
            if (i != md.getColumnCount()) {
                msg.append(TAB);
            }
        }
        msg.append(NEWLINE);
        displayRowCount++;
    }
    return msg.toString();
}

From source file:edu.ku.brc.specify.toycode.RegPivot.java

/**
 * @param newTblName//w  w w.j  av  a  2s.co m
 * @param stmt
 * @param pStmt
 * @param fillSQL
 * @param secInx
 * @param dbFieldTypes
 * @param dbFieldNames
 * @param inxToName
 * @return
 * @throws SQLException
 */
private int fillTrackTableX(final String newTblName, final Statement stmt, final PreparedStatement pStmt,
        final String fillSQL, final int secInx, final Vector<Integer> dbFieldTypes,
        final Vector<String> dbFieldNames, final HashMap<Integer, String> inxToName) throws SQLException {
    System.out.println("Filling Track Table.");
    int instCnt = 0;

    HashMap<String, Object> nameToVals = new HashMap<String, Object>();

    System.out.println(fillSQL);

    String prevId = null;
    ResultSet rs = stmt.executeQuery(fillSQL);
    ResultSetMetaData rsmd = rs.getMetaData();

    HashMap<String, Integer> nameToIndex = new HashMap<String, Integer>();
    for (int c = 1; c <= rsmd.getColumnCount(); c++) {
        nameToIndex.put(rsmd.getColumnName(c), c);
        System.out.println(c + " - " + rsmd.getColumnName(c));
    }

    while (rs.next()) {
        String id = rs.getString(1);
        if (prevId == null)
            prevId = id;

        if (!prevId.equals(id)) {
            for (int i = 1; i < secInx; i++) {
                //System.out.println("Put: "+dbFieldNames.get(i-1)+"  "+dbFieldTypes.get(i-1));//+"  = "+rs.getObject(i));
                if (dbFieldTypes.get(i - 1) == java.sql.Types.TIMESTAMP) {
                    try {
                        String ts = rs.getString(i);
                        if (StringUtils.isNotEmpty(ts) && ts.equals("0000-00-00 00:00:00")) {
                            //nameToVals.put(dbFieldNames.get(i-1), null);
                            continue;
                        }
                    } catch (Exception ex) {
                        nameToVals.put(dbFieldNames.get(i - 1), null);//"2000-01-01 00:00:00");
                        continue;
                    }
                }
                nameToVals.put(dbFieldNames.get(i - 1), rs.getObject(i));
            }

            for (int i = 0; i < dbFieldNames.size(); i++) {
                int fInx = i + 1;
                String name = inxToName.get(i);
                Object value = nameToVals.get(name);

                pStmt.setObject(fInx, null);

                int typ = dbFieldTypes.get(i);

                if (value != null) {
                    switch (typ) {
                    case java.sql.Types.INTEGER:
                        if (value instanceof Integer) {
                            pStmt.setInt(fInx, (Integer) value);
                        }
                        break;

                    case java.sql.Types.VARCHAR:
                        if (value instanceof String) {
                            pStmt.setString(fInx, (String) value);
                        }
                        break;

                    case java.sql.Types.TIMESTAMP: {
                        if (value instanceof Timestamp) {
                            pStmt.setTimestamp(fInx, (Timestamp) value);
                        }
                        break;
                    }
                    }
                } else {
                    pStmt.setObject(fInx, null);
                }
            }
            pStmt.executeUpdate();

            prevId = id;
            nameToVals.clear();
        }

        String name = rs.getString(secInx);
        name = StringUtils.replace(name, "(", "_");
        name = StringUtils.replace(name, ")", "_");

        Integer intVal = (Integer) rs.getObject(secInx + 1);
        String strVal = (String) rs.getObject(secInx + 2);
        nameToVals.put(name, strVal != null ? strVal : intVal);
    }

    String alterSQL = "ALTER TABLE " + newTblName + " ADD Lookup VARCHAR(64) AFTER IP";
    BasicSQLUtils.update(connection, alterSQL);

    alterSQL = "ALTER TABLE " + newTblName + " ADD Country VARCHAR(64) AFTER Lookup";
    BasicSQLUtils.update(connection, alterSQL);

    alterSQL = "ALTER TABLE " + newTblName + " ADD City VARCHAR(64) AFTER Country";
    BasicSQLUtils.update(connection, alterSQL);

    return instCnt;
}

From source file:com.ToResultSet.java

@Path("/input")
@GET//from  ww  w  .j a  va  2 s  .c o  m
@Produces(MediaType.APPLICATION_JSON)
public String toResultSet(@QueryParam("budget") String x, @QueryParam("maxbudget") String y,
        @QueryParam("location") String location, @QueryParam("date") String minDateString)
        throws ParserConfigurationException, TransformerException, ParseException {
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        System.out.println("Fehler bei MySQL-JDBC-Bridge" + e);

    }

    //SQL Query wird hier erzeugt je nach dem Daten, die angegeben werden

    try {

        int minBudget = 0;
        int maxBudget = 0;
        try {

            minBudget = Integer.valueOf(x);
        } catch (NumberFormatException e) {
            if (x.length() > 0) {
                return "Ihre Budget soll aus Zahlen bestehen";
            }
        }
        try {

            maxBudget = Integer.valueOf(y);
        } catch (NumberFormatException e) {
            if (y.length() > 0) {
                return "Ihre Budget soll aus Zahlen bestehen";
            }
        }
        try {
            test = Integer.valueOf(location);
            if (test >= 0) {
                return "Location soll aus String bestehen";
            }
        } catch (Exception e) {
        }

        try {
            if (minDateString.substring(2, 3).contains("-") && minDateString.length() > 0) {
                return "Date Format soll yyyy-MM-dd";
            } else {
                java.util.Date date1 = sdf.parse(minDateString);
                minDate = new java.sql.Date(date1.getTime());
            }

        } catch (Exception e) {
            if (minDateString.length() > 0)
                return "Date Format soll yyyy-MM-dd";
        }

        //Connection mit dem SQL wird erzeugt 

        String url = "jdbc:mysql://" + host + "/" + dbName;
        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/location?autoReconnect=true&useSSL=false", "root", "dreamhigh");
        statement = connection.createStatement();

        String sqlQuery = "Select * FROM " + dbTable;

        List<String> whereClause = new ArrayList<>();

        if (minBudget > 0) {
            whereClause.add("Budget >= " + minBudget);
        }
        if (maxBudget > 0) {
            whereClause.add("Budget <= " + maxBudget);
        }
        if (minBudget > maxBudget && maxBudget > 0 && minBudget > 0) {
            return "Minimal Budget soll kleiner als Maximal Budget";
        }

        if (minDate != null) {
            whereClause.add("Date >= '" + minDate + "'");
        }
        if (location != null && !location.isEmpty()) {
            whereClause.add("Location = '" + location + "'");
        }

        //Die Daten werden nach dem Budget absteigend sortiert 

        if (whereClause.size() > 0) {
            sqlQuery += " WHERE " + StringUtils.join(whereClause, " AND ") + " ORDER BY Budget DESC";
        }

        if (whereClause.size() == 0) {
            sqlQuery += " ORDER BY Budget DESC";
        }

        resultSet = statement.executeQuery(sqlQuery);

        int spalten = resultSet.getMetaData().getColumnCount();
        System.out.println("Anzahl Spalten: " + spalten);

        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document doc = builder.newDocument();
        Element results = doc.createElement("Results");
        doc.appendChild(results);

        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/location?autoReconnect=true&useSSL=false", "root", "dreamhigh");
        ResultSetMetaData rsmd = resultSet.getMetaData();
        int colCount = rsmd.getColumnCount();

        while (resultSet.next()) {
            String[] str = new String[8];
            for (int k = 1; k <= spalten; k++) {
                str[k - 1] = resultSet.getString(k);
                result.add(resultSet.getString(k));
            }

            //Result wird hier als XML zuerst erzeugt und dann Json
            Element row = doc.createElement("Row");
            results.appendChild(row);
            for (int ii = 1; ii <= colCount; ii++) {
                String columnName = rsmd.getColumnName(ii);
                Object value = resultSet.getObject(ii);
                Element node = doc.createElement(columnName);

                if (value != null) {

                    node.appendChild(doc.createTextNode(value.toString()));
                    row.appendChild(node);
                }
            }
        }

        System.out.println(getDocumentAsXml(doc));

        try {
            xmlJSONObj = XML.toJSONObject(getDocumentAsXml(doc));

            outPut = xmlJSONObj.toString(4);

            System.out.println(outPut);

            return outPut;
        } catch (JSONException je) {

        }

    } catch (SQLException e) {
        System.out.println("Fehler bei Tabellenabfrage: " + e);

    }
    return outPut;
}

From source file:edu.ku.brc.specify.toycode.RegPivot.java

/**
 * @param newTblName/*from  w ww. j a va 2  s  . c  o  m*/
 * @param stmt
 * @param pStmt
 * @param fillSQL
 * @param secInx
 * @param dbFieldTypes
 * @param dbFieldNames
 * @param inxToName
 * @return
 * @throws SQLException
 */
private int fillRegisterTable(final String newTblName, final Statement stmt, final PreparedStatement pStmt,
        final String fillSQL, final int secInx, final Vector<Integer> dbFieldTypes,
        final Vector<String> dbFieldNames, final HashMap<Integer, String> inxToName) throws SQLException {
    System.out.println("Filling Register Table.");

    int instCnt = 0;

    System.out.println(fillSQL);

    ResultSet rs = stmt.executeQuery(fillSQL);
    ResultSetMetaData rsmd = rs.getMetaData();

    HashMap<String, Integer> nameToIndex = new HashMap<String, Integer>();
    for (int c = 1; c <= rsmd.getColumnCount(); c++) {
        nameToIndex.put(rsmd.getColumnName(c), c);
        System.out.println(c + " - " + rsmd.getColumnName(c));
    }

    //int nameInx = nameToIndex.get("i.Name");
    boolean debug = false;

    String prevRegId = null;

    HashMap<String, HashMap<String, Object>> instHash = new HashMap<String, HashMap<String, Object>>();
    HashMap<String, HashMap<String, Object>> divHash = new HashMap<String, HashMap<String, Object>>();
    HashMap<String, HashMap<String, Object>> dspHash = new HashMap<String, HashMap<String, Object>>();
    HashMap<String, HashMap<String, Object>> colHash = new HashMap<String, HashMap<String, Object>>();

    HashMap<String, Object> nameToVals = new HashMap<String, Object>();

    while (rs.next()) {
        String regId = rs.getString(1);
        if (prevRegId == null)
            prevRegId = regId;

        for (int i = 1; i < secInx; i++) {
            if (debug)
                System.out.println("Put: " + dbFieldNames.get(i - 1) + "  " + dbFieldTypes.get(i - 1) + "  = "
                        + rs.getObject(i));
            nameToVals.put(dbFieldNames.get(i - 1), rs.getObject(i));
        }
        String name = rs.getString(secInx);
        name = StringUtils.replace(name, "(", "_");
        name = StringUtils.replace(name, ")", "_");

        if (name.equals("reg_type")) {
            String strVal = (String) rs.getObject(secInx + 2);
            name = strVal + "_number";

            nameToVals.put(name, regId);
            if (debug)
                System.out.println("Put: " + name + " = " + regId);
        } else {
            Integer intVal = (Integer) rs.getObject(secInx + 1);
            String strVal = (String) rs.getObject(secInx + 2);
            nameToVals.put(name, strVal != null ? strVal : intVal);
            if (debug)
                System.out.println("Put: " + name + " = " + intVal + " / " + strVal);
        }

        if (debug)
            System.out.println("-------------------------------------------");

        if (!prevRegId.equals(regId)) {
            String instNum = (String) nameToVals.get("Institution_number");
            String divNum = (String) nameToVals.get("Division_number");
            String dspNum = (String) nameToVals.get("Discipline_number");
            String colNum = (String) nameToVals.get("Collection_number");

            if (StringUtils.isNotEmpty(instNum)) {
                copyHash(instNum, instHash, nameToVals);
            }

            if (StringUtils.isNotEmpty(divNum)) {
                copyHash(divNum, divHash, nameToVals);
            }

            if (StringUtils.isNotEmpty(dspNum)) {
                copyHash(dspNum, dspHash, nameToVals);
            }

            if (StringUtils.isNotEmpty(colNum)) {
                // 1288612353.83
                String cn = (String) nameToVals.get("Collection_number");
                copyHash(colNum, colHash, nameToVals);
            }

            /*{
            System.err.println("ID is empty:");
            for (String key : nameToVals.keySet())
            {
                System.out.println("--: "+key+" = "+nameToVals.get(key));
            }
            System.err.println("===============");
            }*/
            prevRegId = regId;
            nameToVals.clear();
        }
    }

    writeHash(instHash, 0, pStmt, dbFieldTypes, dbFieldNames, inxToName);
    writeHash(divHash, 1, pStmt, dbFieldTypes, dbFieldNames, inxToName);
    writeHash(dspHash, 2, pStmt, dbFieldTypes, dbFieldNames, inxToName);
    writeHash(colHash, 3, pStmt, dbFieldTypes, dbFieldNames, inxToName);

    String alterSQL = "ALTER TABLE " + newTblName + " ADD Lookup VARCHAR(64) AFTER IP";
    BasicSQLUtils.update(connection, alterSQL);

    alterSQL = "ALTER TABLE " + newTblName + " ADD Country VARCHAR(64) AFTER Lookup";
    BasicSQLUtils.update(connection, alterSQL);

    alterSQL = "ALTER TABLE " + newTblName + " ADD City VARCHAR(64) AFTER Country";
    BasicSQLUtils.update(connection, alterSQL);

    return instCnt;
}

From source file:com.dbmojo.QueryExecutor.java

/** Execute a query i.e. NOT AN UPDATE. This method handles both
  * raw SQL and prepared statements.//from  ww  w. j a  va 2s  . co  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:DatabaseServlet.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 va 2  s  . c o m*/
    Statement stmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsm = null;

    response.setContentType("text/html");
    java.io.PrintWriter out = response.getWriter();
    out.println("<html><head><title>Typical Database Access</title></head><body>");
    out.println("<h2>Database info</h2>");
    out.println("<table border='1'><tr>");

    try {

        //load the database driver
        Class.forName("oracle.jdbc.driver.OracleDriver");

        //The JDBC URL for this Oracle database
        String url = "jdbc:oracle:thin:@142.3.169.178:1521:ORCL";

        //Create the java.sql.Connection to the database
        conn = DriverManager.getConnection(url, "usr", "pass");

        //Create a statement for executing some SQL
        stmt = conn.createStatement();

        rs = stmt.executeQuery(sql);

        rsm = rs.getMetaData();

        int colCount = rsm.getColumnCount();

        //print column names
        for (int i = 1; i <= colCount; ++i) {

            out.println("<th>" + rsm.getColumnName(i) + "</th>");
        }

        out.println("</tr>");

        while (rs.next()) {

            out.println("<tr>");

            for (int i = 1; i <= colCount; ++i)
                out.println("<td>" + rs.getString(i) + "</td>");

            out.println("</tr>");

        }

    } catch (Exception e) {

        throw new ServletException(e.getMessage());

    } finally {

        try {

            stmt.close();
            conn.close();

        } catch (SQLException sqle) {
        }

    }

    out.println("</table><br><br>");

    out.println("</body>");
    out.println("</html>");

    out.close();

}

From source file:org.cloudgraph.rdb.service.JDBCSupport.java

protected List<PropertyPair> fetchRow(PlasmaType type, StringBuilder sql, Connection con) {
    List<PropertyPair> result = new ArrayList<PropertyPair>();
    PreparedStatement statement = null;
    ResultSet rs = null;/* www .  ja  v  a2s  .co m*/
    try {
        if (log.isDebugEnabled()) {
            log.debug("fetch: " + sql.toString());
        }
        statement = con.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, /*
                                                                                       * ResultSet
                                                                                       * .
                                                                                       * TYPE_SCROLL_INSENSITIVE
                                                                                       * ,
                                                                                       */
                ResultSet.CONCUR_READ_ONLY);

        statement.execute();
        rs = statement.getResultSet();
        ResultSetMetaData rsMeta = rs.getMetaData();
        int numcols = rsMeta.getColumnCount();
        int count = 0;
        while (rs.next()) {
            for (int i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnName(i);
                int columnType = rsMeta.getColumnType(i);
                PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName);
                PlasmaProperty valueProp = prop;
                while (!valueProp.getType().isDataType()) {
                    valueProp = getOppositePriKeyProperty(valueProp);
                }
                Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp);
                if (value != null) {
                    PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value);
                    if (!valueProp.equals(prop))
                        pair.setValueProp(valueProp);
                    result.add(pair);
                }
            }
            count++;
        }
        if (log.isDebugEnabled())
            log.debug("returned " + count + " results");
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
    }
    return result;
}