Example usage for java.sql PreparedStatement getMetaData

List of usage examples for java.sql PreparedStatement getMetaData

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getMetaData

Introduction

In this page you can find the example usage for java.sql PreparedStatement getMetaData.

Prototype

ResultSetMetaData getMetaData() throws SQLException;

Source Link

Document

Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed.

Usage

From source file:me.doshou.admin.monitor.web.controller.SQLExecutorController.java

@PageableDefaults(pageNumber = 0, value = 10)
@RequestMapping(value = "/sql", method = RequestMethod.POST)
public String executeQL(final @RequestParam("sql") String sql, final Model model, final Pageable pageable) {

    model.addAttribute("sessionFactory", HibernateUtils.getSessionFactory(em));

    String lowerCaseSQL = sql.trim().toLowerCase();
    final boolean isDML = lowerCaseSQL.startsWith("insert") || lowerCaseSQL.startsWith("update")
            || lowerCaseSQL.startsWith("delete");
    final boolean isDQL = lowerCaseSQL.startsWith("select");

    if (!isDML && !isDQL) {
        model.addAttribute(Constants.ERROR,
                "SQL????insert?update?delete?select");
        return showSQLForm();
    }//from   ww  w  . j ava 2  s . c om
    try {
        new TransactionTemplate(transactionManager).execute(new TransactionCallback<Void>() {
            @Override
            public Void doInTransaction(TransactionStatus status) {

                if (isDML) {
                    Query query = em.createNativeQuery(sql);
                    int updateCount = query.executeUpdate();
                    model.addAttribute("updateCount", updateCount);
                } else {
                    String findSQL = sql;
                    String countSQL = "select count(*) count from (" + findSQL + ") o";
                    Query countQuery = em.createNativeQuery(countSQL);
                    Query findQuery = em.createNativeQuery(findSQL);
                    findQuery.setFirstResult(pageable.getOffset());
                    findQuery.setMaxResults(pageable.getPageSize());

                    Page page = new PageImpl(findQuery.getResultList(), pageable,
                            ((BigInteger) countQuery.getSingleResult()).longValue());

                    model.addAttribute("resultPage", page);

                    em.unwrap(Session.class).doWork(new Work() {
                        @Override
                        public void execute(final Connection connection) throws SQLException {
                            PreparedStatement psst = connection.prepareStatement(sql);
                            ResultSetMetaData metaData = psst.getMetaData();

                            List<String> columnNames = Lists.newArrayList();
                            for (int i = 1, l = metaData.getColumnCount(); i <= l; i++) {
                                columnNames.add(metaData.getColumnLabel(i));
                            }
                            psst.close();
                            model.addAttribute("columnNames", columnNames);
                        }
                    });
                }

                return null;
            }
        });
    } catch (Exception e) {
        StringWriter sw = new StringWriter();
        e.printStackTrace(new PrintWriter(sw));
        model.addAttribute(Constants.ERROR, sw.toString());
    }

    return showSQLForm();
}

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testPreparedStatementMetaData() throws SQLException {
    PreparedStatement prep = conn.prepareStatement("select * from table(x int = ?, name varchar = ?)");
    ResultSetMetaData meta = prep.getMetaData();
    assertEquals(2, meta.getColumnCount());
    assertEquals("INTEGER", meta.getColumnTypeName(1));
    assertEquals("VARCHAR", meta.getColumnTypeName(2));
    prep = conn.prepareStatement("call 1");
    meta = prep.getMetaData();//from  w ww. j av a 2  s. c om
    assertEquals(1, meta.getColumnCount());
    assertEquals("INTEGER", meta.getColumnTypeName(1));
}

From source file:eionet.cr.dao.virtuoso.VirtuosoStagingDatabaseDAO.java

