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:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getOracleConnection();
    // Step-2: identify the stored procedure
    String proc3StoredProcedure = "{ call proc3(?, ?, ?) }";
    // Step-3: prepare the callable statement
    CallableStatement cs = conn.prepareCall(proc3StoredProcedure);
    // Step-4: set input parameters ...
    // first input argument
    cs.setString(1, "abcd");
    // third input argument
    cs.setInt(3, 10);/*from w ww . jav  a 2 s .  c  o  m*/
    // Step-5: register output parameters ...
    cs.registerOutParameter(2, java.sql.Types.VARCHAR);
    cs.registerOutParameter(3, java.sql.Types.INTEGER);
    // Step-6: execute the stored procedures: proc3
    cs.execute();
    // Step-7: extract the output parameters
    // get parameter 2 as output
    String param2 = cs.getString(2);
    // get parameter 3 as output
    int param3 = cs.getInt(3);
    System.out.println("param2=" + param2);
    System.out.println("param3=" + param3);
    conn.close();
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Connection con = null;
    CallableStatement proc_stmt = null;

    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE", "USERID",
            "PASSWORD");
    proc_stmt = con.prepareCall("{ call someStoredProc() }");

    proc_stmt.executeQuery();/* ww  w  .  j av  a  2  s.c  o  m*/

}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);//ww w . java2 s.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:Main.java

public static void main(String[] args) throws Exception {
    // DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system",
            "manager");
    String plsql = " declare " + "    p_id varchar2(20) := null; " + "    l_rc sys_refcursor;" + " begin "
            + "    p_id := ?; " + "    ? := 'input parameter was = ' || p_id;" + "    open l_rc for "
            + "        select 1 id, 'abc' name from dual; " + "    ? := l_rc;" + " end;";

    CallableStatement cs = c.prepareCall(plsql);
    cs.setString(1, "12345");
    cs.registerOutParameter(2, Types.VARCHAR);
    //  cs.registerOutParameter(3, OracleTypes.CURSOR);

    cs.execute();//  www. j  a  v  a 2 s .  com

    System.out.println("Result = " + cs.getObject(2));

    ResultSet cursorResultSet = (ResultSet) cs.getObject(3);
    while (cursorResultSet.next()) {
        System.out.println(cursorResultSet.getInt(1) + " " + cursorResultSet.getString(2));
    }
    cs.close();
    c.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    String WRITE_OBJECT_SQL = "BEGIN " + "  INSERT INTO java_objects(object_id, object_name, object_value) "
            + "  VALUES (?, ?, empty_blob()) " + "  RETURN object_value INTO ?; " + "END;";
    String READ_OBJECT_SQL = "SELECT object_value FROM java_objects WHERE object_id = ?";

    Connection conn = getOracleConnection();
    conn.setAutoCommit(false);/*  w ww  . j a  va2 s  .c  om*/
    List<Object> list = new ArrayList<Object>();
    list.add("This is a short string.");
    list.add(new Integer(1234));
    list.add(new java.util.Date());

    // write object to Oracle
    long id = 0001;
    String className = list.getClass().getName();
    CallableStatement cstmt = conn.prepareCall(WRITE_OBJECT_SQL);

    cstmt.setLong(1, id);
    cstmt.setString(2, className);

    cstmt.registerOutParameter(3, java.sql.Types.BLOB);

    cstmt.executeUpdate();
    BLOB blob = (BLOB) cstmt.getBlob(3);
    OutputStream os = blob.getBinaryOutputStream();
    ObjectOutputStream oop = new ObjectOutputStream(os);
    oop.writeObject(list);
    oop.flush();
    oop.close();
    os.close();

    // Read object from oracle
    PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL);
    pstmt.setLong(1, id);
    ResultSet rs = pstmt.executeQuery();
    rs.next();
    InputStream is = rs.getBlob(1).getBinaryStream();
    ObjectInputStream oip = new ObjectInputStream(is);
    Object object = oip.readObject();
    className = object.getClass().getName();
    oip.close();
    is.close();
    rs.close();
    pstmt.close();
    conn.commit();

    // de-serialize list a java object from a given objectID
    List listFromDatabase = (List) object;
    System.out.println("[After De-Serialization] list=" + listFromDatabase);
    conn.close();
}

