Zac Loureiro

My last final project blog left off with us having a connection established between Java rest api and a SQLite database. We accomplished this through use of Java imports that were available to us. The next step was finding out how to actually access our database with queries that are sent with our backend Java rest api. We started with a simple get method, in the rest api format it is a @GetMapping method. We were just trying to run a query to get all the ‘artists’ in our database, ‘artists’ being music artists is one of the tables in our database. The query in SQLite would be “select * from artists”. There are a series of methods available with the sql imports in Java to help execute this query using the backend. Here is a look at our complete method:

@GetMapping(“/artists”)

public ResponseEntity<Object> getAllArtists() throws SQLException {

   PreparedStatement statement = conn.prepareStatement(“select * from artists”);

   ResultSet rs = statement.executeQuery();

   ArrayList<Map<String, String>> results = new ArrayList<>();

   while (rs.next()) {

       Map<String, String> temp = new HashMap<>();

       temp.put(“ArtistId”, rs.getString(“ArtistId”));

       temp.put(“Name”, rs.getString(“Name”));

       results.add(temp);

   }

   return new ResponseEntity<>(results, HttpStatus.OK);

}

The line @GetMapping(“/artists”) establishes our path for the rest api. The lines PreparedStatement statement = conn.prepareStatement(“select * from artists”); 

and

ResultSet rs = statement.executeQuery();

are available via the sql imports. The first of these two lines creates the query as a variable “statement” of type “PreparedStatement” within our connection. Then a variable “rs” of type “ResultSet” is set equal to “statement.executeQuery()”. This sets “rs” equal to the result of the query, which in this case is all the artists in the database. Then the data of the artists is loaded into an ArrayList of type Map<String, String> and returned. Returning an ArrayList of Maps is best for functionality when we got to working on our front end code. Since artists had two fields “ArtistId” and “Name”, which are both Strings, saved the data in a Map<String, String> so that both variables were easy to access. This way we could pinpoint any artist in the database when we began to search for specific artists. Our next task was to create methods that allowed our users to search for a specific artist by name. We needed to also add a post method to allow users to add an artist to our database.

From the blog cs@worcester – Zac&#039;s Blog by zloureiro and used with permission of the author. All other rights reserved by the author.

For my final project in my Software construction, design, and architecture class, my partner and I had the idea of creating a web page that was able to access data of a SQLite database. The structure of this project was to connect to a database using Java as a backend and use Rest api to send out sql commands. We needed to run the backend on a server using a Spring Boot framework available to us. Once our connection was setup and our backend methods to access the database was ready we had to create the front end. We used Typescript and Java Angular to create our front end, or in other words our web page. The typescript code had to connect to the Java rest api so our chain of connections from the top goes; Typescript and Angular -> Java Rest api -> SQLite database. 

Starting the project the very first obstacle we ran into was connection Java rest api to the SQLite database. It was something we had never done before, but thankfully there was helpful resources online. We found out that there was a series of Java imports to facilitate this function. A few imports needed are as follows:

import java.sql.*;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

Using these imports we were able to create an object of type ‘Connection’ in order to establish the actual connection to the database. Here is our method:

public static Connection getConnection() {

   if (conn == null) {

       try {

           conn = DriverManager.getConnection(“jdbc:sqlite:” + dbpath);

       } catch (SQLException e) {

           e.printStackTrace();

       }

   }

   return conn;

}

The set method to set our path:

DatabaseSQL.setPath(“C:/…(insert path here)…”);

We are now able to get the connection inside our Java rest api classes that contain our SQLite query methods using this statement:

Connection conn = DatabaseSQL.getConnection();

As I said this information can be found through many sources online so we were lucky such a function was available for us to use at our disposal. We needed the connection to be established before we could proceed with anything else because the entire project relied on accessing the database. However, in order to actually know if our connection was working we couldn’t rely on the absence of errors. We needed to create a rest api method to access the database and give us a result so that we could be sure.

From the blog cs@worcester – Zac&#039;s Blog by zloureiro and used with permission of the author. All other rights reserved by the author.

By now, I’ve become rather accustomed to Typescript, Angular, and even CSS. Once the basic functionality of this project was working, motivation and momentum made it easy to continue adding more features. As evidence 14 out of 21 commits have been made in the past week, and although some are small it shows how easy adding features becomes when you have a good base built.

The database building and making sure it was in sync with file uploads was the most challenging part of this project so far. Two new services were created: one to handle all interactions with the database and another to provide information about the user that is currently logged in. Having Components communicate through a service decouples them from each other and the parent. The Components can simply subscribe to the data they need. This blog post was most helpful in determining the best approach for my needs, but Angular’s documentation filled in the blanks as needed.

An interesting bug I encountered was that uploading a new file overwrote all previous files. This was occurring because I was using the same reference to storage, but a new one is required for each upload. This was a simple solution, but puzzling at first. This was only noticed when I tried deleting a file: other files were still in the database and storage, but the link to all files returned a 404 error.

Then my least favorite part came: improving the UI. I love making things work, but making them flashy and fancy frankly seems like a waste of time, as long as it doesn’t detract from the user experience. Still, I quite enjoyed making it look nicer, despite some frustrations with CSS. The biggest issue I’ve had is CSS styles from outside components affecting the inner ones. I also wish browsers were more standardized. It has been difficult creating a consistent user experience across browsers, save for creating new elements from scratch. In the case of file uploads, for example, it is much simpler to hide the actual element and forward user clicks from a custom text input and browse button.

My last task will be to add some graphs to process information and display it to the user. This is of little use for the project as it stands, but will be incredibly useful for my Independent Study project next semester when I re-brand it. I have to give some credit to the Angular framework for making it easier, but using software engineering principles has allowed for an iterative project. I have a working project at every step with a much bigger end goal in mind.

There is still some polishing I’d like to do, and of course there could always be more features. This project was a great chance to dive into Angular and web development.

From the blog CS@Worcester – Inquiries and Queries by ausausdauer and used with permission of the author. All other rights reserved by the author.