利用JDBC开发图书管理系统

1、删除功能

void B7_mouseClicked(MouseEvent e) ——“删除”按钮

2、新增功能

void B5_mouseClicked(MouseEvent e)——“新增”按钮

void T1_actionPerformed(ActionEvent e)

void B9_mouseClicked(MouseEvent e)——“确定”按钮

void ToInsert()

void B10_mouseClicked(MouseEvent e)——“离开”按钮

3、修改功能

void B6_mouseClicked(MouseEvent e)——“修改”按钮

void B9_mouseClicked(MouseEvent e)——“确定”按钮

void ToUpdate()

void B10_mouseClicked(MouseEvent e)——“离开”按钮

4、查询功能

void B8_mouseClicked(MouseEvent e)——“查询”按钮

void B9_mouseClicked(MouseEvent e)——“确定”按钮

void ToSelect()

void B10_mouseClicked(MouseEvent e)——“离开”按钮

5、控制画面操作过程

void paintView()

T2、T3、T4组件的addActionListener()

自定义类class myActionListener

 

/*
 * 本例的新增、修改、查询都要配合同一个确定和离开按钮、故本例定义了一个workingMode变量
 * 来分辨当时的状态。而且单击离开按钮时，要回到之前所浏览的那一条数据，所以还定义了一个
 * CurrentRow变量
 */

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

public class MyBook extends JFrame{
    Connection ConnObj;
    Statement SQLStatement;
    ResultSet RS;
    int CurrentRow=1;
    static final int BROWSE=0;
    static final int PREINSERT=1;
    static final int TOINSERT=2;
    static final int TOUPDATE=3;
    static final int TOSELECT=4;
    static final int SELECTED=5;
    int workingMode=MyBook.BROWSE;
    
    //========================================
    MyBook frame;
    JPanel contentPane;
    Panel panel1;
    Label L1,L2,L3,L4,L5;
    TextField T1,T2,T3,T4;
    Button B1,B2,B3,B4,B5,B6,B7,B8,B9,B10;

    public MyBook() {
        frame=this;
        contentPane=(JPanel)this.getContentPane();
        contentPane.setLayout(null);
        //=============================================
        panel1=new Panel();
        panel1.setBounds(0, 0, 380, 160);
        panel1.setLayout(null);
        contentPane.add(panel1,null);

        L1=new Label("状态： 浏览模式");
        L1.setBounds(20, 10, 350, 25);
        panel1.add(L1, null);
        L2=new Label("ISBN:");
        L2.setBounds(20, 40, 50, 25);
        panel1.add(L2,null);
        T1=new TextField();
        T1.setBounds(70, 40, 300, 25);
        T1.addActionListener(new ActionListener()
        {//按【Enter】键
            public void actionPerformed(ActionEvent e)
            {//在T1按【Enter】键会触发此事件
                T1_actionPerformed(e);
            }
        });
        panel1.add(T1,null);
        L3=new Label("书名：");
        L3.setBounds(20, 70, 50, 25);
        panel1.add(L3,null);
        L4=new Label("作者：");
        L4.setBounds(20, 100, 50, 25);
        panel1.add(L4,null);
        L5=new Label("价格：");
        L5.setBounds(20, 130, 50, 25);
        panel1.add(L5,null);

        T2=new TextField();
        T2.setBounds(70, 70, 300, 25);
        T2.addActionListener(new MyActionListener());//按【Enter】键
        panel1.add(T2,null);
        T3=new TextField();
        T3.setBounds(70, 100, 300, 25);
        T3.addActionListener(new MyActionListener());//按【Enter】键
        panel1.add(T3,null);
        T4=new TextField();
        T4.setBounds(70, 130, 300, 25);
        T4.addActionListener(new MyActionListener());//按【Enter】键
        panel1.add(T4,null);
        //==========================================
        B1=new Button("第一条");
        B1.setBounds(new Rectangle(30, 180, 65, 20));
        B1.addMouseListener(new MouseAdapter()
        {
            @Override
            public void mouseClicked(MouseEvent e) {
                B1_mouseClicked(e);//程序代码较多，写在另一个方法中
            }
        });
        contentPane.add(B1);

        B2=new Button("上一条");
        B2.setBounds(100, 180, 65, 20);
        B2.addMouseListener(new MouseAdapter()
        {
            @Override
            public void mouseClicked(MouseEvent e) {
                B2_mouseClicked(e);
            }
        });
        contentPane.add(B2);

        B3=new Button("下一条");
        B3.setBounds(170, 180, 65, 20);
        B3.addMouseListener(new MouseAdapter()
        {
            @Override
            public void mouseClicked(MouseEvent e) {
                B3_mouseClicked(e);
            }
        });
        contentPane.add(B3);

        B4=new Button("最末条");
        B4.setBounds(240, 180, 65, 20);
        B4.addMouseListener(new MouseAdapter()
        {
            @Override
            public void mouseClicked(MouseEvent e) {
                B4_mouseClicked(e);
            }
        });
        contentPane.add(B4);
        B5=new Button("新增");
        B5.setBounds(30, 210, 65, 20);
        B5.addMouseListener(new MouseAdapter()
        {
            public void mouseClicked(MouseEvent e)
            {
                B5_mouseClicked(e);
            }
        });
        contentPane.add(B5);
        B6=new Button("修改");
        B6.setBounds(100, 210, 65, 20);
        B6.addMouseListener(new MouseAdapter()
        {
            public void mouseClicked(MouseEvent e)
            {
                B6_mouseClicked(e);
            }
        });
        contentPane.add(B6);
        B7=new Button("删除");
        B7.setBounds(170, 210, 65, 20);
        B7.addMouseListener(new MouseAdapter()
        {
            public void mouseClicked(MouseEvent e)
            {
                B7_mouseClicked(e);
            }
        });
        contentPane.add(B7);
        B8=new Button("查询");
        B8.setBounds(240, 210, 65, 20);
        B8.addMouseListener(new MouseAdapter()
        {
            public void mouseClicked(MouseEvent e)
            {
                B8_mouseClicked(e);
            }
        });
        contentPane.add(B8);
        B9=new Button("确定");
        B9.setBounds(310, 180, 65, 20);
        B9.addMouseListener(new MouseAdapter()
        {
            public void mouseClicked(MouseEvent e)
            {
                B9_mouseClicked(e);
            }
        });
        contentPane.add(B9);
        B10=new Button("离开");
        B10.setBounds(310, 210, 65, 20);
        B10.addMouseListener(new MouseAdapter()
        {
            public void mouseClicked(MouseEvent e)
            {
                B10_mouseClicked(e);
            }
        });
        contentPane.add(B10);
        //==============================================
        //控制画面操作过程
        panel1.setEnabled(false);    //其内的 TextField 将无法编辑文字
        B9.setEnabled(false);
        B10.setEnabled(false);
        //==============================================
        this.setBounds(200, 100, 415, 280);
        this.setTitle("图书管理系统");
        //this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        this.addWindowListener(new WindowAdapter()
        {
            @Override
            public void windowClosing(WindowEvent e) {
                System.exit(0);
            }
        });
        this.setVisible(true);
        //=========================================================
        //加载JDBC Driver、连接数据库、送SELECT语句、返回结果集
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            //载入JDBC Driver
            ConnObj=DriverManager.getConnection("jdbc:odbc:myBook");
            //连接ODBC设置的myBook数据源，jdbc:subprotocol:Alias
            SQLStatement=ConnObj.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //其后由此Statement对象执行SQL指令时，返回的会是可卷动且只读的ResultSet对象
            RS=SQLStatement.executeQuery("select * from BookData");
            //执行SELECT语句，会返回一个结果集
            //查询myBook数据源的BookData数据表全部字段的记录
            
