Java GUI+mysql+分页查询

1.要求 ： 创建一个学生信息管理数据库

2.实现分页查询

代码如下：

a)学生实体类：

/**
 * @author: Annie
 * @date:2016年6月23日
 * @description:学生实体类   学号+姓名+密码
 */
public class student {
    private int sid;
    private String sname;
    private String spassword;

    public student(int sid, String sname, String spassword) {
        this.sid = sid;
        this.sname = sname;
        this.spassword = spassword;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public void setSpassword(String spassword) {
        this.spassword = spassword;
    }

    public int getSid() {
        return this.sid;
    }

    public String getSname() {
        return this.sname;
    }

    public String getSpassword() {
        return this.spassword;
    }

    public void showStudentInfo() {
        System.out.println("学号:" + this.getSid() + "\t姓名:" + this.getSname()
                + "\t密码：" + this.getSpassword());
    }

    public String getStudentInfo() {
        return "学号" + this.getSid() + "姓名" + this.getSname() + "密码："
                + this.getSpassword()+"\n";
    }

}

b)数据库操作类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;


/**
 * @author: Annie
 * @date:2016年6月23日
 * @description:
 */
public class linkDB {
    
      ArrayList<student> arrayS = null;
        final int per_pages_size=5;//每个页面的长度，即每个页面几条数据
        
        //数据库的连接的方法
        public Connection getConnection2() {
            Connection conn = null;
            String url = "jdbc:mysql://localhost:3306/test";
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(url, "root", "12345678");
            } catch (SQLException e) {
                System.out.println("SQL 异常");
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                System.out.println("数据库没有找到");
                e.printStackTrace();
            }
            return conn;
        }

        
        /**
         * 数据库中查询的代码，返回一个结果集
         * */
        public ResultSet getAllStudent_info() {
            Connection conn = this.getConnection2();
            Statement comm = null;
            ResultSet rs = null;
            try {
                comm = conn.createStatement();

                String sql2 = "select * from student order by sid";
                rs = comm.executeQuery(sql2);
                return rs;

            } catch (SQLException e) {
                e.printStackTrace();
            }
            return rs;
        }
        
        
      /**
       * 获取数据库中的总行数
       * */
        public int getPagesTotalSize() {
            //得到查询的结果集
            ResultSet rs = this.getAllStudent_info();
            int totalsize = 0;//数据总体的行数
            try {
                rs.last();  // 将光标移动到此 ResultSet 对象的最后一行
              //  获取当前行编号(先将光标移到最后一行，然后再获取最后一行的下标，即可得到整个数据库的行数)
                totalsize = rs.getRow();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return totalsize;
        }
        
        /**
         * 得到总共的页数的方法
         * */
        public int getPagesNum() {
            int totalsize = (Integer)this.getPagesTotalSize()/per_pages_size;   
            return (totalsize+1);
        }
        
        
        /**
         * 将从数据库里遍历到的数据装到数组里
         * */
        public ArrayList<student> get_per_Page(int per_pages_num) {
            //得到所有学生的结果集
            ResultSet rs = this.getAllStudent_info();
            //定义一个装学生对象的集合
            ArrayList<student> arrayS = new ArrayList();
            int totalsize = this.getPagesTotalSize();
            
            int sid = 0;//学号
            String sname = null;//姓名
            String spassword = null;//密码
            
            if (per_pages_size * (per_pages_num - 1) < totalsize) {
                int start = per_pages_size * (per_pages_num - 1) + 1;
                int end = 0;
                if (per_pages_size * per_pages_num > totalsize) {
                    end = totalsize;
                } else {
                    end = per_pages_size * per_pages_num;
                }
                for (int i = start; i <= end; i++) {
                    try {
                        rs.absolute(i);
                        sid = rs.getInt(1);
                        sname = rs.getString(2);
                        spassword = rs.getString(3);
                        student s = new student(sid, sname, spassword);
                        arrayS.add(s);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            } else {
                System.out.println("超出范围");
            }
            return arrayS;
        }

        
        /**
         * 展示数据的方法
         * */
        public void test_per_page(int id) {
            ArrayList<student> arrayS = this.get_per_Page(id);

            //遍历数组，将数据展示在文本域里
            for (Iterator i = arrayS.iterator(); i.hasNext();) {
                student s = (student) i.next();
                s.showStudentInfo();
            }
        } 
}

c)主界面类

import java.awt.BorderLayout;

import java.awt.Button;
import java.awt.Frame;
import java.awt.GridLayout;
import java.awt.Panel;
import java.awt.TextArea;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.util.ArrayList;
import java.util.Iterator;

/**
 * @author: Annie
 * @date:2016年6月23日
 * @description:
 */
public class MainPages  extends Frame{

    private int currentlyPage = 1; //初始化当前的页数
    private Panel center, bottom;
    private Button previously, next, first, tail;
    private TextArea ta;
    private linkDB ldb = new linkDB();
    ArrayList<student> arrayS = null;

    public MainPages() {

        this.previously = new Button("上一页");
        this.next = new Button("下一页");
        this.first = new Button("首页");
        this.tail = new Button("尾页");
        this.ta = new TextArea();
        ta.setRows(5);
        ta.setBounds(0, 0, 40, 20);
        this.initialization();

        this.center = new Panel(new GridLayout(1, 1));
        this.bottom = new Panel(new GridLayout(1, 4));

        center.add(ta);

        bottom.add(previously);
        bottom.add(next);
        bottom.add(first);
        bottom.add(tail);

        /*对首页、下一页、上一页、尾页做监听*/
        MyListener ml = new MyListener();
        this.previously.addActionListener(ml);
        this.next.addActionListener(ml);
        this.first.addActionListener(ml);
        this.tail.addActionListener(ml);

        // this.add(center,BorderLayout.NORTH);不知是何原因？这样的话，显示不bottom。可能和BorderLayout有关系。
        this.add(center, BorderLayout.CENTER);
        this.add(bottom, BorderLayout.SOUTH);

        this.addWindowListener(new WindowAdapter() {
            public void windowClosing(WindowEvent e) {
                setVisible(false);
                dispose();
                System.exit(0);
            }
        });//对关闭窗口做监听

        this.setBounds(200, 200, 350, 180);
        this.setVisible(true);

    }

    /**
     * 初始化第一页
     * */
    void initialization() {
        showDate();
    }

    /**
     * 对按钮做监听的方法
     * */
    class MyListener implements ActionListener {

        public void actionPerformed(ActionEvent e) {
            ta.setText("");
            if (e.getSource() == previously) {//上一页的按钮
                if (currentlyPage >= 2) {
                    currentlyPage--;
                } else {
                    ta.append("当前为第一页！\n");
                }
                //展示数据
                showDate();


            } else if (e.getSource() == next) { //如果为下一页
                ta.setText("");
                int pagesNum = ldb.getPagesNum();
                if (currentlyPage < pagesNum) {//如果当前的页码小于总页数
                    currentlyPage++;
                } else {
                    ta.append("当前为最后一页！\n");
                }

                showDate();

            } else if (e.getSource() == first) {//如果是首页
                ta.setText("");
                currentlyPage = 1;
                showDate();


            } else if (e.getSource() == tail) {//如果是尾页
                ta.setText("");
                currentlyPage = ldb.getPagesNum();
                showDate();
            }
        }
    }

    /**
     * 展示数据
     */
    public void showDate() {
        arrayS = ldb.get_per_Page(currentlyPage);
        Iterator i = arrayS.iterator();
        while ( i.hasNext()) {
            student s = (student) i.next();
            ta.append(s.getStudentInfo());
        }
    }
    public static void main(String[] args) {
        MainPages mp = new MainPages();
    }
}

 

d)创建数据库的表格及插入数据

create table student
(sid int not null,
sname varchar(50),
spassword varchar(20),
primary key(sid)
);

insert into student values('1','科比','24');
insert into student values('2','加内特','5');
insert into student values('3','艾弗森','23');

insert into student values('11','德罗巴','24');
insert into student values('12','罗尼','5');
insert into student values('13','克里斯蒂亚诺 诺那尔多','23');
insert into student values('21','梅西','24');
insert into student values('22','比利亚','5');
insert into student values('23','哈维','23');
insert into student values('31','麦孔','24');
insert into student values('32','埃弗拉','5');
insert into student values('33','费尔南德斯','23');
insert into student values('41','维迪奇','24');
insert into student values('42','卡西利亚斯','5');

insert into student values('43','詹姆斯','23');
insert into student values('51','韦德','24');
insert into student values('52','姚明','5');
insert into student values('53','邓肯','23');

 

实现效果图：