JDBC 学习笔记

学习参考视频：
[1] JDBC从入门到精通视频教程-JDBC实战精讲
[2] 尚硅谷JDBC核心技术视频教程（康师傅带你一站式搞定jdbc）

代码总结：
testConnection1（）方法介绍了数据库连接的6个步骤：

1.注册JDBC驱动，告诉Java程序将要连接什么数据库；
2.Java程序连接数据库；
3.由连接对象创建操作SQL的对象；
4.执行SQL语句；
5.获取结果集；
6.JVM进程与数据库进程的通信是重量级的，因此需关闭资源。

后续方法都对上述步骤作了不同的优化，在方法上有说明。.

代码如下：

package com.bjpowernode;

import org.junit.Test;

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



public class JDBC01 {


    
    @Test
    public void jdbcTest01() {
        Connection connection = null;
        Statement statement = null;
        try {
            Driver driver = new com.mysql.jdbc.Driver();
            DriverManager.registerDriver(driver);

            connection = DriverManager.getConnection("jdbc:mysql://ip:port/database", "xxx", "xxx");

            statement = connection.createStatement();
            String sql = "INSERT INTO `t_student`(`name`,`gender`) VALUE('小绿',1),('小黄',0);";
            // 返回值 i 表示：i行受到影响
            int i = statement.executeUpdate(sql);
            System.out.println(i);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    
    @Test
    public void jdbcTest02() {
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://ip:port/database", "xxx", "xxx");

            statement = connection.createStatement();
            String sql = "DELETE FROM `t_student` WHERe `name` = '小黄';";
            int i = statement.executeUpdate(sql);
            System.out.println(i);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {

            e.printStackTrace();

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


    
    @Test
    public void jdbcTest03() {

        InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

        String driverClass = properties.getProperty("driverClass");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, user, password);

            statement = connection.createStatement();
            String sql = "INSERT INTO `t_student`(`name`,`gender`) VALUE('小绿',1),('小黄',0);";
            // 返回值 i 表示：i行受到影响
            int i = statement.executeUpdate(sql);
            System.out.println(i);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    
    @Test
    public void jdbcTest04() {
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

        String driverClass = properties.getProperty("driverClass");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, user, password);

            statement = connection.createStatement();
            String sql = "SELECT * FROM `t_student`;";
            // 返回值 i 表示：i行受到影响,注意：由于是查询
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                String gender = resultSet.getInt("gender") == 0 ? "女" : "男";
                System.out.format("姓名：%s，性别：%s", name, gender);
                System.out.println();
            }

        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    
    @Test
    public void jdbcTest05() {
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

        String driverClass = properties.getProperty("driverClass");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, user, password);

            String sql = "SELECt * FROM `t_user` where loginName = ? and loginPwd = ?;";
            statement = connection.prepareStatement(sql);
            statement.setString(1, "sjmp");
            statement.setString(2, "666666");
            // 返回值 i 表示：i行受到影响,注意：由于是查询
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                String gender = resultSet.getInt("gender") == 0 ? "女" : "男";
                System.out.format("姓名：%s，性别：%s", name, gender);
                System.out.println();
            }

        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    
    @Test
    public void jdbcTest06() {
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

        String driverClass = properties.getProperty("driverClass");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        Connection connection = null;
        PreparedStatement statement = null;
        try {
            Class.forName(driverClass);

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

            // 1.取消事务自动提交功能
            connection.setAutoCommit(false);
//           事务逻辑代码，几个sql语句要么都执行，要么都不执行...
            //2.没有出现异常，全部提交
            connection.commit();
        } catch (SQLException | ClassNotFoundException e) {
            //3.出现异常，开始回滚
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
   
    @Test
    public void jdbcTest07() {

        InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        DataSource dataSource = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            properties.load(inputStream);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
            connection = dataSource.getConnection();
            String sql = "SELECt * FROM `t_student` ;";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                String name = resultSet.getString("name");
                String gender = resultSet.getInt("gender") == 0 ? "女" : "男";
                System.out.format("姓名：%s，性别：%s", name, gender);
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}