@Override
public Set<String> prepareStatement(String sql, String dbName) throws DAOException {

    if (StringUtils.isBlank(sql)) {
        throw new IllegalArgumentException("The given SQL statement must not be blank!");
    }//from  w  w w . j a  v a 2 s  . c om

    LinkedHashSet<String> result = new LinkedHashSet<String>();

    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        conn = getSQLConnection(dbName);
        pstmt = SQLUtil.prepareStatement(sql, null, conn);
        ResultSetMetaData metaData = pstmt.getMetaData();
        int colCount = metaData.getColumnCount();
        for (int i = 1; i <= colCount; i++) {
            String colName = metaData.getColumnName(i);
            result.add(colName);
        }
    } catch (SQLException e) {
        throw new DAOException(e.getMessage(), e);
    } finally {
        SQLUtil.close(pstmt);
        SQLUtil.close(conn);
    }

    return result;
}

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testGetMoreResults() throws SQLException {
    Statement stat = conn.createStatement();
    PreparedStatement prep;
    ResultSet rs;//from www  . java 2 s . c  om
    stat.execute("CREATE TABLE TEST(ID INT)");
    stat.execute("INSERT INTO TEST VALUES(1)");

    prep = conn.prepareStatement("SELECT * FROM TEST");
    // just to check if it doesn't throw an exception - it may be null
    prep.getMetaData();
    assertTrue(prep.execute());
    rs = prep.getResultSet();
    assertFalse(prep.getMoreResults());
    assertEquals(-1, prep.getUpdateCount());
    // supposed to be closed now
    assertThrows(SQLErrorCode.OBJECT_CLOSED, rs).next();
    assertEquals(-1, prep.getUpdateCount());

    prep = conn.prepareStatement("UPDATE TEST SET ID = 2");
    assertFalse(prep.execute());
    assertEquals(1, prep.getUpdateCount());
    assertFalse(prep.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
    assertEquals(-1, prep.getUpdateCount());
    // supposed to be closed now
    assertThrows(SQLErrorCode.OBJECT_CLOSED, rs).next();
    assertEquals(-1, prep.getUpdateCount());

    prep = conn.prepareStatement("DELETE FROM TEST");
    prep.executeUpdate();
    assertFalse(prep.getMoreResults());
    assertEquals(-1, prep.getUpdateCount());
}

From source file:com.espertech.esper.epl.db.DatabasePollingViewableFactory.java

private static QueryMetaData getPreparedStmtMetadata(Connection connection, String[] parameters,
        String preparedStatementText, ColumnSettings metadataSetting) throws ExprValidationException {
    PreparedStatement prepared;
    try {//from  w w w . j  ava 2  s  .  c  o m
        if (log.isInfoEnabled()) {
            log.info(".getPreparedStmtMetadata Preparing statement '" + preparedStatementText + "'");
        }
        prepared = connection.prepareStatement(preparedStatementText);
    } catch (SQLException ex) {
        String text = "Error preparing statement '" + preparedStatementText + '\'';
        log.error(text, ex);
        throw new ExprValidationException(text + ", reason: " + ex.getMessage());
    }

    // Interrogate prepared statement - parameters and result
    List<String> inputParameters = new LinkedList<String>();
    try {
        ParameterMetaData parameterMetaData = prepared.getParameterMetaData();
        inputParameters.addAll(Arrays.asList(parameters).subList(0, parameterMetaData.getParameterCount()));
    } catch (Exception ex) {
        try {
            prepared.close();
        } catch (SQLException e) {
            // don't handle
        }
        String text = "Error obtaining parameter metadata from prepared statement, consider turning off metadata interrogation via configuration, for statement '"
                + preparedStatementText + '\'';
        log.error(text, ex);
        throw new ExprValidationException(text + ", please check the statement, reason: " + ex.getMessage());
    }

    Map<String, DBOutputTypeDesc> outputProperties;
    try {
        outputProperties = compileResultMetaData(prepared.getMetaData(), metadataSetting);
    } catch (SQLException ex) {
        try {
            prepared.close();
        } catch (SQLException e) {
            // don't handle
        }
        String text = "Error in statement '" + preparedStatementText
                + "', failed to obtain result metadata, consider turning off metadata interrogation via configuration";
        log.error(text, ex);
        throw new ExprValidationException(text + ", please check the statement, reason: " + ex.getMessage());
    }

    if (log.isDebugEnabled()) {
        log.debug(".createDBEventStream in=" + inputParameters.toString() + " out="
                + outputProperties.toString());
    }

    // Close statement
    try {
        prepared.close();
    } catch (SQLException e) {
        String text = "Error closing prepared statement";
        log.error(text, e);
        throw new ExprValidationException(text + ", reason: " + e.getMessage());
    }

    return new QueryMetaData(inputParameters, outputProperties);
}

From source file:net.sf.farrago.namespace.jdbc.MedJdbcDataServer.java

/**
 * Tests whether a remote SQL query is valid by attempting
 * to prepare it.  This is intended for use by pushdown rules
 * constructing remote SQL from fragments of relational algebra.
 *
 * @param sqlNode SQL query to be tested
 *
 * @return true if statement is valid/*from   w ww  .ja  v  a  2s  .c  om*/
 */
protected boolean isRemoteSqlValid(SqlNode sqlNode) {
    if (assumePushdownValid) {
        return true;
    }
    try {
        SqlDialect dialect = SqlDialect.create(getDatabaseMetaData());
        SqlString sql = sqlNode.toSqlString(dialect);
        sql = MedJdbcNameDirectory.normalizeQueryString(sql);

        // test if sql can be executed against source
        ResultSet rs = null;
        PreparedStatement ps = null;
        Statement testStatement = null;
        try {
            // Workaround for Oracle JDBC thin driver, where
            // PreparedStatement.getMetaData does not actually get metadata
            // before execution
            if (dialect.getDatabaseProduct() == SqlDialect.DatabaseProduct.ORACLE) {
                SqlBuilder buf = new SqlBuilder(dialect);
                buf.append(" DECLARE" + "   test_cursor integer;" + " BEGIN"
                        + "   test_cursor := dbms_sql.open_cursor;" + "   dbms_sql.parse(test_cursor, ");
                buf.literal(dialect.quoteStringLiteral(sql.getSql()));
                buf.append(", " + "   dbms_sql.native);" + "   dbms_sql.close_cursor(test_cursor);"
                        + " EXCEPTION" + " WHEN OTHERS THEN" + "   dbms_sql.close_cursor(test_cursor);"
                        + "   RAISE;" + " END;");
                testStatement = getConnection().createStatement();
                SqlString sqlTest = buf.toSqlString();
                rs = testStatement.executeQuery(sqlTest.getSql());
            } else {
                ps = getConnection().prepareStatement(sql.getSql());
                if (ps != null) {
                    if (ps.getMetaData() == null) {
                        return false;
                    }
                }
            }
        } catch (SQLException ex) {
            return false;
        } catch (RuntimeException ex) {
            return false;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (testStatement != null) {
                    testStatement.close();
                }
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException sqe) {
            }
        }
    } catch (SQLException ex) {
        return false;
    }
    return true;
}

From source file:org.apache.calcite.avatica.jdbc.JdbcMeta.java

public StatementHandle prepare(ConnectionHandle ch, String sql, long maxRowCount) {
    try {/* ww  w.  j av  a2 s .c  om*/
        final Connection conn = getConnection(ch.id);
        final PreparedStatement statement = conn.prepareStatement(sql);
        final int id = System.identityHashCode(statement);
        statementCache.put(id, new StatementInfo(statement));
        StatementHandle h = new StatementHandle(ch.id, id,
                signature(statement.getMetaData(), statement.getParameterMetaData(), sql));
        if (LOG.isTraceEnabled()) {
            LOG.trace("prepared statement " + h);
        }
        return h;
    } catch (SQLException e) {
        throw propagate(e);
    }
}

From source file:org.apache.sqoop.connector.jdbc.oracle.util.OracleQueries.java

public static List<Column> getColDataTypes(Connection connection, OracleTable table, List<String> colNames)
        throws SQLException {
    List<Column> result = new ArrayList<Column>();
    StringBuilder sb = new StringBuilder();
    sb.append("SELECT ");
    for (int idx = 0; idx < colNames.size(); idx++) {
        if (idx > 0) {
            sb.append(",");
        }//from   w w  w  .j ava2 s.co  m
        sb.append(colNames.get(idx));
    }
    sb.append(String.format(" FROM %s WHERE 0=1", table.toString()));

    String sql = sb.toString();
    PreparedStatement statement = connection.prepareStatement(sql);
    try {
        ResultSetMetaData metadata = statement.getMetaData();
        int numCols = metadata.getColumnCount();
        for (int i = 1; i < numCols + 1; i++) {
            String colName = metadata.getColumnName(i);
            Column oracleColumn = OracleSqlTypesUtils.sqlTypeToSchemaType(metadata.getColumnType(i), colName,
                    metadata.getPrecision(i), metadata.getScale(i));

            result.add(oracleColumn);
        }
    } finally {
        statement.close();
    }
    return result;
}

