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.bs.beans.SalesBean.java

public void reportFirstModel(HttpServletRequest request) {
    HttpServletRequest req = (HttpServletRequest) request;
    HttpSession ses = req.getSession(false);
    int emp_id = (Integer.parseInt(ses.getAttribute("status").toString()));
    Connection con = DB.getConnection();
    try {//from www  . j  a va 2s. c  o m
        PreparedStatement stm = con.prepareCall(
                "SELECT distinct MemoNo,sum(Price*Quantity) as GT FROM sales where Sell_Date=DATE_FORMAT(NOW(),'%Y-%m-%d') and Emp_Id=? group by MemoNo");
        stm.setInt(1, emp_id);
        ResultSet rs = stm.executeQuery();
        while (rs.next()) {
            sre1.add(new SeslesReport(rs.getInt(1), rs.getDouble(2)));
        }
    } catch (Exception e) {
    }

}

From source file:com.mobilewallet.common.dao.ForgotPasswordDAO.java

public Object[] getResetPasswordLink(String email, String uuid, String ip) {
    Connection connection = null;
    CallableStatement cstmt = null;
    Object[] obj = null;/*from  w ww  . j  a v  a  2 s  .c  o m*/
    int rvalue = -1;
    long userId = 0;
    try {
        connection = dataSource.getConnection();
        cstmt = connection.prepareCall("{call wp_forgot_pwd_reset_link(?,?,?,?,?)}");
        cstmt.setString(1, email);
        cstmt.setString(2, uuid);
        cstmt.setString(3, ip);
        cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
        cstmt.registerOutParameter(5, java.sql.Types.INTEGER);

        cstmt.execute();

        rvalue = cstmt.getInt(4);
        userId = cstmt.getLong(5);

        obj = new Object[2];
        obj[0] = rvalue;
        obj[1] = userId;
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

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

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

        }
    }
    return obj;
}

From source file:com.bs.beans.SalesBean.java

public void memoFindview() {
    Connection con = DB.getConnection();
    try {/*  w w w  .ja  v  a  2 s  .co  m*/
        PreparedStatement stm = con.prepareCall("select * from sales where MemoNo=?");
        stm.setInt(1, 4);
        ResultSet rs = stm.executeQuery();
        double gt = 0.0;
        while (rs.next()) {
            gt += rs.getDouble(6);
            setDate(rs.getString(7));
            slsList11.add(new SeslesReport(rs.getInt(8), rs.getInt(2), rs.getString(3), rs.getInt(5),
                    rs.getDouble(6), rs.getDate(7), rs.getInt(9)));
            System.out.println(rs.getDouble("Total"));
        }
        setGtota2(gt);
        con.close();
        rs.close();
    } catch (Exception e) {
        System.out.println(e.toString());
    }

}

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

public int submitQuestion(long userId, String question, String answerA, String answerB, String answerC,
        String answerD, String answer) {
    Connection connection = null;
    CallableStatement pstmt = null;
    ResultSet rs = null;//from   w ww . ja  va 2s  .c o m
    int submitted = 0;
    try {
        connection = dataSource.getConnection();
        pstmt = connection.prepareCall("{call submit_question(?,?,?,?,?,?,?,?)}");
        pstmt.setLong(1, userId);
        pstmt.setString(2, question);
        pstmt.setString(3, answerA);
        pstmt.setString(4, answerB);
        pstmt.setString(5, answerC);
        pstmt.setString(6, answerD);
        pstmt.setString(7, answer);
        pstmt.registerOutParameter(8, java.sql.Types.INTEGER);
        pstmt.execute();
        submitted = 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 submitted;
}

From source file:com.mimp.hibernate.HiberMail.java

public ArrayList<Object> usuario2(String user, String pass) {

    org.hibernate.Session session = sessionFactory.getCurrentSession();

    final String usuario = user;
    final String password = pass;

    Work work = new Work() {
        @Override// w  ww. j  a v  a  2 s .c o m
        public void execute(Connection connection) throws SQLException {
            String query = "{call CONTRASENA(?, ?, ?, ?)}";
            CallableStatement statement = connection.prepareCall(query);
            statement.setString(1, usuario);
            statement.setString(2, password);
            statement.registerOutParameter(3, java.sql.Types.VARCHAR);
            statement.registerOutParameter(4, java.sql.Types.VARCHAR);
            statement.execute();

            String correo = statement.getString(3);
            String mensaje = statement.getString(4);
            temp.add(0, correo);
            temp.add(1, mensaje);
            statement.close();
        }
    };

    session.doWork(work);

    return temp;
}

From source file:com.linkedin.pinot.integration.tests.BaseClusterIntegrationTest.java

public static void createH2SchemaAndInsertAvroFiles(List<File> avroFiles, Connection connection) {
    try {//w  w  w  .  ja v a  2 s  .c  om
        connection.prepareCall("DROP TABLE IF EXISTS mytable");
        File schemaAvroFile = avroFiles.get(0);
        DatumReader<GenericRecord> datumReader = new GenericDatumReader<GenericRecord>();
        DataFileReader<GenericRecord> dataFileReader = new DataFileReader<GenericRecord>(schemaAvroFile,
                datumReader);

        Schema schema = dataFileReader.getSchema();
        List<Schema.Field> fields = schema.getFields();
        List<String> columnNamesAndTypes = new ArrayList<String>(fields.size());
        int columnCount = 0;
        for (Schema.Field field : fields) {
            String fieldName = field.name();
            Schema.Type fieldType = field.schema().getType();
            switch (fieldType) {
            case UNION:
                List<Schema> types = field.schema().getTypes();
                String columnNameAndType;
                String typeName = types.get(0).getName();

                if (typeName.equalsIgnoreCase("int")) {
                    typeName = "bigint";
                }

                if (types.size() == 1) {
                    columnNameAndType = fieldName + " " + typeName + " not null";
                } else {
                    columnNameAndType = fieldName + " " + typeName;
                }

                columnNamesAndTypes.add(columnNameAndType.replace("string", "varchar(128)"));
                ++columnCount;
                break;
            case ARRAY:
                String elementTypeName = field.schema().getElementType().getName();

                if (elementTypeName.equalsIgnoreCase("int")) {
                    elementTypeName = "bigint";
                }

                elementTypeName = elementTypeName.replace("string", "varchar(128)");

                for (int i = 0; i < MAX_ELEMENTS_IN_MULTI_VALUE; i++) {
                    columnNamesAndTypes.add(fieldName + "__MV" + i + " " + elementTypeName);
                }
                ++columnCount;
                break;
            case BOOLEAN:
            case INT:
            case LONG:
            case FLOAT:
            case DOUBLE:
            case STRING:
                String fieldTypeName = fieldType.getName();

                if (fieldTypeName.equalsIgnoreCase("int")) {
                    fieldTypeName = "bigint";
                }

                columnNameAndType = fieldName + " " + fieldTypeName + " not null";

                columnNamesAndTypes.add(columnNameAndType.replace("string", "varchar(128)"));
                ++columnCount;
                break;
            case RECORD:
                // Ignore records
                continue;
            default:
                // Ignore other avro types
                LOGGER.warn("Ignoring field {} of type {}", fieldName, field.schema());
            }
        }

        connection.prepareCall("create table mytable("
                + StringUtil.join(",", columnNamesAndTypes.toArray(new String[columnNamesAndTypes.size()]))
                + ")").execute();
        long start = System.currentTimeMillis();
        StringBuilder params = new StringBuilder("?");
        for (int i = 0; i < columnNamesAndTypes.size() - 1; i++) {
            params.append(",?");
        }
        PreparedStatement statement = connection
                .prepareStatement("INSERT INTO mytable VALUES (" + params.toString() + ")");

        dataFileReader.close();

        for (File avroFile : avroFiles) {
            datumReader = new GenericDatumReader<GenericRecord>();
            dataFileReader = new DataFileReader<GenericRecord>(avroFile, datumReader);
            GenericRecord record = null;
            while (dataFileReader.hasNext()) {
                record = dataFileReader.next(record);
                int jdbcIndex = 1;
                for (int avroIndex = 0; avroIndex < columnCount; ++avroIndex) {
                    Object value = record.get(avroIndex);
                    if (value instanceof GenericData.Array) {
                        GenericData.Array array = (GenericData.Array) value;
                        for (int i = 0; i < MAX_ELEMENTS_IN_MULTI_VALUE; i++) {
                            if (i < array.size()) {
                                value = array.get(i);
                                if (value instanceof Utf8) {
                                    value = value.toString();
                                }
                            } else {
                                value = null;
                            }
                            statement.setObject(jdbcIndex, value);
                            ++jdbcIndex;
                        }
                    } else {
                        if (value instanceof Utf8) {
                            value = value.toString();
                        }
                        statement.setObject(jdbcIndex, value);
                        ++jdbcIndex;
                    }
                }
                statement.execute();
            }
            dataFileReader.close();
        }
        LOGGER.info("Insertion took " + (System.currentTimeMillis() - start));
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

From source file:es.indaba.jdbc.annotations.impl.GenericWork.java

@SuppressWarnings("unchecked")
@Override/* w  w  w .ja  va2s. c om*/
public void execute(Connection con) throws SQLException {
    String procedureCall = procedure.value();
    FieldResult[] fields = proceduresResult == null ? new FieldResult[0] : proceduresResult.value();

    CallableStatement st = null;
    try {
        st = con.prepareCall(procedureCall);
        for (SQLParameter p : parameters) {
            int pos = p.getPosition();
            Object val = p.getValue();
            Class type = p.getType();
            Class sqlType = p.getSqlType();
            Integer jdbcType = SQLTypeMapping.getSqlTypeforClass(type);
            if (jdbcType != null) {
                if (val != null) {
                    SQLTypeMapping.setSqlParameter(st, type, sqlType, pos, val);
                } else {
                    st.setNull(pos, jdbcType);
                }
            }
        }
        for (FieldResult field : fields) {
            int position = field.position();
            Class type = field.sqlType();
            if (type == null || type.equals(Object.class)) {
                type = field.type();
            }
            Integer jdbcType = SQLTypeMapping.getSqlTypeforClass(type);
            if (position != FieldResult.RESULTSET) {
                st.registerOutParameter(position, jdbcType);
            }
        }
        st.execute();

        if (!returnType.equals(void.class)) {
            // Return instance
            resultObject = returnType.newInstance();
            ResultSet rs = st.getResultSet();
            for (FieldResult field : fields) {
                String property = field.name();
                Object result = null;
                if (field.position() == FieldResult.RESULTSET) {
                    rs.next();
                    result = SQLTypeMapping.getSqlResultsetResult(rs, field.type(), field.sqlType(), 1);
                } else {
                    result = SQLTypeMapping.getSqlResult(st, field.type(), field.sqlType(), field.position());
                }
                BeanUtils.setProperty(resultObject, property, result);
            }
        }
    } catch (Exception e) {
        logger.log(Level.SEVERE, e.getMessage(), e);
    }
}

From source file:net.solarnetwork.node.dao.jdbc.derby.DerbyCompressTableJob.java

@Override
protected void executeInternal(JobExecutionContext jobContext) throws Exception {
    if (log.isDebugEnabled()) {
        log.debug("Compressing Derby table " + schema + '.' + table + " with purgeRows = " + purgeRows
                + ", defragmentRows = " + defragmentRows + ", truncateEnd = " + truncateEnd);
    }//from  www. j  av a  2s  .  c  o m
    jdbcOperations.execute(new CallableStatementCreator() {
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            if (log.isTraceEnabled()) {
                log.trace("Preparing Derby compress table call [" + COMPRESS_CALL + ']');
            }
            return con.prepareCall(COMPRESS_CALL);
        }
    }, new CallableStatementCallback<Object>() {
        public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
            int idx = 1;
            cs.setString(idx++, schema);
            cs.setString(idx++, table);
            cs.setShort(idx++, purgeRows ? (short) 1 : (short) 0);
            cs.setShort(idx++, defragmentRows ? (short) 1 : (short) 0);
            cs.setShort(idx++, truncateEnd ? (short) 1 : (short) 0);
            boolean result = cs.execute();
            if (log.isTraceEnabled()) {
                log.trace("Derby compress table call returned [" + result + ']');
            }
            return null;
        }
    });
    if (log.isInfoEnabled()) {
        log.info("Compressed Derby table " + schema + '.' + table);
    }
}

From source file:com.mobilewallet.credits.dao.CreditsDAO.java

public int updateCredits(long userId, String isCorrect, int position) {
    int updated = 0;
    Connection connection = null;
    CallableStatement cstmt = null;
    try {//from   ww  w  .ja v a2s .com
        connection = dataSource.getConnection();
        cstmt = connection.prepareCall("{call update_credits_proc(?,?,?,?)}");
        cstmt.setLong(1, userId);
        cstmt.setString(2, isCorrect);
        cstmt.setInt(3, position);
        cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
        cstmt.execute();

        updated = cstmt.getInt(4);
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

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

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

        }
    }
    return updated;
}

From source file:com.jbrisbin.vpc.jobsched.sql.SqlMessageHandler.java

public SqlMessage handleMessage(final SqlMessage msg) throws Exception {
    log.debug("handling message: " + msg.toString());

    DataSource ds = appCtx.getBean(msg.getDatasource(), DataSource.class);
    JdbcTemplate tmpl = new JdbcTemplate(ds);

    String sql = msg.getSql();//from  w w w  . j  av a 2  s  .c o m
    CallableStatementCreator stmtCreator = null;
    CallableStatementCallback<SqlMessage> callback = null;
    if (sql.startsWith("plugin:")) {
        // Use a plugin to get the sql
        String pluginName = (sql.contains("?") ? sql.substring(7, sql.indexOf('?')) : sql.substring(7));
        final Plugin plugin = groovyPluginManager.getPlugin(pluginName);
        Map<String, Object> vars = new LinkedHashMap<String, Object>();
        vars.put("message", msg);
        vars.put("datasource", ds);
        vars.put("listen", groovyClosureFactory.createListenClosure(msg));
        vars.put("mapreduce", groovyClosureFactory.createMapReduceClosure(msg));
        plugin.setContext(vars);

        // Execute this plugin
        plugin.run();

        Object o = plugin.get("sql");
        if (null != o && o instanceof Closure) {
            sql = ((Closure) o).call(msg).toString();
        } else if (o instanceof String || o instanceof GString) {
            sql = o.toString();
        } else {
            throw new IllegalStateException("Can't convert " + String.valueOf(o) + " to SQL statement.");
        }
        msg.setSql(sql);

        o = plugin.get("statementCreator");
        if (null != o && o instanceof Closure) {
            stmtCreator = new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con) throws SQLException {
                    Object obj = ((Closure) plugin.get("statementCreator")).call(new Object[] { con, msg });
                    log.debug("from plugin statementCreator: " + String.valueOf(obj));
                    return (CallableStatement) obj;
                }
            };
        } else {
            throw new IllegalStateException("Can't convert " + String.valueOf(o)
                    + " to CallableStatementCreator. Define a closure named 'statementCreator' in your plugin.");
        }

        o = plugin.get("callback");
        if (null != o && o instanceof Closure) {
            callback = new CallableStatementCallback<SqlMessage>() {
                public SqlMessage doInCallableStatement(CallableStatement cs)
                        throws SQLException, DataAccessException {
                    Object obj = ((Closure) plugin.get("callback")).call(new Object[] { cs, msg });
                    log.debug("from plugin callback: " + String.valueOf(obj));
                    return (SqlMessage) obj;
                }
            };
        } else {
            throw new IllegalStateException("Can't convert " + String.valueOf(o)
                    + " to CallableStatementCallback. Define a closure named 'callback' in your plugin.");
        }
    } else {
        stmtCreator = new CallableStatementCreator() {
            public CallableStatement createCallableStatement(Connection connection) throws SQLException {
                CallableStatement stmt = connection.prepareCall(msg.getSql());
                List<Object> params = msg.getParams();
                if (null != params) {
                    int index = 1;
                    for (Object obj : params) {
                        stmt.setObject(index++, obj);
                    }
                }
                return stmt;
            }
        };
        callback = new CallableStatementCallback<SqlMessage>() {
            public SqlMessage doInCallableStatement(CallableStatement callableStatement)
                    throws SQLException, DataAccessException {
                if (null == msg.getResults().getData()) {
                    msg.getResults().setData(new ArrayList<List<Object>>());
                }
                if (callableStatement.execute()) {
                    ResultSet results = callableStatement.getResultSet();

                    // Pull out column names
                    ResultSetMetaData meta = results.getMetaData();
                    String[] columns = new String[meta.getColumnCount()];
                    for (int i = 1; i <= meta.getColumnCount(); i++) {
                        columns[i - 1] = meta.getColumnName(i);
                    }
                    msg.getResults().getColumnNames().addAll(Arrays.asList(columns));

                    int total = 0;
                    while (results.next()) {
                        List<Object> row = new ArrayList<Object>(columns.length);
                        for (int i = 1; i <= columns.length; i++) {
                            row.add(results.getObject(i));
                        }
                        msg.getResults().getData().add(row);
                        total++;
                    }
                    msg.getResults().setTotalRows(total);

                } else {
                    msg.getResults().getColumnNames().add("updateCount");
                    msg.getResults().setTotalRows(1);
                    List<Object> updCnt = new ArrayList<Object>(1);
                    updCnt.add(callableStatement.getUpdateCount());
                    msg.getResults().getData().add(updCnt);
                }
                return msg;
            }
        };
    }
    try {
        tmpl.setExceptionTranslator(appCtx.getBean(SQLExceptionTranslator.class));
    } catch (NoSuchBeanDefinitionException notfound) {
        // IGNORED
    }

    if (null != stmtCreator && null != callback) {
        try {
            tmpl.execute(stmtCreator, callback);
        } catch (Throwable t) {
            log.error(t.getMessage(), t);
            List<String> errors = new ArrayList<String>();
            errors.add(t.getMessage());
            Throwable cause = t.getCause();
            if (null != cause) {
                do {
                    errors.add(cause.getMessage());
                } while (null != (cause = cause.getCause()));
            }
            msg.getResults().setErrors(errors);
        }
    } else {
        log.warn("CallableStatementCreator and/or CallableStatementCallback where empty. "
                + "Make sure your plugin provides these under 'statementCreator' and 'callback' respectively.");
    }
    return msg;
}