A database in the game: leaderboard, SQL injection, keyboard and sprites

Last session a console program showed how Java writes to and reads from a database. Now the same thing goes into games, the way the third assignment asks for it: when the game ends, a dialog asks for the player's name, the result is saved to a database, a menu item shows the top 10, and a new game can start at any time. On the way we decide where the database belongs in a model–view program, what happens when it isn't there, and why string-built SQL is dangerous.

The main example, Dodger, also shows the rest of the third assignment: sprites drawn with elementary graphics, WASD controls and a game timer. A ship dodges falling asteroids and catches stars; three lives, then game over. The second example, ColorclickerDB, adds a leaderboard to the finished Color clicker from two sessions ago – the same job you may do with your own game.

The one-line architecture: the view only talks to the backend; the backend only talks to an interface; SQL lives in exactly one class. Every rule in this session follows from that.

Where the database goes in model–view

Dodger is split into three packages. This is the structure I recommend for the third assignment:

src/main/java/dodger/
├── Dodger.java          entry point – wires the layers together
├── model/               BACKEND – no Swing, no AWT, no SQL
│   ├── Game.java              tick, press/release, lives, score, time
│   ├── Ship.java, FallingObject.java, Sprite.java, Direction.java
│   ├── Leaderboard.java       save, top; name check; model -> DTO
│   ├── HighScore.java         the model of one result (record)
│   ├── HighScoreDto.java      one row as the view gets it, with the rank
│   ├── HighScoreRepository.java   interface: what the backend needs from storage
│   └── StorageException.java
├── db/                  STORAGE – the only place with SQL
│   ├── Database.java          URL, user, password
│   └── JdbcHighScoreRepository.java
└── view/                VIEW – Swing; calls the backend, never imports db
    ├── DodgerGUI.java         window, menu, status bar, Timer, game over
    ├── GamePanel.java         paintComponent; key bindings
    └── HighScoresDialog.java  a JTable of DTOs

The view calls only the backend, and the backend hides the database. The backend – the model package – has two parts: the game (Game) and the leaderboard (Leaderboard). At the end of a game the view calls Leaderboard.save; from the menu it calls Leaderboard.top and gets HighScoreDto objects. Where the leaderboard lives – an SQLite file, MySQL, a list in memory – the view doesn't know and doesn't care: it never imports the db package.

Leaderboard knows no SQL either. It stores results through the HighScoreRepository interface, which JdbcHighScoreRepository implements – the repository from last session, unchanged. Game and Leaderboard don't know each other: the view connects them at the end of a game (game.getScore() → leaderboard.save). The layers are wired together in one place, main:

public static void main(String[] args) {
    SwingUtilities.invokeLater(() ->
            new DodgerGUI(new Leaderboard(new JdbcHighScoreRepository())));
}

The payoff: the game tests (GameTest) and the leaderboard tests (LeaderboardTest) run without a database, the repository tests run without a game, and swapping the database touches neither the game nor the view.

Frontend and backend

What we have called view and model is, in bigger systems, the frontend and the backend. The frontend is what the user sees and operates – here a Swing window, in a web app the page in the browser. The backend does the work: the rules, the data, the storage. In a web system it runs on a server and the frontend talks to it over the network, but the border is the same:

That is why the view must not reach through the backend to the database. If DodgerGUI wrote SQL itself, the UI and the storage would grow together and neither could be replaced or tested without the other. Larger systems repeat the same idea with more steps: rules in the middle, interfaces ("ports") facing outwards, replaceable adapters for the database, files and the network – I wrote about this for industry projects in Micromonoliths.

Game over: the key sequence

Everything in Dodger is driven by a Swing Timer. On every tick the view advances the model, repaints, updates the status bar – and checks whether the game is over:

private void tick() {          // DodgerGUI – the Timer calls this every TICK_MS
    game.tick();
    gamePanel.repaint();
    refreshStatus();
    if (game.isOver()) {
        timer.stop();
        gameOver();
    }
}

The code says the same as the figure:

private void gameOver() {
    String name = askName();                 // null if the player pressed Cancel
    if (name != null) {
        try {
            leaderboard.save(name, game.getScore());
        } catch (StorageException e) {
            showDatabaseError(e);            // a message – the program keeps running
        }
    }
    showHighScores();
    newGame();
}

Stopping the Timer first matters: the dialogs are modal, and without the stop the game would keep ticking behind them. askName keeps asking until it gets a valid name or the player presses Cancel, which means "don't save":

