org.okinawaopenlabs.orientdb.client.DaoImpl.java Source code

Java tutorial

Introduction

Here is the source code for org.okinawaopenlabs.orientdb.client.DaoImpl.java

Source

/*
 *   Copyright 2015 Okinawa Open Laboratory, General Incorporated Association
 *
 *   Licensed under the Apache License, Version 2.0 (the "License");
 *   you may not use this file except in compliance with the License.
 *   You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *   Unless required by applicable law or agreed to in writing, software
 *   distributed under the License is distributed on an "AS IS" BASIS,
 *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *   See the License for the specific language governing permissions and
 *   limitations under the License.
 */

package org.okinawaopenlabs.orientdb.client;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.NumberUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import com.orientechnologies.orient.core.db.document.ODatabaseDocumentTx;
import com.orientechnologies.orient.core.record.impl.ODocument;

import static org.okinawaopenlabs.constants.ErrorMessage.*;
import static org.okinawaopenlabs.constants.OfpmDefinition.ENABLE_DEVICE_TYPES;
import static org.okinawaopenlabs.constants.OfpmDefinition.LEGACY_DEVICE_TYPES;
import static org.okinawaopenlabs.constants.OfpmDefinition.STATUS_NOTFOUND;
import static org.okinawaopenlabs.constants.OfpmDefinition.OPEN_FLOW_DEVICE_TYPES;
import static org.okinawaopenlabs.constants.OfpmDefinition.STATUS_NOW_USED;

import static org.okinawaopenlabs.constants.OrientDBDefinition.*;

import org.okinawaopenlabs.ofpm.exception.ValidateException;
import org.okinawaopenlabs.ofpm.json.device.DeviceInfo;
import org.okinawaopenlabs.ofpm.json.device.OfcInfo;
import org.okinawaopenlabs.ofpm.utils.OFPMUtils;
import org.okinawaopenlabs.orientdb.utils.handlers.MapListHandler;

public class DaoImpl implements Dao {

    private static final Logger logger = Logger.getLogger(DaoImpl.class);

    protected ConnectionUtils utils = null;
    protected ODatabaseDocumentTx database = null;
    protected List<ODocument> documents = null;

    // jdbc
    protected ConnectionUtilsJdbc utilsJdbc = null;

