Home > OS >  How do I insert into database from model that get value from an array
How do I insert into database from model that get value from an array

Time:05-16

I have a table in a database with 2 columns:

MyTable

Column1 Column2

In my code, I have 2 String arrays (strArr, compareArr) and a model with 2 attribute reference MyTable to keep values.

    class Model()
         int col1;
         String col2;
         constructors; getter; setter;

Then, my strArr and compareArr have some values:

    strArr = {"One", "Two", "Five"};
    compareArr = {"One", "Two", "Three", "Four", "Five"};

I want to put the strArr value into my Model with col2.setCol2(strArr(i)) that equals to compareArr(j). The col1's value is unchangeable during the loop.

This is the code I've tried:

    Model model = new Model();
    model.setCol1(1);
    for(String value : strArr)
    {
        for(String cmpVal : compareArr)
        {
            if(value.equalsIgnoreCase(cmpVal))
            {
                model.setCol2(cmpVal);
                break;
            }
        }
        myTableController.insertMyTable(model); // Insert function into MyTable
    }

insertMyTable(model):

    public void insertMyTable(Model model){
        try{
            String url = "jdbc:msql://localhost/TestDB";
            Connection conn = DriverManager.getConnection(url,"sa","123");
            String sql = "INSERT INTO MyTable VALUES (?, ?)"
            PreparedStatement ps = conn.prepareStatement(); 
            ps.setInt(1, model.getCol1);
            ps.setString(2, model.getCol2);
            ps.executeUpdate();
            conn.close();
        }catch(Exception ex){
            ex.printStackTrace();
        }
    }

The problem is that I can only insert the first model into the db while the rest fails.

CodePudding user response:

Your db code seems OK but I would use try-with-resources blocks. Run this and please confirm if this is what you intend:

import java.util.List;
import java.util.ArrayList;
import java.util.stream.Collectors;

public class Model {
    private int col1;
    private String col2;

    public Model() {
    }

    public Model(int col1, String col2) {
        this.col1 = col1;
        this.col2 = col2;
    }

    public int getCol1() {
        return this.col1;
    }

    public String getCol2() {
        return this.col2;
    }

    public void setCol1(int col1) {
        this.col1 = col1;
    }

    public void setCol2(String col2) {
        this.col2 = col2;
    }

    public String toString() {
        return String.format("%s=%d,%s=%s", "col1", col1, "col2", col2);
    }

    public static void main(String[] args) {
        String[] strArr = { "One", "Two", "Five" };
        String[] compareArr = { "One", "Two", "Three", "Four", "Five" };
        List<String> common = new ArrayList<>(List.of(strArr));
        common.retainAll(new ArrayList<>(List.of(compareArr)));
        List<Model> models = new ArrayList<>();
        for (String col2 : common) {
            Model m = new Model();
            m.setCol1(10);
            m.setCol2(col2);
            models.add(m);
        }
        System.out.println(models);
    }
}
  • Related