源码:https://github.com/2020GetGoodOffer/springStudy
存在问题的JDBC
平时我们写的代码虽然满足需求,但是可能不利于项目的开发和维护,以以下的JDBC代码为例:
public class ModifyStuInfo {
public void save(Student student){
String sql="insert into t_student(id,name) values (?,?)";
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接
connection= DriverManager.getConnection("jdbc:mysql:///test","root","root");
//创建语句对象
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setObject(1,student.getId());
preparedStatement.setObject(2,student.getName());
//执行语句
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (preparedStatement != null)
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void delete(Integer id){
String sql="delete from t_student where id=?";
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接
connection= DriverManager.getConnection("jdbc:mysql:///test","root","root");
//创建语句对象
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setObject(1,id);
//执行语句
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (preparedStatement != null)
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public List<Student> queryAll(){
List<Student> list=new ArrayList<>();
String sql="select * from t_student";
ResultSet resultSet=null;
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接
connection= DriverManager.getConnection("jdbc:mysql:///test","root","root");
//创建语句对象
preparedStatement=connection.prepareStatement(sql);
//执行语句
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
list.add(new Student(id,name));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (resultSet != null)
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (preparedStatement != null)
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
}
抽取数据库工具类
上述代码的功能没有问题,但重复代码过多,我们可以抽取将重复代码放到一个工具类中:
public class JDBCUtil {
private JDBCUtil(){}
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection("jdbc:mysql:///test","root","root");
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(ResultSet resultSet, Statement statement,Connection connection){
try {
if (resultSet != null)
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (statement != null)
statement.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
此时修改学生信息类可改为:
public class ModifyStuInfo {
public void save(Student student){
String sql="insert into t_student(id,name) values (?,?)";
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//获取连接
connection= JDBCUtil.getConnection();
//创建语句对象
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setObject(1,student.getId());
preparedStatement.setObject(2,student.getName());
//执行语句
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.close(null,preparedStatement,connection);
}
}
public void delete(Integer id){
String sql="delete from t_student where id=?";
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//获取连接
connection= JDBCUtil.getConnection();
//创建语句对象
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setObject(1,id);
//执行语句
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.close(null,preparedStatement,connection);
}
}
public List<Student> queryAll(){
List<Student> list=new ArrayList<>();
String sql="select * from t_student";
ResultSet resultSet=null;
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//获取连接
connection= JDBCUtil.getConnection();
//创建语句对象
preparedStatement=connection.prepareStatement(sql);
//执行语句
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
list.add(new Student(id,name));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.close(resultSet,preparedStatement,connection);
}
return list;
}
}
将数据放到配置文件
虽然完成了重复代码的抽取,但数据库中的账户和密码都直接显示在代码中,不利于后期账户密码的维护,我们可以建立一个db.properties来存储这些信息
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///test
username=root
passwd=root
在工具类中获取里面的信息:
static Properties properties;
static {
try {
ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
InputStream is = classLoader.getResourceAsStream("db.properties");
properties = new Properties();
properties.load(is);
Class.forName(properties.getProperty("driverClassName"));
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username"),properties.getProperty("passwd"));
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
创建模板方法
抽取到这里似乎已经完成,但在实现类中仍然有重复代码,在DML操作中,除了SQL语句和设置值不同,其他都一样,将相同的部分抽取,不同的部分通过参数传递进来,无法直接放在工具类,此时可以创建一个模板JDBCTemplate,创建一个DML的模板:
public class JDBCTemplate {
public static void update(String sql,Object[] params){
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//获取连接
connection= JDBCUtil.getConnection();
//创建语句对象
preparedStatement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++){
preparedStatement.setObject(i+1,params[i]);
}
//执行语句
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.close(null,preparedStatement,connection);
}
}
public static void update(String sql,Integer id){
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//获取连接
connection= JDBCUtil.getConnection();
//创建语句对象
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setObject(1,id);
//执行语句
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.close(null,preparedStatement,connection);
}
}
}
再创建一个DQL的模板方法:
public static List<Student> queryAll(String sql,Object...params){
List<Student> list=new ArrayList<>();
ResultSet resultSet=null;
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//获取连接
connection= JDBCUtil.getConnection();
//创建语句对象
preparedStatement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++){
preparedStatement.setObject(i+1,params[i]);
}
//执行语句
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
list.add(new Student(id,name));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.close(resultSet,preparedStatement,connection);
}
return list;
}
实现类直接调用方法即可:
public class ModifyStuInfo {
public void save(Student student){
String sql="insert into t_student(id,name) values (?,?)";
Object[] params=new Object[]{student.getId(),student.getName()};
JDBCTemplate.update(sql,params);
}
public void delete(Integer id){
String sql="delete from t_student where id=?";
JDBCTemplate.update(sql,id);
}
public List<Student> queryAll(){
String sql="select * from t_student";
return JDBCTemplate.queryAll(sql);
}
}
创建DAO接口
这样重复代码就基本处理好了,但还是有一个严重的问题:这个程序的DQL操作只能处理Student类和t_student表的相关数据,无法处理其他数据。不同表就应该有不同的列,不同列处理结果集的代码就应该不一样,处理结果集的操作只有DAO自己清楚。也就是说,处理结果的方法不应该放在模板方法中,应该由每个DAO自己处理,因此我们可以创建一个IRowMapper接口处理结果集:
public interface IRowMapper {
//处理结果集
List rowMapper(ResultSet resultSet) throws Exception;
}
在DQL模板中调用IRowMapper中的处理方法,提醒实现类自己去实现rowMapper方法
public static List<Student> queryAll(String sql,IRowMapper mapper,Object...params){
List<Student> list=new ArrayList<>();
ResultSet resultSet=null;
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//获取连接
connection= JDBCUtil.getConnection();
//创建语句对象
preparedStatement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++){
preparedStatement.setObject(i+1,params[i]);
}
//执行语句
resultSet = preparedStatement.executeQuery();
return mapper.rowMapper(resultSet);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.close(resultSet,preparedStatement,connection);
}
return list;
}
Student对于IRowMapper的实现类:
public class StudentRowMapper implements IRowMapper {
@Override
public List rowMapper(ResultSet resultSet) throws Exception {
List<Student> list=new ArrayList<>();
while (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
list.add(new Student(id,name));
}
return list;
}
}
调用方法:
public List<Student> queryAll(){
String sql="select * from t_student";
return JDBCTemplate.queryAll(sql,new StudentRowMapper());
}
利用泛型实现多功能
此时还有一个小问题,DQL不只查询学生信息(List类型),还查询学生数量,此时需要通过泛型来完成:
接口:
public interface IRowMapper<T> {
//处理结果集
T rowMapper(ResultSet resultSet) throws Exception;
}
实现类:
public class StudentRowMapper implements IRowMapper<List<Student>> {
@Override
public List<Student> rowMapper(ResultSet resultSet) throws Exception {
List<Student> list=new ArrayList<>();
while (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
list.add(new Student(id,name));
}
return list;
}
}
查询模板:
public static<T> T queryAll(String sql,IRowMapper<T> mapper,Object...params){
List<Student> list=new ArrayList<>();
ResultSet resultSet=null;
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//获取连接
connection= JDBCUtil.getConnection();
//创建语句对象
preparedStatement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++){
preparedStatement.setObject(i+1,params[i]);
}
//执行语句
resultSet = preparedStatement.executeQuery();
return mapper.rowMapper(resultSet);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.close(resultSet,preparedStatement,connection);
}
return null;
}
此时还可以查询学生数量:
public Integer getCount(){
String sql="select count(*) from t_student";
return JDBCTemplate.queryAll(sql, new IRowMapper<Integer>() {
@Override
public Integer rowMapper(ResultSet resultSet) throws Exception {
Integer count1 =null;
if(resultSet.next())
count1 =resultSet.getInt("total");
return count1;
}
});
}
此时重构已经完成,好的代码可以让我们以后维护更方便,因此学会代码重构是很重要的。