Java 调用Oracle存储过程
创建存储过程
create or replace procedure query_user drop procedure query_user;
## 没有返回值的存储过程 --删除过程
--删除过程
--创建存储过程 --删除包 --删除过程 |
--------------------------------------------------------------------------------------------------------------------------------------------------
调用存储过程案例代码
public class User {
private int id;
private String username;
private String password;
private String email;
private int age;
private String description;
// setter and getter ......
}
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
public class CallProcedure {
public static void queryUser(Connection conn) {
String sql = "{ call query_user(?) }";
CallableStatement cstmt = null;
try {
conn.setAutoCommit(false);
cstmt = conn.prepareCall(sql);
cstmt.setString(1, "");
cstmt.execute();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (cstmt != null) {
try {
cstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
cstmt = null;
}
}
}
public static void insertUser(Connection conn,User user) {
String sql = "{ call insert_user(?,?,?,?) }";
CallableStatement cstmt = null;
try {
conn.setAutoCommit(false);
cstmt = conn.prepareCall(sql);
cstmt.setString(1, user.getUsername());
cstmt.setString(2, user.getEmail());
cstmt.setString(3, user.getPassword());
cstmt.setInt(4, user.getAge());
cstmt.execute();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (cstmt != null) {
try {
cstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
cstmt = null;
}
}
}
public static int countUser(Connection conn) {
int count = 0;
String sql = "{ call user_count(?,?) }";
CallableStatement cstmt = null;
try {
conn.setAutoCommit(false);
cstmt = conn.prepareCall(sql);
cstmt.setString(1, "Liu");
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.registerOutParameter(2, Types.INTEGER);
cstmt.execute();
System.out.println(cstmt.getString(1));
count = cstmt.getInt(2);
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (cstmt != null) {
try {
cstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
cstmt = null;
}
}
return count;
}
public static List<User> queryUsers(Connection conn) {
List<User> users = new ArrayList<User>();
String sql = "{ call query_users(?,?) }";
CallableStatement cstmt = null;
ResultSet rs = null;
try {
conn.setAutoCommit(false);
cstmt = conn.prepareCall(sql);
cstmt.setString(1, "");
cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(2);
User user = null;
while (rs.next()) {
user = new User();
user.setId(rs.getInt(1));
user.setUsername(rs.getString(2));
user.setEmail(rs.getString(3));
user.setPassword(rs.getString(4));
user.setDescription(rs.getString(5));
user.setAge(rs.getInt(6));
users.add(user);
}
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (cstmt != null) {
try {
cstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
cstmt = null;
}
}
return users;
}
} |