Example usage for java.sql Connection prepareStatement

List of usage examples for java.sql Connection prepareStatement

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

Introduction

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

Prototype

PreparedStatement prepareStatement(String sql) throws SQLException;

Source Link

Document

Creates a PreparedStatement object for sending parameterized SQL statements to the database.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("create view surveyView as (select * from survey);");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    PreparedStatement pstmt = null;
    ParameterMetaData paramMetaData = null;
    String query = "select * from survey where id > ? and name = ?";
    pstmt = conn.prepareStatement(query);
    paramMetaData = pstmt.getParameterMetaData();
    if (paramMetaData == null) {
        System.out.println("db vendor does NOT support ParameterMetaData");
    } else {/*from  w w w.  j  av a  2 s . co m*/
        System.out.println("db vendor supports ParameterMetaData");
        // find out the number of dynamic parameters
        int paramCount = paramMetaData.getParameterCount();
        System.out.println("paramCount=" + paramCount);
        System.out.println("-------------------");
        for (int param = 1; param <= paramCount; param++) {
            System.out.println("param number=" + param);
            int sqlTypeCode = paramMetaData.getParameterType(param);
            System.out.println("param SQL type code=" + sqlTypeCode);

        }
    }

    pstmt.close();
    conn.close();

}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("create view surveyView as (select * from survey);");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    PreparedStatement pstmt = null;
    ParameterMetaData paramMetaData = null;
    String query = "select * from survey where id > ? and name = ?";
    pstmt = conn.prepareStatement(query);
    paramMetaData = pstmt.getParameterMetaData();
    if (paramMetaData == null) {
        System.out.println("db vendor does NOT support ParameterMetaData");
    } else {/* w ww  .  ja v a 2  s .c o  m*/
        System.out.println("db vendor supports ParameterMetaData");
        // find out the number of dynamic parameters
        int paramCount = paramMetaData.getParameterCount();
        System.out.println("paramCount=" + paramCount);
        System.out.println("-------------------");
        for (int param = 1; param <= paramCount; param++) {
            System.out.println("param number=" + param);
            int nullable = paramMetaData.isNullable(param);
            if (nullable == ParameterMetaData.parameterNoNulls) {
                System.out.println("parameter will not allow NULL values.");
            } else if (nullable == ParameterMetaData.parameterNullable) {
                System.out.println("parameter will allow NULL values.");
            } else {
                System.out.println("nullability of a parameter is unknown.");
            }
        }
    }

    pstmt.close();
    conn.close();

}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("create view surveyView as (select * from survey);");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    PreparedStatement pstmt = null;
    ParameterMetaData paramMetaData = null;
    String query = "select * from survey where id > ? and name = ?";
    pstmt = conn.prepareStatement(query);
    paramMetaData = pstmt.getParameterMetaData();
    if (paramMetaData == null) {
        System.out.println("db vendor does NOT support ParameterMetaData");
    } else {/*from   w  w  w . j  a v a2s  .  co m*/
        System.out.println("db vendor supports ParameterMetaData");
        // find out the number of dynamic parameters
        int paramCount = paramMetaData.getParameterCount();
        System.out.println("paramCount=" + paramCount);
        System.out.println("-------------------");
        for (int param = 1; param <= paramCount; param++) {
            System.out.println("param number=" + param);
            int paramMode = paramMetaData.getParameterMode(param);
            System.out.println("param mode=" + paramMode);
            if (paramMode == ParameterMetaData.parameterModeOut) {
                System.out.println("the parameter's mode is OUT.");
            } else if (paramMode == ParameterMetaData.parameterModeIn) {
                System.out.println("the parameter's mode is IN.");
            } else if (paramMode == ParameterMetaData.parameterModeInOut) {
                System.out.println("the parameter's mode is INOUT.");
            } else {
                System.out.println("the mode of a parameter is unknown.");
            }
        }
    }

    pstmt.close();
    conn.close();

}

