下拉列表使用mysql和jdbc实现

Question

我有两张桌子。第一个表Profession有两列(profession_id、profession_name)。第二个表申请者有五列(applicant_id、profession_id、last_name、first_name、entrance_year)。表申请人中的"profession_id“和表专业中的"profession_id”是mysql中的相关字段。

CREATE TABLE PROFESSION (
  PROFESSION_ID   INT         NOT NULL AUTO_INCREMENT,
  PROFESSION_NAME VARCHAR(50) NOT NULL,
  PRIMARY KEY (PROFESSION_ID)
);

CREATE TABLE APPLICANT (
  APPLICANT_ID  INT         NOT NULL AUTO_INCREMENT,
  PROFESSION_ID INT         NOT NULL,
  LAST_NAME     VARCHAR(30) NOT NULL,
  FIRST_NAME    VARCHAR(30) NOT NULL,
  ENTRANCE_YEAR INT         NOT NULL,
  PRIMARY KEY (APPLICANT_ID),
  FOREIGN KEY (PROFESSION_ID) REFERENCES PROFESSION (PROFESSION_ID)
);

这是我的类，使用DB：

public enum ApplicantDBProvider {

    INSTANCE;

    private Connection connection;

    private ApplicantDBProvider() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_applicant", "root", "toor");
        } catch (ClassNotFoundException | SQLException e) {
            System.err.println("Class not found: com.mysql.jdbc.Driver " + e);
            throw new RuntimeException("Class not found: com.mysql.jdbc.Driver");
        }
    }

    public Applicant getApplicant(long applicantId) throws Exception {
        PreparedStatement preparedStatement = null;
        Applicant applicant = null;
        try {
            preparedStatement = connection.prepareStatement("SELECT * FROM applicant WHERE applicant_id=?");
            preparedStatement.setInt(1, (int) applicantId);

            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                applicant = new Applicant();
                applicant.setId(resultSet.getInt("applicant_id"));
                applicant.setFirstName(resultSet.getString("first_name"));
                applicant.setLastName(resultSet.getString("last_name"));
                applicant.setProfessionId(resultSet.getInt("profession_id"));
                applicant.setEntranceYear(resultSet.getInt("entrance_year"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }

        return applicant;
    }

    public List<Applicant> getApplicants() throws Exception {
        Statement statement = null;
        List <Applicant> applicants = new ArrayList<>();

        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM applicant");
            Applicant applicant = null;
            while (resultSet.next()) {
                applicant = new Applicant();
                applicant.setId(resultSet.getInt("applicant_id"));
                applicant.setFirstName(resultSet.getString("first_name"));
                applicant.setLastName(resultSet.getString("last_name"));
                applicant.setProfessionId(resultSet.getInt("profession_id"));
                applicant.setEntranceYear(resultSet.getInt("entrance_year"));
                applicants.add(applicant);

            }

        } catch (SQLException e) {
            throw new Exception(e);
        }

        return applicants;
    }

    public void saveApplicant(Applicant applicant) throws Exception {
        PreparedStatement preparedStatement = null;

        try {
            if (applicant.getId() == -1) {
                preparedStatement = connection.prepareStatement("INSERT INTO applicant (first_name, last_name, profession_id, entrance_year) VALUES (?,?,?,?)");

                preparedStatement.setString(1, applicant.getFirstName());
                preparedStatement.setString(2, applicant.getLastName());
                preparedStatement.setInt(3, (int)applicant.getProfessionId());
                preparedStatement.setInt(4, applicant.getEntranceYear());

            } else {
                preparedStatement = connection.prepareStatement("UPDATE applicant SET first_name=?, last_name=?, profession_id=?, entrance_year=?  WHERE applicant_id=?");

                preparedStatement.setString(1, applicant.getFirstName());
                preparedStatement.setString(2, applicant.getLastName());
                 preparedStatement.setInt(3, (int) applicant.getProfessionId());
                preparedStatement.setInt(4, applicant.getEntranceYear());
                preparedStatement.setInt(5, (int) applicant.getId());
            }
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new Exception(e);
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }

    public void deleteApplicant(long applicantId) throws Exception {
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement("DELETE FROM applicant WHERE applicant_id=?");

            preparedStatement.setInt(1, (int) applicantId);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new Exception(e);
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }

    public Profession getProfession(long professionId) throws Exception {
        PreparedStatement preparedStatement = null;
        Profession profession = null;
        try {
            preparedStatement = connection.prepareStatement("SELECT * FROM profession WHERE profession_id=?");
            preparedStatement.setInt(1, (int) professionId);

            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                profession = new Profession();
                profession.setId(resultSet.getInt("profession_id"));
                profession.setProfessionName(resultSet.getString("profession_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }

        return profession;
    }

    public List<Profession> getProfessions() throws Exception {
        Statement statement = null;

        List<Profession> professions = new ArrayList<>();
        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM profession");
            Profession profession = null;
            while (resultSet.next()) {
                profession = new Profession();
                profession.setId(resultSet.getInt("profession_id"));
                profession.setProfessionName(resultSet.getString("profession_name"));
                professions.add(profession);
            }

        } catch (SQLException e) {
            throw new Exception(e);
        } finally {
            if (statement != null) {
                statement.close();
            }
        }

        return professions;
    }

    public void saveProfession(Profession profession) throws Exception {
        PreparedStatement preparedStatement = null;

        try {
            if (profession.getId() == -1) {
                preparedStatement = connection.prepareStatement("INSERT INTO profession (profession_name) VALUES (?) ");

                preparedStatement.setString(1, profession.getProfessionName());
            } else {
                preparedStatement = connection.prepareStatement("UPDATE profession SET profession_name=? WHERE profession_id=?");

                preparedStatement.setString(1, profession.getProfessionName());
                preparedStatement.setInt(2, (int) profession.getId());
            }
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new Exception(e);
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }

    }

    public void deleteProfession(long professionId) throws Exception {
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement("DELETE FROM profession WHERE profession_id=?");

            preparedStatement.setInt(1, (int) professionId);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new Exception(e);
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }

这工作得很好。但我需要的不是profession_id，而是一个可用职业的下拉列表。我听说，这是用内部连接实现的。但是怎么做，我不知道。请帮帮忙。我很抱歉你需要阅读大量的代码。谢谢。

Answer 1

将此选择放入申请者的读数中

旧的：

SELECT * FROM applicant

新建：(扩展您要读取的字段)：

更新：profession.id错了，他叫它profession。profession_id在这里

select a.first_name as applicant_first_name
     , a.last_name as applicant_last_name, 
     # more applicant ... 
     , p.profession_name
from applicant a
inner join profession p on p.profession_id = a.profession_id
// where ...

如果你有一个来自外部的特定职业，就把它放在where ...中。