具体简单项目如下:
配置四大参数并导入驱动包:
写一个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;
*/