How to use a place holder for preparestatement to bind a variable for table name

Question

I have a problem with a prepareStatement. I am trying to build a query to select the count but table names are different and the code is like this:

String sql = "SELECT COUNT(0) AS CNT FROM ? WHERE STUD_NM <> 'ABC' ";

String tableName;

for (int i = 0; i < studCode.size(); i++) {

    count = 0;

    tableName = "";  

    pstmt = con.prepareStatement(sql);

    pstmt.setString( 1 , "S_"+studCode.get(i));

   Syso(pstmt); // 

}

Query is prepared like,

"SELECT COUNT(0) AS CNT FROM 'S_HUBRECORD' WHERE STUD_NM <> 'ABC'"

Why does it append single quotes to table name?

You may find this replay helpful over here: http://stackoverflow.com/a/11312274/891092 — ZakiMak, Jan 06 '15 at 07:05

score 1 · Answer 1 · answered Jan 06 '15 at 06:41

1

You can't. You need to construct your query with string concatenation. PreparedStatement is for field values, not for table name. The reason it's wrapping quotes around it is also because you used setString.

answered Jan 06 '15 at 06:41

David Xu

5,555
3
28
50

score 0 · Answer 2 · answered Jan 06 '15 at 06:42

0

They treat table name as a string you use type casting may be it work.

answered Jan 06 '15 at 06:42

Aniket

173
12

score 0 · Answer 3 · edited Jan 06 '15 at 06:49

0

You should do like this,

for (int i = 0; i < studCode.size(); i++) {

    String sql = "SELECT COUNT(0) AS CNT FROM "+"S_"+studCode.get(i)+" WHERE STUD_NM <> 'ABC' ";
    count = 0;

    pstmt = con.prepareStatement(sql);

    Syso(pstmt); // 

}

edited Jan 06 '15 at 06:49

AmolKumar

223
1
2
5

answered Jan 06 '15 at 06:44

Sridhar DD

1,972
1
10
17

it works. but can i prepare a query outside of for loop? – AmolKumar Jan 06 '15 at 06:52
The table name will change for each iteration means how will you move it out? – Sridhar DD Jan 06 '15 at 06:54
if i write code like, String sql = "SELECT COUNT(0) AS CNT FROM "+"S_"? WHERE STUD_NM <> 'ABC' "; for (int i = 0; i < studCode.size(); i++) { count = 0; pstmt = con.prepareStatement(sql); pstmt.setString(1,studCode.size()) Syso(pstmt); // } this prepare query correctly but the problem is that it append single quote to the table name. – AmolKumar Jan 06 '15 at 07:00
Thats what we are saying. You can't do that. because prepare statement sees ? like a column value so if it string it appends Single Quote. So if you surely want is out side then You can also use search and replace strategy. – Sridhar DD Jan 06 '15 at 07:06

How to use a place holder for preparestatement to bind a variable for table name

3 Answers3