프리페어드 스테이트먼트

소프트웨어 지원

MySQL, 오라클, DB2, 마이크로소프트 SQL 서버, PostgreSQL을 포함한 주요 DBMS들은 프리페어드 스테이트먼트를 폭넓게 지원한다.

자바 JDBC

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class Main {

  public static void main(String[] args) throws SQLException {
    MysqlDataSource ds = new MysqlDataSource();
    ds.setDatabaseName("mysql");
    ds.setUser("root");

    try (Connection conn = ds.getConnection()) {
      try (Statement stmt = conn.createStatement()) {
        stmt.executeUpdate("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)");
      }

      try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO products VALUES (?, ?)")) {
        stmt.setString(1, "bike");
        stmt.setInt(2, 10900);
        stmt.executeUpdate();
        stmt.setString(1, "shoes");
        stmt.setInt(2, 7400);
        stmt.executeUpdate();
        stmt.setString(1, "phone");
        stmt.setInt(2, 29500);
        stmt.executeUpdate();
      }

      try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM products WHERE name = ?")) {
        stmt.setString(1, "shoes");
        ResultSet rs = stmt.executeQuery();
        rs.next();
        System.out.println(rs.getInt(2));
      }
    }
  }}

PHP PDO

<?php$stmt = null;try {
  $conn = new PDO("mysql:dbname=mysql", "root");
  $conn->exec("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)");
  $stmt = $conn->prepare("INSERT INTO products VALUES (?, ?)");
  $params = array(array("bike", 10900),
                  array("shoes", 7400),
                  array("phone", 29500));
  foreach ($params as $param) $stmt->execute($param);
  $stmt = $conn->prepare("SELECT * FROM products WHERE name = ?");
  $params = array("shoes");
  $stmt->execute($params);
  echo $stmt->fetch()[1];} finally {
  if ($stmt !== null) $stmt->closeCursor();}?>

펄 DBI

my $stmt = $db->prepare("SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?");$stmt->execute($username, $password);

C# ADO.NET

using (SqlCommand command = connection.CreateCommand()){
    command.CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room";
    command.Parameters.AddWithValue("@username", username);
    command.Parameters.AddWithValue("@room", room);

    using (SqlDataReader dataReader = command.ExecuteReader())
    {
        // ...
    }}

파이썬 DB-API

import mysql.connectorconn = Nonecursor = Nonetry:
    conn = mysql.connector.connect(database="mysql", user="root")
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)")
    params = [("bike", 10900),
              ("shoes", 7400),
              ("phone", 29500)]
    cursor.executemany("INSERT INTO products VALUES (%s, %s)", params)
    params = ("shoes",)
    cursor.execute("SELECT * FROM products WHERE name = %s", params)
    print(cursor.fetchall()[0][1])finally:
    if cursor is not None: cursor.close()
    if conn is not None: conn.close()

매직 다이렉트 SQL

Virtual username  Alpha 20   init: 'sister'
Virtual password  Alpha 20   init: 'yellow'

SQL Command:   SELECT * FROM users WHERE USERNAME=:1 AND PASSWORD=:2
Input Arguments:
1:  username
2:  password