dbcallablestatementframe.java

JAVA数据库编程实例随书源码 JAVA数据库编程实例随书源码

package dbcallablestatement;

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import javax.swing.table.*;

/**
 * Title:			使用CallableStatement进行数据库操作
 * Description:			教学示范
 * Copyright:			Copyright (c) 2002
 * Company:			北京师范大学计算机系
 * @author			孙一林
 * @version			1.0
 */

public class dbCallableStatementFrame extends JFrame {
  private JPanel contentPane;
  private Label label1 = new Label();
  private TextField numField = new TextField();
  private Label label2 = new Label();
  private TextField nameField = new TextField();
  private Label label3 = new Label();
  private Choice sexChoice = new Choice();
  private Label label4 = new Label();
  private TextField ageField = new TextField();
  private Label label5 = new Label();
  private TextField departmentField = new TextField();
  private Button saveButton = new Button();
  private Button refreshButton = new Button();
  private Button createButton = new Button();

  Connection connection = null;                //定义与数据库进行连接的Connection对象
  ResultSet rs = null;                         //定义数据库查询的结果集
  Statement statement = null;                  //定义查询数据库的Statement对象
  boolean lastQuery = false;                   //上次查询是否成功
  Vector vector;                               //定义存储结果集数据的数组
  String[] title = {"学号","姓名","年龄","性别","系名"};  //定义显示数据表格的标题
  AbstractTableModel tm;                               //定义显示数据表格的抽象类
  JScrollPane scroll;                                  //定义装载数据表格的容器

