List of usage examples for java.sql ResultSet getTime
java.sql.Time getTime(String columnLabel) throws SQLException;
ResultSet object as a java.sql.Time object in the Java programming language. From source file:PurchasesModel.PurchasesWS.java
/** * Web service operation//from w w w .jav a 2 s. c o m * @param access_token * @param user_id * @return */ @WebMethod(operationName = "getPurchases") public ArrayList<String> getPurchases(@WebParam(name = "access_token") String access_token) throws IOException, ParseException { ArrayList<String> answers = new ArrayList<String>(); Connection conn = DbConnectionManager.getConnection(); String targetIS = "ValidateToken"; String urlParameters = "access_token=" + access_token; HttpURLConnection urlConn = UrlConnectionManager.doReqPost(targetIS, urlParameters); String resp = UrlConnectionManager.getResponse(urlConn); JSONParser parser = new JSONParser(); JSONObject obj = (JSONObject) parser.parse(resp); String statusResp = (String) obj.get("status"); String username = (String) obj.get("username"); System.out.println(statusResp); switch (statusResp) { case "valid": System.out.println(hasPurchases(username)); if (hasPurchases(username)) { try { String query = "SELECT * FROM purchase WHERE purchase.buyer = '" + username + "' ORDER BY purchase.purchase_id DESC"; Statement stmt = conn.createStatement(); ResultSet rslt = stmt.executeQuery(query); while (rslt.next()) { int product_id = rslt.getInt("purchase_id"); String product_name = rslt.getString("product_name"); int product_price = rslt.getInt("product_price"); String seller = rslt.getString("seller"); String buyer = rslt.getString("buyer"); String image = rslt.getString("image"); int quantity = rslt.getInt("quantity"); String consignee = rslt.getString("consignee"); String fulladdress = rslt.getString("fulladdressbuyer"); int postalcode = rslt.getInt("postalcode"); String phonenumber = rslt.getString("newphonenumber"); String creditcard = rslt.getString("creditcard"); String verification = rslt.getString("verification"); Date dateBoughtF = rslt.getDate("datebought"); SimpleDateFormat simpledatefo = new SimpleDateFormat("dd/MM/yyyy"); Time timeBoughtF = rslt.getTime("timebought"); SimpleDateFormat simpletimefo = new SimpleDateFormat("dd/MM/yyyy"); String datebought = rslt.getString("datebought"); String timebought = rslt.getString("timebought"); int totalprice = product_price * quantity; String answer = "<li>" + "<span id='date'>" + "<b>" + datebought + "</b>" + "<br/>" + "at " + timebought + "<hr></hr>" + "</span>" + "<div class='item-list-product'>" + "<div style='position:absolute'>" + "<a href='" + image + "'><img class='img-item' src='" + image + "'></img></a>" + "</div>" + "<div id='product-info' style='width:250px'>" + "<span><b>" + product_name + "</b></span><br/>" + "<span>IDR " + totalprice + "</span> <br/>" + "<span>" + quantity + "pcs</span><br/>" + "<span>@IDR " + product_price + "</span>" + "</div>" + "<div id='eddel' style='font-size:12px;left:380px;width:220px'>" + "<span>Delivery to <b>" + consignee + "</b> </span> <br/>" + "<span>" + fulladdress + "</span> <br/>" + "<span>" + postalcode + "</span><br/>" + "<span>" + phonenumber + "</span>" + "</div>" + "</div>" + "<span style=\"margin-left:120px;font-size:12px\">bought from <b>" + seller + "</b></span>" + "</li>" + "<br>"; answers.add(answer); } } catch (SQLException ex) { System.out.println("Insert to database failed: An Exception has occurred! " + ex); } } else { String answer = "<b>You have not purchased any product.<b>"; System.out.println(answer); answers.add(answer); } break; case "non-valid": String answer = "2"; answers.add(answer); break; default: answer = "3"; answers.add(answer); break; } return answers; }
From source file:org.apache.phoenix.end2end.CSVCommonsLoaderIT.java
@Test public void testAllDatatypes() throws Exception { CSVParser parser = null;/* www. jav a 2 s. c o m*/ PhoenixConnection conn = null; try { // Create table String statements = "CREATE TABLE IF NOT EXISTS " + DATATYPE_TABLE + " (CKEY VARCHAR NOT NULL PRIMARY KEY," + " CVARCHAR VARCHAR, CCHAR CHAR(10), CINTEGER INTEGER, CDECIMAL DECIMAL(31,10), CUNSIGNED_INT UNSIGNED_INT, CBOOLEAN BOOLEAN, CBIGINT BIGINT, CUNSIGNED_LONG UNSIGNED_LONG, CTIME TIME, CDATE DATE);"; conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class); PhoenixRuntime.executeStatements(conn, new StringReader(statements), null); // Upsert CSV file CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, DATATYPE_TABLE, Collections.<String>emptyList(), true); csvUtil.upsert(new StringReader(DATATYPES_CSV_VALUES)); // Compare Phoenix ResultSet with CSV file content PreparedStatement statement = conn.prepareStatement( "SELECT CKEY, CVARCHAR, CCHAR, CINTEGER, CDECIMAL, CUNSIGNED_INT, CBOOLEAN, CBIGINT, CUNSIGNED_LONG, CTIME, CDATE FROM " + DATATYPE_TABLE); ResultSet phoenixResultSet = statement.executeQuery(); parser = new CSVParser(new StringReader(DATATYPES_CSV_VALUES), csvUtil.getFormat()); for (CSVRecord record : parser) { assertTrue(phoenixResultSet.next()); int i = 0; int size = record.size(); for (String value : record) { assertEquals(value, phoenixResultSet.getObject(i + 1).toString().toUpperCase()); if (i < size - 2) break; i++; } // special case for matching date, time values String timeFieldValue = record.get(9); assertEquals(timeFieldValue.isEmpty() ? null : DateUtil.parseTime(record.get(9)), phoenixResultSet.getTime("CTIME")); String dateField = record.get(10); assertEquals(dateField.isEmpty() ? null : DateUtil.parseDate(record.get(10)), phoenixResultSet.getDate("CDATE")); } assertFalse(phoenixResultSet.next()); } finally { if (parser != null) parser.close(); if (conn != null) conn.close(); } }
From source file:br.bookmark.db.util.ResultSetUtils.java
/** * Map JDBC objects to Java equivalents. * Used by getBean() and getBeans().//from ww w. j a va2 s . c o m * <p> * Some types not supported. * Many not work with all drivers. * <p> * Makes binary conversions of BIGINT, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, * REAL, SMALLINT, TIME, TIMESTAMP, TINYINT. * Makes Sting conversions of CHAR, CLOB, VARCHAR, LONGVARCHAR, BLOB, LONGVARBINARY, * VARBINARY. * <p> * DECIMAL, INTEGER, SMALLINT, TIMESTAMP, CHAR, VARCHAR tested with MySQL and Poolman. * Others not guaranteed. * @param classeDestino * @throws NoSuchFieldException * @throws SecurityException */ private static void putEntry(Map properties, ResultSetMetaData metaData, ResultSet resultSet, int i, Class classeDestino) throws Exception { /* In a perfect universe, this would be enough properties.put( metaData.getColumnName(i), resultSet.getObject(i)); But only String, Timestamp, and Integer seem to get through that way. */ String columnName = metaData.getColumnName(i); // Testa se uma FK /*Field[] fields = classeDestino.getDeclaredFields(); for (int j = 0; j < fields.length; j++) { if (fields[j].getAnnotation(DBFK.class) != null) { properties.put(columnName, resultSet.getString(i)); } }*/ //System.out.println(i+"-"+metaData.getColumnType(i)); switch (metaData.getColumnType(i)) { // http://java.sun.com/j2se/1.3.0/docs/api/java/sql/Types.html case Types.BIGINT: properties.put(columnName, new Long(resultSet.getLong(i))); break; case Types.DATE: properties.put(columnName, resultSet.getDate(i)); break; case Types.DECIMAL: case Types.DOUBLE: properties.put(columnName, new Double(resultSet.getDouble(i))); break; case Types.FLOAT: properties.put(columnName, new Float(resultSet.getFloat(i))); break; case Types.INTEGER: int valor = 0; try { // Se o campo esta vazio d erro valor = resultSet.getInt(i); } catch (SQLException e) { } properties.put(columnName, new Integer(valor)); break; case Types.REAL: properties.put(columnName, new Double(resultSet.getString(i))); break; case Types.SMALLINT: properties.put(columnName, new Short(resultSet.getShort(i))); break; case Types.TIME: properties.put(columnName, resultSet.getTime(i)); break; case Types.TIMESTAMP: properties.put(columnName, resultSet.getTimestamp(i)); break; // :FIXME: Throws java.lang.ClassCastException: java.lang.Integer // :FIXME: with Poolman and MySQL unless use getString. case Types.TINYINT: properties.put(columnName, new Byte(resultSet.getString(i))); break; case Types.CHAR: case Types.CLOB: case Types.VARCHAR: case Types.LONGVARCHAR: // :FIXME: Handle binaries differently? case Types.BLOB: case Types.LONGVARBINARY: case Types.VARBINARY: properties.put(columnName, resultSet.getString(i)); break; /* :FIXME: Add handlers for ARRAY BINARY BIT DISTINCT JAVA_OBJECT NULL NUMERIC OTHER REF STRUCT */ // Otherwise, pass as *String property to be converted default: properties.put(columnName + "String", resultSet.getString(i)); break; } // end switch }
From source file:org.apache.sqoop.tool.ImportTool.java
/** * Return the max value in the incremental-import test column. This * value must be numeric./*from ww w .j a v a 2 s . co m*/ */ private Object getMaxColumnId(SqoopOptions options) throws SQLException { StringBuilder sb = new StringBuilder(); String query; sb.append("SELECT MAX("); sb.append(options.getIncrementalTestColumn()); sb.append(") FROM "); if (options.getTableName() != null) { // Table import sb.append(options.getTableName()); String where = options.getWhereClause(); if (null != where) { sb.append(" WHERE "); sb.append(where); } query = sb.toString(); } else { // Free form table based import sb.append("("); sb.append(options.getSqlQuery()); sb.append(") sqoop_import_query_alias"); query = sb.toString().replaceAll("\\$CONDITIONS", "(1 = 1)"); } Connection conn = manager.getConnection(); Statement s = null; ResultSet rs = null; try { LOG.info("Maximal id query for free form incremental import: " + query); s = conn.createStatement(); rs = s.executeQuery(query); if (!rs.next()) { // This probably means the table is empty. LOG.warn("Unexpected: empty results for max value query?"); return null; } ResultSetMetaData rsmd = rs.getMetaData(); checkColumnType = rsmd.getColumnType(1); if (checkColumnType == Types.TIMESTAMP) { return rs.getTimestamp(1); } else if (checkColumnType == Types.DATE) { return rs.getDate(1); } else if (checkColumnType == Types.TIME) { return rs.getTime(1); } else { return rs.getObject(1); } } finally { try { if (null != rs) { rs.close(); } } catch (SQLException sqlE) { LOG.warn("SQL Exception closing resultset: " + sqlE); } try { if (null != s) { s.close(); } } catch (SQLException sqlE) { LOG.warn("SQL Exception closing statement: " + sqlE); } } }
From source file:com.splicemachine.derby.impl.load.HdfsImportIT.java
@Test public void testAlternateDateAndTimeImport() throws Exception { methodWatcher.executeUpdate("delete from " + spliceSchemaWatcher.schemaName + "." + TABLE_12); PreparedStatement ps = methodWatcher.prepareStatement(format("call SYSCS_UTIL.IMPORT_DATA(" + "'%s'," + // schema name "'%s'," + // table name "null," + // insert column list "'%s'," + // file path "','," + // column delimiter "null," + // character delimiter "null," + // timestamp format "'MM/dd/yyyy'," + // date format "'HH.mm.ss'," + // time format "%d," + // max bad records "'%s'," + // bad record dir "null," + // has one line records "null)", // char set spliceSchemaWatcher.schemaName, TABLE_12, getResourceDirectory() + "dateAndTime.in", 0, BADDIR.getCanonicalPath())); ps.execute();//from w w w . j a va 2 s. com ResultSet rs = methodWatcher .executeQuery(format("select * from %s.%s", spliceSchemaWatcher.schemaName, TABLE_12)); List<String> results = Lists.newArrayList(); while (rs.next()) { Date d = rs.getDate(1); Time t = rs.getTime(2); assertNotNull("Date is null!", d); assertNotNull("Time is null!", t); results.add(String.format("Date:%s,Time:%s", d, t)); } Assert.assertTrue("Incorrect number of rows imported", results.size() == 2); }
From source file:org.sakaiproject.webservices.SakaiReport.java
private String getColumnValue(ResultSet rs, int colType, int colIndex) throws SQLException, IOException { String value = ""; switch (colType) { case Types.BIT: case Types.JAVA_OBJECT: value = handleObject(rs.getObject(colIndex)); break;/*from w w w.jav a 2 s. c o m*/ case Types.BOOLEAN: boolean b = rs.getBoolean(colIndex); value = Boolean.valueOf(b).toString(); break; case NCLOB: // todo : use rs.getNClob case Types.CLOB: Clob c = rs.getClob(colIndex); if (c != null) { value = read(c); } break; case Types.BIGINT: value = handleLong(rs, colIndex); break; case Types.DECIMAL: case Types.DOUBLE: case Types.FLOAT: case Types.REAL: case Types.NUMERIC: value = handleBigDecimal(rs.getBigDecimal(colIndex)); break; case Types.INTEGER: case Types.TINYINT: case Types.SMALLINT: value = handleInteger(rs, colIndex); break; case Types.DATE: value = handleDate(rs, colIndex); break; case Types.TIME: value = handleTime(rs.getTime(colIndex)); break; case Types.TIMESTAMP: value = handleTimestamp(rs.getTimestamp(colIndex)); break; case NVARCHAR: // todo : use rs.getNString case NCHAR: // todo : use rs.getNString case LONGNVARCHAR: // todo : use rs.getNString case Types.LONGVARCHAR: case Types.VARCHAR: case Types.CHAR: value = rs.getString(colIndex); break; case Types.VARBINARY: case Types.BINARY: value = handleRaw(rs.getBytes(colIndex)); break; default: value = ""; } if (value == null) { value = ""; } return value; }
From source file:org.pentaho.platform.dataaccess.datasource.wizard.service.agile.CsvTransformGeneratorIT.java
public void testLoadTable1() throws Exception { IPentahoSession session = new StandaloneSession("test"); KettleSystemListener.environmentInit(session); ModelInfo info = createModel();//from w ww . j a va2 s . co m CsvTransformGenerator gen = new CsvTransformGenerator(info, getDatabaseMeta()); // create the model String tableName = info.getStageTableName(); try { gen.execSqlStatement(getDropTableStatement(tableName), getDatabaseMeta(), null); } catch (CsvTransformGeneratorException e) { // table might not be there yet, it is OK } // generate the database table gen.createOrModifyTable(session); // load the table loadTable(gen, info, true, session); // check the results long rowCount = this.getRowCount(tableName); assertEquals((long) 235, rowCount); DatabaseMeta databaseMeta = getDatabaseMeta(); assertNotNull(databaseMeta); Database database = new Database(databaseMeta); assertNotNull(database); database.connect(); Connection connection = null; Statement stmt = null; ResultSet sqlResult = null; try { connection = database.getConnection(); assertNotNull(connection); stmt = database.getConnection().createStatement(); // check the first row Date testDate = new Date(); testDate.setDate(1); testDate.setHours(0); testDate.setMinutes(0); testDate.setMonth(0); testDate.setSeconds(0); testDate.setYear(110); boolean ok = stmt.execute("select * from " + tableName); assertTrue(ok); sqlResult = stmt.getResultSet(); assertNotNull(sqlResult); ok = sqlResult.next(); assertTrue(ok); // test the values assertEquals((long) 3, sqlResult.getLong(1)); assertEquals(25677.96525, sqlResult.getDouble(2)); assertEquals((long) 1231, sqlResult.getLong(3)); assertEquals(testDate.getYear(), sqlResult.getDate(4).getYear()); assertEquals(testDate.getMonth(), sqlResult.getDate(4).getMonth()); assertEquals(testDate.getDate(), sqlResult.getDate(4).getDate()); assertEquals(testDate.getHours(), sqlResult.getTime(4).getHours()); // assertEquals( testDate.getMinutes(), ((Date)cells[3]).getMinutes() ); this fails, a bug in the PDI date parsing? assertEquals(testDate.getSeconds(), sqlResult.getTime(4).getSeconds()); // assertEquals( testDate, cells[3] ); assertEquals("Afghanistan", sqlResult.getString(5)); assertEquals((long) 11, sqlResult.getLong(6)); assertEquals(111.9090909, sqlResult.getDouble(7)); assertEquals(false, sqlResult.getBoolean(8)); } finally { sqlResult.close(); stmt.close(); connection.close(); } }
From source file:CSVWriter.java
private String getColumnValue(ResultSet rs, int colType, int colIndex) throws SQLException, IOException { String value = ""; switch (colType) { case Types.BIT: case Types.JAVA_OBJECT: value = handleObject(rs.getObject(colIndex)); break;/*from ww w . j a va 2s . c om*/ case Types.BOOLEAN: boolean b = rs.getBoolean(colIndex); value = Boolean.valueOf(b).toString(); break; case NCLOB: // todo : use rs.getNClob case Types.CLOB: Clob c = rs.getClob(colIndex); if (c != null) { value = read(c); } break; case Types.BIGINT: value = handleLong(rs, colIndex); break; case Types.DECIMAL: case Types.DOUBLE: case Types.FLOAT: case Types.REAL: case Types.NUMERIC: value = handleBigDecimal(rs.getBigDecimal(colIndex)); break; case Types.INTEGER: case Types.TINYINT: case Types.SMALLINT: value = handleInteger(rs, colIndex); break; case Types.DATE: value = handleDate(rs, colIndex); break; case Types.TIME: value = handleTime(rs.getTime(colIndex)); break; case Types.TIMESTAMP: value = handleTimestamp(rs.getTimestamp(colIndex)); break; case NVARCHAR: // todo : use rs.getNString case NCHAR: // todo : use rs.getNString case LONGNVARCHAR: // todo : use rs.getNString case Types.LONGVARCHAR: case Types.VARCHAR: case Types.CHAR: value = rs.getString(colIndex); break; default: value = ""; } if (value == null) { value = ""; } return value; }
From source file:com.google.visualization.datasource.util.SqlDataSourceHelper.java
/** * Creates a table cell from the value in the current row of the given result * set and the given column index. The type of the value is determined by the * given value type./*from www . j a v a 2 s. c o m*/ * * @param rs The result set holding the data from the sql table. The result * points to the current row. * @param valueType The value type of the column that the cell belongs to. * @param column The column index. Indexes are 0-based. * * @return The table cell. * * @throws SQLException Thrown when the connection to the database failed. */ private static TableCell buildTableCell(ResultSet rs, ValueType valueType, int column) throws SQLException { Value value = null; // SQL indexes are 1- based. column = column + 1; switch (valueType) { case BOOLEAN: value = BooleanValue.getInstance(rs.getBoolean(column)); break; case NUMBER: value = new NumberValue(rs.getDouble(column)); break; case DATE: Date date = rs.getDate(column); // If date is null it is handled later. if (date != null) { GregorianCalendar gc = new GregorianCalendar(TimeZone.getTimeZone("GMT")); // Set the year, month and date in the gregorian calendar. // Use the 'set' method with those parameters, and not the 'setTime' // method with the date parameter, since the Date object contains the // current time zone and it's impossible to change it to 'GMT'. gc.set(date.getYear() + 1900, date.getMonth(), date.getDate()); value = new DateValue(gc); } break; case DATETIME: Timestamp timestamp = rs.getTimestamp(column); // If timestamp is null it is handled later. if (timestamp != null) { GregorianCalendar gc = new GregorianCalendar(TimeZone.getTimeZone("GMT")); // Set the year, month, date, hours, minutes and seconds in the // gregorian calendar. Use the 'set' method with those parameters, // and not the 'setTime' method with the timestamp parameter, since // the Timestamp object contains the current time zone and it's // impossible to change it to 'GMT'. gc.set(timestamp.getYear() + 1900, timestamp.getMonth(), timestamp.getDate(), timestamp.getHours(), timestamp.getMinutes(), timestamp.getSeconds()); // Set the milliseconds explicitly, as they are not saved in the // underlying date. gc.set(Calendar.MILLISECOND, timestamp.getNanos() / 1000000); value = new DateTimeValue(gc); } break; case TIMEOFDAY: Time time = rs.getTime(column); // If time is null it is handled later. if (time != null) { GregorianCalendar gc = new GregorianCalendar(TimeZone.getTimeZone("GMT")); // Set the hours, minutes and seconds of the time in the gregorian // calendar. Set the year, month and date to be January 1 1970 like // in the Time object. // Use the 'set' method with those parameters, // and not the 'setTime' method with the time parameter, since // the Time object contains the current time zone and it's // impossible to change it to 'GMT'. gc.set(1970, Calendar.JANUARY, 1, time.getHours(), time.getMinutes(), time.getSeconds()); // Set the milliseconds explicitly, otherwise the milliseconds from // the time the gc was initialized are used. gc.set(GregorianCalendar.MILLISECOND, 0); value = new TimeOfDayValue(gc); } break; default: String colValue = rs.getString(column); if (colValue == null) { value = TextValue.getNullValue(); } else { value = new TextValue(rs.getString(column)); } break; } // Handle null values. if (rs.wasNull()) { return new TableCell(Value.getNullValueFromValueType(valueType)); } else { return new TableCell(value); } }
From source file:com.cnd.greencube.server.dao.jdbc.JdbcDAO.java
@SuppressWarnings("rawtypes") private Object getColumnValue(ResultSet rs, ResultSetMetaData meta, int index, Class clazz) throws Exception { Object value = null;//from w w w .ja v a2 s.co m int type = meta.getColumnType(index); if (clazz == String.class) { value = rs.getString(index); } else if (clazz == Integer.class) { value = rs.getInt(index); } else if (clazz == Boolean.class) { value = rs.getBoolean(index); } else if (clazz == byte[].class) { if (type == Types.BLOB) value = rs.getBlob(index); else value = rs.getBytes(index); } else if (clazz == Long.class) { value = rs.getLong(index); } else if (clazz == BigInteger.class) { value = rs.getBigDecimal(index); } else if (clazz == Float.class) { value = rs.getFloat(index); } else if (clazz == Double.class) { value = rs.getDouble(index); } else if (clazz == java.util.Date.class) { Timestamp time = rs.getTimestamp(index); if (time == null) value = null; else { value = new java.util.Date(time.getTime()); } } else if (clazz == java.sql.Date.class) { value = rs.getDate(index); } else if (clazz == java.sql.Time.class) { value = rs.getTime(index); } else if (clazz == java.sql.Timestamp.class) { value = rs.getTimestamp(index); } else { throw new Exception("Cannote determin this column type:" + meta.getColumnName(index)); } return value; }