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)

not working is neither a valid Java error nor a valid Excel error. — a_horse_with_no_name, Feb 19 '13 at 8:57
In adddition to the error I spotted, I'd recommend to take a look at what the output is, as I'm almost sure there is an exception of some kind that gets written somewhere. Please put that in your question too by editing it! — ppeterka, Feb 19 '13 at 9:03
I post the out put that I have now.What is the error that I have done.?? — user1705260, Feb 20 '13 at 4:20
As a general comment: Please read up about JDBC and SQL in general. It looks like you're lacking a general understanding of these technologies. Find an example that's working and then start from there. To me, it looks like your whole for loop is wrong. Based on the INPUT statement and the list of arguments, it looks like you're requiring one INSERT per argument (each argument has three values). The way you're doing it now will never work. — nwinkler, Feb 20 '13 at 7:13

ppeterka · Answer 1 · 2013-02-19 11:00:28Z

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(?,?,?)";

Did you fix exception logging? Once you've done that, you should report what gets output. — ppeterka, Feb 19 '13 at 9:11
I missed that somehow. However, seeing that, the answer @nwinkler posted is what you need! I don't know what instances are present in your list, you should "extract" the basic types from them. — ppeterka, Feb 19 '13 at 9:28
Yes.I fixed that.Thank you very much.But after that I got another error.Please check it.I edit my question. — user1705260, Feb 19 '13 at 10:07

nwinkler · Answer 2 · 2013-02-19 09:16:15Z

up vote 1 down vote

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());

answered Feb 19 '13 at 9:16

nwinkler
5,63021649

Yes.I fixed that.Thank you very much.But after that I got another error.Please check it.I edit my question. – user1705260 Feb 19 '13 at 10:10

It looks like your SQL doesn't match the table structure. It's impossible to answer that without knowing more about the table or the SQL. Please see here for a similar error: stackoverflow.com/questions/5304613/… – nwinkler Feb 19 '13 at 10:15

My sql is String sql = "INSERT INTO files_1 VALUES(?)"; and in my tables there are 3 columns.What should I change? – user1705260 Feb 19 '13 at 10:24

1

@user1705260 you should make it: String sql = "INSERT INTO files_1 VALUES(?,?,?)"; The amount of ?'s should correspond to the parameters you use. – ppeterka Feb 19 '13 at 10:58

I did it.But it gives this error now.com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?)' at line 1 – user1705260 Feb 19 '13 at 11:20

| show 3 more comments

asked	1 year ago
viewed	291 times
active	1 year ago

current community

your communities

more stack exchange communities

Writing Excel data to database in Java

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged java database excel or ask your own question.

Linked

Hot Network Questions

current community

your communities

more stack exchange communities

Writing Excel data to database in Java

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged java database excel or ask your own question.

Linked

Related

Hot Network Questions