javaweb学习总结――使用JDBC处理MySQL大数据

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/jdbcStudyusername=rootpassword=XDP

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{      //读取db.properties文件中的数据库连接信息      InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");      Properties prop = new Properties();      prop.load(in);            //获取数据库连接驱动      driver = prop.getProperty("driver");      //获取数据库连接URL地址      url = prop.getProperty("url");      //获取数据库连接用户名      username = prop.getProperty("username");      //获取数据库连接密码      password = prop.getProperty("password");            //加载数据库驱动      Class.forName(driver);          }catch (Exception e) {      throw new ExceptionInInitializerError(e);    }  }    /**  * @Method: getConnection  * @Description: 获取数据库连接对象  * @Anthor:孤傲苍狼  *  * @return Connection数据库连接对象  * @throws SQLException  */   public static Connection getConnection() throws SQLException{    return DriverManager.getConnection(url, username,password);  }    /**  * @Method: release  * @Description: 释放资源，  *   要释放的资源包括Connection数据库连接对象，负责执行SQL命令的Statement对象，存储查询结果的ResultSet对象  * @Anthor:孤傲苍狼  *  * @param conn  * @param st  * @param rs  */   public static void release(Connection conn,Statement st,ResultSet rs){    if(rs!=null){      try{        //关闭存储查询结果的ResultSet对象        rs.close();      }catch (Exception e) {        e.printStackTrace();      }      rs = null;    }    if(st!=null){      try{        //关闭负责执行SQL命令的Statement对象        st.close();      }catch (Exception e) {        e.printStackTrace();      }    }        if(conn!=null){      try{        //关闭Connection数据库连接对象        conn.close();      }catch (Exception e) {        e.printStackTrace();      }    }  }}

PreparedStatement.setCharacterStream(index, reader, length);//注意length长度须设置，并且设置为int型

reader = resultSet. getCharacterStream(String columnLabel);2 string s = resultSet.getString(String columnLabel);

create database jdbcstudy;use jdbcstudy;create table testclob(     id int primary key auto_increment,     resume text);

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: 使用JDBC操作MySQL的大文本* @author: 孤傲苍狼* @date: 2014-9-19 下午10:10:04**/ public class JdbcOperaClob {  /**  * @Method: add  * @Description:向数据库中插入大文本数据  * @Anthor:孤傲苍狼  *  */   @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);      //这种方式获取的路径，其中的空格会被使用“%20”代替      String path = JdbcOperaClob.class.getClassLoader().getResource("data.txt").getPath();      //将“%20”替换回空格      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("插入成功！！");      }      //关闭流      reader.close();    }catch (Exception e) {      e.printStackTrace();    }finally{      JdbcUtils.release(conn, st, rs);    }  }    /**  * @Method: read  * @Description: 读取数据库中的大文本数据  * @Anthor:孤傲苍狼  *  */   @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()){        //使用resultSet.getString("字段名")获取大文本数据的内容        content = rs.getString("resume");        //使用resultSet.getCharacterStream("字段名")获取大文本数据的内容        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);    }  }}

PreparedStatement. setBinaryStream(i, inputStream, length);

InputStream in = resultSet.getBinaryStream(String columnLabel);InputStream in = resultSet.getBlob(String columnLabel).getBinaryStream(); 

create table testblob(   id int primary key auto_increment,   image longblob );

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: 使用JDBC操作MySQL的二进制数据(例如图像、声音、二进制文)* @author: 孤傲苍狼* @date: 2014-9-19 下午10:10:04**/ public class JdbcOperaBlob {  /**  * @Method: add  * @Description:向数据库中插入二进制数据  * @Anthor:孤傲苍狼  *  */   @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);      //这种方式获取的路径，其中的空格会被使用“%20”代替      String path = JdbcOperaBlob.class.getClassLoader().getResource("01.jpg").getPath();      //将“%20”替换会空格      path = path.replaceAll("%20", " ");      File file = new File(path);      FileInputStream fis = new FileInputStream(file);//生成的流      st.setBinaryStream(1, fis,(int) file.length());      int num = st.executeUpdate();      if(num>0){        System.out.println("插入成功！！");      }      fis.close();    }catch (Exception e) {      e.printStackTrace();    }finally{      JdbcUtils.release(conn, st, rs);    }  }    /**  * @Method: read  * @Description: 读取数据库中的二进制数据  * @Anthor:孤傲苍狼  *  */   @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();//这种方法也可以        InputStream in = rs.getBinaryStream("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);    }  }}