Example usage for java.sql SQLException toString

List of usage examples for java.sql SQLException toString

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. SQLException
  6. toString

Introduction

In this page you can find the example usage for java.sql SQLException toString.

Prototype

public String toString()

Source Link

Document

Returns a short description of this throwable.

Usage

From source file:org.cerberus.crud.dao.impl.TestCaseExecutionDAO.java

@Override
public AnswerList readDistinctColumnByTag(String tag, boolean env, boolean country, boolean browser,
        boolean app) {
    AnswerList answer = new AnswerList();
    StringBuilder query = new StringBuilder();
    StringBuilder distinct = new StringBuilder();
    int prev = 0;
    MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);

    if (!(!env && !country && !app && !browser)) {
        if (env) {
            distinct.append("Environment");
            prev++;//w ww.  j a  v  a  2s . c o  m
        }
        if (country) {
            if (prev != 0) {
                prev = 0;
                distinct.append(",");
            }
            distinct.append("Country");
            prev++;
        }
        if (browser) {
            if (prev != 0) {
                prev = 0;
                distinct.append(",");
            }
            distinct.append("Browser");
            prev++;
        }
        if (app) {
            if (prev != 0) {
                prev = 0;
                distinct.append(",");
            }
            distinct.append("Application");
        }

        query.append("SELECT ");
        query.append(distinct.toString());
        query.append(" FROM testcaseexecution exe WHERE exe.tag = ? GROUP BY ");
        query.append(distinct.toString());
    } else {
        //If there is no distinct, select nothing
        query.append("SELECT * FROM testcaseexecution exe WHERE 1 = 0 AND exe.tag = ?");
    }

    Connection connection = this.databaseSpring.connect();

    List<TestCaseExecution> column = new ArrayList<TestCaseExecution>();

    try {
        PreparedStatement preStat = connection.prepareStatement(query.toString());

        preStat.setString(1, tag);
        try {
            ResultSet resultSet = preStat.executeQuery();
            try {
                while (resultSet.next()) {
                    TestCaseExecution tmp = new TestCaseExecution();
                    if (env) {
                        tmp.setEnvironment(resultSet.getString("Environment"));
                    } else {
                        tmp.setEnvironment("");
                    }
                    if (country) {
                        tmp.setCountry(resultSet.getString("Country"));
                    } else {
                        tmp.setCountry("");
                    }
                    if (browser) {
                        tmp.setBrowser(resultSet.getString("Browser"));
                    } else {
                        tmp.setBrowser("");
                    }
                    if (app) {
                        tmp.setApplication(resultSet.getString("Application"));
                    } else {
                        tmp.setApplication("");
                    }
                    column.add(tmp);
                }
                msg.setDescription(msg.getDescription().replace("%ITEM%", "TestCaseExecution")
                        .replace("%OPERATION%", "SELECT"));
                answer = new AnswerList(column, column.size());
            } catch (SQLException exception) {
                MyLogger.log(TestCaseExecutionDAO.class.getName(), Level.ERROR,
                        "Unable to execute query : " + exception.toString());
                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%",
                        "Unable to retrieve the list of entries!"));
                column = null;
            } finally {
                if (resultSet != null) {
                    resultSet.close();
                }
            }
        } catch (SQLException ex) {
            MyLogger.log(TestCaseExecutionDAO.class.getName(), Level.ERROR,
                    "Unable to execute query : " + ex.toString());
            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
            msg.setDescription(
                    msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
            column = null;
        } finally {
            if (preStat != null) {
                preStat.close();
            }
        }
    } catch (SQLException ex) {
        MyLogger.log(TestCaseExecutionDAO.class.getName(), Level.WARN, ex.toString());
        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
        msg.setDescription(
                msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException ex) {
            MyLogger.log(TestCaseExecutionDAO.class.getName(), Level.ERROR,
                    "Unable to execute query : " + ex.toString());
        }
    }

    answer.setResultMessage(msg);
    return answer;
}

From source file:org.cerberus.crud.dao.impl.TestCaseExecutionDAO.java

