JDBC核心API详解

JDBC核心API详解

时间：20180310

Statement对象：可以类比于一艘货船，1.现在java程序中将sql装入Statement对象中，2.Statement对象货船漂洋过海到mysql数据库，3.在数据库中执行Statement对象中sql语句，4.将sql执行的结果放入Statement中，5.Statement再次飘洋过海回到java程序中，6.将Statement对象中结果集ResultSet返回给java程序。

例1：通过Statement去执行sql
使用Statement接口执行DDL语句(Create/drop/alter)

/**
 * 使用Statement接口执行DDL语句(Create/drop/alter)
 * @author mengjie
 *
 */
public class Demo1 {
    //url==jdbc协议：mysql协议+ip地址+端口+数据库
    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.在连接基础上，创建Statement
        Statement stmt = conn.createStatement();
        //5.执行sql,返回结果
        int count = stmt.executeUpdate(sql);
        System.out.println("影响了"+count+"行！");
        //6关闭资源：后打开先关闭
        stmt.close();
        conn.close();
    }
}

例2： 通过Statement去执行sql
使用Statement接口执行DML语句（insert/update/delete）

/**
 * 使用Statement对象执行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.创建连接
        Connection conn = DriverManager.getConnection(url,username,password);
        //3.创建sql
        String sql = "INSERT INTO student (id,NAME,age) VALUES(1,'小花',22)";
        //4.创建statement
        Statement statement = conn.createStatement();
        //5.执行sql
        int n = statement.executeUpdate(sql);
        System.out.println("影响了" + n + "行");
        statement.close();
        conn.close();
    }
}

总结

jdbc操作步骤

1. 注册驱动程序
2. 获取连接对象
3. 准备sql语句
4. 创建Statement对象
5. 执行sql
6. 关闭资源

标准的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只注册一次驱动，静态代码块 
     */
    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）创建一个Statement对象
            stmt = conn.createStatement();
            //4) 执行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只注册一次驱动，静态代码块 
     */
    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)创建statement
           stmt = conn.createStatement();
           //4)执行sql
           res = stmt.executeQuery(sql);
           //取数据
           //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代表当前行有数据，那么就可以使用getXX()方法获取列的值，如果是false，则没有数据，这时
           //如果调用getXX()就会报错！
           //int id = res.getInt(1);
           
           while(res.next()) {
               //1)根据列索引来获取
//              int id = res.getInt(1);
//              String name = res.getString(2);
//              int age = rs.getInt(3);
//              System.out.println(id+"\t" + name +"\t" + age);
               
               //2)根据列名称来获取
               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);
       }
   }
}