java - 使用java在sql插入上声明标量变量

Question

我正在尝试插入数据库，但额外的好处是检查是否存在基于 5 个字段的重复数据。

例如，我想插入一行数据，但对于每个 ID，它将检查 4 个其他字段，如果所有 5 个字段都匹配我不想插入。除此之外，插入。

到目前为止，我已经做出了 sql 语句。我在一个数据库虚拟对象上尝试了我的查询，看看它是否有效，但是当我在 java 中使用所有准备好的语句运行它时，它给了我一个错误，上面写着“必须声明标量变量”。这是我的代码，我不知道在哪里声明或必须声明什么。它取决于数据库的配置吗？

System.out.println("connection created successfully using properties file");
    PreparedStatement pstmt2 = null;
    PreparedStatement pstmt3 = null;
    PreparedStatement pstmt5 = null;
    PreparedStatement pstmt6 = null;
    ResultSet rs = null;


    BufferedReader reader = null;
    try {
        reader = new BufferedReader(new FileReader(
                                "C:\\Users\\darroyo\\Documents\\pruebasx.txt"));
    } catch (FileNotFoundException e1) {
    logger.error(e1.getMessage());
    }
    String line = null;
    try {
        line = reader.readLine();
    } catch (IOException e1) {
    logger.error(e1.getMessage());
    }


    String query = " insert into FRONTMC.HECHO (folio_hecho, folio_orden,"
                                + "clave_sentido, titulos_hecho, precio, importe, liquidacion, contraparte, id_estatus, isin, contrato,"
                                + "secondary_exec_id, exec_id, F11_ClOrdID, fecha_recepcion, fecha_sentra,emisora,serie)"
                                + " select ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,convert(varchar(30),cast(? as datetime),120),convert(varchar(30),cast(? as datetime),120),?,?"
                                +" from FRONTMC.HECHO WHERE NOT EXISTS (SELECT * FROM FRONTMC.HECHO WHERE ISIN = ?"
                                + "AND EMISORA = ? AND SERIE = ? AND CLAVE_SENTIDO = ? AND SECONDARY_EXEC_ID =?";



    PreparedStatement preparedStmt = null;
    try {
         preparedStmt = con.prepareStatement(query);
    } catch (SQLException e1) {
    logger.error(e1.getMessage());
    }

    Map<Integer,String> hm1 = new HashMap<Integer,String>();
    try {
        do {
            try{
                String[] tokens = line.split("");
                for (int i = 0; i != tokens.length; i++) {

                int dataIndex = tokens[i].indexOf('=') + 1;
                String data = tokens[i].substring(dataIndex);
                hm1.put(new Integer(i),data);
                }
                String query2 = "select emisora from FRONTMC.EMISORA_CUSTODIO_SIGLO where isin = ?";
                String query5 = " insert into FRONTMC.HECHO (emisora)"
                                    + " values ( ?)";

                pstmt2 = con.prepareStatement(query2);
                pstmt5 = con.prepareStatement(query5);

                String query3 = "select serie from FRONTMC.EMISORA_CUSTODIO_SIGLO where isin = ?";
                String query6 = " insert into FRONTMC.HECHO (emisora)"
                                    + " values ( ?)";
                pstmt3 = con.prepareStatement(query3); // create a statement
                            pstmt6 =con.prepareStatement(query6);


    setParameterString(preparedStmt,1, hm1.get(23));

    setParameterString(preparedStmt,2, hm1.get(19));

    setParameterString(preparedStmt,3, hm1.get(15));

    setParameterString(preparedStmt,4, hm1.get(30));

    setParameterString(preparedStmt,5, hm1.get(16));

    setParameterString(preparedStmt,6, hm1.get(18));

    setParameterString(preparedStmt,7, hm1.get(8));

    setParameterString(preparedStmt,8, hm1.get(33));

    setParameterString(preparedStmt,9, hm1.get(27));

    setParameterString(preparedStmt,10, hm1.get(17));

    setParameterString(preparedStmt,11, hm1.get(26));

    setParameterString(preparedStmt,12, hm1.get(23));

    setParameterString(preparedStmt,13, hm1.get(10));

    setParameterString(preparedStmt,14, hm1.get(14));

    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMdd");
    SimpleDateFormat sdf2 = new SimpleDateFormat("dd/MM/yyyy");
    String ds2 = null;

    ds2 = sdf2.format(sdf1.parse(hm1.get(6)));

    String newfecha1 = ds2;

    setParameterString(preparedStmt,15, newfecha1);

    SimpleDateFormat sdf3 = new SimpleDateFormat("yyyyMMdd");
    SimpleDateFormat sdf4 = new SimpleDateFormat("dd/MM/yyyy");
    String ds4 = null;

    ds4 = sdf4.format(sdf3.parse(hm1.get(6)));

    String newfecha3 = ds4;

    setParameterString(preparedStmt,16, newfecha3);

    pstmt2.setString(1, hm1.get(17)); 
    rs = pstmt2.executeQuery();

    while (rs.next()) {

    String emisora = rs.getString(1);
    pstmt5.setString(1,emisora);
    setParameterString(preparedStmt,17, emisora);

    pstmt3.setString(1, hm1.get(17)); 
    rs = pstmt3.executeQuery();

    while (rs.next()) {

    String serie = rs.getString(1);
    pstmt6.setString(1,serie); 


    System.out.println(serie);
    setParameterString(preparedStmt,18, serie);

    setParameterString(preparedStmt,19, hm1.get(17));

    setParameterString(preparedStmt,20, emisora);

    setParameterString(preparedStmt,21, serie);

    setParameterString(preparedStmt,22, hm1.get(15));

    setParameterString(preparedStmt,23, hm1.get(23));

    preparedStmt.execute();
    }

    }}catch(Exception ab){
        new Thread(new Runnable(){
        @Override
        public void run(){
        errorcon2();
        }
        }).start();
        logger.error(ab.getMessage());
        System.out.println(ab.getMessage());
        ab.printStackTrace();
        }

        }while ((line = reader.readLine()) != null);}
        catch(Exception a){
        logger.error(a.getMessage());
        }

        new Thread(new Runnable(){
        @Override
        public void run(){
        exitomsj();
        }
        }).start();

这是我得到的例外：误译它是不可扩展的，它是标量的。

com.microsoft.sqlserver.jdbc.SQLServerException: Debe declarar la variable escalar "@P18AND".
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:367)
at swt.swtapp2$2.mouseDown(swtapp2.java:488)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:193)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4418)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1079)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4236)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3824)
at org.eclipse.jface.window.Window.runEventLoop(Window.java:818)
at org.eclipse.jface.window.Window.open(Window.java:794)
at swt.swtapp2.main(swtapp2.java:610)

标签： javasql-serverjdbcinsertduplicates

Answer 1

你少了一个空格。

+ "AND EMISORA = ? AND ...

应该

+ " AND EMISORA = ? AND ...