1 driver=com.mysql.jdbc.Driver2 url=jdbc:mysql://localhost:3306/jdbcStudy3 username=root4 password=XDP

 1 package me.gacl.utils; 2  3 import java.io.InputStream; 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 import java.sql.Statement; 9 import java.util.Properties;10 11 public class JdbcUtils {12 13     private static String driver = null;14     private static String url = null;15     private static String username = null;16     private static String password = null;17     18     static{19         try{20             //读取db.properties文件中的数据库连接信息21             InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");22             Properties prop = new Properties();23             prop.load(in);24             25             //获取数据库连接驱动26             driver = prop.getProperty("driver");27             //获取数据库连接URL地址28             url = prop.getProperty("url");29             //获取数据库连接用户名30             username = prop.getProperty("username");31             //获取数据库连接密码32             password = prop.getProperty("password");33             34             //加载数据库驱动35             Class.forName(driver);36             37         }catch (Exception e) {38             throw new ExceptionInInitializerError(e);39         }40     }41     42     /**43     * @Method: getConnection44     * @Description: 获取数据库连接对象45     * @Anthor:孤傲苍狼46     *47     * @return Connection数据库连接对象48     * @throws SQLException49     */ 50     public static Connection getConnection() throws SQLException{51         return DriverManager.getConnection(url, username,password);52     }53     54     /**55     * @Method: release56     * @Description: 释放资源，57     *     要释放的资源包括Connection数据库连接对象，负责执行SQL命令的Statement对象，存储查询结果的ResultSet对象58     * @Anthor:孤傲苍狼59     *60     * @param conn61     * @param st62     * @param rs63     */ 64     public static void release(Connection conn,Statement st,ResultSet rs){65         if(rs!=null){66             try{67                 //关闭存储查询结果的ResultSet对象68                 rs.close();69             }catch (Exception e) {70                 e.printStackTrace();71             }72             rs = null;73         }74         if(st!=null){75             try{76                 //关闭负责执行SQL命令的Statement对象77                 st.close();78             }catch (Exception e) {79                 e.printStackTrace();80             }81         }82         83         if(conn!=null){84             try{85                 //关闭Connection数据库连接对象86                 conn.close();87             }catch (Exception e) {88                 e.printStackTrace();89             }90         }91     }92 }

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

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

1 create database jdbcstudy;2 use jdbcstudy;3 create table testclob4 (5          id int primary key auto_increment,6          resume text7 );

  1 package me.gacl.demo;  2   3 import java.io.File;  4 import java.io.FileReader;  5 import java.io.FileWriter;  6 import java.io.Reader;  7 import java.sql.Connection;  8 import java.sql.PreparedStatement;  9 import java.sql.ResultSet; 10 import me.gacl.utils.JdbcUtils; 11 import org.junit.Test; 12  13 /** 14 * @ClassName: JdbcOperaClob 15 * @Description: 使用JDBC操作MySQL的大文本 16 * @author: 孤傲苍狼 17 * @date: 2014-9-19 下午10:10:04 18 * 19 */  20 public class JdbcOperaClob { 21  22     /** 23     * @Method: add 24     * @Description:向数据库中插入大文本数据 25     * @Anthor:孤傲苍狼 26     * 27     */  28     @Test 29     public void add(){ 30         Connection conn = null; 31         PreparedStatement st = null; 32         ResultSet rs = null; 33         Reader reader = null; 34         try{ 35             conn = JdbcUtils.getConnection(); 36             String sql = "insert into testclob(resume) values(?)"; 37             st = conn.prepareStatement(sql); 38             //这种方式获取的路径，其中的空格会被使用“%20”代替 39             String path = JdbcOperaClob.class.getClassLoader().getResource("data.txt").getPath(); 40             //将“%20”替换回空格 41             path = path.replaceAll("%20", " "); 42             File file = new File(path); 43             reader = new FileReader(file); 44             st.setCharacterStream(1, reader,(int) file.length()); 45             int num = st.executeUpdate(); 46             if(num>0){ 47                 System.out.println("插入成功！！"); 48             } 49             //关闭流 50             reader.close(); 51         }catch (Exception e) { 52             e.printStackTrace(); 53         }finally{ 54             JdbcUtils.release(conn, st, rs); 55         } 56     } 57      58     /** 59     * @Method: read 60     * @Description: 读取数据库中的大文本数据 61     * @Anthor:孤傲苍狼 62     * 63     */  64     @Test 65     public void read(){ 66         Connection conn = null; 67         PreparedStatement st = null; 68         ResultSet rs = null; 69         try{ 70             conn = JdbcUtils.getConnection(); 71             String sql = "select resume from testclob where id=2"; 72             st = conn.prepareStatement(sql); 73             rs = st.executeQuery(); 74              75             String contentStr =""; 76             String content = ""; 77             if(rs.next()){ 78                 //使用resultSet.getString("字段名")获取大文本数据的内容 79                 content = rs.getString("resume"); 80                 //使用resultSet.getCharacterStream("字段名")获取大文本数据的内容 81                 Reader reader = rs.getCharacterStream("resume"); 82                 char buffer[] = new char[1024]; 83                 int len = 0; 84                 FileWriter out = new FileWriter("D:\\1.txt"); 85                 while((len=reader.read(buffer))>0){ 86                     contentStr += new String(buffer); 87                     out.write(buffer, 0, len); 88                 } 89                 out.close(); 90                 reader.close(); 91             } 92             System.out.println(content); 93             System.out.println("-----------------------------------------------"); 94             System.out.println(contentStr); 95         }catch (Exception e) { 96             e.printStackTrace(); 97         }finally{ 98             JdbcUtils.release(conn, st, rs); 99         }100     }101 }

1 PreparedStatement. setBinaryStream(i, inputStream, length);

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

1 create table testblob2 (3      id int primary key auto_increment,4      image longblob5 );

 1 package me.gacl.demo; 2  3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.FileOutputStream; 6 import java.io.InputStream; 7 import java.sql.Connection; 8 import java.sql.PreparedStatement; 9 import java.sql.ResultSet;10 import me.gacl.utils.JdbcUtils;11 import org.junit.Test;12 13 /**14 * @ClassName: JdbcOperaClob15 * @Description: 使用JDBC操作MySQL的二进制数据(例如图像、声音、二进制文)16 * @author: 孤傲苍狼17 * @date: 2014-9-19 下午10:10:0418 *19 */ 20 public class JdbcOperaBlob {21 22     /**23     * @Method: add24     * @Description:向数据库中插入二进制数据25     * @Anthor:孤傲苍狼26     *27     */ 28     @Test29     public void add(){30         Connection conn = null;31         PreparedStatement st = null;32         ResultSet rs = null;33         try{34             conn = JdbcUtils.getConnection();35             String sql = "insert into testblob(image) values(?)";36             st = conn.prepareStatement(sql);37             //这种方式获取的路径，其中的空格会被使用“%20”代替38             String path = JdbcOperaBlob.class.getClassLoader().getResource("01.jpg").getPath();39             //将“%20”替换会空格40             path = path.replaceAll("%20", " ");41             File file = new File(path);42             FileInputStream fis = new FileInputStream(file);//生成的流43             st.setBinaryStream(1, fis,(int) file.length());44             int num = st.executeUpdate();45             if(num>0){46                 System.out.println("插入成功！！");47             }48             fis.close();49         }catch (Exception e) {50             e.printStackTrace();51         }finally{52             JdbcUtils.release(conn, st, rs);53         }54     }55     56     /**57     * @Method: read58     * @Description: 读取数据库中的二进制数据59     * @Anthor:孤傲苍狼60     *61     */ 62     @Test63     public void read() {64         Connection conn = null;65         PreparedStatement st = null;66         ResultSet rs = null;67         try {68             conn = JdbcUtils.getConnection();69             String sql = "select image from testblob where id=?";70             st = conn.prepareStatement(sql);71             st.setInt(1, 1);72             rs = st.executeQuery();73             if (rs.next()) {74                 //InputStream in = rs.getBlob("image").getBinaryStream();//这种方法也可以75                 InputStream in = rs.getBinaryStream("image");76                 int len = 0;77                 byte buffer[] = new byte[1024];78                 79                 FileOutputStream out = new FileOutputStream("D:\\1.jpg");80                 while ((len = in.read(buffer)) > 0) {81                     out.write(buffer, 0, len);82                 }83                 in.close();84                 out.close();85             }86         } catch (Exception e) {87             e.printStackTrace();88         } finally {89             JdbcUtils.release(conn, st, rs);90         }91     }92 }