JDBC API Demo

Jdbc1.java

/*
 * JDBC API Demo
 *
 * JDBC (Java Database Connectivity API) provides several classes and interfaces
 * that enables java applications to communicate with the backend system.
 *
 * JDBC Architecture
 * Java Application -> Driver Manager -> Available drivers (MySQL Driver, Oracle, SQL Server).
 *
 * All the java applications should follow the standard steps such as:
 *  1. Load the driver.
 *  2. Register with driver manager.
 *  3. Establish the connection using the driver manager.
 *  4. Prepare a statement.
 *  5. Execute the SQL query.
 *  6. Close the established connection.
 *
 * List of Interfaces:
 * - Connection
 * - Statement
 * - PrepareStatement
 * - ResultSet
 * - RowSet
 * - JdbcRowSet
 * - CacheRowSet
 *
 * List of Classes:
 * - DriverManager
 * - Timestamp
 *
 * SQL structure query language is an interface between a database and the users.
 *
 * SQL Commands:
 *  - DDL (Data Definition Language): CREATE, ALTER & DROP
 *  - DML (Data Manipulation Language): INSERT, UPDATE, DELETE & SELECT
 *    - E.g. INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...)
 *    - E.g. INSERT INTO table_name VALUES(val1, val2, ...)
 *    - E.g. UPDATE table_name SET col = value WHERE [condition]
 *    - E.g. DELETE FROM table_name WHERE col1 = val1 AND col2 = val2
 *  Used by DBA (Database Administrators):
 *  - TCL (Transaction Control Language): COMMIT & ROLLBACK
 *  - DCL (Data Control Language): GRAND & REVOKE
 *
 * Usage:
 * - Download the connector from http://dev.mysql.com/downloads/connector/j/
 * - Configure your classpath system variable to point to your connector .jar file.
 * - Run your MySQL server.
 * - Go to phpmyadmin and create training table with 4 columns: id, name, course, address
 * - Run:
 *    - javap com.mysql.jdbc.Driver
 *    - javac Jdbc1.java
 *    - java Jdbc1
 *
 */

import java.sql.*;
import static java.lang.Math.random;
import java.io.Console;

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

    // Load the driver.
    Class.forName("com.mysql.jdbc.Driver");

    // Establish the connection to the existing database: training.
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost/training", "root", "");
    System.out.println("Connected!");

    // Create a statement.
    Statement st = con.createStatement();

    // Generate random data.
    // String name = Long.toHexString(Double.doubleToLongBits(Math.random()));
    // String course = Long.toHexString(Double.doubleToLongBits(Math.random()));
    // String address = Long.toHexString(Double.doubleToLongBits(Math.random()));

    // Input data.
    Console console = System.console();
    String name = console.readLine("Enter name:");
    String course = console.readLine("Enter course:");
    String address = console.readLine("Enter address:");

    // Execute the query.
    st.execute("INSERT INTO student (name, course, address) VALUES ('" + name + "', '" + course + "', '" + address + "')");
    System.out.println("name = " + name);
    System.out.println("course = " + course);
    System.out.println("address = " + address);
    System.out.println("Record created.");

    // Update the query if doesn't contain comma.
    if (!address.contains(",")) {
      String newaddress = address + ", London, UK";
      st.execute("UPDATE student SET address = '" + newaddress + "'");
      System.out.println("Address updated to: " + newaddress);
    }

    // Delete record if contains 'test' text.
    if (name.contains("test") || course.contains("test") || address.contains("test")) {
      st.execute("DELETE FROM student WHERE name = '" + name + "'");
      System.out.println("Test record removed.");
    }

  }
  
}