Insert query to mysql database from java not working

Question

I have a problem with following method. The method generate a valid query, but it cannot insert the table monitor.

The method:

public void upLoadData(String table, String[] dbCols,List<LinkedHashMap<String,String>> values){
        String wq = "INSERT INTO "+table+" (";
        for (int j = 0; j < dbCols.length; j++) {
            wq+=dbCols[j]+",";
        }
        wq = wq.replaceAll(",$", ""); //Remove comma from the end of line.
        wq+=") VALUES";

        for (LinkedHashMap<String, String> val : values) {
            wq+="(";
            for (int i = 0; i < dbCols.length; i++) {
                wq+="'"+val.get(dbCols[i])+"',";
            }
            wq = wq.replaceAll(",$","");
            wq+="),";
        }
        wq = wq.replaceAll(",$", ";");

        System.out.println(wq);
        myDB.DBInsert(wq, MyDBPool.getConnections());
}

And the insert method:

public void DBInsert(String query, Connection conn){
    Statement stm;
    try{
        stm = conn.createStatement(); // conn from db connection pool
        stm.executeUpdate(query);
    }catch(SQLException ex){ex.getLocalizedMessage();}
}

The output is:

INSERT INTO monitor (id,arr_date,company,disp_size,disp_type,producer,prod_type,color,cond_cat,comments) 
VALUES('H13/2:3445','2015-11-15','Valami','jó','21','Dell','T32','fekete','B','sadsadasdasd'),
('H14:/3:5567','2015-11-15','Nincs','TFT','19','HP','B32','piros','A','sadsadasd'),
('H13/8:3321','2015-11-15','CCCP','CRT','19','nincs','T24','fehér','D','sadsadsad');

Manualy(PhPMyAdmin) insert is not problem. I use JDBC, and Hikari dbpool, XAMPP v3.2.1

Any help will be appreciate. Thanks!

No, it seems to every thing is alright, no error message nor from mysql, java — BalckHand
– BalckHand, Commented Nov 15, 2015 at 7:45

Elliott Frisch · Accepted Answer · 2015-11-15 07:50:44Z

2

You aren't commiting, and you aren't closeing your Statement (or Connection). You could use a try-with-resources close and add a commit like

public void DBInsert(String query, Connection conn) {
    try (Statement stm = conn.createStatement()){
        stm.executeUpdate(query);
        conn.commit();
    } catch (SQLException ex) {
        ex.getLocalizedMessage();
    } finally {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Also, I strongly recommend you use a PreparedStatement and ? placeholder (or bind paramters) for performance and security reasons instead of building your inserts as String.

edited Nov 15, 2015 at 7:50

answered Nov 15, 2015 at 7:46

Elliott Frisch

202k20 gold badges166 silver badges265 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

antoniodvr Over a year ago

Moreover I suggest to use StringBuilder/StringBuffer to construct the queries if you need to append any values.

BalckHand Over a year ago

Thanks, but still not working, I update my method as you adviced, change Statement to PreparedStatement, put placeholders, but no affect. By the way I have another query that use the original insert method and it's work fine. Is there any opportunities the primary key of the table is disturb the insert task?

BalckHand · Accepted Answer · 2015-11-15 11:41:01Z

0

Thanks for the help and advise, finally I found the problem, my old insert method is work fine(but I update and refact as Elliott Frisch advised), the problem is only I swap two columns with different types,so PhPMyAdmin is handle the error(give 0 to mismatch value), but Java JDBC cannot resolve, so drop the insert task.

Once more, Thanks a lot! :)

edited Nov 15, 2015 at 11:41

answered Nov 15, 2015 at 11:31

BalckHand

34 bronze badges

Collectives™ on Stack Overflow

Insert query to mysql database from java not working

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related