Java連接sql數(shù)據(jù)庫增刪改查

1、Java 連接 sql2000 數(shù)據(jù)庫增刪改查功能1. 數(shù)據(jù)庫連接package edu;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:mi

2、crosoft:sqlserver:/localhost:1433;DatabaseName=shop ;String username = sa;String password = sa;try Class.forName (driver);conn = DriverManager.getConnection (url,username,password); catch (ClassNotFoundException e) e.printStackTrace(); catch (SQLException e) e.printStackTrace();return conn;2. 生成 get

3、 set 方法 package edu;public class Country private String name;private String region;private String area;privateString privateString population;private String gdp;public String getName() return name;public void setName(String name) = name;public String getRegion() return region;public void s

4、etRegion(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 s

5、etGdp(String gdp) this.gdp = gdp;3 實(shí)現(xiàn)方法package edu;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.JOp

6、tionPane;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 private private private private private private private private private private pr

7、ivate private private private private private private privateJFrameJPanelJLabeljFrame; jContentPane; lblName = null;JTextField tfName = null;JLabel lblRegion private private private private private private private private private private private private private private private private private privat

8、e privateJFrameJPanelJLabeljFrame; jContentPane; lblName = null;JTextField tfName = null;JLabel lblRegion = null;JComboBox cbRegion = null;JLabel lblArea = null;JTextField tfArea = null;JLabel lblPopu = null; JTextField tfPopu = null;JLabel lblGdp = null; JTextField tfGDP = null;JButtonJButtonJButto

9、nJButtonJButtonJButtonbtnAdd = null;btnDel = null;btnModify = null; btnQuery = null; btnUpPage = null; btnDownPage = null;JTable table = null;TableModeltm = null;JScrollPanejsp = null;private JTextField getTfName() if (tfName = null) tfName = new JTextField();tfName.setBounds(new Rectangle(89, 17, 1

10、73, 27);return tfName;private JComboBox getCbRegion() if (cbRegion = null) String str = 請(qǐng)選擇, 亞洲, 歐洲 , 非洲, 北美洲, 南美洲, 大洋洲,南極洲 ;cbRegion = new JComboBox(str); cbRegion.setBounds(new Rectangle(360, 17, 229, 28);return cbRegion;private JTextField getTfArea() if (tfArea = null) tfArea = new JTextField();t

11、fArea.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 Rectang

12、le(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) btn UpPage = new JButto n(上一頁)

13、; 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

14、() 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); btnModi

15、fy.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 ar

16、gs) 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

17、 Rectangle(450, 250, 610, 454);jFrame.setContentPane(getJContentPane();jFrame .se tTit le(國家信息管理);return jFrame;private JPanel getJContentPane() if (jContentPane = null)lblGdp = new JLabel();lblGdp.setBounds(new Rectangle(15, 106, 63, 27);lblGdp .se tTex t(國家 GDP);lblPopu = new JLabel();lblPopu.setB

18、ounds(new Rectangle(285, 61, 63, 27);lblPopu .se tTex t(人口數(shù)量);lblArea = new JLabel();lblArea.setBounds(new Rectangle(15, 61, 63, 27);lblArea .se tTex t(國家面積);lblRegion = new JLabel();lblRegion.setBounds(new Rectangle(285, 17, 63, 27);lblRegion .se tTex t(所屬地區(qū));lblName = new JLabel();lblName.setBound

19、s( 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); j

20、ContentPane.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

21、.add(getBtnQuery(), null); jContentPane.add(getBtnUpPage(), null); jContentPane.add(getBtnDownPage(), null);return jContentPane; TOC o 1-5 h z public void actionPerformed(ActionEvent e) if (e.ge tActio nCommand().equals(查找)selectAll(); else if (e.ge tActio nCommand().equals(下一頁)selectDownPage(); els

22、e if (e.ge tActio nCommand().equals(上一頁)selectUpPage(); else if (e.ge tActio nCommand().equals(添加)if (tfName.getText().equals()| cbRegion .getSelectedltem().equals(請(qǐng)選擇) |tfArea.getText().equals() |tfPopu.getText().equals() |tfGDP.getText().equals() JOptionPane.showMessageDialog(jFrame, 請(qǐng)?zhí)顚懴嚓P(guān)信息);else

23、 Country co = new Country(); co.setNametfName.getText(); co.setRegion(String)cbRegion.getSelectedItem(); co.setAreatfArea.getText(); co.setPopulationtfPopu.getText(); co.setGdptfGDP.getText();boolean flag = CountryOperate. add(co);if (flag) JOptionPane.showMessageDialog (jFrame, 添加成功); else JOptionP

24、ane.showMessageDialog (jFrame, 添加失敗); else if (e.ge tActio nCommand().equals(刪除)if(!tfName.getText().equals()String key =tfName.getText();boolean flag = CountryOperate. del(key);if (flag) JOptionPane.showMessageDialog(jFrame, 刪除成功); else JOptionPane.showMessageDialog(jFrame, 刪除失敗);else JOp t i o nP

25、a n e .showMessageDialog ( jFr am e , 請(qǐng)?zhí)顚懸粋€(gè)國家名字); else if (e.ge tActio nCommand().equals(更新)if (!tfName.getText().equals()& cbRegion.ge tSelec tedl tem().equals(請(qǐng)選擇)& tfArea.getText().equals()& tfPopu.getText().equals()& tfGDP.getText().equals() Country cou = new Country(); cou.setNametfName.getText

26、(); cou.setRegion(StringcbRegion.getSelectedItem(); cou.setPopulationtfPopu.getText();cou.setAreatfArea.getText(); cou.setGdptfGDP.getText();boolean flag = CountryOperate. modify(cou); if(flag)JOptionPane.showMessageDialog(jFrame, 更新成功);else JOptionPane.showMessageDialog(jFrame, 更新失敗); else JOp t i

27、o nP a n e .showMessageDialog ( jFr am e , 請(qǐng)?zhí)顚懴鄳?yīng)的信息);public void setValues(Object obj) this.tm.setDatavalues(obj);public void selectAll() List list = CountryOperate.queryFirst ();Object datavalues = new Objectlist.size()5; for (int i = 0; i list.size(); i+) Country co = list.get(i);datavaluesi0datav

28、aluesi1datavaluesi2datavaluesi3datavaluesi4= co.getName();= co.getRegion();= co.getArea();= co.getRegion();= 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

29、.add(jsp);public void selectDownPage() List list = CountryOperate.downPage ();if (list != null) Object datavalues = new Objectlist.size()5;for (int i = 0; i list.size(); i+) Country co = list.get(i);datavaluesi0datavaluesi1datavaluesi0datavaluesi1datavaluesi2datavaluesi3datavaluesi4= co.getName();=

30、co.getRegion();= co.getArea();= co.getRegion();= co.getGdp();this.setValues(datavalues);table.updateUI(); else JOptionPane.showMessageDialog (jFrame, 已經(jīng)是最后一頁);public void selectUpPage() List list = CountryOperate.upPage ();if (list != null) Object datavalues = new Objectlist.size()5; for (int i = 0;

31、 i list.size(); i+) Country co = list.get(i);datavaluesi0datavaluesi1datavaluesi0datavaluesi1datavaluesi2datavaluesi3datavaluesi4= co.getName();= co.getRegion();= co.getArea();= co.getRegion();= co.getGdp();this.setValues(datavalues);table.updateUI(); else JOptionPane.showMessageDialog (jFrame, 已經(jīng)是第

32、一頁);4 操作方法package edu;import javax.swing.table.AbstractTableModel;public class TableModel extends AbstractTableModel public final static String columnHeaders = 國家名稱，“所屬地區(qū)，“國家面積，“人口數(shù)量，“國家GDP;private Object datavalues = , , , , ;publicint getColumnCount() return datavalues0.length; public int getRowCo

33、unt() 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; 5 操作方法package edu;import java.s

34、ql.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 *

35、from bbc order by name ;List list = query (sql);return list;public static List downPage() int y = queryCount();List list = null;int count = 0; / 總頁數(shù)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

36、 = 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

37、(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

38、();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

39、 != 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 +

40、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, c