Clob:Character Large Object字符(小说)
Blob:Binary Large Object二进制
LobDemo.java文件:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
//大数据的存取
public class LobDemo {
/*
create table t2(
id int,
content longtext
);
*/
//存大文本数据
@Test
public void test1() throws Exception{
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("insert into t2 values (?,?)");
stmt.setInt(1, 1);
//以流的方式
File file = new File("src/jpm.txt");
Reader reader = new FileReader(file);
stmt.setCharacterStream(2, reader, (int)file.length());//PreparedStatement的实现是由数据库驱动提供的
//MySQL:setCharacterStream(int,Reader,long);根本没有实现。
//MySQL根本不支持那么大的数据。
stmt.executeUpdate();
JdbcUtil.release(null, stmt, conn);
}
//取大文本数据
@Test
public void test2() throws Exception{
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("select * from t2 where id=1");
ResultSet rs = stmt.executeQuery();
if(rs.next()){
Reader r = rs.getCharacterStream("content");
//内容保存D盘的1.txt文件中
Writer w = new FileWriter("d:/1.txt");
int len = -1;
char c[] = new char[1024];
while((len=r.read(c))!=-1){
w.write(new String(c), 0, len);
}
r.close();
w.close();
}
JdbcUtil.release(rs, stmt, conn);
}
/*
create table t3(
id int,
content longblob
);
*/
//存大文件
@Test
public void test3() throws Exception{
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("insert into t3 values (?,?)");
stmt.setInt(1, 1);
//以流的方式
InputStream in = new FileInputStream("src/26.jpg");
stmt.setBinaryStream(2, in, in.available());
stmt.executeUpdate();
JdbcUtil.release(null, stmt, conn);
}
//取大文件
@Test
public void test4() throws Exception{
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("select * from t3 where id=1");
ResultSet rs = stmt.executeQuery();
if(rs.next()){
InputStream in = rs.getBinaryStream("content");
OutputStream out = new FileOutputStream("d:/wife.jpg");
int len = -1;
byte b[] = new byte[1024];
while((len=in.read(b))!=-1){
out.write(b,0,len);
}
in.close();
out.close();
}
JdbcUtil.release(null, stmt, conn);
}
}
JdbcUtil.java文件:
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
//工具类
public class JdbcUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
static{
try {
ClassLoader cl = JdbcUtil.class.getClassLoader();
InputStream in = cl.getResourceAsStream("dbcfg.properties");
Properties props = new Properties();
props.load(in);
driverClass = props.getProperty("driverClass");
url = props.getProperty("url");
user = props.getProperty("user");
password = props.getProperty("password");
Class.forName(driverClass);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws Exception{
Connection conn = DriverManager.getConnection(url,user, password);
return conn;
}
public static void release(ResultSet rs,Statement stmt,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
dbcnfg.properties文件:
`
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day16
user=root
password=admin