From source file:movierecommend.MovieRecommend.java

public static void main(String[] args) throws ClassNotFoundException, SQLException {
    String url = "jdbc:sqlserver://localhost;databaseName=MovieDB;integratedSecurity=true";
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    Connection conn = DriverManager.getConnection(url);

    Statement stm = conn.createStatement();
    ResultSet rsRecnik = stm.executeQuery("SELECT Recnik FROM Recnik WHERE (ID_Zanra = 1)"); //citam recnik iz baze za odredjeni zanr
    String recnik[] = null;/*from   w  ww.j av  a 2  s. c o m*/

    while (rsRecnik.next()) {
        recnik = rsRecnik.getString("Recnik").split(","); //delim recnik na reci

    }

    ResultSet rsFilmovi = stm.executeQuery(
            "SELECT TOP (200) Naziv_Filma, LemmaPlots, " + "ID_Filma FROM Film WHERE (ID_Zanra = 1)");
    List<Film> listaFilmova = new ArrayList<>();
    Film f = null;
    int rb = 0;
    while (rsFilmovi.next()) {
        f = new Film(rb, Integer.parseInt(rsFilmovi.getString("ID_Filma")), rsFilmovi.getString("Naziv_Filma"),
                rsFilmovi.getString("LemmaPlots"));
        listaFilmova.add(f);
        rb++;

    }
    //kreiranje vektorskog modela
    M = MatrixUtils.createRealMatrix(recnik.length, listaFilmova.size());
    System.out.println("Prva tezinska matrica");

    for (int i = 0; i < recnik.length; i++) {
        String recBaza = recnik[i];
        for (Film film : listaFilmova) {
            for (String lemmaRec : film.getPlotLema()) {
                if (recBaza.equals(lemmaRec)) {
                    M.setEntry(i, film.getRb(), M.getEntry(i, film.getRb()) + 1);
                }
            }
        }
    }
    //racunanje tf-idf
    System.out.println("td-idf");
    M = LSA.calculateTfIdf(M);
    System.out.println("SVD");
    //SVD
    SingularValueDecomposition svd = new SingularValueDecomposition(M);
    RealMatrix V = svd.getV();
    RealMatrix Vk = V.getSubMatrix(0, V.getRowDimension() - 1, 0, brojDimenzija - 1); //dimenzija je poslednji argument
    //kosinusna slicnost
    System.out.println("Cosin simmilarity");
    CallableStatement stmTop = conn.prepareCall("{call Dodaj_TopList(?,?,?)}");

    for (int j = 0; j < listaFilmova.size(); j++) {
        Film fl = listaFilmova.get(j);
        List<Film> lFilmova1 = new ArrayList<>();
        lFilmova1.add(listaFilmova.get(j));
        double sim = 0.0;
        for (int k = 0; k < listaFilmova.size(); k++) {
            // System.out.println(listaFilmova.size());                
            sim = LSA.cosinSim(j, k, Vk.transpose());
            listaFilmova.get(k).setSimilarity(sim);
            lFilmova1.add(listaFilmova.get(k));
        }
        Collections.sort(lFilmova1);
        for (int k = 2; k < 13; k++) {
            stmTop.setString(1, fl.getID() + "");
            stmTop.setString(2, lFilmova1.get(k).getID() + "");
            stmTop.setString(3, lFilmova1.get(k).getSimilarity() + "");
            stmTop.execute();
        }

    }

    stm.close();
    rsRecnik.close();
    rsFilmovi.close();
    conn.close();

}

From source file:Main.java

