Example usage for java.sql ResultSet first

List of usage examples for java.sql ResultSet first

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. first

Introduction

In this page you can find the example usage for java.sql ResultSet first.

Prototype

boolean first() throws SQLException;

Source Link

Document

Moves the cursor to the first row in this ResultSet object.

Usage

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

@Override
public TestCaseExecution findLastTCExecutionInGroup(String test, String testCase, String environment,
        String country, String build, String revision, String browser, String browserVersion, String ip,
        String port, String tag) {

    TestCaseExecution result = null;//from   w w w. ja va 2 s  . c  om
    StringBuilder query = new StringBuilder();
    query.append("SELECT exe.* FROM testcaseexecution exe ")
            .append("WHERE exe.test = ? AND exe.testcase = ? AND exe.country = ? AND exe.browser = ? ");
    if (!StringUtil.isNull(environment)) {
        query.append("AND exe.environment IN (");
        query.append(environment);
        query.append(") ");
    }
    if (!StringUtil.isNull(build)) {
        query.append("AND exe.build IN (");
        query.append(build);
        query.append(") ");
    }
    if (!StringUtil.isNull(revision)) {
        query.append("AND exe.revision IN (");
        query.append(revision);
        query.append(") ");
    }
    if (!StringUtil.isNull(browserVersion)) {
        query.append("AND exe.browserfullversion LIKE ? ");
    }
    if (!StringUtil.isNull(ip)) {
        query.append("AND exe.ip LIKE ? ");
    }
    if (!StringUtil.isNull(port)) {
        query.append("AND exe.port LIKE ? ");
    }
    if (!StringUtil.isNull(tag)) {
        query.append("AND exe.tag LIKE ? ");
    }
    query.append("ORDER BY exe.id DESC");

    Connection connection = this.databaseSpring.connect();
    try {
        PreparedStatement preStat = connection.prepareStatement(query.toString());
        preStat.setString(1, test);
        preStat.setString(2, testCase);
        preStat.setString(3, country);
        preStat.setString(4, browser);
        int i = 5;
        if (!StringUtil.isNull(browserVersion)) {
            preStat.setString(i, browserVersion);
            i++;
        }
        if (!StringUtil.isNull(ip)) {
            preStat.setString(i, ip);
            i++;
        }
        if (!StringUtil.isNull(port)) {
            preStat.setString(i, port);
            i++;
        }
        if (!StringUtil.isNull(tag)) {
            preStat.setString(i, tag);
        }

        try {
            ResultSet resultSet = preStat.executeQuery();
            try {
                if (resultSet.first()) {
                    result = this.loadFromResultSet(resultSet);
                }
            } catch (SQLException exception) {
                LOG.error("Unable to execute query : " + exception.toString());
            } finally {
                resultSet.close();
            }
        } catch (SQLException exception) {
            LOG.error("Unable to execute query : " + exception.toString());
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        LOG.error("Unable to execute query : " + exception.toString());
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            LOG.warn(e.toString());
        }
    }
    return result;
}

From source file:net.xqx.controller.web.QyzzController.java

/**
 * ?/*from w  w w  . j  a  va2s . com*/
 * 
 * @return
 */
