databases.md

Database Information

CSC205, CSC220, and CSC240 are not database courses, but we will briefly cover databases if we have time. We will cover a very small subset of SQL - CREATE TABLE, INSERT, SELECT, and possibly UPDATE - just enough to be able to do something non-trivial in our programs.

General Database Links

• Things I Wished More Developers Knew About Databases

SQL Tutorials

• An non-technical introduction to SQL
• Another more complete SQL tutorial
• VIDEO Intro to SQL from Khan Academy

Other SQL links

• SQL Concepts from A to Z. That site also has an expaination of SQL Joins without using Venn Diagrams
• SQL queries don't start with SELECT has some good information about how SQL queries work
• A Visual Explaination of SQL Joins
• A guide to optimizing queries

JDBC

• Jenkov's JDBC Tutorial
• 10 common mistakes Java developers make when writing SQL
• 10 more common mistakes Java developers make when writing SQL

Setting up SQLite in Eclipse

1. Download the SQLite jar
2. Install the JAR file in your project.
3. Left click on your project and select Build Path -> Configure Build Path
4. Click the Libraries tab and click "Add External JAR"
5. Navigate to the directory you downloaded the SQLite JAR file and select it.
6. Click OK
7. If the installation worked, try to execute the class below. It should state that it made the connection correctly.

import java.sql.Connection;
import java.sql.DriverManager; import java.sql.SQLException;

public class SQLiteTest {

    public static void main(String args[]) {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        }
        Connection conn = null;
        try {
                conn = DriverManager.getConnection("jdbc:sqlite:csc205.db");
                System.out.println("Opened database connection!");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println(e.getClass().getName() + ": " + e.getMessage());
        }
        finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println(e.getMessage());
                }
            }
        }
    }
}