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.TestBatteryDAO.java

@Override
public TestBattery findTestBatteryByTestBatteryName(String testBattery) throws CerberusException {
    boolean throwEx = false;
    final String query = "SELECT * FROM testbattery t WHERE t.testbattery = ?";

    TestBattery testBatteryResult = null;
    Connection connection = this.databaseSpring.connect();
    try {/*from   w ww. j  av  a2  s. c o  m*/
        PreparedStatement preStat = connection.prepareStatement(query);
        preStat.setString(1, testBattery);
        try {
            ResultSet resultSet = preStat.executeQuery();
            try {
                if (resultSet.first()) {
                    testBatteryResult = this.loadFromResultSet(resultSet);
                }
            } catch (SQLException exception) {
                MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR,
                        "Unable to execute query : " + exception.toString());
            } finally {
                resultSet.close();
            }
        } catch (SQLException exception) {
            MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR,
                    "Unable to execute query : " + exception.toString());
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR,
                "Unable to execute query : " + exception.toString());
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            MyLogger.log(TestBatteryDAO.class.getName(), Level.WARN, e.toString());
        }
    }
    if (throwEx) {
        throw new CerberusException(new MessageGeneral(MessageGeneralEnum.NO_DATA_FOUND));
    }
    return testBatteryResult;
}

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

@Override
public List<TestBattery> findAll() throws CerberusException {
    boolean throwEx = false;
    final String query = "SELECT * FROM testbattery t order by testbattery asc, description asc";

    List<TestBattery> testBatteryList = new ArrayList<TestBattery>();
    Connection connection = this.databaseSpring.connect();
    try {/*from  ww w.  j  a  v a 2 s .  c  o  m*/
        PreparedStatement preStat = connection.prepareStatement(query);
        try {
            ResultSet resultSet = preStat.executeQuery();
            try {
                while (resultSet.next()) {
                    testBatteryList.add(this.loadFromResultSet(resultSet));
                }
            } catch (SQLException exception) {
                MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR,
                        "Unable to execute query : " + exception.toString());
                testBatteryList = null;
            } finally {
                resultSet.close();
            }
        } catch (SQLException exception) {
            MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR,
                    "Unable to execute query : " + exception.toString());
            testBatteryList = null;
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR,
                "Unable to execute query : " + exception.toString());
        testBatteryList = null;
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            MyLogger.log(TestBatteryDAO.class.getName(), Level.WARN, e.toString());
        }
    }
    if (throwEx) {
        throw new CerberusException(new MessageGeneral(MessageGeneralEnum.NO_DATA_FOUND));
    }
    return testBatteryList;
}

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

@Override
public AnswerList readByCriteria(int start, int amount, String colName, String dir, String searchTerm,
        String individualSearch) {
    AnswerList response = new AnswerList();
    MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
    List<TestBattery> testBatteryList = new ArrayList<TestBattery>();
    StringBuilder searchSQL = new StringBuilder();

    StringBuilder query = new StringBuilder();
    //SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit clauses that 
    //were applied -- used for pagination p
    query.append("SELECT SQL_CALC_FOUND_ROWS * FROM testbattery ");

    searchSQL.append(" where 1=1 ");

    if (!StringUtil.isNullOrEmpty(searchTerm)) {
        searchSQL.append(" and (`testbatteryid` like ?");
        searchSQL.append(" or `testbattery` like ?");
        searchSQL.append(" or `description` like ?)");
    }// www.j a va  2  s.c  o  m
    if (!StringUtil.isNullOrEmpty(individualSearch)) {
        searchSQL.append(" and ( ? )");
    }
    query.append(searchSQL);

    if (!StringUtil.isNullOrEmpty(colName)) {
        query.append("order by `").append(colName).append("` ").append(dir);
    }
    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);
    }

    Connection connection = this.databaseSpring.connect();
    try {
        PreparedStatement preStat = connection.prepareStatement(query.toString());
        try {
            int i = 1;
            if (!Strings.isNullOrEmpty(searchTerm)) {
                preStat.setString(i++, "%" + searchTerm + "%");
                preStat.setString(i++, "%" + searchTerm + "%");
                preStat.setString(i++, "%" + searchTerm + "%");
            }
            if (!StringUtil.isNullOrEmpty(individualSearch)) {
                preStat.setString(i++, individualSearch);
            }

            ResultSet resultSet = preStat.executeQuery();
            try {
                //gets the data
                while (resultSet.next()) {
                    testBatteryList.add(this.loadFromResultSet(resultSet));
                }

                //get the total number of rows
                resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
                int nrTotalRows = 0;

                if (resultSet != null && resultSet.next()) {
                    nrTotalRows = resultSet.getInt(1);
                }

                if (testBatteryList.size() >= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain. That means that we may miss some lines in the resultList.
                    LOG.error("Partial Result in the query.");
                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%",
                            "Maximum row reached : " + MAX_ROW_SELECTED));
                    response = new AnswerList(testBatteryList, nrTotalRows);
                } else if (testBatteryList.size() <= 0) {
                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
                    response = new AnswerList(testBatteryList, nrTotalRows);
                } else {
                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
                    msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME)
                            .replace("%OPERATION%", "SELECT"));
                    response = new AnswerList(testBatteryList, nrTotalRows);
                }

            } catch (SQLException exception) {
                LOG.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!"));

            } finally {
                if (resultSet != null) {
                    resultSet.close();
                }
            }

        } catch (SQLException exception) {
            LOG.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!"));
        } finally {
            if (preStat != null) {
                preStat.close();
            }
        }

    } catch (SQLException exception) {
        LOG.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!"));
    } finally {
        try {
            if (!this.databaseSpring.isOnTransaction()) {
                if (connection != null) {
                    connection.close();
                }
            }
        } catch (SQLException exception) {
            LOG.warn("Unable to close connection : " + exception.toString());
        }
    }

    response.setResultMessage(msg);
    return response;
}

