JDBC Helloworld
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
//import com.mysql.jdbc.Driver;
public class HelloJDBC {
@Test
public void method() {
/*
* 1.導入驅動
* 新建lib文件夾用來存放導入的包爷辙，將包Add to Build Path
* 2.注冊驅動
* 3.獲取連接對象
* 4.根據(jù)連接對象，獲取可以執(zhí)行SQL語句的對象
* 5.執(zhí)行SQL語句竹椒，獲取結果集
* 6.操作結果集
* 7.釋放資源
*/
try {
//2.注冊驅動
//使用下邊的方式注冊驅動會注冊兩次童太，使用的話需要導包
// DriverManager.registerDriver(new Driver());
//Driver dir = new Driver();//也可以直接new Driver();
//直接只單獨加載：Dirver.class 該字節(jié)碼文件，推薦使用反射的方式加載驅動胸完，只加載一次
//如果忘記類名书释，可以寫一個Driver之后導包，復制"com.mysql.jdbc.Driver"
Class.forName("com.mysql.jdbc.Driver");
//3.獲取連接對象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc", "root", "123");
//4.根據(jù)連接對象赊窥，獲取可以執(zhí)行SQL語句的對象
Statement stat = conn.createStatement();
//5.執(zhí)行SQL語句征冷，獲取結果集
String sql = "select * from student;";//后邊可以不加分號
ResultSet rs = stat.executeQuery(sql);
//6.操作結果集
while(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
System.out.println(id + "------" + name);
}
//7.釋放資源
rs.close();
stat.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
如上的釋放資源會有問題，通過try catch finally代碼嵌套進行優(yōu)化誓琼，如下代碼是實現(xiàn)了增刪改查四個功能。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
/**
* JDBC CURD
* @author me
*/
public class JDBC_CURD {
/**
* 增
*/
@Test
public void method1() {
Connection conn = null;
Statement stat = null;
try {
//注冊驅動
Class.forName("com.mysql.jdbc.Driver");
//獲得數(shù)據(jù)庫鏈接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc", "root", "123");
//根據(jù)鏈接對象肴捉，獲取可以執(zhí)行SQL語句的對象
stat = conn.createStatement();
//執(zhí)行SQL語句
String sql = "insert into student(id, name) values(null, 'zhuba');";//一次只能執(zhí)行一條SQL語句
int r = stat.executeUpdate(sql);
if(r > 0) {
System.out.println("添加成功");
System.out.println(r + "行受到影響");
} else {
System.out.println("添加失敗");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(stat != null) {
stat.close();
stat = null;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 刪
*/
@Test
public void method2() {
Connection conn = null;
Statement stat = null;
try {
//注冊驅動
Class.forName("com.mysql.jdbc.Driver");
//獲取和數(shù)據(jù)庫的物理鏈接
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/testjdbc", "root", "123");
//通過數(shù)據(jù)庫鏈接對象，獲取執(zhí)行SQL語句的對象
stat = conn.createStatement();
//執(zhí)行SQL語句
String sql = "delete from student where name = 'zhangsan'";
int r = stat.executeUpdate(sql);
if(r > 0) {
System.out.println("刪除成功");
} else {
System.out.println("刪除失敗");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(stat != null) {
stat.close();
stat = null;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 改
*/
@Test
public void method3() {
Connection conn = null;
Statement stat = null;
try {
//注冊驅動
Class.forName("com.mysql.jdbc.Driver");
//獲得數(shù)據(jù)庫鏈接對象
conn = DriverManager.getConnection("jdbc:mysql:///testjdbc", "root", "123");
//通過數(shù)據(jù)庫鏈接對象獲取可以執(zhí)行SQL語句的對象
stat = conn.createStatement();
//執(zhí)行SQL語句
String sql = "update student set name = 'zhangsan_2' where name = 'zhangsan';";
int r = stat.executeUpdate(sql);
if(r > 0) {
System.out.println("修改成功");
System.out.println(r + "行受到影響");
} else {
System.out.println("修改失敗");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(stat != null) {
stat.close();
stat = null;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 查
*/
@Test
public void method4() {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
// 注冊驅動
Class.forName("com.mysql.jdbc.Driver");
// 獲得與數(shù)據(jù)庫的鏈接
conn = DriverManager.getConnection("jdbc:mysql:///testjdbc", "root", "123");
// 獲得執(zhí)行SQL的對象
stat = conn.createStatement();
// 執(zhí)行SQL語句,獲得結果集
String sql = "select * from student;";
rs = stat.executeQuery(sql);
// 操作結果集
while (rs.next()) {
// 獲得該行該列的對象
int id = rs.getInt("id");
String name = rs.getString("name");
System.out.println(id + "---" + name);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 關閉鏈接
try {
if (rs != null) { //避免未給對象賦值，導致.close()空指針異常
rs.close();
rs = null;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (stat != null) {
stat.close();
stat = null;// GC優(yōu)先回收null對象
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
Statement 的 executeQuary()啸驯，executeUpdate()喊括，execute() 三者的區(qū)別：
- ResultSet executeQuery(String sql) throws SQLException
Executes the given SQL statement, which returns a single ResultSet object.
- int executeUpdate(String sql) throws SQLException
Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
- boolean execute(String sql) throws SQLException
Executes the given SQL statement, which may return multiple results. In some (uncommon) situations, a single SQL statement may return multiple result sets and/or update counts. Normally you can ignore this unless you are (1) executing a stored procedure that you know may return multiple results or (2) you are dynamically executing an unknown SQL string.
