Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.*;
- import java.util.ArrayList;
- /**
- * Creates sqlite database connection. Consists methods to work with database fields.
- */
- class DBContainer {
- private Connection conn = null;
- /**
- * Opens a database connection.
- */
- void open() {
- try {
- Class.forName("org.sqlite.JDBC");
- conn = DriverManager.getConnection("jdbc:sqlite:./src/db/user.db");
- System.out.println("Connected");
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- /**
- * Closes a database connection.
- */
- void close() {
- try {
- conn.close();
- System.out.println("Disconnected");
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- /**
- * Creates a library.
- */
- void createLib() {
- try {
- DatabaseMetaData dbmd = conn.getMetaData();
- String[] types = {"TABLE"};
- ResultSet rs = dbmd.getTables(null, null, "%", types);
- while (rs.next()) {
- if (rs.getString("TABLE_NAME").equals("library")) return;
- }
- String query = "CREATE TABLE library (" +
- "id INTEGER PRIMARY KEY AUTOINCREMENT, \n" +
- "\titem VARCHAR(15), \n" +
- "\tprotein REAL, \n" +
- "\tfat REAL, \n" +
- "\tcarbohydrates REAL)";
- Statement statement = conn.createStatement();
- statement.executeUpdate(query);
- statement.close();
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- /**
- * Adds an item to a library (a table in sqlite database).
- * @param item - name of a item.
- * @param protein - protein content of an item.
- * @param fat - fat content of an item.
- * @param carbohydrates - carbohydrates content of an item.
- */
- void addToLib(String item, float protein, float fat, float carbohydrates) {
- try {
- String query = "INSERT INTO library (item, protein, fat, carbohydrates) " +
- "VALUES ('" + item + "', '" + protein + "', '" + fat + "', '" + carbohydrates + "')";
- Statement statement = conn.createStatement();
- statement.executeUpdate(query);
- statement.close();
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- /**
- * Creates a DietContainer object and fills it with library's values.
- * @param container - a DietContainer object to collect values of library.
- */
- void showLib(DietContainer container) {
- try {
- Statement statement = conn.createStatement();
- String query = "SELECT item, protein, fat, carbohydrates FROM library";
- ResultSet rs = statement.executeQuery(query);
- while (rs.next()) {
- container.add(rs.getString("item"),
- rs.getFloat("protein"),
- rs.getFloat("fat"),
- rs.getFloat("carbohydrates"));
- }
- rs.close();
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- /**
- * Deletes an item from a library.
- * @param item - an item of a library to delete.
- */
- void deleteLibItem(String item) {
- try {
- Statement statement = conn.createStatement();
- String query = "DELETE FROM 'library' WHERE item = " + "'" + item + "'";
- statement.executeUpdate(query);
- statement.close();
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- /**
- * Creates a table in an sqlite database.
- * @param table - name of a table.
- */
- void createTable(String table) {
- try {
- String query = "CREATE TABLE " + table + "(" +
- "id INTEGER PRIMARY KEY AUTOINCREMENT, \n" +
- "\titem VARCHAR(15), \n" +
- "\tprotein REAL, \n" +
- "\tfat REAL, \n" +
- "\tcarbohydrates REAL)";
- Statement statement = conn.createStatement();
- statement.executeUpdate(query);
- statement.close();
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- /**
- * Adds an item to an sqlite database's table.
- * @param item - name of an item.
- * @param table - name of a table.
- */
- void addToTable(String item, String table) {
- float protein;
- float fat;
- float carbohydrates;
- try {
- Statement statement = conn.createStatement();
- String query = "SELECT item, protein, fat, carbohydrates FROM "
- + "\'library\'" + " WHERE item = " + "'" + item + "'";
- ResultSet rs = statement.executeQuery(query);
- protein = rs.getFloat("protein");
- fat = rs.getFloat("fat");
- carbohydrates = rs.getFloat("carbohydrates");
- query = "INSERT INTO " + table + " (item, protein, fat, carbohydrates) " +
- "VALUES ('" + item + "', '" + protein + "', '" + fat + "', '" + carbohydrates + "')";
- statement.executeUpdate(query);
- statement.close();
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- /**
- * Fills a DietContainer object with an sqlite database table's values.
- * @param container - a DietContainer object to collect table's values.
- * @param table - a table with needed values.
- */
- void showTable(DietContainer container, String table) {
- container.tableName = table;
- try {
- Statement statement = conn.createStatement();
- String query = "SELECT item, protein, fat, carbohydrates FROM " + table;
- ResultSet rs = statement.executeQuery(query);
- while (rs.next()) {
- container.add(rs.getString("item"),
- rs.getFloat("protein"),
- rs.getFloat("fat"),
- rs.getFloat("carbohydrates"));
- }
- rs.close();
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- /**
- * Gets names of tables into the given list.
- * @param tableNames - list to collect table names.
- */
- void getTables(ArrayList<String> tableNames) {
- try {
- DatabaseMetaData dbmd = conn.getMetaData();
- String[] types = {"TABLE"};
- ResultSet rs = dbmd.getTables(null, null, "%", types);
- while (rs.next()) {
- tableNames.add(rs.getString("TABLE_NAME"));
- }
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- /**
- * Deletes an item from a given table.
- * @param item - an item to delete.
- * @param table - table with needed item.
- */
- void removeFromTable(String item, String table) {
- try {
- Statement statement = conn.createStatement();
- String query = "DELETE FROM " + "'"+ table + "'" + " WHERE item = " + "'" + item + "'";
- statement.executeUpdate(query);
- statement.close();
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- /**
- * Deletes a table from an sqlite database.
- * @param table - a table to delete.
- */
- void deleteTable(String table) {
- try {
- String query = "DROP TABLE " + table;
- Statement statement = conn.createStatement();
- statement.executeUpdate(query);
- statement.close();
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- //TODO
- void createGroup(String group) {
- StringBuilder sb = new StringBuilder("_");
- sb.append(group);
- try {
- String query = "CREATE TABLE " + "'" + sb + "'" + " (" +
- "id INTEGER PRIMARY KEY AUTOINCREMENT, \n" +
- "\ttableName VARCHAR(15), \n" +
- "\tprotein REAL, \n" +
- "\tfat REAL, \n" +
- "\tcarbohydrates REAL)";
- Statement statement = conn.createStatement();
- statement.executeUpdate(query);
- statement.close();
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- //TODO
- void addToGroup(DietContainer container, String table, String group) {
- //container.tableName = table;
- try {
- Statement statement = conn.createStatement();
- String query = "SELECT item, protein, fat, carbohydrates FROM " + table;
- ResultSet rs = statement.executeQuery(query);
- while (rs.next()) {
- container.add(rs.getString("item"),
- rs.getFloat("protein"),
- rs.getFloat("fat"),
- rs.getFloat("carbohydrates"));
- }
- rs.close();
- query = "INSERT INTO " + group + "(tableName, protein, fat, carbohydrates) " +
- "VALUES ('"
- + table
- + "', '"
- + container.proteinAbsoluteTotal
- + "', '"
- + container.fatAbsoluteTotal
- + "', '"
- + container.carbohydratesAbsoluteTotal + "')";
- statement.executeUpdate(query);
- statement.close();
- } catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- //TODO
- void showGroups() {
- }
- //TODO
- void showGroup(DietContainer container, String group) {
- container.groupName = group;
- try {
- Statement statement = conn.createStatement();
- String query = "SELECT tableName, protein, fat, carbohydrates FROM " + "'" + group + "'";
- ResultSet rs = statement.executeQuery(query);
- while (rs.next()) {
- container.add(rs.getString("tableName"),
- rs.getFloat("protein"),
- rs.getFloat("fat"),
- rs.getFloat("carbohydrates"));
- }
- rs.close();
- }
- catch (Exception e) {
- System.out.println(e.getMessage());
- }
- }
- //TODO
- void removeFromGroup(String table, String group) {
- }
- //TODO
- void deleteGroup(String group) {
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement