1.15.2 Insert hashmap MySQL and create if not exists

Discussion in 'Spigot Plugin Development' started by Xurvold, Apr 2, 2020.

  1. Don't have much experience with MySQL databases so I am asking it here.
    How would I insert my hasmap data into a MySQL database, and how would I create (an offline) table if it can't connect to the database?
     
  2. You'd need to generate the prepared statement SQL string with column names and placeholders yourself. Here's a kickoff example:

    Code (Java):
    StringBuilder sql = new StringBuilder("INSERT INTO ").append(tableName).append(" (");
    StringBuilder placeholders = new StringBuilder();

    for (Iterator<String> iter = dataMap.keySet().iterator(); iter.hasNext();) {
       sql.append(iter.next());
       placeholders.append("?");

       if (iter.hasNext()) {
           sql.append(",");
           placeholders.append(",");
       }
    }

    sql.append(") VALUES (").append(placeholders).append(")");
    preparedStatement = connection.prepareStatement(sql.toString());
    int i = 0;

    for (String value : dataMap.values()) {
       preparedStatement.setObject(i++, value);
    }

    int affectedRows = prepatedStatement.executeUpdate();
    // ...
    This has the additional advantage that you can use Map<String, Object> where Object can also be a Number (Integer, Long, etc), Date, byte[], and so on, at least those types for which the PreparedStatement already has setter methods.

    Keep in mind that you've a serious SQL injection attack hole if the tableName and map keys are controlled by the enduser.