1.最初的jdbc连接:
1.资源文件jdbc.properties
jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:ORCL
jdbc.username=root
jdbc.password=123456
2.JdbcUtil.java
package com.lanqiao.dao;
import java.sql.*;
import java.util.ResourceBundle;
public class JdbcUtil {
private static String getValue(String key) {
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
return bundle.getString(key);
}
private static final String DRIVER = getValue("jdbc.driver");
private static final String URL = getValue("jdbc.url");
private static final String USERNAME = getValue("jdbc.username");
private static final String PASSWORD = getValue("jdbc.password");
static{
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
System.err.println("注册数据库驱动程序失败。" + e.getMessage());
}
}
public static Connection getConnection() {
try {
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
return conn;
} catch (SQLException e) {
System.err.println("a获得数据连接失败。" + e.getMessage());
}
return null;
}
public static void close(Connection conn, Statement stmt, ResultSet rs) {
try {
if(rs != null){
rs.close();
}
if(stmt != null){
stmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
System.out.println(getValue("jdbc.driver"));
System.out.println(getConnection());
}
}
3.Jdbc调用步骤
1. 将驱动程序包加入到Module 的dependency 中
2. 新建一个属性文件:jdbc.properties
3. 编写一个JdbcUtils工具类:
a) 加载/注册数据库驱动程序
b) 获取数据库连接
c) 释放数据库资源(Connection, Statement, ResultSet)
4. 编写一个DAO类:EmployeeDao,提供一个查询所有员工的方法:List<Employee> selectAll();
a) 通过JdbcUtils工具类获得数据库连接
Connection conn = JdbcUtils.getConnection();
b) 通过数据库连接Connection创建语句对象
Statement stmt = conn.createStatement();
c) 执行语句并获得查询结果集
ResultSet rs = stmt.executeQuery("select * from employee order by empno");
d) 遍历查询结果集
while(rs.next()){
Xxx value = rs.getXxx("列名");
Employee e = new Employee(.....);
arrayList.add(e);
}
e) 最后必须关闭数据资源:Connection, Statement, Resultset
4.Jdbc调用存储过程
Connection connection = JdbcUtil.getConnection();
try {
CallableStatement cs =
connection.prepareCall("{call sum_sal_by_job(?,?,?)}");
cs.setString(1,"CLERK");
cs.setString(3, "default value");
cs.registerOutParameter(2, Types.DOUBLE, 2);
cs.registerOutParameter(3, Types.VARCHAR);
boolean isSucc = cs.execute();
double sumSal = cs.getDouble(2);
String x = cs.getString(3);
System.out.println("总工资:" + sumSal);
System.out.println("测试in out模式:" + x);
}
2.不使用资源文件,数据库密码直接写进jdbc连接里
package dao;
import java.sql.*;
import java.util.ResourceBundle;
public class MyJdbcUtil {
static{
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.err.println("注册数据库驱动程序失败。" + e.getMessage());
}
}
public static Connection getConnection() {
try {
Connection conn =
DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:ORCL",
"root",
"123456");
return conn;
} catch (SQLException e) {
System.err.println("a获得数据连接失败。" + e.getMessage());
}
return null;
}
public static void close(Connection conn, Statement stmt, ResultSet rs) {
try {
if(rs != null){
rs.close();
}
if(stmt != null){
stmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
System.out.println(getConnection());
}
}