Java的JDBC操作

Java的JDBC操作

1.JDBC入门

1.1.什么是JDBC

JDBC从物理结构上来说就是java语言访问数据库的一套接口集合，本质上是java语言根数据库之间的协议。JDBC提供一组类和接口，通过使用JDBC，开发人员可以使用java代码发送sql语句，来操作数据库

1.2.使用JDBC发送SQL的前提

登录数据库服务器（连接数据库服务器）需要有以下几项：

• 数据库的IP地址
• 端口
• 数据库用户名
• 密码
  
  java连接数据库代码示例：

    /**
    * 连接数据库的三种方式
    */
public class dbConnect {
    //连接数据库的URL,
    // JDBC协议：数据库子协议：//主机：端口/连接的数据库
    private String url="jdbc:mysql://localhost:3306/db";
    private String user="root";//用户名
    private String password = "root";//密码

    /**
     *  1,连接数据库方法1:
     * @throws SQLException
     */
    @Test
    public void connect1() throws SQLException {
        //1.连接的创建驱动程序类对象
        //com.mysql.jdbc.Driver()是mysql连接java的驱动，由mysql提供
        Driver driver=new com.mysql.jdbc.Driver();//新版本写发，推荐
//        Driver driver = new org.gjt.mm.mysql.Driver();//旧版本写法
        //设置用户名和密码
        Properties props = new Properties();
        props.setProperty("user",user);
        props.setProperty("password",password);

        //2.连接数据库,返回连接对象
        Connection conn = driver.connect(url,props);

        System.out.println(conn);
    }

    /**
     *  2.连接数据库方法2：
     *  使用驱动管理器来连接数据库
     */
    @Test
    public void connect2() throws SQLException {
        Driver driver = new com.mysql.jdbc.Driver();
        //1.注册驱动程序（可以注册多个驱动程序）
        //下面一行的代码在加载类的时候已经被执行了，不需要再写了
//        DriverManager.registerDriver(driver);

        //2.连接到数据库
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);
    }

    /**
     *  3.连接数据库方法3：
     *  推荐使用此方法来连接数据库
     */
    @Test
    public void connect3() throws ClassNotFoundException, SQLException {
        //通过字节码对象的方法加载静态代码块，从而注册驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //连接到具体的数据库
        Connection conn = DriverManager.getConnection(url,user,password);
        //打印判断是否连接成功
        System.out.println(conn);
    }
}

1.3.JDBC接口的核心API

JDBC的接口主要来源于旧版的java.sql.和新版的javax.sql.

Drive接口：表示java驱动程序接口。所有具体的数据库厂商要实现此接口

connect(url,properties):连接数据库的方法

• url语法:JDBC协议：数据库子协议：//主机：端口/数据库
• user:数据库的用户名
• password：数据库用户密码

DriveManager类：驱动管理器类，用于管理所有注册的驱动程序

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语句

ResultSet executeQuery(String sql);执行的静态查询sql语句

PreparedStatement接口：用于执行预编译sql语句，是Statement的子接口

int executeUpdate();执行预编译的更新sql语句

ResultSet executeQuery();执行预编译的查询sql语句

CallableStatement接口：用于执行存储过程sql语句，是PreparedStatement的子接口

Result executeQuery()；调用存储过程的方法

ResultSet接口：用于执行存储过程的sql语句

Result executeQuery()；调用存储过程的方法

对ResultSet结果获取数据的基本思想是：先指定行，然后获取该行上字段的值

ResultSet用于代表sql语句查询的结果，对结果进行封装，并使用游标来获取数据，在初始的时候，游标在第一行之前 ，调用ResultSet.next()方法，可以使游标指向具体的数据行，每调用一次向下调用一行，调用该方法获取该行的数据。

ResultSet是用于封装结果的，所以提供的主要是get方法，当使用next()方法指定到一行的时候，可以使用以下两种方式来获取指定行的字段值：

方法1：

通过索引来获取，索引从1开始，注意，并不是从0开始，

• getObject(int index);Object使用字段的数据类型来替换
  
  方法2：
  
  指定数据类型并带有字段的参数来获取，例如：
• getString("name");

常用的几个类型：

int getInt();

varchar getString()

date getdate()

bit getBoolean()

ResultSet还提供了对结果集进行定位行的方法：

• next();移动到下一行
• previous();移动到前一行
• absolute(int row);移动到指定行
• beforeFist();移动ResultSet的最前面
• afteLast()；移动到ResultSet的最最后

2.使用Statement执行SQL语句

当书库已经连接并准备好sql语句之后，就要将这个语句在数据库中执行，这个使用使用Statement对象，该对象主要提供两种方法，一种是

