转自 http://xdnh.iteye.com/blog/939858
文本大字段类型(CLOB)
------------------------------------------------------------------------------------------------------------------------
>>>>>>>>>> 数据库脚本 <<<<<<<<<<
CREATE TABLE `clob_test` (
`id` integer NOT NULL AUTO_INCREMENT ,
`txt` text NOT NULL ,
PRIMARY KEY (`id`)
)
;
>>>>>>>>>> ClobTest.java <<<<<<<<<<
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)
------------------------------------------------------------------------------------------------------------------------
>>>>>>>>>> 数据库脚本 <<<<<<<<<<
CREATE TABLE `blob_test` (
`id` integer NOT NULL AUTO_INCREMENT ,
`pic` blob NOT NULL ,
PRIMARY KEY (`id`)
)
;
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 <<<<<<<<<<
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 <<<<<<<<<<
package com.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 数据库工具类<br />
* 单例模式
*
* @author MY
* @version 1.0, 2011-03-03
*
*/
public final class JdbcUtilsSing {
private String url = "jdbc:mysql:///jdbc";
private String username = "root";
private String password = "";
private static JdbcUtilsSing instance = null;
private JdbcUtilsSing() {
}
public static JdbcUtilsSing getInstance() {
if (instance == null) {
synchronized (JdbcUtilsSing.class) {
if (instance == null) {
instance = new JdbcUtilsSing();
}
}
}
return instance;
}
static {
try {
// 1、注册驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
/**
* 得到数据库连接
*
* @return
* @throws SQLException
*/
public Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
/**
* 关闭数据库资源
*
* @param rs
* @param stmt
* @param conn
*/
public 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());
}
}
}
}
}