java swing 表格 分页 sql 增 删 改 查

数据库连接类

    import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {  public static Connection getConnection(){
  Connection conn = null;
  String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
  String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=Instane";
  String username = "sa";
  String password = "";   try {
   Class.forName(driver);
   conn = DriverManager.getConnection(url,username,password);
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();   return conn;
 }

}
业务类

     public class Country {

 private String name;
 private String region;
 private String area;
 private String population;
 private String gdp;

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

 public String getRegion() {
  return region;
 }

 public void setRegion(String region) {
  this.region = region;
 }

 public String getArea() {
  return area;
 }

 public void setArea(String area) {
  this.area = area;
 }

 public String getPopulation() {
  return population;
 }

 public void setPopulation(String population) {
  this.population = population;
 }

 public String getGdp() {
  return gdp;
 }

 public void setGdp(String gdp) {
  this.gdp = gdp; }
表格模型类

    import javax.swing.table.AbstractTableModel;

public class TableModel extends AbstractTableModel {

 public final static String[] columnHeaders = {
  "国家名称","所属地区","国家面积","人口数量","国家GDP"  
 private Object[][] datavalues = {  };  public int getColumnCount() {
  return datavalues[0].length;
 }

 public int getRowCount() {
  return datavalues.length;
 }

 public Object getValueAt(int rowIndex, int columnIndex) {
  return datavalues[rowIndex][columnIndex];  
 public String getColumnName(int column) {
  return columnHeaders[column];
 }

 public void setDatavalues(Object[][] datavalues) {
  this.datavalues = datavalues;
 }

}
数据库操作类

   import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CountryOperate {
 static int i = 1;
 static int x = 0;

 public static List<Country> queryFirst() {

  String sql = "select top 5 * from bbc order by name";
  List<Country> list = query(sql);
  return list;
 }

 public static List<Country> downPage() {
  int y = queryCount();
  List<Country> list = null;
  int count = 0; // 总页数
  if (y % 5 == 0) {
   count = y / 5;
  } else {
   count = y / 5 + 1;   if (i < count) {
   x = ((++i) - 1) * 5;
   String sql = "select top "
     + x
     + " * from (select * from bbc where name not in(select top "
     + x + " name from bbc order by name)) as A ";
   list = query(sql);
  } else if (i >= count) {
   list = null;   return list;
 }

 public static int queryCount() {
  int count = 0;
  Connection conn = DBConnection.getConnection();
  String sql = "select count(*) from bbc";
  Statement stmt = null;
  ResultSet rs = null;
  try {
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
   while (rs.next()) {
    count = rs.getInt(1);   } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    if (rs != null)
     rs.close();
    if (stmt != null)
     stmt.close();
    if (conn != null)
     conn.close();
   } catch (SQLException e) {
    e.printStackTrace();   }
  return count;

 public static List<Country> query(String sql) {
  Connection conn = DBConnection.getConnection();
  List<Country> list = new ArrayList<Country>();
  Statement stmt = null;
  ResultSet rs = null;
  try {
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
   while (rs.next()) {
    Country cou = new Country();
    cou.setName(rs.getString(1));
    cou.setRegion(rs.getString(2));
    cou.setArea(rs.getString(3));
    cou.setPopulation(rs.getString(4));
    cou.setGdp(rs.getString(5));
    list.add(cou);   } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    if (rs != null)
     rs.close();
    if (stmt != null)
     stmt.close();
    if (conn != null)
     conn.close();
   } catch (SQLException e) {
    e.printStackTrace();   }
  return list;
 }

 public static List<Country> upPage() {
  List<Country> list = null;
  if (i > 2) {
   x = ((--i) - 1) * 5;
   String sql = "select top "
     + x
     + " * from (select * from bbc where name not in(select top "
     + x + " name from bbc order by name)) as A ";
   list = query(sql);
  } else if (i == 2) {
   list = queryFirst();
   i--;
  } else if (i < 2) {
   list = null;   return list;
 }

 public static boolean add(Country cou) {
  Connection conn = DBConnection.getConnection();
  PreparedStatement pstmt = null;
  String sql = "insert into bbc values(?,?,?,?,?)";
  int x = 0;
  try {
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, cou.getName());
   pstmt.setString(2, cou.getRegion());
   pstmt.setString(3, cou.getArea());
   pstmt.setString(4, cou.getPopulation());
   pstmt.setString(5, cou.getGdp());
   x = pstmt.executeUpdate();
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    if (pstmt != null)
     pstmt.close();
    if (conn != null) {
     conn.close();    } catch (SQLException e) {
    e.printStackTrace();   }
  if (x == 1) {
   return true;
  } else {
   return false;  }

 public static boolean del(String key) {
  Connection conn = DBConnection.getConnection();
  PreparedStatement stmt = null;
  String sql = "delete from bbc where name =?";
  int x = 0;
  try {
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, key);
   x = stmt.executeUpdate();
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    if (stmt != null)
     stmt.close();
    if (conn != null)
     conn.close();
   } catch (SQLException e) {
    e.printStackTrace();   }
  if (x == 1) {
   return true;
  } else {
   return false;  }

 public static boolean modify(Country cou) {
  Connection conn = DBConnection.getConnection();
  PreparedStatement pstmt = null;
  String sql = "update bbc set Region=?,Area=?,Population=?,Gdp=? where name =?";
  int x = 0;
  try {
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, cou.getName());
   pstmt.setString(2, cou.getArea());
   pstmt.setString(3, cou.getPopulation());
   pstmt.setString(4, cou.getGdp());
   pstmt.setString(5, cou.getName());
   x = pstmt.executeUpdate();
  } catch (SQLException e) {
   e.printStackTrace();   if(x==1){
   return true;
  }else {
   return false;  } 主界面类

    import java.awt.Rectangle;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.List;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.SwingUtilities;

public class CountryInfoGui implements ActionListener {

 private JFrame jFrame;
 private JPanel jContentPane;
 private JLabel lblName = null;
 private JTextField tfName = null;
 private JLabel lblRegion = null;
 private JComboBox cbRegion = null;
 private JLabel lblArea = null;
 private JTextField tfArea = null;
 private JLabel lblPopu = null;
 private JTextField tfPopu = null;
 private JLabel lblGdp = null;
 private JTextField tfGDP = null;
 private JButton btnAdd = null;
 private JButton btnDel = null;
 private JButton btnModify = null;
 private JButton btnQuery = null;
 private JButton btnUpPage = null;
 private JButton btnDownPage = null;
 private JTable table = null;
 TableModel tm = null;
 JScrollPane jsp = null;

 private JTextField getTfName() {
  if (tfName == null) {
   tfName = new JTextField();
   tfName.setBounds(new Rectangle(89, 17, 173, 27));   return tfName;
 }

 private JComboBox getCbRegion() {
  if (cbRegion == null) {
   String[] str = { "请选择", "亚洲", "欧洲", "非洲", "北美洲", "南美洲", "大洋洲",    cbRegion = new JComboBox(str);
   cbRegion.setBounds(new Rectangle(360, 17, 229, 28));   return cbRegion;
 }

 private JTextField getTfArea() {
  if (tfArea == null) {
   tfArea = new JTextField();
   tfArea.setBounds(new Rectangle(89, 61, 173, 27));   return tfArea;
 }

 private JTextField getTfPopu() {
  if (tfPopu == null) {
   tfPopu = new JTextField();
   tfPopu.setBounds(new Rectangle(360, 61, 229, 28));   return tfPopu;
 }

 private JTextField getTfGDP() {
  if (tfGDP == null) {
   tfGDP = new JTextField();
   tfGDP.setBounds(new Rectangle(89, 105, 173, 27));   return tfGDP;
 }

 private JButton getBtnAdd() {
  if (btnAdd == null) {
   btnAdd = new JButton();
   btnAdd.setBounds(new Rectangle(46, 152, 71, 30));
   btnAdd.addActionListener(this);
   btnAdd.setText("添加");   return btnAdd;
 }

 private JButton getBtnUpPage() {
  if (btnUpPage == null) {
   btnUpPage = new JButton("上一页");
   btnUpPage.addActionListener(this);
   btnUpPage.setBounds(200, 390, 80, 30);   return btnUpPage;
 }

 private JButton getBtnDownPage() {
  if (btnDownPage == null) {
   btnDownPage = new JButton("下一页");
   btnDownPage.addActionListener(this);
   btnDownPage.setBounds(300, 390, 80, 30);   return btnDownPage;
 }

 private JButton getBtnDel() {
  if (btnDel == null) {
   btnDel = new JButton();
   btnDel.setBounds(new Rectangle(189, 152, 71, 30));
   btnDel.addActionListener(this);
   btnDel.setText("删除");   return btnDel;
 }

 private JButton getBtnModify() {
  if (btnModify == null) {
   btnModify = new JButton();
   btnModify.setBounds(new Rectangle(342, 152, 71, 30));
   btnModify.addActionListener(this);
   btnModify.setText("更新");   return btnModify;
 }

 private JButton getBtnQuery() {
  if (btnQuery == null) {
   btnQuery = new JButton();
   btnQuery.setBounds(new Rectangle(484, 152, 71, 30));
   btnQuery.addActionListener(this);
   btnQuery.setText("查找");   return btnQuery;
 }

 public static void main(String[] args) {
  SwingUtilities.invokeLater(new Runnable() {
   public void run() {
    CountryInfoGui application = new CountryInfoGui();
    application.getJFrame().setVisible(true);   });
 }

 private JFrame getJFrame() {
  if (jFrame == null) {
   jFrame = new JFrame();
   jFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
   jFrame.setBounds(new Rectangle(450, 250, 610, 454));
   jFrame.setContentPane(getJContentPane());
   jFrame.setTitle("国家信息管理");   return jFrame;
 }

 private JPanel getJContentPane() {
  if (jContentPane == null) {
   lblGdp = new JLabel();
   lblGdp.setBounds(new Rectangle(15, 106, 63, 27));
   lblGdp.setText("国家GDP");
   lblPopu = new JLabel();
   lblPopu.setBounds(new Rectangle(285, 61, 63, 27));
   lblPopu.setText("人口数量");
   lblArea = new JLabel();
   lblArea.setBounds(new Rectangle(15, 61, 63, 27));
   lblArea.setText("国家面积");
   lblRegion = new JLabel();
   lblRegion.setBounds(new Rectangle(285, 17, 63, 27));
   lblRegion.setText("所属地区");
   lblName = new JLabel();
   lblName.setBounds(new Rectangle(15, 17, 63, 27));
   lblName.setText("国家名称");
   jContentPane = new JPanel();
   jContentPane.setLayout(null);
   jContentPane.add(lblName, null);
   jContentPane.add(getTfName(), null);
   jContentPane.add(lblRegion, null);
   jContentPane.add(getCbRegion(), null);
   jContentPane.add(lblArea, null);
   jContentPane.add(getTfArea(), null);
   jContentPane.add(lblPopu, null);
   jContentPane.add(getTfPopu(), null);
   jContentPane.add(lblGdp, null);
   jContentPane.add(getTfGDP(), null);
   jContentPane.add(getBtnAdd(), null);
   jContentPane.add(getBtnDel(), null);
   jContentPane.add(getBtnModify(), null);
   jContentPane.add(getBtnQuery(), null);
   jContentPane.add(getBtnUpPage(), null);
   jContentPane.add(getBtnDownPage(), null);   return jContentPane;
 }

 public void actionPerformed(ActionEvent e) {
  if (e.getActionCommand().equals("查找")) {
   selectAll();
  } else if (e.getActionCommand().equals("下一页")) {
   selectDownPage();
  } else if (e.getActionCommand().equals("上一页")) {
   selectUpPage();
  } else if (e.getActionCommand().equals("添加")) {
   if (tfName.getText().equals("")
     || cbRegion.getSelectedItem().equals("请选择")
     || tfArea.getText().equals("")
     || tfPopu.getText().equals("")
     || tfGDP.getText().equals("")) {
    JOptionPane.showMessageDialog(jFrame, "请填写相关信息");
   }else {
    Country co = new Country();
    co.setName(tfName.getText());
    co.setRegion((String) cbRegion.getSelectedItem());
    co.setArea(tfArea.getText());
    co.setPopulation(tfPopu.getText());
    co.setGdp(tfGDP.getText());
    boolean flag = CountryOperate.add(co);
    if (flag) {
     JOptionPane.showMessageDialog(jFrame, "添加成功");
    } else {
     JOptionPane.showMessageDialog(jFrame, "添加失败");    }
  } else if (e.getActionCommand().equals("删除")) {
   if(!tfName.getText().equals("")){
    String key = tfName.getText();
    boolean flag = CountryOperate.del(key);
    if (flag) {
     JOptionPane.showMessageDialog(jFrame, "删除成功");
    } else {
     JOptionPane.showMessageDialog(jFrame, "删除失败");    }else {
    JOptionPane.showMessageDialog(jFrame, "请填写一个国家名字");   } else if (e.getActionCommand().equals("更新")) {
   if (!tfName.getText().equals("")
     && !cbRegion.getSelectedItem().equals("请选择")
     && !tfArea.getText().equals("")
     && !tfPopu.getText().equals("")
     && !tfGDP.getText().equals("")) {
    Country cou = new Country();
    cou.setName(tfName.getText());
    cou.setRegion((String)cbRegion.getSelectedItem());
    cou.setPopulation(tfPopu.getText());
    cou.setArea(tfArea.getText());
    cou.setGdp(tfGDP.getText());
    boolean flag = CountryOperate.modify(cou);
    if(flag){
     JOptionPane.showMessageDialog(jFrame, "更新成功");
    }else {
     JOptionPane.showMessageDialog(jFrame, "更新失败");    } else {
    JOptionPane.showMessageDialog(jFrame, "请填写相应的信息");   }
 }

 public void setValues(Object[][] obj) {
  this.tm.setDatavalues(obj);
 }

 public void selectAll() {
  List<Country> list = CountryOperate.queryFirst();
  Object[][] datavalues = new Object[list.size()][5];
  for (int i = 0; i < list.size(); i++) {
   Country co = list.get(i);
   datavalues[i][0] = co.getName();
   datavalues[i][1] = co.getRegion();
   datavalues[i][2] = co.getArea();
   datavalues[i][3] = co.getRegion();
   datavalues[i][4] = co.getGdp();   tm = new TableModel();
  this.setValues(datavalues);
  table = new JTable(tm);
  table.updateUI();
  JScrollPane jsp = new JScrollPane(table);
  jsp.setBounds(0, 200, 600, 182);
  jContentPane.add(jsp);
 }

 public void selectDownPage() {
  List<Country> list = CountryOperate.downPage();
  if (list != null) {
   Object[][] datavalues = new Object[list.size()][5];
   for (int i = 0; i < list.size(); i++) {
    Country co = list.get(i);
    datavalues[i][0] = co.getName();
    datavalues[i][1] = co.getRegion();
    datavalues[i][2] = co.getArea();
    datavalues[i][3] = co.getRegion();
    datavalues[i][4] = co.getGdp();    this.setValues(datavalues);
   table.updateUI();
  } else {
   JOptionPane.showMessageDialog(jFrame, "已经是最后一页");  }

 public void selectUpPage() {
  List<Country> list = CountryOperate.upPage();
  if (list != null) {
   Object[][] datavalues = new Object[list.size()][5];
   for (int i = 0; i < list.size(); i++) {
    Country co = list.get(i);
    datavalues[i][0] = co.getName();
    datavalues[i][1] = co.getRegion();
    datavalues[i][2] = co.getArea();
    datavalues[i][3] = co.getRegion();
    datavalues[i][4] = co.getGdp();    this.setValues(datavalues);
   table.updateUI();
  } else {
   JOptionPane.showMessageDialog(jFrame, "已经是第一页");  }