• Phoenix的jdbc封装


    一、Phoenix版本

    <dependency>
    <groupId>org.apache.phoenix</groupId>
    <artifactId>phoenix-core</artifactId>
    <version>4.8.0-HBase-1.2</version>
    </dependency>

    二、连接Phoenix和基本CRUD封装

    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    /**
     * 功能介绍:使用jdbc对数据库操作:查询、更新(插入/修改/删除)、批量更新
     */
    public class DButil {
    
        private final Logger LOGGER = LoggerFactory.getLogger(getClass());
    
        /**jdbc的链接*/
        private Connection conn = null;
    
        /**准备sql*/
        private PreparedStatement ps = null;
    
        {
            initConnection();
        }
    
        /**
         * @param sql
         * @param params 参数
         * 功能介绍:更新操作(修改,删除,插入)
         */
        public int executeUpdate(String sql, Object[] params) {
            if(null == conn){
                initConnection();
            }
            try {
                ps = conn.prepareStatement(sql);
                if (params.length != 0) {
                    for (int i = 0; i < params.length; i++) {
                        ps.setObject(i + 1, params[i]);
                    }
                }
                int rows = ps.executeUpdate();
                conn.commit();
                return rows;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                closeUpdate();
            }
            return 0;
        }
    
        /**
         * @param sql
         * @param list
         * 功能介绍:批量更新
         */
        public void batchUpdate(String sql, List<Object[]> list) {
    
            if(null == conn){
                initConnection();
            }
            try {
                ps = conn.prepareStatement(sql);
                //关闭自动提交事务
                conn.setAutoCommit(false);
                //防止内存溢出
                final int batchSize = 1000;
                //记录插入数量
                int count = 0;
                int size = list.size();
                Object[] obj = null;
                for (int i = 0; i < size; i++) {
                    obj = list.get(i);
                    for (int j = 0; j < obj.length; j++) {
                        ps.setObject(j + 1, obj[j]);
                    }
                    ps.addBatch();
                    if (++count % batchSize == 0) {
                        ps.executeBatch();
                        conn.commit();
                    }
                }
                ps.executeBatch();
                conn.commit();
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
                try {
                    conn.rollback();
                    conn.setAutoCommit(true);
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            } finally {
                //关闭资源
                closeUpdate();
            }
        }
    
        /**
         * @param sql
         * @param params
         * 功能介绍:查询操作
         */
        public List<Map<String, Object>> executeQuery(String sql, Object[] params) {
            if(null == conn){
                initConnection();
            }
            ResultSet rs = null;
            List<Map<String, Object>> list = null;
            try {
                ps = conn.prepareStatement(sql);
                if (params != null) {
                    for (int i = 0; i < params.length; i++) {
                        ps.setObject(i + 1, params[i]);
                    }
                }
    
                long startTime = System.currentTimeMillis();
                rs = ps.executeQuery();
                LOGGER.info("UserBigTableService sql-executeQuery-time: " + (System.currentTimeMillis() - startTime) + "ms");
    
                list = new ArrayList<>();
                //移动光标,如果新的当前行有效,则返回 true;如果不存在下一行,则返回 false
                while (rs.next()) {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    Map<String, Object> map = new HashMap<>(16);
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        map.put(rsmd.getColumnName(i), rs.getObject(i));
                    }
                    list.add(map);
                }
                return list;
    
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                closeQuery(rs);
            }
            return null;
        }
    
        /**
         * @param sql
         * @param params
         * 功能介绍:查询操作一条记录
         */
        public Map<String, Object> query (String sql, Object[] params) {
            if(null == conn){
                initConnection();
            }
            ResultSet rs = null;
            Map<String, Object> map = null;
            try {
                ps = conn.prepareStatement(sql);
                if (params != null) {
                    for (int i = 0; i < params.length; i++) {
                        ps.setObject(i + 1, params[i]);
                    }
                }
                rs = ps.executeQuery();
                //移动光标,如果新的当前行有效,则返回 true;如果不存在下一行,则返回 false
                while (rs.next()) {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    map = new HashMap<>(16);
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        map.put(rsmd.getColumnName(i), rs.getObject(i));
                    }
                    //若有多条记录,取第一条。
                    break;
                }
                return map;
    
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                closeQuery(rs);
            }
            return null;
        }
    
        /**
         * 初始化连接
         */
        private void initConnection() {
            try {
                //local
               conn = DriverManager.getConnection("jdbc:phoenix:192.168.1.220");
                
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        /**
         * 功能介绍:关闭更新资源
         */
        private void closeUpdate() {
            try {
                if (ps != null) {
                    ps.close();
                }
    
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
        /**
         * @param rs 功能介绍:关闭查询资源
         */
        private void closeQuery(ResultSet rs) {
            try {
                if (rs != null) {
                    rs.close();
                }
    
                if (ps != null) {
                    ps.close();
                }
    
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
    }
    knmz
  • 相关阅读:
    FFmpeg源码分析:avcodec_find_decoder
    FFmpeg源码分析:解码器流程
    05Linux网络编程基础 ---- 定时器
    04Linux网络编程基础 ---- 信号
    03Linux网络编程基础 ---- IO复用
    SRS流媒体服务器04 ---- st-thread框架
    react-render()
    react开发学习
    php代码运行提速的20个小技巧(转)
    Symfony2 资料篇
  • 原文地址:https://www.cnblogs.com/Hello-java/p/11099060.html
Copyright © 2020-2023  润新知