Java连接数据库增删改查.doc

Java连接sql2000数据库增删改查功能学院：仲恺农业工程学院 姓名：陈伟团 专业：计算机科学与技术 班级：084班1. 数据库连接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:microsoft: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 set方法package edu;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;3实现方法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.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(StringcbRegion.getSelectedItem(;cou.setPopulation(tfPopu.getText(;cou.setArea(tfArea.getText(;cou.setGdp(tfGDP.getText(;boolean flag = CountryOperate.modify(cou;if(flagJOptionPane.showMessageDialog(jFrame, "更新成功"else JOptionPane.showMessageDialog(jFrame, "更新失败" else JOptionPane.showMessageDialog(jFrame, "请填写相应的信息"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;datavaluesi0 = co.getName(;datavaluesi1 = co.getRegion(;datavaluesi2 = co.getArea(;datavaluesi3 = co.getRegion(;datavaluesi4 = 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 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;datavaluesi0 = co.getName(;datavaluesi1 = co.getRegion(;datavaluesi2 = co.getArea(;datavaluesi3 = co.getRegion(;datavaluesi4 = co.getGdp(;this.setValues(datavalues;table.updateUI(; else JOptionPane.showMessageDialog(jFrame, "已经是最后一页"public void selectUpPage( List list = CountryOperate. upPage(;if (list != null Object datavalues = new Objectlist.size(5;for (int i = 0; i < list.size(; i+ Country co = list.get(i;datavaluesi0 = co.getName(;datavaluesi1 = co.getRegion(;datavaluesi2 = co.getArea(;datavaluesi3 = co.getRegion(;datavaluesi4 = co.getGdp(;this.setValues(datavalues;table.updateUI(; else JOptionPane.showMessageDialog(jFrame, "已经是第一页"4操作方法package edu;import javax.swing.table.AbstractTableModel;public class TableModel extends AbstractTableModel public final 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;5操作方法package edu;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 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 != nullrs.close(;if (stmt != nullstmt.close(;if (conn != nullconn.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 != nullrs.close(;if (stmt != nullstmt.close(;if (conn != nullconn.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 != nullpstmt.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 != nullstmt.close(;if (conn != nullconn.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=1return true;else return false;6实现界面效果1.添加功能1. 删除功能3查找功能