java基础（十三）：JDBC

JDBC概述
使用JDBC完成添加/更新/删除操作
使用JDBC完成查询操作
JDBC语法总结
使用PreparedSatement完善JDBC操作
手动启动事务管理
使用JDBC完成多表查询操作
采用分层实现JDBC操作

JDBC概述

JDBC（Java Data Base Connectivity,Java数据库连接）

是一种用于执行SQL语句的Java API，为多种关系数据库提供统一访问

它由一组用Java语言编写的类和接口组成

程序员只需用JDBC API写一个程序，就可以访问所有数据库。

JDBC API

　　提供者：Sun公司

　　内容：供程序员调用的接口与类，集成在java.sql和javax.sql包中，如

　　　　　　　　DriverManager类作用：管理各种不同的JDBC驱动

　　　　　　　　Connection接口

　　　　　　　　Statement接口

　　　　　　　　ResultSet接口

JDBC 驱动

　　提供者：数据库厂商

　　作用：负责连接各种不同的数据库

JDBC对Java程序员而言是API，对实现与数据库连接的服务提供商而言是接口模型。

三方关系

　　SUN公司是规范制定者，制定了规范JDBC（连接数据库规范）

　　数据库厂商微软、甲骨文等分别提供实现JDBC接口的驱动jar包

　　程序员学习JDBC规范来应用这些jar包里的类。

简单地说，JDBC 可做三件事：

　　与数据库建立连接、发送操作数据库的语句并处理结果。

DriverManager ：依据数据库的不同，管理JDBC驱动
Connection ：负责连接数据库并担任传送数据的任务
Statement ：由 Connection 产生、负责发送执行SQL语句
ResultSet：负责保存Statement执行后所产生的查询结果

JDBC语法总结

1：加载一个Driver驱动
2：创建数据库连接（Connection）
3 ：创建SQL命令发送器Statement
4：通过Statement发送SQL命令并得到结果
5：处理SQL结果（select语句）
6：关闭数据库资源
- ResultSet
- Statement
- Connection。

1.加载驱动

加载JDBC驱动是通过调用方法java.lang.Class.forName()，

下面列出常用的几种数据库驱动程序加载语句的形式 :

Class.forName(“oracle.JDBC.driver.OracleDriver”)；//使用Oracle的JDBC驱动程序
Class.forName(“com.microsoft.JDBC.sqlserver.SQLServerDriver”)；//使用SQL Server的JDBC驱动程序
Class.forName(“com.ibm.db2.JDBC.app.DB2Driver”)；//使用DB2的JDBC驱动程序
Class.forName("com.mysql.JDBC.Driver");//使用MySql的JDBC驱动程序

2.创建数据库连接

与数据库建立连接的方法是调用

DriverManager.getConnection(String url, String user, String password )方法

Connection conn=null;
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String user=“scott";
String password=“tiger";
conn = DriverManager.getConnection(url, user, password);

3.创建Statement并发送命令

Statement对象用于将 SQL 语句发送到数据库中，或者理解为执行sql语句
有三种 Statement对象：
- Statement：用于执行不带参数的简单SQL语句；
- PreparedStatement（从 Statement 继承）：用于执行带参数或不带参数的预编译SQL语句；
- CallableStatement（从PreparedStatement 继承）：用于执行数据库存储过程的调用。

4.处理ResultSet结果

ResultSet对象是executeQuery()方法的返回值，它被称为结果集，它代表符合SQL语句条件的所有行，并且它通过一套getXXX方法（这些get方法可以访问当前行中的不同列）提供了对这些行中数据的访问。
ResultSet里的数据一行一行排列，每行有多个字段，且有一个记录指针，指针所指的数据行叫做当前数据行，我们只能来操作当前的数据行。我们如果想要取得某一条记录，就要使用ResultSet的next()方法 ,如果我们想要得到ResultSet里的所有记录，就应该使用while循环。
ResultSet对象自动维护指向当前数据行的游标。每调用一次next()方法，游标向下移动一行。
初始状态下记录指针指向第一条记录的前面，通过next()方法指向第一条记录。循环完毕后指向最后一条记录的后面。

5.关闭数据库资源

　　（1）作为一种好的编程风格，应在不需要Statement对象和Connection对象时显式地关闭它们。

　　　　　　关闭Statement对象和Connection对象的语法形式为：

　　　　　　　 public void close() throws SQLException

　　（2）用户不必关闭ResultSet。当它的 Statement 关闭、重新执行或用于从多结果序列中获取下一个结果时，该ResultSet将被自动关闭。

注意：要按先ResultSet结果集，后Statement，最后Connection的顺序关闭资源，因为Statement和ResultSet是需要连接是才可以使用的，所以在使用结束之后有可能其他的Statement还需要连接，所以不能先关闭Connection。

PreparedStatement 接口继承 Statement接口
PreparedStatement比普通的Statement对象使用起来更加灵活，更有效率

