How to insert into MySQL tables with auto_increment foreign key via servlet connected to JSP

Question

I'm trying to make a servlet do the same thing as this SQL statement:

INSERT INTO event(title, description, start, end, guest_no) VALUES('someTitle', 'someDescription', '2018-02-02 20:00:00', '2018-02-02 21:00:00', 6);

INSERT INTO shift(event_id, start, end, positions) VALUES(LAST_INSERT_ID(), '2018-02-02 20:00:00', '2018-02-02 21:00:00', 2);

The forms I have at the moment take input from html and the information is inserted into the SQL table using a separate servlet for each table. But I can't seem to get it to add to both tables at the same time as I would with the above statement? I've already tried batches and thought about doing a transaction but I just need to know how to make LAST_INSERT_ID() work in java post method.

<section>
      <form name="create" action="${pageContext.request.contextPath}/createEventShift" method="post">
     
        <hr>
        <label for="title"><b>Event Name</b></label>
        <input type="text" placeholder="Enter title of the event" name="title" required>
        <hr>
        <label for="description"><b>Description</b></label>
        <input type="text" placeholder="Describe your event" name="description" required>

        <label for="guest_no"><b>Number of Guests</b></label>
        <input type="number" placeholder="Write how many guests" name="guest_no" required>

        <label for="start"><b>Start Date & Time (yyyy-MM-dd HH:mm:ss)</b></label>
        <input type="datetime-local" placeholder="Start" name="start" step="2">

        <label for="end"><b>End Date & Time (yyyy-MM-dd HH:mm:ss)</b></label>
        <input type="datetime-local" placeholder="End" name="end" step="2">
      
    <div class="expansive-button v2">
      <div><i></i></div>
    </div>

      <h3>Add Shift</h3>
      
      <label for="event_id"><b>Event ID</b></label>
      <input type="" name="event_id" value="LAST_INSERT_ID()">
            
          <label for="startshift"><b>Shift Start (yyyy-MM-dd HH:mm:ss)</b></label>
   <input type="datetime-local" placeholder="Start date and time of shift" name="startshift" step="2">
          
   <label for="endshift"><b>Shift End (yyyy-MM-dd HH:mm:ss)</b></label>
   <input type="datetime-local" placeholder="End date and time of shift" name="endshift" step="2">
          
   <label for="positions"><b>Number of Staff Needed</b></label>
   <input type="number" placeholder="How many staff do you need for this shift" name="positions">
    </div>
    
    <br/><br />
    
    <button>Submit</button>
     </form>

And the Servlet looks like this:

@WebServlet("/createEventShift")

public class createEventShift extends HttpServlet {
private static final String URL = "jdbc:mysql://localhost:3306/e_manager";
private static final String USER = "root";
private static final String PASSWORD = "2timeLearning!";

private static Connection conn = null;

static {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(URL, USER, PASSWORD);
    } catch (ClassNotFoundException | SQLException e) {
    }

}

public static Connection getConnection() {
    return conn;
}

@Override
protected void doPost(HttpServletRequest request, 
        HttpServletResponse response) throws ServletException, IOException {

    String insertSQL = "INSERT INTO event(title, description, start, end, "
            + "guest_no) VALUES(?, ?, ?, ?, ?)";
    String insertSQL2 = "INSERT INTO shift(event_id, start, end, positions)"
            + " VALUES(LAST_INSERT_ID(), ?, ?, ?)";

    String title = request.getParameter("title");
    String description = request.getParameter("description");
    String start = request.getParameter("start");
    String end = request.getParameter("end");
    String guest_no = request.getParameter("guest_no");       
    String ss = request.getParameter("startshift");
    String es = request.getParameter("endshift");
    String pos = request.getParameter("positions");

    try {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(createEventShift.class.getName()).log
    (Level.SEVERE, null, ex);
        }

        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        PreparedStatement ce = conn.prepareStatement(insertSQL); 
        PreparedStatement cs = conn.prepareStatement(insertSQL2);

        ce.setString(1, title);
        ce.setString(2, description);
        ce.setString(3, start); 
        ce.setString(4, end);
        ce.setInt(5, Integer.parseInt(guest_no));
        ce.executeUpdate(insertSQL);
        ce.close();

        cs.setString(2, ss);
        cs.setString(3, es);
        cs.setInt(4, Integer.parseInt(pos));
        cs.executeUpdate(insertSQL2);
        cs.close();

         response.sendRedirect("/viewEvents.jsp");     

        conn.close();

    } catch (SQLException ex) {

    }
}}

