Java连接Oracle（高级篇）

在项目工程目录下新建一个config文件夹，在config文件夹里创建一个database.properties文件，配置相关Oracle数据库的driver、url、username、password。

#Oracle
jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:moonlight
jdbc.username=scott
jdbc.password=tiger

 

在DBUtil.java中封装数据库常用操作。

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {
	public static Connection getConnection() {
		Connection conn = null;
		Properties props = null;
		FileInputStream in = null;
		
		try {
			props = new Properties();
			in = new FileInputStream("config/database.properties");
			props.load(in);
			in.close();

			String jdbcdriver = props.getProperty("jdbc.driver");
			if (jdbcdriver != null) {
				System.setProperty("jdbc.driver", jdbcdriver);
			}

			String url = props.getProperty("jdbc.url");
			String username = props.getProperty("jdbc.username");
			String password = props.getProperty("jdbc.password");

			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return conn;
	}

	public static void close(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void close(PreparedStatement pstmt) {
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void close(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

 

最后，在JDBCDemo.java中测试一下是否配置连接成功。

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCDemo {
	public static void main(String[] args) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String keyword = "SMI";

		String sql = "SELECT empno, ename, job FROM emp WHERE ename LIKE ?";

		conn = DBUtil.getConnection();
		pstmt = conn.prepareStatement(sql);

		pstmt.setString(1, "%" + keyword + "%");

		rs = pstmt.executeQuery();
		while (rs.next()) {
			int empno = rs.getInt("empno");
			String ename = rs.getString("ename");
			String job = rs.getString("job");

			System.out.println("empno: " + empno + ", ename: " + ename
					+ ", job: " + job);
		}

		DBUtil.close(rs);
		DBUtil.close(pstmt);
		DBUtil.close(conn);
	}
}

 

使用JDBC调用Oracle存储过程

CallableStatement对象用于调用数据库服务器端的存储过程（Procedure），由java.sql.CallableStatement接口类型表示，该接口继承了java.sql.PreparedStatement接口。

存储过程是由SQL语句（以及数据库相关的增强指令，如Oracle中的PL/SQL语句）和流控制语句书写的过程程序，存储过程经过数据库编译和优化后存储在数据库服务器中，充分利用存储过程来完成应用系统的逻辑操作处理可以提高系统的运行性能和可维护性，但却会影响数据库的可移植性。

CallableStatement接口的用法与PreparedStatement类似

SQL源文件

drop procedure MyProcedure;

drop table person;

create table person
(
    id number primary key,
    name char(20),
    age number
);

create or replace procedure MyProcedure
(
    v_id in person.id%TYPE,
    v_name in person.name%TYPE,
    v_age in person.age%TYPE
)
as
begin
    insert into person values(v_id, v_name, v_age);
end;
/

  

Java源文件

package org.fool.jdbc;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class CallStatementTest
{
	public static void main(String[] args)
	{
		Connection conn = null;
		CallableStatement cstmt = null;

		conn = DBUtil.getConnection();

		try
		{
			cstmt = conn.prepareCall("{call MyProcedure(?, ?, ?)}");
			cstmt.setInt(1, 1);
			cstmt.setString(2, "Oracle");
			cstmt.setInt(3, 30);

			cstmt.execute();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		DBUtil.close(cstmt);
		DBUtil.close(conn);
	}

	public static class DBUtil
	{
		public static Connection getConnection()
		{
			Connection conn = null;
			Properties props = null;
			FileInputStream in = null;

			try
			{
				props = new Properties();
				in = new FileInputStream("config/database.properties");
				props.load(in);
				in.close();

				String jdbcdriver = props.getProperty("jdbc.driver");
				if (jdbcdriver != null)
				{
					Class.forName(jdbcdriver);
				}

				String url = props.getProperty("jdbc.url");
				String username = props.getProperty("jdbc.username");
				String password = props.getProperty("jdbc.password");

				conn = DriverManager.getConnection(url, username, password);
			}
			catch (FileNotFoundException | ClassNotFoundException
					| SQLException e)
			{
				e.printStackTrace();
			}
			catch (IOException e)
			{
				e.printStackTrace();
			}

			return conn;
		}

		public static void close(Connection conn)
		{
			if (conn != null)
			{
				try
				{
					conn.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}

		public static void close(PreparedStatement pstmt)
		{
			if (pstmt != null)
			{
				try
				{
					pstmt.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}

		public static void close(CallableStatement cstmt)
		{
			if (cstmt != null)
			{
				try
				{
					cstmt.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}

		public static void close(ResultSet rs)
		{
			if (rs != null)
			{
				try
				{
					rs.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
	}
}