Database Management in Java

This practical assignment focuses on implementing database management functionalities in Java using SQLite. The provided Java code demonstrates the creation of a `DBManager` class that handles Create, Read, Update, and Delete (CRUD) operations on an `AreaGridTiles` table within a SQLite database. The code includes functions for inserting new records (`insertAreaGridTile`), retrieving records based on area ID (`readAreaGridTiles`), and a `main` method for testing purposes. The `AreaGridTile` class is implicitly used to represent individual database records. The code showcases the use of JDBC (Java Database Connectivity) to interact with the SQLite database, handling connections, executing SQL queries, and processing results. The assignment likely aims to teach students about database interaction in a programming context, covering topics such as database connection management, SQL query execution, result set processing, and exception handling.

package com.dronerecon.ws;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
public class DBManager {
public String DBLocation = "dronedata.sqlite"; // For running from an IDE w/ DB located in
local project folder.
// For running in IDE but accessing full path to DB in Tomcat website (NOTE: May need to
adjust 8.5).
//public String DBLocation = "C:\\Program Files\\Apache Software Foundation\\Tomcat 8.5\\
webapps\\dronereconportal\\db\\dronedata.sqlite";
// Create connection with DB.
// This is called internally by each CRUD function as a first step.
private Connection connect() {
// SQLite connection string
String url = "jdbc:sqlite:" + DBLocation;
Connection conn = null;
try {
Class.forName("org.sqlite.JDBC");
// Connect to DB.
conn = DriverManager.getConnection(url);
} catch (Exception e) {
System.err.println(e.getMessage());
}
return conn;
}
// Insert DB record into AreaGridTiles table.
// This is the "C" in CRUD.
public void insertAreaGridTile(String sAreaID, int iX, int iY, int iR, int iG){
Connection c = connect();

Statement stmt = null;
try {
c.setAutoCommit(false);
stmt = c.createStatement();
String sql = "INSERT INTO AreaGridTiles (area_id,x,y,r,g,timestamp) " +
"VALUES ('" + sAreaID + "'," + iX + "," + iY + "," + iR + "," + iG + ",datetime());";
stmt.executeUpdate(sql);
stmt.close();
c.commit();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
e.printStackTrace();
}
}
// Select DB records from AreaGridTiles table.
// This is the "R" in CRUD.
public ArrayList<AreaGridTile> readAreaGridTiles(String sAreaID){
Connection c = connect();
Statement stmt = null;
// Used to hold tiles retrieved from DB.
ArrayList<AreaGridTile> lstTiles = new ArrayList<>();
try {
c.setAutoCommit(false);
stmt = c.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM AreaGridTiles WHERE area_id =
'" + sAreaID + "';" );
while ( rs.next() ) {
sAreaID = rs.getString("area_id");
int iX = rs.getInt("x");
int iY = rs.getInt("y");
int iR = rs.getInt("r");
int iG = rs.getInt("g");
String sTimestamp = rs.getString("timestamp");

AreaGridTile oTile = new AreaGridTile();
oTile.areaID = sAreaID;
oTile.x = iX;
oTile.y = iY;
oTile.r = iR;
oTile.g = iG;
oTile.timestamp = sTimestamp;
lstTiles.add(oTile);
/* Uncomment for debugging
System.out.println( "Area ID : " + sAreaID);
System.out.println( "x : " + iX);
System.out.println( "y : " + iY);
System.out.println( "r : " + iR);
System.out.println( "g : " + iG);
System.out.println( "timestamp : " + sTimestamp);
*/
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
}
return lstTiles;
}
// Only for testing in an IDE.
public static void main(String[] args){
DBManager oDBManager = new DBManager();
oDBManager.insertAreaGridTile("abc123",10,10,243,109);
System.out.println("Record inserted.");
ArrayList<AreaGridTile> oTiles = oDBManager.readAreaGridTiles("23abc");
for(AreaGridTile oTile: oTiles){
System.out.println("tile: " + oTile.x + "," + oTile.y);
}

}
}