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

public static void main(String[] args) throws Exception, IOException, SQLException {
    Class.forName("org.gjt.mm.mysql.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/databaseName", "root", "root");
    String INSERT_PICTURE = "insert into MyPictures(id, name, photo) values (?, ?, ?)";

    FileInputStream fis = null;// ww  w.j a v  a 2s. c  o m
    PreparedStatement ps = null;
    try {
        conn.setAutoCommit(false);
        File file = new File("myPhoto.png");
        fis = new FileInputStream(file);
        ps = conn.prepareStatement(INSERT_PICTURE);
        ps.setString(1, "001");
        ps.setString(2, "name");
        ps.setBinaryStream(3, fis, (int) file.length());
        ps.executeUpdate();
        conn.commit();
    } finally {
        ps.close();
        fis.close();
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    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')");

    String query = "select * from survey where id > ? and name = ?";
    PreparedStatement pstmt = conn.prepareStatement(query);
    ParameterMetaData 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  .  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);
            String paramTypeName = paramMetaData.getParameterTypeName(param);
            System.out.println("param SQL type name=" + paramTypeName);
        }
    }

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

}

From source file:examples.KafkaStreamsDemo.java

public static void main(String[] args) throws InterruptedException, SQLException {
    /**/*  w  w w.j  a v a2s.c  o m*/
     * The example assumes the following SQL schema
     *
     *    DROP DATABASE IF EXISTS beer_sample_sql;
     *    CREATE DATABASE beer_sample_sql CHARACTER SET utf8 COLLATE utf8_general_ci;
     *    USE beer_sample_sql;
     *
     *    CREATE TABLE breweries (
     *       id VARCHAR(256) NOT NULL,
     *       name VARCHAR(256),
     *       description TEXT,
     *       country VARCHAR(256),
     *       city VARCHAR(256),
     *       state VARCHAR(256),
     *       phone VARCHAR(40),
     *       updated_at DATETIME,
     *       PRIMARY KEY (id)
     *    );
     *
     *
     *    CREATE TABLE beers (
     *       id VARCHAR(256) NOT NULL,
     *       brewery_id VARCHAR(256) NOT NULL,
     *       name VARCHAR(256),
     *       category VARCHAR(256),
     *       style VARCHAR(256),
     *       description TEXT,
     *       abv DECIMAL(10,2),
     *       ibu DECIMAL(10,2),
     *       updated_at DATETIME,
     *       PRIMARY KEY (id)
     *    );
     */
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        System.err.println("Failed to load MySQL JDBC driver");
    }
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/beer_sample_sql", "root",
            "secret");
    final PreparedStatement insertBrewery = connection.prepareStatement(
            "INSERT INTO breweries (id, name, description, country, city, state, phone, updated_at)"
                    + " VALUES (?, ?, ?, ?, ?, ?, ?, ?)" + " ON DUPLICATE KEY UPDATE"
                    + " name=VALUES(name), description=VALUES(description), country=VALUES(country),"
                    + " country=VALUES(country), city=VALUES(city), state=VALUES(state),"
                    + " phone=VALUES(phone), updated_at=VALUES(updated_at)");
    final PreparedStatement insertBeer = connection.prepareStatement(
            "INSERT INTO beers (id, brewery_id, name, description, category, style, abv, ibu, updated_at)"
                    + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)" + " ON DUPLICATE KEY UPDATE"
                    + " brewery_id=VALUES(brewery_id), name=VALUES(name), description=VALUES(description),"
                    + " category=VALUES(category), style=VALUES(style), abv=VALUES(abv),"
                    + " ibu=VALUES(ibu), updated_at=VALUES(updated_at)");

    String schemaRegistryUrl = "http://localhost:8081";

    Properties props = new Properties();
    props.put(StreamsConfig.APPLICATION_ID_CONFIG, "streams-test");
    props.put(StreamsConfig.BOOTSTRAP_SERVERS_CONFIG, "localhost:9092");
    props.put(StreamsConfig.ZOOKEEPER_CONNECT_CONFIG, "localhost:2181");
    props.put(AbstractKafkaAvroSerDeConfig.SCHEMA_REGISTRY_URL_CONFIG, schemaRegistryUrl);
    props.put(StreamsConfig.KEY_SERDE_CLASS_CONFIG, KeyAvroSerde.class);
    props.put(StreamsConfig.VALUE_SERDE_CLASS_CONFIG, ValueAvroSerde.class);

    props.put(ConsumerConfig.AUTO_OFFSET_RESET_CONFIG, "earliest");

    KStreamBuilder builder = new KStreamBuilder();

    KStream<String, GenericRecord> source = builder.stream("streaming-topic-beer-sample");

    KStream<String, JsonNode>[] documents = source.mapValues(new ValueMapper<GenericRecord, JsonNode>() {
        @Override
        public JsonNode apply(GenericRecord value) {
            ByteBuffer buf = (ByteBuffer) value.get("content");
            try {
                JsonNode doc = MAPPER.readTree(buf.array());
                return doc;
            } catch (IOException e) {
                return null;
            }
        }
    }).branch(new Predicate<String, JsonNode>() {
        @Override
        public boolean test(String key, JsonNode value) {
            return "beer".equals(value.get("type").asText()) && value.has("brewery_id") && value.has("name")
                    && value.has("description") && value.has("category") && value.has("style")
                    && value.has("abv") && value.has("ibu") && value.has("updated");
        }
    }, new Predicate<String, JsonNode>() {
        @Override
        public boolean test(String key, JsonNode value) {
            return "brewery".equals(value.get("type").asText()) && value.has("name") && value.has("description")
                    && value.has("country") && value.has("city") && value.has("state") && value.has("phone")
                    && value.has("updated");
        }
    });
    documents[0].foreach(new ForeachAction<String, JsonNode>() {
        @Override
        public void apply(String key, JsonNode value) {
            try {
                insertBeer.setString(1, key);
                insertBeer.setString(2, value.get("brewery_id").asText());
                insertBeer.setString(3, value.get("name").asText());
                insertBeer.setString(4, value.get("description").asText());
                insertBeer.setString(5, value.get("category").asText());
                insertBeer.setString(6, value.get("style").asText());
                insertBeer.setBigDecimal(7, new BigDecimal(value.get("abv").asText()));
                insertBeer.setBigDecimal(8, new BigDecimal(value.get("ibu").asText()));
                insertBeer.setDate(9, new Date(DATE_FORMAT.parse(value.get("updated").asText()).getTime()));
                insertBeer.execute();
            } catch (SQLException e) {
                System.err.println("Failed to insert record: " + key + ". " + e);
            } catch (ParseException e) {
                System.err.println("Failed to insert record: " + key + ". " + e);
            }
        }
    });
    documents[1].foreach(new ForeachAction<String, JsonNode>() {
        @Override
        public void apply(String key, JsonNode value) {
            try {
                insertBrewery.setString(1, key);
                insertBrewery.setString(2, value.get("name").asText());
                insertBrewery.setString(3, value.get("description").asText());
                insertBrewery.setString(4, value.get("country").asText());
                insertBrewery.setString(5, value.get("city").asText());
                insertBrewery.setString(6, value.get("state").asText());
                insertBrewery.setString(7, value.get("phone").asText());
                insertBrewery.setDate(8, new Date(DATE_FORMAT.parse(value.get("updated").asText()).getTime()));
                insertBrewery.execute();
            } catch (SQLException e) {
                System.err.println("Failed to insert record: " + key + ". " + e);
            } catch (ParseException e) {
                System.err.println("Failed to insert record: " + key + ". " + e);
            }
        }
    });

    final KafkaStreams streams = new KafkaStreams(builder, props);
    streams.start();
    Runtime.getRuntime().addShutdownHook(new Thread(new Runnable() {
        @Override
        public void run() {
            streams.close();
        }
    }));
}