@Override
public AnswerList readByCriteria(int start, int amount, String sort, String searchTerm,
        Map<String, List<String>> individualSearch) throws CerberusException {
    MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
    AnswerList answer = new AnswerList();
    List<String> individalColumnSearchValues = new ArrayList<String>();

    final StringBuffer query = new StringBuffer();

    query.append("SELECT * FROM testcaseexecution exe ");
    query.append("where exe.`start`> '").append(DateUtil.getMySQLTimestampTodayDeltaMinutes(-360000))
            .append("' ");

    if (!StringUtil.isNullOrEmpty(searchTerm)) {
        query.append("and (exe.`id` like ? ");
        query.append(" or exe.`test` like ? ");
        query.append(" or exe.`testCase` like ? ");
        query.append(" or exe.`build` like ? ");
        query.append(" or exe.`revision` like ? ");
        query.append(" or exe.`environment` like ? ");
        query.append(" or exe.`country` like ? ");
        query.append(" or exe.`browser` like ? ");
        query.append(" or exe.`version` like ? ");
        query.append(" or exe.`platform` like ? ");
        query.append(" or exe.`browserfullversion` like ? ");
        query.append(" or exe.`start` like ? ");
        query.append(" or exe.`end` like ? ");
        query.append(" or exe.`controlstatus` like ? ");
        query.append(" or exe.`controlmessage` like ? ");
        query.append(" or exe.`application` like ? ");
        query.append(" or exe.`ip` like ? ");
        query.append(" or exe.`url` like ? ");
        query.append(" or exe.`port` like ? ");
        query.append(" or exe.`tag` like ? ");
        query.append(" or exe.`finished` like ? ");
        query.append(" or exe.`verbose` like ? ");
        query.append(" or exe.`status` like ? ");
        query.append(" or exe.`crbversion` like ? ");
        query.append(" or exe.`executor` like ? ");
        query.append(" or exe.`screensize` like ? )");
    }//from  w  w  w.j  a va 2 s  .c om
    if (individualSearch != null && !individualSearch.isEmpty()) {
        query.append(" and ( 1=1 ");
        for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {
            query.append(" and ");
            query.append(SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));
            individalColumnSearchValues.addAll(entry.getValue());
        }
        query.append(" ) ");
    }

    if (!StringUtil.isNullOrEmpty(sort)) {
        query.append(" order by ").append(sort);
    }

    if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {
        query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);
    } else {
        query.append(" limit ").append(start).append(" , ").append(amount);
    }

    // Debug message on SQL.
    if (LOG.isDebugEnabled()) {
        LOG.debug("SQL : " + query.toString());
    }
    List<TestCaseExecution> testCaseExecutionList = new ArrayList<TestCaseExecution>();
    Connection connection = this.databaseSpring.connect();
    try {
        System.out.print(query.toString());
        PreparedStatement preStat = connection.prepareStatement(query.toString());
        int i = 1;
        if (!Strings.isNullOrEmpty(searchTerm)) {
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
            preStat.setString(i++, "%" + searchTerm + "%");
        }
        for (String individualColumnSearchValue : individalColumnSearchValues) {
            preStat.setString(i++, individualColumnSearchValue);
        }

        try {
            ResultSet resultSet = preStat.executeQuery();
            try {
                while (resultSet.next()) {
                    testCaseExecutionList.add(this.loadFromResultSet(resultSet));
                }
                msg.setDescription(msg.getDescription().replace("%ITEM%", "TestCaseExecution")
                        .replace("%OPERATION%", "SELECT"));
                //                    answer = new AnswerList(testCaseExecutionList, testCaseExecutionList.size());
                answer.setTotalRows(testCaseExecutionList.size());
            } catch (SQLException exception) {
                MyLogger.log(TestCaseExecutionDAO.class.getName(), Level.ERROR,
                        "Unable to execute query : " + exception.toString());
                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%",
                        "Unable to retrieve the list of entries!"));
                testCaseExecutionList = null;
            } finally {
                resultSet.close();
            }
        } catch (SQLException exception) {
            MyLogger.log(TestCaseExecutionDAO.class.getName(), Level.ERROR,
                    "Unable to execute query : " + exception.toString());
            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
            msg.setDescription(
                    msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
            testCaseExecutionList = null;
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        MyLogger.log(TestCaseExecutionDAO.class.getName(), Level.ERROR,
                "Unable to execute query : " + exception.toString());
        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
        msg.setDescription(
                msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
        testCaseExecutionList = null;
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            MyLogger.log(TestCaseExecutionDAO.class.getName(), Level.WARN, e.toString());
            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
            msg.setDescription(
                    msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
        }
    }

    answer.setResultMessage(msg);
    answer.setDataList(testCaseExecutionList);
    return answer;
}

From source file:org.cerberus.crud.dao.impl.TestCaseExecutionInQueueDAO.java

@Override
public AnswerList readBySystemByVarious(String system, List<String> testList, List<String> applicationList,
        List<String> projectList, List<String> tcstatusList, List<String> groupList, List<String> tcactiveList,
        List<String> priorityList, List<String> targetsprintList, List<String> targetrevisionList,
        List<String> creatorList, List<String> implementerList, List<String> buildList,
        List<String> revisionList, List<String> environmentList, List<String> countryList,
        List<String> browserList, List<String> tcestatusList, String ip, String port, String tag,
        String browserversion, String comment, String bugid, String ticket) {
    AnswerList answer = new AnswerList();
    MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
    List<TestCaseExecutionInQueue> tceList = new ArrayList<TestCaseExecutionInQueue>();
    List<String> whereClauses = new LinkedList<String>();

    StringBuilder query = new StringBuilder();

    int paramNumber = 0;

    query.append(" select t.ID as statusExecutionID, t.* from ( ");
    query.append(" select exq.*, tec.*, app.* ");
    query.append(" from testcaseexecutionqueue exq ");
    query.append(" inner join testcase tec on exq.test = tec.test and exq.testcase = tec.testcase ");
    query.append(" inner join application app on tec.application = app.application ");

    String testClause = SqlUtil.generateInClause("exq.test", testList);
    if (!StringUtil.isNullOrEmpty(testClause)) {
        whereClauses.add(testClause);//from   w w  w . j a v  a2 s .c  om
    }

    String applicationClause = SqlUtil.generateInClause("tec.application", applicationList);
    if (!StringUtil.isNullOrEmpty(applicationClause)) {
        whereClauses.add(applicationClause);
    }

    String projectClause = SqlUtil.generateInClause("tec.project", projectList);
    if (!StringUtil.isNullOrEmpty(projectClause)) {
        whereClauses.add(projectClause);
    }
    //test case status: working, fully_implemented, ...
    String tcsClause = SqlUtil.generateInClause("exq.status", tcstatusList);
    if (!StringUtil.isNullOrEmpty(tcsClause)) {
        whereClauses.add(tcsClause);
    }

    //group 
    String groupClause = SqlUtil.generateInClause("tec.group", groupList);
    if (!StringUtil.isNullOrEmpty(groupClause)) {
        whereClauses.add(groupClause);
    }
    //test case active
    String tcactiveClause = SqlUtil.generateInClause("tec.tcactive", tcactiveList);
    if (!StringUtil.isNullOrEmpty(tcactiveClause)) {
        whereClauses.add(tcactiveClause);
    }

    //test case active
    String priorityClause = SqlUtil.generateInClause("tec.Priority", priorityList);
    if (!StringUtil.isNullOrEmpty(priorityClause)) {
        whereClauses.add(priorityClause);
    }

    //target sprint
    String targetsprintClause = SqlUtil.generateInClause("tec.TargetBuild", targetsprintList);
    if (!StringUtil.isNullOrEmpty(targetsprintClause)) {
        whereClauses.add(targetsprintClause);
    }

    //target revision
    String targetrevisionClause = SqlUtil.generateInClause("tec.TargetRev", targetrevisionList);
    if (!StringUtil.isNullOrEmpty(targetrevisionClause)) {
        whereClauses.add(targetrevisionClause);
    }

    //creator
    String creatorClause = SqlUtil.generateInClause("tec.UsrCreated", creatorList);
    if (!StringUtil.isNullOrEmpty(creatorClause)) {
        whereClauses.add(creatorClause);
    }

    //implementer
    String implementerClause = SqlUtil.generateInClause("tec.Implementer", implementerList);
    if (!StringUtil.isNullOrEmpty(implementerClause)) {
        whereClauses.add(implementerClause);
    }

    //build
    String buildClause = SqlUtil.generateInClause("exq.Build", buildList);
    if (!StringUtil.isNullOrEmpty(buildClause)) {
        whereClauses.add(buildClause);
    }
    //revision
    String revisionClause = SqlUtil.generateInClause("exq.Revision", revisionList);
    if (!StringUtil.isNullOrEmpty(revisionClause)) {
        whereClauses.add(revisionClause);
    }
    //environment
    String environmentClause = SqlUtil.generateInClause("exq.Environment", environmentList);
    if (!StringUtil.isNullOrEmpty(environmentClause)) {
        whereClauses.add(environmentClause);
    }
    //country
    String countryClause = SqlUtil.generateInClause("exq.Country", countryList);
    if (!StringUtil.isNullOrEmpty(countryClause)) {
        whereClauses.add(countryClause);
    }
    //browser
    String browserClause = SqlUtil.generateInClause("exq.Browser", browserList);
    if (!StringUtil.isNullOrEmpty(browserClause)) {
        whereClauses.add(browserClause);
    }
    //test case execution
    String tcestatusClause = SqlUtil.generateInClause("exq.ControlStatus", tcestatusList);
    if (!StringUtil.isNullOrEmpty(tcestatusClause)) {
        whereClauses.add(tcestatusClause);
    }

    if (!StringUtil.isNullOrEmpty(system)) {
        whereClauses.add(" app.system like ? ");
    }
    if (!StringUtil.isNullOrEmpty(ip)) {
        whereClauses.add(" exq.IP like ? ");
    }
    if (!StringUtil.isNullOrEmpty(port)) {
        whereClauses.add(" exq.port like ? ");
    }
    if (!StringUtil.isNullOrEmpty(tag)) {
        whereClauses.add(" exq.tag like ? ");
    }
    if (!StringUtil.isNullOrEmpty(browserversion)) {
        whereClauses.add(" exq.browserfullversion like ? ");
    }
    if (!StringUtil.isNullOrEmpty(comment)) {
        whereClauses.add(" exq.comment like ? ");
    }
    if (!StringUtil.isNullOrEmpty(bugid)) {
        whereClauses.add(" tec.BugID like ? ");
    }
    if (!StringUtil.isNullOrEmpty(ticket)) {
        whereClauses.add(" tec.Ticket like ? ");
    }

    if (whereClauses.size() > 0) {
        query.append("where ");
        String joined = StringUtils.join(whereClauses, " and ");
        query.append(joined);
    }

    query.append(" order by exq.ID desc ");
    query.append(" ) as t group by t.test, t.testcase, t.environment, t.browser, t.country");
    Connection connection = this.databaseSpring.connect();

    try {
        PreparedStatement preStat = connection.prepareStatement(query.toString());
        if (testList != null) {
            for (String param : testList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (applicationList != null) {
            for (String param : applicationList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (projectList != null) {
            for (String param : projectList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (tcstatusList != null) {
            for (String param : tcstatusList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (groupList != null) {
            for (String param : groupList) {
                preStat.setString(++paramNumber, param);
            }
        }

        if (tcactiveList != null) {
            for (String param : tcactiveList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (priorityList != null) {
            for (String param : priorityList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (targetsprintList != null) {
            for (String param : targetsprintList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (targetrevisionList != null) {
            for (String param : targetrevisionList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (creatorList != null) {
            for (String param : creatorList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (implementerList != null) {
            for (String param : implementerList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (buildList != null) {
            for (String param : buildList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (revisionList != null) {
            for (String param : revisionList) {
                preStat.setString(++paramNumber, param);
            }
        }
        //environment
        if (environmentList != null) {
            for (String param : environmentList) {
                preStat.setString(++paramNumber, param);
            }
        }
        //country
        if (countryList != null) {
            for (String param : countryList) {
                preStat.setString(++paramNumber, param);
            }
        }
        //browser            
        if (browserList != null) {
            for (String param : browserList) {
                preStat.setString(++paramNumber, param);
            }
        }
        //controlstatus
        if (tcestatusList != null) {
            for (String param : tcestatusList) {
                preStat.setString(++paramNumber, param);
            }
        }

        if (!StringUtil.isNullOrEmpty(system)) {
            preStat.setString(++paramNumber, system);
        }

        if (!StringUtil.isNullOrEmpty(ip)) {
            preStat.setString(++paramNumber, "%" + ip + "%");
        }
        if (!StringUtil.isNullOrEmpty(port)) {
            preStat.setString(++paramNumber, "%" + port + "%");
        }
        if (!StringUtil.isNullOrEmpty(tag)) {
            preStat.setString(++paramNumber, "%" + tag + "%");
        }
        if (!StringUtil.isNullOrEmpty(browserversion)) {
            preStat.setString(++paramNumber, "%" + browserversion + "%");
        }
        if (!StringUtil.isNullOrEmpty(comment)) {
            preStat.setString(++paramNumber, "%" + comment + "%");
        }
        if (!StringUtil.isNullOrEmpty(bugid)) {
            preStat.setString(++paramNumber, "%" + bugid + "%");
        }
        if (!StringUtil.isNullOrEmpty(ticket)) {
            preStat.setString(++paramNumber, "%" + ticket + "%");
        }

        try {
            ResultSet resultSet = preStat.executeQuery();
            try {
                while (resultSet.next()) {
                    tceList.add(this.loadWithDependenciesFromResultSet(resultSet));
                }
                if (tceList.isEmpty()) {
                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
                } else {
                    msg.setDescription(msg.getDescription().replace("%ITEM%", "TestCaseExecutionInQueue")
                            .replace("%OPERATION%", "SELECT"));
                }

            } catch (SQLException exception) {
                MyLogger.log(TestCaseExecutionInQueueDAO.class.getName(), Level.ERROR,
                        "Unable to execute query : " + exception.toString());
                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%",
                        "Unable to retrieve the list of entries!"));
                tceList.clear();
            } catch (FactoryCreationException ex) {
                MyLogger.log(TestCaseExecutionInQueueDAO.class.getName(), Level.ERROR,
                        "Unable to execute query : " + ex.toString());
                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%",
                        "Unable to retrieve the list of entries!"));
                tceList.clear();
            } finally {
                if (resultSet != null) {
                    resultSet.close();
                }
            }
        } catch (SQLException ex) {
            MyLogger.log(TestCaseExecutionInQueueDAO.class.getName(), Level.ERROR,
                    "Unable to execute query : " + ex.toString());
            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
            msg.setDescription(
                    msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
        } finally {
            if (preStat != null) {
                preStat.close();
            }
        }
    } catch (SQLException ex) {
        MyLogger.log(TestCaseExecutionInQueueDAO.class.getName(), Level.WARN, ex.toString());
        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
        msg.setDescription(
                msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException ex) {
            MyLogger.log(TestCaseExecutionInQueueDAO.class.getName(), Level.ERROR,
                    "Unable to execute query : " + ex.toString());
        }
    }
    answer.setTotalRows(tceList.size());
    answer.setDataList(tceList);
    answer.setResultMessage(msg);
    return answer;
}

From source file:org.cerberus.crud.dao.impl.TestCaseExecutionDAO.java

@Override
public AnswerList readBySystemByVarious(String system, List<String> testList, List<String> applicationList,
        List<String> projectList, List<String> tcstatusList, List<String> groupList, List<String> tcactiveList,
        List<String> priorityList, List<String> targetsprintList, List<String> targetrevisionList,
        List<String> creatorList, List<String> implementerList, List<String> buildList,
        List<String> revisionList, List<String> environmentList, List<String> countryList,
        List<String> browserList, List<String> tcestatusList, String ip, String port, String tag,
        String browserversion, String comment, String bugid, String ticket) {

    AnswerList answer = new AnswerList();
    MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
    List<TestCaseExecution> tceList = new ArrayList<TestCaseExecution>();
    List<String> whereClauses = new LinkedList<String>();

    StringBuilder query = new StringBuilder();

    int paramNumber = 0;

    query.append(" select t.ID as statusExecutionID, t.* from ( ");
    query.append(" select exe.*, tec.*, app.* ");
    query.append(" from testcaseexecution exe ");
    query.append(" inner join testcase tec on exe.test = tec.test and exe.testcase = tec.testcase ");
    query.append(" inner join application app on exe.application = app.application ");

    String testClause = SqlUtil.generateInClause("exe.test", testList);
    if (!StringUtil.isNullOrEmpty(testClause)) {
        whereClauses.add(testClause);/* w w w.j  a  v a 2 s.co m*/
    }

    String applicationClause = SqlUtil.generateInClause("exe.application", applicationList);
    if (!StringUtil.isNullOrEmpty(applicationClause)) {
        whereClauses.add(applicationClause);
    }

    String projectClause = SqlUtil.generateInClause("tec.project", projectList);
    if (!StringUtil.isNullOrEmpty(projectClause)) {
        whereClauses.add(projectClause);
    }
    //test case status: working, fully_implemented, ...
    String tcsClause = SqlUtil.generateInClause("exe.status", tcstatusList);
    if (!StringUtil.isNullOrEmpty(tcsClause)) {
        whereClauses.add(tcsClause);
    }

    //group 
    String groupClause = SqlUtil.generateInClause("tec.group", groupList);
    if (!StringUtil.isNullOrEmpty(groupClause)) {
        whereClauses.add(groupClause);
    }
    //test case active
    String tcactiveClause = SqlUtil.generateInClause("tec.tcactive", tcactiveList);
    if (!StringUtil.isNullOrEmpty(tcactiveClause)) {
        whereClauses.add(tcactiveClause);
    }

    //test case active
    String priorityClause = SqlUtil.generateInClause("tec.Priority", priorityList);
    if (!StringUtil.isNullOrEmpty(priorityClause)) {
        whereClauses.add(priorityClause);
    }

    //target sprint
    String targetsprintClause = SqlUtil.generateInClause("tec.TargetBuild", targetsprintList);
    if (!StringUtil.isNullOrEmpty(targetsprintClause)) {
        whereClauses.add(targetsprintClause);
    }

    //target revision
    String targetrevisionClause = SqlUtil.generateInClause("tec.TargetRev", targetrevisionList);
    if (!StringUtil.isNullOrEmpty(targetrevisionClause)) {
        whereClauses.add(targetrevisionClause);
    }

    //creator
    String creatorClause = SqlUtil.generateInClause("tec.UsrCreated", creatorList);
    if (!StringUtil.isNullOrEmpty(creatorClause)) {
        whereClauses.add(creatorClause);
    }

    //implementer
    String implementerClause = SqlUtil.generateInClause("tec.Implementer", implementerList);
    if (!StringUtil.isNullOrEmpty(implementerClause)) {
        whereClauses.add(implementerClause);
    }

    //build
    String buildClause = SqlUtil.generateInClause("exe.Build", buildList);
    if (!StringUtil.isNullOrEmpty(buildClause)) {
        whereClauses.add(buildClause);
    }
    //revision
    String revisionClause = SqlUtil.generateInClause("exe.Revision", revisionList);
    if (!StringUtil.isNullOrEmpty(revisionClause)) {
        whereClauses.add(revisionClause);
    }
    //environment
    String environmentClause = SqlUtil.generateInClause("exe.Environment", environmentList);
    if (!StringUtil.isNullOrEmpty(environmentClause)) {
        whereClauses.add(environmentClause);
    }
    //country
    String countryClause = SqlUtil.generateInClause("exe.Country", countryList);
    if (!StringUtil.isNullOrEmpty(countryClause)) {
        whereClauses.add(countryClause);
    }
    //browser
    String browserClause = SqlUtil.generateInClause("exe.Browser", browserList);
    if (!StringUtil.isNullOrEmpty(browserClause)) {
        whereClauses.add(browserClause);
    }
    //test case execution
    String tcestatusClause = SqlUtil.generateInClause("exe.ControlStatus", tcestatusList);
    if (!StringUtil.isNullOrEmpty(tcestatusClause)) {
        whereClauses.add(tcestatusClause);
    }

    if (!StringUtil.isNullOrEmpty(system)) {
        whereClauses.add(" app.system like ? ");
    }
    if (!StringUtil.isNullOrEmpty(ip)) {
        whereClauses.add(" exe.IP like ? ");
    }
    if (!StringUtil.isNullOrEmpty(port)) {
        whereClauses.add(" exe.port like ? ");
    }
    if (!StringUtil.isNullOrEmpty(tag)) {
        whereClauses.add(" exe.tag like ? ");
    }
    if (!StringUtil.isNullOrEmpty(browserversion)) {
        whereClauses.add(" exe.browserfullversion like ? ");
    }
    if (!StringUtil.isNullOrEmpty(comment)) {
        whereClauses.add(" exe.comment like ? ");
    }
    if (!StringUtil.isNullOrEmpty(bugid)) {
        whereClauses.add(" tec.BugID like ? ");
    }
    if (!StringUtil.isNullOrEmpty(ticket)) {
        whereClauses.add(" tec.Ticket like ? ");
    }

    if (whereClauses.size() > 0) {
        query.append("where ");
        String joined = StringUtils.join(whereClauses, " and ");
        query.append(joined);
    }

    query.append(" order by exe.ID desc ");
    query.append(" ) as t group by t.test, t.testcase, t.environment, t.browser, t.country");
    Connection connection = this.databaseSpring.connect();

    try {
        PreparedStatement preStat = connection.prepareStatement(query.toString());
        if (testList != null) {
            for (String param : testList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (applicationList != null) {
            for (String param : applicationList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (projectList != null) {
            for (String param : projectList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (tcstatusList != null) {
            for (String param : tcstatusList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (groupList != null) {
            for (String param : groupList) {
                preStat.setString(++paramNumber, param);
            }
        }

        if (tcactiveList != null) {
            for (String param : tcactiveList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (priorityList != null) {
            for (String param : priorityList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (targetsprintList != null) {
            for (String param : targetsprintList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (targetrevisionList != null) {
            for (String param : targetrevisionList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (creatorList != null) {
            for (String param : creatorList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (implementerList != null) {
            for (String param : implementerList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (buildList != null) {
            for (String param : buildList) {
                preStat.setString(++paramNumber, param);
            }
        }
        if (revisionList != null) {
            for (String param : revisionList) {
                preStat.setString(++paramNumber, param);
            }
        }
        //environment
        if (environmentList != null) {
            for (String param : environmentList) {
                preStat.setString(++paramNumber, param);
            }
        }
        //country
        if (countryList != null) {
            for (String param : countryList) {
                preStat.setString(++paramNumber, param);
            }
        }
        //browser            
        if (browserList != null) {
            for (String param : browserList) {
                preStat.setString(++paramNumber, param);
            }
        }
        //controlstatus
        if (tcestatusList != null) {
            for (String param : tcestatusList) {
                preStat.setString(++paramNumber, param);
            }
        }

        if (!StringUtil.isNullOrEmpty(system)) {
            preStat.setString(++paramNumber, system);
        }

        if (!StringUtil.isNullOrEmpty(ip)) {
            preStat.setString(++paramNumber, "%" + ip + "%");
        }
        if (!StringUtil.isNullOrEmpty(port)) {
            preStat.setString(++paramNumber, "%" + port + "%");
        }
        if (!StringUtil.isNullOrEmpty(tag)) {
            preStat.setString(++paramNumber, "%" + tag + "%");
        }
        if (!StringUtil.isNullOrEmpty(browserversion)) {
            preStat.setString(++paramNumber, "%" + browserversion + "%");
        }
        if (!StringUtil.isNullOrEmpty(comment)) {
            preStat.setString(++paramNumber, "%" + comment + "%");
        }
        if (!StringUtil.isNullOrEmpty(bugid)) {
            preStat.setString(++paramNumber, "%" + bugid + "%");
        }
        if (!StringUtil.isNullOrEmpty(ticket)) {
            preStat.setString(++paramNumber, "%" + ticket + "%");
        }

        try {
            ResultSet resultSet = preStat.executeQuery();
            try {
                while (resultSet.next()) {
                    tceList.add(loadWithDependenciesFromResultSet(resultSet));
                }
                if (tceList.isEmpty()) {
                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
                } else {
                    msg.setDescription(msg.getDescription().replace("%ITEM%", "TestCaseExecution")
                            .replace("%OPERATION%", "SELECT"));
                }

            } catch (SQLException exception) {
                MyLogger.log(TestCaseExecutionDAO.class.getName(), Level.ERROR,
                        "Unable to execute query : " + exception.toString());
                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%",
                        "Unable to retrieve the list of entries!"));
                tceList.clear();
            } finally {
                if (resultSet != null) {
                    resultSet.close();
                }
            }
        } catch (SQLException ex) {
            MyLogger.log(TestCaseExecutionDAO.class.getName(), Level.ERROR,
                    "Unable to execute query : " + ex.toString());
            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
            msg.setDescription(
                    msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
        } finally {
            if (preStat != null) {
                preStat.close();
            }
        }
    } catch (SQLException ex) {
        MyLogger.log(TestCaseExecutionDAO.class.getName(), Level.WARN, ex.toString());
        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
        msg.setDescription(
                msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException ex) {
            MyLogger.log(TestCaseExecutionDAO.class.getName(), Level.ERROR,
                    "Unable to execute query : " + ex.toString());
        }
    }
    answer.setTotalRows(tceList.size());
    answer.setDataList(tceList);
    answer.setResultMessage(msg);
    return answer;
}

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

private void doTestSelectAll(String tableName, int maxRows, int fetchSize) throws Exception {
    boolean isPartitionTable = tableName.equals(partitionedTableName);

    Statement stmt = con.createStatement();
    if (maxRows >= 0) {
        stmt.setMaxRows(maxRows);/*from  w  w  w .  j av  a2s .  co m*/
    }
    if (fetchSize > 0) {
        stmt.setFetchSize(fetchSize);
        assertEquals(fetchSize, stmt.getFetchSize());
    }

    // JDBC says that 0 means return all, which is the default
    int expectedMaxRows = maxRows < 1 ? 0 : maxRows;

    assertNotNull("Statement is null", stmt);
    assertEquals("Statement max rows not as expected", expectedMaxRows, stmt.getMaxRows());
    assertFalse("Statement should not be closed", stmt.isClosed());

    ResultSet res;

    // run some queries
    res = stmt.executeQuery("select * from " + tableName);
    assertNotNull("ResultSet is null", res);
    assertTrue("getResultSet() not returning expected ResultSet", res == stmt.getResultSet());
    assertEquals("get update count not as expected", -1, stmt.getUpdateCount());
    int i = 0;

    ResultSetMetaData meta = res.getMetaData();
    int expectedColCount = isPartitionTable ? 3 : 2;
    assertEquals("Unexpected column count", expectedColCount, meta.getColumnCount());

    boolean moreRow = res.next();
    while (moreRow) {
        try {
            i++;
            assertEquals(res.getInt(1), res.getInt(tableName + ".under_col"));
            assertEquals(res.getInt(1), res.getInt("under_col"));
            assertEquals(res.getString(1), res.getString(tableName + ".under_col"));
            assertEquals(res.getString(1), res.getString("under_col"));
            assertEquals(res.getString(2), res.getString(tableName + ".value"));
            assertEquals(res.getString(2), res.getString("value"));
            if (isPartitionTable) {
                assertEquals(res.getString(3), partitionedColumnValue);
                assertEquals(res.getString(3), res.getString(partitionedColumnName));
                assertEquals(res.getString(3), res.getString(tableName + "." + partitionedColumnName));
            }
            assertFalse("Last result value was not null", res.wasNull());
            assertNull("No warnings should be found on ResultSet", res.getWarnings());
            res.clearWarnings(); // verifying that method is supported

            // System.out.println(res.getString(1) + " " + res.getString(2));
            assertEquals("getInt and getString don't align for the same result value",
                    String.valueOf(res.getInt(1)), res.getString(1));
            assertEquals("Unexpected result found", "val_" + res.getString(1), res.getString(2));
            moreRow = res.next();
        } catch (SQLException e) {
            System.out.println(e.toString());
            e.printStackTrace();
            throw new Exception(e.toString());
        }
    }

    // supposed to get 500 rows if maxRows isn't set
    int expectedRowCount = maxRows > 0 ? maxRows : 500;
    assertEquals("Incorrect number of rows returned", expectedRowCount, i);

    // should have no more rows
    assertEquals(false, moreRow);

    assertNull("No warnings should be found on statement", stmt.getWarnings());
    stmt.clearWarnings(); // verifying that method is supported

    assertNull("No warnings should be found on connection", con.getWarnings());
    con.clearWarnings(); // verifying that method is supported

    stmt.close();
    assertTrue("Statement should be closed", stmt.isClosed());
}

From source file:org.agnitas.backend.Data.java

/**
 * query all basic information about this mailing
 * @param status_id the reference to the mailing
 *//*from  ww w.j  a va 2s .  com*/
private void queryMailingInformations(String status_id) throws Exception {
    checkDatabase();
    try {
        String[] sdetail = status_id.split(":", 2);

        if (sdetail.length == 2) {
            setupMailingInformations(sdetail[0], sdetail[1]);
        } else {
            Map<String, Object> row;
            int bs, st;
            int genstat;
            String moreCols;
            String query;

            maildrop_status_id = Long.parseLong(status_id);

            moreCols = moreStatusColumns();
            query = "SELECT company_id, mailing_id, status_field, senddate, step, blocksize, genstatus";
            if (moreCols != null) {
                query += ", " + moreCols;
            }
            query += " FROM maildrop_status_tbl WHERE status_id = :statusID";
            row = dbase.querys(query, "statusID", maildrop_status_id);
            company_id = dbase.asLong(row.get("company_id"));
            mailing_id = dbase.asLong(row.get("mailing_id"));
            status_field = dbase.asString(row.get("status_field"));
            sendtimestamp = (Timestamp) row.get("senddate");
            st = dbase.asInt(row.get("step"));
            bs = dbase.asInt(row.get("blocksize"));
            genstat = dbase.asInt(row.get("genstatus"));
            moreStatusColumnsParse(row);
            moreStatusColumnsPostParse();
            if (status_field.equals("C"))
                status_field = "E";
            if (bs > 0)
                setBlockSize(bs);
            setStepping(st);
            if (genstat != 1)
                throw new Exception("Generation state is not 1, but " + genstat);
            if (isAdminMailing() || isTestMailing() || isWorldMailing() || isRuleMailing()
                    || isOnDemandMailing()) {
                setGenerationStatus(1, 2);
            }
        }
        if (mailing_id > 0) {
            retreiveMailingInformation();

            if (targetExpression != null) {
                StringBuffer buf = new StringBuffer();
                int tlen = targetExpression.length();

                for (int n = 0; n < tlen; ++n) {
                    char ch = targetExpression.charAt(n);

                    if ((ch == '(') || (ch == ')')) {
                        buf.append(ch);
                    } else if ((ch == '&') || (ch == '|')) {
                        if (ch == '&')
                            buf.append(" AND");
                        else
                            buf.append(" OR");
                        while (((n + 1) < tlen) && (targetExpression.charAt(n + 1) == ch))
                            ++n;
                    } else if (ch == '!') {
                        buf.append(" NOT");
                    } else if ("0123456789".indexOf(ch) != -1) {
                        int newn = n;
                        long tid = 0;
                        int pos;
                        Target temp;

                        while ((n < tlen) && ((pos = "0123456789".indexOf(ch)) != -1)) {
                            newn = n;
                            tid *= 10;
                            tid += pos;
                            ++n;
                            if (n < tlen)
                                ch = targetExpression.charAt(n);
                            else
                                ch = '\0';
                        }
                        n = newn;
                        temp = getTarget(tid);
                        if ((temp != null) && temp.valid())
                            buf.append(" (" + temp.sql + ")");
                    }
                }
                if (buf.length() >= 3)
                    subselect = buf.toString();
            }
            retreiveMediaInformation();
        }
        if (subselect == null) {
            if (isRuleMailing()) {
                try {
                    dbase.update("DELETE FROM maildrop_status_tbl WHERE status_id = :statusID", "statusID",
                            maildrop_status_id);
                } catch (SQLException e) {
                    logging(Log.ERROR, "init", "Failed to disable rule based mailing: " + e.toString());
                }
                throw new Exception("Missing target: Rule based mailing generation aborted and disabled");
            } else if (isOnDemandMailing()) {
                try {
                    setGenerationStatus(0, 4);
                } catch (Exception e) {
                    logging(Log.ERROR, "init", "Failed to set genreation status: " + e.toString());
                }
                throw new Exception(
                        "Missing target: On Demand mailing generation aborted and left in undefined condition");
            }
        }
        if ((encoding == null) || (encoding.length() == 0))
            encoding = defaultEncoding;
        if ((charset == null) || (charset.length() == 0))
            charset = defaultCharset;
        //
        // get all possible URLs that should be replaced
        List<Map<String, Object>> rc;

        URLlist = new Vector<URL>();
        if (mailing_id > 0) {
            rc = dbase.query("SELECT url_id, full_url, " + dbase.measureType + extraURLTableColumns()
                    + " FROM rdir_url_tbl " + "WHERE company_id = :companyID AND mailing_id = :mailingID"
                    + extraURLTableClause(), "companyID", company_id, "mailingID", mailing_id);
            for (int n = 0; n < rc.size(); ++n) {
                Map<String, Object> row = rc.get(n);
                long id = dbase.asLong(row.get("url_id"));
                String dest = dbase.asString(row.get("full_url"));
                long usage = dbase.asLong(row.get(dbase.measureRepr));

                if (usage != 0) {
                    URL url = new URL(id, dest, usage);

                    extraURLTableGetColumns(url, row);
                    URLlist.addElement(url);
                }
            }
        }
        urlcount = URLlist.size();
        getURLDetails();

        //
        // and now try to determinate the layout of the
        // customer table
        getTableLayout("customer_" + company_id + "_tbl", null);

        Hashtable<String, Column> cmap = new Hashtable<String, Column>();

        for (int n = 0; n < lcount; ++n) {
            Column c = layout.get(n);

            cmap.put(c.name.toLowerCase(), c);
        }

        rc = dbase.query("SELECT col_name, shortname FROM customer_field_tbl WHERE company_id = :companyID",
                "companyID", company_id);
        for (int n = 0; n < rc.size(); ++n) {
            Map<String, Object> row = rc.get(n);
            String column = dbase.asString(row.get("col_name"));

            if (column != null) {
                Column c = cmap.get(column);

                if (c != null) {
                    c.setAlias(dbase.asString(row.get("shortname")));
                }
            }
        }

        retreiveCompanyInfo();
        if (rdirDomain != null) {
            if (profileURL == null)
                profileURL = rdirDomain + profileTag;
            if (unsubscribeURL == null)
                unsubscribeURL = rdirDomain + unsubscribeTag;
            if (autoURL == null)
                autoURL = rdirDomain + autoTag;
            if (onePixelURL == null)
                onePixelURL = rdirDomain + onePixelTag;
        }
    } catch (Exception e) {
        logging(Log.ERROR, "init", "Error in quering initial data: " + e);
        throw new Exception("Database error/initial query: " + e, e);
    }
}

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

/**
 * Test the cancellation of a query that is running.
 * We spawn 2 threads - one running the query and
 * the other attempting to cancel./*from w  w w. j  a  va  2s  .c  o m*/
 * We're using a dummy udf to simulate a query,
 * that runs for a sufficiently long time.
 * @throws Exception
 */
@Test
public void testQueryCancel() throws Exception {
    String udfName = SleepUDF.class.getName();
    Statement stmt1 = con.createStatement();
    stmt1.execute("create temporary function sleepUDF as '" + udfName + "'");
    stmt1.close();
    final Statement stmt = con.createStatement();
    // Thread executing the query
    Thread tExecute = new Thread(new Runnable() {
        @Override
        public void run() {
            try {
                System.out.println("Executing query: ");
                stmt.executeQuery(
                        "select sleepUDF(t1.under_col) as u0, t1.under_col as u1, " + "t2.under_col as u2 from "
                                + tableName + "t1 join " + tableName + " t2 on t1.under_col = t2.under_col");
                fail("Expecting SQLException");
            } catch (SQLException e) {
                // This thread should throw an exception
                assertNotNull(e);
                System.out.println(e.toString());
            }
        }
    });
    // Thread cancelling the query
    Thread tCancel = new Thread(new Runnable() {
        @Override
        public void run() {
            try {
                Thread.sleep(1000);
                System.out.println("Cancelling query: ");
                stmt.cancel();
            } catch (Exception e) {
                // No-op
            }
        }
    });
    tExecute.start();
    tCancel.start();
    tExecute.join();
    tCancel.join();
    stmt.close();
}

From source file:org.etudes.component.app.jforum.JforumDataServiceImpl.java

/**
 * update topic/*from ww w. jav a  2s  .  c o m*/
 * @param connection connection
 * @param createdTopicId topic id
 * @param createdPostId post id
 */
private void updateTopic(Connection connection, int topicId, int postId) {
    String topicUpdate = "UPDATE jforum_topics SET topic_first_post_id = ?, topic_last_post_id = ? WHERE topic_id = ?";
    try {
        PreparedStatement p = connection.prepareStatement(topicUpdate);

        p.setInt(1, postId);
        p.setInt(2, postId);
        p.setInt(3, topicId);
        p.executeUpdate();

        p.close();
    } catch (SQLException e) {
        if (logger.isErrorEnabled())
            logger.error("updateTopic():Error while updating post : " + e.toString());
        e.printStackTrace();
    }

}

From source file:org.etudes.component.app.jforum.JforumDataServiceImpl.java

/**
 * create post text/* w  w  w  . j a  v  a2s  . c  o  m*/
 * @param connection connection   
 * @param postId post id
 * @param postText post text
 * @param postSubject post subject
 */
private void addPostText(Connection connection, int postId, String postText, String postSubject) {
    String addNewPostText = "INSERT INTO jforum_posts_text ( post_id, post_text, post_subject ) "
            + "VALUES (?, ?, ?)";
    try {
        PreparedStatement p = connection.prepareStatement(addNewPostText);
        p.setInt(1, postId);
        p.setString(2, postText);
        p.setString(3, postSubject);
        p.executeUpdate();
        p.close();
    } catch (SQLException e) {
        if (logger.isErrorEnabled())
            logger.error("addPostText():Error while creating post text : " + e.toString());
        e.printStackTrace();
    }

}

From source file:org.etudes.component.app.jforum.JforumDataServiceImpl.java

/**
 * create post/*from w  w w.j ava  2  s . co m*/
 * @param connection
 * @param exisPostId
 * @param topicId
 * @param forumId
 * @param userId
 * @return
 */
private int createPost(Connection connection, int exisPostId, int topicId, int forumId, int userId) {
    if (logger.isDebugEnabled())
        logger.debug("Entering createPost......");
    //use exisPostId for post properties

    String addNewPost = null;
    PreparedStatement p = null;
    ResultSet rs = null;
    int postId = -1;

    try {
        String exisPostDetails = "SELECT post_id, topic_id, forum_id, user_id, post_time, "
                + "enable_bbcode, enable_html, enable_smilies, enable_sig,"
                + "attach , need_moderate FROM jforum_posts WHERE post_id = ?";
        PreparedStatement exisPostDetailsStmnt = connection.prepareStatement(exisPostDetails);
        exisPostDetailsStmnt.setInt(1, exisPostId);
        ResultSet rsexisPostDetails = exisPostDetailsStmnt.executeQuery();

        int enableBbcode = 0;
        int enableHtml = 0;
        int enableSmilies = 0;
        int enableSig = 0;
        int attach = 0;
        int needModerate = 0;

        if (rsexisPostDetails.next()) {
            enableBbcode = rsexisPostDetails.getInt("enable_bbcode");
            enableHtml = rsexisPostDetails.getInt("enable_html");
            enableSmilies = rsexisPostDetails.getInt("enable_smilies");
            enableSig = rsexisPostDetails.getInt("enable_sig");
            attach = rsexisPostDetails.getInt("attach");
            needModerate = rsexisPostDetails.getInt("need_moderate");
        }
        exisPostDetailsStmnt.close();
        rsexisPostDetails.close();

        if (sqlService.getVendor().equals("oracle")) {
            addNewPost = "INSERT INTO jforum_posts (post_id, topic_id, forum_id, user_id, "
                    + "post_time, poster_ip, enable_bbcode, enable_html, enable_smilies, "
                    + "enable_sig, post_edit_time, attach, need_moderate) "
                    + "VALUES (jforum_posts_seq.nextval, ?, ?, ?, SYSDATE, ?, ?, ?, ?,?, SYSDATE, ?, ?)";

            p = connection.prepareStatement(addNewPost);
            p.setInt(1, topicId);
            p.setInt(2, forumId);
            p.setLong(3, userId);
            //p.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
            UsageSession usageSession = UsageSessionService.getSession();
            p.setString(4, usageSession.getIpAddress());
            p.setInt(5, enableBbcode);
            p.setInt(6, enableHtml);
            p.setInt(7, enableSmilies);
            p.setInt(8, enableSig);
            p.setInt(9, attach);
            p.setInt(10, needModerate);
            p.executeUpdate();

            p.close();

            String lastGeneratedTopidId = "SELECT jforum_posts_seq.currval FROM DUAL";
            p = connection.prepareStatement(lastGeneratedTopidId);
            rs = p.executeQuery();

            if (rs.next()) {
                postId = rs.getInt(1);
            }

            rs.close();
            p.close();
        } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) {
            addNewPost = "INSERT INTO jforum_posts (topic_id, forum_id, user_id, "
                    + "post_time, poster_ip, enable_bbcode, enable_html, enable_smilies, "
                    + "enable_sig, post_edit_time, attach, need_moderate) "
                    + "VALUES (?, ?, ?, NOW(), ?, ?, ?, ?, ?, NOW(), ?, ?)";
            p = connection.prepareStatement(addNewPost, Statement.RETURN_GENERATED_KEYS);
            p.setInt(1, topicId);
            p.setInt(2, forumId);
            p.setLong(3, userId);
            //p.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
            UsageSession usageSession = UsageSessionService.getSession();
            p.setString(4, usageSession.getIpAddress());
            p.setInt(5, enableBbcode);
            p.setInt(6, enableHtml);
            p.setInt(7, enableSmilies);
            p.setInt(8, enableSig);
            p.setInt(9, attach);
            p.setInt(10, needModerate);
            p.executeUpdate();

            rs = p.getGeneratedKeys();
            if (rs.next()) {
                postId = rs.getInt(1);

                rs.close();
                p.close();
            }
        }

    } catch (SQLException e) {
        if (logger.isErrorEnabled())
            logger.error("createPost():Error while creating post : " + e.toString());
        e.printStackTrace();
    }
    if (logger.isDebugEnabled())
        logger.debug("Exiting createPost......");

    return postId;
}