JAVA访问Oracle11g

(1)Oracle自带REF动态游标（用于返回）

SYS_REFCURSOR

(2)自定义REF动态游标（用于返回）

CREATE OR REPLACE PACKAGE ref1 IS

    TYPE ref_cur_emp IS REF CURSOR;

END;

 

自定义使用方法：ref1.ref_cur_emp;

1. 创建过程（查询表所有的值）

CREATE OR REPLACE PROCEDURE p_getAll(tball out SYS_REFCURSOR)

AS

BEGIN

  OPEN tball FOR SELECT empno,ename,job,NVL(mgr,0) mgr,hiredate,sal,NVL(comm,0) comm,deptno FROM emp;

END;

2. java添加jar，写一个数据库帮助类，建立连接，附件:jar

public static final String CLASSNAME = "oracle.jdbc.driver.OracleDriver";

public static final String URL = "jdbc:oracle:thin://127.0.0.1:1521:ORCL";

public static final String USER = "scott";

public static final String PASSWORD = "ysys200206";

    

    //加载驱动

    static{

    try {

Class.forName(CLASSNAME);

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

    }

    

    //获得连接

    public static Connection getCon(){

    Connection con = null;

    try {

con = DriverManager.getConnection(URL, USER, PASSWORD);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

    return con;

    }

    

    //关闭资源

    public static void close(Connection con,PreparedStatement ps, ResultSet rs){

    try {

if(con!=null && !con.isClosed()){

con.close();

}

if(ps!=null){

ps.close();

}

if(rs!=null){

rs.close();

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

    }

    

    public static void close(Connection con,PreparedStatement ps){

    close(con, ps, null);

    }

    

    public static void close(Connection con){

    close(con, null);

}

3. 写一个实体类并封装

private Integer empno;

private String ename;

private String job;

private Integer mgr;

private String hiredate;

private double sal;

private double comm;

private Integer deptno;

/**

 *

 */

public Emp() {

super();

// TODO Auto-generated constructor stub

}

 

/**

 * @param empno

 * @param ename

 * @param job

 * @param mgr

 * @param hiredate

 * @param sal

 * @param comm

 * @param deptno

 */

public Emp(Integer empno, String ename, String job, Integer mgr,

String hiredate, double sal, double comm, Integer deptno) {

super();

this.empno = empno;

this.ename = ename;

this.job = job;

this.mgr = mgr;

this.hiredate = hiredate;

this.sal = sal;

this.comm = comm;

this.deptno = deptno;

}

 

/**

 * @return the empno

 */

public Integer getEmpno() {

return empno;

}

 

/**

 * @param empno the empno to set

 */

public void setEmpno(Integer empno) {

this.empno = empno;

}

 

/**

 * @return the ename

 */

public String getEname() {

return ename;

}

 

/**

 * @param ename the ename to set

 */

public void setEname(String ename) {

this.ename = ename;

}

 

/**

 * @return the job

 */

public String getJob() {

return job;

}

 

/**

 * @param job the job to set

 */

public void setJob(String job) {

this.job = job;

}

 

/**

 * @return the mgr

 */

public Integer getMgr() {

return mgr;

}

 

/**

 * @param mgr the mgr to set

 */

public void setMgr(Integer mgr) {

this.mgr = mgr;

}

 

/**

 * @return the hiredate

 */

public String getHiredate() {

return hiredate;

}

 

/**

 * @param hiredate the hiredate to set

 */

public void setHiredate(String hiredate) {

this.hiredate = hiredate;

}

 

/**

 * @return the sal

 */

public double getSal() {

return sal;

}

 

/**

 * @param sal the sal to set

 */

public void setSal(double sal) {

this.sal = sal;

}

 

/**

 * @return the comm

 */

public double getComm() {

return comm;

}

 

/**

 * @param comm the comm to set

 */

public void setComm(double comm) {

this.comm = comm;

}

 

/**

 * @return the deptno

 */

public Integer getDeptno() {

return deptno;

}

 

/**

 * @param deptno the deptno to set

 */

public void setDeptno(Integer deptno) {

this.deptno = deptno;

}

 

/* (non-Javadoc)

 * @see java.lang.Object#toString()

 */

@Override

public String toString() {

return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job

+ ", mgr=" + mgr + ", hiredate=" + hiredate + ", sal=" + sal

+ ", comm=" + comm + ", deptno=" + deptno + "]";

}

4. 写一个对数据进行CRUD的类

Connection con=null;

PreparedStatement ps=null;

CallableStatement cs=null;

ResultSet rs=null;

List<Emp> list=null;

//查询

public List<Emp> getTableAll(){

list=new ArrayList<Emp>();

try {

con=DBHleper.getCon();

cs=con.prepareCall("{call p_getAll(?)}");

cs.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

cs.execute();

rs=(ResultSet)cs.getObject(1);

while(rs.next()){

Emp e=new Emp(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getDouble(6),rs.getDouble(7),rs.getInt(8));

list.add(e);

}

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

} finally {

DBHleper.close(con, ps, rs);

}

return list;

}

5. 写一个测试类，进行数据测试

public static void main(String[] args) {

List<Emp> list=new EmpDao().getTableAll();

for (Emp e : list) {

System.out.println(e);

}

}

 

结果如下：

Emp [empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=1980-12-17 00:00:00.0, sal=800.0, comm=0.0, deptno=20]

Emp [empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=1981-02-20 00:00:00.0, sal=1600.0, comm=300.0, deptno=30]

Emp [empno=7521, ename=WARD, job=SALESMAN, mgr=7698, hiredate=1981-02-22 00:00:00.0, sal=1250.0, comm=500.0, deptno=30]

Emp [empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=1981-04-02 00:00:00.0, sal=2975.0, comm=0.0, deptno=20]

Emp [empno=7654, ename=MARTIN, job=SALESMAN, mgr=7698, hiredate=1981-09-28 00:00:00.0, sal=1250.0, comm=1400.0, deptno=30]

Emp [empno=7698, ename=BLAKE, job=MANAGER, mgr=7839, hiredate=1981-05-01 00:00:00.0, sal=2850.0, comm=0.0, deptno=30]

Emp [empno=7782, ename=CLARK, job=MANAGER, mgr=7839, hiredate=1981-06-09 00:00:00.0, sal=2450.0, comm=0.0, deptno=10]

Emp [empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=1987-04-19 00:00:00.0, sal=3000.0, comm=0.0, deptno=20]

Emp [empno=7839, ename=KING, job=PRESIDENT, mgr=0, hiredate=1981-11-17 00:00:00.0, sal=5000.0, comm=0.0, deptno=10]

Emp [empno=7844, ename=TURNER, job=SALESMAN, mgr=7698, hiredate=1981-09-08 00:00:00.0, sal=1500.0, comm=0.0, deptno=30]

Emp [empno=7876, ename=ADAMS, job=CLERK, mgr=7788, hiredate=1987-05-23 00:00:00.0, sal=1100.0, comm=0.0, deptno=20]

Emp [empno=7900, ename=JAMES, job=CLERK, mgr=7698, hiredate=1981-12-03 00:00:00.0, sal=950.0, comm=0.0, deptno=30]

Emp [empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=1981-12-03 00:00:00.0, sal=3000.0, comm=0.0, deptno=20]

Emp [empno=7934, ename=MILLER, job=CLERK, mgr=7782, hiredate=1982-01-23 00:00:00.0, sal=1300.0, comm=0.0, deptno=10]