JDBC的Dao层设计

最新推荐文章于 2024-11-12 21:05:28 发布

Xsqone

最新推荐文章于 2024-11-12 21:05:28 发布

阅读量493

点赞数 3

分类专栏： Java从入门到入土文章标签：数据库 java

本文链接：https://blog.youkuaiyun.com/qq_38591577/article/details/128555737

版权

Java从入门到入土专栏收录该内容

12 篇文章

订阅专栏

文章介绍了如何通过创建BaseDao类来封装JDBC的数据库操作，包括从database.properties文件读取配置信息，建立数据库连接，以及提取增、删、改、查的方法。BaseDao类中包含了静态代码块来初始化连接参数，提供了getConnection()方法获取连接，以及close()方法释放资源。此外，还实现了executeUpdate()用于执行更新操作，selectObjectById()和selectObjectList()用于查询单条和多条数据，所有这些都提高了代码复用性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言

在前面的博文中JDBC基础使用写增删改查会出现很多重复代码，可以将重复代码提取出来。

一、设计JDBC

层次结构：
项目结构：

二、BaseDao

2.1、将资源提取到文件

在database.properties中将需要的资源放入其中：

mysqlDriver=com.mysql.cj.jdbc.Driver
mysqlUrl=jdbc:mysql://192.168.136.204:3306/jdbcdb
mysqlUser=root
mysqlPwd=root

创建一个jdbc的基层类BaseDao(读取配置文件放入其中)：

	private static String mysqlDriver;
    private static String mysqlUrl;
    private static String mysqlUser;
    private static String mysqlPwd;
    static {
        Properties properties = new Properties();
        InputStream inputStream = BaseDao.class.getClassLoader().getResourceAsStream("database.properties");
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        mysqlDriver = properties.getProperty("mysqlDriver");
        mysqlUrl = properties.getProperty("mysqlUrl");
        mysqlUser = properties.getProperty("mysqlUser");
        mysqlPwd = properties.getProperty("mysqlPwd");
//        System.out.println(mysqlDriver);
//        System.out.println(mysqlUrl);
//        System.out.println(mysqlUser);
//        System.out.println(mysqlPwd);
    }

将读取配置文件放入static代码块中，在BaseDao类加载的时候就会获取到资源文件中的资源

2.2、建立连接数据库提取方法

    /**
     * 建立connection链接
     * @return Connection
     */
    public Connection getConnection(){
        Connection connection = null;
        try {
            Class.forName(mysqlDriver);
            connection = DriverManager.getConnection(mysqlUrl, mysqlUser, mysqlPwd);

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return connection;
    }

2.3、释放资源提取方法

        /**
     * 重载 释放资源
     * @param resultSet 返回值
     * @param preparedStatement Statement
     * @param connection connection连接
     */
    public void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection){
        try {
            if (null!=resultSet)
                resultSet.close();
            if (null!=preparedStatement)
                preparedStatement.close();
            if (null!=connection)
                connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public void close(PreparedStatement preparedStatement,Connection connection){
        this.close(null,preparedStatement,connection);
    }
    public void close(Connection connection){
        this.close(null,connection);
    }

此处使用了重载（方法名相同，参数列表不同，功能相似）

2.4、将增删改提取方法

    /**
     * 增删改
     * @param sql sql语句
     * @param params 传参
     * @return 操作的条数
     */
    public Integer executeUpdate(String sql, Object... params){
        Integer num = -1;
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            if (null!=params) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i+1,params[i]);
                }
            }
            num = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {

            this.close(preparedStatement,connection);
        }
        if (num>0){
            System.out.println("操作成功");
        }
        return num;
    }

此处使用了传不定值的参使用(Object… params)
调用此方法只需要将sql语句和参数就可操作数据

2.5、提取查询一条数据的方法

    public T selectObjectById(RowMapper<T> row,String sql,Object... params){
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            if (params!=null) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i+1,params[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                return row.mapper(resultSet);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.close(resultSet,preparedStatement,connection);
        }

        return null;
    }

这里用到了泛型（由于返回的值不能直接确定），传接口（将resultSet处理方法实现接口此处就可直接返回需要的值）
调用此方法将处理resultSet的lambda、sql语句和参数即可获取所需的数据

2.6、提取查询多条数据的方法

public List<T> selectObjectList(RowMapper<T> row, String sql, Object... params){

        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<T> ts = new ArrayList<T>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            if (params!=null) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i+1,params[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ts.add(row.mapper(resultSet));
            }
            return ts;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.close(resultSet,preparedStatement,connection);
        }

    }

与查询一条类似，但是处理数据不同。

三、StudentDao接口


import org.example.demo.mysql.pojo.Student;

import java.util.List;

/**
 * @program: jdbcMy
 * @interfaceName StudentDao
 * @description:
 * @author: 太白
 * @create: 2023-01-03 14:21
 **/
public interface StudentDao {

    Integer saveStudent(Student student);

    Integer deleteStudent(String sid);

    Integer updateStudent(Student student);

    Student selectStudentById(String sid);

    List<Student> selectStudentList();
}

四、RowMapper工具接口

package org.example.demo.mysql.utils;

import java.sql.ResultSet;

/**
 * @program: jdbcMy
 * @interfaceName RowMapper
 * @description:
 * @author: 太白
 * @create: 2023-01-04 19:57
 **/
public interface RowMapper<T> {

    T mapper(ResultSet resultSet);
}

五、StudentDaoImpl实现类

package org.example.demo.mysql.dao2;

import org.example.demo.mysql.dao.StudentDao;
import org.example.demo.mysql.pojo.Student;
import org.example.demo.mysql.utils.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @program: jdbcMy
 * @interfaceName StudentDao2
 * @description:
 * @author: 太白
 * @create: 2023-01-04 20:00
 **/
public class StudentDao2Impl extends BaseDao2<Student> implements StudentDao {
//   增
    public Integer saveStudent(Student student) {
        String sql = "insert into student (sid,sname,sage,ssex) value (?,?,?,?)";
        Integer integer = super.executeUpdate(sql,student.getSid(),student.getSname(),student.getSage(),student.getSsex());
        return integer;
    }
//  删
    public Integer deleteStudent(String sid) {
        String sql = "delete from student where sid = ?";
        Integer integer = super.executeUpdate(sql,sid);
        return integer;
    }
//  改
    public Integer updateStudent(Student student) {
        String sql = "update student set sname=?,sage=?,ssex=? where sid=?";
        Integer integer = super.executeUpdate(sql,student.getSname(),student.getSage(),student.getSsex(),student.getSid());
        return integer;
    }
//  查一条数据
    public Student selectStudentById(String sid) {
        String sql = "select sid,sname,sage,ssex from student where sid = ?";
        Student student = super.selectObjectById(row, sql, sid);
        return student;
    }

//    实现处理result的接口方法（lambda表达式）
    RowMapper<Student> row = new RowMapper<Student>() {
        public Student mapper(ResultSet resultSet) {
            Student student = new Student();
            try {
                student.setSid(resultSet.getString("sid"));
                student.setSname(resultSet.getString("sname"));
                student.setSage(resultSet.getDate("sage"));
                student.setSsex(resultSet.getString("ssex"));
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

            return student;
        }
    };
//    查多条数据
    public List<Student> selectStudentList() {
        String sql = "select sid,sname,sage,ssex from student";

        List<Student> students = super.selectObjectList(row, sql);
        return students;
    }


}