[Assignment description]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
// This program is meant to illustrate how one can use JDBC to update the
// database as well as execute queries. It creates and queries a table with
// names and ages of the Von Trapp children from The Sound of Music.
public class JdbcSample {
private static String[] VON_TRAPPS = { "Liesl 16", "Fredrick 14",
"Louisa 13", "Kurt 11", "Brigitta 10", "Marta 7", "Gretl 5" };
public static void main(String[] args) {
Connection conn = openConnection();
try {
createTable(conn);
populateTable(conn);
PreparedStatement query = conn.prepareStatement(
"SELECT name, age FROM choir WHERE name LIKE ?");
Scanner in = new Scanner(System.in);
while(true) {
System.out.print("Child name? ");
query.setString(1, in.nextLine()); // Note JDBC counts from 1!
processQuery(conn, query);
}
} catch(SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
}
}
private static Connection openConnection() {
try {
Class.forName("org.sqlite.JDBC"); // This loads the driver
} catch (ClassNotFoundException e1) {
System.err.println("SqliteJDBC library not found. "
+ "Perhaps you need to set the build path?");
System.exit(-1);
}
try {
// Note that the URL below specifies the filename for storing the DB
return DriverManager.getConnection("jdbc:sqlite:choir.db");
} catch (SQLException e) {
System.err.println("Could not connect to DBMS.");
System.exit(-1);
}
return null; // we'll never get here, but the compiler insists
}
private static void createTable(Connection conn) throws SQLException {
Statement stmt = conn.createStatement();
stmt.executeUpdate("CREATE TABLE choir(name VARCHAR(10), age INTEGER)");
}
private static void populateTable(Connection conn) throws SQLException {
PreparedStatement insert = conn.prepareStatement(
"INSERT INTO choir (name, age) VALUES (?, ?)");
for (String child : VON_TRAPPS) {
String[] tokens = child.split(" ");
insert.setString(1, tokens[0]); // note JDBC counts from 1!
insert.setInt(2, Integer.parseInt(tokens[1]));
int updated = insert.executeUpdate();
if (updated == 0) {
System.err.println("Failed to insert " + tokens[0]);
}
}
}
private static void processQuery(Connection conn, PreparedStatement query)
throws SQLException {
ResultSet rows = query.executeQuery();
boolean found = false;
while (rows.next()) {
found = true;
String name = rows.getString(1); // Again, it counts from 1!
int age = rows.getInt(2);
System.out.printf("%-10s %2d\n", name, age);
}
if (!found) {
System.out.println("No such child found.");
}
rows.close();
}
}
[Assignment description]