List of usage examples for java.sql CallableStatement setDate
void setDate(String parameterName, java.sql.Date x) throws SQLException;
java.sql.Date value using the default time zone of the virtual machine that is running the application. From source file:Main.java
public static void main(String[] argv) throws Exception { Class.forName(DB_DRIVER);//from www . j a va2s. co m Connection dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD); CallableStatement callableStatement = null; String insertStoreProc = "{call insertPERSON(?,?,?,?)}"; java.util.Date today = new java.util.Date(); callableStatement = dbConnection.prepareCall(insertStoreProc); callableStatement.setInt(1, 1000); callableStatement.setString(2, "name"); callableStatement.setString(3, "system"); callableStatement.setDate(4, new java.sql.Date(today.getTime())); callableStatement.executeUpdate(); System.out.println("Record is inserted into PERSON table!"); callableStatement.close(); dbConnection.close(); }
From source file:jongo.jdbc.JDBCExecutor.java
/** * Utility method which registers in a CallableStatement object the different {@link jongo.jdbc.StoredProcedureParam} * instances in the given list. Returns a List of {@link jongo.jdbc.StoredProcedureParam} with all the OUT parameters * registered in the CallableStatement/* w w w .j ava 2 s. c o m*/ * @param cs the CallableStatement object where the parameters are registered. * @param params a list of {@link jongo.jdbc.StoredProcedureParam} * @return a list of OUT {@link jongo.jdbc.StoredProcedureParam} * @throws SQLException if we fail to register any of the parameters in the CallableStatement */ private static List<StoredProcedureParam> addParameters(final CallableStatement cs, final List<StoredProcedureParam> params) throws SQLException { List<StoredProcedureParam> outParams = new ArrayList<StoredProcedureParam>(); int i = 1; for (StoredProcedureParam p : params) { final Integer sqlType = p.getType(); if (p.isOutParameter()) { l.debug("Adding OUT parameter " + p.toString()); cs.registerOutParameter(i++, sqlType); outParams.add(p); } else { l.debug("Adding IN parameter " + p.toString()); switch (sqlType) { case Types.BIGINT: case Types.INTEGER: case Types.TINYINT: // case Types.NUMERIC: cs.setInt(i++, Integer.valueOf(p.getValue())); break; case Types.DATE: cs.setDate(i++, (Date) JongoUtils.parseValue(p.getValue())); break; case Types.TIME: cs.setTime(i++, (Time) JongoUtils.parseValue(p.getValue())); break; case Types.TIMESTAMP: cs.setTimestamp(i++, (Timestamp) JongoUtils.parseValue(p.getValue())); break; case Types.DECIMAL: cs.setBigDecimal(i++, (BigDecimal) JongoUtils.parseValue(p.getValue())); break; case Types.DOUBLE: cs.setDouble(i++, Double.valueOf(p.getValue())); break; case Types.FLOAT: cs.setLong(i++, Long.valueOf(p.getValue())); break; default: cs.setString(i++, p.getValue()); break; } } } return outParams; }
From source file:exifIndexer.MetadataReader.java
public static void walk(String path, boolean is_recursive) { File root = new File(path); File[] list = root.listFiles(); String filePath;// www .j a v a2 s. c o m String fileName; String fileExt; String valueName; String tagName; String catName; Metadata metadata; long fileSize; long fileLastModified; java.util.Date utilDate; java.sql.Date sqlDate; String sql = "{ ? = call INSERTIMAGE(?,?,?,?,?) }"; String sqlMetaData = "{ call INSERTMETADATA (?,?,?,?) }"; CallableStatement statement; CallableStatement statementMeta; long result; if (list == null) { return; } for (File f : list) { if (f.isDirectory() && is_recursive) { walk(f.getAbsolutePath(), true); } else { filePath = FilenameUtils.getFullPath(f.getAbsolutePath()); fileName = FilenameUtils.getBaseName(f.getName()); fileExt = FilenameUtils.getExtension(f.getName()); utilDate = new java.util.Date(f.lastModified()); sqlDate = new java.sql.Date(utilDate.getTime()); fileSize = f.length(); try { metadata = ImageMetadataReader.readMetadata(f.getAbsoluteFile()); try { DBHandler db = new DBHandler(); db.openConnection(); Connection con = db.getCon(); // llamada al metodo insertar imagen SQL con (filePath,fileName,fileExtension,fileSize, fileLastModified) statement = con.prepareCall(sql); statement.setString(2, filePath); statement.setString(3, fileName); statement.setString(4, fileExt); statement.setLong(5, fileSize); statement.setDate(6, sqlDate); statement.registerOutParameter(1, java.sql.Types.NUMERIC); statement.execute(); result = statement.getLong(1); // llamada al metodo insertar metadatos SQL con (idImg,valueName, tagName, catName) for (Directory directory : metadata.getDirectories()) { for (Tag tag : directory.getTags()) { valueName = tag.getDescription(); tagName = tag.getTagName(); catName = directory.getName(); if (isNull(valueName) || isNull(tagName) || isNull(catName) || valueName.equals("") || tagName.equals("") || catName.equals("") || valueName.length() > 250 || tagName.length() > 250 || catName.length() > 500) { System.out.println("Exif row omitted."); System.out.println("Omitting: [" + catName + "] " + tagName + " " + valueName); } else { statementMeta = con.prepareCall(sqlMetaData); statementMeta.setLong(1, result); statementMeta.setString(2, valueName); statementMeta.setString(3, tagName); statementMeta.setString(4, catName); statementMeta.executeUpdate(); } } } db.closeConnection(); } catch (SQLException ex) { System.err.println("Error with SQL command. \n" + ex); } } catch (ImageProcessingException e) { System.out.println("ImageProcessingException " + e); } catch (IOException e) { System.out.println("IOException " + e); } } } }
From source file:com.intuit.it.billing.data.BillingDAOImpl.java
/** * getAllocationsList// w ww . jav a 2s .c o m * * To be used in a caching method where we are pulling all of the allocations at once. The way we can do this * is to merge a date range based set of billing history records with a date range set of allocations. * <p/> * <p/> * <b>DATABASE PROCEDURE:</b> * * @code * FUNCTION fn_get_allocations( * customer IN VARCHAR2, * start_date IN DATE, * end_date IN DATE ) * RETURN ref_cursor; * @endcode * <p/> * <b>DATABASE RESULT SET:</b> * <ul> * <li>ALLOCATION_DATE,</li> * <li>ALLOCATION_T, </li> * <li>ALLOCATION_AMT,</li> * <li>AR_ITEM_NO, </li> * <li>BILL_ITEM_NO, </li> * <li>ITEM_DESCRIPTION, </li> * <li>ITEM_CODE,</li> * <li>AR_ITEM_DATE, </li> * <li>BILL_ITEM_DATE </li> * <li>LICENSE</li> * </ul> * * @param customer : The Customer.accountNo account number of the customer who's allocations we need * @param startDate : The starting date of the allocation - to be merged with a billing history record set * @param endDate : The ending date of the allocation - to be merged with a billing history record set * * @return A list of Allocation objects. * */ @Override public List<Allocation> getAllocationsList(String customer, Date startDate, Date endDate) throws JSONException { List<Allocation> allocs = new ArrayList<Allocation>(); java.sql.Date sqlStartDate = new java.sql.Date(startDate.getTime()); java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime()); String query = "begin ? := billing_inquiry.fn_get_allocations( ?, ?, ? ); end;"; Connection conn = null; ResultSet rs = null; // DB Connection try { conn = this.getConnection(); } catch (SQLException e) { throw JSONException.sqlError(e); } catch (NamingException e) { throw JSONException.namingError(e.toString()); } try { CallableStatement stmt = conn.prepareCall(query); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.setString(2, customer); stmt.setDate(3, sqlStartDate); stmt.setDate(4, sqlEndDate); stmt.execute(); rs = (ResultSet) stmt.getObject(1); while (rs.next()) { Allocation a = new Allocation(); a.setAllocatedFromItem(rs.getString("AR_ITEM_NO")); a.setAllocatedToItem(rs.getString("BILL_ITEM_NO")); a.setAllocationAmount(rs.getBigDecimal("ALLOCATION_AMT")); a.setAllocationDate(rs.getTimestamp("ALLOCATION_DATE")); a.setItemCode(rs.getString("ITEM_CODE")); a.setItemDescription(rs.getString("ITEM_DESCRIPTION")); a.setLicense(rs.getString("LICENSE")); allocs.add(a); } conn.close(); rs.close(); } catch (SQLException e) { throw JSONException.sqlError(e); } if (allocs == null || allocs.isEmpty()) { throw JSONException.noDataFound("Null set returned - no data found"); } return allocs; }
From source file:com.intuit.it.billing.data.BillingDAOImpl.java
/** * getBillingHistory is to be used to get get the billing history of a given customer. * <p/>// w ww. j a v a 2 s. c o m * <p/> * <b>DATABASE PROCEDURE:</b> * * @code * FUNCTION fn_get_history( * customer IN VARCHAR2, * start_date IN DATE, * end_date IN DATE, * page IN INTEGER, * records IN INTEGER ) * RETURN ref_cursor; * @endcode * <p/> * <b>DATABASE RESULT SET:</b> * <ul> * <li>ITEM_ID,</li> * <li>BILL_ITEM_NO,</li> * <li>AR_ACCOUNT_NO,</li> * <li>ACCOUNT_NO,</li> * <li>ORDER_NO,</li> * <li>ORDER_LINE_NO,</li> * <li>EVENT_TYPE,</li> * <li>CHARGE_TYPE,</li> * <li> CURRENCY,</li> * <li>CREATED_DATE,</li> * <li>BILL_DATE,</li> * <li>DUE_DATE,</li> * <li>STATUS,</li> * <li>REASON_CODE,</li> * <li>ITEM_TOTAL,</li> * <li>ITEM_DUE,</li> * <li>ITEM_DISPUTED,</li> * <li>ITEM_BASE,</li> * <li>ITEM_TAX,</li> * <li>ITEM_DESCRIPTION,</li> * <li>ITEM_CODE,</li> * <li>LICENSE,</li> * <li>PAY_TYPE,</li> * <li>PAY_DESCR,</li> * <li>PAY_ACCT_TYPE, * <li>PAY_PSON,</li> * <li>QUANTITY </li> * </ul> * * @param customer : The Customer.accountNo of the customer we want history for * @param startDate : The starting date of the allocation - to be merged with a billing history record set * @param endDate : The ending date of the allocation - to be merged with a billing history record set * @param skip : Starting record for server side paging * @param pageSize : How many records to retrieve * * @return A list of LineItem objects in reverse date order sort */ @Override public List<LineItem> getBillingHistory(String cust, Date startDate, Date endDate, Integer startPage, Integer pageSize) throws JSONException { List<LineItem> history = new ArrayList<LineItem>(); java.sql.Date sqlStartDate = new java.sql.Date(startDate.getTime()); java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime()); String query = "begin ? := billing_inquiry.fn_get_history( ?, ?, ?, ?, ? ); end;"; Connection conn = null; ResultSet rs = null; // DB Connection try { conn = this.getConnection(); } catch (SQLException e) { throw JSONException.sqlError(e); } catch (NamingException e) { throw JSONException.namingError(e.toString()); } try { CallableStatement stmt = conn.prepareCall(query); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.setString(2, cust); stmt.setDate(3, sqlStartDate); stmt.setDate(4, sqlEndDate); stmt.setInt(5, startPage); stmt.setInt(6, pageSize); stmt.execute(); rs = (ResultSet) stmt.getObject(1); while (rs.next()) { LineItem l = new LineItem(); l.setRowId(rs.getInt("ROW_ID")); l.setBaseAmount(rs.getBigDecimal("ITEM_BASE")); l.setItemTotal(rs.getBigDecimal("ITEM_TOTAL")); l.setItemDue(rs.getBigDecimal("ITEM_DUE")); l.setItemDisputed(rs.getBigDecimal("ITEM_DISPUTED")); l.setTaxAmount(rs.getBigDecimal("ITEM_TAX")); l.setBillDate(rs.getTimestamp("BILL_DATE")); l.setBillItemNo(rs.getString("BILL_ITEM_NO")); l.setBillTo(rs.getString("AR_ACCOUNT_NO")); l.setChargeType(rs.getString("CHARGE_TYPE")); l.setCreatedDate(rs.getTimestamp("CREATED_DATE")); l.setCurrency(rs.getString("CURRENCY")); l.setDueDate(rs.getTimestamp("DUE_DATE")); l.setEventType(rs.getString("EVENT_TYPE")); l.setItemCode(rs.getString("ITEM_CODE")); l.setItemDescription(rs.getString("ITEM_DESCRIPTION")); l.setLicense(rs.getString("LICENSE")); l.setItemID(rs.getString("ITEM_ID")); l.setOrderLine(rs.getString("ORDER_LINE_NO")); l.setOrderNo(rs.getString("ORDER_NO")); l.setPayAccountType(rs.getString("PAY_ACCT_TYPE")); l.setPayDescription(rs.getString("PAY_DESCR")); l.setPayType(rs.getString("PAY_TYPE")); l.setpSON(rs.getString("PAY_PSON")); l.setQuantity(rs.getInt("QUANTITY")); l.setReasonCode(rs.getString("REASON_CODE")); l.setStatus(rs.getInt("STATUS")); history.add(l); } conn.close(); rs.close(); } catch (SQLException e) { throw JSONException.sqlError(e); } if (history == null || history.isEmpty()) { throw JSONException.noDataFound("Null set returned - no data found"); } return history; }
From source file:it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP.java
private void setDate(CallableStatement cs, java.sql.Date d) throws SQLException { if (useName) { cs.setDate(currName, d); } else {/*from w ww . jav a2 s . co m*/ cs.setDate(colIdx, d); } }
From source file:com.mimp.hibernate.HiberNna.java
public void crearInforme(InformeNna tempInf, Long idExp) { Session session = sessionFactory.getCurrentSession(); session.beginTransaction();//from w w w . j a v a 2s . c o m final InformeNna inf = tempInf; final Long id = idExp; Work work = new Work() { @Override public void execute(Connection connection) throws SQLException { String hql = "{call HN_INSERT_INF(?,?,?,?,?)}"; CallableStatement statement = connection.prepareCall(hql); statement.setLong(1, id); statement.setString(2, inf.getNumero()); statement.setDate(3, (java.sql.Date) inf.getFecha()); statement.setString(4, inf.getResultado()); statement.setString(5, inf.getObservaciones()); statement.execute(); statement.close(); } }; session.doWork(work); }
From source file:com.mimp.hibernate.HiberNna.java
public void updateInforme(InformeNna temp) { Session session = sessionFactory.getCurrentSession(); session.beginTransaction();//from www . j av a 2s.c o m final InformeNna inf = temp; Work work = new Work() { @Override public void execute(Connection connection) throws SQLException { String hql = "{call HN_UPDATE_INF(?,?,?,?,?)}"; CallableStatement statement = connection.prepareCall(hql); statement.setLong(1, inf.getIdinformeNna()); statement.setString(2, inf.getNumero()); statement.setDate(3, (java.sql.Date) inf.getFecha()); statement.setString(4, inf.getResultado()); statement.setString(5, inf.getObservaciones()); statement.execute(); statement.close(); } }; session.doWork(work); }
From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java
@Override public int editAuction(final Auction auction) { int uid = -1; Connection conn = null;//from ww w.j a v a 2 s.c o m CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_EDITAUCTION (?,?,?,?,?,?)}"); stmt.registerOutParameter(1, Types.INTEGER); stmt.setString(2, auction.getName()); stmt.setDate(3, new Date(auction.getStartDate())); stmt.setDate(4, new Date(auction.getEndDate())); stmt.setString(5, auction.getLogoUrl()); stmt.setString(6, auction.getColor()); stmt.execute(); uid = stmt.getInt(1); } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("AUCTION [method:{} result:{}]", new Object[] { "edit", uid }); } return uid; }
From source file:com.mimp.hibernate.HiberNna.java
public void crearExpNna(ExpedienteNna temp) { Session session = sessionFactory.getCurrentSession(); session.beginTransaction();/*ww w .ja va2s .c om*/ final ExpedienteNna expnna = temp; Work work = new Work() { @Override public void execute(Connection connection) throws SQLException { String hql = "{call HN_SAVE_EXP_NNA(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"; CallableStatement statement = connection.prepareCall(hql); statement.setLong(1, expnna.getNna().getIdnna()); statement.setLong(2, expnna.getUnidad().getIdunidad()); statement.setString(3, expnna.getNumero()); statement.setDate(4, (java.sql.Date) expnna.getFechaIngreso()); statement.setString(5, expnna.getHt()); statement.setString(6, expnna.getNExpTutelar()); statement.setString(7, expnna.getProcTutelar()); statement.setShort(8, expnna.getFichaIntegral()); statement.setString(9, expnna.getComentarios()); statement.setString(10, expnna.getRespLegalNombre()); statement.setString(11, expnna.getRespLegalP()); statement.setString(12, expnna.getRespLegalM()); statement.setString(13, expnna.getRespPsicosocialNombre()); statement.setString(14, expnna.getRespPiscosocialP()); statement.setString(15, expnna.getRespPsicosocialM()); statement.setString(16, expnna.getEstado()); statement.setDate(17, (java.sql.Date) expnna.getFechaEstado()); statement.setShort(18, expnna.getAdoptable()); statement.setDate(19, (java.sql.Date) expnna.getFechaResolCons()); statement.setShort(20, expnna.getNacional()); statement.setString(21, expnna.getDiagnostico()); statement.setString(22, expnna.getCodigoReferencia()); statement.setString(23, expnna.getNActual()); statement.setString(24, expnna.getApellidopActual()); statement.setString(25, expnna.getApellidomActual()); statement.setString(26, expnna.getObservaciones()); statement.setDate(27, (java.sql.Date) expnna.getFechaInvTutelar()); statement.setDate(28, (java.sql.Date) expnna.getFechaIngPrio()); statement.setDate(29, (java.sql.Date) expnna.getFechaActualizacion()); statement.execute(); statement.close(); } }; session.doWork(work); }