From source file:com.mysql.stresstool.RunnableQuerySelectPCH.java

/**
 * When an object implementing interface <code>Runnable</code> is used to create a thread, starting the thread causes the object's <code>run</code> method to be called in
 * that separately executing thread./*from   ww  w  .  j av a2s .c o m*/
 *
 * @todo Implement this java.lang.Runnable method
 */
public void run() {

    Connection conn = createConnection();
    {
        SoftReference sf = new SoftReference(createConnection());
        conn = (Connection) sf.get();
    }

    if (conn != null) {
        ThreadInfo thInfo;
        thInfo = new ThreadInfo();
        thInfo.setId(this.ID);
        thInfo.setType("select");
        active = true;
        thInfo.setStatusActive(this.isActive());
        StressTool.setInfoSelect(this.ID, thInfo);

        try {
            Statement stmt = null;
            ResultSet rs = null;
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            PreparedStatement pstmt;
            long execTime = 0;
            int repeat = 0;
            int sqlParameterNumbers = 0;

            long threadTimeStart = System.currentTimeMillis();

            if (this.sqlQuery != null && !this.sqlQuery.equals("")) {
                try {

                    pstmt = conn.prepareStatement(sqlQuery);
                    sqlParameterNumbers = pstmt.getParameterMetaData().getParameterCount();

                } catch (SQLException sqlex) {
                    sqlex.printStackTrace();
                    conn.close();
                    return;
                }
            } else {
                pstmt = null;
            }

            String[][] sqlParameterValues;

            if (this.sqlQuery == null || this.sqlQuery.equals("")) {
                pkRange = new PrimaryKeyRangeDefiner(repeatNumber);
                pkRange.setLastResetLoop(0);
                pkRange = setPkRange(conn, pkRange);

                //                              3351000110
                sqlParameterValues = null;
            } else {
                sqlParameterValues = new String[repeatNumber][sqlParameterNumbers];
                for (repeat = 0; repeat < repeatNumber; repeat++) {
                    for (int pvalue = 0; pvalue < sqlParameterNumbers; pvalue++) {
                        sqlParameterValues[repeat][pvalue] = "3"
                                + String.valueOf(StressTool.getNumberFromRandom(500000000).intValue());
                    }
                }

            }

            for (repeat = 0; repeat < repeatNumber; repeat++) {
                int pkStart = 0;
                int pkEnds = 0;
                int recordFound = 0;
                if (repeat > 0 && pkRange.getLooprefresh() < (repeat - pkRange.getLastResetLoop())) {
                    pkRange = setPkRange(conn, pkRange);
                    pkRange.setLastResetLoop(repeat);
                }

                if (pkRange.getKeyStart(repeat) > 0 && pkRange.getKeyEnd(repeat) > 0) {
                    pkStart = pkRange.getKeyStart(repeat);
                    pkEnds = pkRange.getKeyEnd(repeat);
                }

                if (pkStart > pkEnds) {
                    int dummy = pkStart;
                    pkStart = pkEnds;
                    pkEnds = dummy;
                }

                if (pkEnds == 0)
                    continue;

                String select = "";
                {
                    SoftReference sf = new SoftReference(generateSelectString(pkStart, pkEnds, select));
                    select = (String) sf.get();
                }
                int[] iLine = null;

                try {

                    long timeStart = System.currentTimeMillis();

                    try {
                        if (this.sqlQuery != null && !this.sqlQuery.equals("")) {

                            if (this.getIBatchSelect() > 0) {
                                for (int pstmtbatch = 0; pstmtbatch < this.getIBatchSelect(); pstmtbatch++) {
                                    for (int ii = 1; ii <= sqlParameterNumbers; ii++) {
                                        pstmt.setString(ii, sqlParameterValues[StressTool
                                                .getNumberFromRandom(repeatNumber).intValue()][ii - 1]);
                                        {
                                            SoftReference sf = new SoftReference(pstmt.executeQuery());
                                            rs = (ResultSet) sf.get();
                                        }

                                        //                                             System.out.print(this.sqlQuery + " BATCH | " + ii +" | " + sqlParameterValues[repeat][ii - 1] + "\n") ;
                                    }
                                }
                            } else {
                                for (int ii = 1; ii <= sqlParameterNumbers; ii++) {
                                    pstmt.setString(ii, sqlParameterValues[repeat][ii - 1]);
                                    //                                        System.out.print(this.sqlQuery + " | " + ii +" | " + sqlParameterValues[repeat][ii - 1] + "\n") ;
                                }
                                rs = pstmt.executeQuery();

                            }

                        } else {
                            rs = stmt.executeQuery(select);
                            rs.last();
                        }
                    } catch (Exception ex) {
                        if (StressTool.getErrorLogHandler() != null) {
                            StressTool.getErrorLogHandler().appendToFile(ex.toString());
                        } else
                            ex.printStackTrace();
                    } finally {
                        if (rs != null) {
                            rs.close();
                            rs = null;
                        }

                    }
                    long timeEnds = System.currentTimeMillis();
                    //                            recordFound = rs.getRow();

                    execTime = (timeEnds - timeStart);
                    thInfo.setExecutedLoops(repeat);

                } catch (SQLException sqle) {
                    if (StressTool.getErrorLogHandler() != null) {
                        StressTool.getErrorLogHandler().appendToFile(sqle.toString());
                    } else
                        sqle.printStackTrace();
                } finally {
                    if (rs != null) {
                        rs.close();
                        rs = null;
                    }

                    //                            intDeleteInterval++;
                    if (doLog)
                        System.out.println(
                                "Query Select TH = " + this.getID() + " Id = " + pkStart + " IdEnd = " + pkEnds
                                        + " Record found = " + recordFound + " Exec Time(ms) =" + execTime);

                }

                if (sleepFor > 0 || this.getSleepSelect() > 0) {
                    if (this.getSleepSelect() > 0) {
                        Thread.sleep(getSleepSelect());
                    } else
                        Thread.sleep(sleepFor);
                }
            }
            //                System.out.println("Query Select/Delete TH = " + this.getID() + " COMPLETED! ");

            long threadTimeEnd = System.currentTimeMillis();
            this.executionTime = (threadTimeEnd - threadTimeStart);
            //                this.setExecutionTime(executionTime);

            active = false;
            thInfo.setExecutionTime(executionTime);
            thInfo.setStatusActive(false);
            StressTool.setInfoSelect(this.ID, thInfo);
            stmt = null;

            return;

        } catch (Exception ex) {
            ex.printStackTrace();
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }

}

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

@Override
public List<TestBattery> findTestBatteriesByDescription(String description) throws CerberusException {
    boolean throwEx = false;
    final String query = "SELECT * FROM testbattery t WHERE t.description = ?";

    List<TestBattery> testBatteryList = new ArrayList<TestBattery>();
    Connection connection = this.databaseSpring.connect();
    try {//  w  w w .  j  a  va 2 s .com
        PreparedStatement preStat = connection.prepareStatement(query);
        preStat.setString(1, description);
        try {
            ResultSet resultSet = preStat.executeQuery();
            try {
                while (resultSet.next()) {
                    testBatteryList.add(this.loadFromResultSet(resultSet));
                }
            } catch (SQLException exception) {
                MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR,
                        "Unable to execute query : " + exception.toString());
                testBatteryList = null;
            } finally {
                resultSet.close();
            }
        } catch (SQLException exception) {
            MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR,
                    "Unable to execute query : " + exception.toString());
            testBatteryList = null;
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR,
                "Unable to execute query : " + exception.toString());
        testBatteryList = null;
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            MyLogger.log(TestBatteryDAO.class.getName(), Level.WARN, e.toString());
        }
    }
    if (throwEx) {
        throw new CerberusException(new MessageGeneral(MessageGeneralEnum.NO_DATA_FOUND));
    }
    return testBatteryList;
}

