Example usage for java.sql PreparedStatement setObject

List of usage examples for java.sql PreparedStatement setObject

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setObject

Introduction

In this page you can find the example usage for java.sql PreparedStatement setObject.

Prototype

void setObject(int parameterIndex, Object x) throws SQLException;

Source Link

Document

Sets the value of the designated parameter using the given object.

Usage

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  a v  a 2s. 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();
        }
    }
}

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

private void updateAttendees(AccessToken token, Connection con, Event event) throws SQLException {
    String q = "update EventLink set eventlink_state=?, eventlink_required=?, eventlink_userupdate=?, eventlink_percent=?, eventlink_is_organizer=? "
            + "where eventlink_event_id = ? AND eventlink_entity_id = ?";
    PreparedStatement ps = null;
    int[] updatedAttendees;
    List<Attendee> mightInsert = new LinkedList<Attendee>();
    List<Attendee> toInsert = new LinkedList<Attendee>();

    try {//from w  w w.  ja  v a  2  s.c  om
        ps = con.prepareStatement(q);

        for (Attendee at : event.getAttendees()) {
            int idx = 1;

            ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VPARTSTAT,
                    at.getParticipation().getState().toString()));
            ps.setObject(idx++,
                    obmHelper.getDBCP().getJdbcObject(ObmHelper.VROLE, at.getParticipationRole().toString()));
            ps.setInt(idx++, token.getObmId());
            ps.setInt(idx++, at.getPercent());
            ps.setBoolean(idx++, at.isOrganizer());
            ps.setInt(idx++, event.getObmId().getObmId());
            ps.setInt(idx++, at.getEntityId().getId());
            ps.addBatch();
            mightInsert.add(at);
        }
        updatedAttendees = ps.executeBatch();
    } finally {
        obmHelper.cleanup(null, ps, null);
    }

    for (int i = 0; i < updatedAttendees.length; i++) {
        if (updatedAttendees[i] == 0) {
            Attendee at = mightInsert.get(i);
            toInsert.add(at);
        }
    }

    logger.info("event modification needs to add " + toInsert.size() + " attendees.");
    insertAttendees(token, event, con, toInsert);
}

From source file:it.fub.jardin.server.DbUtils.java

/**
 * Put a Java Object in a PreparedStatement. Return the number of transferred
 * Objects./*from  w w w.  j  a  v  a  2 s.  c  om*/
 * 
 * @param ps
 *          The PreparedStatement to modify
 * @param i
 *          The index in which to put the Java Object
 * @param value
 *          The Object to put in the PreparedStatement
 * @return Number of transferred Objects (1 if all went OK, 0 otherwise). Tip:
 *         you can use this value to increment index pointer.
 * @throws SQLException
 */
private Integer putJavaObjectInPs(final PreparedStatement ps, final Integer i, final Object value)
        throws SQLException {

    // TODO Warning!
    if ((value != null) && (value.toString().length() > 0)) {
        ps.setObject(i, value);
    } else {
        ps.setNull(i, Types.NULL);
    }

    return 1;
}

From source file:org.nuxeo.ecm.core.storage.sql.Mapper.java

/**
 * Makes a NXQL query to the database./*from w  w w  .j  av  a 2s  . c  o m*/
 *
 * @param query the query
 * @param queryFilter the query filter
 * @param countTotal if {@code true}, count the total size without
 *            limit/offset
 * @param session the current session (to resolve paths)
 * @return the list of matching document ids
 * @throws StorageException
 * @throws SQLException
 */
