java-对于jdbc的封装总结

Love The Way You Lie 2022-10-22 14:56 151阅读 0赞

## java-对于jdbc的封装总结 ##

--------------------

都需要导入mysql的驱动包

--------------------

**第一种 jdbcUtil：**

public class JDBCUtils { 
    	/*获得mysql连接*/
        public static Connection getConnection(){ 
        //1.导入jar包
    	//2.注册驱动
    	Class.forName("com.mysql.cj.jdbc.Driver");
    	//3.创建连接
    	Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/XXX","XX","XX");
    	System.out.println(con);
        }
    }

**第二种 properties +JdbcUtils：**

**jdbc.properties**

user=root
    password=123456
    url=jdbc:mysql://localhost:3306/db1?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
    driver=com.mysql.cj.jdbc.Driver

**jdbcUtils**

public class JDBCUtils { 
        private static Properties properties;
    
        /*加载property文件和加载msyql驱动*/
        static { 
            try { 
    
                properties = new Properties();
                FileInputStream is = new FileInputStream("jdbc.properties");
                properties.load(is);
    
                String driver=properties.getProperty("driver");
                Class.forName(driver);
            } catch (Exception e) { 
                e.printStackTrace();
            }
        }
        /*获得mysql连接*/
        public static Connection getConnection(){ 
            try { 
                return DriverManager.getConnection(properties.getProperty("url"),properties);
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            return null;
        }
        /*释放资源*/
        public static void close(ResultSet rs, Statement stmt, Connection conn){ 
            if( rs != null){ 
                try { 
                    rs.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
    
            if( stmt != null){ 
                try { 
                    stmt.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
    
            if( conn != null){ 
                try { 
                    conn.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
        }
    }

**第三种：JdbcTemplate和druid**  
导入springjdbc的相关jar包及druid的jar包

durid.properties:

url=jdbc:mysql://localhost:3306/db1?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
    #连接池
    driverClassName=com.mysql.cj.jdbc.Driver
    username=root
    password=123456
    
    #初始化连接数
    initialSize=10
    #最大连接数
    maxActive=30
    #最小连接数
    minIdle=10
    #最大等待时间
    maxWait=2000
    #缓冲开启
    poolPreparedStatements=true
    #最大缓冲区
    maxOpenPreparedStatements=20

JdbcUtil:

/* * 利用druid连接池获得数据库连接和连接池 * 对于数据库连接封装 * 有获得连接池的方法 和 获得连接对象的方法，关闭资源的方法 * */
    public class JDBCUtil { 
        /*连接池对象*/
        private static DataSource ds;
    
        static { 
            //1.导入jar包，定义配置文件
            //2.加载配置文件
            FileInputStream is = null;
            try { 
                is = new FileInputStream("druid.properties");
                Properties pro = new Properties();
                pro.load(is);
                //3.利用druid连接池，获得连接池对象
                ds = DruidDataSourceFactory.createDataSource(pro);
            } catch (Exception e) { 
                e.printStackTrace();
            }
        }
        //获取连接池对象的方法
        public static DataSource getDataSource(){ 
            return ds;
        }
        //释放资源的方法--针对 查询
        public void close(Statement stmt, Connection con, ResultSet rs){ 
            if(rs!=null){ 
                try { 
                    rs.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
            if(stmt!=null){ 
                try { 
                    stmt.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
            if(con!=null){ 
                try { 
                    con.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
        }
    }

使用:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW96aHV6aHV5YW5n_size_16_color_FFFFFF_t_70]

**第四种 durid.properties+commons-dbutils-1.6.jar**  
导入druid jar包和commons-dbutils-1.6.jar

druid.properties:

driverClassName=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://localhost:3306/eshop?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
    username:root
    password:123456

JdbcUtil:

public class JdbcUtil { 
        private static QueryRunner runner=null;
        static { 
            InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("druid.properties");
            Properties pro = new Properties();
            try { 
                pro.load(is);
                DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);
                 runner = new QueryRunner(dataSource);
            } catch (Exception e) { 
                e.printStackTrace();
            }
        }
    	/*查询多条数据*/
        public static <T> List<T> queryList(Class clazz, String sql, Object... args){ 
            try { 
                BasicRowProcessor brp = new BasicRowProcessor(new GenerousBeanProcessor());
                return runner.query(sql,new BeanListHandler<T>(clazz,brp),args);
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            return null;
        }
    
        /* 查询一个 <T>声明 */
        public static <T> T queryOne(Class clazz, String sql, Object... args){ 
            try { 
                BasicRowProcessor brp = new BasicRowProcessor(new GenerousBeanProcessor());
                return runner.query(sql,new BeanHandler<T>(clazz,brp),args);
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            return null;
        }
    
        /** * 查询数据总数 * @param sql * @param args * @return */
        public static int queryCount(String sql,Object... args){ 
            try { 
                return runner.query(sql, resultSet -> { 
                    resultSet.next();
                    return resultSet.getInt(1);
                });
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            return 0;
        }
    
        /*增删改*/
        public static int update(String sql, Object... args){ 
            try { 
                BasicRowProcessor brp = new BasicRowProcessor(new GenerousBeanProcessor());
                return runner.update(sql,args);
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            return 0;
        }
    }

使用：  
![在这里插入图片描述][20210317191154807.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW96aHV6aHV5YW5n_size_16_color_FFFFFF_t_70]: /images/20221022/7cd89d60e28245eca8b825bb8a930587.png
[20210317191154807.png]: /images/20221022/d345e8656dcb4562955641ddbd5dd66d.png