Sql错误，当我在JAVA代码中实现联接时

Question

我有两张桌子。

第一桌，

Profession和两个columns (profession_id, profession_name)。

第二桌，

Applicant和五columns (applicant_id, profession_id, last_name, first_name, entrance_year)。

表Applicant中的Applicant和表Profession中的profession_id是MySQL中的相关字段。

我需要，而不是profession_id，得到一个下拉列表的可用职业。在这里，我使用INNER JOIN实现了

这是我的班，和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");
        ResultSet resultSet = statement.executeQuery("select a.applicant_id, a.first_name, a.last_name, a.entrance_year, p.profession_name from applicant a join profession p on a.profession_id = p.profession_id");
        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 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();
        }
    }

}

我的jsp：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
  <title></title>
</head>
<body>
<h1>Add applicant</h1>

<form method="post" action="controller?command=saveApplicant">

  <table>
    <tr>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Profession ID</th>
      <th>Entrance Year</th>
    </tr>
  </table>

  <c:choose>
    <c:when test="${applicant ne null}">
      <input type="text" name="first_name" value="${applicant.getFirstName()}"/>
      <input type="text" name="last_name" value="${applicant.getLastName()}"/>
      <input type="text" name="profession_id" value="${applicant.getProfessionId()}"/>
      <input type="text" name="entrance_year" value="${applicant.getEntranceYear()}"/>
      <input type="hidden" name="applicant_id" value="${applicant.getId()}"/>
    </c:when>
    <c:otherwise>
      <input type="text" name="first_name" value=""/>
      <input type="text" name="last_name" value=""/>
      <input type="text" name="profession_id" value=""/>
      <input type="text" name="entrance_year" value=""/>
    </c:otherwise>
  </c:choose>
  <input type=submit value=submit>
</form>
</body>
</html>

我在数据库中实现了这个查询(您可以在我的代码中看到)：

select a.applicant_id, a.first_name, a.last_name, a.entrance_year, p.profession_name from applicant a join profession p on a.profession_id = p.profession_id

但是，当我运行服务器并打开jsp时，出现了错误: java.lang.Exception: java.sql.SQLException:列'profession_id‘未找到。

我不明白，这是一个多么大的错误。会是什么？请告诉我。谢谢。

Answer 1

编辑：

在这个查询中，

从申请者中选择a.applicant_id，a.first_name，a.last_name，a.entrance_year，p.profession_name，p.profession_id

你只有，

a.applicant_id，a.first_name，a.last_name，a.entrance_year，p.profession_name

没有p.profession_id

所以当你做一个，

applicant.setProfessionId(resultSet.getInt("profession_id"));

您将得到一个异常，因为结果列表中没有该列。

ResultSet resultSet = statement.executeQuery("select a.applicant_id, a.first_name, a.last_name, a.entrance_year, p.profession_name, p.profession_id from applicant a join profession p on a.profession_id = p.profession_id");

回答你的，

我有课。Applicant.java。在这个类中，我有长数据类型的professionId。现在是我的问题。当我更改applicant.setProfessionId(resultSet.getInt("profession_id")) on applicant.setProfessionId(resultSet.getString("profession_name"))，时，我需要将professionId更改为字符串数据类型

可以将类变量添加到Application.java类中，

作为，

private String professionName;

public String getProfessionName(){
    return professionName;
}


public String setProfessionName(String professionName){
    this.professionName = professionName
}

两者兼有，

applicant.setProfessionId(resultSet.getInt("profession_id"));
applicant.setProfessionName(resultSet.getString("profession_name"));

不过，

如果你在尝试，我引用你的话

而不是profession_id，得到一个可用职业的下拉列表。

您应该以不同的方式来处理这个问题，因为这个联接只会为一个profession_name提供一个application，

在taglib和数据绑定上拥有一个战利品。

编辑2: