JDBC常用API小結

建立數據庫鏈接的三種方式：

package com.victor_01;import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;import org.junit.Test;public class Test1 {private String url = "jdbc:mysql://192.168.244.144:3306/test";private String user = "root";private String password = "123456";@Testpublic void test1() throws Exception {Driver driver = new com.mysql.jdbc.Driver();Properties prop = new Properties();prop.setProperty("user", user);prop.setProperty("password", password);Connection conn = driver.connect(url, prop);System.out.println(conn);}@Testpublic void test2() throws SQLException{Driver driver = new com.mysql.jdbc.Driver();DriverManager.registerDriver(driver);Connection conn = DriverManager.getConnection(url, user, password);System.out.println(conn);}@Testpublic void test3() throws Exception {Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection(url, user, password);System.out.println(conn);}}

推薦使用第三種，其實第二種和第三種本質上是一種，后者是前者的優化版。

?

JDBC接口核心的API

 JDBC接口核心的APIjava.sql.*   和  javax.sql.*|- Driver接口： 表示java驅動程序接口。所有的具體的數據庫廠商要來實現此接口。|- connect(url, properties):  連接數據庫的方法。url: 連接數據庫的URL URL語法： jdbc協議:數據庫子協議://主機:端口/數據庫
                            user： 數據庫的用戶名password： 數據庫用戶密碼|- DriverManager類： 驅動管理器類，用于管理所有注冊的驅動程序|-registerDriver(driver)  : 注冊驅動類對象|-Connection getConnection(url,user,password);  獲取連接對象|- Connection接口： 表示java程序和數據庫的連接對象。|- Statement createStatement() ： 創建Statement對象|- PreparedStatement prepareStatement(String sql)  創建PreparedStatement對象|- CallableStatement prepareCall(String sql) 創建CallableStatement對象|- Statement接口： 用于執行靜態的sql語句|- int executeUpdate(String sql)  ： 執行靜態的更新sql語句（DDL，DML）|- ResultSet executeQuery(String sql)  ：執行的靜態的查詢sql語句（DQL）|-PreparedStatement接口：用于執行預編譯sql語句|- int executeUpdate() ： 執行預編譯的更新sql語句（DDL，DML）|-ResultSet executeQuery()  ： 執行預編譯的查詢sql語句（DQL）|-CallableStatement接口：用于執行存儲過程的sql語句（call xxx）|-ResultSet executeQuery()  ： 調用存儲過程的方法|- ResultSet接口：用于封裝查詢出來的數據|- boolean next() ： 將光標移動到下一行|-getXX() : 獲取列的值

?

Statement接口

package com.victor_01;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;import org.junit.Test;public class Demo2 {private String url = "jdbc:mysql://192.168.244.144:3306/test";private String user = "root";private String password = "123456";@Testpublic void Test1() {Connection conn = null;Statement stmt = null;ResultSet rs = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection(url, user, password);stmt = conn.createStatement();String sql = "drop table if exists jdbc_test";int result = stmt.executeUpdate(sql);System.out.println("Drop table:" + result);sql = "create table jdbc_test(id int,name varchar(10))";result = stmt.executeUpdate(sql);System.out.println("Create result:" + result);sql = "insert into jdbc_test values(1,'hello')";result = stmt.executeUpdate(sql);System.out.println("Insert result:" + result);int id = 2;String name = "world";sql = "insert into jdbc_test values(" + id + ",'" + name + "')";result = stmt.executeUpdate(sql);System.out.println("Insert result2:" + result);name = "java";sql = "update jdbc_test set name='" + name + "' where id=" + id+ "";result = stmt.executeUpdate(sql);System.out.println("Update result:" + result);sql = "select * from jdbc_test";rs = stmt.executeQuery(sql);while (rs.next()) {System.out.println(rs.getInt("id") + " " + rs.getString("name"));}} catch (Exception e) {e.printStackTrace();} finally {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (stmt != null)try {stmt.close();} catch (SQLException e) {e.printStackTrace();}if (conn != null)try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}

?

PreparedStatement接口

package com.victor_01;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;import org.junit.Test;public class Demo3 {private String url="jdbc:mysql://192.168.244.144:3306/test";private String user="root";private String password="123456";@Testpublic void PreparedStatementTest() throws Exception{Class.forName("com.mysql.jdbc.Driver");Connection conn=DriverManager.getConnection(url, user, password);String sql="insert into jdbc_test(id,name) values(?,?)";PreparedStatement prestmt =conn.prepareStatement(sql);prestmt.setInt(1, 3);prestmt.setString(2,"tom");int result=prestmt.executeUpdate();System.out.println(result);sql="update jdbc_test set name=? where id=?";prestmt=conn.prepareStatement(sql);prestmt.setString(1, "steve");prestmt.setInt(2, 3);result=prestmt.executeUpdate();System.out.println(result);sql="select * from jdbc_test where id=?";prestmt=conn.prepareStatement(sql);prestmt.setInt(1, 3);ResultSet rs= prestmt.executeQuery();while(rs.next()){System.out.println("id="+rs.getInt(1)+";name="+rs.getString(2));}rs.close();prestmt.close();conn.close();}}

?

CallableStatement接口

package com.victor_01;import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;import org.junit.Test;public class Demo3 {private String url="jdbc:mysql://192.168.244.144:3306/test";private String user="root";private String password="123456";@Testpublic void PreparedStatementTest() throws Exception{Class.forName("com.mysql.jdbc.Driver");Connection conn=DriverManager.getConnection(url, user, password);String sql="drop procedure findById";Statement stmt=conn.createStatement();int result=stmt.executeUpdate(sql);System.out.println("drop result:="+result);sql=" CREATE PROCEDURE findById(IN sid INT) BEGIN select * from jdbc_test where id=sid; end";stmt=conn.createStatement();result=stmt.executeUpdate(sql);System.out.println("create result:="+result);//直接調用存儲過程sql="call findById(2)";CallableStatement stmt1=conn.prepareCall(sql);ResultSet rs=stmt1.executeQuery();while(rs.next()){System.out.println("id="+rs.getInt(1)+",name="+rs.getString(2));}//傳參調用存儲過程sql="call findById(?)";stmt1=conn.prepareCall(sql);stmt1.setInt(1, 4);rs=stmt1.executeQuery();while(rs.next()){System.out.println("id="+rs.getInt(1)+",name="+rs.getString(2));}//帶有輸出參數的存儲過程sql="drop procedure findById1";stmt=conn.createStatement();result=stmt.executeUpdate(sql);System.out.println("drop findById1 result:"+result);sql=" CREATE PROCEDURE findById1(IN sid INT,OUT sname VARCHAR(10)) BEGIN select name into sname from jdbc_test where id=sid; end";stmt=conn.createStatement();result=stmt.executeUpdate(sql);System.out.println("create result:="+result);sql="call findById1(?,?)";stmt1=conn.prepareCall(sql);stmt1.setInt(1, 4);stmt1.registerOutParameter(2, java.sql.Types.VARCHAR);stmt1.executeQuery(); //注意：結果不是返回到ResultSet中，而是返回到輸出參數中。   String sname=stmt1.getString(2);System.out.println(sname);rs.close();stmt1.close();stmt.close();conn.close();}}