Example usage for java.sql Connection prepareCall

List of usage examples for java.sql Connection prepareCall

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. prepareCall

Introduction

In this page you can find the example usage for java.sql Connection prepareCall.

Prototype

CallableStatement prepareCall(String sql) throws SQLException;

Source Link

Document

Creates a CallableStatement object for calling database stored procedures.

Usage

From source file:com.mobilewallet.users.dao.UserDAO.java

public int updateProfile(long userId, String mCode, String mobileNumber, String dob, String gender,
        String occupation, String income) {
    Connection connection = null;
    CallableStatement pstmt = null;
    ResultSet rs = null;/*www  .  java  2s. c o  m*/
    int updated = 0;
    try {
        connection = dataSource.getConnection();
        pstmt = connection.prepareCall("{call UPDATE_PROFILE(?,?,?,?,?,?,?,?)}");
        pstmt.setLong(1, userId);
        pstmt.setString(2, mCode);
        pstmt.setString(3, mobileNumber);
        pstmt.setString(4, dob);
        pstmt.setString(5, gender);
        pstmt.setString(6, occupation);
        pstmt.setString(7, income);
        pstmt.registerOutParameter(8, java.sql.Types.INTEGER);
        pstmt.execute();
        updated = pstmt.getInt(8);
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }
    return updated;
}

From source file:com.mobilewallet.admin.dao.QuestionDAO.java

public int approveQuestion(long userId, long q_id, String is_admin_approved) {
    Connection connection = null;
    CallableStatement pstmt = null;
    ResultSet rs = null;//from   w w  w  .j  av a  2s .  c  o  m
    int approved = 0;
    try {
        connection = dataSource.getConnection();
        pstmt = connection.prepareCall("{call approve_question(?,?,?,?)}");
        pstmt.setLong(1, userId);
        pstmt.setLong(2, q_id);
        pstmt.setString(3, is_admin_approved);

        pstmt.registerOutParameter(4, java.sql.Types.INTEGER);
        pstmt.execute();
        approved = pstmt.getInt(4);
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }
    return approved;
}

From source file:de.whs.poodle.repositories.ExerciseRepository.java

public void save(Exercise exercise) {
    if (exercise.getTitle().trim().isEmpty())
        throw new BadRequestException("noTitleSpecified");
    if (exercise.getText().trim().isEmpty())
        throw new BadRequestException("noExerciseTextSpecified");

    jdbc.execute(new ConnectionCallback<Void>() {

        @Override//w  w w.  j a v a 2  s  .  co  m
        public Void doInConnection(Connection con) throws SQLException, DataAccessException {
            try (CallableStatement exercisePs = con
                    .prepareCall("{ ? = CALL create_exercise(?,?,?::exercise_visibility,?,?,?,?,?,?,?,?,?) }");

                    PreparedStatement tagsPs = con
                            .prepareStatement("INSERT INTO exercise_to_tag(exercise_id,tag_id) VALUES(?,?)");) {
                con.setAutoCommit(false);

                // inner try for rollback
                try {
                    // create exercise
                    exercisePs.registerOutParameter(1, Types.INTEGER); // new_id

                    exercisePs.setString(2, exercise.getText());

                    /*
                     * The root id is always the ID of the first revision. If this
                     * is a new exercise, this ID obviously doesn't exist yet. We set
                     * NULL in this case, but a trigger in the DB will automatically
                     * set the root_id to the generated id.
                     */
                    if (exercise.getRootId() == 0)
                        exercisePs.setNull(3, Types.INTEGER);
                    else
                        exercisePs.setInt(3, exercise.getRootId());

                    exercisePs.setString(4, exercise.getVisibility().toString());
                    exercisePs.setString(5, exercise.getTitle());
                    exercisePs.setInt(6, exercise.getChangedBy().getId());
                    exercisePs.setString(7, exercise.getHint1());
                    exercisePs.setString(8, exercise.getHint2());

                    // sample solution
                    SampleSolutionType sampleSolutionType = exercise.getSampleSolutionType();

                    if (sampleSolutionType == SampleSolutionType.NONE) {
                        exercisePs.setNull(9, Types.INTEGER);
                        exercisePs.setNull(10, Types.VARCHAR);
                    } else if (sampleSolutionType == SampleSolutionType.FILE) {
                        exercisePs.setInt(9, exercise.getSampleSolution().getFile().getId());
                        exercisePs.setNull(10, Types.VARCHAR);
                    } else { // must be text
                        exercisePs.setNull(9, Types.INTEGER);
                        exercisePs.setString(10, exercise.getSampleSolution().getText());
                    }

                    // attachments
                    List<Integer> attachmentIds = exercise.getAttachments().stream().map(a -> a.getId())
                            .collect(Collectors.toList());

                    Array anhaengeIdsArray = con.createArrayOf("int4", attachmentIds.toArray());
                    exercisePs.setArray(11, anhaengeIdsArray);

                    exercisePs.setInt(12, exercise.getCourseId());

                    exercisePs.setString(13, exercise.getComment());

                    exercisePs.executeUpdate();

                    /* Set the generated ID so the calling function can read it. */
                    exercise.setId(exercisePs.getInt(1));

                    // create relation to tags
                    tagsPs.setInt(1, exercise.getId());

                    for (Tag t : exercise.getTags()) {
                        tagsPs.setInt(2, t.getId());
                        tagsPs.addBatch();
                    }

                    tagsPs.executeBatch();

                    con.commit();
                } catch (SQLException e) {
                    con.rollback();
                    throw e;
                } finally {
                    con.setAutoCommit(true);
                }
            }

            return null;
        }
    });
}

From source file:com.mobilewallet.admin.dao.QuestionDAO.java

public int submitQuestion(String question, String qType, String option1, String option2, String option3,
        String option4, String answer, String explanation, String isAdminApproved) {
    Connection connection = null;
    CallableStatement pstmt = null;
    ResultSet rs = null;/* w ww  .j  a  va  2s.com*/
    int submitted = 0;
    try {
        connection = dataSource.getConnection();
        pstmt = connection.prepareCall("{call SUBMIT_QUESTION(?,?,?,?,?,?,?,?,?,?)}");
        pstmt.setString(1, qType);
        pstmt.setString(2, question);
        pstmt.setString(3, option1);
        pstmt.setString(4, option2);
        pstmt.setString(5, option3);
        pstmt.setString(6, option4);
        pstmt.setString(7, answer);
        pstmt.setString(8, explanation);
        pstmt.setString(9, isAdminApproved);
        pstmt.registerOutParameter(10, java.sql.Types.INTEGER);
        pstmt.execute();
        submitted = pstmt.getInt(10);
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }
    return submitted;
}

From source file:com.yoncabt.ebr.jdbcbridge.pool.DataSource.java

private void fillHint(Connection connection, String client, String module, String action) {
    if (connection.getClass().getName().startsWith("oracle")) { //dier veritabanlar iin bakmak lazm
        try {/*from   w w  w.  j  a va2  s  . co  m*/
            CallableStatement call;
            call = connection.prepareCall("call dbms_application_info.set_client_info(?)");
            call.setString(1, client);
            call.execute();

            call = connection
                    .prepareCall("call dbms_application_info.set_module(module_name => ?, action_name => ?)");
            call.setString(1, module);
            call.setString(2, action);
            call.execute();

        } catch (SQLException ex) {
            logManager.error(ex);
        }
    }
}

From source file:com.wabacus.system.dataset.update.action.rationaldb.SPUpdateAction.java

public void updateData(ReportRequest rrequest, Map<String, String> mRowData, Map<String, String> mParamValues)
        throws SQLException {
    String realsql = getExecuteSql(rrequest, mRowData, mParamValues);
    AbsDatabaseType dbtype = rrequest.getDbType(this.datasource);
    Connection conn = rrequest.getConnection(this.datasource);
    CallableStatement cstmt = null;
    try {/* ww  w  .  j a  va  2s.  c om*/
        ReportBean rbean = this.ownerUpdateBean.getOwner().getReportBean();
        if (Config.show_sql)
            log.info("Execute sql:" + realsql);
        cstmt = conn.prepareCall(realsql);
        if (lstParams != null && lstParams.size() > 0) {
            int idx = 1;
            IDataType varcharTypeObj = Config.getInstance().getDataTypeByClass(VarcharType.class);
            EditableReportParamBean paramBeanTmp;
            for (Object paramObjTmp : this.lstParams) {
                if (paramObjTmp instanceof EditableReportParamBean) {
                    paramBeanTmp = (EditableReportParamBean) paramObjTmp;
                    paramBeanTmp.getDataTypeObj()
                            .setPreparedStatementValue(idx++,
                                    paramBeanTmp.getRuntimeParamValue(rrequest, rbean, mRowData, mParamValues,
                                            this.datasource, ownerUpdateBean.isAutoReportdata()),
                                    cstmt, dbtype);
                } else {
                    varcharTypeObj.setPreparedStatementValue(idx++,
                            paramObjTmp == null ? "" : String.valueOf(paramObjTmp), cstmt, dbtype);
                }
            }
        }
        int outputindex = -1;
        if (this.returnValueParamname != null && !this.returnValueParamname.trim().equals("")) {//
            outputindex = this.lstParams == null ? 1 : this.lstParams.size() + 1;
            cstmt.registerOutParameter(outputindex, java.sql.Types.VARCHAR);
        }
        cstmt.execute();
        if (outputindex > 0) {
            String rtnVal = cstmt.getString(outputindex);
            storeReturnValue(rrequest, mParamValues, rtnVal);
        }
    } finally {
        WabacusAssistant.getInstance().release(null, cstmt);
    }
}

From source file:com.tera.common.database.query.CQueryService.java

@Override
public <T> T call(String call, CallReadQuery<T> query, String errorMessage) {
    Connection connection = null;
    CallableStatement statement = null;
    ResultSet resultSet = null;/*from  www. j av a  2 s  . c  o  m*/

    try {
        connection = databaseFactory.getConnection();
        statement = connection.prepareCall(call);
        query.setParams(statement);
        resultSet = statement.executeQuery();
        return query.handleRead(resultSet);
    } catch (Exception ex) {
        if (errorMessage == null)
            log.error("Error calling stored procedure " + ex, ex);
        else
            log.error(errorMessage + " " + ex, ex);
    } finally {
        close(resultSet, statement, connection);
    }
    return null;
}

From source file:com.jfootball.dao.hibernate.PlayerDaoImpl.java

public void careerPlayerJob() {
    logger.info("Fix CareerPlayer job");

    Session session = hibernateTemplate.getSessionFactory().getCurrentSession();
    SessionFactoryImpl sessionFactory = (SessionFactoryImpl) session.getSessionFactory();

    try {//  www .  ja  v  a2s  .  co  m
        Connection conn = sessionFactory.getConnectionProvider().getConnection();
        CallableStatement cstmt = conn.prepareCall("{ call careerPlayerBatch() }");
        cstmt.execute();
    } catch (SQLException e) {
        logger.error(e);
    }
    logger.info("Fix CareerPlayer job finished");
}

From source file:com.cisco.iwe.services.util.EmailMonitor.java

/**
 * /*ww w  . j a  va  2s.  c o  m*/
 * @throws Exception
 */
/* This sends notification to the user via mail in case the user has uploaded an expense receipt type apart from .jpg,.jpeg,.bmp,.png,.txt,.doc,.pdf formats. */
public void sendNotification() throws Exception {
    CallableStatement stmt = null;
    Connection conn = null;
    try {
        conn = DataBaseUtil.getDevConnection();
        stmt = conn.prepareCall(EmailParseConstants.sendNotificationQuery);
        stmt.setString(1, EmailParseConstants.genericMailAccount);
        stmt.setString(2, EmailParseConstants.emailHostName);
        stmt.setString(3, EmailParseConstants.mailPortNumber);
        stmt.executeUpdate();
    } finally {
        try {
            if (stmt != null) {
            }
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }
}

From source file:com.jfootball.dao.hibernate.PlayerDaoImpl.java

public void endSeasonJob() {
    logger.info("Execute endSeason job");

    Session session = hibernateTemplate.getSessionFactory().getCurrentSession();
    SessionFactoryImpl sessionFactory = (SessionFactoryImpl) session.getSessionFactory();

    try {/*from  w w w.j a  v  a  2  s  .  co  m*/
        Connection conn = sessionFactory.getConnectionProvider().getConnection();
        CallableStatement cstmt = conn.prepareCall("{ call endSeasonBatch(?) }");

        GregorianCalendar gc = new GregorianCalendar();
        gc.setTime(new Date(System.currentTimeMillis()));

        int year = gc.get(Calendar.YEAR);

        cstmt.setString("param_year", "30/06/" + year); // current year.

        cstmt.execute();

        logger.info("EndSeason job executed");
    } catch (SQLException e) {
        logger.error(e);
    }

    logger.info("EndSeason job finished");
}