JDBC : Batch insert not inserting value to database

Question

I have to execute multiple insert queries using JDBC for which I am trying to execute batch statement. Everything works fine in my code but when i try to see values in the table, the table is empty. Here is the code :

SessionImpl sessionImpl = (SessionImpl) getSessionFactory().openSession(); 
    Connection conn = (Connection) sessionImpl.connection();

    Statement statement = (Statement) conn.createStatement();

    for (String query : queries) {
        statement.addBatch(query);
    }
    statement.executeBatch();
    statement.close();
    conn.close();

And the

List<String> queries

contains insert queries like:

insert into demo values (null,'Sharmzad','10006','http://demo.com','3 Results','some values','$44.00','10006P2','No Ratings','No Reviews','Egypt','Duration: 8 hours','tour','Day Cruises');

And the table structure is like:

create table demo ( ID INTEGER PRIMARY KEY AUTO_INCREMENT,supplierName varchar(200),supplierId varchar(200),supplierUrl varchar(200),totalActivities varchar(200),activityName varchar(200),activityPrice varchar(200),tourCode varchar(200),starRating varchar(200),totalReviews varchar(200),geography varchar(200),duration varchar(200),category varchar(200),subCategory varchar(200));

No exception is thrown anywhere but no value is inserted. Can someone explain?

If i remove null (which is for auto-increment id), i get an exception of "Not enough column values" — roger_that, Oct 17 '16 at 09:46
I can't see `conn.commit()` did you configure _"autocommit"_ for your connection — Timothy Truckle, Oct 17 '16 at 09:52
Take a look at [this](http://stackoverflow.com/questions/4355046/java-insert-multiple-rows-into-mysql-with-preparedstatement), might help! — N00b Pr0grammer, Oct 17 '16 at 09:56
primary key had default not null constraint and I just wonder why your insert query don't throw error message like ERROR: null value in column "ID" violates not-null constraint. — Ye Win, Oct 17 '16 at 10:04
I guess i need to commit the connection. I haven't done that. — roger_that, Oct 17 '16 at 10:09

score 5 · Accepted Answer · edited Oct 17 '16 at 10:05

5

Most JDBC drivers use autocommit, but some of them do not. If you don't know, you should use either .setAutoCommit(true) before the transaction or .commit() after it..

edited Oct 17 '16 at 10:05

N00b Pr0grammer

4,503
5
32
46

answered Oct 17 '16 at 09:52

Roc Aràjol

190
9

score 1 · Answer 2 · answered Oct 17 '16 at 09:48

Could be a transaction issue. Perhaps you're not committing your transaction? If so, then it is normal not to see anything in the database.

You can check if this is the case by running a client in READ_UNCOMMITTED transaction mode, right after .executeBatch(); (but before close()) and see if there are any rows.

ScaisEdge · Answer 3 · 2016-10-17T10:13:15.740

1

You don't should assign a value to ID add supply all the others columns name

 insert into demo 
 (
 supplierName 
,supplierId 
,supplierUrl 
,totalActivities 
,activityName 
,activityPrice 
,tourCode 
,starRating 
,totalReviews 
,geography 
,duration 
,category 
,subCategory 
)
values (
'Sharmzad'
,'10006'
,'http://demo.com'
,'3 Results'
,'some values'
,'$44.00'
,'10006P2'
,'No Ratings'
,'No Reviews'
,'Egypt'
,'Duration: 8 hours
','tour'
,'Day Cruises'
);

and add commit to your code

edited Oct 17 '16 at 10:13

answered Oct 17 '16 at 09:49

ScaisEdge

131,976
10
91
107

is it necessary to add ( supplierName ,supplierId ,supplierUrl ,totalActivities ,activityName ,activityPrice ,tourCode ,starRating ,totalReviews ,geography ,duration ,category ,subCategory ) before mentioning values? – roger_that Oct 17 '16 at 09:51
@roger_that If you don't assign all the columns .. you must inform the query which column match the related value.. and this is done by an explicit naming – ScaisEdge Oct 17 '16 at 09:52
Did that. Nothing happening. Do i need to commit the connection as well? – roger_that Oct 17 '16 at 10:08
Yeah. I had to commit. Thanks. – roger_that Oct 17 '16 at 10:12
@roger_that . .. i have update the asnwer ..with the tips for commit – ScaisEdge Oct 17 '16 at 10:14

JDBC : Batch insert not inserting value to database

3 Answers3