Java Code Examples for com.alibaba.druid.pool.DruidDataSource#getConnection()

@GetMapping("/datasource2")
public Map<String, Object> datasource2() throws SQLException {
    DruidDataSource druidDataSource = (DruidDataSource)dataSource;
    Map result = new HashMap();
    result.put("数据源类名", druidDataSource.getClass()+"");
    // 获取数据库连接对象
    Connection connection = druidDataSource.getConnection();
    // 判断连接对象是否为空
    result.put("能否正确获得连接", connection != null);
    result.put("initialSize值为",druidDataSource.getInitialSize());
    result.put("maxActive值为",druidDataSource.getMaxActive());
    result.put("minIdle值为",druidDataSource.getMinIdle());
    result.put("validationQuery值为",druidDataSource.getValidationQuery());
    result.put("maxWait值为",druidDataSource.getMaxWait());
    connection.close();
    return result;
}
 

private Connection getConnection(InvokeContext ctx) throws SQLException {
    DruidDataSource dataSource = dataSources.get(ctx.getSlotName());
    if (dataSource == null) {
        DruidDataSource tmp;
        synchronized (this) {
            tmp = createDataSource(ctx);
        }
        dataSources.put(ctx.getSlotName(), tmp);
        dataSource = dataSources.get(ctx.getSlotName());
        LOG.info("DataSource Initialized. Slot:{},DataSource:{}", ctx.getSlotName(), dataSource.getName());
    }
    return dataSource.getConnection();
}
 

private void doSelect(DruidDataSource dataSource, int start, int end) throws Exception {
    for (int i = start; i < end; ++i) {
        String tableName = "t" + i;
        String sql = "select * from " + tableName + " where " + tableName + ".id = " + i;
        Connection conn = dataSource.getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute(sql);
        stmt.close();
        conn.close();
    }
}
 

private void doUpdate(DruidDataSource dataSource, int start, int end) throws Exception {
    for (int i = start; i < end; ++i) {
        String tableName = "t" + i;
        String sql = "update " + tableName + " set name='aaa' where " + tableName + ".id = " + i;
        Connection conn = dataSource.getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute(sql);
        stmt.close();
        conn.close();
    }
}
 

private Connection getConnection(InvokeContext ctx) throws SQLException {
    DruidDataSource dataSource = dataSources.get(ctx.getSlotName());
    if (dataSource == null) {
        DruidDataSource tmp;
        synchronized (this) {
            tmp = createDataSource(ctx);
        }
        dataSources.put(ctx.getSlotName(), tmp);
        dataSource = dataSources.get(ctx.getSlotName());
        LOG.info("DataSource Initialized. Slot:{},DataSource:{}", ctx.getSlotName(), dataSource.getName());
    }
    return dataSource.getConnection();
}
 

/**
 * @param args
 */
public static void main(String[] args) {
    try {
        DruidDataSource ds = new DruidDataSource();
        ds.setUsername("test");
        ds.setPassword("");
        ds.setUrl("jdbc:mysql://10.20.153.178:9066/");
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setMaxActive(-1);
        ds.setMinIdle(0);
        ds.setTimeBetweenEvictionRunsMillis(600000);
        // ds.setNumTestsPerEvictionRun(Integer.MAX_VALUE);
        ds.setMinEvictableIdleTimeMillis(DruidDataSource.DEFAULT_MIN_EVICTABLE_IDLE_TIME_MILLIS);
        Connection conn = ds.getConnection();

        Statement stm = conn.createStatement();
        stm.execute("show @@version");

        ResultSet rst = stm.getResultSet();
        rst.next();
        String version = rst.getString("VERSION");

        System.out.println(version);
        ds.close();
    } catch (Exception exception) {
        System.out.println("10.20.153.178:9066   " + exception.getMessage() + exception);
    }
}
 

