JDBC: Connect, Insert and Select
Problem
Open an in-memory SQLite database, create a table scores(name, score), and insert one row for each input line (name and score, separated by a space). Then read the rows back with a SELECT that sorts them: the highest score first, and the name in A to Z order when the scores are equal. Print each row as: name score.
Constraints
- 1 <= lines <= 100
- name has no spaces
- -1000 <= score <= 1000
Examples
Input
ann 50 bob 70 cy 60
Output
bob 70 cy 60 ann 50
Solutions
The database stores the rows and sorts them with ORDER BY. The steps are always the same in JDBC: open a connection, run SQL (with ? places for values), read the result rows, and close everything. try-with-resources closes the objects for you. JavaScript and TypeScript use the built-in node:sqlite module (Node.js 22.5 or newer), which prints an experimental warning. Python uses the sqlite3 module that comes with Python.
Java needs a JDBC driver at run time. Download sqlite-jdbc from Maven Central (org.xerial:sqlite-jdbc) and run: java -cp .:sqlite-jdbc.jar Main (on Windows use ; instead of :).
/*
* Problem : Run the commands.
* Input : Input lines.
* Output : Output lines.
* Approach: SQL ORDER BY
* Time : O(n log n)
* Space : O(n)
* Java : 8 or newer.
*/
import java.util.Scanner;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public class Main {
// This method solves the task. It receives the input and returns the answer.
static String[] solve(String[] lines) {
List<String> out = new ArrayList<String>();
// Step 1: DriverManager opens a connection. The text jdbc:sqlite::memory: means a database that lives only in memory.
try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
// Step 2: A Statement runs SQL that has no parameters, such as CREATE TABLE.
try (Statement create = conn.createStatement()) {
create.execute("CREATE TABLE scores (name TEXT, score INTEGER)");
}
// Step 3: A PreparedStatement has ? places. We set the values with setString and setInt, never by joining text.
try (PreparedStatement insert = conn.prepareStatement("INSERT INTO scores (name, score) VALUES (?, ?)")) {
for (String line : lines) {
String[] part = line.split(" ");
insert.setString(1, part[0]);
insert.setInt(2, Integer.parseInt(part[1]));
insert.executeUpdate();
}
}
// Step 4: ORDER BY lets the database do the sorting. rs.next() moves to the next row, and returns false at the end.
try (Statement query = conn.createStatement();
ResultSet rs = query.executeQuery("SELECT name, score FROM scores ORDER BY score DESC, name ASC")) {
while (rs.next()) {
out.add(rs.getString("name") + " " + rs.getInt("score"));
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
return out.toArray(new String[0]);
}
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
java.util.List<String> all = new java.util.ArrayList<>();
while (scanner.hasNextLine()) {
String line = scanner.nextLine().trim();
if (!line.isEmpty()) {
all.add(line);
}
}
String[] lines = all.toArray(new String[0]);
System.out.println(String.join("\n", solve(lines)));
}
}
Hints (2)
- Why do we use ? places and not string joining?
- What does rs.next() return at the end of the rows?