Mysql工具类

package actions.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.apache.log4j.Logger;

public class DBMysqlUtil {
	private Connection conn = null;
    private PreparedStatement ps = null;
	private ResultSet rs = null;
	private String dbDriver = null;
	private String dbConnectionURL = null;
	private String dbUsername = null;
	private String dbPassword = null;
	private PropUtil PropUtil=null;
	private Logger logger = Logger.getLogger(DBMysqlUtil.class);
	
	public DBMysqlUtil(){
		PropUtil = new PropUtil("config/db.properties");
		dbDriver = PropUtil.get("Driver");
		dbConnectionURL = PropUtil.get("ConnectionURL");
		dbUsername = PropUtil.get("Username");
		dbPassword = PropUtil.get("Password");	
	}
	
	public DBMysqlUtil(String dbDriver, String dbConnectionURL, String dbUsername,String dbPassword){
		this.dbDriver = dbDriver;
		this.dbConnectionURL = dbConnectionURL;
		this.dbUsername = dbUsername;
		this.dbPassword = dbPassword;
	}
	/**
	 * 功能：获取数据库连接
	 */
	private Connection getConnection() {
		System.out.println("连接地址："+dbConnectionURL);
		System.out.println("用户名："+dbUsername);
		System.out.println("密码："+dbPassword);
		try {
			Class.forName(dbDriver);
			conn = DriverManager.getConnection(dbConnectionURL, dbUsername,
					dbPassword);
			logger.info("数据库连接成功");
		} catch (Exception e) {
			logger.error("Error: DbUtil.getConnection() 获得数据库链接失败.\r\n链接类型:"
					+ dbDriver + "\r\n链接URL:" + dbConnectionURL + "\r\n链接用户:"
					+ dbUsername + "\r\n链接密码:" + dbPassword, e);
		}
		return conn;
	}
	
	/**
	 * 功能：执行查询语句
	 */
    public ResultSet select(String sql) {  
    	logger.info("Exec select sql:" + sql);
            try {
            	conn = getConnection();
				ps = conn.prepareStatement(sql);				
				rs = ps.executeQuery(sql);
			} catch (SQLException e) {				
				logger.error("查询数据异常:"+ e.getMessage());
			}
            return rs;
        
    }
    
    /**
	 * 功能：执行查询语句，获取记录数
	 */
	public int getRecordCount(String sql) {
		logger.info("Exec getRecordCount sql:" + sql);
		int counter = 0;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);				
			rs = ps.executeQuery(sql);
			while (rs.next()) {										
				counter++;
			}
		} catch (SQLException e) {
			logger.error("执行DbUtil.getRecordCount()方法发生异常，异常信息：", e);
		}finally {
     	   close();
        }
		  System.out.println("counter总数："+counter);
		return counter;
	}
    
    /**
   	 * 功能:针对单条记录执行更新操作(新增、修改、删除)
   	 */
   public int executeupdate(String sql) throws Exception {
	   logger.info("Exec update sql:" + sql);
           int num = 0;
           try {
               conn = getConnection();
               ps = conn.prepareStatement(sql);
               num = ps.executeUpdate();
           } catch (SQLException sqle) {
        	   logger.error("insert/update/delete  data Exception: " +
                   sqle.getMessage());
           } finally {
        	   close();
           }
           System.out.println("影响条数："+num);
           return num;
       }
    
       /**
   	 * 
   	 * 功能:批量执行SQL(update或delete)
   	 * 
   	 * @param sqlList
   	 *            sql语句集合
   	 */
   	public int executeBatch(List<String> sqlList) {
   		int result = 0;
   		for (String sql : sqlList) {
   			try {
				result += executeupdate(sql);
			} catch (Exception e) {
				  System.out.println("查询异常："+e.getMessage());
			}
   		}
   		System.out.println("executeBatch Result:"+result);
   		return result;
   	}  
    
    /**
	 * 功能:关闭数据库的连接
	 */
	public void close() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
			logger.info("关闭数据库连接成功");
		} catch (Exception e) {
			logger.error("执行DbUtil.close()方法发生异常，异常信息：", e);
		}
	}
}

测试工具类：

package actions.common;

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

import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

public class DBMysqlUtilTest {
	DBMysqlUtil DBUtil =null;
    private   String DBDRIVER = "com.mysql.jdbc.Driver";
    private   String DBURL = "jdbc:mysql://192.168.10.56:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull"; //??URL
    private   String DBUSER = "pluto";
    private   String DBPASSWORD = "pluto123";
	
	@BeforeTest
	public void init(){
		 DBUtil=new DBMysqlUtil(DBDRIVER,DBURL,DBUSER,DBPASSWORD);
//		 DBUtil=new DBMysqlUtil();
	}

	@Test(description="查询")
	public void testquery() {
		String sql="select * from users where name='lisi'";
		ResultSet rs=DBUtil.select(sql);		
		try {
			while(rs.next()){
				String NAME=rs.getString("NAME");
				System.out.println("NAME:"+NAME);
				String AGE=rs.getString("age");
				System.out.println("AGE:"+AGE);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.close();
	}

	@Test(description="查询总数")
	public void testGetCount(){
		String sql="select * from users";
		DBUtil.getRecordCount(sql);		
	}
	
	@Test(description="批量执行SQL")
	public void testBatch(){
		String sql1="insert into users(name,age) values('zhaoliu',30);";
		String sql2="delete from  users where name ='lisi';";
		
		List<String>  sqlList=new ArrayList<String>();
		sqlList.add(sql1);
		sqlList.add(sql2);
		
		DBUtil.executeBatch(sqlList);
		
		
	}
	
	@Test(description="插入")
	public void testinsert() throws Exception{
		String sql="insert into users(name,age) values('lisi',22);";
		System.out.println("执行SQL："+sql);
		
		
		DBUtil.executeupdate(sql);
		
	}
	
	@Test(description="删除")
	public void testdelete() throws Exception{
		String sql="delete from  users where name ='lisi';";
		System.out.println("执行SQL："+sql);
		
		
		DBUtil.executeupdate(sql);
		
	}
	
	@Test(description="修改")
	public void testupdate() throws Exception{
		String sql="update users set  age=50 where name ='lisi';";
		System.out.println("执行SQL："+sql);
				
		DBUtil.executeupdate(sql);
		
	}
}

执行结果：

2017-05-09 16:32:03,823 INFO [actions.common.DBMysqlUtil]select(line:60)Exec select sql:select * from users where name='lisi'
 连接地址：jdbc:mysql://192.168.10.56:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
用户名：pluto
密码：pluto123
2017-05-09 16:32:03,977 INFO [actions.common.DBMysqlUtil]getConnection(line:47)数据库连接成功
 NAME:lisi
AGE:30
NAME:lisi
AGE:25
2017-05-09 16:32:03,993 INFO [actions.common.DBMysqlUtil]close(line:148)关闭数据库连接成功
 PASSED: testquery