各位大侠,小弟最近写了个公共的查询方法,sql语句是由前台传进来的,得到sql之后执行stmt.executeQuery(sql).但由于没有考虑使用绑定变量,现在想在执行sql之前再执行alter session set cursor_sharing=SIMILAR一下,但是尝试了一些方法没办法实现,我把代码帖出来,寻找高手指点:代码如下:
public static ResultBean query(String sql) {
if (sql == null) return new ResultBean(0,null, 0, 0);
int dataRows = 0;
int dataCols = 0;
Vector rows = new Vector();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = ConnectionFactory.getConnection(ApplicationConstants.LOCAL_DATASOURCE_JNDI_NAME);
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs != null) {
ResultSetMetaData rsmeta = rs.getMetaData();
dataCols = rsmeta.getColumnCount();
while (rs.next()) {
String[] row = new String[dataCols];
for (int i = 0; i < dataCols; i++) {
String s = (rs.getString(i + 1) == null ?
"" : rs.getString(i + 1).trim());
row[i] = s;
}
rows.addElement(row);
dataRows++;
}
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if(rs!=null)rs.close();
if(stmt!=null)stmt.close();
if(conn!=null)conn.close();
} catch (Exception exx) {
System.out.println("finally is error = "+exx);
}
} if (dataRows > 0) {
String[][] data = new String[dataRows][dataCols];
for (int i = 0; i < dataRows; i++) {
data[i] = (String[])rows.elementAt(i);
}
return new ResultBean(0,data, dataRows, dataCols);
} else
return new ResultBean(0,null, 0, 0);
}另外:
尝试了调用存过的形式.虽然可以执行,但貌似效率提高不是很大.(使用游标返回值)
也使用了申明两个Statement 共用1个连接,写第一个执行alter session set cursor_sharing=SIMILAR这句话第二个才执行传来的sql,但在数据库里执行select * from v$sqlarea where sql_text like'select a.phone_no from dcusthigh a where a.card_type in%'结果还是没有走绑定变量的形式```
在这里请高人指教啊!
public static ResultBean query(String sql) {
if (sql == null) return new ResultBean(0,null, 0, 0);
int dataRows = 0;
int dataCols = 0;
Vector rows = new Vector();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = ConnectionFactory.getConnection(ApplicationConstants.LOCAL_DATASOURCE_JNDI_NAME);
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs != null) {
ResultSetMetaData rsmeta = rs.getMetaData();
dataCols = rsmeta.getColumnCount();
while (rs.next()) {
String[] row = new String[dataCols];
for (int i = 0; i < dataCols; i++) {
String s = (rs.getString(i + 1) == null ?
"" : rs.getString(i + 1).trim());
row[i] = s;
}
rows.addElement(row);
dataRows++;
}
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if(rs!=null)rs.close();
if(stmt!=null)stmt.close();
if(conn!=null)conn.close();
} catch (Exception exx) {
System.out.println("finally is error = "+exx);
}
} if (dataRows > 0) {
String[][] data = new String[dataRows][dataCols];
for (int i = 0; i < dataRows; i++) {
data[i] = (String[])rows.elementAt(i);
}
return new ResultBean(0,data, dataRows, dataCols);
} else
return new ResultBean(0,null, 0, 0);
}另外:
尝试了调用存过的形式.虽然可以执行,但貌似效率提高不是很大.(使用游标返回值)
也使用了申明两个Statement 共用1个连接,写第一个执行alter session set cursor_sharing=SIMILAR这句话第二个才执行传来的sql,但在数据库里执行select * from v$sqlarea where sql_text like'select a.phone_no from dcusthigh a where a.card_type in%'结果还是没有走绑定变量的形式```
在这里请高人指教啊!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货