代码示例

demo1

package cn.sxt.jdbc.demo1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Tester {

    /**
     * 引入驱动Jar
     *         JRE System Library——>鼠标右键——>build Path——>Config build Path ——〉Libraries——〉add external jars
     * 访问数据库的步骤
     *         1、加载驱动
     *         2、创建数据库连接
     *         3、创建语句发送器Statement
     *         4、Statement对象发送Sql语句（发送给服务器）
     *         5、处理服务器的响应结果
     *         6、关闭资源
     * 
     * connection
     *         一个数据库连接对象
     * Statement
     *         概述
     *             将Sql语句发送给数据库服器（也可以理解为Sql语句的执行者）
     *         对象    
     *             Statement            ：可以执行不带参数的Sql语句
     *             PreparedStatement    ：执行执行带参数和不带参数的Sql语句
     *             CallableStatement    ：用于执行存储过程的Satatement
     * 
     *             PreparedStatement比Statement更加方便灵活。所以一般使用PreparedStatement
     *         方法
     *             executeUpdate()        ：执行非查询语句（insert、delete、update）
     *             executeQuery()        ：执行查询语句（select）
     * @param args
     * @throws ClassNotFoundException 
     * @throws SQLException 
     */
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Scanner input = new Scanner(System.in);
        
        //6、结果处理
        /*
        if(delete(50) >0)
            System.out.println("数据删除成功");
        else
            System.out.println("数据删除失败");
            
        int count = add(70, "tdt","nj");
        System.out.println(count);
        
        getAll();
        */
        
        System.out.print("请输入部门ID：");
        int id = input.nextInt();
        
        System.out.println("请输入部门名称：");
        String name = input.next();
        
        System.out.println("请输入部门所在地：");
        String loc = input.next();
        
        System.out.println(add(id, name, loc));
        
        
    }
    
    static String url = "jdbc:oracle:thin:@localhost:1521:oracle";
    static String user = "scott";
    static String password = "tiger";
    
    /**
     * 查询所有数据
     */
    static void getAll(){
        
        try {
            //1、加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            
            //2、获取连接对象
            Connection conn = DriverManager.getConnection(url,user, password);
            
            //3、创建statement对象
            Statement statement = conn.createStatement();
                        
            //4、执行语句
            String sql = "select * from dept";
            ResultSet rs = statement.executeQuery(sql);
            //5、处理查询结果
            while(rs.next()){
                System.out.println(rs.getInt("DeptNo") + "	" + rs.getString("DName") + "	" +rs.getString("Loc"));
            }
            //6、关闭资源
            rs.close();
            statement.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    /**
     * 将部门信息添加到dept表中
     * @param id
     * @param name
     * @param loc
     * @return
     */
    static int add(int id, String name, String loc){    
        int count = 0;
        try {
            //1、加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            
            //2、获取连接对象
            Connection conn = DriverManager.getConnection(url, user, password);
            
            //3、创建statement对象
            Statement statement = conn.createStatement();
            
            //4、执行Sql语句
            //拼接Sql语句
            String sql = "insert into dept values(" + id +",'" +name+ "','"+loc+"')";
            System.out.println(sql);
            count =  statement.executeUpdate(sql);
            
            //5、关闭对象
            statement.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return count;
    }
    
    /**
     * 使用Statement执行非查询语句
     * @param id
     * @return
     */
    static int delete2(int id){
        int count = 0;
        
        try {
            //1、加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            
            //2、获取连接对象
            Connection conn =  DriverManager.getConnection(url, user, password);
            
            //3、创建Statement对象
            Statement statement = conn.createStatement();
            
            //4、执行命令
            String sql = "delete from dept where deptNo=" + id;
            count = statement.executeUpdate(sql);
            
            //5、关闭资源
            statement.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return count;
    }
    
    /**
     * 按照ID删除部门信息
     *         使用preparedStatement对象进行实现
     * @param id
     */
    static int delete(int id){
        int count = 0;
        
        try {
            // 1、加载JDBC驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            
            // 2、创建数据库连接
            // jdbc:oracle:thin ：固定的，前缀，表示当前连接数据库的类型信息
            // localhost ：本地服务器的地址
            // 1521 ：数据库服务器的端口
            // oracle ：数据库实例的名称
            //String url = "jdbc:oracle:thin:@localhost:1521:oracle";
            //String user = "scott";
            //String password = "tiger";
            // DriverManager ：驱动管理器
            // getConnection() ：根据驱动信息可以获取到一个连接对象——Connection
            Connection conn = DriverManager.getConnection(url, user, password);

            // 3、获取Statement对象
            String sql = "delete from dept where deptNo =" + id;
            PreparedStatement statement = conn.prepareStatement(sql);

            // 4、发送Sql语句（执行Sql语句）
            // executeUpdate() ：将Sql语句发送给数据库服器（返回值：执行该语句后受影响的行数）
            count = statement.executeUpdate();
            
            // 5、关闭资源
            statement.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return count;
    }

}

demo2

package cn.sxt.jdbc.demo2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * "insert into dept values(" + id +",'" +name+ "','"+loc+"')"
 * Sql语句的生成方式是拼接Sql语句
 * 拼接Sql语句容易造成Sql注入攻击。注入攻击可能造成系统崩溃
 * 
 * 命令行参数
 *         意义
 *             1、有效的避免注入攻击，更加安全可靠
 * 
 *         实现
 *             PreparedStatement对象
 *                 方法
 *                     setXxxx(参数索引，值)
 *                 说明
 *                     setXxxx        ：设置指定类型的参数给PreparedStatement对象
 *                     参数索引        ：当前设置的参数的索引位置，从1开始
 *                     值            ：需要设置的参数之
 * @author Terry
 *
 */
public class Tester {
    static String url = "jdbc:oracle:thin:@localhost:1521:oracle";
    static String user = "scott";
    static String password = "tiger";

    /**
     * @param args
     */
    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        
        System.out.print("请输入部门ID：");
        int id = input.nextInt();
        
        System.out.print("请输入部门名称：");
        String name = input.next();
        
        System.out.print("请输入部门所在地：");
        String loc = input.next();
        
        int c = add(id, name, loc);
        System.out.println(c);
    }
    
    /**
     * 使用参数化的方式执行非查询语句
     * @param id
     * @param name
     * @param addr
     * @return
     */
    static int add(int id, String name ,String addr){    
        int count = 0;
        try {
            //1、加载驱动
            //通过反射加载对应的类
            Class.forName("oracle.jdbc.driver.OracleDriver");
            
            //2、获取连接对象            
            Connection con = DriverManager.getConnection(url, user, password);
            
            //3、创建Statement对象
            //？表示语句执行过程中所需要的参数
            String sql = "insert into dept values(?,?,?)";
            //带有参数的语句需要通过PreparedStatement对象才能执行
            PreparedStatement statement= con.prepareStatement(sql);
            
            //将参数传递给Statement对象
            //将int类型参数——id传递给Statement对象
            statement.setInt(1,id);
            statement.setString(2, name);
            statement.setString(3, addr);
            
            //4、执行Sql语句
            count = statement.executeUpdate();
            
            //5、关闭资源
            statement.close();
            con.close();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return count;
    }

}

demo3

工具类：可以实现执行查询语句和非查询语句

package cn.sxt.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 工具类
 *         可以实现执行查询语句和非查询语句
 * @author Terry
 *
 */
public class DBUtil {
    //数据库连接参数
    private static String url = "jdbc:oracle:thin:@localhost:1521:oracle";
    private static String user = "scott";
    private static String password = "tiger";
    
    //定义JDBC对象
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    
    /**
     * 加载驱动并初始化Connection对象
     */
    private void init(){        
        try {
            //加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            
            //获取连接对象
            con = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    /**
     * 执行非查询语句
     * eg：insert、delete及update
     * @param sql
     * @return        ：受影响行数
     */
    public int executeUpdate(String sql,Object...parameters){
        //初始化Connection对象
        init();
                
        try {
            //获取Prepared对象
            ps = con.prepareStatement(sql);
            
            //为PreparedStatement初始化参数
            for(int i = 1;i<=parameters.length;i++){
                //i-1    ：因为数组的下标是从0开始的。所以这里需要使用i-1
                ps.setObject(i, parameters[i-1]);
            }
            
            //执行Sql语句
            return ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally{
            close();
        }
        
        return -1;
    }
    
    /**
     * 执行查询语句
     * 注意：ResultSet（连接式的数据访问对象）使用过程中JDBC不能关闭
     * @param sql
     * @return        ：查询结果集
     */
    public ResultSet executeQuery(String sql,Object...parameters){
        //初始化Connection对象
        init();
                
        try {
            //获取PreparedStatement对象
            ps = con.prepareStatement(sql);
            
            //为PreparedStatement初始化参数
            for(int i = 1;i<=parameters.length;i++){
                //i-1    ：因为数组的下标是从0开始的。所以这里需要使用i-1
                ps.setObject(i, parameters[i-1]);
            }
            
            //执行语句
            return ps.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }        
        return null;
    }
    
    /**
     * 关闭所有的JDBC资源对象
     */
    public void close() {
        try {
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {
            if (con != null)
                con.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }    
    }
}

调用示例

package cn.sxt.jdbc.demo3;

import cn.sxt.util.*;

public class Tester {

    /**
     * @param args
     */
    public static void main(String[] args) {
        DBUtil util = new DBUtil();
        //util.executeUpdate("delete from Dept");
        util.executeUpdate("insert into dept values(?,?,?)", 91,"i'm2","tjj");
    }

}