MySql之原生JDBC衍生JDBCUtils

之前学习了原生JDBC的使用,但是发现设定数据库信息,获取数据库驱动以及连接对象这一块代码过于冗余

故抽取出来作为工具类使用,衍生出JDBCUtils

一.案例展示

1.测试准备

• ①MySql数据库一枚
• ②database名为batistest一枚

• 

• ③项目工程

2.代码展示:

***pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.howie</groupId>
    <artifactId>native_JDBC_JDBCUtils</artifactId>
    <version>1.0-SNAPSHOT</version>
    
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>
</project>

***User.java

package com.howie.pojo;

import java.util.Date;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 17:18
 * @Version 1.0
 */
public class User {

    private int id;
    private String username;
    private int sex;
    private Date birthday;
    private String address;

    public User(int id, String username, int sex, Date birthday, String address) {
        this.id = id;
        this.username = username;
        this.sex = sex;
        this.birthday = birthday;
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", sex=" + sex +
                ", birthday=" + birthday +
                ", address='" + address + '\'' +
                '}';
    }
}

***JDBCUtils.java

package com.howie.utils;

import java.sql.*;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 18:11
 * @Version 1.0
 */
public class JDBCUtils {

    //1.数据库驱动信息
    private static String driver = "com.mysql.jdbc.Driver";
    //2.数据库连接信息
    private static String url =
            "jdbc:mysql://localhost:3306/batistest?characterEncoding=utf8";
    private static String username = "root";
    private static String password = "root";

    //3.静态代码块用于获取MySql驱动
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 4.工具提供获取数据库连接的方法
     * @return
     */
    public static Connection getConnection() throws SQLException {
        //5.获取数据库连接并返回
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 5.释放资源
     * @param rs 结果集
     * @param stat sql执行者
     * @param conn 数据库连接对象
     */
    public static void closeResource(ResultSet rs, Statement stat,Connection conn){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

***GetData.java 

package com.withutils;

import com.howie.pojo.User;
import com.howie.utils.JDBCUtils;

import java.sql.*;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 18:24
 * @Version 1.0
 */
public class GetData {

    public static void main(String[] args) throws SQLException {
        //1.获取连接
        Connection connection = JDBCUtils.getConnection();
        //2.获取sql语句执行对象
        Statement statement = connection.createStatement();
        //3.获取结果集
        ResultSet resultSet = statement.executeQuery("SELECT * FROM `user`");
        //3.1遍历结果集
        while (resultSet.next()){
            //3.2封装对象
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            int sex = resultSet.getInt("sex");
            Date birthday = resultSet.getDate("birthday");
            String address = resultSet.getString("address");
            User user = new User(id,username,sex,birthday,address);
            System.out.println(user);
        }
        //5.关闭资源
        JDBCUtils.closeResource(resultSet,statement,connection);
    }
}

3.测试

测试输出如下:

User{id=1, username='张三', sex=1, birthday=2018-07-10, address='北京'}
User{id=2, username='李四', sex=1, birthday=2018-07-10, address='上海'}
User{id=3, username='王五', sex=1, birthday=2018-07-10, address='广州'}
User{id=4, username='王六', sex=1, birthday=2018-07-10, address='深圳'}