private String askName() {
    String message = "Game over! Your score: " + game.getScore() + "\nYour name:";
    while (true) {
        String name = JOptionPane.showInputDialog(frame, message, "Game over", JOptionPane.PLAIN_MESSAGE);
        if (name == null) {
            return null;                                  // Cancel: don't save
        }
        if (Leaderboard.isValidName(name)) {
            return name.strip();
        }
        message = "Please enter a name (1-" + Leaderboard.MAX_NAME_LENGTH + " characters):";
    }
}

When there is no database

The repository wraps every SQLException into a StorageException – a checked exception in the model package, so the view doesn't depend on java.sql. The backend passes it on, the view catches it, shows a message and carries on.

} catch (SQLException e) {      // in JdbcHighScoreRepository
    throw new StorageException("Could not save the score: " + e.getMessage(), e);
}

If the MySQL container isn't running, the game is still playable – only saving and the leaderboard fail. That is the right behaviour: a side feature failing must not bring the program down. The message includes e.getMessage(), which tells what went wrong: no server, wrong password, unknown database. Try it: switch Database.java to MySQL while the container is stopped and play until the end.

SQL injection

Suppose the name goes into the SQL by string concatenation:

// WRONG – never build SQL from user input
String sql = "INSERT INTO highscores (name, score) VALUES ('" + name + "', " + score + ")";
statement.executeUpdate(sql);

and the player types this as their name:

Robert'); DELETE FROM highscores; --

Then the database receives this – and reads it as two statements:

INSERT INTO highscores (name, score) VALUES ('Robert'); DELETE FROM highscores; --', 7)

The leaderboard is gone. A query works the same way: WHERE name = '" + name + "' with the name x' OR '1'='1 returns every row. This is SQL injection: user input executed as SQL. In web applications it is one of the most common and most damaging bugs (stolen data, deleted data) – and the defence is simple:

// RIGHT – the SQL text is a constant, the value goes in as DATA
private static final String INSERT =
        "INSERT INTO highscores (name, score, created_at) VALUES (?, ?, ?)";

PreparedStatement statement = connection.prepareStatement(INSERT);
statement.setString(1, name);     // still just a string, even if it contains SQL
statement.setInt(2, score);

A PreparedStatement sends the structure of the SQL (which statement, which columns) and the data to the database separately; the data never becomes part of the statement. The repository test checks exactly this:

@Test
public void nameWithSqlInItIsStoredAsPlainText() throws StorageException {
    String evil = "Robert'); DROP TABLE highscores; --";

    repository.save(new HighScore(evil, 5, T0));

    List<HighScore> top = repository.findTop(10);
    assertEquals(1, top.size());            // the table is still there
    assertEquals(evil, top.get(0).name());  // and the "name" is just text
}

The rule: everything that comes from the user – or from a file, or the network – goes into SQL through a ?. The SQL text itself is a constant in the code, with no concatenation in it.

Input validation is something else

The backend also strips the name and rejects it if it is empty or longer than 50 characters. That is not the protection against injection – that is the PreparedStatement in the repository. It is ordinary validation: we don't want empty names on the leaderboard, and VARCHAR(50) holds no more.

public void save(String name, int score) throws StorageException {   // Leaderboard
    if (!isValidName(name)) {
        throw new IllegalArgumentException("Invalid name: '" + name + "'");
    }
    repository.save(new HighScore(name.strip(), score, LocalDateTime.now()));
}

public static boolean isValidName(String name) {
    return name != null && !name.isBlank() && name.strip().length() <= MAX_NAME_LENGTH;
}

The check runs in two places, for two reasons. The view (askName) uses the same isValidName before saving and asks again – for the user: a sensible message, immediately, instead of an exception. The backend checks again and throws IllegalArgumentException – for the data: the backend can be called by other code than this view, so its check is the real one.

Keyboard: key bindings

The keyboard is handled with key bindings (InputMap + ActionMap), not with a KeyListener, for two reasons. A KeyListener only works while the panel has the focus, which it loses after you use the menu; a binding in WHEN_IN_FOCUSED_WINDOW mode works wherever the focus is inside the window. And pressing and releasing a key can be bound separately.

private void bindKeys() {                     // GamePanel
    for (Map.Entry<String, Direction> entry : KEYS.entrySet()) {   // "W" -> UP, "UP" -> UP, ...
        String key = entry.getKey();
        Direction direction = entry.getValue();
        bind("pressed " + key, "press-" + key, () -> game.press(direction));
        bind("released " + key, "release-" + key, () -> game.release(direction));
    }
}

private void bind(String keyStroke, String actionName, Runnable action) {
    getInputMap(JComponent.WHEN_IN_FOCUSED_WINDOW).put(KeyStroke.getKeyStroke(keyStroke), actionName);
    getActionMap().put(actionName, new AbstractAction() {
        @Override
        public void actionPerformed(ActionEvent e) {
            action.run();
        }
    });
}

