通过替换给定列的值来显示表中的行

Question

我有两个表，部门和员工都有公共列department\u id

首先，我显示了departments表中的部门名称，然后显示了employees表中的Employeer\u id。现在我的查询是，我想从用户那个里获取部门名称，并和部门表进行比较，若名称可用，结果应该显示该特定部门的员工id

    import java.sql.*;
    class OracleCon {
    public static void main(String args[]) {
       try {
        //step1 load the driver class   
           Class.forName("oracle.jdbc.driver.OracleDriver"); 
        //step2 create the connection object 
           Connection con= DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:xe","hr","hr"); 
        //step3 create the statement object 
           Statement stmt = con.createStatement();
           ResultSet rs = stmt.executeQuery("select * from departments");
          while (rs.next()) {
            String dname = rs.getString("DEPARTMENT_NAME");
            System.out.println(dname);
          }

      Statement stmt1 = con.createStatement();
      ResultSet rs1 = stmt1.executeQuery("select * from employees");
      while (rs1.next()) {
         Integer eno = rs1.getInt("employee_id");
         System.out.println(eno);

      } 
       con.close();
     } catch (Exception e) {
         System.out.println(e);

     }
   }
}

Answer 1

我将假设您的employees和department表具有外键关系（或逻辑键关系），这样employees就可以。部门名称=部门。部门名称。一个简单的数据库连接应该可以完成这项工作。

此外，如果要按部门名称（或任何其他变量）搜索，请使用PreparedStatement，而不是语句。

String sql = "select e.* from employee e,  department d where d.department_name = ? and d.department_name=e.deparment.name"); )// ? will be replaced by a parameter obtained from a user

PreparedStatement stmt = con.prepareStatement(sql);

stmt.setString(1, deptName); //deptName is obtained from the user

ResultSet rs = stmt.executeQuery();
.....