JavaWeb JDBC封装类

前言

在学习JavaWeb时,会写许多多数据库的操作,将这些代码进行封装,能够有效的减少代码的工作量。不说了,上代码。

代码

封装前需要一个配置文件——db.properties,这个文件应放在src目录下,最好不要放在其他包下,代码如下:

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/数据库名
username = 登录名
password = 登录密码

JDBC封装类

public class DBUtil {

	private DBUtil() {
		throw new RuntimeException("这是一个工具类");
	}

	static ResourceBundle bundle = ResourceBundle.getBundle("db");
	private static String driver = bundle.getString("driver");
	private static String url = bundle.getString("url");
	private static String username = bundle.getString("username");
	private static String password = bundle.getString("password");

	static {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取数据库连接
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		try {
			return DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	/**
	 * 数据更新
	 * 
	 * @param sql
	 *            带占位符的sql语句
	 * @param params
	 *            占位符的值
	 * @return 受影响的行数
	 */
	public static int update(String sql, Object... params) {
		Connection connection = getConnection();
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					preparedStatement.setObject(i + 1, params[i]);
				}
			}

			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(preparedStatement, connection);
		}

		return 0;
	}

	public static int update(String sql, List<?> params) {
		return update(sql, params.toArray());
	}

	public static void updates(Map<String, List<Object>> map) {
		if (map != null) {
			Connection connection = getConnection();
			try {
				connection.setAutoCommit(false);
				for (String sql : map.keySet()) {
					PreparedStatement prepareStatement = connection.prepareStatement(sql);
					List<Object> params = map.get(sql);
					if (params != null) {
						for (int i = 0; i < params.size(); i++) {
							prepareStatement.setObject(i + 1, params.get(i));
						}
					}

					prepareStatement.executeUpdate();
					prepareStatement.close();
				}

				connection.commit();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 查询数据
	 * 
	 * @param sql
	 *            带占位符的sql语句
	 * @param params
	 *            占位符的值
	 * @return 查询的结果集:Map<String, Object>中放的是查询数据的一行数据
	 */
	public static List<Map<String, Object>> retrieve(String sql, Object... params) {
		List<Map<String, Object>> list = new ArrayList<>();
		Connection connection = getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					preparedStatement.setObject(i + 1, params[i]);
				}
			}
			resultSet = preparedStatement.executeQuery();
			ResultSetMetaData metaData = resultSet.getMetaData();
			int columnCount = metaData.getColumnCount();
			while (resultSet.next()) {
				Map<String, Object> map = new HashMap<>();
				for (int i = 0; i < columnCount; i++) {
					String columnName = metaData.getColumnName(i + 1);
					Object value = resultSet.getObject(columnName);
					if (value == null) {
						value = "";
					}

					map.put(columnName, value);
				}

				list.add(map);
			}

			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(resultSet, preparedStatement, connection);
		}

		return null;
	}

	public static void close(ResultSet resultSet) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void close(PreparedStatement preparedStatement) {
		if (preparedStatement != null) {
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void close(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void close(PreparedStatement preparedStatement, Connection connection) {
		close(preparedStatement);
		close(connection);
	}

	public static void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
		close(resultSet);
		close(preparedStatement);
		close(connection);
	}
}

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页