[大数据]JDBCUtils附加增删改查功能

1. db.properties连接

   driver = com.mysql.jdbc.Driver
   url = jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8
   user = root
   password = 123456

2. 工具类连接数据库? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? JDBCUtils：

   package cn.pzhu.util;
   
   import java.io.IOException;
   import java.io.InputStream;
   import java.sql.*;
   import java.util.*;
   
   public class JDBCUtils {
       private static String driver = null;
       private static String url = null;
       private static String user = null;
       private static String password = null;
       private Connection con;
       private PreparedStatement preparedStatement;
       private ResultSet resultSet;
       //1.注册驱动
       static{
           InputStream in = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");
           Properties properties = new Properties();
           try {
               properties.load(in);
               driver = properties.getProperty("driver");
               url = properties.getProperty("url");
               user = properties.getProperty("user");
               password = properties.getProperty("password");
               try {
                   Class.forName(driver);
               } catch (ClassNotFoundException e) {
                   e.printStackTrace();
               }
           } catch (IOException e) {
               e.printStackTrace();
           }
       }
      public JDBCUtils(){
          try {
              con=DriverManager.getConnection(url, user, password);
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
       //2.获得连接
       public  Connection getConnection(){
           return con;
       }
   
       //3.释放资源
       public static void close(Connection connection, Statement statement, ResultSet result){
           if(result!=null){
               try {
                   result.close();
               } catch (SQLException throwables) {
                   throwables.printStackTrace();
               }
           }
           if(connection!=null){
               try {
                   connection.close();
               } catch (SQLException throwables) {
                   throwables.printStackTrace();
               }
           }           if(statement!=null){
               try {
                   statement.close();
               } catch (SQLException throwables) {
                   throwables.printStackTrace();
               }
           }
       }
   
       /**
        * 增删改操作
        * @param sql
        * @param params
        * @return
        * @throws SQLException
        */
       public boolean updateSql(String sql, List<Object> params) throws SQLException{
           boolean flag=false;
           int result=-1;
           int index=1;
           preparedStatement=con.prepareStatement(sql);
           //空参数判断
           if(params!=null&&!params.isEmpty()) {
               //params遍历
               for (int i = 0; i < params.size(); i++) {
                   //预编译 填充参数
                   preparedStatement.setObject(index++, params.get(i));
   
               }
           }
   
           result=preparedStatement.executeUpdate();
           //执行成功
           if(result>0){
               flag=true;
           }
           return flag;
       }
   
       /**
        * 单查询
        * @param sql
        * @param params
        * @return Map<String, Object>
        * @throws SQLException
        */
       public Map<String, Object> findSimpleResult(String sql, List<Object> params) throws SQLException{
          Map<String,Object> map=new HashMap<String,Object>();
          int index=1;//setObject（）都是1开始
          preparedStatement=con.prepareStatement(sql);
          //空参数判断
          if(params!=null&&!params.isEmpty()){
              for (int i=0;i<params.size();i++){
                  //填充占位符
                  preparedStatement.setObject(index++,params.get(i));
              }
              resultSet = preparedStatement.executeQuery();
              ResultSetMetaData metaData=resultSet.getMetaData();//以getMetaData就包括了数据的字段名称、类型以及数目等表格所必须具备的信息
              int column_len=metaData.getColumnCount();//获取字段数
              while (resultSet.next()){
                  for(int i=0;i<column_len;i++){
                      String column_name=metaData.getColumnName(i+1);//坐标从1起
                      Object column_value=resultSet.getObject(column_name);//获取字段值
                      if(column_value==null){
                          column_value="";
                      }
                      map.put(column_name,column_value);//结果填充到map
                  }
              }
          }
          preparedStatement.close();
          resultSet.close();
        return map;
       }
   
   
       /**
        * 多条查询
        * @param sql
        * @param params
        * @return
        * @throws SQLException
        */
       public List<Map<String, Object>> findMoreResult(String sql, List<Object> params) throws SQLException{
           List<Map<String,Object>> list=new ArrayList<Map<String, Object>>();
           int index=1;//setObject（）都是1开始
           preparedStatement=con.prepareStatement(sql);
               for (int i=0;i<params.size();i++){
                   //填充占位符
                   preparedStatement.setObject(index++,params.get(i));
               }
               resultSet = preparedStatement.executeQuery();
               ResultSetMetaData metaData=resultSet.getMetaData();//以getMetaData就包括了数据的字段名称、类型以及数目等表格所必须具备的信息
               int column_len=metaData.getColumnCount();//获取字段数
               while (resultSet.next()){
                   Map<String,Object> map=new HashMap<String, Object>();
                   for(int i=0;i<column_len;i++){
                       String column_name=metaData.getColumnName(i+1);//坐标从1起
                       Object column_value=resultSet.getObject(column_name);//获取字段值
                       if(column_value==null){
                           column_value="";
                       }
                       map.put(column_name,column_value);//结果填充到map
                   }
                   list.add(map);
               }
           return list;
       }
   
       /**
        * 释放resultSet preparedStatement资源
        */
       public void closeAll(){
           if(resultSet!=null){
               try {
                   resultSet.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
           if (preparedStatement!=null){
               try {
                   preparedStatement.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
           if (con!=null){
               try {
                   con.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
       }
   }
   

   3.测试类测试

   package cn.pzhu.test;
   
   import cn.pzhu.util.JDBCUtil;
   import cn.pzhu.util.JDBCUtils;
   import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;
   
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.util.ArrayList;
   import java.util.HashMap;
   import java.util.List;
   import java.util.Map;
   
   public class Test_7 {
       public static void main(String[] args) throws SQLException {
           JDBCUtils jdbcUtils=new JDBCUtils();
            Connection connection= jdbcUtils.getConnection();
            List<Object> list=new ArrayList();
            list.add("1");
            list.add("Alan");
            boolean key= jdbcUtils.updateSql("UPDATE USER SET PASSWORD = ? WHERE NAME = ? ",list);
            System.out.println("updateSql测试.........");
            System.out.println(key);
            List <Object> list1=new ArrayList<Object>();
            list1.add("6");
            String sql="SELECT  * FROM USER WHERE NAME=?";
            Map<String,Object> map=jdbcUtils.findSimpleResult(sql,list1);
            System.out.println("单条查询测试............");
            System.out.println(map);
            List<Object> list2=new ArrayList<Object>();
            String sql2="SELECT  * FROM USER";
            List<Map<String,Object>> listmap=jdbcUtils.findMoreResult(sql2,list2);
            System.out.println("多条查询测试.............");
            System.out.println(listmap);
            jdbcUtils.closeAll();
   
       }
   }
   

   测试结果：

   OK.............测试成功！?