From source file:at.alladin.rmbt.controlServer.HistoryResource.java

@Post("json")
public String request(final String entity) {
    long startTime = System.currentTimeMillis();
    addAllowOrigin();/*  ww w .  j  a va2s .  co m*/

    JSONObject request = null;

    final ErrorList errorList = new ErrorList();
    final JSONObject answer = new JSONObject();
    String answerString;

    final String clientIpRaw = getIP();
    System.out.println(MessageFormat.format(labels.getString("NEW_HISTORY"), clientIpRaw));

    if (entity != null && !entity.isEmpty())
        // try parse the string to a JSON object
        try {
            request = new JSONObject(entity);

            String lang = request.optString("language");

            // Load Language Files for Client

            final List<String> langs = Arrays
                    .asList(settings.getString("RMBT_SUPPORTED_LANGUAGES").split(",\\s*"));

            if (langs.contains(lang)) {
                errorList.setLanguage(lang);
                labels = ResourceManager.getSysMsgBundle(new Locale(lang));
            } else
                lang = settings.getString("RMBT_DEFAULT_LANGUAGE");

            //                System.out.println(request.toString(4));

            if (conn != null) {
                final Client client = new Client(conn);

                if (request.optString("uuid").length() > 0
                        && client.getClientByUuid(UUID.fromString(request.getString("uuid"))) > 0) {

                    final Locale locale = new Locale(lang);
                    final Format format = new SignificantFormat(2, locale);

                    String limitRequest = "";
                    if (request.optInt("result_limit", 0) != 0) {
                        final int limit = request.getInt("result_limit");

                        //get offset string if there is one
                        String offsetString = "";
                        if ((request.optInt("result_offset", 0) != 0)
                                && (request.getInt("result_offset") >= 0)) {
                            offsetString = " OFFSET " + request.getInt("result_offset");
                        }

                        limitRequest = " LIMIT " + limit + offsetString;
                    }

                    final ArrayList<String> deviceValues = new ArrayList<>();
                    String deviceRequest = "";
                    if (request.optJSONArray("devices") != null) {
                        final JSONArray devices = request.getJSONArray("devices");

                        boolean checkUnknown = false;
                        final StringBuffer sb = new StringBuffer();
                        for (int i = 0; i < devices.length(); i++) {
                            final String device = devices.getString(i);

                            if (device.equals("Unknown Device"))
                                checkUnknown = true;
                            else {
                                if (sb.length() > 0)
                                    sb.append(',');
                                deviceValues.add(device);
                                sb.append('?');
                            }
                        }

                        if (sb.length() > 0)
                            deviceRequest = " AND (COALESCE(adm.fullname, t.model) IN (" + sb.toString() + ")"
                                    + (checkUnknown ? " OR model IS NULL OR model = ''" : "") + ")";
                        //                            System.out.println(deviceRequest);

                    }

                    final ArrayList<String> filterValues = new ArrayList<>();
                    String networksRequest = "";

                    if (request.optJSONArray("networks") != null) {
                        final JSONArray tmpArray = request.getJSONArray("networks");
                        final StringBuilder tmpString = new StringBuilder();

                        if (tmpArray.length() >= 1) {
                            tmpString.append("AND nt.group_name IN (");
                            boolean first = true;
                            for (int i = 0; i < tmpArray.length(); i++) {
                                if (first)
                                    first = false;
                                else
                                    tmpString.append(',');
                                tmpString.append('?');
                                filterValues.add(tmpArray.getString(i));
                            }
                            tmpString.append(')');
                        }
                        networksRequest = tmpString.toString();
                    }

                    final JSONArray historyList = new JSONArray();

                    final PreparedStatement st;

                    try {
                        if (client.getSync_group_id() == 0) {
                            //use faster request ignoring sync-group as user is not synced (id=0)
                            st = conn.prepareStatement(String.format(

                                    "SELECT DISTINCT"
                                            + " t.uuid, time, timezone, speed_upload, speed_download, ping_median, network_type, nt.group_name network_type_group_name,"
                                            + " COALESCE(adm.fullname, t.model) model" + " FROM test t"
                                            + " LEFT JOIN device_map adm ON adm.codename=t.model"
                                            + " LEFT JOIN network_type nt ON t.network_type=nt.uid"
                                            + " WHERE t.deleted = false AND t.implausible = false AND t.status = 'FINISHED'"
                                            + " AND client_id = ?" + " %s %s" + " ORDER BY time DESC" + " %s",
                                    deviceRequest, networksRequest, limitRequest));
                        } else { //use slower request including sync-group if client is synced
                            st = conn.prepareStatement(String.format("SELECT DISTINCT"
                                    + " t.uuid, time, timezone, speed_upload, speed_download, ping_median, network_type, nt.group_name network_type_group_name,"
                                    + " COALESCE(adm.fullname, t.model) model" + " FROM test t"
                                    + " LEFT JOIN device_map adm ON adm.codename=t.model"
                                    + " LEFT JOIN network_type nt ON t.network_type=nt.uid"
                                    + " WHERE t.deleted = false AND t.implausible = false AND t.status = 'FINISHED'"
                                    + " AND (t.client_id IN (SELECT ? UNION SELECT uid FROM client WHERE sync_group_id = ? ))"
                                    + " %s %s" + " ORDER BY time DESC" + " %s", deviceRequest, networksRequest,
                                    limitRequest));

                        }

                        int i = 1;
                        st.setLong(i++, client.getUid());
                        if (client.getSync_group_id() != 0)
                            st.setInt(i++, client.getSync_group_id());

                        for (final String value : deviceValues)
                            st.setString(i++, value);

                        for (final String filterValue : filterValues)
                            st.setString(i++, filterValue);

                        //System.out.println(st.toString());

                        final ResultSet rs = st.executeQuery();

                        while (rs.next()) {
                            final JSONObject jsonItem = new JSONObject();

                            jsonItem.put("test_uuid", rs.getString("uuid"));

                            final Date date = rs.getTimestamp("time");
                            final long time = date.getTime();
                            final String tzString = rs.getString("timezone");
                            final TimeZone tz = TimeZone.getTimeZone(tzString);
                            jsonItem.put("time", time);
                            jsonItem.put("timezone", tzString);
                            final DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.MEDIUM,
                                    DateFormat.MEDIUM, locale);
                            dateFormat.setTimeZone(tz);
                            jsonItem.put("time_string", dateFormat.format(date));

                            jsonItem.put("speed_upload", format.format(rs.getInt("speed_upload") / 1000d));
                            jsonItem.put("speed_download", format.format(rs.getInt("speed_download") / 1000d));

                            final long ping = rs.getLong("ping_median");
                            jsonItem.put("ping", format.format(ping / 1000000d));
                            // backwards compatibility for old clients
                            jsonItem.put("ping_shortest", format.format(ping / 1000000d));
                            jsonItem.put("model", rs.getString("model"));
                            jsonItem.put("network_type", rs.getString("network_type_group_name"));

                            //for appscape-iPhone-Version: also add classification to the response
                            jsonItem.put("speed_upload_classification", Classification
                                    .classify(classification.THRESHOLD_UPLOAD, rs.getInt("speed_upload")));
                            jsonItem.put("speed_download_classification", Classification
                                    .classify(classification.THRESHOLD_DOWNLOAD, rs.getInt("speed_download")));
                            jsonItem.put("ping_classification", Classification
                                    .classify(classification.THRESHOLD_PING, rs.getLong("ping_median")));
                            // backwards compatibility for old clients
                            jsonItem.put("ping_shortest_classification", Classification
                                    .classify(classification.THRESHOLD_PING, rs.getLong("ping_median")));

                            historyList.put(jsonItem);
                        }

                        if (historyList.length() == 0)
                            errorList.addError("ERROR_DB_GET_HISTORY");
                        // errorList.addError(MessageFormat.format(labels.getString("ERROR_DB_GET_CLIENT"),
                        // new Object[] {uuid}));

                        rs.close();
                        st.close();
                    } catch (final SQLException e) {
                        e.printStackTrace();
                        errorList.addError("ERROR_DB_GET_HISTORY_SQL");
                        // errorList.addError("ERROR_DB_GET_CLIENT_SQL");
                    }

                    answer.put("history", historyList);
                } else
                    errorList.addError("ERROR_REQUEST_NO_UUID");

            } else
                errorList.addError("ERROR_DB_CONNECTION");

        } catch (final JSONException e) {
            errorList.addError("ERROR_REQUEST_JSON");
            System.out.println("Error parsing JSDON Data " + e.toString());
        } catch (final IllegalArgumentException e) {
            errorList.addError("ERROR_REQUEST_NO_UUID");
        }
    else
        errorList.addErrorString("Expected request is missing.");

    try {
        answer.putOpt("error", errorList.getList());
    } catch (final JSONException e) {
        System.out.println("Error saving ErrorList: " + e.toString());
    }

    answerString = answer.toString();

    long elapsedTime = System.currentTimeMillis() - startTime;
    System.out.println(MessageFormat.format(labels.getString("NEW_HISTORY_SUCCESS"), clientIpRaw,
            Long.toString(elapsedTime)));

    return answerString;
}

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

