Example usage for java.sql Connection 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:com.netflix.genie.web.data.utils.H2Utils.java

/**
 * Split the existing command executable on any whitespace characters and insert them into the
 * {@code command_executable_arguments} table in order.
 * <p>//  ww  w .ja  v a2 s .  c  om
 * See: {@code src/main/resources/db/migration/h2/V4_0_0__Genie_4.sql} for usage
 *
 * @param con The database connection to use
 * @throws Exception On Error
 */
public static void splitV3CommandExecutableForV4(final Connection con) throws Exception {
    try (PreparedStatement commandsQuery = con.prepareStatement(V3_COMMAND_EXECUTABLE_QUERY);
            PreparedStatement insertCommandArgument = con.prepareStatement(V4_COMMAND_ARGUMENT_SQL);
            ResultSet rs = commandsQuery.executeQuery()) {
        while (rs.next()) {
            final long commandId = rs.getLong(V3_COMMAND_ID_INDEX);
            final String executable = rs.getString(V3_COMMAND_EXECUTABLE_INDEX);
            final String[] arguments = StringUtils.splitByWholeSeparator(executable, null);
            if (arguments.length > 0) {
                insertCommandArgument.setLong(V4_COMMAND_ID_INDEX, commandId);
                for (int i = 0; i < arguments.length; i++) {
                    insertCommandArgument.setString(V4_COMMAND_ARGUMENT_INDEX, arguments[i]);
                    insertCommandArgument.setInt(V4_COMMAND_ARGUMENT_ORDER_INDEX, i);
                    insertCommandArgument.executeUpdate();
                }
            }
        }
    }
}

---

From source file:Main.java

public static long writeJavaObject(Connection conn, Object object) throws Exception {
    String className = object.getClass().getName();
    PreparedStatement pstmt = conn.prepareStatement(WRITE_OBJECT_SQL);
    pstmt.setString(1, className);/*from w  w  w  .  j a v  a2  s.  c  o m*/
    pstmt.setObject(2, object);
    pstmt.executeUpdate();
    ResultSet rs = pstmt.getGeneratedKeys();
    int id = -1;
    if (rs.next()) {
        id = rs.getInt(1);
    }
    rs.close();
    pstmt.close();
    return id;
}

---

From source file:com.acme.spring.hibernate.HibernateTestHelper.java

/**
 * <p>Executes a sql script.</p>
 *
 * @param session the hibernate session//from  w  ww . j a  v a  2  s  . com
 * @param fileName     the file name
 *
 * @throws java.io.IOException if any error occurs
 */
public static void runScript(Session session, String fileName) throws IOException {

    // retrieves the resource from class path
    InputStream input = Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName);

    BufferedReader inputReader = new BufferedReader(new InputStreamReader(input));

    // loads the entire file
    StringBuilder stringBuilder = new StringBuilder();
    String line;
    while ((line = inputReader.readLine()) != null) {

        if (!line.startsWith("--")) {
            stringBuilder.append(line);
        }
    }

    // splits the commands by semicolon
    String[] commands = stringBuilder.toString().split(";");

    for (final String command : commands) {

        session.doWork(new Work() {
            public void execute(Connection connection) throws SQLException {

                connection.prepareStatement(command).execute();
            }
        });
    }
}

---

From source file:SerializeJavaObjects_MySQL.java

public static long writeJavaObject(Connection conn, Object object) throws Exception {
    String className = object.getClass().getName();
    PreparedStatement pstmt = conn.prepareStatement(WRITE_OBJECT_SQL);

    // set input parameters
    pstmt.setString(1, className);// www  .j  a va2s  .  co  m
    pstmt.setObject(2, object);
    pstmt.executeUpdate();

    // get the generated key for the id
    ResultSet rs = pstmt.getGeneratedKeys();
    int id = -1;
    if (rs.next()) {
        id = rs.getInt(1);
    }

    rs.close();
    pstmt.close();
    System.out.println("writeJavaObject: done serializing: " + className);
    return id;
}

---

From source file:Main.java

public static byte[] getBLOB(int id, Connection conn) throws Exception {
    ResultSet rs = null;//from  www. j  a v  a  2s  .  c  om
    PreparedStatement pstmt = null;
    String query = "SELECT photo FROM MyPictures WHERE id = ?";
    try {
        pstmt = conn.prepareStatement(query);
        pstmt.setInt(1, id);
        rs = pstmt.executeQuery();
        rs.next();
        Blob blob = rs.getBlob(3);
        // materialize BLOB onto client
        return blob.getBytes(1, (int) blob.length());
    } finally {
        rs.close();
        pstmt.close();
        conn.close();
    }
}

---

From source file:Main.java

public static byte[] getBLOB(int id, Connection conn) throws Exception {
    ResultSet rs = null;//from  w w w  . j  a va2  s  .c o m
    PreparedStatement pstmt = null;
    String query = "SELECT photo FROM MyPictures WHERE id = ?";
    try {
        pstmt = conn.prepareStatement(query);
        pstmt.setInt(1, id);
        rs = pstmt.executeQuery();
        rs.next();
        Blob blob = rs.getBlob("photo");
        // materialize BLOB onto client
        return blob.getBytes(1, (int) blob.length());
    } finally {
        rs.close();
        pstmt.close();
        conn.close();
    }
}

---

From source file:com.thoughtworks.go.server.datamigration.M001.java

static void perform(Connection cxn, long id, String selections, boolean isBlacklist) throws SQLException {
    try (PreparedStatement ps = cxn.prepareStatement(
            "UPDATE pipelineselections SET selections = NULL, version = ?, filters = ? WHERE id = ?")) {
        ps.setInt(1, SCHEMA);//from  ww w .ja va 2 s. com
        ps.setString(2, asJson(selections, isBlacklist));
        ps.setLong(3, id);
        ps.executeUpdate();
    }
}

---

From source file:com.l2jfree.gameserver.datatables.PetNameTable.java

public static boolean doesPetNameExist(String name, int petNpcId) {
    boolean result = true;
    Connection con = null;

    try {/*  w ww  .j  a  va2 s. co  m*/
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement = con.prepareStatement(
                "SELECT name FROM pets p, items i WHERE p.item_obj_id = i.object_id AND name=? AND i.item_id=?");
        statement.setString(1, name);
        statement.setString(2, Integer.toString(PetDataTable.getItemIdByPetId(petNpcId)));
        ResultSet rset = statement.executeQuery();
        result = rset.next();
        rset.close();
        statement.close();
    } catch (SQLException e) {
        _log.warn("could not check existing petname:" + e.getMessage(), e);
    } finally {
        L2DatabaseFactory.close(con);
    }

    return result;
}

---

From source file:com.cgdecker.guice.jdbc.Hsqldb.java

private static void setUpDatabase(DataSource dataSource) {
    Connection conn = null;
    try {//w  w w .  j  a  va 2 s .  c o m
        conn = dataSource.getConnection();
        PreparedStatement pS = conn.prepareStatement("SET DATABASE TRANSACTION CONTROL MVCC");
        pS.executeUpdate();
        pS.close();

        pS = conn.prepareStatement("DROP TABLE foo IF EXISTS");
        pS.executeUpdate();
        pS.close();

        pS = conn.prepareStatement("CREATE TABLE foo ( id INTEGER, name VARCHAR(100) )");
        pS.executeUpdate();
        pS.close();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

---

From source file:com.jmstoolkit.pipeline.plugin.XMLValueTransformerTest.java

/**
 * Starts an embedded Derby database, creates the lookup table and
 * inserts one row for the test. Also creates the source and expected
 * result XML documents. Finally, initializes the XMLValueTransformer.
 * // w  w w .  ja  v a  2 s . c  o  m
 * @throws Exception on JDBC problems
 */
@BeforeClass
public static void setUpClass() throws Exception {
    // loads the "driver" which apparently means the database is running
    Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
    PreparedStatement ps;
    //Connection connection = DriverManager.getConnection("jdbc:derby:XDB;create=true");     

    EmbeddedDataSource40 dataSource = new EmbeddedDataSource40();
    dataSource.setCreateDatabase("create");
    dataSource.setDatabaseName(DB_NAME);
    Connection connection = dataSource.getConnection();
    ps = connection.prepareStatement(SQL_CREATE_TABLE);
    ps.execute();
    ps = connection.prepareStatement(SQL_INSERT_ROW);
    ps.execute();
    ps.close();
    // Create the transformer
    XFORM = new XMLValueTransformer(dataSource);
    XFORM.setSql(SQL_SELECT);
    XFORM.setSrcPath("/trade/currency");

    // Create the XML SOURCE document
    SOURCE = DocumentHelper.createDocument();
    Element root = SOURCE.addElement("trade");
    root.addElement("currency").addText("XXX");
    // create the expected result document for comparison
    RESULT = DocumentHelper.createDocument();
    root = RESULT.addElement("trade");
    root.addElement("currency").addText("USD");
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• »