Convert clob to string java

Java Sample Programs

Below code shows the two ways to convert database CLOB to String code.

1) Convert CLOB to String using BufferedReader:

import java.io.BufferedReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * @author http://java-sample-program.blogspot.in/ * Convert CLOB to String using BufferedReader */ public class ClobTest < public static void main(String[] args) throws Exception< Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; try < Class.forName("oracle.jdbc.driver.OracleDriver"); con = DriverManager.getConnection("jdbc:oracle:thin:@IPAddress:Port:Database", "username","password"); pstmt = con.prepareStatement("SELECT clobCol FROM TableName"); rs = pstmt.executeQuery(); while(rs.next())< StringBuffer strOut = new StringBuffer(); String aux; try < BufferedReader br = new BufferedReader(rs.getClob("clobCol"). getCharacterStream()); while ((aux=br.readLine())!=null) < strOut.append(aux); strOut.append(System.getProperty("line.separator")); >>catch (Exception e) < e.printStackTrace(); >String clobStr = strOut.toString(); System.out.println(clobStr); > >catch(Exception e) < e.printStackTrace(); >finally < try< if(pstmt != null)< pstmt.close(); >if(rs!= null) < rs.close(); >if(con!= null) < con.close(); >>catch (SQLException e) < e.printStackTrace(); >> > >

Use the above method if a very large Clob can be returned by the database.

2) Convert CLOB to String using getSubString() method:

import java.io.BufferedReader; import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * @author http://java-sample-program.blogspot.in/ * Convert CLOB to String using getSubString() method */ public class ClobTest < public static void main(String[] args) throws Exception< Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; try < Class.forName("oracle.jdbc.driver.OracleDriver"); con = DriverManager.getConnection("jdbc:oracle:thin:@IPAddress:Port:Database", "username","password"); pstmt = con.prepareStatement("SELECT clobCol FROM TableName"); rs = pstmt.executeQuery(); while(rs.next())< Clob clob = rs.getClob("clobCol"); if (clob != null) < String clobStr = clob.getSubString(1, (int) clob.length()); System.out.println(clobStr); >> >catch(Exception e) < e.printStackTrace(); >finally < try< if(pstmt != null)< pstmt.close(); >if(rs!= null) < rs.close(); >if(con!= null) < con.close(); >>catch (SQLException e) < e.printStackTrace(); >> > >

Источник

How to convert a CLOB type to String in Java?

CLOB stands for Character Large Object in general, an SQL Clob is a built-in datatype and is used to store large amount of textual data. Using this datatype, you can store data up to 2,147,483,647 characters.

The java.sql.Clob interface of the JDBC API represents the CLOB datatype. Since the Clob object in JDBC is implemented using an SQL locator, it holds a logical pointer to the SQL CLOB (not the data).

MySQL database provides support for this data type using four variables namely, TINYTEXT, TEXT, MEDIUMTEXT and, LONGTEXT.

To convert CLOB data type to string

• Retrieve the Clob value from a table using the getClob() or getCharacterStream() method of the PresparedStatement interface.

Reader r = clob.getCharacterStream();

• Read each character one by one from the retrieved Stream of characters and append them to the StringBuilder or StringBuffer.

int j = 0; StringBuffer buffer = new StringBuffer(); int ch; while ((ch = r.read())!=-1) < buffer.append(""+(char)ch); >System.out.println(buffer.toString()); j++;

System.out.println(buffer.toString());

Example

Let us create a table with name technologies_data in MySQL database using the following query −

CREATE TABLE Technologies (Name VARCHAR(255), Type VARCHAR(255), Article LONGTEXT);

The third column of the table Article stores the data of type CLOB.

Following JDBC program initially inserts 5 records in the technologies_data table storing text file (contents of it) it to the article column (CLOB type).

Then, it retrieves the records of the table and, displays the name and contents of the article. Here, we are trying to converting the data of the retrieved CLOB into String and display it.

