JdbcUtils工具类的优化升级——通过配置文件连接mysql8.0，并对mysql8.0中的表进行[增删改]操作

雷神乐乐

已于 2023-01-04 20:50:55 修改

阅读量553

点赞数

分类专栏： # JDBC 文章标签： mysql 数据库 sql java Powered by 金山文档

于 2023-01-03 23:03:55 首次发布

本文链接：https://blog.csdn.net/Helen_1997_1997/article/details/128535017

版权

JDBC 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

文章介绍了如何通过创建一个BaseDao基类来封装数据库连接和资源释放，同时使用配置文件管理数据库连接参数，以实现更灵活的数据库切换。此外，还展示了Dog和Master两个Pojo类，以及对应的DogDao和MasterDao接口及其实现类，用于数据库的增删改操作。文章通过测试类进行了功能验证。

摘要由CSDN通过智能技术生成

我之前的博文JDBC重构——JdbcUtils工具类的封装写了一个JdbcUtils的工具类，但是这个类也会有一个问题：如下图所示：

连接数据库的代码在java中是写死的，如果我们想要换一个数据库进行连接，就会很麻烦，这时我们需要添加一个配置文件，当我们更换数据库时，就是需要修改配置文件的参数即可。

注意：配置文件的内容不要有空格！

一、新建一个资源目录

资源目录下新建一个配置文件

mysqldriver=com.mysql.cj.jdbc.Driver
mysqlurl=jdbc:mysql://192.168.180.141:3306/jdbcstudb
mysqlusername=root
mysqlpwd=root

二、定义一个基类BaseDao

基类BaseDao用来加载properties文件，实例化Connection，释放资源，执行公共方法，简单理解，还是把一些冗余的代码进行封装。

package com.atguigu.kb21.dao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

/**
 * 加载properties文件，实例化Connection，释放资源，执行公共方法
 */
public class BaseDao {
    // 将配置文件映射为属性
    private static String diver;
    private static String url;
    private static String username;
    private static String pwd;

    static {
        // 加载配置文件
        Properties properties = new Properties();
        InputStream inputStream = BaseDao.class.getClassLoader().getResourceAsStream("database.properties");
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        diver = properties.getProperty("mysqldriver");
        url = properties.getProperty("mysqlurl");
        username = properties.getProperty("mysqlusername");
        pwd = properties.getProperty("mysqlpwd");

        System.out.println(diver+"\n"+url+"\n"+username+"\n"+pwd);
    }

