JAVA 

JDBC Programming

Goal: Write at least two classes that will store and retrieve data of your choice. Deliverables: An Eclipse project that has two classes, which load and display your data. Details: Choose a data schema that contains at least five columns, one of which is a primary key. Create a .csv file with at least eight data lines. The first row optionally contains the column names. Write a Load class with a main method that loads the data from the .csv file into the database table. Download the SQLite3 database driver sqlite-jdbc-3.8.7.jar to the folder where you are keeping your Java projects.

Select Projects >> Properties >> Java Build Path >> Libraries Tab >> Add External Jars... Button. Browse for and

open sqlite-jdbc-3.7.2.jar;

click OK. Write a Display class that inputs a primary key,

then displays on the screen the corresponding data in a row of the table.

Revent Example

 LoadKids.java 

// LoadKids Example // Source code file LoadKids.java // Load data from CSV file kids.txt // into the database table kids import java.io.File; import java.io.FileNotFoundException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; public class LoadKids { public static void main(String[] args) { Connection c = null; Statement s = null; Scanner fromFile = null; String sql1 = null, sql2 = null; String line = null, name = null, gender = null; String[ ] fields; int age = 0; try { // Define Connection and Statement objects. Class.forName("org.sqlite.JDBC"); c = DriverManager.getConnection("jdbc:sqlite:kids.db"); s = c.createStatement(); // Instantiate scanner to read from file. fromFile = new Scanner(new File ("kids.txt")); // Create kids table. sql1 = "create table if not exists " + "kids(kidid integer, " + "name varchar(10), " + "gender varchar(1), " + "age integer);"; System.out.println("sql1: " + sql1); s.executeUpdate(sql1); // Read and throw away header line. fromFile.nextLine( ); // Populate kids table. for (int id = 1001; fromFile.hasNextLine( ); id++) { line = fromFile.nextLine( ); fields = line.split(" "); name = fields[0].trim( ); gender = fields[1].trim( ); age = Integer.parseInt(fields[2].trim( )); sql2 = String.format( "insert into kids (kidid, name, gender, age) " + "values (%d, '%s', '%s', %d);", id, name, gender, age); System.out.println(sql2); s.executeUpdate(sql2); } c.close( ); } catch (FileNotFoundException e) { System.out.println("File queries.sql not found."); System.err.println( e.getClass().getName() + ": " + e.getMessage() ); } catch(SQLException e) { System.out.println("SQLException."); System.err.println( e.getClass().getName() + ": " + e.getMessage() ); } catch (ClassNotFoundException e ) { System.err.println( e.getClass().getName() + ": " + e.getMessage() ); } finally { fromFile.close( ); } } } 

DisplayKids.java 

// DisplayKids Example // Source code file DisplayKids.java // Display data from kids table // given the id. import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; public class DisplayKids { public static void main(String[] args) { Connection c = null; Statement s = null; ResultSet rs = null; int id = 0, age = 0; String sql = null, name = null, gender = null; Scanner fromKeyboard = new Scanner(System.in); try { // Define Connection and Statement objects. Class.forName("org.sqlite.JDBC"); c = DriverManager.getConnection("jdbc:sqlite:kids.db"); s = c.createStatement(); while (id != -1) { // Read id and display corresponding table row. System.out.print("Enter desired id: "); id = fromKeyboard.nextInt( ); sql = "select name, gender, age from kids " + "where kidid = " + id + ";"; System.out.println(sql); rs = s.executeQuery(sql); while (rs.next( )) { name = rs.getString("name"); gender = rs.getString("gender"); age = rs.getInt("age"); System.out.println("Name: " + name); System.out.println("Gender: " + gender); System.out.println("Age: " + age); } } } catch(SQLException e) { System.out.println("SQLException."); System.err.println( e.getClass().getName() + ": " + e.getMessage() ); } catch (ClassNotFoundException e ) { System.err.println( e.getClass().getName() + ": " + e.getMessage() ); } finally { fromKeyboard.close( ); } } }