There is a database, it has 2 entities and a many to many connection, I create a DAO for crud operations, then the question arises, I want to create a developer and add skill to it. 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(String dev_name, String sk_name) { String sql = "INSERT INTO skills_developers (dev_id, sk_id) " + "SELECT developers.id, skills.id " + "FROM developers, skills " + "WHERE d.name = ? AND s.name = ?"; try (Connection connection = ApplicationJDBC.getConnection()) { PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, developer.getFullName()); preparedStatement.setString(2, skill.getName()); } catch (SQLException e) { e.printStackTrace(); } }

Why not add to the table ??

    1 answer 1

     INSERT INTO skills_developers (dev_id, sk_id) SELECT d.id, s.id FROM developers d, skills s WHERE d.name = '?' AND s.name = '?';

    In the parameters, we pass the person’s last name and the name of his skill.

    Ps. And it would be nice to do

     ALTER TABLE skills_developers ADD PRIMARY KEY (dev_id, sk_id);

    Well, in order to accidentally add two times ...

    • The request itself is working, but for some reason it doesn’t add it to the table via connection - h0la_dev
    • the request is working but for some reason it doesn’t add through connection. So, somewhere in the Connection is plowed up ... for example, a jamb with encodings or there quotes in the data. No errors or warnings? Look in the General Log - whether the request gets to the server, and if so, in what form. - Akina
    • I would like to initially do this: select all skill IDs and then getById one by one - add our razbotchika to the collection. But he stalled - h0la_dev
    • Are you trying to transfer the list of skills to the request? I would like to do it initially. Are you trying to beat the SQL server in terms of data processing efficiency? in vain ... - Akina