public static void main(String[] args) throws Exception {
    DruidDataSource ds = new DruidDataSource();
    ds.setUrl("jdbc:mysql://42.120.217.1/DRDS_1053599184310491_HOIBS_CLUSTER?characterEncoding=utf8");
    ds.setUsername("DRDS_1053599184310491_HOIBS_CLUSTER");
    ds.setPassword("hfd000999");

    Connection conn = ds.getConnection();
    conn.prepareStatement("set names utf8mb4").executeUpdate();

    byte[] b3 = { (byte) 0xF0, (byte) 0x9F, (byte) 0x90, (byte) 0xA0 }; // 0xF0
                                                                        // 0x9F
                                                                        // 0x8F
                                                                        // 0x80

    String text = new String(b3, "utf-8");
    text.getBytes("utf-8");
    PreparedStatement ps = conn.prepareStatement("insert into INS_EBAY_MESSAGE(msg_id,deadline_time,folder,item_end_time,item_no,message_no,is_read,receive_time,recipient_usr_id,sender,send_to_name,subject,create_time,order_id,usr_id,text) values(?,'2015-03-24 16:37:16','0','2014-02-23 02:42:29','181330498504','54529786058','true','2014-03-24 16:37:16','spring.inc','babz.brinz','spring.inc','babz.brinzClearForAppleiPhone55G5SHotSale0.5mmUltraThinMatteBackCaseSkin','2014-06-30 18:19:26',7001,4010,?);");
    long msg_id = System.currentTimeMillis();
    ps.setLong(1, msg_id);
    ps.setString(2, text);
    ps.executeUpdate();

    ps = conn.prepareStatement("select * from INS_EBAY_MESSAGE where msg_id=?");
    ps.setLong(1, msg_id);

    ResultSet rs = ps.executeQuery();

    while (rs.next()) {
        System.out.println(rs.getString("text").getBytes());
    }
    ps.close();
    conn.close();
    System.out.println("query done");
}
 

public static void main(String[] args) throws Exception {
    DruidDataSource ds = new DruidDataSource();
    ds.setUrl("jdbc:mysql://127.0.0.1:9507/");
    ds.setUsername("TDDL5_APP");
    ds.setPassword("TDDL5_APP");
    ds.init();

    System.out.println("init done");
    Connection conn = ds.getConnection();
    // insert a record
    // conn.prepareStatement("replace into sample_table (id,name,address) values (1,'sun','hz')").executeUpdate();
    System.out.println("insert done");
    // select all records
    PreparedStatement ps = conn.prepareStatement("replace into tddl_category (id,name,gmt_modified,gmt_create) values (1,'aa',now(),now())");
    // ps.executeUpdate();

    ps = conn.prepareStatement("show @@version");
    // ps.setDate(1, new Date(System.currentTimeMillis()));
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        StringBuilder sb = new StringBuilder();
        int count = rs.getMetaData().getColumnCount();
        for (int i = 1; i <= count; i++) {
            String key = rs.getMetaData().getColumnLabel(i);
            Object val = rs.getObject(i);
            sb.append("[" + rs.getMetaData().getTableName(i) + "." + key + "->" + val + "]");
        }
        System.out.println(sb.toString());
    }

    rs.close();
    ps.close();
    conn.close();
    System.out.println("query done");
}
 

@Test
public void testJDBC() throws Exception {
    Properties properties = new Properties();
    properties.put(PROP_URL, "jdbc:elasticsearch://127.0.0.1:9300/" + TestsConstants.TEST_INDEX_ACCOUNT);
    properties.put(PROP_CONNECTIONPROPERTIES, "client.transport.ignore_cluster_name=true");
    DruidDataSource dds = (DruidDataSource) ElasticSearchDruidDataSourceFactory.createDataSource(properties);
    Connection connection = dds.getConnection();
    PreparedStatement ps = connection.prepareStatement("SELECT /*! USE_SCROLL*/ gender,lastname,age,_scroll_id from  " + TestsConstants.TEST_INDEX_ACCOUNT + " where lastname='Heath'");
    ResultSet resultSet = ps.executeQuery();

    ResultSetMetaData metaData = resultSet.getMetaData();
    assertThat(metaData.getColumnName(1), equalTo("gender"));
    assertThat(metaData.getColumnName(2), equalTo("lastname"));
    assertThat(metaData.getColumnName(3), equalTo("age"));

    List<String> result = new ArrayList<String>();
    String scrollId = null;
    while (resultSet.next()) {
        scrollId = resultSet.getString("_scroll_id");
        result.add(resultSet.getString("lastname") + "," + resultSet.getInt("age") + "," + resultSet.getString("gender"));
    }

    ps.close();
    connection.close();
    dds.close();

    Assert.assertEquals(1, result.size());
    Assert.assertEquals("Heath,39,F", result.get(0));
    Assert.assertFalse(Matchers.isEmptyOrNullString().matches(scrollId));
}
 

