JDBC中大文本类型的处理

深碍√TFBOYSˉ_ 2022-09-29 15:53 138阅读 0赞

**Oracle中大文本数据类型**  
Clob 长文本类型  
Blob 二进制类型

**MySQL数据库**  
Text 长文本类型  
Blob 二进制类型

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

-- 测试大数据类型
    CREATE TABLE test( id INT PRIMARY KEY AUTO_INCREMENT, content LONGTEXT, img LONGBLOB );

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

Text:

public class App_text {
    
        // 全局参数
        private Connection con;
        private Statement stmt;
        private PreparedStatement pstmt;
        private ResultSet rs;
    
    
        @Test
        // 1. 保存大文本数据类型 ( 写longtext)
        public void testSaveText() {
            String sql = "insert into test(content) values(?)";
            try {
                // 连接
                con = JdbcUtil.getConnection();
                // pstmt 对象
                pstmt = con.prepareStatement(sql);
                // 设置参数
                // 先获取文件路径
                String path = App_text.class.getResource("tips.txt").getPath();
                FileReader reader = new FileReader(new File(path));
                pstmt.setCharacterStream(1, reader);
    
                // 执行sql
                pstmt.executeUpdate();
    
                // 关闭
                reader.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.closeAll(con, pstmt, null);
            }
        }
    
        @Test
        // 2. 读取大文本数据类型 ( 读longtext)
        public void testGetAsText() {
            String sql = "select * from test;";
            try {
                // 连接
                con = JdbcUtil.getConnection();
                // pstmt 对象
                pstmt = con.prepareStatement(sql);
                // 读取
                rs = pstmt.executeQuery();
                if (rs.next()) {
                    // 获取长文本数据, 方式1:
                    //Reader r = rs.getCharacterStream("content");
    
                    // 获取长文本数据, 方式2:
                    System.out.print(rs.getString("content"));
                }
    
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.closeAll(con, pstmt, null);
            }
        }
    }

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

blob

public class App_blob {
    
        // 全局参数
        private Connection con;
        private Statement stmt;
        private PreparedStatement pstmt;
        private ResultSet rs;
    
    
        @Test
        // 1. 二进制数据类型 ( 写longblob)
        public void testSaveText() {
            String sql = "insert into test(img) values(?)";
            try {
                // 连接
                con = JdbcUtil.getConnection();
                // pstmt 对象
                pstmt = con.prepareStatement(sql);
                // 获取图片流
                InputStream in = App_text.class.getResourceAsStream("7.jpg");
                pstmt.setBinaryStream(1, in);
    
                // 执行保存图片
                pstmt.execute();
    
                // 关闭
                in.close();
    
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.closeAll(con, pstmt, null);
            }
        }
    
        @Test
        // 2. 读取大文本数据类型 ( 读longblob)
        public void testGetAsText() {
            String sql = "select img from test where id=2;";
            try {
                // 连接
                con = JdbcUtil.getConnection();
                // pstmt 对象
                pstmt = con.prepareStatement(sql);
                // 读取
                rs = pstmt.executeQuery();
                if (rs.next()) {
                    // 获取图片流
                    InputStream in = rs.getBinaryStream("img");
                    // 图片输出流
                    FileOutputStream out = new FileOutputStream(new File("c://1.jpg"));
                    int len = -1;
                    byte b[] = new byte[1024];
                    while ((len = in.read(b)) != -1) {
                        out.write(b, 0, len);
                    }
                    // 关闭
                    out.close();
                    in.close();
                }
    
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.closeAll(con, pstmt, null);
            }
        }
    }