JDBC中CLOB字段类型的示例
测试数据库MYSQL
测试表:
CREATE TABLE `jdbc.clob_test` ( `
id` INTEGER NOT NULL AUTO_INCREMENT,
clob` TEXT NOT NULL,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
示例代码:
package test.jdbc;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.Reader;
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* 这是一个CLOB字段类型的示例
*/
public class ClobRW {
public static void main(String[] args) throws Exception {
create();
read();
}
public static void create()
throws Exception {
// TODO Auto-generated method stub
System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jdbc", "root", "");
String sql = "insert into clob_test (clob) values (?)";
PreparedStatement stat = conn.prepareStatement(sql);
File file = new File("src/test/jdbc/ClobRW.java");
Reader reader = new BufferedReader(new FileReader(file));
stat.setCharacterStream(1, reader, (int)file.length());
stat.executeUpdate();
reader.close();
stat.close();
conn.close();
}
public static void read() throws Exception {
// TODO Auto-generated method stub
System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jdbc", "root", "");
String sql = "select clob from clob_test";
PreparedStatement stat = conn.prepareStatement(sql);
ResultSet rs = stat.executeQuery();
while (rs.next()) {
Clob clob = rs.getClob("clob");
Reader reader = clob.getCharacterStream();
File file = new File("ClobRW.java.bak");
Writer writer = new BufferedWriter(new FileWriter(file));
char[] cbuf = new char[1024];
for (int i = 0; (i = reader.read(cbuf)) > 0;) {
writer.write(cbuf, 0, i);
}
writer.close();
reader.close();
}
rs.close();
stat.close();
conn.close();
}
}
运行结果:
create方法的结果可以用desc clob_test命令看表中插入的文件内容
read方法的结果可以看当前目录下生成的ClobRW.java.bak文件的内容
总结:
在向clob字段类型中插入数据时,要使用javaio的reader,读入文件。
而相反从clob字段中读出数据时,同样使用javaio的reader,再用javaio的writer写入文件。
实践时的发生的问题和原因调查:
如果在设置字符流的地方不加类型转换的话,如下:
stat.setCharacterStream(1, reader, file.length());
则会出现如下错误
Exception in thread "main" java.lang.AbstractMethodError: com.mysql.jdbc.PreparedStatement.setCharacterStream(ILjava/io/Reader;J)V
at test.jdbc.ClobRW.create(ClobRW.java:37)
at test.jdbc.ClobRW.main(ClobRW.java:23)
后来看了看java和mysql的jdbc驱动两方面的代码,原因明白,原来是用的jdk1.6的版本中,有长度为long类型的方法。
而对应的mysql的jdbc驱动jar中,还没有实现。
将其进行类型转换后,即可正常运行。
测试数据库MYSQL
测试表:
CREATE TABLE `jdbc.clob_test` ( `
id` INTEGER NOT NULL AUTO_INCREMENT,
clob` TEXT NOT NULL,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
示例代码:
package test.jdbc;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.Reader;
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* 这是一个CLOB字段类型的示例
*/
public class ClobRW {
public static void main(String[] args) throws Exception {
create();
read();
}
public static void create()
throws Exception {
// TODO Auto-generated method stub
System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jdbc", "root", "");
String sql = "insert into clob_test (clob) values (?)";
PreparedStatement stat = conn.prepareStatement(sql);
File file = new File("src/test/jdbc/ClobRW.java");
Reader reader = new BufferedReader(new FileReader(file));
stat.setCharacterStream(1, reader, (int)file.length());
stat.executeUpdate();
reader.close();
stat.close();
conn.close();
}
public static void read() throws Exception {
// TODO Auto-generated method stub
System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jdbc", "root", "");
String sql = "select clob from clob_test";
PreparedStatement stat = conn.prepareStatement(sql);
ResultSet rs = stat.executeQuery();
while (rs.next()) {
Clob clob = rs.getClob("clob");
Reader reader = clob.getCharacterStream();
File file = new File("ClobRW.java.bak");
Writer writer = new BufferedWriter(new FileWriter(file));
char[] cbuf = new char[1024];
for (int i = 0; (i = reader.read(cbuf)) > 0;) {
writer.write(cbuf, 0, i);
}
writer.close();
reader.close();
}
rs.close();
stat.close();
conn.close();
}
}
运行结果:
create方法的结果可以用desc clob_test命令看表中插入的文件内容
read方法的结果可以看当前目录下生成的ClobRW.java.bak文件的内容
总结:
在向clob字段类型中插入数据时,要使用javaio的reader,读入文件。
而相反从clob字段中读出数据时,同样使用javaio的reader,再用javaio的writer写入文件。
实践时的发生的问题和原因调查:
如果在设置字符流的地方不加类型转换的话,如下:
stat.setCharacterStream(1, reader, file.length());
则会出现如下错误
Exception in thread "main" java.lang.AbstractMethodError: com.mysql.jdbc.PreparedStatement.setCharacterStream(ILjava/io/Reader;J)V
at test.jdbc.ClobRW.create(ClobRW.java:37)
at test.jdbc.ClobRW.main(ClobRW.java:23)
后来看了看java和mysql的jdbc驱动两方面的代码,原因明白,原来是用的jdk1.6的版本中,有长度为long类型的方法。
而对应的mysql的jdbc驱动jar中,还没有实现。
将其进行类型转换后,即可正常运行。