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();
}
}
}
运行结果如下: