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 {
    String url = "jdbc:mysql://localhost/sampledb";
    String username = "root";
    String password = "";

    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection(url, username, password);

    String sql = "DELETE FROM users WHERE user_id = ?";
    int userId = 2;

    PreparedStatement statement = connection.prepareStatement(sql);

    statement.setInt(1, userId);/*  www . j  a  v  a 2 s  .co m*/

    int rows = statement.executeUpdate();

    System.out.println(rows + " record(s) deleted.");
    connection.close();
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);// w  w w .  j a va2s .  c  om

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);
    String sql = "INSERT INTO mysql_all_table (col_binarystream) VALUES(?)";
    PreparedStatement pstmt = connection.prepareStatement(sql);

    byte[] buffer = "some data".getBytes();
    pstmt.setBytes(1, buffer);
    pstmt.executeUpdate();
    pstmt.close();

    Statement stmt = connection.createStatement();
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM mysql_all_table");
    while (resultSet.next()) {
        byte[] bytes = resultSet.getBytes("col_binarystream");
    }
}

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");

    String sql = "SELECT * FROM product WHERE year_made = ?";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setInt(1, 2002);/*w w  w.  j  a  va 2s.  com*/
    ResultSet rs1 = prest.executeQuery();
    while (rs1.next()) {
        String mov_name = rs1.getString(1);
        int mad_year = rs1.getInt(2);
        System.out.println(mov_name + "\t- " + mad_year);
    }
    prest.setInt(1, 2003);
    ResultSet rs2 = prest.executeQuery();
    while (rs2.next()) {
        String mov_name = rs2.getString(1);
        int mad_year = rs2.getInt(2);
        System.out.println(mov_name + "\t- " + mad_year);
    }
}

From source file:DemoPreparedStatementSetURL.java

public static void main(String[] args) throws Exception {
    String id = "0001";
    String urlValue = "http://www.java2s.com";
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {//w  w  w. jav  a 2  s .co m
        conn = getConnection();
        String query = "insert into url_table(id, url) values(?, ?)";
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, id);
        pstmt.setURL(2, new java.net.URL(urlValue));
        // 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 {
    try {//from   w  w w  .  j  a  v a 2  s . c  o  m
        String url = "jdbc:odbc:databaseName";
        String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        String user = "guest";
        String password = "guest";
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        String changeLastName = "UPDATE authors SET lastname = ? WHERE authid = ?";
        PreparedStatement updateLastName = connection.prepareStatement(changeLastName);

        updateLastName.setString(1, "Martin"); // Set lastname placeholder value
        updateLastName.setInt(2, 4); // Set author ID placeholder value

        int rowsUpdated = updateLastName.executeUpdate(); // execute the update
        System.out.println("Rows affected: " + rowsUpdated);
        connection.close();
    } catch (ClassNotFoundException cnfe) {
        System.err.println(cnfe);
    } catch (SQLException sqle) {
        System.err.println(sqle);
    }
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    String url = "jdbc:mysql://localhost:3306/";
    String dbName = "javatutorial";
    String userName = "root";
    String password = "root";

    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection(url + dbName, userName, password);
    File imgfile = new File("images.jpg");
    FileInputStream fin = new FileInputStream(imgfile);
    PreparedStatement pre = con.prepareStatement("insert into Image values(?,?,?)");
    pre.setInt(1, 5);/*  w  ww.  j  a v a  2 s . c o m*/
    pre.setString(2, "A");
    pre.setBinaryStream(3, fin, (int) imgfile.length());
    pre.executeUpdate();
    pre.close();
    con.close();
}

From source file:DemoPreparedStatementSetString.java

public static void main(String[] args) throws Exception {
    String stringValue = "stringValueToBeInserted";
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {// w w  w  .j  a  va  2 s.  c o m
        conn = getConnection();
        String query = "insert into string_table(string_column) values(?)";
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, stringValue);
        // 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 {
    Connection conn = getConnection();
    Statement st = conn.createStatement();

    st.executeUpdate("create table survey (Id int, b CLOB);");

    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO survey VALUES(1,?)");

    File file = new File("c:/Java_Dev/data.txt");

    FileReader reader = new FileReader(file);
    pstmt.setCharacterStream(1, reader);

    pstmt.execute();/*w  w w.  ja  v  a2s.  c o  m*/

    ResultSet resultSet = pstmt.executeQuery("select b from survey ");

    File data = new File("C:\\a.txt");
    Reader dataReader = resultSet.getCharacterStream(1);
    FileWriter writer = new FileWriter(data);
    char[] buffer = new char[1];
    while (dataReader.read(buffer) > 0) {
        writer.write(buffer);
    }
    writer.close();

    reader.close();
    st.close();
    conn.close();
}

From source file:UpdateRecordsUsingPreparedStatement.java

public static void main(String[] args) throws Exception {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {//from   w  w  w. j a v a 2 s  .  co m
        conn = getConnection();
        String query = "update dept set DEPT_LOC = ? where DEPT_NUM = ? ";
        pstmt = conn.prepareStatement(query); // create a statement
        pstmt.setString(1, "deptLocation"); // set input parameter 1
        pstmt.setInt(2, 1001); // set input parameter 2
        pstmt.executeUpdate(); // execute update statement
    } catch (Exception e) {
        e.printStackTrace();
        System.exit(1);
    } finally {
        pstmt.close();
        conn.close();
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {//from  w  w w .jav a2 s. c  o  m
        conn = getConnection();
        String query = "insert into message values(?, ?, ?)";

        pstmt = conn.prepareStatement(query); // create a statement
        pstmt.setInt(1, 5); // set input parameter 1
        pstmt.setString(2, "head5"); // set input parameter 2
        pstmt.setString(3, "data5"); // set input parameter 3
        pstmt.executeUpdate(); // execute insert statement
        pstmt = conn.prepareStatement("insert into msgtag values(?, ?, ?)"); // create a statement
        pstmt.setInt(1, 55); // set input parameter 1
        pstmt.setString(2, "tag5"); // set input parameter 2
        pstmt.setInt(3, 5); // set input parameter 3
        pstmt.executeUpdate(); // execute insert statement
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        pstmt.close();
        conn.close();
    }
}