利用JDBC实现的一个基本的数据表维护界面

xiahousj

首先实现一个DBAccount类，完成数据库连接细节，这里使用的是mysql，你可以自行设计一个输入界面来获取用户输入的数据库连接参数：

class DBAccount{

    //数据库路径
    String theDBName;

    //用户名
    String theUserID;

    //帐户
    String thePasswd;

    DBAccount(){
        theDBName="jdbc:mysql://localhost/dbname";
        theUserID="root";
        thePasswd="";
    }

    /**设置数据库路径*/
    public void setDBName(String dbname){
        theDBName=dbname;
        return;
    }

    /**设置数据库用户名*/
    public void setUserID (String id){
        theUserID=id;
        return;
    }

    /**设置数据库用户密码*/
    public void setPasswd(String pwd){
        thePasswd=pwd;
        return;
    }
}

然后设计一个面板来显示相应的组件，包括一个下拉列表用于选择数据表，一个文本输入框用于输入待执行的SQL语句，一个Table用于显示查询、选择、管理后的数据集，几个管理数据库的按钮。

/**数据对象基类，负责各数据表的操作抽象*/

class DB extends JPanel implements ActionListener{

    //数据库帐户
    DBAccount theDBAccount;

    //数据库操作类
    DBServer theDBServer;

    //当前打开数据库的表信息
    Vector vectorOfDBTable;

    //显示当前数据集数据的表
    JTable tableOfDBTable;

    QueryTableModel dbTableModel;

    JTextArea textareaOfSQL;
    JButton buttonOfQueryOk;
    JButton buttonOfSetBlank;
    JComboBox comboboxOfDBTable;

    JButton buttonOfDBTableDataAdd;
    JButton buttonOfDBTableDataModify;
    JButton buttonOfDBTableDataDelete;
    JButton buttonOfDBTableDataRefresh;

    //保存当前数据表的列名称
    Vector vectorOfTableColumnName=new Vector(0);

    //保存当前数据表的数据
    Vector vectorOfTableData=new Vector(0);

    //DialogOfDataModify dlgOfDataModify;

    //放置数据记录添加相关的控件
    RecordAddPanel paneOfRecordAdd;

    /**构造函数*/
    DB(){
        theDBAccount=new DBAccount();
        //JOptionPane.showMessageDialog(null,    UIManager.getLookAndFeel(),"提示",JOptionPane.PLAIN_MESSAGE);
        getDBConnect();
        getTableData();
        setRecordAddPanelContent();
    }

    /**事件处理*/
    public void actionPerformed(ActionEvent e){



        //tableOfDBTable.setColumnSelectionAllowed(true);

        Object eventSource= e.getSource();

        //选择数据表
        if(eventSource==comboboxOfDBTable){
            getTableData();
            setRecordAddPanelContent();

            //theDBServer.dbModel.fireTableChanged(null);
            //tableOfDBTable.setVisible(false);
            //init(vectorOfDBTable);
        }

        //撤销查询语句
        else if(eventSource==buttonOfSetBlank){
            textareaOfSQL.setText("");
            getTableData();

        }

        //输入查询语句
        else if(eventSource==buttonOfQueryOk){
            String QuerySentence=textareaOfSQL.getText();
            if (QuerySentence.equals("")){
                JOptionPane.showMessageDialog(null,"您没有输入任何查询语句，请检查您的输入！",
                        "错误",JOptionPane.ERROR_MESSAGE);
                return;
            }
            if(!theDBServer.getQueryData(QuerySentence)){
                JOptionPane.showMessageDialog(null,"数据库操作出现错误，请检查您的输入！",
                        "错误",JOptionPane.ERROR_MESSAGE);
            }
            //getTableData();
            setRecordAddPanelContent();
        }

        //修改数据表记录
        else if(eventSource==buttonOfDBTableDataModify){

            dbTableDataModify();
            getTableData();
            setRecordAddPanelContent();

        }

        //添加记录
        else if(eventSource==buttonOfDBTableDataAdd){
            //recordDataAdd(comboboxOfDBTable.getSelectedItem().toString(),                );
            dbTableDataAdd();
            getTableData();
        }

        //删除记录
        else if(eventSource==buttonOfDBTableDataDelete){

            dbTableRowDelete();
            getTableData();

        }

    }

