Have servlet perform insert and update function

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
}
LVL 12
rcmbAsked:
Who is Participating?
 
lhankinsConnect With a Mentor Commented:
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
 
CeldricCommented:
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
 
rcmbAuthor Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
CeldricCommented:
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
 
lhankinsCommented:
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
 
CeldricCommented:
Sorry about ignore my stupidity plz.
I didnt read your whole code.
Remember the UPDATE query needs a executeUpdate() too....
0
 
rcmbAuthor Commented:
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
 
CeldricCommented:
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
 
rcmbAuthor Commented:
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
 
Giant2Commented:
>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
 
Giant2Commented:
Sorry for not help you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.