List of usage examples for java.sql ResultSet getFloat
float getFloat(String columnLabel) throws SQLException;
ResultSet object as a float in the Java programming language. From source file:org.etudes.component.app.jforum.JforumDataServiceImpl.java
/** * creates category/*from w ww .ja v a 2s . co m*/ * @param connection * @param toContextId * @param catName * @param moderated * @return categoy Id */ private int createCategory(Connection connection, String toContextId, String catName, int moderated, int gradable, Date startDate, Date endDate, int lockEndDate, int exisCatgeoryId) throws Exception { if (logger.isDebugEnabled()) logger.debug("creating category with title :" + catName + " for site : " + toContextId); int categoryId = -1; try { int order = 1; String categoryGetMaxOrderSql = "SELECT MAX(display_order) FROM jforum_categories"; PreparedStatement p = connection.prepareStatement(categoryGetMaxOrderSql); ResultSet rs = p.executeQuery(); if (rs.next()) { order = rs.getInt(1) + 1; } rs.close(); p.close(); String categoryAddNewSql = null; if (sqlService.getVendor().equals("oracle")) { categoryAddNewSql = "INSERT INTO jforum_categories (categories_id, title, display_order, moderated, gradable, start_date, end_date, lock_end_date) " + "VALUES (jforum_categories_seq.nextval, ?, ?, ?, ?, ?, ?, ?)"; p = connection.prepareStatement(categoryAddNewSql); p.setString(1, catName); p.setInt(2, order); p.setInt(3, moderated); p.setInt(4, gradable); if (startDate == null) { p.setTimestamp(5, null); } else { p.setTimestamp(5, new Timestamp(startDate.getTime())); } if (endDate == null) { p.setTimestamp(6, null); p.setInt(7, 0); } else { p.setTimestamp(6, new Timestamp(endDate.getTime())); p.setInt(7, lockEndDate); } p.executeUpdate(); p.close(); String categoryLastGeneratedCategoryId = "SELECT jforum_categories_seq.currval FROM DUAL"; p = connection.prepareStatement(categoryLastGeneratedCategoryId); rs = p.executeQuery(); if (rs.next()) { categoryId = rs.getInt(1); } rs.close(); p.close(); } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) { categoryAddNewSql = "INSERT INTO jforum_categories (title, display_order, moderated, gradable, start_date, end_date, lock_end_date) VALUES (?, ?, ?, ?, ?, ?, ?)"; p = connection.prepareStatement(categoryAddNewSql, Statement.RETURN_GENERATED_KEYS); p.setString(1, catName); p.setInt(2, order); p.setInt(3, moderated); p.setInt(4, gradable); if (startDate == null) { p.setTimestamp(5, null); } else { p.setTimestamp(5, new Timestamp(startDate.getTime())); } if (endDate == null) { p.setTimestamp(6, null); p.setInt(7, 0); } else { p.setTimestamp(6, new Timestamp(endDate.getTime())); p.setInt(7, lockEndDate); } p.executeUpdate(); rs = p.getGeneratedKeys(); if (rs.next()) { categoryId = rs.getInt(1); } rs.close(); p.close(); } String courseCategoryAddNewSql = "INSERT INTO jforum_sakai_course_categories (course_id,categories_id) VALUES (?, ?)"; p = connection.prepareStatement(courseCategoryAddNewSql); p.setString(1, toContextId); p.setInt(2, categoryId); p.execute(); p.close(); // create grade if category is gradable if (gradable == 1) { String gradeModelSelectByCategoryId = "SELECT grade_id, context, grade_type, forum_id, topic_id, points, add_to_gradebook, categories_id, min_posts, min_posts_required " + "FROM jforum_grade WHERE forum_id = 0 and topic_id = 0 and categories_id = ?"; PreparedStatement gradePrepStmnt = connection.prepareStatement(gradeModelSelectByCategoryId); gradePrepStmnt.setInt(1, exisCatgeoryId); ResultSet rsGrade = gradePrepStmnt.executeQuery(); float gradePoints = 0f; if (rsGrade.next()) { gradePoints = rsGrade.getFloat("points"); } int addToGradebook = rsGrade.getInt("add_to_gradebook"); boolean minPostsRequired = false; int minPosts = 0; if (rsGrade.getInt("min_posts_required") == 1) { minPostsRequired = true; minPosts = rsGrade.getInt("min_posts"); } rsGrade.close(); gradePrepStmnt.close(); int gradeId = createGrade(connection, toContextId, GRADE_BY_CATEGORY, 0, 0, categoryId, gradePoints, addToGradebook, minPostsRequired, minPosts, catName); // add to gradebook if ((gradeId > 0) && (addToGradebook == 1)) { createGradebookEntry(gradeId, catName, JForumUtil.toDoubleScore(gradePoints), endDate); } } } catch (SQLException e) { if (logger.isErrorEnabled()) logger.error("createCategory():Error occurred while creating category with title : " + catName); e.printStackTrace(); throw e; } return categoryId; }
From source file:org.etudes.component.app.jforum.JforumDataServiceImpl.java
/** * create topic//from w w w.j a v a 2s . co m * @param connection connection * @param forumId forum id * @param topicTitle topic title * @param userId user id * @param topicType topic type * @param firstPostId first post id * @return */ private int createTopic(Connection connection, String toContextId, int fromForumId, int fromTopicId, int forumId, String topicTitle, int userId, int topicType, int topicGrade, int firstPostId, Date startDate, Date endDate, int lockEndDate, Date gradebookEndDate) { if (logger.isDebugEnabled()) logger.debug("creating topic with topicName :" + topicTitle + " for forum_id : " + forumId); if (logger.isDebugEnabled()) logger.debug("Entering createTopic......"); int topicId = -1; try { PreparedStatement p = null; ResultSet rs = null; String topicAddNew = null; if (sqlService.getVendor().equals("oracle")) { topicAddNew = "INSERT INTO jforum_topics (topic_id, forum_id, topic_title, " + "user_id, topic_time, topic_first_post_id, topic_last_post_id, " + "topic_type, moderated, topic_grade, topic_export, start_date, end_date, lock_end_date)" + "VALUES (jforum_topics_seq.nextval, ?, ?, ?, ?, SYSDATE, ?, ?, ?, ?, ?, ?, ?, ?)"; p = connection.prepareStatement(topicAddNew); p.setInt(1, forumId); p.setString(2, topicTitle); p.setInt(3, userId); //p.setTimestamp(4, new Timestamp(System.currentTimeMillis())); p.setInt(4, 0); p.setInt(5, 0); p.setInt(6, topicType); p.setInt(7, 0); if (topicGrade == GRADE_YES) p.setInt(8, topicGrade); else p.setInt(8, GRADE_NO); p.setInt(9, EXPORT_YES); if (startDate == null) { p.setTimestamp(10, null); } else { p.setTimestamp(10, new Timestamp(startDate.getTime())); } if (endDate == null) { p.setTimestamp(11, null); p.setInt(12, 0); } else { p.setTimestamp(11, new Timestamp(endDate.getTime())); p.setInt(12, lockEndDate); } p.executeUpdate(); p.close(); String forumLastGeneratedTopicId = "SELECT jforum_topics_seq.currval FROM DUAL"; p = connection.prepareStatement(forumLastGeneratedTopicId); rs = p.executeQuery(); if (rs.next()) { topicId = rs.getInt(1); } rs.close(); p.close(); } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) { topicAddNew = "INSERT INTO jforum_topics (forum_id, topic_title, user_id, " + "topic_time, topic_first_post_id, topic_last_post_id, topic_type, moderated, topic_grade, topic_export, start_date, end_date, lock_end_date)" + "VALUES (?, ?, ?, NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?)"; p = connection.prepareStatement(topicAddNew, Statement.RETURN_GENERATED_KEYS); p.setInt(1, forumId); p.setString(2, topicTitle); p.setInt(3, userId); //p.setTimestamp(4, new Timestamp(System.currentTimeMillis())); p.setInt(4, 0); p.setInt(5, 0); p.setInt(6, topicType); p.setInt(7, 0); if (topicGrade == GRADE_YES) p.setInt(8, topicGrade); else p.setInt(8, GRADE_NO); p.setInt(9, EXPORT_YES); if (startDate == null) { p.setTimestamp(10, null); } else { p.setTimestamp(10, new Timestamp(startDate.getTime())); } if (endDate == null) { p.setTimestamp(11, null); p.setInt(12, 0); } else { p.setTimestamp(11, new Timestamp(endDate.getTime())); p.setInt(12, lockEndDate); } p.executeUpdate(); rs = p.getGeneratedKeys(); if (rs.next()) { topicId = rs.getInt(1); rs.close(); p.close(); } } //create grade for grade topic if (topicGrade == GRADE_YES) { String gradeModelSelectByForumTopicId = "SELECT grade_id, context, grade_type, forum_id, topic_id, points, add_to_gradebook, categories_id, min_posts, min_posts_required " + "FROM jforum_grade WHERE forum_id = ? and topic_id = ?"; PreparedStatement gradePrepStmnt = connection.prepareStatement(gradeModelSelectByForumTopicId); gradePrepStmnt.setInt(1, fromForumId); gradePrepStmnt.setInt(2, fromTopicId); ResultSet rsGrade = gradePrepStmnt.executeQuery(); float gradePoints = 0f; if (rsGrade.next()) { gradePoints = rsGrade.getFloat("points"); } int addToGradebook = rsGrade.getInt("add_to_gradebook"); boolean minPostsRequired = false; int minPosts = 0; if (rsGrade.getInt("min_posts_required") == 1) { minPostsRequired = true; minPosts = rsGrade.getInt("min_posts"); } rsGrade.close(); gradePrepStmnt.close(); int gradeId = createGrade(connection, toContextId, GRADE_BY_TOPIC, forumId, topicId, 0, gradePoints, addToGradebook, minPostsRequired, minPosts, topicTitle); if (startDate != null || endDate != null) { } else if (gradebookEndDate != null) { endDate = gradebookEndDate; } if ((gradeId > 0) && (addToGradebook == 1)) { createGradebookEntry(gradeId, topicTitle, gradePoints, endDate); } } } catch (SQLException e) { if (logger.isErrorEnabled()) logger.error("createTopic():Error while creating topic : " + e.toString()); e.printStackTrace(); } if (logger.isDebugEnabled()) logger.debug("Exiting createTopic......"); return topicId; }
From source file:org.ensembl.healthcheck.util.DBUtils.java
/** * Compare a particular column in two ResultSets. * //from w ww . ja v a 2s .co m * @param rs1 * The first ResultSet to compare. * @param rs2 * The second ResultSet to compare. * @param i * The index of the column to compare. * @return True if the type and value of the columns match. */ public static boolean compareColumns(ResultSet rs1, ResultSet rs2, int i, boolean warnNull) { try { ResultSetMetaData rsmd = rs1.getMetaData(); Connection con1 = rs1.getStatement().getConnection(); Connection con2 = rs2.getStatement().getConnection(); if (rs1.getObject(i) == null) { if (warnNull) { logger.fine("Column " + rsmd.getColumnName(i) + " is null in table " + rsmd.getTableName(i) + " in " + DBUtils.getShortDatabaseName(con1)); } return (rs2.getObject(i) == null); // true if both are null } if (rs2.getObject(i) == null) { if (warnNull) { logger.fine("Column " + rsmd.getColumnName(i) + " is null in table " + rsmd.getTableName(i) + " in " + DBUtils.getShortDatabaseName(con2)); } return (rs1.getObject(i) == null); // true if both are null } // Note deliberate early returns for performance reasons switch (rsmd.getColumnType(i)) { case Types.INTEGER: return rs1.getInt(i) == rs2.getInt(i); case Types.SMALLINT: return rs1.getInt(i) == rs2.getInt(i); case Types.TINYINT: return rs1.getInt(i) == rs2.getInt(i); case Types.VARCHAR: String s1 = rs1.getString(i); String s2 = rs2.getString(i); // ignore "AUTO_INCREMENT=" part in final part of table // definition s1 = s1.replaceAll("AUTO_INCREMENT=[0-9]+ ", ""); s2 = s2.replaceAll("AUTO_INCREMENT=[0-9]+ ", ""); return s1.equals(s2); case Types.FLOAT: return rs1.getFloat(i) == rs2.getFloat(i); case Types.DOUBLE: return rs1.getDouble(i) == rs2.getDouble(i); case Types.TIMESTAMP: return rs1.getTimestamp(i).equals(rs2.getTimestamp(i)); default: // treat everything else as a String (should deal with ENUM and // TEXT) if (rs1.getString(i) == null || rs2.getString(i) == null) { return true; // ???? } else { return rs1.getString(i).equals(rs2.getString(i)); } } // switch } catch (SQLException se) { throw new SqlUncheckedException("Could not compare two columns sets", se); } }
From source file:com.emc.plants.service.impl.ReportGeneratorBean.java
/** * Run the report to get the top selling items for a range of dates. * * @param startdate Start of date range. * @param enddate End of date range./*from w w w . ja v a2s. c om*/ * @param quantity Number of items to return in report. * @param reportFormat - Report format information. * @return Report containing results. */ @SuppressWarnings("unchecked") public Report getTopSellersForDates(java.util.Date startdate, java.util.Date enddate, int quantity, ReportFormat reportFormat) { Report report = null; Connection conn = null; ResultSet results = null; PreparedStatement sqlStatement = null; try { // Establish connection to datasource. String orderItemsTableName = "ORDERITEM"; DataSource ds = (DataSource) Util.getInitialContext().lookup("jdbc/PlantsByWebSphereDataSource"); conn = ds.getConnection(); // Set sort order of ascending or descending. String sortOrder; if (reportFormat.isAscending()) sortOrder = "ASC"; else sortOrder = "DESC"; // Set up where by clause. String startDateString = Long.toString(startdate.getTime()); if (startDateString.length() < 14) { StringBuffer sb = new StringBuffer(Util.ZERO_14); sb.replace((14 - startDateString.length()), 14, startDateString); startDateString = sb.toString(); } String endDateString = Long.toString(enddate.getTime()); if (endDateString.length() < 14) { StringBuffer sb = new StringBuffer(Util.ZERO_14); sb.replace((14 - endDateString.length()), 14, endDateString); endDateString = sb.toString(); } String whereString = " WHERE sellDate BETWEEN '" + startDateString + "' AND '" + endDateString + "' "; // Create SQL statement. String sqlString = "SELECT inventoryID, name, category," + " SUM(quantity * (price - cost)) as PROFIT FROM " + orderItemsTableName + whereString + " GROUP BY inventoryID, name, category ORDER BY PROFIT " + sortOrder + ", name"; Util.debug("sqlstring=" + sqlString); sqlStatement = conn.prepareStatement(sqlString); results = sqlStatement.executeQuery(); int i; // Initialize vectors to store data in. Vector[] vecs = new Vector[4]; for (i = 0; i < vecs.length; i++) { vecs[i] = new Vector(); } // Sift thru results. int count = 0; while ((results.next()) && (count < quantity)) { count++; i = 1; vecs[0].addElement(results.getString(i++)); vecs[1].addElement(results.getString(i++)); vecs[2].addElement(new Integer(results.getInt(i++))); vecs[3].addElement(new Float(results.getFloat(i++))); } // Create report. report = new Report(); report.setReportFieldByRow(Report.ORDER_INVENTORY_ID, vecs[0]); report.setReportFieldByRow(Report.ORDER_INVENTORY_NAME, vecs[1]); report.setReportFieldByRow(Report.ORDER_INVENTORY_CATEGORY, vecs[2]); report.setReportFieldByRow(Report.PROFITS, vecs[3]); } catch (Exception e) { Util.debug("exception in ReportGeneratorBean:getTopSellersForDates. " + e); e.printStackTrace(); } finally { // Clean up. try { if (results != null) results.close(); } catch (Exception ignore) { } try { if (sqlStatement != null) sqlStatement.close(); } catch (Exception ignore) { } // Close Connection. try { if (conn != null) conn.close(); } catch (Exception ignore) { } } return report; }
From source file:org.apache.bigtop.itest.hive.TestJdbc.java
@Test public void preparedStmtAndResultSet() throws SQLException { final String tableName = "bigtop_jdbc_psars_test_table"; try (Statement stmt = conn.createStatement()) { stmt.execute("drop table if exists " + tableName); stmt.execute("create table " + tableName + " (bo boolean, ti tinyint, db double, fl float, " + "i int, lo bigint, sh smallint, st varchar(32))"); }//from w ww .j a va 2s.c om // NOTE Hive 1.2 theoretically support binary, Date & Timestamp in JDBC, but I get errors when I // try to put them in the query. try (PreparedStatement ps = conn .prepareStatement("insert into " + tableName + " values (?, ?, ?, ?, ?, ?, ?, ?)")) { ps.setBoolean(1, true); ps.setByte(2, (byte) 1); ps.setDouble(3, 3.141592654); ps.setFloat(4, 3.14f); ps.setInt(5, 3); ps.setLong(6, 10L); ps.setShort(7, (short) 20); ps.setString(8, "abc"); ps.executeUpdate(); } try (PreparedStatement ps = conn.prepareStatement("insert into " + tableName + " (i, st) " + "values(?, ?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) { ps.setNull(1, Types.INTEGER); ps.setObject(2, "mary had a little lamb"); ps.executeUpdate(); ps.setNull(1, Types.INTEGER, null); ps.setString(2, "its fleece was white as snow"); ps.clearParameters(); ps.setNull(1, Types.INTEGER, null); ps.setString(2, "its fleece was white as snow"); ps.execute(); } try (Statement stmt = conn.createStatement()) { ResultSet rs = stmt.executeQuery("select * from " + tableName); ResultSetMetaData md = rs.getMetaData(); int colCnt = md.getColumnCount(); LOG.debug("Column count is " + colCnt); for (int i = 1; i <= colCnt; i++) { LOG.debug("Looking at column " + i); String strrc = md.getColumnClassName(i); LOG.debug("Column class name is " + strrc); int intrc = md.getColumnDisplaySize(i); LOG.debug("Column display size is " + intrc); strrc = md.getColumnLabel(i); LOG.debug("Column label is " + strrc); strrc = md.getColumnName(i); LOG.debug("Column name is " + strrc); intrc = md.getColumnType(i); LOG.debug("Column type is " + intrc); strrc = md.getColumnTypeName(i); LOG.debug("Column type name is " + strrc); intrc = md.getPrecision(i); LOG.debug("Precision is " + intrc); intrc = md.getScale(i); LOG.debug("Scale is " + intrc); boolean boolrc = md.isAutoIncrement(i); LOG.debug("Is auto increment? " + boolrc); boolrc = md.isCaseSensitive(i); LOG.debug("Is case sensitive? " + boolrc); boolrc = md.isCurrency(i); LOG.debug("Is currency? " + boolrc); intrc = md.getScale(i); LOG.debug("Scale is " + intrc); intrc = md.isNullable(i); LOG.debug("Is nullable? " + intrc); boolrc = md.isReadOnly(i); LOG.debug("Is read only? " + boolrc); } while (rs.next()) { LOG.debug("bo = " + rs.getBoolean(1)); LOG.debug("bo = " + rs.getBoolean("bo")); LOG.debug("ti = " + rs.getByte(2)); LOG.debug("ti = " + rs.getByte("ti")); LOG.debug("db = " + rs.getDouble(3)); LOG.debug("db = " + rs.getDouble("db")); LOG.debug("fl = " + rs.getFloat(4)); LOG.debug("fl = " + rs.getFloat("fl")); LOG.debug("i = " + rs.getInt(5)); LOG.debug("i = " + rs.getInt("i")); LOG.debug("lo = " + rs.getLong(6)); LOG.debug("lo = " + rs.getLong("lo")); LOG.debug("sh = " + rs.getShort(7)); LOG.debug("sh = " + rs.getShort("sh")); LOG.debug("st = " + rs.getString(8)); LOG.debug("st = " + rs.getString("st")); LOG.debug("tm = " + rs.getObject(8)); LOG.debug("tm = " + rs.getObject("st")); LOG.debug("tm was null " + rs.wasNull()); } LOG.debug("bo is column " + rs.findColumn("bo")); int intrc = rs.getConcurrency(); LOG.debug("concurrency " + intrc); intrc = rs.getFetchDirection(); LOG.debug("fetch direction " + intrc); intrc = rs.getType(); LOG.debug("type " + intrc); Statement copy = rs.getStatement(); SQLWarning warning = rs.getWarnings(); while (warning != null) { LOG.debug("Found a warning: " + warning.getMessage()); warning = warning.getNextWarning(); } rs.clearWarnings(); } }
From source file:org.latticesoft.util.resource.dao.Param.java
private Object readValue(ResultSet rs) throws SQLException { Object retVal = null;/*from w w w . jav a 2 s . c o m*/ switch (this.sqlType) { case Types.VARCHAR: case Types.CHAR: String s = null; if (this.getSqlIndex() == 0) { s = rs.getString(this.getSqlName()); } else { s = rs.getString(this.getSqlIndex()); } retVal = s; break; case Types.BOOLEAN: boolean b = false; if (this.getSqlIndex() == 0) { b = rs.getBoolean(this.getSqlName()); } else { b = rs.getBoolean(this.getSqlIndex()); } retVal = new Boolean(b); break; case Types.INTEGER: int i = 0; if (this.getSqlIndex() == 0) { i = rs.getInt(this.getSqlName()); } else { i = rs.getInt(this.getSqlIndex()); } retVal = new Integer(i); break; case Types.SMALLINT: short ss = 0; if (this.getSqlIndex() == 0) { ss = rs.getShort(this.getSqlName()); } else { ss = rs.getShort(this.getSqlIndex()); } retVal = new Short(ss); break; case Types.TINYINT: byte bb = 0; if (this.getSqlIndex() == 0) { bb = rs.getByte(this.getSqlName()); } else { bb = rs.getByte(this.getSqlIndex()); } retVal = new Byte(bb); break; case Types.BIGINT: long l = 0; if (this.getSqlIndex() == 0) { l = rs.getLong(this.getSqlName()); } else { l = rs.getLong(this.getSqlIndex()); } retVal = new Long(l); break; case Types.DOUBLE: double dd = 0; if (this.getSqlIndex() == 0) { dd = rs.getDouble(this.getSqlName()); } else { dd = rs.getDouble(this.getSqlIndex()); } retVal = new Double(dd); break; case Types.FLOAT: float f = 0; if (this.getSqlIndex() == 0) { f = rs.getFloat(this.getSqlName()); } else { f = rs.getFloat(this.getSqlIndex()); } retVal = new Float(f); break; case Types.NUMERIC: BigDecimal bd = null; if (this.getSqlIndex() == 0) { bd = rs.getBigDecimal(this.getSqlName()); } else { bd = rs.getBigDecimal(this.getSqlIndex()); } retVal = bd; break; case Types.TIMESTAMP: Timestamp ts = null; if (this.getSqlIndex() == 0) { ts = rs.getTimestamp(this.getSqlName()); } else { ts = rs.getTimestamp(this.getSqlIndex()); } retVal = ts; break; default: if (this.getSqlIndex() == 0) { retVal = rs.getObject(this.getSqlName()); } else { retVal = rs.getObject(this.getSqlIndex()); } break; } if (log.isDebugEnabled()) { log.debug(this.getAttribute() + "=" + retVal); } return retVal; }
From source file:csiro.pidsvc.mappingstore.ManagerJson.java
@SuppressWarnings("unchecked") public JSONObject getMappings(int page, String mappingPath, String type, String creator, int includeDeprecated) throws SQLException { PreparedStatement pst = null; ResultSet rs = null; JSONObject ret = new JSONObject(); final int pageSize = 10; final String sourceView = (includeDeprecated == 2 ? "vw_deprecated_mapping" : (includeDeprecated == 1 ? "vw_latest_mapping" : "vw_active_mapping")); try {// w ww. j a v a 2 s .co m String query = "mapping_path IS NOT NULL"; if (mappingPath != null && !mappingPath.isEmpty()) query += " AND (title ILIKE ? OR mapping_path ILIKE ?)"; if (type != null && !type.isEmpty()) query += " AND type = ?"; if (creator != null && !creator.isEmpty()) query += " AND creator = ?"; query = "SELECT COUNT(*) FROM " + sourceView + (query.isEmpty() ? "" : " WHERE " + query) + ";\n" + "SELECT mapping_id, mapping_path, title, description, creator, type, to_char(date_start, 'DD/MM/YYYY HH24:MI') AS date_start, to_char(date_end, 'DD/MM/YYYY HH24:MI') AS date_end FROM " + sourceView + (query.isEmpty() ? "" : " WHERE " + query) + " ORDER BY COALESCE(title, mapping_path) LIMIT " + pageSize + " OFFSET " + ((page - 1) * pageSize) + ";"; pst = _connection.prepareStatement(query); // Bind parameters twice to two almost identical queries. for (int i = 1, j = 0; j < 2; ++j) { if (!mappingPath.isEmpty()) { pst.setString(i++, "%" + mappingPath.replace("\\", "\\\\") + "%"); pst.setString(i++, "%" + mappingPath.replace("\\", "\\\\") + "%"); } if (!type.isEmpty()) pst.setString(i++, type); if (!creator.isEmpty()) pst.setString(i++, creator); } if (pst.execute()) { rs = pst.getResultSet(); rs.next(); ret.put("count", rs.getInt(1)); ret.put("page", page); ret.put("pages", (int) Math.ceil(rs.getFloat(1) / pageSize)); JSONArray jsonArr = new JSONArray(); for (pst.getMoreResults(), rs = pst.getResultSet(); rs.next();) { // String dateStart = sdf.format(sdfdb.parse(rs.getString("date_start"))); // String dateEnd = rs.getString("date_end"); // if (dateEnd != null) // dateEnd = sdf.format(sdfdb.parse(dateEnd)); jsonArr.add(JSONObjectHelper.create("mapping_id", rs.getString("mapping_id"), "mapping_path", rs.getString("mapping_path"), "title", rs.getString("title"), "description", rs.getString("description"), "creator", rs.getString("creator"), "type", rs.getString("type"), "date_start", rs.getString("date_start"), "date_end", rs.getString("date_end"), "date", "")); } ret.put("results", jsonArr); } } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } return ret; }
From source file:org.etudes.component.app.jforum.JForumGradeServiceImpl.java
/** * //from w w w . j av a 2s . co m * @param sql * @param fields * @return */ protected List<Grade> readGrades(String sql, Object[] fields) { final List<Grade> grades = new ArrayList<Grade>(); this.sqlService.dbRead(sql, fields, new SqlReader() { public Object readSqlResultRecord(ResultSet result) { try { Grade grade = new GradeImpl(); ((GradeImpl) grade).setId(result.getInt("grade_id")); grade.setContext(result.getString("context")); grade.setType(result.getInt("grade_type")); grade.setForumId(result.getInt("forum_id")); grade.setTopicId(result.getInt("topic_id")); grade.setPoints(result.getFloat("points")); grade.setAddToGradeBook(result.getInt("add_to_gradebook") == 1); grade.setCategoryId(result.getInt("categories_id")); grade.setMinimumPostsRequired(result.getInt("min_posts_required") == 1); grade.setMinimumPosts(result.getInt("min_posts")); grades.add(grade); return null; } catch (SQLException e) { if (logger.isWarnEnabled()) { logger.warn("readGrades: " + e, e); } return null; } } }); return grades; }
From source file:com.nway.spring.jdbc.bean.JavassistBeanProcessor.java
private Object processColumn(ResultSet rs, int index, Class<?> propType, String writer, StringBuilder handler) throws SQLException { if (propType.equals(String.class)) { handler.append("bean.").append(writer).append("(").append("$1.getString(").append(index).append("));"); return rs.getString(index); } else if (propType.equals(Integer.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getInt(").append(index).append("));"); return rs.getInt(index); } else if (propType.equals(Integer.class)) { handler.append("bean.").append(writer).append("(").append("integerValue($1.getInt(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Integer.class); } else if (propType.equals(Long.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getLong(").append(index).append("));"); return rs.getLong(index); } else if (propType.equals(Long.class)) { handler.append("bean.").append(writer).append("(").append("longValue($1.getLong(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Long.class); } else if (propType.equals(java.sql.Date.class)) { handler.append("bean.").append(writer).append("(").append("$1.getDate(").append(index).append("));"); return rs.getDate(index); } else if (propType.equals(java.util.Date.class) || propType.equals(Timestamp.class)) { handler.append("bean.").append(writer).append("(").append("$1.getTimestamp(").append(index) .append("));"); return rs.getTimestamp(index); } else if (propType.equals(Double.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getDouble(").append(index).append("));"); return rs.getDouble(index); } else if (propType.equals(Double.class)) { handler.append("bean.").append(writer).append("(").append("doubleValue($1.getDouble(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Double.class); } else if (propType.equals(Float.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getFloat(").append(index).append("));"); return rs.getFloat(index); } else if (propType.equals(Float.class)) { handler.append("bean.").append(writer).append("(").append("floatValue($1.getFloat(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Float.class); } else if (propType.equals(Time.class)) { handler.append("bean.").append(writer).append("(").append("$1.getTime(").append(index).append("));"); return rs.getTime(index); } else if (propType.equals(Boolean.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getBoolean(").append(index).append("));"); return rs.getBoolean(index); } else if (propType.equals(Boolean.class)) { handler.append("bean.").append(writer).append("(").append("booleanValue($1.getBoolean(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Boolean.class); } else if (propType.equals(byte[].class)) { handler.append("bean.").append(writer).append("(").append("$1.getBytes(").append(index).append("));"); return rs.getBytes(index); } else if (BigDecimal.class.equals(propType)) { handler.append("bean.").append(writer).append("(").append("$1.getBigDecimal(").append(index) .append("));"); return rs.getBigDecimal(index); } else if (Blob.class.equals(propType)) { handler.append("bean.").append(writer).append("(").append("$1.getBlob(").append(index).append("));"); return rs.getBlob(index); } else if (Clob.class.equals(propType)) { handler.append("bean.").append(writer).append("(").append("$1.getClob(").append(index).append("));"); return rs.getClob(index); } else if (propType.equals(Short.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getShort(").append(index).append("));"); return rs.getShort(index); } else if (propType.equals(Short.class)) { handler.append("bean.").append(writer).append("(").append("shortValue($1.getShort(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Short.class); } else if (propType.equals(Byte.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getByte(").append(index).append("));"); return rs.getByte(index); } else if (propType.equals(Byte.class)) { handler.append("bean.").append(writer).append("(").append("byteValue($1.getByte(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Byte.class); } else {/*from w w w. j a va 2 s .co m*/ handler.append("bean.").append(writer).append("(").append("(").append(propType.getName()).append(")") .append("$1.getObject(").append(index).append("));"); return rs.getObject(index); } }
From source file:Logica.Usuario.java
/** * * @param nit//w w w.jav a2 s .c o m * @return ArayList * @throws RemoteException * * Genera un listado con los tems asociados a un proveedor en particular */ @Override public ArrayList<ItemInventario> itemxProv(String nit) throws RemoteException { EntityManagerFactory emf = Persistence.createEntityManagerFactory("Biot_ServerPU"); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; String statement = "select item.cinterno, item.descripcion, item.presentacion, item.cantidad, item.precio, item.ccalidad, item.cesp" + " from ixp, proveedor, item" + " where ixp.cinterno =item.cinterno and ixp.nit = proveedor.nit and proveedor.nit= ?;"; ItemInventario item = null; ArrayList<ItemInventario> lista = new ArrayList<>(); try { con = Conexion.conexion.getConnection(); ps = con.prepareStatement(statement); ps.setString(1, nit); rs = ps.executeQuery(); while (rs.next()) { item = new ItemInventario(rs.getString(1), rs.getString(2), rs.getString(3), rs.getFloat(4), rs.getFloat(5), rs.getString(6), "", "", rs.getString(7)); lista.add(item); } } catch (SQLException ex) { System.out.println("Error funcion \"Item por Proveedor \""); Logger.getLogger(Usuario.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (ps != null) { ps.close(); } if (rs != null) { rs.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { System.out.println("Error cerrando conexion"); } } return lista; }