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

绝地灬酷狼 2022-08-09 06:28 141阅读 0赞

**具体简单项目如下:**

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

写一个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;  
  
  
\*/