Java程序调用Oracle

1. java程序调用存储过程

public void procedureTest(){String sql &＃61; "{ call pro_test111(?,?,?)}";CallableStatement call &＃61; null;try {call &＃61; conn.prepareCall(sql);//IN需要set OUT需要reistercall.setString(1, "jdbc");call.setInt(2, 2);call.setString(3, "default");call.registerOutParameter(3, Types.VARCHAR);call.execute();//拿到返回值Object object &＃61; 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&＃61;c3p0
className&＃61;oracle.jdbc.driver.OracleDriver
url&＃61;jdbc:oracle:thin:&＃64;localhost:1521:orcl
username&＃61;chalco1129
password&＃61;chalco1129
initialPoolSize&＃61;10
minPoolSize&＃61;20
maxPoolSize&＃61;50
maxIdleTime&＃61;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;
/*** 数据库连接配置信息* &＃64;author mjzhud**/
public class DBConfiguration {private static Properties pro;static {//加载属性文件&＃xff0c;读取数据库连接配置信息pro &＃61; new Properties();try {pro.load(DBConfiguration.class.getResourceAsStream("/db.properties"));} catch (IOException e) {e.printStackTrace();System.out.println("未找到配置文件&＃xff01;&＃xff01;&＃xff01;");}}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 &＃61; 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("连接失败&＃xff0c;检查用户名和密码");}return null;}public static void close(Statement statement,Connection conn){try {if (statement !&＃61; null) statement.close();} catch (SQLException e) {e.printStackTrace();}try {if (conn !&＃61; null) conn.close();} catch (SQLException e) {e.printStackTrace();}}public static void close(ResultSet rs,Statement statement,Connection conn){try {if (rs !&＃61; 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 &＃61; DBConfiguration.getConnection();public static QueryRunner runner &＃61; new QueryRunner();public static int add(String sql) throws SQLException{int count &＃61; -1;try {conn.setAutoCommit(false);count &＃61; 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 &＃61; -1;try {conn.setAutoCommit(false);count &＃61; 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批处理&＃xff0c;只能处理同一预处理sql语句* &＃64;param sql* &＃64;param paramArr* &＃64;return* &＃64;throws SQLException*/public static int dbUtilsBatch(String sql,Object[][] paramArr) throws SQLException{int[] count &＃61; null;try {conn.setAutoCommit(false);count &＃61; runner.batch(conn , sql, paramArr);} catch (Exception e) {conn.rollback();return -1;} finally {conn.commit();}int updateCount &＃61; 0;if (count !&＃61; null && count.length > 0) {for (int i : count) {if (i > 0) updateCount&＃43;&＃43;;}}System.out.println("影响的行数&＃61;"&＃43;updateCount);return 1;}/*** Statement可以批量处理各种sql* &＃64;param sqls* &＃64;return* &＃64;throws SQLException*/public static int statementBatch(String[] sqls) throws SQLException{Statement statement &＃61; conn.createStatement();for (String sql : sqls) {statement.addBatch(sql);}int[] count &＃61; null;try {conn.setAutoCommit(false);count &＃61; statement.executeBatch();} catch (Exception e) {conn.rollback();return -1;} finally {conn.commit();DBConfiguration.close(statement, conn);}int updateCount &＃61; 0;if (count !&＃61; null && count.length > 0) {for (int i : count) {if (i > 0) updateCount&＃43;&＃43;;}}System.out.println("影响的行数&＃61;"&＃43;updateCount);return 1;}public static List> queryList(String sql) throws SQLException{return runner.query(conn, sql, new MapListHandler());}public static List> 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 &＃61; id;}public String getName() {return name;}public void setName(String name) {this.name &＃61; name;}public int getGender() {return gender;}public void setGender(int gender) {this.gender &＃61; gender;}public int getAge() {return age;}public void setAge(int age) {this.age &＃61; age;}public String getEmail() {return email;}public void setEmail(String email) {this.email &＃61; email;}&＃64;Overridepublic String toString() {return "Student [id&＃61;" &＃43; id &＃43; ", name&＃61;" &＃43; name &＃43; ", gender&＃61;" &＃43; gender&＃43; ", age&＃61;" &＃43; age &＃43; ", email&＃61;" &＃43; email &＃43; "]";}
}

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 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 &＃61; "insert into stu values (seq_stu.nextval,?,?,?,?)";Object[] params &＃61; new Object[]{stu.getName(),stu.getGender(),stu.getAge(),stu.getEmail()};return JdbcDBUtil.add(sql,params);}public int updateById(Student stu) throws SQLException {String sql &＃61; "update stu set name &＃61; ?,gender&＃61;?,age&＃61;?,email&＃61;? where id &＃61; ?";Object[] params &＃61; 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 &＃61; "delete from stu where id &＃61; " &＃43; id;return JdbcDBUtil.delete(sql);}public Student queryById(int id) throws SQLException {String sql &＃61; "select * from stu where id &＃61; ?";return JdbcDBUtil.runner.query(DBConfiguration.getConnection(), sql, new BeanHandler(Student.class),id);}public List queryAll() throws SQLException {String sql &＃61; "select * from stu where id &＃61; ?";return JdbcDBUtil.runner.query(DBConfiguration.getConnection(), sql, new BeanListHandler(Student.class));}
}