From source file:GetParamMetaData.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";

    Connection con;
    PreparedStatement pstmt;/*from www  .  j a v  a 2  s.c  o  m*/
    ParameterMetaData pmd;

    String sql = "UPDATE COFFEES SET SALES = ? " + "WHERE COF_NAME = ?";

    try {

        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        pstmt = con.prepareStatement(sql);

        pmd = pstmt.getParameterMetaData();

        int totalDigits = pmd.getPrecision(1);
        int digitsAfterDecimal = pmd.getScale(1);
        boolean b = pmd.isSigned(1);
        System.out.println("The first parameter ");
        System.out.println("    has precision " + totalDigits);
        System.out.println("    has scale " + digitsAfterDecimal);
        System.out.println("    may be a signed number " + b);

        int count = pmd.getParameterCount();
        System.out.println("count is " + count);

        for (int i = 1; i <= count; i++) {
            int type = pmd.getParameterType(i);
            String typeName = pmd.getParameterTypeName(i);
            System.out.println("Parameter " + i + ":");
            System.out.println("    type is " + type);
            System.out.println("    type name is " + typeName);
        }

        pstmt.close();
        con.close();

    } catch (Exception e) {
        e.printStackTrace();

    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getOracleConnection();
    String[] columnNames = { "id", "name", "content", "date_created" };
    Object[] inputValues = new Object[columnNames.length];
    inputValues[0] = new java.math.BigDecimal(100);
    inputValues[1] = new String("String Value");
    inputValues[2] = new String("This is my resume.");
    inputValues[3] = new Timestamp((new java.util.Date()).getTime());

    String insert = "insert into resume (id, name, content, date_created ) values(?, ?, ?, ?)";
    PreparedStatement pstmt = conn.prepareStatement(insert);

    pstmt.setObject(1, inputValues[0]);/*from  ww w  . jav a  2  s. c o  m*/
    pstmt.setObject(2, inputValues[1]);
    pstmt.setObject(3, inputValues[2]);
    pstmt.setObject(4, inputValues[3]);

    pstmt.executeUpdate();
    String query = "select id, name, content, date_created from resume where id=?";

    PreparedStatement pstmt2 = conn.prepareStatement(query);
    pstmt2.setObject(1, inputValues[0]);
    ResultSet rs = pstmt2.executeQuery();
    Object[] outputValues = new Object[columnNames.length];
    if (rs.next()) {
        for (int i = 0; i < columnNames.length; i++) {
            outputValues[i] = rs.getObject(i + 1);
        }
    }
    System.out.println("id=" + ((java.math.BigDecimal) outputValues[0]).toString());
    System.out.println("name=" + ((String) outputValues[1]));
    System.out.println("content=" + ((Clob) outputValues[2]));
    System.out.println("date_created=" + ((java.sql.Date) outputValues[3]).toString());

    rs.close();
    pstmt.close();
    pstmt2.close();
    conn.close();

}

From source file:driver.ieSystem2.java

public static void main(String[] args) throws SQLException {

    Scanner sc = new Scanner(System.in);
    boolean login = false;
    int check = 0;
    int id = 0;/*ww  w .j  a  va 2s  .co  m*/
    String user = "";
    String pass = "";
    Person person = null;
    Date day = null;

    JOptionPane.showMessageDialog(null, "WELCOME TO SYSTEM", "Starting Project",
            JOptionPane.INFORMATION_MESSAGE);
    do {
        System.out.println("What do you want?");
        System.out.println("press 1 : Login");
        System.out.println("press 2 : Create New User");
        System.out.println("Press 3 : Exit ");
        System.out.println("");
        do {
            try {
                System.out.print("Enter: ");
                check = sc.nextInt();
            } catch (InputMismatchException e) {
                JOptionPane.showMessageDialog(null, "Invalid Input", "Message", JOptionPane.WARNING_MESSAGE);
                sc.next();
            }
        } while (check <= 1 && check >= 3);

        // EXIT 
        if (check == 3) {
            System.out.println("Close Application");
            System.exit(0);
        }
        // CREATE USER
        if (check == 2) {
            System.out.println("-----------------------------------------");
            System.out.println("Create New User");
            System.out.println("-----------------------------------------");
            System.out.print("Account ID: ");
            id = sc.nextInt();
            System.out.print("Username: ");
            user = sc.next();
            System.out.print("Password: ");
            pass = sc.next();
            try {
                Person.createUser(id, user, pass, 0, 0, 0, 0, 0);
                System.out.println("-----------------------------------------");
                System.out.println("Create Complete");
                System.out.println("-----------------------------------------");
            } catch (Exception e) {
                System.out.println("-----------------------------------------");
                System.out.println("Error, Try again");
                System.out.println("-----------------------------------------");
            }
        } else if (check == 1) {
            // LOGIN
            do {
                System.out.println("-----------------------------------------");
                System.out.println("LOGIN ");
                System.out.print("Username: ");
                user = sc.next();
                System.out.print("Password: ");
                pass = sc.next();
                if (Person.checkUser(user, pass)) {
                    System.out.println("-----------------------------------------");
                    System.out.println("Login Complete");
                } else {
                    System.out.println("-----------------------------------------");
                    System.out.println("Invalid Username / Password");
                }
            } while (!Person.checkUser(user, pass));
        }
    } while (check != 1);
    login = true;

    person = new Person(user);
    do {
        System.out.println("-----------------------------------------");
        System.out.println("Hi " + person.getPerName());
        System.out.println("Press 1 : Add Income");
        System.out.println("Press 2 : Add Expense");
        System.out.println("Press 3 : Add Save");
        System.out.println("Press 4 : History");
        System.out.println("Press 5 : Search");
        System.out.println("Press 6 : Analytics");
        System.out.println("Press 7 : Total");
        System.out.println("Press 8 : All Summary");
        System.out.println("Press 9 : Sign Out");
        do {
            try {
                System.out.print("Enter : ");
                check = sc.nextInt();
            } catch (InputMismatchException e) {
                System.out.println("Invalid Input");
                sc.next();
            }
        } while (check <= 1 && check >= 5);
        // Add Income
        if (check == 1) {
            double Income;
            String catalog = "";
            double IncomeTotal = 0;
            catalog = JOptionPane.showInputDialog("What is your income : ");
            Income = Integer.parseInt(JOptionPane.showInputDialog("How much is it   "));

            person.addIncome(person.getPerId(), day, catalog, Income);
            person.update();
        } //Add Expense
        else if (check == 2) {
            double Expense;
            String catalog = "";
            catalog = JOptionPane.showInputDialog("What is your expense :");
            Expense = Integer.parseInt(JOptionPane.showInputDialog("How much is it   "));
            person.addExpense(person.getPerId(), day, catalog, Expense);
            person.update();
        } //Add Save 
        else if (check == 3) {
            double Saving;
            double SavingTotal = 0;
            String catalog = "";

            Saving = Integer.parseInt(JOptionPane.showInputDialog("How much is it "));
            SavingTotal += Saving;
            person.addSave(person.getPerId(), day, catalog, Saving);
            person.update();
        } //History
        else if (check == 4) {
            String x;
            do {
                System.out.println("-----------------------------------------");
                System.out.println("YOUR HISTORY");
                System.out.println("Date                Type           Amount");
                System.out.println("-----------------------------------------");
                List<History> history = person.getHistory();
                if (history != null) {
                    int count = 1;
                    for (History h : history) {
                        if (count++ <= 1) {
                            System.out.println(h.getHistoryDateTime() + "   " + h.getHistoryDescription()
                                    + "           " + h.getAmount());
                        } else {
                            System.out.println(h.getHistoryDateTime() + "   " + h.getHistoryDescription()
                                    + "           " + h.getAmount());
                        }
                    }
                }
                System.out.println("-----------------------------------------");
                System.out.print("Back to menu (0 or back) : ");
                x = sc.next();
            } while (!x.equalsIgnoreCase("back") && !x.equalsIgnoreCase("0"));
        } //Searh
        else if (check == 5) {
            try {
                Connection conn = ConnectionDB.getConnection();
                long a = person.getPerId();
                String NAME = "Salary";
                PreparedStatement ps = conn.prepareStatement(
                        "SELECT * FROM  INCOME WHERE PERID = " + a + "  and CATALOG LIKE '%" + NAME + "%' ");
                ResultSet rec = ps.executeQuery();

                while ((rec != null) && (rec.next())) {
                    System.out.print(rec.getDate("Days"));
                    System.out.print(" - ");
                    System.out.print(rec.getString("CATALOG"));
                    System.out.print(" - ");
                    System.out.print(rec.getDouble("AMOUNT"));
                    System.out.print(" - ");
                }
                ps.close();
                conn.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        } //Analy 
        else if (check == 6) {
            String x;
            do {
                DecimalFormat df = new DecimalFormat("##.##");
                double in = person.getIncome();
                double ex = person.getExpense();
                double sum = person.getSumin_ex();
                System.out.println("-----------------------------------------");
                System.out.println("Income : " + df.format((in / sum) * 100) + "%");
                System.out.println("Expense : " + df.format((ex / sum) * 100) + "%");
                System.out.println("\n\n");
                System.out.print("Back to menu (0 or back) : ");
                x = sc.next();
            } while (!x.equalsIgnoreCase("back") && !x.equalsIgnoreCase("0"));
        } //TOTAL
        else if (check == 7) {
            String x;
            do {
                System.out.println("-----------------------------------------");
                System.out.println("TOTALSAVE             TOTAL");
                System.out.println(
                        person.getTotalSave() + " Baht" + "             " + person.getTotal() + " Baht");
                System.out.println("\n\n");
                System.out.print("Back to menu (0 or back) : ");
                x = sc.next();
            } while (!x.equalsIgnoreCase("back") && !x.equalsIgnoreCase("0"));
        } //ALL Summy
        else if (check == 8) {
            String x;
            do {
                DecimalFormat df = new DecimalFormat("##.##");
                double in = person.getIncome();
                double ex = person.getExpense();
                double sum = person.getSumin_ex();
                double a = ((in / sum) * 100);
                double b = ((ex / sum) * 100);
                System.out.println("-----------------------------------------");
                System.out.println("ALL SUMMARY");
                System.out.println("Account: " + person.getPerName());
                System.out.println("");
                System.out.println("Total Save ------------- Total");
                System.out
                        .println(person.getTotalSave() + " Baht               " + person.getTotal() + " Baht");
                System.out.println("");

                System.out.println("INCOME --------------- EXPENSE");
                System.out.println(df.format(a) + "%" + "                  " + df.format(b) + "%");

                System.out.println("-----------------------------------------");
                System.out.println("\n\n");
                System.out.print("Back to menu (0 or back) : ");
                x = sc.next();
            } while (!x.equalsIgnoreCase("back") && !x.equalsIgnoreCase("0"));
        } //LOG OUT 
        else {
            System.out.println("See ya.\n");
            login = false;
            break;
        }
    } while (true);
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("create view surveyView as (select * from survey);");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");
    st.executeUpdate("insert into survey (id,name ) values (2,'anotherValue')");

    ResultSet rs = null;/*from www  . j  a  va2 s .c o  m*/
    PreparedStatement ps = null;

    String query = "select id, name from survey where id = ?";

    ps = conn.prepareStatement(query);
    // specify values for all input parameters
    ps.setInt(1, 001); // set the first parameter: id

    // now, PreparedStatement object is ready to be executed.
    rs = ps.executeQuery();
    // iterate the result set object
    while (rs.next()) {
        int id = rs.getInt(1);
        String name = rs.getString(2);
        System.out.println("[id=" + id + "][name=" + name + "]");
    }

    // NOTE: you may use PreparedStatement as many times as you want
    // here we use it for another set of parameters:
    ps.setInt(1, 002); // set the first parameter: id

    // now, PreparedStatement object is ready to be executed.
    rs = ps.executeQuery();
    // iterate the result set object
    while (rs.next()) {
        int id = rs.getInt(1);
        String name = rs.getString(2);
        System.out.println("[id=" + id + "][name=" + name + "]");
    }
    rs.close();
    ps.close();
    conn.close();

}

From source file:DemoPreparedStatementSetBinaryStream.java

public static void main(String[] args) throws Exception {
    String smallFileName = "smallFileName.dat";
    String largeFileName = "largeFileName.dat";
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {// w  ww.j a v a  2  s  . c  o m
        conn = getConnection();
        File smallFile = new File(smallFileName);
        int smallFileLength = (int) smallFile.length();
        InputStream smallStream = (InputStream) new FileInputStream(smallFile);
        File largeFile = new File(largeFileName);
        int largeFileLength = (int) largeFile.length();
        InputStream largeStream = (InputStream) new FileInputStream(largeFile);

        String query = "insert into binary_table(id, raw_column, long_raw_column) values(?, ?, ?)";

        conn.setAutoCommit(false);
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, "0001");
        pstmt.setBinaryStream(2, smallStream, smallFileLength);
        pstmt.setBinaryStream(3, largeStream, largeFileLength);

        // execute query, and return number of rows created
        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
        conn.commit();
    } finally {
        pstmt.close();
        conn.close();
    }
}

