java连接Oracle并实用存储过程实现增删改查(CRUD)

具体简单项目如下:

配置四大参数并导入驱动包:

   写一个Oracle.properties的配置文件,类容如下:

username=用户名

password=密码

driver=oracle.jdbc.driver.OracleDriver

url=jdbc\:oracle\:thin\:@localhost\:1521\:xe

编写工具类(DBUtil.java):

代码如下: 

public class DBUtil {
//四个参数
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;

//使用静态代码块的特性进行配置文件的读取，并复制给上面的静态变量
static {
//读取配置文件
Properties properties=new Properties();
try {
properties.load(DBUtil.class.getClassLoader().getResourceAsStream("Oracle.properties"));
driver =properties.getProperty("driver");
url =properties.getProperty("url");
username =properties.getProperty("username");
password =properties.getProperty("password");
//加载驱动类
Class.forName(driver);

} catch (Exception e) {
throw new RuntimeException(e);
}
}

//获得连接
public static Connection getConnection(){
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}

//关闭连接
public static void close(Connection connection,PreparedStatement pstamt,ResultSet rs){
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}

if (pstamt!=null) {
try {
pstamt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}

if (connection!=null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}

}

写一个实体类(Exp_user):

如下参数:

private int id;
private String username;
private String password;

有参构造和无参构造;getxxx()/setxxx()方法;重写了tostring方法

具体代码如下:

public class User_oracle {


//增
public void insert_exp_user(String username,String password){
Connection con=DBUtil.getConnection();
CallableStatement cs=null;
ResultSet rs=null;
try {
String sql="{call insert_exp_user(?,?)}";
cs=con.prepareCall(sql);
cs.setString(1, username);
cs.setString(2, password);
cs.execute();
} catch (Exception e) {
throw new RuntimeException(e);
}finally{
DBUtil.close(con, cs, rs);
}
}
//改
public void update_exp_user(String username,String password){
Connection con=DBUtil.getConnection();
CallableStatement cs=null;
try {
String sql="{call update_exp_user(?,?)}";
cs=con.prepareCall(sql);
cs.setString(1, username);
cs.setString(2, password);
cs.execute();
} catch (Exception e) {
throw new RuntimeException(e);
}finally{
DBUtil.close(con, cs);
}
}
//删
public void delete_exp_user(String username){
Connection con=DBUtil.getConnection();
CallableStatement cs=null;
ResultSet rs=null;
try {
String sql="{call delete_exp_user(?)}";
cs=con.prepareCall(sql);
cs.setString(1, username);
cs.execute();
} catch (Exception e) {
throw new RuntimeException(e);
}finally{
DBUtil.close(con, cs, rs);
}
}

//查询单个数据
public Exp_user select_one_exp_user(int id){
Connection con=DBUtil.getConnection();
CallableStatement cs=null;
ResultSet rs=null;
Exp_user exp_user=null;
try {
String sql="{call select_one_exp_user(?,?,?)}";
cs=con.prepareCall(sql);
cs.setInt(1, id);
cs.registerOutParameter(2, Types.VARCHAR);
cs.registerOutParameter(3, Types.VARCHAR);
cs.execute();
exp_user=new Exp_user(id, cs.getString(2), cs.getString(3));
} catch (Exception e) {
throw new RuntimeException(e);
}finally{
DBUtil.close(con, cs, rs);
}
return exp_user;
}

//查询所有
public ArrayList<Exp_user> selec_all_exp_user(){
ArrayList<Exp_user> exp_users =new ArrayList<>();
Connection con=DBUtil.getConnection();
CallableStatement cs=null;
ResultSet rs=null;
try {
cs = con.prepareCall("{call mypack.myproc(?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
rs = (ResultSet)cs.getObject(1);
while(rs!=null&& rs.next()){
exp_users.add(new Exp_user(rs.getInt("id"), rs.getString("username"), rs.getString("password")));
}
} catch (Exception e) {
throw new RuntimeException(e);
}finally{
DBUtil.close(con, cs, rs);
}
return exp_users;
}

public static void main(String[] args) {
User_oracle u=new User_oracle();
//u.insert_exp_user("jack", "123");
//u.insert_exp_user("lucy", "123");
//u.insert_exp_user("alex", "123");

//u.update_exp_user("tom", "111");
//u.delete_exp_user("tom");

//System.out.println(u.select_one_exp_user(3).toString());

System.out.println(u.selec_all_exp_user().toString());
}
}


/*
创建序列
create sequence seq_exp_user


创建表:
create table exp_user(id number,username varchar2(50),password varchar2(50))


增加存储过程:
reate or replace procedure update_exp_user(
  param1 varchar2,param2 varchar2
)as 
begin
  insert into exp_user values(seq_exp_user.nextval,param1,param1);
  commit;
end;

更新存储过程:
reate or replace procedure update_exp_user(
  param1 varchar2,param2 varchar2
)as 
begin
  update exp_user set password = param2 where username  = param1;
  commit;
end;


删除存储过程:
create or replace procedure delete_exp_user(
  param varchar2
)as 
begin
  delete from exp_user where username=param;
  commit;
end;

查询一条存储过程:
create or replace procedure select_one_exp_user(
  param1 in number,param2 out varchar2,param3 out varchar2
)as begin
  select username into param2 from exp_user where id = param1;
  select password into param3 from exp_user where id = param1;
  //--select username,password into param2,param3 from exp_user where id = param1;
end;

查询多条(1):这里建了一个包，其中有两个元素：mycursor游标和myproc存储过程.
CREATE OR REPLACE PACKAGE mypack IS
     TYPE mycursor IS REF CURSOR;
PROCEDURE myproc(outcursor IN OUT mycursor);
END mypack;
查询多条(2):这里详细定义了mycursor和myproc的body。注意：CREATE PACKAGE和CREATE PACKAGE BODY不能一起执行，必须先后执行，否则会报错.
CREATE OR REPLACE PACKAGE BODY mypack IS
PROCEDURE myproc(
        outcursor IN OUT mycursor
    )
IS
    BEGIN
    OPEN outcursor FOR
    SELECT * FROM Exp_user;
    RETURN;
  END myproc;
END;


*/