Ways of concatenating string in a prepared statement

Question

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)

Good. Can you provide three columns from three tables. Please provide with three table names with their column names — Jim Macaulay, Sep 11 '16 at 12:56
The exceptions you get have nothing to do with the concatenation. Post the actual code causing the exception, and its complete stack trace. This looks like terrible database design, BTW. — JB Nizet, Sep 11 '16 at 12:56
I already edited the post, I have no idea why im getting Null pointer exception on retrieving the data. but I can store data on the tables. — ZZZZZZZZZ, Sep 11 '16 at 13:04
Well, either employeeCode or employeeSales is null. Read http://stackoverflow.com/questions/218384/what-is-a-nullpointerexception-and-how-do-i-fix-it — JB Nizet, Sep 11 '16 at 13:08
but employeeCode and employeeSales are not null and is working fine on SqlInsert() and InsertRecord() as I can store it on the database.. — ZZZZZZZZZ, Sep 11 '16 at 13:10
Trace your program flow to see if the `sqlRet` method can possibly be called before `employeeCode` and `employeeSales` have been instantiated. — Gord Thompson, Sep 11 '16 at 13:25

score 0 · Answer 1 · answered Sep 11 '16 at 13:03

0

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

answered Sep 11 '16 at 13:03

Jim Macaulay

4,709
4
28
53

I don't think this is asked... these 3 tables seem in the question seem to be a poor attempt at sharding, not logially connected tables, so that you could have a join query on them. – Robert Sep 11 '16 at 13:47
Yeah got it. Will help you – Jim Macaulay Sep 11 '16 at 13:50

Ways of concatenating string in a prepared statement

1 Answers1

Linked

Related