JDBC操作MySQL Lob字段记实51CTO博客 - 超凡娱乐

JDBC操作MySQL Lob字段记实51CTO博客

2019-01-04 21:32:04 | 作者: 宇熙 | 标签: 字段,操作,测验 | 浏览: 2051

JDBC操作MySQL Lob字段记实   尽管Java的耐久化结构数不胜数,但都离不开JDBC技能,JDBC在某些时分是其他结构难以替代的。也是java操作数据库最底子的技能。   上文写了JDBC操作DB2 Lob字段bug问题,为此,我还特意写了MySQL平台下的Lob字段操作,以便能得出更为精确的定论。   本文经过一个简略的Java类,就能增修改查MySQL的Lob字段。google一下,JDBC操作数据库Lob字段的完好代码一个也没找到。因而把这个测验代码也放在blog上,期望给正在用JDBC做MySQL开发的朋友们一点参阅。   环境: MySQL-5.0.45 mysql-connector-java-5.1.5.zip   测验的SQL脚本: CREATE TABLE t_lob (
  NAME varchar(24) DEFAULT NULL,
  TXT text,
  IMG blob
) ENGINE=InnoDB DEFAULT CHARSET=gbk;   测验代码: 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();
    }
}
  逐个运转各个测验办法,都没有问题。
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表超凡娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章