下面列出了java.sql.PreparedStatement#setClob ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
@Override
public void setClobAsCharacterStream(
PreparedStatement ps, int paramIndex, final Reader characterStream, int contentLength)
throws SQLException {
if (characterStream != null) {
Clob clob = (Clob) createLob(ps, true, new LobCallback() {
@Override
public void populateLob(Object lob) throws Exception {
Method methodToInvoke = lob.getClass().getMethod("getCharacterOutputStream", (Class[]) null);
Writer writer = (Writer) methodToInvoke.invoke(lob, (Object[]) null);
FileCopyUtils.copy(characterStream, writer);
}
});
ps.setClob(paramIndex, clob);
if (logger.isDebugEnabled()) {
logger.debug("Set character stream for Oracle CLOB with length " + clob.length());
}
}
else {
ps.setClob(paramIndex, (Clob) null);
logger.debug("Set Oracle CLOB to null");
}
}
@Test
public void testClob() throws Exception {
String clobString = "A TEST OF CLOB INSERT...";
PreparedStatement ps = conn.prepareStatement("INSERT INTO documents VALUES (?, ?)");
Clob clob = conn.createClob();
clob.setString(1,clobString);
ps.setInt(1, 100);
ps.setClob(2, clob);
ps.execute();
ps = conn.prepareStatement("SELECT c FROM documents where id=100");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String s = rs.getString(1);
Assert.assertEquals(s, clobString, s);
}
}
@Override
public void setClobAsCharacterStream(
PreparedStatement ps, int paramIndex, Reader characterStream, int contentLength)
throws SQLException {
Clob clob = ps.getConnection().createClob();
try {
FileCopyUtils.copy(characterStream, clob.setCharacterStream(1));
}
catch (IOException ex) {
throw new DataAccessResourceFailureException("Could not copy into LOB stream", ex);
}
this.temporaryClobs.add(clob);
ps.setClob(paramIndex, clob);
if (logger.isDebugEnabled()) {
logger.debug(characterStream != null ?
"Copied character stream into temporary CLOB with length " + contentLength :
"Set CLOB to null");
}
}
public static final void setStatementParameters(Connection conn, PreparedStatement ps,
UUID stmt_id,
StringBuilder xmlFragment) throws SQLException {
ps.setString(1, (stmt_id != null ? stmt_id.toString() : null));
final Clob c = conn.createClob();
try {
c.setCharacterStream(1).write(xmlFragment.toString());
} catch (IOException e) {
if (GemFireXDUtils.TracePlanAssertion) {
SanityManager.DEBUG_PRINT(GfxdConstants.TRACE_PLAN_ASSERTION,
"couldn't set clob stream.", e);
}
else if (GemFireXDUtils.TracePlanGeneration) {
SanityManager.DEBUG_PRINT(GfxdConstants.TRACE_PLAN_GENERATION,
"couldn't set clob stream.");
}
}
ps.setClob(2, c);
}
/**
* Tests the ClobWriter.write(String str) method
**/
public void testClobCharacterWrite1ParamString() throws Exception
{
PreparedStatement stmt3 = prepareStatement(
"SELECT c FROM testBlobX1 WHERE a = 1");
ResultSet rs3 = stmt3.executeQuery();
rs3.next();
Clob clob = rs3.getClob(1);
assertTrue("FAIL -- clob is NULL", clob != null);
Writer clobWriter = clob.setCharacterStream(1L);
clobWriter.write(unicodeTestString);
clobWriter.close();
PreparedStatement stmt4 = prepareStatement(
"UPDATE testBlobX1 SET c = ? WHERE a = 1");
stmt4.setClob(1, clob);
stmt4.executeUpdate();
stmt4.close();
rs3.close();
rs3 = stmt3.executeQuery();
assertTrue("FAIL -- clob not found", rs3.next());
long new_length = rs3.getClob(1).length();
assertEquals("FAIL -- wrong clob length", unicodeTestString.length(), new_length);
// Check contents ...
Reader lStream = rs3.getClob(1).getCharacterStream();
assertTrue("FAIL - Clob and buffer contents do not match",
compareClobReader2CharArray(
unicodeTestString.toCharArray(),
lStream));
lStream.close();
rs3.close();
stmt3.close();
}
@Override
public void setClobAsString(PreparedStatement ps, int paramIndex, @Nullable String content)
throws SQLException {
if (streamAsLob) {
if (content != null) {
ps.setClob(paramIndex, new StringReader(content), content.length());
}
else {
ps.setClob(paramIndex, (Clob) null);
}
}
else if (wrapAsLob) {
if (content != null) {
ps.setClob(paramIndex, new PassThroughClob(content));
}
else {
ps.setClob(paramIndex, (Clob) null);
}
}
else {
ps.setString(paramIndex, content);
}
if (logger.isDebugEnabled()) {
logger.debug(content != null ? "Set string for CLOB with length " + content.length() :
"Set CLOB to null");
}
}
@Override
public void setClobAsCharacterStream(
PreparedStatement ps, int paramIndex, Reader characterStream, int contentLength)
throws SQLException {
if (streamAsLob) {
if (characterStream != null) {
ps.setClob(paramIndex, characterStream, contentLength);
}
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 {
ps.setCharacterStream(paramIndex, characterStream, contentLength);
}
if (logger.isDebugEnabled()) {
logger.debug(characterStream != null ? "Set character stream for CLOB with length " + contentLength :
"Set CLOB to null");
}
}
@Override
public void setClobAsString(PreparedStatement ps, int paramIndex, @Nullable String content)
throws SQLException {
if (streamAsLob) {
if (content != null) {
ps.setClob(paramIndex, new StringReader(content), content.length());
}
else {
ps.setClob(paramIndex, (Clob) null);
}
}
else if (wrapAsLob) {
if (content != null) {
ps.setClob(paramIndex, new PassThroughClob(content));
}
else {
ps.setClob(paramIndex, (Clob) null);
}
}
else {
ps.setString(paramIndex, content);
}
if (logger.isDebugEnabled()) {
logger.debug(content != null ? "Set string for CLOB with length " + content.length() :
"Set CLOB to null");
}
}
protected void addBatchInsert(PreparedStatement stmt, int oid, int cid, int sid, int qty,
String status, Timestamp time, BigDecimal bid, int tid, boolean isPut) throws SQLException {
JSONObject json = new JSONObject();
String jsonLog ="";
if (SQLTest.hasJSON && ! SQLHelper.isDerbyConn(stmt.getConnection()) ) {
json = getJSONObject(oid,cid,sid,qty,status,time,bid,tid);
jsonLog = ",JSON_DETAILS: " +json.toJSONString();
}
Log.getLogWriter().info( (SQLHelper.isDerbyConn(stmt.getConnection())? "Derby - " :"gemfirexd - " ) + (isPut ? "putting " : "inserting ") + " into trade.buyorders with data OID:" + oid +
",CID:"+ cid + ",SID:" + sid + ",QTY:" + qty + ",STATUS:" + status +
",TIME:"+ time + ",BID:" + bid + ",TID:" + tid + jsonLog);
stmt.setInt(1, oid);
stmt.setInt(2, cid);
stmt.setInt(3, sid);
stmt.setInt(4, qty);
stmt.setBigDecimal(5, bid);
stmt.setTimestamp(6, time);
stmt.setString(7, status);
stmt.setInt(8, tid);
if (SQLTest.hasJSON && ! SQLHelper.isDerbyConn(stmt.getConnection()) ) { Clob jsonClob = stmt.getConnection().createClob();
jsonClob.setString(1, json.toJSONString());
stmt.setClob(9, jsonClob); }
stmt.addBatch();
}
@Override
public void setClobAsString(PreparedStatement ps, int paramIndex, String content)
throws SQLException {
if (streamAsLob) {
if (content != null) {
ps.setClob(paramIndex, new StringReader(content), content.length());
}
else {
ps.setClob(paramIndex, (Clob) null);
}
}
else if (wrapAsLob) {
if (content != null) {
ps.setClob(paramIndex, new PassThroughClob(content));
}
else {
ps.setClob(paramIndex, (Clob) null);
}
}
else {
ps.setString(paramIndex, content);
}
if (logger.isDebugEnabled()) {
logger.debug(content != null ? "Set string for CLOB with length " + content.length() :
"Set CLOB to null");
}
}
/**
* Tests the ClobWriter.write(int c) method
**/
public void testClobCharacterWrite1Char() throws Exception
{
char testchar = 'a';
PreparedStatement stmt3 = prepareStatement(
"SELECT c FROM testBlobX1 WHERE a = 1");
ResultSet rs3 = stmt3.executeQuery();
rs3.next();
Clob clob = rs3.getClob(1);
assertTrue ("FAIL -- clob is NULL", clob != null);
Writer clobWriter = clob.setCharacterStream(1L);
clobWriter.write(testchar);
clobWriter.close();
PreparedStatement stmt4 = prepareStatement(
"UPDATE testBlobX1 SET c = ? WHERE a = 1");
stmt4.setClob(1, clob);
stmt4.executeUpdate();
stmt4.close();
rs3.close();
rs3 = stmt3.executeQuery();
assertTrue("FAIL -- clob not found", rs3.next());
clob = rs3.getClob(1);
long new_length = clob.length();
assertEquals("FAIL -- wrong clob length", 1, new_length);
// Check contents ...
Reader lStream = clob.getCharacterStream();
char clobchar = (char) lStream.read();
assertEquals("FAIL - fetched Clob and original contents do " +
"not match", testchar, clobchar);
lStream.close();
rs3.close();
stmt3.close();
}
protected void addBatchInsert(PreparedStatement stmt, int oid, int cid, int sid, int qty,
String status, Timestamp time, BigDecimal bid, int tid, boolean isPut) throws SQLException {
JSONObject json = new JSONObject();
String jsonLog ="";
if (SQLTest.hasJSON && ! SQLHelper.isDerbyConn(stmt.getConnection()) ) {
json = getJSONObject(oid,cid,sid,qty,status,time,bid,tid);
jsonLog = ",JSON_DETAILS: " +json.toJSONString();
}
Log.getLogWriter().info( (SQLHelper.isDerbyConn(stmt.getConnection())? "Derby - " :"gemfirexd - " ) + (isPut ? "putting " : "inserting ") + " into trade.buyorders with data OID:" + oid +
",CID:"+ cid + ",SID:" + sid + ",QTY:" + qty + ",STATUS:" + status +
",TIME:"+ time + ",BID:" + bid + ",TID:" + tid + jsonLog);
stmt.setInt(1, oid);
stmt.setInt(2, cid);
stmt.setInt(3, sid);
stmt.setInt(4, qty);
stmt.setBigDecimal(5, bid);
stmt.setTimestamp(6, time);
stmt.setString(7, status);
stmt.setInt(8, tid);
if (SQLTest.hasJSON && ! SQLHelper.isDerbyConn(stmt.getConnection()) ) { Clob jsonClob = stmt.getConnection().createClob();
jsonClob.setString(1, json.toJSONString());
stmt.setClob(9, jsonClob); }
stmt.addBatch();
}
/**
* Test the createClob method implementation in the Connection interface
*
* @exception SQLException, FileNotFoundException, Exception if error occurs
*/
public void testCreateClob() throws SQLException,
FileNotFoundException, IOException,
Exception{
Connection conn = getConnection();
int b, c;
Clob clob;
Statement s = createStatement();
PreparedStatement ps =
prepareStatement("insert into clobtable2 (n, clobcol)" + " values(?,?)");
ps.setInt(1,1000);
clob = conn.createClob();
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 = clob.setAsciiStream(1);
ArrayList beforeUpdateList = new ArrayList();
c = is.read();
while(c>0) {
os.write(c);
beforeUpdateList.add(c);
c = is.read();
}
ps.setClob(2, clob);
ps.executeUpdate();
Statement stmt = createStatement();
ResultSet rs =
stmt.executeQuery("select clobcol from clobtable2 where n = 1000");
assertTrue(rs.next());
clob = rs.getClob(1);
assertEquals(beforeUpdateList.size(), clob.length());
//Get the InputStream from this Clob.
InputStream in = clob.getAsciiStream();
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 < clob.length(); i++) {
assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
}
os.close();
is.close();
}
/**
* Tests the ClobOutputStream.write(int b) method
**/
public void testClobAsciiWrite1Param() throws Exception
{
InputStream streamIn = new LoopingAlphabetStream(streamSize[1]);
PreparedStatement stmt3 = prepareStatement(
"SELECT c FROM testBlobX1 WHERE a = 1");
ResultSet rs3 = stmt3.executeQuery();
rs3.next();
Clob clob = rs3.getClob(1);
assertTrue("FAIL -- clob is NULL", clob != null);
int buffer;
OutputStream outstream = clob.setAsciiStream(1L);
while ((buffer = streamIn.read()) != -1) {
outstream.write(buffer);
}
outstream.close();
streamIn.close();
PreparedStatement stmt4 = prepareStatement(
"UPDATE testBlobX1 SET c = ? WHERE a = 1");
stmt4.setClob(1, clob);
stmt4.executeUpdate();
stmt4.close();
rs3.close();
rs3 = stmt3.executeQuery();
assertTrue("FAIL -- clob not found", rs3.next());
long new_length = rs3.getClob(1).length();
assertEquals("FAIL -- wrong clob length", streamSize[1], new_length);
// Check contents ...
InputStream fStream = new LoopingAlphabetStream(streamSize[1]);
InputStream lStream = rs3.getClob(1).getAsciiStream();
assertTrue("FAIL - Clob and file contents do not match", compareLob2File(fStream, lStream));
fStream.close();
lStream.close();
rs3.close();
stmt3.close();
}
/**
* fill the prepared statement object
*
* @param pstmt
* @param indexs
* @param types
* @param values
* @throws SQLException
*/
public static void setPreparedStatement(final PreparedStatement pstmt, final List<Integer> indexs, final List<String> types,
final List<Object> values) throws SQLException {
for (int i = 0; i < indexs.size(); i++) {
Integer index = indexs.get(i);
PreparedStatementTable.Type type = PreparedStatementTable.Type.valueOf(types.get(i));
Object value = values.get(i);
switch (type) {
case BigDecimal:
pstmt.setBigDecimal(index, (BigDecimal) value);
break;
case Blob:
pstmt.setBlob(index, (Blob) value);
break;
case Boolean:
pstmt.setBoolean(index, (boolean) value);
break;
case Byte:
pstmt.setByte(index, (byte) value);
break;
case Bytes:
pstmt.setBytes(index, (byte[]) value);
break;
case Clob:
pstmt.setClob(index, (Clob) value);
break;
case Date:
pstmt.setTimestamp(index, new Timestamp(((Date) value).getTime()));
break;
case Double:
pstmt.setDouble(index, (double) value);
break;
case Float:
pstmt.setFloat(index, (float) value);
break;
case Int:
pstmt.setInt(index, (int) value);
break;
case Long:
pstmt.setLong(index, (long) value);
break;
case Object:
pstmt.setObject(index, value);
break;
case Short:
pstmt.setShort(index, (short) value);
break;
case String:
pstmt.setString(index, (String) value);
break;
case Time:
pstmt.setTime(index, (Time) value);
break;
case Null:
pstmt.setNull(index, (int) value);
break;
default:
pstmt.setString(index, (String) value);
break;
}
}
}
/**
* Tests that Derby specific end-of-stream markers aren't passed over to
* the temporary Clob, which doesn't use such markers.
* <p>
* Passing the marker over will normally result in a UTF encoding exception.
* <p>
* ID USAGE: reads id 2, writes id 10002
*/
public void testInsertCharacter_ReadOnlyToTemporary()
throws IOException, SQLException {
setAutoCommit(false);
// Insert data, a medium sized Clob to store it as a stream.
PreparedStatement ps = prepareStatement(
"insert into ClobTestData values (?,?)");
int initalSize = 128*1024;
ps.setInt(1, 2);
ps.setCharacterStream(
2, new LoopingAlphabetReader(initalSize), initalSize);
ps.executeUpdate();
// Select the Clob, and change one character.
PreparedStatement psSelect = prepareStatement(
"select dClob from ClobTestData where id = ?");
psSelect.setInt(1, 2);
ResultSet lRs = psSelect.executeQuery();
lRs.next();
Clob lClob = lRs.getClob(1);
lClob.setString(1, "K");
Reader r = lClob.getCharacterStream();
assertEquals('K', r.read());
long length = 1;
while (true) {
// Since we're skipping characters, the bytes have to be decoded
// and we will detect any encoding errors.
long skipped = r.skip(4096);
if (skipped > 0) {
length += skipped;
} else {
break;
}
}
lRs.close();
assertEquals("Wrong length!", initalSize, length);
// Reports the correct length, now try to insert it.
ps.setInt(1, 10003);
ps.setClob(2, lClob);
ps.executeUpdate();
// Fetch it back.
psSelect.setInt(1, 10003);
lRs = psSelect.executeQuery();
lRs.next();
Clob lClob2 = lRs.getClob(1);
assertEquals(lClob.getCharacterStream(), lClob2.getCharacterStream());
assertEquals(initalSize, lClob2.length());
}
/**
* Fulfills prepared statement with values.
*
* @param pstmt - statement created from query.
* @param preparedStatementTable - properties that store all indexes, types and values for prepared statement.
* @throws SQLException if failed to insert value into prepared statement.
*/
public static void fillPreparedStatement(PreparedStatement pstmt, SnowflakePreparedStatementTableProperties preparedStatementTable)
throws SQLException {
List<Integer> indexes = preparedStatementTable.indexes.getValue();
List<String> types = preparedStatementTable.types.getValue();
List<Object> values = preparedStatementTable.values.getValue();
for (int i = 0; i < indexes.size(); i++) {
Integer index = indexes.get(i);
SnowflakePreparedStatementTableProperties.Type type = SnowflakePreparedStatementTableProperties.Type.valueOf(types.get(i));
Object value = values.get(i);
if (value == null) {
pstmt.setNull(index, Types.NULL);
continue;
}
switch (type) {
case BigDecimal:
pstmt.setBigDecimal(index, (BigDecimal) value);
break;
case Blob:
pstmt.setBlob(index, (Blob) value);
break;
case Boolean:
pstmt.setBoolean(index, (boolean) value);
break;
case Byte:
pstmt.setByte(index, (byte) value);
break;
case Bytes:
pstmt.setBytes(index, (byte[]) value);
break;
case Clob:
pstmt.setClob(index, (Clob) value);
break;
case Date:
pstmt.setTimestamp(index, new Timestamp(((Date) value).getTime()));
break;
case Double:
pstmt.setDouble(index, (double) value);
break;
case Float:
pstmt.setFloat(index, (float) value);
break;
case Int:
pstmt.setInt(index, (int) value);
break;
case Long:
pstmt.setLong(index, (long) value);
break;
case Object:
pstmt.setObject(index, value);
break;
case Short:
pstmt.setShort(index, (short) value);
break;
case String:
pstmt.setString(index, (String) value);
break;
case Time:
pstmt.setTime(index, (Time) value);
break;
case Null:
pstmt.setNull(index, (int) value);
break;
default:
pstmt.setString(index, (String) value);
break;
}
}
}
/** Assigns a value to a dynamic parameter in a prepared statement, calling
* the appropriate {@code setXxx} method based on the type of the value. */
private static void setDynamicParam(PreparedStatement preparedStatement,
int i, Object value) throws SQLException {
if (value == null) {
preparedStatement.setObject(i, null, SqlType.ANY.id);
} else if (value instanceof Timestamp) {
preparedStatement.setTimestamp(i, (Timestamp) value);
} else if (value instanceof Time) {
preparedStatement.setTime(i, (Time) value);
} else if (value instanceof String) {
preparedStatement.setString(i, (String) value);
} else if (value instanceof Integer) {
preparedStatement.setInt(i, (Integer) value);
} else if (value instanceof Double) {
preparedStatement.setDouble(i, (Double) value);
} else if (value instanceof java.sql.Array) {
preparedStatement.setArray(i, (java.sql.Array) value);
} else if (value instanceof BigDecimal) {
preparedStatement.setBigDecimal(i, (BigDecimal) value);
} else if (value instanceof Boolean) {
preparedStatement.setBoolean(i, (Boolean) value);
} else if (value instanceof Blob) {
preparedStatement.setBlob(i, (Blob) value);
} else if (value instanceof Byte) {
preparedStatement.setByte(i, (Byte) value);
} else if (value instanceof NClob) {
preparedStatement.setNClob(i, (NClob) value);
} else if (value instanceof Clob) {
preparedStatement.setClob(i, (Clob) value);
} else if (value instanceof byte[]) {
preparedStatement.setBytes(i, (byte[]) value);
} else if (value instanceof Date) {
preparedStatement.setDate(i, (Date) value);
} else if (value instanceof Float) {
preparedStatement.setFloat(i, (Float) value);
} else if (value instanceof Long) {
preparedStatement.setLong(i, (Long) value);
} else if (value instanceof Ref) {
preparedStatement.setRef(i, (Ref) value);
} else if (value instanceof RowId) {
preparedStatement.setRowId(i, (RowId) value);
} else if (value instanceof Short) {
preparedStatement.setShort(i, (Short) value);
} else if (value instanceof URL) {
preparedStatement.setURL(i, (URL) value);
} else if (value instanceof SQLXML) {
preparedStatement.setSQLXML(i, (SQLXML) value);
} else {
preparedStatement.setObject(i, value);
}
}
/**
* Tests the ClobOutputStream.write(byte b[], int off, int len) method
**/
public void testClobAsciiWrite3Param() throws Exception {
InputStream streamIn = new LoopingAlphabetStream(streamSize[0]);
assertTrue("FAIL -- file not found", streamIn != null);
PreparedStatement stmt3 = prepareStatement(
"SELECT c FROM testBlobX1 WHERE a = 1");
ResultSet rs3 = stmt3.executeQuery();
rs3.next();
Clob clob = rs3.getClob(1);
assertTrue("FAIL -- clob is NULL", clob != null);
int count = 0;
byte[] buffer = new byte[1024];
OutputStream outstream = clob.setAsciiStream(1L);
while ((count = streamIn.read(buffer)) != -1) {
outstream.write(buffer, 0, count);
}
outstream.close();
streamIn.close();
PreparedStatement stmt4 = prepareStatement(
"UPDATE testBlobX1 SET c = ? WHERE a = 1");
stmt4.setClob(1, clob);
stmt4.executeUpdate();
stmt4.close();
rs3.close();
rs3 = stmt3.executeQuery();
assertTrue("FAIL -- clob not found", rs3.next());
clob = rs3.getClob(1);
long new_length = clob.length();
assertEquals("FAIL -- wrong clob length",
streamSize[0], new_length);
// Check contents ...
InputStream fStream = new LoopingAlphabetStream(streamSize[0]);
InputStream lStream = clob.getAsciiStream();
assertTrue("FAIL - Clob and file contents do not match",
compareLob2File(fStream, lStream));
fStream.close();
lStream.close();
rs3.close();
stmt3.close();
}
protected void insertObjectDo(Object value, PreparedStatement statement, int index) throws SQLException {
statement.setClob(index, (Clob) value);
}