public PartialList<Serializable> query(String query, QueryFilter queryFilter, boolean countTotal,
        Session session) throws StorageException {
    QueryMaker queryMaker = findQueryMaker(query);
    if (queryMaker == null) {
        throw new StorageException("No QueryMaker accepts query: " + query);
    }
    QueryMaker.Query q = queryMaker.buildQuery(sqlInfo, model, session, query, queryFilter);

    if (q == null) {
        log("Query cannot return anything due to conflicting clauses");
        return new PartialList<Serializable>(Collections.<Serializable>emptyList(), 0);
    }

    long limit = queryFilter.getLimit();
    long offset = queryFilter.getOffset();
    if (isLogEnabled()) {
        String sql = q.selectInfo.sql;
        if (limit != 0) {
            sql += " -- LIMIT " + limit + " OFFSET " + offset;
        }
        if (countTotal) {
            sql += " -- COUNT TOTAL";
        }
        logSQL(sql, q.selectParams);
    }
    PreparedStatement ps = null;
    try {
        ps = connection.prepareStatement(q.selectInfo.sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        int i = 1;
        for (Object object : q.selectParams) {
            if (object instanceof Calendar) {
                Calendar cal = (Calendar) object;
                Timestamp ts = new Timestamp(cal.getTimeInMillis());
                ps.setTimestamp(i++, ts, cal); // cal passed for timezone
            } else if (object instanceof String[]) {
                Array array = sqlInfo.dialect.createArrayOf(Types.VARCHAR, (Object[]) object, connection);
                ps.setArray(i++, array);
            } else {
                ps.setObject(i++, object);
            }
        }
        ResultSet rs = ps.executeQuery();

        // get all the returned data. name -> id 
        Column column = q.selectInfo.whatColumns.get(0);
        Map<Serializable, String> id2Name = new LinkedHashMap<Serializable, String>();
        while (rs.next()) {
            String id = (String) column.getFromResultSet(rs, 1);
            String name = rs.getString("name");
            id2Name.put(id, name);
        }

        String uname = queryFilter.getPrincipal().getName();
        Map<Serializable, CRMPermission> permissions = CRMPermissionRestService.filterHierarchyResult(uname,
                id2Name);

        long totalSize = permissions.size();
        List<Serializable> allDocIds = new ArrayList<Serializable>();
        allDocIds.addAll(permissions.keySet());

        List<Serializable> pageDocIds = null;
        if (limit == 0) {
            limit = allDocIds.size();
        }

        int startIdx = (offset > allDocIds.size()) ? allDocIds.size() : (int) offset;
        int endIdx = ((offset + limit) > allDocIds.size()) ? allDocIds.size() : (int) (offset + limit);

        pageDocIds = allDocIds.subList(startIdx, endIdx);

        return new PartialList<Serializable>(pageDocIds, totalSize);
    } catch (SQLException e) {
        checkConnectionReset(e);
        throw new StorageException("Invalid query: " + query, e);
    } finally {
        if (ps != null) {
            try {
                closePreparedStatement(ps);
            } catch (SQLException e) {
                log.error("Cannot close connection", e);
            }
        }
    }
}

From source file:org.latticesoft.util.resource.dao.Param.java

private void setValueToStatement(Object o, PreparedStatement pstmt) throws SQLException {
    if (log.isDebugEnabled()) {
        log.debug(this.sqlIndex + "=" + o);
    }//ww w.j  a  v a2 s  .c o m
    switch (this.sqlType) {
    case Types.VARCHAR:
    case Types.CHAR:
        String s = (String) o;
        pstmt.setString(this.sqlIndex, s);
        break;
    case Types.BOOLEAN:
        if (o != null && o instanceof Boolean) {
            boolean b = ((Boolean) o).booleanValue();
            pstmt.setBoolean(this.sqlIndex, b);
        }
        break;
    case Types.INTEGER:
        if (o != null && o instanceof Integer) {
            int i = ((Integer) o).intValue();
            pstmt.setInt(this.sqlIndex, i);
        }
        break;
    case Types.SMALLINT:
        if (o != null && o instanceof Short) {
            short ss = ((Short) o).shortValue();
            pstmt.setShort(this.sqlIndex, ss);
        }
        break;
    case Types.TINYINT:
        if (o != null && o instanceof Byte) {
            byte bb = ((Byte) o).byteValue();
            pstmt.setByte(this.sqlIndex, bb);
        }
        break;
    case Types.BIGINT:
        if (o != null && o instanceof Long) {
            long l = ((Long) o).longValue();
            pstmt.setLong(this.sqlIndex, l);
        }
        break;
    case Types.DOUBLE:
        if (o != null && o instanceof Double) {
            double dd = ((Double) o).doubleValue();
            pstmt.setDouble(this.sqlIndex, dd);
        }
        break;
    case Types.FLOAT:
        if (o != null && o instanceof Float) {
            float f = ((Float) o).floatValue();
            pstmt.setFloat(this.sqlIndex, f);
        }
        break;
    case Types.NUMERIC:
        if (o != null && o instanceof BigDecimal) {
            BigDecimal bd = (BigDecimal) o;
            pstmt.setBigDecimal(this.sqlIndex, bd);
        }
        break;
    case Types.TIMESTAMP:
        if (o != null && o instanceof Timestamp) {
            Timestamp ts = (Timestamp) o;
            pstmt.setTimestamp(this.sqlIndex, ts);
        }
        break;
    case Types.NULL:
        if (log.isDebugEnabled()) {
            log.debug(this.sqlIndex + " IS NULL");
        }
        pstmt.setNull(this.sqlIndex, Types.NULL);
        break;
    default:
        if (o != null) {
            pstmt.setObject(this.sqlIndex, o);
        }
    }
}

From source file:edu.ku.brc.specify.toycode.RegPivot.java

/**
 * @param newTblName/*from ww  w . ja va 2 s. c  o m*/
 * @param stmt
 * @param pStmt
 * @param fillSQL
 * @param secInx
 * @param dbFieldTypes
 * @param dbFieldNames
 * @param inxToName
 * @return
 * @throws SQLException
 */
private int fillTrackTableX(final String newTblName, final Statement stmt, final PreparedStatement pStmt,
        final String fillSQL, final int secInx, final Vector<Integer> dbFieldTypes,
        final Vector<String> dbFieldNames, final HashMap<Integer, String> inxToName) throws SQLException {
    System.out.println("Filling Track Table.");
    int instCnt = 0;

    HashMap<String, Object> nameToVals = new HashMap<String, Object>();

    System.out.println(fillSQL);

    String prevId = null;
    ResultSet rs = stmt.executeQuery(fillSQL);
    ResultSetMetaData rsmd = rs.getMetaData();

    HashMap<String, Integer> nameToIndex = new HashMap<String, Integer>();
    for (int c = 1; c <= rsmd.getColumnCount(); c++) {
        nameToIndex.put(rsmd.getColumnName(c), c);
        System.out.println(c + " - " + rsmd.getColumnName(c));
    }

    while (rs.next()) {
        String id = rs.getString(1);
        if (prevId == null)
            prevId = id;

        if (!prevId.equals(id)) {
            for (int i = 1; i < secInx; i++) {
                //System.out.println("Put: "+dbFieldNames.get(i-1)+"  "+dbFieldTypes.get(i-1));//+"  = "+rs.getObject(i));
                if (dbFieldTypes.get(i - 1) == java.sql.Types.TIMESTAMP) {
                    try {
                        String ts = rs.getString(i);
                        if (StringUtils.isNotEmpty(ts) && ts.equals("0000-00-00 00:00:00")) {
                            //nameToVals.put(dbFieldNames.get(i-1), null);
                            continue;
                        }
                    } catch (Exception ex) {
                        nameToVals.put(dbFieldNames.get(i - 1), null);//"2000-01-01 00:00:00");
                        continue;
                    }
                }
                nameToVals.put(dbFieldNames.get(i - 1), rs.getObject(i));
            }

            for (int i = 0; i < dbFieldNames.size(); i++) {
                int fInx = i + 1;
                String name = inxToName.get(i);
                Object value = nameToVals.get(name);

                pStmt.setObject(fInx, null);

                int typ = dbFieldTypes.get(i);

                if (value != null) {
                    switch (typ) {
                    case java.sql.Types.INTEGER:
                        if (value instanceof Integer) {
                            pStmt.setInt(fInx, (Integer) value);
                        }
                        break;

                    case java.sql.Types.VARCHAR:
                        if (value instanceof String) {
                            pStmt.setString(fInx, (String) value);
                        }
                        break;

                    case java.sql.Types.TIMESTAMP: {
                        if (value instanceof Timestamp) {
                            pStmt.setTimestamp(fInx, (Timestamp) value);
                        }
                        break;
                    }
                    }
                } else {
                    pStmt.setObject(fInx, null);
                }
            }
            pStmt.executeUpdate();

            prevId = id;
            nameToVals.clear();
        }

        String name = rs.getString(secInx);
        name = StringUtils.replace(name, "(", "_");
        name = StringUtils.replace(name, ")", "_");

        Integer intVal = (Integer) rs.getObject(secInx + 1);
        String strVal = (String) rs.getObject(secInx + 2);
        nameToVals.put(name, strVal != null ? strVal : intVal);
    }

    String alterSQL = "ALTER TABLE " + newTblName + " ADD Lookup VARCHAR(64) AFTER IP";
    BasicSQLUtils.update(connection, alterSQL);

    alterSQL = "ALTER TABLE " + newTblName + " ADD Country VARCHAR(64) AFTER Lookup";
    BasicSQLUtils.update(connection, alterSQL);

    alterSQL = "ALTER TABLE " + newTblName + " ADD City VARCHAR(64) AFTER Country";
    BasicSQLUtils.update(connection, alterSQL);

    return instCnt;
}

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

@Override
public List<Event> findAllEvents(AccessToken token, ObmUser calendarUser, EventType typeFilter) {
    String ev = "SELECT " + EVENT_SELECT_FIELDS + ", eventexception_date as recurrence_id " + " FROM Event e "
            + "INNER JOIN EventLink att ON att.eventlink_event_id=e.event_id "
            + "INNER JOIN UserEntity ue ON att.eventlink_entity_id=ue.userentity_entity_id "
            + "INNER JOIN EventEntity ON e.event_id=evententity_event_id "
            + "INNER JOIN UserObm o ON e.event_owner=o.userobm_id "
            + "INNER JOIN UserObm c ON e.event_usercreate=c.userobm_id "
            + "INNER JOIN Domain ON e.event_domain_id=domain_id "
            + "LEFT JOIN EventCategory1 ON e.event_category1_id=eventcategory1_id "
            + "LEFT JOIN EventException ON e.event_id = eventexception_child_id "
            + "WHERE ue.userentity_user_id=? and e.event_type=? ";

    StringBuilder sb = new StringBuilder(ev);

    PreparedStatement evps = null;
    ResultSet evrs = null;//from w  ww.  j  ava 2s  . co  m
    Connection con = null;

    Map<EventObmId, Event> eventById = new HashMap<EventObmId, Event>();
    List<Event> ret = new LinkedList<Event>();
    Calendar cal = getGMTCalendar();
    try {
        con = obmHelper.getConnection();
        evps = con.prepareStatement(sb.toString());
        int idx = 1;
        evps.setObject(idx++, calendarUser.getUid());
        evps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, typeFilter.toString()));
        evrs = evps.executeQuery();

        while (evrs.next()) {
            Event event = eventFromCursor(cal, evrs);
            eventById.put(event.getObmId(), event);
            ret.add(event);
        }

        IntegerIndexedSQLCollectionHelper eventIds = new IntegerIndexedSQLCollectionHelper(ret);
        loadAttendees(con, eventById, calendarUser.getDomain().getName());
        loadAlerts(con, token, eventById, eventIds);
        loadExceptions(con, cal, eventById, eventIds);
        loadEventExceptions(con, token, eventById, eventIds);

    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
    } finally {
        obmHelper.cleanup(con, evps, evrs);
    }

    return ret;
}

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

