JDBC常用API小结

建立数据库链接的三种方式：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

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";       @Test     public 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);     }           @Test     public 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);     }           @Test     public void test3() throws Exception {         Class.forName("com.mysql.jdbc.Driver");         Connection conn = DriverManager.getConnection(url, user, password);         System.out.println(conn);               }   }

推荐使用第三种，其实第二种和第三种本质上是一种，后者是前者的优化版。

 

JDBC接口核心的API

 JDBC接口核心的API
                java.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接口

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82

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";       @Test     public 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接口

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

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";     @Test     public 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接口

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74

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";     @Test     public 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();        }                        }