From source file:MainClass.java

public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "yourName", "mypwd");
    Statement stmt = conn.createStatement();

    String streamingDataSql = "CREATE TABLE XML_Data (id INTEGER, Data LONG)";
    try {/*from  w ww  . j av  a2s .c om*/
        stmt.executeUpdate("DROP TABLE XML_Data");
    } catch (SQLException se) {
        if (se.getErrorCode() == 942)
            System.out.println("Error dropping XML_Data table:" + se.getMessage());
    }
    stmt.executeUpdate(streamingDataSql);

    File f = new File("employee.xml");
    long fileLength = f.length();
    FileInputStream fis = new FileInputStream(f);
    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO XML_Data VALUES (?,?)");
    pstmt.setInt(1, 100);
    pstmt.setAsciiStream(2, fis, (int) fileLength);
    pstmt.execute();
    fis.close();
    ResultSet rset = stmt.executeQuery("SELECT Data FROM XML_Data WHERE id=100");
    if (rset.next()) {
        InputStream xmlInputStream = rset.getAsciiStream(1);
        int c;
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        while ((c = xmlInputStream.read()) != -1)
            bos.write(c);
        System.out.println(bos.toString());
    }
    conn.close();
}

From source file:DemoPreparedStatementSetCharacterStream.java

public static void main(String[] args) throws Exception {
    String fileName = "charDataFile.txt";
    Reader fileReader = null;/*  w ww. ja  v a2  s.  c  o  m*/
    long fileLength = 0;
    Connection conn = null;
    PreparedStatement pstmt = null;

    try {
        File file = new File(fileName);
        fileLength = file.length();
        fileReader = (Reader) new BufferedReader(new FileReader(file));
        System.out.println("fileName=" + fileName);
        System.out.println("fileLength=" + fileLength);

        conn = getConnection();
        // begin transaction
        conn.setAutoCommit(false);
        // prepare SQL query for inserting a new row using SetCharacterStream()
        String query = "insert into char_stream_table (id, char_stream_column) values(?, ?)";
        // create PrepareStatement object
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, fileName);
        pstmt.setCharacterStream(2, fileReader, (int) fileLength);
        // execute query, and return number of rows created
        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
        // end transaction
        conn.commit();
    } finally {
        pstmt.close();
        conn.close();
    }
}