Java程序调用Oracle

1. java程序调用存储过程

public void procedureTest(){
		String sql = "{ call pro_test111(?,?,?)}";
		CallableStatement call = null;
		try {
			call = conn.prepareCall(sql);
			//IN需要set  OUT需要reister
			call.setString(1, "jdbc");
			call.setInt(2, 2);
			call.setString(3, "default");
			call.registerOutParameter(3, Types.VARCHAR);
			call.execute();
			//拿到返回值
			Object object = call.getObject(3);
			System.out.println(object);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//关闭连接
			DBConfig.close(call, conn);
		}
	}

2. DBUtils工具调用Oracle

2.1 首先引入oracle6.jar commons-dbutils-1.4.jar c3p0-0.9.1.2.jar包

2.2 在新建db.properties

poolName=c3p0
className=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
username=chalco1129
password=chalco1129
initialPoolSize=10
minPoolSize=20
maxPoolSize=50
maxIdleTime=30

2.3 新建数据库连接配置信息工具类

package com.isoftstone.utils;
import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
 * 数据库连接配置信息
 * @author mjzhud
 *
 */
public class DBConfiguration {
	private static Properties pro;
	static {
		//加载属性文件，读取数据库连接配置信息
		pro = new Properties();
		try {
			pro.load(DBConfiguration.class.getResourceAsStream("/db.properties"));
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("未找到配置文件！！！");
		}
	}
	
	public static Connection getConnection() {
		if ("c3p0".equals(pro.getProperty("poolName"))) return getC3P0Connection();
		if ("dbcp".equals(pro.getProperty("poolName"))) return getC3P0Connection();
		return null;
	}
	
	
	
	private static Connection getC3P0Connection(){
		ComboPooledDataSource c3p0 = new ComboPooledDataSource();
		try {
			c3p0.setDriverClass(pro.getProperty("className"));
			c3p0.setJdbcUrl(pro.getProperty("url"));
			c3p0.setUser(pro.getProperty("username"));
			c3p0.setPassword(pro.getProperty("password"));
			c3p0.setInitialPoolSize(Integer.valueOf(pro.getProperty("initialPoolSize")));
			c3p0.setMinPoolSize(Integer.valueOf(pro.getProperty("minPoolSize")));
			c3p0.setMaxPoolSize(Integer.valueOf(pro.getProperty("maxPoolSize")));
			c3p0.setMaxIdleTime(Integer.valueOf(pro.getProperty("maxIdleTime")));
			return c3p0.getConnection();
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("连接失败，检查用户名和密码");
		}
		return null;
	}
	
	public static void close(Statement statement,Connection conn){
		try {
			if (statement != null) statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
                try {
			if (conn != null) conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void close(ResultSet rs,Statement statement,Connection conn){
		try {
			if (rs != null) rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		close(statement,conn);
	}
}

2.4 新建JdbcDBUtil工具类

package com.isoftstone.utils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Before;
import org.junit.Test;
import bios.report.core.chart.t;
import com.isoftstone.manager.bean.Student;
import com.isoftstone.utils.DBConfiguration;

public class JdbcDBUtil {

	private static Connection conn = DBConfiguration.getConnection();
	public static QueryRunner runner = new QueryRunner();
	
	
	public static int add(String sql) throws SQLException{
		int count = -1;
		try {
			conn.setAutoCommit(false);
			count = runner.update(conn , sql);
		} catch (Exception e) {
			conn.rollback();
		} finally {
			conn.commit();
		}
		if (count > 0) {
			System.out.println("一条新增成功");
			return 1;
		}
		System.out.println("一条新增失败");
		return -1;
	}
	
	public static int add(String sql,Object[] params) throws SQLException{
		int count = -1;
		try {
			conn.setAutoCommit(false);
			count = runner.update(conn , sql, params);
		} catch (Exception e) {
			conn.rollback();
		} finally {
			conn.commit();
		}
		if (count > 0) {
			System.out.println("一条新增成功");
			return 1;
		}
		System.out.println("一条新增失败");
		return -1;
	}
	public static int update(String sql) throws SQLException{
		try {
			conn.setAutoCommit(false);
			runner.update(conn , sql);
		} catch (Exception e) {
			conn.rollback();
			return -1;
		} finally {
			conn.commit();
		}
		System.out.println("一条更新成功");
		return 1;
	}
	
	public static int update(String sql,Object[] params) throws SQLException{
		try {
			conn.setAutoCommit(false);
			runner.update(conn , sql, params);
		} catch (Exception e) {
			conn.rollback();
			return -1;
		} finally {
			conn.commit();
		}
		System.out.println("一条更新成功");
		return 1;
	}
	public static int delete(String sql) throws SQLException{
		try {
			conn.setAutoCommit(false);
			runner.update(conn , sql);
		} catch (Exception e) {
			conn.rollback();
			return -1;
		} finally {
			conn.commit();
		}
		System.out.println("一条删除成功");
		return 1;
	}
	
	public static int delete(String sql,Object[] params) throws SQLException{
		try {
			conn.setAutoCommit(false);
			runner.update(conn , sql, params);
		} catch (Exception e) {
			conn.rollback();
			return -1;
		} finally {
			conn.commit();
		}
		System.out.println("一条删除成功");
		return 1;
	}
	
	/**
	 * DBUtils批处理，只能处理同一预处理sql语句
	 * @param sql
	 * @param paramArr
	 * @return
	 * @throws SQLException
	 */
	public static int dbUtilsBatch(String sql,Object[][] paramArr) throws SQLException{
		int[] count = null;
		try {
			conn.setAutoCommit(false);
			count = runner.batch(conn , sql, paramArr);
		} catch (Exception e) {
			conn.rollback();
			return -1;
		} finally {
			conn.commit();
		}
		int updateCount = 0;
		if (count != null && count.length > 0) {
			for (int i : count) {
				if (i > 0) updateCount++;
	 		}
		}
		System.out.println("影响的行数="+updateCount);
		return 1;
	}
	
	/**
	 * Statement可以批量处理各种sql
	 * @param sqls
	 * @return
	 * @throws SQLException
	 */
	public static int statementBatch(String[] sqls) throws SQLException{
		Statement statement = conn.createStatement();
		for (String sql : sqls) {
			statement.addBatch(sql);
		}
		int[] count = null;
		try {
			conn.setAutoCommit(false);
			count = statement.executeBatch();
		} catch (Exception e) {
			conn.rollback();
			return -1;
		} finally {
			conn.commit();
			DBConfiguration.close(statement, conn);
		}
		int updateCount = 0;
		if (count != null && count.length > 0) {
			for (int i : count) {
				if (i > 0) updateCount++;
	 		}
		}
		System.out.println("影响的行数="+updateCount);
		return 1;
	}
	
	public static List<Map<String, Object>> queryList(String sql) throws SQLException{
		return runner.query(conn, sql, new MapListHandler());
	}
	public static List<Map<String, Object>> queryList(String sql,Object[] params) throws SQLException{
		return runner.query(conn, sql, new MapListHandler(),params);
	}
}

2.5 新建Bean类

package com.isoftstone.manager.bean;

public class Student {
	private long id;
	private String name;
	private int gender;
	private int age;
	private String email;
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getGender() {
		return gender;
	}
	public void setGender(int gender) {
		this.gender = gender;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", gender=" + gender
				+ ", age=" + age + ", email=" + email + "]";
	}
}

2.6 新建Dao接口

package com.isoftstone.manager.dao;
import java.sql.SQLException;
import java.util.List;
import com.isoftstone.manager.bean.Student;

public interface StudentDao {
	public int add(Student stu) throws SQLException;
	public int updateById(Student stu) throws SQLException;
	public int deleteById(int id) throws SQLException;
	public Student queryById(int id) throws SQLException;
	public List<Student> queryAll() throws SQLException;
}

2.7 新建Dao实现类

package com.isoftstone.manager.dao.impl;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.isoftstone.manager.bean.Student;
import com.isoftstone.manager.dao.StudentDao;
import com.isoftstone.utils.DBConfiguration;
import com.isoftstone.utils.JdbcDBUtil;

public class StudentDaoImpl implements StudentDao {

	public int add(Student stu) throws SQLException {
		String sql = "insert into stu values (seq_stu.nextval,?,?,?,?)";
		Object[] params = new Object[]{stu.getName(),stu.getGender(),stu.getAge(),stu.getEmail()};
		return JdbcDBUtil.add(sql,params);
	}

	public int updateById(Student stu) throws SQLException {
		String sql = "update stu set name = ?,gender=?,age=?,email=? where id = ?";
		Object[] params = new Object[]{stu.getName(),stu.getGender(),stu.getAge(),stu.getEmail(),stu.getId()};
		return JdbcDBUtil.update(sql,params);
	}

	public int deleteById(int id) throws SQLException {
		String sql = "delete from stu where id = " + id;
		return JdbcDBUtil.delete(sql);
	}

	public Student queryById(int id) throws SQLException {
		String sql = "select * from stu where id = ?";
		return JdbcDBUtil.runner.query(DBConfiguration.getConnection(), sql, new BeanHandler<Student>(Student.class),id);
	}

	public List<Student> queryAll() throws SQLException {
		String sql = "select * from stu where id = ?";
		return JdbcDBUtil.runner.query(DBConfiguration.getConnection(), sql, new BeanListHandler<Student>(Student.class));
	}
}