下面列出了java.sql.PreparedStatement#setBytes ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
@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();
}
}
/***********************************************************************************************
* 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();
}
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();
}
/**
* 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));
}
@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();
}
}
@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);
}
}
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 );
}
}
@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();
}
}
@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);
}
}
@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);
}
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Byte[] parameter, JdbcType jdbcType) throws SQLException {
ps.setBytes(i, ByteArrayUtils.convertToPrimitiveArray(parameter));
}
/**
* 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;
}
@Override
public void bind(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setBytes(1, environment.toByteArray());
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, byte[] parameter, JdbcType jdbcType)
throws SQLException {
ps.setBytes(i, parameter);
}
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;
}
}