SQL syntax error when inserting new row

Question

Below is my code for when the user clicks Apply Balance. This works for the first part and the user's balance updates just fine but when I try and execute the second statement a SQL syntax errors appears. What may be causing the problem?

public void mouseClicked(MouseEvent e) {
            if (cal == true) {
                try {
                int balchange = updatebal;
                String username = (String) userPicker.getSelectedItem();
                Connection conn = DriverManager.getConnection( Host, Name, Pass );  
                PreparedStatement pst = conn.prepareStatement("UPDATE table_1 SET user_bal='"+balchange+"' WHERE user_name='"+username+"'");
                pst.execute();


                String sign = "£";
                String PayName = textField_1.getText();
                PreparedStatement pst2 = conn.prepareStatement("INSERT INTO payment_info (payment_name, payment_amount, payment_date, username)"+" VALUES ('"+PayName+"', '"+sign+balchange+"', '"+Date+"', '"+username+"'");
                pst2.execute();
                cal = false;
                } 
                catch (Exception e3) {
                e3.printStackTrace();
                }
            }
            else {
                JOptionPane.showMessageDialog(null, "Please use the Calculator First!");
            }
        }

Below is the stack trace I get when I run this.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
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:939)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
at AdminPanelMain$7.mouseClicked(AdminPanelMain.java:444)
at java.awt.Component.processMouseEvent(Component.java:6538)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
at java.awt.Component.processEvent(Component.java:6300)
at java.awt.Container.processEvent(Container.java:2236)
at java.awt.Component.dispatchEventImpl(Component.java:4891)
at java.awt.Container.dispatchEventImpl(Container.java:2294)
at java.awt.Component.dispatchEvent(Component.java:4713)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4534)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
at java.awt.Container.dispatchEventImpl(Container.java:2280)
at java.awt.Window.dispatchEventImpl(Window.java:2750)
at java.awt.Component.dispatchEvent(Component.java:4713)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:709)
at java.awt.EventQueue$3.run(EventQueue.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
at java.awt.EventQueue$4.run(EventQueue.java:731)
at java.awt.EventQueue$4.run(EventQueue.java:729)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

Answer 1

Look at here:

PreparedStatement pst2 = conn.prepareStatement("INSERT INTO payment_info (payment_name, payment_amount, payment_date, username)"+" VALUES ('"+PayName+"', '"+sign+balchange+"', '"+Date+"', '"+username+"'")

It seems like you lack of right bracket in your sql statement which should be VALUES() not VALUES(.

By the way, there are several ORM systems, such as mybatis or hibernate, why not choose one? They are not only can help you reduce work also makes it easy to access your database.

Answer 2

Your second query is missing closing bracket in Values clause.

Instead of directly appending parameters in the query use parameterized queries.

public void mouseClicked(MouseEvent e) {
    if (cal == true) {
        try {
        int balchange = updatebal;
        String username = (String) userPicker.getSelectedItem();
        Connection conn = DriverManager.getConnection( Host, Name, Pass );  
        PreparedStatement pst = conn.prepareStatement("UPDATE table_1 SET user_bal=? WHERE user_name=?");

        pst.setInt(1, balchange);
        pst.setString(2, username);

        pst.execute();

        String sign = "£";
        String PayName = textField_1.getText();
        PreparedStatement pst2 = conn.prepareStatement("INSERT INTO payment_info (payment_name, payment_amount, payment_date, username)"
        + " VALUES (?, ?, ?, ?)");

        pst2.setString(1, PayName);
        pst2.setString(2, sign + balchange);
        pst2.setString(3, "Date");//if it's date column use ps2.setDate(3, new Date());
        pst2.setString(4, username);

        pst2.execute();
        cal = false;
        } 
        catch (Exception e3) {
        e3.printStackTrace();
        }
    }
    else {
        JOptionPane.showMessageDialog(null, "Please use the Calculator First!");
    }
}

That will look much cleaner and will be easier to write. Most of all it will save you from SQL Injection attacks.

Here's Oracle Docs for parameterized queries https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html