首先对于jdbc-url的配置:
mysql数据库:
jdbc:mysql://localhost/test?useServerPrepStmts=true&cachePrepStmts=true
oracle数据库
格式一: Oracle JDBC Thin using an SID:
jdbc:oracle:thin:@host:port:SID
Example: jdbc:oracle:thin:@localhost:1521:orcl
格式二: Oracle JDBC Thin using a ServiceName:
jdbc:oracle:thin:@//host:port/service_name
Example:jdbc:oracle:thin:@//localhost:1521/service_name
下面的参数公用:
useServerPrepStmts:是否使用服务端预编译
cachePrepStmts:是否开启缓冲,这个地方不开启缓冲的话代码里预编译的sql语句不会同步mysql服务端
prepStmtCacheSize:这个是服务端预编译sql语句的大小设置,默认是25,可以设置,超过会报错 ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements (current value: 1)
可以使用mysql客户端语句来验证上面的说明
prepare ins from “xxx” 预编译一条sql
set @a=999,@b='hello'; 设置值
execute ins using @a,@b; 执行语句
deallocate prepare ins; 删除一条预编译sql
获取到链接之后 PreParedStatement:预编译使用的编译器,继承了Statement,使用预编译模式还可以防止sql攻击
代码详情(我这里暂时不需要使用预编译):
Class.forName("com.mysql.jdbc.Driver");
Connection test_ora_so = DriverManager.getConnection("jdbc:mysql://136.192.50.109:60004/ORD_SO", "test_ora_so", "test@13579%!!so");
PreparedStatement preparedStatement = test_ora_so.prepareStatement(sql);
for(int a=1;a
preparedStatement.setString(a, params[a - 1]);
}
ResultSet resultSet = preparedStatement.executeQuery();
StringBuffer s = new StringBuffer();
while (resultSet.next()){
//获取此条结果的所有字段信息
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
for(int i=1;i
//获取字段名
String columnName = metaData.getColumnName(i);
switch (columnName) {
case "ORDER_ITEM_ID" :
s.append(resultSet.getString("ORDER_ITEM_ID")).append(",");
s.substring(0, s.length() - 1);
break;
case "PROVISALE" :
s.append(resultSet.getString("PROVISALE"));
break;
case "CUST_ORDER_ID":
s.append(resultSet.getString("CUST_ORDER_ID"));
break;
}
}
//s.append(orderItemId + ",");
}
return s.toString().endsWith(",") ? s.substring(0, s.length() - 1) : s.toString();