四个月的蜕变之JDBC的四个步骤和代码实现

4.释放资源

在我们编程时如果需要操作数据库只需要按照这四个步骤就可以了。

我事先在Navicat for MySQL中建了一个store数据库，并且创建了一个有四个字段的学生表，如下图：

package cn.edu360.entity;public class Student {	PRivate Integer id;	private String name;	private String gender;	private int age;	public Student() {	}	public Student(Integer id, String name, String gender, int age) {		super();		this.id = id;		this.name = name;		this.gender = gender;		this.age = age;	}	@Override	public String toString() {		return "Student [id=" + id + ", name=" + name + ", gender=" + gender + ", age=" + age + "]";	}	public Integer getId() {		return id;	}	public void setId(Integer id) {		this.id = id;	}	public String getName() {		return name;	}	public void setName(String name) {		this.name = name;	}	public String getGender() {		return gender;	}	public void setGender(String gender) {		this.gender = gender;	}	public int getAge() {		return age;	}	public void setAge(int age) {		this.age = age;	}}将四个步骤中的1、2、4封装成cn.edu360.utils如下：
package cn.edu360.utils;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/* * JDBC四个步骤 */public class JDBCUtils {	private static String driverName = "com.mysql.jdbc.Driver";	private static String jdbc_url = "jdbc:mysql://localhost:3306/store";	private static String username = "root";	private static String passWord = "12580";	// 1.注册驱动，只需要注册一次即可（在静态代码中执行一次即可）	static {		try {			Class.forName(driverName);		} catch (ClassNotFoundException e) {			e.printStackTrace();		}	}	// 2.获得连接对象，以后需要多次获得	public static Connection getConnection() {		try {			return DriverManager.getConnection(jdbc_url, username, password);		} catch (SQLException e) {			e.printStackTrace();		}		return null;	}	// 3.获取statement对象，需要反复执行，不需要定义这个方法了	// 4.释放资源，需要反复执行的(使用方法的重载来定义两个不同参数列表的释放方法)	public static void release(Statement stmt, Connection connection) {		if (stmt != null) {			try {				stmt.close();			} catch (SQLException e) {				e.printStackTrace();			}			stmt = null;		}		if (connection != null) {			try {				connection.close();			} catch (SQLException e) {				e.printStackTrace();			}			connection = null;		}	}	public static void release(ResultSet rs, Statement stmt, Connection connection) {		if (rs != null) {			try {				rs.close();			} catch (SQLException e) {				e.printStackTrace();			}			rs = null;		}		if (stmt != null) {			try {				stmt.close();			} catch (SQLException e) {				e.printStackTrace();			}			stmt = null;		}		if (connection != null) {			try {				connection.close();			} catch (SQLException e) {				e.printStackTrace();			}			connection = null;		}	}}增删改查封装成cn.edu360.dao如下：
package cn.edu360.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import cn.edu360.entity.Student;import cn.edu360.utils.JDBCUtils;public class StudentDao {	/**	 * 添加数据到MySQL中	 * 	 * @param student	 */	public void save(Student student) {		// 获得connection		Connection connection = JDBCUtils.getConnection();		PreparedStatement prepareStatement = null;		// 创建Statement对象		// 定义一个sql		String sql = "INSERT INTO student VALUES (?, ?, ?, ?)";		try {			prepareStatement = connection.prepareStatement(sql);						prepareStatement.setInt(1, student.getId());			prepareStatement.setString(2, student.getName());			prepareStatement.setString(3, student.getGender());			prepareStatement.setInt(4, student.getAge());			// 执行sql语句			prepareStatement.executeUpdate();		} catch (SQLException e) {			e.printStackTrace();		}		// 释放资源		JDBCUtils.release(prepareStatement, connection);	}	/**	 * 根据ID号删除数据	 * 	 * @param id	 */	public void deleteById(Integer id) {		// 获得connection		Connection connection = JDBCUtils.getConnection();		PreparedStatement prepareStatement = null;		// 创建Statement对象		// 定义一个sql		String sql = "DELETE FROM student WHERE id = ?";		try {			prepareStatement = connection.prepareStatement(sql);			prepareStatement.setInt(1, id);			// 执行sql语句			prepareStatement.executeUpdate();		} catch (SQLException e) {			e.printStackTrace();		}		// 释放资源		JDBCUtils.release(prepareStatement, connection);	}	/**	 * 更新学生信息	 * 	 * @param student	 */	public void update(Student student) {		// 获得connection		Connection connection = JDBCUtils.getConnection();		PreparedStatement prepareStatement = null;		// 创建Statement对象		// 定义一个sql		String sql = "UPDATE student SET name = ? , gender = ? , age = ? WHERE id = ?";		try {			prepareStatement = connection.prepareStatement(sql);			prepareStatement.setString(1, student.getName());			prepareStatement.setString(2, student.getGender());			prepareStatement.setInt(3, student.getAge());			prepareStatement.setInt(4, student.getId());			// 执行sql语句			prepareStatement.executeUpdate();		} catch (SQLException e) {			e.printStackTrace();		}		// 释放资源		JDBCUtils.release(prepareStatement, connection);	}	/**	 * 根据ID号查找学生信息	 * 	 * @param id	 * @return	 */	public Student getById(Integer id) {		Student s = null;		// 获得connection		Connection connection = JDBCUtils.getConnection();		PreparedStatement prepareStatement = null;		ResultSet rs =null;		// 创建Statement对象		// 定义一个sql		String sql = "SELECT * FROM student WHERE id = ?";		try {			prepareStatement = connection.prepareStatement(sql);			prepareStatement.setInt(1,id);			// 执行sql语句			rs = prepareStatement.executeQuery();			// 从rs中得到数据			rs.next();			Integer sid = rs.getInt(1);			String name = rs.getString(2);			String gender = rs.getString(3);			int age = rs.getInt(4);			s = new Student();			s.setId(sid);			s.setName(name);			s.setGender(gender);			s.setAge(age);		} catch (SQLException e) {			e.printStackTrace();		}		// 释放资源		JDBCUtils.release(rs,prepareStatement, connection);		return s;	}	/**	 * 查找所有学生信息	 * 	 * @return	 */	public List<Student> findAll() {		ArrayList<Student> list = new ArrayList<Student>();		// 获得connection		Connection connection = JDBCUtils.getConnection();		PreparedStatement prepareStatement = null;		ResultSet rs = null;		// 创建Statement对象		// 定义一个sql		String sql = "SELECT * FROM student";		try {			prepareStatement = connection.prepareStatement(sql);			// 执行sql语句			rs = prepareStatement.executeQuery();			while (rs.next()) {				// 从rs中得到数据				Integer sid = rs.getInt(1);				String name = rs.getString(2);				String gender = rs.getString(3);				int age = rs.getInt(4);				Student s = new Student();				s.setId(sid);				s.setName(name);				s.setGender(gender);				s.setAge(age);				list.add(s);			}		} catch (SQLException e) {			e.printStackTrace();		}		// 释放资源		JDBCUtils.release(rs,prepareStatement, connection);		return list;	}}
测试代码如下：
package cn.edu360.test;import java.util.List;import org.junit.Test;import cn.edu360.dao.StudentDao;import cn.edu360.entity.Student;public class TestDao {	@Test	public void testSave() {		StudentDao dao = new StudentDao();		Student s = new Student(11, "tom", "f", 20);		dao.save(s);	}	@Test	public void testDelete() {		StudentDao dao = new StudentDao();		dao.deleteById(11);	}	@Test	public void testUpdate() {		StudentDao dao = new StudentDao();		Student s = new Student(1, "zhaozhoa", "m", 18);		dao.update(s);	}	@Test	public void testGetById() {		StudentDao dao = new StudentDao();		Student s = dao.getById(2);		System.out.println(s);	}	@Test	public void testFindAll() {		StudentDao dao = new StudentDao();		List<Student> list = dao.findAll();		for (Student s : list) {			System.out.println(s);		}	}}