JDBC实现学生信息管理系统——DBUtil工具类

import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
/**
 * 
 * 数据库工具类
 * 
 * @author 刘晓阳
 */
public class DBUtil {
	
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 
	 * 建立连接
	 *
	 * @author 刘晓阳
	 */
	public static Connection getConnection() {
		try {
			String url = PropertiesUtil.getValue("jdbc.url");
			String username = PropertiesUtil.getValue("jdbc.username");
			String password = PropertiesUtil.getValue("jdbc.password");
			return DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 
	 * 判断数据是否存在
	 *
	 * @author 刘晓阳
	 */
	public static int exist(String sql, Object... params) {
		class RowRapper implements IRowMapper {

			int flag;

			@Override
			public void rowMapper(ResultSet resultSet) {
				try {
					if (resultSet.next()) {
						flag = -1;// 学号存在，无法再添加，可以查询修改和删除
					} else {
						flag = 1;// 学号不存在，可以添加，不能查询修改和删除
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

			}
		}
		RowRapper rowRapper = new RowRapper();
		select(sql, rowRapper, params);
		return rowRapper.flag;
	}
	
	/**
	 * 
	 * 修改数据
	 *
	 * @author 刘晓阳
	 */
	public static boolean update(String sql) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection();
			statement = connection.createStatement();
			int result = statement.executeUpdate(sql);
			return result > 0;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(connection, statement);
		}
		return false;
	}
	
	/**
	 * 
	 * 修改数据(防sql注入)
	 *
	 * @author 刘晓阳
	 */
	public static boolean update(String sql, Object... params) {

		PreparedStatement preparedStatement = null;
		try {
			
			Connection connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 1; i <= params.length; i++) {
				preparedStatement.setObject(i, params[i - 1]);
			}
			int result = preparedStatement.executeUpdate();
			if (result > 0) {
				return result > 0;
			} else {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return false;
	}
	
	/**
	 * 
	 * 查询数据
	 *
	 * @author 刘晓阳
	 */
	public static void select(String sql, IRowMapper rowmapper) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultset = null;
		try {
			connection = getConnection();
			statement = connection.createStatement();
			resultset = statement.executeQuery(sql);
			rowmapper.rowMapper(resultset);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(connection, statement, resultset);
		}

	}
	
	/**
	 * 
	 * 查询数据(防sql注入）
	 *
	 * @author 刘晓阳
	 */
	public static void select(String str, IRowMapper rowmapper, Object... params) {

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultset = null;

		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(str);
			for (int i = 1; i <= params.length; i++) {
				preparedStatement.setObject(i, params[i - 1]);
			}
			resultset = preparedStatement.executeQuery();
			rowmapper.rowMapper(resultset);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(connection, preparedStatement, resultset);
		}
	}
	
	/**
	 * 
	 * 批量处理
	 *
	 * @author 刘晓阳
	 */
	public static boolean batch(String... sqls) {
		Connection connection = null;
		Statement statement = null;
		try {
			
			connection = getConnection();
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			for (String sql : sqls) {
				statement.addBatch(sql);
			}
			statement.executeBatch();
			connection.commit();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			if (connection != null) {
				try {
					connection.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		} finally {
			close(connection, statement);
		}
		return false;
	}
	
	/**
	 * 
	 * 批量处理(防sql注入)
	 *
	 * @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) {
			e.printStackTrace();
			if (connection != null) {
				try {
					connection.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		} finally {
			close(connection, preparedstatement);
		}
		return false;
	}

	
	/**
	 *
	 * 关闭接口(释放资源)
	 *
	 * @author 刘晓阳
	 */
	public static void close(Connection connection, Statement statement) {
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 
	 * 关闭接口(释放资源)
	 *
	 * @author 刘晓阳
	 */
	public static void close(Connection connection, Statement statement, ResultSet resultset) {
		if (resultset != null) {
			try {
				resultset.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		close(connection, statement);
	}
}