4.JDBC与代码重构

对于之前的处理数据库的方法有大量代码是相同的，如注册驱动，建立连接，关闭连接
因此可以将相同代码专门封装在JDBCUtil类中
之后建立连接可以调用JDBCUtil.getConn()方法
关闭文件可以调用JDBCUtil.closeConn()方法

package cn.edu.hbue.wmp;

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

public class JDBCUtil {
	static{
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("JDBC driver error");
			e.printStackTrace();
		}
	}
	
	public static Connection getConn() {
		Connection connection = null;
		try {
			
			String url = "jdbc:mysql://localhost:3306/grade"
					+ "?user=root&password=18170021&serverTimezone=UTC";
			connection = DriverManager.getConnection(url);
		}catch (SQLException e) {
			System.out.println("JDBC connection error");
			e.printStackTrace();
		}
		return connection;
	}

	public static void closeConn(Statement stmt,Connection conn) {
		try{
			if(stmt!= null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}catch(SQLException e){
			System.out.println("close error");
			e.printStackTrace();
		}
	}


	  //之后查询要用到ResultSet
	public static void closeConn(ResultSet rs, Statement stmt, Connection conn){ 
		try{
			if(rs != null){
				rs.close();
			}
			if(stmt!= null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}catch(SQLException e){
			System.out.println("close error");
			e.printStackTrace();
		}
	}
}

例：通用删除

package cn.edu.hbue.wmp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class GeneralJDBCUpdate {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		String sql = "delete from student where id = ?";  //有个id占位符，之后要填充，也就是通过id确定删除
		update(sql,11);      

	}
	
	//要传入sql语句和不确定的占位符
	public static void update(String sql,Object ... args){
		
		Connection connection = null;
		PreparedStatement ps = null;
		//1.获取连接
		connection = JDBCUtil.getConn();
		
		try {
			//2.预编译sql语句
			ps = connection.prepareStatement(sql);
			
			//3.填充占位符
			for(int i = 0;i < args.length;i++){
				ps.setObject(i + 1, args[i]);
			}
			
			//4.执行sql语句
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			//5.关闭资源
			JDBCUtil.closeConn(ps,connection);
			
		}
	
	}

}