在Java GUI中将数据从JTable插入MySQL表

Question

我已经在Java中制作了一个GUI，它与MySQL服务器连接并插入，删除，更新数据。我在此GUI上有一个部分，您可以在文本区域中编写MySQL查询，结果显示在Jtable上。一切都很好！我可以打印JTable中的数据或将它们保存到文本文件中！

现在，我想添加另一个特性：当我双击一个特定单元格时，我想更改JTable的数据，并且我想通过单击按钮来更新MySQL表中的数据。

我在网上找遍了，也找不到好的例子，也找不到好的解决办法。我拥有的< code>JTable是动态的；这意味着无论插入什么查询，数据都将显示所需的列名和数据

以下是代码：

ArrayList columnNames = new ArrayList();
ArrayList data = new ArrayList();

data_connector getdata1 = new data_connector();
host = getdata1.getHost();
username = getdata1.getUsername();
password1 = getdata1.getPassword();
mysql_command = getdata1.getMysql_command();
command_name = getdata1.getCommand_name();
setTitle(command_name);

//  Connect to an MySQL Database, run query, get result set
String url = "jdbc:mysql://"+host+":3306/xxxxx";
String userid = username;
String password = password1;
String sql = mysql_command;


// Java SE 7 has try-with-resources
// This will ensure that the sql objects are closed when the program 
// is finished with them
try (Connection connection = DriverManager.getConnection( url, userid, password );
     Statement stmt = connection.createStatement();
     ResultSet rs = stmt.executeQuery( sql ))
{
    ResultSetMetaData md = rs.getMetaData();
    int columns = md.getColumnCount();

    //  Get column names
    for (int i = 1; i <= columns; i++)
        columnNames.add(md.getColumnName(i));

    //  Get row data
    while (rs.next())
    {
        ArrayList row = new ArrayList(columns);
        for (int i = 1; i <= columns; i++)
            row.add(rs.getObject(i));

        data.add(row);
    }
}
catch (SQLException e)
{
    System.out.println(e.getMessage());
    JOptionPane.showMessageDialog(null, e.getMessage());
    mysql_fail_flag = 1;
}

// Create Vectors and copy over elements from ArrayLists to them
// Vector is deprecated but I am using them in this example to keep 
// things simple - the best practice would be to create a custom defined
// class which inherits from the AbstractTableModel class
Vector columnNamesVector = new Vector();
Vector dataVector = new Vector();

for (int i = 0; i < data.size(); i++)
{
    ArrayList subArray = (ArrayList)data.get(i);
    Vector subVector = new Vector();
    for (int j = 0; j < subArray.size(); j++)
        subVector.add(subArray.get(j));

    dataVector.add(subVector);
}

for (int i = 0; i < columnNames.size(); i++ )
    columnNamesVector.add(columnNames.get(i));
contentPane.setLayout(null);

// Create table with database data    
table = new JTable(dataVector, columnNamesVector)
{
    public Class getColumnClass(int column)
    {
        for (int row = 0; row < getRowCount(); row++)
        {
            Object o = getValueAt(row, column);

            if (o != null)
                return o.getClass();
        }

        return Object.class;
    }
};

// table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
JScrollPane scrollPane = new JScrollPane(table);
scrollPane.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_ALWAYS);

scrollPane.setBounds(5, 5, xframeWidth-20, yframeHeight-70);
getContentPane().add(scrollPane);

JPanel buttonPanel = new JPanel();
buttonPanel.setBounds(5, 856, 1574, 1);
getContentPane().add(buttonPanel);
buttonPanel.setLayout(null);

Answer 1

好的！！！我设法通过编辑单元格然后按回车键单独更新每个单元格！这只适用于1个表，但对我的项目来说没问题！这是代码…

