java_sql_jtable表格查询.doc

java swing sql-jtable表格 查询 分类: java 2010-08-11 19:37 1132人阅读 评论(0) 收藏 举报 swingjavastringnulllistobject数据库连接类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) = 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 publicfinal static String columnHeaders = 国家名称,所属地区,国家面积,人口数量,国家GDP;private Object datavalues = , , , , ;public int getColumnCount() return datavalues0.length;public int getRowCount() return datavalues.length;public Object getValueAt(int rowIndex, int columnIndex) return datavaluesrowIndexcolumnIndex;public String getColumnName(int column) return columnHeaderscolumn;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 queryFirst() String sql = select top 5 * from bbc order by name;List list = query(sql);return list;public static List downPage() int y = queryCount();List list = null;int count = 0; / 总页数if (y % 5 = 0) count = y / 5; else count = y / 5 + 1;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 query(String sql) Connection conn = DBConnection.getConnection();List list = new ArrayList();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 upPage() List 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 list = CountryOp