Easy JDBC using Spring

Spring has a well-written module for JDBC. We’ll use it to produce clean code for our application in 10 minutes.

JDBC σε 10 λεπτά

Ας αποθηκεύσουμε άτομα στην βάση. Για κάθε άτομο μας ενδιαφέρει το όνομα, η ηλικία και ένας μοναδικός αριθμός (κλειδί).

• Το μοντέλο
• Η βάση δεδομένων
• Εισαγωγή (insert)
• Επιλογή (select)
• Αλλαγή (update)
• Διαγραφή (delete)
• Επανάληψη

Για τον σκοπό αυτό θα φτιάξουμε ακριβώς τρεις κλάσεις: Την Connector η οποία διαχειρίζεται τις συνδέσεις με την βάση, την Person που απεικονίζει ένα άτομο και την PersonHandler η οποία διαχειρίζεται τα άτομα ως προς την βάση (εισάγει, διαγράφει κλπ).

Το μοντέλο

Πρόκειται για ένα απλό javabean που περιέχει τις πληροφορίες που μας ενδιαφέρουν.

public class Person {
    private String name;
    private int age;
    private int id;

    // getters and setters
}

Η βάση δεδομένων

Για να φτιάξεις τη βάση στην MySQL γράψε διαδοχικά.

create database hello;
use hello;
CREATE TABLE persons (name VARCHAR(255),
age SMALLINT, id BIGINT AUTO_INCREMENT, primary key (id));

Για περισσότερες πληροφορίες μπορείς να δεις εδώ.

Κατέβασε τον JDBC driver της MySQL από το mysql.com > downloads > Connectors > Connector/J > Source and Binaries (zip) > Pick a mirror > No thanks, just take me to the downloads > Greece [National Technical University of Athens] HTTP > mysql-connector-java-5.1.7.zip. Μέσα στο zip υπάρχει το mysql-connector-java-5.1.7-bin.jar, το οποίο θα μπει στο classpath του project.

Xρειαζόμαστε μια βολική κλάση η οποία θα μπορεί να συνδέεται και αποσυνδέεται. Να λοιπόν πώς συνδεόμαστε,

/**
 * Επιστρέφει μια σύνδεση με την βάση.
 * Φορτώνουμε τον JDBC driver, του δίνουμε
 * το username και password, καθώς και το url
 * της βάσης δεδομένων.
 */
public static Connection getConnection() throws Exception {
    Connection result = null;
    Class.forName("com.mysql.jdbc.Driver");
    String user = "nikos";
    String pass = "12345";
    String url = "jdbc:mysql://127.0.0.1/hello";
    result = DriverManager.getConnection(url, user, pass);
    result.setAutoCommit(false);
    return result;
}

και να πώς αποσυνδεόμαστε.

/**
 * Αποσυνδέεται από τη βάση.
 */
public static void disconnect(PreparedStatement statement,
    ResultSet set, Connection connection) throws Exception {
    if (statement != null) {
        statement.close();
    }
    if (set != null) {
        set.close();
    }
    if (connection != null) {
        connection.close();
    }
}

Αυτές λοιπόν οι δύο μέθοδοι ανήκουν στην κλάση Connector.

Εισαγωγή (insert)

Για να βάλουμε δεδομένα στη βάση, συνδεόμαστε καταρχήν μαζί της. Έπειτα προετοιμάζουμε την πρόταση SQL και την γεμίζουμε με τις τιμές από το javabean. Αφού εκτελέσουμε και πάρουμε το αποτέλεσμα, κλείνουμε την σύνδεση.

public boolean insert(Person person) throws Exception {
    boolean result = false;
    Connection connection = null;
    PreparedStatement statement = null;
    String sql = "INSERT INTO persons(name, age) VALUES(?, ?)";
    try {
        // Σύνδεση
        connection = Connector.getConnection();
        // Προετοιμασία
        statement = connection.prepareStatement(sql);
        statement.setString(1, person.getName());
        statement.setInt(2, person.getAge());
        // Εκτέλεση
        if (statement.executeUpdate() == 1) {
            result = true;
            connection.commit();
        }
    } catch (Exception e) {
        connection.rollback();
        throw e;
    } finally {
        // Αποσύνδεση
        Connector.disconnect(statement, null, connection);
    }
    return result;
}

