Insert Data to MySql DB from ArrayList

Question

Can anyone help me do this a bit better, I have an ArrayList with data been added everytime a question is answered in the session by calling the method saveQuestionToPlayersQuestion(), after all questions are answered the savePlayersQuestionsToDB() meth is called. I Have a Table in the schema for that data.

I have it working and saving to database, but don't think its the correct way of doing it.

Can I just Insert the arraylist at once, meaning instead of calling the

ConnectionClass.createPlaySessionInDB(pQ.getPlayer_id(),
                        pQ.getQuestion_tbl_id(), pQ.getAns(),
                        pQ.getPlayer_score(), pQ.getPlay_session_id());

for every object that's in the List, Its OK when only 3 question are answered, But what happens if they have to answer 20 or 30+ question. Is there a better way.

My declared ArrayList

private ArrayList<Play_Questions> playQuestionList;


playQuestionList = new ArrayList<Play_Questions>();

Here is the method I call to save each question answered to playQuestionList and the next method savePlayersQuestionsToDB() is the one called to save all the object to the DB using a enhanced for loop.

/**
 * add the question to playQuestionList
 */
public void saveQuestionToPlayersQuestion() {
    Play_Questions temp = new Play_Questions(playerId, question_tbl_id,
            choosenAnswer, scorePerQuestion, nextPlaySessionId);
    playQuestionList.add(temp);
    playQuestionList.toString();
}

/**
 * save the playQuestion to DataBase
 */
