JDBC的学习（三）——通用工具类的封装

建立一个JDBC的配置文件，再封装一个工具类，工具类读取配置文件。

首先是配置文件：

jdbc.username=root
jdbc.password=1234
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc\:mysql\://localhost\:3306/moviedb

然后是工具类：

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;


/**
 * 通用的基础的 数据库操作工具类
 * @author littledyf
 *
 */
public class BaseDao {

	// 表示定义数据库的用户名
	private static String USERNAME ;

	// 定义数据库的密码
	private static String PASSWORD;

	// 定义数据库的驱动信息
	private static String DRIVER;

	// 定义访问数据库的地址
	private static String URL;

	// 定义数据库的链接
	private Connection connection;

	// 定义sql语句的执行对象
	private PreparedStatement pstmt;

	// 定义查询返回的结果集合
	private ResultSet resultSet;
	
	static{
		//加载数据库配置信息，并给相关的属性赋值
		loadConfig();
	}

	/**
	 * 加载数据库配置信息，并给相关的属性赋值
	 */
	public static void loadConfig() {
		try {
			InputStream inStream = BaseDao.class
					.getResourceAsStream("/jdbc.properties");
			Properties prop = new Properties();
			prop.load(inStream);
			USERNAME = prop.getProperty("jdbc.username");
			PASSWORD = prop.getProperty("jdbc.password");
			DRIVER= prop.getProperty("jdbc.driver");
			URL = prop.getProperty("jdbc.url");
		} catch (Exception e) {
			throw new RuntimeException("读取数据库配置文件异常！", e);
		}
	}
	//打开数据库连接
	public Connection openConn() {
		try {
			Class.forName(DRIVER); // 注册驱动
			connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); // 获取连接
		} catch (Exception e) {
			throw new RuntimeException("获取连接失败！", e);
		}
		return connection;
	}
	//关闭资源
	public void closeConn() {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	//通用增删改
	public boolean update(String sql, List<?> params)
			throws SQLException {
		boolean flag = false;
		int result = -1;// 表示当用户执行添加删除和修改的时候所影响数据库的行数
		pstmt = connection.prepareStatement(sql);
		int index = 1;
		// 填充sql语句中的占位符
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		result = pstmt.executeUpdate();
		flag = result > 0 ? true : false;
		return flag;
	}
	//通用查询
	public ResultSet query(String sql,List<?> params){
		try {
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			int index = 1;
			pstmt = connection.prepareStatement(sql);
			//匹配占位符
			if (params != null && !params.isEmpty()) {
				for (int i = 0; i < params.size(); i++) {
					pstmt.setObject(index++, params.get(i));
				}
			}
			//执行查询语句
			return pstmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	//通用查询,结果格式化返回,获取到数据库中的列名对应的值
	public List<Map<String, Object>> queryResult(String sql, List<?> params)
			throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		int index = 1;
		pstmt = connection.prepareStatement(sql);
		//匹配占位符
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		//执行查询语句
		resultSet = pstmt.executeQuery();
		//将查询结果整理
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			Map<String, Object> map = new HashMap<String, Object>();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
			list.add(map);
		}
		return list;
	}
}

测试类，这里只做全部查询和根据条件查询测试：

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

import org.junit.Test;

import com.qfedu.DBUtils.BaseDao;


public class TestDBUtils {
	//全部查询测试
	@Test
	public void t1(){
		BaseDao jdbcUtil = new BaseDao();
		jdbcUtil.openConn();
		try {
			ResultSet result = jdbcUtil.query(
					"select * from movie", null);
			while(result.next()){
				System.out.print(result.getInt(1));
				System.out.print(result.getString(2));
				System.out.print(result.getString(3));
				System.out.print(result.getString(4));
				System.out.print(result.getString(5));
				System.out.print(result.getDouble(6));
				System.out.println(result.getString(7));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			jdbcUtil.closeConn();
		}
	}
	//单独查询测试
	@Test
	public void t2(){
		List list = new ArrayList();
		list.add(1);
		BaseDao jdbcUtil = new BaseDao();
		jdbcUtil.openConn();
		try {
			ResultSet result = jdbcUtil.query(
					"select * from movie where id=?", list);
			while(result.next()){
				System.out.print(result.getInt(1));
				System.out.print(result.getString(2));
				System.out.print(result.getString(3));
				System.out.print(result.getString(4));
				System.out.print(result.getString(5));
				System.out.print(result.getDouble(6));
				System.out.println(result.getString(7));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			jdbcUtil.closeConn();
		}
	}
}