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

@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();
    }
}
 

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);
        }
    }
}
 

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();
  }
}
 

@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();
    }
}
 

/***********************************************************************************************
        * 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();
}
 

/**
 * 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));
}
 

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;
				}
			}
		}
	}
 

/**
 * 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);
    }
}
 

/**
 * 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();
}
 

/**
 * 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();
}
 

@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);
    }
}
 

@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();
    }
}
 

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 );
	}
}
 

@Override
public void setNonNullParameter(PreparedStatement ps, int i, byte[] parameter, JdbcType jdbcType)
    throws SQLException {
  ps.setBytes(i, parameter);
}
 

@Override
public void bind(PreparedStatement preparedStatement) throws SQLException {
    preparedStatement.setBytes(1, environment.toByteArray());
}
 

@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);
  }
}
 

/**
 * 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;
}
 

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;
    }  
    
}
 

@Override
public void setNonNullParameter(PreparedStatement ps, int i, Byte[] parameter, JdbcType jdbcType) throws SQLException {
  ps.setBytes(i, ByteArrayUtils.convertToPrimitiveArray(parameter));
}
 

@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);
    }
}