S
S
sambuca2018-03-22 14:59:16
Java
sambuca, 2018-03-22 14:59:16

Adding to a joining table (many to many) via JDBC?

There is a database, it has 2 entities and a many to many relationship, I create a DAO for crud operations, then the question arises, I want to create a developer and add skill to him. How do I add data to the skills_developers table.

/*Create table developers*/
 
CREATE TABLE developers (
  id   INT          NOT NULL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
  salary VARCHAR(100) NOT NULL
);
 
/*Create table skills*/
 
CREATE TABLE skills (
  id   INT          NOT NULL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);
 
/*Create table skill_developers with links*/
 
CREATE TABLE skills_developers (
  dev_id    INT NOT NULL,
  sk_id     INT NOT NULL,
  FOREIGN KEY (dev_id) REFERENCES developers (id),
  FOREIGN KEY (sk_id) REFERENCES skills (id)
);

public void addDevSkills(long id, long skillID) {
        List<Long> skillsID = new ArrayList<>();
        Statement statement = null;

        String sql1 = "SELECT * FROM skills";
        String sql2 = "INSERT  INTO skills_developers VALUES (?,?)";
        try (Connection connection = ApplicationJDBC.getConnection()) {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql1);
            while (rs.next()) {
               long i = rs.getLong("id");
                if (skillsID != null) {
                    skillsID.add(i);
                }
            }
            PreparedStatement preparedStatement = connection.prepareStatement(sql2);
            Developer developer = new Developer();
                for (Long sk_id : skillsID) {
                    if (sk_id == skillID) {
                        preparedStatement.setLong(1, developer.getId());
                        preparedStatement.setLong(2, sk_id);
                    }
                }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question