title: insert and update in mysql table using JDBC
body: <p>There are two tables in my database (filedetails and filestatus).</p>
  
  <p>I using the following code to insert value in <code>filedetails</code> table.</p>
  
  <p>In <code>filestatus</code> table i have 3 columns </p>
  
  <pre><code>filenumber,
  fdepartment,
  status(either in or out).
  </code></pre>
  
  <p>In my <code>status.jsp</code> page I have given 3 dropdown list in which 2 dropdownlist take <code>filenumber</code> and <code>department</code> from <code>filedetails</code> table and 1 takes status(which is either IN or OUT).</p>
  
  <p>If status is OUT values are simply inserted in <code>filestatus</code> table but if it is in the values are inserted into <code>filestatus</code> and <code>department</code> corresponding to <code>filenumber</code> gets updated.</p>
  
  <p>The problem is the <code>filenumber</code>, if i use the following code to insert <code>filenumber</code> then how will i update the file number ? </p>
  
  <p>Is ther some other way to do this?</p>
  
  <p>file.jsp</p>
  
  <pre><code>&lt;%@page contentType="text/html" pageEncoding="UTF-8"%&gt;
  &lt;!DOCTYPE html&gt;
  &lt;html&gt;
  &lt;head&gt;
      &lt;meta http-equiv="Content-Type" content="text/html; charset=UTF-8"&gt;
      &lt;title&gt;Insert File Page&lt;/title&gt;
      &lt;style&gt;
  header {
  background-color:teal;
  color:white;
  text-align:center;
  padding:30px;
  }
  
  section {
  width:350px;
  float:left;
  padding:150px;
  }
  footer {
  background-color:black;
  color:white;
  clear:both;
  text-align:center;
  padding:5px;
  }
  &lt;/style&gt;
  &lt;/head&gt;
  &lt;body style="background-color:lightsteelblue;"&gt;
     &lt;%
  String userName = null;
  String sessionID = null;
  Cookie[] cookies = request.getCookies();
  if(cookies !=null){
  for(Cookie cookie : cookies){
  if(cookie.getName().equals("user")) userName = cookie.getValue();
  }
  }
  %&gt;
  &lt;header&gt;
  &lt;h3&gt;Hi &lt;%=userName %&gt;&lt;/h3&gt;
  &lt;/header&gt;
  &lt;a href="create.jsp"&gt;&lt;font color="black"&gt;back&lt;/font&gt;&lt;/a&gt;
  &lt;form action=" LogoutServlet" method="post"&gt;
  &lt;input type="submit" value="Logout" &gt;
  &lt;/form&gt;
  &lt;section&gt;
  &lt;form action="FileServlet" method="post"&gt;
  &lt;h3&gt;Insert File Details&lt;/h3&gt;
      &lt;table border="1"&gt;
          &lt;tbody&gt;
              &lt;tr&gt;
                  &lt;td&gt;File Name :&lt;/td&gt;
                  &lt;td&gt;&lt;input type="text" name="filename" value="" size="50"    /&gt;&lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td&gt;File Type&lt;/td&gt;
                  &lt;td&gt;&lt;input type="text" name="type" value="" size="50" /&gt;  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td&gt;Place of Origin(company) :&lt;/td&gt;
                  &lt;td&gt;&lt;input type="text" name="company" value="" size="50"   /&gt;&lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td&gt;HeadOffice :&lt;/td&gt;
                  &lt;td&gt;&lt;input type="text" name="HO" value="" size="50" /&gt; &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td&gt;File Location :&lt;/td&gt;
                  &lt;td&gt;&lt;input type="text" name="department" value="" size="50" /&gt;  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td&gt;Subject :&lt;/td&gt;
                  &lt;td&gt;&lt;input type="text" name="subject" value="" size="50"   /&gt;&lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td&gt;File Number :&lt;/td&gt;
                  &lt;td&gt;&lt;input type="text" name="fileno" value="" size="50"   /&gt;&lt;/td&gt;
              &lt;/tr&gt;
  
          &lt;/tbody&gt;
      &lt;/table&gt;
      &lt;input type="reset" value="Clear" name="Clear" /&gt;
      &lt;input type="submit" value="Submit" name="Submit" /&gt;
  
  &lt;/form&gt;
  &lt;/section&gt;
  &lt;footer&gt;
  Copyright 2008 NSIC. All right reserved.   
  &lt;/footer&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>fileServlet.java</p>
  
  <pre><code>package bean;
  
  import java.io.IOException;
  import java.io.PrintWriter;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import javax.servlet.RequestDispatcher;
  import javax.servlet.ServletException;
  import javax.servlet.http.Cookie;
  import javax.servlet.http.HttpServlet;
  import javax.servlet.http.HttpServletRequest;
  import javax.servlet.http.HttpServletResponse;
  import javax.servlet.http.HttpSession;
  
  public class FileServlet extends HttpServlet {
  
  @Override
  protected void doPost(HttpServletRequest request, HttpServletResponse response)
          throws ServletException, IOException {
     Cookie[] cookies = request.getCookies();
      if(cookies != null){
      for(Cookie cookie : cookies){
          if(cookie.getName().equals("JSESSIONID")){
              System.out.println("JSESSIONID="+cookie.getValue());
              break;
          }
      }
      }       
      HttpSession session = request.getSession(false);
      System.out.println("User="+session.getAttribute("user"));
     if(session!=null &amp;&amp; session.getAttribute("user") != null){
                  String user=(String)session.getAttribute("user"); 
  
            boolean status=false;
      try{            
          String fname=request.getParameter("name");            
          String type=request.getParameter("type");
          String company=request.getParameter("department");
          String headoffice=request.getParameter("HO"); 
          String location=request.getParameter("department");
          String subject=request.getParameter("subject");            
          String fno=company+"/"+headoffice+"/"+location+"/"+type+"/"+fname;
  
  
          Connection con=ConnectionProvider.getCon();
          String sql="insert into files(fileno,fname,ftype,subject,company,headoffice,fdepartment) values (?,?,?,?,?,?)";
          PreparedStatement pstmt =con.prepareStatement(sql);
  
          pstmt.setString(1,fno); 
          pstmt.setString(2,fname);
          pstmt.setString(3,type);
          pstmt.setString(4,subject); 
          pstmt.setString(5,company);
          pstmt.setString(6,headoffice);
          pstmt.setString(7,location);
  
          int rs=pstmt.executeUpdate();
          if(rs&gt;0){status=true;}
      }catch(Exception e){System.out.println(e);}
      if(status){
                 PrintWriter out= response.getWriter();
                 out.println("Values have been inserted"+user);
                 response.sendRedirect("create1.jsp");
  
      }
                 else 
                {
                    PrintWriter out= response.getWriter();
                    out.println("failed");
                    response.sendRedirect("create1.jsp");
  
                }                                
      }else{
      RequestDispatcher rd = getServletContext().getRequestDispatcher("/index.html");
      PrintWriter out= response.getWriter();
      out.println("&lt;font color=red&gt;Either user name or password is wrong.&lt;/font&gt;");
      rd.include(request, response);
      }
  }
  }
  </code></pre>
  
  <p>There is create.jsp which gives link to choose for filedetails.jsp or status.jsp</p>
  
  <p>status.jsp</p>
  
  <pre><code>&lt;%@page contentType="text/html" pageEncoding="UTF-8"%&gt;
  &lt;%@ page import ="java.sql.*" %&gt;
  &lt;!DOCTYPE html&gt;
  &lt;html&gt;
  &lt;head&gt;
      &lt;meta http-equiv="Content-Type" content="text/html; charset=UTF-8"&gt;
      &lt;title&gt;File Status Page&lt;/title&gt;
       &lt;style&gt;
  header {
  background-color:teal;
  color:white;
  text-align:center;
  padding:30px;
  }
  
  section {
  width:350px;
  float:left;
  padding:150px;
  }
  footer {
  background-color:black;
  color:white;
  clear:both;
  text-align:center;
  padding:5px;
  }
  &lt;/style&gt;
  &lt;/head&gt;
  &lt;body style="background-color:lightsteelblue;"&gt;
       &lt;%
  String userName = null;
  String sessionID = null;
  Cookie[] cookies = request.getCookies();
  if(cookies !=null){
  for(Cookie cookie : cookies){
  if(cookie.getName().equals("user")) userName = cookie.getValue();
  }
  }
  %&gt;
  &lt;header&gt;
  &lt;h3&gt;Hi &lt;%=userName %&gt;&lt;/h3&gt;
  &lt;/header&gt;
  &lt;a href="create.jsp"&gt;&lt;font color="black"&gt;back&lt;/font&gt;&lt;/a&gt;
  &lt;form action=" LogoutServlet" method="post"&gt;
  &lt;input type="submit" value="Logout" &gt;
  &lt;/form&gt;
  &lt;section&gt;
  &lt;h3&gt;Change Status&lt;/h3&gt;
  &lt;form action="statusServlet" method="post"&gt;
          &lt;select name="files"&gt;
                  &lt;%
      try{
  String sql="select * from files";
  Class.forName("com.mysql.jdbc.Driver");
  Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/login",
          "root", "root");
  Statement st = con.createStatement();
  ResultSet rs=st.executeQuery(sql);
  while(rs.next()){
  %&gt;                          
    &lt;option value="&lt;%=rs.getInt("fileno")%&gt;"&gt;&lt;%=rs.getString("fname")%&gt;&lt;/option&gt;
  &lt;%}
  rs.close();
  st.close();
  con.close();
  }catch(Exception e){
  e.printStackTrace();
  }
  %&gt; 
          &lt;/select&gt;
          &lt;select name="departments"&gt;
              &lt;%
      try{
  String sql="select * from department";
  Class.forName("com.mysql.jdbc.Driver");
  Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/login",
          "root", "root");
  Statement st = con.createStatement();
  ResultSet rs=st.executeQuery(sql);
  while(rs.next()){
  %&gt;                          
    &lt;option value="&lt;%=rs.getInt("departmentid")%&gt;"&gt;&lt;%=rs.getString("departmentname")%&gt;&lt;/option&gt;
  &lt;%}
  rs.close();
  st.close();
  con.close();
  }catch(Exception e){
  e.printStackTrace();
  }
  %&gt;
          &lt;/select&gt;
          &lt;select name="input"&gt;
              &lt;option&gt;IN&lt;/option&gt;
              &lt;option&gt;OUT&lt;/option&gt;
          &lt;/select&gt;
          &lt;input type="submit" value="submit" name="submit" /&gt;
      &lt;/form&gt;
  &lt;/section&gt;
  &lt;footer&gt;
  Copyright 2008 NSIC. All right reserved.                             
  &lt;/footer&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>I have not yet created statusServlet.java</p>