@Override
public List<TestBattery> findTestBatteriesByTestCase(String test, String testCase) throws CerberusException {
    boolean throwEx = false;
    final String query = "SELECT tb.* FROM testbattery tb inner join testbatterycontent tbc on tb.testbattery = tbc.testbattery where tbc.test = ? and tbc.testcase = ?";

    List<TestBattery> testBatteriesList = new ArrayList<TestBattery>();
    Connection connection = this.databaseSpring.connect();
    try {/*from  ww w . jav  a 2 s. c o  m*/
        PreparedStatement preStat = connection.prepareStatement(query);
        try {
            preStat.setString(1, test);
            preStat.setString(2, testCase);
            ResultSet resultSet = preStat.executeQuery();
            try {
                while (resultSet.next()) {
                    testBatteriesList.add(this.loadFromResultSet(resultSet));
                }
            } catch (SQLException exception) {
                MyLogger.log(TestBatteryContentDAO.class.getName(), Level.ERROR,
                        "Unable to execute query : " + exception.toString());
                testBatteriesList = null;
            } finally {
                resultSet.close();
            }
        } catch (SQLException exception) {
            MyLogger.log(TestBatteryContentDAO.class.getName(), Level.ERROR,
                    "Unable to execute query : " + exception.toString());
            testBatteriesList = null;
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        MyLogger.log(TestBatteryContentDAO.class.getName(), Level.ERROR,
                "Unable to execute query : " + exception.toString());
        testBatteriesList = null;
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            MyLogger.log(TestBatteryContentDAO.class.getName(), Level.WARN, e.toString());
        }
    }
    if (throwEx) {
        throw new CerberusException(new MessageGeneral(MessageGeneralEnum.NO_DATA_FOUND));
    }
    return testBatteriesList;
}

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