• executeUpdate(sql);该方法主要用于向数据库发送插入，修改，删除的命令，返回一个整数
• executeQuery()方法用于向数据库发送查询语句，用于查询数据，返回的是结果封装在ResultSet中，然后再从ResultSet中将数据打印出来

使用executeUpdate插入，修改，删除代码示例：

/**
 * 测试Statement接口
 */
public class StatementDemo {
    private String url = "jdbc:mysql://localhost:3306/db";
    private String user = "root";
    private String password = "root";
    /**
     *1.创建表
     */
    @Test
    public void TestCteate(){
        Connection connection=null;
        Statement statement=null;
        try {
            //1.驱动注册程序
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接对象
            connection = DriverManager.getConnection(url,user,password);
            //3.创建Statement对象
            statement=connection.createStatement();
            //4.准备sql
            String sql = "CREATE TABLE student (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),gender VARCHAR(2));";
            //5.发送sql语句，执行sql语句，并返回结果被影响的行数
            int count = statement.executeUpdate(sql);
            //6.输出
            System.out.println("影响了"+count+"行！");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //7.关闭连接(关闭顺序：后开发先关闭)
            if (statement!=null) try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (connection!=null) try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 插入数据
     * 修改数据
     * 删除数据
     */
    @Test
    public void TestInsert(){
        Connection connection=null;
        Statement statement=null;
        try {
            //1.驱动注册程序
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接对象
            connection = DriverManager.getConnection(url,user,password);
            //3.创建Statement对象
            statement=connection.createStatement();
            //4.准备sql
            //其他的修改，删除，只需要更改这里的sql即可
            //插入
//            String sql = "INSERT INTO student(NAME,gender) VALUES ('张梅','女');";
           //修改
//            String sql = "UPDATE student SET NAME='张三' WHERE id=2";
            //删除
            String sql = "DELETE FROM student WHERE id=2";
            //5.发送sql语句，执行sql语句，并返回结果被影响的行数
            int count = statement.executeUpdate(sql);
            //6.输出
            System.out.println("影响了"+count+"行！");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //7.关闭连接(关闭顺序：后开发先关闭)
            if (statement!=null) try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (connection!=null) try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

使用executeQuery查询数据代码示例：

注意其中的ResultSet方法

    /**
     * 查询数据
     */
    @Test
    public void TestFind(){
        Connection connection=null;
        Statement statement=null;
        try {
            //1.驱动注册程序
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接对象
            connection = DriverManager.getConnection(url,user,password);
            //3.创建Statement对象
            statement=connection.createStatement();
            //4.准备sql
            String sql = "SELECT * FROM student";
            //5.发送sql语句，执行sql语句，并返回查询结果
            ResultSet rs = statement.executeQuery(sql);
            //6.打印出数据
            //方法1：移动光标：
        /*
            boolean flag = rs.next();

			if(flag){
				//取出列值
                //方法1.1：以索引方式
				int id = rs.getInt(1);
				String name = rs.getString(2);
				String gender = rs.getString(3);
				System.out.println(id+","+name+","+gender);

                //方法1.2：以列名称
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String gender = rs.getString("gender");
				System.out.println(id+","+name+","+gender);

			}
        */
			//方法2：使用遍历方法
            //遍历结果
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                System.out.println(id+","+name+","+gender);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //7.关闭连接(关闭顺序：后开发先关闭)
            if (statement!=null) try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (connection!=null) try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

注意：

1.jdbc运行完以后一定要释放资源，特别是Connection对象，它是非常西游的资源，使用完要马上释放。

2.jdbc中的释放要后连接的先释放

3.为确保资源释放代码能够运行，此部分代码一定要放在finally语句中

3.使用PreparedStatement执行SQL语句

在Statement中，可以发现在连接数据库和释放资源方面有代码重复的部分，这个时候可以把重复的部分抽取出来，现在把连接部分和关闭部分的代码抽取放到jdbcUtil这个类中，有连接方法和关闭方法，代码示例如下：

/**
 * jdbc工具类
 * 数据库连接和资源释放
 */
public class jdbcUtil {
    private static String url="jdbc:mysql://localhost:3306/db";
    private static String user="root";
    private static String password="root";
    /**
     * 静态代码块(只加载一次)
     */
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动程序注册出错");
        }

    }
    /**
     * 抽取获取连接对象的方法
     */
    public static Connection getConnect(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url,user,password);
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /**
     * 释放资源的方法
     */
    public static void close(Connection conn, Statement stmt){
        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(Connection conn, Statement stmt, ResultSet rs){
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        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);
            }
        }
    }
}

重复的部分已经抽取了，现在使用抽取的部分来实现PreparedStatement接口的方法

PreparedStatement接口是执行预编译的sql语句的，最大的特点是，sql语句中的参数可以使用问号？来占用一个位置，然后再根据位置来设置sql里面的参数，最后发送到数据库执行

PreparedStatement的CURD方法分别使用代码表示如下：

1.插入

    /**
     * 插入数据
     */
    @Test
    public void TestInsert(){
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            //1.获取连接
            conn = jdbcUtil.getConnect();
            //2.准备预编译的sql
            String sql = "INSERT INTO student(NAME,gender) VALUES(?,?)";//问号？表示一个参数的占位符
            //3.执行编译sql语句（语法检查）
            pstmt = conn.prepareStatement(sql);
            //4.设置参数
            //参数的设置用两个参数，第一个指定是第几个问号，第二个是参数的值，参数位置从1开始
            pstmt.setString(1, "李四");
            pstmt.setString(2, "男");
            //5.发送参数，执行sql
            int count = pstmt.executeUpdate();//注意此处括号里不放sql
            System.out.println("影响了"+count+"行！");

        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            jdbcUtil.close(conn,pstmt);
        }
    }

2.修改

    /**
     * 更改数据
     */
    @Test
    public void testUpdate() {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            //1.获取连接
            conn = jdbcUtil.getConnect();

            //2.准备预编译的sql
            String sql = "UPDATE student SET NAME=? WHERE id=?"; //?表示一个参数的占位符

            //3.执行预编译sql语句(检查语法)
            stmt = conn.prepareStatement(sql);

            //4.设置参数值
            /**
             * 参数一： 参数位置  从1开始
             */
            stmt.setString(1, "王五");
            stmt.setInt(2, 9);

            //5.发送参数，执行sql
            int count = stmt.executeUpdate();

            System.out.println("影响了"+count+"行");

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.close(conn, stmt);
        }
    }

3.删除