private class RowColumnListSelectionListener implements ListSelectionListener {
    public void valueChanged(ListSelectionEvent e) {

        rowIndexStart = table.getSelectedRow();
        rowIndexEnd = table.getSelectionModel().getMaxSelectionIndex();
        colIndexStart = table.getSelectedColumn();
        colIndexEnd = table.getColumnModel().getSelectionModel().getMaxSelectionIndex();

        for ( i = rowIndexStart; i <= rowIndexEnd; i++) {
            for ( j = colIndexStart; j <= colIndexEnd; j++) {

                Object cell_value = table.getValueAt(i,j);
                Cell_value_string_before = (String) cell_value; 
            }
        }                                                       

    }

}

public test_table() {
    setIconImage(Toolkit.getDefaultToolkit().getImage(test_table.class.getResource("/com/sun/java/swing/plaf/windows/icons/Computer.gif")));


    //setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    //setBounds(100, 100, 688, 589);
    Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
    xframeWidth = screenSize.width;  //dynamic size for frame x-axes
    yframeHeight = screenSize.height; //dynamic size for frame y-axes
    int xlocation = xframeWidth*2; //dynamic location x-axes
    int ylocation = yframeHeight*2; //dynamic location y-axes
    setBounds(0,0, xframeWidth, yframeHeight); 
    setResizable(false);

    JMenuBar menuBar = new JMenuBar();
    setJMenuBar(menuBar);

    JMenu mnNewMenu = new JMenu("Αρχείο");
    menuBar.add(mnNewMenu);

    JMenuItem mntmNewMenuItem_1 = new JMenuItem("Εξαγωγή σε .txt αρχείο");
    mntmNewMenuItem_1.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent e) {

            JFileChooser fileChooser =new JFileChooser();
            fileChooser.setDialogTitle("Δημιουργία αρχείου .txt");  
            FileNameExtensionFilter filter = new FileNameExtensionFilter(".txt", "text");
            fileChooser.setFileFilter(filter);  

            int returnVal = fileChooser.showSaveDialog(null);
            if (returnVal == JFileChooser.APPROVE_OPTION) {


                try {
                    File file = fileChooser.getSelectedFile();
                   File newfile = new File(file.getPath()+".txt");
                   // PrintWriter os = new PrintWriter(file);
                    FileWriter fw = new FileWriter(newfile,true);   //filewriter
                    BufferedWriter bw = new BufferedWriter(fw);     //buffered writer
                    PrintWriter os = new PrintWriter(bw, true);

                    os.print("");
                    for (int col = 0; col < table.getColumnCount(); col++) {
                        os.print(table.getColumnName(col) + "\t");
                        os.print(";");
                    }

                    os.println("");
                    os.println("");

                    for (int row = 0; row < table.getRowCount(); row++) {
                        for (int col = 0; col < table.getColumnCount(); col++) {
                            //os.print(table.getColumnName(col) + "\t");
                          // os.print(": ");
                            os.print(table.getValueAt(row, col) + "\t");
                            os.print(";");
                          // os.print(table.getRowCount() + "\t");
                        }
                        os.println("");
                    }
                    os.close();
                    System.out.println("Done!");
                } 
               catch (IOException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();




             }
            }


        }
    });
    mnNewMenu.add(mntmNewMenuItem_1);

    JMenuItem mntmNewMenuItem_2 = new JMenuItem("Εκτύπωση");
    mntmNewMenuItem_2.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent e) {

            try {
                 if (! table.print()) {
                     System.err.println("User cancelled printing");
                 } 
             } catch (java.awt.print.PrinterException e1) {
                 System.err.format("Cannot print %s%n", e1.getMessage()); 
             } 
        }
    });
    mnNewMenu.add(mntmNewMenuItem_2);
    contentPane = new JPanel();
    contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
    setContentPane(contentPane);


    ArrayList columnNames = new ArrayList();
    ArrayList data = new ArrayList();

    // data_connector getdata1 = new data_connector();
    // host = getdata1.getHost();
    // username = getdata1.getUsername();
    // password1 = getdata1.getPassword();
    // mysql_command = getdata1.getMysql_command();
    // command_name = getdata1.getCommand_name();
    //setTitle(command_name);
    //  Connect to an MySQL Database, run query, get result set
    String url = "jdbc:mysql://localhost:3306/υπαλληλοι απε-μπε";
    String userid = "ziorange";
    String password = "120736";
    String sql = "SELECT * FROM `ΥΠΑΛΛΗΛΟΙ 2 test`";
    //String sql = "SELECT `ΚΩΔΙΚΟΣ`,`ΕΠΩΝΥΜΟ`,`ΟΝΟΜΑ`,`ΟΝΟΜΑ ΠΑΤΡΟΣ`,`ΑΜΚΑ`,`ΑΡΙΘΜΟΣ ΜΗΤΡΩΟΥ ΙΚΑ (αν υπάρχει)` FROM `ΥΠΑΛΛΗΛΟΙ 2` WHERE `ΚΩΔΙΚΟΣ`>'0' AND `ΗΜΕΡΟΜΗΝΙΑ ΑΠΟΧΩΡΗΣΗΣ`>'2009-12-31 00:00:00' OR `ΕΙΔΙΚΟΤΗΤΑ` !='ΑΝΤΑΠΟΚΡΙΤΗΣ ΕΞ' AND `ΛΟΓΟΣ ΑΠΟΧΩΡΗΣΗΣ`='-' ORDER BY `ΕΠΩΝΥΜΟ`";

    // Java SE 7 has try-with-resources
    // This will ensure that the sql objects are closed when the program 
    // is finished with them
    try 
    {
         connection = DriverManager.getConnection( url, userid, password );
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery( sql );

        ResultSetMetaData md = rs.getMetaData();
        int columns = md.getColumnCount();

        //  Get column names
        for (int i = 1; i <= columns; i++)
        {
            columnNames.add( md.getColumnName(i) );
        }

        //  Get row data
        while (rs.next())
        {
            ArrayList row = new ArrayList(columns);

            for (int i = 1; i <= columns; i++)
            {
                row.add( rs.getObject(i) );
            }

            data.add( row );
        }
    }
    catch (SQLException e)
    {
         // 
        System.out.println( e.getMessage() );
       JOptionPane.showMessageDialog(null, e.getMessage() );
      mysql_fail_flag=1;
    }

    // Create Vectors and copy over elements from ArrayLists to them
    // Vector is deprecated but I am using them in this example to keep 
    // things simple - the best practice would be to create a custom defined
    // class which inherits from the AbstractTableModel class
    Vector columnNamesVector = new Vector();
    Vector dataVector = new Vector();

    for (int i = 0; i < data.size(); i++)
    {
        ArrayList subArray = (ArrayList)data.get(i);
        Vector subVector = new Vector();
        for (int j = 0; j < subArray.size(); j++)
        {
            subVector.add(subArray.get(j));
        }
        dataVector.add(subVector);
    }

    for (int i = 0; i < columnNames.size(); i++ )
        columnNamesVector.add(columnNames.get(i));
     contentPane.setLayout(null);

    //  Create table with database data    
     table = new JTable(dataVector, columnNamesVector)



    {
        public Class getColumnClass(int column)
        {
            for (int row = 0; row < getRowCount(); row++)
            {
                Object o = getValueAt(row, column);

                if (o != null)
                {
                    return o.getClass();
                }
            }

            return Object.class;
        }
    };

   table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
   table.setColumnSelectionAllowed(true); //epilegei to kathe keli ksexwrista
   table.getSelectionModel().addListSelectionListener(
           new RowColumnListSelectionListener());

   table.getDefaultEditor(String.class).addCellEditorListener(
           new CellEditorListener() {
               public void editingCanceled(ChangeEvent e) {
                   System.out.println("editingCanceled");
               }

               public void editingStopped(ChangeEvent e) {

                   System.out.println("editingStopped: apply additional action");

                     rowIndexStart = table.getSelectedRow();
                     rowIndexEnd = table.getSelectionModel().getMaxSelectionIndex();
                     colIndexStart = table.getSelectedColumn();
                     colIndexEnd = table.getColumnModel().getSelectionModel().getMaxSelectionIndex();

                    for ( i = rowIndexStart; i <= rowIndexEnd; i++) {
                        for ( j = colIndexStart; j <= colIndexEnd; j++) { 

                                Object cell_value = table.getValueAt(i,j);

                                 Cell_value_string_after = (String) cell_value;                                      

                                 ia=i+1;
                                 ja=j+1;

                                 column_name_selected2 = table.getColumnName(ja-1);


                        }

                    }

                    if(Cell_value_string_before.equals(Cell_value_string_after)){
                        System.out.println("Do nothing");
                    }
                    else{
                        System.out.println("UPDATE DATABASE");
                        update_database();
                    }
               }
           });





  JScrollPane scrollPane = new JScrollPane( table );
   scrollPane.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_ALWAYS);

    scrollPane.setBounds(5, 5, xframeWidth-20, yframeHeight-70);
    getContentPane().add( scrollPane );



    JPanel buttonPanel = new JPanel();
    buttonPanel.setBounds(5, 856, 1574, 1);
    getContentPane().add( buttonPanel );
    buttonPanel.setLayout(null);



}

public void update_database(){


    Object get_lastname = table.getValueAt(ia-1, 5);
    String get_lastname_string = (String) get_lastname;
    Object get_name = table.getValueAt(ia-1, 6);
    String get_name_string=(String) get_name;

    System.out.println("RESULT= "+ column_name_selected2 + " - "+Cell_value_string_after+ " - " + get_lastname_string+ " - " + get_name_string  );

    if(column_name_selected2.equals("ΕΠΩΝΥΜΟ") || column_name_selected2.equals("ΟΝΟΜΑ")){
        JOptionPane.showMessageDialog(null, "To επώνυμο και το όνομα δεν μπορεί να αλλάξει","Μήνυμα:",JOptionPane.WARNING_MESSAGE);
    }

    else{
    try {
        PreparedStatement update = (PreparedStatement) connection.prepareStatement

                ("UPDATE `ΥΠΑΛΛΗΛΟΙ 2 TEST` SET `" +column_name_selected2+"` = ? WHERE ΕΠΩΝΥΜΟ= ? AND ΟΝΟΜΑ =? ");


        update.setString(1,Cell_value_string_after);
        update.setString(2,get_lastname_string);
        update.setString(3,get_name_string); 

        int all_edit_query_status=update.executeUpdate();

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }   

    }

}
}

