对jdbc操作工具类JdbcUtil的简单封装与应用

一、jdbcUtil类

package com.test.util;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Properties;public class JdbcUtil {// 表示定义数据库的用户名private static String USERNAME ;// 定义数据库的密码private static String PASSWORD;// 定义数据库的驱动信息private static String DRIVER;// 定义访问数据库的地址private static String URL;// 定义数据库的链接private Connection connection;// 定义sql语句的执行对象private PreparedStatement pstmt;// 定义查询返回的结果集合private ResultSet resultSet;static{//加载数据库配置信息，并给相关的属性赋值loadConfig();}/** * 加载数据库配置信息，并给相关的属性赋值 */public static void loadConfig() {try {InputStream inStream = JdbcUtil.class.getResourceAsStream("/jdbc.properties");Properties prop = new Properties();prop.load(inStream);USERNAME = prop.getProperty("jdbc.username");PASSWORD = prop.getProperty("jdbc.password");DRIVER= prop.getProperty("jdbc.driver");URL = prop.getProperty("jdbc.url");} catch (Exception e) {throw new RuntimeException("读取数据库配置文件异常！", e);}}public JdbcUtil() {}/** * 获取数据库连接 *  * @return 数据库连接 */public Connection getConnection() {try {Class.forName(DRIVER); // 注册驱动connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); // 获取连接} catch (Exception e) {throw new RuntimeException("get connection error!", e);}return connection;}/** * 执行更新操作 *  * @param sql *            sql语句 * @param params *            执行参数 * @return 执行结果 * @throws SQLException */public boolean updateByPreparedStatement(String sql, List<?> params)throws SQLException {boolean flag = false;int result = -1;// 表示当用户执行添加删除和修改的时候所影响数据库的行数pstmt = connection.prepareStatement(sql);int index = 1;// 填充sql语句中的占位符if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {pstmt.setObject(index++, params.get(i));}}result = pstmt.executeUpdate();flag = result > 0 ? true : false;return flag;}/** * 执行查询操作 *  * @param sql *            sql语句 * @param params *            执行参数 * @return * @throws SQLException */public List<Map<String, Object>> findResult(String sql, List<?> params)throws SQLException {List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();int index = 1;pstmt = connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {pstmt.setObject(index++, params.get(i));}}resultSet = pstmt.executeQuery();ResultSetMetaData metaData = resultSet.getMetaData();int cols_len = metaData.getColumnCount();while (resultSet.next()) {Map<String, Object> map = new HashMap<String, Object>();for (int i = 0; i < cols_len; i++) {String cols_name = metaData.getColumnName(i + 1);Object cols_value = resultSet.getObject(cols_name);if (cols_value == null) {cols_value = "";}map.put(cols_name, cols_value);}list.add(map);}return list;}/** * 释放资源 */public void releaseConn() {if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}if (pstmt != null) {try {pstmt.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}public static void main(String[] args) {JdbcUtil jdbcUtil = new JdbcUtil();jdbcUtil.getConnection();try {List<Map<String, Object>> result = jdbcUtil.findResult("select * from t_student", null);for (Map<String, Object> m : result) {System.out.println(m);}} catch (SQLException e) {e.printStackTrace();} finally {jdbcUtil.releaseConn();}}}

二、配置数据库资源

在根目录下新建jdbc.properties文件，用于管理数据库地址和用户名密码。内容如下：

jdbc.username=rootjdbc.password=123456jdbc.driver=com.mysql.jdbc.Driverjdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&autoReconnect=true&characterEncoding=UTF-8

三、应用类

package com.test;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Map;import org.junit.Test;import com.test.util.JdbcUtil;public class JdbcTest {/** * 更新用户信息 */@Testpublic void updateUser(){String name = "张三";int age = 18;int score = 60;int id =1;String sql = "update test_table set name=?,age=?,score=? where id=?";//创建填充参数的listList<Object> paramList = new ArrayList<Object>();//填充参数paramList.add(name);paramList.add(age);paramList.add(score);paramList.add(id);JdbcUtil jdbcUtil = null;boolean bool = false;try {jdbcUtil = new JdbcUtil();jdbcUtil.getConnection(); // 获取数据库链接bool = jdbcUtil.updateByPreparedStatement(sql, paramList);} catch (SQLException e) {System.out.println(this.getClass()+"执行更新操作抛出异常！");e.printStackTrace();} finally {if (jdbcUtil != null) {jdbcUtil.releaseConn(); // 一定要释放资源}}System.out.println("执行更新的结果："+bool);}/** * 根据id查询用户信息 */@Testpublic void findUserById(){int id = 1;String sql = "select * from test_table where id = ?";//创建填充参数的listList<Object> paramList = new ArrayList<Object>();//填充参数paramList.add(id);JdbcUtil jdbcUtil = null;try {jdbcUtil = new JdbcUtil();jdbcUtil.getConnection(); // 获取数据库链接List<Map<String, Object>> mapList = jdbcUtil.findResult(sql.toString(), paramList);if(mapList.size()==1){Map<String, Object> map = mapList.get(0);String name = (String) map.get("name");int age = (int) map.get("age");int score = (int) map.get("score");System.out.println("姓名:"+name+";年龄:"+age+";成绩:"+score);}} catch (SQLException e) {System.out.println(this.getClass()+"执行查询操作抛出异常！");e.printStackTrace();} finally {if (jdbcUtil != null) {jdbcUtil.releaseConn(); // 一定要释放资源}}}}

四、执行结果

update==>执行更新的结果：true
find==>姓名:张三;年龄:18;成绩:60