            //必须先向下移到第一条：next(),因为一开始是指到第一条之前
            //第一次调用 next 方法使第一行成为当前行
            if(RS.next())
            {
                //RS内至少有一条记录，才会返回true
                T1.setText(RS.getString("ISBN"));
                T2.setText(RS.getString("BookName"));
                T3.setText(RS.getString("Author"));
                T4.setText(String.valueOf(RS.getInt("Price")));
            }
            else
                JOptionPane.showMessageDialog(this, "myBook数据库内，无任何数据");
        }
        catch(Exception ex)
        {
            JOptionPane.showMessageDialog(frame, ex.getMessage());
        }
    }

    class MyActionListener implements ActionListener
    {    //T2、T3、T4都加入此ActionListener
        public void actionPerformed(ActionEvent e)
        {
            ((TextField)e.getSource()).transferFocus();//按【Enter】键,focus往下一个组件
        }
    }
    
    void B1_mouseClicked(MouseEvent e)
    {
        try
        {
            RS.first();
            T1.setText(RS.getString("ISBN"));
            T2.setText(RS.getString("BookName"));
            T3.setText(RS.getString("Author"));
            T4.setText(String.valueOf(RS.getInt("Price")));
        }
        catch(SQLException ex){}
    }

    void B2_mouseClicked(MouseEvent e)
    {
        try
        {
            if(RS.previous())
            {
                T1.setText(RS.getString("ISBN"));
                T2.setText(RS.getString("BookName"));
                T3.setText(RS.getString("Author"));
                T4.setText(String.valueOf(RS.getInt("Price")));
            }
            else
            {
                RS.first();
                JOptionPane.showMessageDialog(B2,"已到第一条");
            }
        }
        catch(SQLException ex){}
    }
    
    void B3_mouseClicked(MouseEvent e)
    {
        try
        {
            if(RS.next())
            {
                T1.setText(RS.getString("ISBN"));
                T2.setText(RS.getString("BookName"));
                T3.setText(RS.getString("Author"));
                T4.setText(String.valueOf(RS.getInt("Price")));
            }
            else
            {
                RS.last();
                JOptionPane.showMessageDialog(B3, "已到最末条");
            }
        }
        catch(SQLException ex){}
    }
    
    void B4_mouseClicked(MouseEvent e)
    {
        try
        {
            RS.last();
            T1.setText(RS.getString("ISBN"));
            T2.setText(RS.getString("BookName"));
            T3.setText(RS.getString("Author"));
            T4.setText(String.valueOf(RS.getInt("Price")));
        }
        catch(SQLException ex){}
    }
    
    void B5_mouseClicked(MouseEvent e)
    {    //新增一条数据
        try
        {
            CurrentRow=RS.getRow();    //进行新增之前指到的这一条
        }
        catch(SQLException ex)
        {
            JOptionPane.showMessageDialog(B5, ex.getMessage());
        }
        workingMode=MyBook.PREINSERT;
        paintView();
        T1.setText("");        //清空所有 TextField
        T2.setText("");
        T3.setText("");
        T4.setText("");
        T1.requestFocus();    //光标进入到 T1
    }
    
    void B6_mouseClicked(MouseEvent e)
    {
        workingMode=MyBook.TOUPDATE;
        paintView();
        try
        {
            CurrentRow=RS.getRow();        //现在更新这条的位置
        }
        catch(SQLException ex)
        {
            JOptionPane.showMessageDialog(B6, ex.getMessage());
        }
        T2.requestFocus();
    }
    
    void B7_mouseClicked(MouseEvent e)
    {    //删除一条数据
        try
        {
            //记删除一条后要指到的位置
            if(RS.isLast()&&(!RS.isFirst()))
            {
                //若删除是：最末条且非第一条
                CurrentRow=RS.getRow()-1;    //删除后要移到它的前一条
            }
            else
                CurrentRow=RS.getRow();        //包括：不是最末条、是最末条又正是第一条
            int choice=JOptionPane.showConfirmDialog(frame, "确定要删除现在这一条吗？");
            if(choice==JOptionPane.YES_OPTION)    //若用户单击“是(Y)”按钮
            {
                String deleteSQL="delete from BookData where ISBN='"+T1.getText().trim()+"'";
                //System.out.println(deleteSQL);    //可用于测试时
                SQLStatement.executeUpdate(deleteSQL);    //对数据库进行删除操作
                RS=SQLStatement.executeQuery("select * from BookData");
                if(RS.absolute(CurrentRow))
                {    //移到刚才指的位置，若是删除最末条，会移到它的前一条
                    T1.setText(RS.getString("ISBN"));
                    T2.setText(RS.getString("BookName"));
                    T3.setText(RS.getString("Author"));
                    T4.setText(String.valueOf(RS.getInt("Price")));
                }
                else
                {
                    T1.setText("");
                    T2.setText("");
                    T3.setText("");
                    T4.setText("");
                }
            }
        }
        catch(Exception ex)
        {
            JOptionPane.showMessageDialog(B7, ex.getMessage());
        }
    }
    
    void B8_mouseClicked(MouseEvent e)
    {    //查询数据
        workingMode=MyBook.TOSELECT;
        paintView();
        try
        {
            CurrentRow=RS.getRow();    //目前指到的这一条
        }
        catch(SQLException ex)
        {
            JOptionPane.showMessageDialog(B8, ex.getMessage());
        }
    }
    
    void B9_mouseClicked(MouseEvent e)
    {    //确定操作，用于新增、修改、查询
        switch(workingMode)
        {
        case MyBook.TOINSERT:
            ToInsert();        //确定要进行新增的操作
            break;
        case MyBook.TOUPDATE:
            ToUpdate();        //确定要进行修改的操作
            break;
        case MyBook.TOSELECT:
            ToSelect();        //确定要进行查询的操作
            break;
        default:
            JOptionPane.showMessageDialog(B9, "目的不明！");
        }
        paintView();    //控制画面
    }
    //============================================================
    /*用户单击“离开”按钮的情况:
     * 1.进入新增模式，但不要新增
     * 2.进入修改模式，但不要修改
     * 3.进入查询模式，但不要查询，或是已经查询过了     
     */
    void B10_mouseClicked(MouseEvent e)
    {    //离开新增、修改、查询模式
        try
        {
            RS=SQLStatement.executeQuery("select * from BookData");
            if(RS.absolute(CurrentRow))
            {
                T1.setText(RS.getString("ISBN"));
                T2.setText(RS.getString("BookName"));
                T3.setText(RS.getString("Author"));
                T4.setText(String.valueOf(RS.getInt("Price")));
            }
        }
        catch(SQLException ex)
        {
            JOptionPane.showMessageDialog(B10, ex.getMessage());
        }
        //无论对数据库的新增、修改、查询操作是否成功
        workingMode=MyBook.BROWSE;
        CurrentRow=1;
        paintView();    //控制画面        
    }
    
    void T1_actionPerformed(ActionEvent e)
    {    //在T1内按【Enter】键会调用此方法
        if(workingMode!=MyBook.PREINSERT)
        {    //TOINSERT、TOUPDATE、TOSELECT     状态才有可能
            T1.transferFocus();    //Focus移到下一个组件
            return;
        }
        else
        {    //PREINSERT 状态
            if(T1.getText().trim().equals(""))
            {
                T1.requestFocus();    //令T1取得Focus
                return;
            }
        }
        
        String newISBN=T1.getText().trim();
        if(newISBN.length()>13)
        {    //数据不要超过数据表设置的字段长度
            JOptionPane.showMessageDialog(T1, "ISBN 字段最大长度为 13！");
            return;            
        }
        //新增时先检查该条是否已存在
        try
        {
            RS=SQLStatement.executeQuery("select * from BookData where ISBN='"+newISBN+"'");
            if(RS.next())
            {    //显示已存在的该条数据
                T1.setText(RS.getString("ISBN"));
                T2.setText(RS.getString("BookName"));
                T3.setText(RS.getString("Author"));
                T4.setText(String.valueOf(RS.getInt("Price")));
                JOptionPane.showMessageDialog(T1, "此条记录已存在");
            }
            else
            {
                workingMode=MyBook.TOINSERT;    //不得再更改新的一条ISBN
                T1.setText(newISBN);
            }
        }
        catch(SQLException ex)
        {
            JOptionPane.showMessageDialog(T1, ex.getMessage());
        }
        paintView();    //控制画面
        T1.transferFocus();    //Focus移到下个组件        
    }
    
    void ToInsert()
    {    //实际进行新增
        int checkPrice;    //用来检查价格
        int insertCount=0;
        boolean ifInserted=false;    //记录是否成功新增
        if(T2.getText().trim().equals("")
                ||T3.getText().trim().equals("")
                ||T4.getText().trim().equals("")
                ||T2.getText().trim().length()>50
                ||T3.getText().trim().length()>30)
        {
            JOptionPane.showMessageDialog(B9, "有数据尚未填写正确的值！");
            return;
        }
        try
        {
            checkPrice=Integer.parseInt(T4.getText().trim());
        }
        catch(NumberFormatException ex)
        {
            JOptionPane.showMessageDialog(B9, "价格请填入整数，最大为2147483647");
            return;
        }
        //========================================================================
        String insertSQL="insert into BookData(ISBN,BookName,Author,Price)"
            +"values('"+T1.getText().trim()
            +"','"+T2.getText().trim()
            +"','"+T3.getText().trim()
            +"',"+T4.getText().trim()+")";
        try
        {
            //System.out.println(insertSQL);    //可用于测试时
            insertCount=SQLStatement.executeUpdate(insertSQL);
        }
        catch(SQLException ex)
        {
            JOptionPane.showMessageDialog(B9, ex.getMessage());
        }
        
        try
        {
            RS=SQLStatement.executeQuery("select * from BookData");
            if(insertCount!=0)
            {
                System.out.println("Insert OK");    //可用于测试时
                RS.last();    //若新增成功，就移到最末条
            }
            else
                RS.absolute(CurrentRow);//若失败则移到刚才浏览时指的那条
            
            T1.setText(RS.getString("ISBN"));
            T2.setText(RS.getString("BookName"));
            T3.setText(RS.getString("Author"));
            T4.setText(String.valueOf(RS.getInt("Price")));
        }
        catch(SQLException ex)
        {
            JOptionPane.showMessageDialog(B9, ex.getMessage());
        }
        //无论对数据库的新增操作是否成功
        workingMode=MyBook.BROWSE;
        CurrentRow=1;
    }
    
    void ToUpdate()
    {    //实际进行修改
        int checkPrice;    //用来检查价格
        if(T2.getText().trim().equals("")
                ||T3.getText().trim().equals("")
                ||T4.getText().trim().equals("")
                ||T2.getText().trim().length()>50
                ||T3.getText().trim().length()>30)
        {
            JOptionPane.showMessageDialog(null, "有数据尚未填写正确的值！");
            return;
        }
        try
        {
            checkPrice=Integer.parseInt(T4.getText().trim());
        }
        catch(NumberFormatException ex)
        {    //所填的文字，字面意义若非int整数，转型会失败
            JOptionPane.showMessageDialog(null, "价格请填入整数，最大为2147483647");
            return;
        }
        //==================================================
        String updateSQL="update BookData set BookName='"+T2.getText().trim()
        +"',Author='"+T3.getText().trim()
        +"',Price="+T4.getText().trim()
        +" where ISBN='"+T1.getText().trim()+"'";
        System.out.println(updateSQL);
        try
        {
            SQLStatement.executeUpdate(updateSQL);
        }
        catch(SQLException ex)
        {
            JOptionPane.showMessageDialog(B9, ex.getMessage());
        }
        try
        {
            RS=SQLStatement.executeQuery("select * from BookData");
            RS.absolute(CurrentRow);    //移到修改的那条
            T1.setText(RS.getString("ISBN"));
            T2.setText(RS.getString("BookName"));
            T3.setText(RS.getString("Author"));
            T4.setText(String.valueOf(RS.getInt("Price")));
        }
        catch(SQLException ex)
        {
            JOptionPane.showMessageDialog(B9, ex.getMessage());
        }
        //无论对数据库的修改操作是否成功
        workingMode=MyBook.BROWSE;
        CurrentRow=1;
    }
    
    void ToSelect()
    {    //实际进行查询
        String condi="";
        if(T1.getText().trim().equals("")
                &&T2.getText().trim().equals("")
                &&T3.getText().trim().equals("")
                &&T4.getText().trim().equals(""))
        {
            JOptionPane.showMessageDialog(B8, "至少要填一个字段当条件");
            return;
        }
        //=============================================================
        if(!T1.getText().trim().equals(""))    //若用户有填ISBN当条件
            condi=" ISBN='" + T1.getText().trim() + "'";
        
        if(!T2.getText().trim().equals(""))    //若用户有填BookName当条件
            if(condi.equals(""))
                condi=" BookName='" + T2.getText().trim() + "'";
            else
                condi=condi + " AND BookName='" + T2.getText().trim() + "'";
        
        if(!T3.getText().trim().equals(""))    //若用户有填Author当条件
            if(condi.equals(""))
                condi=" Author='" + T3.getText().trim() + "'";
            else
                condi=condi + " AND Author='" + T3.getText().trim() + "'";
        
        if(!T4.getText().trim().equals(""))    //若用户有填Price当条件
            if(condi.equals(""))
                condi=" Price=" + T4.getText().trim();
            else
                condi=condi + " AND Price=" + T4.getText().trim();
        
        String selectSQL="select * from BookData where" + condi;
        System.out.println(selectSQL);    //可用于测试时
        try
        {
            RS=SQLStatement.executeQuery(selectSQL);
            if(RS.next())
            {    //有查到一条以上符合的记录
                T1.setText(RS.getString("ISBN"));
                T2.setText(RS.getString("BookName"));
                T3.setText(RS.getString("Author"));
                T4.setText(String.valueOf(RS.getInt("Price")));
            }
            else
            {    //没查到符合的记录
                T1.setText("");
                T2.setText("");
                T3.setText("");
                T4.setText("");
            }
        }
        catch(SQLException ex)
        {
            JOptionPane.showMessageDialog(B9, ex.getMessage());
        }
        workingMode=MyBook.SELECTED;    //已查询
        //查完还要回到之前记录的CurrentRow
    }
    
    void paintView()
    {    //控制画面，即控制用户的操作过程
        switch(workingMode)
        {
        case MyBook.BROWSE:
            L1.setText("状态： 浏览模式");
            break;
        case MyBook.PREINSERT:
            L1.setText("状态： 新增模式 - 输入ISBN后按键盘【Enter】键");
            break;
        case MyBook.TOINSERT:
            L1.setText("状态： 新增模式");
            break;
        case MyBook.TOUPDATE:
            L1.setText("状态：修改模式");
            break;
        case MyBook.TOSELECT:
            L1.setText("状态： 查询模式");
            break;
        case MyBook.SELECTED:
            L1.setText("状态： 查询结果");
            break;
        }
        //============================================================
        if(workingMode==MyBook.TOINSERT||workingMode==MyBook.TOUPDATE)
            T1.setEditable(false);
        else
            T1.setEditable(true);
        //============================================================
        if(workingMode==MyBook.PREINSERT)
        {
            T2.setEnabled(false);
            T3.setEnabled(false);
            T4.setEnabled(false);
        }
        else
        {
            T2.setEnabled(true);
            T3.setEnabled(true);
            T4.setEnabled(true);
        }
        //=============================================================
        if(workingMode==MyBook.BROWSE||workingMode==MyBook.SELECTED)
        {
            panel1.setEnabled(false);    //此时光标不可进入其内的T1-T4
            B1.setEnabled(true);
            B2.setEnabled(true);
            B3.setEnabled(true);
            B4.setEnabled(true);
        }
        else
        {
            panel1.setEnabled(true);
            B1.setEnabled(false);
            B2.setEnabled(false);
            B3.setEnabled(false);
            B4.setEnabled(false);
        }
        //=============================================================
        if(workingMode==MyBook.BROWSE)
        {
            B5.setEnabled(true);
            B6.setEnabled(true);
            B7.setEnabled(true);
            B8.setEnabled(true);
        }
        else
        {
            B5.setEnabled(false);
            B6.setEnabled(false);
            B7.setEnabled(false);
            B8.setEnabled(false);
        }
        //==============================================================
        if(workingMode==MyBook.TOINSERT||workingMode==MyBook.TOUPDATE||workingMode==MyBook.TOSELECT)
            B9.setEnabled(true);
        else
            B9.setEnabled(false);
        //==============================================================
        if(workingMode!=MyBook.BROWSE)
            B10.setEnabled(true);
        else
            B10.setEnabled(false);
    }
    
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        // TODO code application logic here
        new MyBook();
    }

}