JDBC核心原理

╰半橙微兮° 2024-03-24 15:34 44阅读 0赞

## 一、连接数据库 ##

### 1.1 获取数据库连接 ###

获取数据库连接是操作数据库的第一步。

#### 1.1.1 方式一 ####

*  最原始的方式连接数据库。

Driver driver = new com.mysql.jdbc.Driver();
    	        String url = "jdbc:mysql://localhost:13306/test1?useSSL=false";
    	        Properties info = new Properties();
    	        info.setProperty("user","root");
    	        info.setProperty("password","root");
    	        Connection connection = driver.connect(url, info);
    	        System.out.println(connection);

![在这里插入图片描述][c3d5c381890f4443b876755a55534555.png]

#### 1.1.2 方式二 ####

*  不出现第三方的API，使程序具有更好的可移植性。

Class clazz = Class.forName("com.mysql.jdbc.Driver");
    	        Driver driver = (Driver) clazz.newInstance();
    	        String url = "jdbc:mysql://localhost:13306/test1?useSSL=false";
    	        Properties info = new Properties();
    	        info.setProperty("user","root");=
    	        info.setProperty("password","root");
    	        Connection connection = driver.connect(url, info);
    	        System.out.println(connection);

#### 1.1.3 方式三 ####

*  使用DriverManager替换Driver。

Class clazz = Class.forName("com.mysql.jdbc.Driver");
    	        Driver driver = (Driver) clazz.newInstance();
    	        String url = "jdbc:mysql://localhost:13306/test1?useSSL=false";
    	        String user = "root";
    	        String password = "root";
    	        DriverManager.registerDriver(driver);
    	        Connection connection = DriverManager.getConnection(url, user, password);
    	        System.out.println(connection);

#### 1.1.4 方式四 ####

*  当Driver类加载到内存，驱动也会跟着注册。  
    ![在这里插入图片描述][258dc7d938364e46ab9d1d5f4032dc54.png]

Class.forName("com.mysql.jdbc.Driver"); //在MySQL下可以省略，但是不建议省略。
    	        String url = "jdbc:mysql://localhost:13306/test1?useSSL=false";
    	        String user = "root";
    	        String password = "root";
    	        Connection connection = DriverManager.getConnection(url, user, password);
    	        System.out.println(connection);

#### 1.1.5 方式五 ####

*  将数据库的基本信息声明在配置文件中，实现数据和代码的分离，实现了解耦，减少硬编码，增加灵活性。
 *  如果需要修改配置文件，可以避免程序重新打包。

编写配置文件jdbc.properties

user=root
    			password=root
    			url=jdbc:mysql://localhost:13306/test1?useSSL=false
    			driverClass=com.mysql.jdbc.Driver

读取配置文件并获取连接

InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
    	        Properties properties = new Properties();
    	        properties.load(inputStream);
    	        String user = properties.getProperty("user");
    	        String password = properties.getProperty("password");
    	        String url = properties.getProperty("url");
    	        String driverClass = properties.getProperty("driverClass");
    	        Class.forName(driverClass);
    	        Connection connection = DriverManager.getConnection(url, user, password);
    	        System.out.println(connection);

![在这里插入图片描述][293d5879f9b6412c8b5671e998f70e6f.png]

### 1.2 预编译SQL语句，返回PreparedStatement实例 ###

*  通过数据库连接进行预编译SQL并获取prepareStatement对象。

String sql = "insert into book(id,name,authors) values (?,?,?)";
                preparedStatement = connection.prepareStatement(sql);

### 1.3 给占位符赋值 ###

*  填充SQL中的占位符

preparedStatement.setInt(1,5);
                preparedStatement.setString(2,"Art of Love");
                preparedStatement.setString(3,"jixian");

### 1.4 执行SQL ###

*  用preparedStatement向数据库发送SQL语句。

preparedStatement.execute();

### 1.5 关闭连接 ###

*  关闭数据库连接和preparedStatement连接。

