基于Java+SQLServer2005增刪改查實例

1、 工程數(shù)據(jù)庫設(shè)計與應(yīng)用 實驗報告 實驗五 數(shù)據(jù)庫應(yīng)用的基本操作之一學(xué)號: 1030412612 班級:計算機(jī)1206 姓名:王成義成績 指導(dǎo)教師：戴月明一、實驗?zāi)康耐ㄟ^實驗熟練掌握調(diào)用數(shù)據(jù)庫的方法，掌握如何通過鏈接字調(diào)用數(shù)據(jù)庫的方法。二、實驗內(nèi)容1、創(chuàng)建一個新的數(shù)據(jù)庫鏈接字。2、建立一個新的窗體，在窗體上使用控件來對數(shù)據(jù)庫進(jìn)行相應(yīng)操作。三、實驗完成情況1、數(shù)據(jù)庫連接測試：源碼：/*Test.java*/import java.sql.*;public class Test public static void main(String srg) String driverName = "

2、;com.microsoft.sqlserver.jdbc.SQLServerDriver" / 加載JDBC驅(qū)動String dbURL = "jdbc:sqlserver:/localhost:1433; DatabaseName=Student" / 連接服務(wù)器和數(shù)據(jù)庫testString userName = "sa" / 默認(rèn)用戶名String userPwd = "root" / 用戶密碼Connection dbConn;try Class.forName(driverName);dbConn = Driver

3、Manager.getConnection(dbURL, userName, userPwd);System.out.println("Connection Successful!"); / 如果連接成功/ 控制臺輸出Connection/ Successful! catch (Exception e) e.printStackTrace();結(jié)果截圖：（圖1）2、 工程目錄：（圖2）3、 數(shù)據(jù)庫連接類：/*DBConnection.java*/package edu;import java.sql.*;public class DBConnection public st

4、atic Connection getConnection() Connection conn = null;String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver" / 加載JDBC驅(qū)動String url = "jdbc:sqlserver:/localhost:1433; DatabaseName=Student" / 連接服務(wù)器和數(shù)據(jù)庫testString username = "sa"String password = "root"try

5、 Class.forName(driver);conn = DriverManager.getConnection(url, username, password); catch (ClassNotFoundException e) e.printStackTrace(); catch (SQLException e) e.printStackTrace();return conn;4、 生成學(xué)生表對應(yīng)的get，set方法：/*Student.java*/package edu;public class Student private String Sid;private String Sna

6、me;private String Ssex;private String Sclass;private String Stel;public String getSid() return Sid;public void setSid(String sid) Sid = sid;public String getSname() return Sname;public void setSname(String sname) Sname = sname;public String getSsex() return Ssex;public void setSsex(String ssex) Ssex

7、 = ssex;public String getSclass() return Sclass;public void setSclass(String sclass) Sclass = sclass;public String getStel() return Stel;public void setStel(String stel) Stel = stel;5、 實現(xiàn)方法，學(xué)生信息系統(tǒng)管理界面：/*StudentInfo.java*/package edu;import java.awt.Rectangle;import java.awt.event.ActionEvent;import

8、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.JTextFi

9、eld;import javax.swing.SwingUtilities;public class StudentInfo implements ActionListener private JFrame jFrame;private JPanel jContentPane;private JLabel lblName = null;private JTextField tfName = null;private JLabel lblRegion = null;private JTextField cbRegion = null;private JLabel lblArea = null;p

10、rivate 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 btn

11、UpPage = 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 JTextField getCbRegion() if (cbRe

12、gion = null) cbRegion = new JTextField();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) tfPo

13、pu = 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.setB

14、ounds(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;priv

15、ate 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

16、, 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 btnModif

17、y;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 voi

18、d run() StudentInfo application = new StudentInfo();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(getJConten

19、tPane();jFrame.setTitle("學(xué)生信息管理-王成義-1030412612-計算機(jī)1206");return jFrame;private JPanel getJContentPane() if (jContentPane = null) lblGdp = new JLabel();lblGdp.setBounds(new Rectangle(15, 106, 63, 27);lblGdp.setText("電話");/5lblPopu = new JLabel();lblPopu.setBounds(new Rectangle(285

20、, 61, 63, 27);lblPopu.setText("班級");/4lblArea = new JLabel();lblArea.setBounds(new Rectangle(15, 61, 63, 27);lblArea.setText("性別");/3lblRegion = new JLabel();lblRegion.setBounds(new Rectangle(285, 17, 63, 27);lblRegion.setText("姓名");/2lblName = new JLabel();lblName.setB

21、ounds(new Rectangle(15, 17, 63, 27);lblName.setText("學(xué)號");/1jContentPane = 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

22、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.add(get

23、BtnQuery(), 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 i

24、f (e.getActionCommand().equals("上一頁") selectUpPage(); else if (e.getActionCommand().equals("添加") if (tfName.getText().equals("")| cbRegion.getText().equals("")| tfArea.getText().equals("")| tfPopu.getText().equals("")| tfGDP.getText().equal

25、s("") JOptionPane.showMessageDialog(jFrame, "請?zhí)顚懴嚓P(guān)信息"); else Student stu = new Student();stu.setSid(tfName.getText();stu.setSname(cbRegion.getText();stu.setSsex(tfArea.getText();stu.setSclass(tfPopu.getText();stu.setStel(tfGDP.getText();boolean flag = StudentOperate.add(stu);if (

26、flag) JOptionPane.showMessageDialog(jFrame, "添加成功"); else JOptionPane.showMessageDialog(jFrame, "添加失敗"); else if (e.getActionCommand().equals("刪除") if (!tfName.getText().equals("") String key = tfName.getText();boolean flag = StudentOperate.del(key);if (flag)

27、JOptionPane.showMessageDialog(jFrame, "刪除成功"); else JOptionPane.showMessageDialog(jFrame, "刪除失敗"); else JOptionPane.showMessageDialog(jFrame, "請?zhí)顚懸粋€學(xué)號"); else if (e.getActionCommand().equals("更新") if (!tfName.getText().equals("")&& !cbRegion.

28、getText().equals("")&& !tfArea.getText().equals("")&& !tfPopu.getText().equals("")&& !tfGDP.getText().equals("") Student stu = new Student();stu.setSid(tfName.getText();stu.setSname(cbRegion.getText();stu.setSsex(tfArea.getText();stu.se

29、tSclass(tfPopu.getText();stu.setStel(tfGDP.getText();System.out.println(tfName.getText()+'|'+cbRegion.getText()+'|'+tfArea.getText()+'|'+tfPopu.getText()+'|'+tfGDP.getText();boolean flag = StudentOperate.modify(stu);if (flag) JOptionPane.showMessageDialog(jFrame, &quo

30、t;更新成功"); else JOptionPane.showMessageDialog(jFrame, "更新失敗"); else JOptionPane.showMessageDialog(jFrame, "請?zhí)顚懴鄳?yīng)的信息");public void setValues(Object obj) this.tm.setDatavalues(obj);public void selectAll() List<Student> list = StudentOperate.queryFirst();Object datavalues

31、= new Objectlist.size()5;for (int i = 0; i < list.size(); i+) Student stu = list.get(i);datavaluesi0 = stu.getSid();datavaluesi1 = stu.getSname();datavaluesi2 = stu.getSsex();datavaluesi3 = stu.getSclass();datavaluesi4 = stu.getStel();tm = new TableModel();this.setValues(datavalues);table = new J

32、Table(tm);table.updateUI();JScrollPane jsp = new JScrollPane(table);jsp.setBounds(0, 200, 600, 182);jContentPane.add(jsp);public void selectDownPage() List<Student> list = StudentOperate.downPage();if (list != null) Object datavalues = new Objectlist.size()5;for (int i = 0; i < list.size();

33、 i+) Student stu = list.get(i);datavaluesi0 = stu.getSid();datavaluesi1 = stu.getSname();datavaluesi2 = stu.getSsex();datavaluesi3 = stu.getSclass();datavaluesi4 = stu.getStel();this.setValues(datavalues);table.updateUI(); else JOptionPane.showMessageDialog(jFrame, "已經(jīng)是最后一頁");public void s

34、electUpPage() List<Student> list = StudentOperate.upPage();if (list != null) Object datavalues = new Objectlist.size()5;for (int i = 0; i < list.size(); i+) Student stu = list.get(i);datavaluesi0 = stu.getSid();datavaluesi1 = stu.getSname();datavaluesi2 = stu.getSsex();datavaluesi3 = stu.ge

35、tSclass();datavaluesi4 = stu.getStel();this.setValues(datavalues);table.updateUI(); else JOptionPane.showMessageDialog(jFrame, "已經(jīng)是第一頁");6、 事件操作方法實現(xiàn)：/*TableModel.java*/package edu;import javax.swing.table.AbstractTableModel;public class TableModel extends AbstractTableModel public final st

36、atic String columnHeaders = "學(xué)號", "姓名", "性別","班級", "電話" ;private Object datavalues = " ", " ", " ", " ", " " ;public int getColumnCount() return datavalues0.length;public int getRowCount() return data

37、values.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;/*StudentOperate.java*/package edu;import java.sql.C

38、onnection;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 StudentOperate static int i = 1;static int x = 0;public static List<Student> queryFirst() String sql = &quo

39、t;select top 5 * from S order by Sid"List<Student> list = query(sql);return list;public static List<Student> downPage() int y = queryCount();List<Student> list = null;int count = 0; / 總頁數(shù)if (y % 5 = 0) count = y / 5; else count = y / 5 + 1;if (i < count) x = (+i) - 1) * 5;S

40、tring sql = "select top "+ 5+ " * from (select * from S where Sid not in(select top "+ x + " Sid from S order by Sid) as A "list = query(sql); else if (i >= count) list = null;return list;public static int queryCount() int count = 0;Connection conn = DBConnection.get

41、Connection();String sql = "select count(*) from S"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 != nul

42、l)stmt.close();if (conn != null)conn.close(); catch (SQLException e) e.printStackTrace();return count;public static List<Student> query(String sql) Connection conn = DBConnection.getConnection();List<Student> list = new ArrayList<Student>();Statement stmt = null;ResultSet rs = null

43、;try stmt = conn.createStatement();rs = stmt.executeQuery(sql);while (rs.next() Student stu = new Student();stu.setSid(rs.getString(1);stu.setSname(rs.getString(2);stu.setSsex(rs.getString(3);stu.setSclass(rs.getString(4);stu.setStel(rs.getString(5);list.add(stu); catch (SQLException e) e.printStack

44、Trace(); 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<Student> upPage() List<Student> list = null;if (i > 2) x = (-i) - 1) * 5;String sql = "select top

45、"+ 5+ " * from (select * from S where Sid not in(select top "+ x + " Sid from S order by Sid) as A "list = query(sql); else if (i = 2) list = queryFirst();i-; else if (i < 2) list = null;return list;public static boolean add(Student stu) Connection conn = DBConnection.getConnection();PreparedStatement pstmt = null;String sql = "insert into S values(?,?,?,?,?)"int x = 0;try pstmt = conn.prepareStatement(sql);pstmt.setString(1, stu.getSid();pstmt.setSt