public static int storedProcWithResultSet() throws Exception {
    Connection conn = null;
    CallableStatement cs = conn.prepareCall("{? = call proc (?,?,?,?,?,?,?)}");

    // register input parameters
    cs.setString(2, "");
    cs.setString(3, "");
    cs.setString(4, "123");
    // regsiter ouput parameters
    cs.registerOutParameter(5, java.sql.Types.CHAR);
    cs.registerOutParameter(6, java.sql.Types.CHAR);
    cs.registerOutParameter(7, java.sql.Types.CHAR);

    // Procedure execution
    ResultSet rs = cs.executeQuery();

    ResultSetMetaData rsmd = rs.getMetaData();
    int nbCol = rsmd.getColumnCount();
    while (rs.next()) {
        for (int i = 1; i <= nbCol; i++) {
            System.out.println(rs.getString(i));
            System.out.println(rs.getString(i));
        }/*from  w w w . j ava  2 s .  c  o  m*/
    }
    // OUTPUT parameters
    System.out.println("return code of Stored procedure = : " + cs.getInt(1));
    for (int i = 5; i <= 7; i++)
        System.out.println("parameter " + i + " : " + cs.getString(i));
    return cs.getInt(1);
}

From source file:com.medlog.webservice.util.ToneAnalyzerExample.java

