将增删查改封装语句写到一个工具类

1，将增删查改语句直接写到一个工具类的代码
工具类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {

//查询方法
	public static void query(String sql,IRowMapper rowMapper) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			rowMapper.rowMapper(resultSet);//多态
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if (resultSet!=null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			try {
				if (statement!=null) {
					statement.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			try {
				if (connection!=null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}


//增删改方法
	public static boolean update(String sql) {
		Connection connection = null;
		Statement statement= null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
			statement= connection.createStatement();
			return statement.executeUpdate(sql)>0;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if (statement!=null) {
					statement.close();
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			try {
				if (connection !=null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}
}

接口类

interface IRowMapper{
	void rowMapper(ResultSet rs);
}

查询类中代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Select {
	public static void main(String[] args) {
		String sql="select * from student";

		class RowMapper implements IRowMapper{
			@Override
			public void rowMapper(ResultSet st) {
				try {
					while(st.next()) {	
						String id=st.getString("id");
						String studentName=st.getString("student_name");
						String address=st.getString("address");
						String mobile=st.getString("mobile");
						System.out.println(id+":"+studentName+":"+address+":"+mobile);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
			}
		}
		
		RowMapper rowMapper=new RowMapper();	//2，实现类对象
		DBUtil.query(sql,rowMapper);
	}
}

Update类中代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Update {
	public static void main(String[] args) {
		String sql="delete from student where student_name='tom'";
		if(DBUtil.update(sql)) {	//update(sql)返回一个boolean类型的值，可用在if条件中。	
			System.out.println("ok");
		}else {
			System.out.println("no");
		}
	}
}

2，将工具类中代码优化

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {
	
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");//1、加载驱动：因为驱动只需要加载一次，所以定义在静态代码块中，只在类加载时加载一次驱动。
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	private static Connection getConnection() {	//2、获取连接：将连接定义为一个方法，实现代码的复用。
		try {
			return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void query(String sql,IRowMapper rowMapper) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();		//调用连接方法
			statement = connection.createStatement();	//3、创建语句
			resultSet = statement.executeQuery(sql);	//4、执行SQL
			rowMapper.rowMapper(resultSet);	//5、处理结果
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(resultSet,statement,connection);	//6、释放资源：将关闭代码定义为一个方法，实现代码的复用。
		}
	}

	public static boolean update(String sql) {
		Connection connection = null;
		Statement statement= null;
		try {
			connection = getConnection();
			statement= connection.createStatement();	//3、创建语句
			return statement.executeUpdate(sql)>0;	//4、执行SQL  和 5、处理结果
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(statement,connection);	//6、释放资源
		}
		return false;
	}
	
	//构造方法的重载
	private static void close(Statement statement,Connection connection) {
		try {
			if (statement!=null) {
				statement.close();
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			if (connection !=null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private static void close(ResultSet resultSet,Statement statement,Connection connection) {
		try {
			if (resultSet!=null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		close(statement,connection);		//调用上一个构造方法，代码优化。
	}
}