Results 1 to 3 of 3
  1. #1
    techsing14 is offline Member
    Join Date
    Oct 2010
    Posts
    24
    Rep Power
    0

    Default How to prompt if there is no match in database using servlet??

    Hi am trying to compare two columns(serial_no) in two different tables Table1 and Table2, If there is a match I want to update 10 variables to table1 if no match then simply copy the serial_no to table1, I wrote a join for the first part now I don;t have any idea about how to copy if there is no match....Please can anyone look into my code and help me out....here is my code

    StatusServlet
    Java Code:
    package update;
    
    import java.io.*;
    import javax.servlet.*;
    import javax.servlet.http.*;
    import java.sql.*;
    import java.util.*;
    
    public class StatusServlet extends HttpServlet {
    
        public void doPost(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
            response.setContentType("text/html");
            PrintWriter out = response.getWriter();
           
            String connectionURL = "jdbc:oracle:thin:localhost:1521:xe";
            String driver = "oracle.jdbc.driver.OracleDriver";
            String user = "hr";
            String pass = "hr";
            String var1 = request.getParameter("var1");
            String var2 = request.getParameter("var2");
             String var3 = request.getParameter("var3");
             String var4 = request.getParameter("var4");
             String var5 = request.getParameter("var5");
            String var6 = request.getParameter("var6");
            String var7 = request.getParameter("var7");
            String var8 = request.getParameter("var8");
            String var9 = request.getParameter("var9");
            String var10 = request.getParameter("var10");
            
    
    
    
            Connection connection = null;
            PreparedStatement pstatement = null;
            try {
                Class.forName(driver).newInstance();
               connection = DriverManager.getConnection(connectionURL, user, pass);
    
    
             pstatement = connection.prepareStatement("update Table1 set var1 ='"+ var1+"',var2='"+var2+"',var3 = '"+var3+"',var4 = '"+var4+"',var5='"+var5+"',var6='"+var6+"',var7='"+var7+"',var8='"+var8+"',var9='"+var9+"',var10='"+var10+"' where SerialNo in(select DB.SerialNo from Table1 DB, Table2 OUT where OUT.SerialNo = DB.SerialNo and DB.Transaction_Status = 'IN')");
             int i = pstatement.executeUpdate();
             if (i>0) {
                         out.print("<br>");
               out.print("<table  align=\"center\" font=\"18\" style=\"background-color:#efefef\"width=\"50%\" border=\"1\">");
               out.print("<tr><th>Order Issued, Database Updated Successfully</th></tr>");
               out.print("</table>");
    
                       } else {
    
                       out.print("<script language=\"javascript\">");
                       out.print("alert(\"Order Not Issued, Please try again!\");");
                       out.print("document.location=\"add_new_transaction.jsp\"");
                       out.print("</script>");
                 }
              
                 pstatement.close();
                 }
      catch(ClassNotFoundException e){
      out.println("Couldn't load database driver: " + e.getMessage());
      }
      catch(SQLException e){
      out.println("SQLException caught: " + e.getMessage());
      }
      catch (Exception e){
      out.println("AnyException: " +e.getMessage());
      }
      finally {
      // Always close the database connection.
      try {
      if (connection != null) connection.close();
      }
      catch (SQLException ignored){
      out.println(ignored);
      }
      }

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,830
    Rep Power
    19

    Default

    Oracle has a MERGE command, though I think it's not intended for multi-threaded situations (you can still get a duplicate key error from it).
    The most solid way is do an INSERT, catch any duplicate key error and do the UPDATE. Do this in a stored procedure (it's easier to handle the duplicate error).

  3. #3
    techsing14 is offline Member
    Join Date
    Oct 2010
    Posts
    24
    Rep Power
    0

Similar Threads

  1. entering servlet form values into database
    By mallikanala in forum New To Java
    Replies: 2
    Last Post: 06-20-2011, 11:25 AM
  2. Replies: 0
    Last Post: 04-23-2011, 05:15 PM
  3. How to match arrayList with a String?
    By Lund01 in forum Advanced Java
    Replies: 2
    Last Post: 10-14-2010, 02:07 PM
  4. Readlines and find a match
    By Lund01 in forum Java Applets
    Replies: 35
    Last Post: 10-12-2010, 08:16 PM
  5. A crazy gui match
    By amarenash23 in forum New To Java
    Replies: 8
    Last Post: 12-30-2009, 03:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •