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

[techsing14]

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

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);
  }
  }

[Tolls]

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

[techsing14]

Thanks alot for the help. Will try this.