DBUtil

package common;import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class DBUtil {// 需要封裝和數據庫之間的連接操作.private static final String URL = "jdbc:mysql://127.0.0.1:3306/xxx?characterEncoding=utf8&useSSL=false";private static final String USERNAME = "xxx";private static final String PASSWORD = "xxx";private static volatile DataSource dataSource = null;private static DataSource getDataSource() {if (dataSource == null) {synchronized (DBUtil.class) {if (dataSource == null) {MysqlDataSource mysqlDataSource = new MysqlDataSource();mysqlDataSource.setURL(URL);mysqlDataSource.setUser(USERNAME);mysqlDataSource.setPassword(PASSWORD);dataSource = mysqlDataSource;}}}return dataSource;}public static Connection getConnection() throws SQLException {return getDataSource().getConnection();}public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) {if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}
}

xxxDAO

public class ProblemDAO {public void insert(Problem problem) {Connection connection = null;PreparedStatement statement = null;try {// 1. 和數據庫建立連接connection = DBUtil.getConnection();// 2. 構造 SQL 語句String sql = "insert into xxx values(null, ?, ?, ?, ?, ?)";statement = connection.prepareStatement(sql);statement.setString(1, problem.getTitle());statement.setString(2, problem.getLevel());statement.setString(3, problem.getDescription());statement.setString(4, problem.getTemplateCode());statement.setString(5, problem.getTestCode());// 3. 執行 SQLint ret = statement.executeUpdate();if (ret != 1) {System.out.println("題目新增失敗!");} else {System.out.println("題目新增成功!");}} catch (SQLException e) {e.printStackTrace();} finally {DBUtil.close(connection, statement, null);}}public void delete(int id) {Connection connection = null;PreparedStatement statement = null;try {// 1. 和數據庫建立連接connection = DBUtil.getConnection();// 2. 拼裝 SQL 語句String sql = "delete from xxx where id = ?";statement = connection.prepareStatement(sql);statement.setInt(1, id);// 3. 執行 SQLint ret = statement.executeUpdate();if (ret != 1) {System.out.println("刪除題目失敗!");} else {System.out.println("刪除題目成功!");}} catch (SQLException throwables) {throwables.printStackTrace();} finally {DBUtil.close(connection, statement, null);}}// 這個操作是把當前題目列表中的所有題都查出來了// 萬一數據庫中的題目特別多, 咋辦? 只要實現 "分頁查詢" 即可. 后臺實現分頁查詢, 非常容易.// 前端傳過來一個當前的 "頁碼" , 根據頁碼算一下, 依據 sql limit offset 語句, 要算出來 offset 是 幾// 但是前端這里實現一個分頁器稍微麻煩一些(比后端要麻煩很多). 此處暫時不考慮分頁功能.public List<Problem> selectAll() {List<Problem> problems = new ArrayList<>();Connection connection = null;PreparedStatement statement = null;ResultSet resultSet = null;try {// 1. 和數據庫建立連接connection = DBUtil.getConnection();// 2. 拼裝 SQLString sql = "select id, title, level from xxx";statement = connection.prepareStatement(sql);// 3. 執行 SQLresultSet = statement.executeQuery();// 4. 遍歷 resultSetwhile (resultSet.next()) {// 每一行都是一個 Problem 對象Problem problem = new Problem();problem.setId(resultSet.getInt("id"));problem.setTitle(resultSet.getString("title"));problem.setLevel(resultSet.getString("level"));problems.add(problem);}return problems;} catch (SQLException throwables) {throwables.printStackTrace();} finally {DBUtil.close(connection, statement, resultSet);}return null;}public Problem selectOne(int id) {Connection connection = null;PreparedStatement statement = null;ResultSet resultSet = null;try {// 1. 和數據庫建立連接connection = DBUtil.getConnection();// 2. 拼接 SQL 語句String sql = "select * from oj_table where id = ?";statement = connection.prepareStatement(sql);statement.setInt(1, id);// 3. 執行 SQLresultSet = statement.executeQuery();// 4. 遍歷查詢結果. (由于 id 是主鍵, 按照 id 查找的結果一定是唯一的)if (resultSet.next()) {Problem problem = new Problem();problem.setId(resultSet.getInt("id"));problem.setTitle(resultSet.getString("title"));problem.setLevel(resultSet.getString("level"));problem.setDescription(resultSet.getString("description"));problem.setTemplateCode(resultSet.getString("templateCode"));problem.setTestCode(resultSet.getString("testCode"));return problem;}} catch (SQLException throwables) {throwables.printStackTrace();} finally {DBUtil.close(connection, statement, resultSet);}return null;}