Answer 2

在您实际创建JTable的表单中，我认为这并不容易。您要做的是子类化AbstractTableModel，并覆盖setValueAt（）方法。您的子类可以如下所示：

class MyModel extends AbstractTableModel
{
    private ResultSet result;
    private ResultSetMetaData metadata;
    public MyModel (ResultSet rs)
    {
        super();
        result = rs; // mustn't be null, maybe check and throw NPE
        metadata = result.getMetaData();
    }

    public int getRowCount ()
    {
        result.last();
        return result.getRow(); // See http://stackoverflow.com/questions/8292256/get-number-of-rows-returned-by-resultset-in-java
    }
    public int getColumnCount ()
    {
        return metadata.getColumnCount();
    }

    public Object getValueAt (int row, int col)
    {
        result.absolute(row);
        return result.getString(col);
    }
    public String getColumnName (int col)
    {
        return metadata.getColumnName(col);
    }

    public void setValueAt (Object value, int row, int col)
    {
        result.absolute(row);
        result.updateObject(col, value);
    }
}

我没有测试过，但是你的代码一定是这样的。请注意，您不能关闭< code >语句 、< code >连接或< code >结果集(或创建一个新的< code >结果集导致一些db驱动程序(如MySQL)销毁旧的结果集)来防止任何异常。