Blob data type, database transaction

catalogue

Blob type field

Batch insert

Database transaction

Blob type field

Overview: in MySQL, Blob is a large binary object. It is a container that can store a large amount of data. It can accommodate data of different sizes.

Note: PreparedStatement must be used to insert Blob type data, because Blob type data cannot be spliced with string.

Introduction: there are four Blob types of MySQL (they are the same except for the maximum amount of information stored). In actual use, different Blob types are defined according to the size of the data to be stored.

be careful:

  • If the stored file is too large, the performance of the database will degrade
  • If the error xxx too large is reported after specifying the relevant Blob type, find the my.ini file in the mysql installation directory and add the following configuration parameters: max_allowed_packet=16M. At the same time, note: after modifying the my.ini file, you need to restart the mysql service

Code implementation: insert and query Blob type data

    //Insert a Blob type field into the data table customers
    @Test
    public void testInsert() throws Exception {
        //Get connection
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);

        //Fill placeholder
        ps.setObject(1, "Joker Xue");
        ps.setObject(2, "xuezq@qq.com");
        ps.setObject(3, "1983-7-17");
        //Manipulate variables of Blob type
        FileInputStream is = new FileInputStream(new File("x.jpg"));
        ps.setBlob(4, is);
        //implement
        ps.execute();
        JDBCUtils.closeResource(conn, ps);
    }

    //Query the Blob type field in the data table customers
    @Test
    public void testQuery() {
        Connection conn = null;
        PreparedStatement ps = null;
        InputStream is = null;
        FileOutputStream fos = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth,photo from customers where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 22);
            //implement
            rs = ps.executeQuery();
            if (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date birth = rs.getDate("birth");

                Customer cust = new Customer(id, name, email, birth);
                System.out.println(cust);

                //Download Blob type fields and save them locally as files
                Blob photo = rs.getBlob("photo");
                is = photo.getBinaryStream();
                fos = new FileOutputStream("xzq.jpg");
                byte[] buffer = new byte[1024];
                int len;
                while ((len = is.read(buffer)) != -1) {
                    fos.write(buffer, 0, len);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (is != null)
                    is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if (fos != null)
                    fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            JDBCUtils.closeResource(conn, ps, rs);
        }
    }

Batch insert

Overview: when you need to insert or update records in batches, you can use the batch update mechanism of Java, which allows multiple statements to be submitted to the database for batch processing at one time.

JDBC batch processing statements include three methods:

  1. addBatch(String): add SQL statements or parameters that need batch processing
  2. executeBatch(): executes batch processing statements
  3. clearBatch(): clear cached data

There are usually two cases of batch execution of SQL statements:

  • Batch processing of multiple SQL statements
  • Batch parameter transfer of an SQL statement

Code implementation: insert 20000 pieces of data into the data table

#Provide a goods table in the database. Create the following:
CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
    @Test
    public void testInsert4() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCUtils.getConnection();

            //Setting does not allow automatic submission of data
            conn.setAutoCommit(false);

            String sql = "insert into goods(name)values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 20000; i++) {
                ps.setObject(1, "name_" + i);

                //1. Save sql
                ps.addBatch();
                if (i % 500 == 0) {
                    //2. Implementation
                    ps.executeBatch();
                    //3. Empty the batch
                    ps.clearBatch();
                }
            }
            //Submit data
            conn.commit();

            long end = System.currentTimeMillis();
            System.out.println("Execution time:" + (end - start)); //Execution time: 703
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
    }

Database transaction

Note: only the transaction processing in JDBC is introduced here. For the basic introduction of database transactions, please refer to the previous articles of the blogger.

Overview: a group of logical operation units that transform data from one state to another. (a group of logical operation units, one or more DML operations)

characteristic:

  • Once the data is submitted, it cannot be rolled back
  • When will the data be submitted?
    • Once the DDL operation is executed, it will be submitted automatically. set autocommit = false will invalidate the DDL operation
    • By default, once the DML is executed, it will be submitted automatically. set sutocommit = false to cancel the automatic submission of DML operations
    • Close the database connection and the data will be submitted automatically
    • When a connection object is created, the transaction is automatically committed by default

In the JDBC program, multiple SQL statements are executed as one transaction:

  • Call setAutoCommit(false) of the Connection object to cancel the auto commit transaction
  • After all SQL statements have been successfully executed, call commit() to commit the transaction.
  • When an exception occurs, the rollback() method is called to roll back the transaction

Code implementation: user AA transfers 100 to user BB

    //**********Transfer operation considering database transactions****************
    @Test
    public void testUpdateWithTx() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            System.out.println(conn.getAutoCommit()); //true
            
            //1. Cancel automatic submission of data
            conn.setAutoCommit(false);

            String sql1 = "update user_table set balance = balance - 100 where user = ?";
            update(conn,sql1,"AA");
            //Analog network exception
            //System.out.println(10/0);
            String sql2 = "update user_table set balance = balance + 100 where user = ?";
            update(conn,sql2,"BB");
            System.out.println("Transfer succeeded!");

            //2. Submission of data
            conn.commit();
        }catch (Exception e){
            e.printStackTrace();
            //3. Rollback data
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        finally {
            //Modify it to automatically submit data, mainly for the use of database connection pool
            try {
                conn.setAutoCommit(true);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            JDBCUtils.closeResource(conn,null);
        }
    }

    //General addition, deletion and modification operations - version 2.0 (considering previous transactions)
    public int update(Connection conn,String sql,Object ...args) {
        PreparedStatement ps = null;
        try {
            //1. Precompile the sql statement and return the PreparedStatement instance
            ps = conn.prepareStatement(sql);
            //2. Fill placeholder
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1,args[i]);
            }
            //3. Implementation
            return ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //4. Resource shutdown
            JDBCUtils.closeResource(null, ps);
        }
        return 0;
    }

Tags: Java

Posted on Tue, 05 Oct 2021 17:51:25 -0400 by umer