put data in List thenn insert it its values into database

Question

I have to store questions in session afterward in List then insert them all into database by one click

my servlet

Question question = new Question(title, content, idExam);
        request.getSession().setAttribute("question", question);
        int quizKey = ExamDAO.add_question(question);
        ArrayList<Question> ques = new ArrayList<Question>();
        ques.add(question);

my dao

cnx = Connect.getConnection();
        String req = "insert into question(title, content, id_examen) values(?,?,?)";
        PreparedStatement st = cnx.prepareStatement(req, Statement.RETURN_GENERATED_KEYS);
        st.setString(1, question.getTitre());
        st.setString(2, question.getContenu());
        st.setInt(3, question.getIdExamen());
        st.executeBatch();

        ResultSet rs = st.getGeneratedKeys();

        if (rs.next()) {


quizKey = rs.getInt(1);
            }

how to do that ?

score 0 · Answer 1 · answered May 24 '19 at 08:18

0

Try st.executeUpdate(). This is usually used for manipulating statements like DELETE, INSERT or UPDATE. The method will return either the row count for SQL Data Manipulation Language (DML) statements or 0 for statements that return nothing.

answered May 24 '19 at 08:18

Jumper

41
4

score 0 · Answer 2 · answered May 27 '19 at 19:20

If you have a List<Question> and you wish to insert all in one go then you have to utilize what is called as - batching or batch update. Refer here for more details

You will be iterating your list of questions and setting parameters for each question then adding that statement to batch (by using st.addBatch()) & then finally call - st.executeBatch().

In your code sample, you are executing a batch but there is only one prepared statement in that batch. You need as many prepared statements as number of questions in the list.

    String req = "insert into question(title, content, id_examen) values(?,?,?)";
    PreparedStatement st = cnx.prepareStatement(req, Statement.RETURN_GENERATED_KEYS);
    for(Question question : ques){
     st.setString(1, question.getTitre());
        st.setString(2, question.getContenu());
        st.setInt(3, question.getIdExamen());
       st.addBatch();
    }
st.executeBatch();

With this approach, you might have issues in collecting generated keys as illustrated in this SO question so if you really need those ids , you will have to turn off auto commit, execute updates in loop without batching, collected generated ids in a map or list for each insert statement and then finally commit your connection in the end.

in fact I want to put List of Question in a session, then to insert them all in database by one click. the query is is already done what about the List of question in servlet. how to do it ?? — julia, May 28 '19 at 00:26
make your `Question` class serializable & then put `ques` in session. `ArrayList` is already serializable. — Sabir Khan, May 28 '19 at 06:00

put data in List thenn insert it its values into database

2 Answers2