import java.io.FileReader; import java.io.Reader; import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class ClobToString < public static void main(String args[]) throws Exception < //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/sampledatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established. "); //Creating a Statement object Statement stmt = con.createStatement(); //Inserting values String query = "INSERT INTO Technologies_data VALUES (?, ?, ?)"; PreparedStatement pstmt = con.prepareStatement(query); pstmt.setString(1, "JavaFX"); pstmt.setString(2, "Java Library"); FileReader reader = new FileReader("E:\images\javafx_contents.txt"); pstmt.setClob(3, reader); pstmt.execute(); pstmt.setString(1, "CoffeeScript"); pstmt.setString(2, "Scripting Language"); reader = new FileReader("E:\images\coffeescript_contents.txt"); pstmt.setClob(3, reader); pstmt.execute(); pstmt.setString(1, "Cassandra"); pstmt.setString(2, "NoSQL Database"); reader = new FileReader("E:\images\cassandra_contents.txt"); pstmt.setClob(3, reader); pstmt.execute(); //Retrieving the data ResultSet rs = stmt.executeQuery("select * from Technologies_data"); System.out.println("Contents of the table are: "); while(rs.next()) < System.out.println("Article: "+rs.getString("Name")); Clob clob = rs.getClob("Article"); Reader r = clob.getCharacterStream(); StringBuffer buffer = new StringBuffer(); int ch; while ((ch = r.read())!=-1) < buffer.append(""+(char)ch); >System.out.println("Contents: "+buffer.toString()); System.out.println(" "); > > >

Output

Connection established. Contents of the table are: Article: JavaFX Contents: JavaFX is a Java library using which you can develop Rich Internet Applications. By using Java technology, these applications have a browser penetration rate of 76%. Article: CoffeeScript Contents: CoffeeScript is a lightweight language based on Ruby and Python which transcompiles (compiles from one source language to another) into JavaScript. It provides better syntax avoiding the quirky parts of JavaScript, still retaining the flexibility and beauty of the language. Article: Cassandra Contents: Apache Cassandra is a highly scalable, high-performance distributed database designed to handle large amounts of data across many commodity servers, providing high availability with no single point of failure. It is a type of NoSQL database. Let us first understand what a NoSQL database does.

Источник

Convert Clob to String

To convert CLOB data type to string Retrieve the Clob value from a table using the getClob() or getCharacterStream() method of the PresparedStatement interface. Example Let us create a table with name technologies_data in MySQL database using the following query − The third column of the table Article stores the data of type CLOB.

Convert Clob to String

How can I get String out of Clob. I did google it, but

myClob.getSubString(0, (int) info.length())); 

is the only thing I get. Console says:

java.sql.SQLException: Invalid argument (s) in call at oracle.sql.CLOB.getSubString(CLOB.java:278) at ru.tenet.es09.dao.CompanyDAOImpl.get(CompanyDAOImpl.java:72) at ru.tenet.es09.dao.CompanyDAOImpl.getList(CompanyDAOImpl.java:132) at ru.tenet.es09.dao.AddressDAOImpl.getList(AddressDAOImpl.java:59) at ru.tenet.es09.Test.main(Test.java:11)

It points on getSubString() method. What is wrong?

Assuming you’re using standard JDBC, once you have a ResultSet object you should be able to call ResultSet#getString(«clob_field_name») to retrieve your CLOB data.

I know I’m late to this party!. Here is the one liner i used from hibernate library. If hibernate is already integrated to project then we can use annotations to convert clob to java String. In my case i had custom result transformer which read data from multiple tables after costly join. In the resultSetTransformer the below line does the job.

ClobType.INSTANCE.toString((Clob) tuple[2]) // org.hibernate.type.ClobType 

this my way (sorry my english)

 res = ps.executeQuery(); try < while (res.next()) < System.out.println(res.getClob(1));//confirm data sRet = res.getString(1); >> catch (SQLException e) < e.printStackTrace(); >finally

