package XiaB204;
import com.mysql.jdbc.Driver;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author xxp
* @date 2020/7/20-14:01
*/
public class JDBCClob {
//写入小说的函数
public static void ClobDemo(){
Connection conn=null;
PreparedStatement preparedStatement=null;
try {
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver)clazz.newInstance();
String URL="jdbc:mysql://localhost:3306/test?useUnicode=true&&characterEncoding=utf8";
Properties info = new Properties();
info.setProperty("user","root");
info.setProperty("password","Xxx");
conn = driver.connect(URL, info);//获取连接
String sql="insert into mynovel values(?,?)";//预编译sql语句,推荐的用法,有效预防了SQL注入问题
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1,1);//设置预编译的第一个参数
File file = new File("E:\\all.txt");//读取e盘下文件
InputStream inputStream = new FileInputStream(file);
Reader reader = new InputStreamReader(inputStream,"utf-8");//这里的utf-8和读的应设置成一致,否则会出现乱码
preparedStatement.setCharacterStream(2,reader,(int)file.length());
int count=preparedStatement.executeUpdate();
if(count>0){
System.out.println("操作成功");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if(preparedStatement!=null){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//读取小说的函数
public static void ClobReader(){
Connection conn=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver)clazz.newInstance();
String URL="jdbc:mysql://localhost:3306/test?useUnicode=true&&characterEncoding=utf8";
Properties info = new Properties();
info.setProperty("user","root");
info.setProperty("password","XXX");
conn = driver.connect(URL, info);//获取连接
String sql="select novel from mynovel where id=?";//预编译SQL
preparedStatement = conn.prepareStatement(sql);//
preparedStatement.setInt(1,1);//设置第一个参数
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){//对结果集处理
Reader novel = resultSet.getCharacterStream("novel");//获取从数据库查询到的字符集
FileWriter writer = new FileWriter("src/小说.txt");//把字符写入到src/小说.txt文件
char[] tem=new char[100];
int len=-1;
while((len=novel.read(tem))!=-1){
writer.write(tem,0,len);
}
writer.close();
novel.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) {
// ClobDemo();
ClobReader();
}
}
报错信息:
Packet for query is too large (4241240 > 4194304). You can change this value on the server by setting the max_allowed_packet'
超过了默认的最大的容量
show variables like '%max_allowed_packet%';
大文件存放在mynovel表格中
create table mynovel(
id numeric primary key,
novel MEDIUMTEXT --可根据文件大小设置属性
);
进行了修改
alter table mynovel modify novel LONGTEXT;