天天看點
傳回

JDBC【PreparedStatment、批處理、處理二進制、自動主鍵、調用存儲過程、函數】1.PreparedStatement對象2.批處理3.處理大文本和二進制資料4.擷取資料庫的自動主鍵列5.調用資料庫的存儲過程

1.PreparedStatement對象

PreparedStatement對象繼承Statement對象，它比Statement對象更強大，使用起來更簡單

  1. Statement對象編譯SQL語句時，如果SQL語句有變量，就需要使用分隔符來隔開，如果變量非常多，就會使SQL變得非常複雜。PreparedStatement可以使用占位符，簡化sql的編寫
  2. Statement會頻繁編譯SQL。PreparedStatement可對SQL進行預編譯，提高效率，預編譯的SQL存儲在PreparedStatement對象中
  3. PreparedStatement防止SQL注入。【Statement通過分隔符'++',編寫永等式，可以不需要密碼就進入資料庫】 
//模拟查詢id為2的資訊
        String id = "2";

        Connection connection = UtilsDemo.getConnection();

        String sql = "SELECT * FROM users WHERE id = ?";
        PreparedStatement preparedStatement = connection.preparedStatement(sql);

        //第一個參數表示第幾個占位符【也就是?号】，第二個參數表示值是多少
        preparedStatement.setString(1,id);

        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            System.out.println(resultSet.getString("name"));
        }

        //釋放資源
        UtilsDemo.release(connection, preparedStatement, resultSet);

2.批處理

當需要向資料庫發送一批SQL語句執行時，應避免向資料庫一條條發送執行，采用批處理以提升執行效率

批處理有兩種方式：

  1. Statement
  2. PreparedStatement

通過executeBath()方法批量處理執行SQL語句，傳回一個int[]數組，該數組代表各句SQL的傳回值

以下代碼是以Statement方式實作批處理

/*
        * Statement執行批處理
        *
        * 優點：
        *       可以向資料庫發送不同的SQL語句
        * 缺點：
        *       SQL沒有預編譯
        *       僅參數不同的SQL，需要重複寫多條SQL
        * */
        Connection connection = UtilsDemo.getConnection();

        Statement statement = connection.createStatement();
        String sql1 = "UPDATE users SET name='zhongfucheng' WHERE id='3'";
        String sql2 = "INSERT INTO users (id, name, password, email, birthday)" +
                " VALUES('5','nihao','123','[email protected]','1995-12-1')";

        //将sql添加到批處理
        statement.addBatch(sql1);
        statement.addBatch(sql2);

        //執行批處理
        statement.executeBatch();

        //清空批處理的sql
        statement.clearBatch();

        UtilsDemo.release(connection, statement, null);

以下方式以PreparedStatement方式實作批處理

/*
        * PreparedStatement批處理
        *   優點：
        *       SQL語句預編譯了
        *       對于同一種類型的SQL語句，不用編寫很多條
        *   缺點：
        *       不能發送不同類型的SQL語句
        *
        * */
        Connection connection = UtilsDemo.getConnection();

        String sql = "INSERT INTO test(id,name) VALUES (?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        for (int i = 1; i <= 205; i++) {
            preparedStatement.setInt(1, i);
            preparedStatement.setString(2, (i + "zhongfucheng"));

            //添加到批進行中
            preparedStatement.addBatch();

            if (i %2 ==100) {

                //執行批處理
                preparedStatement.executeBatch();

                //清空批處理【如果資料量太大，所有資料存入批處理，記憶體肯定溢出】
                preparedStatement.clearBatch();
            }

        }
        //不是所有的%2==100，剩下的再執行一次批處理
        preparedStatement.executeBatch();

        //再清空
        preparedStatement.clearBatch();

        UtilsDemo.release(connection, preparedStatement, null);

3.處理大文本和二進制資料

clob和blob

  • clob用于存儲大文本
  • blob用于存儲二進制資料

MYSQL

MySQL存儲大文本是用Test【代替clob】，Test又分為4類

  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

同理blob也有這4類

下面用JDBC連接配接MySQL資料庫去操作大文本資料和二進制資料

/*
*用JDBC操作MySQL資料庫去操作大文本資料
*
*setCharacterStream(int parameterIndex,java.io.Reader reader,long length)
*第二個參數接收的是一個流對象，因為大文本不應該用String來接收，String太大會導緻記憶體溢出
*第三個參數接收的是檔案的大小
*
* */
public class Demo5 {

    @Test
    public void add() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            String sql = "INSERT INTO test2 (bigTest) VALUES(?) ";
            preparedStatement = connection.prepareStatement(sql);

            //擷取到檔案的路徑
            String path = Demo5.class.getClassLoader().getResource("BigTest").getPath();
            File file = new File(path);
            FileReader fileReader = new FileReader(file);

