List of usage examples for java.sql Clob length
long length() throws SQLException;
From source file:Main.java
public static void main(String[] args) throws Exception { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "yourName", "mypwd"); Statement stmt = conn.createStatement(); createBlobClobTables(stmt);/*ww w . j a v a 2 s .c o m*/ PreparedStatement pstmt = conn.prepareStatement("INSERT INTO BlobClob VALUES(40,?,?)"); File file = new File("blob.txt"); FileInputStream fis = new FileInputStream(file); pstmt.setBinaryStream(1, fis, (int) file.length()); file = new File("clob.txt"); fis = new FileInputStream(file); pstmt.setAsciiStream(2, fis, (int) file.length()); fis.close(); pstmt.execute(); ResultSet rs = stmt.executeQuery("SELECT * FROM BlobClob WHERE id = 40"); rs.next(); java.sql.Blob blob = rs.getBlob(2); java.sql.Clob clob = rs.getClob(3); byte blobVal[] = new byte[(int) blob.length()]; InputStream blobIs = blob.getBinaryStream(); blobIs.read(blobVal); ByteArrayOutputStream bos = new ByteArrayOutputStream(); bos.write(blobVal); blobIs.close(); char clobVal[] = new char[(int) clob.length()]; Reader r = clob.getCharacterStream(); r.read(clobVal); StringWriter sw = new StringWriter(); sw.write(clobVal); r.close(); conn.close(); }
From source file:Main.java
public static void main(String[] args) throws Exception { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "yourName", "mypwd"); Statement stmt = conn.createStatement(); createBlobClobTables(stmt);//from w w w . java2 s.c o m PreparedStatement pstmt = conn.prepareStatement("INSERT INTO BlobClob VALUES(40,?,?)"); File file = new File("blob.txt"); FileInputStream fis = new FileInputStream(file); pstmt.setBinaryStream(1, fis, (int) file.length()); file = new File("clob.txt"); fis = new FileInputStream(file); pstmt.setAsciiStream(2, fis, (int) file.length()); fis.close(); pstmt.execute(); ResultSet rs = stmt.executeQuery("SELECT * FROM BlobClob WHERE id = 40"); rs.next(); java.sql.Blob blob = rs.getBlob(2); java.sql.Clob clob = rs.getClob("myClobColumn"); byte blobVal[] = new byte[(int) blob.length()]; InputStream blobIs = blob.getBinaryStream(); blobIs.read(blobVal); ByteArrayOutputStream bos = new ByteArrayOutputStream(); bos.write(blobVal); blobIs.close(); char clobVal[] = new char[(int) clob.length()]; Reader r = clob.getCharacterStream(); r.read(clobVal); StringWriter sw = new StringWriter(); sw.write(clobVal); r.close(); conn.close(); }
From source file:Main.java
public static String getCLOB(int id) throws Exception { Connection conn = null;/*from w ww .ja v a2s . c o m*/ ResultSet rs = null; PreparedStatement pstmt = null; String query = "SELECT clobData FROM tableName WHERE id = ?"; try { conn = getConnection(); pstmt = conn.prepareStatement(query); pstmt.setInt(1, id); rs = pstmt.executeQuery(); rs.next(); Clob clob = rs.getClob(1); // materialize CLOB onto client String wholeClob = clob.getSubString(1, (int) clob.length()); return wholeClob; } finally { rs.close(); pstmt.close(); conn.close(); } }
From source file:com.afforess.nsdump.Test.java
public static void testNationDump() throws IOException, SQLException { NationsDump dump = new NationsDump(); dump.parse();//from w ww . j a v a2 s . c o m Connection conn = dump.getDatabaseConnection(); PreparedStatement statement = conn.prepareStatement("SELECT (name) FROM nations"); ResultSet result = statement.executeQuery(); int total = 0; while (result.next()) { total++; } result.close(); System.out.println("Total nations: " + total); statement = conn.prepareStatement("SELECT * FROM nations WHERE name = 'sakhovelo'"); result = statement.executeQuery(); result.next(); for (int i = 1; i <= 10; i++) { if (i == 4) { Clob clob = result.getClob(i); String motto = clob.getSubString(1, (int) clob.length()); String mottoEscaped = StringEscapeUtils.unescapeHtml(motto); System.out.println("Raw: " + motto + " Escaped: " + mottoEscaped); } else { System.out.println(result.getString(i)); } } File db = new File("./ns-db.h2.db"); db.delete(); }
From source file:be.dataminded.nifi.plugins.util.JdbcCommon.java
public static long convertToAvroStream(final ResultSet rs, final OutputStream outStream, String recordName, ResultSetRowCallback callback, final int maxRows, boolean convertNames) throws SQLException, IOException { final Schema schema = createSchema(rs, recordName, convertNames); final GenericRecord rec = new GenericData.Record(schema); final DatumWriter<GenericRecord> datumWriter = new GenericDatumWriter<>(schema); try (final DataFileWriter<GenericRecord> dataFileWriter = new DataFileWriter<>(datumWriter)) { dataFileWriter.create(schema, outStream); final ResultSetMetaData meta = rs.getMetaData(); final int nrOfColumns = meta.getColumnCount(); long nrOfRows = 0; while (rs.next()) { if (callback != null) { callback.processRow(rs); }//from ww w. j a va 2s .c o m for (int i = 1; i <= nrOfColumns; i++) { final int javaSqlType = meta.getColumnType(i); // Need to handle CLOB and BLOB before getObject() is called, due to ResultSet's maximum portability statement if (javaSqlType == CLOB) { Clob clob = rs.getClob(i); if (clob != null) { long numChars = clob.length(); char[] buffer = new char[(int) numChars]; InputStream is = clob.getAsciiStream(); int index = 0; int c = is.read(); while (c > 0) { buffer[index++] = (char) c; c = is.read(); } rec.put(i - 1, new String(buffer)); clob.free(); } else { rec.put(i - 1, null); } continue; } if (javaSqlType == BLOB) { Blob blob = rs.getBlob(i); if (blob != null) { long numChars = blob.length(); byte[] buffer = new byte[(int) numChars]; InputStream is = blob.getBinaryStream(); int index = 0; int c = is.read(); while (c > 0) { buffer[index++] = (byte) c; c = is.read(); } ByteBuffer bb = ByteBuffer.wrap(buffer); rec.put(i - 1, bb); blob.free(); } else { rec.put(i - 1, null); } continue; } final Object value = rs.getObject(i); if (value == null) { rec.put(i - 1, null); } else if (javaSqlType == BINARY || javaSqlType == VARBINARY || javaSqlType == LONGVARBINARY || javaSqlType == ARRAY) { // bytes requires little bit different handling byte[] bytes = rs.getBytes(i); ByteBuffer bb = ByteBuffer.wrap(bytes); rec.put(i - 1, bb); } else if (value instanceof Byte) { // tinyint(1) type is returned by JDBC driver as java.sql.Types.TINYINT // But value is returned by JDBC as java.lang.Byte // (at least H2 JDBC works this way) // direct put to avro record results: // org.apache.avro.AvroRuntimeException: Unknown datum type java.lang.Byte rec.put(i - 1, ((Byte) value).intValue()); } else if (value instanceof Short) { //MS SQL returns TINYINT as a Java Short, which Avro doesn't understand. rec.put(i - 1, ((Short) value).intValue()); } else if (value instanceof BigDecimal) { // Avro can't handle BigDecimal as a number - it will throw an AvroRuntimeException such as: "Unknown datum type: java.math.BigDecimal: 38" try { int scale = meta.getScale(i); BigDecimal bigDecimal = ((BigDecimal) value); if (scale == 0) { if (meta.getPrecision(i) < 10) { rec.put(i - 1, bigDecimal.intValue()); } else { rec.put(i - 1, bigDecimal.longValue()); } } else { rec.put(i - 1, bigDecimal.doubleValue()); } } catch (Exception e) { rec.put(i - 1, value.toString()); } } else if (value instanceof BigInteger) { // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that. // It the SQL type is BIGINT and the precision is between 0 and 19 (inclusive); if so, the BigInteger is likely a // long (and the schema says it will be), so try to get its value as a long. // Otherwise, Avro can't handle BigInteger as a number - it will throw an AvroRuntimeException // such as: "Unknown datum type: java.math.BigInteger: 38". In this case the schema is expecting a string. if (javaSqlType == BIGINT) { int precision = meta.getPrecision(i); if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) { rec.put(i - 1, value.toString()); } else { try { rec.put(i - 1, ((BigInteger) value).longValueExact()); } catch (ArithmeticException ae) { // Since the value won't fit in a long, convert it to a string rec.put(i - 1, value.toString()); } } } else { rec.put(i - 1, value.toString()); } } else if (value instanceof Number || value instanceof Boolean) { if (javaSqlType == BIGINT) { int precision = meta.getPrecision(i); if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) { rec.put(i - 1, value.toString()); } else { rec.put(i - 1, value); } } else { rec.put(i - 1, value); } } else { // The different types that we support are numbers (int, long, double, float), // as well as boolean values and Strings. Since Avro doesn't provide // timestamp types, we want to convert those to Strings. So we will cast anything other // than numbers or booleans to strings by using the toString() method. rec.put(i - 1, value.toString()); } } dataFileWriter.append(rec); nrOfRows += 1; if (maxRows > 0 && nrOfRows == maxRows) break; } return nrOfRows; } }
From source file:CSVWriter.java
private static String read(Clob c) throws SQLException, IOException { StringBuffer sb = new StringBuffer( (int) c.length()); Reader r = c.getCharacterStream(); char[] cbuf = new char[2048]; int n = 0;/*from w ww .j a v a2 s. co m*/ while ((n = r.read(cbuf, 0, cbuf.length)) != -1) { if (n > 0) { sb.append(cbuf, 0, n); } } return sb.toString(); }
From source file:CSVWriter.java
private static String read(Clob c) throws SQLException, IOException { StringBuilder sb = new StringBuilder((int) c.length()); Reader r = c.getCharacterStream(); char[] cbuf = new char[CLOBBUFFERSIZE]; int n;// w w w. j a va2 s . co m while ((n = r.read(cbuf, 0, cbuf.length)) != -1) { sb.append(cbuf, 0, n); } return sb.toString(); }
From source file:cognition.pipeline.data.helper.ClobHelper.java
private String getString(Object expectedClob) { SerializableClobProxy clobProxy = (SerializableClobProxy) Proxy.getInvocationHandler(expectedClob); Clob wrappedClob = clobProxy.getWrappedClob(); try {/*from w ww. ja v a2 s. c o m*/ return wrappedClob.getSubString(1, (int) wrappedClob.length()); } catch (SQLException e) { e.printStackTrace(); return "error: could not retrieve text"; } }
From source file:com.ccoe.build.dal.SessionMapper.java
public Session mapRow(ResultSet rs, int arg1) throws SQLException { Session session = new Session(); session.setDuration(rs.getLong("duration")); session.setEnvironment(rs.getString("environment")); session.setJavaVersion(rs.getString("java_version")); session.setMavenVersion(rs.getString("maven_version")); session.setStartTime(rs.getDate("start_time")); session.setUserName(rs.getString("user_name")); session.setStatus(rs.getString("status")); session.setGoals(rs.getString("goals")); session.setAppName(rs.getString("pool_name")); session.setMachineName(rs.getString("machine_name")); Clob stacktrace = rs.getClob("full_stacktrace"); if (stacktrace != null) { session.setFullStackTrace(stacktrace.getSubString(1, (int) stacktrace.length())); }/*from ww w . j a va 2s. c o m*/ session.setExceptionMessage(rs.getString("cause")); session.setCategory(rs.getString("category")); session.setId(rs.getInt("id")); session.setFilter(rs.getString("filter")); return session; }
From source file:com.squid.core.jdbc.formatter.DefaultJDBCDataFormatter.java
protected String read(Clob c) throws SQLException, IOException { StringBuffer sb = new StringBuffer((int) c.length()); Reader r = c.getCharacterStream(); char[] cbuf = new char[2048]; int n = 0;/*from w w w.j a va2 s.co m*/ while ((n = r.read(cbuf, 0, cbuf.length)) != -1) { if (n > 0) { sb.append(cbuf, 0, n); } } return sb.toString(); }