个人中心
gpt4 book ai didi

java - 获取表数据并保存到数据库 - SQLEXception 一般错误

转载 作者：行者123 更新时间：2023-12-01 11:19:21 26 4
gpt4 key购买 nike

我已经使用 java swing 制作了一个库存系统。我的代码工作正常，直到我将这些代码行添加到我的“保存”按钮中:

这应该是:

从表中获取数据并将其保存到数据库

  1. 单击按钮后，获取传入表中每行的数据
  2. 在字符串数组中每列设置行数据

  3. 将这些数据插入我的表 IncomingTransactionITems

    int rowCount=jTable3.getRowCount();
            String[] ItemList=new String[6];
            for(int rowctr=1;rowctr<rowCount;rowctr++){
                 ItemList[0]= String.valueOf(rowctr);                 //    Incoming Transaction_Number
                 ItemList[1]= this.jTextField1.getText();             //    Transaction ID
                 ItemList[2]=this.jTable3.getModel().getValueAt(rowctr, 0).toString();              //    Item Number
                 ItemList[3]=this.jTable3.getModel().getValueAt(rowctr, 2).toString();              //    Quantity
                 ItemList[4]=this.jTable3.getModel().getValueAt(rowctr, 3).toString();              //    Unit Price
                 ItemList[5]=this.jTable3.getModel().getValueAt(rowctr, 4).toString();              //    Total Price     

    //                    Save onto IncomingTransactionItems Table in Database                   
                String sqlItems="INSERT INTO IncomingTransactionItems(Incoming_Transaction_Number, Transaction_ID,Item_Number, Quantity, Unit_Price,Total_Price)values('"
                + ItemList[0]+"','"              //    Incoming Transaction_Number
                + ItemList[1]+"','"              //    Transaction ID
                + ItemList[2]+"','"              //    Item Number
                + ItemList[3]+"','"              //    Quantity
                + ItemList[4]+"','"              //    Unit Price
                + ItemList[5]+"')";              //    Total Price       

                 try{
                    pst= conn.prepareStatement(sqlItems);
                    pst.execute();

                    this.UpdateJTable();

                }
                catch(SQLException | HeadlessException e){
                   Logger.getLogger(Encoding_Incoming_Batch.class.getName()).log(Level.SEVERE, null, e);
                }

    但是当我运行该程序时，它抛出异常:

    Inventory_System.Encoding_Incoming_Batch cmdADDMouseReleased
    SEVERE: null
    java.sql.SQLException: General error

我认为我的数据库可能是只读的，所以我检查了系统的其他功能(即添加项目、日志、更新价格表等)仍然工作正常。

我检查了我的表的传入事务号主键，如果我可能保存重复值，但事实并非如此，因为程序获取表中的值数并将其加一以形成新记录。

错误点表明这一行有问题

pst= conn.prepareStatement(sqlItems);

我是错过了什么还是我做错了什么？

编辑

发布我的按钮的完整代码

if(cmdADD.isEnabled()){
        //==================UPDATE ITEM COUNT and PRICE===================
        int rowCountloop=jTable3.getRowCount();
        float PreviousPrice;
        float NewPrice;

        for(int rowctr=1;rowctr<rowCountloop;rowctr++){

            //          get Item Count
            int ItemQuantity=Integer.valueOf(this.jTable2.getModel().getValueAt(rowctr,1).toString());
            int IncomingQuantity=Integer.valueOf(this.jTable3.getModel().getValueAt(rowctr, 2).toString());

            //          add incoming Items
            int ItemTotal=ItemQuantity+IncomingQuantity;

            PreviousPrice=(Float.valueOf(this.jTable2.getModel().getValueAt(rowctr,2).toString()));
            NewPrice=(Float.valueOf(this.jTable3.getModel().getValueAt(rowctr, 3).toString()));
            float newTotalPrice=((float) ((PreviousPrice*ItemQuantity)+(NewPrice*IncomingQuantity))/ItemTotal);

            //          convert values to 2 decimal
           double PreviousPrice2 = Math.round(PreviousPrice*100.0)/100.0;
            double NewPrice2 = Math.round(NewPrice*100.0)/100.0;
            double newTotalPrice2 = Math.round(newTotalPrice*100.0)/100.0;
            //          Update Value of Quantity and Price

            //                String sqlUpdate= "Update allItems set Quantity='"+ItemTotal+"',Unit_Cost='"+NewPrice2+"',Total_Cost='"+newTotalPrice2+"' where Item_Number='"+this.jTable3.getModel().getValueAt(rowctr, 0).toString()+"'";
           String sqlUpdate= "Update allItems set Quantity=?,Unit_Cost=?,Total_Cost=? where Item_Number='"+this.jTable3.getModel().getValueAt(rowctr, 0).toString()+"'";
            try {
                PreparedStatement pstUpdate= conn.prepareStatement(sqlUpdate);
                pstUpdate.setInt(1, ItemTotal);
                pstUpdate.setDouble(2, NewPrice2);
                pstUpdate.setDouble(2, newTotalPrice2);
                pstUpdate.executeUpdate();
            } catch (SQLException ex) {
                Logger.getLogger(Encoding_Inventory_Batch.class.getName()).log(Level.SEVERE, null, ex);
            }

            //==================SAVE Price Change to PriceList==========================================

                String sqlPriceChange="insert into PriceList(PriceID,ItemNumber,NewPrice,PreviousPrice,DateAndTime)values(?,?,?,?,?)";
                 try (PreparedStatement pstPriceChange = conn.prepareStatement(sqlPriceChange)){    
                pstPriceChange.setString(1, "PL"+ (PriceIDtxt.getText())+"-"+rowctr);
                pstPriceChange.setString(2,this.jTable3.getModel().getValueAt(rowctr, 0).toString());
                pstPriceChange.setDouble(3,NewPrice2);
                pstPriceChange.setDouble(4,PreviousPrice2);
                pstPriceChange.setString(5, (dateFormatwithtime.format(date)));

                pstPriceChange.executeUpdate();

            }
            catch(SQLException | HeadlessException e){
                JOptionPane.showMessageDialog(null, e);
            }

        }// end of loop
        //==========================================================================================
        int RowCounter=jTable3.getRowCount();

        if(RowCounter<=1){
            JOptionPane.showMessageDialog(this,"Please Input Data on Incoming List","System Message", JOptionPane.ERROR_MESSAGE);
        }else{
            int reply = JOptionPane.showConfirmDialog(null, "Confirm and SAVE?", "System Message", JOptionPane.YES_NO_OPTION,JOptionPane.QUESTION_MESSAGE);
                 if (reply == JOptionPane.YES_OPTION) {  
                int answer = JOptionPane.showConfirmDialog(null, "Data Cannot be modified once it is SAVED, Continue?", "System Message", JOptionPane.YES_NO_OPTION,JOptionPane.WARNING_MESSAGE);
                    if (answer == JOptionPane.YES_OPTION) {

            int rowCount=jTable3.getRowCount();
            String[] ItemList=new String[6];
            String sqlItems="INSERT INTO IncomingTransactionItems(Incoming_Transaction_Number, Transaction_ID,Item_Number, Quantity, Unit_Price,Total_Price)values(?,?,?,?,?,?)";
            for(int rowctr=1;rowctr<rowCount;rowctr++){
                 ItemList[0]= String.valueOf(rowctr);                                               //    Incoming Transaction_Number
                 ItemList[1]= this.jTextField1.getText();                                           //    Transaction ID
                 ItemList[2]=this.jTable3.getModel().getValueAt(rowctr, 0).toString();              //    Item Number
                 ItemList[3]=this.jTable3.getModel().getValueAt(rowctr, 2).toString();              //    Quantity
                 ItemList[4]=this.jTable3.getModel().getValueAt(rowctr, 3).toString();              //    Unit Price
                 ItemList[5]=this.jTable3.getModel().getValueAt(rowctr, 4).toString();              //    Total Price        

    //                    Save onto IncomingTransactionItems Table in Database                   


                try (PreparedStatement pstInsert = conn.prepareStatement(sqlItems)){
    // You could do this with a for-loop, I've done it long
    // to demonstrate the use of PreparedStatement
                pstInsert.setString(1, ItemList[0]);
                pstInsert.setString(2, ItemList[1]);
                pstInsert.setString(3, ItemList[2]);
                pstInsert.setString(4, ItemList[3]);
                pstInsert.setString(5, ItemList[4]);
                pstInsert.setString(6, ItemList[5]);
                pstInsert.executeUpdate();
                 }catch(SQLException | HeadlessException e){
                   Logger.getLogger(Encoding_Incoming_Batch.class.getName()).log(Level.SEVERE, null, e);
                }

                                   String sql="INSERT INTO Incoming_Transactions (Transaction_ID, Date_Delivered, Ref_No, PO_No,JO_No, Supplier_Code,Notes, Status, Time_Stamp )\n" +
                                                        "VALUES (?,?,?,?,?,?,?,?,?)";





                try (PreparedStatement pstTransactions = conn.prepareStatement(sql)){    
                pstTransactions.setString(1, (this.TransactionNotxt.getText()));
                pstTransactions.setString(2,  this.DateDeliveredChooser.getDate().toString());
                pstTransactions.setString(3,this.ReferenceTxt.getText());
                pstTransactions.setString(4,this.POtxt.getText());
                pstTransactions.setString(5, this.JOtxt.getText());
                pstTransactions.setString(6, this.SupplierCodetxt.getText());
                pstTransactions.setString(7, this.NoteTextArea.getText());
                pstTransactions.setString(8, "Processed");
                pstTransactions.setString(9, dateFormatwithtime.format(date));
                pstTransactions.execute();



                }
                catch(SQLException | HeadlessException e){
                    Logger.getLogger(Encoding_Incoming_Batch.class.getName()).log(Level.SEVERE, null, e);
                }
//                  update Table to show changes
                this.UpdateJTable();
            }
            JOptionPane.showMessageDialog(null, "Transaction Saved");
            this.cmdADD.setEnabled(false);
            this.cmdDELETE.setEnabled(false);
            CountDataPriceList(); // Increment Price List number
                    }
                }
        }

    }

这是完整的错误

    run:
Jul 16, 2015 1:19:31 PM Inventory_System.Encoding_Incoming_Batch cmdADDMouseReleased
SEVERE: null
java.sql.SQLException: General error
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6993)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3156)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:215)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatement.java:137)
at Inventory_System.Encoding_Incoming_Batch.cmdADDMouseReleased(Encoding_Incoming_Batch.java:1240)
at Inventory_System.Encoding_Incoming_Batch.access$3600(Encoding_Incoming_Batch.java:44)
at Inventory_System.Encoding_Incoming_Batch$30.mouseReleased(Encoding_Incoming_Batch.java:915)
at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:290)
at java.awt.Component.processMouseEvent(Component.java:6516)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
at java.awt.Component.processEvent(Component.java:6281)
at java.awt.Container.processEvent(Container.java:2229)
at java.awt.Component.dispatchEventImpl(Component.java:4872)
at java.awt.Container.dispatchEventImpl(Container.java:2287)
at java.awt.Component.dispatchEvent(Component.java:4698)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
at java.awt.Container.dispatchEventImpl(Container.java:2273)
at java.awt.Window.dispatchEventImpl(Window.java:2719)
at java.awt.Component.dispatchEvent(Component.java:4698)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:747)
at java.awt.EventQueue.access$300(EventQueue.java:103)
at java.awt.EventQueue$3.run(EventQueue.java:706)
at java.awt.EventQueue$3.run(EventQueue.java:704)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
at java.awt.EventQueue$4.run(EventQueue.java:720)
at java.awt.EventQueue$4.run(EventQueue.java:718)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:717)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)

