String selectSQL = "select top ? * from (select top ? song.singer_id,song.song_id,song.song_name,song.title_url,song.mv_url,singer.singer_name from webdb_prod_song as song,webdb_prod_singer as singer where song.singer_id=singer.singer_id) as music where music.singer_id not in (select top ? song.singer_id from webdb_prod_song as song,webdb_prod_singer as singer where song.singer_id=singer.singer_id)";
Object[] args = { maxRows, maxRows*pageNo, maxRows*(pageNo - 1) };
List<Musics> listMusics = new ArrayList<Musics>();
SqlRowSet rs = musicJdbcTemplate.queryForRowSet(selectSQL,args);
// SqlRowSet rs = musicJdbcTemplate.queryForRowSet(selectSQL);
while (rs.next()) {
如果我不用?占位,则没有任何问题,求解
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select top ? * from (select top ? song.singer_id,song.song_id,song.song_name,song.title_url,song.mv_url,singer.singer_name from webdb_prod_song as song,webdb_prod_singer as singer where song.singer_id=singer.singer_id) as music where music.singer_id not in (select top ? song.singer_id from webdb_prod_song as song,webdb_prod_singer as singer where song.singer_id=singer.singer_id)]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: '@P0' 附近有语法错误。
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:583)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:501)
javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:96)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
------解决方案--------------------
从没看到这样写的,?站位符不是要放到条件中的吗?
------解决方案--------------------
错误显示你的sql语法错误。
在你用?占位的时候sql应该会变成 select top @P0 * from ....
而并非你想的是sql拼接。用你的数字去替换。数字替换是在试行过程当中。
就正如我们写一个select ? * from tab 用 tab.a去代替问号。但是出来的结果并非tab.a列的值,而只是"tab.a"