基本概念：JDBC(Java DataBase Connectivity,java數(shù)據(jù)庫連接)是一種用于執(zhí)行SQL語句的Java API喻鳄，可以為多種關(guān)系數(shù)據(jù)庫提供統(tǒng)一訪問趟咆，它由一組用Java語言編寫的類和接口組成唆涝。JDBC提供了一種基準，據(jù)此可以構(gòu)建更高級的工具和接口寇钉，使數(shù)據(jù)庫開發(fā)人員能夠編寫數(shù)據(jù)庫應(yīng)用程序虚循。
一袭祟、數(shù)據(jù)庫連接
1.加載數(shù)據(jù)庫驅(qū)動類
(1)在工程下新建lib文件夾亏掀，將 ojdbc6.jar（jar包在：E:\oracle\product\11.2.0\dbhome_1\jdbc\lib）拷貝至該目錄下
(2)右鍵ojdbc6.jar文件缸榄，build path --> add too build path
(3)將驅(qū)動類加載到內(nèi)存中
Class.forName("oracle.jdbc.driver.OracleDriver");
2.JDBC連接字符串
MySQL：
String Driver="com.mysql.jdbc.Driver"; //驅(qū)動程序
String URL="jdbc:mysql://localhost:3306/db_name"; //連接的URL,db_name為數(shù)據(jù)庫名
String Username="username"; //用戶名
String Password="password"; //密碼
Class.forName(Driver);
Connection con=DriverManager.getConnection(URL,Username,Password);
Oracle:
String Driver="oracle.jdbc.driver.OracleDriver"; //連接數(shù)據(jù)庫的方法
String URL="jdbc:oracle:thin:@localhost:1521:orcl"; //orcl為數(shù)據(jù)庫的SID
String Username="username"; //用戶名
String Password="password"; //密碼
Class.forName(Driver) ; //加載數(shù)據(jù)庫驅(qū)動
Connection con=DriverManager.getConnection(URL,Username,Password); //常用數(shù)據(jù)庫鏈接方式
3.獲取數(shù)據(jù)庫連接對象
Connection conn = DriverManager.getConnection(url,user,password);
4.寫sql
String sql = "xxxxxx";
5.創(chuàng)建PreparedStatement對象
PreparedStatement ps = conn.prepareStatement(sql);
6.執(zhí)行查詢返回ResultSet結(jié)果集對象
ResultSet rs = ps.executeQuery();
7.遍歷結(jié)果集渤弛，獲取查詢結(jié)果
while(rs.next){
rs.getXxx()
}
8.釋放資源
rs.close()
ps.close()
conn.close()
例：
package cn.mystudy;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "SCOTT";
String password = "0128";
Connection conn = DriverManager.getConnection(url,user,password);
String sql = "select * from emp where deptno = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, 20);
ResultSet rs = ps.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("empno"));
}
rs.close();
ps.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
二、JDBC的封裝
新建一個util包甚带，然后在其下面建立DBUtil.java
package util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DBUtil {
private static String DRIVER = "";
private static String URL = "";
private static String USER = "";
private static String PASSWORD = "";
static{
try {
InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
Properties prop = new Properties();
prop.load(in);
DRIVER = prop.getProperty("driver");
URL = prop.getProperty("url");
USER = prop.getProperty("user");
PASSWORD = prop.getProperty("password");
Class.forName(DRIVER);
} catch (IOException e) {
e.printStackTrace();
System.out.println("配置文件讀取失敗");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL,USER,PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn,Statement ps) {
if (ps !=null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn !=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection conn,Statement ps,ResultSet rs) {
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps!=null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
在根目錄下建立db.properties文件
# do not write space
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
user=SCOTT
password=0128
# mysql db
#driver=com.mysql.jdbc.Driver
#url=jdbc:mysql://localhost:3306/db_name
#user=root
#password=1234
使用DBUtil.java
package cn.mystudy;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import util.DBUtil;
public class TestJDBC2 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
String sql = "select * from emp where deptno = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 20);
rs=ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("empno"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(conn, ps, rs);
}
}
}
