Example usage for java.sql PreparedStatement getConnection

List of usage examples for java.sql PreparedStatement getConnection

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

Introduction

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

Prototype

Connection getConnection() throws SQLException;

Source Link

Document

Retrieves the Connection object that produced this Statement object.

Usage

From source file:org.sakaiproject.lap.dao.Database.java

/**
 * Closes a prepared statement, after returning the borrowed connection
 * /*from ww  w . j a  v a2 s.c o m*/
 * @param preparedStatement the prepared statement
 */
protected void closePreparedStatement(PreparedStatement preparedStatement) {
    try {
        if (preparedStatement != null && !preparedStatement.isClosed()) {
            try {
                returnConnection(preparedStatement.getConnection());
            } catch (Exception e) {
                log.error("Error returning connection to pool: " + e, e);
            } finally {
                preparedStatement.close();
            }
        }
    } catch (Exception e) {
        log.error("Error closing prepared statement: " + e, e);
    }
}

From source file:org.sakaiproject.oaai.dao.Db.java

protected void closePreparedStatement(PreparedStatement preparedStatement) {
    try {/*w ww  .  j  a  v  a2s  .c om*/
        if (preparedStatement != null && !preparedStatement.isClosed()) {
            try {
                returnConnection(preparedStatement.getConnection());
            } catch (Exception e) {
                log.error("Error returning connection to pool: " + e, e);
            } finally {
                preparedStatement.close();
            }
        }
    } catch (Exception e) {
        log.error("Error closing prepared statement: " + e, e);
    }
}

From source file:org.siphon.jssql.SqlExecutor.java

