I am trying to delete a particular record that I inserted, but it's throwing an error

Question

Bank.java

    import javax.swing.*;
    import javax.swing.table.DefaultTableModel;
    import javax.swing.table.TableModel;
    import java.awt.event.ActionEvent;
    import java.awt.event.ActionListener;
    import java.awt.event.MouseAdapter;
    import java.awt.event.MouseEvent;
    import java.sql.*;
    
    public class bank extends JFrame{
        private JPanel b;
        private JTextField textField1;
        private JTextField textField2;
        private JTextField textField3;
        private JTextField textField4;
        private JTextField textField5;
        private JTextField textField6;
        private JTextField textField7;
        private JTextField textField8;
        private JTable table1;
        private JButton SUBMITButton;
        private JButton DEPOSITButton;
        private JButton WITHDRAWButton;
        private JButton CHECKBALANCEButton;
        private JButton DELETEButton;
    
    
        Integer dep_cash = 0;
        Integer wd_cash = 0;
        Integer acc = 0;
        Integer cash = 0;
//creating connection
        private Connection getConnection(){
            try{
                Class.forName("com.mysql.cj.jdbc.Driver");
                Connection con = DriverManager.getConnection("jdbc:mysql://localhost/bank management", "root", "");
                return con;
            }
            catch (SQLException |ClassNotFoundException e){
                return null;
            }
        }
//constructor   
        public bank(){
            setContentPane(b);
            setTitle("Bank Management System");
            setSize(700, 750);
            setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
            setVisible(true);
    
            show_details();
    
            Connection con = getConnection();
    
            SUBMITButton.addActionListener(new ActionListener() {
                @Override
                public void actionPerformed(ActionEvent e) {
                    String f_name = textField1.getText();
                    String l_name = textField2.getText();
                    String address = textField3.getText();
                    String pin = textField4.getText();
                    String acc = textField5.getText();
                    String mobile = textField6.getText();
                    String email = textField7.getText();
    
    
                    if(pin.length()<=5 && acc.length()<=11 && mobile.length()<=10) {
    
                        input_details(f_name, l_name, address, pin, acc, mobile, email);
                        textField1.setText("");
                        textField2.setText("");
                        textField3.setText("");
                        textField4.setText("");
                        textField5.setText("");
                        textField6.setText("");
                        textField7.setText("");
                    }
                    else {
                        try {
                            if(pin.length()>5){
                                textField4.setText("");
                                JOptionPane.showMessageDialog(null,"Length of the pin field is extended");
    
                            }
                            else if(acc.length()>11){
                                textField5.setText("");
                                JOptionPane.showMessageDialog(null,"Length of the account number field is extended");
    
                            }
                            else if(mobile.length()>10){
                                textField6.setText("");
                                JOptionPane.showMessageDialog(null,"Length of the mobile number field is extended");
    
                            }
                        }
                        catch (Exception e1){
                            JOptionPane.showMessageDialog(null,"Please re-enter the values");
                        }
                    }
    
    
                }
            });
    
            DEPOSITButton.addActionListener(new ActionListener() {
                @Override
                public void actionPerformed(ActionEvent e) {
    
    //                cash = Integer.valueOf(textField8.getText());
    
                    acc = Integer.valueOf(textField5.getText());
                    dep_cash = Integer.valueOf(textField8.getText());
                    Integer total_cash = dep_cash+cash;
    
                    deposit_money(acc, total_cash);
                    dep_cash = 0;
                }
            });
    
            WITHDRAWButton.addActionListener(new ActionListener() {
                @Override
                public void actionPerformed(ActionEvent e) {
                    acc = Integer.valueOf(textField5.getText());
                    wd_cash = Integer.valueOf(textField8.getText());
                    Integer tot_cash = cash-wd_cash;
    
                    if (tot_cash>=0) {
                        withdraw_money(acc, tot_cash);
                        wd_cash = 0;
                    }
    
                    else
                        JOptionPane.showMessageDialog(null, "You don't have enough balance.");
                        textField8.setText("");
                }
            });
    
            CHECKBALANCEButton.addActionListener(new ActionListener() {
                @Override
                public void actionPerformed(ActionEvent e) {
                    check_balance( cash, acc);
                }
            });
    
            DELETEButton.addActionListener(new ActionListener() {
                @Override
                public void actionPerformed(ActionEvent e) {
    
                    if( acc != null) {
                        delete_acc(acc);
    
                    }
                    else
                        JOptionPane.showMessageDialog(null, "Please select a field");
    
                }
            });
            
        }
    
        private void show_details(){
            Connection con = getConnection();
    
            if(con != null){
                String[] column = {"First Name", "Last name", "address", "Pin", "AccountNo.", "MobileNo.", "EmailID", "balance"};
    //            String[] column = {"First Name", "Last name", "address", "Pin", "AccountNo.", "MobileNo.", "EmailID","balance"};
    
                DefaultTableModel model = new DefaultTableModel();
                model.setColumnIdentifiers(column);
    
    
            try {
                String sql = "SELECT * FROM tbl_bank";
    
                PreparedStatement st = null;
                st = con.prepareStatement(sql);
    
                ResultSet rs = st.executeQuery(sql);
    
                while (rs.next()) {
    
                    Object[] ob = new Object[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7),rs.getString(8)};
    //                Object[] ob = new Object[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7)};
                    model.addRow(ob);
                }
    
    
            } catch (SQLException e) {
                e.printStackTrace();
            }
    
    
            table1.setModel(model);
    
                table1.getColumnModel().getColumn(4).setMinWidth(0);
                table1.getColumnModel().getColumn(4).setMaxWidth(0);
                table1.getColumnModel().getColumn(7).setMinWidth(0);
                table1.getColumnModel().getColumn(7).setMaxWidth(0);
    
                table1.addMouseListener(new MouseAdapter() {
                    @Override
                    public void mouseClicked(MouseEvent e) {
                        int i = table1.getSelectedRow();
                        TableModel model = table1.getModel();
    
                        acc = Integer.parseInt(model.getValueAt(i, 4).toString());
    
    
                        textField1.setText(model.getValueAt(i, 0).toString());
                        textField2.setText(model.getValueAt(i, 1).toString());
                        textField3.setText(model.getValueAt(i, 2).toString());
                        textField4.setText(model.getValueAt(i, 3).toString());
                        textField5.setText(model.getValueAt(i, 4).toString());
                        textField6.setText(model.getValueAt(i, 5).toString());
                        textField7.setText(model.getValueAt(i, 6).toString());
                        textField8.setText(model.getValueAt(i, 7).toString());
    
                        cash = Integer.parseInt(model.getValueAt(i,7).toString());
    
                        textField8.setText("");
    
                    }
                });
    
            }
    
            else {
                JOptionPane.showMessageDialog(null, "Connection Error");
            }
    
        }
    
        private void input_details(String f_name,String l_name,String address,String pin,String acc,String mobile,String email){
            Connection con = getConnection();
    
            if (con != null){
                try{
                    String sql = "INSERT INTO `tbl_bank`(`First Name`, `Last name`, `address`, `Pin`, `AccountNo.`, `MobileNo.`, `EmailID`) VALUES (?, ?, ?, ?, ?, ?, ?)";
    
                    PreparedStatement st = null;
                    st = con.prepareStatement(sql);
                    st.setString(1,f_name);
                    st.setString(2,l_name);
                    st.setString(3, address);
                    st.setString(4, pin);
                    st.setString(5, acc);
                    st.setString(6, mobile);
                    st.setString(7, email);
    
    
    
                    int rowCount = st.executeUpdate();
    
                    if (rowCount!=0 ){
                        show_details();
                        JOptionPane.showMessageDialog(null, "data inserted");
                    }else {
                        JOptionPane.showMessageDialog(null, "data not inserted");
                    }
                }
                catch (SQLException ex){
                    ex.printStackTrace();
                }
            }else {
                JOptionPane.showMessageDialog(null, "Connection error");
            }
        }
    
        private void deposit_money(Integer account, Integer c){
    
            Connection con = getConnection();
            if (con != null){
                try{
                    String sql ="update tbl_bank set `balance`=? where `AccountNo.`=?";
                    PreparedStatement st = null;
                    st= con.prepareStatement(sql);
                    st.setInt(1, c);
                    st.setInt(2, account);
    
                    int rowCount = st.executeUpdate();
                    if(rowCount != 0){
                        show_details();
                        JOptionPane.showMessageDialog(null,"Hello "+textField1.getText()+" your account has been credited by Rs."+dep_cash);
                        textField8.setText("");
                    }else {
                        JOptionPane.showMessageDialog(null, "Amount couldn't be deposited");
                    }
                }catch (SQLException|ArrayIndexOutOfBoundsException e){
                    e.printStackTrace();
                }
            }
            else{
                JOptionPane.showMessageDialog(null,"Connection error");
            }
        }
    
        private void withdraw_money(Integer account, Integer c){
            Connection con = getConnection();
            if (con != null){
                try{
                    String sql ="update tbl_bank set `balance`=? where `AccountNo.`=?";
                    PreparedStatement st = null;
                    st= con.prepareStatement(sql);
                    st.setInt(1, c);
                    st.setInt(2, account);
    
                    int rowCount = st.executeUpdate();
                    if(rowCount != 0){
                        show_details();
                        JOptionPane.showMessageDialog(null,"Hello "+textField1.getText()+" your account has been debited by Rs."+wd_cash);
                        textField8.setText("");
                    }else {
                        JOptionPane.showMessageDialog(null, "Amount couldn't be witdrawn");
                    }
                }catch (SQLException|ArrayIndexOutOfBoundsException e){
                    e.printStackTrace();
                }
            }
            else{
                JOptionPane.showMessageDialog(null,"Connection error");
            }
        }
    
        private void check_balance(Integer cas, Integer ac){
    
            Connection con = getConnection();
            if(con != null){
                try{
                    String sql = "SELECT balance=? FROM tbl_bank WHERE AccountNo.=?";
                    PreparedStatement st = null;
                    st= con.prepareStatement(sql);
                    st.setInt(1,cas);
                    st.setInt(2,ac);
                    JOptionPane.showMessageDialog(null, "Hello " + textField1.getText()+ " your account has a balance of Rs." + cas);
                }
                catch (Exception e){
                    e.printStackTrace();
                }
            }
            else{
                JOptionPane.showMessageDialog(null, "Connection error");
            }
        }
    
        private void delete_acc(Integer ac){
    
            Connection con = getConnection();
            if(con!= null){
                try
                {
                    String sql = "delete from tbl_bank where AccountNo.=?";
                    PreparedStatement st = null;
                    st = con.prepareStatement(sql);
    
                    st.setInt(1,ac);
                    int rowCount = st.executeUpdate();
                    if (rowCount != 0){
                        show_details();
                        acc = null;
                        JOptionPane.showMessageDialog(null, "data deleted");
                    }
                    else {
                        JOptionPane.showMessageDialog(null, "data not deleted");
                    }
                }
                 catch (SQLException e){
    
                     e.printStackTrace();
                }
            }
            else{
                JOptionPane.showMessageDialog(null, "Connection error");
            }
        }
    
        public static void main(String[] args) {
            new bank();
        }
    }

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=45654'
 at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
 at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
 at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
 at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
 at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
 at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1348)
 at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025) at bank.delete_acc(bank.java:357)
 at bank$5.actionPerformed(bank.java:151)
 at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1967)
 at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2308)
 at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:405)
 at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262)
 at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:279)
 at java.desktop/java.awt.Component.processMouseEvent(Component.java:6636)
 at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3342)
 at java.desktop/java.awt.Component.processEvent(Component.java:6401)
 at java.desktop/java.awt.Container.processEvent(Container.java:2263)
 at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:5012)
 at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2321)
 at java.desktop/java.awt.Component.dispatchEvent(Component.java:4844)
 at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4918)
 at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Container.java:4547)
 at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Container.java:4488)
 at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2307)
 at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2762)
 at java.desktop/java.awt.Component.dispatchEvent(Component.java:4844)
 at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:772)
 at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:721)
 at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:715)
 at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
 at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
 at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:95)
 at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:745)
 at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:743)
 at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
 at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
 at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:742)
 at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
 at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
 at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
 at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
 at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101) at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)