Προσέξτε την πρόταση SQL, η οποία περιέχει ερωτηματικά: “INSERT INTO persons(name, age) VALUES(?, ?)”. Πρόκειται για ένα PreparedStatement.

Επιλογή (select)

public Person select(int id) throws Exception {
    Person person = null;
    ResultSet set = null;
    Connection connection = null;
    PreparedStatement statement = null;
    String sql = "SELECT * FROM persons WHERE id = ?";
    try {
        // Σύνδεση
        connection = Connector.getConnection();
        // Προετοιμασία
        statement = connection.prepareStatement(sql);
        statement.setInt(1, id);
        // Εκτέλεση
        set = statement.executeQuery();
        if (set.next()) {
            person = new Person();
            person.setName(set.getString("name"));
            person.setAge(set.getInt("age"));
            person.setId(set.getInt("id"));
        }
    } finally {
        // Αποσύνδεση
        Connector.disconnect(statement, set, connection);
    }
    return person;
}

Αλλαγή (update)

public boolean update(Person person) throws Exception {
    boolean result = false;
    Connection connection = null;
    PreparedStatement statement = null;
    String sql = "UPDATE persons SET name = ?, age = ? WHERE id = ?";
    try {
        // Σύνδεση
        connection = Connector.getConnection();
        // Προετοιμασία
        statement = connection.prepareStatement(sql);
        statement.setString(1, person.getName());
        statement.setInt(2, person.getAge());
        statement.setInt(3, person.getId());
        // Εκτέλεση
        if (statement.executeUpdate() == 1) {
            result = true;
            connection.commit();
        }
    } catch (Exception e) {
        connection.rollback();
        throw e;
    } finally {
        // Αποσύνδεση
        Connector.disconnect(statement, null, connection);
    }
    return result;
}

Διαγραφή (delete)

public boolean delete(int id) throws Exception {
    boolean result = false;
    Connection connection = null;
    PreparedStatement statement = null;
    String sql = "DELETE FROM persons WHERE id = ?";
    try {
        // Σύνδεση
        connection = Connector.getConnection();
        // Προετοιμασία
        statement = connection.prepareStatement(sql);
        statement.setInt(1, id);
        // Εκτέλεση
        if (statement.executeUpdate() == 1) {
            result = true;
            connection.commit();
        }
    } catch (Exception e) {
        connection.rollback();
        throw e;
    } finally {
        // Αποσύνδεση
        Connector.disconnect(statement, null, connection);
    }
    return result;
}

Επανάληψη

Το JDBC είναι στρωτό:

1. Σύνδεση.
2. Προετοιμασία πρότασης SQL.
3. Εκτέλεση και επιστροφή του αποτελέσματος.
4. Αποσύνδεση.

• MySQL σε 3 λεπτά
• Connection, Statement, PreparedStatement, ResultSet.

JDBC dalam 10 menit

Selamat datang saudara dari Indonesia. Pelajaran sederhana ini tentang JDBC dasar. Saudara akan membuat sebuah aplikasi Java yang bisa berkomunikasi dengan basis data.

Setiap metode yang pakai JDBC pasti ikut langkah yang berikut:

• Langkah 1. Menyambungkan ke basis data.
• Langkah 2. Bersiap sebuah perintah SQL.
• Langkah 3. Bertanya perintah SQL dan mengambil hasilnya.
• Langkah 4. Melepaskan sambungan.

Mari kita menyimpan orang-orang (persons) di basis data. Untuk setiap orang kita tertarik tentang 1. nama (name) 2. umur (age) dan 3. satu nomor yang unik (key).

