Java DruidPooledConnection类代码示例

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
@SneakyThrows
private static String findShopId(String shopName) {
    try (DruidPooledConnection connection = DATA_SOURCE.getConnection();
         PreparedStatement preparedStatement =
                 connection.prepareStatement("SELECT id FROM it_shop WHERE name = ?")) {

        preparedStatement.setString(1, shopName);
        try (ResultSet resultSet = preparedStatement.executeQuery()) {
            if (resultSet.next()) {
                return resultSet.getString(1);
            } else {
                return null;
            }
        }
    }
}
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
public static DruidPooledConnection getConnection() {
    DruidPooledConnection con = null;
    try {
        con = dataSource.getConnection();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return con;
}
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
/** 
 * 从连接池中获取数据源链接 
 *  
 * @author gaoxianglong 
 *  
 * @return Connection 数据源链接 
 */  
public static DruidPooledConnection getConnection() {  
	DruidPooledConnection conn = null;  
	if (null != dataSource) {  
		try {  
			conn = dataSource.getConnection();  
		} catch (SQLException e) {  
			e.printStackTrace();  
		}  
	}  
	return conn;  
}
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
@SneakyThrows
private static void insertShop(DruidPooledConnection connection, TcCrawlerShop shop) {
    try (PreparedStatement preparedStatement = connection.prepareStatement(
            "INSERT INTO it_shop("
                    + "id, "
                    + "name, "
                    + "location, "
                    + "describing_rate, "
                    + "service_rate, "
                    + "delivery_rate, "
                    + "comprehensive_rate, "
                    + "support_cod, "
                    + "support_online_pay, "
                    + "updated_person, "
                    + "updated_timestamp, "
                    + "created_person, "
                    + "created_timestamp"
                    + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
        preparedStatement.setString(1, shop.getId());
        preparedStatement.setString(2, shop.getName());
        preparedStatement.setString(3, PROVINCES[RandomUtils.nextInt(0, PROVINCES.length)]);
        preparedStatement.setInt(4, shop.getDescribingRate());
        preparedStatement.setInt(5, shop.getServiceRate());
        preparedStatement.setInt(6, shop.getDeliveryRate());
        preparedStatement.setInt(7, shop.getComprehensiveRate());
        preparedStatement.setString(8, RandomUtils.nextBoolean() ? "0" : "1");
        preparedStatement.setString(9, RandomUtils.nextBoolean() ? "0" : "1");
        preparedStatement.setString(10, TcBaseConsts.STR_32_0);
        preparedStatement.setString(11, "2016-12-31 12:12:12");
        preparedStatement.setString(12, TcBaseConsts.STR_32_0);
        preparedStatement.setString(13, "2016-12-31 12:12:12");
        preparedStatement.execute();
    }
}
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
@SneakyThrows
private static void insertItem(DruidPooledConnection connection, TcCrawlerItem item) {
    try (PreparedStatement preparedStatement = connection.prepareStatement(
            "INSERT INTO it_item("
                    + "id, "
                    + "shop_id, "
                    + "title, "
                    + "description, "
                    + "price, "
                    + "stock, "
                    + "sales_volume, "
                    + "fav_volume, "
                    + "comments_volue, "
                    + "delected, "
                    + "updated_person, "
                    + "updated_timestamp, "
                    + "created_person, "
                    + "created_timestamp"
                    + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
        preparedStatement.setString(1, item.getId());
        preparedStatement.setString(2, item.getShopId());
        preparedStatement.setString(3, item.getTitle());
        preparedStatement.setString(4, item.getDescription());
        preparedStatement.setLong(5, item.getPrice());
        preparedStatement.setInt(6, item.getStock());
        preparedStatement.setInt(7, RandomUtils.nextInt(10, 1500));
        preparedStatement.setInt(8, RandomUtils.nextInt(100, 15000));
        preparedStatement.setInt(9, RandomUtils.nextInt(5, 500));
        preparedStatement.setString(10, "0");
        preparedStatement.setString(11, TcBaseConsts.STR_32_0);
        preparedStatement.setString(12, "2016-12-31 12:12:12");
        preparedStatement.setString(13, TcBaseConsts.STR_32_0);
        preparedStatement.setString(14, "2016-12-31 12:12:12");
        preparedStatement.execute();
    }
}
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
@SneakyThrows
private static void insertItemAttr(DruidPooledConnection connection, List<TcCrawlerItemAttr> attrs) {
    for (int i = 0; i < attrs.size(); i++) {
        TcCrawlerItemAttr attr = attrs.get(i);
        PreparedStatement preparedStatement = connection.prepareStatement(
                "INSERT INTO it_item_attr("
                        + "id, "
                        + "item_id, "
                        + "name, "
                        + "value, "
                        + "sequence, "
                        + "updated_person, "
                        + "updated_timestamp, "
                        + "created_person, "
                        + "created_timestamp"
                        + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
        preparedStatement.setString(1, attr.getId());
        preparedStatement.setString(2, attr.getItemId());
        preparedStatement.setString(3, attr.getName());
        preparedStatement.setString(4, attr.getValue());
        preparedStatement.setLong(5, attr.getSequence());
        preparedStatement.setString(6, TcBaseConsts.STR_32_0);
        preparedStatement.setString(7, "2016-12-31 12:12:12");
        preparedStatement.setString(8, TcBaseConsts.STR_32_0);
        preparedStatement.setString(9, "2016-12-31 12:12:12");
        preparedStatement.execute();
    }
}
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
@SneakyThrows
private static void insertItemCover(DruidPooledConnection connection, List<TcCrawlerItemCover> covers) {
    for (int i = 0; i < covers.size(); i++) {
        TcCrawlerItemCover cover = covers.get(i);
        try (PreparedStatement preparedStatement = connection.prepareStatement(
                "INSERT INTO it_item_cover("
                        + "id, "
                        + "item_id, "
                        + "cover, "
                        + "sequence, "
                        + "updated_person, "
                        + "updated_timestamp, "
                        + "created_person, "
                        + "created_timestamp"
                        + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?)")) {
            preparedStatement.setString(1, cover.getId());
            preparedStatement.setString(2, cover.getItemId());
            preparedStatement.setString(3, cover.getCover());
            preparedStatement.setLong(4, cover.getSequence());
            preparedStatement.setString(5, TcBaseConsts.STR_32_0);
            preparedStatement.setString(6, "2016-12-31 12:12:12");
            preparedStatement.setString(7, TcBaseConsts.STR_32_0);
            preparedStatement.setString(8, "2016-12-31 12:12:12");
            preparedStatement.execute();
        }
    }
}
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
@SneakyThrows
private static void insertItemDetail(DruidPooledConnection connection, List<TcCrawlerItemDetail> details) {
    for (int i = 0; i < details.size(); i++) {
        TcCrawlerItemDetail detail = details.get(i);
        try (PreparedStatement preparedStatement = connection.prepareStatement(
                "INSERT INTO it_item_detail("
                        + "id, "
                        + "item_id, "
                        + "detail, "
                        + "sequence, "
                        + "updated_person, "
                        + "updated_timestamp, "
                        + "created_person, "
                        + "created_timestamp"
                        + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?)")) {
            preparedStatement.setString(1, detail.getId());
            preparedStatement.setString(2, detail.getItemId());
            preparedStatement.setString(3, detail.getDetail());
            preparedStatement.setLong(4, detail.getSequence());
            preparedStatement.setString(5, TcBaseConsts.STR_32_0);
            preparedStatement.setString(6, "2016-12-31 12:12:12");
            preparedStatement.setString(7, TcBaseConsts.STR_32_0);
            preparedStatement.setString(8, "2016-12-31 12:12:12");
            preparedStatement.execute();
        }
    }
}
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
/**
 * @description:return write connection
 * @date:2014-5-6 下午12:01:54
 * @version:v1.0
 * @return
 */
public DruidPooledConnection getConnection() {
	try
	{
		return ddsWrite.getConnection();
	}catch(Exception e)
	{
		e.printStackTrace();
	}
	return null;
}
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
/**
 * @description:return read connection
 * @date:2014-5-6 下午12:02:15
 * @version:v1.0
 * @return
 */
public DruidPooledConnection getReadConnection(){
	try
	{
		return ddsread.getConnection();
	}catch(Exception e)
	{
		e.printStackTrace();
	}
	return null;
}
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
@Override
    public List<SysMenu> getMenuBySys(String sys) throws SQLException {

        String sql_select_funcgrp = "SELECT * FROM af_funcgrp WHERE type = 'leftMenu' AND status = 1 AND sys = ? ORDER BY disporder";
        String sq_select_func = "SELECT * FROM af_func WHERE grp_name = ? AND status = 1 ORDER BY disporder";

        DruidPooledConnection connection = druidDataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(sql_select_funcgrp);
        statement.setString(1, sys);
        ResultSet resultSet = statement.executeQuery();
        List<SysMenu> sysMenuList = new ArrayList<>();
        while (resultSet.next()){
            SysMenu sysMenu = new SysMenu();
            sysMenu.setMenu_name(resultSet.getString("namec"));
            sysMenu.setHref(resultSet.getString("plugin"));
            sysMenu.setIcon(resultSet.getString("icon"));

            PreparedStatement statement1 = connection.prepareStatement(sq_select_func);
            statement1.setString(1, resultSet.getString("name"));
            ResultSet resultSet1 = statement1.executeQuery();
            List<SysMenu.ChildMenu> childMenuList = new ArrayList<>();
            int i = 0;
            while (resultSet1.next()){
                SysMenu.ChildMenu childMenu = sysMenu.new ChildMenu();
                childMenu.setMenu_name(resultSet1.getString("namec"));
                childMenu.setHref(resultSet1.getString("plugin"));
//                childMenu.setIcon(resultSet1.getString("icon"));
                childMenuList.add(childMenu);
                i++;
            }
            statement1.close();
            //resultSet1.close();
            sysMenu.setChild_num(i);
            sysMenu.setChildes(childMenuList);
            sysMenuList.add(sysMenu);
        }
        connection.close();
        resultSet.close();      //  只需关闭一次
        statement.close();
        return sysMenuList;
    }
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
@Override
    public List<VoMenu> getMenuByPage(int page, int limit, int level, String type, String sys) throws SQLException {
//        String sql_select_menu = "SELECT id, sys, name, state, disporder, type, level, parent_name";
//        String sql_select_menu = "SELECT id, sys, name, state, disporder, type, level, parent_name";
        String sql_select_menu = "SELECT * FROM ! WHERE type = ? AND sys = ? LIMIT " + (page - 1) * limit + "," + (page * limit);

        if (level == 1){
            sql_select_menu = sql_select_menu.replace("!", "af_funcgrp");
        }else if(level == 2){
            sql_select_menu = sql_select_menu.replace("!", "af_func");
        }
        DruidPooledConnection connection = druidDataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(sql_select_menu);
        statement.setString(1, type);
        statement.setString(2, sys);
        ResultSet resultSet = statement.executeQuery();
        List<VoMenu> voMenuList = new ArrayList<>();
        while(resultSet.next()){
            VoMenu voMenu = new VoMenu();
            voMenu.setId(resultSet.getInt("id"));
            voMenu.setDisporder(resultSet.getInt("disporder"));
            voMenu.setLevel(level + "级菜单");
            voMenu.setName(resultSet.getString("name"));
            voMenu.setNamee(resultSet.getString("namee"));
            voMenu.setNamec(resultSet.getString("namec"));
            voMenu.setSys(resultSet.getString("sys"));
            voMenu.setIcon(resultSet.getString("icon"));
            if (level == 1) {
                voMenu.setParent_name("");
            }
            else if (level == 2) {
                voMenu.setParent_name(resultSet.getString("grp_name"));
            }
            if (resultSet.getInt("status") == 0){
                voMenu.setStatus(BusinessConstants.SYS_MENU_STATUS_0.getMsg());
            }else if (resultSet.getInt("status") == 1){
                voMenu.setStatus(BusinessConstants.SYS_MENU_STATUS_1.getMsg());
            }
            voMenu.setType(type);
            voMenuList.add(voMenu);
        }

        resultSet.close();
        statement.close();
        connection.close();
        return voMenuList;
    }
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
@SneakyThrows
public static void doInsert(String shopName,
                            int shopDescRate,
                            int shopServiceRate,
                            int shopDeliveryRate,
                            int shopRate,
                            String itemTitle,
                            String itemDesc,
                            long itemPrice,
                            int itemStock,
                            List<String> itemAttr,
                            List<String> itemCovers,
                            List<String> itemDetails) {
    String shopId = findShopId(shopName);
    String itemId = TcIdGen.upperCaseUuid();
    boolean insertShop = Objects.isNull(shopId);
    shopId = TcIdGen.upperCaseUuid();
    TcCrawlerShop shop = null;
    if (insertShop) {
        shop = new TcCrawlerShop(shopId, shopName, shopDescRate, shopServiceRate,
                shopDeliveryRate, shopRate);
    }
    TcCrawlerItem item =
            new TcCrawlerItem(itemId, shopId, itemTitle, itemDesc, itemPrice, itemStock);
    List<TcCrawlerItemAttr> attrs = Lists.newArrayList();
    for (int i = 0; i < itemAttr.size(); i++) {
        attrs.add(new TcCrawlerItemAttr(
                TcIdGen.upperCaseUuid(),
                itemId,
                itemAttr.get(i).split(":")[0].trim(),
                itemAttr.get(i).split(":")[1].trim(),
                i));
    }
    List<TcCrawlerItemCover> covers = Lists.newArrayList();
    for (int i = 0; i < itemCovers.size(); i++) {
        covers.add(new TcCrawlerItemCover(
                TcIdGen.upperCaseUuid(),
                itemId,
                itemCovers.get(i).trim(),
                i));
    }
    List<TcCrawlerItemDetail> details = Lists.newArrayList();
    for (int i = 0; i < itemDetails.size(); i++) {
        details.add(new TcCrawlerItemDetail(
                TcIdGen.upperCaseUuid(),
                itemId,
                itemDetails.get(i).trim(),
                i));
    }

    DruidPooledConnection connection = DATA_SOURCE.getConnection();
    connection.setAutoCommit(false);
    try {
        if (!Objects.isNull(shop)) {
            insertShop(connection, shop);
        }
        insertItem(connection, item);
        insertItemAttr(connection, attrs);
        insertItemCover(connection, covers);
        insertItemDetail(connection, details);

        System.out.println("commit");
        connection.commit();
    } catch (Exception e) {
        connection.rollback();
        log.error("===> INSERT ERROR: {}", item.getId(), e);
    } finally {
        JdbcUtils.closeConnection(connection);
    }
}
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
public DruidConnect(DruidPooledConnection connection) {
    this.connection = connection;
}
 

import com.alibaba.druid.pool.DruidPooledConnection; //导入依赖的package包/类
public static String getEncoding(Connection conn) {
    if (conn instanceof IConnection) {
        return ((IConnection) conn).getEncoding();
    }

    if (conn instanceof TConnectionWrapper) {
        conn = ((TConnectionWrapper) conn).getTargetConnection();
        return getEncoding(conn);
    }

    if (conn instanceof DruidPooledConnection) {
        conn = ((DruidPooledConnection) conn).getConnection();
        return getEncoding(conn);
    }

    try {
        final Class<?> clazz = conn.getClass();
        Method getMethod = getMethodCaches.get(clazz, new Callable<Method>() {

            @Override
            public Method call() throws Exception {

                Class c = clazz;
                while (true) {
                    try {
                        Method setMethod = c.getDeclaredMethod("getEncoding", new Class[] {});
                        if (setMethod != null) {
                            setMethod.setAccessible(true);
                        }
                        return setMethod;
                    } catch (Exception ex) {
                    }

                    c = c.getSuperclass();

                    if (c == null) {
                        throw new TddlRuntimeException(ErrorCode.ERR_EXECUTOR, "get getEncoding error, clazz:"
                                                                               + clazz);
                    }
                }
            }
        });

        return (String) getMethod.invoke(conn, new Object[] {});
    } catch (Throwable e) {
        throw new TddlNestableRuntimeException(e);
    }
}