    /**
     * 删除数据
     */
    @Test
    public void testDelete() {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            //1.获取连接
            conn = jdbcUtil.getConnect();

            //2.准备预编译的sql
            String sql = "DELETE FROM student WHERE id=?"; //?表示一个参数的占位符

            //3.执行预编译sql语句(检查语法)
            stmt = conn.prepareStatement(sql);

            //4.设置参数值
            /**
             * 参数一： 参数位置  从1开始
             */
            stmt.setInt(1, 9);

            //5.发送参数，执行sql
            int count = stmt.executeUpdate();

            System.out.println("影响了"+count+"行");

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.close(conn, stmt);
        }
    }

4.查询

    /**
     * 查询数据
     */
    @Test
    public void testQuery() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            //1.获取连接
            conn = jdbcUtil.getConnect();

            //2.准备预编译的sql
            String sql = "SELECT * FROM student";

            //3.预编译
            stmt = conn.prepareStatement(sql);

            //4.执行sql
            rs = stmt.executeQuery();

            //5.遍历rs
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                System.out.println(id+","+name+","+gender);
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            //关闭资源
            jdbcUtil.close(conn,stmt,rs);
        }
    }

PreparedStatement 和 Statment 区别：

1.语法不同：PreparedStatement可以使用预编译的sql，而Statement只能使用静态的sql

2.效率不同：PreparedStatement可以使用sql缓存区，效率比Statement高。Oracle是支持sql缓存区的，mysql是不支持缓存区的

3.安全性不同：PreparedStatement可以有效防止sql注入，而Statement不能防止sql注入

推荐使用PreparedStatement

4.CallableStatement执行存储过程

CallableStatement是PreparedStatement的子类，所以可以使用PreparedStatement的方法，即也可以使用编译sql，同时该类是用来执行存储过程的，所以两者之间是可以连在一起用的。

注意没有executeUpdate方法，所有调用存储过程都是使用executeQuery方法。

根据存储过程的不同，将代码分为两种：一个是只有出入参数的存储过程，一个是只有输出过程的存储过程

带有出入参数的存储过程

# 带有出入参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_findById(IN sid INT)
  BEGIN
    SELECT * FROM student WHERE id=sid;
  END $

CALL pro_findById(4);

带有输出参数的存储过程

# 带有输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_findById2(IN sid INT,OUT sname VARCHAR(20))
  BEGIN
    SELECT name INTO sname FROM student WHERE id=sid;
  END $

CALL pro_findById2(4,@name);

SELECT @name;

使用jdbc分别调用以上两种存储过程

/**
 *使用CallableStatement调用存储过程
 */
