回顾JDBC之Oracle的CRUD以及分页细节

package com.wang.aliyun.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
/**  
 * @author captain  
 * @date 2019年1月2日  
 * 【注意：开发中很少用Statement接口】
 * 1.只需要传入完整的sql就可以操作了。
 * 2.使用它的子接口 PreparedStatement
 */
public class JDBCDemo2 {
  public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
  public static final String URL = "jdbc:oracle:thin:@localhost:1521:mldn";
  public static final String USER = "scoot";
  public static final String PASSWORD = "tiger";
  public static void main(String[] args) throws Exception{
    // 1.引入Oracle的驱动类
    Class.forName(DRIVER);
    // 2.创建连接
    Connection conn = DriverManager.getConnection(URL,USER,PASSWORD);
    // 3.更新
    // 如果使用拼接 的丽'热巴 会报错，单引号在数据库中是字符串
    //String name = "迪丽'热巴";
    //int age = 18;
    //Date birth = new Date();
    //String note = "美女我的";
    //String sql = " INSERT INTO member(id,name,age,birth,note) VALUES(myseq.nextval,?,?,?,?)";
    // 创建操作对象【注意：开发中很少用Statement接口】
    //PreparedStatement pstmt = conn.prepareStatement(sql);
    //pstmt.setString(1,name);
    //pstmt.setInt(2,age);
    //pstmt.setDate(3,new java.sql.Date(birth.getTime()));
    //pstmt.setString(4, note);
    //pstmt.executeUpdate();
    // 4.模糊查询
    //String column = "name"; // 定义模糊查询的列
    //String keyword = "热巴"; // 模糊词
    //String sql = " select id,name,age,birth,note from member where"+ column +" like ?";
    //PreparedStatement pstmt = conn.prepareStatement(sql);
    // 模糊查询
    //pstmt.setString(1, "%"+ keyword +"%");
    // 5. 根据id查询
    //String sql = " select id,name,age,birth,note from member where id =?";
    //PreparedStatement pstmt = conn.prepareStatement(sql);
    //pstmt.setInt(1,50);
    // 6.分页查询
    String column = "name"; // 定义模糊查询的列
    String keyword = "热巴"; // 模糊词
    int currentPage = 1; // 当前页
    int lineSize = 5; // 分页大小
    String sql = "SELECT * FROM("
        + " SELECT id,name,birth,note,ROWNUM rn "
        + " FROM member WHERE "+ column + " LIKE ? AND ROWNUM <=?) temp"
        +" WHERE temp.rn>?";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    // 模糊查询
    pstmt.setString(1, "%"+ keyword +"%");
    // 分页查询
    pstmt.setInt(2,currentPage * lineSize);
    pstmt.setInt(3,(currentPage-1) * lineSize);
    // 7.封装数据 ResultSet 可以封装查询的所有数据
    ResultSet rs = pstmt.executeQuery(sql);
    while (rs.next()) {
      int id = rs.getInt(1);
      String name1 = rs.getString(2); 
      Date birth1 = rs.getDate(3);
      System.out.println(id+name1+birth1);
    }
    conn.close();
  }
}