@Test
public void test() throws InterruptedException, SQLException {

    DruidDataSource dataSource = new DruidDataSource();

    dataSource.setUrl(JDBC_URL);
    dataSource.setValidationQuery("select 'x'");
    dataSource.setUsername("test");
    dataSource.setPassword("test");

    dataSource.init();
    try {
        Connection connection = dataSource.getConnection();
        Assert.assertNotNull(connection);

        connection.close();

        PluginTestVerifier verifier = PluginTestVerifierHolder.getInstance();
        verifier.printCache();

        verifier.verifyTrace(event(serviceType, getConnectionMethod));
        verifier.verifyTrace(event(serviceType, closeConnectionMethod));
    } finally {
        if (dataSource != null) {
            dataSource.close();
        }
    }
}
 

public static void main(String[] args) throws SQLException, InterruptedException {
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring/spring-context.xml");
        context.start();
        DruidDataSource dataSource = context.getBean("dataSource", DruidDataSource.class);

        // 向DruidHealthTracker中添加数据源，即可收集健康信息
        DruidHealthTracker.addDataSource("ad", dataSource);

        Connection connection = dataSource.getConnection();
        String sql = "select * from config";
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        while (true) {
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
//            JdbcUtils.printResultSet(resultSet);
            System.out.println("test");
            Thread.sleep(ThreadLocalRandom.current().nextLong(5000));
        }
//        System.out.println(resultSet.getFetchSize());


//        for (int i = 0; i < 10; i++) {
//            new Thread(() -> {
//                ResultSet resultSet = null;
//                try {
//                    while (true) {
//                        resultSet = ps.executeQuery();
//                        System.out.println(resultSet.getFetchSize());
//                        Thread.sleep(ThreadLocalRandom.current().nextLong(5000));
//                    }
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                } catch (InterruptedException e) {
//                    e.printStackTrace();
//                } finally {
//                    if (resultSet != null) {
//                        try {
//                            resultSet.close();
//                        } catch (SQLException e) {
//                            e.printStackTrace();
//                        }
//                    }
//                }
//            }).start();
//        }


//        ps.close();
//        connection.close();
//        Thread.sleep(Integer.MAX_VALUE);
    }
 

@Test
public void test01() throws SQLException {
    DruidDataSource dataSource = new DruidDataSource();
    // dataSource.setDriverClassName("oracle.jdbc.OracleDriver");
    // dataSource.setUrl("jdbc:oracle:thin:@127.0.0.1:49161:XE");
    // dataSource.setUsername("mytest");
    // dataSource.setPassword("m121212");

    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/mytest?useUnicode=true");
    dataSource.setUsername("root");
    dataSource.setPassword("121212");

    dataSource.setInitialSize(1);
    dataSource.setMinIdle(1);
    dataSource.setMaxActive(2);
    dataSource.setMaxWait(60000);
    dataSource.setTimeBetweenEvictionRunsMillis(60000);
    dataSource.setMinEvictableIdleTimeMillis(300000);

    dataSource.init();

    Connection conn = dataSource.getConnection();

    conn.setAutoCommit(false);
    PreparedStatement pstmt = conn
        .prepareStatement("insert into user (id,name,role_id,c_time,test1,test2) values (?,?,?,?,?,?)");

    java.util.Date now = new java.util.Date();
    for (int i = 1; i <= 10000; i++) {
        pstmt.clearParameters();
        pstmt.setLong(1, (long) i);
        pstmt.setString(2, "test_" + i);
        pstmt.setLong(3, (long) i % 4 + 1);
        pstmt.setDate(4, new java.sql.Date(now.getTime()));
        pstmt.setString(5, null);
        pstmt.setBytes(6, null);

        pstmt.execute();
        if (i % 5000 == 0) {
            conn.commit();
        }
    }
    conn.commit();

    pstmt.close();

    // Statement stmt = conn.createStatement();
    // ResultSet rs = stmt.executeQuery("select * from user t where 1=2");
    //
    // ResultSetMetaData rsm = rs.getMetaData();
    // int cnt = rsm.getColumnCount();
    // for (int i = 1; i <= cnt; i++) {
    // System.out.println(rsm.getColumnName(i) + " " + rsm.getColumnType(i));
    // }

    // rs.close();
    // stmt.close();

    // PreparedStatement pstmt = conn
    // .prepareStatement("insert into tb_user (id,name,role_id,c_time,test1,test2)
    // values (?,?,?,?,?,?)");
    // pstmt.setBigDecimal(1, new BigDecimal("5"));
    // pstmt.setString(2, "test");
    // pstmt.setBigDecimal(3, new BigDecimal("1"));
    // pstmt.setDate(4, new Date(new java.util.Date().getTime()));
    // byte[] a = { (byte) 1, (byte) 2 };
    // pstmt.setBytes(5, a);
    // pstmt.setBytes(6, a);
    // pstmt.execute();
    //
    // pstmt.close();

    conn.close();
    dataSource.close();
}
 

