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

public static void main(String[] args) {
    ResultSet rs = null;/*from w  w w.  j  ava2  s.com*/
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        conn = getConnection();
        String query = "select count(*) from tableName";
        pstmt = conn.prepareStatement(query);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            int numberOfRows = rs.getInt(1);
            System.out.println("numberOfRows= " + numberOfRows);
        } else {
            System.out.println("error: could not get the record counts");
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection conn = DriverManager
            .getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"
                    + "Dbq=C://Book1.xlsx;");

    PreparedStatement s = conn.prepareStatement("SELECT * FROM [Sheet1$] WHERE [MetricMonth] = ?");
    s.setString(1, "Jul-2013");
    s.execute();//w  ww  . j  a  v a  2  s. co m
    ResultSet rs = s.getResultSet();
    if (rs != null) {
        while (rs.next()) {
            System.out.println(rs.getInt("All"));
        }
    }
    s.close();

    conn.close();
}

From source file:DemoPreparedStatementSetBoolean.java

public static void main(String[] args) throws Exception {
    boolean booleanValue = true;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {//from   w w w.ja v a 2  s .  c o  m
        conn = getConnection();
        String query = "insert into boolean_table(id, boolean_column) values(?, ?)";
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, "0001");
        pstmt.setBoolean(2, booleanValue);
        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
    } finally {
        pstmt.close();
        conn.close();
    }
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", "root", "root");
    con.setAutoCommit(false);//from  ww w .  ja v a2 s. c o  m
    String sql = "INSERT product VALUES(?,?)";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setString(1, "A");
    prest.setInt(2, 2002);
    prest.addBatch();
    prest.setString(1, "B");
    prest.setInt(2, 1998);
    prest.addBatch();
    prest.setString(1, "C");
    prest.setInt(2, 1980);
    prest.addBatch();
    prest.setString(1, "D");
    prest.setInt(2, 1975);
    prest.addBatch();
    int count[] = prest.executeBatch();
    con.commit();
    con.close();
}

From source file:DemoPreparedStatementSetDate.java

public static void main(String[] args) throws Exception {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {//from  ww w . j a  v a  2  s  .  c  o m
        conn = getConnection();
        String query = "insert into date_table(id, date_column) values(?, ?)";
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, "0001");
        java.sql.Date date = getCurrentJavaSqlDate();
        pstmt.setDate(2, date);

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

From source file:Main.java

public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(url, username, password);
    conn.setAutoCommit(false);/*from  w  w w .  j a  va  2s  .  co  m*/

    String sql = "INSERT INTO pictures (name, description, image) VALUES (?, ?, ?)";
    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setString(1, "java.gif");
    stmt.setString(2, "Java Official Logo");

    File image = new File("D:\\a.gif");
    FileInputStream fis = new FileInputStream(image);
    stmt.setBinaryStream(3, fis, (int) image.length());
    stmt.execute();

    conn.commit();
    fis.close();
    conn.close();
}

From source file:Main.java

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

    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(url, username, password);
    conn.setAutoCommit(false);/*from  w  w  w . j av a  2  s  . c om*/

    String sql = "INSERT INTO documents (name, description, data) VALUES (?, ?, ?)";
    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setString(1, "a.txt");
    stmt.setString(2, "b");

    File data = new File("C:\\a.txt");
    FileReader reader = new FileReader(data);
    stmt.setCharacterStream(3, reader, (int) data.length());
    stmt.execute();

    conn.commit();
    reader.close();
    conn.close();

}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    int count = 0;
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", "root", "root");

    String sql = "SELECT title,year_made FROM product WHERE year_made >= ? AND year_made <= ?";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setInt(1, 2000);//from  w  w  w.j a v  a2 s.co  m
    prest.setInt(2, 2009);
    ResultSet rs = prest.executeQuery();
    while (rs.next()) {
        String mov_name = rs.getString(1);
        int mov_year = rs.getInt(2);
        count++;
        System.out.println(mov_name + "\t" + "- " + mov_year);
    }
    System.out.println("Number of records: " + count);
    prest.close();
    con.close();
}

From source file:jp.co.opentone.bsol.linkbinder.CorresponBodyUpdater.java

/**
 * @param args/*from  w w w . ja  v  a  2s  .  co m*/
 */
public static void main(String[] args) throws Exception {

    Connection con = getConnection();
    PreparedStatement stmt = null;
    boolean success = true;
    try {
        con.setAutoCommit(false);

        stmt = con.prepareStatement("UPDATE correspon SET body = ? WHERE id = ?");
        execute(stmt);

        success = true;
    } finally {
        if (success) {
            con.commit();
        } else {
            con.rollback();
        }

        if (stmt != null)
            stmt.close();
        con.close();
    }

}

From source file:InsertTextFileToOracle.java

public static void main(String[] args) throws Exception {
    String id = "001";
    String fileName = "fileName.txt";

    FileInputStream fis = null;/*from w w w.  ja va 2s.  c  o m*/
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
        conn = getConnection();
        conn.setAutoCommit(false);
        File file = new File(fileName);
        fis = new FileInputStream(file);
        pstmt = conn.prepareStatement("insert into DataFiles(id, fileName, fileBody) values (?, ?, ?)");
        pstmt.setString(1, id);
        pstmt.setString(2, fileName);
        pstmt.setAsciiStream(3, fis, (int) file.length());
        pstmt.executeUpdate();
        conn.commit();
    } catch (Exception e) {
        System.err.println("Error: " + e.getMessage());
        e.printStackTrace();
    } finally {
        pstmt.close();
        fis.close();
        conn.close();
    }
}