java - 为什么我收到此错误 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException : Unknown column in 'sdate' 'field list'

Question

我正在我的项目中使用 Java swing、JDBC 和 MySQL 数据库制作一个小型库存管理项目。我有三个按钮，“购买”、“销售”和“清除”。如果我点击购买按钮，它会通过添加产品名称、价格、数量等更新我的“产品、购买”表。购买按钮工作正常，但销售按钮不工作。

这是我的项目 View :

当我点击我的销售按钮时，它给我这个异常:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'sdate' in 'field list' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192) at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:823) at com.imp.ProductController.SaveSale(ProductController.java:76) at com.imp.Inventory$3.actionPerformed(Inventory.java:154)

at javax.swing.AbstractButton.fireActionPerformed(Unknown Source) at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.setPressed(Unknown Source) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source) at java.awt.Component.processMouseEvent(Unknown Source) at javax.swing.JComponent.processMouseEvent(Unknown Source) at java.awt.Component.processEvent(Unknown Source) at java.awt.Container.processEvent(Unknown Source) at java.awt.Component.dispatchEventImpl(Unknown Source) at java.awt.Container.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source) at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source) at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source) at java.awt.Container.dispatchEventImpl(Unknown Source) at java.awt.Window.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.EventQueue.dispatchEventImpl(Unknown Source) at java.awt.EventQueue.access$500(Unknown Source) at java.awt.EventQueue$3.run(Unknown Source) at java.awt.EventQueue$3.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source) at java.awt.EventQueue$4.run(Unknown Source) at java.awt.EventQueue$4.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source) at java.awt.EventQueue.dispatchEvent(Unknown Source) at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.run(Unknown Source)

我该如何解决这个问题？

这是我的源代码:

IMP/src/com/imp/dao/DatabaseConnectionHelper.java

package com.imp.dao;

import java.sql.Connection;
import java.sql.DriverManager;

public class DatabaseConnectionHelper {
    public static void main(String[] args) throws Exception {
        getConnection();

    }
    public static Connection getConnection() throws Exception {
        try {
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/imp";
            String username = "root";
            String password = "password";
            Class.forName(driver);

            Connection conn = DriverManager.getConnection(url, username, password);
            System.out.println("Database Connected");
            return conn;
        } 
        catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }
}

/IMP/src/com/imp/ProductController.java

package com.imp;

import java.awt.List;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.swing.DefaultComboBoxModel;
import javax.swing.JComboBox;

import com.imp.dao.DatabaseConnectionHelper;
import com.mysql.jdbc.CallableStatement;

public class ProductController {
    public static boolean SavePname ( String pname ) throws SQLException {
        Connection myConn = null;
        CallableStatement myCsmt = null;
        boolean check = true;

        try {
            myConn = DatabaseConnectionHelper.getConnection();
            myCsmt = (CallableStatement) myConn.prepareCall("{ CALL save_product(?) }");

            myCsmt.setString(1, pname);
            check = myCsmt.execute();
        } 
        catch (Exception exp) {
            exp.printStackTrace();
        }
        finally{
            Close (myConn, myCsmt);
        }
        return check;
    }

    public static boolean SavePurchase ( String pname, String price, String pdate, String qty ) throws SQLException {
        Connection myConn = null;
        CallableStatement myCsmt = null;
        boolean check = true;

        try {
            myConn = DatabaseConnectionHelper.getConnection();
            myCsmt = (CallableStatement) myConn.prepareCall("{ CALL save_purchase(getProductid(?), ?, ?, ?) }");

            myCsmt.setString(1, pname);
            myCsmt.setString(2, price);
            myCsmt.setString(3, pdate);
            myCsmt.setString(4, qty);

            check = myCsmt.execute();
        } 
        catch (Exception exp) {
            exp.printStackTrace();
        }
        finally{
            Close (myConn, myCsmt);
        }
        return check;
    }

    public static boolean SaveSale ( String pname, String price, String Sdate, String qty ) throws SQLException {
        Connection myConn = null;
        CallableStatement myCsmt = null;
        boolean check = true;

        try {
            myConn = DatabaseConnectionHelper.getConnection();
            myCsmt = (CallableStatement) myConn.prepareCall("{ CALL save_sale(getProductid(?), ?, ?, ?) }");

            myCsmt.setString(1, pname);
            myCsmt.setString(2, price);
            myCsmt.setString(3, Sdate);
            myCsmt.setString(4, qty);

            check = myCsmt.execute();
        } 
        catch (Exception exp) {
            exp.printStackTrace();
        }
        finally{
            Close (myConn, myCsmt);
        }
        return check;
    }

    public static void LoadComboBox ( JComboBox combo ) throws SQLException {
        Connection myConn = null;
        CallableStatement myCsmt = null;
        ResultSet myRs = null;

        try {
            myConn = DatabaseConnectionHelper.getConnection();
            myCsmt = (CallableStatement) myConn.prepareCall("{ CALL listProduct() }");

            myCsmt.execute();
            myRs = myCsmt.getResultSet();

           ArrayList pList = new ArrayList();

           while ( myRs.next() ) {
               pList.add(myRs.getString(1));
           }

           combo.setModel(new DefaultComboBoxModel(pList.toArray()));
           combo.insertItemAt("Select one", 0);
           combo.setSelectedIndex(0);
       } 
       catch (Exception exp) {
           exp.printStackTrace();
       }
       finally{
           Close (myConn, myCsmt, myRs);
       }
   }


   private static void Close(Connection myConn, CallableStatement myStmt)
        throws SQLException {

       if (myStmt != null) {
           myStmt.close();
       }

       if (myConn != null) {
           myConn.close();
       }
    }

    private static void Close(Connection myConn, CallableStatement myStmt, ResultSet myRs)
        throws SQLException {

        if (myStmt != null) {
            myStmt.close();
        }

        if (myConn != null) {
            myConn.close();
        }

        if (myRs != null) {
            myRs.close();
        }
    }
}

/IMP/src/com/imp/Inventory.java(这是我的框架文件)

package com.imp;

import java.awt.BorderLayout;
import java.awt.EventQueue;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLType;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;

import com.mysql.jdbc.CallableStatement;

import javax.swing.GroupLayout;
import javax.swing.GroupLayout.Alignment;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JComboBox;
import javax.swing.LayoutStyle.ComponentPlacement;
import javax.swing.JTextField;
import javax.swing.JButton;
import java.awt.event.ActionListener; 
import java.awt.event.ActionEvent;
import java.awt.event.ItemListener;
import java.awt.event.ItemEvent;
import com.imp.dao.DatabaseConnectionHelper;
public class Inventory extends JFrame {

