文章目录
1. 使用数据库实现题目管理
1.1 设计数据库
题目表一般有的属性:
- 题号
- 标题
- 难度
- 描述
- 测试用例
- 自带的代码
create database if not exists Oj_Online;
use Oj_Online;
drop table if exists oj_problem;
-- 题目表
create table oj_problem(
-- 题目的序号
id int primary key auto_increment,
-- 题目的标题
title varchar(50),
-- 题目的难度
level varchar(50),
-- 题目的描述
description varchar(4096),
-- 题目给定的代码
templateCode varchar(4096),
-- 题目的测试用例
testCode varchar(4096)
);
1.2 封装 DBUtil
在 common 中创建 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 {
public static final String URL = "jdbc:mysql://127.0.0.1:3306/Oj_Online?characterEncoding=utf8&useSSL=false";
public static final String USERNAME = "root";
public static final String PASSWORD = "";
private static volatile DataSource dataSource = null;
private static DataSource getDataSource() {
if(dataSource == null){
synchronized (DBUtil.class){
if(dataSource == null){
dataSource = new MysqlDataSource();
((MysqlDataSource) dataSource).setURL(URL);
((MysqlDataSource) dataSource).setUser(USERNAME);
((MysqlDataSource) dataSource).setPassword(PASSWORD);
}
}
}
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();
}
}
}
}
1.3 创建 Problem类
这个类表示一个题目
package dao;
public class Problem {
private int id;
private String title;
private String level;
private String description;
private String templateCode;
private String testCode;
// ...一堆getter和setter
}
1.4 创建 ProblemDao类
这个类封装了对 Problem类的增删改查操作.
1.4.1 实现新增题目的功能
/**
* 新增题目
* @param problem 题目
*/
public void insert(Problem problem){
Connection connection = null;
PreparedStatement statement =null;
try {
// 1. 建立连接
connection = DBUtil.getConnection();
// 2. 拼装 SQL 语句
String sql = "insert into oj_problem 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. 执行 SQL 语句
int ret = statement.executeUpdate();
if(ret == 1){
System.out.println("插入成功!");
}else {
System.out.println("插入失败!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 4. 关闭释放资源
DBUtil.close(connection,statement,null);
}
}
1.4.2 实现删除题目的功能
/**
* 删除对应id的题目
* @param id 题目的序号
*/
public void delete(int id){
Connection connection = null;
PreparedStatement statement = null;
try {
// 1. 建立连接
connection = DBUtil.getConnection();
// 2. 拼装 SQL 语句
String sql = "delete from oj_problem where id = ?";
statement = connection.prepareStatement(sql);
statement.setInt(1,id);
// 3. 执行 SQL 语句
int ret = statement.executeUpdate();
if(ret == 1){
System.out.println("删除成功!");
}else {
System.out.println("删除失败!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 4. 关闭释放资源
DBUtil.close(connection,statement,null);
}
}
1.4.3 实现查询题目列表的功能
/**
* 查询题目列表
* @return 题目列表
*/
public List<Problem> selectAll(){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
List<Problem> list = new ArrayList<>();
try {
// 1. 建立连接
connection = DBUtil.getConnection();
// 2. 拼装 SQL 语句
String sql = "select id,title,level from oj_problem";
statement = connection.prepareStatement(sql);
// 3. 执行 SQL 语句
resultSet = statement.executeQuery();
// 4. 遍历结果集
while (resultSet.next()){
Problem problem = new Problem();
problem.setId(resultSet.getInt("id"));
problem.setTitle(resultSet.getString("title"));
problem.setLevel(resultSet.getString("level"));
list.add(problem);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
}finally {
// 5. 关闭释放资源
DBUtil.close(connection,statement,resultSet);
}
return null;
}
1.4.4 实现查找对应id题目的功能
/**
* 查找对应id的题目
* @param id 题目序号
* @return 返回一个题目
*/
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_problem where id = ?";
statement = connection.prepareStatement(sql);
statement.setInt(1,id);
// 3. 执行 SQL 语句
resultSet = statement.executeQuery();
// 4. 遍历结果集
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 e) {
e.printStackTrace();
} finally {
// 5. 关闭释放资源
DBUtil.close(connection,statement,resultSet);
}
return null;
}
1.5 测试这几个功能
1.5.1 设计测试用例测试新增功能
public static void main(String[] args) {
ProblemDao problemDao = new ProblemDao();
Problem problem = new Problem();
problem.setTitle("两数之和");
problem.setLevel("简单");
problem.setDescription("给定一个整数数组 nums和一个整数目标值 target，请你在该数组中找出 和为目标值 target 的那两个整数，并返回它们的数组下标。\n" +
"你可以假设每种输入只会对应一个答案。但是，数组中同一个元素在答案里不能重复出现。\n" +
"你可以按任意顺序返回答案。\n" +
"示例 1：\n" +
"输入：nums = [2,7,11,15], target = 9\n" +
"输出：[0,1]\n" +
"解释：因为 nums[0] + nums[1] == 9 ，返回 [0, 1] 。\n" +
"示例 2：\n" +
"输入：nums = [3,2,4], target = 6\n" +
"输出：[1,2]\n" +
"示例 3：\n" +
"输入：nums = [3,3], target = 6\n" +
"输出：[0,1]\n" +
"\n" +
"提示：\n" +
"2 <= nums.length <= 104\n" +
"-109 <= nums[i] <= 109\n" +
"-109 <= target <= 109\n" +
"只会存在一个有效答案\n" +
"进阶：你可以想出一个时间复杂度小于 O(n2) 的算法吗？\n" +
"来源：力扣（LeetCode）\n" +
"链接：https://leetcode.cn/problems/two-sum\n" +
"著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。");
problem.setTemplateCode("class Solution {\n" +
" public int[] twoSum(int[] nums, int target) {\n" +
"\n" +
" }\n" +
"}");
problem.setTestCode(" public static void main(String[] args) {\n" +
" Solution solution = new Solution();\n" +
" int[] test1 = {2,7,11,15};\n" +
" int target1 = 9;\n" +
" int[] res1 = solution.twoSum(test1, target1);\n" +
" if (res1.length == 2 && res1[0] == 0 && res1[1] == 1){\n" +
" System.out.println(\"testcase1 OK!\");\n" +
" }else {\n" +
" System.out.println(\"testcase1 FAILED!\");\n" +
" }\n" +
" int[] test2 = {3,2,4};\n" +
" int target2 = 6;\n" +
" int[] res2 = solution.twoSum(test2, target2);\n" +
" if (res2.length == 2 && res2[0] == 1 && res2[1] == 2){\n" +
" System.out.println(\"testcase2 OK!\");\n" +
" }else {\n" +
" System.out.println(\"testcase2 FAILED!\");\n" +
" }\n" +
" }");
problemDao.insert(problem);
}
查看数据库中的内容
1.5.2 测试删除功能
再次新增一个内容
执行以下代码
ProblemDao problemDao = new ProblemDao();
problemDao.delete(2);
再次查看数据库
1.5.3 测试查询列表功能
ProblemDao problemDao = new ProblemDao();
List<Problem> list = problemDao.selectAll();
System.out.println(list);
1.5.4 测试查询对应id的功能
ProblemDao problemDao = new ProblemDao();
Problem problem = problemDao.selectOne(1);
System.out.println(problem);
