自定义DAO模式实现对数据库的增删改查

最新推荐文章于 2024-07-22 15:12:37 发布

沐风听雨_A

最新推荐文章于 2024-07-22 15:12:37 发布

阅读量458

点赞数 5

文章标签：数据库 java 算法

本文链接：https://blog.csdn.net/wehpd/article/details/137522925

版权

1、创建数据库管理类：

package com.db.daomodule;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseManager {
    private static DatabaseManager instance;
    private Connection connection;

    private DatabaseManager() {
        // 私有构造函数，禁止外部直接创建实例
    }

    public static synchronized DatabaseManager getInstance() {
        if (instance == null) {
            instance = new DatabaseManager();
        }
        return instance;
    }

    public Connection getConnection() throws SQLException {
        if (connection == null || connection.isClosed()) {
            // 数据库连接配置
            String url = "jdbc:mysql://localhost:3306/test_data";
            String username = "root";
            String password = "admin";

            connection = DriverManager.getConnection(url, username, password);
        }

        return connection;
    }
}

2、创建WordsInfoDao接口：

package com.db.daomodule;
import java.util.List;
//创建WordsInfoDao接口：
public interface WordsInfoDao {
	
    List<WordsInfo> getAllWords();

    WordsInfo getWordsById(int id);

    void addWords(WordsInfo words);

    void updateWords(WordsInfo words);

    void deleteWords(int id);
}

3、定义一个WordsInfo类来表示words_info表的数据：


public class WordsInfo {
    private int id;
    private String engName;
    private String chiVal;
    private String lastUsedTime;
    private int usedTimes;
    private String createdTime;
    private int priority;

    // getter和setter方法

    public int getId() {
        return id;
    }

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

    public String getEngName() {
        return engName;
    }

    public void setEngName(String engName) {
        this.engName = engName;
    }

    public String getChiVal() {
        return chiVal;
    }

    public void setChiVal(String chiVal) {
        this.chiVal = chiVal;
    }

    public String getLastUsedTime() {
        return lastUsedTime;
    }

    public void setLastUsedTime(String lastUsedTime) {
        this.lastUsedTime = lastUsedTime;
    }

    public int getUsedTimes() {
        return usedTimes;
    }

    public void setUsedTimes(int usedTimes) {
        this.usedTimes = usedTimes;
    }

    public String getCreatedTime() {
        return createdTime;
    }

    public void setCreatedTime(String createdTime) {
        this.createdTime = createdTime;
    }

    public int getPriority() {
        return priority;
    }

    public void setPriority(int priority) {
        this.priority = priority;
    }
}

4、创建WordsInfoDaoImpl类实现WordsInfoDao接口：

package com.db.daomodule;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
//创建WordsInfoDaoImpl类实现WordsInfoDao接口：
public class WordsInfoDaoImpl implements WordsInfoDao {
	
    private Connection connection;

    // 构造方法，用于获得数据库连接
    public WordsInfoDaoImpl(Connection connection) {
        this.connection = connection;
    }

    @Override
    public List<WordsInfo> getAllWords() {
        List<WordsInfo> wordsList = new ArrayList<>();

        String query = "SELECT * FROM words_info";

        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(query);

            while (resultSet.next()) {
                WordsInfo words = new WordsInfo();
                words.setId(resultSet.getInt("Id"));
                words.setEngName(resultSet.getString("eng_name"));
                words.setChiVal(resultSet.getString("chi_val"));
                words.setLastUsedTime(resultSet.getString("last_used_time"));
                words.setUsedTimes(resultSet.getInt("used_times"));
                words.setCreatedTime(resultSet.getString("created_time"));
                words.setPriority(resultSet.getInt("priority"));

                wordsList.add(words);
            }

            resultSet.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return wordsList;
    }

    @Override
    public WordsInfo getWordsById(int id) {
        WordsInfo words = new WordsInfo();

        String query = "SELECT * FROM words_info WHERE Id = ?";

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                words.setId(resultSet.getInt("Id"));
                words.setEngName(resultSet.getString("eng_name"));
                words.setChiVal(resultSet.getString("chi_val"));
                words.setLastUsedTime(resultSet.getString("last_used_time"));
                words.setUsedTimes(resultSet.getInt("used_times"));
                words.setCreatedTime(resultSet.getString("created_time"));
                words.setPriority(resultSet.getInt("priority"));
            }

            resultSet.close();
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return words;
    }

    @Override
    public void addWords(WordsInfo words) {
        String query = "INSERT INTO words_info (eng_name, chi_val, last_used_time, used_times, created_time, priority) VALUES (?, ?, ?, ?, ?, ?)";

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.setString(1, words.getEngName());
            preparedStatement.setString(2, words.getChiVal());
            preparedStatement.setString(3, words.getLastUsedTime());
            preparedStatement.setInt(4, words.getUsedTimes());
            preparedStatement.setString(5, words.getCreatedTime());
            preparedStatement.setInt(6, words.getPriority());

            preparedStatement.executeUpdate();

            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateWords(WordsInfo words) {
        String query = "UPDATE words_info SET eng_name = ?, chi_val = ?, last_used_time = ?, used_times = ?, created_time = ?, priority = ? WHERE Id = ?";

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.setString(1, words.getEngName());
            preparedStatement.setString(2, words.getChiVal());
            preparedStatement.setString(3, words.getLastUsedTime());
            preparedStatement.setInt(4, words.getUsedTimes());
            preparedStatement.setString(5, words.getCreatedTime());
            preparedStatement.setInt(6, words.getPriority());
            preparedStatement.setInt(7, words.getId());

            preparedStatement.executeUpdate();

            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteWords(int id) {
        String query = "DELETE FROM words_info WHERE Id = ?";

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.setInt(1, id);

            preparedStatement.executeUpdate();

            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5、在主程序中使用DAO接口和DAO实现类进行数据库操作：

package com.db.daomodule;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

//在主程序中使用以上的DAO接口和DAO实现类来进行数据库操作：
public class Main {
	public static void main(String[] args) {

		DatabaseManager manager = DatabaseManager.getInstance();
		// 获取当前时间
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String currentTime = dateFormat.format(new Date());

		try {

			// 获取数据库连接
			Connection connection = manager.getConnection();

			// 创建WordsInfoDao对象
			WordsInfoDao wordsInfoDao = new WordsInfoDaoImpl(connection);

			// 获取所有字典数据
			List<WordsInfo> wordsList = wordsInfoDao.getAllWords();
			for (WordsInfo words : wordsList) {
				System.out.println(words.getEngName() + ": " + words.getChiVal());
			}

			// 根据Id获取字典数据
			WordsInfo words = wordsInfoDao.getWordsById(1);
			System.out.println("Id: " + words.getId());
			System.out.println("English Name: " + words.getEngName());
			System.out.println("Chinese Value: " + words.getChiVal());

			// 添加新的字典数据
			WordsInfo newWords = new WordsInfo();
			newWords.setEngName("hello");
			newWords.setChiVal("非常好");
			newWords.setLastUsedTime(currentTime);
			newWords.setUsedTimes(1);
			newWords.setCreatedTime(currentTime);
			newWords.setPriority(0);
			wordsInfoDao.addWords(newWords);

			// 更新字典数据
			WordsInfo existingWords = wordsInfoDao.getWordsById(1);
			existingWords.setEngName("updated");
			existingWords.setChiVal("更新后的值");
			existingWords.setLastUsedTime(currentTime);
			wordsInfoDao.updateWords(existingWords);

			// 删除字典数据
			wordsInfoDao.deleteWords(69);

			// 关闭数据库连接
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

运行结果如下：