Solved

Have servlet perform insert and update function

Posted on 2004-10-17
11
260 Views
Last Modified: 2013-11-24
I need a little help with this page. This is a servlet that currently only does an Insert into the SQL database. What I need to do is pass an action parameter where it will perform an update or insert based on the action parameter. How would I do that with the following code?

I can write the sql query and such I just need to know how to make the page perform one function if the parameter is "AddRider" and another function if the parameter is UpdateRider.

RCMB

---Code starts here---

public class RiderData extends HttpServlet {
      
      public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

              doGet(request, response);
      }

  public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {

        try{
           
                  Connection conn = null;
                  Statement stmt = null;
                  ResultSet rs = null;
                  conn = ConnectionManager.getConnection();
               
               //Create a statement for executing some SQL
               String sql =
                     "INSERT INTO Riders (SSN, last_name, first_name, middle_name, " +
                     "rank_rate, pri_nok, arrival, arrival_time," +
                     "underway, entered_by, entered_date) " +
                     "VALUES " +
                     "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

               PreparedStatement statement = conn.prepareStatement(sql);
               statement.setString(1, request.getParameter("ssn1") + "-" + request.getParameter("ssn2") + "-" + request.getParameter("ssn3"));
               statement.setString(2, request.getParameter("last_name"));
               statement.setString(3, request.getParameter("first_name"));
               statement.setString(4, request.getParameter("middle_name"));
               statement.setString(5, request.getParameter("rank_rate"));
               statement.setString(6, request.getParameter("pri_nok"));
               statement.setString(7, request.getParameter("arrival_date"));
               statement.setString(8, request.getParameter("arrival_date") + " " + request.getParameter("arrival_time"));
               statement.setString(9, request.getParameter("underway"));
               statement.setString(10, request.getParameter("entered_by"));
               statement.setString(11, request.getParameter("entered_date"));

               statement.executeQuery();

               if (statement != null)
                     statement.close();
               if (conn != null)
                     conn.close();
               System.out.println("#rows="+statement.executeQuery());
               
               RequestDispatcher rd = getServletContext().getNamedDispatcher("Riders");
               rd.forward(request,response);

         } catch (Exception e) {
         }
      } //doGet
}
0
Comment
Question by:rcmb
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 7

Accepted Solution

by:
lhankins earned 500 total points
ID: 12335144
Basically you just want to call request.getParameter() with the name of the action parameter (which I'm assuming is "action" in the snippet below) :

      String parameter = request.getParameter("action");
      if ("AddRider".equals(parameter))
      {
         //--- put your insert SQL here...
      }
      else if ("UpdateRider".equals(parameter))
      {
         //--- put your update SQL here...
      }

Also - you probably want to be using statement.executeUpdate() instead of statement.executeQuery()   (queries don't generally result in modifications to the db)...


0
 
LVL 1

Expert Comment

by:Celdric
ID: 12335178
Just to add to Ihankins very complete answer.
This will work only if you call your page with get parameters i.e:

www.someurl.com/thisservlet?action=AddRider

or

www.someurl.com/thisservlet?action=UpdateRider

I dont know how much you know about Gets and Posts, so please dont get offended if you
already knew this. Sorry in advance.

else, if the variable was sent via Post (Im not 100% plz correct me if im wrong), getParameter
wont work and youll have to use getAttribute instead.

Oh, if you use the getAttribute, youll have to make it String:
String parameter = request.getAttribute("action").toString();

Hope this helped.

0
 
LVL 12

Author Comment

by:rcmb
ID: 12335254
Okay - I am using a URL to pass my information. I have made the changes and what happens now is the query is not updating but adding a whole new record. It is adding my additional information so I am not sure how this is happening.

In my add new rider form (I use the same for both update and insert but I do not include the departure date and time in my initial insert form) everything is working fine. When I access to do an update which basically allows the user to insert the departure date and time the code grabs the data and makes a new record with the departure date and time recorded but not the modified by or modified date.

Here is the code after I made the recommended changes:

public class RiderData extends HttpServlet {
      
      public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

              doGet(request, response);
      }

  public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {

        try{
           
                  Connection conn = null;
                  Statement stmt = null;
                  ResultSet rs = null;
                  conn = ConnectionManager.getConnection();
               
               //Create a statement for executing some SQL
               String Action = request.getParameter("action");
               if (Action.equals("addrider")) {

               String sql =
                     "INSERT INTO Riders (SSN, last_name, first_name, middle_name, " +
                     "rank_rate, pri_nok, arrival, arrival_time," +
                     "underway, entered_by, entered_date) " +
                     "VALUES " +
                     "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

               PreparedStatement statement = conn.prepareStatement(sql);
               statement.setString(1, request.getParameter("ssn1") + "-" + request.getParameter("ssn2") + "-" + request.getParameter("ssn3"));
               statement.setString(2, request.getParameter("last_name"));
               statement.setString(3, request.getParameter("first_name"));
               statement.setString(4, request.getParameter("middle_name"));
               statement.setString(5, request.getParameter("rank_rate"));
               statement.setString(6, request.getParameter("pri_nok"));
               statement.setString(7, request.getParameter("arrival_date"));
               statement.setString(8, request.getParameter("arrival_date") + " " + request.getParameter("arrival_time"));
               statement.setString(9, request.getParameter("underway"));
               statement.setString(10, request.getParameter("entered_by"));
               statement.setString(11, request.getParameter("entered_date"));

               statement.execute();
               
               if (statement != null)
                     statement.close();
               if (conn != null)
                     conn.close();
               System.out.println("#rows="+statement.execute());
               
               } else if (Action.equals("updaterider")) {
               
               String sql =
                     "UPDATE Riders SET SSN=?, last_name=?, first_name=?, middle_name=?, " +
                     "rank_rate=?, pri_nok=?, arrival=?, arrival_time=?, " +
                     "departure=?, departure_time=?, underway=?, modified_by=?, " +
                     "modified_date=? WHERE riderid=?";

               PreparedStatement statement = conn.prepareStatement(sql);
               statement.setString(1, request.getParameter("ssn1") + "-" + request.getParameter("ssn2") + "-" + request.getParameter("ssn3"));
               statement.setString(2, request.getParameter("last_name"));
               statement.setString(3, request.getParameter("first_name"));
               statement.setString(4, request.getParameter("middle_name"));
               statement.setString(5, request.getParameter("rank_rate"));
               statement.setString(6, request.getParameter("pri_nok"));
               statement.setString(7, request.getParameter("arrival_date"));
               statement.setString(8, request.getParameter("arrival_date") + " " + request.getParameter("arrival_time"));
               statement.setString(9, request.getParameter("departure_date"));
               statement.setString(10, request.getParameter("departure_date") + " " + request.getParameter("departure_time"));
               statement.setString(11, request.getParameter("underway"));
               statement.setString(12, request.getParameter("modified_by"));
               statement.setString(13, request.getParameter("modified_date"));
               statement.setString(14, request.getParameter("riderid"));

               statement.execute();

               if (statement != null)
                     statement.close();
               if (conn != null)
                     conn.close();
               System.out.println("#rows= "+statement.execute());

               }

               } catch (Exception e) {
         }
      } //doGet
}
0
 
LVL 1

Expert Comment

by:Celdric
ID: 12335277
Dude, INSERT isnt made to make updates, its made to INSERT a new row..
oh sorry for the aggressiveness ;)

http://www.tizag.com/mysqlTutorial/mysqlupdate.php

In your query, I dont know what the ? do, but following this general idea..

     "INSERT INTO Riders (SSN, last_name, first_name, middle_name, " +
                  "rank_rate, pri_nok, arrival, arrival_time," +
                  "underway, entered_by, entered_date) " +
                  "VALUES " +
                  "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

Give me a few, Ill try to make your query,.
0
 
LVL 7

Expert Comment

by:lhankins
ID: 12335290
are you passing in "AddRider" or "addrider"...?  (in your original post, you listed it as "AddRider", but in your code above you have it as "addrider").    If you want to do a case insensitive comparison, you can use the equalIgnoreCase method on String, e.g :

      if (Action.equalsIgnoreCase("addrider"))
      {
         
      }

Also - I'm a bit murky on what's going to happen when you call execute a second time in your debug statement, e.g :

           statement.execute();

          // <snip>
           System.out.println("#rows= "+statement.execute());

You shouldn't do that... if you want to know the number of rows modified, do this (so that you're not calling .execute twice) :


           int rows = statement.execute();

          // <snip>
           System.out.println("#rows= " + rows);

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 1

Expert Comment

by:Celdric
ID: 12335296
Sorry about ignore my stupidity plz.
I didnt read your whole code.
Remember the UPDATE query needs a executeUpdate() too....
0
 
LVL 12

Author Comment

by:rcmb
ID: 12335326
It is my understanding that execute without any action following (executeUpdate, executeQuery, etc) will just perform whatever function is required -- Am I wrong here?

RCMB
0
 
LVL 1

Expert Comment

by:Celdric
ID: 12335357
No idea, but where do you get riders_id?
is it an autoincrement?
If the get parameter isnt addrider or updaterider it should do anything..I think.

have you tried it with ecexuteupdate?
0
 
LVL 12

Author Comment

by:rcmb
ID: 12335584
The ? are just part of the Prepared statement. It makes it easier to write the SQL string by using a prepared statement and much easier to edit.

I forgive you for your quick outburst. I too get frustrated sometimes with some peoples responses (I spend most of my time in the FrontPage arena).

The code process here confuses me as how can I execute an update query and have it actually insert a new record. I am definitely calling out things right in the servlet because in my insert statement I do not even have the statement inserting departure date and time. It is only called in the update statment.

I did try it with execute update and no change -- I get the riderid from an autoincrement field in the database. I cannot use any other field because you can have multiple entries for the same person in the DB. Bascially what happens is this database will track personnel riding onboard a ship. When the riders comes aboard his/her data is stored and then when they depart we just update with the departure date and time. If they come back we just retireve their data and then do an insert for a new ride.

0
 
LVL 12

Expert Comment

by:Giant2
ID: 12336459
>Okay - I am using a URL to pass my information. I have made the changes and what happens now is the query is not updating but adding a whole new record. It is adding my additional information so I am not sure how this is happening...

In your code I see:
 if (Action.equals("addrider")) {
//insert
}
 } else if (Action.equals("updaterider")) {
//update
}

So, the only thing can drive you to see that you are seeing (everytime insert a new record) is that you pass to the variable Action [use downletter next time] the value "addrider" and not "updaterider".
>String Action = request.getParameter("action");
So,  check the value you pass to the parameter "action".
I believe the problem is this.

Bye, Giant.
0
 
LVL 12

Expert Comment

by:Giant2
ID: 12355289
Sorry for not help you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now