@RequestMapping("/fdckfqy")
public String fdckfqy(HttpServletRequest request) {
    PageDao pageDao = new PageDao();
    String pageCount = request.getParameter("pageCount");// ???
    if (pageCount == null || "".equals(pageCount)) {
        pageCount = "1";
    }
    int totalRow = 0;// ?
    String total = request.getParameter("totalRow");
    int totalrow = 0;
    if (null != total && !"".equals(total)) {
        totalrow = Integer.parseInt(total);
    }

    Page page = null;
    String companyName = request.getParameter("companyName");
    if (companyName != null && !"".equals(companyName)) {
        companyName = companyName.trim();
    }
    String certificateLevel = request.getParameter("certificateLevel");
    request.setAttribute("companyName", companyName);
    request.setAttribute("certificateLevel", certificateLevel);

    Connection conn = pageDao.getAptitudeConnection();
    PreparedStatement statement = null;
    ResultSet rs = null;
    String hql = "";
    try {

        if (companyName != null && !"".equals(companyName)
                && (certificateLevel == null || "".equals(certificateLevel))) {
            hql = "select count(*) from TCompanyInfo c left join TCertificate cer "
                    + "on c.fCompanyId=cer.fCompanyId left join TRegInfo r "
                    + "on c.fCompanyId=r.fCompanyInfoId where c.fChecked=1 and c.fCompanyName like " + "'" + "%"
                    + companyName + "%" + "'";
            String sql = "select c.fCompanyName,c.fCompanyType,c.fRightMan,r.fCapital,r.fPaiclUpCapital,r.fRegLicenseNo,"
                    + "cer.fCertificateLevel,cer.fCertificateNo,c.fOperatingDate,cer.fCertifyDate,"
                    + "cer.fValidBeginDate,cer.fValidEndDate,c.fAddress,c.fDetails,c.fCompanyId from TCompanyInfo c left join TCertificate cer "
                    + "on c.fCompanyId=cer.fCompanyId left join TRegInfo r "
                    + "on c.fCompanyId=r.fCompanyInfoId where c.fChecked=1 and c.fCompanyName like ? order by c.fCompanyId desc";
            totalRow = pageDao.getAmount(hql);// select count ?
            if (totalRow != totalrow) {
                pageCount = "1";
            }
            page = new Page(totalRow, pageCount, 11);
            statement = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setMaxRows(page.getEndIndex());
            statement.setString(1, "%" + companyName + "%");
            rs = statement.executeQuery();
            rs.first();
            rs.relative(page.getBeginIndex() - 1);
            request.setAttribute("totalPage", page.getTotal());// ?
            request.setAttribute("pageCount", page.getCount());// ??
            request.setAttribute("companyName", companyName);// ??
        } else if (companyName != null && !"".equals(companyName) && certificateLevel != null
                && !"".equals(certificateLevel)) {
            hql = "select count(*) from TCompanyInfo c left join TCertificate cer "
                    + "on c.fCompanyId=cer.fCompanyId left join TRegInfo r "
                    + "on c.fCompanyId=r.fCompanyInfoId where c.fChecked=1 and c.fCompanyName like " + "'" + "%"
                    + companyName + "%" + "'" + " and cer.fCertificateLevel=" + "'" + certificateLevel + "'";

            String sql = "select c.fCompanyName,c.fCompanyType,c.fRightMan,r.fCapital,r.fPaiclUpCapital,r.fRegLicenseNo,"
                    + "cer.fCertificateLevel,cer.fCertificateNo,c.fOperatingDate,cer.fCertifyDate,"
                    + "cer.fValidBeginDate,cer.fValidEndDate,c.fAddress,c.fDetails,c.fCompanyId from TCompanyInfo c left join TCertificate cer "
                    + "on c.fCompanyId=cer.fCompanyId left join TRegInfo r "
                    + "on c.fCompanyId=r.fCompanyInfoId where c.fChecked=1 and c.fCompanyName like ? and cer.fCertificateLevel=? order by c.fCompanyId desc";
            totalRow = pageDao.getAmount(hql);// select count ?
            if (totalRow != totalrow) {
                pageCount = "1";
            }
            page = new Page(totalRow, pageCount, 11);
            statement = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setMaxRows(page.getEndIndex());
            statement.setString(1, "%" + companyName + "%");
            statement.setString(2, certificateLevel);
            rs = statement.executeQuery();
            rs.first();
            rs.relative(page.getBeginIndex() - 1);
            request.setAttribute("totalPage", page.getTotal());// ?
            request.setAttribute("pageCount", page.getCount());// ??
            request.setAttribute("companyName", companyName);// ??
            request.setAttribute("certificateLevel", certificateLevel);// ??
        }

        TCompanyInfo companyInfo = null;
        List<TCompanyInfo> companyInfos = new ArrayList<TCompanyInfo>();
        if (rs != null) {
            while (rs.next()) {
                companyInfo = new TCompanyInfo();
                companyInfo.setfCompanyName(rs.getString(1));
                companyInfo.setfCompanyType(rs.getString(2));
                companyInfo.setfRightMan(rs.getString(3));
                companyInfo.setfCapitals(rs.getString(4));
                companyInfo.setfCapitalsUp(rs.getString(5));
                companyInfo.setfLicenseNo(rs.getString(6));
                companyInfo.setfCertificateLevel(rs.getString(7));
                companyInfo.setfCertificateNo(rs.getString(8));
                companyInfo.setfOperatingDate(rs.getString(9));
                companyInfo.setfCertificateDate(rs.getString(10));
                companyInfo.setfCertificateBeginDate(rs.getString(11));
                companyInfo.setfCertificateEndDate(rs.getString(12));
                companyInfo.setfRegAddress(rs.getString(13));
                companyInfo.setfDetails(rs.getString(14));
                companyInfo.setfCompanyId(rs.getInt(15));
                companyInfos.add(companyInfo);
            }
        }
        request.setAttribute("companyInfos", companyInfos);
        // rs.close();
        // statement.close();
        // conn.close();

    } catch (SQLException e) {
        System.out.println("!");
        e.printStackTrace();
        return "web/qycx";
    } finally {
        pageDao.closeConnection(rs, statement, conn);

    }

    // 
    Sort hotNewsSort = new Sort(Direction.DESC, "fdjTimes", "ffbTime");
    Pageable hotNewsRecPageable = new PageRequest(0, 8, hotNewsSort);
    List<TNews> hotNewsList = newsDao.getHotNews(hotNewsRecPageable).getContent();
    request.setAttribute("hotNewsList", hotNewsList);

    // ??
    Sort recNewsSort = new Sort(Direction.DESC, "fIsRecord", "ffbTime");
    Pageable recNewsRecPageable = new PageRequest(0, 8, recNewsSort);
    List<TNews> recNewsList = newsDao.getNewsRec(recNewsRecPageable).getContent();
    request.setAttribute("recNewsList", recNewsList);
    request.setAttribute("totalRow", totalRow);
    return "web/qycx";
}

From source file:net.swas.explorer.httpprofile.DOProfile.java

/**
 * This function is responsible for inserting parent and leaf nodes
 * @param urls/*from ww w . ja  v a  2  s  .  c  om*/
 * @throws SQLException
 */
public void insertPairs(ArrayList<String> urls) throws SQLException {
    int max = 0;
    for (String string : urls) {
        if (max == 0) {
            max = getMaxIdPairs(max);
        } else {
            max = max + 1;
        }
        System.out.println("url size : " + urls.size());
        System.out.println("url : " + string);
        StringTokenizer urlTokenizer = new StringTokenizer(string, "/");
        System.out.println("total tokens are= " + urlTokenizer.countTokens());
        urlTokenizer.nextToken();
        int value = 0;
        int x = 0;
        String parent = "";
        while (urlTokenizer.hasMoreTokens()) {
            ResultSet rs = null;
            String name = urlTokenizer.nextToken();
            System.out.println("Name :" + name);
            if (x == 0) {
                String sql_qry = "select name from pairs where name= ?";
                PreparedStatement stmt = (PreparedStatement) cdb.prepareQuery(sql_qry);
                stmt.setString(1, name);
                rs = cdb.executeQuery(stmt);
                rs.next();
            } else {
                String sql_qry = "select name, parentid from pairs where name=?";
                PreparedStatement stmt = (PreparedStatement) cdb.prepareQuery(sql_qry);
                stmt.setString(1, name);
                rs = cdb.executeQuery(stmt);
                rs.next();

                String sqlQry = "select  id from pairs where name=?";
                PreparedStatement pstmt = (PreparedStatement) cdb.prepareQuery(sqlQry);
                pstmt.setString(1, parent);
                ResultSet rss = cdb.executeQuery(pstmt);
                rss.next();
                value = rss.getInt(1);
            }

            String sql_qry = "select name, parentid from pairs where name=?";
            PreparedStatement stmt = (PreparedStatement) cdb.prepareQuery(sql_qry);
            stmt.setString(1, name);
            rs = cdb.executeQuery(stmt);
            rs.next();
            if (!rs.first()) {
                if (x == 0)
                    value = 0;
                else
                    max = max + 1;

                String sqlInsertPairQry = "INSERT INTO pairs (id, name, parentid) VALUES (?,?,?)";
                PreparedStatement pstmt = (PreparedStatement) cdb.prepareQuery(sqlInsertPairQry);
                pstmt.setLong(1, max);
                pstmt.setString(2, name);
                pstmt.setLong(3, value);
                cdb.updateQuery(pstmt);
                System.out.println("inserted is " + name);

            } else {

            }
            parent = name;
            // value=max;
            x++;
        }
    }

}

From source file:com.cws.esolutions.security.dao.usermgmt.impl.SQLUserManager.java

/**
 * @see com.cws.esolutions.security.dao.usermgmt.interfaces.UserManager#loadUserAccount(java.lang.String)
 *//*from  w w  w .  ja v a2  s.com*/
public synchronized List<Object> loadUserAccount(final String userGuid) throws UserManagementException {
    final String methodName = SQLUserManager.CNAME
            + "#loadUserAccount(final String guid) throws UserManagementException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", userGuid);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object> userAccount = null;

    try {
        sqlConn = SQLUserManager.dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{ CALL loadUserAccount(?) }");
        stmt.setString(1, userGuid); // common name

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("ResultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.last();
                int x = resultSet.getRow();

                if (DEBUG) {
                    DEBUGGER.debug("x: {}", x);
                }

                if ((x == 0) || (x > 1)) {
                    throw new UserManagementException("No user account was located for the provided data.");
                }

                resultSet.first();

                userAccount = new ArrayList<Object>(
                        Arrays.asList(resultSet.getString(userAttributes.getCommonName()),
                                resultSet.getString(userAttributes.getUserId()),
                                resultSet.getString(securityAttributes.getLockCount()),
                                resultSet.getString(securityAttributes.getLastLogin()),
                                resultSet.getString(securityAttributes.getExpiryDate()),
                                resultSet.getString(userAttributes.getSurname()),
                                resultSet.getString(userAttributes.getGivenName()),
                                resultSet.getString(userAttributes.getDisplayName()),
                                resultSet.getString(userAttributes.getEmailAddr()),
                                resultSet.getString(userAttributes.getTelephoneNumber()),
                                resultSet.getString(userAttributes.getMemberOf()),
                                resultSet.getString(securityAttributes.getIsSuspended()),
                                resultSet.getString(securityAttributes.getOlrSetupReq()),
                                resultSet.getString(securityAttributes.getOlrLocked())));

                if (DEBUG) {
                    DEBUGGER.debug("UserAccount: {}", userAccount);
                }
            }
        } else {
            throw new UserManagementException("No users were located with the provided information");
        }
    } catch (SQLException sqx) {
        throw new UserManagementException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (stmt != null) {
                stmt.close();
            }

            if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
                sqlConn.close();
            }
        } catch (SQLException sqx) {
            throw new UserManagementException(sqx.getMessage(), sqx);
        }
    }

    return userAccount;
}

From source file:dbservlet.Servlet.java

public void update(HttpServletRequest request, HttpServletResponse response)
        throws ClassNotFoundException, SQLException, ServletException, IOException {
    Connection conn = null;//from   w  w  w  .ja va2s.  c o m
    Statement stat = null;
    ResultSet rs = null;
    ResultSet rs0 = null;
    ResultSet rs1 = null;
    ResultSet rs2 = null;
    String data = request.getParameter("data");
    StringBuilder html = new StringBuilder();
    JSONArray datalist = null;
    JSONObject input = null;
    try {
        input = new JSONObject(data);
    } catch (JSONException ex) {
        Logger.getLogger(Servlet.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        datalist = input.getJSONArray("data");
    } catch (JSONException ex) {
        Logger.getLogger(Servlet.class.getName()).log(Level.SEVERE, null, ex);
    }
    Date now = new Date();
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
    String BuyDate = dateFormat.format(now);
    System.out.println(BuyDate);
    for (int i = 0; i < datalist.length(); i++) {
        String product_ID = "";
        String quantity = "";
        String customer_ID = "";
        //String BuyDate="";
        String salesperson_id = "";
        try {
            product_ID = (String) datalist.getJSONObject(i).get("id");
            quantity = (String) datalist.getJSONObject(i).get("amount");
            customer_ID = (String) datalist.getJSONObject(i).get("customer_ID");
            //BuyDate = (String) datalist.getJSONObject(i).get("BuyDate");
            salesperson_id = (String) datalist.getJSONObject(i).get("salesperson_id");
            if (salesperson_id.length() != 7) {
                response.getWriter().print("0");
                return;
            }
            //System.out.println(salesperson_id);
        } catch (JSONException ex) {
            Logger.getLogger(Servlet.class.getName()).log(Level.SEVERE, null, ex);
        }

        conn = connect();
        stat = conn.createStatement();
        rs = stat.executeQuery(
                "select salesperson_id,customer_ID from Salespersons,Customers_Info where salesperson_id="
                        + salesperson_id + " and customer_ID=" + customer_ID + "");
        //System.out.print(rs.getString(salesperson_id));
        if (!rs.first()) {

            response.getWriter().print("0");
            System.out.print(rs);

        } else {
            try {
                int intage = Integer.parseInt(quantity);
                rs1 = stat.executeQuery(
                        "select inventory_amount from Products where product_ID=" + product_ID + "");
                if (rs1.next()) {
                    int initage = rs1.getInt("inventory_amount");
                    int quantityresult = initage - intage;
                    String s = String.valueOf(quantityresult);
                    stat.execute("update Products set inventory_amount=" + s + " where product_ID=" + product_ID
                            + "");
                    response.getWriter().print("1");
                    request.setAttribute("result", select(product_ID, ""));
                } else {
                    response.getWriter().print("0");
                }
            } catch (Exception e) {
                //                     System.out.println("fuck1");
                //                     request.getRequestDispatcher("productwrong.jsp").forward(request, response);
            }

            try {
                rs1 = stat.executeQuery("select MAX(order_number) from Transactions");
                int orderNumber;
                if (rs1.next()) {
                    orderNumber = 1 + rs1.getInt(1);
                } else {
                    orderNumber = 1;
                }

                String ran = String.valueOf(orderNumber);
                while (ran.length() < 7) {
                    ran = 0 + ran;
                }
                stat.execute(
                        "INSERT INTO `Transactions`(`order_number`, `BuyDate`, `salesperson_id`, `customer_ID`, `product_ID`, `quantity`) VALUES ('"
                                + ran + "','" + BuyDate + "','" + salesperson_id + "','" + customer_ID + "','"
                                + product_ID + "','" + quantity + "')");

            } catch (Exception e) {

                request.getRequestDispatcher("tranwrong.jsp").forward(request, response);
            }

            try {
                int initquan = Integer.parseInt(quantity);
                rs1 = stat.executeQuery("select * from Products where product_ID=" + product_ID + "");
                rs1.next();

                double initprice = rs1.getDouble("price");

                rs2 = stat
                        .executeQuery("select * from Salespersons where salesperson_id=" + salesperson_id + "");
                rs2.next();
                double salesvolume = rs2.getDouble("sales_volume");

                double finalresult = salesvolume + initprice * initquan;

                stat.execute("update Salespersons set sales_volume=" + finalresult + " where salesperson_id="
                        + salesperson_id + "");
                //request.getRequestDispatcher("update.jsp").forward(request, response);
            } catch (Exception e) {
                System.out.println("hello");
                request.getRequestDispatcher("salewrong.jsp").forward(request, response);
            }
        }

        //            html.append("");
        //            String result=html.toString();
        //response.getWriter().print("sucess");
        //request.getRequestDispatcher("salewrong.jsp").forward(request, response);
    }
}

From source file:ProcessRequest.java

public int insertNewTicketDetail(JSONObject recordObject) throws SQLException, JSONException {
    String valuesPortion = "";
    String columnsPortion = "";

    //recordObject.remove(DbSingleton.TicketSchema.COLUMN_TICKET_NUMBER);
    //recordObject.put(DbSingleton.TicketSchema.COLUMN_TICKET_NUMBER, getNextTicketNumber());

    //get column names
    JsonParser parser = new JsonParser();
    JsonObject recordGson = parser.parse(recordObject.toString()).getAsJsonObject();
    Set<Map.Entry<String, JsonElement>> entrySet = recordGson.entrySet();
    {//from   w  ww .  java 2s. c  om
        Iterator<Map.Entry<String, JsonElement>> iterator = entrySet.iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, JsonElement> entry = iterator.next();
            if (entry.getKey().equals("metadata")) {
                //do nothing
            } else {
                JsonPrimitive value = entry.getValue().getAsJsonPrimitive();
                //if(value.isString() && value.getAsString().equals("")) {
                //don't want this null value/key pair
                //} else {
                if (!columnsPortion.equals("")) {
                    columnsPortion += ",";
                }
                columnsPortion += entry.getKey();
                //System.out.println(entry.getKey()+" : "+value.getAsString());
                //}
            }
        }
    }

    //get values
    {
        Iterator<Map.Entry<String, JsonElement>> iterator = entrySet.iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, JsonElement> entry = iterator.next();
            if (entry.getKey().equals("metadata")) {
                //do nothing
            } else {
                JsonPrimitive value = entry.getValue().getAsJsonPrimitive();
                //System.out.println(entry.getKey()+" : "+value.getAsString());
                if (value.isString()) {
                    if (!valuesPortion.equals("")) {
                        valuesPortion += ",";
                    }
                    //System.out.println(entry.getKey()+" : '"+value.getAsString()+"'");
                    valuesPortion += "'" + value.getAsString() + "'";
                } else if (value.isNumber()) {
                    if (value.getAsString().contains(".")) {
                        if (!valuesPortion.equals("")) {
                            valuesPortion += ",";
                        }
                        //System.out.println(entry.getKey()+" : "+(new Double(value.getAsDouble()).toString()));
                        valuesPortion += new Double(value.getAsDouble()).toString();
                    } else {
                        if (!valuesPortion.equals("")) {
                            valuesPortion += ",";
                        }
                        //System.out.println(entry.getKey()+" : "+(new Long(value.getAsLong()).toString()));
                        String ticketNumber = new Long(value.getAsLong()).toString();
                        valuesPortion += ticketNumber;
                    }
                } else if (value.isBoolean()) {
                    if (!valuesPortion.equals("")) {
                        valuesPortion += ",";
                    }
                    //System.out.println(entry.getKey()+" : "+(new Boolean(value.getAsBoolean()).toString()));
                    if (value.getAsBoolean())
                        valuesPortion += "1";
                    else
                        valuesPortion += "0";
                }
            }
        }
    }

    String insertQuery = "INSERT INTO " + DbSingleton.TICKET_DETAIL_TABLE_NAME + "(" + columnsPortion
            + ") VALUES (" + valuesPortion + ")";
    String identQuery = "SELECT IDENT_CURRENT ('" + DbSingleton.TICKET_DETAIL_TABLE_NAME
            + "') AS TicketDetailID";

    executeQuery(insertQuery);
    ResultSet identResultSet = executeQuery(identQuery);
    identResultSet.first();
    //System.out.println(identResultSet.getRow());
    int ticketDetailID = identResultSet.getInt(1);
    //System.out.println(ticketID);

    return ticketDetailID;
}

From source file:ProcessRequest.java

public JSONObject parseNewProduct(JSONObject requestObject) throws SQLException, JSONException {
    JSONObject record = new JSONObject();
    JSONArray recordArray = requestObject.getJSONArray("records");
    String productID = null;/*  w w  w.  j av  a2 s  . c  om*/
    JSONObject responseObject = new JSONObject();
    for (int i = 0; i < recordArray.length(); i++) {
        record = recordArray.getJSONObject(i);
        productID = record.getString(DbSingleton.ProductSchema.COLUMN_ID);
        String checkQuery = "SELECT * FROM Product WHERE ProductID='" + productID + "'";
        ResultSet checkResultSet = executeQuery(checkQuery);
        checkResultSet.last();
        int rowCount = checkResultSet.getRow();
        checkResultSet.first();
        if (rowCount < 1)
            newProduct(record);
        else {
            //updateCustomer(record);
            responseObject.put("error", "Product already exists!");
        }
    }

    responseObject.put("records", recordArray.length());
    responseObject.put(DbSingleton.ProductSchema.COLUMN_ID, productID);

    return responseObject;
}

From source file:ProcessRequest.java

public JSONObject parseNewCustomer(JSONObject requestObject) throws SQLException, JSONException {
    JSONObject record = new JSONObject();
    JSONArray recordArray = requestObject.getJSONArray("records");
    String customerID = null;/*from  w w  w .j a va2  s .c  o m*/
    JSONObject responseObject = new JSONObject();
    for (int i = 0; i < recordArray.length(); i++) {
        record = recordArray.getJSONObject(i);
        customerID = record.getString(DbSingleton.CustomerSchema.COLUMN_ID);
        String checkQuery = "SELECT * FROM Customer WHERE CustomerID='" + customerID + "'";
        ResultSet checkResultSet = executeQuery(checkQuery);
        checkResultSet.last();
        int rowCount = checkResultSet.getRow();
        checkResultSet.first();
        if (rowCount < 1)
            newCustomer(record);
        else {
            //updateCustomer(record);
            responseObject.put("error", "Customer already exists!");
        }
    }

    responseObject.put("records", recordArray.length());
    responseObject.put(DbSingleton.CustomerSchema.COLUMN_ID, customerID);

    return responseObject;
}

From source file:ProcessRequest.java

public int insertNewTicket(JSONObject recordObject) throws SQLException, JSONException {
    String valuesPortion = "";
    String columnsPortion = "";

    recordObject.remove(DbSingleton.TicketSchema.COLUMN_TICKET_NUMBER);
    recordObject.put(DbSingleton.TicketSchema.COLUMN_TICKET_NUMBER, getNextTicketNumber());

    //get column names
    JsonParser parser = new JsonParser();
    JsonObject recordGson = parser.parse(recordObject.toString()).getAsJsonObject();
    Set<Map.Entry<String, JsonElement>> entrySet = recordGson.entrySet();
    {/* w  ww . j  a v  a 2  s.c o m*/
        Iterator<Map.Entry<String, JsonElement>> iterator = entrySet.iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, JsonElement> entry = iterator.next();
            if (entry.getKey().equals("metadata") || entry.getKey().equals("TicketID")) {
                //do nothing
            } else {
                JsonPrimitive value = entry.getValue().getAsJsonPrimitive();
                //if(value.isString() && value.getAsString().equals("")) {
                //don't want this null value/key pair
                //} else {
                if (!columnsPortion.equals("")) {
                    columnsPortion += ",";
                }
                columnsPortion += entry.getKey();
                //System.out.println(entry.getKey()+" : "+value.getAsString());
                //}
            }
        }
    }

    //get values
    {
        Iterator<Map.Entry<String, JsonElement>> iterator = entrySet.iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, JsonElement> entry = iterator.next();
            if (entry.getKey().equals("metadata") || entry.getKey().equals("TicketID")) {
                //do nothing
            } else {
                JsonPrimitive value = entry.getValue().getAsJsonPrimitive();
                //System.out.println(entry.getKey()+" : "+value.getAsString());
                if (value.isString()) {
                    if (!valuesPortion.equals("")) {
                        valuesPortion += ",";
                    }
                    //System.out.println(entry.getKey()+" : '"+value.getAsString()+"'");
                    valuesPortion += "'" + value.getAsString() + "'";
                } else if (value.isNumber()) {
                    if (value.getAsString().contains(".")) {
                        if (!valuesPortion.equals("")) {
                            valuesPortion += ",";
                        }
                        //System.out.println(entry.getKey()+" : "+(new Double(value.getAsDouble()).toString()));
                        valuesPortion += new Double(value.getAsDouble()).toString();
                    } else {
                        if (!valuesPortion.equals("")) {
                            valuesPortion += ",";
                        }
                        //System.out.println(entry.getKey()+" : "+(new Long(value.getAsLong()).toString()));
                        String ticketNumber = new Long(value.getAsLong()).toString();
                        valuesPortion += ticketNumber;
                    }
                } else if (value.isBoolean()) {
                    if (!valuesPortion.equals("")) {
                        valuesPortion += ",";
                    }
                    if (value.getAsBoolean())
                        valuesPortion += "1";
                    else
                        valuesPortion += "0";
                }
            }
        }
    }

    String insertQuery = "INSERT INTO " + DbSingleton.TICKET_TABLE_NAME + "(" + columnsPortion + ") VALUES ("
            + valuesPortion + ")";
    String identQuery = "SELECT IDENT_CURRENT ('" + DbSingleton.TICKET_TABLE_NAME + "') AS TicketID";

    executeQuery(insertQuery);
    ResultSet identResultSet = executeQuery(identQuery);
    identResultSet.first();
    //System.out.println(identResultSet.getRow());
    int ticketID = identResultSet.getInt(1);
    //System.out.println(ticketID);

    ////////////////////TICKET NUMBER CHECK
    boolean uniqueTicketNumber = false;
    while (!uniqueTicketNumber) {
        String ticketNumberCheckQuery = "SELECT * FROM " + DbSingleton.TICKET_TABLE_NAME + " WHERE "
                + DbSingleton.TicketSchema.COLUMN_TICKET_NUMBER + "="
                + recordObject.getInt(DbSingleton.TicketSchema.COLUMN_TICKET_NUMBER);
        ResultSet ticketNumberCheckResultSet = executeQuery(ticketNumberCheckQuery);
        int rowCount = 0;
        ticketNumberCheckResultSet.last();
        rowCount = ticketNumberCheckResultSet.getRow();
        ticketNumberCheckResultSet.first();
        if (rowCount < 1)
            throw new SQLException(LOG_TAG + ": wat...");
        else if (rowCount > 1) {
            uniqueTicketNumber = false;
        } else {
            uniqueTicketNumber = true;
        }
        if (!uniqueTicketNumber) {
            //update the ticket for the next iteration of this loop, attempting, again, to get a unique ticket number
            String uniqueTicketNumberQuery = "UPDATE " + DbSingleton.TICKET_TABLE_NAME + " SET "
                    + DbSingleton.TicketSchema.COLUMN_TICKET_NUMBER + "=" + getNextTicketNumber() + " WHERE "
                    + DbSingleton.TicketSchema.COLUMN_TICKET_ID + "=" + ticketID;
            executeQuery(uniqueTicketNumberQuery);
        }
    }
    ////////////////////

    return ticketID;
}

From source file:ProcessRequest.java

public int getNextTicketNumber() throws SQLException, JSONException {
    String ticketNumberQuery = "SELECT MAX(" + DbSingleton.TicketSchema.COLUMN_TICKET_NUMBER + ") FROM "
            + DbSingleton.TICKET_TABLE_NAME;
    System.out.println("querying ticket number...");
    ResultSet ticketNumberResultSet = executeQuery(ticketNumberQuery);
    int ticketNumber = 0;
    System.out.println("getting row count...");
    ticketNumberResultSet.last();//from  w  ww.  j av  a2  s.  com
    int rowCount = ticketNumberResultSet.getRow();
    ticketNumberResultSet.first();
    if (rowCount < 1) {
        //throw new SQLException(LOG_TAG+": wat...");
        //this means there aren't any tickets yet.
        //TODO: use the "beginning ticket number" value from the database, or 0
    } else {
        ticketNumber = ticketNumberResultSet.getInt(1);
    }
    System.out.println("next ticket number: " + ticketNumber);

    return ticketNumber + 1;
}