JDBC（du）

钟佩颖

已于 2024-08-03 14:18:28 修改

阅读量347

点赞数 13

文章标签： java 大数据数据库 java-ee

于 2024-08-02 17:30:48 首次发布

本文链接：https://blog.csdn.net/weixin_63627378/article/details/140876069

版权

JDBC简介

JDBC原理

接口在开发中的作用

编写程序模拟JDBC接口

JDBC编程六步

package aaa;

import java.sql.*;

public class a {
    public static void main(String[] args) throws SQLException {
        //注册驱动
        Driver driver = new com.mysql.cj.jdbc.Driver();//创建核心驱动对象
        DriverManager.registerDriver(driver);//注册驱动

        //获取连接
        String url="jdbc:mysql://localhost:3306/jdbc";
        String user ="root";
        String password ="123456";
        Connection connection = DriverManager.getConnection(url, user, password);

//        System.out.println(connection);
        //获取数据库操作对象
        Statement statement = connection.createStatement();
        Statement statement1 = connection.createStatement();
//        System.out.println(statement);
//        System.out.println(statement1);
        String sql="insert into t_user (id,name,password,realname,gender,tel) values ( '5','wangw','123','王五','女','15323734374')";
//        boolean execute = statement1.execute(sql);
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        connection.close();
        statement.close();



    }

}

JDBC连接MySQL时的URL格式

MySQL URL中的其它常用配置

注册驱动的常用方式

动态配置连接数据库的信息

Java中读取properties配置文件的八种方式总结_properties如何获取其中某个对象-CSDN博客

package aaa;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import java.util.ResourceBundle;

public class a {
    public static void main(String[] args) throws SQLException, IOException {
        //注册驱动
//        Driver driver = new com.mysql.cj.jdbc.Driver();//创建核心驱动对象
//        DriverManager.registerDriver(driver);//注册驱动

        //获取连接
//        String url="jdbc:mysql://localhost:3306/jdbc";
//        String user ="root";
//        String password ="123456";

//        ResourceBundle bundle = ResourceBundle.getBundle("aaa.jdbc");
//        ResourceBundle bundle = ResourceBundle.getBundle("aaa.jdbc");
       // InputStream fileInputStream = new FileInputStream("D:\\IEDAFile\\JavaWeb\\jdbc\\src\\main\\java\\aaa\\jdbc.properties");
        InputStream fileInputStream = new FileInputStream("jdbc/src/main/java/aaa/jdbc.properties");

        Properties properties = new Properties();
        properties.load(fileInputStream);
//        properties.list(System.out);



//
//        String driver = bundle.getString("driver");
//        String url = bundle.getString("url");
//        String user = bundle.getString("username");
//        String password = bundle.getString("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user =properties.getProperty("username");
        String password = properties.getProperty("password");

        Connection connection = DriverManager.getConnection(url, user, password);

//        System.out.println(connection);
        //获取数据库操作对象
        Statement statement = connection.createStatement();
//        Statement statement1 = connection.createStatement();
//        System.out.println(statement);
        System.out.println(statement1);
        String sql="insert into t_user (id,name,password,realname,gender,tel) values ( '9','wangw','123','王五','女','15323734374')";
       // boolean execute = statement.execute(sql);
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        connection.close();
        statement.close();



    }

}

获取连接的其他方式(了解)

getConnection(String url)

getConnection(String url, Properties info)

JDBC的查询操作

通过列索引获取数据（以String类型获取）

通过列名获取数据（以String类型获取）

package aaa;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import java.util.ResourceBundle;

