MYSQL中JDBC工具类的抽取

1.前提准备

use day04_db;
create table user (
  id int primary key auto_increment,
  username varchar(20) not null,
  birthday date,
  sex char(1) default '男',
  address varchar(50)
);

insert into user values (null, '孙悟空','1980-10-24','男','花果山水帘洞');
insert into user values (null, '白骨精','1992-11-12','女','白虎岭白骨洞');
insert into user values (null, '猪八戒','1983-05-20','男','福临山云栈洞');
insert into user values (null, '蜘蛛精','1995-03-22','女','盤丝洞');

select * from user;

2抽取JDBC工具类

抽取工具类的好处：
提高开发效率
减少代码量

2.1创建jdbc.properties文件

url=jdbc:mysql://localhost/day04_db
username=root
password=root
driverClass=com.mysql.jdbc.Driver

2.2抽取工具类

思想：抽取必要的方法为静态代码块，调用即执行
在静态代码块中创建注册驱动的方法
创建连接的方法getConnection
创建释放资源的方法release

代码：

package cn.itcast.utils;

import javax.xml.transform.Result;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    //思路：方便对数据库的使用，省去了大量的创建连接数据库使用语句
    //基本步骤
    //1.对基本的语句抽取成静态代码块
    //2.创建连接的方法
    //创建销毁的方法
    private static  String driverClass;
    private static  String url;
    private static String username;
    private static String password;
    static {
        try {
            Properties properties= new Properties();
            properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
            driverClass = properties.getProperty("driverClass");
            url = properties.getProperty("url");
            username= properties.getProperty("username");
            password= properties.getProperty("password");
            Class.forName(driverClass);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3.使用工具类对数据的增删改查操作

3.1查询所有

@Test
    public void test01() throws Exception {
        //查询所有
        Connection connection = JDBCUtils.getConnection();
        String sql = "select * from user";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            String username = resultSet.getString("username");
            String birthday = resultSet.getString("birthday");
            String sex = resultSet.getString("sex");
            String address = resultSet.getString("address");
            System.out.println(id + username + birthday + sex + address);
        }


        JDBCUtils.release(connection,preparedStatement,resultSet);
    }

3.2根据id查询

@Test
    public void test02() throws Exception {
        //通过ID查询
        Connection connection = JDBCUtils.getConnection();
        String sql="select * from user where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,2);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            String username = resultSet.getString("username");
            String birthday = resultSet.getString("birthday");
            String sex = resultSet.getString("sex");
            String address = resultSet.getString("address");
            System.out.println(id + username + birthday + sex + address);
        }
        JDBCUtils.release(connection,preparedStatement,resultSet);

    }

3.3添加数据

@Test
    public void test03() throws Exception {
        //添加数据
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into user values(null,?,?,?,?)";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"quanweiya");
        preparedStatement.setString(2,"2020-06-18");
        preparedStatement.setString(3, "男");
        preparedStatement.setString(4, "北京");
        int i = preparedStatement.executeUpdate();
        if (i == 1) {
            System.out.println("数据添加成功");
        } else {
            System.out.println("数据添加失败");
        }
        JDBCUtils.release(connection,preparedStatement,null);
    }

3.4修改数据

@Test
    public void test04() throws Exception {
        //修改数据
        Connection connection = JDBCUtils.getConnection();
        String sql = "update user set username=? ,address=? where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, "大太阳");
        preparedStatement.setString(2,"北京朝阳区");
        preparedStatement.setInt(3, 10);
        int i = preparedStatement.executeUpdate();
        if (i == 1) {
            System.out.println("数据修改成功");
        } else {
            System.out.println("数据修改失败");
        }

        JDBCUtils.release(connection,preparedStatement,null);
    }

3.5删除数据

 @Test
    public void test05() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "delete from user where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 10);
        int i = preparedStatement.executeUpdate();
        if (i == 1) {
            System.out.println("数据删除成功");
        } else {
            System.out.println("数据删除失败");
        }

        JDBCUtils.release(connection,preparedStatement,null);

    }