This weekend I built a simple SQL exercise to explain the way to embed external libraries into weecod. Truly nothing extraordinary when you’ll see how you can do it in less than 30 lines of code. See it in action: http://weecod.com/sqldemo
Step 1: Create a new exercise
I use Java as the backend language to play with SQL. Go to your Dashboard > Question Editor > Create a new Java programming exercise.
Step 2: Add the external dependencies
I've packaged two libraries into a single zip file:- HSQLDB, a relational database written in Java (BSD license). It's lightweight, fast and SQL standards-compliant.
- A simple helper class to display the results of a SQL statement in the console (to test a solution) and to validate two SQL statements return the same result (to validate a solution). You can obtain the source code from the zip file.
Step 3: A solution skeleton
class SQL {Candidates will have to update the “query” string to provide their own solution.
static String query = "SELECT * FROM customer";
}
Step 4: Code to test a solution
import java.sql.*;Here I create a standalone database and a table for customers. This piece of code perform some inserts to allow the candidate to test the query under practical conditions of use. The result is displayed in the console. Simple.
import com.weecod.sql.*;
public class Tester {
public static void main(String[] args) throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:mydb", "sa", "");
conn.createStatement().executeUpdate("CREATE TABLE customer (firstname VARCHAR(32), lastname VARCHAR(32))");
PreparedStatement insert = conn.prepareStatement("INSERT INTO customer VALUES (?,?)");
String[][] inserts = { { "Andrew", "Walton" }, { "Lili", "Ma" }, { "Laurent", "Giroux" } };
for (String[] d : inserts) {
insert.setString(1, d[0]);
insert.setString(2, d[1]);
insert.executeUpdate();
}
SQLHelper.displayResults(SQL.query, conn);
//##DISPLAY_BEGIN##
//##DISPLAY_END##
}
}
Step 5: Code to validate a solution
import java.sql.*;
import com.cartser.codemachine.java.*;
import com.cartser.codemachine.java.lafouine.*;
import com.weecod.sql.*;
public class Validator extends WeecodValidator {
Connection conn;
String validSelect = "SELECT firstname FROM customer WHERE firstname LIKE 'L%' ORDER BY firstname";
public void validate() throws Exception {
prepareDB();
SQLHelper.validateResults(SQL.query, validSelect, conn);
}
void prepareDB() throws SQLException {
conn = DriverManager.getConnection("jdbc:hsqldb:mem:mydb", "sa", "");
conn.createStatement().executeUpdate("CREATE TABLE customer (firstname VARCHAR(32), lastname VARCHAR(32))");
PreparedStatement insert = conn.prepareStatement("INSERT INTO customer VALUES (?,?)");
String[][] inserts = { { "Laurie", "Akia" }, { "Andrew", "Walton" }, { "Lara", "Croft" } };
for (String[] d : inserts) {
insert.setString(1, d[0]);
insert.setString(2, d[1]);
insert.executeUpdate();
}
}
}
It’s quite similar to the code displayed in the previous step, except that this time the solution is checked to see if it works correctly: the result from a valid query (validSelect) is compared to the result from the candidate’s query. If they’re different SQLHelper.validateResults throws an exception and the solution is invalidated.
No comments
Post a Comment