Example usage for java.sql PreparedStatement setBlob

Introduction

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

Prototype

void setBlob(int parameterIndex, InputStream inputStream) throws SQLException;

Source Link

Document

Sets the designated parameter to a InputStream object.

Usage

From source file:EmployeeInit.java

public static void main(String[] args) throws Exception {
    Connection con;/* w  ww.j  av a2  s  .co  m*/
    con = DriverManager.getConnection("jdbc:derby://localhost:1527/" + "c:\\db\\employee");

    PreparedStatement ps;
    ps = con.prepareStatement("insert into employee(name,photo) " + "values(?,?)");
    ps.setString(1, "Duke");

    Blob blob = con.createBlob();
    ImageIcon ii = new ImageIcon("duke.png");

    ObjectOutputStream oos;
    oos = new ObjectOutputStream(blob.setBinaryStream(1));
    oos.writeObject(ii);
    oos.close();
    ps.setBlob(2, blob);
    ps.execute();
    blob.free();
    ps.close();
}

---

From source file:DemoPreparedStatementSetBlob.java

public static void main(String[] args) throws Exception {
    Connection conn = null;//w  ww .  j ava  2  s  .  c  om
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    java.sql.Blob blob = null;
    try {
        conn = getConnection();
        // prepare blob object from an existing binary column
        pstmt = conn.prepareStatement("select photo from my_pictures where id = ?");
        pstmt.setString(1, "0001");
        rs = pstmt.executeQuery();
        rs.next();
        blob = rs.getBlob(1);

        // prepare SQL query for inserting a new row using setBlob()
        String query = "insert into blob_table(id, blob_column) values(?, ?)";
        // begin transaction
        conn.setAutoCommit(false);

        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, "0002");
        pstmt.setBlob(2, blob);

        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
        // end transaction
        conn.commit();
    } finally {
        rs.close();
        pstmt.close();
        conn.close();
    }
}

---

From source file:ImageStringToBlob.java

private static int writeBlobToDb(Connection conn, Long id, Blob dataBlob) throws Exception {
    String sql = "update client_image set contents = ? where image_id = ?";
    PreparedStatement pst = conn.prepareStatement(sql);
    pst.setBlob(1, dataBlob);
    pst.setLong(2, id);/*from w  ww  .ja v a 2  s.  c o m*/

    return pst.executeUpdate();
}

---

From source file:com.siemens.scr.avt.ad.hibernate.BinaryBlobType.java

public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
    st.setBlob(index, Hibernate.createBlob((byte[]) value));
}

---

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();//from ww  w  .  ja va  2 s.  c  om
    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.springframework.jdbc.support.lob.TemporaryLobCreator.java

@Override
public void setBlobAsBytes(PreparedStatement ps, int paramIndex, @Nullable byte[] content) throws SQLException {

    if (content != null) {
        Blob blob = ps.getConnection().createBlob();
        blob.setBytes(1, content);/*from   w  ww  . j  a  v  a 2  s . com*/
        this.temporaryBlobs.add(blob);
        ps.setBlob(paramIndex, blob);
    } else {
        ps.setBlob(paramIndex, (Blob) null);
    }

    if (logger.isDebugEnabled()) {
        logger.debug(content != null ? "Copied bytes into temporary BLOB with length " + content.length
                : "Set BLOB to null");
    }
}

---

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();/* w w w.  ja v  a 2s. co 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.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();//www  . j  av  a 2 s .  c  om
    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.ojbc.intermediaries.sn.dao.rapback.FbiRapbackDao.java

public Integer saveSubsequentResults(final SubsequentResults subsequentResults) {
    log.debug("Inserting row into SUBSEQUENT_RESULTS table : " + subsequentResults.toString());

    final String SUBSEQUENT_RESULTS_INSERT = "insert into SUBSEQUENT_RESULTS "
            + "(ucn, RAP_SHEET, RESULTS_SENDER_ID) " + "values (?, ?, ?)";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(SUBSEQUENT_RESULTS_INSERT,
                    new String[] { "ucn", "RAP_SHEET", "RESULTS_SENDER_ID" });
            ps.setString(1, subsequentResults.getUcn());
            ps.setBlob(2, new SerialBlob(ZipUtils.zip(subsequentResults.getRapSheet())));
            ps.setInt(3, subsequentResults.getResultsSender().ordinal() + 1);
            return ps;
        }//from w w  w . j a  v a2s  .  c om
    }, keyHolder);

    return keyHolder.getKey().intValue();
}

---

From source file:org.apache.lucene.store.jdbc.index.AbstractJdbcIndexOutput.java

public void close() throws IOException {
    super.close();
    final long length = length();
    doBeforeClose();/* w  ww  .  j av a2s  .  c o m*/
    jdbcDirectory.getJdbcTemplate().update(jdbcDirectory.getTable().sqlInsert(), new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setFetchSize(1);
            ps.setString(1, name);
            InputStream is = null;
            try {
                is = openInputStream();
                if (jdbcDirectory.getDialect().useInputStreamToInsertBlob()) {
                    ps.setBinaryStream(2, is, (int) length());
                } else {
                    ps.setBlob(2, new InputStreamBlob(is, length));
                }
                ps.setLong(3, length);
                ps.setBoolean(4, false);
            } catch (IOException e) {
                throw new SQLException(e);
            }
        }
    });
    doAfterClose();
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• »