mysql jdbcUtil

JdbcUtils.java

package com.product.dbutil.jdbc;

import java.lang.reflect.Field;
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.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtils {

	// 定义数据库的用户名
	private final String USERNAME = "root";
	// 定义数据库的密码
	private final String PASSWORD = "";
	// 定义数据库的驱动
	private final String DRIVER = "com.mysql.jdbc.Driver";
	// 定义访问数据库的地址
	private final String URL = "jdbc:mysql://localhost:3306/laoluo_web";
	// 定义数据库链接
	private Connection connection;
	// 定义sql语句的执行对象
	private PreparedStatement pstmt;
	// 定义查询返回的结果
	private ResultSet resultSet;
	// 实现批处理操作的功能
	private Statement stmt;

	public JdbcUtils() {
		try {
			Class.forName(DRIVER);
			System.out.println("注册驱动成功");
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	// 定义获得数据库的链接
	public Connection getConnection() {
		try {
			connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return connection;
	}
	
	public boolean deleteByBatch(String[] sqls) throws SQLException{
		boolean flag = false;
		stmt = connection.createStatement();
		if(sqls != null ){
			for(int i = 0; i < sqls.length; i++){
				stmt.addBatch(sqls[i]);
			}
		}
		int[] count = stmt.executeBatch();
		if (count != null) {
			flag = true;
		}
		return flag;
	}

	/**
	 * 完成对数据库表的添加删除和修改的操作
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public boolean updateByPreparedStatement(String sql, List<Object> params)
			throws SQLException {
		boolean flag = false;
		int result = -1; // 当用户执行添加删除和修改所影响数据库的行数
		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));

			}
		}
		result = pstmt.executeUpdate();
		flag = result > 0 ? true : false;
		return flag;
	}

	/**
	 * 查询返回单条记录
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public Map<String, Object> findSingleResult(String sql, List<Object> params)
			throws SQLException {
		Map<String, Object> map = new HashMap<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()) {
			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 == "") {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}

		}
		return map;
	}

	/**
	 * 返回多条记录
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> findMoreResult(String sql,
			List<Object> 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 == "") {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
			list.add(map);
		}
		return list;
	}

	// jdbc的封装可以用反射机制来封装
	public <T> T findSingleRefResult(String sql, List<Object> params,
			Class<T> cls) throws Exception {
		T resultObject = null;
		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()) {
			// 通过反射机制创建实例
			resultObject = cls.newInstance();
			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 == "") {
					cols_value = "";
				}
				Field field = cls.getDeclaredField(cols_name);
				field.setAccessible(true); // 打开javabean的访问private权限
				field.set(resultObject, cols_value);
			}
		}
		return resultObject;
	}

	public <T> List<T> findMoreRefResult(String sql, List<Object> params,
			Class<T> cls) throws Exception {
		List<T> list = new ArrayList<T>();
		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()) {
			// 通过反射机制创建实例
			T resultObject = cls.newInstance();
			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 == "") {
					cols_value = "";
				}
				Field field = cls.getDeclaredField(cols_name);
				field.setAccessible(true); // 打开javabean的访问private权限
				field.set(resultObject, cols_value);
			}
			list.add(resultObject);
		}
		return list;
	}

	/**
	 * 关闭链接
	 */
	public void releaseConn() {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	/**
	 * 
	 * @param args
	 */
	// public static void main(String[] args) {
	// JdbcUtils jdbcUtils = new JdbcUtils();
	// jdbcUtils.getConnection();
	// 测试增加数据
	// String sql = "insert into userinfo(username,pswd) values(?,?)";
	// List<Object> params = new ArrayList<Object>();
	// params.add("shuting");
	// params.add("520");
	// try {
	// boolean flag = jdbcUtils.updateByPreparedStatement(sql, params);
	// System.out.println("插入一条数据结果：" + flag);
	// } catch (SQLException e) {
	// // TODO Auto-generated catch block
	// e.printStackTrace();
	// }finally{
	// jdbcUtils.releaseConn();
	// }

	// 测试查询单条数据
	// String sql = "select * from userinfo where id = ? ";
	// List<Object> params = new ArrayList<Object>();
	// params.add(1);
	// try {
	// Map<String, Object> map = jdbcUtils.findSingleResult(sql, params);
	// System.out.println("测试查询单条数据");
	// System.out.println(map);
	// } catch (Exception e) {
	// // TODO: handle exception
	// }finally{
	// jdbcUtils.releaseConn();
	// }

	// 测试查询多条数据
	// String sql = "select * from userinfo";
	// try {
	// List<Map<String, Object>> list = jdbcUtils
	// .findMoreResult(sql, null);
	// System.out.println("测试查询多条数据");
	// System.out.println(list);
	// } catch (Exception e) {
	// // TODO: handle exception
	// }finally{
	// jdbcUtils.releaseConn();
	// }

	// 测试反射方式查询单条数据
	// String sql = "select * from userinfo where id = ? ";
	// List<Object> params = new ArrayList<Object>();
	// params.add(1);
	// try {
	// UserInfo userInfo = jdbcUtils.findSingleRefResult(sql, params,
	// UserInfo.class);
	// System.out.println("测试反射方式查询单条数据");
	// System.out.println(userInfo);
	// } catch (Exception e) {
	// // TODO: handle exception
	// } finally {
	// jdbcUtils.releaseConn();
	// }

	// 测试反射方式查询多条数据
	// String sql = "select * from userinfo";
	// try {
	// List<UserInfo> list = jdbcUtils.findMoreRefResult(sql, null,
	// UserInfo.class);
	// System.out.println("测试反射方式查询多条数据");
	// System.out.println(list);
	// } catch (Exception e) {
	// // TODO: handle exception
	// } finally {
	// jdbcUtils.releaseConn();
	// }
	// }
}

userInfo是javabean 两个属性