Code for basic SQL using JDBC and derby

Details

This code shows the basic elements of SQL with JDBC and the derby database installed with JDK1.7, or available at Oracle. Make sure the jar file JDKInstallDirectory/db/lib/derby.jar is in your CLASSPATH. Almost every line needs a try-catch block around it, but these are excluded here for clarity. Most actions throw a SQLException, except Class.forName which throws a ClassNotFoundException.

Original author/s: Sun/Oracle
Original location/s: Drawn from API and JDBC Getting Started guide
Adapted by: Andy Evans
License: unknown
More info:

 

Imports and instance variables

	import java.sql.*;

 

Code

	// Make the driver available
	Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
	// or	
	DriverManager.registerDriver(new org.apache.derby.jdbc.EmbeddedDriver());
	
	// Connect to a pre-existing database:
	String strUrl = "jdbc:derby:m:\\databases\\Results";
	// or make a database:
	String strUrl = "jdbc:derby:m:\\databases\\Results;create=true";

	Connection conn = DriverManager.getConnection(strUrl);

	// Create a table with a standard statement:
	String createTable = 
	    "CREATE TABLE Results (" +
				"Address varchar(255)," +
				"Burglaries int" + 
			")";
	
	Statement st = conn.createStatement();
	st.execute(createTable);
	
	// Making a scollable updating statement
	Statement st2 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	
	// Running a query.
	ResultSet rs = st2.executeQuery("SELECT Address, Burglaries FROM Results");
	while (rs.next()) {
		System.out.println(rs.getString("Address") + " " + rs.getInt("Burglaries"));
	}
	
	// Closing 
	rs.close();
	st.close();
	st2.close();
	conn.close();