1. Instalasi MySQL
2. Bangunan struktur basis data
3. Memasukkan JDBC driver di classpath
4. Bangunan kelas penyambung
5. Bangunan model
6. Memasukkan data (insert)
7. Mengambil data (select)
8. Mengubahkan data (update)
9. Menhapus data (delete)
10. Mencoba kalau semua sudah pas

Untuk aplikasi ini hanya tiga kelas harus dibuat: Connector kelas yang bisa menyambungkan ke basis data, Person kelas yang ada menggambarkan seorang dan PersonSQL kelas yang bisa memasukkan, mengubahkan, mengambil dan menghapus orang2 dari basis data itu.

1. Instalasi MySQL

Mendapatkan MySQL dari mysql.com → downloads → MySQL Community Server → Windows ZIP/Setup.EXE (x86) → Pick a mirror → No thanks, just take me to the downloads → Japan [JMPA] HTTP → mysql-5.0.51b-win32.zip

Mendapatkan JDBC driver MySQL (perhubungan antara Java dan MySQL, disebut driver atau connector dalam bahasa inggris) dari mysql.com → downloads → Connectors → Connector/J → Source and Binaries (zip) → Pick a mirror → No thanks, just take me to the downloads → Japan [JMPA] HTTP → mysql-connector-java-5.1.6.zip

MySQL dapat diinstal dengan mudah. Habis proses instalasi kotak dialog konfigurasi akan muncul. Pilih “Best Support For Multilingualism” dan tulis di kertas sandi administrator (root password).

Menaktifkan MySQL melalui Start → Programs → MySQL → MySQL Server 5.0 → MySQL Command Line Client → dan masukan sandi yang tadi.

2. Bangunan struktur basis data

Membuat sebuah struktur basis data di MySQL dengan create database hello;

Untuk memastikan ketiklah show databases; dan terus use hello;

Untuk membuat sebuah tabel di MySQL tulislah
CREATE TABLE persons (name VARCHAR(255), age SMALLINT, id BIGINT AUTO_INCREMENT, primary key (id));

Untuk memastikan ketik show tables; dan terus describe persons;

3. Memasukkan JDBC driver di classpath

Di dalam mysql-connector-java-5.1.6.zip ada mysql-connector-java-5.1.6-bin.jar. Ini cara untuk masukkan jar ini di classpath…

Kalau pakai Eclipse: Project → Java Build Path → Libraries → Add External JARs…

Kalau pakai Netbeans: File → “Project” Properties → Libraries → Add JAR/Folder

Kalau pakai JDeveloper 11g: Tools → Project Properties… → Libraries and Classpath → Add JAR/Directory…

4. Bangunan kelas penyambung

Kelas ini akan menyambung ke basis data dan akan melepaskan sambungan ini.

Pertama kita harus daftar JDBC driver,

     Class.forName("com.mysql.jdbc.Driver");

dan kasih tahu tentang nama user, sandi

     String username = "root";
     String password = "qweasdzxc";

dan url basis data,

     String url = "jdbc:mysql://127.0.0.1/hello";

Karena MySQL di komputer yang sama dengan aplikasi alamatnya 127.0.0.1. jdbc:mysql:// adalah protokol dan hello nama struktur basis data.

Inilah keseluruhan metode yang menyambungkan ke basis data.

public static Connection getConnection() throws Exception {
   Class.forName("com.mysql.jdbc.Driver");
   String username = "root";
   String password = "qweasdzxc";
   String url = "jdbc:mysql://127.0.0.1/hello";
   connection = DriverManager.getConnection(url, username, password);
   connection.setAutoCommit(false);
   return connection;
}

Inilah keseluruhan metode yang melepaskan sambungan dari basis data.

public static void closeConnection(PreparedStatement statement,
      ResultSet results, Connection connection) throws Exception {
   if (statement != null) {
      statement.close();
      statement = null;
   }
   if (results != null) {
      results.close();
      results = null;
   }
   if (connection != null) {
      connection.close();
      connection = null;
   }
}

5. Bangunan model

Kelas ini menggambarkan seorang. Metodenya hanya get dan set (javabean).

