Using SQLite in a Java Spring project with jdbc

Recording the activity of our app using SQLite and jdbc

Let us see how to set up a simple SQLite database and how to insert data in it using jdbc. As an example we will use the restful server that we created in a previous post

So suppose that you have a rest controller and you want to record each request of data into a simple table created in a SQLite database.

Setting up the SQLite database

The first thing you need to know is how SQLite works. The magic of SQLite is that you do not need an actual server to communicate with a SQLite database, you just use a *.db file.

SQLite databases are perfect for environments in which you do not want to communicate with an actual database server but you want the advantages of using queries to store and retrieve data. This technology allows  you to create a *.db file and use jdbc to post and extract data into that file. In case that your application grows bigger you later can replace this setting for an actual connection to a MySQL or Oracle SQL database.

So first you need to create the database. For that I recommend you install a simple SQLite IDE, for instance I have used SQLiteStudio with success in the past.

Once in SQLiteStudio go to Database -> Create database, and choose a name. For instance I called mine activity_log.

Then create a new query and run the following code:

CREATE  TABLE app_activity_log (
  log_id  INTEGER PRIMARY KEY AUTOINCREMENT,
  username VARCHAR(45) not null,
  user_ip VARCHAR(45),
  photos_sent VARCHAR(100),
  date_accessed DATETIME);

This will create the table that we will need. The idea is that every time the user calls the Spring Controller that shots the camera, we will record the user data and the list of photos taken in our table app_ativity_log

Inside your sqlitestudio folder you will find the database that you just created (activity_log.db).

Connect with a SQLite database using spring and jdbc

The right way to connect to any database from a spring application is to create a DAO (data access object), and make it into a spring bean.

In this section we will create the following classes inside our spring app

Before creating the DAO we need a POJO (plain old java object, TAppActivityLog) that imitates the structure of our table app_activity_log:

\src\main\java\com\hjbello\dao\TAppActivityLog.java

package com.hjbello.dao;

import java.util.Date;

public class TAppActivityLog {
 private String username;
 private String userIp;
 private String photosSent;
 private Date dateAccessed;
 
 public TAppActivityLog(String username, String userIp, String photosSent, Date dateAccessed) {
  super();
  this.username = username;
  this.userIp = userIp;
  this.photosSent = photosSent;
  this.dateAccessed = dateAccessed;
 }
 public TAppActivityLog() {
  super();
 }
 public String getUsername() {
  return username;
 }
 public void setUsername(String username) {
  this.username = username;
 }
 public String getUserIp() {
  return userIp;
 }
 public void setUserIp(String userIp) {
  this.userIp = userIp;
 }
 public String getPhotosSent() {
  return photosSent;
 }
 public void setPhotosSent(String photosSent) {
  this.photosSent = photosSent;
 }
 public Date getDateAccessed() {
  return dateAccessed;
 }
 public void setDateAccessed(Date dateAccessed) {
  this.dateAccessed = dateAccessed;
 }

}

Now, since we will use injection of dependencies, we will need an interface for the DAO:

\src\main\java\com\hjbello\dao\RecordActivityDAO.java

package com.hjbello.dao;

import java.sql.Connection;

public interface RecordActivityDAO {
 public Connection connect();
 
 public void save(TAppActivityLog tAppActivityLog);

}

And finally the implementation of the previous interface, which we will declare as a bean using the annotation @Component

\src\main\java\com\hjbello\dao\RecordActivityDAOImpl.java

package com.hjbello.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
 
@Component
public class RecordActivityDAOImpl implements RecordActivityDAO {
 final static Logger logger = LoggerFactory.getLogger(RecordActivityDAOImpl.class);

 public Connection connect() {
  // SQLite connection string
  String dbFile = System.getProperty("user.home") + "/sqlite_databases/activity_log.db";
  String url = "jdbc:sqlite:"+dbFile;
  Connection conn = null;
  try {
   conn = DriverManager.getConnection(url);
   logger.info("Connected to database");
  } catch (SQLException e) {
   logger.info(e.getMessage());
  }
  return conn;
 }

 public  void save(TAppActivityLog tAppActivityLog) {
  String sql = "INSERT INTO app_activity_log (username, user_ip, date_accessed,photos_sent) "
    + "values (?,?,?,?)";

  try (Connection conn = this.connect();
    PreparedStatement pstmt = conn.prepareStatement(sql)) {

   pstmt.setString(1,tAppActivityLog.getUsername());
   pstmt.setString(2,tAppActivityLog.getUserIp());
   pstmt.setString(3,  tAppActivityLog.getDateAccessed().toString());
   pstmt.setString(4,tAppActivityLog.getPhotosSent());

   pstmt.executeUpdate();
   logger.info("Activity recorded");
  } catch (SQLException e) {
   System.out.println(e.getMessage());
  }
 }


}

As you see, in order to connect with the database, we just need to use the usual jdbc statement:

         DriverManager.getConnection(url);

And inside the string url we put the local route to the file activity_log.db. In my case is a little messy because I wanted to use the folder of the active user, for which I needed to add:

        String dbFile = System.getProperty("user.home") + "/sqlite_databases/activity_log.db";
        String url = "jdbc:sqlite:"+dbFile;

instead of just writing 

        String url = "C:/Users/hugo/sqlite_databases/activity_log.db

In that I make it more crossed platform. If you were using a MySQL database you would need an actual url to the server where you have you database.

in the function save you have the insert that we will need. Similar queries would be analogous. As you see is the same jdbc statements as you use for other SQL dialects.

Have in mind that for this to work you will need the SQLite driver inside your buildpath, so if you are using maven you will need the following dependency in your pom.xml file:

  <dependency>
   <groupId>org.xerial</groupId>
   <artifactId>sqlite-jdbc</artifactId>
   <version>3.15.1</version>
  </dependency>

Invoke the DAO

Now inside the spring rest controller I want to invoke the DAO. For that, I will inject it using @Autowired and use it right away with the adequate parsing. I will put here snippets instead of the whole class, If you want the complete code check here.

So we declare the DAO like this

 @Autowired 
 RecordActivityDAOImpl recordActivityDao;

And then

// we record this request in the database
     TAppActivityLog tAppActivityLog = new TAppActivityLog();
     tAppActivityLog.setDateAccessed(new Date());
     tAppActivityLog.setPhotosSent(imagesPath.toString());   
     
     Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
     String username = authentication.getName();
     tAppActivityLog.setUsername(username);
     tAppActivityLog.setUserIp(httpRequest.getRemoteAddr());
     
       recordActivityDao.save(tAppActivityLog);

I obtain the user using the spring context and the ip using the httpRequest, which is an input of our controller.

Check that the data has been stored

If you run a test you can go back to your SQLite IDE and run a simple query:

select * from app_activity_log;

You will see that everything has worked well

Download the full project here

https://github.com/HugoJBello/java-projects-tutorials/tree/master/spring-rest-security-webcam