void setArg(PreparedStatement ps, int index, Object arg) throws SQLException, SqlExecutorException,
        UnsupportedDataTypeException, NoSuchMethodException, ScriptException {
    boolean output = false;
    int outputParameterType = 0;
    CallableStatement cs = null;/*from w  w  w  .  java2 s  .c o  m*/
    if (ps instanceof CallableStatement) {
        cs = (CallableStatement) ps;
        if (arg instanceof ScriptObjectMirror && ((ScriptObjectMirror) arg).containsKey("OUT")) {
            ScriptObjectMirror jsarg = ((ScriptObjectMirror) arg);
            outputParameterType = (int) jsarg.get("JDBC_TYPE");
            arg = jsarg.get("VALUE");
            output = true;
        }
    }
    if (output) {
        cs.registerOutParameter(index + 1, outputParameterType);
        if (JsTypeUtil.isNull(arg) || (arg instanceof Double && Double.isNaN((Double) arg))) {
            return;
        }
    }

    if (JsTypeUtil.isNull(arg)) {
        ps.setObject(index + 1, null);
    } else if (arg instanceof CharSequence) {
        ps.setString(index + 1, arg.toString());
    } else if (arg instanceof NativeString) {
        ps.setString(index + 1, arg.toString());
    } else if (arg instanceof Double) { // js number always be
        // Doublebut if its came from
        // JSON.parse since JSON is jdk
        // given global object, it will
        // make Integer and ...
        double d = ((Double) arg).doubleValue();
        if (d == (int) d) {
            ps.setInt(index + 1, (int) d);
        } else if (d == (long) d) {
            ps.setLong(index + 1, (long) d);
        } else {
            ps.setBigDecimal(index + 1, new BigDecimal(d));
        }
    } else if (arg instanceof Integer) {
        ps.setInt(index + 1, (Integer) arg);
    } else if (arg instanceof Long) {
        ps.setLong(index + 1, (Long) arg);
    } else if (arg instanceof Float) {
        ps.setFloat(index + 1, (Float) arg);
    } else if (jsTypeUtil.isNativeDate(arg)) {
        ps.setTimestamp(index + 1, parseDate(arg));
    } else if (arg instanceof ZonedDateTime) {
        ZonedDateTime zdt = (ZonedDateTime) arg;
        ps.setTimestamp(index + 1, new Timestamp(zdt.toInstant().toEpochMilli()));
    } else if (arg instanceof Boolean) {
        ps.setBoolean(index + 1, JsTypeUtil.isTrue(arg));
    } else if (arg instanceof ScriptObjectMirror || arg instanceof ScriptObject) {
        String attr = null;
        Object value = null;
        if (arg instanceof ScriptObjectMirror) {
            ScriptObjectMirror atm = (ScriptObjectMirror) arg;
            if (atm.keySet().contains("toJavaObject")) {
                Object obj = atm.callMember("toJavaObject");
                setArg(ps, index, obj);
                return;
            }

            attr = atm.keySet().iterator().next();
            value = atm.get(attr);
        } else {
            ScriptObject obj = (ScriptObject) arg;
            if (obj.containsKey("toJavaObject")) {
                ScriptObjectMirror atm = (ScriptObjectMirror) jsTypeUtil.toScriptObjectMirror(obj);
                Object result = atm.callMember("toJavaObject");
                setArg(ps, index, result);
                return;
            }
            String[] arr = obj.getOwnKeys(false);
            if (arr.length == 0) {
                throw new SqlExecutorException("js argument " + arg + " (" + arg.getClass() + ") at " + index
                        + " is an empty js object");
            }
            attr = arr[0];
            value = obj.get(attr);
        }

        if ("STRING".equals(attr)) {
            ps.setString(index + 1, String.valueOf(value));
        } else if ("DECIMAL".equals(attr)) {
            if (value instanceof Double) {
                ps.setBigDecimal(index + 1, new BigDecimal((Double) value));
            } else {
                ps.setBigDecimal(index + 1, new BigDecimal(value + ""));
            }
        } else if ("INT".equals(attr)) {
            if (value instanceof Double) {
                if (((Double) value).isNaN()) {
                    ps.setObject(index + 1, null);
                } else {
                    ps.setInt(index + 1, ((Double) value).intValue());
                }
            } else {
                ps.setInt(index + 1, new Integer(value + ""));
            }
        } else if ("BOOLEAN".equals(attr)) {
            ps.setBoolean(index + 1, JsTypeUtil.isTrue(arg));
        } else if ("DOUBLE".equals(attr)) {
            if (value instanceof Double) {
                if (((Double) value).isNaN()) {
                    ps.setObject(index + 1, null);
                } else {
                    ps.setDouble(index + 1, (double) value);
                }
            } else {
                ps.setDouble(index + 1, new Double(value + ""));
            }
        } else if ("FLOAT".equals(attr)) {
            if (value instanceof Double) {
                if (((Double) value).isNaN()) {
                    ps.setObject(index + 1, null);
                } else {
                    ps.setFloat(index + 1, (float) (double) value);
                }
            } else {
                ps.setFloat(index + 1, new Float(value + ""));
            }
        } else if ("DATE".equals(attr)) {
            ps.setTimestamp(index + 1, parseDate(value));
        } else if ("TIME".equals(attr)) {
            ps.setTimestamp(index + 1, parseTime(value));
        } else if ("BINARY".equals(attr)) {
            ps.setBytes(index + 1, parseBinary(value));
        } else if ("CLOB".equals(attr)) {
            Clob clob = ps.getConnection().createClob();
            clob.setString(1, String.valueOf(value));
            ps.setClob(index + 1, clob);
        } else if ("LONG".equals(attr)) {
            if (value instanceof Double) {
                if (((Double) value).isNaN()) {
                    ps.setObject(index + 1, null);
                } else {
                    ps.setLong(index + 1, ((Double) value).longValue());
                }
            } else {
                ps.setLong(index + 1, new Long(value + ""));
            }
        } else if ("OUTCURSOR".equals(attr)) {
            // cs.registerOutParameter(i+1, OracleTypes.CURSOR);
            cs.registerOutParameter(index + 1, -10);
        } else if ("ARRAY".equals(attr)) {
            if (value instanceof NativeArray) {
                ps.setArray(index + 1, createSqlArray(ps.getConnection(), (NativeArray) value));
            } else {
                setArg(ps, index, value); // value is {ARRAY : ['int', e1, e2, ...]}
            }
            // ps.setObject(i+1, createSqlArray(ps.getConnection(),
            // (NativeArray) value));
        } else if ("JSON".equals(attr) || "JSONB".equals(attr)) {
            PGobject obj = new PGobject();
            obj.setType(attr.toLowerCase());
            obj.setValue(this.JSON.tryStringify(value));
            ps.setObject(index + 1, obj);
        } else if ("UUID".equals(attr)) {
            if (value != null) {
                ps.setObject(index + 1, UUID.fromString(value.toString()));
            } else {
                ps.setObject(index + 1, null);
            }
        } else {
            if (this.defaultJsonDbType != null) {
                PGobject obj = new PGobject();
                obj.setType(this.defaultJsonDbType);
                obj.setValue(this.JSON.tryStringify(arg));
                ps.setObject(index + 1, obj);
            } else {
                throw new SqlExecutorException("js argument " + arg + " (" + arg.getClass() + ") not support");
            }
        }
    } else {
        throw new SqlExecutorException(
                "js argument " + arg + " (" + arg.getClass() + ") at " + index + " not support");
    }
}

