4.釋放資源

在我們編程時如果需要操作數(shù)據(jù)庫只需要按照這四個步驟就可以了。

我事先在Navicat for MySQL中建了一個store數(shù)據(jù)庫，并且創(chuàng)建了一個有四個字段的學生表，如下圖：

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.注冊驅動，只需要注冊一次即可（在靜態(tài)代碼中執(zhí)行一次即可）	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對象，需要反復執(zhí)行，不需要定義這個方法了	// 4.釋放資源，需要反復執(zhí)行的(使用方法的重載來定義兩個不同參數(shù)列表的釋放方法)	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 {	/**	 * 添加數(shù)據(jù)到MySQL中	 * 	 * @param student	 */	public void save(Student student) {		// 獲得connection		Connection connection = JDBCUtils.getConnection();		PreparedStatement prepareStatement = null;		// 創(chuàng)建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());			// 執(zhí)行sql語句			prepareStatement.executeUpdate();		} catch (SQLException e) {			e.printStackTrace();		}		// 釋放資源		JDBCUtils.release(prepareStatement, connection);	}	/**	 * 根據(jù)ID號刪除數(shù)據(jù)	 * 	 * @param id	 */	public void deleteById(Integer id) {		// 獲得connection		Connection connection = JDBCUtils.getConnection();		PreparedStatement prepareStatement = null;		// 創(chuàng)建Statement對象		// 定義一個sql		String sql = "DELETE FROM student WHERE id = ?";		try {			prepareStatement = connection.prepareStatement(sql);			prepareStatement.setInt(1, id);			// 執(zhí)行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;		// 創(chuàng)建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());			// 執(zhí)行sql語句			prepareStatement.executeUpdate();		} catch (SQLException e) {			e.printStackTrace();		}		// 釋放資源		JDBCUtils.release(prepareStatement, connection);	}	/**	 * 根據(jù)ID號查找學生信息	 * 	 * @param id	 * @return	 */	public Student getById(Integer id) {		Student s = null;		// 獲得connection		Connection connection = JDBCUtils.getConnection();		PreparedStatement prepareStatement = null;		ResultSet rs =null;		// 創(chuàng)建Statement對象		// 定義一個sql		String sql = "SELECT * FROM student WHERE id = ?";		try {			prepareStatement = connection.prepareStatement(sql);			prepareStatement.setInt(1,id);			// 執(zhí)行sql語句			rs = prepareStatement.executeQuery();			// 從rs中得到數(shù)據(jù)			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;		// 創(chuàng)建Statement對象		// 定義一個sql		String sql = "SELECT * FROM student";		try {			prepareStatement = connection.prepareStatement(sql);			// 執(zhí)行sql語句			rs = prepareStatement.executeQuery();			while (rs.next()) {				// 從rs中得到數(shù)據(jù)				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);		}	}}