try {
        
                    if (preparedStatement != null)
                    preparedStatement.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
                try {
        
                    if (connection != null)
                    connection.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }

### 1.6 JDBC完整代码 ###

JDBC的完整流程：

Connection connection = null;
            	PreparedStatement preparedStatement = null;
            try {
        
                InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
                Properties properties = new Properties();
                properties.load(inputStream);
                String user = properties.getProperty("user");
                String password = properties.getProperty("password");
                String url = properties.getProperty("url");
                String driverClass = properties.getProperty("driverClass");
                Class.forName(driverClass);
                connection = DriverManager.getConnection(url, user, password);
                String sql = "insert into book(id,name,authors) values (?,?,?)";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1,5);
                preparedStatement.setString(2,"Art of Love");
                preparedStatement.setString(3,"jixian");
                preparedStatement.execute();
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                try {
        
                    if (preparedStatement != null)
                    preparedStatement.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
                try {
        
                    if (connection != null)
                    connection.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }

## 二、JDBCUtils工具类 ##

### 2.1 封装JDBCUtils类 ###

把获取连接和关闭连接封装在工具类里

public class JDBCUtils {
        
    	    // 获取数据库连接
    	    public static Connection getConnection() throws Exception{
        
    	        InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
    	        Properties properties = new Properties();
    	        properties.load(inputStream);
    	        String user = properties.getProperty("user");
    	        String password = properties.getProperty("password");
    	        String url = properties.getProperty("url");
    	        String driverClass = properties.getProperty("driverClass");
    	        Class.forName(driverClass);
    	        Connection connection = DriverManager.getConnection(url, user, password);
    	        return connection;
    	    }
    	    // 关闭资源
    	    public static void closeResource(Connection connection, Statement statement){
        
    	        if (connection != null) {
        
    	            try {
        
    	                connection.close();
    	            } catch (SQLException e) {
        
    	                e.printStackTrace();
    	            }
    	        }
    	        if (statement != null) {
        
    	            try {
        
    	                statement.close();
    	            } catch (SQLException e) {
        
    	                e.printStackTrace();
    	            }
    	        }
    	    }
    	    public static void closeResource(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        
    	        if (connection != null) {
        
    	            try {
        
    	                connection.close();
    	            } catch (SQLException e) {
        
    	                e.printStackTrace();
    	            }
    	        }
    	        if (preparedStatement != null) {
        
    	            try {
        
    	                preparedStatement.close();
    	            } catch (SQLException e) {
        
    	                e.printStackTrace();
    	            }
    	        }
    	        if (resultSet != null) {
        
    	            try {
        
    	                resultSet.close();
    	            } catch (SQLException e) {
        
    	                e.printStackTrace();
    	            }
    	        }
    	    }
    }

### 2.2 编写测试代码 ###

测试JDBCUtils类的使用

Connection connection = null;
            PreparedStatement preparedStatement = null;
            try {
        
                // 获取数据库连接
                connection = JDBCUtils.getConnection();
                String sql = "update book set price = ? where name = ?";
                // 预编译SQL语句，返回PreparedStatement实例
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setFloat(1,20.0F);
                preparedStatement.setString(2,"Art of Love");
                preparedStatement.execute();
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                JDBCUtils.closeResource(connection, preparedStatement);
            }

### 2.3 使用Druid数据库连接池 ###

连接池中的数据库连接可以重复使用，减少资源的消耗。

public class JDBCUtils {
        
        // 使用Druid数据库连接池
        private static DataSource dataSource;
        static {
        
            InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            try {
        
                properties.load(inputStream);
                dataSource = DruidDataSourceFactory.createDataSource(properties);
            } catch (Exception e) {
        
                e.printStackTrace();
            }
        }
        // 获取数据库连接
        public static Connection getConnection() throws Exception{
        
            Connection connection = dataSource.getConnection();
            return connection;
        }
        // 关闭资源
        public static void closeResource(Connection connection, Statement statement){
        
            if (connection != null) {
        
                try {
        
                    connection.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if (statement != null) {
        
                try {
        
                    statement.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
        public static void closeResource(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        
            if (connection != null) {
        
                try {
        
                    connection.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
        
                try {
        
                    preparedStatement.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if (resultSet != null) {
        
                try {
        
                    resultSet.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
    }

## 三、CRUD操作 ##

### 3.1. 封装通用的增删改操作 ###

增删改操作不需要返回结果，可以把增删改封装到一个方法里。

public void update(String sql, Object ...args){
        
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            try {
        
                connection = JDBCUtils.getConnection();
                preparedStatement = connection.prepareStatement(sql);
                for (int i = 0; i < args.length; i++) {
        
                    preparedStatement.setObject(i+1, args[i]);
                }
                preparedStatement.execute();
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                JDBCUtils.closeResource(connection, preparedStatement);
            }
        }

编写测试代码：

String sql = "update book set note = ? where id = ?";
            update(sql, "novel", 5);

![在这里插入图片描述][e35493e65cc847a6a8ed83c17d0c5677.png]

### 3.2 查询操作 ###

*  一个Java类对应数据库一张表。
 *  一个JavaBean对象对应数据库中的一条记录。

创建JavaBean类，使用JavaBean来存储数据库的一条记录。

Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
        
                connection = JDBCUtils.getConnection();
                preparedStatement = connection.prepareStatement("select  id,name,email,birth from customer where id = ?");
                preparedStatement.setInt(1,1);
                resultSet = preparedStatement.executeQuery();
                while (resultSet.next()){
        
                    int id = resultSet.getInt(1);
                    String name = resultSet.getString(2);
                    String email = resultSet.getString(3);
                    Date birth = resultSet.getDate(4);
                    Customer customer = new Customer(id, name, email, birth);
                    System.out.println(customer);
                }
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                JDBCUtils.closeResource(connection, preparedStatement, resultSet);
            }

![在这里插入图片描述][ef5f82028eb7426687b276ef863dfdb8.png]

### 3.3 针对某张表通用的查询操作 ###

查询单条记录并封装到对象中，如果数据库中的列名和类的属性名不一致，查询数据的时候可以起别名，获取数据的时候获取列的别名。

public Customer queryCustomer(String sql, Object ...args){
        
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
        
                connection = JDBCUtils.getConnection();
                preparedStatement = connection.prepareStatement(sql);
                for (int i = 0; i < args.length; i++) {
        
                    preparedStatement.setObject(i + 1, args[i]);
                }
                resultSet = preparedStatement.executeQuery();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                if (resultSet.next()){
        
                    Customer customer = new Customer();
                    for (int i = 0; i < columnCount; i++) {
        
                        Object columnValue = resultSet.getObject(i + 1);
                        //获取列的别名，如果没有别名就取列名
                        String columnName = metaData.getColumnLabel(i + 1);
                        Field field = Customer.class.getDeclaredField(columnName);
                        field.setAccessible(true);
                        field.set(customer, columnValue);
                    }
                    return customer;
                }
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                JDBCUtils.closeResource(connection, preparedStatement, resultSet);
            }
            return null;
        }

测试查询一条记录：

Customer customer = queryCustomer("select id,name,email,birth from customer where id = ?", 4);
            System.out.println(customer);

![在这里插入图片描述][912d87f4e4a441e7844f585e4000e241.png]

### 3.4 返回单个结果的通用查询操作 ###

针对不同的表都可以进行查询并返回相应的对象。

public <T> T queryObject(Class<T> clazz, String sql, Object ...args){
        
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
        
                connection = JDBCUtils.getConnection();
                preparedStatement = connection.prepareStatement(sql);
                for (int i = 0; i < args.length; i++) {
        
                    preparedStatement.setObject(i + 1, args[i]);
                }
                resultSet = preparedStatement.executeQuery();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                if (resultSet.next()){
        
                    T t = clazz.newInstance();
                    for (int i = 0; i < columnCount; i++) {
        
                        Object columnValue = resultSet.getObject(i + 1);
                        String columnName = metaData.getColumnLabel(i + 1);
                        Field field = clazz.getDeclaredField(columnName);
                        field.setAccessible(true);
                        field.set(t, columnValue);
                    }
                    return t;
                }
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                JDBCUtils.closeResource(connection, preparedStatement, resultSet);
            }
            return null;
        }

编写测试代码：

Customer customer = queryObject(Customer.class, "select id,name,email,birth from customer where id = ?", 7);
            System.out.println(customer);

![在这里插入图片描述][ee3411a75ab84186a72d3a6587b36cb8.png]

### 3.5 返回多个结果的查询操作 ###

查出来的结果是多个，可以把结果封装到集合中。

public <T> List<T> queryObjects(Class<T> clazz, String sql, Object ...args){
        
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
        
                connection = JDBCUtils.getConnection();
                preparedStatement = connection.prepareStatement(sql);
                for (int i = 0; i < args.length; i++) {
        
                    preparedStatement.setObject(i + 1, args[i]);
                }
                resultSet = preparedStatement.executeQuery();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                List<T> list = new ArrayList<>();
                while (resultSet.next()){
        
                    T t = clazz.newInstance();
                    for (int i = 0; i < columnCount; i++) {
        
                        Object columnValue = resultSet.getObject(i + 1);
                        String columnName = metaData.getColumnLabel(i + 1);
                        Field field = clazz.getDeclaredField(columnName);
                        field.setAccessible(true);
                        field.set(t, columnValue);
                    }
                    list.add(t);
                }
                return list;
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                JDBCUtils.closeResource(connection, preparedStatement, resultSet);
            }
            return null;
        }

测试多个结果的查询：

List<Customer> customers = queryObjects(Customer.class, "select id,name,email,birth from customer");
            System.out.println(customers);

![在这里插入图片描述][e50073b28b174095a9b09a62cec2ee5f.png]

[c3d5c381890f4443b876755a55534555.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/75954a29421043fba0a59ee67618b40a.png
[258dc7d938364e46ab9d1d5f4032dc54.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/e6cc0cf575154acdb374d9f4b5e9dce2.png
[293d5879f9b6412c8b5671e998f70e6f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/813ff295047747508ba70cac64042a77.png
[e35493e65cc847a6a8ed83c17d0c5677.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/35d3928afd914c49ae9b6bd50da6a6f6.png
[ef5f82028eb7426687b276ef863dfdb8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/b3031864188d441b883c7d628edc3b4b.png
[912d87f4e4a441e7844f585e4000e241.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/7fe45d924ce84339ac4b627dbfbf9a8f.png
[ee3411a75ab84186a72d3a6587b36cb8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/d1a3f96ab29c4d9d82ba8c7f9665e42f.png
[e50073b28b174095a9b09a62cec2ee5f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/91c236f3eca6401d837f88ab2acb55aa.png