CODEWITHSUNDEEP
javasqlswingfor-loopjdbc
Blondie
Blondie

Reputation: 43

Query in a loop which is executed only once Java

I am currently working on a Java project (on NetBeans) and I am struggling with a problem.

In fact, I have a jTable which contains several elements, which element has a jCheckBox in the second column and I would like to make a query to add the selected element (selected by the jCheckBox of course) in a table.

I can get the data that I want to add, but my query works only once. I have already check my loop but I don't where the problem comes from.

I let you see the code :

try {
    // Getting id of the selected value in the jComboBox
    String idParcours = oParcoursDAO.findIdParcours(jComboBoxParcours.getSelectedItem().toString());
    int id = Integer.parseInt(idParcours);

    // for each value in the jTable
    for(int i=0; i <jTable2.getRowCount(); i++){

        boolean isChecked = (Boolean)jTable2.getValueAt(i, 1);
        String nomPoi = (String)jTable2.getValueAt(i, 0);     
            // if the value is selected
            if(isChecked){
                String IDPoi = oParcoursDAO.findIdPoi(nomPoi);
                int idpoi = Integer.parseInt(IDPoi);

                System.out.println("idpoi "+idpoi); // It works I saw as idpoi as I have choose
                System.out.println("id "+id) // It works too

                oParcoursDAO.addPoi(idpoi,id);   // it works only once             
             }
        }               
     }catch (SQLException ex) {
    Logger.getLogger(ModificationParcoursJInternalFrame.class.getName()).log(Level.SEVERE, null, ex);
}

Thank you in advance for your help.

This is my statement

public void addPoi(int idPoi,int idParcours) throws SQLException{

    String query = "INSERT INTO TB_POI_PARCOURS (id_poi,id_parcours) VALUES (?,?) ";
    PreparedStatement preparedStatement = conn.prepareStatement(query);
    preparedStatement.setInt(1,idPoi);
    preparedStatement.setInt(2,idParcours);  
    preparedStatement.executeUpdate();
    preparedStatement.close();
}

Upvotes: 1

Views: 572

Answers (1)

David Rabinowitz
David Rabinowitz

Reputation: 30448

Why are you running one query per line? You can execute all of them in a single SQL using batch queries. It will require you to change the code but it will make it more efficient:

public void addPoi(Map<integer,Integer> poiMap) throws SQLException{

    String query = "INSERT INTO TB_POI_PARCOURS (id_poi,id_parcours) VALUES (?,?) ";
    PreparedStatement preparedStatement = conn.prepareStatement(query);
    for(Integer idPoi:poiMap.keySet()) {
        preparedStatement.setInt(1,idPoi);
        preparedStatement.setInt(2,poiMap.get(idPoi));  
        preparedStatement.addBatch();
    }
    preparedStatement.executeBatch();
    preparedStatement.close();
}

Of course the original method has to be changed accordingly.

Upvotes: 2

Related Questions