JDBC连接数据库

JDBC操作详解

JDBC就是用Java语言操作数据库，在使用它之前，首先我要引入依赖。

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>

接下来就是具体步骤

    public static final String URL = "jdbc:mysql://localhost:3306/jdbc_demo";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "123456";


    public static int insert(User user) throws SQLException {
//    1.注册数据库驱动
        DriverManager.registerDriver(new Driver());
//    2.获取与的数据库连接
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
//    3.书写sql语句
        Statement statement = connection.createStatement();
        String sql = String.format("INSERT INTO user (name,age) VALUES (%s,%d )", user.getName(), user.getAge());
//    4.执行sql语句
        statement.executeUpdate(sql);
//    5.关闭数据库连接
        connection.close();
    }

我们把异常捕获一下

   public static int insert(User user) {
//        1.注册数据库驱动
        DriverManager.registerDriver(new Driver());
        Connection connection = null;
        try {
//            2.建立与数据库的连接
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
//            3.书写sql语句
//            使用connection.createStatement 有严重的sql注入
//            因此之后使用prepareStatement
            Statement statement = connection.createStatement();
            String sql = String.format("INSERT INTO user (name,age) VALUES (%s,%d )", user.getName(), user.getAge());
//            4.执行sql语句
            int i = statement.executeUpdate(sql);
            return i;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("新增错误");
        } finally {
            try {
//                5.关闭与数据库的连接
                connection.close();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
        return -1;
    }

我们还可以优化一下：

首先，数据库驱动注册可以省略

因为在 com.mysql.cj.jdbc.Driver内部代码有这样一段：

static {
            try {
               DriverManager.registerDriver(new Driver());
            } catch (SQLException var1) {
                throw new RuntimeException("Can't register driver!");
           }
        }

即在类加载时便注册了数据库驱动因此注册驱动可以省略

其次，使用createStatement有严重的sql注入问题，因此我们改用prepareStatement

接下来，Java1.7特性，提供了try-with-resource，try()可以传入一个实现了AutoCloseable接口的类，在离开try块时将自动调用close()方法。Collection正好实现了AutoCloseable。

优化后的代码：

    public static int insert(User user) {
        try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String sql = "INSERT INTO user(name,age) VALUES (?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setInt(2, user.getAge());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("新增错误");
        }
        return -1;
    }

抽取Jdbc工具类 

我们可以把url,username,password等数据库相关信息和getConnection方法，封装到一个工具类里；还可以利用配置文件导入数据库相关信息

在resourse下创建jdbc.properties

url:jdbc:mysql://localhost:3306/jdbc_demo"
username:root
password:123456

JdbcUtils

    private static final String URL;
    private static final String USERNAME;
    private static final String PASSWORD;

    static {
        InputStream resource = ClassLoader.getSystemResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }

        URL = properties.getProperty("url");
        USERNAME = properties.getProperty("username");
        PASSWORD = properties.getProperty("password");
    }

    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            System.err.println("数据库连接参数错误，Connection获取失败！");
            System.err.println(e.getMessage());
        }
        return connection;
    }

Druid数据库连接池

之前的代码，每进行一次sql语句，都要先建立连接之后断开，这样占用宝贵的连接资源，因此使用池化技术，使与数据库的连接不再断开。

首先引入druid依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.20</version>
        </dependency>

druid.properties 

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/project_01
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000

编写Jdbc工具类

 private static DataSource druidDataSource;

    static {
        InputStream resource = ClassLoader.getSystemResourceAsStream("druid.properties");
        Properties properties = new Properties();
        try {
            properties.load(resource);
            druidDataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {

        Connection connection = null;
        try {
            connection = druidDataSource.getConnection();
        } catch (SQLException e) {
            System.out.println("数据库异常！");
        }
        return connection;
    }

开启事务

    public static int update(String id, String name, Timestamp updateTime) {
        Connection connection = JdbcUtils.getConnection();
        try {
            // 1.开启事务
            connection.setAutoCommit(false);
            // sql1 查询
            // 判断查询的结果
            // sql2 修改    
            // 2.事务提交         
            connection.commit();
            return i;
        } catch (SQLException e) {
            try {
            // 3.回滚
                connection.rollback();
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
            System.out.println(e.getMessage());
        }
        return -1;
    }