    /**设置数据字段添加面板的内容*/
    public void setRecordAddPanelContent(){
        int columnNum=tableOfDBTable.getModel().getColumnCount();
        String[] columnName=new String[columnNum];

        for (int i=0;i<columnNum;i++){
            columnName[i]=tableOfDBTable.getModel().getColumnName(i);

        }
        paneOfRecordAdd.setVisible(false);

        paneOfRecordAdd.setContent(columnName);
    }

    /**删除数据表记录*/
    public void dbTableRowDelete(){
        int xOfSelectedData=tableOfDBTable.getSelectedRow();

        if (xOfSelectedData>=0){

            String tableName=comboboxOfDBTable.getSelectedItem().toString();
            String keyWord=tableOfDBTable.getModel().getValueAt(xOfSelectedData,0).toString();
            String columnName=tableOfDBTable.getModel().getColumnName(0);

            int sure=JOptionPane.showConfirmDialog(null,"您确认删除该记录吗？","确认删除",JOptionPane.YES_NO_CANCEL_OPTION);

            System.out.println(sure);

            if (sure==0){
                theDBServer.tableRowDelete(tableName,columnName,keyWord);
            }

        }else{
            JOptionPane.showMessageDialog(null,
                    "您没有选中待删除的记录！","错误",JOptionPane.ERROR_MESSAGE);
        }
    }


    /**添加数据库记录*/
    public void dbTableDataAdd(){
        int length=paneOfRecordAdd.columnName.length;
        String tableName=comboboxOfDBTable.getSelectedItem().toString();

        String[] recordColumnName=new String[length];
        recordColumnName=paneOfRecordAdd.columnName;

        String[] recordContent=new String[length];

        for (int i=0;i<length;i++){
            recordContent[i]=paneOfRecordAdd.columnTextField[i].getText();
        }

        theDBServer.recordDataAdd(tableName,recordColumnName,recordContent);
    }

    /**修改数据表记录*/
    public void dbTableDataModify(){

        //theDBServer.dbModel.setValueAt(1,1,"aa");
        int xOfSelectedData=tableOfDBTable.getSelectedRow();
        int yOfSelectedData=tableOfDBTable.getSelectedColumn();
        System.out.println(xOfSelectedData+"\n");
        System.out.println(yOfSelectedData+"\n");

        if (xOfSelectedData>=0||yOfSelectedData>=0){
            String waitInput=(String)tableOfDBTable.getValueAt(
                    xOfSelectedData,yOfSelectedData);
            System.out.println(waitInput);

            String input=(String)JOptionPane.showInputDialog(this,"请键入新的记录字段值：",
                "修改",JOptionPane.QUESTION_MESSAGE,null,null,waitInput);

            String dbChooserName=comboboxOfDBTable.getSelectedItem().toString();
            String coulmnName=theDBServer.dbModel.getColumnName(yOfSelectedData);
            String keyCoulmnName=theDBServer.dbModel.getColumnName(0);
            String keyWord=tableOfDBTable.getValueAt(xOfSelectedData,0).toString();

            System.out.println("列名:"+coulmnName);
            System.out.println("主键:"+keyWord);

            theDBServer.getDBUpdate(dbChooserName,coulmnName,keyCoulmnName,keyWord,input);

            //theDBServer.dbModel.setValueAt(input,xOfSelectedData,yOfSelectedData);//
            //.setValueAt(xOfSelectedData,yOfSelectedData,input);

        }else{
            JOptionPane.showMessageDialog(null,
                    "您没有选中待修改的字段！","错误",JOptionPane.ERROR_MESSAGE);
        }

    }

