Java File I/O, CSV, and Database Demo Tutorial

Java File I/O, CSV, and Database Demo

This example demonstrates how to read and write text files, parse CSV data, and perform basic database operations using JDBC with UCanAccess and an MS Access database.

Required Imports

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

Class Declaration and Constants

public class Demo05 {

    private static final String IN_FILE = "./input.txt";
    private static final String OUT_FILE = "./output.txt";
    private static final String CSV_FILE = "./employees.csv";

    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

    private static final String MS_FILE = "./Employees.accdb";
    private static final String DB_URL = "jdbc:ucanaccess://" + MS_FILE;
    private static final String JDBC_DRIVER = "net.ucanaccess.jdbc.UcanaccessDriver";

File I/O Demo

The fileIODemo() method reads each line from an input file, prints it to the console with a line number, and writes the same output to an output file.

public static void fileIODemo() {

    BufferedReader br = null;
    // FileReader fr = null;
    PrintWriter pw = null;

    try {

        // Step 1: open a file
        // fr = new FileReader(IN_FILE);
        // br = new BufferedReader(fr);
        br = new BufferedReader(new FileReader(IN_FILE));
        pw = new PrintWriter(new File(OUT_FILE));

        String line = "";
        int count = 0;

        // Step 2: process the file
        // line = br.readLine();
        // while (line != null) {
        //     System.out.println(count + ": " + line);
        //     count++;
        //     line = br.readLine();
        // }
        while ((line = br.readLine()) != null) {
            System.out.println(count + ": " + line);
            pw.println(count + ": " + line);
            count++;
        }

        // Step 3: close the file
        br.close();
        pw.close();

    } catch (IOException e) {
        e.printStackTrace();
    }

}

CSV Demo

The csvDemo() method reads a CSV file where each line contains a name, employee number, and salary. Each record is converted into an Employee object and stored in an ArrayList.

public static void csvDemo() {

    BufferedReader br = null;
    ArrayList<Employee> employees = new ArrayList<Employee>();

    try {
        br = new BufferedReader(new FileReader(CSV_FILE));
        String line;

        while ((line = br.readLine()) != null) {
            String[] tokens = line.split(",");
            String n = tokens[0];
            String e = tokens[1];
            double s = Double.parseDouble(tokens[2]);
            Employee emp = new Employee(n, e, s);
            employees.add(emp);
        }

        br.close();

    } catch (IOException e) {
        e.printStackTrace();
    }

    // for (Employee emp : employees) {
    //     System.out.println(emp);
    // }
    for (int i = 0; i < employees.size(); i++) {
        Employee emp = employees.get(i);
        System.out.println(emp);
    }

}

Database Operations

The following methods demonstrate how to connect to a Microsoft Access database through UCanAccess and perform CRUD operations.

• initDB(): Loads the JDBC driver, opens a connection, and creates a statement.
• readDB(): Runs a SELECT query and prints each record.
• insertDB(): Inserts a new employee record.
• updateDB(): Updates the salary of an existing employee.
• deleteDB(): Deletes an employee record.
• closeDB(): Closes the result set, statement, and connection.

public static void initDB() {
    try {
        Class.forName(JDBC_DRIVER);
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    try {
        connection = DriverManager.getConnection(DB_URL);
        statement = connection.createStatement();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static void readDB() {
    String sqlStr = "SELECT * FROM EMPLOYEE";

    try {
        resultSet = statement.executeQuery(sqlStr);

        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            String n = resultSet.getString("Name");
            String e = resultSet.getString("ENumber");
            double s = resultSet.getDouble("Salary");
            Employee emp = new Employee(n, e, s);
            System.out.println("Record #" + id + ": " + emp);
        }

    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static void closeDB() {
    try {
        if (resultSet != null) {
            resultSet.close();
        }
        if (connection != null) {
            connection.close();
        }
        if (statement != null) {
            statement.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static void insertDB() {
    Employee emp = new Employee("Michael Ma", "098765", 100000);
    String sqlStr =
        "INSERT INTO EMPLOYEE (NAME, ENUMBER, SALARY) VALUES (" +
        "'" + emp.getName() + "'" + ", " +
        "'" + emp.getEid() + "'" + ", " +
        emp.getSalary() + ")";
    try {
        statement.executeUpdate(sqlStr);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static void updateDB() {
    // String sqlStr = "UPDATE EMPLOYEE SET SALARY = " + 100000 +
    //         " WHERE ENUMBER='" + "023456" + "'";
    String sqlStr = "UPDATE EMPLOYEE SET SALARY = " + 100000 +
            " WHERE NAME = '" + "Ivan Wong" + "'";
    // System.out.println(sqlStr);
    try {
        statement.executeUpdate(sqlStr);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static void deleteDB() {
    String sqlStr = "DELETE FROM EMPLOYEE WHERE NAME = '" +
            "Simon Li" + "'";
    // System.out.println(sqlStr);
    try {
        statement.executeUpdate(sqlStr);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

public static void dbDemo() {
    initDB();
    // insertDB();
    // updateDB();
    deleteDB();
    readDB();
    closeDB();
}

public static void main(String[] args) {
    // fileIODemo();
    // csvDemo();
    dbDemo();
}

}