COMP9120 Chap.8 Database Application Development
Database Application Development
Database Application Development is the Week-7 topic of COMP9120 Database Management Systems at the University of Sydney, where SQL stops living in a terminal and starts running inside real Java or Python programs. You learn the call-level APIs (JDBC and the Python DB-API), why prepared statements defeat SQL injection, how stored procedures cut network round trips, and where indexes speed up the back end.
It is the foundation for Assignment 2 (a 13% group project), and the lecturer flags it as out of scope for the final exam and the in-class quiz.
What this chapter covers
- 01
Why the impedance mismatch forces a call-level API between set-oriented SQL and record-oriented code
- 02
The four-step JDBC model: connect, create a Statement, execute, iterate the ResultSet cursor
- 03
The Python DB-API workflow with psycopg2: connection, cursor, execute, fetchone / fetchmany / fetchall
- 04
How SQL injection works and why string concatenation of user input is never safe
- 05
Prepared / parameterised statements: bind placeholders, reuse the query plan, block injection
- 06
Stored procedures in PL/pgSQL with IN / OUT / INOUT parameters, called via CallableStatement or callproc
- 07
How moving logic server-side reduces network round trips between client and database
- 08
Indexes as an access path: B+-tree vs hash, clustered vs unclustered, and when to add one
- 09
Error handling: SQLException state codes in Java and the psycopg2 exception hierarchy in Python
Cutting network round trips with a stored procedure
- +1Client-side, each SQL statement is its own request/reply, so one order = 3 round trips.
- +1Total client-side round trips = 3 statements x 200 orders = 600.
- +1A stored procedure bundles the three statements into one server-side routine invoked with a single call.
- +1With the procedure, one order = 1 round trip, so 200 orders = 200 round trips.
- +1Round trips saved = 600 - 200 = 400.
- +1That is a 400/600 = 66.7% reduction, so latency and network load fall (fewer round trips = faster).
Key terms
- Impedance mismatch
- The gap between set-oriented SQL (which returns whole relations) and a record-oriented host language such as Java or Python; bridged with a call-level API and a cursor that walks results one row at a time.
- JDBC
- Java Database Connectivity: a DBMS-independent Java API. The workflow is acquire a Connection, create a Statement, execute a query, and iterate the ResultSet cursor, catching SQLException on error.
- Python DB-API
- Python's standard database interface (PEP 249), implemented per engine (psycopg2 for PostgreSQL). You open a connection, get a cursor, execute a query, then fetchone / fetchmany / fetchall the rows.
- Prepared statement
- A parameterised query whose plan is prepared once, then executed with bound host variables supplied through placeholders (? in JDBC, %s or %(name)s in the Python DB-API). It blocks SQL injection and allows plan reuse.
- SQL injection
- An attack where user input is concatenated into a query string so that supplied text (for example ' OR '1'='1) changes the query's meaning. Prepared statements prevent it by never treating parameter values as SQL.
- Stored procedure
- Application logic stored and run inside the DBMS, written in an SQL extension such as PostgreSQL's PL/pgSQL. Parameters may be IN, OUT or INOUT; it reduces network round trips and adds an abstraction layer.
- CallableStatement / callproc
- The mechanisms that invoke a stored procedure: JDBC's CallableStatement with prepareCall("{call proc(?,?)}") and registerOutParameter, or the Python cursor's callproc() method.
- Index (clustered vs unclustered)
- An access path mapping search-key values to records. A B+-tree serves equality and range; a hash serves equality only. A clustered index stores data in key order (cheap ranges); an unclustered one can cost about one I/O per matching row.
Database Application Development FAQ
Is Database Application Development on the COMP9120 final exam?
No. The Week-7 lecturer states this material is not covered in the final exam or the in-class quiz; the quiz covers Weeks 1-9 except Week 7. It is assessed through Assignment 2, a 13% group project that builds a client interface plus an advanced back end with stored procedures and indexes. Confirm the exact scope and dates on Canvas for your offering.
Do I need Java or Python for Assignment 2?
Assignment 2 requires either Java (with JDBC) or Python (with the DB-API / psycopg2); you pick one and learn the language yourself. The database concepts are the same either way: connect, execute, iterate results, use prepared statements, and add stored procedures and indexes on the server side.
Can AI help me with database application development in COMP9120?
Yes, as a tutor rather than an answer service. Sia can explain, step by step, how a prepared statement defeats an injection string, why a stored procedure saves round trips, or how a B+-tree index differs from a hash index, and can walk through a small worked example. It will not write your Assignment 2 code for you or promise a specific grade; the point is to understand the API so you can build and debug your own submission.
Exam move
Treat this chapter as build-and-test, not memorise: set up PostgreSQL with pgAdmin early and get a tiny Java (JDBC) or Python (psycopg2) program to connect, run one SELECT, and loop over the results before you touch the assignment logic.
Then practise the three security-critical moves in isolation: rewrite a concatenated query as a prepared statement, wrap a multi-statement update in a PL/pgSQL stored procedure and call it, and add an index to a column that appears in a WHERE or JOIN.
Because this topic is assessed by Assignment 2 rather than the timed exam, the marks reward working, secure code and a sensible back end, so spend your effort making the program run correctly and handling errors gracefully rather than reciting definitions.
Working through Database Application Development in COMP9120? Sia is AskSia’s AI Computer Science tutor — ask any COMP9120 Database Application Development question and get a clear, step-by-step explanation grounded in how COMP9120 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
