Multiple Delete MSSQL delete queries in Java Program [closed]

Question

I am writing a program where I am changing roles. The Change Role process involves deleting from two tables(to clear the current role/group), inserting into two tables(to set the role/group).

I have allowMultipleQueries = true in my connection string, but it looks like only the first query is running.

The database is an MSSQL db.

Is there a way to run both queries? Can I delete from both tables?

The code I have is below:

JButton changeRoleBtn = new JButton("Change Role");
    changeRoleBtn.setBounds(50, 375, 150, 30);
    changeRoleBtn.setToolTipText("Changes the role of the User");
    changeRoleBtn.addActionListener(new ActionListener()
    {
        public void actionPerformed(ActionEvent e)
        {
            if (requesterRole.isSelected())
            {
                StringBuffer getRolesQuery3 = new StringBuffer("delete from hib.personrole where personid = '");
                getRolesQuery3.append(userID).append("'");
                StringBuffer getRolesQuery4 = new StringBuffer("delete from hib.persongroup where personid = '");
                getRolesQuery4.append(userID).append("'");
                try 
                {
                    ResultSet rs = stmt.executeQuery(getRolesQuery3.toString());
                    ResultSet rs1 = stmt.executeQuery(getRolesQuery4.toString());

                    boolean empty = true;
                    if(empty)
                    {
                        userRoleLbl.setText("The User is a Requester");
                        System.out.println(rs);
                        System.out.println(rs1);
                    }
                }
                catch(Exception e2)
                {
                    System.out.println(e2);
                }
            }
        }
    });

I have changed it to have the prepared statement I get the following error though when I run it. java.sql.SQLException: Invalid parameter index 2.

    changeRoleBtn.addActionListener(new ActionListener()
    {
        public void actionPerformed(ActionEvent e)
        {
            if (requesterRole.isSelected())
            {
                try
                {
                    PreparedStatement ps1, ps2;
                    ps1 = con.prepareStatement("delete from hib.personrole where personid = ?");
                    ps2 = con.prepareStatement("delete from hib.persongroup where personid = ?");

                    ps1.setInt(1, userID);
                    ps2.setInt(2, userID);

                    ps1.executeQuery();
                    ps2.executeQuery();

                    con.commit();

                    userRoleLbl.setText("The user is a requester");

                }
                catch(Exception e3)
                {
                    e3.printStackTrace();
                }

            }
        }
    });

Answer 1

I believe it will be more appropriate to use the batch here.When you send several SQL statements to the database at once, you reduce the amount of communication overhead, thereby improving performance.

JDBC drivers are not required to support this feature. You should use the DatabaseMetaData.supportsBatchUpdates() method to determine if the target database supports batch update processing. The method returns true if your JDBC driver supports this feature.

• The addBatch() method of Statement, PreparedStatement, and CallableStatement is used to add individual statements to the batch. The executeBatch() is used to start the execution of all the statements grouped together.
• The executeBatch() returns an array of integers, and each element of the array represents the update count for the respective update statement.
• Just as you can add statements to a batch for processing, you can remove them with the clearBatch() method. This method removes all the statements you added with the addBatch() method. However, you cannot selectively choose which statement to remove.

Sample code

con.setAutoCommit(false);
stmt.addBatch(getRolesQuery3);  
stmt.addBatch(getRolesQuery4);
ResultSet rs = stmt.executeBatch();

Answer 2

You have to execute each delete instruction independently, but there's no restriction to do it.

As I said in my comment, your code is vulnerable to SQL injection, so I suggest you use prepared statements:

 // ...
 PreparedStatement ps1, ps2;
 ps1 = con.prepareStatement("delete from hib.personrole where personid = ?");
 ps2 = con.prepareStatement("delete from hib.persongroup where personid = ?");

 ps1.setString(1, userID);
 ps2.setString(1, userID);

 ps1.execute();
 ps2.execute();
 // ...

Further reference:

• http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
• http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html

Hope this helps