CODEWITHSUNDEEP
javamysqltry-catch
Suri
Suri

Reputation: 209

write special exception message for try catch during Inserting in a MySQL table

I want to insert information in a MySQL table based on the following code:

public void writeMapDocNameToDocId(File file, ArrayList<String> docNames) {
            PreparedStatement preparedStmt= null;
            try{
                conn.setAutoCommit(false);
                String query = " insert into DocumentNameId (docName)"
                        + " values (?)";
                preparedStmt = conn.prepareStatement(query);
                int i=0;


                for (String docName : docNames) 
                { 
                    preparedStmt.setString (1, docName);
                    preparedStmt.addBatch();
                    i++;
                    if (i % 1000 == 0 || i == docNames.size()) {
                        preparedStmt.executeBatch(); 
                    }
                }
                conn.commit();
                preparedStmt.close();
                conn.setAutoCommit(true);
            }
            catch(Exception e){
                e.printStackTrace();
            }

        }

I got the following error for some of the DocName.

java.sql.BatchUpdateException: Data truncation: Data too long for column 'docName' at row 1
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1269)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:955)
    at dataLayer.RepositorySQL.writeMapDocNameToDocId(RepositorySQL.java:508)

I want to know which docNames cause this error but I don't know how should I write message in catch that show me the docName.Since, docName is undefined in catch body.

I am not an expert in java and MySQLso my code could be based on wrong foundations. Please help me to understand my mistakes

Upvotes: 1

Views: 127

Answers (1)

CubeJockey
CubeJockey

Reputation: 2219

Here is an illustration in code, based on my comment above: 

int i=0; //Declare i outside of your try-catch statement

try{
    ...
    for (String docName : docNames){ 
        preparedStmt.setString (1, docName);
        preparedStmt.addBatch();
        i++;
        if (i % 1000 == 0 || i == docNames.size()) {
            preparedStmt.executeBatch(); 
        }
    }
    ...
}
catch(Exception e){

    String docNameError = docNames.get(i - 1); 
    //subtract 1 from i, because i is only incremented after a successful add 
    //to your prepared statement.

    //use docNameError however you'd like to report the exception

    e.printStackTrace();
}

Alternatively, you could increment i at the start of each iteration of your for-each loop, to avoid the subtraction in your catch:

for (String docName : docNames){
    i++;
    preparedStmt.setString(1,docName);

Upvotes: 1

Related Questions