Ways of concatenating string in a prepared stateme

问题:

This question already has an answer here:

• What is a NullPointerException, and how do I fix it? 12 answers

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.

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);

I tried different ways like "select * from MachineProblem2"+employeeCode but 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"

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

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;
}

//Error//

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)

回答1:

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

Just tell me you need it as sql query or java