DBManager Class for Drone Recon Portal

4 Pages740 Words222 Views

Added on 2019-09-19

About This Document

This class provides CRUD functions for the AreaGridTiles table in the Drone Recon Portal SQLite database. It includes functions to insert and select records from the table.

DBManager Class for Drone Recon Portal

Added on 2019-09-19

Related Documents

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();

DBManager Class for Drone Recon Portal_1

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");

DBManager Class for Drone Recon Portal_2

End of preview

Want to access all the pages? Upload your documents or become a member.