From source file:org.athrun.android.framework.agent.common.DBCommandRunner.java

private String execute(String command) throws Exception {
    String[] args = command.split("" + (char) 18);
    String _command = args[0];//from   w w w. j  a va 2 s . c  o  m
    PreparedStatement prepareStatement = connection.prepareStatement(_command);
    ResultSet rs = prepareStatement.executeQuery();// execute(),executeBatch(),executeUpdate()
    StringBuilder sb = new StringBuilder();

    java.sql.ResultSetMetaData rsmd = prepareStatement.getMetaData();
    //
    int columnCount = rsmd.getColumnCount();
    //
    List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
    Map<String, Object> data = null;
    String recordIndex = null;
    String attribute = null;

    if (args.length == 3) {
        recordIndex = args[1];
        attribute = args[2];
    }
    //

    while (rs.next()) {
        data = new HashMap<String, Object>();
        //
        for (int i = 1; i <= columnCount; i++) {
            data.put(rsmd.getColumnLabel(i), rs.getObject(rsmd.getColumnLabel(i)));
        }
        // ??MapList

        datas.add(data);
    }
    if (recordIndex != null && attribute != null) {
        String value = getAttributeRecord(datas, recordIndex, attribute);
        sb.append(value);
        sb.append("\n");
    }

    rs.close();
    prepareStatement.close();

    return sb.toString();
}

From source file:org.pentaho.di.core.database.Database.java

/**
 * @param ps//  ww  w .j  a va2s. co  m
 *          The prepared insert statement to use
 * @return The generated keys in auto-increment fields
 * @throws KettleDatabaseException
 *           in case something goes wrong retrieving the keys.
 */
public RowMetaAndData getGeneratedKeys(PreparedStatement ps) throws KettleDatabaseException {
    ResultSet keys = null;
    try {
        keys = ps.getGeneratedKeys(); // 1 row of keys
        ResultSetMetaData resultSetMetaData = keys.getMetaData();
        if (resultSetMetaData == null) {
            resultSetMetaData = ps.getMetaData();
        }
        RowMetaInterface rowMeta;
        if (resultSetMetaData == null) {
            rowMeta = new RowMeta();
            rowMeta.addValueMeta(new ValueMeta("ai-key", ValueMetaInterface.TYPE_INTEGER));
        } else {
            rowMeta = getRowInfo(resultSetMetaData, false, false);
        }

        return new RowMetaAndData(rowMeta, getRow(keys, resultSetMetaData, rowMeta));
    } catch (Exception ex) {
        throw new KettleDatabaseException("Unable to retrieve key(s) from auto-increment field(s)", ex);
    } finally {
        if (keys != null) {
            try {
                keys.close();
            } catch (SQLException e) {
                throw new KettleDatabaseException("Unable to close resultset of auto-generated keys", e);
            }
        }
    }
}