[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

USE SERVLET TO UPDATE TWO TABLES

I have a servlet I use to update a database. Basically what happens is the user will reset a certain field to 'No' for all users when required to do so. My problem is this update only includes one table and I need it to update two tables. Below is the code I use to update the first table. How can I modify this code to include my second table ("pers_riders") so I can also perform the query:


UPDATE pers_riders SET underway='No'

RCMB


Code of current page:


      public boolean ResetUW() {

            Connection conn = null;
            PreparedStatement pstmt = null;

            // perform database update
            try {
                  conn = ConnectionManager.getConnection();

                  String sql =
                        "UPDATE pers_members SET underway=? WHERE prospective_gain = 'No'";

                  PreparedStatement statement = conn.prepareStatement(sql);
                  statement.setString(1, Underway);
                  
                  statement.executeUpdate();
                  System.out.println("#rows= "+statement.executeUpdate());

                  if (statement != null)
                        statement.close();
                  if (conn != null)
                        conn.close();

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

            return true;
      }
0
rcmb
Asked:
rcmb
  • 6
  • 2
1 Solution
 
zzynxSoftware engineerCommented:
What's the problem?
Just create another prepared statement and executeUpdate() that also?
Or am I missing something?
0
 
zzynxSoftware engineerCommented:
I mean:

               String sql =
                    "UPDATE pers_members SET underway=? WHERE prospective_gain = 'No'";

               PreparedStatement statement = conn.prepareStatement(sql);
               statement.setString(1, Underway);
               
               int n = statement.executeUpdate();
//               System.out.println("#rows= "+statement.executeUpdate());        // Don't call executeUpdate twice!!!  <<<<<<<<<
                System.out.println("#rows= "+n);

               if (statement != null)
                    statement.close();

               // New one
               sql = ................ ; // your update statement for the 2nd table
               statement = conn.prepareStatement(sql)
               statement.setString(1, Underway); // or whatever

               n = statement.executeUpdate();
               System.out.println("#rows= "+n);

               if (statement != null)
                    statement.close();
               if (conn != null)
                    conn.close();
0
 
TimYatesCommented:
And just to be sure, move your final closes into a finally block:

so:

              if (statement != null)
                    statement.close();
               if (conn != null)
                    conn.close();

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

becomes

          } catch (Exception e) {
                  e.printStackTrace();
          } finally {
              try { if (statement != null) statement.close(); } catch( SQLException ex ) {}
               try { if (conn != null) conn.close(); } catch( SQLException ex ) {}
          }
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
rcmbAuthor Commented:
Okay -- Here is what I have done:

Look at my notes:

      public boolean ResetUW() {

            Connection conn = null;
            PreparedStatement pstmt = null;

            // perform database update
            try {
                  conn = ConnectionManager.getConnection();


                  String sql =
                        "UPDATE pers_members SET underway=? WHERE prospective_gain = 'No'";

                  PreparedStatement statement = conn.prepareStatement(sql);
                  statement.setString(1, Underway);
                  
                  int n = statement.executeUpdate();
                  System.out.println("# crewmembers rows udpated = "+n);

                  if (statement != null)
                        statement.close();

                  sql =
                        "UPDATE pers_riders SET underway = ? WHERE underway = 'Yes'";

                  statement = conn.prepareStatement(sql);

// On this line I get an error that states Duplicate local variable
                  PreparedStatement statement = conn.prepareStatement(sql);
                  statement.setString(1, Underway);
                  
                  n = statement.executeUpdate();
                  System.out.println("# rider rows udpated = "+n);

                  if (statement != null)
                        statement.close();
                  if (conn != null)
                        conn.close();
            
            } catch (Exception e) {
                     e.printStackTrace();
            }

            return true;
      }

Tim -- When I try to implement your code at the end I get "statement cannot be defined"

RCMB
0
 
zzynxSoftware engineerCommented:
>>// On this line I get an error that states Duplicate local variable
>>               PreparedStatement statement = conn.prepareStatement(sql);

Just remove that line it's obsolete
0
 
zzynxSoftware engineerCommented:
That line is a copy of the line above it
0
 
rcmbAuthor Commented:
Okay -- I have it figured out.

I renamed the second statement to statement2 and added a close for statement2. This worked like a champ.

Thanks for your help.

RCMB
0
 
zzynxSoftware engineerCommented:
>> Tim -- When I try to implement your code at the end I get "statement cannot be defined"
Move
        PreparedStatement statement

outside the try - catch block
0
 
zzynxSoftware engineerCommented:
Thanks for accepting.

Make sure you don't have

           statement = conn.prepareStatement(sql);

           PreparedStatement statement2 = conn.prepareStatement(sql);

Don't do the update twice!!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now