public class CallableStatementDemo {
    /**
     *调用带有输入参数的存储过程
     * CALL pro_findById(4);
     */
    @Test
    public void testIn(){
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

        try {
            //1.获取连接方法
            conn = jdbcUtil.getConnect();
            //2.准备sql
            String sql = "CALL pro_findById(?)";//可以执行预编译sql
            //3.预编译
            cstmt=conn.prepareCall(sql);
            //4.设置输入参数
            cstmt.setInt(1,4);
            //5.发送参数
            rs = cstmt.executeQuery();//注意：所有调用存储过程的sql语句都是使用executeQuery方法
            //遍历结果
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                System.out.println(id+","+name+","+gender);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            jdbcUtil.close(conn,cstmt,rs);
        }

    }

    /**
     * 执行带有输出参数的存储过程
     * CALL pro_findById2(4,@NAME);
     */
    @Test
    public void TestOut(){
        Connection conn =null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

        try {
            //1.获取连接
            conn = jdbcUtil.getConnect();
            //2.准备sql
            String sql = "CALL pro_findById2(?,?)";//第一个？是输入参数，第二个？是输出参数
            //3.预编译
            cstmt = conn.prepareCall(sql);
            //4.设置输入参数
            cstmt.setInt(1,4);
            //5.设置输出参数（注册输出参数）
            /**
             * 参数一：参数位置
             * 参数二：存储过程中的输出参数的jdbc类型
             */
            cstmt.registerOutParameter(2, Types.VARCHAR);
            //6.发送参数，执行
            cstmt.executeQuery();//结果不是返回到结果集中，而是返回到输出参数中
            //7.得到输出参数的值
            /**
             * 索引值：预编译sql中的输出参数的位置
             */
            String result = cstmt.getString(2);//getXX方法专门用于获取存储过程中的输出参数

            System.out.println(result);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            jdbcUtil.close(conn,cstmt,rs);
        }

    }
}

5.类路径的读取和jdbcUtil的配置文件

在jdbcUtil类中，我们把数据库，驱动，用户名，密码写死了，以后想要修改就很麻烦，这种情况，我们会把配置文件再次抽取出来，创建一个db.properties文件来保存一些配置文件。需要输入信息，在这个文件里面配置就可以了

首先，配置文件是这样的：

url=jdbc:mysql://localhost:3306/db
user=root
password=123456
driverClass=com.mysql.jdbc.Driver

然后，在jdbcUtil类中调用：

/**
 * jdbc工具类
 * 数据库连接和资源释放
 */
public class jdbcUtil {
    private static String url=null;
    private static String user=null;
    private static String password=null;
    private static String driverClass=null;
    /**
     * 静态代码块(只加载一次)
     */
    static {
        try {
            //读取db.properties
            Properties props = new Properties();

            /**
             * 使用类路径的读取方法
             */
            /**
             *  . :代表java命令运行的目录
             *  在java项目下：点 . java命令的运行目录从项目的根目录开始
             *  在web项目下： 点 . java命令的运行目录从tomcat/bin目录开始
             *  使用点.来指定目录会产生在java 项目和javaweb项目中不适用的情况
             */
            /**
             * /:斜杠表示classpath的根目录
             * 在java项目下，classpath的根目录从bin目录开始
             * 在web项目下，classpath的根目录从WEB-INF/classes目录开始。
             * 而db.properties就在/目录下
             * 一般都是使用/来指定按文件名字
             */
            InputStream in = jdbcUtil.class.getResourceAsStream("/db.properties");
            //加载文件
            props.load(in);
            //读取信息
            url=props.getProperty("url");
            user=props.getProperty("user");
            password=props.getProperty("password");
            driverClass=props.getProperty("driverClass");

            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动程序注册出错");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    /**
     * 抽取获取连接对象的方法
     */
    public static Connection getConnect(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url,user,password);
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /**
     * 释放资源的方法
     */
    public static void close(Connection conn, Statement stmt){
        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(Connection conn, Statement stmt, ResultSet rs){
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        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);
            }
        }
    }
}

其中比较重要的就是文件路径的查找，因为在java项目和javaweb项目中的文件路径是不一样的，这个时候就可以使用类路径来查找。java中也通常使用点和斜杠来指定路径，但是指定的路径是不一样的：

点指定路径：

. :代表java命令运行的目录

在java项目下：点 . java命令的运行目录从项目的根目录开始

在web项目下： 点 . java命令的运行目录从tomcat/bin目录开始

使用点.来指定目录会产生在java 项目和javaweb项目中不适用的情况

斜杠指定路径：

/:斜杠表示classpath的根目录

在java项目下，classpath的根目录从bin目录开始

在web项目下，classpath的根目录从WEB-INF/classes目录开始。

而db.properties就在/目录下

一般都是使用/来指定按文件名字