@Override
public AnswerList readByCriteria(int start, int amount, String colName, String dir, String searchTerm,
        Map<String, List<String>> individualSearch) {
    AnswerList response = new AnswerList();
    MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
    List<TestBattery> testBatteryList = new ArrayList<TestBattery>();
    StringBuilder searchSQL = new StringBuilder();
    List<String> individalColumnSearchValues = new ArrayList<String>();

    StringBuilder query = new StringBuilder();
    //SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit clauses that
    //were applied -- used for pagination p
    query.append("SELECT SQL_CALC_FOUND_ROWS * FROM testbattery ");

    searchSQL.append(" where 1=1 ");

    if (!StringUtil.isNullOrEmpty(searchTerm)) {
        searchSQL.append(" and (`testbatteryid` like ?");
        searchSQL.append(" or `testbattery` like ?");
        searchSQL.append(" or `description` like ?)");
    }/*from www. j a  v a  2s.  c  om*/
    if (individualSearch != null && !individualSearch.isEmpty()) {
        searchSQL.append(" and ( 1=1 ");
        for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {
            searchSQL.append(" and ");
            String key = "IFNULL(`testbattery`." + entry.getKey() + ",'')";
            String q = SqlUtil.getInSQLClauseForPreparedStatement(key, entry.getValue());
            if (q == null || q == "") {
                q = "(`testbattery`." + entry.getKey() + " IS NULL OR " + entry.getKey() + " = '')";
            }
            searchSQL.append(q);
            individalColumnSearchValues.addAll(entry.getValue());
        }
        searchSQL.append(" )");
    }
    query.append(searchSQL);

    if (!StringUtil.isNullOrEmpty(colName)) {
        query.append("order by `").append(colName).append("` ").append(dir);
    }
    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);
    }

    Connection connection = this.databaseSpring.connect();
    try {
        PreparedStatement preStat = connection.prepareStatement(query.toString());
        try {
            int i = 1;
            if (!Strings.isNullOrEmpty(searchTerm)) {
                preStat.setString(i++, "%" + searchTerm + "%");
                preStat.setString(i++, "%" + searchTerm + "%");
                preStat.setString(i++, "%" + searchTerm + "%");
            }
            for (String individualColumnSearchValue : individalColumnSearchValues) {
                preStat.setString(i++, individualColumnSearchValue);
            }

            ResultSet resultSet = preStat.executeQuery();
            try {
                //gets the data
                while (resultSet.next()) {
                    testBatteryList.add(this.loadFromResultSet(resultSet));
                }

                //get the total number of rows
                resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
                int nrTotalRows = 0;

                if (resultSet != null && resultSet.next()) {
                    nrTotalRows = resultSet.getInt(1);
                }

                if (testBatteryList.size() >= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain. That means that we may miss some lines in the resultList.
                    LOG.error("Partial Result in the query.");
                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%",
                            "Maximum row reached : " + MAX_ROW_SELECTED));
                    response = new AnswerList(testBatteryList, nrTotalRows);
                } else if (testBatteryList.size() <= 0) {
                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
                    response = new AnswerList(testBatteryList, nrTotalRows);
                } else {
                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
                    msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME)
                            .replace("%OPERATION%", "SELECT"));
                    response = new AnswerList(testBatteryList, nrTotalRows);
                }

            } catch (SQLException exception) {
                LOG.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!"));

            } finally {
                if (resultSet != null) {
                    resultSet.close();
                }
            }

        } catch (SQLException exception) {
            LOG.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!"));
        } finally {
            if (preStat != null) {
                preStat.close();
            }
        }

    } catch (SQLException exception) {
        LOG.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!"));
    } finally {
        try {
            if (!this.databaseSpring.isOnTransaction()) {
                if (connection != null) {
                    connection.close();
                }
            }
        } catch (SQLException exception) {
            LOG.warn("Unable to close connection : " + exception.toString());
        }
    }

    response.setResultMessage(msg);
    return response;
}

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

@Override
public AnswerList findTestCaseCountryPropertiesByValue1(int testDataLib, String name, String country,
        String propertyType) {//from  ww  w .ja  v  a 2 s  . c  om
    AnswerList ansList = new AnswerList();
    MessageEvent rs;
    List<TestListDTO> listOfTests = new ArrayList<TestListDTO>();
    StringBuilder query = new StringBuilder();
    query.append(
            "select count(*) as total, tccp.property, t.Test, tc.TestCase, t.Description as testDescription, tc.Description as testCaseDescription, tc.Application, ");
    query.append("tc.TcActive as Active, tc.`Group`, tc.UsrCreated, tc.`Status` ");
    query.append("from testcasecountryproperties tccp    ");
    query.append("inner join test t on t.test = tccp.test ");
    query.append("inner join testcase tc  on t.test = tccp.test  and t.test = tc.test ");
    query.append("inner join testdatalib tdl on tdl.`name` = tccp.value1  and ");
    query.append(
            "(tccp.Country = tdl.Country or tdl.country='') and tccp.test = t.test and tccp.testcase = tc.testcase ");
    query.append("where tccp.`Type` LIKE ? and tdl.TestDataLibID = ? ");
    query.append("and tdl.`Name` LIKE ? and (tdl.Country = ? or tdl.country='') ");
    query.append("group by tccp.test, tccp.testcase, tccp.property ");

    Connection connection = this.databaseSpring.connect();
    try {
        PreparedStatement preStat = connection.prepareStatement(query.toString());
        try {
            preStat.setString(1, propertyType);
            preStat.setInt(2, testDataLib);
            preStat.setString(3, name);
            preStat.setString(4, country);

            HashMap<String, TestListDTO> map = new HashMap<String, TestListDTO>();

            HashMap<String, List<PropertyListDTO>> auxiliaryMap = new HashMap<String, List<PropertyListDTO>>();//the key is the test + ":" +testcasenumber

            String key, test, testCase;
            ResultSet resultSet = preStat.executeQuery();
            try {
                while (resultSet.next()) {
                    TestListDTO testList;
                    TestCaseListDTO testCaseDTO;
                    List<PropertyListDTO> propertiesList;

                    test = resultSet.getString("Test");
                    testCase = resultSet.getString("TestCase");

                    //TEST
                    //gets the info from test cases that match the desired information
                    if (map.containsKey(test)) {
                        testList = map.get(test);
                    } else {
                        testList = new TestListDTO();

                        testList.setDescription(resultSet.getString("testDescription"));
                        testList.setTest(test);
                    }

                    //TESTCASE
                    key = test + ":" + testCase;
                    if (!auxiliaryMap.containsKey(key)) {
                        //means that we must associate a new test case with a test
                        testCaseDTO = new TestCaseListDTO();
                        testCaseDTO.setTestCaseDescription(resultSet.getString("testCaseDescription"));
                        testCaseDTO.setTestCaseNumber(testCase);
                        testCaseDTO.setApplication(resultSet.getString("Application"));
                        testCaseDTO.setCreator(resultSet.getString("tc.UsrCreated"));
                        testCaseDTO.setStatus(resultSet.getString("Status"));

                        testCaseDTO.setGroup(resultSet.getString("Group"));
                        testCaseDTO.setIsActive(resultSet.getString("Active"));
                        testList.getTestCaseList().add(testCaseDTO);
                        map.put(test, testList);
                        propertiesList = new ArrayList<PropertyListDTO>();
                    } else {
                        propertiesList = auxiliaryMap.get(key);
                    }

                    PropertyListDTO prop = new PropertyListDTO();

                    prop.setNrCountries(resultSet.getInt("total"));
                    prop.setPropertyName(resultSet.getString("property"));
                    propertiesList.add(prop);
                    //stores the information about the properties
                    auxiliaryMap.put(key, propertiesList);

                }

                //assigns the list of tests retrieved by the query to the list
                listOfTests = new ArrayList<TestListDTO>(map.values());

                //assigns the list of properties to the correct testcaselist
                for (TestListDTO list : listOfTests) {
                    for (TestCaseListDTO cases : list.getTestCaseList()) {
                        cases.setPropertiesList(
                                auxiliaryMap.get(list.getTest() + ":" + cases.getTestCaseNumber()));
                    }
                }
                if (listOfTests.isEmpty()) {
                    rs = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
                } else {
                    rs = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
                    rs.setDescription(rs.getDescription().replace("%ITEM%", "List of Test Cases")
                            .replace("%OPERATION%", "Select"));
                }

            } catch (SQLException exception) {
                LOG.error("Unable to execute query : " + exception.toString());
                rs = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
                rs.setDescription(
                        rs.getDescription().replace("%DESCRIPTION%", "Unable to get the list of test cases."));
            } finally {
                if (resultSet != null) {
                    resultSet.close();
                }
            }
        } catch (SQLException exception) {
            LOG.error("Unable to execute query : " + exception.toString());
            rs = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
            rs.setDescription(
                    rs.getDescription().replace("%DESCRIPTION%", "Unable to get the list of test cases."));
        } finally {
            if (preStat != null) {
                preStat.close();
            }
        }
    } catch (SQLException exception) {
        LOG.error("Unable to execute query : " + exception.toString());
        rs = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
        rs.setDescription(
                rs.getDescription().replace("%DESCRIPTION%", "Unable to get the list of test cases."));
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            LOG.warn(e.toString());
        }
    }
    ansList.setResultMessage(rs);
    ansList.setDataList(listOfTests);

    return ansList;
}

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

@Override
public List<TestBattery> findTestBatteryByCriteria(Integer testBatteryID, String testBattery,
        String Description) throws CerberusException {
    boolean throwEx = false;
    final StringBuffer query = new StringBuffer("SELECT * FROM testbattery t WHERE 1=1");

    if (testBatteryID != null) {
        query.append(" AND t.testBatteryID = ?");
    }//from  ww w.j  a  va 2 s . c om
    if (testBattery != null && !"".equals(testBattery.trim())) {
        query.append(" AND t.testBattery LIKE ?");
    }
    if (Description != null && !"".equals(Description.trim())) {
        query.append(" AND t.Description LIKE ?");
    }

    List<TestBattery> testBatteriesList = new ArrayList<TestBattery>();
    Connection connection = this.databaseSpring.connect();
    try {
        PreparedStatement preStat = connection.prepareStatement(query.toString());
        int index = 1;
        if (testBatteryID != null) {
            preStat.setInt(index, testBatteryID);
            index++;
        }
        if (testBattery != null && !"".equals(testBattery.trim())) {
            preStat.setString(index, "%" + testBattery.trim() + "%");
            index++;
        }
        if (Description != null && !"".equals(Description.trim())) {
            preStat.setString(index, "%" + Description.trim() + "%");
            index++;
        }

        try {
            ResultSet resultSet = preStat.executeQuery();
            try {
                while (resultSet.next()) {
                    testBatteriesList.add(this.loadFromResultSet(resultSet));
                }
            } catch (SQLException exception) {
                MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR,
                        "Unable to execute query : " + exception.toString());
                testBatteriesList = null;
            } finally {
                resultSet.close();
            }
        } catch (SQLException exception) {
            MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR,
                    "Unable to execute query : " + exception.toString());
            testBatteriesList = null;
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR,
                "Unable to execute query : " + exception.toString());
        testBatteriesList = null;
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            MyLogger.log(TestBatteryDAO.class.getName(), Level.WARN, e.toString());
        }
    }
    if (throwEx) {
        throw new CerberusException(new MessageGeneral(MessageGeneralEnum.NO_DATA_FOUND));
    }
    return testBatteriesList;
}