    /**获取数据库连接*/
    public void getDBConnect(){
        theDBServer=new DBServer(theDBAccount);
        boolean successed=theDBServer.getDBConnection();
        if (!successed){
            JOptionPane.showMessageDialog(null,"未能正确连接到数据库！",
                    "错误",JOptionPane.ERROR_MESSAGE);
            //System.exit(0);
        }else{
            vectorOfDBTable=theDBServer.getTableName();
            if(vectorOfDBTable==null){
                JOptionPane.showMessageDialog(null,"未能正确连接到数据库！",
                        "错误",JOptionPane.ERROR_MESSAGE);
                //System.exit(0);
            }else{
                init(vectorOfDBTable);
                addComponentListener();
                tableOfDBTable.setModel(theDBServer.dbModel);
                tableOfDBTable.setCellSelectionEnabled(true);
            }
        }


    }

    /**界面初始化*/
    public void init(Vector vectorOfDBTable){

        setLayout(new BorderLayout());

        //各静态控件
        JPanel leftPanel;
        JPanel rightPanel;
        JPanel bottomPanel;
        JPanel buttonPanel;
        JPanel topPanel;
        JPanel topLeftPanel;
        JPanel topLeftTopPanel;
        JPanel topRightPanel;

        JLabel labelOfDBTable;


        //左边
        leftPanel=new JPanel(new BorderLayout());
        JPanel northLeftPanel=new JPanel(new FlowLayout(FlowLayout.LEFT));
        JPanel southLeftPanel=new JPanel(new FlowLayout(FlowLayout.CENTER));
        JPanel centerLeftPanel=new JPanel(new BorderLayout());

        leftPanel.setPreferredSize(new Dimension(250,600));
        leftPanel.setBorder(new TitledBorder(new TitledBorder(new EtchedBorder(EtchedBorder.RAISED,
        Color.white, new Color(165, 163, 151)), "数据查询")));
        centerLeftPanel.setBorder(new LineBorder(new Color(165, 163, 151)));

        labelOfDBTable=new JLabel("数据表: ");
        comboboxOfDBTable=new JComboBox(vectorOfDBTable);
        comboboxOfDBTable.setPreferredSize(new Dimension(170,20));
        textareaOfSQL=new JTextArea(1,50);
        textareaOfSQL.setLineWrap(true);
        textareaOfSQL.setWrapStyleWord(true);
        JScrollPane textSQL=new JScrollPane(textareaOfSQL);
        textSQL.setHorizontalScrollBar(null);
        buttonOfQueryOk=new JButton("提交");
        buttonOfSetBlank=new JButton("撤销");

        northLeftPanel.add(labelOfDBTable);
        northLeftPanel.add(comboboxOfDBTable);
        southLeftPanel.add(buttonOfQueryOk);
        southLeftPanel.add(buttonOfSetBlank);
        centerLeftPanel.add(textSQL);

        leftPanel.add(northLeftPanel,"North");
        leftPanel.add(centerLeftPanel,"Center");
        leftPanel.add(southLeftPanel,"South");

        add(leftPanel,"West");

        //右边
        rightPanel=new JPanel(new BorderLayout());
        JPanel centerRightPanel=new JPanel(new BorderLayout());
        JPanel southRightPanel=new JPanel(new FlowLayout(FlowLayout.CENTER));

        rightPanel.setPreferredSize(new Dimension(550,600));
        rightPanel.setBorder(new TitledBorder(new TitledBorder(new EtchedBorder(EtchedBorder.RAISED,
        Color.white, new Color(165, 163, 151)), "数据表")));
        centerRightPanel.setBorder(new LineBorder(new Color(165, 163, 151)));

        //tableOfDBTable=new JTable();

        tableOfDBTable=new JTable(theDBServer.dbModel);

        JScrollPane scrollpane = new JScrollPane(tableOfDBTable);

        paneOfRecordAdd=new RecordAddPanel();

        buttonOfDBTableDataAdd=new JButton("添加");
        buttonOfDBTableDataModify=new JButton("修改");
        buttonOfDBTableDataDelete=new JButton("删除");
        //buttonOfDBTableDataRefresh=new JButton("刷新");

        centerRightPanel.add(scrollpane,"Center");
        centerRightPanel.add(paneOfRecordAdd,"South");
        southRightPanel.add(buttonOfDBTableDataAdd);
        southRightPanel.add(buttonOfDBTableDataModify);
        southRightPanel.add(buttonOfDBTableDataDelete);
        //southRightPanel.add(buttonOfDBTableDataRefresh);

        rightPanel.add(centerRightPanel,"Center");
        rightPanel.add(southRightPanel,"South");
        add(rightPanel,"Center");

    }

    /**添加控件事件监听*/
    public void addComponentListener()    {
        buttonOfQueryOk.addActionListener(this);
        buttonOfSetBlank.addActionListener(this);
        comboboxOfDBTable.addActionListener(this);

        buttonOfDBTableDataAdd.addActionListener(this);
        buttonOfDBTableDataModify.addActionListener(this);
        buttonOfDBTableDataDelete.addActionListener(this);
        //buttonOfDBTableDataRefresh.addActionListener(this);
    }

    /**获取用户选定数据表的数据*/
    public void getTableData(){
        String dbChooserName=comboboxOfDBTable.getSelectedItem().toString();
        if(!theDBServer.getTableData(dbChooserName)){
            JOptionPane.showMessageDialog(null,"数据库操作出现错误，请检查您的输入！",
                    "错误",JOptionPane.ERROR_MESSAGE);
        }
        theDBServer.getTableData(dbChooserName);
        tableOfDBTable.setModel(theDBServer.dbModel);
    }

    public void sysExit(){
        System.exit(0);
    }

}

其中，增加数据表记录的部分单独实现了一个JPanel类来实现，可以动态适应当前数据表记录的列数。
class RecordAddPanel extends JPanel{
    String[] columnName;
    JLabel[] columnNameLabel;
    JTextField[] columnTextField;
    JPanel[] layPanel;

    RecordAddPanel(){

        //setLayout(new FlowLayout(FlowLayout.LEFT));
        //setPreferredSize(new Dimension(550,60));
        setBorder(new LineBorder(new Color(165, 163, 151)));
        //setVisible(true);
    }

    public void setContent(String[] data){
        removeAll();
        int layNum;
        int length=data.length;
        columnName=new String[length];
        columnNameLabel=new JLabel[length];
        columnTextField=new JTextField[length];
        int i;

        if (length%3!=0){
            layNum=length/3+1;
        }else{
            layNum=length/3;
        }
        layPanel=new JPanel[layNum];

        setVisible(false);
        setLayout(new GridLayout(layNum,1));
        setPreferredSize(new Dimension(550,30*layNum));

        System.out.println("this:"+layNum);
        for (int j=0;j<layNum;j++){

            layPanel[j]=new JPanel(new FlowLayout(FlowLayout.LEFT));

            for (i=3*j;i<3*(j+1);i++){

                if(i<length){
                    columnName[i]=data[i];
                    columnNameLabel[i]=new JLabel(columnName[i]);
                    columnTextField[i]=new JTextField(10);
                    //System.out.println(columnName[i]);

                    layPanel[j].add(columnNameLabel[i]);
                    layPanel[j].add(columnTextField[i]);
                }
            }

            add(layPanel[j]);
        }
        setVisible(true);
    }

    public void clearContent(){

        removeAll();

        setVisible(true);
    }
}

最后是和数据库打交道的类了，负责将DB面板上的用户行为转换为实际的数据库操作，并负责刷新用户面板上的数据表源；

class DBServer extends JPanel{

    DBAccount theDBAccount;

    QueryTableModel dbModel;

    String DBPath;
    String DBUser;
    String UserPasswd;
    Statement theStatement;
    String ul;

    Connection theConnection;

    public DBServer(DBAccount dbAccount){
        theDBAccount=dbAccount;

    }

