9.602
Working with Databases in Java

Introduction

Java programs can connect to virtually any relational (and many non-relational) databases as well as tabular data in files. This tutorial provides an overview of the different approaches to working with data in databases.

Sample Code

The sample code below shows how to connect to a SQLite database using JDBC and issue a SQL SELECT statement to retrieve data and then access the result set. However, the code is procedural and not object-oriented. In practice, one must create an object-relational layer and saving and loading of data should be done within the object through dedicated methods and in the constructor.

SQLite Database Java Source Code

Running the code requires downloading and importing the SQLite package.

---

/*
 * Example: Connect to SQLite Database
 *
 * Requires SQLite JDBC Driver from 
 * https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc
 * to be added to CLASSPATH or IDE Libraries
 */

package sqliteconnect;

/**
 *
 * @author Martin Schedlbauer, PhD
 */

import java.sql.*;

public class SQLiteConnect {
    
    public static Connection connect (String db) 
    {
        
        try{
            Class.forName("org.sqlite.JDBC");
        }catch(ClassNotFoundException e) {
            System.out.println("jdbc driver missing");
        }
        
        Connection conn = null;
        try {
            // db parameters
            String url = "jdbc:sqlite:" + db;
            // create a connection to the database
            conn = DriverManager.getConnection(url);
            
            System.out.println("Connection to SQLite has been established.");
            
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        
        return (conn);
    }
    
    public static ResultSet retrieveCourses(Connection conn) 
    {
        ResultSet rs = null;

        try {
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM Courses");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return (rs);
    }
    
    public static ResultSet retrieveCourse(Connection conn, int cid) 
    {
        ResultSet rs = null;

        try {
            String sql = "SELECT * FROM Courses WHERE cid = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt (1, cid);
            rs = ps.executeQuery();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return (rs);
    }
    
    public static int addCourse(Connection conn, 
                                int cid, String title, String dept, int cr) 
    {
        int n = 0;

        try {
            String insertSQL = "INSERT INTO Courses VALUES(?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(insertSQL);
            ps.setInt(1,cid);
            ps.setString(2,title);
            ps.setString(3,dept);
            ps.setInt(4,cr);
            n = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return (n);
    }
    
    public static int removeCourse(Connection conn, int cid) 
    {
        int n = 0;

        try {
            String updateSQL = "DELETE FROM Courses WHERE cid = ?";
            PreparedStatement ps = conn.prepareStatement(updateSQL);
            ps.setInt(1,cid);
            n = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return (n);
    }
    
    public static void printResultSet (ResultSet rs)
    {
        try {
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnsNumber = rsmd.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= columnsNumber; i++) {
                    if (i > 1) {
                        System.out.print(",  ");
                    }
                    String columnValue = rs.getString(i);
                    System.out.print(columnValue + " " + rsmd.getColumnName(i));
                }
                System.out.println("");
            }

            while (rs.next()) {
                String title = rs.getString("title");
                System.out.println(title);
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
    
    public static void main(String[] args) 
    {
        System.out.println("connecting to sqlite db...");
        
        String sqliteDB = "courseDB.db";
        Connection conn = connect (sqliteDB);
        
        System.out.println("retrieving all courses...");
        printResultSet (retrieveCourses (conn));
        
        System.out.println("removing course cid = 404...");
        removeCourse (conn, 404);
        
        System.out.println("adding new course...");
        addCourse (conn, 404, "Theory of Computation", "CS", 3);
        
        System.out.println("retrieving new course...");
        printResultSet (retrieveCourse (conn, 404));
    }
}

---

Conclusion

---

Files & Resources

All Files for Lesson 9.602

---

References

The sqldf Package. R Documentation.

Copy Data Frame to Tables: dbWriteTable

Errata

None collected yet. Let us know.