2

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.

2
  • 2
    Typically the data structure List<Map<Object, Object>> 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. Commented Sep 3, 2018 at 6:14
  • @TimBiegeleisen ideally it should be List<Employee> :) Commented Sep 3, 2018 at 7:09

4 Answers 4

1

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.

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

Comments

0
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);
    }

}

Comments

0

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.

Comments

0

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
}

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.