Вставьте CLOB в базу данных Oracle
Мой вопрос: как вы обходите ORA-01704: string literal too long ошибка при вставке (или выполнении чего-либо в запросах) с CLOBs?
Я хочу иметь такой запрос:
INSERT ALL
INTO mytable VALUES ('clob1')
INTO mytable VALUES ('clob2') --some of these clobs are more than 4000 characters...
INTO mytable VALUES ('clob3')
SELECT * FROM dual;
Когда я пытаюсь это с реальными значениями, хотя я получаю ORA-01704: string literal too long назад. Это довольно очевидно, но как мне вставить clob (или выполнить какой-либо оператор вообще с clob)?
Я пытался посмотреть на этот вопрос, но я не думаю, что он имеет то, что я ищу. Сгустки у меня в List<String> и я перебираю их, чтобы сделать заявление. Мой код таков:
private void insertQueries(String tempTableName) throws FileNotFoundException, DataException, SQLException, IOException {
String preQuery = " into " + tempTableName + " values ('";
String postQuery = "')" + StringHelper.newline;
StringBuilder inserts = new StringBuilder("insert all" + StringHelper.newline);
List<String> readQueries = getDomoQueries();
for (String query : readQueries) {
inserts.append(preQuery).append(query).append(postQuery);
}
inserts.append("select * from dual;");
DatabaseController.getInstance().executeQuery(databaseConnectionURL, inserts.toString());
}
public ResultSet executeQuery(String connection, String query) throws DataException, SQLException {
Connection conn = ConnectionPool.getInstance().get(connection);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
conn.commit();
ConnectionPool.getInstance().release(conn);
return rs;
}
6 ответов
Вы пробираетесь к сложному.
Используйте PreparedStatement и addBatch() для каждого сгустка в вашем списке:
String sql = "insert into " + tempTableName + " values (?)";
PreparedStatement stmt = connection.prepareStatement(sql);
for (String query : readQueries) {
stmt.setCharacterStream(1, new StringReader(query), query.lenght());
stmt.addBatch();
}
stmt.exececuteBatch();
Нет необходимости возиться с экранирующими строками, нет проблем с длиной литералов, нет необходимости создавать временные сгустки. И, скорее всего, так же быстро, как использование одного оператора INSERT ALL.
Если вы используете текущий драйвер (> 10.2), то я думаю, что вызов setCharacterStream() и создание Reader также не нужны. Просто setString(1, query) Скорее всего, будет работать.
Необходимо учитывать следующее автоматическое переключение режима ввода для больших данных. Существует три режима ввода: прямая привязка, привязка к потоку и привязка больших объектов.
Для операторов PL/SQL
Методы потока setBytes и setBinary используют прямую привязку для данных размером менее 32767 байт.
Методы setBytes и setBinaryStream используют привязку больших объектов для данных, размер которых превышает 32766 байт.
Методы setString, setCharacterStream и setAsciiStream используют прямую привязку для данных размером менее 32767 байт в наборе символов базы данных.
Методы setString, setCharacterStream и setAsciiStream используют привязку больших объектов для данных, размер которых превышает 32766 байт в наборе символов базы данных.
Методы setBytesForBlob и setStringForClob, представленные в интерфейсе oracle.jdbc.OraclePreparedStatement, используют привязку больших объектов для любого размера данных.
Ниже приведен пример помещения содержимого файла во входной параметр CLOB процедуры PLSQL:
public int fileToClob( FileItem uploadFileItem ) throws SQLException, IOException
{
//for using stmt.setStringForClob method, turn the file to a big String
FileItem item = uploadFileItem;
InputStream inputStream = item.getInputStream();
InputStreamReader inputStreamReader = new InputStreamReader( inputStream );
BufferedReader bufferedReader = new BufferedReader( inputStreamReader );
StringBuffer stringBuffer = new StringBuffer();
String line = null;
while((line = bufferedReader.readLine()) != null) { //Read till end
stringBuffer.append(line);
stringBuffer.append("\n");
}
String fileString = stringBuffer.toString();
bufferedReader.close();
inputStreamReader.close();
inputStream.close();
item.delete();
OracleCallableStatement stmt;
String strFunction = "{ call p_file_to_clob( p_in_clob => ? )}";
stmt= (OracleCallableStatement)conn.prepareCall(strFunction);
try{
SasUtility servletUtility = sas.SasUtility.getInstance();
stmt.setStringForClob(1, fileString );
stmt.execute();
} finally {
stmt.close();
}
}
Вам нужно будет использовать переменные связывания, а не создавать оператор SQL с использованием конкатенации строк. Это будет полезно с точки зрения безопасности, производительности и надежности, так как снизит риск атак SQL-инъекций, сократит время, которое Oracle должен тратить на жесткий анализ оператора SQL, и устранит потенциальные является специальным символом в строке, который вызывает генерирование недопустимого оператора SQL (т.е. одинарную кавычку).
Я ожидаю, что вы хотите что-то вроде
private void insertQueries(String tempTableName) throws FileNotFoundException, DataException, SQLException, IOException {
String preQuery = " into " + tempTableName + " values (?)" + StringHelper.newline;
StringBuilder inserts = new StringBuilder("insert all" + StringHelper.newline);
List<String> readQueries = getDomoQueries();
for (String query : readQueries) {
inserts.append(preQuery);
}
inserts.append("select * from dual");
Connection conn = ConnectionPool.getInstance().get(connection);
PreparedStatement pstmt = conn.prepareStatement(
inserts);
int i = 1;
for (String query : readQueries) {
Clob clob = CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_SESSION);
clob.setString(i, query);
pstmt.setClob(i, clob);
i = i + 1;
}
pstmt.executeUpdate();
}
BLOB (большие двоичные объекты) и CLOB(большие символы) - это специальные типы данных, которые могут содержать большие куски данных в виде объектов или текста. Объекты Blob и Clob сохраняют данные объектов в базе данных в виде потока.
Пример кода:
public class TestDB {
public static void main(String[] args) {
try {
/** Loading the driver */
Class.forName("com.oracle.jdbc.Driver");
/** Getting Connection */
Connection con = DriverManager.getConnection("Driver URL","test","test");
PreparedStatement pstmt = con.prepareStatement("insert into Emp(id,name,description)values(?,?,?)");
pstmt.setInt(1,5);
pstmt.setString(2,"Das");
// Create a big CLOB value...AND inserting as a CLOB
StringBuffer sb = new StringBuffer(400000);
sb.append("This is the Example of CLOB ..");
String clobValue = sb.toString();
pstmt.setString(3, clobValue);
int i = pstmt.executeUpdate();
System.out.println("Done Inserted");
pstmt.close();
con.close();
// Retrive CLOB values
Connection con = DriverManager.getConnection("Driver URL","test","test");
PreparedStatement pstmt = con.prepareStatement("select * from Emp where id=5");
ResultSet rs = pstmt.executeQuery();
Reader instream = null;
int chunkSize;
if (rs.next()) {
String name = rs.getString("name");
java.sql.Clob clob = result.getClob("description")
StringBuffer sb1 = new StringBuffer();
chunkSize = ((oracle.sql.CLOB)clob).getChunkSize();
instream = clob.getCharacterStream();
BufferedReader in = new BufferedReader(instream);
String line = null;
while ((line = in.readLine()) != null) {
sb1.append(line);
}
if (in != null) {
in.close();
}
// this is the clob data converted into string
String clobdata = sb1.toString();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
Мне нравится использовать классы из пакета java.sql.*, А не oracle.* Вещи. Для меня простой подход
Connection con = ...;
try (PreparedStatement pst = con.prepareStatement(
"insert into tbl (other_fld, clob_fld) values (?,?)", new String[]{"tbl_id"});
) {
Clob clob = con.createClob();
readIntoClob(clob, inputStream);
pst.setString(1, "other");
pst.setClob(2, clob);
pst.executeUpdate();
try (ResultSet rst = pst.getGeneratedKeys()) {
if (rst == null || !rst.next()) {
throw new Exception("error with getting auto-generated key");
}
id = rst.getBigDecimal(1);
}
перестал работать, когда тестирование (текущий tomcat, jdbc) перешло в производство (зависло в Tomcat6 по глупым причинам). con.createClob() возвращает значение null по неизвестным в этой версии причинам, поэтому мне пришлось сделать этот двойной дубль (мне понадобилось много времени, чтобы выяснить это, поэтому я поделился здесь...)
try (PreparedStatement pst = con.prepareStatement(
"insert into tbl (other_fld) values (?)", new String[]{"tbl_id"});
PreparedStatement getClob= con.prepareStatement(
"select clob_fld from tbl where tbl_id = ? for update");
) {
Clob clob = con.createClob();
readIntoClob(clob, inputStream);
pst.setString(1, "other");
pst.executeUpdate();
try (ResultSet rst = pst.getGeneratedKeys()) {
if (rst == null || !rst.next()) {
throw new Exception("error with getting auto-generated key");
}
id = rst.getBigDecimal(1);
}
// fetch back fresh record, with the Clob
getClob.setBigDecimal(1, id);
getClob.execute();
try (ResultSet rst = getClob.getResultSet()) {
if (rst == null || !rst.next()) {
throw new Exception("error with fetching back clob");
}
Clob c = rst.getClob(1);
// Fill in data
readIntoClob(c, stream);
// that's all
}
} catch (SQLException) {
...
}
для полноты вот
// Read data from an input stream and insert it in to the clob column
private static void readIntoClob(Clob clob, InputStream stream) {
try (BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(stream))) {
char[] buffer = new char[CHUNK_BUFFER_SIZE];
int charsRead;
try (Writer wr = clob.setCharacterStream(1L)) {
// Loop for reading of chunk of data and then write into the clob.
while ((charsRead = bufferedReader.read(buffer)) != -1) {
wr.write(buffer, 0, charsRead);
}
} catch (SQLException | IOException ex) {
...
}
}
}
который откуда-то на SO, спасибо.