  public dbCallableStatementFrame() {
    enableEvents(AWTEvent.WINDOW_EVENT_MASK);
    try {
      jbInit();
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }

  private void jbInit() throws Exception  {          //初始化用户界面
    contentPane = (JPanel) this.getContentPane();
    label1.setText("学号");
    label1.setBounds(new Rectangle(43, 17, 32, 23));
    contentPane.setLayout(null);
    this.setSize(new Dimension(400, 492));
    this.setTitle("使用CallableStatement操作数据库");
    numField.setBounds(new Rectangle(81, 17, 93, 28));
    label2.setText("姓名");
    label2.setBounds(new Rectangle(194, 17, 32, 23));
    nameField.setBounds(new Rectangle(232, 17, 102, 28));
    label3.setText("性别");
    label3.setBounds(new Rectangle(27, 61, 29, 23));
    sexChoice.setBounds(new Rectangle(63, 61, 63, 28));
    label4.setText("年龄");
    label4.setBounds(new Rectangle(141, 61, 29, 23));
    ageField.setBounds(new Rectangle(180, 61, 57, 28));
    label5.setText("系别");
    label5.setBounds(new Rectangle(255, 61, 29, 23));
    departmentField.setBounds(new Rectangle(295, 61, 57, 28));
    saveButton.setLabel("存储到数据库");
    saveButton.setBounds(new Rectangle(141, 412, 117, 30));
    saveButton.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(ActionEvent e) {
        saveButton_actionPerformed(e);
      }
    });
    refreshButton.setLabel("刷新显示结果");
    refreshButton.setBounds(new Rectangle(262, 412, 117, 30));
    refreshButton.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(ActionEvent e) {
        refreshButton_actionPerformed(e);
      }
    });
    createButton.setLabel("创建存储过程");
    createButton.setBounds(new Rectangle(20, 412, 117, 30));
    createButton.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(ActionEvent e) {
        createButton_actionPerformed(e);
      }
    });
    contentPane.add(label1, null);
    contentPane.add(numField, null);
    contentPane.add(label2, null);
    contentPane.add(nameField, null);
    contentPane.add(label3, null);
    contentPane.add(sexChoice, null);
    contentPane.add(label4, null);
    contentPane.add(ageField, null);
    contentPane.add(label5, null);
    contentPane.add(departmentField, null);
    contentPane.add(saveButton, null);
    contentPane.add(createButton, null);
    contentPane.add(refreshButton, null);

    sexChoice.addItem("男");
    sexChoice.addItem("女");
    saveButton.setEnabled(false);
  }

  protected void processWindowEvent(WindowEvent e) {
    super.processWindowEvent(e);
    if (e.getID() == WindowEvent.WINDOW_CLOSING) {
      System.exit(0);
    }
  }

  void initTable() {                           //初始化显示数据集结果的表格
    JTable table;
    vector = new Vector();
    tm = new AbstractTableModel() {            //实现表格抽象类的接口
      public int getColumnCount() {
        return title.length;
      }
      public int getRowCount() {
        return vector.size();
      }
      public Object getValueAt(int row, int column) {
        if(!vector.isEmpty())
        {
          return ((Vector)vector.elementAt(row)).elementAt(column);
        }
        else
        {
          return null;
        }
      }
      public void setValueAt(Object value, int row, int column)
      {
      }
      public String getColumnName(int column)
      {
        return title[column];
      }
      public Class getColumnClass(int c)
      {
        return getValueAt(0,c).getClass();
      }
      public boolean isCellEditable(int row, int column)
      {
        return false;
      }
    };
    table = new JTable(tm);
    table.setToolTipText("Display Query Result");
    table.setAutoResizeMode(table.AUTO_RESIZE_SUBSEQUENT_COLUMNS);
    table.setCellSelectionEnabled(false);
    table.setShowHorizontalLines(true);
    table.setShowVerticalLines(true);
    scroll = new JScrollPane(table);
    scroll.setBounds(50,100,300,250);
    contentPane.add(scroll,null);
  }

  void createButton_actionPerformed(ActionEvent e) {    //在数据库中创建存储过程
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:studentSqlsvr";          //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      statement = connection.createStatement();  //创建Statement接口实例
      String sql = "create procedure insert_studentbase ";//创建要创建存储过程的SQL语句
      sql+= "(@学号 int, @姓名 varchar(20), @年龄 int, ";
      sql+= "@性别 varchar(2), @系名 varchar(20))";
      sql+= " as insert into studentbase(学号,姓名,年龄,性别,系名) ";
      sql+= "values (@学号,@姓名,@年龄,@性别,@系名)";
      statement.executeUpdate(sql);                   //使用Statement接口实例创建存储过程
    }
    catch(SQLException ex){                          //捕捉异常
      System.out.println("\nERROR:----- SQLException -----\n");
      while (ex != null) {
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
        System.out.println("ErrorCode: " + ex.getErrorCode());
        ex = ex.getNextException();
      }
    }
    catch(Exception ex ) {
      ex.printStackTrace();
    }
    finally {
      try {
        if(statement != null)
        {
          statement.close();                      //关闭Statement接口实例
        }
        if(connection != null)
        {
          connection.close();                   //关闭Connection接口实例
        }
      }
      catch (SQLException ex)  {
        System.out.println("\nERROR:----- SQLException -----\n");
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
        System.out.println("ErrorCode: " + ex.getErrorCode());
      }
    }
    saveButton.setEnabled(true);
    createButton.setEnabled(false);
  }

  void saveButton_actionPerformed(ActionEvent e) {     //将用户数据使用存储过程存储到数据库
    int stu_Number = Integer.parseInt(numField.getText());
    String stu_Name = nameField.getText();
    String stu_Sex = sexChoice.getSelectedItem();
    int stu_Age = Integer.parseInt(ageField.getText());
    String stu_Department = departmentField.getText();
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:studentSqlsvr";          //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "{call insert_studentbase(?,?,?,?,?)}";  //创建调用存储过程的SQL语句
      CallableStatement cStatement = connection.prepareCall(sql);  //创建CallableStatement接口调用存储过程
      cStatement.setInt(1, stu_Number);                //设置一条数据的各个字段的值
      cStatement.setString(2, stu_Name);
      cStatement.setInt(3, stu_Age);
      cStatement.setString(4, stu_Sex);
      cStatement.setString(5, stu_Department);
      cStatement.executeUpdate();                      //通过存储过程将一条数据插入数据库
      JOptionPane msg = new JOptionPane();
      String insert_msg = "添加记录成功";
      JOptionPane.showMessageDialog(this, insert_msg, insert_msg, 1);
      cStatement.close();                          //关闭CallableStatement接口实例
    }
    catch(SQLException ex){                          //捕捉异常
      System.out.println("\nERROR:----- SQLException -----\n");
      while (ex != null) {
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
        System.out.println("ErrorCode: " + ex.getErrorCode());
        ex = ex.getNextException();
      }
    }
    catch(Exception ex ) {
      ex.printStackTrace();
    }
    finally {
      try {
        if(statement != null)
        {
          statement.close();                      //关闭Statement接口实例
        }
        if(connection != null)
        {
          connection.close();                   //关闭Connection接口实例
        }
      }
      catch (SQLException ex)  {
        System.out.println("\nERROR:----- SQLException -----\n");
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
        System.out.println("ErrorCode: " + ex.getErrorCode());
      }
    }
    numField.setText("");
    nameField.setText("");
    ageField.setText("");
    departmentField.setText("");
  }

  void refreshButton_actionPerformed(ActionEvent e) {       //使用表格显示数据库数据
    if(lastQuery)                               //若上次查询成功，则首先清除上次查询的表格
    {
      scroll.setVisible(false);
      contentPane.remove(scroll);
    }
    initTable();                               //初始化新的表格
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:studentSqlsvr";          //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  //创建Statement接口实例
      String sql = "select * from studentbase";        //创建取出studentbase表中所有数据的SQL语句
      rs = statement.executeQuery(sql);                //将数据存入结果集中
      vector.removeAllElements();
      tm.fireTableStructureChanged();                  //刷新表格显示结果集中的数据
      while(rs.next())
      {
        Vector rec_vector = new Vector();
        rec_vector.addElement(String.valueOf(rs.getInt("学号")));
        rec_vector.addElement(rs.getString("姓名"));
        rec_vector.addElement(rs.getString("性别"));
        rec_vector.addElement(String.valueOf(rs.getShort("年龄")));
        rec_vector.addElement(rs.getString("系名"));
        vector.addElement(rec_vector);
      }
      tm.fireTableStructureChanged();
      lastQuery = true;                              //数据库查询操作成功
    }
    catch(SQLException ex){                          //捕捉异常
      System.out.println("\nERROR:----- SQLException -----\n");
      while (ex != null) {
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
        System.out.println("ErrorCode: " + ex.getErrorCode());
        ex = ex.getNextException();
      }
    }
    catch(Exception ex ) {
      ex.printStackTrace();
    }
    finally {
      try {
        if(statement != null)
        {
          statement.close();                      //关闭Statement接口实例
        }
        if(connection != null)
        {
          connection.close();                   //关闭Connection接口实例
        }
      }
      catch (SQLException ex)  {
        System.out.println("\nERROR:----- SQLException -----\n");
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
        System.out.println("ErrorCode: " + ex.getErrorCode());
      }
    }
  }
}