Jul 16, 2015 1:19:31 PM Inventory_System.Encoding_Incoming_Batch cmdADDMouseReleased
SEVERE: null
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3156)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:215)
at Inventory_System.Encoding_Incoming_Batch.cmdADDMouseReleased(Encoding_Incoming_Batch.java:1278)
at Inventory_System.Encoding_Incoming_Batch.access$3600(Encoding_Incoming_Batch.java:44)
at Inventory_System.Encoding_Incoming_Batch$30.mouseReleased(Encoding_Incoming_Batch.java:915)
at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:290)
at java.awt.Component.processMouseEvent(Component.java:6516)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
at java.awt.Component.processEvent(Component.java:6281)
at java.awt.Container.processEvent(Container.java:2229)
at java.awt.Component.dispatchEventImpl(Component.java:4872)
at java.awt.Container.dispatchEventImpl(Container.java:2287)
at java.awt.Component.dispatchEvent(Component.java:4698)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
at java.awt.Container.dispatchEventImpl(Container.java:2273)
at java.awt.Window.dispatchEventImpl(Window.java:2719)
at java.awt.Component.dispatchEvent(Component.java:4698)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:747)
at java.awt.EventQueue.access$300(EventQueue.java:103)
at java.awt.EventQueue$3.run(EventQueue.java:706)
at java.awt.EventQueue$3.run(EventQueue.java:704)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
at java.awt.EventQueue$4.run(EventQueue.java:720)
at java.awt.EventQueue$4.run(EventQueue.java:718)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:717)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)

BUILD STOPPED (total time: 1 minute 45 seconds)

最佳答案

PreparedStatement 应该与实际值的位置标记 (?) 一起使用，这有助于防止 SQL 注入(inject)攻击，但也可以让您少考虑传递不同类型值所需的编码(例如 int、double 和 Date)。

因此，您无需在 sqlItems 中指定实际值，而是提供带有占位符的抽象查询，例如...

String sqlItems="INSERT INTO IncomingTransactionItems(Incoming_Transaction_Number, Transaction_ID,Item_Number, Quantity, Unit_Price,Total_Price)values(?,?,?,?,?, ?)"

然后，您将每列的值“绑定(bind)”到语句...

pst.setString(1, ItemList[0]);

其中 1 表示第一个通配符，2 表示第二个通配符，依此类推(请记住，这不是 0 索引的，它是 1 索引，其中 1 是可能的最低值)。

所以，它可能看起来更像这样......

int rowCount=jTable3.getRowCount();
String[] ItemList=new String[6];
String sqlItems="INSERT INTO IncomingTransactionItems(Incoming_Transaction_Number, Transaction_ID,Item_Number, Quantity, Unit_Price,Total_Price)values(?,?,?,?,?, ?)"
for(int rowctr=1;rowctr<rowCount;rowctr++){
     ItemList[0]= String.valueOf(rowctr);                 //    Incoming Transaction_Number
     ItemList[1]= this.jTextField1.getText();             //    Transaction ID
     ItemList[2]=this.jTable3.getModel().getValueAt(rowctr, 0).toString();              //    Item Number
     ItemList[3]=this.jTable3.getModel().getValueAt(rowctr, 2).toString();              //    Quantity
     ItemList[4]=this.jTable3.getModel().getValueAt(rowctr, 3).toString();              //    Unit Price
     ItemList[5]=this.jTable3.getModel().getValueAt(rowctr, 4).toString();              //    Total Price     

     try (PreparedStatement pst = conn.prepareStatement(sqlItems)){
        // You could do this with a for-loop, I've done it long
        // to demonstrate the use of PreparedStatement
        pst.setString(1, ItemList[0]);
        pst.setString(2, ItemList[1]);
        pst.setString(3, ItemList[2]);
        pst.setString(4, ItemList[3]);
        pst.setString(5, ItemList[4]);
        pst.setString(6, ItemList[5]);
        pst.executeUpdate();
    }
    catch(SQLException | HeadlessException e){
       Logger.getLogger(Encoding_Incoming_Batch.class.getName()).log(Level.SEVERE, null, e);
    }
}
this.UpdateJTable();