SOobject.setLongStringField( new SerialClob(entityString.toCharArray()));//Converting String to CLOB 

public String getLongStringField() < Reader reader = null; BufferedReader bufferedReader = null; try < reader = longStringField.getCharacterStream(); bufferedReader = new BufferedReader(reader); return IOUtils.toString(bufferedReader); >catch (Exception e) < throw new RuntimeException("Error while reading String from CLOB", e); >finally < IOUtils.closeQuietly(reader); IOUtils.closeQuietly(bufferedReader); >> 

Convert Clob containing emoji to String in Java, So I am trying to convert Clob with emoji to String in Java Spring specifically. My service obtained data from DB that already support UTF-8 encoding. Say I have this Clob data and wants this as St

How to convert a CLOB type to String in Java?

CLOB stands for Character Large Object in general, an sql clob is a built-in datatype and is used to store large amount of textual data. Using this datatype, you can store data up to 2,147,483,647 characters.

The java.sql.clob interface of the JDBC API represents the CLOB datatype. Since the Clob object in JDBC is implemented using an SQL locator, it holds a logical pointer to the SQL Clob (not the data).

MySQL database provides support for this data type using four variables namely, TINYTEXT, TEXT, MEDIUMTEXT and, LONGTEXT.

To convert CLOB data type to string

• Retrieve the Clob value from a table using the getClob() or getCharacterStream() method of the PresparedStatement interface.

Reader r = clob.getCharacterStream();

• Read each character one by one from the retrieved Stream of characters and append them to the StringBuilder or StringBuffer .

int j = 0; StringBuffer buffer = new StringBuffer(); int ch; while ((ch = r.read())!=-1) < buffer.append(""+(char)ch); >System.out.println(buffer.toString()); j++;

System.out.println(buffer.toString());

Example

Let us create a table with name technologies_data in MySQL database using the following query −

CREATE TABLE Technologies (Name VARCHAR(255), Type VARCHAR(255), Article LONGTEXT);

The third column of the table Article stores the data of type CLOB.

Following JDBC program initially inserts 5 records in the technologies_data table storing text file (contents of it) it to the article column (CLOB type).

Then, it retrieves the records of the table and, displays the name and contents of the article. Here, we are trying to converting the data of the retrieved CLOB into String and display it.

import java.io.FileReader; import java.io.Reader; import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class ClobToString < public static void main(String args[]) throws Exception < //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/sampledatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established. "); //Creating a Statement object Statement stmt = con.createStatement(); //Inserting values String query = "INSERT INTO Technologies_data VALUES (?, ?, ?)"; PreparedStatement pstmt = con.prepareStatement(query); pstmt.setString(1, "JavaFX"); pstmt.setString(2, "Java Library"); FileReader reader = new FileReader("E:\\images\\javafx_contents.txt"); pstmt.setClob(3, reader); pstmt.execute(); pstmt.setString(1, "CoffeeScript"); pstmt.setString(2, "Scripting Language"); reader = new FileReader("E:\\images\\coffeescript_contents.txt"); pstmt.setClob(3, reader); pstmt.execute(); pstmt.setString(1, "Cassandra"); pstmt.setString(2, "NoSQL Database"); reader = new FileReader("E:\\images\\cassandra_contents.txt"); pstmt.setClob(3, reader); pstmt.execute(); //Retrieving the data ResultSet rs = stmt.executeQuery("select * from Technologies_data"); System.out.println("Contents of the table are: "); while(rs.next()) < System.out.println("Article: "+rs.getString("Name")); Clob clob = rs.getClob("Article"); Reader r = clob.getCharacterStream(); StringBuffer buffer = new StringBuffer(); int ch; while ((ch = r.read())!=-1) < buffer.append(""+(char)ch); >System.out.println("Contents: "+buffer.toString()); System.out.println(" "); > > >

Output