From source file:org.smallmind.persistence.orm.hibernate.LongArrayUserType.java

@Override
public void nullSafeSet(final PreparedStatement statement, final Object object, final int i,
        final SessionImplementor sessionImplementor) throws HibernateException, SQLException {

    Connection connection = statement.getConnection();

    long[] castObject = (long[]) object;
    Long[] longs = ArrayUtils.toObject(castObject);
    Array array = connection.createArrayOf("long", longs);

    statement.setArray(i, array);/*  www . ja  va 2  s  . co m*/
}

From source file:org.sonar.core.persistence.profiling.PersistenceProfilingTest.java

@Test
public void should_enable_profiling_when_profiling_is_full() throws Exception {
    final Logger sqlLogger = (Logger) LoggerFactory.getLogger("sql");
    ListAppender<ILoggingEvent> appender = new ListAppender<ILoggingEvent>();
    appender.setContext(new ContextBase());
    appender.start();/*from w w w. ja v  a  2s  . c o  m*/
    sqlLogger.addAppender(appender);

    BasicDataSource originDataSource = mock(BasicDataSource.class);

    Connection connection = mock(Connection.class);
    when(originDataSource.getConnection()).thenReturn(connection);

    String sql = "select 'polop' from dual;";
    String sqlWithParams = "insert into polop (col1, col2, col3, col4) values (?, ?, ?, ?, ?);";
    int param1 = 42;
    String param2 = "plouf";
    Date param3 = new Date(System.currentTimeMillis());
    Timestamp param4 = new Timestamp(System.currentTimeMillis());
    byte[] param5 = "blob".getBytes("UTF-8");

    PreparedStatement preparedStatement = mock(PreparedStatement.class);
    when(connection.prepareStatement(sqlWithParams)).thenReturn(preparedStatement);
    when(preparedStatement.execute()).thenReturn(true);

    Statement statement = mock(Statement.class);
    when(connection.createStatement()).thenReturn(statement);
    when(statement.execute(sql)).thenReturn(true);

    Settings settings = new Settings();
    settings.setProperty(Profiling.CONFIG_PROFILING_LEVEL, Profiling.Level.FULL.toString());

    BasicDataSource resultDataSource = PersistenceProfiling.addProfilingIfNeeded(originDataSource, settings);

    assertThat(resultDataSource).isInstanceOf(ProfilingDataSource.class);
    assertThat(resultDataSource.getUrl()).isNull();
    assertThat(resultDataSource.getConnection().getClientInfo()).isNull();
    PreparedStatement preparedStatementProxy = resultDataSource.getConnection().prepareStatement(sqlWithParams);
    preparedStatementProxy.setInt(1, param1);
    preparedStatementProxy.setString(2, param2);
    preparedStatementProxy.setDate(3, param3);
    preparedStatementProxy.setTimestamp(4, param4);
    preparedStatementProxy.setBlob(5, new ByteArrayInputStream(param5));
    assertThat(preparedStatementProxy.getConnection()).isNull();
    assertThat(preparedStatementProxy.execute()).isTrue();
    final Statement statementProxy = resultDataSource.getConnection().createStatement();
    assertThat(statementProxy.getConnection()).isNull();
    assertThat(statementProxy.execute(sql)).isTrue();

    assertThat(appender.list).hasSize(2);
    assertThat(appender.list.get(0).getLevel()).isEqualTo(Level.INFO);
    assertThat(appender.list.get(0).getFormattedMessage()).contains(sqlWithParams)
            .contains(" - parameters are: ").contains(Integer.toString(param1)).contains(param2);
    assertThat(appender.list.get(1).getLevel()).isEqualTo(Level.INFO);
    assertThat(appender.list.get(1).getFormattedMessage()).contains(sql);
}

From source file:org.sonar.core.persistence.profiling.ProfiledDataSourceTest.java

@Test
public void log_sql_requests() throws Exception {
    BasicDataSource originDataSource = mock(BasicDataSource.class);

    Connection connection = mock(Connection.class);
    when(originDataSource.getConnection()).thenReturn(connection);

    String sql = "select 'polop' from dual;";
    String sqlWithParams = "insert into polop (col1, col2, col3, col4) values (?, ?, ?, ?, ?);";
    int param1 = 42;
    String param2 = "plouf";
    Date param3 = new Date(System.currentTimeMillis());
    Timestamp param4 = new Timestamp(System.currentTimeMillis());
    byte[] param5 = "blob".getBytes("UTF-8");

    PreparedStatement preparedStatement = mock(PreparedStatement.class);
    when(connection.prepareStatement(sqlWithParams)).thenReturn(preparedStatement);
    when(preparedStatement.execute()).thenReturn(true);

    Statement statement = mock(Statement.class);
    when(connection.createStatement()).thenReturn(statement);
    when(statement.execute(sql)).thenReturn(true);

    ProfiledDataSource ds = new ProfiledDataSource(originDataSource);

    assertThat(ds.getUrl()).isNull();/*  w ww. j  a v  a2  s. com*/
    assertThat(ds.getConnection().getClientInfo()).isNull();
    PreparedStatement preparedStatementProxy = ds.getConnection().prepareStatement(sqlWithParams);
    preparedStatementProxy.setInt(1, param1);
    preparedStatementProxy.setString(2, param2);
    preparedStatementProxy.setDate(3, param3);
    preparedStatementProxy.setTimestamp(4, param4);
    preparedStatementProxy.setBlob(5, new ByteArrayInputStream(param5));
    assertThat(preparedStatementProxy.getConnection()).isNull();
    assertThat(preparedStatementProxy.execute()).isTrue();
    final Statement statementProxy = ds.getConnection().createStatement();
    assertThat(statementProxy.getConnection()).isNull();
    assertThat(statementProxy.execute(sql)).isTrue();

    assertThat(logTester.logs()).hasSize(2);
    assertThat(logTester.logs().get(1)).contains(sql);
}

From source file:org.sonar.db.profiling.ProfiledDataSourceTest.java

@Test
public void execute_and_log_prepared_statement_with_parameters() throws Exception {
    logTester.setLevel(LoggerLevel.TRACE);

    Connection connection = mock(Connection.class);
    when(originDataSource.getConnection()).thenReturn(connection);

    String sqlWithParams = "insert into polop (col1, col2, col3, col4) values (?, ?, ?, ?, ?)";
    int param1 = 42;
    String param2 = "plouf";
    Date param3 = new Date(System.currentTimeMillis());
    Timestamp param4 = new Timestamp(System.currentTimeMillis());
    byte[] param5 = "blob".getBytes("UTF-8");

    PreparedStatement preparedStatement = mock(PreparedStatement.class);
    when(connection.prepareStatement(sqlWithParams)).thenReturn(preparedStatement);
    when(preparedStatement.execute()).thenReturn(true);

    ProfiledDataSource ds = new ProfiledDataSource(originDataSource, ProfiledConnectionInterceptor.INSTANCE);

    assertThat(ds.getUrl()).isNull();/*from ww w. j  av  a2 s  .co m*/
    assertThat(ds.getConnection().getClientInfo()).isNull();
    PreparedStatement preparedStatementProxy = ds.getConnection().prepareStatement(sqlWithParams);
    preparedStatementProxy.setInt(1, param1);
    preparedStatementProxy.setString(2, param2);
    preparedStatementProxy.setDate(3, param3);
    preparedStatementProxy.setTimestamp(4, param4);
    preparedStatementProxy.setBlob(5, new ByteArrayInputStream(param5));
    assertThat(preparedStatementProxy.getConnection()).isNull();
    assertThat(preparedStatementProxy.execute()).isTrue();

    assertThat(logTester.logs(LoggerLevel.TRACE)).hasSize(1);
    assertThat(logTester.logs(LoggerLevel.TRACE).get(0))
            .contains("sql=insert into polop (col1, col2, col3, col4) values (?, ?, ?, ?, ?)")
            .contains("params=42, plouf");
}

From source file:org.sonar.db.profiling.ProfiledDataSourceTest.java

