Java Code Examples for java.sql.PreparedStatement#setBytes()

The following examples show how to use java.sql.PreparedStatement#setBytes() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: QueryCompileTest.java    From phoenix with BSD 3-Clause "New" or "Revised" License 6 votes vote down vote up
@Test
public void testStringConcatExpression() throws Exception {
    long ts = nextTimestamp();
    String query = "SELECT entity_id,a_string FROM atable where 2 || a_integer || ? like '2%'";
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = new Properties(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    byte []x=new byte[]{127,127,0,0};//Binary data
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setBytes(1, x);
        statement.executeQuery();
        fail();
    } catch (SQLException e) { // expected
        assertTrue(e.getMessage().contains("Concatenation does not support"));
    } finally {
        conn.close();
    }
}
 
Example 2
Source File: ByteArrayTypeHandler.java    From alfresco-repository with GNU Lesser General Public License v3.0 6 votes vote down vote up
public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException
{
    if (parameter == null)
    {
        ps.setNull(i, Types.BINARY);
    }
    else
    {
        try
        {
            ps.setBytes(i, (byte[])parameter);
        }
        catch (Throwable e)
        {
            throw new SerializationException(e);
        }
    }
}
 
Example 3
Source File: BlobTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
public static void insertData_43623(final Connection conn,
    final byte[] jobData) throws SQLException {
  PreparedStatement pstmt = conn.prepareStatement("insert into "
      + "QUARTZ_TRIGGERS(TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_STATE, "
      + "JOB_NAME, JOB_GROUP, JOB_DATA) values(?, ?, ?, ?, ?, ?)");
  AvailablePort.rand.nextBytes(jobData);
  final int numRows = 10;
  for (int cnt = 1; cnt <= numRows; cnt++) {
    pstmt.setString(1, "trig" + cnt);
    pstmt.setString(2, "grp" + cnt);
    pstmt.setString(3, "st" + cnt);
    pstmt.setString(4, "job" + cnt);
    pstmt.setString(5, "jgrp" + cnt);
    pstmt.setBytes(6, jobData);
    pstmt.execute();
  }
}
 
