当前位置: 首页 > 知识库问答 >
问题:

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

雍嘉勋
2023-03-14

我已经在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);

共有2个答案

太叔何平
2023-03-14

好的!!!我设法通过编辑单元格然后按回车键单独更新每个单元格!这只适用于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();
    }   

    }

}
}
程沛
2023-03-14

在您实际创建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)销毁旧的结果集)来防止任何异常。

 类似资料:
  • 问题内容: 我正在尝试将JTable中的多行数据保存到数据库中,这是我的代码供参考: 问题是,它仅将一行数据插入数据库。有人可以帮帮我吗?:( 谢谢! 问题答案: 从循环中删除以下行代码并将其放置在循环之前 示例: 用以下代码替换您的代码 然后运行它认为它起作用。 对于批量插入示例,请参见https://my.vertica.com/docs/5.0/HTML/Master/14878.htm

  • 我有2列的jtable,然后我想把它的值插入数据库; 我知道这可以用像.. 问题是..getRowCount不知道该行是否为空,那么在数据库中,该空值仍将被插入(它将在数据库中生成我的自动增量值) 我的问题是如何从jtable中插入数据库,但是空行不会插入? 如果我要求太多,请给我一个处理空行的线索, 原谅我的英语 下面是向数据库中添加数据的坏方法

  • 问题内容: 我正在尝试使用angularjs从前端向mysql db插入数据。 但是,即使没有错误消息,它也不会插入数据库 。以下是我使用的代码。 index.html script.js View1.html 以下是我的php文件 insert.php 我知道我在这里做一些愚蠢的事情。我今天才刚刚开始学习angularjs。当我尝试将纯HTML的php代码插入db时,它的工作原理非常完美。希望有

  • 问题内容: 我有一张表格,列出了来自特定网站的评论数量,如下所示: 我还有另一个表,列出了所有站点,例如从1到10 使用以下代码,我可以找出上个月缺少哪些站点条目: 生产: 我希望能够使用一些默认值(即“ 0”)将查询中列出的缺失网站插入到注释表中 问题是,我如何更新/插入表/值? 干杯, 背风处 问题答案:

  • 问题内容: 我想从一个表中读取所有数据,然后将一些数据插入到另一个表中。我的查询是 但我有一个错误 请帮我。 问题答案: 您可以使用INSERT … SELECT语法。请注意,您可以在SELECT部分​​中直接引用“ 1”。

  • 主要内容:基本语法,向表中的全部字段添加值,向表中指定字段添加值,使用 INSERT INTO…FROM 语句复制表数据数据库与表创建成功以后,需要向数据库的表中插入数据。在 MySQL 中可以使用 INSERT 语句向数据库已有的表中插入一行或者多行元组数据。 基本语法 INSERT 语句有两种语法形式,分别是 INSERT…VALUES 语句和 INSERT…SET 语句。 1) INSERT…VALUES语句 INSERT VALUES 的语法格式为: INSERT INTO <表名> [