人力资源管理系统之四-数据操作基础类

用Jsp+bean+servlet实现该系统确实有点麻烦，用框架要简单很多。

数据库操作类

在scr目录下建立database.properties文件，代码如下

driver=com.mysql.jdbc.Driver
user=root
password=123456
url=jdbc:mysql://localhost:3306/hr?useUnicode=true&autoReconnect=true&characterEncoding=UTF-8

数据库操作类

package com.gdpi.utils;

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

public class DBUtil {
	String user = null;
	String password = null;
	String url = null;
	String driver = null;

	private Connection conn = null;
	private PreparedStatement pstm = null;
	private ResultSet rs = null;

	// 获得连接

	public Connection getConn() {
		try {
			// 通过反射获得输入流
			InputStream in = DBUtil.class.getClassLoader().getResourceAsStream("database.properties");
			// 获得属性
			Properties props = new Properties();
			props.load(in);
			// 获得属性值
			driver = props.getProperty("driver");
			url = props.getProperty("url");
			user = props.getProperty("user");
			password = props.getProperty("password");
			// System.out.println(driver+","+url+","+","+user+","+password);
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 执行查询
	 * 
	 * @return返回记录
	 */
	public int getRecord(String sql) {
		int totalRecord = 0;
		try {
			conn = this.getConn();
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				totalRecord = rs.getInt(1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return totalRecord;
	}

	/*
	 * 执行查询
	 * 
	 * @sql
	 * 
	 * @return 返回rs
	 */

	public ResultSet excuteQuery(String sql) {
		try {
			conn = this.getConn();
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/*
	 * 执行带参数的查询
	 * 
	 * @sql
	 * 
	 * @Obj
	 * 
	 * @return 返回rs
	 */
	public ResultSet excuteQuery(String sql, Object[] obj) {
		try {

			conn = this.getConn();
			pstm = conn.prepareStatement(sql);
			// 装载参数
			prepareStateSql(obj, obj.length);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			System.out.println("查询操作失败");
		}
		return rs;
	}

	/**
	 * 执行插入、更新、删除操作
	 * 
	 * @param sql
	 * @param obj
	 * @return 受影响的行数
	 */
	public int excuteUpdate(String sql, Object[] obj) {
		int count = 0;
		try {
			conn = this.getConn();
			pstm = conn.prepareStatement(sql);
			// 装载参数
			prepareStateSql(obj, obj.length);
			count = pstm.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("插入、更新、删除操作失败");
		}
		return count;
	}

	/**
	 * 实现装载prepareStatement
	 * 
	 * @param obj
	 * @param length
	 */
	public void prepareStateSql(Object[] obj, int length) throws SQLException {
		for (int i = 0; i < length; i++) {
			if (obj[i] == null) {
				obj[i] = false;
			}
			pstm.setObject(i + 1, obj[i]);
		}
	}

	// 关闭连接
	public void release() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (pstm != null) {
				pstm.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (Exception e) {
		}
	}
}

测试类

建立数据库hr，在hr建如下结构的dept表

package com.gdpi.test;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.junit.Before;
import org.junit.Test;

import com.gdpi.bean.Dept;
import com.gdpi.utils.DBUtil;

public class DBUtilTest {
	private DBUtil dBUtil = null;

	@Before
	public void before() {
		dBUtil = new DBUtil();
	}

	@Test
	public void testGetConn() {
		dBUtil.getConn();
	}

	@Test
	public void testGetRecord() {
		String sql = "select count(*) from dept";
		int count = dBUtil.getRecord(sql);
		System.out.println("*****" + count);

	}

	// 测试查询无参
	@Test
	public void testQuery() {
		ResultSet rs = null;
		String sql = "select * from dept";
		rs = dBUtil.excuteQuery(sql);
		List<Dept> depts = new ArrayList<Dept>();
		try {
			while (rs.next()) {
				int deptId = rs.getInt("deptId");
				String deptNo = rs.getString("deptNo");
				String deptName = rs.getString("deptName");
				int enabled = rs.getInt("enabled");
				Dept dept = new Dept();
				dept.setDeptId(deptId);
				dept.setDeptNo(deptNo);
				dept.setDeptName(deptName);
				dept.setEnabled(enabled);
				depts.add(dept);
			}
			for (Dept dept : depts) {
				System.out.println(dept);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dBUtil.release();
		}

	}

	// 测试查询有参
	@Test
	public void testQueryArgs() {
		ResultSet rs = null;
		Object[] obj = new Object[] { 22 };
		String sql = "select * from dept where deptId=?";
		rs = dBUtil.excuteQuery(sql, obj);
		List<Dept> depts = new ArrayList<Dept>();
		try {
			while (rs.next()) {
				int deptId = rs.getInt("deptId");
				String deptNo = rs.getString("deptNo");
				String deptName = rs.getString("deptName");
				int enabled = rs.getInt("enabled");
				Dept dept = new Dept();
				dept.setDeptId(deptId);
				dept.setDeptNo(deptNo);
				dept.setDeptName(deptName);
				dept.setEnabled(enabled);
				depts.add(dept);
			}
			for (Dept dept : depts) {
				System.out.println(dept);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dBUtil.release();
		}
	}
	// 测试增、改、删除
		@Test
		public void testUpdate() {
			int count = 0;
			String sql = "delete from dept where deptId=?";
			Object[] obj = new Object[] {22};
			count = dBUtil.excuteUpdate(sql, obj);
			System.out.println("****"+count);
			dBUtil.release();
		}
}

到此为止完成数据访问工具的封装。