下面列出了java.sql.PreparedStatement#setCharacterStream ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
public static void setRandomValue(Random r,
PreparedStatement ps, int column, int jdbcType, int precision)
throws SQLException, IOException
{
Object val = getRandomValue(r, jdbcType, precision);
if (val instanceof StringReaderWithLength) {
StringReaderWithLength rd = (StringReaderWithLength) val;
ps.setCharacterStream(column, rd, rd.getLength());
} else if (val instanceof InputStream) {
InputStream in = (InputStream) val;
ps.setBinaryStream(column, in, in.available());
} else
ps.setObject(column, val, jdbcType);
}
@Override
public void setClobAsCharacterStream(
PreparedStatement ps, int paramIndex, Reader characterStream, int contentLength)
throws SQLException {
if (streamAsLob) {
if (characterStream != null) {
if (contentLength >= 0) {
ps.setClob(paramIndex, characterStream, contentLength);
}
else {
ps.setClob(paramIndex, characterStream);
}
}
else {
ps.setClob(paramIndex, (Clob) null);
}
}
else if (wrapAsLob) {
if (characterStream != null) {
ps.setClob(paramIndex, new PassThroughClob(characterStream, contentLength));
}
else {
ps.setClob(paramIndex, (Clob) null);
}
}
else if (contentLength >= 0) {
ps.setCharacterStream(paramIndex, characterStream, contentLength);
}
else {
ps.setCharacterStream(paramIndex, characterStream);
}
if (logger.isDebugEnabled()) {
logger.debug(characterStream != null ? "Set character stream for CLOB with length " + contentLength :
"Set CLOB to null");
}
}
public void testUpdateClobLengthless()
throws Exception {
// Life span of Clob objects are limited by the transaction. Need
// autocommit off so Clob objects survive execution of next statement.
getConnection().setAutoCommit(false);
Reader r1 = new java.io.StringReader(new String(BYTES1));
// InputStream for insertion.
Reader r2 = new java.io.StringReader(new String(BYTES2));
// Prepared Statement used to insert the data
PreparedStatement ps_sb = prep("dClob");
ps_sb.setInt(1, key);
ps_sb.setCharacterStream(2, r1);
ps_sb.executeUpdate();
ps_sb.close();
// Update operation
ResultSet rs1 = fetchUpd("dClob", key);
rs1.next();
rs1.updateClob(1, r2);
rs1.updateRow();
rs1.close();
// Query to see whether the data that has been updated.
rs1 = fetch("dClob", key);
rs1.next();
assertEquals(new StringReader(new String(BYTES2)),
rs1.getCharacterStream(1));
rs1.close();
}
public void testUpdateCharacterStreamLengthless()
throws IOException, SQLException {
String str = "This is the (\u0FFF\u1234) test string";
String strUpdated = "An updated (\u0FEF\u9876) test string";
// Insert test data
PreparedStatement psChar = prep("dLongVarchar");
psChar.setInt(1, key);
psChar.setCharacterStream(2, new StringReader(str));
psChar.execute();
psChar.close();
// Update test data
ResultSet rs = fetchUpd("dLongVarchar", key);
rs.next();
rs.updateCharacterStream(1, new StringReader(strUpdated));
rs.updateRow();
rs.close();
// Verify that update took place and is correct.
rs = fetch("dLongVarchar", key);
rs.next();
Reader updatedStr = rs.getCharacterStream(1);
for (int i=0; i < strUpdated.length(); i++) {
assertEquals("Strings differ at index " + i,
strUpdated.charAt(i),
updatedStr.read());
}
assertEquals("Too much data in stream", -1, updatedStr.read());
updatedStr.close();
}
public void testUpdateCharacterStreamLengthless()
throws IOException, SQLException {
String str = "This is the (\u0FFF\u1234) test string";
String strUpdated = "An updated (\u0FEF\u9876) test string";
// Insert test data
PreparedStatement psChar = prep("dLongVarchar");
psChar.setInt(1, key);
psChar.setCharacterStream(2, new StringReader(str));
psChar.execute();
psChar.close();
// Update test data
ResultSet rs = fetchUpd("dLongVarchar", key);
rs.next();
rs.updateCharacterStream(1, new StringReader(strUpdated));
rs.updateRow();
rs.close();
// Verify that update took place and is correct.
rs = fetch("dLongVarchar", key);
rs.next();
Reader updatedStr = rs.getCharacterStream(1);
for (int i=0; i < strUpdated.length(); i++) {
assertEquals("Strings differ at index " + i,
strUpdated.charAt(i),
updatedStr.read());
}
assertEquals("Too much data in stream", -1, updatedStr.read());
updatedStr.close();
}
public void testLargeMultiByteCharLob() throws SQLException, IOException {
getConnection().setAutoCommit(false);
Statement s = createStatement();
PreparedStatement ps = prepareStatement("INSERT INTO MB_CLOBTABLE VALUES(?,?)");
// We allocate 16MB for the test so use something bigger than that.
ps.setInt(1,1);
LoopingAlphabetReader reader = new LoopingAlphabetReader(LONG_CLOB_LENGTH, CharAlphabet.cjkSubset());
ps.setCharacterStream(2, reader, LONG_CLOB_LENGTH);
ps.executeUpdate();
ResultSet rs = s.executeQuery("SELECT K, LENGTH(C), C FROM MB_CLOBTABLE" +
"-- GEMFIREXD-PROPERTIES constraint=pk\n ORDER BY K");
rs.next();
assertEquals(LONG_CLOB_LENGTH_STRING, rs.getString(2));
// make sure we can still access the clob after getting length.
// It should be ok because we reset the stream
Reader rsReader = rs.getCharacterStream(3);
int len= 0;
char[] buf = new char[32672];
for (;;) {
int size = rsReader.read(buf);
if (size == -1)
break;
len += size;
int expectedValue = ((len -1) % 12) + '\u4E00';
if (size != 0)
assertEquals(expectedValue,buf[size -1]);
}
assertEquals(LONG_CLOB_LENGTH, len);
rs.close();
// Select just length without selecting the clob.
rs = s.executeQuery("SELECT K, LENGTH(C) FROM MB_CLOBTABLE " +
"ORDER BY K");
JDBC.assertFullResultSet(rs, new String [][] {{"1",LONG_CLOB_LENGTH_STRING}});
}
@Test
public void testQueryWithMultiHost() throws SQLException {
PreparedStatement preparedStatement = multiHostConnection.prepareStatement("SELECT * FROM test WHERE a = ? or b = ? or c=? or d = ?", 1, 1);
preparedStatement.setAsciiStream(1, inputStream);
preparedStatement.setAsciiStream(2, inputStream, 10);
preparedStatement.setAsciiStream(3, inputStream, 1000000L);
preparedStatement.setCharacterStream(4, reader);
ResultSet resultSet = preparedStatement.executeQuery();
preparedStatement.close();
verify(mysqlPreparedStatement).executeQuery();
verify(mysqlPreparedStatement).close();
}
/**
* Tests that the max field size limit is handled correctly when accessing
* values as streams. The limit should apply for VARCHAR, but not for CLOB.
*
* @throws IOException if something goes wrong
* @throws SQLException if something goes wrong
*/
public void testSetMaxFieldSizeLarge()
throws IOException, SQLException {
// Insert test data.
int id = 1;
int clobSize = 2*1024*1024; // 2 MB
int vcSize = 32672;
int limit = 10;
PreparedStatement ps = prepareStatement(
"insert into setMaxFieldSize values (?,?,?)");
ps.setInt(1, id);
ps.setCharacterStream(2, new LoopingAlphabetReader(vcSize), vcSize);
ps.setCharacterStream(3, new LoopingAlphabetReader(clobSize), clobSize);
ps.executeUpdate();
// Fetch data back with a limit.
Statement stmt = createStatement();
stmt.setMaxFieldSize(limit);
ResultSet rs = stmt.executeQuery("select dVarchar, dClob from " +
"setMaxFieldSize where id = " + id);
assertTrue(rs.next());
String vcStr = drainStringFromSource(rs.getCharacterStream(1));
// Limit should apply to VARCHAR.
assertEquals(limit, vcStr.length());
// Limit should *not* apply to CLOB.
String vsClob = drainStringFromSource(rs.getCharacterStream(2));
assertEquals(clobSize, vsClob.length());
rs.close();
// Again, but without a limit.
stmt = createStatement();
rs = stmt.executeQuery("select dVarchar, dClob from " +
"setMaxFieldSize where id = " + id);
assertTrue(rs.next());
vcStr = drainStringFromSource(rs.getCharacterStream(1));
assertEquals(vcSize, vcStr.length());
vsClob = drainStringFromSource(rs.getCharacterStream(2));
assertEquals(clobSize, vsClob.length());
rs.close();
}
public void testUpdateTriggerOnClobColumn() throws SQLException, IOException
{
Connection conn = getConnection();
Statement s = createStatement();
String trig = " create trigger t_lob1 after update of str1 on lob1 ";
trig = trig + " REFERENCING OLD AS old NEW AS new FOR EACH ROW MODE DB2SQL ";
trig = trig + " insert into t_lob1_log(oldvalue, newvalue) values (old.str1, new.str1)";
s.executeUpdate("create table LOB1 (str1 Varchar(80), C_lob CLOB(50M))");
s.executeUpdate("create table t_lob1_log(oldvalue varchar(80), newvalue varchar(80), chng_time timestamp default current_timestamp)");
s.executeUpdate(trig);
conn.commit();
PreparedStatement ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?)");
int clobSize = 1024*64+1;
ps.setString(1, clobSize +"");
// - set the value of the input parameter to the input stream
ps.setCharacterStream(2, makeCharArrayReader('a', clobSize), clobSize);
ps.execute();
conn.commit();
PreparedStatement ps2 = prepareStatement("update LOB1 set c_lob = ? where str1 = '" + clobSize + "'");
ps2.setCharacterStream(1,makeCharArrayReader('b',clobSize), clobSize);
ps2.executeUpdate();
conn.commit();
// --- reading the clob make sure it was updated
ResultSet rs = s.executeQuery("SELECT * FROM LOB1 where str1 = '" + clobSize + "'");
rs.next();
Reader r = rs.getCharacterStream(2);
char expectedCharValue = 'b';
assertReaderContents(r, clobSize, expectedCharValue);
rs.close();
s.executeUpdate("drop table lob1");
s.executeUpdate("drop table t_lob1_log");
}
@Override
public void setCharacterStream(final int parameterIndex, final Reader reader)
throws SQLException {
ParameterCallback callback = new ParameterCallbackAction(
parameterIndex, reader) {
@Override
public void call(PreparedStatement preparedStatement)
throws SQLException {
preparedStatement.setCharacterStream(parameterIndex(), reader);
}
};
addParameterCallback(callback);
}
public static Test baseSuite(String name) {
TestSuite suite = new TestSuite(name);
suite.addTestSuite(LargeDataLocksTest.class);
return new CleanDatabaseTestSetup(suite) {
/**
* Create and populate table
*
* @see org.apache.derbyTesting.junit.CleanDatabaseTestSetup#decorateSQL(java.sql.Statement)
*/
protected void decorateSQL(Statement s) throws SQLException {
Connection conn = getConnection();
conn.setAutoCommit(false);
PreparedStatement ps = null;
String sql;
sql = "CREATE TABLE t1 (bc CLOB(1M), bincol BLOB(1M), datalen int)";
s.executeUpdate(sql);
// Insert big and little values
sql = "INSERT into t1 values(?,?,?)";
ps = conn.prepareStatement(sql);
ps.setCharacterStream(1, new java.io.StringReader(Utilities
.repeatChar("a", 38000)), 38000);
ps.setBytes(2, Utilities.repeatChar("a", 38000).getBytes());
ps.setInt(3, 38000);
ps.executeUpdate();
ps.close();
conn.commit();
}
};
}
public void testUpdateClobLengthlessParameterName()
throws Exception {
Reader r1 = new java.io.StringReader(new String(BYTES1));
// InputStream for insertion.
Reader r2 = new java.io.StringReader(new String(BYTES2));
// Prepared Statement used to insert the data
PreparedStatement ps_sb = prep("dClob");
ps_sb.setInt(1, key);
ps_sb.setCharacterStream(2, r1);
ps_sb.executeUpdate();
ps_sb.close();
// Update operation
ResultSet rs1 = fetchUpd("dClob", key);
rs1.next();
rs1.updateClob("dClob", r2);
rs1.updateRow();
rs1.close();
// Query to see whether the data that has been updated.
rs1 = fetch("dClob", key);
rs1.next();
assertEquals(new StringReader(new String(BYTES2)),
rs1.getCharacterStream(1));
rs1.close();
}
/**
* Tests updates on reader.
*/
public void testUpdateableReader () throws Exception {
getConnection().setAutoCommit (false);
PreparedStatement ps = prepareStatement ("insert into updateClob " +
"(id , data) values (? ,?)");
ps.setInt (1, 1);
StringBuilder sb = new StringBuilder ();
String base = "SampleSampleSample";
for (int i = 0; i < 100; i++) {
sb.append (base);
}
ps.setCharacterStream (2, new StringReader (sb.toString()),
sb.length());
ps.execute();
ps.close();
Statement stmt = createStatement ();
ResultSet rs = stmt.executeQuery("select data from " +
"updateClob where id = 1");
rs.next();
Clob clob = rs.getClob (1);
rs.close();
stmt.close();
assertEquals (sb.length(), clob.length());
Reader r = clob.getCharacterStream();
char [] clobData = new char [sb.length()];
r.read (clobData);
assertEquals ("mismatch from inserted string",
String.valueOf (clobData), sb.toString());
r.close();
//update before gettting the reader
clob.setString (50, dummy);
r = clob.getCharacterStream();
r.skip (49);
char [] newChars = new char [dummy.length()];
r.read (newChars);
assertEquals ("update not reflected", dummy,
String.valueOf (newChars));
//update again and see if stream is refreshed
clob.setString (75, dummy);
r.skip (75 - 50 - dummy.length());
char [] testChars = new char [dummy.length()];
r.read (testChars);
assertEquals ("update not reflected", dummy,
String.valueOf (newChars));
r.close();
//try inserting some unicode string
String unicodeStr = getUnicodeString();
clob.setString (50, unicodeStr);
char [] utf16Chars = new char [unicodeStr.length()];
r = clob.getCharacterStream();
r.skip(49);
r.read(utf16Chars);
assertEquals ("update not reflected", unicodeStr,
String.valueOf (utf16Chars));
r.close();
Writer w = clob.setCharacterStream (1);
//write enough data to switch the data to file
r = clob.getCharacterStream ();
for (int i = 0; i < 10000; i++) {
w.write (dummy);
}
w.close();
clob.setString (500, unicodeStr);
r.skip (499);
char [] unicodeChars = new char [unicodeStr.length()];
r.read (unicodeChars);
assertEquals ("update not reflected", unicodeStr,
String.valueOf (unicodeChars));
}
@Override
public void setNonNullParameter(PreparedStatement ps, int index, String parameter, JdbcType jdbcType)
throws SQLException {
StringReader reader = new StringReader(parameter);
ps.setCharacterStream(index, reader, parameter.length());
}
protected void setClob(final PreparedStatement ps, final int index, final Clob clob) throws SQLException {
Reader reader = clob.getCharacterStream();
ps.setCharacterStream(index, reader, (int) clob.length());
registerResource(reader);
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
StringReader reader = new StringReader(parameter);
ps.setCharacterStream(i, reader, parameter.length());
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType)
throws SQLException {
StringReader reader = new StringReader(parameter);
ps.setCharacterStream(i, reader, parameter.length());
}
/**
* Test updating a large clob
*/
public void testUpdateableStoreReader () throws Exception {
getConnection().setAutoCommit (false);
PreparedStatement ps = prepareStatement ("insert into updateClob " +
"(id , data) values (? ,?)");
ps.setInt (1, 2);
StringBuilder sb = new StringBuilder ();
String base = "SampleSampleSample";
for (int i = 0; i < 100000; i++) {
sb.append (base);
}
//insert a large enough data to ensure stream is created in dvd
ps.setCharacterStream (2, new StringReader (sb.toString()),
sb.length());
ps.execute();
ps.close();
Statement stmt = createStatement ();
ResultSet rs = stmt.executeQuery("select data from " +
"updateClob where id = 2");
rs.next();
Clob clob = rs.getClob (1);
rs.close();
stmt.close();
assertEquals (sb.length(), clob.length());
Reader r = clob.getCharacterStream();
String newString = "this is a new string";
//access reader before modifying the clob
long l = r.skip (100);
clob.setString (1001, newString);
//l chars are already skipped
long toSkip = 1000 - l;
while (toSkip > 0) {
long skipped = r.skip (toSkip);
toSkip -= skipped;
}
char [] newdata = new char [newString.length()];
int len = r.read(newdata);
assertEquals ("updated not reflected", newString,
new String (newdata, 0, len));
r.close();
}
/**
* Insert a clob and test length.
*
* @param lengthless if true use the lengthless setCharacterStream api
*
* @throws SQLException
* @throws IOException
* @throws InvocationTargetException
* @throws IllegalAccessException
* @throws IllegalArgumentException
*/
private void testClobLength(boolean lengthless) throws SQLException, IOException, IllegalArgumentException,
IllegalAccessException, InvocationTargetException {
getConnection().setAutoCommit(false);
Statement s = createStatement();
s.executeUpdate("CREATE TABLE CLOBTABLE (K INT CONSTRAINT PK PRIMARY KEY, C CLOB(" + LONG_CLOB_LENGTH + "))");
PreparedStatement ps = prepareStatement("INSERT INTO CLOBTABLE VALUES(?,?)");
// We allocate 16MB for the test so use something bigger than that.
ps.setInt(1,1);
LoopingAlphabetReader reader = new LoopingAlphabetReader(LONG_CLOB_LENGTH);
if (lengthless) {
Method m = null;
try {
Class c = ps.getClass();
m = c.getMethod("setCharacterStream",new Class[] {Integer.TYPE,
InputStream.class});
} catch (NoSuchMethodException e) {
// ignore method not found as method may not be present for
// jdk's lower than 1.6.
println("Skipping lengthless insert because method is not available");
return;
}
m.invoke(ps, new Object[] {new Integer(2), reader});
}
else
ps.setCharacterStream(2, reader, LONG_CLOB_LENGTH);
ps.executeUpdate();
// insert a zero length clob.
ps.setInt(1, 2);
ps.setString(2, "");
ps.executeUpdate();
// insert a null clob.
ps.setInt(1, 3);
ps.setString(2,null);
ps.executeUpdate();
// insert a short clob
ps.setInt(1, 4);
ps.setString(2, new String(SHORT_CLOB_CHARS));
ps.executeUpdate();
// Currently need to use optimizer override to force use of the index.
// Derby should use sort avoidance and do it automatically, but there
// appears to be a bug.
ResultSet rs = s.executeQuery("SELECT K, LENGTH(C), C FROM CLOBTABLE" +
"-- GEMFIREXD-PROPERTIES constraint=pk\n ORDER BY K");
rs.next();
assertEquals(LONG_CLOB_LENGTH_STRING,rs.getString(2));
// make sure we can still access the clob after getting length.
// It should be ok because we reset the stream
Reader rsReader = rs.getCharacterStream(3);
int len= 0;
char[] buf = new char[32672];
for (;;) {
int size = rsReader.read(buf);
if (size == -1)
break;
len += size;
int expectedValue = ((len -1) % 26) + 'a';
if (size != 0)
assertEquals(expectedValue,buf[size -1]);
}
assertEquals(LONG_CLOB_LENGTH,len);
// empty clob
rs.next();
assertEquals("0",rs.getString(2));
String chars = rs.getString(3);
assertEquals(0, chars.length());
// null clob
rs.next();
assertEquals(null, rs.getString(2));
chars = rs.getString(3);
assertEquals(null, chars);
// short clob
rs.next();
assertEquals("" + SHORT_CLOB_CHARS.length , rs.getString(2));
chars = rs.getString(3);
assertTrue(Arrays.equals(chars.toCharArray(), SHORT_CLOB_CHARS));
rs.close();
// Select just length without selecting the clob.
rs = s.executeQuery("SELECT K, LENGTH(C) FROM CLOBTABLE " +
"ORDER BY K");
JDBC.assertFullResultSet(rs, new String [][] {{"1",LONG_CLOB_LENGTH_STRING},{"2","0"},
{"3",null},{"4","6"}});
}
/**
* Inserts a small (smaller than internal conversion buffer) string value.
*
* @param colIndex column to insert into (see constants)
* @param lengthless whether the length of the stream should be specified
* or not on insertion
* @param totalLength the total character length of the stream to insert
* @param blanks number of trailing blanks in the stream
* @return The id of the row inserted.
*
* @throws IOException if reading from the source stream fails
* @throws SQLException if something goes wrong, or the test fails
*/
private int insertSmall(int colIndex, boolean lengthless,
int totalLength, int blanks)
throws IOException, SQLException {
int id = ID.getAndAdd(1);
PreparedStatement ps = prepareStatement(
"insert into " + TABLE_SMALL + " values (?,?,?,?,?)");
ps.setInt(1, id);
ps.setNull(2, Types.CLOB);
ps.setNull(3, Types.VARCHAR);
ps.setNull(4, Types.LONGVARCHAR);
ps.setNull(5, Types.CHAR);
int colWidth = SMALL_SIZE;
if (colIndex == LONGVARCHAR) {
colWidth = 32700;
}
int expectedLength = Math.min(totalLength, colWidth);
// Length of CHAR is always the defined length due to padding.
if (colIndex == CHAR) {
colWidth = expectedLength = CHAR_SIZE;
}
println("totalLength=" + totalLength + ", blanks=" + blanks +
", colWidth=" + colWidth + ", expectedLength=" +
expectedLength);
Reader source = new LoopingAlphabetReader(totalLength,
CharAlphabet.modernLatinLowercase(), blanks);
// Now set what we are going to test.
if (lengthless) {
ps.setCharacterStream(colIndex, source);
} else {
ps.setCharacterStream(colIndex, source, totalLength);
}
try {
// Exceute the insert.
assertEquals(1, ps.executeUpdate());
if (totalLength > expectedLength) {
assertTrue(totalLength - blanks <= expectedLength);
}
// Fetch the value.
assertEquals(expectedLength,
getStreamLength(TABLE_SMALL, colIndex, id));
} catch (SQLException sqle) {
// Sanity check of the length.
if (colIndex == LONGVARCHAR) {
// Truncation is not allowed.
assertTrue(totalLength > expectedLength);
} else {
// Total length minus blanks must still be larger then the
// expected length.
assertTrue(totalLength - blanks > expectedLength);
}
// The error handling here is very fuzzy...
// This will hopefully be fixed, such that the exception thrown
// will always be 22001. Today this is currently wrapped by several
// other exceptions.
String expectedState = "XSDA4";
if (colIndex == CHAR || colIndex == VARCHAR) {
if (lengthless) {
expectedState = "XJ001";
} else {
if (!usingEmbedded()) {
expectedState = "XJ001";
} else {
expectedState = "22001";
}
}
}
assertSQLState(expectedState, sqle);
}
return id;
}