    // 封装Connection连接数据库
    public Connection getConnection(){
        Connection connection = null;
        try {
            Class.forName(diver);
            connection = DriverManager.getConnection(url, username, pwd);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    // 封装释放资源
    public void close(Connection connection,PreparedStatement preparedStatement){
        try {
            if(null != connection){
                connection.close();
            }
            if(null != preparedStatement){
                preparedStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

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

    /**
     * 将新增，修改和删除操作统一抽象到一个方法中
     * @param sqlStr
     * @param params ...用法：表示n个参数[0,+∞]
    */
    // 在进行增删改操作时，每次执行sql都会返回int数值，因此，可以对执行sql的语句进行封装
    public int executeUpdate(String sqlStr,Object...params){
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        int num = -1;
        try {
            preparedStatement = connection.prepareStatement(sqlStr);
            if(null != params){
                for (int i = 0; i < params.length; i++) {
                    // 传参
                    preparedStatement.setObject(i+1,params[i]);
                }
            }
            num = preparedStatement.executeUpdate();
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.close(connection,preparedStatement);
        }
        return num;
    }


    public static void main(String[] args) {
        BaseDao baseDao = new BaseDao();

        // 测试getConnection方法
        Connection connection = baseDao.getConnection();
        System.out.println(connection+"连接成功！");

        // 测试executeUpdate方法
        String sql = "delete from dog where id = 21";
        int num = baseDao.executeUpdate(sql);
        System.out.println("操作成功！"+num);

        // 测试close方法
        baseDao.close(connection);
        System.out.println("释放资源成功！");
    }
}

三、定义Dog类

我们将sql语句的运行结果输出到控制台后，对应操作的表要作为类，方便后续的操作，这里我们还是用dog表，将dog表中的所有字段映射为Dog类的属性

package com.atguigu.kb21.pojo;

import java.util.Date;

/**
 * SQL语句要进行java操作
 */
public class Dog {
    // 将dog表中的每一个字段映射为Dog类的每一个属性
    private Integer id;
    private String name;
    private Integer health;
    private Integer love;
    private String strain;
    private Date lytime;

    // 这里的有参构造方法重载是因为id是自动生成，lytime为系统时间，所以不需要手动传参
    public Dog(String name, Integer health, Integer love, String strain) {
        this.name = name;
        this.health = health;
        this.love = love;
        this.strain = strain;
    }

    public Dog(Integer id, String name, Integer health, Integer love, String strain, Date lytime) {
        this.id = id;
        this.name = name;
        this.health = health;
        this.love = love;
        this.strain = strain;
        this.lytime = lytime;
    }

    public Dog() {
    }

    @Override
    public String toString() {
        return "Dog{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", health=" + health +
                ", love=" + love +
                ", strain='" + strain + '\'' +
                ", lytime=" + lytime +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getHealth() {
        return health;
    }

    public void setHealth(Integer health) {
        this.health = health;
    }

    public Integer getLove() {
        return love;
    }

    public void setLove(Integer love) {
        this.love = love;
    }

    public String getStrain() {
        return strain;
    }

    public void setStrain(String strain) {
        this.strain = strain;
    }

    public Date getLytime() {
        return lytime;
    }

    public void setLytime(Date lytime) {
        this.lytime = lytime;
    }
}

四、新建DogDao接口，实现增删改功能

package com.atguigu.kb21.dao;

import com.atguigu.kb21.pojo.Dog;

/**
 * 对数据库中Dog表的操作定义，比如，新增宠物，根据id删除宠物，根据健康值删除宠物
 * 根据id修改宠物信息
 * 根据id查询宠物信息
 * 根据多条件查询宠物信息
 */
public interface DogDao {
    // 新增宠物的功能
    Integer saveDog(Dog dog);

    // 根据id修改宠物信息
    Integer updateDog(Dog dog);

    // 根据id删除宠物信息
    Integer delById(Integer id);

    // 根据健康值删除宠物信息
    Integer delByHealth(Integer health);
}

五、新建DogDaoImpl实现类，继承基类BaseDao，并且实现DogDao接口的功能

package com.atguigu.kb21.dao;

import com.atguigu.kb21.pojo.Dog;

import java.sql.Connection;

public class DogDaoImpl extends BaseDao implements DogDao{
    @Override
    public Integer saveDog(Dog dog) {
//      Connection connection = super.getConnection();// 这句代码也可以不写，因为在调用执行方法时，执行方法同时也调用了getConnection方法
        String sql = "insert into dog(name,health,love,strain,lytime) values(?,?,?,?,now())";
        int num = super.executeUpdate(sql, dog.getName(), dog.getHealth(), dog.getLove(), dog.getStrain());
        System.out.println("新增成功！"+num);
        return num;
    }

    @Override
    public Integer updateDog(Dog dog) {
        Connection connection = super.getConnection();
        String sql = "update dog set name = ? , health = ? where id = ?";
        int num = super.executeUpdate(sql, dog.getName(), dog.getHealth(), dog.getId());
        System.out.println("修改成功！"+num);
        return num;
    }

    @Override
    public Integer delById(Integer id) {
        Connection connection = super.getConnection();
        String sql = "delete from dog where id = ?";
        int num = super.executeUpdate(sql, id);
        System.out.println("删除id成功！"+num);
        return num;
    }

    @Override
    public Integer delByHealth(Integer health) {
        Connection connection = super.getConnection();
        String sql = "delete from dog where health = ?";
        int num = super.executeUpdate(sql, health);
        System.out.println("删除health成功！"+num);
        return num;
    }
}

六、新建DogTest测试类，对dog表进行增删改操作

package com.atguigu.kb21;

import com.atguigu.kb21.dao.DogDaoImpl;
import com.atguigu.kb21.pojo.Dog;
import org.junit.Test;

public class DogTest {
    @Test
    public void testDogDaoInsert(){
        DogDaoImpl dogDao = new DogDaoImpl();
        Dog dog = new Dog("旺财", 80, 90, "泰迪");
        dogDao.saveDog(dog);
    }

    @Test
    public void testDogDaoUpdate(){
        DogDaoImpl dogDao = new DogDaoImpl();
        Dog dog = new Dog("麦克斯", 35, 100, "雪纳瑞");
        dog.setId(14);
        dogDao.updateDog(dog);
    }

    @Test
    public void testDogDaoDelById(){
        DogDaoImpl dogDao = new DogDaoImpl();
        dogDao.delById(7);
    }

    @Test
    public void testDogDaoDelByHealth(){
        DogDaoImpl dogDao = new DogDaoImpl();
        dogDao.delByHealth(90);
    }
}

对master表进行增删改的操作

七、定义Master类

package com.atguigu.kb21.pojo;

public class Master {
    private Integer pid;
    private String name;
    private Integer age;
    private String gender;
    private Integer yearnum;
    private Integer did;
    private Dog dog;

    public Master(String name, Integer age, String gender, Integer yearnum, Integer did) {
        this.name = name;
        this.age = age;
        this.gender = gender;
        this.yearnum = yearnum;
        this.did = did;
    }

    public Master(Integer pid, String name, Integer age, String gender, Integer yearnum, Integer did, Dog dog) {
        this.pid = pid;
        this.name = name;
        this.age = age;
        this.gender = gender;
        this.yearnum = yearnum;
        this.did = did;
        this.dog = dog;
    }

    public Master() {
    }

    @Override
    public String toString() {
        return "Master{" +
                "pid=" + pid +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", gender='" + gender + '\'' +
                ", yearnum=" + yearnum +
                ", did=" + did +
                ", dog=" + dog +
                '}';
    }

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Integer getYearnum() {
        return yearnum;
    }

    public void setYearnum(Integer yearnum) {
        this.yearnum = yearnum;
    }

    public Integer getDid() {
        return did;
    }

    public void setDid(Integer did) {
        this.did = did;
    }

    public Dog getDog() {
        return dog;
    }

    public void setDog(Dog dog) {
        this.dog = dog;
    }
}

八、创建MasterDao接口，定义接口功能

package com.atguigu.kb21.dao;

import com.atguigu.kb21.pojo.Master;

/**
 * 要对Master表做的操作，接口方法写入到下面
 */
public interface MasterDao {
    // TODO 增 删 改 查询主人及狗狗的信息
    // 新增主人信息
    Integer saveMaster(Master master);

    // 根据pid修改主人信息
    Integer updateMaster(Master master);

    // 根据did删除主人信息
    Integer delByDid(Integer did);
}

九、创建MasterDaoImpl类，继承基类BaseDao，并且实现MasterDao接口的功能

package com.atguigu.kb21.dao;

import com.atguigu.kb21.pojo.Master;

/**
 * String name, Integer age, String gender, Integer yearnum, Integer did
 */
public class MasterDaoImpl extends BaseDao implements MasterDao{
    @Override
    public Integer saveMaster(Master master) {
        String sql = "insert into master(name,age,gender,yearnum,did) values(?,?,?,?,?)";
        int num = super.executeUpdate(sql,master.getName(),master.getAge(),master.getGender(),master.getYearnum(),master.getDid());
        System.out.println("新增成功！"+num);
        return num;
    }

    @Override
    public Integer updateMaster(Master master) {
        String sql = "update master set name = ?,age = ? where pid = ?";
        int num = super.executeUpdate(sql, master.getName(),master.getAge(),master.getPid());
        System.out.println("修改成功！"+num);
        return num;
    }

    @Override
    public Integer delByDid(Integer did) {
        String sql = "delete from master where did = ?";
        int num = super.executeUpdate(sql, did);
        System.out.println("删除成功！"+num);
        return num;
    }
}

十、创建MasterTest测试类，对master表进行增删改操作

package com.atguigu.kb21;

import com.atguigu.kb21.dao.MasterDaoImpl;
import com.atguigu.kb21.pojo.Master;
import org.junit.Test;

public class MasterTest {
    @Test
    public void testMasterDaoInsert(){
        MasterDaoImpl masterDao = new MasterDaoImpl();
        Master master = new Master("小红",29,"女",9,8);
        masterDao.saveMaster(master);
    }

    @Test
    public void testMasterDaoUpdate(){
        MasterDaoImpl masterDao = new MasterDaoImpl();
        Master master = new Master("小八",40,"女",7,6);
        master.setPid(1);
        masterDao.updateMaster(master);
    }

    @Test
    public void testMasterDaoDelByDid(){
        MasterDaoImpl masterDao = new MasterDaoImpl();
        masterDao.delByDid(6);
    }
}