java - 实时服务器上的 MYSQL 未知列错误

Question

从Java中的servlet中，我调用一个函数来执行查询，查询如下，我尝试过这些格式:

错误是:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'TFM00100' in 'field list'

我尝试过的查询:

 SELECT * FROM tbl_contract AS tc   WHERE  tc.`contact_id` = 'TFM00100'    
 SELECT * FROM tbl_contract AS tc   WHERE  tc.`contact_id` = "TFM00100"    
 SELECT * FROM tbl_contract   WHERE  `contact_id` = 'TFM00100'    
 SELECT * FROM tbl_contract   WHERE  `contact_id` = "TFM00100"     
 SELECT * FROM tbl_contract   WHERE  contact_id = "TFM00100"     
 SELECT * FROM tbl_contract AS tc  WHERE (tc.`contact_id`  =  'TFM-00100');

我也尝试过使用准备好的声明。

但是当调用特定函数从实时服务器上的 servlet 执行查询时，会返回相同的错误，在本地服务器上它工作正常。此外，当在实时 MySQL 数据库上执行特定查询时，它工作正常。

仅 Servlet 相关代码:

public class AttandenceInOutReport extends HttpServlet {

private static final long serialVersionUID = 1L;

public AttandenceInOutReport() {
    super();
}

protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    proccessRequest(request, response);
}

protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    proccessRequest(request, response);
}

protected void proccessRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    /**
     * Setting default response
     */
     // Rest of code

    try {
        /**
         * converting request into string
         */
        String res = WebServiceUtils.parseRequestToString(request);

        /**
         * Request write in logger
         */
        logger.info("Request from client" + res);

        AttandenceReportReq reportReq = new Gson().fromJson(res, AttandenceReportReq.class);

        if (reportReq != null && !reportReq.equals("") && (!WebServiceUtils.isEmpty(reportReq.getStartDate()))
                && (!WebServiceUtils.isEmpty(reportReq.getEndDate())) 
                && (!WebServiceUtils.isEmpty(reportReq.getContractId()))) {

            final DateTimeFormatter formatter = DateTimeFormat.forPattern("yyyy-MM-dd");

            LocalDate startDate = formatter.parseLocalDate(reportReq.getStartDate());
            LocalDate endDate = formatter.parseLocalDate(reportReq.getEndDate());
            String contractId = reportReq.getContractId();

            //List<TblContract> contactList = new ArrayList<TblContract>();

            // Function being called here

            String contractName = CommonHandler.getContract(contractId);

           // Rest of the code
           // ...............

        }

    } catch (Exception e) {
        success = false;
        logger.error("Exception in processing request " + e);
    }

           // Rest of the code
           // ...............
    }
}

被调用的函数:

public static String getContractName(String conId) {

    Connection conn = (Connection) DBConnection.getInstance().getConnection();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String contractName = "";

    String query =  "SELECT contract_name FROM tbl_contract WHERE contact_id = '" + conId + "' ";

    try {
        pstmt = conn.prepareStatement(query);
        rs = pstmt.executeQuery();

        if(rs.next()) {    
            contractName = rs.getString("contract_name");   
        }

   } catch (Exception e) {
       e.printStackTrace();
       return contractName;   
   } finally {
       if (conn != null) {    
       try {
           rs.close();
           pstmt.close();
           conn.close();    
       } catch (Exception e) {
           e.printStackTrace();
           return contractName;
       }
   }
  }
    return contractName; 
}

Answer 1

您可以尝试以下代码:

   String query =  "SELECT contract_name FROM tbl_contract WHERE contact_id = ? ";

    try {
        pstmt = conn.prepareStatement(query);
        ps.setString(1, conId);
        rs = pstmt.executeQuery();