JDBC+Access的常用操作

创建名为DIRECTORY的Access数据库,并在ODBC中配置数据源名为DIRECTORY:

本例中使用到的两个表: User_Group,Directory
create table User_Group
(
   Group_ID  counter primary key,
   Group_Name varchar(30) not null unique
)

create table Directory  //
(
  Name varchar(25) not null primary key,
  NameAbbreviate varchar(25) not null,
  Telephone varchar(12) ,
  Mobilephone char(11),
  GroupID integer  references User_Group(Group_ID) not null,
  Photo OLEObject not null,
  Remark varchar(100)
)
1.使用JDBC连接Access数据库
package MyConnection;
import javax.swing.*;
import java.sql.*;
public class MyCon
 {
    public Connection con;
    public MyCon()
    {
      try
       {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       }
       catch(java.lang.ClassNotFoundException e)
       {
        JOptionPane.showMessageDialog(null,e.getMessage(),"错误",
                                             JOptionPane.ERROR_MESSAGE);
       }
       try
        {
          con=DriverManager.getConnection("jdbc:odbc:DIRECTORY","",null);
        }
       catch(SQLException sqle)
        {
              JOptionPane.showMessageDialog(null,sqle.getMessage()," 异常",
                                             JOptionPane.ERROR_MESSAGE);
        }
    }
 }

2.数据库操作
(1)查询:将数据库中组名读到choicegroup下拉列表框中
       try
        {
           MyCon mycon=new MyCon();
           Statement stmt=mycon.con.createStatement();
           ResultSet rs=stmt.executeQuery("select * from User_Group");

           choicegroup.add("请选择分组");
           while(rs.next())
            {
               choicegroup.add(rs.getString("Group_Name"));//choicegroup为组名列表框
            }
           stmt.close();
           mycon.con.close();
        }
       catch(SQLException sqle)
         {            
            JOptionPane.showMessageDialog(null,sqle.getMessage(),"异常",JOptionPane.ERROR_MESSAGE);
         }