    public DaoImpl(ConnectionUtils utils) throws SQLException {
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("DaoImpl(utils=%s) - start", utils));
        }
        this.utils = utils;
        init();
        if (logger.isTraceEnabled()) {
            logger.trace("DaoImpl() - end");
        }
    }

    // jdbc
    public DaoImpl(ConnectionUtilsJdbc utils) {
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("DaoImpl(utils=%s) - start", utils));
        }
        this.utilsJdbc = utils;
        if (logger.isTraceEnabled()) {
            logger.trace("DaoImpl() - end");
        }
    }

    // default constructor
    public DaoImpl() {
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("DaoImpl() - start"));
        }
        if (logger.isTraceEnabled()) {
            logger.trace("DaoImpl() - end");
        }
    }

    // connectionUtil setter
    public void setConnectionUtilsJdbc(ConnectionUtilsJdbc utils) {
        this.utilsJdbc = utils;
    }

    synchronized private void init() throws SQLException {
        if (logger.isTraceEnabled()) {
            logger.trace("init() - start");
        }
        database = utils.getDatabase();
        if (logger.isTraceEnabled()) {
            logger.trace("init() - end");
        }
    }

    synchronized public void close() throws SQLException {
        if (logger.isTraceEnabled()) {
            logger.trace("close() - start");
        }
        if (database != null && !database.isClosed()) {
            utils.close(database);
        }
        if (logger.isTraceEnabled()) {
            logger.trace("close() - end");
        }
    }

    /* (non-Javadoc)
     * @see org.okinawaopenlabs.orientdb.client.Dao#getDeviceNameFromDatapathId(java.lang.String)
     */
    @Override
    public String getDeviceNameFromDatapathId(Connection conn, String datapathId) throws SQLException {
        final String fname = "getDeviceNameFromDatapathId";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, datapathId=%s) - start", fname, conn, datapathId));
        }
        String ret = null;
        try {
            //         List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_DEVICENAME_FROM_DATAPATHID,
            //                    new MapListHandler(), datapathId);
            //         if (records.size() <= 0) {
            //                throw new Exception(String.format(NOT_FOUND, datapathId));
            //         }
            //         ret = records.get(0).get("name").toString();
            //         return ret;
            List<Map<String, Object>> infoMapList = this.getNodeInfoList(conn);

            for (Map<String, Object> infoMap : infoMapList) {
                if (infoMap.containsKey("datapathId")) {
                    if (datapathId.contentEquals((String) infoMap.get("datapathId"))) {
                        return (String) infoMap.get("name");
                    }
                }
            }

            // not found.
            return null;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", ret));
            }
        }
    }

    @Override
    public String getPortRidFromDeviceNamePortNumber(Connection conn, String deviceName, Integer portNumber)
            throws SQLException {
        final String fname = "getPortRidFromDeviceNamePortNumber";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(deviceName=%s, portNumber=%s) - start", fname, deviceName, portNumber));
        }
        String ret = null;
        try {
            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_PORTRID_FROM_DEVICENAME_PORTNUMBER,
                    new MapListHandler(), deviceName, portNumber);
            if (records.size() <= 0) {
                throw new Exception(String.format(NOT_FOUND, deviceName + "," + portNumber));
            }
            ret = records.get(0).get("rid").toString();
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", ret));
            }
        }
    }

    @Override
    public List<Map<String, Map<String, Object>>> getDevicePortInfoSetFlowFromPortRid(Connection conn,
            String portRid) throws SQLException {
        final String fname = "getDevicePortInfoSetFlowFromPortRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(portRid=%s) - start", fname, portRid));
        }
        List<Map<String, Map<String, Object>>> ret = new ArrayList<Map<String, Map<String, Object>>>();
        try {
            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_PATCH_INFO_FROM_PATCHWIRING_PORTRID,
                    new MapListHandler(), portRid);
            if (records.size() <= 0) {
                throw new Exception(String.format(NOT_FOUND, portRid + ":wiring info"));
            }
            String inDeviceName = records.get(0).get("inDeviceName").toString();
            String inPortName = records.get(0).get("inPortName").toString();
            String outDeviceName = records.get(0).get("outDeviceName").toString();
            String outPortName = records.get(0).get("outPortName").toString();
            records = utilsJdbc.query(conn, SQL_GET_PATCH_INFO_FROM_PATCHWIRING_DEVICENAME_PORTNAME,
                    new MapListHandler("out", "in", "parent"), inDeviceName, inPortName, outDeviceName,
                    outPortName);
            if (records.size() != 3 && records.size() != 1) {
                throw new Exception(UNEXPECTED_ERROR);
            }

            Iterator<Map<String, Object>> it = records.iterator();
            while (it.hasNext()) {
                Map<String, Object> record = it.next();
                Map<String, Object> inPortInfo = getPortInfoFromPortRid(conn, record.get("in").toString());
                Map<String, Object> outPortInfo = getPortInfoFromPortRid(conn, record.get("out").toString());
                Map<String, Object> parentInfo = getDeviceInfoFromDeviceRid(conn, record.get("parent").toString());

                Map<String, Map<String, Object>> tmp = new HashMap<String, Map<String, Object>>();
                tmp.put("in", inPortInfo);
                tmp.put("out", outPortInfo);
                tmp.put("parent", parentInfo);
                ret.add(tmp);
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getCableLinksFromDeviceName(Connection conn, String deviceName)
            throws SQLException {
        final String fname = "getCableLinks";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
        }
        List<Map<String, Object>> maps = null;
        MapListHandler rhs = new MapListHandler("inDeviceName", "inPortName", "inPortNumber", "outDeviceName",
                "outPortName", "outPortNumber", "@rid.asString()", "band", "used");
        try {
            maps = utilsJdbc.query(conn, SQL_GET_CABLE_LINKS, rhs, deviceName);
            return maps;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, maps));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getPatchWiringsFromDeviceName(Connection conn, String deviceName)
            throws SQLException {
        final String fname = "getPatchWirings";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
        }
        List<Map<String, Object>> maps = null;
        MapListHandler rhs = new MapListHandler("inDeviceName", "inPortName", "inPortNumber", "outDeviceName",
                "outPortName", "outPortNumber", "@rid.asString()", "band", "used");
        try {
            maps = utilsJdbc.query(conn, SQL_GET_PATCH_WIRINGS_FROM_DEVICENAME, rhs, deviceName);
            return maps;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, maps));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getLogicalLinksFromDeviceName(Connection conn, String deviceName)
            throws SQLException {
        final String fname = "getLogicalLinksFromDeviceName";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
        }
        List<Map<String, Object>> maps = null;
        MapListHandler rhs = new MapListHandler("inDeviceName", "inPortName", "outDeviceName", "outPortName");
        try {
            maps = utilsJdbc.query(conn, SQL_GET_LOGICAL_LINKS_FROM_DEVICENAME, rhs, deviceName, deviceName);

            return maps;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, maps));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getPatchWiringsFromDeviceNamePortName(Connection conn, String deviceName,
            String portName) throws SQLException {
        final String fname = "getPatchWiringsFromDeviceNamePortName";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, devicename=%s, portName=%s) - start", fname, conn, deviceName,
                    portName));
        }
        List<Map<String, Object>> maps = null;
        try {
            MapListHandler rsh = new MapListHandler("in", "out", "parent", "inDeviceName", "inPortName",
                    "outDeviceName", "outPortName", "sequence");
            maps = utilsJdbc.query(conn, SQL_GET_PATCH_WIRINGS_FROM_DEVICENAME_PORTNAME, rsh, deviceName, portName);
            return maps;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, maps));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getPatchWiringsFromParentRid(Connection conn, String parentRid)
            throws SQLException {
        final String fname = "getPatchWiringsFromParentRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, parentRid=%s) - start", fname, conn, parentRid));
        }
        List<Map<String, Object>> ret = null;
        try {
            ret = utilsJdbc.query(conn, SQL_GET_PATCH_WIRINGS_FROM_PARENTRID, new MapListHandler("in", "out",
                    "parent", "inDeviceName", "inPortName", "outDeviceName", "outPortName", "sequence"), parentRid);
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", ret));
            }
        }
    }

    @Override
    public boolean isContainsPatchWiringFromDeviceNamePortName(Connection conn, String deviceName, String portName)
            throws SQLException {
        final String fname = "isContainsPatchWiringFromDeviceNamePortName";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, devicename=%s, portName=%s) - start", fname, conn, deviceName,
                    portName));
        }
        boolean ret = true;
        try {
            MapListHandler rsh = new MapListHandler("in", "out", "parent", "inDeviceName", "inPortName",
                    "outDeviceName", "outPortName");
            List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_PATCH_WIRINGS_FROM_DEVICENAME_PORTNAME,
                    rsh, deviceName, portName);
            if (maps == null || maps.isEmpty()) {
                ret = false;
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public boolean isDeviceNameContainedIntoPatchWiring(Connection conn, String deviceName) throws SQLException {
        final String fname = "isDeviceNameContainedIntoPatchWiring";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
        }
        boolean ret = true;
        try {
            MapListHandler rsh = new MapListHandler("parent");
            List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_PATCH_WIRINGS_FROM_DEVICENAME, rsh,
                    deviceName);
            if (maps == null || maps.isEmpty()) {
                ret = false;
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public boolean isNodeRidContainedIntoPatchWiring(Connection conn, String nodeRid) throws SQLException {
        final String fname = "isNodeRidContainedIntoPatchWiring";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, nodeRid=%s) - start", fname, conn, nodeRid));
        }
        boolean ret = true;
        try {
            MapListHandler rsh = new MapListHandler("parent");
            List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_PATCH_WIRINGS_FROM_NODERID, rsh,
                    nodeRid);
            if (maps == null || maps.isEmpty()) {
                ret = false;
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public boolean isPortRidContainedIntoPatchWiring(Connection conn, String portRid) throws SQLException {
        final String fname = "isPortRidContainedIntoPatchWiring";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, portRid=%s) - start", fname, conn, portRid));
        }
        boolean ret = true;
        try {
            MapListHandler rsh = new MapListHandler("parent");
            List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_PATCH_WIRINGS_FROM_PORTRID, rsh, portRid,
                    portRid);
            if (maps == null || maps.isEmpty()) {
                ret = false;
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int deletePatchWiring(Connection conn, String deviceName, String portName) throws SQLException {
        final String fname = "deletePatchWiring";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, devicename=%s, portName=%s) - start", fname, conn, deviceName,
                    portName));
        }
        int ret = 0;
        try {
            Object[] params = { deviceName, portName, deviceName, portName };
            ret = utilsJdbc.update(conn, SQL_DELETE_PATCH_WIRING_FROM_DEVICE_NAME_PORT_NAME, params);
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
        return ret;
    }

    @Override
    public Map<String, Object> getCableLinkFromInPortRid(Connection conn, String inPortRid) throws SQLException {
        final String fname = "getCableLinkFromInPortRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, inPortRid=%s) - start", fname, conn, inPortRid));
        }
        Map<String, Object> ret = null;
        try {
            List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_CABLE_FROM_IN_PORTRID,
                    new MapListHandler(), inPortRid);
            if (!maps.isEmpty()) {
                ret = maps.get(0);
            }
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
        return ret;
    }

    @Override
    public Map<String, Object> getCableLinkFromOutPortRid(Connection conn, String outPortRid) throws SQLException {
        final String fname = "getCableLinkFromOutPortRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, outPortRid=%s) - start", fname, conn, outPortRid));
        }
        Map<String, Object> ret = null;
        try {
            List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_CABLE_FROM_OUT_PORTRID,
                    new MapListHandler(), outPortRid);
            if (!maps.isEmpty()) {
                ret = maps.get(0);
            }
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
        return ret;
    }

    @Override
    public int createCableLink(Connection conn, String deviceName0, String portName0, String deviceName1,
            String portName1) throws SQLException {
        final String fname = "createCableLink";
        if (logger.isTraceEnabled()) {
            logger.trace(
                    String.format("%s(conn=%s, deviceName0=%s, portName0=%s, deviceName1=%s, portName1=%s) - start",
                            fname, conn, deviceName0, portName0, deviceName1, portName1));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            String port0Rid = this.getPortRidFromDeviceNamePortName(conn, deviceName0, portName0);
            if (StringUtils.isBlank(port0Rid)) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }
            String port1Rid = this.getPortRidFromDeviceNamePortName(conn, deviceName1, portName1);
            if (StringUtils.isBlank(port1Rid)) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }

            String sql = SQL_INSERT_CABLE;
            sql = sql.replaceFirst("\\?", port0Rid);
            sql = sql.replaceFirst("\\?", port1Rid);
            int result = utilsJdbc.update(conn, sql);
            if (result != 1) {
                ret = DB_RESPONSE_STATUS_EXIST;
                return ret;
            }

            sql = SQL_INSERT_CABLE;
            sql = sql.replaceFirst("\\?", port1Rid);
            sql = sql.replaceFirst("\\?", port0Rid);
            result = utilsJdbc.update(conn, sql);
            if (result != 1) {
                ret = DB_RESPONSE_STATUS_EXIST;
                return ret;
            }

            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int deleteCableLink(Connection conn, String deviceName0, String portName0, String deviceName1,
            String portName1) throws SQLException {
        final String fname = "deleteCableLink";
        if (logger.isTraceEnabled()) {
            logger.trace(
                    String.format("%s(conn=%s, deviceName0=%s, portName0=%s, deviceName1=%s, portName1=%s) - start",
                            fname, conn, deviceName0, portName0, deviceName1, portName1));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            boolean contain = false;
            // TODO
            //         contain = this.isContainsPatchWiringFromDeviceNamePortName(conn, deviceName0, portName0);
            //         if (contain) {
            //            ret = DB_RESPONSE_STATUS_USED;
            //            return ret;
            //         }

            // TODO
            //         contain = this.isContainsPatchWiringFromDeviceNamePortName(conn, deviceName1, portName1);
            //         if (contain) {
            //            ret = DB_RESPONSE_STATUS_USED;
            //            return ret;
            //         }

            String port0Rid = this.getPortRidFromDeviceNamePortName(conn, deviceName0, portName0);
            if (StringUtils.isBlank(port0Rid)) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }
            String port1Rid = this.getPortRidFromDeviceNamePortName(conn, deviceName1, portName1);
            if (StringUtils.isBlank(port1Rid)) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }

            // TODO
            //         contain = this.isPortRidContainedIntoPatchWiring(conn, port0Rid);
            //         if (contain) {
            //            ret = DB_RESPONSE_STATUS_USED;
            //            return ret;
            //         }

            // TODO
            //         contain = this.isPortRidContainedIntoPatchWiring(conn, port1Rid);
            //         if (contain) {
            //            ret = DB_RESPONSE_STATUS_USED;
            //            return ret;
            //         }

            String sql = SQL_DELETE_CABLE_FROM_ONE_PORTRID;
            sql = sql.replaceFirst("\\?", port0Rid);
            sql = sql.replaceFirst("\\?", port1Rid);
            int result = utilsJdbc.update(conn, sql);
            if (result == 0) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }
            if (result != 1) {
                ret = DB_RESPONSE_STATUS_FAIL;
                return ret;
            }

            sql = SQL_DELETE_CABLE_FROM_ONE_PORTRID;
            sql = sql.replaceFirst("\\?", port1Rid);
            sql = sql.replaceFirst("\\?", port0Rid);
            result = utilsJdbc.update(conn, sql);
            if (result == 0) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }
            if (result != 1) {
                ret = DB_RESPONSE_STATUS_FAIL;
                return ret;
            }

            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public void updateCableLinkUsedFromPortRid(Connection conn, String portRid, long newUsed) throws SQLException {
        final String fname = "updateCableLinkUsedFromPortRid";
        if (logger.isTraceEnabled()) {
            logger.trace(
                    String.format("%s(conn=%s, portRid=%s, newUsed=%s) - start", fname, conn, portRid, newUsed));
        }
        try {
            Object[] params = { newUsed, portRid, portRid };
            int result = utilsJdbc.update(conn, SQL_UPDATE_CALBE_LINK_USED_VALUE_FROM_PORT_RID, params);
            if (result != 4 && result != 2) {
                throw new SQLException(String.format(INVALID_NUMBER_OF, "link(represent cable)"));
            }
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        }
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s() - end", fname));
        }
    }

    @Override
    public List<Map<String, Object>> getShortestPath(Connection conn, String ridA, String ridZ)
            throws SQLException {
        final String fname = "getShortestPath";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, ridA=%s, ridZ=%s) - start", fname, conn, ridA, ridZ));
        }
        List<Map<String, Object>> ret = null;
        try {
            String sql = SQL_GET_DIJKSTRA_PATH_FLATTEN;
            sql = sql.replaceFirst("\\?", ridA);
            sql = sql.replaceFirst("\\?", ridZ);
            MapListHandler rsh = new MapListHandler("rid", "name", "number", "node_name", "class");
            ret = utilsJdbc.query(conn, sql, rsh);

            for (Map<String, Object> current : ret) {
                String deviceName = "";
                if (StringUtils.equals((String) current.get("class"), "node")) {
                    deviceName = (String) current.get("name");
                } else {
                    deviceName = (String) current.get("node_name");
                }
                Map<String, Object> nodeMap = this.getNodeInfoFromDeviceName(conn, deviceName);
                current.put("type", (String) nodeMap.get("type"));
            }
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
        return ret;
    }

    @Override
    public int insertPatchWiring(Connection conn, String ofpRid, String in, String out, String inDeviceName,
            String inPortName, String outDeviceName, String outPortName, int sequence) throws SQLException {
        final String fname = "insertPatchWiring";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format(
                    "%s(conn=%s, ofpRid=%s, in=%s, out=%s, inDeviceName=%s, inPortName=%s, outDeviceName=%s, outPortName=%s, sequence=%s) - start",
                    fname, conn, ofpRid, in, out, inDeviceName, inPortName, outDeviceName, outPortName, sequence));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            Object[] forwardParams = { in, out, ofpRid, inDeviceName, inPortName, outDeviceName, outPortName,
                    sequence };
            int result = utilsJdbc.update(conn, SQL_INSERT_PATCH_WIRING_2, forwardParams);
            if (result != 1) {
                throw new SQLException(String.format(PATCH_INSERT_FAILD, inDeviceName, inPortName, outDeviceName,
                        outPortName, ofpRid, in, out));
            }
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
        return ret;
    }

    @Override
    public int insertLogicalLink(Connection conn, String in_node_id, String in_node_name, String in_port_id,
            String in_port_name, String out_node_id, String out_node_name, String out_port_id, String out_port_name,
            Long nw_instance_id, String nw_instance_type) throws SQLException {
        final String fname = "insertLogicalLink";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format(
                    "%s(conn=%s, in_node_id=%s, in_node_name=%s, in_port_id=%s, in_port_name=%s, out_node_id=%s, out_node_name=%s, out_port_id=%s, out_port_name=%s, nw_instance_id=%s, nw_instance_type=%s) - start",
                    fname, conn, in_node_id, in_node_name, in_port_id, in_port_name, out_node_id, out_node_name,
                    out_port_id, out_port_name, nw_instance_id, nw_instance_type));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            Object[] forwardParams = { in_node_id, in_node_name, in_port_id, in_port_name, out_node_id,
                    out_node_name, out_port_id, out_port_name, nw_instance_id, nw_instance_type };
            int result = utilsJdbc.update(conn, SQL_INSERT_LOGICAL_LINK, forwardParams);
            if (result != 1) {
                throw new SQLException(String.format(PATCH_INSERT_FAILD, in_node_id, in_node_name, in_port_id,
                        in_port_name, out_node_id, out_node_name, out_port_id, out_port_name, nw_instance_id,
                        nw_instance_type));
            }
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
        return ret;
    }

    @Override
    public Long getNwInstanceId(Connection conn) throws SQLException {
        final String fname = "getNwInstanceId";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s) - start", fname, conn));
        }
        List<String> ret = null;
        try {
            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_MAX_NW_INSTANCE_ID,
                    new MapListHandler());
            Long maxNwInstanceId = MIN_NETWORK_INSTANCE_ID;
            if (records.size() > 0) {
                maxNwInstanceId = (Long) Long.parseLong((records.get(0).get("maxNwInstanceId").toString())) + 1;
                if (maxNwInstanceId > MAX_NETWORK_INSTANCE_ID) {
                    records = utilsJdbc.query(conn, SQL_GET_NW_INSTANCE_ID, new MapListHandler());
                    Long count = MIN_NETWORK_INSTANCE_ID;
                    for (Map<String, Object> record : records) {
                        Long curId = (Long) record.get("maxNwInstanceId");
                        if (curId != count) {
                            // found.
                            return count;
                        }
                        count++;
                    }

                    // not found.
                    return -1L;
                }
            }

            return maxNwInstanceId;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", ret));
            }
        }
    }

    @Override
    public Map<String, Object> getNodeInfoFromDeviceName(Connection conn, String deviceName) throws SQLException {
        final String fname = "getNodeInfoFromDeviceName";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
        }
        Map<String, Object> map = null;
        try {
            List<Map<String, Object>> infoMapList = this.getNodeInfoList(conn);

            for (Map<String, Object> infoMap : infoMapList) {
                if (deviceName.contentEquals((String) infoMap.get("name"))) {
                    return infoMap;
                }
            }

            // not found.
            return null;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, map));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getNodeInfoList(Connection conn) throws SQLException {
        final String fname = "getNodeInfoList";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s) - start", fname, conn));
        }
        List<Map<String, Object>> maps = null;
        try {
            maps = utilsJdbc.query(conn, SQL_GET_NODE_INFO_LIST, new MapListHandler());

            List<Map<String, Object>> ofsMaps = utilsJdbc.query(conn, SQL_GET_OFS_INFO_LIST, new MapListHandler());
            for (Map<String, Object> infoMap : maps) {
                for (Map<String, Object> ofsMap : ofsMaps) {
                    String rid = (String) infoMap.get("rid");
                    if (rid.equals((String) ofsMap.get("node_id"))) {
                        infoMap.put("sw_instance_id", ofsMap.get("rid"));
                        infoMap.put("datapathId", (String) ofsMap.get("datapathId"));
                        infoMap.put("ip", (String) ofsMap.get("ip"));
                        infoMap.put("port", (Integer) ofsMap.get("port"));
                        Integer port = (Integer) ofsMap.get("port");
                        infoMap.put("ofcIp", (String) ofsMap.get("ip") + ":" + port.toString());
                    }
                }
            }

            return maps;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, maps));
            }
        }
    }

    @Override
    public Map<String, Object> getNodeInfoFromDeviceRid(Connection conn, String nodeRid) throws SQLException {
        final String fname = "getDeviceInfoFromDeviceRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, nodeRid=%s) - start", fname, conn, nodeRid));
        }
        Map<String, Object> map = null;
        try {
            List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_NODE_INFO_FROM_DEVICE_RID,
                    new MapListHandler(), nodeRid);
            if (!maps.isEmpty()) {
                map = maps.get(0);
            }
            return map;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, map));
            }
        }
    }

    @Override
    public String getNodeRidFromDeviceName(Connection conn, String deviceName) throws SQLException {
        final String fname = "getNodeRidFromDeviceName";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
        }
        String ret = null;
        try {
            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_NODE_RID_FROM_DEVICENAME,
                    new MapListHandler("rid"), deviceName);
            if (records != null && !records.isEmpty() && records.get(0) != null) {
                ret = (String) records.get(0).get("rid");
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int createNodeInfo(Connection conn, String deviceName, String deviceType, String location, String tenant,
            String datapathId, String ofcIp) throws SQLException {
        final String fname = "createNodeInfo";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format(
                    "%s(conn=%s, deviceName=%s, deviceType=%s, location=%s, tenant=%s, datapathId=%s, ofcIp=%s) - start",
                    fname, conn, deviceName, deviceType, location, tenant, datapathId, ofcIp));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            Map<String, Object> current = this.getNodeInfoFromDeviceName(conn, deviceName);
            if (current != null) {
                return DB_RESPONSE_STATUS_EXIST;
            }

            Object[] params = { deviceName, location, deviceType, tenant };
            int nRecords = utilsJdbc.update(conn, SQL_INSERT_NODE_INFO, params);
            if (nRecords == 0) {
                return DB_RESPONSE_STATUS_EXIST;
            }

            if (ArrayUtils.contains(SYSTEM_RESOURCE_TYPES, deviceType)) {
                String nodeRid = this.getNodeRidFromDeviceName(conn, deviceName);
                if (StringUtils.isBlank(nodeRid)) {
                    return DB_RESPONSE_STATUS_NOT_FOUND;
                }

                ret = this.createOfsInfo(conn, nodeRid, datapathId, ofcIp);
            }

            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    public String getResourceRidFromNodeRid(Connection conn, String nodeRid, String deviceType)
            throws SQLException {
        final String fname = "getResourceRidFromNodeRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, nodeRid=%s, deviceType=%s) - start", fname, conn, nodeRid,
                    deviceType));
        }
        String ret = null;

        try {
            String sql = null;
            if (ArrayUtils.contains(SYSTEM_RESOURCE_TYPES, deviceType)) {
                sql = SQL_GET_STSTEM_RESOURCE_RID_FROM_NODE_RID;
            } else {
                sql = SQL_GET_RENT_RESOURCE_RID_FROM_NODE_RID;
            }

            List<Map<String, Object>> records = utilsJdbc.query(conn, sql, new MapListHandler("rid"), nodeRid);
            if (records != null && !records.isEmpty() && records.get(0) != null) {
                ret = (String) records.get(0).get("rid");
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    public int createResourceInfo(Connection conn, String deviceName, String deviceType, String datapathId,
            String tenant, String ofcIp) throws SQLException {
        final String fname = "createResourceInfo";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format(
                    "%s(conn=%s, deviceName=%s, deviceType=%s, tenant=%s, datapathId=%s, ofcIp=%s) - start", fname,
                    conn, deviceName, deviceType, tenant, datapathId, ofcIp));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            String nodeRid = this.getNodeRidFromDeviceName(conn, deviceName);
            if (StringUtils.isBlank(nodeRid)) {
                return DB_RESPONSE_STATUS_NOT_FOUND;
            }

            String sql = null;
            if (ArrayUtils.contains(SYSTEM_RESOURCE_TYPES, deviceType)) {
                sql = SQL_INSERT_SYSTEM_RESOURCE_INFO;
            } else {
                sql = SQL_INSERT_RENT_RESOURCE_INFO;
            }
            //         Object[] params = {nodeRid, deviceType, tenant};
            //         int nRecords = utilsJdbc.update(conn, sql, params);
            sql = sql.replaceFirst("\\?", nodeRid);
            sql = sql.replaceFirst("\\?", deviceType);
            sql = sql.replaceFirst("\\?", tenant);
            int nRecords = utilsJdbc.update(conn, sql);
            if (nRecords == 0) {
                return DB_RESPONSE_STATUS_EXIST;
            }

            if (ArrayUtils.contains(SYSTEM_RESOURCE_TYPES, deviceType)) {
                String resourceRid = this.getResourceRidFromNodeRid(conn, nodeRid, deviceType);
                ret = this.createOfsInfo(conn, resourceRid, datapathId, ofcIp);
            }

            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    public int createOfsInfo(Connection conn, String nodeRid, String datapathId, String ofcIp) throws SQLException {
        final String fname = "createOfsInfo";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, nodeRid=%s, datapathId=%s, ofcIp=%s) - start", fname, conn,
                    nodeRid, datapathId, ofcIp));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            if (!StringUtils.isBlank(datapathId) && !StringUtils.isBlank(ofcIp)) {
                String ofcRid = this.getOfcRid(conn, ofcIp);
                if (StringUtils.isBlank(ofcRid)) {
                    return DB_RESPONSE_STATUS_NOT_FOUND;
                }
                //            Object[] params = {datapathId, nodeRid, ofcRid};
                //            int nRecords = utilsJdbc.update(conn, SQL_INSERT_OFS_INFO, params);
                String sql = SQL_INSERT_OFS_INFO;
                sql = sql.replaceFirst("\\?", datapathId);
                sql = sql.replaceFirst("\\?", nodeRid);
                sql = sql.replaceFirst("\\?", ofcRid);
                int nRecords = utilsJdbc.update(conn, sql);

                if (nRecords == 0) {
                    return DB_RESPONSE_STATUS_EXIST;
                }

                String ofsRid = this.getOfsRid(conn, nodeRid);
                if (StringUtils.isBlank(ofsRid)) {
                    return DB_RESPONSE_STATUS_NOT_FOUND;
                }

                sql = SQL_UPDATE_NODE_SW_INSTANCE_INFO_FROM_RID;
                sql = sql.replaceFirst("\\?", "OpenFlowSwitch");
                sql = sql.replaceFirst("\\?", ofsRid);
                sql = sql.replaceFirst("\\?", nodeRid);
                int result = utilsJdbc.update(conn, sql);
                if (result == 0) {
                    ret = DB_RESPONSE_STATUS_EXIST;
                    return ret;
                }
            }

            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    public String getOfsRid(Connection conn, String nodeRid) throws SQLException {
        final String fname = "getOfsRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, nodeRid=%s) - start", fname, conn, nodeRid));
        }

        String ret = null;
        try {
            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_OFS_RID_FROM_NODE_ID,
                    new MapListHandler("rid"), nodeRid);
            if (records != null && !records.isEmpty() && records.get(0) != null) {
                ret = (String) records.get(0).get("rid");
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public String getOfcRid(Connection conn, String ofcIp) throws SQLException {
        final String fname = "getOfcRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, ofcIp=%s) - start", fname, conn, ofcIp));
        }

        String ret = null;
        try {
            String[] ofc = ofcIp.split(":", 0);
            String ip = ofc[0];
            if (!this.isNum(ofc[1])) {
                return null;
            }
            int port = Integer.parseInt(ofc[1]);

            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_OFC_RID_FROM_IP_AND_PORT,
                    new MapListHandler("rid"), ip, port);
            if (records != null && !records.isEmpty() && records.get(0) != null) {
                ret = (String) records.get(0).get("rid");
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    public boolean isNum(String number) {
        try {
            Integer.parseInt(number);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }

    @Override
    public int updateNodeInfo(Connection conn, String keyDeviceName, String deviceName, String location,
            String tenant, String datapathId, String ofcIp, String type) throws SQLException {
        final String fname = "updateNodeInfo";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format(
                    "%s(conn=%s, keyDeviceName=%s, newDeviceName=%s, datapathId=%s, ofcIp=%s) - start", fname, conn,
                    keyDeviceName, deviceName, datapathId, ofcIp));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            Map<String, Object> current = this.getNodeInfoFromDeviceName(conn, keyDeviceName);
            if (current == null) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }

            String nodeRid = (String) current.get("rid");
            if (StringUtils.isBlank(deviceName)) {
                deviceName = (String) current.get("name");
            }

            if (StringUtils.isBlank(type)) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }

            if (!type.equals((String) current.get("type"))) {
                ret = DB_RESPONSE_STATUS_INVALID_ERR;
                return ret;
            }

            if (StringUtils.isBlank(location)) {
                location = (String) current.get("location");
            }
            if (StringUtils.isBlank(tenant)) {
                tenant = (String) current.get("tenant");
            }

            if (!ArrayUtils.contains(LEGACY_DEVICE_TYPES, type)) {
                if (StringUtils.isBlank(datapathId)) {
                    datapathId = (String) current.get("datapathId");
                }
            }

            String sql = SQL_UPDATE_NODE_INFO_FROM_RID;
            sql = sql.replaceFirst("\\?", deviceName);
            sql = sql.replaceFirst("\\?", location);
            sql = sql.replaceFirst("\\?", tenant);
            sql = sql.replaceFirst("\\?", nodeRid);
            int result = utilsJdbc.update(conn, sql);
            if (result == 0) {
                ret = DB_RESPONSE_STATUS_EXIST;
                return ret;
            }

            if (ArrayUtils.contains(SYSTEM_RESOURCE_TYPES, (String) current.get("type"))) {
                if (StringUtils.isBlank(ofcIp)) {
                    ofcIp = (String) current.get("ofcIp");
                }
                String ofcRid = this.getOfcRid(conn, ofcIp);
                if (StringUtils.isBlank(ofcRid)) {
                    ret = DB_RESPONSE_STATUS_NOT_FOUND;
                    return ret;
                }

                sql = SQL_UPDATE_OFS_INFO_FROM_RID;
                sql = sql.replaceFirst("\\?", datapathId);
                sql = sql.replaceFirst("\\?", ofcRid);
                sql = sql.replaceFirst("\\?", (String) current.get("sw_instance_id"));
                result = utilsJdbc.update(conn, sql);
                if (result == 0) {
                    ret = DB_RESPONSE_STATUS_EXIST;
                    return ret;
                }
            }

            //TODO
            //         Object[] updDevNamePara = {deviceName, keyDeviceName};
            //         utilsJdbc.update(conn, SQL_UPDATE_PORT_DEVICENAME, updDevNamePara);
            //         utilsJdbc.update(conn, SQL_UPDATE_PATCH_WIRING_INDEVICENAME,  updDevNamePara);
            //         utilsJdbc.update(conn, SQL_UPDATE_PATCH_WIRING_OUTDEVICENAME, updDevNamePara);

            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int deleteNodeInfo(Connection conn, String deviceName) throws SQLException {
        final String fname = "deleteNodeInfo";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            Map<String, Object> deviceMap = this.getNodeInfoFromDeviceName(conn, deviceName);
            if (deviceMap == null) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }

            String nodeRid = (String) deviceMap.get("rid");

            // TODO
            //         boolean contain = this.isDeviceNameContainedIntoPatchWiring(conn, deviceName);
            //         if (contain) {
            //            ret = DB_RESPONSE_STATUS_FORBIDDEN;
            //            return ret;
            //         }

            // TODO
            //         contain = this.isNodeRidContainedIntoPatchWiring(conn, nodeRid);
            //         if (contain) {
            //            ret = DB_RESPONSE_STATUS_FORBIDDEN;
            //            return ret;
            //         }

            Object[] params = { deviceName };
            utilsJdbc.update(conn, SQL_DELETE_PORT_FROM_DEVICENAME, params);

            if (ArrayUtils.contains(SYSTEM_RESOURCE_TYPES, (String) deviceMap.get("type"))) {
                String sql = SQL_DELETE_OFS_FROM_RID;
                sql = sql.replaceFirst("\\?", (String) deviceMap.get("sw_instance_id"));
                int nRecord = utilsJdbc.update(conn, sql);
                if (nRecord != 1) {
                    ret = DB_RESPONSE_STATUS_FAIL;
                    return ret;
                }
            }

            String sql = SQL_DELETE_NODE_FROM_NODERID;
            sql = sql.replaceFirst("\\?", nodeRid);
            int nRecord = utilsJdbc.update(conn, sql);
            if (nRecord != 1) {
                ret = DB_RESPONSE_STATUS_FAIL;
                return ret;
            }

            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int createPortInfo(Connection conn, String portName, Integer portNumber, Integer band, String deviceName,
            String network) throws SQLException {
        final String fname = "createPortInfo";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, portName=%s, portNumber=%s, band=%s, deviceName=%s) - start",
                    fname, conn, portName, portNumber, band, deviceName));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            /* PHASE 1: Insert port info */
            Map<String, Object> devMap = this.getNodeInfoFromDeviceName(conn, deviceName);

            if (devMap == null) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }

            if (StringUtils.equals((String) devMap.get("type"), NODE_TYPE_SERVER)) {
                portNumber = null;
            }

            Map<String, Object> current = this.getPortInfoFromPortName(conn, deviceName, portName);
            if (current != null) {
                ret = DB_RESPONSE_STATUS_EXIST;
                return ret;
            }
            if (network == null) {
                network = (String) current.get("");
            }
            Object[] params = { portName, portNumber, band, deviceName, network };
            int nRecords = utilsJdbc.update(conn, SQL_INSERT_PORT_INFO, params);
            if (nRecords == 0) {
                ret = DB_RESPONSE_STATUS_EXIST;
                return ret;
            }

            /* PHASE 2: Insert Bus-link */
            String devRid = (String) devMap.get("rid");
            String devType = (String) devMap.get("type");
            String portRid = this.getPortRidFromDeviceNamePortName(conn, deviceName, portName);
            if (StringUtils.isBlank(portRid)) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }

            /* Here, decide Used default value of bus of Node. */
            Long used = USED_BLOCKING_VALUE;
            if (ArrayUtils.contains(BIG_USED_BUS_TYPES, devType)) {
                used = BIG_USED_BUS_VALUE;
            } else if (StringUtils.equals(devType, NODE_TYPE_LEAF)) {
                used = 0L;
            }

            String sql = SQL_INSERT_BUS;
            sql = sql.replaceFirst("\\?", portRid);
            sql = sql.replaceFirst("\\?", devRid);
            sql = sql.replaceFirst("\\?", used.toString());
            nRecords = utilsJdbc.update(conn, sql);
            if (nRecords == 0) {
                ret = DB_RESPONSE_STATUS_EXIST;
                return ret;
            }

            sql = SQL_INSERT_BUS;
            sql = sql.replaceFirst("\\?", devRid);
            sql = sql.replaceFirst("\\?", portRid);
            sql = sql.replaceFirst("\\?", used.toString());
            nRecords = utilsJdbc.update(conn, sql);
            if (nRecords == 0) {
                ret = DB_RESPONSE_STATUS_EXIST;
                return ret;
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int updatePortInfo(Connection conn, String keyPortName, String keyDeviceName, String portName,
            Integer portNumber, Integer band, String network) throws SQLException {
        final String fname = "updatePortInfo";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format(
                    "%s(conn=%s, keyPortName=%s, keyDeviceName=%s, portName=%s, portNumber=%s, band=%s) - start",
                    fname, conn, keyPortName, keyDeviceName, portName, portNumber, band));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            Map<String, Object> current = this.getPortInfoFromPortName(conn, keyDeviceName, keyPortName);
            if (current == null) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                if (logger.isTraceEnabled()) {
                    logger.trace(String.format("%s(ret=%s) - end", fname, ret));
                }
                return ret;
            }

            String portRid = (String) current.get("rid");
            if (StringUtils.isBlank(portName)) {
                portName = (String) current.get("name");
            }
            if (portNumber == null) {
                portNumber = (Integer) current.get("portNumber");
            }
            if (band == null) {
                band = (Integer) current.get("band");
            }
            if (network == null) {
                network = (String) current.get("network");
            }

            Object[] params = { portName, portNumber, band, network, portRid };
            int result = utilsJdbc.update(conn, SQL_UPDATE_PORT_INFO_FROM_RID, params);
            if (result == 0) {
                ret = DB_RESPONSE_STATUS_EXIST;
                if (logger.isTraceEnabled()) {
                    logger.trace(String.format("%s(ret=%s) - end", fname, ret));
                }
                return ret;
            }

            // TODO
            //         Object[] updPortNamePara = {portName, keyPortName, keyDeviceName};
            //         utilsJdbc.update(conn, SQL_UPDATE_PATCH_WIRING_INPORTNAME, updPortNamePara);
            //         utilsJdbc.update(conn, SQL_UPDATE_PATCH_WIRING_OUTPORTNAME, updPortNamePara);
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
        return ret;
    }

    @Override
    public int deletePortInfo(Connection conn, String portName, String deviceName) throws SQLException {
        final String fname = "deletePortInfo";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, portName=%s, deviceName=%s) - start", fname, conn, portName,
                    deviceName));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            String portRid = this.getPortRidFromDeviceNamePortName(conn, deviceName, portName);
            if (StringUtils.isBlank(portRid)) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }

            // TODO
            //         boolean contain = this.isContainsPatchWiringFromDeviceNamePortName(conn, deviceName, portName);
            //         if (contain) {
            //            ret = DB_RESPONSE_STATUS_FORBIDDEN;
            //            return ret;
            //         }

            // TODO
            //         contain = this.isPortRidContainedIntoPatchWiring(conn, portRid);
            //         if (contain) {
            //            ret = DB_RESPONSE_STATUS_FORBIDDEN;
            //            return ret;
            //         }

            String sql = SQL_DELETE_PORT_FROM_PORTRID;
            sql = sql.replaceFirst("\\?", portRid);
            int nRecord = utilsJdbc.update(conn, sql);
            if (nRecord != 1) {
                ret = DB_RESPONSE_STATUS_FAIL;
                return ret;
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public Map<String, Object> getPortInfoFromPortName(Connection conn, String deviceName, String portName)
            throws SQLException {
        final String fname = "getPortInfoFromPortName";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, deviceName=%s, portName=%s) - start", fname, conn, deviceName,
                    portName));
        }
        Map<String, Object> map = null;
        try {
            List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_PORT_INFO_FROM_PORT_NAME,
                    new MapListHandler(), portName, deviceName);
            if (!maps.isEmpty()) {
                map = maps.get(0);
            }
            return map;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, map));
            }
        }
    }

    @Override
    public Map<String, Object> getPortInfoFromPortRid(Connection conn, String rid) throws SQLException {
        final String fname = "getPortInfoFromPortRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(rid=%s) - start", fname, rid));
        }
        Map<String, Object> ret = null;
        try {
            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_PORT_INFO_FROM_PORTRID,
                    new MapListHandler(), rid);
            if (records != null && !records.isEmpty()) {
                ret = records.get(0);
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("getPortInfo(ret=%s) - end", ret));
            }
        }
    }

    @Override
    public String getPortRidFromDeviceNamePortName(Connection conn, String deviceName, String portName)
            throws SQLException {
        final String fname = "getPortRidFromDeviceNamePortName";
        if (logger.isTraceEnabled()) {
            logger.trace(
                    String.format("%s(conn=%s, deviceName=%s, portName=%s) - start", fname, deviceName, portName));
        }
        String ret = null;
        try {
            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_PORT_RID_FROM_DEVICENAME_PORTNAME,
                    new MapListHandler("rid"), portName, deviceName);
            if (records != null && !records.isEmpty() && records.get(0) != null) {
                ret = (String) records.get(0).get("rid");
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getPortInfoListFromDeviceName(Connection conn, String deviceName)
            throws SQLException {
        final String fname = "getPortInfoListFromDeviceName";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, deviceName));
        }
        List<Map<String, Object>> ret = null;
        try {
            ret = utilsJdbc.query(conn, SQL_GET_PORT_INFO_FROM_DEVICENAME, new MapListHandler(), deviceName);
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public Map<String, Object> getNeighborPortFromPortRid(Connection conn, String portRid) throws SQLException {
        final String fname = "getNeighborPortFromPortRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, portRid=%s) - start", fname, portRid));
        }
        Map<String, Object> ret = null;
        try {
            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_NEIGHBOR_PORT_INFO_FROM_PORT_RID,
                    new MapListHandler(), portRid);
            if (records != null && !records.isEmpty()) {
                ret = records.get(0);
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public Map<String, Object> getDeviceInfoFromDeviceRid(Connection conn, String rid) throws SQLException {
        final String fname = "getDeviceInfoFromDeviceRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(rid=%s) - start", fname, rid));
        }
        Map<String, Object> record = null;
        try {
            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_DEVICE_INFO_FROM_DEVICERID,
                    new MapListHandler(), rid);
            if (records.size() <= 0) {
                String msg = String.format(NOT_FOUND, "node(" + rid + ")");
                throw new SQLException(msg);
            }
            record = records.get(0);
            return record;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, record));
            }
        }
    }

    static synchronized public String getInternalMacFromDeviceNameInPortSrcMacDstMac(ConnectionUtilsJdbc utilsJdbc,
            Connection conn, String deviceName, String inPort, String srcMac, String dstMac) throws SQLException {
        final String fname = "getInternalMacFromDeviceNameInPortSrcMacDstMac";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, deviceName=%s, inPort=%s, srcMac=%s, dstMac=%s) - start", fname,
                    conn, deviceName, inPort, srcMac, dstMac));
        }
        String ret = null;
        try {
            DbAccessFlag.lock();

            List<Map<String, Object>> records = utilsJdbc.query(conn,
                    SQL_GET_INTERNALMAC_FROM_SRCMAC_DSTMAC_INPORT_DEVICENAME, new MapListHandler(), deviceName,
                    inPort, srcMac, dstMac);
            if (records.size() > 0) {
                ret = OFPMUtils.longToMacAddress(Long.parseLong((records.get(0).get("internalMac").toString())));
            } else {
                records = utilsJdbc.query(conn, SQL_GET_MAX_INTERNALMAC, new MapListHandler());
                Long newInternalMac = 1L;
                if (records.size() > 0) {
                    newInternalMac = Long.parseLong((records.get(0).get("maxInternalMac").toString())) + 1L;
                }
                Object[] params = { deviceName, inPort, srcMac, dstMac, newInternalMac };
                int rows = utilsJdbc.update(conn, SQL_INSERT_INTERNALMAC, params);
                if (rows == 0) {
                    // TODO exist error
                }
                ret = OFPMUtils.longToMacAddress(newInternalMac);
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", ret));
            }
            DbAccessFlag.unlock();
        }
    }

    @Override
    public List<String> getInternalMacListFromDeviceNameInPort(Connection conn, String deviceName, String inPort)
            throws SQLException {
        final String fname = "getInternalMacListFromDeviceNameInPort";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, deviceName=%s, inPort=%s) - start", fname, conn, deviceName,
                    inPort));
        }
        List<String> ret = null;
        try {
            ret = new ArrayList<String>();
            List<Map<String, Object>> records = utilsJdbc.query(conn,
                    SQL_GET_INTERNALMAC_LIST_FROM_DEVICENAME_INPORT, new MapListHandler("internalMac"), deviceName,
                    inPort);
            for (int i = 0; i < records.size(); i++) {
                Map<String, Object> record = records.get(i);
                Long internalMac = Long.parseLong(record.get("internalMac").toString());
                ret.add(OFPMUtils.longToMacAddress(internalMac));
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", ret));
            }
        }
    }

    @Override
    public int deleteInternalMac(Connection conn, String deviceName, int inPort) throws SQLException {
        final String fname = "deleteInternalMac";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, devicename=%s, portName=%d) - start", fname, conn, deviceName,
                    inPort));
        }
        int ret = 0;
        try {
            Object[] params = { deviceName, inPort };
            ret = utilsJdbc.update(conn, SQL_DELETE_INTERNALMAC_FROM_DEVICE_NAME_PORT_NAME, params);
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
        return ret;
    }

    @Override
    public List<Map<String, Object>> getInternalMacInfoListFromDeviceNameInPort(Connection conn, String deviceName,
            Integer portNumber) throws SQLException {
        final String fname = "getInternalMacInfoListFromDeviceNameInPort";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, deviceName=%s, portNumber=%s) - start", fname, conn, deviceName,
                    portNumber));
        }
        List<Map<String, Object>> ret = null;
        try {
            ret = utilsJdbc.query(conn, SQL_GET_INTERNALMAC_INFO_LIST_FROM_DEVICENAME_PORTNUMBER,
                    new MapListHandler("srcMac", "dstMac", "internalMac"), deviceName, portNumber);
            if (ret == null) {
                return new ArrayList<Map<String, Object>>();
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", ret));
            }
        }
    }

    @Override
    public String getPortBandFromDeviceNamePortName(Connection conn, String deviceName, String portName)
            throws SQLException {
        final String fname = "getPortBandFromDeviceNamePortName";
        if (logger.isTraceEnabled()) {
            logger.trace(
                    String.format("%s(conn=%s, deviceName=%s, portName=%s) - start", fname, deviceName, portName));
        }
        String ret = null;
        try {
            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_PORT_BAND_FROM_DEVICENAME_PORTNAME,
                    new MapListHandler("band"), portName, deviceName);
            if (records != null && !records.isEmpty() && records.get(0) != null) {
                ret = (String) Integer.toString((Integer) records.get(0).get("band"));
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getOfcInfoList(Connection conn) throws SQLException {
        final String fname = "getOfcInfoList";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s) - start", fname, conn));
        }
        List<Map<String, Object>> maps = null;
        //      List<Map<String, Object>> rent_resource_maps = null;
        try {
            maps = utilsJdbc.query(conn, SQL_GET_OFC_INFO_LIST, new MapListHandler());
            return maps;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, maps));
            }
        }
    }

    @Override
    public Map<String, Object> getOfcInfo(Connection conn, String ofcIpPort) throws SQLException {
        final String fname = "getOfcInfo";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, ofcIpPort=%s) - start", fname, conn, ofcIpPort));
        }

        String[] ofc = ofcIpPort.split(":", 0);
        String ip = ofc[0];
        int port = Integer.parseInt(ofc[1]);

        List<Map<String, Object>> infoMapList = getOfcInfoList(conn);

        for (Map<String, Object> infoMap : infoMapList) {

            if (ip.equals((String) infoMap.get("ip")) && (port == (Integer) infoMap.get("port"))) {
                return infoMap;
            }
        }
        try {
            // not found
            return null;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(conn=%s, ofcIpPort=%s) - end", fname, conn, ofcIpPort));
            }
        }
    }

    @Override
    public Map<String, Object> getOfcRidInfo(Connection conn, String ofcIpPort) throws SQLException {
        final String fname = "getOfcRidInfo";
        /*              if (logger.isTraceEnabled()){
                logger.trace(String.format("%s(conn=%s, ip=%s, port=%s) - start", fname, conn, ip, port));
        }
        */
        Map<String, Object> ret = null;
        //          List<Map<String, Object>> rent_resource_maps = null;
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, ofcIpPort=%s) - start", fname, conn, ofcIpPort));
        }

        String[] ofc = ofcIpPort.split(":", 0);
        String ip = ofc[0];
        int port = Integer.parseInt(ofc[1]);
        try {
            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_OFC_INFO_FROM_IP_PORT,
                    new MapListHandler(), ip, port);
            if (records != null && !records.isEmpty()) {
                ret = records.get(0);
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int createOfcInfo(Connection conn, String ip, Integer port) throws SQLException {

        final String fname = "createOfcInfo";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, ofcIp=%s, ofcPort=%d) - start", fname, conn, ip, port));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            String ofcIpPort = ip + ":" + port.toString();
            Map<String, Object> current = this.getOfcRidInfo(conn, ofcIpPort);
            if (current != null) {
                return DB_RESPONSE_STATUS_EXIST;
            }

            Object[] params = { ip, port };
            int nRecords = utilsJdbc.update(conn, SQL_INSERT_OFC_INFO, params);
            if (nRecords == 0) {
                return DB_RESPONSE_STATUS_EXIST;
            }

            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }

    }

    @Override
    public int updateOfcInfo(Connection conn, String ofcIpPort, String ip, Integer port) throws SQLException {
        final String fname = "updateOfcInfo";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, ip=%s, port=%s) - start", fname, conn, ip, port));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            Map<String, Object> current = this.getOfcRidInfo(conn, ofcIpPort);
            if (current == null) {
                ret = DB_RESPONSE_STATUS_NOT_FOUND;
                return ret;
            }

            String OfcRid = (String) current.get("rid");
            if (StringUtils.isBlank(ip)) {
                ip = (String) current.get("ip");
            }
            if (port == null) {
                port = (Integer) current.get("port");
            }

            Object[] params = { ip, port, OfcRid };
            int result = utilsJdbc.update(conn, SQL_UPDATE_OFC_INFO, params);

            if (result == 0) {
                ret = DB_RESPONSE_STATUS_EXIST;
                return ret;
            }

            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int deleteOfcInfo(Connection conn, String ofcIpPort) throws SQLException {
        final String fname = "deleteOfcInfo";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, ofcIpPort=%s) - start", fname, conn, ofcIpPort));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            String[] ofc = ofcIpPort.split(":", 0);
            String ip = ofc[0];
            int port = Integer.parseInt(ofc[1]);

            Object[] params = { ip, port };
            int nRecord = utilsJdbc.update(conn, SQL_DELETE_OFC_FROM_IP_AND_PORT, params);
            if (nRecord != 1) {
                ret = DB_RESPONSE_STATUS_FAIL;
                return ret;
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }

    }

    @Override
    public int insertRoute(Connection conn, Integer sequence_num, String logical_link_id, String node_id,
            String node_name, String in_port_id, String in_port_name, Integer in_port_number, String out_port_id,
            String out_port_name, Integer out_port_number) throws SQLException {
        final String fname = "insertRoute";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format(
                    "%s(conn=%s, sequence_num=%s, logical_link_id=%s, node_id=%s, node_name=%s, in_port_id=%s, in_port_name=%s, in_port_number=%s, out_port_id=%s, out_port_name=%s, out_port_number=%s) - start",
                    fname, conn, sequence_num, logical_link_id, node_id, node_name, in_port_id, in_port_name,
                    in_port_number, out_port_id, out_port_name, out_port_number));
        }
        int ret = DB_RESPONSE_STATUS_OK;
        try {
            Object[] params = { sequence_num, logical_link_id, node_id, node_name, in_port_id, in_port_name,
                    in_port_number, out_port_id, out_port_name, out_port_number };
            int result = utilsJdbc.update(conn, SQL_INSERT_ROUTE_INFO, params);
            if (result != 1) {
                throw new SQLException(String.format(ROUTE_INSERT_FAILD, sequence_num, logical_link_id, node_id,
                        node_name, in_port_id, in_port_name, in_port_number, out_port_id, out_port_name,
                        out_port_number));
            }
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
        return ret;
    }

    @Override
    public Map<String, Object> getLogicalLinkFromNodeNamePortName(Connection conn, String node_name,
            String port_name) throws SQLException {
        final String fname = "getLogicalLinkFromNodeNamePortName";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, node_name=%s, port_name=%s) - start", fname, conn, node_name,
                    port_name));
        }
        Map<String, Object> map = null;
        try {
            List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_LOGICAL_LINK_FROM_NODE_NAME_PORT_NAME,
                    new MapListHandler(), node_name, port_name, node_name, port_name);
            if (!maps.isEmpty()) {
                map = maps.get(0);
            }
            return map;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, map));
            }
        }
    }

    @Override
    public Map<String, Object> getLogicalLinkFromRid(Connection conn, String logicalLinkRid) throws SQLException {
        final String fname = "getLogicalLinkFromRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, logicalLinkRid=%s) - start", fname, conn, logicalLinkRid));
        }
        Map<String, Object> map = null;
        try {
            List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_LOGICAL_LINK_FROM_RID,
                    new MapListHandler(), logicalLinkRid);
            if (!maps.isEmpty()) {
                map = maps.get(0);
            }
            return map;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, map));
            }
        }
    }

    @Override
    public boolean isContainsLogicalLinkFromDeviceNamePortName(Connection conn, String deviceName, String portName)
            throws SQLException {
        final String fname = "isContainsLogicalLinkFromDeviceNamePortName";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, devicename=%s, portName=%s) - start", fname, conn, deviceName,
                    portName));
        }
        boolean ret = true;
        try {
            Map<String, Object> map = getLogicalLinkFromNodeNamePortName(conn, deviceName, portName);
            if (map == null || map.isEmpty()) {
                ret = false;
            }
            return ret;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getRouteFromLogicalLinkId(Connection conn, String logical_link_id)
            throws SQLException {
        final String fname = "getRouteFromLogicalLinkId";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, logical_link_id=%s) - start", fname, conn, logical_link_id));
        }
        List<Map<String, Object>> maps = null;
        try {
            MapListHandler rhs = new MapListHandler("@rid.asString()", "sequence_num", "logical_link_id", "node_id",
                    "node_name", "in_port_id", "in_port_name", "out_port_id", "out_port_name");
            maps = utilsJdbc.query(conn, SQL_GET_ROUTE_FROM_LOGICAL_LINK_ID, rhs, logical_link_id);
            return maps;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, maps));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getRouteFromNodeRid(Connection conn, String nodeRid) throws SQLException {
        final String fname = "getRouteFromNodeRid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, nodeRid=%s) - start", fname, conn, nodeRid));
        }
        List<Map<String, Object>> maps = null;
        try {
            MapListHandler rhs = new MapListHandler("@rid.asString()", "sequence_num", "logical_link_id", "node_id",
                    "node_name", "in_port_id", "in_port_name", "in_port_number", "out_port_id", "out_port_name",
                    "out_port_number");
            maps = utilsJdbc.query(conn, SQL_GET_ROUTE_FROM_NODERID, rhs, nodeRid);
            return maps;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, maps));
            }
        }
    }

    @Override
    public int deleteLogicalLinkFromNodeNamePortName(Connection conn, String deviceName, String portName)
            throws SQLException {
        final String fname = "deleteLogicalLinkFromNodeNamePortName";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, devicename=%s, portName=%s) - start", fname, conn, deviceName,
                    portName));
        }
        int ret = 0;
        try {
            Object[] params = { deviceName, portName, deviceName, portName };
            ret = utilsJdbc.update(conn, SQL_DELETE_LOGICAL_LINK_FROM_DEVICE_NAME_PORT_NAME, params);
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
        return ret;
    }

    @Override
    public int deleteRouteFromLogicalLinkRid(Connection conn, String logical_link_id) throws SQLException {
        final String fname = "deleteLogicalLinkFromNodeNamePortName";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, logical_link_id=%s) - start", fname, conn, logical_link_id));
        }
        int ret = 0;
        try {
            Object[] params = { logical_link_id };
            ret = utilsJdbc.update(conn, SQL_DELETE_ROUTE_FROM_LOGICAL_LINK_ID, params);
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
        return ret;
    }

    @Override
    public List<Map<String, Object>> getCableList(Connection conn) throws SQLException {
        final String fname = "getCableList";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s) - start", fname, conn));
        }
        List<Map<String, Object>> maps = null;
        MapListHandler rhs = new MapListHandler("inDeviceName", "inPortName", "inPortNumber", "outDeviceName",
                "outPortName", "outPortNumber", "@rid.asString()", "band", "used");
        try {
            maps = utilsJdbc.query(conn, SQL_GET_CABLE_LINKS_ALL, rhs);
            return maps;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, maps));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getNetworkidFromSpineid(Connection conn, String in_spine_id,
            String out_spine_id, String network_type) throws SQLException {
        final String fname = "getNetworkidFromSpineid";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, in_spine_id=%s, out_spine_id=%s) - start", fname, conn,
                    in_spine_id, out_spine_id));
        }

        try {
            MapListHandler rsh = new MapListHandler();
            Object[] params = { in_spine_id, out_spine_id, network_type };
            List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_NETWORK_ID_FROM_SPID, rsh, params);

            return maps;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                //logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public String payoutNetworkid(Connection conn, String in_spine_id, String out_spine_id, String network_type)
            throws SQLException {
        final String fname = "getCableList";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s) - start", fname, conn));
        }

        String nwid = "Outer_Tag is sold out!";
        try {
            MapListHandler rsh = new MapListHandler();
            Object[] params = { network_type };
            List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_NETWORK_ID_OF_FREE, rsh, params);
            if (!maps.isEmpty()) {
                nwid = maps.get(0).get("outer_tag").toString();
                Object[] insert_params = { in_spine_id, out_spine_id, nwid, network_type };
                int status = utilsJdbc.update(conn, SQL_UPDATE_PAYOUT_OF_NETWORK_ID, insert_params);
            }
            return nwid;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                //logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int returnNetworkid(Connection conn, String spine_id, String network_type) throws SQLException {
        final String fname = "getCableList";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s) - start", fname, conn));
        }

        try {
            Object[] params = { spine_id, spine_id };
            int status = utilsJdbc.update(conn, SQL_UPDATE_RETURN_OF_NETWORK_ID, params);
            return status;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                //logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int insertOutertag(Connection conn, String localvlan, String outer_tag, String agid, String ag_inport,
            String ag_outport, String type, String network_type) throws SQLException {
        final String fname = "insertOutertag";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s) - start", fname, conn));
        }

        try {
            Object[] params = { agid, ag_inport, ag_outport, localvlan, outer_tag, type, network_type };
            int status = utilsJdbc.update(conn, SQL_INSERT_OUTER_TAG_OF_FLOW, params);
            return status;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                //logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getOutertagflows(Connection conn, String agid) throws SQLException {
        final String fname = "getOutertagflows";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s) - start", fname, conn));
        }
        List<Map<String, Object>> flows = null;
        try {
            Object[] params = { agid };
            flows = utilsJdbc.query(conn, SQL_GET_OUTER_TAG_FLOWS, new MapListHandler(), params);
            return flows;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                //logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int DeleteOutertagflows(Connection conn, Long localvlan) throws SQLException {
        final String fname = "DeleteOutertagflows";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s) - start", fname, conn));
        }
        try {
            Object[] params = { localvlan };
            int status = utilsJdbc.update(conn, SQL_DELETE_OUTER_TAG_OF_FLOW, params);
            return status;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                //logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int AddNetworkId(Connection conn, int networkid, String type) throws SQLException {
        final String fname = "AddNetworkId";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s) - start", fname, conn));
        }
        try {
            int status;
            Object[] params = { networkid, type };
            List<Map<String, Object>> query = utilsJdbc.query(conn, SQL_GET_ASSIGNED_NETWORK_ID,
                    new MapListHandler(), params);
            if (query.isEmpty() == true) {
                status = utilsJdbc.update(conn, SQL_INSERT_NETWORK_ID, params);
            } else {
                status = 1;
            }
            return status;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                //logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public int delNetworkId(Connection conn, int networkid, String type) throws SQLException {
        final String fname = "delNetworkId";
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s) - start", fname, conn));
        }
        try {
            int status;
            Object[] params = { networkid, type };
            List<Map<String, Object>> result = utilsJdbc.query(conn, SQL_GET_ASSIGNED_NETWORK_ID,
                    new MapListHandler(), params);
            if (result.get(0).containsKey("spine1") == true) {
                if (!result.get(0).get("spine1").equals("") && !result.get(0).get("spine2").equals("")) {
                    status = STATUS_NOW_USED;
                } else {
                    status = utilsJdbc.update(conn, SQL_DELETE_NETWORK_ID, params);
                }
            } else {
                status = utilsJdbc.update(conn, SQL_DELETE_NETWORK_ID, params);
            }
            return status;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                //logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

    @Override
    public List<Map<String, Object>> getNetworkId(Connection conn) throws SQLException {
        final String fname = "getNetworkId";
        /*              if (logger.isTraceEnabled()){
                logger.trace(String.format("%s(conn=%s, ip=%s, port=%s) - start", fname, conn, ip, port));
        }
        */
        Map<String, Object> ret = null;
        if (logger.isTraceEnabled()) {
            logger.trace(String.format("%s(conn=%s, ofcIpPort=%s) - start", fname, conn, ""));
        }

        try {
            List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_NETWORK, new MapListHandler());
            if (records != null && !records.isEmpty()) {
                ret = records.get(0);
            }
            return records;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(ret=%s) - end", fname, ret));
            }
        }
    }

}