public void savePlayersQuestionsToDB() {

    for (Play_Questions pQ : playQuestionList) {
        if (pQ == null) {
            System.out.println("Play Question List is empty");
        } else
            try {
                ConnectionClass.createPlaySessionInDB(pQ.getPlayer_id(),
                        pQ.getQuestion_tbl_id(), pQ.getAns(),
                        pQ.getPlayer_score(), pQ.getPlay_session_id());
                System.out.println("Worked check DB --->>");
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                System.out
                        .println("Error with ElbtView savePlayersQuestionsToDB()");
            }
    }

Here is the method in the Connection Class

    public static void createPlaySessionInDB(int player_id,
        int question_tbl_id, String ans, int player_score,
        int play_session_id) throws SQLException {
    String sql = "INSERT INTO player_questions (id, player_id, question_tbl_id, ans, player_score, play_session_id ) VALUES (null,?,?,?,?,?)";
    try {
        preparedStatement = preparedStatement(sql);
        preparedStatement.setInt(1, player_id);
        preparedStatement.setInt(2, question_tbl_id);
        preparedStatement.setString(3, ans);
        preparedStatement.setInt(4, player_score);
        preparedStatement.setInt(5, play_session_id);

        // execute the SQL statement
        preparedStatement.executeUpdate();

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        System.out
                .println("Problem with ConnectionClass createPlaySessionInDB method: "
                        + e.getMessage());
    } finally {
        // close the connection
        getConnection().close();
    }
}

Here is the Play_Questions class

public class Play_Questions {
private int player_id; 
private int question_tbl_id; 
private String ans;
private int player_score; 
private int play_session_id;

/**
 * Default Constructor
 */
public Play_Questions(){
    this(0,0,null,0,0);
}

/**
 * @param player_id: 
 *      the players id
 * @param question_tbl_id: 
 *      the question id from question table
 * @param ans: 
 *      the answer selected by player
 * @param player_score: 
 *      the score they achieved for answering
 * @param play_session_id: 
 *      the play session id
 */
public Play_Questions(int player_id, int question_tbl_id, String ans,
        int player_score, int play_session_id) {
    this.player_id = player_id;
    this.question_tbl_id = question_tbl_id;
    this.ans = ans;
    this.player_score = player_score;
    this.play_session_id = play_session_id;
}

/**
 * @return the player_id
 */
public int getPlayer_id() {
    return player_id;
}

/**
 * @param player_id the player_id to set
 */
public void setPlayer_id(int player_id) {
    this.player_id = player_id;
}

/**
 * @return the question_tbl_id
 */
public int getQuestion_tbl_id() {
    return question_tbl_id;
}

/**
 * @param question_tbl_id the question_tbl_id to set
 */
public void setQuestion_tbl_id(int question_tbl_id) {
    this.question_tbl_id = question_tbl_id;
}

/**
 * @return the ans
 */
public String getAns() {
    return ans;
}

/**
 * @param ans the ans to set
 */
public void setAns(String ans) {
    this.ans = ans;
}

/**
 * @return the player_score
 */
public int getPlayer_score() {
    return player_score;
}

/**
 * @param player_score the player_score to set
 */
public void setPlayer_score(int player_score) {
    this.player_score = player_score;
}

/**
 * @return the play_session_id
 */
public int getPlay_session_id() {
    return play_session_id;
}

/**
 * @param play_session_id the play_session_id to set
 */
public void setPlay_session_id(int play_session_id) {
    this.play_session_id = play_session_id;
}

Your help in making me code this a bit better will be greatly appreciate.

Gman

Answer 1

This is my approach , its best you use a very transparent approach that models the problem close to a real life scenario .

 public class Question
{
     private int question_no ;
     // The right answer for this question may be a , b or c 
     private String question_answer ;
     private int question_point ;

    public Question()
    {

    }

    /**
     * @return the question_id
     */
    public int getQuestion_id() {
        return question_no;
    }

    /**
     * @param question_id the question_id to set
     */
    public void setQuestion_id(int question_id) {
        this.question_no = question_id;
    }

    /**
     * @return the question_answer
     */
    public String getQuestion_answer() {
        return question_answer;
    }

    /**
     * @param question_answer the question_answer to set
     */
    public void setQuestion_answer(String question_answer) {
        this.question_answer = question_answer;
    }

    /**
     * @return the question_point
     */
    public int getQuestion_point() {
        return question_point;
    }

    /**
     * @param question_point the question_point to set
     */
    public void setQuestion_point(int question_point) {
        this.question_point = question_point;
    }
}

Now the answer class

 /**
 * 
 *    Track an answer 
 */
public class Answer
{

    private String answer ;
    // correct or failed 
    private String status ;
    public Answer()
    {

    }

    /**
     * @return the answer
     */
    public String getAnswer() {
        return answer;
    }

    /**
     * @param answer the answer to set
     */
    public void setAnswer(String answer) {
        this.answer = answer;
    }

    /**
     * @return the status
     */
    public String getStatus() {
        return status;
    }

    /**
     * @param status the status to set
     */
    public void setStatus(String status) {
        this.status = status;
    }




}

Now the player class that encapsulates all the operations for each player after successful login a player object is added to session , it handles operations like marks , save to db ,etc

/**
 * 
 *  encapsulates a player 
 * Class should be placed in session for web applications 
 */
public class Player
{
    String username ;
    String password ;

    // holds all the questions arranged for this player without allowing duplicates in questions 
    Set questions = new HashSet<Question>();

    // map this players question and answer 
    Map question_answers = new HashMap<Question, Answer>();


    /**
     * 
     *   Allows you to dynamically set questions for players 
     * @param questions_ 
     */
    public Player(Set questions_ )
    {
        this.questions = questions_;
    }


    // if you want the same set of questions for all players 
    public Player()
    {

     }

    /**
     * Question answered for this particular user 
     * please note that the player object is in session if it is a web application 
     * @param q
     * @param a 
     */
    public void answerQuestion(Question q , Answer a)
    {
        question_answers.put(q, a);
    }

    /**
     * 
     *  The user might go back to a previous question to change an answer 
     * @param q
     * @param a 
     */
   public void updateAnswer(Question q, Answer a)
    {
       // remove the question and update it with 
        if(question_answers.containsKey(q))
        {
            question_answers.remove(q);
        }


        // add the new q & a 
        answerQuestion(q, a);
    }

   /**
     * 
     *   finally save the players data 
     *   here your db model counts it would have to cater for 
     *  each players question and answer , send the batch update using batch prepared statements 
     */
    public void savePlayerData()
    {
        // db code is commented because i didnt write db codes 
        // status int the column will stand for correct or fail 
       // String save_sql =insert into results(player_id , question_id , answer , status) values(?,?,?,?)
        // PreparedStatement pstat = dbConnection.prepareStatement(save_sql);
        //dbConnection.setAutoCommit(false);
        // if automark is enabled 
        autoMark();
        Iterator it = question_answers.values().iterator();
        while(it.hasNext())
        {
            // fetch each question 
            Question q = (Question)it.next();
            // Fetch each answer based on the question 
            Answer a = (Answer)question_answers.get(q);

            int question_id = q.getQuestion_id();
            String answer = a.getAnswer();
            String answer_status = a.getStatus();
            /**
             *    
             *    
             *   commented cause i would have to write db backing code , lol !
             *    
             *    pstat.setInt(1, getUsername());
             *   pstat.setInt(2, question_id);
             *   pstat.setString(3 , answer);
             *  pstat.setString(4 , answer_status)
             * pstat.addBatch();
             * pstat.executeBatch();
             * 
             */

        }
        //dbConnection.setAutoCommit(false);

    }


    /**
     * 
     *   This method can allow your program to auto mark if 
     *   the question and answer  if it is based on a , b , c 
     */
    public void autoMark()
    {
        Iterator it = question_answers.values().iterator();
        while(it.hasNext())
        {
            // fetch each question 
            Question q = (Question)it.next();
            // Fetch each answer based on the question 
            Answer a = (Answer)question_answers.get(q);

            if(q.getQuestion_answer().equalsIgnoreCase(a.getAnswer()))
            {
                a.setStatus("Correct");
            }
            else
            {
                a.setStatus("Failed");
            }
            updateAnswer(q, a);
        }
    }

}

So anytime a player answers a question u call

p.answerQuestion(Question q , Answer a)

The question object is the particular question answered and the answer object is created to ,match the question . U can also track the users current question by adding a value called current_question on the player class with getter and setter(Question Object) to track the current question in cases where the user might go back to a previous question you can then call

p.updateAnswer(Question q, Answer a)

Same thing u pass the particular question an d the new answer object

p.savePlayerData()

Saves data to the db

p.autoMark()

This method is called in the p.savePlayerData() method before the records are saved so the db only holds final assessment & marked records , This can come in handy for intelligent reporting such as who has the highest score . Thats it if you have extra questions you can contact me tyger2007@gmail.com.

Answer 2

Ok I did manage to achieve what I was trying to do, here is the revised code to help others that have the same problem. This is not the definite way to do this just the only way I know at this time to acheive what I need to. If anyone has a nice, faster way of doing this I would love to know. Got some inspiration from this thread here Java: Insert multiple rows into MySQL with PreparedStatement

Here is the method I call to save each question answered to playQuestionList and the next method savePlayersQuestionsToDB(), Instead of doing the work here I pass the Arraylist to the Connection Class and loop through it there.

/**
 * add the question to playQuestionList
 */
public void saveQuestionToPlayersQuestion() {
    Play_Questions temp = new Play_Questions(playerId, question_tbl_id,
            choosenAnswer, scorePerQuestion, nextPlaySessionId);
    playQuestionList.add(temp);
}

/**
 * save the playQuestion to DataBase
 */
public void savePlayersQuestionsToDB() {
    try {
        ConnectionClass.savePlaySessionInDB(playQuestionList);
        System.out.println("Worked check DB --->>");
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        System.out
                .println("Error with ElbtView savePlayersQuestionsToDB()");
    }
}

and here is the method in the ConnectionClass

/**
 * Creates a batch using addBatch()and then executes it by calling
 * ececuteBatch()
 * 
 * @param playQuestions
 *            the ArrayList<T> to be added to batch and then saved to DB
 * @throws SQLException
 *             when executing the the batch
 */
public static void savePlaySessionInDB(
        ArrayList<Play_Questions> playQuestions) throws SQLException {
    try {
        String sql = "INSERT INTO player_questions (id, player_id, question_tbl_id, ans, player_score, play_session_id ) VALUES (null,?,?,?,?,?)";
        preparedStatement = preparedStatement(sql);
        for (int i = 0; i < playQuestions.size(); i++) {
            Play_Questions playQuestion = playQuestions.get(i);
            preparedStatement.setInt(1, playQuestion.getPlayer_id());
            preparedStatement.setInt(2, playQuestion.getQuestion_tbl_id());
            preparedStatement.setString(3, playQuestion.getAns());
            preparedStatement.setInt(4, playQuestion.getPlayer_score());
            preparedStatement.setInt(5, playQuestion.getPlay_session_id());
            preparedStatement.addBatch();

            if ((i + 1) % 100 == 0) {
                preparedStatement.executeBatch(); // excute every 100 items
            }
        }
        preparedStatement.executeBatch();
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        System.out
                .println("Problem with ConnectionClass savePlaySessionInDB(ArrayList<Play_Questions> playQuestions) method: "
                        + e.getMessage());
    } finally {
        // close the connection
        getConnection().close();
    }

}

Defiantly open to a much better suggestion.

Gman

Answer 3

I must say that as this solution actually works perfectly now , in the long hol it might still pose some problems it actually depends on the nature of your application , is it an application that will be deployed and used by a lot of users ? or its just a test application ?, Anyway i do advise that you model your objects properly as this is the most important thing in object oriented programming , My suggestions :

1. Isolate Question , Player , Answer Objects , Having Objects Like Play_Question will make your application difficult to extend dynamically .
2. Use an Iterator for your loops , its been optimized to perform better than for loops especially in collection.

3. For me i naturally use a singleton connection class if it is a desktop application & JNDI if its a web application , the connection class uses a connection pool so my application does not open a new connection for every db request , it makes your application super fast in db request [http://jolbox.com/ bonecp connection pooling for Desktop apps if web use JNDI ]

   Maps are very easy to use its a case of key/value pairs , All the same nice work and have fun. .

Answer 4

It's ok ,spent quite some time developing applications that generally I always develop to scale ,lol ,I used iterators in the Players class in the method savePlayerData() u can always use it with your collection classes .