JDBC: 数据库基本操作演示程序

来源：互联网发布：罗伯特森生涯数据编辑：程序博客网时间：2024/04/29 12:46

/** @author：毛毛

JDBC中操作数据库的一些基本操作演示程序。GUI设计界面是随意的。

这里数据库是Access表，数据库名：Data, 表名：Student。

package AccessSQL;

import java.awt.*;
import java.sql.*;
import java.awt.event.*;

import javax.swing.*;

/*
* 数据库操作，插入，删除，查询等
*/
public class ModifyDisplay extends JFrame
implements ActionListener{

//定义各个组件

JLabel lbRow = new JLabel("行数");
JTextField tfRow = new JTextField();

JLabel lbID = new JLabel("学号");
JLabel lbName = new JLabel("姓名");
JLabel lbAge = new JLabel("年龄");
JLabel lbPro = new JLabel("专业");

JTextField tfID = new JTextField();
JTextField tfName = new JTextField();
JTextField tfAge = new JTextField();
JTextField tfPro = new JTextField();

JButton btnFind = new JButton("查询");
JButton btnDel = new JButton("删除");
JButton btnAll = new JButton("全部");
JButton btnInsert = new JButton("插入");

JLabel lPro = new JLabel("专业");
JTextField tPro = new JTextField();
JButton btnFind2 = new JButton("按专业查询");

JPanel panel = new JPanel();
JTextArea taInfo = new JTextArea();
private Container contrainer = getContentPane();

public ModifyDisplay()
{
  super("学生信息查询");
  setSize(400, 300);
  this.setBounds(100, 100, 500, 600);
}

//设置容器面板的布局
public void setLayout()
{

  panel.setLayout(null);

   //在面板panel中相对panel, 其中lbRow:left=0 top=10 width=30 height=25
  lbRow.setBounds(0, 10, 30, 25);
  tfRow.setBounds(40, 10, 70, 25);
  btnFind.setBounds(130, 10, 70, 25);
  btnDel.setBounds(210, 10, 70, 25);
  btnAll.setBounds(290, 10, 70, 25);

  lbID.setBounds(10, 50, 60, 25);
  lbName.setBounds(75, 50, 60, 25);
  lbAge.setBounds(140, 50, 60, 25);
  lbPro.setBounds(210, 50, 60, 25);

  tfID.setBounds(0, 80, 60, 25);
  tfName.setBounds(65, 80, 60, 25);
  tfAge.setBounds(130, 80, 60, 25);
  tfPro.setBounds(200, 80, 60, 25);
  btnInsert.setBounds(275, 80, 70, 25);

  lPro.setBounds(0, 120, 30, 25);
  tPro.setBounds(40, 120, 90, 25);
  btnFind2.setBounds(160, 120, 100, 25);

  panel.add(lbRow);
  panel.add(tfRow);
  panel.add(btnFind);
  panel.add(btnDel);
  panel.add(btnAll);

  panel.add(lbID);
  panel.add(lbName);
  panel.add(lbAge);
  panel.add(lbPro);

  panel.add(tfID);
  panel.add(tfName);
  panel.add(tfAge);
  panel.add(tfPro);
  panel.add(btnInsert);

  panel.add(lPro);
  panel.add(tPro);
  panel.add(btnFind2);

//注册监听器
  btnFind.addActionListener(this);
  btnDel.addActionListener(this);
  btnAll.addActionListener(this);
  btnInsert.addActionListener(this);
  btnFind2.addActionListener(this);

  getContentPane().setLayout(null);

  //panel: left=20 top=10 width=370 height=110
  panel.setBounds(20, 10, 370, 150);
  taInfo.setBounds(20, 160, 370, 380);

  getContentPane().add(panel);
  getContentPane().add(taInfo);

}

public void GetRecord(int Row, int type, String ID, String Name,
   String Age, String Pro) throws SQLException{

  String URL,SQL;
  Connection con = null;
  try
  {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  }
  catch(ClassNotFoundException ex)
  {
      taInfo.setText(ex.getMessage());
      System.out.println(-1);
  }

  try{

//连接数据库, 向数据库发送执行请求
   URL = "jdbc:odbc:Data";
   con = DriverManager.getConnection(URL);
   Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
      ResultSet.CONCUR_UPDATABLE);
   SQL = "SELECT ID,姓名,age,专业 FROM Student";
   ResultSet rs = stmt.executeQuery(SQL);

   taInfo.setText("");
   taInfo.append("学号/t姓名/t年龄/t专业/n/n");
   if(type == 0) //全部查询
   {
    rs.beforeFirst();
    while(rs.next())
    {
     taInfo.append(rs.getInt("ID") + "/t");
     taInfo.append(rs.getString("姓名") + "/t");
     taInfo.append(rs.getInt("age") + "/t");
     taInfo.append(rs.getString("专业") + "/n");
    }
   }
   else if(type == 1) //按行查询
   {
    rs.absolute(Row);
    taInfo.append(rs.getInt("ID") + "/t");
    taInfo.append(rs.getString("姓名") + "/t");
    taInfo.append(rs.getInt("age") + "/t");
    taInfo.append(rs.getString("专业") + "/n");
   }
   else if(type == 2) //删除

   {
      rs.absolute(Row);
       rs.deleteRow();
       rs.updateRow();

    rs.beforeFirst();
    while(rs.next())
    {
     taInfo.append(rs.getInt("ID") + "/t");
     taInfo.append(rs.getString("姓名") + "/t");
     taInfo.append(rs.getInt("age") + "/t");
     taInfo.append(rs.getString("专业") + "/n");
    }
   }
   else if(type == 3) //插入
   {
       rs.last();
       rs.moveToInsertRow();
       int id = Integer.parseInt(ID);
      int age = Integer.parseInt(Age);

    rs.updateInt("ID", id);
    rs.updateString("姓名", Name);
    rs.updateInt("age", age);
    rs.updateString("专业", Pro);

    rs.insertRow();
    rs.updateRow();

    rs.beforeFirst();
    while(rs.next())
    {
     taInfo.append(rs.getInt("ID") + "/t");
     taInfo.append(rs.getString("姓名") + "/t");
     taInfo.append(rs.getInt("age") + "/t");
     taInfo.append(rs.getString("专业") + "/n");
    }
   }
   else if(type == 4) //按专业查询
   {
    String sql = "SELECT ID,姓名,age,专业 FROM Student"+" WHERE 专业=?";
    PreparedStatement preSt = con.prepareStatement(sql);
    preSt.setString(1,Pro);
    ResultSet rs1 = preSt.executeQuery();

    while(rs1.next())
    {
     taInfo.append(rs1.getInt("ID") + "/t");
     taInfo.append(rs1.getString("姓名") + "/t");
     taInfo.append(rs1.getInt("age") + "/t");
     taInfo.append(rs1.getString("专业") + "/n");
    }

    rs1.close();
    preSt.close();
   }
   contrainer.add(taInfo);

   rs.close();
   stmt.close();
  }
   catch(SQLException ex){
    taInfo.setText(ex.getMessage());
   }
   finally{
    con.close();
   }
}

