JDBC的MYSQL

 

一、处理大文本

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

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

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

       package Test;

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 {

       public static void main(String[] args) {

              insert();

              //find();

       }

       public static void insert(){

              Connection conn=null;

              PreparedStatement pstmt=null;

              try {

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

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

                     //conn=DB.getConn();

                     String sql="insert into testclob (resume) value(?)";

                     pstmt=conn.prepareStatement(sql);

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

                     System.out.println(string);

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

                     pstmt.setString(1, "ddddddd");

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

                     pstmt.executeUpdate();

              } catch (Exception e) {

                     e.printStackTrace();

              }finally{

                     try {

                            pstmt.close();

                            conn.close();

                     } catch (SQLException e) {

                            e.printStackTrace();

                     }

                    

              }

       }

       public static void find(){

              Connection conn=null;

              PreparedStatement pstmt=null;

              ResultSet rs=null;

              try {

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

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

                     //conn=DB.getConn();

                     String sql="select resume from testclob where id=7";

                     pstmt=conn.prepareStatement(sql);

                     rs=pstmt.executeQuery();

                     if(rs.next()){

                            Reader reader=rs.getCharacterStream("resume");

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

                            char buff[]=new char[1024];

                            int len=0;

                            FileWriter fw=new FileWriter("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 {

 

       public static void main(String[] args) {

                     //insert();

                     find();

       }

       public static void insert(){

              Connection conn=null;

              PreparedStatement pstmt=null;

              try {

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

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

                     //conn=DB.getConn();

                     String sql="insert into testblob (images) value(?)";

                     pstmt=conn.prepareStatement(sql);

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

                     pstmt.setBinaryStream(1,new FileInputStream(f),f.length());

                     int i=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();

                     }

              }

       }

      

      

       public static void find(){

              Connection conn=null;

              PreparedStatement pstmt=null;

              ResultSet rs=null;

              try {

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

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

                     //conn=DB.getConn();

                     String sql="select images from testblob where id=1";

                     pstmt=conn.prepareStatement(sql);

                     rs=pstmt.executeQuery();

                     if(rs.next()){

                            InputStream fis=rs.getBinaryStream("images");

                            byte[] buff=new byte[1024];

                            int len=0;

                            FileOutputStream fos=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 {

       public static void main(String[] args) {

              insert();

       }

       public static void insert(){

              Connection conn=null;

              Statement pstmt=null;

              try {

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

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

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

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

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

                     //String sql4="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();

                     }

              }

       }

       public static void prepar(){

              Connection conn=null;

              PreparedStatement pstmt=null;

              try {

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

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

                     String sql="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();

                     }

              }

       }

 

}