【JDBC】JDBC的使用(数据库的增删改查询)

这里写目录标题

1._ JDBC

JDBC（Java DataBase Connectivity，Java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问。

简单说就是用Java语言来操作数据库。原来我们操作数据库是在控制台使用SQL语句来操作数据库，JDBC是用Java语言向数据库发送SQL语句。

JDBC的原理

早期SUN公司的天才们想编写一套可以连接天下所有数据库的API，但是当他们刚刚开始时就发现这是不可完成的任务，因为各个厂商的数据库服务器差异太大了。后来SUN开始与数据库厂商们讨论，最终得出的结论是，由SUN提供一套访问数据库的规范（就是一组接口），并提供连接数据库的协议标准，然后各个数据库厂商会遵循SUN的规范提供一套访问自己公司的数据库服务器的API实现。SUN提供的规范命名为JDBC，而各个厂商提供的，遵循了JDBC规范的，可以访问自己数据库的API被称之为驱动。

JDBC是接口，而JDBC驱动才是接口的实现，没有驱动无法完成数据库连接！每个数据库厂商都有自己的驱动，用来连接自己公司的数据库。

JDBC – Java官方提供 – 一系列接口 – 规范

驱动 – 数据库厂商提供 – JDBC接口的实现类 – 实现、

JDBC的基本使用和 PreparedStatement （防止sql注入）

import java.sql.*;

public class TestJdbc {
        public static void main(String[] args) throws ClassNotFoundException, SQLException {
            //驱动名
            String driverName = "com.mysql.jdbc.Driver";
            //连接数据库的url
            String url = "jdbc:mysql://localhost:3306/test?useSSL=false";
            //用户名
            String username = "root";
            //密码
            String password = "root";

            //加载驱动
            Class.forName(driverName);

            //SQL语句
            String sql = "SELECT * FROM user WHERE id=" + 1;
            String sql1 = "SELECT * FROM user WHERE id=  ? ";
            //建立连接
            Connection connection = DriverManager.getConnection(url, username, password);


            // 第一种方式 常规方执行sql语句
            //Statement statement = connection.createStatement();
            发送SQL语句
             增、删、改使用executeUpdate => int i = statement.executeUpdate(sql);
             查询使用executeQuery
            //ResultSet resultSet = statement.executeQuery(sql);

            // 第二种方式
            // PreparedStatement => 防止sql注入
            PreparedStatement statement = connection.prepareStatement(sql1);
            statement.setInt(1,3);  // 设置第一个参数的值是3 
            ResultSet resultSet = statement.executeQuery();

            //处理结果
            while(resultSet.next()) {
                int id  = resultSet.getInt("id");
                String name = resultSet.getString("username");

                System.out.println(id);
                System.out.println(name);
            }

            //释放资源
            resultSet.close();  // 如果不是查询就没有这个
            statement.close();
            connection.close();
        }
}

2._ 连接池

连接池是一种管理连接的技术。

用连接池来管理Connection，可以重复使用连接。有了连接池，我们就不用自己来创建Connection，而是通过池来获取Connection对象。当使用完Connection后，调用Connection的close()方法也不会真的关闭Connection，而是把Connection归还给池。连接池就可以再利用这个Connection对象了。

Java为数据库连接池提供了公共的接口：javax.sql.DataSource，各个厂商可以让自己的连接池实现这个接口。这样应用程序可以方便的切换不同厂商的连接池。

其实所有的池都可以理解为一个概念：将实现某种功能的对象开始就创建多份，就比如一滴水就是一个对象，很多水就成了池。

Druid连接池

Druid是阿里巴巴开源的一个数据源，主要用于java数据库连接池，相比spring推荐的DBCP和hibernate推荐的C3P0、Proxool数据库连接池，Druid在市场上占有绝对的优势。

所需资源：

  1. 将MySQL驱动jar包
  2. Druid jar包拷贝到lib目录下；
public class Test01 {
    public static void main(String[] args) throws SQLException {
        //创建Druid连接池对象
        DruidDataSource dataSource = new DruidDataSource();

        //为Druid连接池设置参数
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/db_test?useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        //初始化连接数量
        dataSource.setInitialSize(10);
        //最大连接数量
        dataSource.setMaxActive(100);
        //最小空闲连接
        dataSource.setMinIdle(5);
        // 打印出druid连接池的各种信息
        System.out.println(dataSource);

        //从连接池中获取连接
        Connection connection = dataSource.getConnection();
        //获取PreparedStatement
        PreparedStatement statement = connection.prepareStatement("INSERT INTO user(id,username) VALUES(?, ?)");
        //设置参数
        statement.setInt(1, 30);   // 设置第一个参数的值为30
        statement.setString(2, "张三");

        //发送SQL语句
        int i = statement.executeUpdate();   // 返回受影响的行数
        System.out.println(i);
        
        //归还连接
        connection.close();
    }
}