The model receives directions (press(Direction), release(Direction)), and tick moves the ship in every held direction. So the ship moves as long as the key is held, and diagonally too (W + D). The model knows no keys: binding the arrow keys next to WASD, as Dodger does, doesn't change it.

private final Set<Direction> pressed = EnumSet.noneOf(Direction.class);

public void press(Direction direction)   { pressed.add(direction); }
public void release(Direction direction) { pressed.remove(direction); }

public void tick() {
    if (over) {
        return;
    }
    ticks++;
    for (Direction direction : pressed) {    // W + D held: up AND right – diagonal
        ship.move(direction, WIDTH, HEIGHT);
    }
    // ... objects fall, collisions, new objects
}

Time: ticks, not the clock

Game measures time in ticks: tick counts how often it was called, and the elapsed time is ticks × TICK_MS.

public long getElapsedMillis() {
    return ticks * TICK_MS;          // not System.currentTimeMillis()
}

This is independent of the system clock, which pays off three times: in a test, calling tick 50 times makes one second "pass"; the time doesn't grow after the game is over; and when the Timer stops because the leaderboard dialog is open, the game clock stops with it.

@Test
public void elapsedTimeIsCountedInTicks() {
    Game game = quietGame();                // a Random that never spawns anything
    for (int i = 0; i < 50; i++) {
        game.tick();
    }
    assertEquals(50L * Game.TICK_MS, game.getElapsedMillis());   // 1 second
}

The Color clicker used the clock (System.currentTimeMillis), because its game doesn't advance in ticks. In a game driven by a Timer, counting ticks is simpler.

Sprites drawn with shapes

In the model, a sprite is a rectangle – x, y, width, height – and a collision is two rectangles overlapping:

public boolean intersects(Sprite other) {   // Sprite – the model
    return x < other.x + other.width && other.x < x + width
        && y < other.y + other.height && other.y < y + height;
}

The view (GamePanel.paintComponent) decides whether that rectangle looks like a triangle, a circle or a star. The model has no idea.

private void drawShip(Graphics2D g2, Ship ship) {   // GamePanel – the view
    Polygon triangle = new Polygon();
    triangle.addPoint(ship.getX() + ship.getWidth() / 2, ship.getY());   // tip
    triangle.addPoint(ship.getRight(), ship.getBottom());
    triangle.addPoint(ship.getX(), ship.getBottom());
    g2.setColor(SHIP);
    g2.fillPolygon(triangle);
}

If you want images instead of shapes, the model doesn't change, only the draw… methods. Load the image once – from src/main/resources/images/, so it ends up in the jar – and draw it into the sprite's rectangle. The Arkanoid example next session does this.

// later, with images instead of shapes – only the view changes
Image shipImage = ImageIO.read(getClass().getResource("/images/ship.png"));   // once, not per frame
g2.drawImage(shipImage, ship.getX(), ship.getY(), ship.getWidth(), ship.getHeight(), null);

Tests

GameTest checks the rules: the ship moves while a direction is held, diagonally too, and stops at the edges; objects fall at their speed and disappear at the bottom; a star is +1 point, an asteroid −1 life, an object passing beside the ship doesn't collide; losing the last life ends the game, after which nothing happens; time is counted in ticks.

Two tools tame the randomness. Game takes a Random in its constructor, so a test can pass a fixed seed – or a Random whose nextDouble always returns 1.0, so nothing ever spawns. A package-private spawn(FallingObject) then puts a specific object above the ship. The "asteroid hits the ship" test doesn't wait for luck.

A database under a finished program

ColorclickerDB is the optimised Color clicker with its model unchanged – Board, Field, Position and all their tests – plus a leaderboard. This is what you do when you put a database under any existing game. What changes?

The callback keeps the board panel ignorant of the database: it only announces "the board is full", and the window decides what that means.

public BoardGUI(int boardSize, Runnable onGameOver) {    // ColorclickerDB
    this.onGameOver = onGameOver;
    // ...
}

// in click(), where the old version showed a JOptionPane:
if (board.isOver()) {
    timer.stop();
    onGameOver.run();          // BoardGUI doesn't know what happens next
}

// ColorClickerGUI creates it:
boardGUI = new BoardGUI(boardSize, this::gameOver);

Here the result is a time, and smaller is better; and only results on the same board size are comparable. So the table and the query differ from Dodger's:

CREATE TABLE IF NOT EXISTS results (
    name       VARCHAR(50) NOT NULL,
    size       INTEGER     NOT NULL,     -- 5, 10, 15 or 20
    millis     BIGINT      NOT NULL,     -- the time: smaller is better
    created_at TIMESTAMP   NOT NULL
);

