首页 > 代码库 > JDBCUtil

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 =http://www.mamicode.com/= 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=root  jdbc.password=123456  jdbc.driver=com.mysql.jdbc.Driver  jdbc.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 {                              /**          * 更新用户信息          */          @Test          public void updateUser(){              String name = "张三";              int age = 18;              int score = 60;              int id =1;              String sql = "update test_table set name=?,age=?,score=? where id=?";              //创建填充参数的list              List<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查询用户信息          */          @Test          public void findUserById(){              int id = 1;              String sql = "select * from test_table where id = ?";              //创建填充参数的list              List<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

JDBCUtil