Lecture 5 – notes

package demo;

import java.sql.*;
import java.util.*;

/**
 * Database is an interface to the college application database, it
 * uses JDBC to connect to a SQLite3 file.
 */
public class Database {

    /**
     * The database connection.
     */
    private Connection conn;

    /**
     * Creates the database interface object. Connection to the
     * database is performed later.
     */
    public Database() {
        conn = null;
    }

    /**
     * Opens a connection to the database, using the specified
     * filename (if we'd used a traditional DBMS, such as PostgreSQL
     * or MariaDB, we would have specified username and password
     * instead).
     */
    public boolean openConnection(String filename) {
        try {
            conn = DriverManager.getConnection("jdbc:sqlite:" + filename);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * Closes the connection to the database.
     */
    public void closeConnection() {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Checks if the connection to the database has been established
     *
     * @return true if the connection has been established
     */
    public boolean isConnected() {
        return conn != null;
    }

    /* ================================== */
    /* --- insert your own code below --- */
    /* ===============================*== */

}

$ gradle init

Select type of project to generate:
  1: basic
  2: application
  3: library
  4: Gradle plugin
Enter selection (default: basic) [1..4] 2

Select implementation language:
  1: C++
  2: Groovy
  3: Java
  4: Kotlin
  5: Scala
  6: Swift
Enter selection (default: Java) [1..6] 6

Select build script DSL:
  1: Kotlin
  2: Groovy
Enter selection (default: Kotlin) [1..2] 1

Project name (default: java-demo): demo
Generate build using new APIs and behavior (some features may
change in the next minor release)? (default: no) [yes,no] no

> Task :init
To learn more about Gradle by exploring our Samples at https://docs.gradle.org/8.5/samples/sample_building_swift_applications.html

BUILD SUCCESSFUL in 26s
2 actionable tasks: 2 executed

$ tree
.
├── app
│  ├── build.gradle
│  └── src
│     ├── main
│     │  ├── java
│     │  │  └── demo
│     │  │     └── App.java
│     │  └── resources
│     └── test
│        ├── java
│        │  └── demo
│        │     └── AppTest.java
│        └── resources
├── gradle
│  └── wrapper
│     ├── gradle-wrapper.jar
│     └── gradle-wrapper.properties
├── gradlew
├── gradlew.bat
└── settings.gradle
$

package demo;

public class App {

    public static void main(String[] args) {
        new App().run();
    }

    void run() {
        System.out.println("hello, world!");
    }
}

implementation group: 'org.xerial', name: 'sqlite-jdbc', version: '3.45.0.0'

/*
 * This file was generated by the Gradle 'init' task.
 *
 * This generated file contains a sample Java application project to get you started.
 * For more details on building Java & JVM projects, please refer to https://docs.gradle.org/8.5/userguide/building_java_projects.html in the Gradle documentation.
 */

plugins {
    // Apply the application plugin to add support for building a CLI application in Java.
    id 'application'
}

repositories {
    // Use Maven Central for resolving dependencies.
    mavenCentral()
}

dependencies {
    // Use JUnit Jupiter for testing.
    testImplementation libs.junit.jupiter

    testRuntimeOnly 'org.junit.platform:junit-platform-launcher'

    implementation group: 'org.xerial', name: 'sqlite-jdbc', version: '3.45.0.0'

    // This dependency is used by the application.
    implementation libs.guava
}

// Apply a specific Java toolchain to ease working on different environments.
java {
    toolchain {
        languageVersion = JavaLanguageVersion.of(21)
    }
}

application {
    // Define the main class for the application.
    mainClass = 'demo.App'
}

tasks.named('test') {
    // Use JUnit Platform for unit tests.
    useJUnitPlatform()
}

package demo;

public class App {

    public static void main(String[] args) {
        new App().run();
    }

    public void run() {
        var db = new Database();
        db.openConnection("colleges.sqlite");
        var applicants = db.findApplicants("Stanford", "CS");
        for (var a : applicants) {
            System.out.printf("%6d: %-20s (%.1f)\n",
                              a.id,
                              a.name,
                              a.gpa);
        }
    }
}

SELECT  s_name, gpa
FROM    students
        JOIN applications USING (s_id)
WHERE   c_name = 'Stanford'
        AND major = 'CS';

var found = new ArrayList<...>();
var query =
    """
    SELECT ...
    """;
try (var ps = conn.prepareStatement(query)) {
    ps.setString(1, ...);          // set parameters...
    var resultSet = ps.executeQuery();
    while (resultSet.next()) {
        // extract the values in rs into an object,
        // and then add the object to our list of
        // found values
        found.add(...(resultSet));
    }
} catch (SQLException e) {
    e.printStackTrace();
}
return found;

class Applicant {

    public final int id;
    public final String name;
    public final double gpa;

    private Applicant  (int id, String name, double gpa) {
        this.id = id;
        this.name = name;
        this.gpa = gpa;
    }

    public static Applicant fromRS(ResultSet rs) throws SQLException {
        return new Applicant(rs.getInt("s_id"),
                             rs.getString("s_name"),
                             rs.getDouble("gpa"));
    }
}

$ db-pojo -j
Applicant
s_id -> id : int
s_name -> name : String
gpa : double
<CTRL-D>
class Applicant {

    public final String id;
    public final String name;
    public final double gpa;

    private Applicant  (String id, String name, double gpa) {
        this.id = id;
        this.name = name;
        this.gpa = gpa;
    }

    public static Applicant fromRS(ResultSet rs) throws SQLException {
        return new Applicant(rs.getString("s_id"), rs.getString("s_name"), rs.getDouble("gpa"));
    }
}

$ python db-pojo -j

/* ================================== */
/* --- insert your own code below --- */
/* ===============================*== */

public List<Applicant> findApplicants(String college, String major) {
    var found = new ArrayList<Applicant>();
    var query =
        """
        SELECT  s_id, s_name, gpa
        FROM    applications
                JOIN students USING (s_id)
        WHERE   c_name = ? AND major = ?
        """;
    try (var ps = conn.prepareStatement(query)) {
        ps.setString(1, college);
        ps.setString(2, major);
        var resultSet = ps.executeQuery();
        while (resultSet.next()) {
            found.add(Applicant.fromRS(resultSet));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return found;
}

void run() {
    var db = new Database();
    db.openConnection("colleges.sqlite");
    var applicants = db.findApplicants("Stanford", "CS");
    for (var a : applicants) {
        System.out.printf("%3d: %20s %.1f\n",
                          a.id,
                          a.name,
                          a.gpa);
    }
}

$ ./gradlew run

> Task :app:run
123:                  Amy 3.9
876:                Irene 3.9
987:                Helen 3.7

BUILD SUCCESSFUL in 795ms
2 actionable tasks: 1 executed, 1 up-to-date

$ gradlew run

public void inflateGrades(String college, double percentage) {
    var factor = 1.0 + percentage / 100.0;
    var statement =
        """
        UPDATE   students
        SET      gpa = min(4.0, gpa * ?)
        WHERE    s_id IN (
                    SELECT DISTINCT s_id
                    FROM   applications
                    WHERE  c_name = ?
        )
        """;
    try (var ps = conn.prepareStatement(statement)) {
        ps.setDouble(1, factor);
        ps.setString(2, college);
        ps.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}