mysql lob字段_JDBC操作MySQL Lob字段记实

"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();

}

}