List of usage examples for java.sql ParameterMetaData getParameterCount
int getParameterCount() throws SQLException;
PreparedStatement object for which this ParameterMetaData object contains information. From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getHSQLConnection(); System.out.println("Got Connection."); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int,name varchar);"); st.executeUpdate("create view surveyView as (select * from survey);"); st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')"); PreparedStatement pstmt = null; ParameterMetaData paramMetaData = null; String query = "select * from survey where id > ? and name = ?"; pstmt = conn.prepareStatement(query); paramMetaData = pstmt.getParameterMetaData(); if (paramMetaData == null) { System.out.println("db vendor does NOT support ParameterMetaData"); } else {/*w ww . j av a2s . c o m*/ System.out.println("db vendor supports ParameterMetaData"); // find out the number of dynamic parameters int paramCount = paramMetaData.getParameterCount(); System.out.println("paramCount=" + paramCount); System.out.println("-------------------"); for (int param = 1; param <= paramCount; param++) { System.out.println("param number=" + param); int paramMode = paramMetaData.getParameterMode(param); System.out.println("param mode=" + paramMode); if (paramMode == ParameterMetaData.parameterModeOut) { System.out.println("the parameter's mode is OUT."); } else if (paramMode == ParameterMetaData.parameterModeIn) { System.out.println("the parameter's mode is IN."); } else if (paramMode == ParameterMetaData.parameterModeInOut) { System.out.println("the parameter's mode is INOUT."); } else { System.out.println("the mode of a parameter is unknown."); } } } pstmt.close(); conn.close(); }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getHSQLConnection(); Statement st = conn.createStatement(); // st.executeUpdate("drop table survey;"); st.executeUpdate("create table survey (id int,name varchar(30));"); st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')"); PreparedStatement pstmt = null; ParameterMetaData paramMetaData = null; String query = "select * from survey where id > ? "; System.out.println("conn=" + conn); pstmt = conn.prepareStatement(query); paramMetaData = pstmt.getParameterMetaData(); if (paramMetaData == null) { System.out.println("db vendor does NOT support ParameterMetaData"); } else {/*w w w.jav a 2 s . co m*/ System.out.println("db vendor supports ParameterMetaData"); // find out the number of dynamic parameters int paramCount = paramMetaData.getParameterCount(); System.out.println("paramCount=" + paramCount); } pstmt.close(); conn.close(); }
From source file:GetParamMetaData.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;/* www . j a va2s. c o m*/ PreparedStatement pstmt; ParameterMetaData pmd; String sql = "UPDATE COFFEES SET SALES = ? " + "WHERE COF_NAME = ?"; try { Class.forName("myDriver.ClassName"); } catch (java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); } try { con = DriverManager.getConnection(url, "myLogin", "myPassword"); pstmt = con.prepareStatement(sql); pmd = pstmt.getParameterMetaData(); int totalDigits = pmd.getPrecision(1); int digitsAfterDecimal = pmd.getScale(1); boolean b = pmd.isSigned(1); System.out.println("The first parameter "); System.out.println(" has precision " + totalDigits); System.out.println(" has scale " + digitsAfterDecimal); System.out.println(" may be a signed number " + b); int count = pmd.getParameterCount(); System.out.println("count is " + count); for (int i = 1; i <= count; i++) { int type = pmd.getParameterType(i); String typeName = pmd.getParameterTypeName(i); System.out.println("Parameter " + i + ":"); System.out.println(" type is " + type); System.out.println(" type name is " + typeName); } pstmt.close(); con.close(); } catch (Exception e) { e.printStackTrace(); } }
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;// w w w .jav a 2s . c o m try { 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:org.tradex.jdbc.JDBCHelper.java
/** * Generates a SqlParameterSource for the passed SQL text and supplied binds * @param sql The SQL to bind to/* w ww . j a v a2 s . c om*/ * @param binds The supplied variables to bind * @return a SqlParameterSource */ public SqlParameterSource getBinds(String sql, final Object... binds) { final MapSqlParameterSource sqlParamSource = new MapSqlParameterSource(); int[] parameterTypes = TYPE_CACHE.get(sql); if (parameterTypes == null) { synchronized (TYPE_CACHE) { parameterTypes = TYPE_CACHE.get(sql); if (parameterTypes == null) { Connection conn = null; PreparedStatement ps = null; try { conn = ds.getConnection(); ps = conn.prepareStatement(NamedParameterUtils.parseSqlStatementIntoString(sql).toString()); ParameterMetaData pmd = ps.getParameterMetaData(); int paramCount = pmd.getParameterCount(); if (paramCount > 0 && (binds == null || binds.length != paramCount)) { throw new RuntimeException("Bind Count [" + (binds == null ? 0 : binds.length) + "] was not equal to parameter count [" + paramCount + "]"); } parameterTypes = new int[paramCount]; for (int i = 0; i < paramCount; i++) { parameterTypes[i] = pmd.getParameterType(i + 1); } } catch (RuntimeException re) { throw re; } catch (Exception e) { throw new RuntimeException("Failed to get binds for [" + sql + "]", e); } finally { try { ps.close(); } catch (Exception e) { } try { conn.close(); } catch (Exception e) { } } } TYPE_CACHE.put(sql, parameterTypes); } } for (int i = 0; i < parameterTypes.length; i++) { sqlParamSource.addValue("" + i, binds[i], parameterTypes[i]); } return sqlParamSource; }
From source file:QueryRunner.java
/** * Fill the <code>PreparedStatement</code> replacement parameters with * the given objects.//from w ww . j a va2 s. c o m * @param stmt PreparedStatement to fill * @param params Query replacement parameters; <code>null</code> is a valid * value to pass in. * @throws SQLException if a database access error occurs */ public void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException { if (params == null) { return; } ParameterMetaData pmd = stmt.getParameterMetaData(); if (pmd.getParameterCount() < params.length) { throw new SQLException( "Too many parameters: expected " + pmd.getParameterCount() + ", was given " + params.length); } for (int i = 0; i < params.length; i++) { if (params[i] != null) { stmt.setObject(i + 1, params[i]); } else { // VARCHAR works with many drivers regardless // of the actual column type. Oddly, NULL and // OTHER don't work with Oracle's drivers. int sqlType = Types.VARCHAR; if (!pmdKnownBroken) { try { sqlType = pmd.getParameterType(i + 1); } catch (SQLException e) { pmdKnownBroken = true; } } stmt.setNull(i + 1, sqlType); } } }
From source file:com.cisco.dvbu.ps.deploytool.services.RegressionManagerUtils.java
/** * Similar to the same method in original pubtest utility, but doesn't throw an exception if 0 rows are returned * and uses existing(established) JDBC connection corresponding to its published datasource name. * // ww w . j av a2 s . co m */ public static String executeProcedure(RegressionItem item, HashMap<String, Connection> cisConnections, String outputFile, String delimiter, String printOutputType) throws CompositeException { // Set the command and action name String command = "executeProcedure"; String actionName = "REGRESSION_TEST"; int rows = 0; String result = null; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; start = System.currentTimeMillis(); long firstRowLatency = 0L; // Don't execute if -noop (NO_OPERATION) has been set otherwise execute under normal operation. if (CommonUtils.isExecOperation()) { try { conn = getJdbcConnection(item.database, cisConnections); // don't need to check for null here. String URL = null; String userName = null; if (conn.getMetaData() != null) { if (conn.getMetaData().getURL() != null) URL = conn.getMetaData().getURL(); if (conn.getMetaData().getUserName() != null) userName = conn.getMetaData().getUserName(); } RegressionManagerUtils.printOutputStr(printOutputType, "debug", "RegressionManagerUtils.executeQuery(item, cisConnections, outputFile, delimiter, printOutputType). item.database=" + item.database + " cisConnections.URL=" + URL + " cisConnections.userName=" + userName + " outputFile=" + outputFile + " delimiter=" + delimiter + " printOutputType=" + printOutputType, ""); RegressionManagerUtils.printOutputStr(printOutputType, "debug", "DEBUG: connection to DB successful", ""); String query = item.input.replaceAll("\n", " "); // Convert a CALL statement into a SELECT * FROM statement // { CALL SCH1.LookupProduct( 3 ) } --> SCH1.LookupProduce( 3 ) if (query.toUpperCase().contains("CALL")) { query = "SELECT * FROM " + RegressionManagerUtils.getProcedure(query); ; } // Prepare the query stmt = (CallableStatement) conn.prepareCall(query); // Register output parameter types for (int i = 0; i < item.outTypes.length; i++) { if (!"-".equals(item.outTypes[i])) { int jdbcType = -1; try { jdbcType = Types.class.getField(item.outTypes[i]).getInt(null); } catch (Exception e) { RegressionManagerUtils.error(item.lineNum, item.outTypes[i], "No such JDBC type in java.sql.Types"); } stmt.registerOutParameter(i + 1, jdbcType); } } stmt.executeQuery(); // Print scalars ParameterMetaData pmd = stmt.getParameterMetaData(); int params = pmd.getParameterCount(); boolean addSep = false; String content = ""; for (int i = 0; i < params; i++) { if (addSep) { content += delimiter; } if (stmt.getObject(i + 1) != null) content += stmt.getObject(i + 1).toString(); else content += ""; addSep = true; } if (outputFile != null) CommonUtils.appendContentToFile(outputFile, content); RegressionManagerUtils.printOutputStr(printOutputType, "results", content, ""); // Get the result cursor and metadata cursor rs = stmt.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData(); int columns = rsmd.getColumnCount(); // Get the column metadata addSep = false; content = ""; for (int i = 0; i < columns; i++) { if (addSep) { content += delimiter; } if (rsmd.getColumnName(i + 1) != null) content += rsmd.getColumnName(i + 1).toString(); else content += ""; addSep = true; } if (outputFile != null) CommonUtils.appendContentToFile(outputFile, content); RegressionManagerUtils.printOutputStr(printOutputType, "results", content, ""); // Print cursors boolean firstRow = true; while (rs != null) { // Read the values while (rs.next()) { if (firstRow) { firstRowLatency = System.currentTimeMillis() - start; firstRow = false; } addSep = false; content = ""; for (int i = 0; i < columns; i++) { if (addSep) { content += delimiter; } if (rs.getObject(i + 1) != null) content += rs.getObject(i + 1).toString(); else content += ""; addSep = true; } if (outputFile != null) CommonUtils.appendContentToFile(outputFile, content); RegressionManagerUtils.printOutputStr(printOutputType, "results", content, ""); rows++; } stmt.getMoreResults(); rs = stmt.getResultSet(); } } catch (SQLException e) { throw new CompositeException("executeProcedure(): " + e.getMessage()); } catch (Exception e) { throw new CompositeException("executeProcedure(): " + e.getMessage()); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } } catch (SQLException e) { rs = null; stmt = null; throw new CompositeException( "executeProcedure(): unable to close ResultSet or Statement" + e.getMessage()); } } RegressionManagerUtils.printOutputStr(printOutputType, "results", "\nCompleted executeProcedure()", ""); } else { logger.info("\n\nWARNING - NO_OPERATION: COMMAND [" + command + "], ACTION [" + actionName + "] WAS NOT PERFORMED.\n"); } // <rows>:<firstRowLatency> result = "" + rows + ":" + firstRowLatency; return result; /* Note: to process this result string on the client invocation side use the following pattern: * * String result = RegressionManagerUtils.executeQuery(item, cisConnections, outputFile, delim, printOutputType, "results"); String results[] = result.split(":"); if (results.length > 1) { rowCount = Integer.valueOf(results[0]); firstRowLatency.addAndGet(Long.parseLong(results[1])); } */ }
From source file:dbutils.DbUtilsTemplate.java
public void fillStatement(PreparedStatement stmt, Object... params) throws SQLException { // check the parameter count, if we can ParameterMetaData pmd = null; if (!pmdKnownBroken) { pmd = stmt.getParameterMetaData(); int stmtCount = pmd.getParameterCount(); int paramsCount = params == null ? 0 : params.length; if (stmtCount != paramsCount) { throw new SQLException( "Wrong number of parameters: expected " + stmtCount + ", was given " + paramsCount); }/*w w w . j a v a 2 s .c om*/ } // nothing to do here if (params == null) { return; } for (int i = 0; i < params.length; i++) { if (params[i] != null) { stmt.setObject(i + 1, params[i]); } else { // VARCHAR works with many drivers regardless // of the actual column type. Oddly, NULL and // OTHER don't work with Oracle's drivers. int sqlType = Types.VARCHAR; if (!pmdKnownBroken) { try { /* * It's not possible for pmdKnownBroken to change from * true to false, (once true, always true) so pmd cannot * be null here. */ sqlType = pmd.getParameterType(i + 1); } catch (SQLException e) { pmdKnownBroken = true; } } stmt.setNull(i + 1, sqlType); } } }
From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java
public void testParameterMetaData() throws SQLException { PreparedStatement prep = conn.prepareStatement("SELECT ?, ?, ? FROM DUAL"); ParameterMetaData pm = prep.getParameterMetaData(); assertEquals("java.lang.String", pm.getParameterClassName(1)); assertEquals("VARCHAR", pm.getParameterTypeName(1)); assertEquals(3, pm.getParameterCount()); assertEquals(ParameterMetaData.parameterModeIn, pm.getParameterMode(1)); assertEquals(Types.VARCHAR, pm.getParameterType(1)); assertEquals(0, pm.getPrecision(1)); assertEquals(0, pm.getScale(1));// w ww . j a v a2 s . c o m assertEquals(ResultSetMetaData.columnNullableUnknown, pm.isNullable(1)); assertEquals(pm.isSigned(1), true); assertThrows(SQLErrorCode.INVALID_VALUE_2, pm).getPrecision(0); assertThrows(SQLErrorCode.INVALID_VALUE_2, pm).getPrecision(4); prep.close(); assertThrows(SQLErrorCode.OBJECT_CLOSED, pm).getPrecision(1); Statement stat = conn.createStatement(); stat.execute("CREATE TABLE TEST3(ID INT, NAME VARCHAR(255), DATA DECIMAL(10,2))"); PreparedStatement prep1 = conn.prepareStatement("UPDATE TEST3 SET ID=?, NAME=?, DATA=?"); PreparedStatement prep2 = conn.prepareStatement("INSERT INTO TEST3 VALUES(?, ?, ?)"); checkParameter(prep1, 1, "java.lang.Integer", 4, "INTEGER", 10, 0); checkParameter(prep1, 2, "java.lang.String", 12, "VARCHAR", 255, 0); checkParameter(prep1, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2); checkParameter(prep2, 1, "java.lang.Integer", 4, "INTEGER", 10, 0); checkParameter(prep2, 2, "java.lang.String", 12, "VARCHAR", 255, 0); checkParameter(prep2, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2); PreparedStatement prep3 = conn .prepareStatement("SELECT * FROM TEST3 WHERE ID=? AND NAME LIKE ? AND ?>DATA"); checkParameter(prep3, 1, "java.lang.Integer", 4, "INTEGER", 10, 0); checkParameter(prep3, 2, "java.lang.String", 12, "VARCHAR", 0, 0); checkParameter(prep3, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2); stat.execute("DROP TABLE TEST3"); }
From source file:edu.utah.further.core.data.logging.LoggingPreparedStatement.java
/** * Log prepared statement. Report parameters separately from the query. * * @throws SQLException/*from w w w .j a va 2 s . c om*/ */ @SuppressWarnings("unused") private void logPreparedStatement() throws SQLException { if (log.isInfoEnabled()) { log.info("Executing prepared statement: " + substituteParameters(parameterValues)); log.info("Executing prepared statement: " + query); final ParameterMetaData parameterMetaData = statement.getParameterMetaData(); for (int i = 1; i <= parameterMetaData.getParameterCount(); i++) { log.info("Parameter " + i + ": type " + parameterMetaData.getParameterType(i) + " value " + parameterValues.get(i)); } } }