java—如何通过连接到jsp的servlet插入带有自动增量外键的mysql表

7nbnzgx9 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(348)

我试图让servlet执行与此sql语句相同的操作：
在事件（标题、描述、开始、结束、访客编号）值中插入（'sometitle'，'somedescription'，'2018-02-02 20:00:00'，'2018-02-02 21:00:00'，6）；
插入到班次（事件\u id，开始，结束，位置）值（最后一个\u insert \u id（），'2018-02-02 20:00:00'，'2018-02-02 21:00:00'，2）；
我目前使用的表单从html获取输入，并使用一个单独的servlet将信息插入到sql表中。但我似乎不能像上面的语句那样，同时将它添加到两个表中？我已经尝试过批处理并考虑过做一个事务，但我只需要知道如何在JavaPOST方法中使用last\u insert\u id（）。

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

servlet如下所示：

@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) {

    }
}}

有人能告诉我怎么做吗(是的，我对这一切都是陌生的）

Java mysql servlets forms prepared-statement http-post

来源：https://stackoverflow.com/questions/50437135/how-to-insert-into-mysql-tables-with-auto-increment-foreign-key-via-servlet-conn

1条答案

按热度按时间

cvxl0en21#

通常我们将数据库逻辑与servlet分开。因为如果您有许多servlet，那么您的代码将更难维护（您必须为每个servlet反复编写数据库连接）。从安全的Angular 来看也不建议这样做。除了其他原因。。。
你可以做一些事情让事情变得更简单。创建另一个仅用于数据库连接的类，并且每当您想对数据库执行某些操作时，都可以调用该类（下面我将向您演示如何执行该操作）：

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

还有一个有用的东西要知道，这是所谓的模型-视图-控制器（mvc）。。。这是一种将软件划分为三个相互关联的部分的方法，同样，原因之一是使其更易于管理。在这种设置中，您可以将视图视为jsp/html页面，控制器是servlet，模型是用于在两者之间进行接口的对象。
让我们创建一个模型来处理您的事件和轮班（但实际上这样的事情应该是分开的）
让我们创建另一个类，在这里我称之为 EventsAndShifts . 在这个类中，我们将处理各种数据库操作。通常我们会为eventsandshifts数据库连接创建另一个类（称为数据访问对象模式，简称dao），但在本例中，我们将在同一个类中进行。

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

}

现在我们有了所有的设置，让我们创造一些奇迹：

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

希望这有助于你了解更多如何把它放在一起，让我知道如果有什么不工作或如果你有问题。

赞(0）回复(0）举报 2021-06-20

我来回答

java—如何通过连接到jsp的servlet插入带有自动增量外键的mysql表

1条答案

相关问题

热门标签

最新问答