This is the table we are going to use to fetch database from the SQLite Database

The best way to fetch data from SQLite database using Java




From the previous article, I showed you how to Insert data into the SQLite database. This being the 3rd article of Java Database Series, I will teach you how to fetch data from SQLite database using Java.

Understanding how to fetch data from the SQLite database is very vita especially when presenting that data to a user.

There many ways you can use to fetch data from the SQLite database, but this article will demonstrate the best way to fetch data from SQLite Database using Java.

To Fetch data from the SQLite Database, we will use a while loop; Looping from the database as far as ResultSet has data.

We are going to fetch data from the following table. I have written an article on how to create this kind of SQLite database.

This is the table we are going to use to fetch database from the SQLite Database

The following code illustrates how to fetch data from the SQLite database using the Java Programming language.

There are two methods you can use to fetch data from the SQLite database:

  1. Fetching the whole table from the database e.g “SELECT * FROM table_name”
  2. Fetching specific columns of the table from the database e.g “SELECT column1, column2, … FROM table_name WHERE column =? “

How To Fetch the Whole Table of the SQLite Database using Java Programming Language

 

private static void readAllData() {
    Connection con = DbConnection.connect(); 
    PreparedStatement ps = null; 
    ResultSet rs = null; 
    
    try {
      String sql = "SELECT * FROM users";
      ps = con.prepareStatement(sql); 
      rs = ps.executeQuery();
      System.out.println("ALL USERS\n");
      while(rs.next()) {
        String firstName = rs.getString("firstName"); 
        String secondName = rs.getString("secondName"); 
        String email = rs.getString("email"); 
        String password = rs.getString("password"); 
        
        
        
        System.out.println("First Name: "+firstName);
        System.out.println("Second Name: "+secondName);
        System.out.println("Email: "+email);
        System.out.println("Password: "+password+"\n\n");
        
      }
    } catch(SQLException e) {
      System.out.println(e.toString());
    } finally {
      try {
        rs.close();
        ps.close();
        con.close(); 
      } catch(SQLException e) {
        System.out.println(e.toString());
      }
    }
    
    
  }

 

From the above example, you’ll realize that we’ve not used if(rs.next()). Please don’t use it; the while loop will keep looping as far as rs.next() is true, so no need to use if(rs.next()).

Fetching specific columns of the table row from the SQLite database in Java

The following code will demonstrate how to fetch a specific row from SQLite Database using Java Programing

private static void readSpecificRow() {
    // lets read specific row on the database
    Connection con = DbConnection.connect(); 
    PreparedStatement ps = null; 
    ResultSet rs = null; 
    try {
      String sql = "Select firstName from users where email = ? "; 
      ps = con.prepareStatement(sql); 
      ps.setString(1, "eunice@test.com");
      rs = ps.executeQuery(); 
      
      // we are reading one row, so no need to loop 
      String firstName = rs.getString(1); 
      System.out.println(firstName);// it should give us eunice
      
    } catch(SQLException e) {
      System.out.println(e.toString());
    } finally {
      // close connections
      try{
        rs.close(); 
        ps.close();
        con.close(); 
      } catch (SQLException e) {
        // TODO: handle exception
        System.out.println(e.toString());
      }
      
    }
  }

Java Database Tutorial Series

  1. Java Database Connection
  2. How to Insert Data into SQLite database Using Java
  3. The best way to fetch data from SQLite database using Java
  4. How to Update Data Into SQLite Database using Java Programming 
  5. How to delete a row (a record) from SQLite database using Java Programming
  6. How to get the number of rows in an SQLite Database Table Using Java