Druid -- JDBC tool class case

What is? Druid？

​ Druid Is an efficient data query system , The main solution is to aggregate query a large number of time-based data . Data can be ingested in real time , Enter into Druid It can be checked immediately after , At the same time, the data is almost immutable . It's usually a matter of fact event based on timing , After the fact comes into Druid, The external system can query the fact . At present, the commonly used data sources are c3p0、dbcp、proxool、druid.

Druid characteristic ：

​ Sub second query ：druid It provides fast aggregation ability and sub second performance OLAP Query power , Multi tenant design , It's an ideal way to analyze applications for users
Real time data injection ：druid Support stream data injection , And provides event driven data , Ensure the effectiveness and unity of events in real-time and offline environments
​ Extensible PB Levels of storage ：druid Clusters can be easily expanded to PB The amount of data , A million levels of data injection per second . Even if you scale up the data , It can also guarantee its effectiveness
​ Multi environment deployment ：druid It can run on commercial hardware , It can also run on the cloud . It can inject data from a variety of data systems , Include hadoop,spark,kafka,storm and samza etc.
​ A rich community ：druid Have a rich community , For everyone to learn

rely on

  <properties>
        <mysql.version>5.1.49</mysql.version>
        <log4j.version>1.2.12</log4j.version>
    </properties>

 <dependencies>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>${log4j.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.27</version>
        </dependency>
        <!-- commons-dbutils -->
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.6</version>
        </dependency>
        <!-- fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.22</version>
        </dependency>
    </dependencies>

It needs to be modified according to the situation database,username,password

jdbc.properties The configuration file

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.71.222:3306/test?useSSL=false&createDatabaseIfNotExist=true&characterEncoding=UTF-8
username=root
password=root
** The following configuration is not required 
* Initialization length 
initialSize=5
* maximum connection 
maxActive=10
* Timeout waiting time 
maxWait=3000

log4j.properties file

log4j.rootLogger=warn, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n
log4j.appender.logfile=org.apache.log4j.FileAppender
log4j.appender.logfile.File=target/spring.log
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d %p [%c] - %m%n

Prepare a table

create databases test;
use test;
create table student(
name varchar(30),
age int(10),
gender varchar(10)
);
insert into student(name,age,gender) values('zwh',19,'male');
insert into student(name,age,gender) values('cm',18,'female');

// Check it out 
select * from stundet;

JDBCReadUtils

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.log4j.Logger;

import java.io.File;
import java.io.FileInputStream;
import java.io.Serializable;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class JDBCReadUtils implements Serializable {
    
    private static Logger logger = Logger.getLogger(JDBCReadUtils.class);
    /** *  Realization JDBCHelper Singleton of  */
    private static JDBCReadUtils instance = null;
    private QueryRunner runner = null;

    /** *  In the process of implementing the singleton , Create a unique database connection pool  */
    private JDBCReadUtils(String url) {
    
        Properties properties = new Properties();
        try {
    
            properties.load(new FileInputStream(new File(url)));
            runner = new QueryRunner(DruidDataSourceFactory.createDataSource(properties));

        } catch (Exception e) {
    
            logger.error(e.getMessage(), e);
        }
    }

    public JDBCReadUtils() {
    
    }

    /** *  Access to the singleton  */
    public static JDBCReadUtils getInstance(String url) {
    
        if (instance == null) {
    
            synchronized (JDBCReadUtils.class) {
    
                if (instance == null) {
    
                    instance = new JDBCReadUtils(url);
                }
            }
        }
        return instance;
    }



    /** *  Inquire about （ return Array result ） */
    private Object[] queryArray(String sql, Object... params) {
    
        Object[] result = null;
        try {
    
            result = runner.query(sql, new ArrayHandler(), params);
        } catch (SQLException e) {
    
            logger.error(e.getMessage());
        }
        return result;
    }

    /** *  Inquire about （ return ArrayList result ） */
    public List<Object[]> queryArrayList(String sql, Object... params) {
    
        List<Object[]> result = null;
        try {
    
            result = runner.query(sql, new ArrayListHandler(), params);
        } catch (SQLException e) {
    
            logger.error(e.getMessage());
        }
        return result == null ? new ArrayList<Object[]>() : result;
    }

    /** *  to update （ Include UPDATE、INSERT、DELETE, Returns the number of affected rows ） */
    public int update(String sql, Object... params) {
    
        int result = 0;
        try {
    
            result = runner.update(sql, params);
        } catch (SQLException e) {
    
            logger.error(e.getMessage());
        }
        return result;
    }
}

JDBCTest

import java.util.List;

public class JDBCTest {
    
    public static void main(String[] args) {
    
 
        JDBCReadUtils readUtils = JDBCReadUtils
                .getInstance("D:\\javaproject\\k12project\\druidJDBC\\src\\main\\resources\\jdbc.properties");
        
        // lookup 
        //o[0]  Write according to the number of fields , The following dynamic parameters are used to write where Conditions 
        //  Such as ：List<Object[]> result = readUtils.queryArrayList("select * from student where age>?","18");
        /*List<Object[]> result = readUtils.queryArrayList("select name,age from student"); for (Object[] o : result) { System.out.println(o[0]+ "\t" + o[1] ); }*/

        
        // to update 
        int update = readUtils.update("insert into student(name,age,gender) values('wjing',12,'female') ");
        System.out.println(update);


    }
}