下面列出了java.sql.Connection#createBlob ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
@Override
public void changePassword(String user, HashedPassword password, Connection con) throws SQLException {
OptionalInt authId = retrieveIdFromTable(user, con);
if (authId.isPresent()) {
final int id = authId.getAsInt();
// Insert password in the correct table
String sql = "UPDATE " + xfPrefix + "user_authenticate SET data=? WHERE " + col.ID + "=?;";
try (PreparedStatement pst = con.prepareStatement(sql)) {
String serializedHash = XfBCrypt.serializeHash(password.getHash());
byte[] bytes = serializedHash.getBytes();
Blob blob = con.createBlob();
blob.setBytes(1, bytes);
pst.setBlob(1, blob);
pst.setInt(2, id);
pst.executeUpdate();
}
// ...
sql = "UPDATE " + xfPrefix + "user_authenticate SET scheme_class=? WHERE " + col.ID + "=?;";
try (PreparedStatement pst = con.prepareStatement(sql)) {
pst.setString(1, XfBCrypt.SCHEME_CLASS);
pst.setInt(2, id);
pst.executeUpdate();
}
}
}
/**
* Tests fix for Bug#56122 - JDBC4 functionality failure when using replication connections.
*/
public void testBug56122() throws Exception {
for (final Connection testConn : new Connection[] { this.conn, getFailoverConnection(), getLoadBalancedConnection(),
getMasterSlaveReplicationConnection() }) {
testConn.createClob();
testConn.createBlob();
testConn.createNClob();
testConn.createSQLXML();
testConn.isValid(12345);
testConn.setClientInfo(new Properties());
testConn.setClientInfo("NAME", "VALUE");
testConn.getClientInfo();
testConn.getClientInfo("CLIENT");
assertThrows(SQLFeatureNotSupportedException.class, new Callable<Void>() {
public Void call() throws Exception {
testConn.createArrayOf("A_TYPE", null);
return null;
}
});
assertThrows(SQLFeatureNotSupportedException.class, new Callable<Void>() {
public Void call() throws Exception {
testConn.createStruct("A_TYPE", null);
return null;
}
});
}
}
/**
* Load only one record
*/
public static void loadAllTypesData(Connection conn, String tablename) throws SQLException {
try (PreparedStatement statement = conn
.prepareStatement("insert into " + tablename + " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)")) {
statement.setShort(1, (short) 32767);
statement.setInt(2, 2147483647);
statement.setLong(3, 9223372036854775807l);
statement.setFloat(4, 1.11111111f);
statement.setDouble(5, 2.222222222);
statement.setBigDecimal(6, new BigDecimal("1234567890.1234567890"));
statement.setString(7, "abcd");
statement.setString(8, "abcdefg");
Blob blob = conn.createBlob();
byte[] bytes = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
blob.setBytes(1, bytes);
statement.setBlob(9, blob);
Clob clob = conn.createClob();
clob.setString(1, "abcdefg");
statement.setClob(10, clob);
statement.setDate(11, Date.valueOf("2016-12-28"));
statement.setTime(12, Time.valueOf("14:30:33"));
statement.setTimestamp(13, Timestamp.valueOf("2016-12-28 14:31:56.12345"));
statement.setBoolean(14, true);
statement.executeUpdate();
}
if (!conn.getAutoCommit()) {
conn.commit();
}
}
@Override
public Blob executeOnConnection(Connection connection) throws SQLException {
return connection.createBlob();
}
/**
* tests set bytes method of blob in memory only mode (less than 4k)
*/
public void testSetBytesSmallBlob () throws SQLException {
Connection con = getConnection();
con.setAutoCommit (false);
PreparedStatement pstmt = con.prepareStatement("insert into " +
"blobtest (id, data) values (?,?)");
pstmt.setInt (1,1);
Blob blob = con.createBlob();
//add 1024 bytes
byte [] data = new byte [BUFFER_SIZE];
for (int i = 0; i < BUFFER_SIZE; i++) {
data [i] = (byte) (i % 255);
}
blob.setBytes (1, data);
assertEquals (BUFFER_SIZE, blob.length());
pstmt.setBlob (2, blob);
pstmt.executeUpdate();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
"select data from blobtest where id = 1");
assertEquals(true, rs.next());
blob = rs.getBlob (1);
assertEquals (BUFFER_SIZE, blob.length());
//update blob in the middle
byte [] data1 = new byte [UPDATE_SIZE];
for (int i = 0; i < UPDATE_SIZE; i++)
data1 [i] = 120;//just any value
blob.setBytes (UPDATE_SIZE, data1);
byte [] data2 = blob.getBytes (100, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//update it at the end
blob.setBytes (BUFFER_SIZE + 1, data1);
assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//insert the blob and test again
pstmt.setInt (1, 2);
pstmt.setBlob (2, blob);
pstmt.executeUpdate();
rs = stmt.executeQuery("select data from blobtest where " +
"id = 2");
assertEquals(true, rs.next());
blob = rs.getBlob (1);
assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
data2 = blob.getBytes (100, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//test truncate on small size blob
blob = con.createBlob();
data = new byte [100];
for (int i = 0; i < 100; i++) {
data [i] = (byte) i;
}
blob.setBytes (1, data);
assertEquals (blob.length(), 100);
blob.truncate (50);
assertEquals (blob.length(), 50);
blob.setBytes (1, data);
assertEquals ("set failed", blob.length(), 100);
blob.truncate (50);
assertEquals ("truncation failed", blob.length(), 50);
rs.close();
con.commit();
stmt.close();
pstmt.close();
}
/**
* Tests large blob (more than 4k) to ensure LOBStreamControl uses file.
*/
public void testSetBytesLargeBlob() throws Exception {
startVMs(1, 4);
Connection con = TestUtil.getConnection();
Statement stmt = con.createStatement();
stmt.execute("create table blobtest (id integer, data Blob)");
stmt.close();
con.setAutoCommit(false);
PreparedStatement pstmt = con.prepareStatement("insert into "
+ "blobtest (id, data) values (?,?)");
Blob blob = con.createBlob();
byte[] data = new byte[BUFFER_SIZE];
for (int i = 0; i < BUFFER_SIZE; i++) {
data[i] = (byte)(i % 255);
}
// now add more than 4k so file get in use
for (int i = 0; i < 5; i++)
blob.setBytes(i * BUFFER_SIZE + 1, data);
assertEquals(BUFFER_SIZE * 5, blob.length());
// update blob in the middle
byte[] data1 = new byte[UPDATE_SIZE];
for (int i = 0; i < UPDATE_SIZE; i++)
data1[i] = 120;// just any value
blob.setBytes(BUFFER_SIZE + 1, data1);
blob.setBytes(BUFFER_SIZE * 5 + 1, data1);
assertEquals(5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
// insert it into table
pstmt.setInt(1, 3);
pstmt.setBlob(2, blob);
pstmt.executeUpdate();
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select data from blobtest where "
+ "id = 3");
assertEquals(true, rs.next());
blob = rs.getBlob(1);
byte[] data2 = blob.getBytes(BUFFER_SIZE + 1, UPDATE_SIZE);
assertEquals(5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals(data1[i], data2[i]);
data2 = blob.getBytes(5 * BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals(data1[i], data2[i]);
// test truncate
blob.truncate(BUFFER_SIZE);
assertEquals("truncate failed", BUFFER_SIZE, blob.length());
rs.close();
con.commit();
stmt.close();
pstmt.close();
}
/**
* Updates the xenforo tables after a player auth has been saved.
*
* @param auth the player auth which was saved
* @param id the account id
* @param con connection to the database
*/
private void updateXenforoTablesOnSave(PlayerAuth auth, int id, Connection con) throws SQLException {
// Insert player password, salt in xf_user_authenticate
String sql = "INSERT INTO " + xfPrefix + "user_authenticate (user_id, scheme_class, data) VALUES (?,?,?)";
try (PreparedStatement pst = con.prepareStatement(sql)) {
pst.setInt(1, id);
pst.setString(2, XfBCrypt.SCHEME_CLASS);
String serializedHash = XfBCrypt.serializeHash(auth.getPassword().getHash());
byte[] bytes = serializedHash.getBytes();
Blob blob = con.createBlob();
blob.setBytes(1, bytes);
pst.setBlob(3, blob);
pst.executeUpdate();
}
// Update player group in xf_users
sql = "UPDATE " + tableName + " SET " + tableName + ".user_group_id=? WHERE " + col.NAME + "=?;";
try (PreparedStatement pst = con.prepareStatement(sql)) {
pst.setInt(1, xfGroup);
pst.setString(2, auth.getNickname());
pst.executeUpdate();
}
// Update player permission combination in xf_users
sql = "UPDATE " + tableName + " SET " + tableName + ".permission_combination_id=? WHERE " + col.NAME + "=?;";
try (PreparedStatement pst = con.prepareStatement(sql)) {
pst.setInt(1, xfGroup);
pst.setString(2, auth.getNickname());
pst.executeUpdate();
}
// Insert player privacy combination in xf_user_privacy
sql = "INSERT INTO " + xfPrefix + "user_privacy (user_id, allow_view_profile, allow_post_profile, "
+ "allow_send_personal_conversation, allow_view_identities, allow_receive_news_feed) VALUES (?,?,?,?,?,?)";
try (PreparedStatement pst = con.prepareStatement(sql)) {
pst.setInt(1, id);
pst.setString(2, "everyone");
pst.setString(3, "members");
pst.setString(4, "members");
pst.setString(5, "everyone");
pst.setString(6, "everyone");
pst.executeUpdate();
}
// Insert player group relation in xf_user_group_relation
sql = "INSERT INTO " + xfPrefix + "user_group_relation (user_id, user_group_id, is_primary) VALUES (?,?,?)";
try (PreparedStatement pst = con.prepareStatement(sql)) {
pst.setInt(1, id);
pst.setInt(2, xfGroup);
pst.setString(3, "1");
pst.executeUpdate();
}
}
/**
* Tests large blob (more than 4k) to ensure LOBStreamControl uses file.
*/
public void testSetBytesLargeBlob() throws SQLException {
Connection con = TestUtil.getConnection();
Statement stmt = con.createStatement();
stmt.execute ("create table blobtest (id integer, data Blob)" + getSuffix());
stmt.close();
con.setAutoCommit (false);
PreparedStatement pstmt = con.prepareStatement("insert into " +
"blobtest (id, data) values (?,?)");
Blob blob = con.createBlob();
byte [] data = new byte [BUFFER_SIZE];
for (int i = 0; i < BUFFER_SIZE; i++) {
data [i] = (byte) (i % 255);
}
//now add more than 4k so file get in use
for (int i = 0; i < 5; i++)
blob.setBytes (i * BUFFER_SIZE + 1, data);
assertEquals (BUFFER_SIZE * 5 , blob.length());
//update blob in the middle
byte [] data1 = new byte [UPDATE_SIZE];
for (int i = 0; i < UPDATE_SIZE; i++)
data1 [i] = 120;//just any value
blob.setBytes (BUFFER_SIZE + 1, data1);
blob.setBytes (BUFFER_SIZE * 5 + 1, data1);
assertEquals (5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
//insert it into table
pstmt.setInt (1, 3);
pstmt.setBlob (2, blob);
pstmt.executeUpdate ();
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select data from blobtest where " +
"id = 3");
assertEquals(true, rs.next());
blob = rs.getBlob (1);
byte [] data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
assertEquals (5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
data2 = blob.getBytes (5 * BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//test truncate
blob.truncate (BUFFER_SIZE);
assertEquals ("truncate failed", BUFFER_SIZE, blob.length());
rs.close();
con.commit();
stmt.close();
pstmt.close();
}
/**
* tests set bytes method of blob in memory only mode (less than 4k)
*/
public void testSetBytesSmallBlob () throws SQLException {
Connection con = TestUtil.getConnection();
Statement stmt = con.createStatement();
stmt.execute ("create table blobtest (id integer, data Blob)"+ getSuffix());
stmt.close();
con.setAutoCommit (false);
PreparedStatement pstmt = con.prepareStatement("insert into " +
"blobtest (id, data) values (?,?)");
pstmt.setInt (1,1);
Blob blob = con.createBlob();
//add 1024 bytes
byte [] data = new byte [BUFFER_SIZE];
for (int i = 0; i < BUFFER_SIZE; i++) {
data [i] = (byte) (i % 255);
}
blob.setBytes (1, data);
assertEquals (BUFFER_SIZE, blob.length());
pstmt.setBlob (2, blob);
pstmt.executeUpdate();
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select data from blobtest where id = 1");
assertEquals(true, rs.next());
blob = rs.getBlob (1);
assertEquals (BUFFER_SIZE, blob.length());
//update blob in the middle
byte [] data1 = new byte [UPDATE_SIZE];
for (int i = 0; i < UPDATE_SIZE; i++)
data1 [i] = 120;//just any value
blob.setBytes (UPDATE_SIZE, data1);
byte [] data2 = blob.getBytes (100, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//update it at the end
blob.setBytes (BUFFER_SIZE + 1, data1);
assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//insert the blob and test again
pstmt.setInt (1, 2);
pstmt.setBlob (2, blob);
pstmt.executeUpdate();
rs = stmt.executeQuery("select data from blobtest where " +
"id = 2");
assertEquals(true, rs.next());
blob = rs.getBlob (1);
assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
data2 = blob.getBytes (100, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//test truncate on small size blob
blob = con.createBlob();
data = new byte [100];
for (int i = 0; i < 100; i++) {
data [i] = (byte) i;
}
blob.setBytes (1, data);
assertEquals (blob.length(), 100);
blob.truncate (50);
assertEquals (blob.length(), 50);
blob.setBytes (1, data);
assertEquals ("set failed", blob.length(), 100);
blob.truncate (50);
assertEquals ("truncation failed", blob.length(), 50);
rs.close();
con.commit();
stmt.close();
pstmt.close();
stmt = con.createStatement();
stmt.execute ("drop table blobtest");
this.waitTillAllClear();
stmt.close();
}
/**
* Test the createBlob method implementation in the Connection interface
*
* @exception SQLException, FileNotFoundException, Exception if error occurs
*/
public void testCreateBlob() throws SQLException,
FileNotFoundException,
IOException,
Exception{
Connection conn = getConnection();
int b, c;
Blob blob;
Statement s = createStatement();
PreparedStatement ps =
prepareStatement("insert into blobtable2 (n, blobcol)" + " values(?,?)");
ps.setInt(1,1000);
blob = conn.createBlob();
try {
is = (FileInputStream) AccessController.doPrivileged(
new PrivilegedExceptionAction() {
public Object run() throws FileNotFoundException {
return new FileInputStream("extin/short.txt");
}
});
} catch (PrivilegedActionException e) {
// e.getException() should be an instance of FileNotFoundException,
// as only "checked" exceptions will be "wrapped" in a
// PrivilegedActionException.
throw (FileNotFoundException) e.getException();
}
OutputStream os = blob.setBinaryStream(1);
ArrayList beforeUpdateList = new ArrayList();
int actualLength = 0;
c = is.read();
while(c>0) {
os.write(c);
beforeUpdateList.add(c);
c = is.read();
actualLength ++;
}
ps.setBlob(2, blob);
ps.executeUpdate();
Statement stmt = createStatement();
ResultSet rs =
stmt.executeQuery("select blobcol from blobtable2 where n = 1000");
assertTrue(rs.next());
blob = rs.getBlob(1);
assertEquals(beforeUpdateList.size(), blob.length());
//Get the InputStream from this Blob.
InputStream in = blob.getBinaryStream();
ArrayList afterUpdateList = new ArrayList();
b = in.read();
while (b > -1) {
afterUpdateList.add(b);
b = in.read();
}
assertEquals(beforeUpdateList.size(), afterUpdateList.size());
//Now check if the two InputStreams
//match
for (int i = 0; i < blob.length(); i++) {
assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
}
os.close();
is.close();
}
protected void setUp() throws Exception {
Connection conn = getConnection();
blob = conn.createBlob();
in = blob.getBinaryStream();
out = blob.setBinaryStream (1);
}
/**
* Tests large blob (more than 4k) to ensure LOBStreamControl uses file.
*/
public void testSetBytesLargeBlob () throws SQLException {
Connection con = getConnection();
con.setAutoCommit (false);
PreparedStatement pstmt = con.prepareStatement("insert into " +
"blobtest (id, data) values (?,?)");
Blob blob = con.createBlob();
byte [] data = new byte [BUFFER_SIZE];
for (int i = 0; i < BUFFER_SIZE; i++) {
data [i] = (byte) (i % 255);
}
//now add more than 4k so file get in use
for (int i = 0; i < 5; i++)
blob.setBytes (i * BUFFER_SIZE + 1, data);
assertEquals (BUFFER_SIZE * 5 , blob.length());
//update blob in the middle
byte [] data1 = new byte [UPDATE_SIZE];
for (int i = 0; i < UPDATE_SIZE; i++)
data1 [i] = 120;//just any value
blob.setBytes (BUFFER_SIZE + 1, data1);
blob.setBytes (BUFFER_SIZE * 5 + 1, data1);
assertEquals (5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
//insert it into table
pstmt.setInt (1, 3);
pstmt.setBlob (2, blob);
pstmt.executeUpdate ();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select data from blobtest where " +
"id = 3");
assertEquals(true, rs.next());
blob = rs.getBlob (1);
byte [] data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
assertEquals (5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
data2 = blob.getBytes (5 * BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//test truncate
blob.truncate (BUFFER_SIZE);
assertEquals ("truncate failed", BUFFER_SIZE, blob.length());
rs.close();
con.commit();
stmt.close();
pstmt.close();
}
/**
* Test the createBlob method implementation in the Connection interface
*
* @exception SQLException, FileNotFoundException, Exception if error occurs
*/
public void testCreateBlob() throws SQLException,
FileNotFoundException,
IOException,
Exception{
Connection conn = getConnection();
int b, c;
Blob blob;
Statement s = createStatement();
PreparedStatement ps =
prepareStatement("insert into blobtable2 (n, blobcol)" + " values(?,?)");
ps.setInt(1,1000);
blob = conn.createBlob();
try {
is = AccessController.doPrivileged(
new PrivilegedExceptionAction<FileInputStream>() {
public FileInputStream run() throws FileNotFoundException {
return new FileInputStream("extin/short.txt");
}
});
} catch (PrivilegedActionException e) {
// e.getException() should be an instance of FileNotFoundException,
// as only "checked" exceptions will be "wrapped" in a
// PrivilegedActionException.
throw (FileNotFoundException) e.getException();
}
OutputStream os = blob.setBinaryStream(1);
ArrayList<Integer> beforeUpdateList = new ArrayList<Integer>();
int actualLength = 0;
c = is.read();
while(c>0) {
os.write(c);
beforeUpdateList.add(c);
c = is.read();
actualLength ++;
}
ps.setBlob(2, blob);
ps.executeUpdate();
Statement stmt = createStatement();
ResultSet rs =
stmt.executeQuery("select blobcol from blobtable2 where n = 1000");
assertTrue(rs.next());
blob = rs.getBlob(1);
assertEquals(beforeUpdateList.size(), blob.length());
//Get the InputStream from this Blob.
InputStream in = blob.getBinaryStream();
ArrayList<Integer> afterUpdateList = new ArrayList<Integer>();
b = in.read();
while (b > -1) {
afterUpdateList.add(b);
b = in.read();
}
assertEquals(beforeUpdateList.size(), afterUpdateList.size());
//Now check if the two InputStreams
//match
for (int i = 0; i < blob.length(); i++) {
assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
}
os.close();
is.close();
}
/**
* tests set bytes method of blob in memory only mode (less than 4k)
*/
public void testSetBytesSmallBlob() throws Exception {
startVMs(1, 4);
Connection con = TestUtil.getConnection();
Statement stmt = con.createStatement();
stmt.execute("create table blobtest (id integer, data Blob)");
stmt.close();
con.setAutoCommit(false);
PreparedStatement pstmt = con.prepareStatement("insert into "
+ "blobtest (id, data) values (?,?)");
pstmt.setInt(1, 1);
Blob blob = con.createBlob();
// add 1024 bytes
byte[] data = new byte[BUFFER_SIZE];
for (int i = 0; i < BUFFER_SIZE; i++) {
data[i] = (byte)(i % 255);
}
blob.setBytes(1, data);
assertEquals(BUFFER_SIZE, blob.length());
pstmt.setBlob(2, blob);
pstmt.executeUpdate();
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select data from blobtest where id = 1");
assertEquals(true, rs.next());
blob = rs.getBlob(1);
assertEquals(BUFFER_SIZE, blob.length());
// update blob in the middle
byte[] data1 = new byte[UPDATE_SIZE];
for (int i = 0; i < UPDATE_SIZE; i++)
data1[i] = 120;// just any value
blob.setBytes(UPDATE_SIZE, data1);
byte[] data2 = blob.getBytes(100, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals(data1[i], data2[i]);
// update it at the end
blob.setBytes(BUFFER_SIZE + 1, data1);
assertEquals(BUFFER_SIZE + UPDATE_SIZE, blob.length());
data2 = blob.getBytes(BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals(data1[i], data2[i]);
// insert the blob and test again
pstmt.setInt(1, 2);
pstmt.setBlob(2, blob);
pstmt.executeUpdate();
rs = stmt.executeQuery("select data from blobtest where id = 2");
assertEquals(true, rs.next());
blob = rs.getBlob(1);
assertEquals(BUFFER_SIZE + UPDATE_SIZE, blob.length());
data2 = blob.getBytes(100, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals(data1[i], data2[i]);
data2 = blob.getBytes(BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals(data1[i], data2[i]);
// test truncate on small size blob
blob = con.createBlob();
data = new byte[100];
for (int i = 0; i < 100; i++) {
data[i] = (byte)i;
}
blob.setBytes(1, data);
assertEquals(blob.length(), 100);
blob.truncate(50);
assertEquals(blob.length(), 50);
blob.setBytes(1, data);
assertEquals("set failed", blob.length(), 100);
blob.truncate(50);
assertEquals("truncation failed", blob.length(), 50);
rs.close();
con.commit();
stmt.close();
pstmt.close();
stmt = con.createStatement();
stmt.execute("drop table blobtest");
stmt.close();
}
/**
* Tests large blob (more than 4k) to ensure LOBStreamControl uses file.
*/
public void testSetBytesLargeBlob() throws SQLException {
Connection con = TestUtil.getConnection();
Statement stmt = con.createStatement();
stmt.execute ("create table blobtest (id integer, data Blob)" + getSuffix());
stmt.close();
con.setAutoCommit (false);
PreparedStatement pstmt = con.prepareStatement("insert into " +
"blobtest (id, data) values (?,?)");
Blob blob = con.createBlob();
byte [] data = new byte [BUFFER_SIZE];
for (int i = 0; i < BUFFER_SIZE; i++) {
data [i] = (byte) (i % 255);
}
//now add more than 4k so file get in use
for (int i = 0; i < 5; i++)
blob.setBytes (i * BUFFER_SIZE + 1, data);
assertEquals (BUFFER_SIZE * 5 , blob.length());
//update blob in the middle
byte [] data1 = new byte [UPDATE_SIZE];
for (int i = 0; i < UPDATE_SIZE; i++)
data1 [i] = 120;//just any value
blob.setBytes (BUFFER_SIZE + 1, data1);
blob.setBytes (BUFFER_SIZE * 5 + 1, data1);
assertEquals (5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
//insert it into table
pstmt.setInt (1, 3);
pstmt.setBlob (2, blob);
pstmt.executeUpdate ();
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select data from blobtest where " +
"id = 3");
assertEquals(true, rs.next());
blob = rs.getBlob (1);
byte [] data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
assertEquals (5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
data2 = blob.getBytes (5 * BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//test truncate
blob.truncate (BUFFER_SIZE);
assertEquals ("truncate failed", BUFFER_SIZE, blob.length());
rs.close();
con.commit();
stmt.close();
pstmt.close();
}
/**
* tests set bytes method of blob in memory only mode (less than 4k)
*/
public void testSetBytesSmallBlob () throws SQLException {
Connection con = TestUtil.getConnection();
Statement stmt = con.createStatement();
stmt.execute ("create table blobtest (id integer, data Blob)"+ getSuffix());
stmt.close();
con.setAutoCommit (false);
PreparedStatement pstmt = con.prepareStatement("insert into " +
"blobtest (id, data) values (?,?)");
pstmt.setInt (1,1);
Blob blob = con.createBlob();
//add 1024 bytes
byte [] data = new byte [BUFFER_SIZE];
for (int i = 0; i < BUFFER_SIZE; i++) {
data [i] = (byte) (i % 255);
}
blob.setBytes (1, data);
assertEquals (BUFFER_SIZE, blob.length());
pstmt.setBlob (2, blob);
pstmt.executeUpdate();
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select data from blobtest where id = 1");
assertEquals(true, rs.next());
blob = rs.getBlob (1);
assertEquals (BUFFER_SIZE, blob.length());
//update blob in the middle
byte [] data1 = new byte [UPDATE_SIZE];
for (int i = 0; i < UPDATE_SIZE; i++)
data1 [i] = 120;//just any value
blob.setBytes (UPDATE_SIZE, data1);
byte [] data2 = blob.getBytes (100, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//update it at the end
blob.setBytes (BUFFER_SIZE + 1, data1);
assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//insert the blob and test again
pstmt.setInt (1, 2);
pstmt.setBlob (2, blob);
pstmt.executeUpdate();
rs = stmt.executeQuery("select data from blobtest where " +
"id = 2");
assertEquals(true, rs.next());
blob = rs.getBlob (1);
assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
data2 = blob.getBytes (100, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//test truncate on small size blob
blob = con.createBlob();
data = new byte [100];
for (int i = 0; i < 100; i++) {
data [i] = (byte) i;
}
blob.setBytes (1, data);
assertEquals (blob.length(), 100);
blob.truncate (50);
assertEquals (blob.length(), 50);
blob.setBytes (1, data);
assertEquals ("set failed", blob.length(), 100);
blob.truncate (50);
assertEquals ("truncation failed", blob.length(), 50);
rs.close();
con.commit();
stmt.close();
pstmt.close();
stmt = con.createStatement();
stmt.execute ("drop table blobtest");
this.waitTillAllClear();
stmt.close();
}
/**
* Test the createBlob method implementation in the Connection interface
*
* @exception SQLException, FileNotFoundException, Exception if error occurs
*/
public void testCreateBlob() throws SQLException,
FileNotFoundException,
IOException,
Exception{
Connection conn = getConnection();
int b, c;
Blob blob;
Statement s = createStatement();
PreparedStatement ps =
prepareStatement("insert into blobtable2 (n, blobcol)" + " values(?,?)");
ps.setInt(1,1000);
blob = conn.createBlob();
try {
is = (FileInputStream) AccessController.doPrivileged(
new PrivilegedExceptionAction() {
public Object run() throws FileNotFoundException {
return new FileInputStream("extin/short.txt");
}
});
} catch (PrivilegedActionException e) {
// e.getException() should be an instance of FileNotFoundException,
// as only "checked" exceptions will be "wrapped" in a
// PrivilegedActionException.
throw (FileNotFoundException) e.getException();
}
OutputStream os = blob.setBinaryStream(1);
ArrayList beforeUpdateList = new ArrayList();
int actualLength = 0;
c = is.read();
while(c>0) {
os.write(c);
beforeUpdateList.add(c);
c = is.read();
actualLength ++;
}
ps.setBlob(2, blob);
ps.executeUpdate();
Statement stmt = createStatement();
ResultSet rs =
stmt.executeQuery("select blobcol from blobtable2 where n = 1000");
assertTrue(rs.next());
blob = rs.getBlob(1);
assertEquals(beforeUpdateList.size(), blob.length());
//Get the InputStream from this Blob.
InputStream in = blob.getBinaryStream();
ArrayList afterUpdateList = new ArrayList();
b = in.read();
while (b > -1) {
afterUpdateList.add(b);
b = in.read();
}
assertEquals(beforeUpdateList.size(), afterUpdateList.size());
//Now check if the two InputStreams
//match
for (int i = 0; i < blob.length(); i++) {
assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
}
os.close();
is.close();
}
protected void setUp() throws Exception {
Connection conn = getConnection();
blob = conn.createBlob();
in = blob.getBinaryStream();
out = blob.setBinaryStream (1);
}
/**
* Tests large blob (more than 4k) to ensure LOBStreamControl uses file.
*/
public void testSetBytesLargeBlob () throws SQLException {
Connection con = getConnection();
con.setAutoCommit (false);
PreparedStatement pstmt = con.prepareStatement("insert into " +
"blobtest (id, data) values (?,?)");
Blob blob = con.createBlob();
byte [] data = new byte [BUFFER_SIZE];
for (int i = 0; i < BUFFER_SIZE; i++) {
data [i] = (byte) (i % 255);
}
//now add more than 4k so file get in use
for (int i = 0; i < 5; i++)
blob.setBytes (i * BUFFER_SIZE + 1, data);
assertEquals (BUFFER_SIZE * 5 , blob.length());
//update blob in the middle
byte [] data1 = new byte [UPDATE_SIZE];
for (int i = 0; i < UPDATE_SIZE; i++)
data1 [i] = 120;//just any value
blob.setBytes (BUFFER_SIZE + 1, data1);
blob.setBytes (BUFFER_SIZE * 5 + 1, data1);
assertEquals (5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
//insert it into table
pstmt.setInt (1, 3);
pstmt.setBlob (2, blob);
pstmt.executeUpdate ();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select data from blobtest where " +
"id = 3");
assertEquals(true, rs.next());
blob = rs.getBlob (1);
byte [] data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
assertEquals (5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
data2 = blob.getBytes (5 * BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//test truncate
blob.truncate (BUFFER_SIZE);
assertEquals ("truncate failed", BUFFER_SIZE, blob.length());
rs.close();
con.commit();
stmt.close();
pstmt.close();
}
/**
* tests set bytes method of blob in memory only mode (less than 4k)
*/
public void testSetBytesSmallBlob () throws SQLException {
Connection con = getConnection();
con.setAutoCommit (false);
PreparedStatement pstmt = con.prepareStatement("insert into " +
"blobtest (id, data) values (?,?)");
pstmt.setInt (1,1);
Blob blob = con.createBlob();
//add 1024 bytes
byte [] data = new byte [BUFFER_SIZE];
for (int i = 0; i < BUFFER_SIZE; i++) {
data [i] = (byte) (i % 255);
}
blob.setBytes (1, data);
assertEquals (BUFFER_SIZE, blob.length());
pstmt.setBlob (2, blob);
pstmt.executeUpdate();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
"select data from blobtest where id = 1");
assertEquals(true, rs.next());
blob = rs.getBlob (1);
assertEquals (BUFFER_SIZE, blob.length());
//update blob in the middle
byte [] data1 = new byte [UPDATE_SIZE];
for (int i = 0; i < UPDATE_SIZE; i++)
data1 [i] = 120;//just any value
blob.setBytes (UPDATE_SIZE, data1);
byte [] data2 = blob.getBytes (100, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//update it at the end
blob.setBytes (BUFFER_SIZE + 1, data1);
assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//insert the blob and test again
pstmt.setInt (1, 2);
pstmt.setBlob (2, blob);
pstmt.executeUpdate();
rs = stmt.executeQuery("select data from blobtest where " +
"id = 2");
assertEquals(true, rs.next());
blob = rs.getBlob (1);
assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
data2 = blob.getBytes (100, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
for (int i = 0; i < UPDATE_SIZE; i++)
assertEquals (data1 [i], data2 [i]);
//test truncate on small size blob
blob = con.createBlob();
data = new byte [100];
for (int i = 0; i < 100; i++) {
data [i] = (byte) i;
}
blob.setBytes (1, data);
assertEquals (blob.length(), 100);
blob.truncate (50);
assertEquals (blob.length(), 50);
blob.setBytes (1, data);
assertEquals ("set failed", blob.length(), 100);
blob.truncate (50);
assertEquals ("truncation failed", blob.length(), 50);
rs.close();
con.commit();
stmt.close();
pstmt.close();
}