Java JDBC

Java JDBC: demoMavenH2

Java JDBC API

JDBC is Database Independent

The Java JDBC API standardizes how to connect to a database, how to execute queries against it, how to navigate the result of such a query, and how to execute updates in the database, etc..

JDBC is Not SQL Independent

JDBC does not standardize the SQL sent to the database. The SQL is written by you, the user of the JDBC API. The SQL dialect used by the various different databases will vary slightly, be careful.

JDBC is for Relational Databases

The Java JDBC API is intended for interaction with relational databases, meaning databases that you interact with via standard SQL. The JDBC API is not intended for non-relational databases.

Project demoMavenH2

With a Maven Java project: repo

We use in this project:

• Connection
• DriverManager.getConnection
• connection.createStatement()
• Statement statement
  • The Java JDBC Statement, java.sql.Statement, interface is used to execute SQL statements against a relational database.
  • You obtain a JDBC Statement from a JDBC Connection. Once you have a Java Statement instance you can execute either a database query or an database update with it.
• result = statement.executeUpdate();
  • The Java JDBC ResultSet interface represents the result of a database query. The text about queries shows how the result of a query is returned as a java.sql.ResultSet.
  • This ResultSet is then iterated to inspect the result.

Dependencies

    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>1.4.199</version>
    </dependency>

Main class

`

public class MavenH2 {

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

    //let s define aur connection, URL jdbc
    //how to create a string connection to H2
    //GET path on Windows: jdbc:h2:C:\Users\AdministradorCIFO\dataBaseH2\campusPayPal
    //GET localhost jdb tcp: jdbc:h2:tcp://localhost/
    //concatenate from USER WINDOWS: jdbc:h2:tcp://localhost/~/dataBaseH2/campusPayPal
    String stringURLConnection = "jdbc:h2:tcp://localhost/~/dataBaseH2/campusPayPal";
    String username = "albert";
    String password = "1234";
    //connect ot url with connection object
    Connection connection = DriverManager.getConnection(stringURLConnection, username, password);
    System.out.println("Connected to H2 local database.");

    String sql = "CREATE TABLE users (ID int PRIMARY KEY, name VARCHAR(50), surname VARCHAR(50), age INTEGER)";

    Statement statement = connection.createStatement();
    statement.execute(sql);

    System.out.println("Created table cards");

    sql = "INSERT INTO users (ID, name, surname, age) VALUES (1, 'Amanda', 'Jones', 25 )";
    int result = statement.executeUpdate(sql);
    System.out.println("Operation write to DB: " +  result);

    sql = "INSERT INTO users (ID, name, surname, age) VALUES (2, 'Linda', 'Jones', 28 )";
    result = statement.executeUpdate(sql);
    System.out.println("Operation write to DB: " +  result);

    sql = "INSERT INTO users (ID, name, surname, age) VALUES (3, 'Susy', 'Jones', 56 )";
    result = statement.executeUpdate(sql);
    System.out.println("Operation write to DB: " +  result);

    sql = "INSERT INTO users (ID, name, surname, age) VALUES (4, 'Lola', 'Figuerols', 36 )";
    result = statement.executeUpdate(sql);
    System.out.println("Operation write to DB: " +  result);

    sql = "SELECT * FROM users";

    //Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(sql);

    int count = 0;
    while (resultSet.next()) {
        count++;
        int ID = resultSet.getInt("ID");
        String name = resultSet.getString("name");
        String surname = resultSet.getString("surname");
        int age = resultSet.getInt("age");

        System.out.println("users #" + count + ": " + ID + ", " + name + " "  + surname + ", " + age);
    }


    connection.close();

}}

`

Architecture Servlets

Servlet Container vs. Spring Containers, link.