一、向数据库读取文本文件(CLOB)
- 建表
create table myNovel (
id int primary key auto_increment,
novel text);
/*其中,MySQL提供了以下数据类型来存储CLOB:
tinytext(255)、text(64k)、mediumtext(16M)、longtext(4G)*/
- 代码运行
package com.jdbc;
import com.jdbc.dao.jdbcDao;
import com.jdbc.dao.jdbcDaoImpl;
public class main {
public static void main(String[] args) {
jdbcDao dao=new jdbcDaoImpl();
dao.addByClob();
dao.selectByClob();
}
}
package com.jdbc.dao;
public interface jdbcDao {
//添加小说
public void addByClob();
//取出小说
public void selectByClob();
}
package com.jdbc.dao;
import com.jdbc.util.util;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class jdbcDaoImpl implements jdbcDao {
//添加小说
public void addByClob() {
Connection conn=null;
PreparedStatement ps=null;
try {
conn= util.getConnection();
ps=util.getPreparedStatement(conn,"insert into myNovel values (?,?)");
ps.setInt(1,1);
//由于内存原因,不能通过基本数据类型写入,所以要通过流
File file=new File("D:\\moon.txt");
//将小说写进流中
InputStream in=new FileInputStream(file);
//InputStreamReader可设置编码,这里设置成utf-8
Reader reader=new InputStreamReader(in,"utf-8");
//用流取代占位符
ps.setCharacterStream(2,reader,file.length());
//如果插入成功则读取
if (ps.executeUpdate()>0) {
System.out.println("插入成功");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
util.closePs(ps);
util.closeConn(conn);
}
}
/**
* 取出小说
*/
@Override
public void selectByClob() {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn=util.getConnection();
ps=util.getPreparedStatement(conn,"select novel from myNovel where id=?");
ps.setInt(1,1);
rs=ps.executeQuery();
if (rs.next()) {
Reader reader=rs.getCharacterStream("novel");
//通过流操作输出,将小说存放在src/小说1.txt中
Writer writer=new FileWriter("src/小说1.txt");
char[] chars=new char[100];
int length=-1;
while ((length=reader.read(chars))!=-1) {
writer.write(chars,0,length);
}
writer.close();
reader.close();
}
}catch (Exception e) {
e.printStackTrace();
} finally {
util.closeRs(rs);
util.closePs(ps);
util.closeConn(conn);
}
}
}
package com.jdbc.util;
import com.sun.xml.internal.bind.v2.runtime.reflect.Lister;
import javax.management.DescriptorRead;
import javax.naming.ldap.PagedResultsControl;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import java.util.concurrent.CopyOnWriteArrayList;
public class util {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
//获取配置文件
Properties p=new Properties();
InputStream is=util.class.getResourceAsStream("/jdbc.properties");
try {
p.load(is);
driver=p.getProperty("driver");
url=p.getProperty("url");
username=p.getProperty("username");
password=p.getProperty("password");
//加载驱动类
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//创建数据库连接对象
public static Connection getConnection() {
Connection conn=null;
try {
conn= DriverManager.getConnection(url,username,password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
//创建sql语句对象
public static PreparedStatement getPreparedStatement(Connection conn,String sql) {
PreparedStatement ps=null;
try {
ps=conn.prepareStatement(sql);
} catch (Exception e) {
e.printStackTrace();
}
return ps;
}
public static Statement getStatement(Connection conn) {
Statement stmt=null;
try {
stmt=conn.createStatement();
} catch (Exception e) {
e.printStackTrace();
}
return stmt;
}
//关闭资源
public static void closeRs(ResultSet rs) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void closePs(PreparedStatement ps) {
try {
ps.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void closeConn(Connection conn) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
- 结果
二、向数据库读取二进制文件(BLOB)
- 建表
create table myImg (
id int primary key auto_increment,
img longblob);
/*其中,MySQL提供了以下数据类型来存储BLOB:
tinyblob、blob、mediumblob、longblob*/
- 代码运行
package com.jdbc;
import com.jdbc.dao.jdbcDao;
import com.jdbc.dao.jdbcDaoImpl;
public class main {
public static void main(String[] args) {
jdbcDao dao=new jdbcDaoImpl();
dao.addByBlob();
dao.selectByBlob();
}
}
package com.jdbc.dao;
import com.jdbc.util.util;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class jdbcDaoImpl implements jdbcDao {
/**
* 添加二进制文件
*/
public void addByBlob() {
Connection conn=null;
PreparedStatement ps=null;
try {
conn= util.getConnection();
ps=util.getPreparedStatement(conn,"insert into myImg values (?,?)");
ps.setInt(1,1);
File file=new File("D:\\moon.jpg");
InputStream in=new FileInputStream(file);
ps.setBinaryStream(2,in,file.length());
if (ps.executeUpdate() > 0) {
System.out.println("插入成功");
}
in.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
util.closePs(ps);
util.closeConn(conn);
}
}
/**
* 取出二进制文件
*/
@Override
public void selectByBlob() {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn=util.getConnection();
ps=util.getPreparedStatement(conn,"select img from myImg where id=?");
ps.setInt(1,1);
rs=ps.executeQuery();
if (rs.next()) {
InputStream in=rs.getBinaryStream("img");
OutputStream out=new FileOutputStream("src/img.jpg");
byte[] bytes=new byte[1000];
int length=-1;
while ((length=in.read(bytes))!=-1) {
out.write(bytes,0,length);
}
out.close();
in.close();
}
}catch (Exception e) {
e.printStackTrace();
} finally {
util.closeRs(rs);
util.closePs(ps);
util.closeConn(conn);
}
}
}
public class util {
……
}
- 结果
三、CLOB与BLOB的区别
CLOB | BLOB |
---|---|
字符流(Reader,WRriter) | 字节流(InputStream,OutputStream) |
setCharacterStream | setBinaryStream |
getCharacterStream | getBinaryStream |
最后小小总结+提醒,如果运行代码时,ps.executeUpdate()出错,可能是二进制文件过大,更改一下数据库BLOB的类型就好(例如我一开始数据库中img的类型是blob,结果就报错了,所以接着我果断改为了longblob就运行成功啦)