private List<DeletedEvent> findDeletedEvents(ObmUser calendarUser, Date d, EventType eventType,
        List<DeletedEvent> declined) {

    List<DeletedEvent> result = new LinkedList<DeletedEvent>();
    result.addAll(declined);// w ww.  j  av  a  2 s  .  com
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    String q = "SELECT deletedevent_event_id, deletedevent_event_ext_id FROM DeletedEvent "
            + "WHERE deletedevent_user_id=? AND deletedevent_type=? ";
    if (d != null) {
        q += "AND deletedevent_timestamp >= ?";
    }
    try {
        con = obmHelper.getConnection();
        ps = con.prepareStatement(q);
        ps.setInt(1, calendarUser.getUid());
        ps.setObject(2, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, eventType.toString()));
        if (d != null) {
            ps.setTimestamp(3, new Timestamp(d.getTime()));
        }

        rs = ps.executeQuery();
        while (rs.next()) {
            result.add(DeletedEvent.builder().eventObmId(rs.getInt(1)).eventExtId(rs.getString(2)).build());
        }
    } catch (SQLException se) {
        logger.error(se.getMessage(), se);
    } finally {
        obmHelper.cleanup(con, ps, rs);
    }

    return result;
}

From source file:org.obm.domain.dao.CalendarDaoJdbcImpl.java

