Example usage for java.sql Connection TRANSACTION_READ_COMMITTED

List of usage examples for java.sql Connection TRANSACTION_READ_COMMITTED

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. TRANSACTION_READ_COMMITTED

Introduction

In this page you can find the example usage for java.sql Connection TRANSACTION_READ_COMMITTED.

Prototype

int TRANSACTION_READ_COMMITTED

To view the source code for java.sql Connection TRANSACTION_READ_COMMITTED.

Click Source Link

Document

A constant indicating that dirty reads are prevented; non-repeatable reads and phantom reads can occur.

Usage

From source file:tds.dll.mysql.StudentDLL.java

/**
 * Open a new test opportunity for testing. Main purpose of this function is
 * to apply business rules. -- Assumptions: -- the given opportunity does not
 * exist and is within testing limits, as verified by _CanOpenTestOpportunity
 * -- the session is open for business, as verified by _CanOpenTestOpportunity
 * //  w  ww .ja v  a 2  s .c  o m
 * @param connection
 * @param clientname
 * @param testee
 * @param testkey
 * @param opportunity
 * @param sessionKey
 * @param browserKey
 * @param testeeID
 * @param testeeName
 * @param status
 * @param guestAccommodations
 * @param testoppkeyRef
 * @throws ReturnStatusException
 */
public SingleDataResultSet _OpenNewOpportunity_SP(SQLConnection connection, String clientname, Long testee,
        String testkey, Integer opportunity, UUID sessionKey, UUID browserKey, String testeeID,
        String testeeName, String status, String guestAccommodations, _Ref<UUID> testoppkeyRef)
        throws ReturnStatusException {
    if (status == null)
        status = "pending";

    Date starttime = _dateUtil.getDateWRetStatus(connection);

    boolean auditProc = _commonDll.AuditProc_FN(connection, "_OpenNewOpportunity");

    Date today = _dateUtil.getDateWRetStatus(connection);
    String environment = _commonDll.getExternsColumnByClientName(connection, clientname, "environment");

    Integer sessionType = null;
    final String SQL_QUERY1 = " select  sessionType  from session where _Key = ${sessionKey}";
    SqlParametersMaps parms1 = (new SqlParametersMaps()).put("sessionKey", sessionKey);
    SingleDataResultSet result = executeStatement(connection, SQL_QUERY1, parms1, false).getResultSets().next();
    DbResultRecord record = (result.getCount() > 0 ? result.getRecords().next() : null);
    if (record != null) {
        sessionType = record.<Integer>get("sessionType");
    }

    testoppkeyRef.set(UUID.randomUUID());

    _Ref<Boolean> segmentedRef = new _Ref<>();
    _Ref<String> algorithmRef = new _Ref<>();
    _Ref<String> subjectRef = new _Ref<>();
    _Ref<String> testIdRef = new _Ref<>();

    _GetTestParms_SP(connection, clientname, testkey, subjectRef, testIdRef, segmentedRef, algorithmRef);

    DataBaseTable windowsTbl = getDataBaseTable("newOppWindows")
            .addColumn("WID", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 100)
            .addColumn("maxOpps", SQL_TYPE_To_JAVA_TYPE.INT).addColumn("numOpps", SQL_TYPE_To_JAVA_TYPE.INT)
            .addColumn("startDate", SQL_TYPE_To_JAVA_TYPE.DATETIME)
            .addColumn("endDate", SQL_TYPE_To_JAVA_TYPE.DATETIME)
            .addColumn("formkey", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 50)
            .addColumn("modeOpps", SQL_TYPE_To_JAVA_TYPE.INT)
            .addColumn("testmode", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 100)
            .addColumn("modeMax", SQL_TYPE_To_JAVA_TYPE.INT)
            .addColumn("testkey", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 250);

    final String clientnameFinal = clientname;
    final String testIDFinal = testIdRef.get();
    final Long testeeFinal = testee;
    final Integer sessionTypeFinal = sessionType;
    executeMethodAndInsertIntoTemporaryTable(connection, new AbstractDataResultExecutor() {
        @Override
        public SingleDataResultSet execute(SQLConnection connection) throws ReturnStatusException {
            // getting windowID, windowMax , startDate, endDate, formkey, mode,
            // modeMax, testkey from _GetTesteeTestWindows,
            // They will populate (WID, maxopps, startDate, endDate, formkey,
            // testmode, modeMax, testkey) columns in windows tbl
            // Still need numopps, and modeopps
            SingleDataResultSet result = _GetTesteeTestWindows_SP(connection, clientnameFinal, testIDFinal,
                    testeeFinal, sessionTypeFinal);
            result.resetColumnName(1, "wid");
            result.resetColumnName(2, "maxopps");
            result.resetColumnName(6, "testmode");

            result.addColumn("numopps", SQL_TYPE_To_JAVA_TYPE.INT);
            result.addColumn("modeopps", SQL_TYPE_To_JAVA_TYPE.INT);

            Iterator<DbResultRecord> records = result.getRecords();
            while (records.hasNext()) {
                DbResultRecord record = records.next();
                record.addColumnValue("numopps", 0);
                record.addColumnValue("modeopps", 0);
            }
            return result;
        }
    }, windowsTbl, true);

    Date minStartDate = null;
    final String SQL_QUERY2 = "select min(startDate)  as minStartDate from ${windowsTblName}";
    Map<String, String> unquotedParms2 = new HashMap<>();
    unquotedParms2.put("windowsTblName", windowsTbl.getTableName());
    result = executeStatement(connection, fixDataBaseNames(SQL_QUERY2, unquotedParms2), null, false)
            .getResultSets().next();
    record = (result.getCount() > 0 ? result.getRecords().next() : null);
    if (record != null) {
        minStartDate = record.<Date>get("minStartDate");
    }

    String windowId = null;
    String mode = null;
    final String SQL_QUERY3 = "select  WID as windowID, testmode as mode from ${windowsTblName} where testkey = ${testkey} and startDate = ${minStartDate} limit 1";
    Map<String, String> unquotedParms3 = unquotedParms2;
    SqlParametersMaps parms3 = (new SqlParametersMaps()).put("testkey", testkey).put("minStartDate",
            minStartDate);
    result = executeStatement(connection, fixDataBaseNames(SQL_QUERY3, unquotedParms3), parms3, false)
            .getResultSets().next();
    record = (result.getCount() > 0 ? result.getRecords().next() : null);
    if (record != null) {
        windowId = record.<String>get("windowId");
        mode = record.<String>get("mode");
    }
    connection.dropTemporaryTable(windowsTbl);

    if (windowId == null)
        return _commonDll._ReturnError_SP(connection, clientname, "_OpenNewOpportunity",
                "There is no active testing window for this student at this time");

    _Ref<Long> newIdRef = new _Ref<>();
    //TODO Temporary Commented on 10.20.2014 per Larry's suggestion.
    //    _CreateClientReportingID_SP (connection, clientname, testoppkeyRef.get (), newIdRef);
    //
    //    if (newIdRef.get () == null) {
    //
    //      _commonDll._LogDBError_SP (connection, "_OpenNewOpportunity", "Unable to create a unique reporting ID", testee, testkey, opportunity, null);
    //      return _commonDll._ReturnError_SP (connection, clientname, "_OpenNewOpportunity", "Unable to create a unique reporting ID");
    //    }

    // -- version is irrespective of deleted status
    Integer version = null;
    final String SQL_QUERY4 = "select max(_version)  as version from testopportunity "
            + " where _efk_Testee = ${testee} and _efk_TestID = ${testID} and Opportunity = ${opportunity} and clientname = ${clientname}";
    SqlParametersMaps parms4 = (new SqlParametersMaps()).put("testee", testee).put("opportunity", opportunity)
            .put("clientname", clientname).put("testid", testIdRef.get());
    result = executeStatement(connection, SQL_QUERY4, parms4, false).getResultSets().next();
    record = (result.getCount() > 0 ? result.getRecords().next() : null);
    if (record != null) {
        version = record.<Integer>get("version");
    }

    if (version == null)
        version = 1;
    else
        version++;

    final String SQL_QUERY5 = "insert into testopportunity (_key, _version, clientname, _efk_Testee, _efk_TestID, Opportunity, "
            + "     Status,  Subject, TesteeID, TesteeName, _fk_Browser, DateChanged,  ReportingID,  windowID,"
            + "     mode, isSegmented, algorithm,_efk_AdminSubject, environment, SessID, ProctorName, waitingForSegment, datejoined) "
            + " select ${testoppkey}, ${version}, ${clientname}, ${testee}, ${testID}, ${opportunity},  "
            + "        'paused', ${subject}, ${testeeID}, ${testeeName}, ${browserKey}, ${today}, ${newID}, ${windowID}, "
            + "         ${mode}, ${segmented}, ${algorithm}, ${testkey}, ${environment}, SessionID, ProctorName, 1, now(3)"
            + " from session where _Key = ${sessionKey}";
    SqlParametersMaps parms5 = (new SqlParametersMaps()).put("testoppkey", testoppkeyRef.get())
            .put("version", version).put("clientname", clientname).put("testee", testee)
            .put("testID", testIdRef.get()).put("opportunity", opportunity).put("subject", subjectRef.get())
            .put("testeeID", testeeID).put("testeeName", testeeName).put("browserKey", browserKey)
            .put("today", today).put("newID", newIdRef.get()).put("windowID", windowId).put("mode", mode)
            .put("segmented", segmentedRef.get()).put("algorithm", algorithmRef.get()).put("testKey", testkey)
            .put("environment", environment).put("sessionKey", sessionKey);
    executeStatement(connection, SQL_QUERY5, parms5, false).getUpdateCount();

    String context = "INITIAL";
    _commonDll._SetTesteeAttributes_SP(connection, clientname, testoppkeyRef.get(), testee, context);

    final String SQL_QUERY6 = "select _fk_TestOpportunity from ${ArchiveDB}.systemerrors where procname = '_SetTesteeAttributes' and _fk_TestOpportunity = ${testoppkey} limit 1";
    SqlParametersMaps parms6 = (new SqlParametersMaps()).put("testoppkey", testoppkeyRef.get());
    if (exists(executeStatement(connection, fixDataBaseNames(SQL_QUERY6), parms6, false))) {
        // -- we have had deadlock problems with this, try again?
        _commonDll._SetTesteeAttributes_SP(connection, clientname, testoppkeyRef.get(), testee, context);
    }

    // -- insert the given accommodations or defaults
    _InitOpportunityAccommodations_SP(connection, testoppkeyRef.get(), guestAccommodations);

    Integer transactionIsolation = null;
    try {
        transactionIsolation = connection.getTransactionIsolation();
        connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

        final String SQL_QUERY7 = "update testopportunity set status = ${status} where _key = ${testoppkey}";
        SqlParametersMaps parms7 = (new SqlParametersMaps()).put("testoppkey", testoppkeyRef.get())
                .put("status", status);
        executeStatement(connection, SQL_QUERY7, parms7, false).getUpdateCount();

    } catch (SQLException se) {
        throw new ReturnStatusException(se);
    } finally {
        try {
            if (transactionIsolation != null)
                connection.setTransactionIsolation(transactionIsolation);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (auditProc) {
        // String sessionDB = getAppSettings ().get ("TDSSessionDBName");
        String sessionDB = getTdsSettings().getTDSSessionDBName();
        final String SQL_QUERY8 = "insert into ${ArchiveDB}.opportunityaudit (_fk_TestOpportunity, _fk_Session, AccessType, hostname, _fk_Browser, dateaccessed, dbname) "
                + " values (${testoppkey}, ${sessionKey}, ${status}, ${hostname}, ${browserKey}, now(3), ${dbname})";
        String localhost = _commonDll.getLocalhostName();
        SqlParametersMaps parms8 = (new SqlParametersMaps()).put("testoppkey", testoppkeyRef.get())
                .put("status", status).put("sessionKey", sessionKey).put("hostname", localhost)
                .put("browserKey", browserKey).put("dbname", sessionDB);
        executeStatement(connection, fixDataBaseNames(SQL_QUERY8), parms8, false).getUpdateCount();
    }

    _commonDll._LogDBLatency_SP(connection, "_OpenNewOpportunity", starttime, testee, true, null,
            testoppkeyRef.get(), null, clientname, null);

    return null;
}

From source file:org.apache.hadoop.hive.metastore.MyXid.java

@Override
public boolean isView(String dbName, String tblName) throws NoSuchObjectException, MetaException {
    Connection con = null;/*from   ww w  . ja v  a2  s  .  co  m*/
    Statement ps = null;
    boolean success = false;
    boolean is = false;

    dbName = dbName.toLowerCase();
    tblName = tblName.toLowerCase();

    try {
        con = getSegmentConnection(dbName);
    } catch (MetaStoreConnectException e1) {
        LOG.error(
                "check is a view error error, db=" + dbName + ", tbl=" + tblName + ", msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    } catch (SQLException e1) {
        LOG.error(
                "check is a view error error, db=" + dbName + ", tbl=" + tblName + ", msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    }

    try {
        con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        ps = con.createStatement();

        String sql = "select tbl_type from tbls where db_name='" + dbName + "' and tbl_name='" + tblName + "'";
        String type = null;
        boolean isTblFind = false;

        ResultSet tSet = ps.executeQuery(sql);
        while (tSet.next()) {
            type = tSet.getString(1);
            isTblFind = true;
            break;
        }

        if (!isTblFind) {
            LOG.error("check is a view error error, db=" + dbName + ", tbl=" + tblName);
            throw new NoSuchObjectException("can not find table/view:" + dbName + ", tbl=" + tblName);
        }

        if (type != null && type.equalsIgnoreCase("VIRTUAL_VIEW")) {
            is = true;
        }

        success = true;
    } catch (SQLException sqlex) {
        LOG.error("check is a view error error, db=" + dbName + ", tbl=" + tblName + ", msg="
                + sqlex.getMessage());
        sqlex.printStackTrace();
        throw new MetaException(sqlex.getMessage());
    } finally {
        closeStatement(ps);
        closeConnection(con);
    }

    return is;
}

From source file:org.apache.hadoop.hive.metastore.MyXid.java

@Override
public boolean isAUserName(String userName) throws NoSuchObjectException, MetaException {
    Connection con = null;// w w w .j av  a 2s.  c o m
    Statement ps = null;
    boolean is = false;
    userName = userName.toLowerCase();

    try {
        con = getGlobalConnection();
    } catch (MetaStoreConnectException e1) {
        LOG.error("check user exist error, user=" + userName + ", msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    } catch (SQLException e1) {
        LOG.error("check user exist error, user=" + userName + ", msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    }

    try {
        con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        ps = con.createStatement();

        String sql = "select count(1) from tdwuser where user_name='" + userName + "'";
        int count = 0;

        ResultSet uSet = ps.executeQuery(sql);
        while (uSet.next()) {
            count = uSet.getInt(1);
            break;
        }

        if (count == 1) {
            is = true;
        }

    } catch (SQLException sqlex) {
        LOG.error("check user exist error, user=" + userName + ", msg=" + sqlex.getMessage());
        sqlex.printStackTrace();
        throw new MetaException(sqlex.getMessage());
    } finally {
        closeStatement(ps);
        closeConnection(con);
    }

    return is;
}

From source file:org.apache.hadoop.hive.metastore.MyXid.java

@Override
public List<String> getPlayRoles(String userName) throws NoSuchObjectException, MetaException {
    Connection con = null;/*ww w . j a va  2 s  . c  o  m*/
    ;
    Statement ps = null;
    List<String> roles = new ArrayList<String>();
    userName = userName.toLowerCase();

    try {
        con = getGlobalConnection();
    } catch (MetaStoreConnectException e1) {
        LOG.error("get roles error,  msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    } catch (SQLException e1) {
        LOG.error("get roles error,  msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    }

    try {
        con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        ps = con.createStatement();

        String sql = "select  role_name from tdwuserrole where user_name='" + userName + "'";

        ResultSet roleSet = ps.executeQuery(sql);

        while (roleSet.next()) {
            roles.add(roleSet.getString(1));
        }
    } catch (SQLException sqlex) {
        LOG.error("get roles error,  msg=" + sqlex.getMessage());
        sqlex.printStackTrace();
        throw new MetaException(sqlex.getMessage());
    } finally {
        closeStatement(ps);
        closeConnection(con);
    }

    return roles;
}

From source file:org.apache.hadoop.hive.metastore.MyXid.java

@Override
public List<String> getDatabasesWithOwner(String owner) throws MetaException {
    Connection con;//from   w ww.  j  a  v  a  2s  . c om
    Statement stmt = null;
    owner = owner.toLowerCase();

    List<String> dbNameList = new ArrayList<String>();

    try {
        con = getGlobalConnection();
    } catch (MetaStoreConnectException e1) {
        LOG.error("get databases error msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    } catch (SQLException e1) {
        LOG.error("get databases error msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    }

    try {
        con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

        stmt = con.createStatement();
        String sql = "select db_name, owner from router where owner='" + owner + "'";
        ResultSet ret = stmt.executeQuery(sql);

        dbNameList = new ArrayList<String>();

        while (ret.next()) {
            dbNameList.add(ret.getString(1));
        }
        ret.close();
    } catch (SQLException x) {
        x.printStackTrace();
        try {
            con.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        LOG.error("get databases error msg=" + x.getMessage());
        throw new MetaException(x.getMessage());
    } finally {
        closeStatement(stmt);
        closeConnection(con);
    }

    return dbNameList;
}

From source file:org.apache.hadoop.hive.metastore.MyXid.java

public Map<String, String> getTableWithType(String dbName, String pattern) throws MetaException {
    Connection con;//from  w ww .j ava 2 s . c o  m
    Statement ps = null;
    Map<String, String> tableMap = new TreeMap<String, String>();

    dbName = dbName.toLowerCase();
    pattern = pattern.toLowerCase();

    try {
        con = getSegmentConnection(dbName);
    } catch (MetaStoreConnectException e1) {
        LOG.error("get table error, db=" + dbName + ", pattern=" + pattern + ", msg=" + e1.getMessage());

        throw new MetaException(e1.getMessage());
    } catch (SQLException e1) {
        LOG.error("get table error, db=" + dbName + ", pattern=" + pattern + ", msg=" + e1.getMessage());

        throw new MetaException(e1.getMessage());
    }

    try {
        con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        ps = con.createStatement();

        String sql = null;

        if (pattern == null || pattern.isEmpty() || pattern.equals(".*") || pattern.equals("*")) {
            sql = "select tbl_name, tbl_type from tbls where db_name='" + dbName + "'";
        } else {
            pattern = pattern.replace('*', '%');

            sql = "select tbl_name, tbl_type from tbls where db_name='" + dbName + "'" + " and tbl_name like '"
                    + pattern + "'";
        }

        ResultSet tblSet = ps.executeQuery(sql);

        while (tblSet.next()) {
            tableMap.put(tblSet.getString(1), tblSet.getString(2));
        }
    } catch (SQLException sqlex) {
        LOG.error("get table error, db=" + dbName + ", pattern=" + pattern + ", msg=" + sqlex.getMessage());
        sqlex.printStackTrace();
        throw new MetaException(sqlex.getMessage());
    } finally {
        closeStatement(ps);
        closeConnection(con);
    }

    return tableMap;
}

From source file:org.apache.hadoop.hive.metastore.MyXid.java

public Map<String, TableInfo> getTableWithTypeWithParams(String dbName, String pattern) throws MetaException {
    // TODO Auto-generated method stub
    Connection con;//from  w  w  w. j  a  v a  2 s.  co  m
    Statement ps = null;

    Map<String, TableInfo> tblInfoMap = new TreeMap<String, TableInfo>();

    dbName = dbName.toLowerCase();
    pattern = pattern.toLowerCase();

    try {
        con = getSegmentConnection(dbName);
    } catch (MetaStoreConnectException e1) {
        // TODO Auto-generated catch block
        LOG.error("get table error, db=" + dbName + ", pattern=" + pattern + ", msg=" + e1.getMessage());

        throw new MetaException(e1.getMessage());
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        LOG.error("get table error, db=" + dbName + ", pattern=" + pattern + ", msg=" + e1.getMessage());

        throw new MetaException(e1.getMessage());
    }

    try {
        con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        ps = con.createStatement();

        String sql = null;

        if (pattern == null || pattern.isEmpty() || pattern.equals(".*") || pattern.equals("*")) {
            sql = "select create_time, tbl_type, tbl_name, tbl_owner, pri_part_type, tbl_comment from tbls where db_name='"
                    + dbName + "'";
        } else {
            pattern = pattern.replace('*', '%');

            sql = "select create_time, tbl_type, tbl_name, tbl_owner, pri_part_type, tbl_comment from tbls where db_name='"
                    + dbName + "'" + " and tbl_name like '" + pattern + "'";
        }

        ResultSet tblSet = ps.executeQuery(sql);
        TableInfo info = null;
        Map<String, String> params = null;
        String priPartType = null;

        while (tblSet.next()) {
            //System.out.println("########################table name is " + tblSet.getString(3));
            info = new TableInfo();
            params = new HashMap<String, String>();

            info.setCreateTime(tblSet.getString(1));
            params.put("tbl_type", tblSet.getString(2));
            info.setOwner(tblSet.getString(4));

            priPartType = tblSet.getString(5);
            if (priPartType == null) {
                params.put("is_part_table", "no");
            } else {
                params.put("is_part_table", "yes");
            }
            info.setComment(tblSet.getString(6));
            info.setTblParams(params);
            tblInfoMap.put(tblSet.getString(3), info);
        }
    } catch (SQLException sqlex) {
        LOG.error("get table error, db=" + dbName + ", pattern=" + pattern + ", msg=" + sqlex.getMessage());
        sqlex.printStackTrace();
        throw new MetaException(sqlex.getMessage());
    } finally {
        closeStatement(ps);
        closeConnection(con);
    }

    return tblInfoMap;
}

From source file:org.apache.hadoop.hive.metastore.MyXid.java

public Map<String, TableInfo> getTableWithTypeWithKeyWord(String dbName, String keyWord) throws MetaException {
    // TODO Auto-generated method stub
    Connection con;//from   w  w  w  . ja va 2s  . c o  m
    Statement ps = null;

    Map<String, TableInfo> tblInfoMap = new TreeMap<String, TableInfo>();

    dbName = dbName.toLowerCase();
    //pattern = pattern.toLowerCase();

    try {
        con = getSegmentConnection(dbName);
    } catch (MetaStoreConnectException e1) {
        // TODO Auto-generated catch block
        LOG.error("get table error, db=" + dbName + ", key word=" + keyWord + ", msg=" + e1.getMessage());

        throw new MetaException(e1.getMessage());
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        LOG.error("get table error, db=" + dbName + ", key word=" + keyWord + ", msg=" + e1.getMessage());

        throw new MetaException(e1.getMessage());
    }

    try {
        con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        ps = con.createStatement();

        String sql = null;

        sql = "select create_time, tbl_type, tbl_name, tbl_owner, pri_part_type, tbl_comment from tbls where db_name='"
                + dbName + "'";

        if (keyWord != null) {
            sql += " and (tbl_name ilike '%" + keyWord + "%' or tbl_comment ilike '%" + keyWord + "%')";
        }

        ResultSet tblSet = ps.executeQuery(sql);
        TableInfo info = null;
        Map<String, String> params = null;
        String priPartType = null;

        while (tblSet.next()) {
            //System.out.println("########################table name is " + tblSet.getString(3));
            info = new TableInfo();
            params = new HashMap<String, String>();

            info.setCreateTime(tblSet.getString(1));
            params.put("tbl_type", tblSet.getString(2));
            info.setOwner(tblSet.getString(4));

            priPartType = tblSet.getString(5);
            if (priPartType == null) {
                params.put("is_part_table", "no");
            } else {
                params.put("is_part_table", "yes");
            }
            info.setComment(tblSet.getString(6));
            info.setTblParams(params);
            tblInfoMap.put(tblSet.getString(3), info);
        }
    } catch (SQLException sqlex) {
        LOG.error("get table error, db=" + dbName + ", key word=" + keyWord + ", msg=" + sqlex.getMessage());
        sqlex.printStackTrace();
        throw new MetaException(sqlex.getMessage());
    } finally {
        closeStatement(ps);
        closeConnection(con);
    }

    return tblInfoMap;
}

From source file:org.apache.hadoop.hive.metastore.MyXid.java

@Override
public List<String> getDbsByPriv(String user, String passwd) throws MetaException {
    Connection con = null;//from   ww w.j  ava  2  s .  c o  m
    ;
    Statement ps = null;
    Set<String> tempSet = new HashSet<String>();
    List<String> dbs = new ArrayList<String>();
    user = user.toLowerCase();

    try {
        con = getGlobalConnection();
    } catch (MetaStoreConnectException e1) {
        LOG.error("get db by priv error,  msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    } catch (SQLException e1) {
        LOG.error("get db by priv error,  msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    }

    try {
        con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        ps = con.createStatement();

        String sql = "select db_name from dbpriv where user_name='" + user + "' and select_priv=true";

        ResultSet dbSet = ps.executeQuery(sql);

        while (dbSet.next()) {
            tempSet.add(dbSet.getString(1));
        }
        dbSet.close();

        sql = "select db_name from tdwuserrole, dbpriv where tdwuserrole.user_name='" + user
                + "' and tdwuserrole.role_name=dbpriv.user_name and dbpriv.select_priv=true";

        dbSet = ps.executeQuery(sql);

        while (dbSet.next()) {
            tempSet.add(dbSet.getString(1));
        }
        dbSet.close();

        sql = "select db_name from tblpriv where user_name='" + user + "' and select_priv=true";

        dbSet = ps.executeQuery(sql);

        while (dbSet.next()) {
            tempSet.add(dbSet.getString(1));
        }
        dbSet.close();

        sql = "select tblpriv.db_name from tdwuserrole, tblpriv where tdwuserrole.user_name='" + user
                + "' and tdwuserrole.role_name=tblpriv.user_name and tblpriv.select_priv=true";

        dbSet = ps.executeQuery(sql);

        while (dbSet.next()) {
            tempSet.add(dbSet.getString(1));
        }
        dbSet.close();
    } catch (SQLException sqlex) {
        LOG.error("get db by priv error,  msg=" + sqlex.getMessage());
        sqlex.printStackTrace();
        throw new MetaException(sqlex.getMessage());
    } finally {
        closeStatement(ps);
        closeConnection(con);
    }

    for (String i : tempSet) {
        dbs.add(i);
    }

    return dbs;
}

From source file:org.apache.hadoop.hive.metastore.MyXid.java

@Override
public Map<String, String> getTblsByPriv(String user, String passwd, String db)
        throws NoSuchObjectException, MetaException {
    Connection con = null;//from ww w.j a v a  2 s  .co  m
    ;
    Statement ps = null;
    Set<String> tempSet = new HashSet<String>();
    List<String> dbs = new ArrayList<String>();
    user = user.toLowerCase();
    db = db.toLowerCase();
    boolean isDbPriv = false;

    Map<String, String> tblMap = getTableWithType(db, "*");
    Map<String, String> tblMapPriv = new TreeMap<String, String>();
    if (tblMap.isEmpty()) {
        return new TreeMap<String, String>();
    }

    try {
        con = getGlobalConnection();
    } catch (MetaStoreConnectException e1) {
        LOG.error("get tbls by priv error,  msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    } catch (SQLException e1) {
        LOG.error("get tbls by priv error,  msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    }

    try {
        con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        ps = con.createStatement();

        String sql = "select db_name from dbpriv where user_name='" + user
                + "' and select_priv=true and db_name='" + db + "'";

        ResultSet dbSet = ps.executeQuery(sql);

        while (dbSet.next()) {
            isDbPriv = true;
        }
        dbSet.close();

        if (!isDbPriv) {
            sql = "select db_name from tdwuserrole, dbpriv where tdwuserrole.user_name='" + user
                    + "' and tdwuserrole.role_name=dbpriv.user_name and dbpriv.select_priv=true and db_name='"
                    + db + "'";

            dbSet = ps.executeQuery(sql);

            while (dbSet.next()) {
                isDbPriv = true;
            }
            dbSet.close();
        }

        if (isDbPriv) {
            return tblMap;
        } else {
            sql = "select tbl_name from tblpriv where user_name='" + user
                    + "' and select_priv=true and db_name='" + db + "'";

            ResultSet tblSet = ps.executeQuery(sql);
            String tblName = null;
            String type = null;
            while (tblSet.next()) {
                tblName = tblSet.getString(1);
                type = tblMap.get(tblName);
                if (type != null) {
                    tblMapPriv.put(tblName, type);
                }
            }
            tblSet.close();

            sql = "select a.tbl_name from tblpriv a, tdwuserrole b "
                    + "where a.user_name=b.role_name and b.user_name='" + user
                    + "' and a.select_priv=true and a.db_name='" + db + "'";

            tblSet = ps.executeQuery(sql);
            tblName = null;
            type = null;
            while (tblSet.next()) {
                tblName = tblSet.getString(1);
                type = tblMap.get(tblName);
                if (type != null) {
                    tblMapPriv.put(tblName, type);
                }
            }
            tblSet.close();
            return tblMapPriv;
        }
    } catch (SQLException sqlex) {
        LOG.error("get db by priv error,  msg=" + sqlex.getMessage());
        sqlex.printStackTrace();
        throw new MetaException(sqlex.getMessage());
    } finally {
        closeStatement(ps);
        closeConnection(con);
    }
}