Example usage for java.sql PreparedStatement execute

List of usage examples for java.sql PreparedStatement execute

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. execute

Introduction

In this page you can find the example usage for java.sql PreparedStatement execute.

Prototype

boolean execute() throws SQLException;

Source Link

Document

Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.

Usage

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);// ww  w .  j  a va  2s. co m

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

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

    Class.forName("COM.cloudscape.core.JDBCDriver").newInstance();

    Connection conn = DriverManager.getConnection("jdbc:cloudscape:GAMETRADER");

    conn.setAutoCommit(false);/* w  w w.  j a  va 2 s. c  o m*/

    Statement s = conn.createStatement();
    s.executeUpdate("CREATE TABLE XMLData(GAMEID INT, MANUAL SERIALIZE(org.w3c.dom.Document))");
    conn.commit();

    File file = new File("XMLData.xml");
    InputStream is = new FileInputStream(file);

    PreparedStatement ps = conn.prepareStatement("INSERT INTO XMLData VALUES(?,?)");

    ps.setInt(1, 1285757);

    DOMParser parser = new DOMParser();
    parser.parse("XMLData.xml");
    Document manual = parser.getDocument();
    ps.setObject(2, manual);

    ps.execute();

    conn.commit();
}

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 ww  w  . j av a  2  s .c o 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 {
    String url = "jdbc:mysql://localhost/testdb";
    String username = "root";
    String password = "";
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = null;/* ww w.  j  a  v a  2 s .  c  o  m*/
    try {
        conn = DriverManager.getConnection(url, username, password);
        conn.setAutoCommit(false);

        Statement st = conn.createStatement();
        st.execute("INSERT INTO orders (username, order_date) VALUES ('java', '2007-12-13')",
                Statement.RETURN_GENERATED_KEYS);

        ResultSet keys = st.getGeneratedKeys();
        int id = 1;
        while (keys.next()) {
            id = keys.getInt(1);
        }
        PreparedStatement pst = conn.prepareStatement(
                "INSERT INTO order_details (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)");
        pst.setInt(1, id);
        pst.setString(2, "1");
        pst.setInt(3, 10);
        pst.setDouble(4, 100);
        pst.execute();

        conn.commit();
        System.out.println("Transaction commit...");
    } catch (SQLException e) {
        if (conn != null) {
            conn.rollback();
            System.out.println("Connection rollback...");
        }
        e.printStackTrace();
    } finally {
        if (conn != null && !conn.isClosed()) {
            conn.close();
        }
    }
}

From source file:EmployeeInit.java

public static void main(String[] args) throws Exception {
    Connection con;/*from   w  w w  .j  av a  2 s .  c  o m*/
    con = DriverManager.getConnection("jdbc:derby://localhost:1527/" + "c:\\db\\employee");

    PreparedStatement ps;
    ps = con.prepareStatement("insert into employee(name,photo) " + "values(?,?)");
    ps.setString(1, "Duke");

    Blob blob = con.createBlob();
    ImageIcon ii = new ImageIcon("duke.png");

    ObjectOutputStream oos;
    oos = new ObjectOutputStream(blob.setBinaryStream(1));
    oos.writeObject(ii);
    oos.close();
    ps.setBlob(2, blob);
    ps.execute();
    blob.free();
    ps.close();
}

From source file:Main.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();

    createBlobClobTables(stmt);//from w  ww  .  j  a  v  a 2  s  . c  om

    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO BlobClob VALUES(40,?,?)");

    File file = new File("blob.txt");
    FileInputStream fis = new FileInputStream(file);
    pstmt.setBinaryStream(1, fis, (int) file.length());

    file = new File("clob.txt");
    fis = new FileInputStream(file);
    pstmt.setAsciiStream(2, fis, (int) file.length());
    fis.close();

    pstmt.execute();

    ResultSet rs = stmt.executeQuery("SELECT * FROM BlobClob WHERE id = 40");
    rs.next();

    java.sql.Blob blob = rs.getBlob(2);
    java.sql.Clob clob = rs.getClob(3);

    byte blobVal[] = new byte[(int) blob.length()];
    InputStream blobIs = blob.getBinaryStream();
    blobIs.read(blobVal);
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    bos.write(blobVal);
    blobIs.close();

    char clobVal[] = new char[(int) clob.length()];
    Reader r = clob.getCharacterStream();
    r.read(clobVal);
    StringWriter sw = new StringWriter();
    sw.write(clobVal);

    r.close();
    conn.close();
}

From source file:Main.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();

    createBlobClobTables(stmt);/*from w  w w  .  ja  v a  2s .c o m*/

    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO BlobClob VALUES(40,?,?)");

    File file = new File("blob.txt");
    FileInputStream fis = new FileInputStream(file);
    pstmt.setBinaryStream(1, fis, (int) file.length());

    file = new File("clob.txt");
    fis = new FileInputStream(file);
    pstmt.setAsciiStream(2, fis, (int) file.length());
    fis.close();

    pstmt.execute();

    ResultSet rs = stmt.executeQuery("SELECT * FROM BlobClob WHERE id = 40");
    rs.next();

    java.sql.Blob blob = rs.getBlob(2);
    java.sql.Clob clob = rs.getClob("myClobColumn");

    byte blobVal[] = new byte[(int) blob.length()];
    InputStream blobIs = blob.getBinaryStream();
    blobIs.read(blobVal);
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    bos.write(blobVal);
    blobIs.close();

    char clobVal[] = new char[(int) clob.length()];
    Reader r = clob.getCharacterStream();
    r.read(clobVal);
    StringWriter sw = new StringWriter();
    sw.write(clobVal);

    r.close();
    conn.close();
}

From source file:com.l2jserver.model.template.NPCTemplateConverter.java

public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException, JAXBException {
    controllers.put("L2Teleporter", TeleporterController.class);
    controllers.put("L2CastleTeleporter", TeleporterController.class);
    controllers.put("L2Npc", BaseNPCController.class);
    controllers.put("L2Monster", MonsterController.class);
    controllers.put("L2FlyMonster", MonsterController.class);
    Class.forName("com.mysql.jdbc.Driver");

    final File target = new File("generated/template/npc");

    System.out.println("Scaning legacy HTML files...");
    htmlScannedFiles = FileUtils.listFiles(L2J_HTML_FOLDER, new String[] { "html", "htm" }, true);

    final JAXBContext c = JAXBContext.newInstance(NPCTemplate.class, Teleports.class);

    final Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
    {/*w ww  .j av a  2 s . com*/
        System.out.println("Converting teleport templates...");
        teleportation.teleport = CollectionFactory.newList();

        final Marshaller m = c.createMarshaller();
        m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);

        final PreparedStatement st = conn.prepareStatement("SELECT * FROM teleport");
        st.execute();
        final ResultSet rs = st.getResultSet();
        while (rs.next()) {
            final TeleportationTemplate template = new TeleportationTemplate();

            template.id = new TeleportationTemplateID(rs.getInt("id"), null);
            template.name = rs.getString("Description");
            TemplateCoordinate coord = new TemplateCoordinate();
            coord.x = rs.getInt("loc_x");
            coord.y = rs.getInt("loc_y");
            coord.z = rs.getInt("loc_z");
            template.point = coord;
            template.price = rs.getInt("price");
            template.item = rs.getInt("itemId");
            if (rs.getBoolean("fornoble")) {
                template.restrictions = new Restrictions();
                template.restrictions.restriction = Arrays.asList("NOBLE");
            }
            teleportation.teleport.add(template);
        }
        m.marshal(teleportation, getXMLSerializer(new FileOutputStream(new File(target, "../teleports.xml"))));
        // System.exit(0);
    }

    System.out.println("Generating template XML files...");
    // c.generateSchema(new SchemaOutputResolver() {
    // @Override
    // public Result createOutput(String namespaceUri,
    // String suggestedFileName) throws IOException {
    // // System.out.println(new File(target, suggestedFileName));
    // // return null;
    // return new StreamResult(new File(target, suggestedFileName));
    // }
    // });

    try {
        final Marshaller m = c.createMarshaller();
        m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);

        final PreparedStatement st = conn.prepareStatement(
                "SELECT npc.*, npcskills.level AS race " + "FROM npc " + "LEFT JOIN npcskills "
                        + "ON(npc.idTemplate = npcskills.npcid AND npcskills.skillid = ?)");
        st.setInt(1, 4416);
        st.execute();
        final ResultSet rs = st.getResultSet();
        while (rs.next()) {
            Object[] result = fillNPC(rs);
            NPCTemplate t = (NPCTemplate) result[0];
            String type = (String) result[1];

            String folder = createFolder(type);
            if (folder.isEmpty()) {
                m.setProperty(Marshaller.JAXB_SCHEMA_LOCATION, "npc ../npc.xsd");
            } else {
                m.setProperty(Marshaller.JAXB_SCHEMA_LOCATION, "npc ../../npc.xsd");
            }

            final File file = new File(target, "npc/" + folder + "/" + t.getID().getID()
                    + (t.getInfo().getName() != null ? "-" + camelCase(t.getInfo().getName().getValue()) : "")
                    + ".xml");
            file.getParentFile().mkdirs();
            templates.add(t);

            try {
                m.marshal(t, getXMLSerializer(new FileOutputStream(file)));
            } catch (MarshalException e) {
                System.err.println("Could not generate XML template file for "
                        + t.getInfo().getName().getValue() + " - " + t.getID());
                file.delete();
            }
        }

        System.out.println("Generated " + templates.size() + " templates");

        System.gc();
        System.out.println("Free: " + FileUtils.byteCountToDisplaySize(Runtime.getRuntime().freeMemory()));
        System.out.println("Total: " + FileUtils.byteCountToDisplaySize(Runtime.getRuntime().totalMemory()));
        System.out.println("Used: " + FileUtils.byteCountToDisplaySize(
                Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory()));
        System.out.println("Max: " + FileUtils.byteCountToDisplaySize(Runtime.getRuntime().maxMemory()));
    } finally {
        conn.close();
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = null;/*from   w  w  w.j a  v  a 2 s  . c  o m*/
    PreparedStatement pstmt = null;
    Statement stmt = null;
    ResultSet rs = null;
    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    stmt = conn.createStatement();
    createXMLTable(stmt);
    File f = new File("build.xml");
    long fileLength = f.length();
    FileInputStream fis = new FileInputStream(f);
    String SQL = "INSERT INTO XML_Data VALUES (?,?)";
    pstmt = conn.prepareStatement(SQL);
    pstmt.setInt(1, 100);
    pstmt.setAsciiStream(2, fis, (int) fileLength);
    pstmt.execute();
    fis.close();
    SQL = "SELECT Data FROM XML_Data WHERE id=100";
    rs = stmt.executeQuery(SQL);
    if (rs.next()) {
        InputStream xmlInputStream = rs.getAsciiStream(1);
        int c;
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        while ((c = xmlInputStream.read()) != -1)
            bos.write(c);
        System.out.println(bos.toString());
    }
    rs.close();
    stmt.close();
    pstmt.close();
    conn.close();
}

From source file:examples.KafkaStreamsDemo.java

public static void main(String[] args) throws InterruptedException, SQLException {
    /**/*from  w ww  . 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();
        }
    }));
}