MySQL连接Java编写通用查询和通用增删改+log4j

数据库连接类DBHelp

package com.bbs.db;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.log4j.Logger;
import org.json.JSONObject;
import org.junit.Test;

import com.bbs.pojo.BBSUser;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;
/**
 * @author yangxiaoqiang
 * */
public class DBHelper {
	private static String CLASSNAME;
	private static String URL;
	private static String USERNAME;
	private static String PASSWORD;
	private  Connection conn = null;
	private  PreparedStatement pstmt = null;
	private  ResultSet rs = null;
	private static Logger log=Logger.getLogger(DBHelper.class);
	static{
		log.info("静态块从配置properties中读取数据-加载数据库连接对象");
		InputStream is=DBHelper.class.getResourceAsStream("/jdbc.properties");
		Properties p=new Properties();
		 try {
			p.load(is);
			CLASSNAME=p.getProperty("CLASSNAME").toString();
			URL=p.getProperty("URL").toString();
			USERNAME=p.getProperty("USERNAME");
			PASSWORD=p.getProperty("PASSWORD");
			Class.forName(CLASSNAME);//加载数据库驱动类
		} catch (IOException e) {
			log.error("properties中读取数据-加载InputStream异常！");
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			log.error("数据库加载驱动类forNmae异常！");
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 获取数据库连接的方法
	 * @return Connectiond对象
	 * 
	 * */
	private static Connection getConnection(){
		Connection conn = null;
		try {
			conn=DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (SQLException e) {
			log.error("connection对象->数据库连接异常！");
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 设置SQL语句中的参数
	 * 
	 * @throws SQLException
	 */
	public void setParameter(Object[] params) throws SQLException {
		if (null != params) {
			for (int i = 0; i < params.length; i++) {
				pstmt.setObject((i + 1), params[i]);
			}
		}
	}
	
	/**
	 * 通用的INSERT，DELETE，UPDATE方法
	 * 
	 * @param sql语句
	 * @param paramsSQL语句中的参数
	 * @return 返回受影响的行数
	 */
	public int executeUpdate(String sql, Object[] params) {
		conn = getConnection();
		int count = 0; // 受影响的行数
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			// 设置参数
			setParameter(params);
			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(conn, pstmt, null);
		}
		return count;
	}
	
/**
 * 执行查询语句的方法
 * @return List集合
 * 
 * */
	public List executeQuery(String sql, Object[] params, Class clz) {
		List list = new ArrayList();//保存所有用户对象
		conn = getConnection();//获取connectoin对象
		Object obj = null;
		try {
			pstmt = (PreparedStatement)conn.prepareStatement(sql);
			setParameter(params); // 设置参数
			rs = (ResultSet) pstmt.executeQuery();
			// 返回结果集的列的信息
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount(); // 列的数量
			while (rs.next()) {
				obj = clz.newInstance(); // 实例化一个对象
				for (int i = 1; i <= columnCount; i++) {
					String columnName = rsmd.getColumnName(i); // 获取查询语句中的列名
					Object value = rs.getObject(columnName); // 根据列名获取值
					获得clz类中所有定义的属性
					Field field = clz.getDeclaredField(columnName);//获取对应的属性，注意名称要与该类的属性名称相同否则出错：
					field.setAccessible(true);//对所有属性设置访问权限  当类中的成员变量为private时 必须设置此项true
					field.set(obj, value);//给属性赋值
				}
				list.add(obj);//然后放入集合中
			}
		} catch (SQLException e) {
			log.error("PreparedStatement预执行sql语句异常！");
			e.printStackTrace();
		} catch (InstantiationException e) {
			log.error("newInstance实例化字节对象异常！");
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			log.error("field.set设置字节对象成员异常！赋值的数据类型不对！！！");
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			log.error("getDeclaredField获取字节对象成员异常，获取的名称与该类的属性名称不符合！！！");
			e.printStackTrace();
		} catch (SecurityException e) {
			log.error("setAccessible设置修饰成员异常，成员属性有private修饰，你没有给他setAccessible(true)访问权限");
			e.printStackTrace();
		} finally {
			closeAll(conn, pstmt, rs);//关闭数据库
		}
		return list;
	}
	
	
	/**
	 * 关闭数据库连接
	 * */
	private static void closeAll(Connection conn, Statement stmt, ResultSet rs) {
		try {
			if (null != rs) {
				rs.close();
				System.out.println("ResultSet-关闭成功！");
			}else System.out.println("ResultSet未开，不需要关闭");
			if (null != stmt) {
				stmt.close();
				System.out.println("PreparedStatement-关闭成功！");
			}else System.out.println("PreparedStatement未开，不需要关闭");
			if (null != conn) {
				conn.close();
				System.out.println("Connection-关闭成功！");
			}else System.out.println("Connection未开，不需要关闭");
		} catch (SQLException e) {
			log.error("数据库关闭异常");
			e.printStackTrace();
		}
	}
	/**
	 * 测试我的数据读取配置文件properties
	 * */
	/*@Test
	public void Testproperties(){
		DBHelper ss=new DBHelper();
		if(ss.CLASSNAME == null){
			log.warn("静态块properties映射数据失败-数据库加载驱动类");
			if(ss.USERNAME == null){
				log.warn("静态块properties映射数据失败-用户名");
				if(ss.URL == null){
					log.warn("静态块properties映射数据失败-地址");
					if(ss.PASSWORD == null){
						log.warn("静态块properties映射数据失败-密码");
					}
				}
			}
		}else
			log.info("静态块读取properties文件数据成功！！");
		if(getConnection() != null){
		System.out.println("数据库连接成功！");
		}
		closeAll(getConnection(),null,null);
	}
	
	
	//获取数据到JSONObject
		DBHelper db = new DBHelper();
		String sqlall = "select * from user";
		JSONObject obj = new JSONObject();//
		
		List list = db.executeQuery(sqlall, null, BBSUser.class);
		obj.put("aaa", list);
		System.out.println(obj.toString());
	for (int i = 0; i < list.size(); i++) {
			BBSUser user = (BBSUser)list.get(i);
			System.out.println(user.toString());
		}
	*/
	
	}

jdbc.properties配置文件

CLASSNAME=com.mysql.jdbc.Driver
URL=jdbc:mysql://localhost:3306/bbs?characterEncoding=utf-8&serverTimezone=GMT
USERNAME=root
PASSWORD=密码

log4j.properties日志文件配置

# Root logger option
log4j.rootLogger=INFO, stdout, file

# Redirect log messages to console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n

# Redirect log messages to a log file
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=/first_bbs-api.log
log4j.appender.file.MaxFileSize=5MB
log4j.appender.file.MaxBackupIndex=10
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n

学习愉快！