这也意味着您可以做一些更像......的事情

pst.setLong(1, rowctr);
pst.setString(2, (String)this.jTable3.getModel().getValueAt(rowctr, 0));
pst.setInt(3, (int)this.jTable3.getModel().getValueAt(rowctr, 1));
pst.setInt(4, (int)this.jTable3.getModel().getValueAt(rowctr, 2));
pst.setDouble(5, (double)this.jTable3.getModel().getValueAt(rowctr, 3));
pst.setDouble(6, (double)this.jTable3.getModel().getValueAt(rowctr, 4));

并使用所需的数据类型为列值添加种子(我猜测此处的实际类型)

您还应该调用 executeUpdate 而不是 execute，它可以为您提供有关操作的更多信息(例如受影响的行数)

看看Using Prepared Statements了解更多详情

I checked the Incoming Transaction Number-Primary Key of My Table, If I Might be SAving a duplicate Value but It does not because the Program gets the Number of Values in the Table and Increments it by one for a new record.

明智的做法是使用自动增量键，并且如果可能的话，不要在插入语句中包含主键，这可以克服可能的线程竞争条件

基于 Java General Error On Insert...??? 的信息，您当前的连接中可能存在未提交的更改，这会导致后续更新失败

假设连接设置为autoCommit ，您可以简单地使用 try-with-resources block 自动关闭连接(和 PreparedStatement)

try (Connection conn = ) {
    String query = "...";
    try (PreparedStatement stmt = conn.prepareStatement(query)) {
        if (stmt.executeUpdate() == 0) {
            System.err.println("Possible error with update, did not update any rows");
        }
    }
} catch (SQLException exp) {
    exp.printStackTrace();
}

否则您应该手动提交更新

try (Connection conn = ) {
    String query = "...";
    try (PreparedStatement stmt = conn.prepareStatement(query)) {
        if (stmt.executeUpdate() == 0) {
            System.err.println("Possible error with update, did not update any rows");
        } else {
            conn.commit();
        }
    }
} catch (SQLException exp) {
    exp.printStackTrace();
}

关于java - 获取表数据并保存到数据库 - SQLEXception 一般错误，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31444452/

26 4 0
文章推荐： java - Astoria 项目究竟如何设法在 Windows(移动)上运行 Android/Java 应用程序
文章推荐： uber-api - 使用 Apple Pay 作为付款方式请求 Uber 乘车
文章推荐： spring - 如何在Spring的rest模板Header中设置NTLM身份验证
文章推荐： java - ParadiseInfo2 变量错误代码
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com