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's Blog by zloureiro and used with permission of the author. All other rights reserved by the author.