@Override
public List<Event> listEventsByIntervalDate(final AccessToken token, final ObmUser obmUser,
        final Date startDate, final Date endDate, final EventType typeFilter) {

    final String sql = "SELECT " + EVENT_SELECT_FIELDS + ", eventexception_date as recurrence_id "
            + " FROM Event e " + "INNER JOIN EventLink att ON att.eventlink_event_id=e.event_id "
            + "INNER JOIN UserEntity ue ON att.eventlink_entity_id=ue.userentity_entity_id "
            + "INNER JOIN EventEntity ON e.event_id=evententity_event_id "
            + "INNER JOIN UserObm o ON e.event_owner=o.userobm_id "
            + "INNER JOIN UserObm c ON e.event_usercreate=c.userobm_id "
            + "INNER JOIN Domain ON e.event_domain_id=domain_id "
            + "LEFT JOIN EventCategory1 ON e.event_category1_id=eventcategory1_id "
            + "LEFT JOIN EventException ON e.event_id = eventexception_child_id "
            + "WHERE e.event_type=? AND ue.userentity_user_id=? "
            + "AND ((event_repeatkind != 'none' AND event_endrepeat <= ?) OR "
            + "(event_repeatkind = 'none' AND event_date >= ? AND event_date <= ?) )";

    PreparedStatement evps = null;
    ResultSet evrs = null;// ww  w  .  ja  v a 2s . c  om
    Connection con = null;

    final List<Event> changedEvent = new LinkedList<Event>();
    final Map<EventObmId, Event> eventById = new HashMap<EventObmId, Event>();
    final List<Event> ret = new LinkedList<Event>();
    final Calendar cal = getGMTCalendar();

    try {
        con = obmHelper.getConnection();
        evps = con.prepareStatement(sql);
        int idx = 1;
        evps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, typeFilter.toString()));
        evps.setObject(idx++, obmUser.getUid());
        evps.setTimestamp(idx++, new Timestamp(endDate.getTime()));
        evps.setTimestamp(idx++, new Timestamp(startDate.getTime()));
        evps.setTimestamp(idx++, new Timestamp(endDate.getTime()));
        evrs = evps.executeQuery();
        while (evrs.next()) {
            Event event = eventFromCursor(cal, evrs);
            Set<Date> extDate = getAllDateEventException(con, event.getObmId());
            Date recurDate = recurrenceHelper.isInIntervalDate(event, startDate, endDate, extDate);
            if (recurDate != null) {
                eventById.put(event.getObmId(), event);
                changedEvent.add(event);
                ret.add(event);
            }
        }

        IntegerIndexedSQLCollectionHelper changedIds = new IntegerIndexedSQLCollectionHelper(changedEvent);
        loadAttendees(con, eventById, obmUser.getDomain().getName());
        loadAlerts(con, token, eventById, changedIds);
        loadExceptions(con, cal, eventById, changedIds);
        loadEventExceptions(con, token, eventById, changedIds);

    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
    } finally {
        obmHelper.cleanup(con, evps, evrs);
    }

    return ret;
}

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