3._ 事务

一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务要处理的问题：把多个对数据库的操作绑定成一个事务，要么都成功，要么都失败。

原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

JDBC处理事务的代码格式：

try {
  con.setAutoCommit(false); //如果true（默认值就是true）表示自动提交，`con.setAutoCommit(false)`表示开启事务；
  //多个JDBC操作
  
  con.commit();//try的最后提交事务
} catch(Exception e) {  // 注意异常类型
  con.rollback();//回滚事务
}

对应的mysql操作

# 1,开启事务
START TRANSACTION;

# 多个SQL操作

# 提交事务
COMMIT;
# 回滚
ROLLBACK;

测试

package com.test;


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

public class Test01 {
    /**
     * 转账
     * 使用传统的JDBC方式进行转账操作
     * */

    public static void main(String[] args) {
        //  创建连接
        Connection conn = null;
        try {
            // 加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 建立连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_test?useSSL=false", "root", "root");
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动失败");
            e.printStackTrace();

        } catch (SQLException throwables) {
            System.out.println("建立连接失败");
            throwables.printStackTrace();

        }

        // 预处理命令/语句
        PreparedStatement preparedStatement = null;
        try {
            // 开启事务
            conn.setAutoCommit(false); //禁止自动提叫

            /* 这里代表sql片段开始，里面写一条或者多条数据 */
            // 准备发送语句
            String sql1 = "update account set money=money-? where username=?";
            String sql2 = "update account set money=money+? where username=?";

            // 操作sql片段1
            preparedStatement = conn.prepareStatement(sql1);
            //设置参数
            preparedStatement.setDouble(1, 500);
            preparedStatement.setString(2, "张三");
            // 发送sql
            preparedStatement.executeUpdate();

            //    //用来测试回滚
            //    //int i = 100/0;

            // 操作sql片段2
            preparedStatement = conn.prepareStatement(sql2);
            //设置参数
            preparedStatement.setDouble(1, 500);
            preparedStatement.setString(2, "李四");
            // 发送sql
            preparedStatement.executeUpdate();
            /* 这里代表sql片段结束 */

            //提交事务
            conn.commit();

        } catch (Exception throwables) {
            throwables.printStackTrace();
            try {
                //回滚事务,回滚是,异常务必大一些
                conn.rollback();
                System.out.println("事务回滚");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } finally {
            // 释放资源
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

4._jdbc自定义工具类

在src下创建jdbc.properties文件，内容如下

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydbjdbc?useSSL=false
username=root
password=root
#初始化连接数量
initialSize=10
#最大连接数量
maxActive=50
#最小空闲连接
minIdle=5

工具类代码如下

ThreadLocal类只有三个方法：

  • void set(T value)：保存值；
  • T get()：获取值；
  • void remove()：移除值。

ThreadLocal内部其实是个Map来保存数据。虽然在使用ThreadLocal时只给出了值，没有给出键，其实它内部使用了当前线程做为键。

数据:

  1. druid-1.1.10.jar
  2. mysql-connector-java-5.1.47.jar

需要注意的是,在这种情况下,无法使用测试类

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4NkDzqcm-1635562885281)(asset/img/01_jdbc/image-20211016122441707.png)]

工具类：

package com.util;


import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtils {
    private static DataSource dataSource;
    private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

    static {
        try {
            Properties prop = new Properties();
            InputStream in = JdbcUtils.class.getResourceAsStream("/jdbc.properties");
            prop.load(in);

            // 使用map方式
            //HashMap<String, Object> prop = new HashMap<>();
            //prop.put("driverClassName", "com.mysql.jdbc.Driver");
            //prop.put("url", "jdbc:mysql://localhost:3306/db_test?useSSL=false");
            //prop.put("username", "root");
            //prop.put("password", "root");
            //
            //prop.put("initialSize", "10");
            //prop.put("maxActive", "50");
            //prop.put("minIdle", "5");

            //创建DataSource
            dataSource = DruidDataSourceFactory.createDataSource(prop);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        Connection connection = tl.get();

        if(connection != null) {
            return connection;
        }
        return dataSource.getConnection();
    }

    //开启事务
    public static void beginTransaction() throws SQLException {
        Connection connection = tl.get();
        if(connection != null) {
            throw new SQLException("事务已经开启，在没有结束当前事务时，不能再开启事务！");
        }

        connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        tl.set(connection);
    }

    //提交事务
    public static void commitTransaction() throws SQLException {
        Connection connection = tl.get();
        if(connection == null) {
            throw new SQLException("当前没有事务，所以不能提交事务！");
        }

        connection.commit();
        connection.close();
        tl.remove();
    }

    //回滚事务
    public static void rollbackTransaction() throws SQLException {
        Connection connection = tl.get();
        if(connection == null) {
            throw new SQLException("当前没有事务，所以不能回滚事务！");
        }

        connection.rollback();
        connection.close();
        tl.remove();
    }

    //释放资源
    public static void close(Connection connection, Statement statement, ResultSet rSet) {
        try {
            if(rSet != null) {
                rSet.close();
            }

            if(statement != null) {
                statement.close();
            }

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

使用:创建测试类

       try {
          JdbcUtils.beginTransaction();//开启事务
          dao.daoMethod1(…);//操作1
          dao.daoMethod2(…); //操作2
          JdbcUtils.commitTransaction();//提交事务
        } catch(Exception e) {
           JdbcUtils.rollbackTransaction();//回滚
        }

package com.test;

import com.util.JdbcUtils;

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


// 测试工具类
public class Test03 {
    public static void main(String[] args) throws SQLException {
        // 测试增删改
        //test_update();
        //
         测试查询
        //test_query();

        // 测试事务
        test_transaction();

    }

    private static void test_update() throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        System.out.println(conn);
        PreparedStatement preparedStatement = conn.prepareStatement("insert into user(username,password) values(?,?)");
        preparedStatement.setString(1, "tom");
        preparedStatement.setString(2, "123");

        int i = preparedStatement.executeUpdate();
        System.out.println(i);
    }

    private static void test_query() throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        System.out.println(conn);
        PreparedStatement preparedStatement = conn.prepareStatement("select * from user");

        ResultSet res = preparedStatement.executeQuery();
        while (res.next()) {
            int id = res.getInt("id");
            String username = res.getString("username");
            String password = res.getString("password");
            System.out.println(id + "-" + username + "-" + password);
        }
    }

    private static void test_transaction() throws SQLException {
        try {
            //开启事务
            JdbcUtils.beginTransaction();

            //转账操作
            jia("张三",500D);
            // 测试异常
            //int i = 1 / 0;
            jian("李四",500D);
            
            //提交事务
            JdbcUtils.commitTransaction();
        } catch (Exception throwables) {
            throwables.printStackTrace();
            try {
                //回滚事务
                System.out.println("事务回滚");
                JdbcUtils.rollbackTransaction();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void jia(String name,Double money) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        // 操作sql片段1
        PreparedStatement   preparedStatement = conn.prepareStatement("update account set money=money+? where username=?");
        //设置参数
        preparedStatement.setDouble(1, money);
        preparedStatement.setString(2, name);
        // 发送sql
        preparedStatement.executeUpdate();
    }
    private static void jian(String name,Double money) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        // 操作sql片段2
        PreparedStatement   preparedStatement = conn.prepareStatement("update account set money=money-? where username=?");
        //设置参数
        preparedStatement.setDouble(1, money);
        preparedStatement.setString(2, name);
        // 发送sql
        preparedStatement.executeUpdate();
    }

}

4._ Commons_DbUtils

DBUtils是Apache Commons组件中的一员，开源免费。是对JDBC的简单封装，但是它还是被很多公司使用。

主要功能：用来操作数据库，简化JDBC的操作。

在使用的时候要和数据库连接池、MySQL的jar包配合使用。

主要类及方法

QueryRunner:执行sql语句的类

  • 创建QueryRunner
    • 构造器：QueryRunner() ，在事务里面使用；
    • 构造器：QueryRunner(连接池对象)
  • update()：执行INSERT、UPDATE、DELETE
  • query()：执行SELECT

关于增删改

int update(String sql, Object… params) --> 可执行增、删、改语句

int update(Connection con, String sql, Object… parmas) --> 需要调用者提供Connection，支持事务

关于查询

T query(String sql, ResultSetHandler rsh, Object… params) --> 可执行查询

T query(Connection con, String sql, ResultSetHadler rsh, Object… params) --> 需要调用者提供Connection，支持事务

resultsethandler接口

BeanHandler(单行) --> 构造器需要一个Class类型的参数，用来把一行结果转换成指定类型的javaBean对象;

BeanListHandler(多行) --> 构造器也是需要一个Class类型的参数，用来把一行结果集转换成一个javabean，那么多行就是转换成List对象，一堆javabean；

MapHandler(单行) --> 把一行结果集转换Map对象；

MapListHandler(多行) --> 把一行记录转换成一个Map，多行就是多个Map，即List；

ScalarHandler(单行单列) --> 通常用与select count(*) from t_stu语句！结果集是单行单列的！它返回一个Object 聚合函数。

资源：将MySQL驱动Jar包、Druid连接池Jar包、DbUtils的Jar包拷贝到lib目录下；

dbutils使用

  1. 新建Java项目；
  2. 在项目下新建lib目录；
  3. 将MySQL驱动Jar包、Druid连接池Jar包、DbUtils的Jar包拷贝到lib目录下；
  4. 选中lib目录右键Add as Library–单击OK;
  5. 创建JdbcUtils工具类拷贝到项目中，并增加如下的方法。
import com.qfedu.entity.Student;
import com.qfedu.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;

public class MyTest {
    private DataSource dataSource = JdbcUtils.getDataSource();

    //测试添加，修改，删除同理
    @Test
    public void testAdd() {
        //创建QueryRunner
        QueryRunner qr = new QueryRunner(dataSource);
        //SQL
        String sql = "INSERT INTO tb_stu(sname, sage, sgender) VALUES(?, ?, ?)";
        int result = 0;
        //参数
        Object[] params = {"zhangsan", 12, "male"};
        //操作--增
        try {
            result = qr.update(sql, params);  // 执行INSERT、UPDATE、DELETE
            System.out.println(result);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //测试查询
    @Test
    public void testSelect1() {
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "SELECT * FROM tb_stu WHERE sid=?";
        Student stu = null;
        Object[] params = {1};
        try {
            stu = qr.query(sql, new BeanHandler<Student>(Student.class), params); // 执行SELECT
            System.out.println(stu);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //测试查询
    @Test
    public void testSelect2() {
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "SELECT * FROM tb_stu";
        List<Student> list = null;
        try {
            list =  qr.query(sql, new BeanListHandler<Student>(Student.class));
            for (Student student : list) {
                System.out.println(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //测试查询
    @Test
    public void testSelect3() {
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "SELECT count(*) FROM tb_stu";

        long count = 0;

        try {
            count = qr.query(sql, new ScalarHandler<Long>());
            System.out.println(count);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

事务

dao代码

import com.qfedu.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;

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

public class AccountDao {
    /*
     * 提款withdrawal
     * */
    public void withdrawal(String cardNum, double money) throws SQLException {
        String sql = "update account set money=money-? where cardNum=?";

        Object[] params = {money, cardNum};

        QueryRunner qr = new QueryRunner();
        qr.update(JdbcUtils.getConnection(), sql, params);
    }

    /*
     * 存款deposit
     * */
    public void deposit(String cardNum, double money) throws SQLException {
        String sql = "update account set money=money+? where cardNum=?";

        Object[] params = {money, cardNum};

        QueryRunner qr = new QueryRunner();
        qr.update(JdbcUtils.getConnection(), sql, params);
    }
}

service代码

import com.qfedu.dao.AccountDao;
import com.qfedu.utils.JdbcUtils;

import java.sql.SQLException;

public class AccountService {
    private AccountDao accountDao = new AccountDao();

    //转账操作
    public void trans(String src, String dst, double money) {

        try {
            JdbcUtils.beginTransaction();
            accountDao.withdrawal(src, money);

            //int i = 100/0;

            accountDao.deposit(dst, money);
            JdbcUtils.commitTransaction();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                JdbcUtils.rollbackTransaction();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试代码

@Test
public void testTransaction() {
    AccountService accountService = new AccountService();

    accountService.trans("10001", "10002", 1000);
}
posted @   coderwcb  阅读(130)  评论(0)  编辑  收藏  举报
相关博文：
· 【mysql】基础知识总结
· Java基础知识面试题2021
· JDBC_SE
· 03-JDBC
· 数据库开发基础（JDBC）
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！
点击右上角即可分享
微信分享提示