André L. S.

The Character Large Object (or CLOB) is a commonly found in databases and used to store high quantity of characters.

At MySQL, for example, this field is called MEMO.

Writing CLOB field

The method setAsciiStream of PreparedStatement allow to pass data to a CLOB.

ps.setAsciiStream(bindPosition, inputStream, textLength);

bindPosition – Position in PreparedStatment’s CLOB field.
inputStream – Used to pass data.
textLength – Data (text) length.

Full code:

String sql = "INSERT INTO TABLE (text) VALUES(?)";
		try{
			String txt = readTxtFile();
			ByteArrayInputStream bais = new ByteArrayInputStream(txt.getBytes());

			PreparedStatement ps = conexao.prepareStatement(sql);
			//CLOB is '?' at first position
			ps.setAsciiStream(1, bais, txt.length());

			ps.execute();

			ps.close();
		}catch (Exception e) {
			e.printStackTrace();
		}

Retrieving CLOB data

We’re using SELECT clause, this clause returns a ResultSet to fetch data and, calling getClob method, passing column name or your position in the query, it give to you a Clob object.

rs.getClob("xml");

Full code:

String sql = "SELECT xml FROM TEST";
		try{
			PreparedStatement ps = conexao.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();

			while(rs.next()){
				Clob clob = rs.getClob("xml");
				BufferedReader reader = new BufferedReader(clob.getCharacterStream());
				StringBuffer strBuf = new StringBuffer();

				String linha = null;
				while((linha = reader.readLine()) != null){
					strBuf.append(linha);
//Character.LINE_SEPARATOR insert break line
					strBuf.append((char)Character.LINE_SEPARATOR);
				}

				System.out.println("=========== CLOB ===========");
				System.out.println(strBuf.toString());
			}

			rs.close();
			ps.close();
		}catch (Exception e) {
			e.printStackTrace();
		}

That’s it! Easy and painless

Download full code here

To write a BLOB see this tutorial.