            //第三個參數，由于測試的Mysql版本過低，是以隻能用int類型的。高版本的不需要進行強轉
            preparedStatement.setCharacterStream(1, fileReader, (int) file.length());

            if (preparedStatement.executeUpdate() > 0) {
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, null);
        }


    }

    /*
    * 讀取大文本資料，通過ResultSet中的getCharacterStream()擷取流對象資料
    * 
    * */
    @Test
    public void read() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "SELECT * FROM test2";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {

                Reader reader = resultSet.getCharacterStream("bigTest");

                FileWriter fileWriter = new FileWriter("d:\\abc.txt");
                char[] chars = new char[1024];
                int len = 0;
                while ((len = reader.read(chars)) != -1) {
                    fileWriter.write(chars, 0, len);
                    fileWriter.flush();
                }
                fileWriter.close();
                reader.close();

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, resultSet);
        }
        
    }


           
/*
* 使用JDBC連接配接MYsql資料庫操作二進制資料
* 如果我們要用資料庫存儲一個大視訊的時候，資料庫是存儲不到的。
* 需要設定max_allowed_packet，一般我們不使用資料庫去存儲一個視訊
* */
public class Demo6 {

    @Test
    public void add() {


        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;


        try {
            connection = JdbcUtils.getConnection();
            String sql = "INSERT INTO test3 (blobtest) VALUES(?)";
            preparedStatement = connection.prepareStatement(sql);

            //擷取檔案的路徑和檔案對象
            String path = Demo6.class.getClassLoader().getResource("1.wmv").getPath();
            File file = new File(path);

            //調用方法
            preparedStatement.setBinaryStream(1, new FileInputStream(path), (int)file.length());

            if (preparedStatement.executeUpdate() > 0) {

                System.out.println("添加成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, null);
        }

    }

    @Test
    public void read() {


        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;


        try {
            connection = JdbcUtils.getConnection();
            String sql = "SELECT * FROM test3";
            preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();


            //如果讀取到資料，就把資料寫到磁盤下
            if (resultSet.next()) {
                InputStream inputStream = resultSet.getBinaryStream("blobtest");
                FileOutputStream fileOutputStream = new FileOutputStream("d:\\aa.jpg");

                int len = 0;
                byte[] bytes = new byte[1024];
                while ((len = inputStream.read(bytes)) > 0) {

                    fileOutputStream.write(bytes, 0, len);

                }
                fileOutputStream.close();
                inputStream.close();

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, null);
        }

    }

Oracle

下面用JDBC連接配接Oracle資料庫去操作大文本資料和二進制資料

//使用JDBC連接配接Oracle資料庫操作二進制資料
    
/*
* 對于Oracle資料庫和Mysql資料庫是有所不同的。
* 1.Oracle定義了BLOB字段，但是這個字段不是真正地存儲二進制資料
* 2.向這個字段存一個BLOB指針，擷取到Oracle的BLOB對象,把二進制資料放到這個指針裡面,指針指向BLOB字段
* 3.需要事務支援
*
* */
public class Demo7 {
    @Test
    public void add() {


        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = UtilsDemo.getConnection();

            //開啟事務
            connection.setAutoCommit(false);

            //插入一個BLOB指針
            String sql = "insert into test4(id,image) values(?,empty_blob())";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            preparedStatement.executeUpdate();

            //把BLOB指針查詢出來,得到BLOB對象
            String sql2 = "select image from test4 where id= ? for update";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setInt(1, 1);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                //得到Blob對象--當成是Oracle的Blob,不是JDBC的,是以要強轉[導的是oracle.sql.BLOB包]
                BLOB  blob = (BLOB) resultSet.getBlob("image");

                //寫入二進制資料
                OutputStream outputStream = blob.getBinaryOutputStream();

                //擷取到讀取檔案讀入流
                InputStream inputStream = Demo7.class.getClassLoader().getResourceAsStream("01.jpg");

                int len=0;
                byte[] bytes = new byte[1024];
                while ((len = inputStream.read(bytes)) > 0) {

                    outputStream.write(bytes, 0, len);
                }
                outputStream.close();
                inputStream.close();
                connection.setAutoCommit(true);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            UtilsDemo.release(connection, preparedStatement, null);
        }

    }

    @Test
    public void find() {
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = UtilsDemo.getConnection();
            String sql = "SELECT * FROM test4 WHERE id=1";

            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {

                //擷取到BLOB對象
                BLOB blob = (BLOB) resultSet.getBlob("image");

                //将資料讀取到磁盤上
                InputStream inputStream = blob.getBinaryStream();
                FileOutputStream fileOutputStream = new FileOutputStream("d:\\zhongfucheng.jpg");
                int len=0;
                byte[] bytes = new byte[1024];

                while ((len = inputStream.read(bytes)) > 0) {

                    fileOutputStream.write(bytes, 0, len);
                }

                inputStream.close();
                fileOutputStream.close();

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            UtilsDemo.release(connection, preparedStatement, null);
        }
    }
}

對于JDBC連接配接Oracle資料庫操作CLOB資料,我就不再重複了,操作跟BLOB幾乎相同

4.擷取資料庫的自動主鍵列

為什麼要擷取資料庫的自動主鍵列資料?

應用場景:

有一張老師表，一張學生表。現在來了一個新的老師，學生要跟着新老師上課。

我首先要知道老師的id編号是多少，學生才能知道跟着哪個老師學習【學生外鍵參照老師主鍵】。

@Test
    public void test() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            String sql = "INSERT INTO test(name) VALUES(?)";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, "ouzicheng");

            if (preparedStatement.executeUpdate() > 0) {

                //擷取到自動主鍵列的值
                resultSet = preparedStatement.getGeneratedKeys();

                if (resultSet.next()) {
                    int id = resultSet.getInt(1);
                    System.out.println(id);
                }
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, null);
        }

5.調用資料庫的存儲過程

調用存儲過程的文法：

{call <procedure-name>[(<arg1>,<arg2>, ...)]}

調用函數的文法：

{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

如果是Output類型的，那麼在JDBC調用的時候是要注冊的。如下代碼所示：

/*
    jdbc調用存儲過程

    delimiter $$
        CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam varchar(255))
        BEGIN
            SELECT CONCAT('zyxw---', inputParam) into inOutParam;
        END $$
    delimiter ;
*/
//我們在JDBC調用存儲過程,就像在調用方法一樣
public class Demo9 {

    public static void main(String[] args) {
        Connection connection = null;
        CallableStatement callableStatement = null;

        try {
            connection = JdbcUtils.getConnection();
            
            callableStatement = connection.prepareCall("{call demoSp(?,?)}");

            callableStatement.setString(1, "nihaoa");
            
            //注冊第2個參數,類型是VARCHAR
            callableStatement.registerOutParameter(2, Types.VARCHAR);
            callableStatement.execute();
            
            //擷取傳出參數[擷取存儲過程裡的值]
            String result = callableStatement.getString(2);
            System.out.println(result);

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
                callableStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

參考資料：

----------------------------------------------------------------------------------過程

#修改mysql語句的結果符為//
mysql > delimiter //

#定義一個過程，擷取users表總記錄數，将10設定到變量count中
create procedure simpleproc(out count int)
begin
    select count(id) into count from users;
end
//

#修改mysql語句的結果符為;
mysql > delimiter ;

#調用過程，将結果覆給變量a，@是定義變量的符号
call simpleproc(@a);

#顯示變量a的值
select @a;

//以下是Java調用Mysql的過程
    String sql = "{call simpleproc(?)}";
    Connection conn = JdbcUtil.getConnection();
    CallableStatement cstmt = conn.prepareCall(sql);
    cstmt.registerOutParameter(1,Types.INTEGER);
    cstmt.execute();
    Integer count = cstmt.getInt(1);
    System.out.println("共有" + count + "人");

----------------------------------------------------------------------------------函數

#修改mysql語句的結果符為//
mysql > delimiter //

#定義一個函數，完成字元串拼接
create function hello( s char(20) ) returns char(50) 
return concat('hello，',s,'!');
//

#修改mysql語句的結果符為;
mysql > delimiter ;

#調用函數
select hello('world');

//以下是Java調用Mysql的函數
    String sql = "{? = call hello(?)}";
    Connection conn = JdbcUtil.getConnection();
    CallableStatement cstmt = conn.prepareCall(sql);
    cstmt.registerOutParameter(1,Types.VARCHAR);
    cstmt.setString(2,"zhaojun");
    cstmt.execute();
    String value = cstmt.getString(1);
    System.out.println(value);
    JdbcUtil.close(cstmt);
    JdbcUtil.close(conn);
如果文章有錯的地方歡迎指正，大家互相交流。習慣在微信看技術文章的同學，可以關注微信公衆号:Java3y

更多的文章可往：

文章的目錄導航
sql 存儲 關系型資料庫 mysql 資料庫 資料存儲通過 官方資料存儲 資料存儲列存儲 資料存儲支援 資料存儲版本
上一篇: JDBC【事務、中繼資料、改造JDBC工具類】1.事務2.中繼資料3.改造JDBC工具類
下一篇: 淺談CDN、SEO、XSS、CSRFCDNSEOXSSCSRF

繼續閱讀