大数据的操作

package cn.vast;

import com.alibaba.druid.pool.DruidDataSource;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

/**
 * 对于大数据量的数据库操作使用多线程，多数据库连接的方式提高效率
 */
public class ThreadPoolManyConnection {


    /**
     * 线程池,10多个线程执行insert 10000条
     * 十个线程表现良好21秒，过多卡顿
     * 其实真是情况，由于与MySQL只建立了一个连接
     * Java开再多的线程也没用，使用druid操作多个连接，使用连接池
     * 读取多个连接并存到自定的连接池集合中
     */
    public static void main(String[] args) {
        long start = System.currentTimeMillis();
        System.out.println("执行插入10000条数据，开始时间" + start);
        ExecutorService threadPool = Executors.newCachedThreadPool();
        threadPool.submit(new ThreadPool(start));
        threadPool.submit(new ThreadPool(start));
        threadPool.submit(new ThreadPool(start));
        threadPool.submit(new ThreadPool(start));
        threadPool.submit(new ThreadPool(start));
        threadPool.submit(new ThreadPool(start));
        threadPool.submit(new ThreadPool(start));
        threadPool.submit(new ThreadPool(start));
        threadPool.submit(new ThreadPool(start));
        threadPool.submit(new ThreadPool(start));

        threadPool.shutdown();


    }

    static class ThreadPool extends Thread {
        static Integer count = 0;
        Long start = 0L;
        Long ent = 0L;


        @Override
        public void run() {
            while (count <= 10000) {
                /**
                 * 使用了多线程加锁，真正锁住，会影响效率，只有一把锁？那不就挨个执行？多线程就没用了
                 * 所以此处在数据库层面校验
                 */
                synchronized (this) {
                    count += 1;
                    if (count <= 10000) {
                            insert(count);
                    } else {
                        long end = System.currentTimeMillis();
                        System.out.println("执行插入10000条数据，结束时间" + end);
                        this.ent = end;
                        Long out = (this.ent - this.start);
                        System.out.println("共耗时 " + out + " 毫秒");
                        System.out.println("共耗时 " + (out / 1000) + " 秒");
                        System.out.println("共耗时 " + ((out / (1000 * 60)) + "." + (out % (1000 * 60))) + " 分");
                        //执行完毕关闭资源
                        for (int i = 0; i < connectionList.size(); i++) {
                            try {
                                connectionList.get(i).close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                        dataSource.close();
                        break;
                    }
                }
            }
        }

        public ThreadPool(Long start) {
            this.start = start;
        }

        public ThreadPool() {
        }

        public Long getStart() {
            return start;
        }

        public void setStart(Long start) {
            this.start = start;
        }

    }


    public static void insert(Integer id) {
        try {

            PreparedStatement ps = getConnection().prepareStatement("insert into user values (" + id + ",'张三','123','110','@163.com')");
            ps.executeUpdate();
            ps.close();//没成功插入一条关闭
        } catch (MySQLIntegrityConstraintViolationException e) {
            System.out.println("id = " + id + " 的用户已经插入，数据库主键阻止了重复插入的数据");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 使用jdbc传统连接池只成功获取了一个
     *
     * @return
     */
    private static DruidDataSource dataSource = null;
    private static List<Connection> connectionList = new ArrayList<>();
    private static Random random = new Random();

    static {
        try {
            dataSource = new DruidDataSource();
            dataSource.setDriverClassName("com.mysql.jdbc.Driver");
            dataSource.setUrl("jdbc:mysql:///test");
            dataSource.setUsername("root");
            dataSource.setPassword("root");
            dataSource.setMaxActive(50);
            for (int i = 0; i < 50; i++) {
                connectionList.add(dataSource.getConnection());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 从druid中获取8个连接，保存到集合中随机取出连接
     * 经过尝试之所以8个，多了获取不到
     *
     * @return
     */
    public synchronized static Connection getConnection() {
        return connectionList.get(random.nextInt(50));
    }

    public synchronized static boolean isInsert(Integer id) {
        ResultSet resultSet = null;
        Boolean flag = false;
        try {
            Connection connection = connectionList.get(random.nextInt(50));
            PreparedStatement ps = connection.prepareStatement("select *  from user where  id=" + id);
            resultSet = ps.executeQuery();
            flag = resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }
}