首先要导入驱动包,可以网上下对应数据库版本的驱动包,也可以使用自带的,
我就使用自带的,我的目录:C:\Oracle11g\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar。
为了代码逻辑好看,都把异常抛出去,
连接:原始方法
public class BaseDao {
//定义数据库连接属性
private static String user = "scott";
private static String password = "123456";
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
//Oracle 数据库的连接
public static Connection getOracelConnection() throws ClassNotFoundException, SQLException {
//利用反射机制加载驱动
Class.forName(driver);
Connection conn = null;
conn = DriverManager.getConnection(url, user, password);
return conn;
}
}
读取外部文件,获取 user 、password、url (框架原理)
public class BaseDao1 {
//定义数据库连接属性
private static String user;
private static String password;
private static String driver;
private static String url;
//使用静态代码块,完成连接参数的初始化
static{
Properties properties = new Properties();
//利用反射原理机制加载属性文件、database.properties
InputStream inputStream =
BaseDao1.class.getResourceAsStream("database.properties");
try {
//加载物理文件
properties.load(inputStream);
user = properties.getProperty("user");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
url = properties.getProperty("url");
} catch (IOException e) {
e.printStackTrace();
}
}
//Oracle 数据库的连接
public static Connection getOracelConnection() throws ClassNotFoundException, SQLException {
//利用反射机制加载驱动
Class.forName(driver);
Connection conn = null;
conn = DriverManager.getConnection(url, user, password);
return conn;
}
}
配置文件 database.properties :
# oracle数据库连接参数
user=scott
password=123456
driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
两种基本调用:使用存储过程。
public class test2 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Connection conn = (Connection) BaseDao.getOracelConnection();
String sql = "call pro_emp(?,?,?,?)";
CallableStatement callableStatement = conn.prepareCall(sql);
//设置输入参数的值
callableStatement.setInt(1, 7788);
//设置java属性与oracle数据库属性类型的映射关系
callableStatement.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
callableStatement.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
callableStatement.registerOutParameter(4,oracle.jdbc.OracleTypes.DATE);
//执行
callableStatement.execute();
//取值
String ename = callableStatement.getString(2);
Double sal = callableStatement.getDouble(3);
Date hiredate = callableStatement.getDate(4);
//输出
System.out.println("名字:"+ename);
System.out.println("工资:"+sal);
System.out.println("入职日期:"+hiredate);
}
}
public class test3 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Connection conn = (Connection) BaseDao.getOracelConnection();
//占位符,(填充符)
String sql = "call pro_deptno(?,?)";
CallableStatement callableStatement = conn.prepareCall(sql);
//设置输入参数的值
callableStatement.setInt(1, 30);
//设置java属性与oracle数据库属性类型的映射关系
callableStatement.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
//执行
callableStatement.execute();
//取值
ResultSet rs = (ResultSet) callableStatement.getObject(2);
while (rs.next()) {
System.out.println("empno = " + rs.getInt("empno"));
System.out.println("ename = " + rs.getString("ename"));
System.out.println("-----------------------------------");
}
}
}
表对应类:
public class Test_oracle {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection conn = null;
conn = BaseDao.getOracelConnection();
if (conn != null) {
System.out.println("oracle数据库连接成功");
}else {
System.out.println("oracle数据库连接失败");
}
PreparedStatement pre = conn.prepareStatement
("select * from emp where empno = ?");
pre.setInt(1,7788);
ResultSet rs = pre.executeQuery();
emp emp = new emp(); //一个emp 类
while (rs.next()) {
emp.setEmpno(rs.getInt("empno"));
emp.setEname(rs.getString("ename"));
emp.setMgr(rs.getInt("mgr"));
emp.setHiredate(rs.getString("hiredate"));
emp.setSal(rs.getDouble("sal"));
emp.setComm(rs.getDouble("comm"));
emp.setDeptno(rs.getInt("deptno"));
}
System.out.println(emp);
}
}