Example usage for java.sql Connection prepareCall

List of usage examples for java.sql Connection 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:exifIndexer.MetadataReader.java

public static void walk(String path, boolean is_recursive) {

    File root = new File(path);
    File[] list = root.listFiles();
    String filePath;//w  w  w  . j av a  2  s  .c om
    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:gridool.util.jdbc.JDBCUtils.java

public static boolean call(Connection conn, String sql, Object[] params) throws SQLException {
    CallableStatement proc = null;
    try {/*from ww w  .j  a  va  2 s  . com*/
        proc = conn.prepareCall(sql);
        fillStatement(proc, params);
        verboseQuery(sql, params);
        return proc.execute();
    } catch (SQLException e) {
        rethrow(e, sql, params);
    } finally {
        close(proc);
    }
    return false;
}

From source file:com.ibm.research.rdf.store.runtime.service.sql.UpdateHelper.java

private static String executeCall(Connection conn, String sql, int retPid, Object... params) {

    CallableStatement stmt = null;
    String ret = null;/*from w ww  .  jav  a  2 s .  c o m*/

    try {
        conn.setAutoCommit(false);
    } catch (SQLException ex) {
        log.error(ex);
        ex.printStackTrace();
        System.out.println(ex.getLocalizedMessage());
        return ret;
    }

    try {

        stmt = conn.prepareCall(sql);
        int i = 1;
        for (Object o : params) {
            stmt.setObject(i, o);
            i++;
        }

        stmt.registerOutParameter(retPid, Types.VARCHAR);

        stmt.execute();
        ret = stmt.getString(retPid);

        conn.commit();

    } catch (SQLException e) {
        //         log.error(e);
        //         e.printStackTrace();
        //         System.out.println(e.getLocalizedMessage());
        ret = null;

        try {
            conn.rollback();
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            log.error(e1);
            e1.printStackTrace();
            System.out.println(e1.getLocalizedMessage());
            ret = null;
        }

    } finally {
        closeSQLObjects(stmt, null);
    }

    try {
        conn.setAutoCommit(true);
    } catch (SQLException ex) {
        log.error(ex);
        ex.printStackTrace();
        System.out.println(ex.getLocalizedMessage());
        ret = null;
    }

    return ret;
}

From source file:ips1ap101.lib.core.db.util.DB.java

public static CallableStatement prepareCall(Connection connection, String sql, Object[] args,
        EnumTipoResultadoSQL resultType, EnumTipoDatoSQL dataType) {
    CallableStatement callableStatement;
    if (connection != null && sql != null) {
        try {/*from   ww  w . ja v  a 2 s  .c  o m*/
            callableStatement = connection.prepareCall(sql);
            int n = args == null ? 0 : args.length;
            if (n > 0) {
                for (int i = 0; i < n; i++) {
                    if (args[i] == null) {
                        //                          callableStatement.setNull(i + 1, java.sql.Types.OTHER);
                        callableStatement.setNull(i + 1, java.sql.Types.NULL);
                    } else if (args[i] instanceof EnumTipoDatoSQL) {
                        EnumTipoDatoSQL tipoDatoSQL = (EnumTipoDatoSQL) args[i];
                        callableStatement.setNull(i + 1, tipoDatoSQL.intValue());
                    } else {
                        callableStatement.setObject(i + 1, args[i]);
                    }
                }
            }
            if (EnumTipoResultadoSQL.SIMPLE.equals(resultType) && dataType != null) {
                callableStatement.registerOutParameter(n + 1, dataType.intValue());
            }
            return callableStatement;
        } catch (SQLException ex) {
            Bitacora.logFatal(ex);
        }
    }
    return null;
}

From source file:jongo.jdbc.JDBCExecutor.java

/**
 * Executes the given stored procedure or function in the RDBMS using the given List 
 * of {@link jongo.jdbc.StoredProcedureParam}.
 * @param database database name or schema where to execute the stored procedure or function
 * @param queryName the name of the stored procedure or function. This gets converted to a {call foo()} statement.
 * @param params a List of {@link jongo.jdbc.StoredProcedureParam} used by the stored procedure or function.
 * @return a List of {@link jongo.rest.xstream.Row} with the results of the stored procedure (if out parameters are given)
 * or the results of the function./*  w  w  w  .  ja  v a 2  s  . c o m*/
 * @throws SQLException
 */
public static List<Row> executeQuery(final String database, final String queryName,
        final List<StoredProcedureParam> params) throws SQLException {
    l.debug("Executing stored procedure " + database + "." + queryName);

    DatabaseConfiguration dbconf = conf.getDatabaseConfiguration(database);
    QueryRunner run = JDBCConnectionFactory.getQueryRunner(dbconf);
    final String call = JongoUtils.getCallableStatementCallString(queryName, params.size());
    List<Row> rows = new ArrayList<Row>();

    Connection conn = null;
    CallableStatement cs = null;
    try {
        l.debug("Obtain connection from datasource");
        conn = run.getDataSource().getConnection();

        l.debug("Create callable statement for " + call);
        cs = conn.prepareCall(call);

        l.debug("Add parameters to callable statement");
        final List<StoredProcedureParam> outParams = addParameters(cs, params);

        l.debug("Execute callable statement");
        if (cs.execute()) {
            l.debug("Got a result set " + queryName);
            ResultSet rs = cs.getResultSet();
            JongoResultSetHandler handler = new JongoResultSetHandler(true);
            rows = handler.handle(rs);
        } else if (!outParams.isEmpty()) {
            l.debug("No result set, but we are expecting OUT values from " + queryName);
            Map<String, String> results = new HashMap<String, String>();
            for (StoredProcedureParam p : outParams) {
                results.put(p.getName(), cs.getString(p.getIndex())); // thank $deity we only return strings
            }
            rows.add(new Row(0, results));
        }
    } catch (SQLException ex) {
        l.debug(ex.getMessage());
        throw ex;
    } finally {
        try {
            if (cs != null && !cs.isClosed())
                cs.close();
        } catch (SQLException ex) {
            l.debug(ex.getMessage());
        }
        try {
            if (conn != null && !conn.isClosed())
                conn.close();
        } catch (SQLException ex) {
            l.debug(ex.getMessage());
        }
    }
    l.debug("Received " + rows.size() + " results.");
    return rows;
}

From source file:cn.gov.scciq.timer.acceptOrder.FRMDao.java

/**
 * //from w  w w . j ava  2  s  . c  om
 * @param declNo
 */
public static void rapidRelease(String declNo) {
    Connection conn = null;
    CallableStatement proc = null;
    String call = "{call Pro_RapidRelease(?)}";
    try {
        conn = DBPool.ds.getConnection();
        proc = conn.prepareCall(call);
        proc.setString(1, declNo);
        proc.execute();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        log.error("N63", e);
    } catch (Exception e) {
        log.error("N64", e);
    } finally {
        try {
            if (proc != null) {
                proc.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            log.error("N65", e);
        }
    }
}

From source file:cn.gov.scciq.timer.acceptOrder.FRMDao.java

/**
 * ???CIQ???????CIQ?/*from   w w  w. j  av  a  2  s. c  om*/
 */
public static int saveDeclInfo(String declNo) {
    int retCode = -1;
    Connection conn = null;
    CallableStatement proc = null;
    String call = "{call Pro_SaveDeclInfo(?,?)}";
    try {
        conn = DBPool.ds.getConnection();
        proc = conn.prepareCall(call);
        proc.setString(1, declNo);
        proc.registerOutParameter(2, Types.INTEGER);
        proc.execute();
        retCode = proc.getInt(2);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        log.error("N48", e);
    } catch (Exception e) {
        log.error("N49", e);
    } finally {
        try {
            if (proc != null) {
                proc.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            log.error("N50", e);
        }
    }
    return retCode;
}

From source file:cn.gov.scciq.timer.acceptOrder.FRMDao.java

/**
 * ??/*  www  . ja  v a 2s . c  o m*/
 */
public static int declFlagAction(String declNo) {
    int retCode = -1;
    Connection conn = null;
    CallableStatement proc = null;
    String call = "{call Pro_DeclFlagAction(?,?)}";
    try {
        conn = DBPool.ds.getConnection();
        proc = conn.prepareCall(call);
        proc.setString(1, declNo);
        proc.registerOutParameter(2, Types.INTEGER);
        proc.execute();
        retCode = proc.getInt(2);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        log.error("N33", e);
    } catch (Exception e) {
        log.error("N34", e);
    } finally {
        try {
            if (proc != null) {
                proc.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            log.error("N35", e);
        }
    }
    return retCode;
}

From source file:cn.gov.scciq.timer.acceptOrder.FRMDao.java

/**
 * ??/*from   w w w  . ja  va  2  s. c  o m*/
 */
public static int checkEntExists(String entCode) {
    int retCode = -1;
    Connection conn = null;
    CallableStatement proc = null;
    String call = "{call Pro_CheckEntExists(?,?)}";
    try {
        conn = DBPool.ds.getConnection();
        proc = conn.prepareCall(call);
        System.out.println("entcode" + entCode);
        proc.setString(1, entCode);
        proc.registerOutParameter(2, Types.INTEGER);
        proc.execute();
        retCode = proc.getInt(2);
        System.out.println("retcode" + retCode);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        log.error("N39", e);
    } catch (Exception e) {
        log.error("N40", e);
    } finally {
        try {
            if (proc != null) {
                proc.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            log.error("N41", e);
        }
    }
    return retCode;
}

From source file:cn.gov.scciq.timer.acceptOrder.FRMDao.java

/**
 * ????/*from   w ww.  ja  v  a  2  s  .  c om*/
 * @param declNo
 */
public static void saveDeclInfoAbnormal(String declNo, int flg) {
    Connection conn = null;
    CallableStatement proc = null;
    String call = "{call Pro_SaveDeclInfoAbnormal(?,?)}";
    try {
        conn = DBPool.ds.getConnection();
        proc = conn.prepareCall(call);
        proc.setString(1, declNo);
        proc.setInt(2, flg);
        proc.execute();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        log.error("N42", e);
    } catch (Exception e) {
        log.error("N43", e);
    } finally {
        try {
            if (proc != null) {
                proc.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            log.error("N44", e);
        }
    }
}