在准备好的语句中连接字符串的方法

问题描述

我有一个Web应用程序，需要根据用户的输入将其数据存储在特定的表中。例如如果用户输入代码A，他们的数据将保存到DatabaseA，而如果用户输入代码B，则数据将保存到DatabaseB。我需要知道是否有办法操作预准备语句，以便将代码连接到预准备语句字符串的末尾。

I have a web application that needs to be stored its data on specific tables depending on the user's input. e.g. if a user input Code "A" their data will be saved to DatabaseA, while if a user input Code "B", the data will be saved to DatabaseB. I need to know if there is way to manipulate the prepared statement in order to just concatenate the code into the end of the prepared statement string.

我有3个表，MachineProblem2A ，2B和2C。有没有办法简单地将代码连接到我下面的sql字符串的末尾？

I have 3 tables, MachineProblem2A,2B,and 2C. is there a way to just simply concatenate the code in to the end of my sql string below?

        String sql="select * from MachineProblem2";
        PreparedStatement pstmnt= conn.prepareStatement(sql);

我尝试了不同的方式，例如select * from MachineProblem2+ employeeCode
但它不起作用。我试过select * from MachineProblem2.concat（employeeCode）它也不能正常工作。
我甚至尝试过在条件语句存在的情况下创建的函数，它取决于employeeCode，例如： if（employeeCode.equals（A）返回select * from MachineProblem2A

I tried different ways like "select * from MachineProblem2"+employeeCodebut it's not working. I tried "select * from MachineProblem2".concat(employeeCode) it's not working as well. I even tried a function that I created where in conditional statements exist and would depend on the employeeCode e.g. if(employeeCode.equals("A") return "select * from MachineProblem2A"

所有这些都给我一个空指针异常或 java.lang.InstantiationException：在范围内找不到bean employeeRecords 错误。

all of this gives me either a Null Pointer Exception or java.lang.InstantiationException: bean employeeRecords not found within scope error.

我在sqlRet（）和getAllRecords（）上得到Null指针但是数据输入正确地存储在指定的表中。我在检索输出时遇到问题。

I'm getting Null pointer on sqlRet() and getAllRecords() but The data inputs were stored properly on the designated tables. I'm having problem with retrieving them for output.

private String sqlInsert(){
    if(employeeCode.equals("A") && employeeSales > 2500){
        return "insert into MachineProblem2A(EmployeeName, EmployeeCode, EmployeeSales, EmployeeGross, EmployeeCommission, EmployeeResult)"+ "values(?,?,?,?,?,?)";
    }else if(employeeCode.equals("B") && employeeSales > 2000){
        return "insert into MachineProblem2B(EmployeeName, EmployeeCode, EmployeeSales, EmployeeGross, EmployeeCommission, EmployeeResult)"+ "values(?,?,?,?,?,?)";
    }else if(employeeCode.equals("C") && employeeSales > 1500){
        return "insert into MachineProblem2C(EmployeeName, EmployeeCode, EmployeeSales, EmployeeGross, EmployeeCommission, EmployeeResult)"+ "values(?,?,?,?,?,?)";
    } return null;

}       
private String sqlRet(){
    if(employeeCode.equals("A") && employeeSales > 2500){
        return "select * from MachineProblem2A";
    }else if(employeeCode.equals("B") && employeeSales > 2000){
        return "select * from MachineProblem2B";
    }else if(employeeCode.equals("C") && employeeSales > 1500){
        return "select * from MachineProblem2C";
    } return null;

}

 private void insertRecord(Connection conn){

    try{


        PreparedStatement pstmnt= conn.prepareStatement(sqlInsert());
        pstmnt.setString(1, employeeName);
        pstmnt.setString(2, employeeCode);
        pstmnt.setDouble(3, employeeSales);
        pstmnt.setDouble(4, gross);
        pstmnt.setDouble(5, commission);
        pstmnt.setDouble(6, result);

        //now commit to database
        pstmnt.executeUpdate();

    }catch(SQLException sqle){
        sqle.printStackTrace();
    }

}


// get records

    public ResultSet getAllRecords(Connection conn){
    ResultSet records = null;

    try{


        PreparedStatement pstmntA= conn.prepareStatement(sqlRet());

        records= pstmntA.executeQuery();



    }catch(SQLException sqle){
        sqle.printStackTrace();
    }
    return records;
}

//错误//

java.lang.NullPointerException
edu.ust.iics.employee.model.EmployeeBean.sqlRet(EmployeeBean.java:183)
edu.ust.iics.employee.model.EmployeeBean.getAllRecords(EmployeeBean.java:225)
edu.ust.iics.employee.controller.EmployeeHistoryServlet.doPost(EmployeeHistoryServlet.java:35)
edu.ust.iics.employee.controller.EmployeeHistoryServlet.doGet(EmployeeHistoryServlet.java:28)
javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

推荐答案

您可以在下方使用查询

You can use below query

SELECT T1.COLUMN_NAME || T2.COLUMN_NAME || T3.COLUMN_NAME
FROM
TABLE_1 T1, TABLE_2 T2, TABLE_3 T3
WHERE T1.PRIMARY_KEY_COLUMN = T2.PRIMARY_KEY_COLUMN
AND
T1.PRIMARY_KEY_COLUMN = T3.PRIMARY_KEY_COLUMN

告诉我你需要它作为sql查询或java

Just tell me you need it as sql query or java

这篇关于在准备好的语句中连接字符串的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！