------------------------------------------------------------------------

DO you have column name like (AccountNo.), ending with dot(.) — Amit Verma, Aug 15 '21 at 04:56
yes... i found the solution "delete from tbl_bank where `AccountNo.` = ?". This works. — Dipesh Paul, Aug 17 '21 at 14:25

score 2 · Accepted Answer · answered Aug 15 '21 at 07:24

From the stack trace in your question, it appears that your [SQL] DELETE statement contains invalid syntax. The statement is

delete from tbl_bank where AccountNo.=?

A period (or full stop or dot) is not allowed in the name of a column in a database table in MySQL. Refer to this stackoverflow question: Is there a “not allowed” character list for mysql table's column names?

Note that in method input_details you have correctly quoted the column name. Hence you need to do the same for the DELETE statement, i.e.

delete from tbl_bank where `AccountNo.` = ?

Also note that you may need to quote the table name as well. Refer to the following part of the MySQL documentation.

Identifier Case Sensitivity

Just missed those quotes... – Dipesh Paul Aug 17 '21 at 14:27 — Dipesh Paul, Aug 17 '21 at 14:27

score -1 · Answer 2 · answered Aug 15 '21 at 05:33

-1

Can you please try the below code?

String sql = "delete from tbl_bank where [AccountNo.]=?";

answered Aug 15 '21 at 05:33

Rahal Kanishka

720
13
27

This should be a comment, not an answer. – Turing85 Aug 15 '21 at 05:54
You're thinking of Microsoft SQL Server, and the OP is using MariaDB. – Mark Rotteveel Aug 15 '21 at 07:56
this didn't worked. Its showing that same error, using "delete from tbl_bank where `AccountNo.` = ?" works. – Dipesh Paul Aug 17 '21 at 14:24

I am trying to delete a particular record that I inserted, but it's throwing an error

2 Answers2