    private JPanel contentPane;
    private JTextField PriceTextField;
    private JTextField QtyTextField;
    private JTextField DateTextField;

/**
 * Launch the application.
 */
public static void main(String[] args) {
    EventQueue.invokeLater(new Runnable() {
        public void run() {
            try {
                Inventory frame = new Inventory();
                frame.setVisible(true);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    });
}

/**
 * Create the frame.
 * @throws SQLException 
 */
public Inventory() throws SQLException {
    setTitle("Inventory Management System");
    setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    setBounds(100, 100, 469, 356);
    contentPane = new JPanel();
    contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
    setContentPane(contentPane);

    JLabel lblProductName = new JLabel("Product Name:");

    JComboBox comboBox = new JComboBox();

    comboBox.setEditable(true);

    JLabel lblNewLabel = new JLabel("Available Quantity:");

    JLabel lblNewLabel_1 = new JLabel("AVG Purchase Price:");

    JLabel lblNewLabel_2 = new JLabel("Price:");

    JLabel lblNewLabel_3 = new JLabel("Quantity:");

    JLabel lblNewLabel_4 = new JLabel("Date:");

    JLabel lbQty = new JLabel("");

    JLabel lbPrice = new JLabel("");

    PriceTextField = new JTextField();
    PriceTextField.setColumns(10);

    QtyTextField = new JTextField();
    QtyTextField.setColumns(10);

    DateTextField = new JTextField();
    DateTextField.setColumns(10);

    JButton btnPurchase = new JButton("Purchase");
    btnPurchase.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent arg0) {
            boolean check = false;

            if ( comboBox.getSelectedIndex() < 0 ){
                try {
                    ProductController.SavePname(comboBox.getSelectedItem().toString());
                } 
                catch (SQLException e) {    
                    e.printStackTrace();
                }
            }

            try {
                check = ProductController.SavePurchase(comboBox.getSelectedItem().toString(), 
                        PriceTextField.getText(), DateTextField.getText(), QtyTextField.getText());
            } 
            catch (SQLException e) {
                e.printStackTrace();
            }

            if (!check){
                JOptionPane.showMessageDialog(rootPane, "Purchase Save SuccessFully.....!!....");
                // By this method we can load all product from our database
                try {
                    ProductController.LoadComboBox(comboBox);
                    Clear();
                } 
                catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        private void Clear() {
            comboBox.setSelectedIndex(0);
            lbQty.setText("");
            lbPrice.setText("");
            PriceTextField.setText("");
            QtyTextField.setText("");
            DateTextField.setText("");
        }
    });

    JButton btnSale = new JButton("Sale");
    btnSale.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent arg0) {

            boolean check = false;

            if ( comboBox.getSelectedIndex() < 0 ){
                try {
                    ProductController.SavePname(comboBox.getSelectedItem().toString());
                } 
                catch (SQLException e) {    
                    e.printStackTrace();
                }
            }

            try {
                check = ProductController.SaveSale(comboBox.getSelectedItem().toString(), 
                        PriceTextField.getText(), DateTextField.getText(), QtyTextField.getText());
            } 
            catch (SQLException e) {
                e.printStackTrace();
            }

            if (!check){
                JOptionPane.showMessageDialog(rootPane, "Sale Save SuccessFully.....!!....");
                // By this method we can load all product from our database
                try {
                    ProductController.LoadComboBox(comboBox);
                    Clear();
                } 
                catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        private void Clear() {
            comboBox.setSelectedIndex(0);
            lbQty.setText("");
            lbPrice.setText("");
            PriceTextField.setText("");
            QtyTextField.setText("");
            DateTextField.setText("");
        }
    });

    comboBox.addItemListener(new ItemListener() {
        @SuppressWarnings({ "null", "resource" })
        public void itemStateChanged(ItemEvent arg0) {
            if ( comboBox.getSelectedIndex() > 0 ) {
                Connection myConn = null;
                CallableStatement myCsmt = null;
                ResultSet myRs = null;

                try {
                    myConn = DatabaseConnectionHelper.getConnection();
                    myCsmt = (CallableStatement) myConn.prepareCall("{?= call getProductQty(?)}");

                    myCsmt.registerOutParameter(1, java.sql.Types.INTEGER);
                    myCsmt.setString(2, comboBox.getSelectedItem().toString());

                    myCsmt.execute();

                    int output = myCsmt.getInt(1);
                    //JLabel lbQty = null;
                    //JLabel lbPrice = null;

                    lbQty.setText(String.valueOf(output));

                    //
                    myCsmt = (CallableStatement) myConn.prepareCall("{CALL avg_price(getProductid(?))}");
                    myCsmt.setString(1, comboBox.getSelectedItem().toString());
                    myCsmt.execute();

                    myRs = myCsmt.getResultSet();

                    while ( myRs.next() ) {
                        lbPrice.setText(myRs.getString(1));
                    }
                } 
                catch (Exception e) {
                    e.printStackTrace();
                }
                finally{
                    try {
                        myConn.close();
                        myCsmt.close();
                        myRs.close();
                    } 
                    catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
    });


    JButton btnClear = new JButton("Clear");
    GroupLayout gl_contentPane = new GroupLayout(contentPane);
    gl_contentPane.setHorizontalGroup(
        gl_contentPane.createParallelGroup(Alignment.LEADING)
            .addGroup(gl_contentPane.createSequentialGroup()
                .addContainerGap()
                .addGroup(gl_contentPane.createParallelGroup(Alignment.LEADING)
                    .addComponent(lblProductName)
                    .addComponent(lblNewLabel)
                    .addComponent(lblNewLabel_1)
                    .addComponent(lblNewLabel_2)
                    .addComponent(lblNewLabel_3)
                    .addComponent(lblNewLabel_4)
                    .addComponent(btnPurchase))
                .addGap(57)
                .addGroup(gl_contentPane.createParallelGroup(Alignment.TRAILING)
                    .addComponent(lbPrice, GroupLayout.DEFAULT_SIZE, 241, Short.MAX_VALUE)
                    .addComponent(lbQty, GroupLayout.DEFAULT_SIZE, 241, Short.MAX_VALUE)
                    .addComponent(DateTextField, 241, 241, 241)
                    .addComponent(QtyTextField, 241, 241, 241)
                    .addComponent(PriceTextField, 241, 241, 241)
                    .addComponent(comboBox, 0, 241, Short.MAX_VALUE)
                    .addGroup(gl_contentPane.createSequentialGroup()
                        .addGap(25)
                        .addComponent(btnSale)
                        .addPreferredGap(ComponentPlacement.RELATED, 106, Short.MAX_VALUE)
                        .addComponent(btnClear)))
                .addContainerGap(144, Short.MAX_VALUE))
    );
    gl_contentPane.setVerticalGroup(
        gl_contentPane.createParallelGroup(Alignment.LEADING)
            .addGroup(gl_contentPane.createSequentialGroup()
                .addContainerGap()
                .addGroup(gl_contentPane.createParallelGroup(Alignment.BASELINE)
                    .addComponent(lblProductName)
                    .addComponent(comboBox, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(ComponentPlacement.UNRELATED)
                .addGroup(gl_contentPane.createParallelGroup(Alignment.BASELINE)
                    .addComponent(lblNewLabel)
                    .addComponent(lbQty, GroupLayout.PREFERRED_SIZE, 21, GroupLayout.PREFERRED_SIZE))
                .addGap(18)
                .addGroup(gl_contentPane.createParallelGroup(Alignment.BASELINE)
                    .addComponent(lblNewLabel_1)
                    .addComponent(lbPrice, GroupLayout.PREFERRED_SIZE, 21, GroupLayout.PREFERRED_SIZE))
                .addGap(18)
                .addGroup(gl_contentPane.createParallelGroup(Alignment.BASELINE)
                    .addComponent(lblNewLabel_2)
                    .addComponent(PriceTextField, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
                .addGap(18)
                .addGroup(gl_contentPane.createParallelGroup(Alignment.BASELINE)
                    .addComponent(lblNewLabel_3)
                    .addComponent(QtyTextField, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
                .addGap(18)
                .addGroup(gl_contentPane.createParallelGroup(Alignment.TRAILING)
                    .addComponent(lblNewLabel_4)
                    .addComponent(DateTextField, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(ComponentPlacement.RELATED, 50, Short.MAX_VALUE)
                .addGroup(gl_contentPane.createParallelGroup(Alignment.BASELINE)
                    .addComponent(btnPurchase)
                    .addComponent(btnClear)
                    .addComponent(btnSale))
                .addGap(48))
    );
    contentPane.setLayout(gl_contentPane);

    // By this method we can load all product from our database
    ProductController.LoadComboBox(comboBox);
}

这是我的产品表结构:

这是我的销售表结构:

这是我的采购表结构:

这是我的 save_sale 存储过程结构

Answer 1

某处你有 Sdate 和 pdate .. 所以 .. sdate 和 Sdate 不一样，还要检查 pdate

    try {
        myConn = DatabaseConnectionHelper.getConnection();
        myCsmt = (CallableStatement) myConn.prepareCall("{ CALL save_sale(getProductid(?), ?, ?, ?) }");

        myCsmt.setString(1, pname);
        myCsmt.setString(2, price);
        myCsmt.setString(3, Sdate); /// this one  
        myCsmt.setString(4, qty);

查看您的图片 .. 您尝试使用 sdate 插入购买中 .. 但在购买中该列名为 pdate ..