(2)添加:通过输入对话框输入新组名添加到grouplist列表中
   try
    {
      MyCon mycon=new MyCon();
      Statement stmt=mycon.con.createStateme(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
      String groupname="",sqlcommand="";
      groupname=JOptionPane.showInputDialog(null,"请输入组名:","新建组",JOptionPane.PLAIN_MESSAGE);
      if(groupname!=null)//单击确定按钮
       {
          groupname=groupname.trim();///去除前后空格
          if(groupname.equals(""))//输入为空,单击确定按钮,去除前后空格
          {
            JOptionPane.showMessageDialog(null,"组名不能为空!","错误",JOptionPane.ERROR_MESSAGE);
            }
            else //组名不为空
            { 
              sqlcommand="select Group_Name from User_Group where Group_Name='"+groupname+"'";
              ResultSet rs=stmt.executeQuery(sqlcommand);
              rs.last();
              if(rs.getRow()>0)
                 {
                   JOptionPane.showMessageDialog(null,"该组名已存在，不能重复添加!","错误",JOptionPane.ERROR_MESSAGE);
                 }
              else
                 {
                   sqlcommand="insert into User_Group(Group_Name) values('"+groupname+"')";
                   stmt.executeUpdate(sqlcommand);
                   JOptionPane.showMessageDialog(null,"新组[ "+groupname+" ]添加成功!","提示",JOptionPane.INFORMATION_MESSAGE);
                   UpdateGroupList();
                 }
            }//组名不为空
          }//if单击确定按钮
          stmt.close();
          mycon.con.close();
        }//try
       catch(SQLException sqle)
        {
           JOptionPane.showMessageDialog(null,sqle.getMessage(),"异常",JOptionPane.INFORMATION_MESSAGE);
        }
(3)修改:双击grouplist中的组名,弹出一输入对话框,输入新组名进行重命名
  try
    {
       MyCon mycon=new MyCon();
       Statement stmt=mycon.con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
      String oldgroupname="",newgroupname="",sqlcommand="";
      int groupid=0;
       int index =grouplist.locationToIndex(e.getPoint());//获得被双击的列表项的索引
      oldgroupname=(String)grouplist.getModel().getElementAt(index);//获得被双击列表项的组名
      ResultSet rs=stmt.executeQuery("select Group_ID from User_Group where Group_Name='"+oldgroupname+"'");
               while(rs.next())
                {
                  groupid=rs.getInt("Group_ID");//读出被双击的组的ID
                }
               newgroupname=JOptionPane.showInputDialog(null,"请输入新组名:","重命名组",JOptionPane.PLAIN_MESSAGE);//去除前后空格
               if(newgroupname!=null)//单击确定按钮
                {
                   newgroupname=newgroupname.trim();
                   if(newgroupname.equals(""))//输入为空,单击确定按钮
                    {
                      JOptionPane.showMessageDialog(null,"不能为空!","错误",JOptionPane.ERROR_MESSAGE);
                    }
                   else//输入不为空
                   {
                     sqlcommand="select * from User_Group where Group_ID <>"+groupid+" and Group_Name='"+newgroupname+"'";
                     rs=stmt.executeQuery(sqlcommand);
                     rs.last();
                     if(rs.getRow()>0)
                     {
                       JOptionPane.showMessageDialog(null,"组[ "+newgroupname+" ]已存在，组名不能重复!","错误",JOptionPane.ERROR_MESSAGE);
                     }
                     else
                     {
                       sqlcommand="update User_Group set Group_Name='"+newgroupname+"' where Group_ID="+groupid;
                       stmt.executeUpdate(sqlcommand);
                       JOptionPane.showMessageDialog(null,"组[ "+oldgroupname+" ]已成功修改为组[ "+newgroupname+" ]!","提示",JOptionPane.INFORMATION_MESSAGE);
                       UpdateGroupList();
                     }
                   }//输入不为空
                 }//if单击确定按钮
             stmt.close();
             mycon.con.close();
           }//try
          catch(SQLException sqle)
           {
             JOptionPane.showMessageDialog(null,sqle.getMessage(),"异常",JOptionPane.INFORMATION_MESSAGE);
           }
(4)删除:在grouplist列表中选中要删除的列表项进行删除

int i=0,groupid=0,result=0;
String deletecommand="",groupname="";
result=JOptionPane.showConfirmDialog(this,"删除组时也将删除组内的成员,你确定要删除组吗?","警告",JOptionPane.OK_CANCEL_OPTION,JOptionPane.WARNING_MESSAGE);
if(result==JOptionPane.OK_OPTION)//单击确定按钮
    {
      try
      {
        MyCon mycon=new MyCon();
        Statement stmt=mycon.con.createStatement();
        for(i=0;i<SelectedGroupIndex.length;i++)
         {
               groupname=(String)grouplist.getModel().getElementAt(SelectedGroupIndex[i]);//获得选中的列表项名称
               ResultSet rs= stmt.executeQuery("select Group_ID from User_Group where    Group_Name='"+groupname+"'");
                   while(rs.next())
                    {
                      groupid=rs.getInt("Group_ID");//查找到该组的ID
                    }
                   deletecommand="delete from Directory where GroupID="+groupid;
                   stmt.executeUpdate(deletecommand);//先删除该组内的成员
                   deletecommand="delete from User_Group where Group_ID="+groupid;
                   stmt.executeUpdate(deletecommand);//再删除组
                }
                stmt.close();
                mycon.con.close();
                JOptionPane.showMessageDialog(this,"成功删除组[ "+groupname+" ]!","删除组成功",JOptionPane.INFORMATION_MESSAGE);
                UpdateGroupList();//更新组列表
             }
            catch(SQLException sqle)
             {
                JOptionPane.showMessageDialog(this,"产生异常:"+sqle.getMessage(),"异常",JOptionPane.ERROR_MESSAGE);
             }
         }//if