Solved

Have servlet perform insert and update function

Posted on 2004-10-17
11
271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses

751 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