`
fireye83
  • 浏览: 133830 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

MYSQ数据库CLOB、BLOB存取

    博客分类：
  • java
阅读更多

文本大字段类型（CLOB）

 

------------------------------------------------------------------------------------------------------------------------

>>>>>>>>>> 数据库脚本 <<<<<<<<<<

 

[sql] view plaincopyprint?
  1. CREATE TABLE `clob_test` (  
  2. `id`  integer NOT NULL AUTO_INCREMENT ,  
  3. `txt`  text NOT NULL ,  
  4. PRIMARY KEY (`id`)  
  5. )  
  6. ;  
CREATE TABLE `clob_test` (
`id`  integer NOT NULL AUTO_INCREMENT ,
`txt`  text NOT NULL ,
PRIMARY KEY (`id`)
)
;


>>>>>>>>>> ClobTest.java <<<<<<<<<<

 

[java] view plaincopyprint?
  1. package com.test;  
  2.   
  3. import java.io.BufferedReader;  
  4. import java.io.BufferedWriter;  
  5. import java.io.File;  
  6. import java.io.FileReader;  
  7. import java.io.FileWriter;  
  8. import java.io.IOException;  
  9. import java.io.Reader;  
  10. import java.io.Writer;  
  11. import java.sql.Clob;  
  12. import java.sql.Connection;  
  13. import java.sql.PreparedStatement;  
  14. import java.sql.ResultSet;  
  15. import java.sql.SQLException;  
  16. import java.sql.Statement;  
  17.   
  18. public class ClobTest {  
  19.   
  20.     public static void main(String[] args) throws SQLException, IOException {  
  21.         read();  
  22.   
  23.         System.out.println("--- End ---");  
  24.     }  
  25.   
  26.     // 增加   
  27.     static void create() throws SQLException, IOException {  
  28.   
  29.         Connection conn = null;  
  30.         PreparedStatement pstmt = null;  
  31.         String sql = "insert into clob_test (txt) values (?)";  
  32.   
  33.         try {  
  34.             conn = JdbcUtils.getConnection();  
  35.             pstmt = conn.prepareStatement(sql);  
  36.   
  37.             File file = new File("src/com/test/JdbcUtils.java");  
  38.             Reader reader = new BufferedReader(new FileReader(file));  
  39.   
  40.             pstmt.setCharacterStream(1, reader, file.length());  
  41.             pstmt.executeUpdate();  
  42.   
  43.             reader.close();  
  44.         } finally {  
  45.             JdbcUtils.free(null, pstmt, conn);  
  46.         }  
  47.     }  
  48.   
  49.     // 查询   
  50.     static void read() throws SQLException, IOException {  
  51.         Connection conn = null;  
  52.         Statement stmt = null;  
  53.         ResultSet rs = null;  
  54.         String sql = "select txt from clob_test";  
  55.         try {  
  56.             conn = JdbcUtils.getConnection();  
  57.             stmt = conn.createStatement();  
  58.             rs = stmt.executeQuery(sql);  
  59.             while (rs.next()) {  
  60.                 Clob clob = rs.getClob(1);  
  61.   
  62.                 Reader reader = clob.getCharacterStream();  
  63.   
  64.                 File file = new File("JdbcUtils.java");  
  65.                 Writer writer = new BufferedWriter(new FileWriter(file));  
  66.   
  67.                 char[] buff = new char[1024];  
  68.   
  69.                 for (int i = 0; (i = reader.read(buff)) > 0;) {  
  70.                     writer.write(buff, 0, i);  
  71.                 }  
  72.   
  73.                 writer.flush();  
  74.                 writer.close();  
  75.                 reader.close();  
  76.   
  77.             }  
  78.         } finally {  
  79.             JdbcUtils.free(rs, stmt, conn);  
  80.         }  
  81.     }  
  82.   
  83. }  
package com.test;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ClobTest {

	public static void main(String[] args) throws SQLException, IOException {
		read();

		System.out.println("--- End ---");
	}

	// 增加
	static void create() throws SQLException, IOException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "insert into clob_test (txt) values (?)";

		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);

			File file = new File("src/com/test/JdbcUtils.java");
			Reader reader = new BufferedReader(new FileReader(file));

			pstmt.setCharacterStream(1, reader, file.length());
			pstmt.executeUpdate();

			reader.close();
		} finally {
			JdbcUtils.free(null, pstmt, conn);
		}
	}

	// 查询
	static void read() throws SQLException, IOException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select txt from clob_test";
		try {
			conn = JdbcUtils.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Clob clob = rs.getClob(1);

				Reader reader = clob.getCharacterStream();

				File file = new File("JdbcUtils.java");
				Writer writer = new BufferedWriter(new FileWriter(file));

				char[] buff = new char[1024];

				for (int i = 0; (i = reader.read(buff)) > 0;) {
					writer.write(buff, 0, i);
				}

				writer.flush();
				writer.close();
				reader.close();

			}
		} finally {
			JdbcUtils.free(rs, stmt, conn);
		}
	}

}

 

 

二进制大字段类型（BLOB）

 

------------------------------------------------------------------------------------------------------------------------

 

>>>>>>>>>> 数据库脚本 <<<<<<<<<<

[sql] view plaincopyprint?
  1. CREATE TABLE `blob_test` (  
  2. `id`  integer NOT NULL AUTO_INCREMENT ,  
  3. `pic`  blob NOT NULL ,  
  4. PRIMARY KEY (`id`)  
  5. )  
  6. ;  
CREATE TABLE `blob_test` (
`id`  integer NOT NULL AUTO_INCREMENT ,
`pic`  blob NOT NULL ,
PRIMARY KEY (`id`)
)
;

 

[java] view plaincopyprint?
  1. package com.test;  
  2.   
  3. import java.io.BufferedInputStream;  
  4. import java.io.BufferedOutputStream;  
  5. import java.io.File;  
  6. import java.io.FileInputStream;  
  7. import java.io.FileOutputStream;  
  8. import java.io.IOException;  
  9. import java.io.InputStream;  
  10. import java.io.OutputStream;  
  11. import java.sql.Blob;  
  12. import java.sql.Connection;  
  13. import java.sql.PreparedStatement;  
  14. import java.sql.ResultSet;  
  15. import java.sql.SQLException;  
  16. import java.sql.Statement;  
  17.   
  18. public class BlobTest {  
  19.   
  20.     public static void main(String[] args) throws SQLException, IOException {  
  21.         read();  
  22.   
  23.         System.out.println("--- End ---");  
  24.     }  
  25.   
  26.     // 增加   
  27.     static void create() throws SQLException, IOException {  
  28.   
  29.         Connection conn = null;  
  30.         PreparedStatement pstmt = null;  
  31.         String sql = "insert into blob_test (pic) values (?)";  
  32.   
  33.         try {  
  34.             conn = JdbcUtils.getConnection();  
  35.             pstmt = conn.prepareStatement(sql);  
  36.   
  37.             File file = new File("level.gif");  
  38.             InputStream is = new BufferedInputStream(new FileInputStream(file));  
  39.   
  40.             pstmt.setBinaryStream(1, is, file.length());  
  41.             pstmt.executeUpdate();  
  42.   
  43.             is.close();  
  44.         } finally {  
  45.             JdbcUtils.free(null, pstmt, conn);  
  46.         }  
  47.     }  
  48.   
  49.     // 查询   
  50.     static void read() throws SQLException, IOException {  
  51.         Connection conn = null;  
  52.         Statement stmt = null;  
  53.         ResultSet rs = null;  
  54.         String sql = "select pic from blob_test";  
  55.         try {  
  56.             conn = JdbcUtils.getConnection();  
  57.             stmt = conn.createStatement();  
  58.             rs = stmt.executeQuery(sql);  
  59.             while (rs.next()) {  
  60.                 Blob blob = rs.getBlob(1);  
  61.   
  62.                 InputStream is = blob.getBinaryStream();  
  63.   
  64.                 File file = new File("logo.gif");  
  65.                 OutputStream os = new BufferedOutputStream(  
  66.                         new FileOutputStream(file));  
  67.   
  68.                 byte[] buff = new byte[1024];  
  69.   
  70.                 for (int i = 0; (i = is.read(buff)) > 0;) {  
  71.                     os.write(buff, 0, i);  
  72.                 }  
  73.   
  74.                 os.flush();  
  75.                 os.close();  
  76.                 is.close();  
  77.   
  78.             }  
  79.         } finally {  
  80.             JdbcUtils.free(rs, stmt, conn);  
  81.         }  
  82.     }  
  83.   
  84. }  
package com.test;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BlobTest {

	public static void main(String[] args) throws SQLException, IOException {
		read();

		System.out.println("--- End ---");
	}

	// 增加
	static void create() throws SQLException, IOException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "insert into blob_test (pic) values (?)";

		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);

			File file = new File("level.gif");
			InputStream is = new BufferedInputStream(new FileInputStream(file));

			pstmt.setBinaryStream(1, is, file.length());
			pstmt.executeUpdate();

			is.close();
		} finally {
			JdbcUtils.free(null, pstmt, conn);
		}
	}

	// 查询
	static void read() throws SQLException, IOException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select pic from blob_test";
		try {
			conn = JdbcUtils.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Blob blob = rs.getBlob(1);

				InputStream is = blob.getBinaryStream();

				File file = new File("logo.gif");
				OutputStream os = new BufferedOutputStream(
						new FileOutputStream(file));

				byte[] buff = new byte[1024];

				for (int i = 0; (i = is.read(buff)) > 0;) {
					os.write(buff, 0, i);
				}

				os.flush();
				os.close();
				is.close();

			}
		} finally {
			JdbcUtils.free(rs, stmt, conn);
		}
	}

}

 

数据库工具类：

------------------------------------------------------------------------------------------------------------------------

 

>>>>>>>>>> JdbcUtils.java <<<<<<<<<<

[java] view plaincopyprint?
  1. package com.test;  
  2.   
  3. import java.sql.Connection;  
  4. import java.sql.DriverManager;  
  5. import java.sql.ResultSet;  
  6. import java.sql.SQLException;  
  7. import java.sql.Statement;  
  8.   
  9. /** 
  10.  * 数据库工具类 
  11.  *  
  12.  * @author MY 
  13.  * @version 1.0, 2011-03-03 
  14.  *  
  15.  */  
  16. public final class JdbcUtils {  
  17.   
  18.     private static String url = "jdbc:mysql:///jdbc";  
  19.     private static String username = "root";  
  20.     private static String password = "";  
  21.   
  22.     private JdbcUtils() {  
  23.   
  24.     }  
  25.   
  26.     static {  
  27.         try {  
  28.             // 1、注册驱动   
  29.             Class.forName("com.mysql.jdbc.Driver");  
  30.         } catch (ClassNotFoundException e) {  
  31.             throw new ExceptionInInitializerError(e);  
  32.         }  
  33.     }  
  34.   
  35.     /** 
  36.      * 得到数据库连接 
  37.      *  
  38.      * @return 
  39.      * @throws SQLException 
  40.      */  
  41.     public static Connection getConnection() throws SQLException {  
  42.         return DriverManager.getConnection(url, username, password);  
  43.     }  
  44.   
  45.     /** 
  46.      * 关闭数据库资源 
  47.      *  
  48.      * @param rs 
  49.      * @param stmt 
  50.      * @param conn 
  51.      */  
  52.     public static void free(ResultSet rs, Statement stmt, Connection conn) {  
  53.         try {  
  54.             if (rs != null) {  
  55.                 rs.close();  
  56.                 rs = null;  
  57.             }  
  58.         } catch (SQLException e) {  
  59.             System.out.println("ResultSet关闭失败：" + e.getMessage());  
  60.         } finally {  
  61.             try {  
  62.                 if (stmt != null) {  
  63.                     stmt.close();  
  64.                     stmt = null;  
  65.                 }  
  66.             } catch (SQLException e) {  
  67.                 System.out.println("Statement关闭失败：" + e.getMessage());  
  68.             } finally {  
  69.                 try {  
  70.                     if (conn != null) {  
  71.                         conn.close();  
  72.                         conn = null;  
  73.                     }  
  74.                 } catch (SQLException e) {  
  75.                     System.out.println("Connection关闭失败：" + e.getMessage());  
  76.                 }  
  77.             }  
  78.         }  
  79.     }  
  80.   
  81. }  
package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 数据库工具类
 * 
 * @author MY
 * @version 1.0, 2011-03-03
 * 
 */
public final class JdbcUtils {

	private static String url = "jdbc:mysql:///jdbc";
	private static String username = "root";
	private static String password = "";

	private JdbcUtils() {

	}

	static {
		try {
			// 1、注册驱动
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	/**
	 * 得到数据库连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, username, password);
	}

	/**
	 * 关闭数据库资源
	 * 
	 * @param rs
	 * @param stmt
	 * @param conn
	 */
	public static void free(ResultSet rs, Statement stmt, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			System.out.println("ResultSet关闭失败：" + e.getMessage());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
			} catch (SQLException e) {
				System.out.println("Statement关闭失败：" + e.getMessage());
			} finally {
				try {
					if (conn != null) {
						conn.close();
						conn = null;
					}
				} catch (SQLException e) {
					System.out.println("Connection关闭失败：" + e.getMessage());
				}
			}
		}
	}

}


>>>>>>>>>> （单例模式）JdbcUtilsSing.java <<<<<<<<<<

[java] view plaincopyprint?
  1. package com.test;  
  2.   
  3. import java.sql.Connection;  
  4. import java.sql.DriverManager;  
  5. import java.sql.ResultSet;  
  6. import java.sql.SQLException;  
  7. import java.sql.Statement;  
  8.   
  9. /** 
  10.  * 数据库工具类<br /> 
  11.  * 单例模式 
  12.  *  
  13.  * @author MY 
  14.  * @version 1.0, 2011-03-03 
  15.  *  
  16.  */  
  17. public final class JdbcUtilsSing {  
  18.   
  19.     private String url = "jdbc:mysql:///jdbc";  
  20.     private String username = "root";  
  21.     private String password = "";  
  22.   
  23.     private static JdbcUtilsSing instance = null;  
  24.   
  25.     private JdbcUtilsSing() {  
  26.   
  27.     }  
  28.   
  29.     public static JdbcUtilsSing getInstance() {  
  30.         if (instance == null) {  
  31.             synchronized (JdbcUtilsSing.class) {  
  32.                 if (instance == null) {  
  33.                     instance = new JdbcUtilsSing();  
  34.                 }  
  35.             }  
  36.         }  
  37.         return instance;  
  38.     }  
  39.   
  40.     static {  
  41.         try {  
  42.             // 1、注册驱动   
  43.             Class.forName("com.mysql.jdbc.Driver");  
  44.         } catch (ClassNotFoundException e) {  
  45.             throw new ExceptionInInitializerError(e);  
  46.         }  
  47.     }  
  48.   
  49.     /** 
  50.      * 得到数据库连接 
  51.      *  
  52.      * @return 
  53.      * @throws SQLException 
  54.      */  
  55.     public Connection getConnection() throws SQLException {  
  56.         return DriverManager.getConnection(url, username, password);  
  57.     }  
  58.   
  59.     /** 
  60.      * 关闭数据库资源 
  61.      *  
  62.      * @param rs 
  63.      * @param stmt 
  64.      * @param conn 
  65.      */  
  66.     public void free(ResultSet rs, Statement stmt, Connection conn) {  
  67.         try {  
  68.             if (rs != null) {  
  69.                 rs.close();  
  70.                 rs = null;  
  71.             }  
  72.         } catch (SQLException e) {  
  73.             System.out.println("ResultSet关闭失败：" + e.getMessage());  
  74.         } finally {  
  75.             try {  
  76.                 if (stmt != null) {  
  77.                     stmt.close();  
  78.                     stmt = null;  
  79.                 }  
  80.             } catch (SQLException e) {  
  81.                 System.out.println("Statement关闭失败：" + e.getMessage());  
  82.             } finally {  
  83.                 try {  
  84.                     if (conn != null) {  
  85.                         conn.close();  
  86.                         conn = null;  
  87.                     }  
  88.                 } catch (SQLException e) {  
  89.                     System.out.println("Connection关闭失败：" + e.getMessage());  
  90.                 }  
  91.             }  
  92.         }  
  93.     }  
  94.   
  95. }  

http://blog.csdn.net/prstaxy/article/details/8206802

分享到：
| linux下安装Oracle 11g数据库(终极版)
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics