List of usage examples for java.sql PreparedStatement setTime
void setTime(int parameterIndex, java.sql.Time x) throws SQLException;
java.sql.Time value. From source file:db_classes.DBManager.java
/** * * @param restaurant that needs to be added to DB * @param creator/*from w w w . jav a 2 s . c om*/ * @param isOwner * @return */ public boolean addRestaurant(Restaurant restaurant, int creator_id, boolean isOwner) { int next_id = 0; try { //query to get the next free id for restaurant String query1 = "SELECT MAX(id) FROM Restaurants"; PreparedStatement ps1 = con.prepareStatement(query1); ResultSet rs1 = ps1.executeQuery(); while (rs1.next()) { next_id = rs1.getInt(1) + 1; } //query to add the restaurant to DB String query = "INSERT INTO Restaurants VALUES (?,?,?,?,?,?,?,?,?,?,?)"; PreparedStatement ps = con.prepareStatement(query); ps.setInt(1, next_id); ps.setString(2, restaurant.getName()); ps.setString(3, restaurant.getAddress()); ps.setInt(4, restaurant.getCivicNumber()); ps.setString(5, restaurant.getCity()); ps.setString(6, restaurant.getDescription()); ps.setString(7, restaurant.getWebSiteUrl()); ps.setInt(8, 0); ps.setInt(9, restaurant.getPrice()); if (isOwner) { ps.setInt(10, creator_id); } else { ps.setInt(10, 0); } ps.setInt(11, creator_id); int update = ps.executeUpdate(); //query to add cuisine types to a restaurant_id PreparedStatement psk = con.prepareStatement("INSERT INTO restaurant_cuisine VALUES (?,?)"); for (String s : restaurant.getCuisineTypes()) { switch (s) { case "Italiana": psk.setInt(1, next_id); psk.setInt(2, 1); psk.executeUpdate(); break; case "Asiatica": psk.setInt(1, next_id); psk.setInt(2, 2); psk.executeUpdate(); break; case "NordAmericana": psk.setInt(1, next_id); psk.setInt(2, 3); psk.executeUpdate(); break; case "Africana": psk.setInt(1, next_id); psk.setInt(2, 4); psk.executeUpdate(); break; case "Caraibica": psk.setInt(1, next_id); psk.setInt(2, 5); psk.executeUpdate(); break; case "SudAmericana": psk.setInt(1, next_id); psk.setInt(2, 6); psk.executeUpdate(); break; case "NordEuropea": psk.setInt(1, next_id); psk.setInt(2, 7); psk.executeUpdate(); break; case "Mediterranea": psk.setInt(1, next_id); psk.setInt(2, 8); psk.executeUpdate(); break; case "MedioOrientale": psk.setInt(1, next_id); psk.setInt(2, 9); psk.executeUpdate(); break; case "Vegana": psk.setInt(1, next_id); psk.setInt(2, 10); psk.executeUpdate(); break; case "FastFood": psk.setInt(1, next_id); psk.setInt(2, 11); psk.executeUpdate(); break; case "Pizzeria": psk.setInt(1, next_id); psk.setInt(2, 12); psk.executeUpdate(); break; } } //query to add hours_ranges PreparedStatement psw = con .prepareStatement("INSERT INTO opening_hours_restaurants VALUES (?,?,?,?,?,?)"); WeekSchedule rest_week = restaurant.getWeek(); if (rest_week.isMonday()) { psw.setInt(1, next_id); psw.setInt(2, 1); psw.setTime(3, rest_week.getMonday_l_op()); psw.setTime(4, rest_week.getMonday_l_cl()); psw.setTime(5, rest_week.getMonday_d_op()); psw.setTime(6, rest_week.getMonday_d_cl()); psw.executeUpdate(); } if (rest_week.isTuesday()) { psw.setInt(1, next_id); psw.setInt(2, 2); psw.setTime(3, rest_week.getTuesday_l_op()); psw.setTime(4, rest_week.getTuesday_l_cl()); psw.setTime(5, rest_week.getTuesday_d_op()); psw.setTime(6, rest_week.getTuesday_d_cl()); psw.executeUpdate(); } if (rest_week.isWednesday()) { psw.setInt(1, next_id); psw.setInt(2, 3); psw.setTime(3, rest_week.getWednesday_l_op()); psw.setTime(4, rest_week.getWednesday_l_cl()); psw.setTime(5, rest_week.getWednesday_d_op()); psw.setTime(6, rest_week.getWednesday_d_cl()); psw.executeUpdate(); } if (rest_week.isThursday()) { psw.setInt(1, next_id); psw.setInt(2, 4); psw.setTime(3, rest_week.getThursday_l_op()); psw.setTime(4, rest_week.getThursday_l_cl()); psw.setTime(5, rest_week.getThursday_d_op()); psw.setTime(6, rest_week.getThursday_d_cl()); psw.executeUpdate(); } if (rest_week.isFriday()) { psw.setInt(1, next_id); psw.setInt(2, 5); psw.setTime(3, rest_week.getFriday_l_op()); psw.setTime(4, rest_week.getFriday_l_cl()); psw.setTime(5, rest_week.getFriday_d_op()); psw.setTime(6, rest_week.getFriday_d_cl()); psw.executeUpdate(); } if (rest_week.isSaturday()) { psw.setInt(1, next_id); psw.setInt(2, 6); psw.setTime(3, rest_week.getSaturday_l_op()); psw.setTime(4, rest_week.getSaturday_l_cl()); psw.setTime(5, rest_week.getSaturday_d_op()); psw.setTime(6, rest_week.getSaturday_d_cl()); psw.executeUpdate(); } if (rest_week.isSunday()) { psw.setInt(1, next_id); psw.setInt(2, 7); psw.setTime(3, rest_week.getSunday_l_op()); psw.setTime(4, rest_week.getSunday_l_cl()); psw.setTime(5, rest_week.getSunday_d_op()); psw.setTime(6, rest_week.getSunday_d_cl()); psw.executeUpdate(); } //query to get the next photo id int next_photo_id = 0; PreparedStatement psn = con.prepareStatement("SELECT MAX(id) FROM photos"); ResultSet rsp = psn.executeQuery(); while (rsp.next()) { next_photo_id = rsp.getInt(1); } //query to insert photo PreparedStatement psp = con.prepareStatement("INSERT INTO photos VALUES (?,?,?,?)"); psp.setInt(1, next_photo_id); psp.setString(2, restaurant.getPhotoPath()); psp.setInt(3, next_id); psp.setInt(4, creator_id); if (update == 0) { return false; } ps1.close(); rs1.close(); ps.close(); psk.close(); psw.close(); double[] coordinates = null; try { coordinates = getCoordinates(restaurant.getAddress(), restaurant.getCivicNumber(), restaurant.getCity()); } catch (IOException ex) { Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("Lat: " + coordinates[0] + " Long: " + coordinates[1]); } catch (SQLException ex) { Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex); } return true; }
From source file:org.hxzon.util.db.springjdbc.StatementCreatorUtils.java
private static void setValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName, Integer scale, Object inValue) throws SQLException { if (inValue instanceof SqlTypeValue) { ((SqlTypeValue) inValue).setTypeValue(ps, paramIndex, sqlType, typeName); } else if (inValue instanceof SqlValue) { ((SqlValue) inValue).setValue(ps, paramIndex); } else if (sqlType == Types.VARCHAR || sqlType == Types.LONGVARCHAR || (sqlType == Types.CLOB && isStringValue(inValue.getClass()))) { ps.setString(paramIndex, inValue.toString()); } else if (sqlType == Types.DECIMAL || sqlType == Types.NUMERIC) { if (inValue instanceof BigDecimal) { ps.setBigDecimal(paramIndex, (BigDecimal) inValue); } else if (scale != null) { ps.setObject(paramIndex, inValue, sqlType, scale); } else {/* ww w . ja v a2 s . com*/ ps.setObject(paramIndex, inValue, sqlType); } } else if (sqlType == Types.DATE) { if (inValue instanceof java.util.Date) { if (inValue instanceof java.sql.Date) { ps.setDate(paramIndex, (java.sql.Date) inValue); } else { ps.setDate(paramIndex, new java.sql.Date(((java.util.Date) inValue).getTime())); } } else if (inValue instanceof Calendar) { Calendar cal = (Calendar) inValue; ps.setDate(paramIndex, new java.sql.Date(cal.getTime().getTime()), cal); } else { ps.setObject(paramIndex, inValue, Types.DATE); } } else if (sqlType == Types.TIME) { if (inValue instanceof java.util.Date) { if (inValue instanceof java.sql.Time) { ps.setTime(paramIndex, (java.sql.Time) inValue); } else { ps.setTime(paramIndex, new java.sql.Time(((java.util.Date) inValue).getTime())); } } else if (inValue instanceof Calendar) { Calendar cal = (Calendar) inValue; ps.setTime(paramIndex, new java.sql.Time(cal.getTime().getTime()), cal); } else { ps.setObject(paramIndex, inValue, Types.TIME); } } else if (sqlType == Types.TIMESTAMP) { if (inValue instanceof java.util.Date) { if (inValue instanceof java.sql.Timestamp) { ps.setTimestamp(paramIndex, (java.sql.Timestamp) inValue); } else { ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime())); } } else if (inValue instanceof Calendar) { Calendar cal = (Calendar) inValue; ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal); } else { ps.setObject(paramIndex, inValue, Types.TIMESTAMP); } } else if (sqlType == SqlTypeValue.TYPE_UNKNOWN) { if (isStringValue(inValue.getClass())) { ps.setString(paramIndex, inValue.toString()); } else if (isDateValue(inValue.getClass())) { ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime())); } else if (inValue instanceof Calendar) { Calendar cal = (Calendar) inValue; ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal); } else { // Fall back to generic setObject call without SQL type specified. ps.setObject(paramIndex, inValue); } } else { // Fall back to generic setObject call with SQL type specified. ps.setObject(paramIndex, inValue, sqlType); } }
From source file:cz.lbenda.dataman.db.RowDesc.java
@SuppressWarnings("ConstantConditions") private <T> void putToPS(ColumnDesc columnDesc, T value, PreparedStatement ps, int position) throws SQLException { if (value == null) { ps.setObject(position, null);//w w w.java 2 s . c om return; } BinaryData bd = value instanceof BinaryData ? (BinaryData) value : null; switch (columnDesc.getDataType()) { case STRING: ps.setString(position, (String) value); break; case BOOLEAN: ps.setBoolean(position, (Boolean) value); break; case TIMESTAMP: ps.setTimestamp(position, (Timestamp) value); break; case DATE: ps.setDate(position, (Date) value); break; case TIME: ps.setTime(position, (Time) value); break; case BYTE: ps.setByte(position, (Byte) value); break; case SHORT: ps.setShort(position, (Short) value); break; case INTEGER: ps.setInt(position, (Integer) value); break; case LONG: ps.setLong(position, (Long) value); break; case FLOAT: ps.setFloat(position, (Float) value); break; case DOUBLE: ps.setDouble(position, (Double) value); break; case DECIMAL: ps.setBigDecimal(position, (BigDecimal) value); break; case UUID: ps.setBytes(position, AbstractHelper.uuidToByteArray((UUID) value)); break; case ARRAY: throw new UnsupportedOperationException("The saving changes in ARRAY isn't supported."); // ps.setArray(position, (Array) value); break; // FIXME the value isn't in type java.sql.Array case BYTE_ARRAY: if (bd == null || bd.isNull()) { ps.setBytes(position, null); } else { try { ps.setBytes(position, IOUtils.toByteArray(bd.getInputStream())); } catch (IOException e) { throw new SQLException(e); } } break; case CLOB: if (bd == null || bd.isNull()) { ps.setNull(position, Types.CLOB); } else { ps.setClob(position, bd.getReader()); } break; case BLOB: if (bd == null || bd.isNull()) { ps.setNull(position, Types.BLOB); } else { ps.setBlob(position, bd.getInputStream()); } break; case OBJECT: ps.setObject(position, value); } }
From source file:chh.utils.db.source.common.JdbcClient.java
private void setPreparedStatementParams(PreparedStatement preparedStatement, List<Column> columnList) throws SQLException { int index = 1; for (Column column : columnList) { Class columnJavaType = Util.getJavaType(column.getSqlType()); if (column.getVal() == null) { preparedStatement.setNull(index, column.getSqlType()); } else if (columnJavaType.equals(String.class)) { preparedStatement.setString(index, (String) column.getVal()); } else if (columnJavaType.equals(Integer.class)) { preparedStatement.setInt(index, (Integer) column.getVal()); } else if (columnJavaType.equals(Double.class)) { preparedStatement.setDouble(index, (Double) column.getVal()); } else if (columnJavaType.equals(Float.class)) { preparedStatement.setFloat(index, (Float) column.getVal()); } else if (columnJavaType.equals(Short.class)) { preparedStatement.setShort(index, (Short) column.getVal()); } else if (columnJavaType.equals(Boolean.class)) { preparedStatement.setBoolean(index, (Boolean) column.getVal()); } else if (columnJavaType.equals(byte[].class)) { preparedStatement.setBytes(index, (byte[]) column.getVal()); } else if (columnJavaType.equals(Long.class)) { preparedStatement.setLong(index, (Long) column.getVal()); } else if (columnJavaType.equals(Date.class)) { preparedStatement.setDate(index, (Date) column.getVal()); } else if (columnJavaType.equals(Time.class)) { preparedStatement.setTime(index, (Time) column.getVal()); } else if (columnJavaType.equals(Timestamp.class)) { preparedStatement.setTimestamp(index, (Timestamp) column.getVal()); } else {/*from ww w . ja v a 2 s. c o m*/ throw new RuntimeException( "Unknown type of value " + column.getVal() + " for column " + column.getColumnName()); } ++index; } }
From source file:com.kylinolap.rest.service.QueryService.java
/** * @param preparedState/*from w ww . jav a2s. co m*/ * @param param * @throws SQLException */ private void setParam(PreparedStatement preparedState, int index, StateParam param) throws SQLException { boolean isNull = (null == param.getValue()); Class<?> clazz = Object.class; try { clazz = Class.forName(param.getClassName()); } catch (ClassNotFoundException e) { e.printStackTrace(); } Rep rep = Rep.of(clazz); switch (rep) { case PRIMITIVE_CHAR: case CHARACTER: case STRING: preparedState.setString(index, isNull ? null : String.valueOf(param.getValue())); break; case PRIMITIVE_INT: case INTEGER: preparedState.setInt(index, isNull ? null : Integer.valueOf(param.getValue())); break; case PRIMITIVE_SHORT: case SHORT: preparedState.setShort(index, isNull ? null : Short.valueOf(param.getValue())); break; case PRIMITIVE_LONG: case LONG: preparedState.setLong(index, isNull ? null : Long.valueOf(param.getValue())); break; case PRIMITIVE_FLOAT: case FLOAT: preparedState.setFloat(index, isNull ? null : Float.valueOf(param.getValue())); break; case PRIMITIVE_DOUBLE: case DOUBLE: preparedState.setDouble(index, isNull ? null : Double.valueOf(param.getValue())); break; case PRIMITIVE_BOOLEAN: case BOOLEAN: preparedState.setBoolean(index, isNull ? null : Boolean.parseBoolean(param.getValue())); break; case PRIMITIVE_BYTE: case BYTE: preparedState.setByte(index, isNull ? null : Byte.valueOf(param.getValue())); break; case JAVA_UTIL_DATE: case JAVA_SQL_DATE: preparedState.setDate(index, isNull ? null : java.sql.Date.valueOf(param.getValue())); break; case JAVA_SQL_TIME: preparedState.setTime(index, isNull ? null : Time.valueOf(param.getValue())); break; case JAVA_SQL_TIMESTAMP: preparedState.setTimestamp(index, isNull ? null : Timestamp.valueOf(param.getValue())); break; default: preparedState.setObject(index, isNull ? null : param.getValue()); } }
From source file:net.sf.farrago.namespace.sfdc.SfdcUdx.java
public static void query(String query, String types, PreparedStatement resultInserter) throws SQLException { SoapBindingStub binding = (SoapBindingStub) FarragoUdrRuntime.getDataServerRuntimeSupport(null); try {//w w w . j ava 2 s . c om QueryOptions qo = new QueryOptions(); int batchsize = 500; qo.setBatchSize(new Integer(batchsize)); binding.setHeader(new SforceServiceLocator().getServiceName().getNamespaceURI(), "QueryOptions", qo); String objName = query; int fromIdx = query.lastIndexOf(" from"); if (fromIdx > 0) { objName = query.substring(fromIdx + 6); } // strip off quotes for boolean values query = stripQuotes(query, objName); log.info("SFDC Query: " + query); QueryResult qr = binding.query(query); if (qr.isDone()) { if (qr.getRecords() != null) { log.info(SfdcResource.instance().RetrievedAllRecordsMsg .str(Integer.toString(qr.getRecords().length), objName)); } } else { if (qr.getRecords() != null) { log.info(SfdcResource.instance().RetrievingRecordsMsg .str(Integer.toString(qr.getRecords().length), objName)); } } SObject[] records = qr.getRecords(); String[] metadataType = types.split(","); // query is of following format: // "select col1,col2,... from" String cols = query.substring(7); fromIdx = cols.lastIndexOf(" from"); cols = cols.substring(0, fromIdx); cols = cols.trim(); String[] columnValues = new String[metadataType.length]; if (records != null) { boolean bContinue = true; while (bContinue) { // for each record returned in query, // get value of each field for (int i = 0; i < records.length; i++) { MessageElement[] elements = records[i].get_any(); if (elements != null) { for (int j = 0; j < elements.length; j++) { MessageElement elt = elements[j]; String eltVal = elt.getValue(); columnValues[j] = (eltVal != null) ? eltVal : "null"; if (metadataType[j].indexOf("TIMESTAMP") != -1) { // TIMESTAMP if (eltVal != null) { String tstampstr = eltVal.replace("T", " "); tstampstr = tstampstr.substring(0, tstampstr.indexOf(".")); java.sql.Timestamp tstamp = java.sql.Timestamp.valueOf(tstampstr); resultInserter.setTimestamp(j + 1, tstamp); } else { resultInserter.setNull(j + 1, java.sql.Types.TIMESTAMP); } } else if (metadataType[j].indexOf("TIME") != -1) { // TIME if (eltVal != null) { String timestr = eltVal.substring(0, eltVal.indexOf(".")); java.sql.Time time = java.sql.Time.valueOf(timestr); resultInserter.setTime(j + 1, time); } else { resultInserter.setNull(j + 1, java.sql.Types.TIME); } } else if (metadataType[j].indexOf("DATE") != -1) { // DATE if (eltVal != null) { java.sql.Date dt = java.sql.Date.valueOf(eltVal); resultInserter.setDate(j + 1, dt); } else { resultInserter.setNull(j + 1, java.sql.Types.DATE); } } else if (metadataType[j].indexOf("INTEGER") != -1) { // INTEGER if (eltVal != null) { int iValue = 0; iValue = Integer.parseInt(eltVal); resultInserter.setInt(j + 1, iValue); } else { resultInserter.setNull(j + 1, java.sql.Types.INTEGER); } } else if (metadataType[j].indexOf("DOUBLE") != -1) { // DOUBLE if (eltVal != null) { resultInserter.setDouble(j + 1, Double.parseDouble(eltVal)); } else { resultInserter.setNull(j + 1, java.sql.Types.DOUBLE); } } else if (eltVal != null) { // VARCHAR - default int rightParen = metadataType[j].indexOf(")"); int prec = Integer.parseInt(metadataType[j].substring(8, rightParen)); if (eltVal.length() > prec) { eltVal = eltVal.substring(0, prec); columnValues[j] = eltVal; } resultInserter.setString(j + 1, eltVal); } else { resultInserter.setNull(j + 1, java.sql.Types.VARCHAR); } } resultInserter.executeUpdate(); } } if (qr.isDone()) { bContinue = false; } else { boolean relogin = true; int retryCnt = 0; while (relogin) { try { qr = binding.queryMore(qr.getQueryLocator()); relogin = false; } catch (AxisFault a) { if (a.getFaultString().contains("Invalid Session ID") && (retryCnt < RETRY_CNT)) { relogin = true; retryCnt++; binding = (SoapBindingStub) FarragoUdrRuntime .getDataServerRuntimeSupport(binding); } else { throw a; } } } records = qr.getRecords(); if (qr.isDone()) { if (qr.getRecords() != null) { log.info(SfdcResource.instance().RetrievedAllRecordsMsg .str(Integer.toString(qr.getRecords().length), objName)); } } else { if (qr.getRecords() != null) { log.info(SfdcResource.instance().RetrievingRecordsMsg .str(Integer.toString(qr.getRecords().length), objName)); } } } } } } catch (AxisFault ae) { SQLException retryExcn = new SQLException(ae.getFaultString(), null, 460150); Exception chainedEx = FarragoResource.instance().RetryableFailure.ex(retryExcn); throw SfdcResource.instance().BindingCallException.ex(ae.getFaultString(), chainedEx); } catch (RemoteException re) { SQLException retryExcn = new SQLException(re.getMessage(), null, 460150); Exception chainedEx = FarragoResource.instance().RetryableFailure.ex(retryExcn); throw SfdcResource.instance().BindingCallException.ex(re.getMessage(), chainedEx); } }
From source file:com.flexoodb.engines.FlexJAXBMappedDBDataEngine.java
private void updatePreparedStatement(String tablename, Hashtable<String, Object[]> fieldswithcontent, PreparedStatement ps) throws Exception { Enumeration en = fieldswithcontent.keys(); int i = 0;/*from ww w .j a v a2 s. c o m*/ while (en.hasMoreElements()) { i++; try { String field = (String) en.nextElement(); Object[] o2 = fieldswithcontent.get(field); String type = (String) o2[0]; Object o = o2[1]; //System.out.println(field+" "+type+" "+o); if (type.equals("string")) { ps.setString(i, (String) o); } else if (type.equals("byte[]") || type.equals("base64Binary")) { ps.setBinaryStream(i, new ByteArrayInputStream((byte[]) o)); } else if (type.equals("dateTime")) { XMLGregorianCalendar cal = (XMLGregorianCalendar) o; Date d = null; if (cal.toString().indexOf("0003-11-30") > -1) { ps.setString(i, "0000-00-00 00:00:00"); } else { d = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(cal.toString()); ps.setTimestamp(i, new java.sql.Timestamp(d.getTime())); } } else if (type.equals("date")) { XMLGregorianCalendar cal = (XMLGregorianCalendar) o; Date d1 = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(cal.toString()); ps.setDate(i, java.sql.Date.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(d1))); } else if (type.equals("time")) { XMLGregorianCalendar cal = (XMLGregorianCalendar) o; String c = cal.toString(); c = c.replaceFirst("0003-11-30", "0000-00-00"); Date d1 = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(c); ps.setTime(i, new java.sql.Time(d1.getTime())); } else if (type.equals("integer")) { ps.setInt(i, ((BigInteger) o).intValue()); } else if (type.equals("double")) { ps.setDouble(i, (Double) o); } else if (type.equals("float")) { ps.setFloat(i, (Float) o); } else if (type.equals("long")) { ps.setLong(i, (Long) o); } else { throw new Exception("unknown type [" + type + "] for field [" + field + "] encountered while trying to update table [" + tablename + "]."); } } catch (Exception e) { e.printStackTrace(); throw e; } } }
From source file:gemlite.core.internal.db.AsyncEventHelper.java
/** * Set column value at given index in a prepared statement. The implementation * tries using the matching underlying type to minimize any data type * conversions, and avoid creating wrapper Java objects (e.g. {@link Integer} * for primitive int)./* w w w . j a v a2 s . c o m*/ * * @param type * the SQL type of the column as specified by JDBC {@link Types} * class * @param ps * the prepared statement where the column value has to be set * @param row * the source row as a {@link ResultSet} from where the value has to * be extracted * @param rowPosition * the 1-based position of the column in the provided * <code>row</code> * @param paramIndex * the 1-based position of the column in the target prepared * statement (provided <code>ps</code> argument) * @param sync * the {@link DBSynchronizer} object, if any; it is used to store * whether the current driver is JDBC4 compliant to enable performing * BLOB/CLOB operations {@link PreparedStatement#setBinaryStream}, * {@link PreparedStatement#setCharacterStream} * * @throws SQLException * in case of an exception in setting parameters */ public final void setColumnInPrepStatement(String type, Object val, PreparedStatement ps, final DBSynchronizer sync, int paramIndex) throws SQLException { switch (type) { case JavaTypes.STRING: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.VARCHAR); else { final String realVal = (String) val; ps.setString(paramIndex, realVal); } break; case JavaTypes.INT1: case JavaTypes.INT2: case JavaTypes.INT3: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.INTEGER); else { final int realVal = (int) val; ps.setInt(paramIndex, realVal); } break; case JavaTypes.DOUBLE1: case JavaTypes.DOUBLE2: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.DOUBLE); else { final double realVal = (double) val; ps.setDouble(paramIndex, realVal); } break; case JavaTypes.FLOAT1: case JavaTypes.FLOAT2: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.FLOAT); else { final float realVal = (float) val; ps.setDouble(paramIndex, realVal); } break; case JavaTypes.BOOLEAN1: case JavaTypes.BOOLEAN2: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.BOOLEAN); else { final boolean realVal = (boolean) val; ps.setBoolean(paramIndex, realVal); } break; case JavaTypes.DATE_SQL: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.DATE); else { final Date realVal = (Date) val; ps.setDate(paramIndex, realVal); } break; case JavaTypes.DATE_UTIL: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.DATE); else { final java.util.Date realVal = (java.util.Date) val; ps.setDate(paramIndex, new Date(realVal.getTime())); } break; case JavaTypes.BIGDECIMAL: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.DECIMAL); else { final BigDecimal realVal = (BigDecimal) val; ps.setBigDecimal(paramIndex, realVal); } break; case JavaTypes.TIME: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.TIME); else { final Time realVal = (Time) val; ps.setTime(paramIndex, realVal); } break; case JavaTypes.TIMESTAMP: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.TIMESTAMP); else { final Timestamp realVal = (Timestamp) val; ps.setTimestamp(paramIndex, realVal); } break; case JavaTypes.OBJECT: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.JAVA_OBJECT); else { final Object realVal = (Object) val; ps.setObject(paramIndex, realVal); } break; default: throw new UnsupportedOperationException("java.sql.Type = " + type + " not supported"); } }
From source file:fll.scheduler.TournamentSchedule.java
/** * Store a tournament schedule in the database. This will delete any previous * schedule for the same tournament.//from www. j a v a 2 s. c om * * @param tournamentID the ID of the tournament */ public void storeSchedule(final Connection connection, final int tournamentID) throws SQLException { PreparedStatement deletePerfRounds = null; PreparedStatement deleteSchedule = null; PreparedStatement deleteSubjective = null; PreparedStatement insertSchedule = null; PreparedStatement insertPerfRounds = null; PreparedStatement insertSubjective = null; try { // delete previous tournament schedule deletePerfRounds = connection.prepareStatement("DELETE FROM sched_perf_rounds WHERE tournament = ?"); deletePerfRounds.setInt(1, tournamentID); deletePerfRounds.executeUpdate(); deleteSubjective = connection.prepareStatement("DELETE FROM sched_subjective WHERE tournament = ?"); deleteSubjective.setInt(1, tournamentID); deleteSubjective.executeUpdate(); deleteSchedule = connection.prepareStatement("DELETE FROM schedule WHERE tournament = ?"); deleteSchedule.setInt(1, tournamentID); deleteSchedule.executeUpdate(); // insert new tournament schedule insertSchedule = connection.prepareStatement("INSERT INTO schedule"// + " (tournament, team_number, judging_station)"// + " VALUES(?, ?, ?)"); insertSchedule.setInt(1, tournamentID); insertPerfRounds = connection.prepareStatement("INSERT INTO sched_perf_rounds"// + " (tournament, team_number, round, perf_time, table_color, table_side)"// + " VALUES(?, ?, ?, ?, ?, ?)"); insertPerfRounds.setInt(1, tournamentID); insertSubjective = connection.prepareStatement("INSERT INTO sched_subjective" // + " (tournament, team_number, name, subj_time)" // + " VALUES(?, ?, ?, ?)"); insertSubjective.setInt(1, tournamentID); for (final TeamScheduleInfo si : getSchedule()) { insertSchedule.setInt(2, si.getTeamNumber()); insertSchedule.setString(3, si.getJudgingGroup()); insertSchedule.executeUpdate(); insertPerfRounds.setInt(2, si.getTeamNumber()); for (int round = 0; round < si.getNumberOfRounds(); ++round) { insertPerfRounds.setInt(3, round); insertPerfRounds.setTime(4, Time.valueOf(si.getPerfTime(round))); insertPerfRounds.setString(5, si.getPerfTableColor(round)); insertPerfRounds.setInt(6, si.getPerfTableSide(round)); insertPerfRounds.executeUpdate(); } for (final SubjectiveTime subjectiveTime : si.getSubjectiveTimes()) { insertSubjective.setInt(2, si.getTeamNumber()); insertSubjective.setString(3, subjectiveTime.getName()); insertSubjective.setTime(4, Time.valueOf(subjectiveTime.getTime())); insertSubjective.executeUpdate(); } } } finally { SQLFunctions.close(deletePerfRounds); deletePerfRounds = null; SQLFunctions.close(deleteSchedule); deleteSchedule = null; SQLFunctions.close(deleteSubjective); deleteSubjective = null; SQLFunctions.close(insertSchedule); insertSchedule = null; SQLFunctions.close(insertPerfRounds); insertPerfRounds = null; SQLFunctions.close(insertSubjective); insertSubjective = null; } }
From source file:edu.ku.brc.specify.toycode.mexconabio.MexConvToSQLNew.java
public void convert(final String tableName, final String fileName) { String str = ""; int fldLen = 0; int inx = 0;//from w w w. j ava2s. c o m Connection conn = null; Statement stmt = null; try { conn = DriverManager.getConnection( "jdbc:mysql://localhost/mex?characterEncoding=UTF-8&autoReconnect=true", "root", "root"); stmt = conn.createStatement(); int[] fieldLengths = null; BasicSQLUtils.deleteAllRecordsFromTable(conn, tableName, SERVERTYPE.MySQL); Vector<Integer> types = new Vector<Integer>(); Vector<String> names = new Vector<String>(); String selectStr = null; String prepareStr = null; try { prepareStr = FileUtils.readFileToString(new File("prepare_stmt.txt")); selectStr = FileUtils.readFileToString(new File("select.txt")); } catch (IOException e) { e.printStackTrace(); } int idInx = selectStr.indexOf("ID,"); if (idInx == 0) { selectStr = selectStr.substring(3); } File file = new File("/Users/rods/Documents/" + fileName); SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy"); //SimpleDateFormat stf = new SimpleDateFormat("k:mm:ss"); int rowCnt = 0; try { System.out.println(prepareStr); PreparedStatement pStmt = conn.prepareStatement(prepareStr); BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF8")); str = in.readLine(); String[] fieldNames = StringUtils.split(str, ","); //String[] fieldNamesDB = StringUtils.split(selectStr, ","); String sql = "SELECT " + selectStr + " FROM " + tableName; System.out.println(sql); ResultSet rs = stmt.executeQuery(sql); ResultSetMetaData rsmd = rs.getMetaData(); fieldLengths = new int[rsmd.getColumnCount()]; for (int i = 1; i <= rsmd.getColumnCount(); i++) { fieldLengths[i - 1] = rsmd.getPrecision(i); types.add(rsmd.getColumnType(i)); names.add(rsmd.getColumnName(i)); System.out.println((i > 1 ? fieldNames[i - 2] : "ID") + " / " + rsmd.getColumnName(i) + " - " + rsmd.getPrecision(i)); } int numCols = rsmd.getColumnCount(); rs.close(); System.out.println("Number of Fields: " + numCols); str = in.readLine(); while (str != null) { //System.err.println(str); str = StringUtils.replace(str.substring(1, str.length() - 1), "\",\"", "|"); Vector<String> fields = split(str); if (fields.size() != numCols) { System.out.println("numCols: " + numCols + " != " + fields.size() + "fields.size()"); continue; } int col = 1; inx = 0; for (String fld : fields) { String field = fld.trim(); //if (field.length() > 1) //{ // field = field.substring(1, field.length()-1); //} //if (inx > 204) break; fldLen = field.length(); pStmt.setObject(col, null); switch (types.get(inx)) { case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARCHAR: case java.sql.Types.LONGNVARCHAR: { if (field.length() > 0) { if (field.length() <= fieldLengths[inx]) { pStmt.setString(col, field); } else { System.err.println(String.format("The data for `%s` (%d) is too big %d f[%s]", names.get(inx), fieldLengths[inx], field.length(), field)); pStmt.setString(col, null); } } else { pStmt.setString(col, null); } } break; case java.sql.Types.DOUBLE: case java.sql.Types.FLOAT: { if (StringUtils.isNotEmpty(field)) { if (StringUtils.isNumeric(field)) { pStmt.setDouble(col, field.length() > 0 ? Double.parseDouble(field) : null); } else { System.err.println(col + " Bad Number[" + field + "] "); pStmt.setDate(col, null); } } else { pStmt.setDate(col, null); } } break; case java.sql.Types.INTEGER: { if (StringUtils.isNotEmpty(field)) { if (StringUtils.isNumeric(field)) { pStmt.setInt(col, field.length() > 0 ? Integer.parseInt(field) : null); } else { System.err.println(col + " Bad Number[" + field + "] "); pStmt.setDate(col, null); } } else { pStmt.setDate(col, null); } } break; case java.sql.Types.TIME: { Time time = null; try { time = Time.valueOf(field); } catch (Exception ex) { } pStmt.setTime(col, time); } break; case java.sql.Types.DATE: { try { if (StringUtils.isNotEmpty(field)) { if (StringUtils.contains(field, "/")) { field = StringUtils.replace(field, "/", "-"); } else if (StringUtils.contains(field, " ")) { field = StringUtils.replace(field, " ", "-"); } pStmt.setDate(col, field.length() > 0 ? new java.sql.Date(sdf.parse(field).getTime()) : null); } else { pStmt.setDate(col, null); } } catch (Exception ex) { System.err.println(col + " Bad Date[" + field + "]\n" + str); pStmt.setDate(col, null); } } break; default: { System.err.println("Error - " + types.get(inx)); } } inx++; col++; } pStmt.execute(); str = in.readLine(); rowCnt++; } in.close(); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (Exception e) { System.err.println("Row: " + rowCnt); System.err.println(str); System.err.println(inx + " " + fieldLengths[inx] + " - Field Len: " + fldLen); e.printStackTrace(); } /*BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file)); while (bis.available() > 0) { int bytesRead = bis.read(bytes); if (bytesRead > 0) { System.arraycopy(bytes, bytesRead, buffer, bufEndInx, bytesRead); bufEndInx += bytesRead; int inx = 0; while (inx < bufEndInx) { if (buffer[inx] != '\n') { String line = } inx++; } } }*/ } catch (Exception ex) { ex.printStackTrace(); } finally { try { stmt.close(); conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } }