详述DBUtil工具类

详述DBUtil工具类

一、IRowMapper接口

package util;

import java.sql.ResultSet;

public interface IRowMapper {
	void RowMapper(ResultSet resultSet);
}

二、 DBUtil工具类

package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * 数据库工具类
 *
 * @author 王豪斌
 */
public class DBUtil {
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}//1、加载驱动程序类
	}
	/**
	 * 获取数据库连接
	 *
	 * @author 王豪斌
	 */
	public static Connection getConnection() {
		try {
			String url = PropertiesUtil.getValue("jdbc.url");
			String username = PropertiesUtil.getValue("jdbc.username");
			String password = PropertiesUtil.getValue("jdbc.password");
			Connection connection = DriverManager.getConnection(url, username, password);//2、获取连接
			return connection;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 修改数据
	 *
	 * @author 王豪斌
	 */
	public static boolean update(String sql) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection();
			statement = connection.createStatement();//3、创建语句
			int effectRows = statement.executeUpdate(sql);//4、执行语句
			return effectRows > 0;//5、处理结果
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(statement,connection);
		}
		return false;
	}
	
	/**
	 * 修改数据
	 *
	 * @author 王豪斌
	 */
	public static boolean update(String sql,Object...prams) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection();
			PreparedStatement preparedStatement= connection.prepareStatement(sql);//3、创建语句
			for(int i = 1;i<=prams.length;i++) {
				preparedStatement.setObject(i, prams[i-1]);
			}
			int effectRows = preparedStatement.executeUpdate();
			return effectRows > 0;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(statement,connection);
		}
		return false;
		
	}
	
	/**
	 * 判断数据是否存在
	 *
	 * @author 王豪斌
	 */
	public static boolean exist(String sql) {
		class RowMapper implements IRowMapper{
			boolean state;
			@Override
			public void RowMapper(ResultSet resultSet) {
				try {
					state = resultSet.next();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		RowMapper rowMapper = new RowMapper();
		select(sql, rowMapper);
		return rowMapper.state;
	}
	
	/**
	 * 判断数据是否存在
	 *
	 * @author 王豪斌
	 */
	public static boolean exist(String sql,Object...prams) {
		class RowMapper implements IRowMapper{
			boolean state;
			@Override
			public void RowMapper(ResultSet resultSet) {
				try {
					state = resultSet.next();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		RowMapper rowMapper = new RowMapper();
		select(sql, rowMapper, prams);
		return rowMapper.state;
	}
	
	/**
	 * 查询数据
	 *
	 * @author 王豪斌
	 */
	public static void select(String sql,IRowMapper rowMapper) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			statement = connection.createStatement();//3、创建sql语句
			resultSet = statement.executeQuery(sql);//4、执行语句
			rowMapper.RowMapper(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(resultSet,statement,connection);
		}
	}
	
	/**
	 * 查询数据
	 *
	 * @author 王豪斌
	 */
	public static void select(String sql,IRowMapper rowMApper,Object...prams) {
		Connection connection = null;//2、获得连接
		Statement statement = null;//3、创建语句
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			PreparedStatement preparedStatement= connection.prepareStatement(sql);//3、创建语句
			for(int i = 1;i<=prams.length;i++) {
				preparedStatement.setObject(i, prams[i-1]);
			}
			resultSet = preparedStatement.executeQuery();
			rowMApper.RowMapper(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(resultSet,statement,connection);
		}
	}
	
	/**
	 * 批量处理
	 *
	 * @author 王豪斌
	 */
	public static boolean batch(String...strings) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection();
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			for (String string : strings) {
				statement.addBatch(string);
			}
			statement.executeBatch();
			connection.commit();
			return true;
		} catch (Exception e) {
			if (connection!=null) {
				try {
					connection.rollback();
					return false;
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
			e.printStackTrace();
		}finally {
			close(statement, connection);
		}
		return false;
	}
	
	/**
	 * 批量处理
	 *
	 * @author 王豪斌
	 */
	public static boolean batch(String sql,Object[]...params) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = getConnection();
			connection.setAutoCommit(false);;
			preparedStatement = connection.prepareStatement(sql);
			for(int i = 0;i < params.length;i++) {
				for(int j = 1;j <= params[i].length;j++) {
					preparedStatement.setObject(j, params[i][j-1]);
				}
				preparedStatement.addBatch();
			}
			preparedStatement.executeBatch();
			connection.commit();
			return true;
		} catch (Exception e) {
			if (connection!=null) {
				e.printStackTrace();
				try {
					connection.rollback();
					System.out.println(111);
					return false;
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
		}finally {
			close(preparedStatement, connection);
		}
		return false;
	}
	
	/**
	 * 释放资源
	 *
	 * @author 王豪斌
	 */
	public static void close(Statement statement,Connection connection) {
		if (statement != null) {
			try {
				statement.close();
			} catch (Exception e) {
				e.printStackTrace();
			} 
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
				e.printStackTrace();
			} 
		}
	}
	
	/**
	 * 释放资源
	 *
	 * @author 王豪斌
	 */
	public static void close(ResultSet resultSet,Statement statement,Connection connection) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
	}
}