CallableStatement简单使用(二)
getConnection(
config.getString("jdbc.connection.url"),
config.getString("jdbc.connection.username"),
config.getString("jdbc.connection.password"));
String sql = "{call proc_getUserCountCondit( , )}";
// 创建CallableStatement对象
cstmt = conn.prepareCall(sql);
// 输入参数赋值
cstmt.setString(1, username);
// 注册输出参数
cstmt.registerOutParameter(2, Types.INTEGER);
// 执行存储过程
cstmt.execute();
rowsCount = cstmt.getInt(2);
} catch (SQLException e) {
e.printStackTrace();
}
return rowsCount;
}
// 执行返回值为游标的存储过程执行返回值为游标的存储过程
public List
getUserListByProc() {
List
userList = null; try { Class.forName(config.getString("jdbc.driver_class")); } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection conn = null; CallableStatement cstmt = null; ResultSet rs = null; try { conn = DriverManager.getConnection( config.getString("jdbc.connection.url"), config.getString("jdbc.connection.username"), config.getString("jdbc.connection.password")); String sql = "{call PROC_GETUSER( )}"; // 创建CallableStatement对象 cstmt = conn.prepareCall(sql); // 注册输出参数 cstmt.registerOutParameter(1, OracleTypes.CURSOR); // 执行存储过程 cstmt.execute(); rs = (ResultSet) cstmt.getObject(1); userList = new ArrayList
(); while (rs.next()) { String username = rs.getString("VU_USER_NAME"); String password = rs.getString("VU_PASSWORD"); User user = new User(); user.setUsername(username); user.setPassword(password); userList.add(user);// 添加用户 } } catch (SQLException e) { e.printStackTrace(); } return userList; } // 函数 public String getUserNameById(String userId) { String username = ""; try { Class.forName(config.getString("jdbc.driver_class")); } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection conn = null; CallableStatement cstmt = null; try { conn = DriverManager.getConnection( config.getString("jdbc.connection.url"), config.getString("jdbc.connection.username"), config.getString("jdbc.connection.password")); String sql = "{ = call FUNC_GETUSERNAME( )}"; // 创建CallableStatement对象 cstmt = conn.prepareCall(sql); // 输入参数赋值 cstmt.setString(2, userId); // 注册输出参数 cstmt.registerOutParameter(1, Types.VARCHAR); // 执行存储过程 cstmt.execute(); username = cstmt.getString(1); } catch (SQLException e) { e.printStackTrace(); } return username; } public static void main(String[] args) { Demo2 d = new Demo2(); System.out.println("用户人数：" + d.getUserCount()); System.out.println("模糊查询-------用户人数：" + d.getUserCountByName("accp")); List
userList = d.getUserListByProc(); for (User user : userList) { System.out.println("用户名：" + user.getUsername() + "，密码：" + user.getPassword()); } System.out.println(d.getUserNameById("accp")); } }