One plug, many sockets
You travel to another country. The wall sockets are different. You buy a travel adapter. Now your charger works.
Databases (organised stores of data) have the same problem. MySQL, Oracle and others all work in a different way. You do not want to rewrite your program for each one.
So there are standard layers. Two famous ones are JDBC and ODBC.
Key words
| Word | What it means |
|---|---|
| Database | An organised store of data that programs can search and change. |
| SQL | The language used to ask a database for data and to change it. |
| Query | A question or instruction written in SQL. |
| API | A set of ready-made tools one program offers to another. |
| JDBC | Java Database Connectivity. The standard way for Java programs to use a database. |
| ODBC | Open Database Connectivity. A standard way for programs in many languages to use a database. |
| Driver | A small piece of software that knows how to talk to one kind of database. |
| Driver manager | Software that picks the right driver for your connection. |
| Connection | An open link between your program and the database. |
| Statement | A part of your code that sends one query to the database. |
| Placeholder | A ? in a query. It marks the place where a value goes later. |
| Prepared statement | A statement with ? placeholders. The values are sent separately. It is safer and faster. |
| Result set | The rows the database sends back for a query. |
| Data source | A saved set of connection details with a name, used by ODBC. |
The cells students fill in are left blank in this preview.
Quick check: the layers
Have a go
Quick check: the layers
3 questionsHave a go: your answers are marked here and then forgotten. Sign in if you would like your score kept, and to pick up where you left off.Sign in to keep your score
Your answers are marked here and are not kept. A college account keeps every student’s marks.
JDBC in Java (read it)
import java.sql.*;
public class Shop {
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://db:3306/shop";
try (Connection conn = DriverManager.getConnection(url, "student", "student")) {
String sql = "SELECT name, price FROM products WHERE price > ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setDouble(1, 10.0);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getString("name") + " " + rs.getDouble("price"));
}
}
}
}
}
}
The JDBC steps, line by line
| Step | JDBC | The same idea in Python |
|---|---|---|
| 1. Connect | DriverManager.getConnection(url, user, password) | sqlite3.connect(file) |
| 2. Prepare | conn.prepareStatement(sql) | the query with a ? placeholder |
| 3. Give the values | ps.setDouble(1, 10.0) | the tuple (10.0,) |
| 4. Run | ps.executeQuery() | conn.execute(query, values) |
| 5. Read the rows | while (rs.next()) ... | for row in result: ... |
| 6. Close | the try block closes everything | conn.close() |
The cells students fill in are left blank in this preview.
Try it: the same steps in Python
import sqlite3
conn = sqlite3.connect(":memory:") # 1. connect
conn.execute("CREATE TABLE products (name TEXT, price REAL)")
conn.executemany("INSERT INTO products VALUES (?, ?)", [("Pen", 1.5), ("Bag", 24.0), ("Lamp", 12.0)])
query = "SELECT name, price FROM products WHERE price > ?" # 2. prepare
result = conn.execute(query, (10.0,)) # 3 and 4
for name, price in result: # 5. read the rows
print(name, price)
conn.close() # 6. close
ODBC from Python (read it)
# Needs the pyodbc package and an ODBC driver installed.
import pyodbc
import os
password = os.environ["DB_PASSWORD"] # the password comes from a setting, not from the code
conn = pyodbc.connect("DSN=ShopDSN;UID=student;PWD=" + password)
cursor = conn.cursor()
cursor.execute("SELECT name FROM products WHERE price > ?", 10.0)
for row in cursor.fetchall():
print(row.name)
conn.close()