java访问Oracle数据库

JDBCUtils.java类中

package utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtils {

    private static String driver = "oracle.jdbc.OracleDriver";
    private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static String user = "c##zs";
    private static String password = "111";
    
    static{
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

  
    public static void release(Connection conn,Statement st,ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                rs = null;   //----> Java GC
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                st = null;
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                conn = null;
            }
        }
    }
}

studentDao类

package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import utils.JDBCUtils;

public class StudentDAO {
	
	/**
     * 查询学生
     */
    public void selectStudent() {
        Connection conn= JDBCUtils.getConnection();
        String sql = "select * from student";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String student_id = rs.getString("student_id");
                String sname = rs.getString("sname");
                String ssex = rs.getString("ssex");
                String sbirth = rs.getString("sbirth");
                System.out.println(student_id+"\t"+sname+"\t"+ssex+"\t"+sbirth);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(conn, pstmt, rs);;
        }
    }

    
    /*存储过程：根据学号查询姓名
    create or replace procedure p_selectsname(p_sid in char, p_sname out varchar2)
	is
	begin
	  select sname into p_sname from student where student_id=p_sid;
	end p_selectsname; 
    */
    public void testProcedure(){
    	Connection conn= JDBCUtils.getConnection();
        PreparedStatement pstmt = null;
    	CallableStatement proc = null;
        try {
			proc = conn.prepareCall("{ call p_selectsname(?,?) }"); //调用存储过程
			proc.setString(1, "1801001");//设置第一个参数输入参数
			proc.registerOutParameter(2, Types.VARCHAR);//第二个参数输出参数,是VARCHAR类型的
			proc.execute();//执行
			String sname = proc.getString(2);//获得输出参数
			System.out.println("学生姓名="+sname);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            JDBCUtils.release(conn, pstmt, null);
        }
    }
    
    /*函数：根据课程名，查询选修这门课程的人数
    CREATE or REPLACE FUNCTION COUNT_STU(F_CNAME IN VARCHAR2) 
	RETURN NUMBER
	AS
	rel NUMBER := 1;
	BEGIN
	  SELECT COUNT(STUDENT_ID) INTO rel FROM SC, COURSE C
	  WHERE sc.COURSE_ID = C.COURSE_ID AND C.CNAME = F_CNAME;
	  RETURN rel;
	END COUNT_STU;
     */
    public void testFunction(){
  
        Connection conn = null;
        CallableStatement call = null;
        try {
            conn = JDBCUtils.getConnection();
            call = conn.prepareCall("{?=call COUNT_STU(?)}");  //调用函数     
             //对于out参数，声明类型
            call.registerOutParameter(1, Types.INTEGER);         
            //对于in参数，赋值
            call.setString(2, "Java");          
            //执行
            call.execute();           
            //取出结果
            int r = call.getInt(1);
            System.out.println("学习人数="+r);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	JDBCUtils.release(conn, null, null);
        }        
    }
    
}

测试类test.java

package dao;

/*
 * 测试访问数据库方法
 */
public class Test {

	public static void main(String[] args) {
		StudentDAO sdao = new StudentDAO();
		
		//查询student表
		//sdao.selectStudent();
        
		//调用存储过程
		sdao.testProcedure();
		
		//调用函数
		//sdao.testFunction();
	}

}

Oracle 中存储过程：根据学号查询姓名

create or replace procedure p_selectsname(p_sid in char, p_sname out varchar2)
	is
	begin
	  select sname into p_sname from student where student_id=p_sid;
	end p_selectsname; 

Oracle中函数：根据课程名，查询选修这门课程的人数

CREATE or REPLACE FUNCTION COUNT_STU(F_CNAME IN VARCHAR2) 
	RETURN NUMBER
	AS
	rel NUMBER := 1;
	BEGIN
	  SELECT COUNT(STUDENT_ID) INTO rel FROM SC, COURSE C
	  WHERE sc.COURSE_ID = C.COURSE_ID AND C.CNAME = F_CNAME;
	  RETURN rel;
	END COUNT_STU;