    private void displaySQLErrors(SQLException e) {
        JOptionPane.showMessageDialog(this,
                "SQLException: " + e.getMessage()+"\n"+
                "SQLState: " + e.getSQLState()+"\n"+
                "VendorError: " + e.getErrorCode(),
                "错误提示",
                JOptionPane.WARNING_MESSAGE);
        }

    public boolean getDBConnection(){


        try{
            if (theConnection==null||theConnection.isClosed()){
                    DBPath=theDBAccount.theDBName;
                    DBUser=theDBAccount.theUserID;
                    UserPasswd=theDBAccount.thePasswd;

                    //加载驱动
                    Class.forName("com.mysql.jdbc.Driver").newInstance();
                    theConnection=DriverManager.getConnection(DBPath,DBUser,UserPasswd);
                    theStatement=theConnection.createStatement();
                    dbModel=new QueryTableModel(this);
                    return true;
            }
            return false;
        }catch(SQLException e){
            JOptionPane.showMessageDialog(this,"连接数据库出错！",
                    "错误提示",JOptionPane.WARNING_MESSAGE);
            return false;
        }catch(Exception e){
            JOptionPane.showMessageDialog(this,"数据库驱动出错！",
                    "错误提示",JOptionPane.WARNING_MESSAGE);
            return false;
        }
    }

    /**获取数据表信息*/
    public Vector getTableName(){

        try{
            DatabaseMetaData dbmd;
            String namestr,typestr,cstr,strn,indexstr,prostr;
            Vector vectorOfTableName=new Vector(0);
            short data;
            int index,pron;

            String[] type={"table"};

            getDBConnection();

            synchronized (theConnection){
            dbmd=theConnection.getMetaData();}

            // 建立一个结果集
            ResultSet rs=dbmd.getTables(null,null,null,type);

            // 输出信息
            while(rs.next()){
                String tempStr=rs.getString("TABLE_NAME");
                vectorOfTableName.addElement(tempStr);
                //typestr=rs.getString("TABLE_TYPE");
                System.out.println(tempStr);
            }
            theConnection.close();
            return vectorOfTableName;

        }catch(SQLException e){
            displaySQLErrors(e);
            return null;
        }
    }

    /**获取表的各列名称
    * @param String tableName:选定的表名称
    * */
    public Vector getTableColumnName(String tableName){
        try{
            String strOfTableName,typestr,cstr,strn,indexstr,prostr;
            short data;

            getDBConnection();


            DatabaseMetaData dbmd=theConnection.getMetaData();

            Vector vectorOfTableColumnName=new Vector(0);

            String[] type={"table"};

            // 建立一个结果集
            ResultSet rs=dbmd.getColumns(null,null,tableName,"%");


            // 产生一个对表列的结果集
            while(rs.next()){

                String temp=rs.getString("COLUMN_NAME");
                vectorOfTableColumnName.addElement(rs.getString("COLUMN_NAME"));

            }
            theConnection.close();
            return vectorOfTableColumnName;
        }
        catch(SQLException e){
            displaySQLErrors(e);
            return null;
        }
    }

    /**获取选定表的数据
    * @param String tableName:选定的表名称
    * */
    public boolean getTableData(String tableName){
        return dbModel.setQuery("select * from "+ tableName);

    }

    /**获取根据主键值得到的单行数据记录*/
    public String[] getSingleRowData(String tablename,String columnname, String keyword){
        String[] record;
        try{
            getDBConnection();

            String querySentence="select * from "+tablename+" where "+columnname+" ="+"´"+keyword+"´";
            //String querySentence="select * from users";
            System.out.println(querySentence);
            ResultSet rs=theStatement.executeQuery(querySentence);
            ResultSetMetaData rm=rs.getMetaData();
            int columnNum=rm.getColumnCount();
            record = new String[columnNum];

            while(rs.next()){
                for (int i=0; i <columnNum; i++) {
                    //System.out.println(rs.getString(i));
                    record[i] = rs.getString(i + 1);
                    System.out.println(record[i]);
                }
            }
            theConnection.close();
        }
        catch(SQLException e){

            displaySQLErrors(e);
            return null;
        }
        return record;
    }

