[学习笔记] JDBC的简单使用_02

抽取util工具类

观察之前代码, 发现不同操作之间只有第4步(执行SQL)不同, 而其他代码(加载驱动, 获取连接, 创建语句)都完全一样, 一样的代码就应该抽取出来
抽取JdbcUtil类:

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtil {
	//注册驱动和连接三要素
	private static String driverClassName = "com.mysql.jdbc.Driver";
	private static String url = "jdbc:mysql://localhost:3306:jdbcdemo";
	private static String username = "root";
	private static String password = "1092568516";
	
	static{		
		try{
        	//加载注册驱动
			Class.forName(driverClassName);
		}catch(Exception e){
		}
	}
	
    //获取连接
	public static Connection getConnection(){
		try{			
			return DriverManager.getConnection(url, username, password);
		}catch(Exception e){
		}
		return null;
	}
	
    //释放资源
	public static void close(Connection conn, Statement st, ResultSet rs){
		try{
			if(rs != null){
				rs.close();
			}
		}catch(Exception e){
		}finally{
			try{
				if(st != null){
					st.close();
				}
			}catch(Exception e){
			}finally{
				try{
					if(conn != null){
						conn.close();
					}
				}catch(Exception e){
				}
			}
		}
	}
    
    //增删改操作, 没有返回的结果集, 所以ResultSet为空
    public static void close(Connection conn, Statement st){
    	close(conn, st, null);
    }
}

以上代码依然存在问题: 耦合度太高

为降低耦合度, 将数据库连接信息分离出来, 维护时更加方便(如: 公司不用MySql而决定用Oracle, 此时需要修改注册驱动, 但不用修改源代码, 修改分离出来的配置文件即可)
新建resources文件夹, 存放分离出来的db.properties文件:

#properties文件形式是key=value, 键和值都是字符串, 不常用加引号
#连接数据库
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcdemo
username=root
password=1092568516

重构后的JdbcUtil代码

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtil {
//	private static String driverClassName = "com.mysql.jdbc.Driver";
//	private static String url = "jdbc:mysql://localhost:3306:jdbcdemo";
//	private static String username = "root";
//	private static String password = "1092568516";
	private static Properties p = new Properties();
	
	static{		
		try{
			//加载和读取dp.properties文件
			InputStream inStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
			p.load(inStream);
//			Class.forName(driverClassName);
			Class.forName(p.getProperty("driverClassName"));
		}catch(Exception e){	
		}
	}
	
	public static Connection getConnection(){
		try{			
//			return DriverManager.getConnection(url, username, password);
			return DriverManager.getConnection(p.getProperty("url"), p.getProperty("username"), p.getProperty("password"));
		}catch(Exception e){
		}
		return null;
	}
	
    //释放资源
	public static void close(Connection conn, Statement st, ResultSet rs){
		try{
			if(rs != null){
				rs.close();
			}
		}catch(Exception e){
		}finally{
			try{
				if(st != null){
					st.close();
				}
			}catch(Exception e){
			}finally{
				try{
					if(conn != null){
						conn.close();
					}
				}catch(Exception e){
				}
			}
		}
	}
    
    public static void close(Connection conn, Statement st){
    	close(conn, st, null);
    }
}

创建学生对象信息(与数据库表列名对应):

//学生信息对象
public class Student {
	private Long id;
	private String sname;
	private Integer age;
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", sname=" + sname + ", age=" + age + "]";
	}
	
}

此时的jdbc代码就有点简单了:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import JDBC_Demo.Student;
import JDBC_Demo.util.JdbcUtil;

public class jdbcDemo{

	//新增数据
	@Override
	public void save(Student student) {
		String sql = "INSERT INTO t_student (sname, age)" + "VALUES ('" + student.getSname() + "', " + student.getAge() + ")";
		Connection conn = null;
		Statement st = null;
        
		try{
        	//使用工具类获取连接
			conn= JdbcUtil.getConnection();
			st = conn.createStatement();
			st.executeUpdate(sql);
		}catch(Exception e){
        
		}finally{
         	JdbcUtil.close(conn, statement);
		}
	}
	
    //删除数据
	@Override
	public void delete(Long id) {
		String sql = "DELETE FROM t_student WHERE id=" + id;
		Connection conn = null;
		Statement st = null;
        
		try{
			conn= JdbcUtil.getConnection();
			st = conn.createStatement();
			st.executeUpdate(sql);
		}catch(Exception e){
			
		}finally{
         	JdbcUtil.close(conn, statement);
		}
	}

	//更改数据
	@Override
	public void update(Long id, Student student) {
		String sql = "UPDATE t_student SET sname='" + student.getSname() + "', age=" + student.getAge() + " WHERE id=" + id;
		Connection conn = null;
		Statement st = null;
        
		try{
			conn= JdbcUtil.getConnection();
			st = conn.createStatement();
			st.executeUpdate(sql);
		}catch(Exception e){
			
		}finally{
         	JdbcUtil.close(conn, statement);
		}
	}

	//查询操作
	@Override
	public Student get(Long id) {
		String sql = "SELECT * FROM t_student WHERE id=" + id;
		Connection conn = null;
		Statement statement = null;
		ResultSet rs = null;
		try{
			conn= JdbcUtil.getConnection();
			st = conn.createStatement();
			rs = statement.executeQuery(sql);
            
            //封装结果集对象
			if(rs.next()){
				Student student = new Student();
				student.setId(rs.getLong("id"));
				student.setSname(rs.getString("sname"));
				student.setAge(rs.getInt("age"));
				
				return student;
			}
		}catch(Exception e){
			
		}finally{
            JdbcUtil.close(conn, statement);
		}
		return null;
	}

	@Override
	public List<Student> listAll() {
		List<Student> stus = new ArrayList<>();
		String sql = "SELECT * FROM t_student";
		Connection conn = null;
		Statement statement = null;
		ResultSet rs = null;
		try{
			conn= JdbcUtil.getConnection();
			statement = conn.createStatement();
			rs = statement.executeQuery(sql);
			while(rs.next()){
				Student student = new Student();
				student.setId(rs.getLong("id"));
				student.setSname(rs.getString("sname"));
				student.setAge(rs.getInt("age"));
				
				stus.add(student);
			}
			return stus;
		}catch(Exception e){
			
		}finally{
			JdbcUtil.close(conn, statement, rs);
		}
		return stus;
	}
}

看似很复杂, 其实很多操作都是一样的