MySQL_(Java)分页查询MySQL中的数据

 

 

  MySQL_(Java)使用JDBC向数据库发起查询请求  传送门

  MySQL_(Java)使用JDBC创建用户名和密码校验查询方法  传送门

  MySQL_(Java)使用preparestatement解决SQL注入的问题  传送门

 

  MySQL数据库中的数据,数据库名garysql,表名garytb,向数据库中添加多行数据  

 

  使用limit做数据查询:limit [位置偏移量,]行数

  位置偏移量是从,哪一行开始(行数从0开始),行数是指查询几行

  从第四行开始查询三行,用户ID从1开始

  

  如果要查询 第7页,每页8行
  起始和末尾行数
  0-7 第一页
  8-15 第二页
  16-23 第三页
  ...
  起始行数为 (页数-1)*8

 

   通过JDBC对MySQL中的数据用户名和密码进行分页查询,查询第四页,没页显示八行数据

 

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC01 {

    public static void main(String[] args) throws SQLException  {
        //查询第四页,每页显示八行数据
        selectUserByPage(4,8);
    }

    public static void selectAll() throws SQLException {
        //注册驱动    使用驱动连接数据库
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            
            //String url ="jdbc:mysql://localhost:3306/garysql";
            //指定编码查询数据库
            String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false";
            String user = "root";
            String password = "123456";
            //建立和数据库的连接
            con = DriverManager.getConnection(url,user,password);
            
            //数据库的增删改查
            stmt = con.createStatement();
            //返回一个结果集
            rs =stmt.executeQuery("select * from garytb");
            
            while(rs.next()) {
                //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3));
                System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password"));
            }
        
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if(rs!=null)
                rs.close();
            if(stmt!=null)
                stmt.close();
            if(con!=null)
                con.close();
        }
    }

    public static boolean  selectByUernamePassword(String username,String password) throws SQLException {
        Connection con=null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            
            String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false";
            con = DriverManager.getConnection(url,"root","123456");
            stmt =con.createStatement();
            String sql = "select * from garytb where username = '"+username+"' and password = '"+password+"'";
            //System.out.println(sql);
            rs = stmt.executeQuery(sql);
            
            if(rs.next()) {
                return true;
            }else {
                return false;
            }
                
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if(rs!=null)
                rs.close();
            if(stmt!=null)
                stmt.close();
            if(con!=null)
                con.close();
        }
        
        return false;
    }

    public static boolean selectByUP2(String username,String password) throws SQLException{
        Connection con=null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            
            String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false";
            con = DriverManager.getConnection(url,"root","123456");
            
            String sql = "select * from garytb where username = ? and password = ?";
            PreparedStatement pstmt = con.prepareStatement(sql);
            //添加参数
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            //进行查询
            rs = pstmt.executeQuery();
                
            if(rs.next()) {
                return true;
            }else {
                return false;
            }
                
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if(rs!=null)
                rs.close();
            if(stmt!=null)
                stmt.close();
            if(con!=null)
                con.close();
        }
        
        return false;
    }

    //pageNumber是页数,第几页,pageCount是每页显示多少个数据
    public static void selectUserByPage(int pageNumber,int pageCount) throws SQLException {
        //注册驱动    使用驱动连接数据库
                Connection con = null;
                PreparedStatement stmt = null;
                ResultSet rs = null;
                try {
                    Class.forName("com.mysql.jdbc.Driver");
                    
                    //String url ="jdbc:mysql://localhost:3306/garysql";
                    //指定编码查询数据库
                    String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false";
                    String user = "root";
                    String password = "123456";
                    //建立和数据库的连接
                    con = DriverManager.getConnection(url,user,password);
                    
                    stmt = con.prepareStatement("select * from garytb limit ?,?");
                    stmt.setInt(1, (pageNumber-1)*pageCount );
                    stmt.setInt(2, pageCount);
                    
                    rs = stmt.executeQuery();
                    
                    while(rs.next()) {
                        //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3));
                        System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password"));
                    }
                
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }finally {
                    if(rs!=null)
                        rs.close();
                    if(stmt!=null)
                        stmt.close();
                    if(con!=null)
                        con.close();
                }
    }
}
JDBC01.java

 

  使用PreparedStatement防止SQL注入

                    stmt = con.prepareStatement("select * from garytb limit ?,?");
                    stmt.setInt(1, (pageNumber-1)*pageCount );
                    stmt.setInt(2, pageCount);
                    
                    rs = stmt.executeQuery();
                    
                    while(rs.next()) {
                        //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3));
                        System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password"));
                    }

 

//pageNumber是页数,第几页,pageCount是每页显示多少个数据
    public static void selectUserByPage(int pageNumber,int pageCount) throws SQLException {
        //注册驱动    使用驱动连接数据库
                Connection con = null;
                PreparedStatement stmt = null;
                ResultSet rs = null;
                try {
                    Class.forName("com.mysql.jdbc.Driver");
                    
                    //String url ="jdbc:mysql://localhost:3306/garysql";
                    //指定编码查询数据库
                    String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false";
                    String user = "root";
                    String password = "123456";
                    //建立和数据库的连接
                    con = DriverManager.getConnection(url,user,password);
                    
                    stmt = con.prepareStatement("select * from garytb limit ?,?");
                    stmt.setInt(1, (pageNumber-1)*pageCount );
                    stmt.setInt(2, pageCount);
                    
                    rs = stmt.executeQuery();
                    
                    while(rs.next()) {
                        //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3));
                        System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password"));
                    }
                
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }finally {
                    if(rs!=null)
                        rs.close();
                    if(stmt!=null)
                        stmt.close();
                    if(con!=null)
                        con.close();
                }
    }

 

posted @ 2019-03-23 19:32 Cynical丶Gary 阅读(...) 评论(...) 编辑 收藏