Connection established. Contents of the table are: Article: JavaFX Contents: JavaFX is a Java library using which you can develop Rich Internet Applications. By using Java technology, these applications have a browser **** rate of 76%. Article: CoffeeScript Contents: CoffeeScript is a lightweight language based on Ruby and Python which transcompiles (compiles from one source language to another) into JavaScript. It provides better syntax avoiding the quirky parts of JavaScript, still retaining the flexibility and beauty of the language. Article: Cassandra Contents: Apache Cassandra is a highly scalable, high-performance distributed database designed to handle large amounts of data across many commodity servers, providing high availability with no single point of failure. It is a type of NoSQL database. Let us first understand what a NoSQL database does.

Java — How to unit test Clob conversion to String?, This simple method returns all the content of a Clob retrieved from the database as String. How can I test/mock it properly, guaranteeing it will always return the full content of the Clob argument (assuming its size will not be higher than an Integer)?

Most efficient solution for reading CLOB to String, and String to CLOB in Java?

I have a big CLOB (more than 32kB) that I want to read to a String, using StringBuilder. How do I do this in the most efficient way? I can not use the «int length» constructor for StringBuilder since the lenght of my CLOB is longer than a «int» and needs a «long» value.

I am not that confortable with the Java I/O classes, and would like to get some guidance.

Edit — I have tried with this code for clobToString():

private String clobToString(Clob data) < StringBuilder sb = new StringBuilder(); try < Reader reader = data.getCharacterStream(); BufferedReader br = new BufferedReader(reader); String line; while(null != (line = br.readLine())) < sb.append(line); >br.close(); > catch (SQLException e) < // handle this exception >catch (IOException e) < // handle this exception >return sb.toString(); > 

Ok I will suppose a general use, first you have to download apache commons , there you will find an utility class named IOUtils which has a method named copy();

Now the solution is: get the input stream of your CLOB object using getAsciiStream() and pass it to the copy() method.

InputStream in = clobObject.getAsciiStream(); StringWriter w = new StringWriter(); IOUtils.copy(in, w); String clobAsString = w.toString(); 

clob.getSubString(1, (int) clob.length()); 

For example Oracle oracle.sql.CLOB performs getSubString() on internal char[] which defined in oracle.jdbc.driver.T4CConnection and just System.arraycopy() and next wrap to String . You never get faster reading than System.arraycopy() .

UPDATE Get driver ojdbc6.jar, decompile CLOB implementation, and study which case could be faster based on the internals knowledge.

My answer is just a flavor of the same. But I tested it with serializing a zipped content and it worked. So I can trust this solution unlike the one offered first (that use readLine) because it will ignore line breaks and corrupt the input.

/********************************************************************************************* * From CLOB to String * @return string representation of clob *********************************************************************************************/ private String clobToString(java.sql.Clob data) < final StringBuilder sb = new StringBuilder(); try < final Reader reader = data.getCharacterStream(); final BufferedReader br = new BufferedReader(reader); int b; while(-1 != (b = br.read())) < sb.append((char)b); >br.close(); > catch (SQLException e) < log.error("SQL. Could not convert CLOB to string",e); return e.toString(); >catch (IOException e) < log.error("IO. Could not convert CLOB to string",e); return e.toString(); >return sb.toString(); > 

I can not use the «int length» constructor for StringBuilder since the length of my CLOB is longer than a int and needs a long value.

If the CLOB length is greater than fits in an int, the CLOB data won’t fit in a String either. You’ll have to use a streaming approach to deal with this much XML data.

If the actual length of the CLOB is smaller than Integer.MAX_VALUE , just force the long to int by putting (int) in front of it.

Fast convert CLOB to String without encoding in java, Fast convert CLOB to String without encoding in java. I have a problem: in my app CLOB converts to string very slow. For example, convert VARCHAR2 (40) to string takes 0,001ms, convert CLOB (~1600) to string takes 0.8. For converting to 1600/40=40 times more bytes, need 0.8/0.001=800 times more time (20 times …

Источник