Example 4
Source File: BinaryRowKeyIT.java    From phoenix with Apache License 2.0 6 votes vote down vote up
@Test
public void testInsertPaddedBinaryValue() throws SQLException {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        initTableValues();
        conn.setAutoCommit(true);
        conn.createStatement().execute("DELETE FROM test_table");
       
        String query = "UPSERT INTO test_table"
                + "(a_binary, a_string) "
                + "VALUES(?,?)";
        PreparedStatement stmt = conn.prepareStatement(query);
        stmt.setBytes(1, new byte[] {0,0,0,0,0,0,0,0,1});
        stmt.setString(2, "a");
        stmt.execute();
        
        ResultSet rs = conn.createStatement().executeQuery("SELECT a_string FROM test_table");
        assertTrue(rs.next());
        assertEquals("a",rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 5
Source File: ForBitDataTest.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up
/***********************************************************************************************
        * All the methods below this line are used by fixtures
        **********************************************************************************************/
private void insertEL(PreparedStatement psi, PreparedStatement pss, PreparedStatement psd, int length, String expectedValue) throws SQLException, IOException 
{
               byte[] data = new byte[length];

	// random simple value check
	int off = (int)  (System.currentTimeMillis() % ((long) length));
	data[off] = 0x23;

	psi.setBytes(1, (length <= 32672) ? data : null);
	psi.setBytes(2, (length <= 32700) ? data : null);
	psi.setBinaryStream(3, new java.io.ByteArrayInputStream(data), length); // BLOB column

	psi.executeUpdate();
	selectData(pss,data,off,length, expectedValue);
	psd.executeUpdate();

	// Set values using stream and then verify that select is successful
	psi.setBinaryStream(1, (length <= 32672) ? new java.io.ByteArrayInputStream(data) : null, length);
	psi.setBinaryStream(2, (length <= 32700) ? new java.io.ByteArrayInputStream(data) : null, length);
	psi.setBinaryStream(3, new java.io.ByteArrayInputStream(data), length); // BLOB column
	psi.executeUpdate();

	selectData(pss,data,off,length, expectedValue);
	psd.executeUpdate();
}
 
Example 6
Source File: ArtigoDAO.java    From redesocial with MIT License 6 votes vote down vote up
/**
 * Método para inserir um artigo no banco de dados
 * @param dto identificador de artigo
 * @author Davi de Faria
 * @throws Exception Possíveis exceções que podem acontecer
 */
@Override
public void inserir(Artigo dto) throws Exception {
    Connection conexao = getConexao();
    
    if (dto.getArtigo().equals("")){
        throw new Exception("O arquivo do artigo não pode estar vazio!");
    }
    
    PreparedStatement pstmt = conexao.prepareStatement("insert into artigos (idioma, revista, issn, data, area_conhecimento, titulo, resumo, url, artigo, categoria) values (?,?,?,?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
    
    pstmt.setString(1, dto.getIdioma());
    pstmt.setString(2, dto.getRevista());
    pstmt.setString(3, dto.getISSN());
    pstmt.setTimestamp(4, new java.sql.Timestamp(dto.getData().getTime()));
    pstmt.setString(5, dto.getAreaConhecimento());
    pstmt.setString(6, dto.getTitulo());
    pstmt.setString(7, dto.getResumo());
    pstmt.setString(8, dto.getURL());
    pstmt.setBytes(9, dto.getArtigo());
    pstmt.setInt(10, dto.getCategoria().getId());
    
    pstmt.executeUpdate();
    
    dto.setId(getId(pstmt));
}
 
Example 7
Source File: Insert.java    From dbtransfer with GNU Lesser General Public License v2.1 6 votes vote down vote up
public void fillParameters(Object stm)
			throws SQLException
	{
		int i = 1;
		PreparedStatement pstm = ( PreparedStatement )stm;
		if( iAssignments != null )
		{
			for( Assignment a: iAssignments )
			{
				a.getOperand().setBackend( getBackend() );
//				a.getOperand().setUnicode( unicode );
				if( a.getOperand().getInnerObject() instanceof byte[] )
				{
					pstm.setBytes( i, ( byte[] )a.getOperand().getInnerObject() );
					++i;
				}
			}
		}
	}
 
Example 8
Source File: JdbcCheckpointSpi.java    From ignite with Apache License 2.0 6 votes vote down vote up
/**
 * Creates checkpoint.
 *
 * @param conn Active database connection.
 * @param key Checkpoint key.
 * @param state Checkpoint data.
 * @param expTime Checkpoint expire time.
 * @return Number of rows affected by query.
 * @throws SQLException Thrown in case of any errors.
 */
private int createCheckpoint(Connection conn, String key, byte[] state, Time expTime) throws SQLException {
    PreparedStatement st = null;

    try {
        st = conn.prepareStatement(insSql);

        st.setString(1, key);
        st.setBytes(2, state);
        st.setTime(3, expTime);

        return st.executeUpdate();
    }
    finally {
        U.close(st, log);
    }
}
 
Example 9
Source File: PrepStmtNullTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
/**
 * Test setNull() on Clob/Blob using Varchar/binary types
 *
 * @exception SQLException if error occurs
 */
public void testNullClobBlob() throws SQLException {

    byte[] b2 = new byte[1];
    b2[0] = (byte)64;

    PreparedStatement pStmt = prepareStatement("insert into ClobBlob values (?,?)");

    pStmt.setNull(1, Types.VARCHAR);
    pStmt.setBytes(2, b2);
    pStmt.execute();
    pStmt.setNull(1, Types.VARCHAR,"");
    pStmt.setBytes(2, b2);
    pStmt.execute();
    pStmt.close();

    Statement stmt1 = createStatement();
    ResultSet rs = stmt1.executeQuery("select * from ClobBlob");
    String [][]  expectedRows = new String[][] { { null, bytesToString(b2) },
    { null, bytesToString(b2) } };
    JDBC.assertFullResultSet(rs, expectedRows);
    rs.close();

    stmt1.close();
}
 
Example 10
Source File: PrepStmtNullTest.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up
/**
 * Test setNull() on Clob/Blob using Varchar/binary types
 *
 * @exception SQLException if error occurs
 */
public void testNullClobBlob() throws SQLException {
    
    byte[] b2 = new byte[1];
    b2[0] = (byte)64;
    
    PreparedStatement pStmt = prepareStatement("insert into ClobBlob values (?,?)");
    
    pStmt.setNull(1, Types.VARCHAR);
    pStmt.setBytes(2, b2);
    pStmt.execute();
    pStmt.setNull(1, Types.VARCHAR,"");
    pStmt.setBytes(2, b2);
    pStmt.execute();
    pStmt.close();
    
    Statement stmt1 = createStatement();
    ResultSet rs = stmt1.executeQuery("select * from ClobBlob");
    String [][]  expectedRows = new String[][] { { null, bytesToString(b2) },
    { null, bytesToString(b2) } };
    JDBC.assertFullResultSet(rs, expectedRows);
    rs.close();
    
    stmt1.close();
}
 
Example 11
Source File: BindingDaoImpl.java    From ballerina-message-broker with Apache License 2.0 5 votes vote down vote up
@Override
public void persist(String exchangeName, Binding binding) throws BrokerException {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
        connection = getConnection();
        statement = connection.prepareStatement(RDBMSConstants.PS_INSERT_BINDING);
        statement.setString(1, exchangeName);
        statement.setString(2, binding.getQueue().getName());
        statement.setString(3, binding.getBindingPattern());
        FieldTable arguments = binding.getArguments();
        byte[] bytes = new byte[(int) arguments.getSize()];
        ByteBuf byteBuf = Unpooled.wrappedBuffer(bytes);
        byteBuf.resetWriterIndex();
        arguments.write(byteBuf);
        statement.setBytes(4, bytes);

        statement.executeUpdate();
        connection.commit();
    } catch (SQLException e) {
        String message = "Error occurred while storing binding " + binding;
        rollback(connection, message);
        throw new BrokerException(message, e);
    } finally {
        close(connection, statement);
    }
}
 
Example 12
Source File: SaltedTableVarLengthRowKeyIT.java    From phoenix with Apache License 2.0 5 votes vote down vote up
@Test
public void testSaltedVarbinaryUpperBoundQuery() throws Exception {
    String tableName = generateUniqueName();
    String ddl = "CREATE TABLE " + tableName +
            " ( k VARBINARY PRIMARY KEY, a INTEGER ) SALT_BUCKETS = 3";
    String dml = "UPSERT INTO " + tableName + " values (?, ?)";
    String sql2 = "SELECT * FROM " + tableName + " WHERE k = ?";

    try (Connection conn = DriverManager.getConnection(getUrl())) {
        conn.createStatement().execute(ddl);
        PreparedStatement stmt = conn.prepareStatement(dml);
        stmt.setInt(2, 1);

        stmt.setBytes(1, new byte[] { 5 });
        stmt.executeUpdate();
        stmt.setBytes(1, new byte[] { 5, 0 });
        stmt.executeUpdate();
        stmt.setBytes(1, new byte[] { 5, 1 });
        stmt.executeUpdate();
        stmt.close();
        conn.commit();

        stmt = conn.prepareStatement(sql2);
        stmt.setBytes(1, new byte[] { 5 });
        ResultSet rs = stmt.executeQuery();

        assertTrue(rs.next());
        assertArrayEquals(new byte[] {5},rs.getBytes(1));
        assertEquals(1,rs.getInt(2));
        assertFalse(rs.next());
        stmt.close();
    }
}
 
Example 13
Source File: AbstractBynaryType.java    From cacheonix-core with GNU Lesser General Public License v2.1 5 votes vote down vote up
public void set(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
	byte[] internalValue = toInternalFormat( value );
	if ( Environment.useStreamsForBinary() ) {
		st.setBinaryStream( index, new ByteArrayInputStream( internalValue ), internalValue.length );
	}
	else {
		st.setBytes( index, internalValue );
	}
}
 
Example 14
Source File: ByteArrayTypeHandler.java    From mybatis with Apache License 2.0 4 votes vote down vote up
@Override
public void setNonNullParameter(PreparedStatement ps, int i, byte[] parameter, JdbcType jdbcType)
    throws SQLException {
  ps.setBytes(i, parameter);
}
 
Example 15
Source File: EnvironmentDao.java    From glowroot with Apache License 2.0 4 votes vote down vote up
@Override
public void bind(PreparedStatement preparedStatement) throws SQLException {
    preparedStatement.setBytes(1, environment.toByteArray());
}
 
Example 16
Source File: PinLaterMySQLBackend.java    From pinlater with Apache License 2.0 4 votes vote down vote up
@Override
protected String enqueueSingleJob(String queueName, PinLaterJob job, int numAutoRetries)
    throws Exception {
  final long currentTimeMillis = System.currentTimeMillis();
  Connection conn = null;
  PreparedStatement stmt = null;
  ResultSet rs = null;
  final ImmutableMap.Entry<String, MySQLDataSources> shard = getRandomEnqueueableShard();
  try {
    conn = shard.getValue().getGeneralDataSource().getConnection();
    String jobsTableName =
        MySQLBackendUtils.constructJobsTableName(queueName, shard.getKey(), job.getPriority());
    stmt = conn.prepareStatement(
        String.format(MySQLQueries.ENQUEUE_INSERT, jobsTableName),
        Statement.RETURN_GENERATED_KEYS);
    stmt.setInt(1, PinLaterJobState.PENDING.getValue());
    stmt.setInt(2, job.getNumAttemptsAllowed());
    stmt.setInt(3, job.getNumAttemptsAllowed());
    stmt.setString(4, job.getCustomStatus());
    stmt.setTimestamp(5, new Timestamp(currentTimeMillis));
    stmt.setTimestamp(6, new Timestamp(job.isSetRunAfterTimestampMillis()
                                       ? job.getRunAfterTimestampMillis() : currentTimeMillis));
    stmt.setBytes(7, job.getBody());
    stmt.executeUpdate();
    rs = stmt.getGeneratedKeys();
    rs.next();
    return new PinLaterJobDescriptor(
        queueName, shard.getKey(), job.getPriority(), rs.getLong(1)).toString();
  } catch (SQLException e) {
    boolean shouldRetry = checkExceptionIsRetriable(e, shard.getKey(), "enqueue");
    if (shouldRetry && numAutoRetries > 0) {
      // Retry the enqueue, potentially on a different shard.
      Stats.incr("enqueue-failures-retry");
      return enqueueSingleJob(queueName, job, numAutoRetries - 1);
    }
    // Out of retries, throw the exception. Wrap it into a PinLaterException if the exception
    // is recognized and return the appropriate error code.
    if (MySQLBackendUtils.isDatabaseDoesNotExistException(e)) {
      throw new PinLaterException(ErrorCode.QUEUE_NOT_FOUND, "Queue not found: " + queueName);
    }
    throw e;
  } finally {
    JdbcUtils.closeResultSet(rs);
    JdbcUtils.closeStatement(stmt);
    JdbcUtils.closeConnection(conn);
  }
}
 
Example 17
Source File: TestDatabase.java    From Azzet with Open Software License 3.0 4 votes vote down vote up
/**
 * Inserts the request into the database.
 * 
 * @param request
 * 		The request string.
 * @param input
 * 		The asset as an InputStream.
 * @return
 * 		Returns true if the asset was inserted, otherwise false.
 * @throws Exception
 * 		An error occurred with the database.
 */
protected boolean insert(String request, InputStream input) throws Exception
{
	Connection con = getConnection();
	
	PreparedStatement stmt = con.prepareStatement(INSERT_QUERY);
	
	boolean inserted = false;
	try 
	{
		stmt.setString(1, request);
		
		// Try Blob, BinaryStream, and finally Bytes if neither work.
		try 
		{
			stmt.setBlob(2, input);
			
			System.out.println(driverName + " inserted as Blob");
		}
		catch (AbstractMethodError e0) 
		{
			try 
			{
				stmt.setBinaryStream(2, input);

				System.out.println(driverName + " inserted as BinaryStream");
			}
			catch (AbstractMethodError e1) 
			{
				stmt.setBytes(2, FormatUtility.getBytes(input));

				System.out.println(driverName + " inserted as Bytes");
			}
		}
		
		inserted = (stmt.executeUpdate() == 1);
	}
	finally 
	{
		stmt.close();
	}

	return inserted;
}
 
Example 18
Source File: DatabaseUtils.java    From StatsAgg with Apache License 2.0 4 votes vote down vote up
private static int setPreparedStatementParameter(PreparedStatement preparedStatement, List<Object> preparedStatementParameters, Object object, AtomicInteger index) {
    
    if ((preparedStatement == null) || (preparedStatementParameters == null)) {
        logger.warn("Can't set preparedStatementParameters - preparedStatementParameters or preparedStatement is null");
        return -1;
    } 

    try {
        if (object == null) {
            preparedStatement.setObject(index.getAndIncrement(), null);
        } 
        else if (object instanceof BigDecimal) {
            preparedStatement.setBigDecimal(index.getAndIncrement(), (BigDecimal) object);
        }
        else if (object instanceof Blob) {
            preparedStatement.setBlob(index.getAndIncrement(), (Blob) object);
        }
        else if (object instanceof Boolean) {
            preparedStatement.setBoolean(index.getAndIncrement(), (Boolean) object);
        }
        else if (object instanceof Byte) {
            preparedStatement.setByte(index.getAndIncrement(), (Byte) object);
        }
        else if (object instanceof byte[]) {
            preparedStatement.setBytes(index.getAndIncrement(), (byte[]) object);
        }
        else if (object instanceof Clob) {
            preparedStatement.setClob(index.getAndIncrement(), (Clob) object);
        }
        else if (object instanceof Double) {
            preparedStatement.setDouble(index.getAndIncrement(), (Double) object);
        }
        else if (object instanceof Float) {
            preparedStatement.setFloat(index.getAndIncrement(), (Float) object);
        }
        else if (object instanceof Integer) {
            preparedStatement.setInt(index.getAndIncrement(), (Integer) object);
        }
        else if (object instanceof List) {
            for (Object listObject : (List) object) {
                setPreparedStatementParameter(preparedStatement, preparedStatementParameters, listObject, index);
            }
        }
        else if (object instanceof Long) {
            preparedStatement.setLong(index.getAndIncrement(), (Long) object);
        }
        else if (object instanceof NClob) {
            preparedStatement.setNClob(index.getAndIncrement(), (NClob) object);
        }
        else if (object instanceof Ref) {
            preparedStatement.setRef(index.getAndIncrement(), (Ref) object);
        }
        else if (object instanceof RowId) {
            preparedStatement.setRowId(index.getAndIncrement(), (RowId) object);
        }
        else if (object instanceof SQLXML) {
            preparedStatement.setSQLXML(index.getAndIncrement(), (SQLXML) object);
        }
        else if (object instanceof Short) {
            preparedStatement.setShort(index.getAndIncrement(), (Short) object);
        }
        else if (object instanceof String) {
            preparedStatement.setString(index.getAndIncrement(), (String) object);
        }
        else if (object instanceof Time) {
            preparedStatement.setTime(index.getAndIncrement(), (Time) object);
        }
        else if (object instanceof java.sql.Timestamp) {
            preparedStatement.setTimestamp(index.getAndIncrement(), (java.sql.Timestamp) object);
        }
        else if (object instanceof java.sql.Date) {
            preparedStatement.setDate(index.getAndIncrement(), (java.sql.Date) object);
        }
        else if (object instanceof java.util.Date) {
            java.util.Date tempDate = (java.util.Date) object;
            java.sql.Date dateSql = new java.sql.Date(tempDate.getTime());
            preparedStatement.setDate(index.getAndIncrement(), dateSql);
        }
        else {
            if (object instanceof Object) {}
            else {
                logger.warn("Setting PreparedStatement parameter to 'object' type when object is not an object type");
            }
            
            preparedStatement.setObject(index.getAndIncrement(), object);
        }
        
        return index.get();
    }
    catch (Exception e) {
        logger.error(e.toString() + System.lineSeparator() + StackTrace.getStringFromStackTrace(e));
        return -1;
    }  
    
}
 
Example 19
Source File: ByteObjectArrayTypeHandler.java    From mybatis with Apache License 2.0 4 votes vote down vote up
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Byte[] parameter, JdbcType jdbcType) throws SQLException {
  ps.setBytes(i, ByteArrayUtils.convertToPrimitiveArray(parameter));
}
 
Example 20
Source File: PostgreSQLApplicationDAOImpl.java    From carbon-device-mgt with Apache License 2.0 4 votes vote down vote up
@Override
public List<Integer> addApplications(List<Application> applications,
                                     int tenantId) throws DeviceManagementDAOException {
    Connection conn;
    PreparedStatement stmt = null;
    ResultSet rs;
    List<Integer> applicationIds = new ArrayList<>();
    try {
        conn = this.getConnection();
        stmt = conn.prepareStatement("INSERT INTO DM_APPLICATION (NAME, PLATFORM, " +
                "CATEGORY, VERSION, TYPE, LOCATION_URL, IMAGE_URL, TENANT_ID,APP_PROPERTIES, " +
                "APP_IDENTIFIER, MEMORY_USAGE, IS_ACTIVE) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", new String[]{"id"});

        for (Application application : applications) {

            stmt.setString(1, application.getName());
            stmt.setString(2, application.getPlatform());
            stmt.setString(3, application.getCategory());
            stmt.setString(4, application.getVersion());
            stmt.setString(5, application.getType());
            stmt.setString(6, application.getLocationUrl());
            stmt.setString(7, application.getImageUrl());
            stmt.setInt(8, tenantId);

            // Removing the application properties saving from the application table.
            stmt.setBytes(9, null);

            stmt.setString(10, application.getApplicationIdentifier());

            // Removing the application memory
            stmt.setInt(11, 0);
            stmt.setBoolean(12, true);

            stmt.executeUpdate();

            rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                applicationIds.add(rs.getInt(1));
            }
        }
        return applicationIds;
    } catch (SQLException e) {
        throw new DeviceManagementDAOException("Error occurred while adding bulk application list", e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, null);
    }
}