java - 使用 'OPE' =? 等语法时，字符串到数字的转换失败

Question

我尝试使用 Spring JDBC 模板运行此查询

  public static String FIND_CNC_OPE_GRAPPE_BY_FCT_ID = 
        "SELECT "
                    +"EXPO.COD_NAT_XPN "
                    +", EXPO.IDF_TEC_XPN "
                    +", EXPO.IDF_FCT_XPN_RIS "
                    +", EXPO.DAT_PRM_IPE_LC_CEC  "
        +"FROM  T_CCA_EXPO EXPO "
        +"WHERE "
        + " 'OPE' = ? ";

为此我使用这个java代码:

  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.ArrayList;
  import java.util.List;

  import org.apache.commons.lang3.StringUtils;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.jdbc.core.JdbcTemplate;
  import org.springframework.stereotype.Repository;

  import lombok.extern.slf4j.Slf4j;

  @Repository
  @Slf4j
  public class ContractGrappeRepository implements ContractGrappeInterfaceRepository {

       static String query = GrappeQueryConstant.FIND_CNC_OPE_GRAPPE_BY_FCT_ID;

       @Autowired
       private JdbcTemplate baseJDBCTemplate;

       private Connection dbConnection;

       private PreparedStatement statement = null;

       private ResultSet dealResultSet;

       @Override
       public ResultSet findGrappeByDealFctID() {

       try {
          dbConnection = baseJDBCTemplate.getDataSource().getConnection();
          statement = dbConnection.prepareStatement(query);
          statement.setString(1,"AKA");
          dealResultSet = statement.executeQuery();
       } catch (SQLException e) {
          throw new RestructNumberException("Exception while fetching contracts",e);
       }

       return null; // TO MODIFY
   }

 }

当编译器到达这一行时 dbConnection.prepareStatement(query);我收到此错误消息；

   java.sql.SQLException: [Teradata Database] [TeraJDBC 16.00.00.24] [Error 3535] [SQLState 22003] A character string failed conversion to a numeric value.
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:309)
at com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:103)
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:311)
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:200)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:137)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.run(StatementController.java:128)
at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:389)
at com.teradata.jdbc.jdbc_4.TDStatement.prepareRequest(TDStatement.java:576)
at com.teradata.jdbc.jdbc_4.TDPreparedStatement.<init>(TDPreparedStatement.java:128)
at com.teradata.jdbc.jdk6.JDK6_SQL_PreparedStatement.<init>(JDK6_SQL_PreparedStatement.java:30)
at com.teradata.jdbc.jdk6.JDK6_SQL_Connection.constructPreparedStatement(JDK6_SQL_Connection.java:82)
at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1330)
at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1374)
at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1360)
at com.teradata.jdbc.jdbc.ManagerConnectionBase.prepareStatement(ManagerConnectionBase.java:294)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)

当我在 Terdata sql Assistant 上运行查询时，没有任何错误。但是当我更改查询中的 where 子句时

    +"WHERE "
    + " 'OPE' = ? ";

至

    +"WHERE "
    + " 'OPE' = EXP.COD_NAT_XPN ";

我没有任何错误，

为什么java不接受这个语法'OPE' = ?

我正在使用 Spring boot 1.5.9.RELEASE

Teradata 版本16.00.00.24

Answer 1

将statement.setString(1,"AKA")更改为setInt

编辑:在

statement = dbConnection.prepareStatement(query);

由于以下规则，您收到错误 link TeraData 正在尝试编译您的 SQL 查询。

对于您的问题，我找到了解决方案 here 。您应该做的是更改查询，如下所示:

" 'OPE' = cast(? as number) ";