Writing Excel data to database in Java

Question

This is my method that I use to write excel file data to a database.

public static void executeSQLUpdate(String sql, List<Object> arguments) {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = getConnection(); //a method that returns a java.sql.Connection to your database
        System.out.println("\n01)conection :"+con);
        pstmt =  con.prepareStatement(sql);
        System.out.println("\n02)pstn :"+pstmt);
        System.out.println( "\n03)arguments size :"+arguments.size());
        if (arguments != null) {
            int i = 1;
            System.out.println( "\n04)if :"+arguments);
            for(Object o : arguments) {
                 System.out.println( "\n05)executeSQLUpdate");
                 System.out.println( "\n06)object."+o);                 
                 System.out.println("\n07)................... :"+i + o);
                 pstmt.setObject(i, o);
                 System.out.println("\n08)____________________"+i+o);

            }
        }
        System.out.print("\n09)errorchk........... :");
        //method to execute insert, update, delete statements...
        pstmt.executeUpdate();
        System.out.print("\n10)+++++++++++++++++ :");
    } catch(SQLException e) {
        System.out.println("\n11)************* :"+e);
        //handle the error...
    } finally {
        //closing the resources (always in finally block, not in the try!)
        try {
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
        }
    }
}

Up to no 07 all the system out are working. But after that any system out are not working. What is the reason for that? Is there any error in this one?

This is my out put:

run:
AAA     BBB     CCC     
DDD     EEE     FFF     
GGG     HHH     III     
JJJ     KKK     LLL     
MMM     NNN     OOO     
PPP     QQQ     RRR 

01)conection :com.mysql.jdbc.JDBC4Connection@6e70c7

02)pstn :com.mysql.jdbc.JDBC4PreparedStatement@29428e: INSERT INTO files_1 VALUES(** NOT SPECIFIED , NOT SPECIFIED , NOT SPECIFIED **)

03)arguments size :6

04)if :[[AAA, BBB, CCC], [DDD, EEE, FFF], [GGG, HHH, III], [JJJ, KKK, LLL], [MMM, NNN, OOO], [PPP, QQQ, RRR]]

05)executeSQLUpdate :

06)object :[AAA, BBB, CCC]

07)................... :1[AAA, BBB, CCC]

08)__________ :1[AAA, BBB, CCC]

05)executeSQLUpdate :

06)object :[DDD, EEE, FFF]

07)................... :1[DDD, EEE, FFF]

08)__________ :1[DDD, EEE, FFF]

05)executeSQLUpdate :

06)object :[GGG, HHH, III]

07)................... :1[GGG, HHH, III]

08)__________ :1[GGG, HHH, III]

05)executeSQLUpdate :

06)object :[JJJ, KKK, LLL]

07)................... :1[JJJ, KKK, LLL]

08)__________ :1[JJJ, KKK, LLL]

05)executeSQLUpdate :

06)object :[MMM, NNN, OOO]

07)................... :1[MMM, NNN, OOO]

08)__________ :1[MMM, NNN, OOO]

05)executeSQLUpdate :

06)object :[PPP, QQQ, RRR]

07)................... :1[PPP, QQQ, RRR]

08)__________ :1[PPP, QQQ, RRR]

09)errorchk........... : 11)***** :No value specified for parameter 2

java.sql.SQLException: No value specified for parameter 2
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
    at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2560)
    at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2536)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2383)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2327)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2312)
    at com.project.bulk.ReadExcelFile.executeSQLUpdate(ReadExcelFile.java:112)
    at com.project.bulk.ReadExcelFile.MethodToData(ReadExcelFile.java:138)
    at com.project.bulk.ReadExcelFile.main(ReadExcelFile.java:39)

BUILD SUCCESSFUL (total time: 3 seconds)

Answer 1

One error for sure is that you increment i two times!!!

System.out.println("\n07)..................."+i++ + o); // one
pstmt.setObject(i++, o); // two

This means that you don't set the even indices, just the odd ones: 1, 3, 5...

This should correct this error:

System.out.println("\n07)..................."+i + o); 
pstmt.setObject(i++, o); // only once, and after the evaluation!

EDIT *Second, but also big mistake*

} catch(SQLException e) {
    System.out.println("\n11)************* :"+e); //WTF?
    //handle the error...    
}

_{excuse me for shouting, this has to happen now!}

Please, for our and your (future) colleagues' mental health's sake, DO NOT EVER DO THIS AGAIN!

Printing exceptions must happen in one of two ways:

• logger.error("message", e);
• e.printStackTrace();

As these reserve the stack trace, and thus enable proper debugging of the code

but should never, ever, ever, never! happen in any of these ways:

• System.out.print(e)
• System.out.print(e.getMessage)
• System.out.print("message " + e.getMessage)
• logger.error(e.getMessage)

So correctly this should be:

} catch(SQLException e) {
    System.out.println("\n11)************* :"+e.getMessage()); 
    e.printStackTrace();
    //TODO: handle the error...    
}

By the way: using proper logging like log4j is well worth the time! It consumes much more time to clean up all the System.out.*, than to set a proper loglevel...

EDIT2

As for the SQL error:

String sql = "INSERT INTO files_1 VALUES(?)"; 

This SQL line tells the DBMS that it will have one parameter to deal with. The table has 3 columns, so you need to specify 3 values. Either constants, or parameters (by using ?). So you should have:

String sql = "INSERT INTO files_1 VALUES(?,?,?)"; 

Answer 2

As indicated by your error

java.sql.SQLException: Invalid argument value: java.io.NotSerializableException

you're trying to set a value with a parameter that's not serializable in this line:

pstmt.setObject(i++, o);

Please make sure that all of your values are either primitives or values that can be mapped to database columns like String or Date.

You can find out which data you're trying to set by putting in a line like this (using a logging framework or System.out.println:

System.out.println("setObject: " + o + ", Class: " + o.getClass());