How to solve the Exception while storing the HashMap in MySQL database using java

Question

I am trying to store the HashMap result into MySQL database. The database contain two columns corresponding to the key and value of HashMap. But there are some errors while inserting the HashMap results into the database table. Following is the exception.

Exception in thread "main"
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
error in your SQL syntax; check the manual that corresponds to your
MariaDB server version for the right syntax to use near 'Key, Value)
VALUES ('17/05/2018','11894060.45')' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) 
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939) 
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) 
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547)   
    at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1541)
    at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2605)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1469)
    at com.ceino.Database.executeUpdate(Database.java:46)   
    at com.ceino.CSVwrite.writeCSVFile(CSVwrite.java:198)    
    at com.ceino.CSVwrite.main(CSVwrite.java:120)

Database.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Database {
    private static String DRIVER_NAME = "com.mysql.jdbc.Driver";
    private static String URL = "jdbc:mysql://localhost:3306/Customer";
    private static String USERNAME = "root";
    private static String PASSWORD = "";
    private static Connection conn = null;

    public Database() {
        try {
            Class.forName(DRIVER_NAME);
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        System.out.println("Database Connection Initialized.");
    }

    public void closeConnection() {
        if (conn == null) return;
        try {
            conn.close();
            conn = null;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public boolean execute(String sql) throws SQLException {
        if (conn
                == null)
            throw new SQLException("Connection null!");
        Statement statement = conn.createStatement();
        boolean res = statement.execute(sql);
        statement.close();
        return res;
    }

    public int executeUpdate(String sql) throws SQLException {
        if (conn
                == null)
            throw new SQLException("Connection null!");
        Statement statement = conn.createStatement();
        int res = statement.executeUpdate(sql);
        statement.close();
        return res;
    }

    public ResultSet executeQuery(String sql) throws SQLException {
        if (conn == null)
            throw new SQLException("Connection null!");
        Statement statement = conn.createStatement();
        ResultSet res = statement.executeQuery(sql);
        statement.close();
        return res;
    }
}

FileWrite.java

static Database db = new Database();

    private static void writeCSVFile(HashMap<String, String> result) throws SQLException {

        for (Map.Entry<String, String> next : result.entrySet()) {

            String rslt1 = next.getKey();
            String rslt2 = next.getValue();
            db.executeUpdate("INSERT INTO amount (Key, Value) VALUES ('" + rslt1 + "','" + rslt2 + "');");
        }
        db.closeConnection();
    }

@KarthikeyanVaithilingam Thank you. Can you help me to solve the issues. — Neetha, Sep 03 '19 at 06:30
[!!! SQL INJECTION ALERT !!!](https://en.wikipedia.org/wiki/SQL_injection) — Jim Garrison, Sep 03 '19 at 06:51

score 3 · Accepted Answer · edited Sep 03 '19 at 07:46

3

Key is a reserved keyword in MySQL so you have to escape it or change it with other key word.

Another thing, don't ever use concatenated strings in your query. Instead use PreparedStatement to avoid SQL njection attacks.

edited Sep 03 '19 at 07:46

user207421

305,947
44
307
483

answered Sep 03 '19 at 06:42

Youcef LAIDANI

55,661
15
90
140

1

If really want to use the keywords use backtick like `INSERT INTO amount (\`Key\`, \`Value\`) VALUES` – seenukarthi Sep 03 '19 at 06:50
@Karthikeyan Vaithilingam this is what I mean sorry I use phone for that I don't mention an example thank you for your helpful comment :) – Youcef LAIDANI Sep 03 '19 at 06:55

score 0 · Answer 2 · answered Sep 03 '19 at 06:55

First of all, you should not use key and value as column names since they belong to a set of reserved keywords in MySQL.

Now that you have used them, you need to escape them using ` in your query.

Here is how your code/query should look like:

db.executeUpdate("INSERT INTO amount (`Key`, `Value`) VALUES ('" + rslt1 + "','" + rslt2 + "');");

This will hopefully solve your problem.

Note that, you should avoid concatenating string using + signs and use String.format() instead as it helps in avoiding any syntax mistakes.

Although in case of Queries, you should be using PreparedStatement instead of String concatenation as it would help you avoiding SQL Injection or any other similar vulnerabilities.

I hope this turns out to be helpful for you.

How to solve the Exception while storing the HashMap in MySQL database using java

2 Answers2