@Test
public void test01() throws SQLException {
    DruidDataSource dataSource = new DruidDataSource();
    // dataSource.setDriverClassName("oracle.jdbc.OracleDriver");
    // dataSource.setUrl("jdbc:oracle:thin:@127.0.0.1:49161:XE");
    // dataSource.setUsername("mytest");
    // dataSource.setPassword("m121212");

    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/mytest?useUnicode=true");
    dataSource.setUsername("root");
    dataSource.setPassword("121212");

    dataSource.setInitialSize(1);
    dataSource.setMinIdle(1);
    dataSource.setMaxActive(2);
    dataSource.setMaxWait(60000);
    dataSource.setTimeBetweenEvictionRunsMillis(60000);
    dataSource.setMinEvictableIdleTimeMillis(300000);

    dataSource.init();

    Connection conn = dataSource.getConnection();

    conn.setAutoCommit(false);
    PreparedStatement pstmt = conn.prepareStatement("insert into user (id,name,role_id,c_time,test1,test2) values (?,?,?,?,?,?)");

    java.util.Date now = new java.util.Date();
    for (int i = 1; i <= 10000; i++) {
        pstmt.clearParameters();
        pstmt.setLong(1, (long) i);
        pstmt.setString(2, "test_" + i);
        pstmt.setLong(3, (long) i % 4 + 1);
        pstmt.setDate(4, new java.sql.Date(now.getTime()));
        pstmt.setString(5, null);
        pstmt.setBytes(6, null);

        pstmt.execute();
        if (i % 5000 == 0) {
            conn.commit();
        }
    }
    conn.commit();

    pstmt.close();

    // Statement stmt = conn.createStatement();
    // ResultSet rs = stmt.executeQuery("select * from user t where 1=2");
    //
    // ResultSetMetaData rsm = rs.getMetaData();
    // int cnt = rsm.getColumnCount();
    // for (int i = 1; i <= cnt; i++) {
    // System.out.println(rsm.getColumnName(i) + " " +
    // rsm.getColumnType(i));
    // }

    // rs.close();
    // stmt.close();

    // PreparedStatement pstmt = conn
    // .prepareStatement("insert into tb_user
    // (id,name,role_id,c_time,test1,test2)
    // values (?,?,?,?,?,?)");
    // pstmt.setBigDecimal(1, new BigDecimal("5"));
    // pstmt.setString(2, "test");
    // pstmt.setBigDecimal(3, new BigDecimal("1"));
    // pstmt.setDate(4, new Date(new java.util.Date().getTime()));
    // byte[] a = { (byte) 1, (byte) 2 };
    // pstmt.setBytes(5, a);
    // pstmt.setBytes(6, a);
    // pstmt.execute();
    //
    // pstmt.close();

    conn.close();
    dataSource.close();
}