How should I store Map> data into database table?

Question

My map stores the string as key and LinkedList of objects as the values. Now, I need to store this data in the database table. My map data is something like-

Key : Value

id : [2, 3, 4, 5]

name : [Rohit, Iravati, Uttam, Sushil]

jobrole : [Software Engineer, Software Engineer, Manager, Director]

salary : [100, 100, 100, 100]

And my table structure is -

[ID,Name,JOBROLE,Salary]

I am not getting how should I read data from map to create below given queries to store this map data in table -

insert into DBROLTA.Employee(id, name, jobrole, salary) values(2, 'Rohit', 'Software Engineer','100');
insert into DBROLTA.Employee(id, name, jobrole, salary) values(3, 'Iravati', 'Software Engineer','100');
insert into DBROLTA.Employee(id, name, jobrole, salary) values(4, 'Uttam', 'Manager','100');

Can anyone please help me.

Typically the data structure `List>` would be used instead of your approach. This way, we can easily create the insert statement for each element in the list, using the map. My advice, rather than trying to answer this, would be to fix your data structure. — Tim Biegeleisen, Sep 03 '18 at 06:14

score 1 · Answer 1 · answered Sep 03 '18 at 06:28

Instaed of storing the key and respective array of values in map, store the key as id and custom object as value.

where custom object is object with the properties id, name, jobrole and Salary.

create Map and store the custom objects against to the ID.So you can easily retrieve the objects and create sql statements and execute them.

 Map<Interger, CustomObject> = new HashMap<Integer, CustomObject>

For multiple inset query execution use batch insert and batch execute methods, this is to avoid multiple database hits.

refer : How to execute multiple SQL statements from java for batch update operations.

score 0 · Answer 2 · answered Sep 03 '18 at 06:26

you can use a for loop to iterate through all of your data and build your query string one at a time

maybe something like this

for(int j = 0; j < mapOfData.get("ID").length(); j++){
     strID = mapOfData.get("ID").get(j);
     strName = mapOfData.get("Name").get(j);

     query = "INSERT INTO TABLE VALUES (" + strID + ", " + strName + ")";
}

you iterate through the entire length of your map (given that there are NO MISSING DATA in your map), access each of the linkedlists and get the value from there.

score 0 · Accepted Answer · answered Sep 03 '18 at 06:38

public static void main(String[] args) {
    Map<String, LinkedList<Object>> map = new HashMap<>();
    LinkedList<Object> data = new LinkedList<>();
    data.add(2);
    data.add(3);
    map.put("id", data);

    data = new LinkedList<>();
    data.add("Rohit");
    data.add("Iravati");
    map.put("name", data);

    data = new LinkedList<>();
    data.add("SE");
    data.add("SSE");
    map.put("jobrole", data);

    data = new LinkedList<>();
    data.add(100);
    data.add(200);
    map.put("salary", data);

    for (int i = 0; i < map.get("id").size(); i++) {
        Object id = map.get("id").get(i);
        Object name = map.get("name").get(i);
        Object jobrole = map.get("jobrole").get(i);
        Object salary = map.get("salary").get(i);
        // TODO Use above values to create your query.
        System.out.println(id + "-" + name + "-" + jobrole + "-" + salary);
    }

}

score 0 · Answer 4 · answered Sep 03 '18 at 06:56

This is a bad way to do it, but your current data structure doesn't leave me much options.

public static void main(String[] args) {

    //GIVEN
    Map<String, LinkedList<Object>> data = new HashMap<>();
    data.put("id", Stream.of(1, 2, 3)
            .collect(Collectors.toCollection(LinkedList::new)));
    data.put("name", Stream.of("a", "b", "c")
            .collect(Collectors.toCollection(LinkedList::new)));
    data.put("jobrole", Stream.of("x", "y", "z")
            .collect(Collectors.toCollection(LinkedList::new)));
    data.put("salary", Stream.of(10.0, 20.0, 30.0)
            .collect(Collectors.toCollection(LinkedList::new)));

    //Let's fix your data structure first
    Employee[] employeesArr = null;
    for (Map.Entry<String, LinkedList<Object>> entry : data.entrySet()) {
        int count = 0;
        if (employeesArr == null) {
            employeesArr = new Employee[entry.getValue().size()];
            for (int i = 0; i < employeesArr.length; i++) {
                employeesArr[i] = new Employee();
            }
        }
        switch (entry.getKey()) {
            case "id":
                for (Object o : entry.getValue()) {
                    employeesArr[count++].setId((Integer) o);
                }
                break;
            case "name":
                for (Object o : entry.getValue()) {
                    employeesArr[count++].setName((String) o);
                }
                break;
            case "jobrole":
                for (Object o : entry.getValue()) {
                    employeesArr[count++].setRole((String) o);
                }
                break;
            case "salary":
                for (Object o : entry.getValue()) {
                    employeesArr[count++].setSalary((Double) o);
                }
                break;
        }
    }

    //employeesArr is a much better data structure
    for (int i = 0; i < employeesArr.length; i++) {
        //use PreparedStatement or an ORM
        System.out.println("insert into DBROLTA.Employee(id, name, jobrole, salary) values ("
                + employeesArr[i].getId() + ", '"
                + employeesArr[i].getName() + "', '"
                + employeesArr[i].getRole() + "', "
                + employeesArr[i].getSalary()
                + ");");
    }

}

Employee.java

public class Employee {
    private int id;
    private String name;
    private String role;
    private double salary;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    //other getters/setters
}

How should I store Map> data into database table?

4 Answers4