代码的重构实例(Java JDBC)学习代码的重构

134 阅读 0 评论 89 点赞

我是靠谱客的博主迷路黄蜂，这篇文章主要介绍代码的重构实例(Java JDBC)学习代码的重构，现在分享给大家，希望可以做个参考。

代码的重构

在我平时的代码开发中，各个部分的功能确实能够实现，但是缺少重构，代码显得十分的臃肿，扩展性差、不易于后续的维护。
下面就简单的Java JDBC的数据访问操作，进行重构的尝试。

声明

本博客内容，借鉴于书籍《Spring5 核心原理与30个类手写实战》，谭勇德著

原始代码

本人用MySQL作为数据，并在springmodel数据库中创建一张名为t_student的数据表。

复制代码

package Test.StudentService;
import Test.Entity.student;
import Test.Utils.DbUtil;
import java.sql.*;
/**
* @Classname stuService
* @Description TODO
* @Date 2020/3/24 18:30
* @Created by ASUS
*/
public class stuService implements IService{
/**
*功能描述：将传入的学生信息加入到数据库中
*@param stu the student need to be saved.
*Author Jason
*Date 2020-03-24 18:33
*/
@Override
public void save_student(student stu){
String sql = "INSERT INTO t_student(name,age) VALUES(?,?)";
Connection connection = null;
PreparedStatement preparedStatement = null;
int age = stu.getStu_Age();
String name = stu.getStu_Name();
try {
Class.forName("com.mysql.jdbc.Driver");//注册驱动
connection = DriverManager.getConnection//获取连接
("jdbc:mysql://localhost:3306/springmodel?serverTimezone=UTC&characterEncoding=utf8","root","");
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,name);
preparedStatement.setObject(2,age);
preparedStatement.execute();//执行SQL语句
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
try{
if(preparedStatement != null){
preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try{
if(connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
*功能描述：根据id值在数据库中检索并删除数据
*@param id the id of the student that need to be deleted.
*Author Jason
*Date 2020-03-24 18:36
*/
@Override
public void delete_stu_byId(int id){
String sql = "DELETE FROM t_student WHERE age=?";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName("com.mysql.jdbc.Driver");//注册驱动
connection = DriverManager.getConnection//获取连接
("jdbc:mysql://localhost:3306/springmodel?serverTimezone=UTC&characterEncoding=utf8","root","");
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,id);
preparedStatement.execute();//执行SQL语句
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
try{
if(preparedStatement != null){
preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try{
if(connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
*功能描述：更新学生的信息数据
*@param stu the student that need to be pull into database.
*Author Jason
*Date 2020-03-24 18:37
*/
@Override
public void update_stu(student stu){
String sql = "UPDATE t_student SET name=? WHERE age=?";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName("com.mysql.jdbc.Driver");//注册驱动
connection = DriverManager.getConnection//获取连接
("jdbc:mysql://localhost:3306/springmodel?serverTimezone=UTC&characterEncoding=utf8","root","");
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,stu.getStu_Name());
preparedStatement.setObject(2,stu.getStu_Age());
preparedStatement.execute();//执行SQL语句
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
try{
if(preparedStatement != null){
preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try{
if(connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
package Test.StudentService;
import Test.Entity.student;
import Test.Utils.DbUtil;
import java.sql.*;
/**
* @Classname stuService
* @Description TODO
* @Date 2020/3/24 18:30
* @Created by ASUS
*/
public class stuService implements IService{
/**
*功能描述：将传入的学生信息加入到数据库中
*@param stu the student need to be saved.
*Author Jason
*Date 2020-03-24 18:33
*/
@Override
public void save_student(student stu){
String sql = "INSERT INTO t_student(name,age) VALUES(?,?)";
Connection connection = null;
PreparedStatement preparedStatement = null;
int age = stu.getStu_Age();
String name = stu.getStu_Name();
try {
Class.forName("com.mysql.jdbc.Driver");//注册驱动
connection = DriverManager.getConnection//获取连接
("jdbc:mysql://localhost:3306/springmodel?serverTimezone=UTC&characterEncoding=utf8","root","");
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,name);
preparedStatement.setObject(2,age);
preparedStatement.execute();//执行SQL语句
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
try{
if(preparedStatement != null){
preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try{
if(connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
*功能描述：根据id值在数据库中检索并删除数据
*@param id the id of the student that need to be deleted.
*Author Jason
*Date 2020-03-24 18:36
*/
@Override
public void delete_stu_byId(int id){
String sql = "DELETE FROM t_student WHERE age=?";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName("com.mysql.jdbc.Driver");//注册驱动
connection = DriverManager.getConnection//获取连接
("jdbc:mysql://localhost:3306/springmodel?serverTimezone=UTC&characterEncoding=utf8","root","");
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,id);
preparedStatement.execute();//执行SQL语句
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
try{
if(preparedStatement != null){
preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try{
if(connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
*功能描述：更新学生的信息数据
*@param stu the student that need to be pull into database.
*Author Jason
*Date 2020-03-24 18:37
*/
@Override
public void update_stu(student stu){
String sql = "UPDATE t_student SET name=? WHERE age=?";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName("com.mysql.jdbc.Driver");//注册驱动
connection = DriverManager.getConnection//获取连接
("jdbc:mysql://localhost:3306/springmodel?serverTimezone=UTC&characterEncoding=utf8","root","");
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,stu.getStu_Name());
preparedStatement.setObject(2,stu.getStu_Age());
preparedStatement.execute();//执行SQL语句
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
try{
if(preparedStatement != null){
preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try{
if(connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}

重构优化过程

可以看出仅仅是简单的增删改的操作，代码行数100+，臃肿得不要太厉害。
其实我们细看代码，可以发现其中有很多的代码是可以抽取出来的，提高程序的复用率。
在每个方法中，都有重复的注册数据库驱动，获取数据库连接的操作代码，相似度很高，于是可以将其抽取到一个工具类DbUtil中。同时观察代码:

复制代码

1
2
3
4
Class.forName("com.mysql.jdbc.Driver");//注册驱动
connection = DriverManager.getConnection//获取连接
("jdbc:mysql://localhost:3306/springmodel?serverTimezone=UTC&characterEncoding=utf8","root","");

得出：有关数据连接的各种数据如：端口localhost:3306，数据库名称：springmodel,数据库的用户名和密码等都显式的出现在代码中，不符合软件架构设计的开闭原则（Open-Closed Pinciple,OCP）（指的是一个软件实体，如类、函数和模块等，对扩展开放，对修改关闭。）在需要对使用的数据名称改变时，就需要对源代码修改，可维护性低；另外，数据库的用户名和密码直接出现在代码中，不仅对后期的账户和密码的维护不利，还存在着一定的隐患。
于是可以将url,name,password等其他信息写入到一个mysql.properties文件中，程序通过读取配置信息，来获得建立连接需要的信息，不仅提高了代码的可扩展性，还一定程度上降低了安全隐患。同样对于不同的SQL语句也进行上述的设计，可以方便后续程序的维护。根据以上思考，建立工具类：

复制代码

/**
* @Classname DbUtil
* @Description TODO
* @Date 2020/3/25 18:22
* @Created by ASUS
*/
public class DbUtil implements Serializable {
private static Properties properties = null;
static {//静态的代码块，一旦产生该类的对象，自动执行该代码块
/*使用static 静态初始化代码块时候注意应用
*try{....}catch(Exception e){...},
*否则会发生类初始化异常*/
try{
//
ClassLoader loader = Thread.currentThread().getContextClassLoader();
//
InputStream is = loader.getResourceAsStream("");
InputStream is = new FileInputStream(new File("config/mysql.properties"));
properties = new Properties();
properties.load(is);
//加载数据库的properties文件
//加载数据库注册驱动
Class.forName(properties.getProperty("driver"));
} catch (Exception e) {
e.printStackTrace();
}
}
/**
*功能描述：返回数据库的连接connection
* 声明为类的静态方法
*/
public static Connection getConnect(){
Connection connection = null;
String url = properties.getProperty("url");
String name = properties.getProperty("user");
String pwd = properties.getProperty("password");
try {
connection = DriverManager.getConnection(url,name,pwd);
} catch (SQLException e) {
e.printStackTrace();
}
return
connection;
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

/**
* @Classname DbUtil
* @Description TODO
* @Date 2020/3/25 18:22
* @Created by ASUS
*/
public class DbUtil implements Serializable {
private static Properties properties = null;
static {//静态的代码块，一旦产生该类的对象，自动执行该代码块
/*使用static 静态初始化代码块时候注意应用
*try{....}catch(Exception e){...},
*否则会发生类初始化异常*/
try{
//
ClassLoader loader = Thread.currentThread().getContextClassLoader();
//
InputStream is = loader.getResourceAsStream("");
InputStream is = new FileInputStream(new File("config/mysql.properties"));
properties = new Properties();
properties.load(is);
//加载数据库的properties文件
//加载数据库注册驱动
Class.forName(properties.getProperty("driver"));
} catch (Exception e) {
e.printStackTrace();
}
}
/**
*功能描述：返回数据库的连接connection
* 声明为类的静态方法
*/
public static Connection getConnect(){
Connection connection = null;
String url = properties.getProperty("url");
String name = properties.getProperty("user");
String pwd = properties.getProperty("password");
try {
connection = DriverManager.getConnection(url,name,pwd);
} catch (SQLException e) {
e.printStackTrace();
}
return
connection;
}
}

mysql.properties

复制代码

1
2
3
4
5
6
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/springmodel?serverTimezone=UTC&characterEncoding=utf8
user=root
password=
serverIP=127.0.0.1

SQL.properties

复制代码

1
2
3
4
5
6
INSERT=INSERT INTO t_student(name,age) VALUES (?,?)
UPDATE=UPDATE t_student SET name=? WHERE age=?
DELETE=DELETE FROM t_student WHERE age=?
QUERY_AGE=SELECT * FROM t_student WHERE age=?
QUERY_ALL=SELECT * FROM t_student

然后在调用的方法中直接调用getConnect()方法即可得到与数据库的连接。程序的可读性也得到提高。到这里似乎已经完成了优化，但仔细观察，代码中除去已经优化的建立连接的部分，仍然有很多的代码类似，比如在进行数据库操作之后，关闭相应的连接，释放资源的代码即可以抽出到工具类中。在DML操作中，除了SQL语句的设置不同，其他部分基本一致，可以进行相同部分抽取，不同部分以参数的形式传入。这些抽取可以构建一个模板类
JDBCTemplate.java

复制代码

/**
* @Classname JDBCtemplate
* @Description TODO
* @Date 2020/3/26 9:38
* @Created by ASUS
*/
public class JDBCtemplate {
//Query template统一查询模版(DQL),借助泛型方便功能的扩展
@Nullable
public static <E> E Query(String sql, IRowMapper<E> mapper, Object...params){
//
List<student> studentList = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
try {
connection = DbUtil.getConnect();
preparedStatement = connection.prepareStatement(sql);
setPreparedStatement(preparedStatement,params);
rs = preparedStatement.executeQuery();
return mapper.mapping(rs);//处理结果集，由实现类自己完成
} catch (Exception e) {
e.printStackTrace();
} finally {
DbUtil.close(rs,preparedStatement,connection);
}
return null;
}
//DML
public static void Manipulation(String sql,Object...params){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = DbUtil.getConnect();
preparedStatement = connection.prepareStatement(sql);
setPreparedStatement(preparedStatement,params);//设置值
preparedStatement.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {//释放资源
DbUtil.close(null,preparedStatement,connection);
}
}
//处理preparedStatement,设置值
private static void setPreparedStatement(PreparedStatement preparedStatement,Object... params) throws SQLException {
for(int i = 0; i < params.length;i++){
preparedStatement.setObject(i+1,params[i]);
}
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
/**
* @Classname JDBCtemplate
* @Description TODO
* @Date 2020/3/26 9:38
* @Created by ASUS
*/
public class JDBCtemplate {
//Query template统一查询模版(DQL),借助泛型方便功能的扩展
@Nullable
public static <E> E Query(String sql, IRowMapper<E> mapper, Object...params){
//
List<student> studentList = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
try {
connection = DbUtil.getConnect();
preparedStatement = connection.prepareStatement(sql);
setPreparedStatement(preparedStatement,params);
rs = preparedStatement.executeQuery();
return mapper.mapping(rs);//处理结果集，由实现类自己完成
} catch (Exception e) {
e.printStackTrace();
} finally {
DbUtil.close(rs,preparedStatement,connection);
}
return null;
}
//DML
public static void Manipulation(String sql,Object...params){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = DbUtil.getConnect();
preparedStatement = connection.prepareStatement(sql);
setPreparedStatement(preparedStatement,params);//设置值
preparedStatement.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {//释放资源
DbUtil.close(null,preparedStatement,connection);
}
}
//处理preparedStatement,设置值
private static void setPreparedStatement(PreparedStatement preparedStatement,Object... params) throws SQLException {
for(int i = 0; i < params.length;i++){
preparedStatement.setObject(i+1,params[i]);
}
}
}

对于模板类的设计,充分的考虑到可扩展性，如果只是对t_stuent的数据和类student的操作，不做其他任何的设计就已经可以了，但是如果以后面对业务的扩展或者更改，就显得有点力不从心了。其实针对不同的表的返回集，应该有不同的操作，而怎样操作返回集，不是模板类所关心的内容，而根据单一职责原则（Simple Responsibility Pinciple,SRP），解耦合，于是创建了带泛型的一个接口：IRowMapper

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/**
* @Classname IRowMapper
* @Description TODO
* @Date 2020/3/26 13:51
* @Created by ASUS
*/
public interface IRowMapper<E> {
/**
*功能描述：根据结果集的类型进行对应处理
*@param resultSet 待处理的结果集
*Author Jason
*Date 2020-03-26 13:52
*/
E mapping(ResultSet resultSet)throws Exception;
}

对从数据库中查询得到的返回集ResultSet由其具体的实现类完成，这样一来，程序就不止是能够查询t_student的数据，还能查如t__teacher表中的数据，扩展性得到提高。
好了，到了这里，这部分的代码重构应该就差不多了。
下面看看优化后的代码：

优化后的代码

复制代码

/**
* @Classname stuServicee_2
* @Description TODO
* @Date 2020/3/26 9:35
* @Created by ASUS
*/
public class stuService_2 implements IService {
//各种在SQL语句存储在config/SQL.properties文件中
private static final String INSERT = "INSERT";
private static final String UPDATE = "UPDATE";
private static final String DELETE = "DELETE";
private static final String QUERY_AGE = "QUERY_AGE";
private static final String QUERY_ALL = "QUERY_ALL";
@Override
public void save_student(student stu) {
String sql = DbUtil.getSQL(INSERT);
JDBCtemplate.Manipulation(sql,stu.getStu_Name(),stu.getStu_Age());
}
@Override
public void delete_stu_byId(int id) {
String sql = DbUtil.getSQL(DELETE);
JDBCtemplate.Manipulation(sql,id);
}
@Override
public void update_stu(student stu) {
String sql = DbUtil.getSQL(UPDATE);
JDBCtemplate.Manipulation(sql,stu.getStu_Name(),stu.getStu_Age());
}
//根据年龄（当前认为的主键,实际数据库中没有设置主键）查询
public student Query_stu(int age){
String sql = DbUtil.getSQL(QUERY_AGE);
List<student> students = JDBCtemplate.Query(sql, new StudentRowMapper(),age);
assert students != null;
return students.size() > 0 ? students.get(0) : null;
}
//以List集合的形式，返回所有表中的数据
public List<student> Query_stu(){
String sql = DbUtil.getSQL(QUERY_ALL);
return JDBCtemplate.Query(sql, new StudentRowMapper());
}
static class StudentRowMapper implements IRowMapper<List<student>>{
/**
* 功能描述：处理学生结果集数据
* @param resultSet 待处理的结果集
* Author Jason
*/
@Override
public List<student> mapping(ResultSet resultSet) throws Exception {
List<student> students = new ArrayList<>();
while (resultSet.next()){
students.add(new student(resultSet.getInt("age"),resultSet.getString("name")));
}
return students;
}
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
/**
* @Classname stuServicee_2
* @Description TODO
* @Date 2020/3/26 9:35
* @Created by ASUS
*/
public class stuService_2 implements IService {
//各种在SQL语句存储在config/SQL.properties文件中
private static final String INSERT = "INSERT";
private static final String UPDATE = "UPDATE";
private static final String DELETE = "DELETE";
private static final String QUERY_AGE = "QUERY_AGE";
private static final String QUERY_ALL = "QUERY_ALL";
@Override
public void save_student(student stu) {
String sql = DbUtil.getSQL(INSERT);
JDBCtemplate.Manipulation(sql,stu.getStu_Name(),stu.getStu_Age());
}
@Override
public void delete_stu_byId(int id) {
String sql = DbUtil.getSQL(DELETE);
JDBCtemplate.Manipulation(sql,id);
}
@Override
public void update_stu(student stu) {
String sql = DbUtil.getSQL(UPDATE);
JDBCtemplate.Manipulation(sql,stu.getStu_Name(),stu.getStu_Age());
}
//根据年龄（当前认为的主键,实际数据库中没有设置主键）查询
public student Query_stu(int age){
String sql = DbUtil.getSQL(QUERY_AGE);
List<student> students = JDBCtemplate.Query(sql, new StudentRowMapper(),age);
assert students != null;
return students.size() > 0 ? students.get(0) : null;
}
//以List集合的形式，返回所有表中的数据
public List<student> Query_stu(){
String sql = DbUtil.getSQL(QUERY_ALL);
return JDBCtemplate.Query(sql, new StudentRowMapper());
}
static class StudentRowMapper implements IRowMapper<List<student>>{
/**
* 功能描述：处理学生结果集数据
* @param resultSet 待处理的结果集
* Author Jason
*/
@Override
public List<student> mapping(ResultSet resultSet) throws Exception {
List<student> students = new ArrayList<>();
while (resultSet.next()){
students.add(new student(resultSet.getInt("age"),resultSet.getString("name")));
}
return students;
}
}
}

与之前的代码相比，不仅是代码量得到很大程度的精简，更重要的是代码的可读性提高，可扩展性得到升，后期的维护也变得更加方便。
在这里我只是简单地进行了一小部分的代码的重构，以后随着功能的复杂程度不断的加大，程序之间的关系就会变得十分的复杂，而重构就可以将海量的代码按照逻辑有效的设计与实现，在项目的后期维护和扩展时都有很重要的作用。