Solved

Have servlet perform insert and update function

Posted on 2004-10-17
11
256 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
for loop with Set 4 45
countX 22 70
XML Paring  Error - Premature end of file. 7 56
wordcount challenge 11 76
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
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 …

706 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

12 Experts available now in Live!

Get 1:1 Help Now