JDBC的MYSQL继续学习

最新推荐文章于 2025-05-15 17:47:47 发布

原创最新推荐文章于 2025-05-15 17:47:47 发布 · 293 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#jdbc #mysql #insert #exception #数据库连接池 #null

本文介绍如何在MySQL中处理大文本数据和进行批量访问操作，包括使用CLOB存储大文本、读取和写入文件，以及使用BLOB存储大字节数，并通过批量SQL语句提高效率。

JDBC的MYSQL

一、处理大文本

首先我们在mysql中建一个表名字是create table testclob( idint primary key auto_increment,resume text)

然后，我们拷入.com.mysql.jdbc.driver.驱动我们把数据存入数据库中，在这里呢我用的是数据库连接池。

我们创建一个类，用于创建测试大文本.这个累的名字是Demo1我们用于测试带上主方法。这个类是这样写的。

packageTest;

import java.io.File;

import java.io.FileReader;

import java.io.FileWriter;

import java.io.Reader;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class Demo1 {

publicstatic void main(String[] args) {

insert();

//find();

}

publicstatic void insert(){

Connectionconn=null;

PreparedStatementpstmt=null;

try{

Class.forName("com.mysql.jdbc.Driver");

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/my","root","qiaoyu.");

//conn=DB.getConn();

Stringsql="insert into testclob (resume) value(?)";

pstmt=conn.prepareStatement(sql);

Stringstring=Demo1.class.getClassLoader().getResource("1.txt").getPath();

System.out.println(string);

Filef=new File("d:/1.txt");

pstmt.setString(1,"ddddddd");

pstmt.setCharacterStream(1,newFileReader("d:/1.txt"),f.length()); //--报错----

pstmt.executeUpdate();

}catch (Exception e) {

e.printStackTrace();

}finally{

try{

pstmt.close();

conn.close();

}catch (SQLException e) {

e.printStackTrace();

}

publicstatic void find(){

Connectionconn=null;

PreparedStatementpstmt=null;

ResultSetrs=null;

try{

Class.forName("com.mysql.jdbc.Driver");

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/my","root","qiaoyu.");

//conn=DB.getConn();

Stringsql="select resume from testclob where id=7";

pstmt=conn.prepareStatement(sql);

rs=pstmt.executeQuery();

if(rs.next()){

Readerreader=rs.getCharacterStream("resume");

System.out.println(rs.getString("resume"));

charbuff[]=new char[1024];

intlen=0;

FileWriter fw=newFileWriter("d:/2.txt");

while((len=reader.read(buff))>-1){

fw.write(buff,0,len);

}

}catch (Exception e) {

e.printStackTrace();

}finally{

try{

rs.close();

conn.close();

pstmt.close();

}catch (SQLException e) {

e.printStackTrace();

}

主要是这样的但是呢？在这里用数据库连接池的时候，上面的是报错的所以最好直接添加写

二、这个我们将blob大字节数的书写是这样的package Test;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class Demo2 {

publicstatic void main(String[] args) {

//insert();

find();

}

publicstatic void insert(){

Connectionconn=null;

PreparedStatementpstmt=null;

try{

Class.forName("com.mysql.jdbc.Driver");

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/my","root","qiaoyu.");

//conn=DB.getConn();

Stringsql="insert into testblob (images) value(?)";

pstmt=conn.prepareStatement(sql);

Filef=new File("d:/3.jpg");

pstmt.setBinaryStream(1,newFileInputStream(f),f.length());

inti=pstmt.executeUpdate();

if(i>0) {

System.out.println("插入成功");

}else{

System.out.println("插入失败");

}

}catch (Exception e) {

e.printStackTrace();

}finally{

try{

pstmt.close();

conn.close();

}catch (SQLException e) {

e.printStackTrace();

}

publicstatic void find(){

Connectionconn=null;

PreparedStatementpstmt=null;

ResultSetrs=null;

try{

Class.forName("com.mysql.jdbc.Driver");

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/my","root","qiaoyu.");

//conn=DB.getConn();

Stringsql="select images from testblob where id=1";

pstmt=conn.prepareStatement(sql);

rs=pstmt.executeQuery();

if(rs.next()){

InputStreamfis=rs.getBinaryStream("images");

byte[]buff=new byte[1024];

intlen=0;

FileOutputStreamfos=new FileOutputStream("d:/2.jpg");

while((len=fis.read(buff))>0) {

fos.write(buff,0,len);

}

}catch (Exception e) {

e.printStackTrace();

}finally{

try{

rs.close();

conn.close();

pstmt.close();

}catch (SQLException e) {

e.printStackTrace();

}

三、在下面我给大家讲的是数据库的批量访问

package Test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Statement;

public class Demo3 {

publicstatic void main(String[] args) {

insert();

}

publicstatic void insert(){

Connectionconn=null;

Statementpstmt=null;

try{

Class.forName("com.mysql.jdbc.Driver");

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/my","root","qiaoyu.");

Stringsql1="insert into testbatch values(3,'aaa')";

Stringsql2="insert into testbatch values(4,'bbb')";

Stringsql3="insert into testbatch values(5,'ccc')";

//Stringsql4="delete from testbatch where id=3";

pstmt=conn.createStatement();

pstmt.addBatch(sql1);

pstmt.addBatch(sql2);

pstmt.addBatch(sql3);

//pstmt.addBatch(sql4);

int[]i= pstmt.executeBatch();

for(int j:i) {

System.out.println("--"+j+"---");

}

pstmt.clearBatch();

}catch (Exception e) {

e.printStackTrace();

}finally{

try{

pstmt.close();

conn.close();

}catch (SQLException e) {

e.printStackTrace();

}

publicstatic void prepar(){

Connectionconn=null;

PreparedStatementpstmt=null;

try{

Class.forName("com.mysql.jdbc.Driver");

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/my","root","qiaoyu.");

Stringsql="insert into testbatch values(?,?)";

pstmt=conn.prepareStatement(sql);

for(int i = 0; i < 10000; i++) {

pstmt.setInt(1,i);

pstmt.setString(2,"ccc");

pstmt.addBatch();

if(i%1000==0){

pstmt.executeBatch();

pstmt.clearBatch();

}

pstmt.executeBatch();

pstmt.clearBatch();

}catch (Exception e) {

e.printStackTrace();

}finally{

try{

pstmt.close();

conn.close();

}catch (SQLException e) {

e.printStackTrace();

}