@Test
public void execute_and_log_prepared_statement_without_parameters() throws Exception {
    logTester.setLevel(LoggerLevel.TRACE);

    Connection connection = mock(Connection.class);
    when(originDataSource.getConnection()).thenReturn(connection);

    String sqlWithParams = "select from dual";
    PreparedStatement preparedStatement = mock(PreparedStatement.class);
    when(connection.prepareStatement(sqlWithParams)).thenReturn(preparedStatement);
    when(preparedStatement.execute()).thenReturn(true);

    ProfiledDataSource ds = new ProfiledDataSource(originDataSource, ProfiledConnectionInterceptor.INSTANCE);

    assertThat(ds.getUrl()).isNull();//from ww w  .  j a  va  2s  .  c  o m
    assertThat(ds.getConnection().getClientInfo()).isNull();
    PreparedStatement preparedStatementProxy = ds.getConnection().prepareStatement(sqlWithParams);
    assertThat(preparedStatementProxy.getConnection()).isNull();
    assertThat(preparedStatementProxy.execute()).isTrue();

    assertThat(logTester.logs(LoggerLevel.TRACE)).hasSize(1);
    assertThat(logTester.logs(LoggerLevel.TRACE).get(0)).contains("sql=select from dual")
            .doesNotContain("params=");
}

From source file:org.springframework.jdbc.core.JdbcTemplate.java

public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
    if (logger.isDebugEnabled()) {
        logger.debug("Executing SQL batch update [" + sql + "]");
    }// w ww .  ja v  a 2s. com
    return (int[]) execute(sql, new PreparedStatementCallback() {
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
            int batchSize = pss.getBatchSize();
            DatabaseMetaData dbmd = ps.getConnection().getMetaData();
            try {
                boolean supportsBatchUpdates = false;
                try {
                    if (dbmd != null) {
                        if (dbmd.supportsBatchUpdates()) {
                            if (logger.isDebugEnabled()) {
                                logger.debug("Batch Updates supported for [" + dbmd.getDriverName() + " "
                                        + dbmd.getDriverVersion() + "]");
                            }
                            supportsBatchUpdates = true;
                        } else {
                            if (logger.isDebugEnabled()) {
                                logger.debug("Batch Updates are not supported for [" + dbmd.getDriverName()
                                        + " " + dbmd.getDriverVersion() + "]");
                            }
                        }
                    }
                } catch (AbstractMethodError ame) {
                    logger.warn("Driver does not support JDBC 2.0 method supportsBatchUpdatres ["
                            + dbmd.getDriverName() + " " + dbmd.getDriverVersion() + "]");
                }
                if (supportsBatchUpdates) {
                    for (int i = 0; i < batchSize; i++) {
                        pss.setValues(ps, i);
                        ps.addBatch();
                    }
                    return ps.executeBatch();
                } else {
                    int[] rowsAffected = new int[batchSize];
                    for (int i = 0; i < batchSize; i++) {
                        pss.setValues(ps, i);
                        rowsAffected[i] = ps.executeUpdate();
                    }
                    return rowsAffected;
                }
            } finally {
                if (pss instanceof ParameterDisposer) {
                    ((ParameterDisposer) pss).cleanupParameters();
                }
            }
        }
    });
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testBatchUpdateWithPreparedStatement() throws Exception {
    final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
    final int[] ids = new int[] { 100, 200 };
    final int[] rowsAffected = new int[] { 1, 2 };

    MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
    mockPreparedStatement.getConnection();
    ctrlPreparedStatement.setReturnValue(mockConnection);
    mockPreparedStatement.setInt(1, ids[0]);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();//from   w w w .j  a  v a  2  s .  c om
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setInt(1, ids[1]);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeBatch();
    ctrlPreparedStatement.setReturnValue(rowsAffected);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
    DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
    mockDatabaseMetaData.getDatabaseProductName();
    ctrlDatabaseMetaData.setReturnValue("MySQL");
    mockDatabaseMetaData.supportsBatchUpdates();
    ctrlDatabaseMetaData.setReturnValue(true);

    mockConnection.prepareStatement(sql);
    ctrlConnection.setReturnValue(mockPreparedStatement);
    mockConnection.getMetaData();
    ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);

    ctrlPreparedStatement.replay();
    ctrlDatabaseMetaData.replay();
    replay();

    BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setInt(1, ids[i]);
        }

        public int getBatchSize() {
            return ids.length;
        }
    };

    JdbcTemplate template = new JdbcTemplate(mockDataSource, false);

    int[] actualRowsAffected = template.batchUpdate(sql, setter);
    assertTrue("executed 2 updates", actualRowsAffected.length == 2);
    assertEquals(rowsAffected[0], actualRowsAffected[0]);
    assertEquals(rowsAffected[1], actualRowsAffected[1]);

    ctrlPreparedStatement.verify();
    ctrlDatabaseMetaData.verify();
}