Javaweb Learning Summary--using JDBC to handle MySQL large data _java

BLOB (Binary large object), binary large objects, is a container in which binary files can be stored. In the computer, a blob is often the type of field used in a database to store a binary file, a blob is a large file, a typical blob is a picture or a sound file, and because of their size, it must be handled in a special way (for example: uploading, downloading, or storing to a database).

I. Basic CONCEPTS

In actual development, it is sometimes necessary to use the program to save large text or binary data directly into the database.

For MySQL, there are only blobs, and there is no clob,mysql storage large text using the Text,text and BLOBs are divided into:

Tinytext, TEXT, Mediumtext and Longtext

Tinyblob, BLOBs, Mediumblob and Longblob

Second, build test environment

2.1, the construction of the test project framework

As shown in figure:

2.2. Write Db.properties configuration file

Driver=com.mysql.jdbc.driver
url=jdbc:mysql://localhost:3306/jdbcstudy
username=root
Password=XDP

2.3, the preparation Jdbcutils tool class

Package me.gacl.utils;
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 Jdbcutils {private static String driver = null;
  private static String URL = null;
  private static String username = null;
  
  private static String password = NULL; static{try{//Read the database connection information in the Db.properties file InputStream in = JdbcUtils.class.getClassLoader (). Getresourceass
      Tream ("Db.properties");
      Properties prop = new properties ();
      
      Prop.load (in);
      Get database connection Drive Driver = Prop.getproperty ("Driver");
      Gets the database connection URL address url = prop.getproperty ("url");
      Gets the database connection user name username = prop.getproperty ("username");
      
      Get database connection Password Password = prop.getproperty ("password");
      
    Load Database driver Class.forName (driver); }catch (Exception e) {throw new exceptionininitialIzererror (e);
  }/** * @Method: getconnection * @Description: Getting database Connection objects * @Anthor: Lonely Wolf * * @return Connection Database connection objects * @throws SQLException */public static Connection getconnection () throws sqlexception{return Drivermanager.get
  Connection (URL, username,password); /** * @Method: Release * @Description: Releasing resources, * resources to be freed include connection database connection objects, statement objects responsible for executing SQL commands, storing query results Resul Tset Object * @Anthor: Aloof Wolf * * @param conn * @param St * @param rs/public static void release (Connection conn
      , Statement St,resultset rs) {if (rs!=null) {try{//close ResultSet object that stores query results rs.close ();
      }catch (Exception e) {e.printstacktrace ();
    rs = null;
      } if (st!=null) {try{//Close the statement object responsible for executing the SQL command St.close ();
      }catch (Exception e) {e.printstacktrace ();
      } if (conn!=null) {try{//Close Connection Database Connection object Conn.close (); }catch (EXception e) {e.printstacktrace ();

 }
    }
  }
}

Third, use JDBC to handle the large text of MySQL

For the text type in MySQL, you can call the following method setting

Preparedstatement.setcharacterstream (index, reader, length);/Note length must be set and set to type int

For the text type in MySQL, you can call the following method to obtain the

Reader = ResultSet. Getcharacterstream (string ColumnLabel); 2 string s = resultset.getstring (string columnlabel);

3.1. Test example

1. Write SQL test Script

Create database jdbcstudy;
Use Jdbcstudy;
CREATE TABLE Testclob
(
     ID int primary key auto_increment,
     resume text
);

2, write the test code as follows:

Package Me.gacl.demo;
Import Java.io.File;
Import Java.io.FileReader;
Import Java.io.FileWriter;
Import Java.io.Reader;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import Me.gacl.utils.JdbcUtils;

Import Org.junit.Test; /** * @ClassName: Jdbcoperaclob * @Description: Using JDBC to manipulate the large text of MySQL * @author: Lonely Wolf * @date: 2014-9-19 Afternoon 10:10:04 * * * * * * Publ  IC class Jdbcoperaclob {/** * @Method: Add * @Description: Insert large text data into the database * @Anthor: Lonely Wolf * * * * @Test public
    void Add () {Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    Reader reader = null;
      try{conn = Jdbcutils.getconnection ();
      String sql = "INSERT into Testclob (resume) VALUES (?)";
      st = conn.preparestatement (SQL);
      This way gets the path where the spaces are used "%20" instead of String path = JdbcOperaClob.class.getClassLoader (). GetResource ("Data.txt"). GetPath ();
      Replace "%20" with Go home Path = Path.replaceall ("%20", ""); File File = new FilE (path);
      reader = new FileReader (file);
      St.setcharacterstream (1, Reader, (int) file.length ());
      int num = St.executeupdate (); if (num>0) {System.out.println (insert succeeded!!)
      ");
    }//Close stream reader.close ();
    }catch (Exception e) {e.printstacktrace ();
    }finally{Jdbcutils.release (Conn, St, RS);
    }/** * @Method: Read * @Description: Reading large text data in the database * @Anthor: Aloof Wolf * * * * * @Test public void Read () {
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
      try{conn = Jdbcutils.getconnection ();
      String sql = "Select resume from Testclob where id=2";
      st = conn.preparestatement (SQL);
      
      rs = St.executequery ();
      String contentstr = "";
      String content = "";
        if (Rs.next ()) {//Use resultset.getstring ("field name") to get the contents of large text data content = rs.getstring ("Resume"); Get content for large text data using Resultset.getcharacterstream ("field name") Reader reader = RS.GEtcharacterstream ("Resume");
        Char buffer[] = new char[1024];
        int len = 0;
        FileWriter out = new FileWriter ("D:\\1.txt");
          while (len=reader.read (buffer) >0) {contentstr + = new String (buffer);
        Out.write (buffer, 0, Len);
        } out.close ();
      Reader.close ();
      } System.out.println (content);
      System.out.println ("-----------------------------------------------");
    System.out.println (CONTENTSTR);
    }catch (Exception e) {e.printstacktrace ();
    }finally{Jdbcutils.release (Conn, St, RS);

 }
  }
}

Iv. using JDBC to process MySQL binary data

For blob types in MySQL, you can call the following method settings:

PreparedStatement. Setbinarystream (i, inputstream, length);

For the blob type in MySQL, you can call the following method to obtain:

InputStream in = Resultset.getbinarystream (String columnlabel);
InputStream in = Resultset.getblob (String columnlabel). Getbinarystream (); 

4.1. Test example

1. Write SQL test Script

CREATE TABLE Testblob
(
   ID int primary key auto_increment,
   image Longblob
 );

2, write the test code as follows:

Package Me.gacl.demo;
Import Java.io.File;
Import Java.io.FileInputStream;
Import Java.io.FileOutputStream;
Import Java.io.InputStream;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import Me.gacl.utils.JdbcUtils;

Import Org.junit.Test; /** * @ClassName: Jdbcoperaclob * @Description: Use JDBC to manipulate MySQL binary data (e.g., image, sound, binary) * @author: Aloof Wolf * @date: 2014-9-19 afternoon 10: 
  10:04 */public class Jdbcoperablob {/** * @Method: Add * @Description: Insert binary data into the database * @Anthor: Aloof Wolf * * * *
    @Test public void Add () {Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
      try{conn = Jdbcutils.getconnection ();
      String sql = "INSERT into Testblob (image) VALUES (?)";
      st = conn.preparestatement (SQL);
      This way gets the path where the spaces are used "%20" instead of String path = JdbcOperaBlob.class.getClassLoader (). GetResource ("01.jpg"). GetPath ();
      Replace "%20" with a space path = Path.replaceall ("%20", ""); File File = newFile (path);
      FileInputStream fis = new FileInputStream (file)//generated stream St.setbinarystream (1, FIS, (int) file.length ());
      int num = St.executeupdate (); if (num>0) {System.out.println (insert succeeded!!)
      ");
    } fis.close ();
    }catch (Exception e) {e.printstacktrace ();
    }finally{Jdbcutils.release (Conn, St, RS);
    }/** * @Method: Read * @Description: Reading binary data in database * @Anthor: Aloof Wolf * * * * * * @Test public void Read () {
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
      try {conn = jdbcutils.getconnection ();
      String sql = "Select image from Testblob where id=?";
      st = conn.preparestatement (SQL);
      St.setint (1, 1);
      rs = St.executequery (); if (Rs.next ()) {//inputstream in = Rs.getblob ("image"). Getbinarystream ()//This method can also be inputstream in = rs.ge
        Tbinarystream ("image");
        int len = 0;
        
        byte buffer[] = new byte[1024]; FIleoutputstream out = new FileOutputStream ("d:\\1.jpg");
        while (len = in.read (buffer) > 0) {out.write (buffer, 0, Len);
        } in.close ();
      Out.close ();
    } catch (Exception e) {e.printstacktrace ();
    finally {jdbcutils.release (conn, St, RS);

 }
  }
}

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.