我有两张桌子。第一个表Profession有两列（profession_id，professional_name）。
第二个表格申请人有五列（applicant_id，accounting_id，last_name，first_name，entry_year）。
表Applicant中的“ profession_id”和表Profession中的“ 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();
}
}
}
这很好。但是，我需要获得不是可用的职业的下拉列表，而不是professional_id。我听说这是通过INNER JOIN实现的。但是如何做到这一点，我不知道。请帮忙。很抱歉，您需要阅读很多代码。谢谢。
最佳答案
将此选择放入申请人的阅读中
旧：
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 ...中即可。