@Override
public List<Event> listEventsByIntervalDate(final AccessToken token, final ObmUser obmUser,
        final Date startDate, final Date endDate, final EventType typeFilter) {

    final String sql = "SELECT " + EVENT_SELECT_FIELDS + ", eventexception_date as recurrence_id "
            + " FROM Event e " + "INNER JOIN EventLink att ON att.eventlink_event_id=e.event_id "
            + "INNER JOIN UserEntity ue ON att.eventlink_entity_id=ue.userentity_entity_id "
            + "INNER JOIN EventEntity ON e.event_id=evententity_event_id "
            + "INNER JOIN UserObm o ON e.event_owner=o.userobm_id "
            + "INNER JOIN UserObm c ON e.event_usercreate=c.userobm_id "
            + "INNER JOIN Domain ON e.event_domain_id=domain_id "
            + "LEFT JOIN EventCategory1 ON e.event_category1_id=eventcategory1_id "
            + "LEFT JOIN EventException ON e.event_id = eventexception_child_id "
            + "WHERE e.event_type=? AND ue.userentity_user_id=? "
            + "AND ((event_repeatkind != 'none' AND event_endrepeat <= ?) OR "
            + "(event_repeatkind = 'none' AND event_date >= ? AND event_date <= ?) )";

    PreparedStatement evps = null;
    ResultSet evrs = null;/*from   www  .  j a v  a 2s  .c  o  m*/
    Connection con = null;

    final List<Event> changedEvent = new LinkedList<Event>();
    final Map<EventObmId, Event> eventById = new HashMap<EventObmId, Event>();
    final List<Event> ret = new LinkedList<Event>();
    final Calendar cal = getGMTCalendar();

    try {
        con = obmHelper.getConnection();
        evps = con.prepareStatement(sql);
        int idx = 1;
        evps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, typeFilter.toString()));
        evps.setObject(idx++, obmUser.getUid());
        evps.setTimestamp(idx++, new Timestamp(endDate.getTime()));
        evps.setTimestamp(idx++, new Timestamp(startDate.getTime()));
        evps.setTimestamp(idx++, new Timestamp(endDate.getTime()));
        evrs = evps.executeQuery();
        while (evrs.next()) {
            Event event = eventFromCursor(cal, evrs);
            Set<Date> extDate = getAllDateEventException(con, event.getObmId());
            Date recurDate = ical4jHelper.isInIntervalDate(event, startDate, endDate, extDate);
            if (recurDate != null) {
                eventById.put(event.getObmId(), event);
                changedEvent.add(event);
                ret.add(event);
            }
        }

        IntegerIndexedSQLCollectionHelper changedIds = new IntegerIndexedSQLCollectionHelper(changedEvent);
        loadAttendees(con, eventById, obmUser.getDomain().getName());
        loadAlerts(con, token, eventById, changedIds);
        loadExceptions(con, cal, eventById, changedIds);
        loadEventExceptions(con, token, eventById, changedIds);

    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
    } finally {
        obmHelper.cleanup(con, evps, evrs);
    }

    return ret;
}