版权声明:本系列博客是根据传智播客 李勇 老师讲的jdbc视频和自己的理解整理如下,希望可以和同水平的菜鸟一起共勉!!
JDBC: Java Data Base Connectivity (java 数据库连接) 由接口和少量的类构成的API. 由 java.sql 和 javax.sql两部分组成。
1. 应用程序、JDBC API、数据库驱动和数据库的关系:
2.Java连接数据库的基本步骤:
(1)注册相应的数据库驱动,(只需注册一次)
(2)建立连接(Connection)
(3)创建用于执行SQL的语句(Statement)
(4)执行语句(statement.execute())
(5)处理执行的结果(ResultSet)
(6)释放资源(rs.close() st.close() conn.close())
2.1 注册相应的数据库驱动
1.
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
会造成DriverManager中产生两个一样的驱动,并会对具体的驱动类产生依赖。
2.
System.setProperty("jdbcDriver", "oracle.jdbc.OracleDriver");
System.setProperty("jdbcDrivers","oracle.jdbc.OracleDriver:com.mysql.jdbc.driver");
虽然不会对具体的驱动类产生依赖;但注册不太方便,可以同时注册多个驱动、中间用:隔开
3.
Class.forName("oracle.jdbc.OracleDriver");
通过Class的forName方法通过类名将驱动加载到虚拟机中,不一定会创建类的实例,
注释: OracleDriver的实现部分中一定包含一个静态代码块,将这个驱动加载到DriverManager的registeredDrivers中(ArrayList类型)。
2.2 建立连接
2.2.1 URL格式 User Password
JDBC:子协议:子名称//主机名:端口/数据库名?属性名=属性值&…
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
user: 数据库登陆名称;password:数据库登陆密码;
2.2.2 通过DriverManager创建连接
Connection conn = DriverManager.getConnection(url,user,password);
2.3 创建执行SQL的语句(Statement)
Statement st = conn.createStatement();
2.4 执行SQL语句,拿到返回结果
String sql = "select * from stu";
ResultSet rs = st.executeQuery(sql);
2.5 处理结果集
while(rs.next()){ System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"+rs.getObject(3));
}
2.6 释放资源
rs.close();
st.close();
conn.close();
3. 简单的实例代码:
3.1
package jdbc.one;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTest {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
seleDB();
}
static void seleDB() throws SQLException, ClassNotFoundException{
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "";
Connection conn = null;
Statement st =null;
ResultSet rs = null;
try{
Class.forName("oracle.jdbc.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
st = conn.createStatement();
String sql = "select * from stu";
rs = st.executeQuery(sql);
while(rs.next()){ System.out.println(rs.getObject(1)+"\t"
+rs.getObject(2)+"\t"+rs.getObject(3));
}
}finally{
try{
if(rs!=null)
rs.close();
}finally{
try{
if(st!=null)
st.close();
}finally{
if(conn!=null)
conn.close();
}
}
}
}
}
3.2 简单的优化、
package jdbc.two;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCVersionTwo {
static void jdbcMethodOne() throws SQLException{
Connection conn = null;
Statement st = null;
ResultSet rs =null;
try{
conn = JDBCUtils.getConnection();
st = conn.createStatement();
String sql = "select * from stu";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"+rs.getObject(3));
}
}finally{
JDBCUtils.free(rs, st, conn);
}
}
public static void jdbcMethodTwo() throws SQLException{
Connection conn = null;
Statement st = null;
ResultSet rs =null;
JDBCUtilsSingle instance = JDBCUtilsSingle.getInstance();
try{
conn = instance.getConnection();
st = conn.createStatement();
String sql = "select * from stu";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"+rs.getObject(3)+
"\t"+rs.getObject(4));
}
}finally{
instance.free(rs, st, conn);
}
}
public static void main(String[] args) throws SQLException {
//jdbcMethodOne();
jdbcMethodTwo();
}
}
第一种静态的工具类代码:
package jdbc.two;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public final class JDBCUtils {
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String user ="scott";
private static String password = "";
//因为驱动只需要注册一次,我们可以通过静态代码块形式实现
static{
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError();
}
}
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url, user, password);
}
public static void free(ResultSet rs, Statement st, Connection conn){
if(rs!=null)
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(st!=null)
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
第二种单例形式的工具类
package jdbc.two;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtilsSingle {
private String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private String user ="scott";
private String password = "qian";
private static JDBCUtilsSingle instance = null;
private JDBCUtilsSingle(){}
//延迟加载的单例模式,
public static JDBCUtilsSingle getInstance(){
if(instance==null)
synchronized (JDBCUtilsSingle.class) {
if(instance==null)
instance = new JDBCUtilsSingle();
}
return instance;
}
//因为驱动只需要注册一次,我们可以通过静态代码块形式实现
static{
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
throw new RuntimeException();
}
}
public Connection getConnection() throws SQLException{
return DriverManager.getConnection(url, user, password);
}
public void free(ResultSet rs, Statement st, Connection conn){
if(rs!=null)
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(st!=null)
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}