JDBC

1. SQL Paging Statement: different database has different paging statment

　　MySQL: LIMIT M,N

　　　　M: the start index, starts from 0

　　　　N: the items amout of each query

　　Query 10 items in each page:

　　　　first page: SELECT * FROM customer LIMIT 0,10;

　　　　second page: SELECT * FROM customer LIMIT 10,10;

　　　　third page: SELECT * FROM customer LIMIT 20,10; 

　　　　x th page: SELECT * FROM customer LIMIT (n-1)*10,10;

　　　　general count: SELECT * FROM customer LIMIT (N-1)*M,M;

2. Paging query: to reduce the memory usage, query the record in batches

3. Import the class related to the paging: Page 

　　private List records; // the total records

　　private int currentPageNum; // the current page number

　　private int totalRecords; // the total records

　　private int pageSize; // the record amount in each page

　　private int totalPage; // the page amount

　　private int startIndex; // the start index of each page

The Page class:

package com.pp.web.beans;

import java.util.List;

public class Page {
    // check from DAO
    private List records; // the total records
    // check from the page
    private int currentPageNum; // the current page number
    // check from DAO
    private int totalRecords; // the total records
    // default number
    private int pageSize = 10; // the record amount in each page
    // calculate by total records
    private int totalPage; // the page amount
    // calculate by the current page
    private int startIndex; // the start index of each page

    public Page(int currentPageNum, int totalRecords) {
        this.currentPageNum = currentPageNum;
        this.totalRecords = totalRecords;
        // calculate the total page
        totalPage = totalRecords % pageSize == 0 ? totalRecords / pageSize
                : (totalRecords / pageSize + 1);
        startIndex = (currentPageNum - 1) * pageSize;
    }
    
    getter/setter...
}

 Batch processing: could improve the effeciency

package com.pp.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

import org.junit.Test;

import com.pp.util.JdbcUtil;

/*
 * Batch processing
 */
/*
 CREATE TABLE t3(
     id INT PRIMARY KEY,
     name VARCHAR(100)
 );
 */
public class BatchDemo {

    @Test
    public void test1(){
        Connection connection = null;
        Statement statement = null;
        try{
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            String sql1 = "INSERT INTO t3 VALUES(1,'aa1')";
            String sql2 = "INSERT INTO t3 VALUES(2,'aa2')";
            String sql3 = "DELETE FROM t3 WHERE id = 1"; 
            // There is a List<String> in the Statement object
            statement.addBatch(sql1);
            statement.addBatch(sql2);
            statement.addBatch(sql3);
            // execute the batch command
            statement.executeBatch();
            
        }catch(Exception e){
            e.printStackTrace();}
        finally{
            JdbcUtil.release(connection, statement, null);
        }
    }
    
    @Test
    public void test2(){
        Connection connection = null;
        PreparedStatement pstatement = null;
        
        try{
            connection = JdbcUtil.getConnection();
            pstatement = connection.prepareStatement("INSERT INTO t3 VALUES(?,?)");
            for(int i = 0; i<=100;i++){
                pstatement.setInt(1, i);
                pstatement.setString(2, "aa"+i);
                pstatement.addBatch();
            }    
            pstatement.executeBatch();
        }catch(Exception e){
            e.printStackTrace();}
        finally{
            JdbcUtil.release(connection, pstatement, null);
        }
    }
    
    @Test
    public void test3(){
        Connection connection = null;
        PreparedStatement pstatement = null;
        
        try{
            connection = JdbcUtil.getConnection();
            pstatement = connection.prepareStatement("INSERT INTO t3 VALUES(?,?)");
            for(int i = 0; i<=10001;i++){
                pstatement.setInt(1, i);
                pstatement.setString(2, "aa"+i);
                // if the data is too much, may lead to memory leak
                // so we can handle the data by batch
                pstatement.addBatch();
                if(i%100==0){
                    pstatement.executeBatch();
                    // clear the data in the list
                    pstatement.clearBatch();
                }
            }    
            pstatement.executeBatch();
        }catch(Exception e){
            e.printStackTrace();}
        finally{
            JdbcUtil.release(connection, pstatement, null);
        }
    }

}

page.jsp