SELECT name, size, millis, created_at FROM results
WHERE size = ?                           -- only results on the same board size
ORDER BY millis ASC, created_at ASC      -- fastest first, earlier wins a tie
LIMIT ?;

One step further – and what not to build

For the assignment, everything above is enough. A few things a production application would do differently, so you know where the next step is (and don't build it for the assignment):

• —Connection pool. The repository opens and closes a connection per operation. For a desktop game that saves once a minute, that is perfect. A web server asking a hundred times a second would take ready connections from a pool – only connect() would change.
• —Transactions. When one operation is several SQL statements that must all happen or none, setAutoCommit(false) … commit() / rollback() groups them. Task 4 of the assignment (Tron) is exactly that: "increase the winner's count, or insert a row if they are new".
• —Passwords in the code. Fine for a practice project whose container uses the same values; in production the credentials come from environment variables or a config file and never get into git.
• —Indexes. With millions of rows, ORDER BY score DESC LIMIT 10 needs an index on the column, or the database scans every row. A leaderboard never gets there.
• —Logging. Instead of System.err and dialogs, a logging library would write errors to a file with timestamps.
• —Migrations. CREATE TABLE IF NOT EXISTS covers the first version. Adding a column later means ALTER TABLE, run in order and versioned by a migration tool. In the assignment: if the table changes, delete the .db file and let the program recreate it.

try (Connection connection = connect()) {
    connection.setAutoCommit(false);                 // the two statements are one unit
    try (PreparedStatement update = connection.prepareStatement(
            "UPDATE wins SET wins = wins + 1 WHERE name = ?")) {
        update.setString(1, name);
        if (update.executeUpdate() == 0) {
            // no such row yet: INSERT INTO wins (name, wins) VALUES (?, 1)
        }
        connection.commit();
    } catch (SQLException e) {
        connection.rollback();
        throw e;
    }
}

String password = System.getenv("DB_PASSWORD");   // not a literal in the code

The third assignment

The shared requirements of the third assignment (beadandó), and where Dodger meets them:

Tips per task – what the state of the model is, what one tick or step does, when the game ends and what goes into the database:

Level files

In tasks 1 and 3 (and maybe your own), the playing field comes from files. The simplest format is a text grid: one line per row, one character per cell.

##########
#D....P..#
#.##.###.#
#.#..H...#
#.#.####.#
#P......P#
##########

Put the files in src/main/resources/levels/ (level01.txt … level10.txt). Maven packs them into the jar, and the program reads them as resources, wherever it is started from:

try (InputStream in = getClass().getResourceAsStream("/levels/level01.txt");
     BufferedReader reader = new BufferedReader(new InputStreamReader(in, StandardCharsets.UTF_8))) {
    List<String> lines = reader.lines().toList();
    // ... build the grid: lines.get(row).charAt(col)
}

Practice

@Before
public void setUp() throws IOException {
    file = Files.createTempFile("scores", ".db");             // an empty file per test
    repository = new JdbcHighScoreRepository("jdbc:sqlite:" + file, null, null);
}

@After
public void tearDown() throws IOException {
    Files.deleteIfExists(file);
}

bind("pressed P", "pause", () -> {
    if (timer.isRunning()) timer.stop(); else timer.start();
});

// Database.java – point it at a server that isn't running
public static final String URL = "jdbc:mysql://localhost:3306/progtech";

List<HighScoreDto> top(int size, int limit) throws StorageException;   // Leaderboard

statement.setInt(1, size);     // WHERE size = ?
statement.setInt(2, limit);    // LIMIT ?

leaderboard.addWin("Anna");
leaderboard.addWin("Anna");
leaderboard.addWin("Bence");
// expected: Anna 2, Bence 1 – and still exactly two rows

Summary

Frontend / backend

what the user sees (the view) / what does the work (rules, data, storage)

Repository

an interface the backend stores through; the JDBC implementation is the only class with SQL

DTO

the object the view receives – what it needs to display, e.g. a row with its rank

SQL injection

user input executed as SQL because it was concatenated into the statement

Key binding

InputMap (key stroke → name) + ActionMap (name → action); works wherever the focus is in the window

Tick

one call of the model's tick() by the Timer; game time = ticks × TICK_MS

• —The view calls the backend (Game, Leaderboard) and receives DTOs; it never imports the db package.
• —Game over: stop the Timer, ask the name, save, show the top 10, new game.
• —SQLException becomes StorageException in the repository; the view shows a message and the game goes on.
• —Everything from the user goes into SQL through ? in a PreparedStatement. Validation is a separate concern.
• —Key bindings call model methods with directions; time is ticks, so tests control it.
• —The table fits the game: decide what the result is, which way is better, and what else the row needs.