From source file:Blobs.java

public static void main(String args[]) {
    if (args.length != 1) {
        System.err.println("Syntax: <java Blobs [driver] [url] " + "[uid] [pass] [file]");
        return;//  w ww .  java2s  .  c om
    }
    try {
        Class.forName(args[0]).newInstance();
        Connection con = DriverManager.getConnection(args[1], args[2], args[3]);
        File f = new File(args[4]);
        PreparedStatement stmt;

        if (!f.exists()) {
            // if the file does not exist
            // retrieve it from the database and write it to the named file
            ResultSet rs;

            stmt = con.prepareStatement("SELECT blobData " + "FROM BlobTest " + "WHERE fileName = ?");

            stmt.setString(1, args[0]);
            rs = stmt.executeQuery();
            if (!rs.next()) {
                System.out.println("No such file stored.");
            } else {
                Blob b = rs.getBlob(1);
                BufferedOutputStream os;

                os = new BufferedOutputStream(new FileOutputStream(f));
                os.write(b.getBytes(0, (int) b.length()), 0, (int) b.length());
                os.flush();
                os.close();
            }
        } else {
            // otherwise read it and save it to the database
            FileInputStream fis = new FileInputStream(f);
            byte[] tmp = new byte[1024];
            byte[] data = null;
            int sz, len = 0;

            while ((sz = fis.read(tmp)) != -1) {
                if (data == null) {
                    len = sz;
                    data = tmp;
                } else {
                    byte[] narr;
                    int nlen;

                    nlen = len + sz;
                    narr = new byte[nlen];
                    System.arraycopy(data, 0, narr, 0, len);
                    System.arraycopy(tmp, 0, narr, len, sz);
                    data = narr;
                    len = nlen;
                }
            }
            if (len != data.length) {
                byte[] narr = new byte[len];

                System.arraycopy(data, 0, narr, 0, len);
                data = narr;
            }
            stmt = con.prepareStatement("INSERT INTO BlobTest(fileName, " + "blobData) VALUES(?, ?)");
            stmt.setString(1, args[0]);
            stmt.setObject(2, data);
            stmt.executeUpdate();
            f.delete();
        }
        con.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:DemoPreparedStatementSetAsciiStream.java

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String query = null;//w  w  w.  j a v a 2  s . c o  m
    try {
        conn = getConnection();
        String fileName = "fileName.txt";
        File file = new File(fileName);
        int fileLength = (int) file.length();
        InputStream stream = (InputStream) new FileInputStream(file);

        query = "insert into  LONG_VARCHAR_TABLE(id, stream) values(?, ?)";
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, fileName);
        pstmt.setAsciiStream(2, stream, fileLength);

        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:SetSavepoint.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";

    try {//from   www.j  a  v  a2  s  .c  o m

        Class.forName("myDriver.className");

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

    try {

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

        String query = "SELECT COF_NAME, PRICE FROM COFFEES " + "WHERE TOTAL > ?";
        String update = "UPDATE COFFEES SET PRICE = ? " + "WHERE COF_NAME = ?";

        PreparedStatement getPrice = con.prepareStatement(query);
        PreparedStatement updatePrice = con.prepareStatement(update);

        getPrice.setInt(1, 7000);
        ResultSet rs = getPrice.executeQuery();

        Savepoint save1 = con.setSavepoint();

        while (rs.next()) {
            String cof = rs.getString("COF_NAME");
            float oldPrice = rs.getFloat("PRICE");
            float newPrice = oldPrice + (oldPrice * .05f);
            updatePrice.setFloat(1, newPrice);
            updatePrice.setString(2, cof);
            updatePrice.executeUpdate();
            System.out.println("New price of " + cof + " is " + newPrice);
            if (newPrice > 11.99) {
                con.rollback(save1);
            }

        }

        getPrice = con.prepareStatement(query);
        updatePrice = con.prepareStatement(update);

        getPrice.setInt(1, 8000);

        rs = getPrice.executeQuery();
        System.out.println();

        Savepoint save2 = con.setSavepoint();

        while (rs.next()) {
            String cof = rs.getString("COF_NAME");
            float oldPrice = rs.getFloat("PRICE");
            float newPrice = oldPrice + (oldPrice * .05f);
            updatePrice.setFloat(1, newPrice);
            updatePrice.setString(2, cof);
            updatePrice.executeUpdate();
            System.out.println("New price of " + cof + " is " + newPrice);
            if (newPrice > 11.99) {
                con.rollback(save2);
            }
        }

        con.commit();

        Statement stmt = con.createStatement();
        rs = stmt.executeQuery("SELECT COF_NAME, " + "PRICE FROM COFFEES");

        System.out.println();
        while (rs.next()) {
            String name = rs.getString("COF_NAME");
            float price = rs.getFloat("PRICE");
            System.out.println("Current price of " + name + " is " + price);
        }

        con.close();

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

}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = null;
    PreparedStatement pstmt = null;
    java.sql.Array sqlArray = null;
    conn = getOracleConnection();//from www .  j a v a 2s .  c  o  m
    //  ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("CHAR_ARRAY", conn);
    String[] content = { "v1", "v2", "v3", "v4" };
    // sqlArray = new oracle.sql.ARRAY(arrayDescriptor, conn, content);

    String query = "insert into CHAR_ARRAY_TABLE(id, array) values(?, ?)";

    pstmt = conn.prepareStatement(query);
    pstmt.setString(1, "0001");
    pstmt.setArray(2, sqlArray);

    int rowCount = pstmt.executeUpdate();
    System.out.println("rowCount=" + rowCount);
    System.out.println("--Demo_PreparedStatement_SetArray end--");
    pstmt.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;

    String createTableSQL = "CREATE TABLE Person(" + "USER_ID NUMBER(5) NOT NULL, "
            + "USERNAME VARCHAR(20) NOT NULL, " + "CREATED_BY VARCHAR(20) NOT NULL, "
            + "CREATED_DATE DATE NOT NULL, " + "PRIMARY KEY (USER_ID) " + ")";

    Class.forName(DB_DRIVER);/*from w  ww . j  a v a2s .co m*/
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);

    preparedStatement = dbConnection.prepareStatement(createTableSQL);

    System.out.println(createTableSQL);

    preparedStatement.executeUpdate();

    preparedStatement.close();
    dbConnection.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')");

    String query = "select * from survey where id > ? and name = ?";
    PreparedStatement pstmt = conn.prepareStatement(query);
    ParameterMetaData paramMetaData = pstmt.getParameterMetaData();
    if (paramMetaData == null) {
        System.out.println("db vendor does NOT support ParameterMetaData");
    } else {// w w  w.  j  ava 2s. c  o m
        System.out.println("db vendor supports ParameterMetaData");
        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);
            System.out.println(paramMetaData.getPrecision(param));
        }
    }

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

    String query = "select * from survey where id > ? and name = ?";
    PreparedStatement pstmt = conn.prepareStatement(query);
    ParameterMetaData paramMetaData = pstmt.getParameterMetaData();
    if (paramMetaData == null) {
        System.out.println("db vendor does NOT support ParameterMetaData");
    } else {//from w  ww  .j  a  va2 s. c o  m
        System.out.println("db vendor supports ParameterMetaData");
        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);
            System.out.println(paramMetaData.getParameterClassName(param));
        }
    }

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

}