当前位置：首页 > news >正文

商城网站建设net2006优化模型

news 2026/4/11 11:44:00

商城网站建设net2006,优化模型,vue做的pc线上网站,定制东莞网站制作公司场景需求需要根据不同的列进行对应的排序操作，实现动态列名排序类似🐟动态查询或更新但是JDBC预编译模式下占位符的排序字段失效 SQL语句分页查询 select * from (select t.*, rownum rn from(select * from emp order by empno desc) t where …

场景需求

需要根据不同的列进行对应的排序操作，实现动态列名排序类似🐟动态查询或更新

但是JDBC预编译模式下占位符的排序字段失效

SQL语句

分页查询

select * from (select t.*, rownum rn from(select * from emp order by empno desc) t where rownum <= 5)    where rn > 0;

指定列排序失效

select * from (select t.*, rownum rn from(select * from emp order by ?  desc) t where rownum <= 5)    where rn > 0;

临时解决字符串拼接

SQL注入问题

select * from (select t.*, rownum rn from(select * from emp order by "+ empno  +  "  desc) t where rownum <= 5)    where rn > 0;

预编译注入排序列名排序失效

PreparedStatement执行SQL时，如果order by之后的排序字段使用占位符，通过setString设置值的话，会导致排序失效

// 4 SQL 语句被预编译并存储在 PreparedStatement 对象中。然后可以使用此对象多次高效地执行该语句。
PreparedStatement pstmt = conn.prepareStatement(sql);// 如果SQL有？号  参数需要注入
pstmt.setString(1, sortColumnName); // 1 代表 第一个？  从1开始   以此类推

源码

/*** An object that represents a precompiled SQL statement.* <P>A SQL statement is precompiled and stored in a* {@code PreparedStatement} object. This object can then be used to* efficiently execute this statement multiple times.** <P><B>Note:</B> The setter methods ({@code setShort}, {@code setString},* and so on) for setting IN parameter values* must specify types that are compatible with the defined SQL type of* the input parameter. For instance, if the IN parameter has SQL type* {@code INTEGER}, then the method {@code setInt} should be used.** <p>If arbitrary parameter type conversions are required, the method* {@code setObject} should be used with a target SQL type.* <P>* In the following example of setting a parameter, {@code con} represents* an active connection:* <pre>{@code*   BigDecimal sal = new BigDecimal("153833.00");*   PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES*                                     SET SALARY = ? WHERE ID = ?");*   pstmt.setBigDecimal(1, sal);*   pstmt.setInt(2, 110592);* }</pre>** @see Connection#prepareStatement* @see ResultSet* @since 1.1*/
public interface PreparedStatement extends Statement {/*** Sets the designated parameter to the given Java {@code String} value.* The driver converts this* to an SQL {@code VARCHAR} or {@code LONGVARCHAR} value* (depending on the argument's* size relative to the driver's limits on {@code VARCHAR} values)* when it sends it to the database.** @param parameterIndex the first parameter is 1, the second is 2, ...* @param x the parameter value* @throws SQLException if parameterIndex does not correspond to a parameter* marker in the SQL statement; if a database access error occurs or* this method is called on a closed {@code PreparedStatement}*/void setString(int parameterIndex, String x) throws SQLException;... 
}