JDBC的初步认识

JDBC是什么？JDBC英文名为：Java Data Base Connectivity(Java数据库连接)，数据库是由不同生产产商决定的，例如Mysql、Oracle、SQL Server,JAVA JDK不可能提供对不同数据库的实现，因此，它就提供了JDBC的接口API，具体的实现由不同的生产产商决定。这样，数据库生产产商都根据JAVA API去实现各自的应用驱动，这问题就迎刃而解了

常用接口

• DriverManager：这个类管理数据库驱动程序的列表，查看加载的驱动是否符合JAVA Driver API的规范

• Connection：与数据库中的连接对象，通过该对象与数据库进行通信

• Statement：把创建的SQL对象，转而存储到数据库当中

• ResultSet：它是一个迭代器，用于检索查询数据

JDBC的使用步骤

import cn.kgc.jdbc.entity.User;
import com.mysql.jdbc.Driver;

import java.sql.*;
import java.util.ArrayList;

public class JDBCDemo01 {

    /*
       java操作数据库的基本步骤
       1. 引入依赖  注册驱动
       2.获取连接对象    如何获取数据库的连接？
       3.获取发送sql语句的对象  发送sql语句
       4.处理结果集
       5.释放资源
    * */
    public static void main(String[] args) throws SQLException {
        // 创建驱动对象
        Driver driver = new Driver();
        // 注册驱动
        DriverManager.registerDriver(driver);

        /**
         *  获取连接  url   user  password    http://www.baidu.com/login.html
         *  url:  数据库的通信地址  jdbc:mysql://localhost:3306/java2216
         *  user：  数据的用户名  root
         *  password：  用户密码  
         */
        String url = "jdbc:mysql://localhost:3306/java2216?useSSL=false";
        String username = "root";
        String password = "***";
        //获取数据库的连接
        Connection connection = DriverManager.getConnection(url, username, password);
        System.out.println("connection = " + connection);

        // 通过连接获取发送sql语句的对象  Statement
        Statement statement = connection.createStatement();
        //编写sql
        //String sql = "SELECT  *  FROM  user   WHERE id = 1";
        String sql = "SELECT  *  FROM  user ";
        // 发送sql语句到mysql数据库
        ResultSet resultSet = statement.executeQuery(sql);
        // 处理结果集  resultSet  resultSet.next()  查询数据结果集中是否还有下一条数据
        // 创建集合 存储查询结果中封装的数据
        ArrayList<User> users = new ArrayList<>();
        while (resultSet.next()){
            //获取当前记录中字段值  根据字段值的数据类型获取
            int id = resultSet.getInt("id");  //  通过字段名获取
            String uname = resultSet.getString(2);  //  通过字段的列索引
            String pwd = resultSet.getString("password");
            User user = new User().setId(id).setPassword(pwd).setUsername(uname);
            users.add(user);
            //System.out.println("user = " + user);
        }

        // 遍历集合
        //users.forEach(user-> System.out.println("user = " + user));

        for (User user : users) {
            System.out.println(user);
        }

        // 释放资源
        resultSet.close();
        statement.close();
        connection.close();

    }

}

 测试JDBC

package cn.kgc.jdbc;

import com.mysql.jdbc.Driver;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TestJDBC {

    // jdbc 添加操作
    @Test
    public void  addUser(){
        // jdbc 6步
        Connection connection =null;
        Statement statement = null;
        try {

            Driver driver = new Driver();
            DriverManager.registerDriver(driver);

            String url = "jdbc:mysql://localhost:3306/java2216?useSSL=false";
            String username = "root";
            String password = "***";

            connection = DriverManager.getConnection(url, username, password);

            statement = connection.createStatement();
            String sql = "insert into user(username,password) values('tom','12345')";

            int i = statement.executeUpdate(sql);
            System.out.println(i>0?"ok":"error");


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            // 释放资源
            try {
                if (statement!=null){  //  避免空指针异常
                    statement.close();
                }

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

            try {
                if ( connection!=null){  //  避免空指针异常
                    connection.close();
                }

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }

    // jdbc 删除操作
    @Test
    public void  deleteUser(){
        // jdbc 6步
        Connection connection =null;
        Statement statement = null;
        try {

            Driver driver = new Driver();
            DriverManager.registerDriver(driver);

            String url = "jdbc:mysql://localhost:3306/java2216?useSSL=false";
            String username = "root";
            String password = "***";

            connection = DriverManager.getConnection(url, username, password);

            statement = connection.createStatement();
            String sql = " delete from user where id = 1";

            int i = statement.executeUpdate(sql);
            System.out.println(i>0?"ok":"error");


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            // 释放资源
            try {
                if (statement!=null){  //  避免空指针异常
                    statement.close();
                }

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

            try {
                if ( connection!=null){  //  避免空指针异常
                    connection.close();
                }

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }


    }

    // jdbc 修改操作
    @Test
    public void  updateUser(){
        // jdbc 6步
        Connection connection =null;
        Statement statement = null;
        try {

            Driver driver = new Driver();
            DriverManager.registerDriver(driver);

            String url = "jdbc:mysql://localhost:3306/java2216?useSSL=false";
            String username = "root";
            String password = "***";

            connection = DriverManager.getConnection(url, username, password);

            statement = connection.createStatement();
            String sql = "update user  set password = '8888' where id = 2";

            int i = statement.executeUpdate(sql);
            System.out.println(i>0?"ok":"error");


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            // 释放资源
            try {
                if (statement!=null){  //  避免空指针异常
                    statement.close();
                }

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

            try {
                if ( connection!=null){  //  避免空指针异常
                    connection.close();
                }

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
    }
}

JDBC工具类

在IDEA中建立resource资源建立jdbc.properties文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/java2216?useSSL=false
username=root
password=***

使用JDBC的步骤中连接数据库和释放资源重复性使用，可以提取出来单独设立JDBCUtil工具类

import com.mysql.jdbc.Driver;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ResourceBundle;

public class JDBCUtil {

    private JDBCUtil(){}

    private static String username;
    private static String driverClassName;
    private static String password;
    private static String url;

    static {  //  执行一次 类加载时

        ResourceBundle file = ResourceBundle.getBundle("jdbc");
        driverClassName = file.getString("driverClassName");
        url = file.getString("url");
        username = file.getString("username");
        password = file.getString("password");
    }

    //获取数据库连接的方法
    public  static Connection  getConnection(){
        //通过反射注册驱动
        Connection connection = null;
        try {
            Class.forName(driverClassName);
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    //释放资源
    public static  void  closeAll(Connection connection, Statement statement, ResultSet resultSet){

        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(statement!=null){

            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(connection!=null){

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

package cn.kgc.jdbc;

import cn.kgc.jdbc.util.JDBCUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;

public class TestJDBCUtil {

    @Test
    public void test1(){  //  测试工具类获取连接的方法

        Connection connection = JDBCUtil.getConnection();
        System.out.println("connection = " + connection);

    }
}