JDBC核心API詳解
時間：20180310
Statement對象：可以類比于一艘貨船乖篷，1.現(xiàn)在java程序中將sql裝入Statement對象中饶碘，2.Statement對象貨船漂洋過海到mysql數(shù)據(jù)庫，3.在數(shù)據(jù)庫中執(zhí)行Statement對象中sql語句瑞驱，4.將sql執(zhí)行的結果放入Statement中，5.Statement再次飄洋過海回到java程序中谎砾，6.將Statement對象中結果集ResultSet返回給java程序。
例1：通過Statement去執(zhí)行sql
使用Statement接口執(zhí)行DDL語句(Create/drop/alter)
/**
* 使用Statement接口執(zhí)行DDL語句(Create/drop/alter)
* @author mengjie
*
*/
public class Demo1 {
//url==jdbc協(xié)議：mysql協(xié)議+ip地址+端口+數(shù)據(jù)庫
private static String url = "jdbc:mysql://localhost:3306/day16";
//user
private static String user = "root";
//password
private static String password = "root";
public static void main(String[] args) throws Exception {
//1.注冊驅動程序
Class.forName("com.mysql.jdbc.Driver");
//2.獲取連接
Connection conn = DriverManager.getConnection(url,user,password);
//3.準備sql語句
String sql = "CREATE TABLE student(id INT, NAME VARCHAR(20),age INT)";
//Statement對象：可以類比于一艘貨輪
//4.在連接基礎上捧颅，創(chuàng)建Statement
Statement stmt = conn.createStatement();
//5.執(zhí)行sql,返回結果
int count = stmt.executeUpdate(sql);
System.out.println("影響了"+count+"行景图！");
//6關閉資源：后打開先關閉
stmt.close();
conn.close();
}
}
例2： 通過Statement去執(zhí)行sql
使用Statement接口執(zhí)行DML語句（insert/update/delete）
/**
* 使用Statement對象執(zhí)行DML語句（insert/update/delete）
* @author mengjie
*
*/
public class Demo2 {
private static String url = "jdbc:mysql://localhost:3306/day16";
private static String username = "root";
private static String password = "root";
public static void main(String[] args) throws Exception {
//1.注冊驅動
Class.forName("com.mysql.jdbc.Driver");
//2.創(chuàng)建連接
Connection conn = DriverManager.getConnection(url,username,password);
//3.創(chuàng)建sql
String sql = "INSERT INTO student (id,NAME,age) VALUES(1,'小花',22)";
//4.創(chuàng)建statement
Statement statement = conn.createStatement();
//5.執(zhí)行sql
int n = statement.executeUpdate(sql);
System.out.println("影響了" + n + "行");
statement.close();
conn.close();
}
}
總結
jdbc操作步驟
- 注冊驅動程序
- 獲取連接對象
- 準備sql語句
- 創(chuàng)建Statement對象
- 執(zhí)行sql
- 關閉資源
標準的jdbc，并利用工具類的寫法
/**
* jdbc通用方法
* @author mengjie
*
*/
public class JdbcUtil {
//url
private static String url = "jdbc:mysql://localhost:3306/day16";
//user
private static String user = "root";
//password
private static String password = "root";
/**
*z只注冊一次驅動碉哑，靜態(tài)代碼塊
*/
static {
//注冊驅動程序
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 獲取連接的方法
* @throws SQLException
*/
public static Connection getConnection() {
try {
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 釋放資源的方法
*/
public static void close(Statement stmt, Connection conn) {
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
public class Demo3 {
public static void main(String[] args) throws Exception {
Connection conn = null;
Statement stmt = null;
try {
//1)獲取連接
conn = JdbcUtil.getConnection();
//2)準備sql
String sql = "CREATE TABLE employee(" +
"id INT PRIMARY KEY AUTO_INCREMENT, "+
"NAME VARCHAR(20),"+
"gender VARCHAR(2), "+
"age INT, "+
"title VARCHAR(20),"+
"email VARCHAR(50),"+
"phone VARCHAR(11))";
//3）創(chuàng)建一個Statement對象
stmt = conn.createStatement();
//4) 執(zhí)行sql
int n = stmt.executeUpdate(sql);
System.out.println("影響了"+n+"行");
} catch (Exception e) {
e.printStackTrace();
} finally {
//5)關閉資源
JdbcUtil.close(stmt, conn);
}
}
}
重載通用類方法
/**
* jdbc通用方法
* @author mengjie
*
*/
public class JdbcUtil {
//url
private static String url = "jdbc:mysql://localhost:3306/day16";
//user
private static String user = "root";
//password
private static String password = "root";
/**
*z只注冊一次驅動挚币，靜態(tài)代碼塊
*/
static {
//注冊驅動程序
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 獲取連接的方法
* @throws SQLException
*/
public static Connection getConnection() {
try {
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 釋放資源的方法
*/
public static void close(Statement stmt, Connection conn) {
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
/**
* 釋放資源的方法
*/
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
public class Demo4 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet res = null;
try{
//1)獲取連接
conn = JdbcUtil.getConnection();
//2)準備sql
String sql = "select * from student";
//3)創(chuàng)建statement
stmt = conn.createStatement();
//4)執(zhí)行sql
res = stmt.executeQuery(sql);
//取數(shù)據(jù)
//int id = res.getInt(1);//java.sql.SQLException: Before start of result set
//上面報錯的原因在于 光標指向第一行之前
//System.out.println(res.next());//true
//System.out.println(res.next());//true
//System.out.println(res.next());//true
//System.out.println(res.next());//true
//System.out.println(res.next());//false //java.sql.SQLException: After end of result set
//s上面報錯的原因在于光標指向最后一行的之后
//next()方法返回值如果是true代表當前行有數(shù)據(jù)，那么就可以使用getXX()方法獲取列的值扣典，如果是false妆毕，則沒有數(shù)據(jù)，這時
//如果調用getXX()就會報錯贮尖！
//int id = res.getInt(1);
while(res.next()) {
//1)根據(jù)列索引來獲取
// int id = res.getInt(1);
// String name = res.getString(2);
// int age = rs.getInt(3);
// System.out.println(id+"\t" + name +"\t" + age);
//2)根據(jù)列名稱來獲取
int id = res.getInt("id");
String name = res.getString("name");
int age = res.getInt("age");
System.out.println(id+"\t" + name + "\t" + age);
}
}catch(Exception e){
e.printStackTrace();
}finally {
JdbcUtil.close(res, stmt, conn);
}
}
}