public static void main(String[] args) {

  ModifyDisplay frame = new ModifyDisplay();
  frame.setLayout();
  frame.show();

}

@Override // 按钮事件处理
public void actionPerformed(ActionEvent e) {

  /*
   * type值含义
   * 0:全部查询 , 1:按行查询 , 2:按行删除 , 3:按行插入, 4:按专业查询
  */
  if(e.getSource() == btnAll)
  {
   int type = 0;
   try{
    GetRecord(0, type, null,null,null,null);

   }catch(SQLException ex){

    taInfo.setText(ex.getMessage());
    taInfo.setForeground(Color.red);
   }
  }
  else if(e.getSource() == btnFind)
  {
   int type;
   String strRow = tfRow.getText();
   char ch;
   boolean flag = false;

   //判断输入的数据是否全是数字
   for(int i=0; i<strRow.length(); i++)
   {
    ch = strRow.charAt(i);
    if(Character.isDigit(ch))
     flag = true;
    else
    {
     flag = false;
        break;
    }
   }

   if(flag)
   {
       type = 1;
    int intRow = Integer.parseInt(strRow);

    try{
     GetRecord(intRow,type,null, null,null,null);
    }
    catch(SQLException ex){
     taInfo.setText(ex.getMessage());
    }
   }
   else
   {
    taInfo.setText("Please only input number!");
    taInfo.setForeground(Color.red);
   }

  }
  else if(e.getSource() == btnDel)
  {
   int type;
   String strRow = tfRow.getText();
   char ch;
   boolean flag = false;

   //判断输入的数据是否全是数字
   for(int i=0; i<strRow.length(); i++)
   {
    ch = strRow.charAt(i);
    if(Character.isDigit(ch))
     flag = true;
    else
    {
     flag = false;
        break;
    }
   }

   if(flag)
   {
       type = 2;
    int intRow = Integer.parseInt(strRow);

    try{
     GetRecord(intRow,type,null,null,null,null);
    }
    catch(SQLException ex){

     taInfo.setText(ex.getMessage());
     taInfo.setForeground(Color.red);
    }
   }
   else
   {
    taInfo.setText("Please only input number!");
    taInfo.setForeground(Color.red);
   }

  }
  else if(e.getSource() == btnInsert)
  {
   String strID = tfID.getText();
   String strName = tfName.getText();
   String strAge = tfAge.getText();
   String strPro = tfPro.getText();

   int type = 3;
   char ch1,ch2;
   boolean flag1=false, flag2=false;

   for(int i=0; i<strID.length(); i++)
   {
    ch1 = strID.charAt(i);
    if(Character.isDigit(ch1))
     flag1 = true;
    else
    {
     flag1 = false;
        break;
    }
   }
   for(int i=0; i<strAge.length(); i++)
   {
    ch2 = strAge.charAt(i);
    if(Character.isDigit(ch2))
     flag2 = true;
    else
    {
     flag2 = false;
        break;
    }
   }
   if(flag1&&flag2)
   {
    try{
     GetRecord(0,type,strID,strName,strAge,strPro);
    }
    catch(SQLException ex){
     taInfo.setText(ex.getMessage());
     taInfo.setForeground(Color.red);
    }
   }
   else
   {
    taInfo.setText("Please input right(correct) student's information!");
    taInfo.setForeground(Color.red);
   }

  }
  else if(e.getSource() == btnFind2)
  {
   int type = 4;
   String strPro = tPro.getText();

   try{
    GetRecord(0, type, null,null,null,strPro);

   }catch(SQLException ex){
    taInfo.setText(ex.getMessage());
    taInfo.setForeground(Color.red);
   }
  }
}
}