public static void processTone(DbConnection dbc, ToneAnalysis tone, int diaryID) {
    CallableStatement cs = null;/*from   w  w w.java2s  . c om*/
    String cat_id = "";
    try {
        //category , tone , sentance,score,text
        List<ToneCategory> to = tone.getDocumentTone().getTones();
        Connection conn = dbc.getConnnection();

        conn.prepareCall(new StringBuilder().append("{call spDiaryTextScoreInsert(").append(diaryID)
                .append(",?,?,?,?,?)}").toString());
        conn.setAutoCommit(false);
        cs.setInt(3, 0);
        cs.setNull(5, java.sql.Types.NVARCHAR);// cat_id);

        for (ToneCategory docTC : to) {
            cat_id = docTC.getId();
            cs.setString(1, cat_id);
            for (ToneScore s : docTC.getTones()) {
                cs.setString(2, s.getId());
                cs.setDouble(4, s.getScore());
                cs.addBatch();
            }
            int[] docRes = cs.executeBatch();
            cs.clearBatch();
            System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() result --- "
                    + ArrayUtils.toString(docRes));
        }
        System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() Process "
                + tone.getSentencesTone().size() + " sentances.");
        for (SentenceTone sentT : tone.getSentencesTone()) {
            to = sentT.getTones();
            cs.setInt(3, sentT.getId());
            cs.setString(5, toS(sentT.getText()).trim());
            for (ToneCategory docTC : to) {
                cat_id = docTC.getId();
                cs.setString(1, cat_id);
                for (ToneScore s : docTC.getTones()) {
                    cs.setString(2, s.getId());
                    cs.setDouble(4, s.getScore());
                    cs.addBatch();
                }
                int[] docRes = cs.executeBatch();
                cs.clearBatch();
                System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() result["
                        + sentT.getId() + "] " + ArrayUtils.toString(docRes));
            }
        }

    } catch (SQLException ex) {
        Logger.getLogger(ToneAnalyzerExample.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.medlog.webservice.services.tone.ToneProcessorFactory.java

private static ArrayList<Integer> processTone(DbConnection dbc, ToneAnalysis tone, int diaryID) {
    CallableStatement cs = null;/*from   w  ww  .  j a  v  a2  s  . com*/
    String cat_id = "";
    ArrayList<Integer> results = new ArrayList<Integer>();
    try {
        //category , tone , sentance,score,text
        List<com.ibm.watson.developer_cloud.tone_analyzer.v3.model.ToneCategory> to = tone.getDocumentTone()
                .getTones();
        Connection conn = dbc.getConnnection();

        cs = conn.prepareCall(new StringBuilder().append("{call spDiaryTextScoreInsert(").append(diaryID)
                .append(",?,?,?,?,?)}").toString());
        conn.setAutoCommit(false);
        cs.setInt(3, 0);
        cs.setNull(5, java.sql.Types.NVARCHAR);// cat_id);

        for (com.ibm.watson.developer_cloud.tone_analyzer.v3.model.ToneCategory docTC : to) {
            cat_id = docTC.getId();
            cs.setString(1, cat_id);
            for (ToneScore s : docTC.getTones()) {
                cs.setString(2, s.getId());
                cs.setDouble(4, s.getScore());
                cs.addBatch();
            }

        }
        System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() Process "
                + tone.getSentencesTone().size() + " sentances.");
        int[] docRes = cs.executeBatch();
        List l = Arrays.asList(docRes);
        results.addAll(l);

        cs.clearBatch();
        System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() result --- "
                + ArrayUtils.toString(docRes));
        int[] sentRes = null;
        for (SentenceTone sentT : tone.getSentencesTone()) {
            to = sentT.getTones();
            cs.setInt(3, sentT.getId());
            cs.setString(5, toS(sentT.getText()).trim());
            for (com.ibm.watson.developer_cloud.tone_analyzer.v3.model.ToneCategory docTC : to) {
                cat_id = docTC.getId();
                cs.setString(1, cat_id);

                try {
                    for (ToneScore s : docTC.getTones()) {
                        cs.setString(2, s.getId());
                        cs.setDouble(4, s.getScore());
                        cs.addBatch();
                    }
                    if (DEBUG) {
                        DbUtl.getWarningsFromStatement(cs);
                    }
                    //                        sentRes = cs.executeBatch();
                    //                        List l = Arrays.asList(sentRes);
                    //                        results.addAll(l);
                } catch (SQLException s) {
                    System.err.println(
                            "com.medlog.webservice.services.tone.ToneProcessorFactory.processTone(loop)"
                                    + DbUtl.printJDBCExceptionMsg(s));
                    s.printStackTrace();
                } catch (Exception s) {

                }

                System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() result["
                        + sentT.getId() + "] " + ArrayUtils.toString(sentRes));
            }
        }
        sentRes = cs.executeBatch();
        try {
            l = Arrays.asList(sentRes);
            results.addAll(l);
            conn.setAutoCommit(true);
            cs.clearBatch();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.setAutoCommit(true);
            } catch (Exception eeee) {
                eeee.printStackTrace();
            }
        }
    } catch (BatchUpdateException ex) {
        Logger.getLogger(ToneAnalyzerExample.class.getName()).log(Level.SEVERE, null, ex);
        System.err.println("com.medlog.webservice.services.tone.ToneProcessorFactory.processTone(batch)"
                + DbUtl.printBatchUpdateException(ex));

    } catch (SQLTimeoutException ex) {

        Logger.getLogger(ToneAnalyzerExample.class.getName()).log(Level.SEVERE, null, ex);
        ex.printStackTrace();
    } catch (SQLException ex) {
        Logger.getLogger(ToneAnalyzerExample.class.getName()).log(Level.SEVERE, null, ex);
        System.err.println("com.medlog.webservice.services.tone.ToneProcessorFactory.processTone(meth)"
                + DbUtl.printJDBCExceptionMsg(ex));

    } finally {
        DbUtl.close(cs);
    }
    return results;
}

From source file:StoredProcUtil.java

public static void addRaceEvent(String name, String location, String date) {

    if ((!check(name)) || (!check(location)) || (!check(date)))
        throw new IllegalArgumentException("Invalid param values passed to addRaceEvent()");

    Connection conn = null;

    try {/* w ww.  j a va  2 s.  c  om*/

        conn = pool.getConnection();

        if (conn == null)
            throw new SQLException("Invalid Connection in addRaceEvent method");

        CallableStatement cs = null;

        //Create an instance of the CallableStatement
        cs = conn.prepareCall("{call addEvent (?,?,?)}");

        cs.setString(1, name);
        cs.setString(2, location);
        cs.setString(3, date);

        //Call the inherited PreparedStatement.executeUpdate() method
        cs.executeUpdate();

        // return the connection to the pool
        conn.close();

    } catch (SQLException sqle) {
    }

}