我如何使用内部sql stament准备好的语句？

When this is get executed, it throws following exception, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT travel_manager_id FROM travel_agency_manager WHERE user_id=(SELECT user_i' at line 1"

当执行此操作时,它会抛出以下异常:“您的SQL语法中有错误;请检查与您的MySQL服务器版本对应的手册,以便在'SELECT travel_manager_id FROM travel_agency_manager WHERE user_id =(SELECT user_i')附近使用正确的语法在第1行“

public class AgencyRegistrationDAOImpl extends DBConnection {

public class AgencyRegistrationDAOImpl扩展DBConnection {

public int registerAgency(Agency agency) throws SQLException {

    int returnVal = 0;
    Connection connection = getConnection();

        if (connection != null) {

            String query = "INSERT INTO travel_agency (agency_manager_id,trade_name," +
        "company_name,address,email,city,district,tpno,web_link,description,agency_image_link,business_registration_number" +
        ",isApproved) VALUES " +
        "(SELECT travel_manager_id FROM travel_agency_manager WHERE user_id=(SELECT user_id FROM users WHERE email=?)),?,?,?,?,?,?,?,?,?,?,?,?)";


            returnVal = CRUDOperations.insertAsPreparedStmt(query, connection, agency.getUserName(), agency.getTradeName(),
                    agency.getCompanyName(),  agency.getAddress(), agency.getEmail(), agency.getCity(),
                    agency.getDistrict(), agency.getTelePhoneNumber(), agency.getWeblink(), agency.getDescription(),
                    agency.getImagePath(), agency.getRegistration(), 0);

        }

    return returnVal;
}

}

public class CRUDOperations {

公共类CRUDOperations {

public static int insertAsPreparedStmt(String query, Connection connection, Object... values) throws SQLException {

    PreparedStatement preparedStatement = null;
    try {

        if (connection != null) {
            preparedStatement = connection.prepareStatement(query);

            for (int index = 0; index < values.length; index++) {
                preparedStatement.setObject(index + 1, values[index]);
            }
            return preparedStatement.executeUpdate();
        }

    } finally {
        if (!connection.isClosed() && !preparedStatement.isClosed()) {
            preparedStatement.close();
            connection.close();
        }
    }
    return 0;
}

}

1 个解决方案

#1

You have an error in your SQL syntax

您的SQL语法有错误

This is not a correct syntax

这不是一个正确的语法

INSERT INTO ... VALUES SELECT ....

The correct syntax is

正确的语法是

INSERT INTO ... SELECT ...

Here is the mysql doc

这是mysql文档

INSERT INTO travel_agency (
            agency_manager_id,trade_name, company_name,address,email,city,district,tpno,web_link,description,agency_image_link,business_registration_number, isApproved
    ) 
    SELECT travel_manager_id,?,?,?,?,?,?,?,?,?,?,?,?
            FROM travel_agency_manager WHERE user_id=
            (
                SELECT user_id FROM users WHERE email=?
            )

#1