public class a {
    public static void main(String[] args) throws SQLException, IOException {
        //注册驱动
//        Driver driver = new com.mysql.cj.jdbc.Driver();//创建核心驱动对象
//        DriverManager.registerDriver(driver);//注册驱动

        //获取连接
//        String url="jdbc:mysql://localhost:3306/jdbc";
//        String user ="root";
//        String password ="123456";

//        ResourceBundle bundle = ResourceBundle.getBundle("aaa.jdbc");
//        ResourceBundle bundle = ResourceBundle.getBundle("aaa.jdbc");
       // InputStream fileInputStream = new FileInputStream("D:\\IEDAFile\\JavaWeb\\jdbc\\src\\main\\java\\aaa\\jdbc.properties");
        InputStream fileInputStream = new FileInputStream("jdbc/src/main/java/aaa/jdbc.properties");

        Properties properties = new Properties();
        properties.load(fileInputStream);
//        properties.list(System.out);



//
//        String driver = bundle.getString("driver");
//        String url = bundle.getString("url");
//        String user = bundle.getString("username");
//        String password = bundle.getString("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user =properties.getProperty("username");
        String password = properties.getProperty("password");

        Connection connection = DriverManager.getConnection(url, user, password);

//        System.out.println(connection);
        //获取数据库操作对象
        Statement statement = connection.createStatement();
//        Statement statement1 = connection.createStatement();
//        System.out.println(statement);
        System.out.println(statement1);
        //String sql="insert into t_user (id,name,password,realname,gender,tel) values ( '10','wangw','123','王五','女','15323734374')";
       // boolean execute = statement.execute(sql);
//        int i = statement.executeUpdate(sql);
        String sql="select * from t_user";
        ResultSet resultSet = statement.executeQuery(sql);
//        System.out.println(i);
        while(resultSet.next()){
            System.out.println(resultSet.getString(1));
            System.out.println(resultSet.getString(2));
            System.out.println(resultSet.getString(3));
            System.out.println(resultSet.getString(4));
            System.out.println("---------------------------");

//            System.out.println(resultSet.getString("id"));
//            System.out.println(resultSet.getString("name"));
//            System.out.println(resultSet.getString("password"));
//            System.out.println(resultSet.getString("realname"));
//            System.out.println(resultSet.getString("gender"));
//            System.out.println(resultSet.getString("tel"));
//            System.out.println("------------------------------------------");

        }
        resultSet.close();
        statement.close();
        connection.close();




    }

}

以指定的类型获取数据

获取结果集的元数据信息（了解）

获取新增行的主键值

package aaa;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import java.util.ResourceBundle;

public class a {
    public static void main(String[] args) throws SQLException, IOException {
        //注册驱动
//        Driver driver = new com.mysql.cj.jdbc.Driver();//创建核心驱动对象
//        DriverManager.registerDriver(driver);//注册驱动

        //获取连接
//        String url="jdbc:mysql://localhost:3306/jdbc";
//        String user ="root";
//        String password ="123456";

//        ResourceBundle bundle = ResourceBundle.getBundle("aaa.jdbc");
//        ResourceBundle bundle = ResourceBundle.getBundle("aaa.jdbc");
       // InputStream fileInputStream = new FileInputStream("D:\\IEDAFile\\JavaWeb\\jdbc\\src\\main\\java\\aaa\\jdbc.properties");
        InputStream fileInputStream = new FileInputStream("jdbc/src/main/java/aaa/jdbc.properties");

        Properties properties = new Properties();
        properties.load(fileInputStream);
//        properties.list(System.out);



//
//        String driver = bundle.getString("driver");
//        String url = bundle.getString("url");
//        String user = bundle.getString("username");
//        String password = bundle.getString("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user =properties.getProperty("username");
        String password = properties.getProperty("password");

        Connection connection = DriverManager.getConnection(url, user, password);

//        System.out.println(connection);
        //获取数据库操作对象
        Statement statement = connection.createStatement();
//        Statement statement1 = connection.createStatement();
//        System.out.println(statement);
        System.out.println(statement1);
        String sql="insert into t_user (id,name,password,realname,gender,tel) values ( '166','wangw','123','王五','女','15323734374')";
       // boolean execute = statement.execute(sql);
//        int i = statement.executeUpdate(sql);
//        String sql="select * from t_user";
//        ResultSet resultSet = statement.executeQuery(sql);
//        System.out.println(i);
        int i = statement.executeUpdate(sql, statement.RETURN_GENERATED_KEYS);
        ResultSet generatedKeys = statement.getGeneratedKeys();
        if(generatedKeys.next()){
            System.out.println(generatedKeys.getInt(1));
        }


//        while(resultSet.next()){
//
//            System.out.println(resultSet.getString(1));
//            System.out.println(resultSet.getString(2));
//            System.out.println(resultSet.getString(3));
//            System.out.println(resultSet.getString(4));
//            System.out.println("---------------------------");
//
            System.out.println(resultSet.getString("id"));
            System.out.println(resultSet.getString("name"));
            System.out.println(resultSet.getString("password"));
            System.out.println(resultSet.getString("realname"));
            System.out.println(resultSet.getString("gender"));
            System.out.println(resultSet.getString("tel"));
            System.out.println("------------------------------------------");
//
//        }
//        resultSet.close();
        statement.close();
        connection.close();




    }

}

SQL注入问题

package com.powernode.jdbc;

import java.sql.*;
import java.util.ResourceBundle;
import java.util.Scanner;

/**
 * 用户登录案例演示SQL注入问题
 */
public class JDBCTest02 {
    public static void main(String[] args) {
        // 输出欢迎页面
        System.out.println("欢迎使用用户管理系统，请登录！");
        // 接收用户名和密码
        Scanner scanner = new Scanner(System.in);
        System.out.print("用户名：");
        String loginName = scanner.nextLine();
        System.out.print("密码：");
        String loginPwd = scanner.nextLine();
        // 读取属性配置文件，获取连接数据库的信息。
        ResourceBundle bundle = ResourceBundle.getBundle("com.powernode.jdbc.jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");
        // JDBC程序验证用户名和密码是否正确
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 1.注册驱动
            Class.forName(driver);
            // 2.获取连接
            conn = DriverManager.getConnection(url, user, password);
            // 3.获取数据库操作对象
            stmt = conn.createStatement();
            // 4.执行SQL语句
            String sql = "select realname from t_user where name = '"+loginName+"' and password = '"+loginPwd+"'";
            rs = stmt.executeQuery(sql);
            // 5.处理查询结果集
            if (rs.next()) { // 如果可以确定结果集中最多只有一条记录的话，可以使用if语句，不一定非要用while循环。
                String realname = rs.getString("realname");
                System.out.println("登录成功，欢迎您" + realname);
            } else {
                System.out.println("登录失败，用户名不存在或者密码错误。");
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6.释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}

解决SQL注入问题

package com.powernode.jdbc;

import java.sql.*;
import java.util.ResourceBundle;
import java.util.Scanner;

/**
 * PreparedStatement解决SQL注入问题
 */
public class JDBCTest03 {
    public static void main(String[] args) {
        // 输出欢迎页面
        System.out.println("欢迎使用用户管理系统，请登录！");
        // 接收用户名和密码
        Scanner scanner = new Scanner(System.in);
        System.out.print("用户名：");
        String loginName = scanner.nextLine();
        System.out.print("密码：");
        String loginPwd = scanner.nextLine();
        // 读取属性配置文件，获取连接数据库的信息。
        ResourceBundle bundle = ResourceBundle.getBundle("com.powernode.jdbc.jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");
        // JDBC程序验证用户名和密码是否正确
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // 1.注册驱动
            Class.forName(driver);
            // 2.获取连接
            conn = DriverManager.getConnection(url, user, password);
            // 3.获取数据库操作对象（获取的是预编译的数据库操作对象）
            String sql = "select realname from t_user where name=? and password=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, loginName);
            pstmt.setString(2, loginPwd);
            // 4.执行SQL语句
            rs = pstmt.executeQuery();
            // 5.处理查询结果集
            if (rs.next()) {
                String realname = rs.getString("realname");
                System.out.println("登录成功，欢迎您" + realname);
            } else {
                System.out.println("登录失败，用户名不存在或者密码错误。");
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6.释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}

package aaa;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import java.util.ResourceBundle;

public class a {
    public static void main(String[] args) throws SQLException, IOException {
        //注册驱动
//        Driver driver = new com.mysql.cj.jdbc.Driver();//创建核心驱动对象
//        DriverManager.registerDriver(driver);//注册驱动

        //获取连接
//        String url="jdbc:mysql://localhost:3306/jdbc";
//        String user ="root";
//        String password ="123456";

//        ResourceBundle bundle = ResourceBundle.getBundle("aaa.jdbc");
//        ResourceBundle bundle = ResourceBundle.getBundle("aaa.jdbc");
       // InputStream fileInputStream = new FileInputStream("D:\\IEDAFile\\JavaWeb\\jdbc\\src\\main\\java\\aaa\\jdbc.properties");
        InputStream fileInputStream = new FileInputStream("jdbc/src/main/java/aaa/jdbc.properties");

        Properties properties = new Properties();
        properties.load(fileInputStream);
//        properties.list(System.out);



//
//        String driver = bundle.getString("driver");
//        String url = bundle.getString("url");
//        String user = bundle.getString("username");
//        String password = bundle.getString("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user =properties.getProperty("username");
        String password = properties.getProperty("password");

        Connection connection = DriverManager.getConnection(url, user, password);

//        System.out.println(connection);
        //获取数据库操作对象
//        Statement statement = connection.createStatement();
//        Statement statement1 = connection.createStatement();
//        System.out.println(statement);
        System.out.println(statement1);
        String sql="delete from t_user where realname = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, "王五");
        int i = preparedStatement.executeUpdate();
        System.out.println(i+"条记录被删除了");


        // boolean execute = statement.execute(sql);
//        int i = statement.executeUpdate(sql);
//        String sql="select * from t_user";
//        ResultSet resultSet = statement.executeQuery(sql);
//        System.out.println(i);
//        int i = statement.executeUpdate(sql, statement.RETURN_GENERATED_KEYS);
//        ResultSet generatedKeys = statement.getGeneratedKeys();
//        if(generatedKeys.next()){
//            System.out.println(generatedKeys.getInt(1));
//        }


//        while(resultSet.next()){
//
//            System.out.println(resultSet.getString(1));
//            System.out.println(resultSet.getString(2));
//            System.out.println(resultSet.getString(3));
//            System.out.println(resultSet.getString(4));
//            System.out.println("---------------------------");
//
            System.out.println(resultSet.getString("id"));
            System.out.println(resultSet.getString("name"));
            System.out.println(resultSet.getString("password"));
            System.out.println(resultSet.getString("realname"));
            System.out.println(resultSet.getString("gender"));
            System.out.println(resultSet.getString("tel"));
            System.out.println("------------------------------------------");
//
//        }
//        resultSet.close();
        preparedStatement.close();
        connection.close();




    }

}

PreparedStatement的使用

新增操作

修改操作

删除操作

模糊查询

分页查询

blob数据的插入和读取

package com.powernode.jdbc;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ResourceBundle;

public class JDBCTest09 {
    public static void main(String[] args) {
        ResourceBundle bundle = ResourceBundle.getBundle("com.powernode.jdbc.jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");

        Connection conn = null;
        PreparedStatement pstmt = null;
        InputStream in = null;
        try {
            // 1. 注册驱动
            Class.forName(driver);
            // 2. 获取连接
            conn = DriverManager.getConnection(url, user, password);
            // 3. 获取预编译的数据操作对象
            String sql = "insert into t_img(img) values(?)";
            pstmt = conn.prepareStatement(sql);
            // 获取文件输入流
            in = new FileInputStream("d:/dog.jpg");
            pstmt.setBlob(1, in);
            // 4. 执行SQL语句
            int count = pstmt.executeUpdate();
            System.out.println("插入了" + count + "条记录");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6. 释放资源
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}

package com.powernode.jdbc;

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.*;
import java.util.ResourceBundle;

public class JDBCTest10 {
    public static void main(String[] args) {
        ResourceBundle bundle = ResourceBundle.getBundle("com.powernode.jdbc.jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // 1. 注册驱动
            Class.forName(driver);
            // 2. 获取连接
            conn = DriverManager.getConnection(url, user, password);
            // 3. 获取预编译的数据操作对象
            String sql = "select img from t_img where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, 1);
            // 4. 执行SQL语句
            rs = pstmt.executeQuery();
            // 5. 处理查询结果集
            if (rs.next()) {
                // 获取二进制大对象
                Blob img = rs.getBlob("img");
                // 获取输入流
                InputStream binaryStream = img.getBinaryStream();
                // 创建输出流，该输出流负责写到本地
                OutputStream out = new FileOutputStream("d:/dog2.jpg");
                byte[] bytes = new byte[1024];
                int readCount = 0;
                while ((readCount = binaryStream.read(bytes)) != -1) {
                    out.write(bytes, 0, readCount);
                }
                out.flush();
                binaryStream.close();
                out.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6. 释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}

JDBC批处理操作

不使用批处理

package com.powernode.jdbc;

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

public class NoBatchTest {
    public static void main(String[] args) {
        ResourceBundle bundle = ResourceBundle.getBundle("com.powernode.jdbc.jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");

        long begin = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 1. 注册驱动
            Class.forName(driver);
            // 2. 获取连接
            conn = DriverManager.getConnection(url, user, password);
            // 3. 获取预编译的数据操作对象
            String sql = "insert into t_product(id, name) values (?, ?)";
            pstmt = conn.prepareStatement(sql);
            int count = 0;
            for (int i = 1; i <= 10000; i++) {
                pstmt.setInt(1, i);
                pstmt.setString(2, "product" + i);
                // 4. 执行SQL语句
                count += pstmt.executeUpdate();
            }
            System.out.println("插入了" + count + "条记录");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6. 释放资源
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("总耗时" + (end - begin) + "毫秒");
    }
}

使用批处理

package com.powernode.jdbc;

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

public class BatchTest {
    public static void main(String[] args) {
        ResourceBundle bundle = ResourceBundle.getBundle("com.powernode.jdbc.jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");

        long begin = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 1. 注册驱动
            Class.forName(driver);
            // 2. 获取连接
            conn = DriverManager.getConnection(url, user, password);
            // 3. 获取预编译的数据操作对象
            String sql = "insert into t_product(id, name) values (?, ?)";
            pstmt = conn.prepareStatement(sql);
            int count = 0;
            for (int i = 1; i <= 10000; i++) {
                pstmt.setInt(1, i);
                pstmt.setString(2, "product" + i);
                pstmt.addBatch();
            }
            count += pstmt.executeBatch().length;
            System.out.println("插入了" + count + "条记录");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6. 释放资源
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("总耗时" + (end - begin) + "毫秒");
    }
}

DbUtils工具类的封装

package com.powernode.jdbc;

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

public class DbUtils {
    private static String url;
    private static String user;
    private static String password;

    static {
        // 读取属性资源文件
        ResourceBundle bundle = ResourceBundle.getBundle("com.powernode.jdbc.jdbc");
        String driver = bundle.getString("driver");
        url = bundle.getString("url");
        user = bundle.getString("user");
        password = bundle.getString("password");
        // 注册驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取数据库连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    /**
     * 释放资源
     * @param conn 连接对象
     * @param stmt 数据库操作对象
     * @param rs 结果集对象
     */
    public static void close(Connection conn, Statement stmt, ResultSet rs){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

JDBC事务

使用转账案例演示事务

实现转账功能

package com.powernode.jdbc;

import com.powernode.jdbc.utils.DbUtils;

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

/**
 * ClassName: JDBCTest19
 * Description: 实现账户转账
 * Datetime: 2024/4/12 15:20
 * Author: 老杜@动力节点
 * Version: 1.0
 */
public class JDBCTest19 {
    public static void main(String[] args) {
        // 转账金额
        double money = 10000.0;

        Connection conn = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
            conn = DbUtils.getConnection();

            // 更新 act-001 账户
            String sql1 = "update t_act set balance = balance - ? where actno = ?";
            ps1 = conn.prepareStatement(sql1);
            ps1.setDouble(1, money);
            ps1.setString(2, "act-001");
            int count1 = ps1.executeUpdate();

            // 更新 act-002账户
            String sql2 = "update t_act set balance = balance + ? where actno = ?";
            ps2 = conn.prepareStatement(sql2);
            ps2.setDouble(1, money);
            ps2.setString(2, "act-002");
            int count2 = ps2.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(null, ps1, null);
            DbUtils.close(conn, ps1, null);
        }

    }
}

JDBC事务默认是自动提交的

添加事务控制

public class JDBCTest19 {
    public static void main(String[] args) {
        // 转账金额
        double money = 10000.0;

        Connection conn = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
            conn = DbUtils.getConnection();
            
            // 开启事务（关闭自动提交机制）
            conn.setAutoCommit(false);

            // 更新 act-001 账户
            String sql1 = "update t_act set balance = balance - ? where actno = ?";
            ps1 = conn.prepareStatement(sql1);
            ps1.setDouble(1, money);
            ps1.setString(2, "act-001");
            int count1 = ps1.executeUpdate();

            String s = null;
            s.toString();

            // 更新 act-002账户
            String sql2 = "update t_act set balance = balance + ? where actno = ?";
            ps2 = conn.prepareStatement(sql2);
            ps2.setDouble(1, money);
            ps2.setString(2, "act-002");
            int count2 = ps2.executeUpdate();
            
            // 提交事务
            conn.commit();

        } catch (Exception e) {
            // 遇到异常回滚事务
            try {
                conn.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(null, ps1, null);
            DbUtils.close(conn, ps1, null);
        }

    }
}

设置JDBC事务隔离级别

public class JDBCTest20 {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = DbUtils.getConnection();
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, null, null);
        }
    }
}

什么是DAO

定义Employee封装数据

package com.powernode.jdbc.beans;

/**
 * ClassName: Employee
 * Description:
 * Datetime: 2024/4/14 23:32
 * Version: 1.0
 */
public class Employee {
    private Long id;
    private String name;
    private String job;
    private Double salary;
    private String hiredate;
    private String address;

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", job='" + job + '\'' +
                ", salary=" + salary +
                ", hiredate='" + hiredate + '\'' +
                ", address='" + address + '\'' +
                '}';
    }

    public Employee() {
    }

    public Employee(Long id, String name, String job, Double salary, String hiredate, String address) {
        this.id = id;
        this.name = name;
        this.job = job;
        this.salary = salary;
        this.hiredate = hiredate;
        this.address = address;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public String getHiredate() {
        return hiredate;
    }

    public void setHiredate(String hiredate) {
        this.hiredate = hiredate;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

定义EmployeeDao

package com.powernode.jdbc.dao;

import com.powernode.jdbc.beans.Employee;
import com.powernode.jdbc.utils.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * ClassName: EmployeeDao
 * Description:
 * Version: 1.0
 */
public class EmployeeDao {
    /**
     * 新增员工
     * @param employee
     * @return
     */
    public int insert(Employee employee) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DbUtils.getConnection();
            String sql = "insert into t_employee(name,job,salary,hiredate,address) values(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, employee.getName());
            ps.setString(2, employee.getJob());
            ps.setDouble(3, employee.getSalary());
            ps.setString(4, employee.getHiredate());
            ps.setString(5, employee.getAddress());
            count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, null);
        }
        return count;
    }

    /**
     * 修改员工
     * @param employee
     * @return
     */
    public int update(Employee employee){
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DbUtils.getConnection();
            String sql = "update t_employee set name=?, job=?, salary=?, hiredate=?, address=? where id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, employee.getName());
            ps.setString(2, employee.getJob());
            ps.setDouble(3, employee.getSalary());
            ps.setString(4, employee.getHiredate());
            ps.setString(5, employee.getAddress());
            ps.setLong(6, employee.getId());
            count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, null);
        }
        return count;
    }

    /**
     * 根据id删除员工信息
     * @param id 员工id
     * @return 1表示成功
     */
    public int deleteById(Long id){
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DbUtils.getConnection();
            String sql = "delete from t_employee where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, null);
        }
        return count;
    }

    /**
     * 根据id查询所有员工
     * @param id
     * @return
     */
    public Employee selectById(Long id){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Employee employee = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "select * from t_employee where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            rs = ps.executeQuery();
            if(rs.next()){
                employee = new Employee();
                employee.setId(id);
                employee.setName(rs.getString("name"));
                employee.setJob(rs.getString("job"));
                employee.setSalary(rs.getDouble("salary"));
                employee.setHiredate(rs.getString("hiredate"));
                employee.setAddress(rs.getString("address"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, rs);
        }
        return employee;
    }

    /**
     * 查询所有员工信息
     * @return 员工列表
     */
    public List<Employee> selectAll(){
        List<Employee> employees = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "select * from t_employee";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                Employee employee = new Employee();
                employee.setId(rs.getLong("id"));
                employee.setName(rs.getString("name"));
                employee.setJob(rs.getString("job"));
                employee.setSalary(rs.getDouble("salary"));
                employee.setHiredate(rs.getString("hiredate"));
                employee.setAddress(rs.getString("address"));
                employees.add(employee);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, rs);
        }
        return employees;
    }
}

BaseDao的封装

package com.powernode.jdbc.dao;

import com.powernode.jdbc.utils.DbUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * ClassName: BaseDao
 * Description: 最基础的Dao，所有的Dao应该去继承该BaseDao
 * Datetime: 2024/4/15 11:08
 * Version: 1.0
 */
public class BaseDao {

    /**
     * 这是一个通用的执行insert delete update语句的方法。
     * @param sql
     * @param params
     * @return
     */
    public int executeUpdate(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            // 获取连接
            conn = DbUtils.getConnection();
            // 获取预编译的数据库操作对象
            ps = conn.prepareStatement(sql);
            // 给 ? 占位符传值
            if(params != null && params.length > 0){
                // 有占位符 ?
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            // 执行SQL语句
            count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, null);
        }
        return count;
    }

    /**
     * 这是一个通用的查询语句
     * @param clazz
     * @param sql
     * @param params
     * @return
     * @param <T>
     */
    public <T> List<T> executeQuery(Class<T> clazz, String sql, Object... params){
        List<T> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 获取连接
            conn = DbUtils.getConnection();
            // 获取预编译的数据库操作对象
            ps = conn.prepareStatement(sql);
            // 给?传值
            if(params != null && params.length > 0){
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            // 执行SQL语句
            rs = ps.executeQuery();

            // 获取查询结果集元数据
            ResultSetMetaData rsmd = rs.getMetaData();

            // 获取列数
            int columnCount = rsmd.getColumnCount();

            // 处理查询结果集
            while(rs.next()){
                // 封装bean对象
                T obj = clazz.newInstance();
                // 给bean对象属性赋值
                /*
                比如现在有一张表：t_user，然后表中有两个字段，一个是 user_id，一个是user_name
                现在javabean是User类，该类中的属性名是：userId,username
                执行这样的SQL语句：select user_id as userId, user_name as username from t_user;
                 */
                for (int i = 1; i <= columnCount; i++) {
                    // 获取查询结果集中的列的名字
                    // 这个列的名字是通过as关键字进行了起别名，这个列名就是bean的属性名。
                    String fieldName = rsmd.getColumnLabel(i);
                    // 获取属性Field对象
                    Field declaredField = clazz.getDeclaredField(fieldName);
                    // 打破封装
                    declaredField.setAccessible(true);
                    // 给属性赋值
                    declaredField.set(obj, rs.getObject(i));
                }

                // 将对象添加到List集合
                list.add(obj);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, rs);
        }
        // 返回List集合
        return list;
    }


    /**
     *
     * @param clazz
     * @param sql
     * @param params
     * @return
     * @param <T>
     */
    public <T> T queryOne(Class<T> clazz, String sql, Object... params){
        List<T> list = executeQuery(clazz, sql, params);
        if(list == null || list.size() == 0){
            return null;
        }
        return list.get(0);
    }

}

连接池

不使用连接池有啥问题

使用连接池来解决什么问题

javax.sql.DataSource

连接池的属性

常用的连接池

连接池的使用

Druid的使用

HikariCP的使用

钟佩颖

关注

13
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
JDBC（du）

return id;return job;/***//*** 新增员工* @return*/try {)";/*** 修改员工* @return*/try {, job=?, salary=?, address=?where id=?/*** 根据id删除员工信息* @param id 员工id。
复制链接

扫一扫