    /**添加数据表字段记录*/
    public void recordDataAdd(String tableName, String[] columnName, String[] data){
        getDBConnection();
        String updataSentence="INSERT INTO "+tableName + " (";

        int length=columnName.length;
        for (int i=0;i<length-1;i++){
            updataSentence=updataSentence+columnName[i]+", ";
        }
        updataSentence=updataSentence+columnName[length-1]+") VALUES (";

        for (int i=0;i<length-1;i++){
            updataSentence=updataSentence+"´"+data[i]+"´, ";
        }
        updataSentence=updataSentence+"´"+data[length-1]+"´)";

        System.out.println(updataSentence);

        try{

            theStatement.executeUpdate(updataSentence);

        }
        catch (SQLException e){
            displaySQLErrors(e);

        }
    }

    /**执行查询语句*/
    public boolean getQueryData(String QuerySentence){

        return dbModel.setQuery(QuerySentence);
    }

    /**修改字段记录项*/
    public void getDBUpdate(String dbChooserName, String coulmnName,
            String keyCoulmnName, String keyWord, String input){

        getDBConnection();

        try{
            String a="UPDATE "+dbChooserName+" SET "+coulmnName+" =´"
                +input+ "´ WHERE "+keyCoulmnName+" =´"+keyWord+"´";
            System.out.println(a);
            theStatement.executeUpdate(a);

        }
        catch (SQLException e){
            displaySQLErrors(e);

        }

    }

    public void tableRowDelete(String tableName, String columnName, String data){
        getDBConnection();

        try{
            String a="DELETE FROM "+tableName+" WHERE "+columnName+" =´"
                +data+ "´";
            System.out.println(a);
            theStatement.executeUpdate(a);

        }
        catch (SQLException e){
            displaySQLErrors(e);

        }
    }

    public static void main (String args[]){

        //DBManager theDBManager=new DBManager();
        //theDBManager.conn("jdbc:mysql://localhost/wpss","root","´xh327413´");
    }
}

/**查询结果对象的数据表模型对象*/
class QueryTableModel extends AbstractTableModel {

    DBServer theDBServer;

    //数据缓存
    Vector cache;
    //列数
    int colCount;
    //表头
    String[] headers;
    //声明对象
    Statement theStatement;

    /**构造函数*/
    public QueryTableModel(DBServer dbserver) {
        cache = new Vector();
        theDBServer=dbserver;

    }

    public String getColumnName(int i){
        return headers[i];
    }
    public int getColumnCount(){
        return colCount;
    }
    public int getRowCount() {
        return cache.size();
    }

    public Object getValueAt(int row, int col) {
        return ((String[])cache.elementAt(row))[col];
    }

    public void setValueAt(int row, int col, String input) {
        ((String[])cache.elementAt(row))[col]=input;
    }

    public boolean setQuery(String query) {
        cache = new Vector();
        try {
            // Execute the query and store the result set and its metadata
            theDBServer.getDBConnection();
            theStatement=theDBServer.theStatement;

            ResultSet rs = theStatement.executeQuery(query);
            ResultSetMetaData meta = rs.getMetaData();
            colCount = meta.getColumnCount();

            // Now we must rebuild the headers array with the new column names
            headers = new String[colCount];
            for (int h=1; h <= colCount; h++) {
                headers[h-1] = meta.getColumnName(h);
            }

            // and file the cache with the records from our query. This would not be
            // practical if we were expecting a few million records in response to our
            // query, but we aren´t, so we can do this.
            while (rs.next()) {
                String[] record = new String[colCount];
                for (int i=0; i < colCount; i++) {
                    record[i] = rs.getString(i + 1);
                }
                cache.addElement(record);
            }
            fireTableChanged(null);
            // notify everyone that we have a new table.
            return true;
        }
        catch(Exception e) {
            cache = new Vector(0); // blank it out and keep going.
            //e.printStackTrace();
            return false;
        }
    }
}