Can anyone tell me how to make this work please? (yes I am new to all this)

See Asad's answer: https://stackoverflow.com/questions/5513180/java-preparedstatement-retrieving-last-inserted-id — Jan B., May 20 '18 at 16:56

Jonathan Laliberte · Accepted Answer · 2018-05-20T17:43:08.360

Normally we seperate database logic from the servlet. Because your code will be harder to maintain if you have many servlets (you have to keep writing your database connections over and over again for each servlet). It's also not advised from a security standpoint. Among other reasons...

Here's something you can do to make things easier. Create another class that you will only use for your database connections, and whenever you want to do something to your database you can call on this class (which i'll show you how to do below):

public class DBConnection {
    private static String url = null;
    private static Connection conn = null;
     public static Connection getConnection(){
     try{

       Class.forName("com.mysql.jdbc.Driver");
       url = "jdbc:mysql://localhost:3306/e_manager";

     conn = DriverManager.getConnection(url,"root","2timeLearning!");
     }   catch (Exception e) {
            System.out.println(e);
        } 
     return conn;
     }
}

There's another useful thing to know which is called Model-View-Controller (MVC)... This is a way to divide your software into three interconnected parts, again, one of the reasons is to make it easier to manage. In your kind of setup you can view your views as your jsps/html pages, controllers are your Servlets and your models are what you use to interface between the two.

Let's create a model to handle both your events and your shifts (but really something like this should be separate)

So let's create another class, here i'll call it EventsAndShifts. In this class we will handle various database operations. Usually we would create another class for EventsAndShifts database connections (something called Data Access Object Pattern, DAO for short) but in this example we will just do it in the same class.

public class EventsAndShifts{    

  //i noticed you wanted to insert the event id for a shift, you can't do it the way you wanted to, you have to do it like this (this method will return an int)
public int setEventInfo(String title, String description, String start, String end, String guestNo){
     int eventID = 0; //initialize
    //get connection from our DBConneciton class we created earlier
    try(Connection conn= DBConnection.getConnection()){
//returning the generated keys lets us get the row id of what we insert
   PreparedStatement pst = conn.prepareStatement("INSERT INTO event(title, description, start, end, guest_no) VALUES(?, ?, ?, ?, ?);", Statement.RETURN_GENERATED_KEYS); 

         pst.setString(1, title);
         pst.setString(2, description);
         pst.setString(3, start); 
         pst.setString(4, end);
         pst.setInt(5, Integer.parseInt(guestNo));

         pst.executeUpdate();   
       //now get the eventID
    ResultSet rs = pst.getGeneratedKeys();
    while (rs.next()) {
    eventID = rs.getInt(1); //get the id of the inserted event
      }

        } catch (SQLException e) {
            e.printStackTrace();
        }
     //we don't need to close our db connection because the try statement does it for us   
return eventID; //return the eventID
}   




public void setShiftInfo(int eventID, String ss, String es, String pos){
    //get connection from our DBConneciton class we created earlier
    try(Connection conn= DBConnection.getConnection()){
   PreparedStatement pst = conn.prepareStatement("INSERT INTO shift(event_id, start, end, positions)VALUES(?, ?, ?, ?);"); 

         pst.setInt(1, eventID);    
         pst.setString(2, ss);
         pst.setString(3, es);
         pst.setInt(4, Integer.parseInt(pos));

         pst.executeUpdate();   

        } catch (SQLException e) {
            e.printStackTrace();
        }
}  



}

Now we have all this setup, let's make some magic happen:

@Override
protected void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {

    //get our parameters
    String title = request.getParameter("title");
    String description = request.getParameter("description");
    String start = request.getParameter("start");
    String end = request.getParameter("end");
    String guest_no = request.getParameter("guest_no");       
    String ss = request.getParameter("startshift");
    String es = request.getParameter("endshift");
    String pos = request.getParameter("positions");

    EventsAndShifts eas = new EventsAndShifts(); //instantiate our EventsAndShifts class

    //insert into event table and return eventID
    int eventID = eas.setEventInfo(title,description,start,end,guest_no);

    //use eventID and insert into event table shift table
    eas.setShiftInfo(eventID,ss,es,pos);

    //all done
  response.sendRedirect("/viewEvents.jsp");     
}

Hope this helps you understand more how to put it all together, let me know if something doesn't work or if you have questions.

good to know, happy to help. Feel free to upvote/tick as correct answer if it worked for you :) — Jonathan Laliberte, May 20 '18 at 20:46

How to insert into MySQL tables with auto_increment foreign key via servlet connected to JSP

1 Answers1