public class Person implements Serializable {

   private String name;
   private int age;
   private int id;

   public int getAge() {
      return age;
   }

   public void setAge(int age) {
      this.age = age;
   }

   public int getId() {
      return id;
   }

   public void setId(int id) {
      this.id = id;
   }

   public String getName() {
      return name;
   }

   public void setName(String name) {
      this.name = name;
   }

}

6. Memasukkan data (insert)

Untuk memasukkan data mari kita ikut langkah yang empat:

Langkah 1. Menyambungkan ke basis data.

     Connector.getConnection();

Langkah 2. Bersiap sebuah perintah SQL.

     connection.prepareStatement("INSERT INTO persons(name, age) VALUES(?, ?)");
     statement.setString(1, person.getName());
     statement.setInt(2, person.getAge());

Langkah 3. Bertanya perintah SQL dan mengambil hasilnya.

     statement.executeUpdate();

Langkah 4. Melepaskan sambungan.

     Connector.closeConnection(...);

Mari kita lihat metode ini.

private Connection connection;
private PreparedStatement statement;

public boolean insert(Person person) throws Exception {
   boolean result = false;
   try {
      // step 1: connect to database
      connection = Connector.getConnection();
      // step 2: prepare the statement
      statement = connection.prepareStatement("INSERT INTO persons(name, age) VALUES(?, ?)");
      statement.setString(1, person.getName());

      statement.setInt(2, person.getAge());
      // step 3: execute the statement and get the results
      if (statement.executeUpdate() == 1) {
         result = true;
         connection.commit();
      }
   } catch (Exception e) {
      connection.rollback();
      throw e;
   } finally {
      // step 4: close the connection
      Connector.closeConnection(statement, null, connection);
   }
   return result;
}

7. Mengambil data (select)

Untuk mencari/mengambil data mari kita tetap ikut langkah yang empat:

Langkah 1. Menyambungkan ke basis data.

     Connector.getConnection();

Langkah 2. Bersiap sebuah perintah SQL.

     statement = connection.prepareStatement("SELECT * FROM persons WHERE id = ?");
     statement.setInt(1, id);

Langkah 3. Bertanya perintah SQL dan mengambil hasilnya.

     statement.executeQuery();

Langkah 4. Melepaskan sambungan.

     Connector.closeConnection();


public Person select(int id) throws Exception {
   Person person = null;
   try {
      // step 1: connect to database
      connection = Connector.getConnection();
      // step 2: prepare the statement and fill it
      statement = connection
            .prepareStatement("SELECT * FROM persons WHERE id = ?");
      statement.setInt(1, id);
      // step 3: execute and get the results
      set = statement.executeQuery();
      if (set.next()) {
         person = new Person();
         person.setName(set.getString("name"));
         person.setAge(set.getInt("age"));
         person.setId(set.getInt("id"));
      }
   } finally {
      // step 4: close the connection
      Connector.closeConnection(statement, set, connection);
   }
   return person;
}

8. Mengubahkan data (update)

Untuk mengubah nilai data yang sudah tersimpan:

Langkah 1. Menyambungkan ke basis data.

     Connector.getConnection();

Langkah 2. Bersiap sebuah perintah SQL.

     statement = connection.prepareStatement("UPDATE persons SET name = ?, age = ? WHERE id = ?");
     statement.setString(1, person.getName());
     statement.setInt(2, person.getAge());
     statement.setInt(3, person.getId());

Langkah 3. Bertanya perintah SQL dan mengambil hasilnya.

     statement.executeUpdate();

Langkah 4. Melepaskan sambungan.

     Connector.closeConnection();


public boolean update(Person person) throws Exception {
   boolean result = false;
   try {
      // step 1: connect to database
      connection = Connector.getConnection();
      // step 2: prepare the statement and fill it
      statement = connection
            .prepareStatement("UPDATE persons SET name = ?, age = ? WHERE id = ?");
      statement.setString(1, person.getName());
      statement.setInt(2, person.getAge());
      statement.setInt(3, person.getId());
      // step 3: execute and get the results
      if (statement.executeUpdate() == 1) {
         result = true;
         connection.commit();
      }
   } catch (Exception e) {
      connection.rollback();
      throw e;
   } finally {
      // step 4: close the connection
      Connector.closeConnection(statement, null, connection);
   }
   return result;
}

9. Menhapus data (delete)

Langkah 1. Menyambungkan ke basis data.

     Connector.getConnection();

Langkah 2. Bersiap sebuah perintah SQL.

     statement = connection.prepareStatement("DELETE FROM persons WHERE id = ?");

Langkah 3. Bertanya perintah SQL dan mengambil hasilnya.

     statement.executeUpdate();

Langkah 4. Melepaskan sambungan.

     Connector.closeConnection();


public boolean delete(int id) throws Exception {
   boolean result = false;
   try {
      // step 1: connect to database
      connection = Connector.getConnection();
      // step 2: prepare the statement and fill it
      statement = connection
            .prepareStatement("DELETE FROM persons WHERE id = ?");
      statement.setInt(1, id);
      // step 3: execute and get the results
      if (statement.executeUpdate() == 1) {
         result = true;
         connection.commit();
      }
   } catch (Exception e) {
      connection.rollback();
      throw e;
   } finally {
      // step 4: close the connection
      Connector.closeConnection(statement, null, connection);
   }
   return result;
}

10. Mencoba kalau semua sudah pas

Pada saat ini saudara sudah membuat aplikasi yang bisa berkomunikasi dengan basis data. Tetapi mari kita berjaga2 dulu dan mencoba semua metode ini.

Misalnya, untuk mencoba metode insert kita bisa membuat seorang person dulu..

   Person person = new Person();
   person.setName("Al Pacino");
   person.setAge(68);

dan coba menyimpan di basis data.

   new PersonSQL().insert(person);

Untuk memastikan di MySQL command prompt SELECT * FROM persons;

Kalau saudara punya waktu yang cukup silakan baca cara-cara Connection, Statement, PreparedStatement, ResultSet.

Sebelum selesai, saya ingin minta maaf karena bahasa Indonesia saya masih kurang. Kalau saudara ingin berkomunikasi mari meninggalkan pesan (comment) di bawa.

Terima kasih,
Nikos.

JDBC in 10 minutes

Let’s store information about persons in the database. For every person we are interested about its name, age and a unique number (key).

• The model
• The database
• Insert data
• Select data
• Delete data
• Update data
• Review

For this purpose we’ll make exactly three classes: a Connector that connects to the database, a Person class that represents an actual person and the PersonHandler class that handles the persons in the database (inserts, deletes etc).

The model

It’s a simple javabean that holds the required information.

public class Person {
    private String name;
    private int age;
    private int id;

    // getters and setters
}

The database

To create our database in MySQL just type.

create database hello;

and then:

use hello;

To create the table in MySQL write

CREATE TABLE persons (
	name VARCHAR(255),
	age SMALLINT,
	id BIGINT AUTO_INCREMENT,
	primary key (id)
);

For more information you may refer to MySQL in 3 minutes.

Get the JDBC driver from mysql.com → downloads → Connectors → Connector/J → Source and Binaries (zip) → Pick a mirror → No thanks, just take me to the downloads &rarr Greece [National Technical University of Athens] HTTP → mysql-connector-java-5.1.8.zip.

Inside the zip lies mysql-connector-java-5.1.8-bin.jar that should be included in the project’s classpath.

We need a comfortable class that is able to connect and disconnect. This is how we connect from a desktop application,

/**
 * Returns a connection to the database.
 * We load the JDBC driver, along with the username and password.
 * Also we provide the url of the database.
 */
public static Connection getConnection() throws Exception {
    Connection result = null;
    Class.forName("com.mysql.jdbc.Driver");
    String user = "nikos";
    String pass = "12345";
    String url = "jdbc:mysql://127.0.0.1/hello";
    result = DriverManager.getConnection(url, user, pass);
    result.setAutoCommit(false);
    return result;
}

and this is how we disconnect.

/**
 * Disconnects from the database.
 */
public static void disconnect(PreparedStatement statement,
    ResultSet set, Connection connection) throws Exception {
    if (statement != null) {
        statement.close();
    }
    if (set != null) {
        set.close();
    }
    if (connection != null) {
        connection.close();
    }
}

So these two methods belong to the Connector class.

Insert data

To insert data in the database, first of all, we should connect to it. Then we prepare the SQL statement and we fill it with the values from the javabean. After executing and taking the result, we close the connection.

public boolean insert(Person person) throws Exception {
    boolean result = false;
    Connection connection = null;
    PreparedStatement statement = null;
    String sql = "INSERT INTO persons(name, age) VALUES(?, ?)";
    try {
        // Connection
        connection = Connector.getConnection();
        // Preparation
        statement = connection.prepareStatement(sql);
        statement.setString(1, person.getName());
        statement.setInt(2, person.getAge());
        // Execution
        if (statement.executeUpdate() == 1) {
            result = true;
            connection.commit();
        }
    } catch (Exception e) {
        connection.rollback();
        throw e;
    } finally {
        // Disconnection
        Connector.disconnect(statement, null, connection);
    }
    return result;
}

Take a look at the SQL statement, that contains question marks:

INSERT INTO persons(name, age) VALUES(?, ?)

In Java, this is a PreparedStatement.

Select data

The following method selects a person based on its unique identifier, the so-called primary key.

public Person select(int id) throws Exception {
    Person person = null;
    ResultSet set = null;
    Connection connection = null;
    PreparedStatement statement = null;
    String sql = "SELECT * FROM persons WHERE id = ?";
    try {
        // Connection
        connection = Connector.getConnection();
        // Preparation
        statement = connection.prepareStatement(sql);
        statement.setInt(1, id);
        // Execution
        set = statement.executeQuery();
        if (set.next()) {
            person = new Person();
            person.setName(set.getString("name"));
            person.setAge(set.getInt("age"));
            person.setId(set.getInt("id"));
        }
    } finally {
        // Disconnection
        Connector.disconnect(statement, set, connection);
    }
    return person;
}

Of course you can select data based on any criteria defined by the business requirements. For example:

SELECT *
  FROM persons
 WHERE age <= ?
   AND age >= ?

Delete data

public boolean delete(int id) throws Exception {
    boolean result = false;
    Connection connection = null;
    PreparedStatement statement = null;
    String sql = "DELETE FROM persons WHERE id = ?";
    try {
        // Connection
        connection = Connector.getConnection();
        // Preparation
        statement = connection.prepareStatement(sql);
        statement.setInt(1, id);
        // Execution
        if (statement.executeUpdate() == 1) {
            result = true;
            connection.commit();
        }
    } catch (Exception e) {
        connection.rollback();
        throw e;
    } finally {
        // Disconnection
        Connector.disconnect(statement, null, connection);
    }
    return result;
}

Update data

public boolean update(Person person) throws Exception {
    boolean result = false;
    Connection connection = null;
    PreparedStatement statement = null;
    String sql = "UPDATE persons SET name = ?, age = ? WHERE id = ?";
    try {
        // Connection
        connection = Connector.getConnection();
        // Preparation
        statement = connection.prepareStatement(sql);
        statement.setString(1, person.getName());
        statement.setInt(2, person.getAge());
        statement.setInt(3, person.getId());
        // Execution
        if (statement.executeUpdate() == 1) {
            result = true;
            connection.commit();
        }
    } catch (Exception e) {
        connection.rollback();
        throw e;
    } finally {
        // Disconnection
        Connector.disconnect(statement, null, connection);
    }
    return result;
}

Review

JDBC is straight-forward:

1. Connection.
2. Preparation of the SQL statement.
3. Execution and return of the result.
4. Disconnection.

• MySQL in 3 minutes
• Easy JDBC using Spring
• Connection, Statement, PreparedStatement, ResultSet.