jdbc操作clob和blob字段
来源:互联网 发布:去掉淘宝推广链接 编辑:程序博客网 时间:2024/05/03 22:42
菜鸟就是菜鸟啊,第一次听说clob和blob。某一任务需要程序中更新一个clob字段,我竟然还在用update语句,结果呢死活给我报语法错误,后来听lead说这是clob字段,让我查下怎么处理clob字段,这才知道妈的还有这玩意。也是从别人哪摘得的代码,甚是好用,贴之:
package lob;
import java.sql.*;
import java.io.*;
/**
* JDBC 读取MySQL lob字段测试
* File: TestLob4MySQL.java
* User: leizhimin
* Date: 2008-3-3 14:44:30
*/
public class TestLob4MySQL {
public static final String url = "jdbc:mysql://localhost/testdb";
public static final String username = "root";
public static final String password = "leizhimin";
public static final String driverClassName = "com.mysql.jdbc.Driver";
/**
* 数据库连接获取器
*
* @return 数据库连接
*/
public static Connection makeConnection() {
Connection conn = null;
try {
Class.forName(driverClassName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 测试数据库连接
*/
public static void testConnection() {
Connection conn = makeConnection();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM mysql.user");
while (rs.next()) {
String s1 = rs.getString(1);
System.out.println(s1);
}
rs.close();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 插入Lob字段
*/
public static void testInsertlob() {
Connection conn = makeConnection();
try {
conn.setAutoCommit(false);
File txtFile = new File("C://txt.txt");
File imgFile = new File("C://img.png");
int txt_len = (int) txtFile.length();
int img_len = (int) imgFile.length();
try {
InputStream fis1 = new FileInputStream(txtFile);
InputStream fis2 = new FileInputStream(imgFile);
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO T_LOB(NAME,TXT,IMG) VALUES('G',?,?)");
pstmt.setAsciiStream(1, fis1, txt_len);
pstmt.setBinaryStream(2, fis2, img_len);
pstmt.executeUpdate();
conn.commit();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 读取lob字段
*/
public static void testQueryLob() {
Connection conn = makeConnection();
try {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT TXT,IMG FROM T_LOB");
int i = 1;
while (rs.next()) {
Clob clob = rs.getClob("TXT");
Blob blob = rs.getBlob("IMG");
InputStream txtIs = rs.getAsciiStream("TXT");
InputStream imgIs = rs.getBinaryStream("IMG");
InputStreamReader txtIsr = new InputStreamReader(txtIs);
InputStreamReader imgIsr = new InputStreamReader(imgIs);
BufferedReader buff_txtIsr = new BufferedReader(txtIsr);
BufferedReader buff_imgIsr = new BufferedReader(imgIsr);
String line = null;
while (null != (line = buff_txtIsr.readLine())) {
System.out.println(line); //将其输出至屏幕,实际你可以按照需要处理
}
File fileOutput = new File("c://img_x" + i + ".png");
FileOutputStream fo = new FileOutputStream(fileOutput);
int c;
while ((c = imgIs.read()) != -1)
fo.write(c);
fo.close();
System.out.println("img " + i + " retrieved!");
i++;
}
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 读取lob字段
*/
public static void testQueryLob1() {
Connection conn = makeConnection();
try {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT TXT,IMG FROM T_LOB");
while (rs.next()) {
Clob clob = rs.getClob("TXT");
Blob blob = rs.getBlob("IMG");
InputStream txtIs = clob.getAsciiStream();
InputStream imgIs = blob.getBinaryStream();
InputStreamReader txtIsr = new InputStreamReader(txtIs);
InputStreamReader imgIsr = new InputStreamReader(imgIs);
BufferedReader buff_txtIsr = new BufferedReader(txtIsr);
BufferedReader buff_imgIsr = new BufferedReader(imgIsr);
String line = null;
while (null != (line = buff_txtIsr.readLine())) {
System.out.println(line); //将其输出至屏幕,实际你可以按照需要处理
}
}
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 删除lob字段
*/
public static void testDeleteLob() {
Connection conn = makeConnection();
try {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
int row = stmt.executeUpdate("DELETE FROM T_LOB");
conn.commit();
System.out.println("删除 " + row + " 行数据!");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 读取lob字段
*/
public static void testUpdateLob() {
Connection conn = makeConnection();
try {
String in_str="HAHAHAHAHAHA!!!";
File in_file=new File("c://img_haha.png");
InputStream txt_is = string2InputStream(in_str);
InputStream img_is =new FileInputStream(in_file);
conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement("UPDATE T_LOB SET TXT=?, IMG=? WHERE NAME='G'");
pstmt.setAsciiStream(1,txt_is,in_str.getBytes().length);
pstmt.setBinaryStream(2,img_is,(int)in_file.length());
int row = pstmt.executeUpdate();
conn.commit();
txt_is.close();
img_is.close();
// System.out.println("更新 " + row + " 行数据!");
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} catch (IOException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String args[]) {
// testInsertlob();
// testQueryLob();
// testQueryLob1();
// testDeleteLob();
testUpdateLob();
}
public static InputStream string2InputStream(String str) {
if (str == null) return null;
return new ByteArrayInputStream(str.getBytes());
}
public static String inputStream2String(InputStream is) {
StringBuffer sb = new StringBuffer();
BufferedReader br = new BufferedReader(new InputStreamReader(is));
String inputLine;
try {
while ((inputLine = br.readLine()) != null) {
sb.append(inputLine).append("/n");
}
} catch (IOException e) {
e.printStackTrace();
}
return sb.toString();
}
}
import java.sql.*;
import java.io.*;
/**
* JDBC 读取MySQL lob字段测试
* File: TestLob4MySQL.java
* User: leizhimin
* Date: 2008-3-3 14:44:30
*/
public class TestLob4MySQL {
public static final String url = "jdbc:mysql://localhost/testdb";
public static final String username = "root";
public static final String password = "leizhimin";
public static final String driverClassName = "com.mysql.jdbc.Driver";
/**
* 数据库连接获取器
*
* @return 数据库连接
*/
public static Connection makeConnection() {
Connection conn = null;
try {
Class.forName(driverClassName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 测试数据库连接
*/
public static void testConnection() {
Connection conn = makeConnection();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM mysql.user");
while (rs.next()) {
String s1 = rs.getString(1);
System.out.println(s1);
}
rs.close();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 插入Lob字段
*/
public static void testInsertlob() {
Connection conn = makeConnection();
try {
conn.setAutoCommit(false);
File txtFile = new File("C://txt.txt");
File imgFile = new File("C://img.png");
int txt_len = (int) txtFile.length();
int img_len = (int) imgFile.length();
try {
InputStream fis1 = new FileInputStream(txtFile);
InputStream fis2 = new FileInputStream(imgFile);
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO T_LOB(NAME,TXT,IMG) VALUES('G',?,?)");
pstmt.setAsciiStream(1, fis1, txt_len);
pstmt.setBinaryStream(2, fis2, img_len);
pstmt.executeUpdate();
conn.commit();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 读取lob字段
*/
public static void testQueryLob() {
Connection conn = makeConnection();
try {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT TXT,IMG FROM T_LOB");
int i = 1;
while (rs.next()) {
Clob clob = rs.getClob("TXT");
Blob blob = rs.getBlob("IMG");
InputStream txtIs = rs.getAsciiStream("TXT");
InputStream imgIs = rs.getBinaryStream("IMG");
InputStreamReader txtIsr = new InputStreamReader(txtIs);
InputStreamReader imgIsr = new InputStreamReader(imgIs);
BufferedReader buff_txtIsr = new BufferedReader(txtIsr);
BufferedReader buff_imgIsr = new BufferedReader(imgIsr);
String line = null;
while (null != (line = buff_txtIsr.readLine())) {
System.out.println(line); //将其输出至屏幕,实际你可以按照需要处理
}
File fileOutput = new File("c://img_x" + i + ".png");
FileOutputStream fo = new FileOutputStream(fileOutput);
int c;
while ((c = imgIs.read()) != -1)
fo.write(c);
fo.close();
System.out.println("img " + i + " retrieved!");
i++;
}
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 读取lob字段
*/
public static void testQueryLob1() {
Connection conn = makeConnection();
try {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT TXT,IMG FROM T_LOB");
while (rs.next()) {
Clob clob = rs.getClob("TXT");
Blob blob = rs.getBlob("IMG");
InputStream txtIs = clob.getAsciiStream();
InputStream imgIs = blob.getBinaryStream();
InputStreamReader txtIsr = new InputStreamReader(txtIs);
InputStreamReader imgIsr = new InputStreamReader(imgIs);
BufferedReader buff_txtIsr = new BufferedReader(txtIsr);
BufferedReader buff_imgIsr = new BufferedReader(imgIsr);
String line = null;
while (null != (line = buff_txtIsr.readLine())) {
System.out.println(line); //将其输出至屏幕,实际你可以按照需要处理
}
}
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 删除lob字段
*/
public static void testDeleteLob() {
Connection conn = makeConnection();
try {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
int row = stmt.executeUpdate("DELETE FROM T_LOB");
conn.commit();
System.out.println("删除 " + row + " 行数据!");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 读取lob字段
*/
public static void testUpdateLob() {
Connection conn = makeConnection();
try {
String in_str="HAHAHAHAHAHA!!!";
File in_file=new File("c://img_haha.png");
InputStream txt_is = string2InputStream(in_str);
InputStream img_is =new FileInputStream(in_file);
conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement("UPDATE T_LOB SET TXT=?, IMG=? WHERE NAME='G'");
pstmt.setAsciiStream(1,txt_is,in_str.getBytes().length);
pstmt.setBinaryStream(2,img_is,(int)in_file.length());
int row = pstmt.executeUpdate();
conn.commit();
txt_is.close();
img_is.close();
// System.out.println("更新 " + row + " 行数据!");
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} catch (IOException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String args[]) {
// testInsertlob();
// testQueryLob();
// testQueryLob1();
// testDeleteLob();
testUpdateLob();
}
public static InputStream string2InputStream(String str) {
if (str == null) return null;
return new ByteArrayInputStream(str.getBytes());
}
public static String inputStream2String(InputStream is) {
StringBuffer sb = new StringBuffer();
BufferedReader br = new BufferedReader(new InputStreamReader(is));
String inputLine;
try {
while ((inputLine = br.readLine()) != null) {
sb.append(inputLine).append("/n");
}
} catch (IOException e) {
e.printStackTrace();
}
return sb.toString();
}
}
- jdbc操作clob和blob字段
- jdbc操作clob和blob字段
- JDBC操作Clob,Blob字段
- jdbc操作Blob和Clob字段与字符串的转换
- jdbc操作blob,clob
- JDBC CLOB\BLOB操作
- jdbc操作blob,clob
- JDBC操作大数据CLOB和BLOB
- jdbc hibernate ibatis 操作Blob 和Clob类型字段(不断更新)
- 通过JDBC 操作 ORACLE BLOB,CLOB字段类型
- JDBC操作DB2 Clob、Blob字段的Bug探究
- 使用JDBC操作基于Oracle的CLOB,BLOB字段类型
- (转)通过JDBC 操作 ORACLE BLOB,CLOB字段类型
- ibatis操作oracle的clob字段和blob字段
- 通过JDBC和Hibernate对Clob和Blob的操作
- 通过JDBC和Hibernate对Clob和Blob的操作
- Oracle中BLOB和CLOB字段的操作
- 利用JDBC操作Oracle CLOB和BLOB类型数据
- 最简单的MFC入门教程---WinMain函数基础篇
- 查看Oracle当前的连接数
- 判断是否IE浏览器的最短js代码
- alcap
- Connect By –From Lily examples
- jdbc操作clob和blob字段
- RTX实时平台介绍(2) – 说说Sleep(1)
- C#中的高精度计时方法(纳秒级别计时)
- 什么是RAID?
- Delphi中自定义类和接口
- SQL 错误代码[MS Windows Error Messages
- 【转】SIP Servlet 概述及范例
- 数据库优化--索引
- 工作簿(Workbook)基本操作应用示例