Java Common - Library - metcarob.com.common.io.db Usage

Submitted by code_admin on Fri, 07/20/2018 - 13:25

This page documents usage of my db library

Main java library usage notes

Steps to create a new usage

1. Create a new class derived from metcarob.com.common.io.db.DBFile

Example:

  1. public class ServerDataFile extends DBFile {
  2.     public ServerDataFile(String string, boolean b) throws IOException, ClassNotFoundException, SQLException {
  3.         super(string, b);
  4.     }
  5.  
  6.     @Override
  7.     protected void CreateDataStructureDerived() throws SQLException {
  8.         String query = "create table picPlayLists (" +
  9.             "id integer not null PRIMARY KEY," +
  10.             "name TEXT not null unique," +
  11.             "path TEXT not null," +
  12.             "subdirs integer not null DEFAULT 1," +
  13.             "created DATETIME not null DEFAULT CURRENT_TIMESTAMP," +
  14.             "creation_note TEXT," +
  15.             "lastplayed DATETIME" +
  16.         ")";
  17.         this.executeUpdate(query);    
  18.        
  19.         query = "create table pathReplacements (" +
  20.             "id integer not null PRIMARY KEY," +
  21.             "name TEXT not null unique," +
  22.             "replacewith TEXT not null" +
  23.         ")";
  24.         this.executeUpdate(query);        
  25.     }
  26.  
  27.     @Override
  28.     protected int getCodeDBFileVersion() {
  29.         return 2;
  30.     }    
  31.    
  32.    
  33.     @Override
  34.     protected void UpdateDataStructureDerived(int p_fromVer, int p_toVer) throws SQLException {
  35.        
  36.         System.out.println("UpdateDataStructureDerived");
  37.        
  38.         if (p_fromVer==1) {
  39.             System.out.println("From ver = 1");
  40.             //Add picPlayLists fields created, creation_note, lastplayed
  41.             String query = "ALTER TABLE picPlayLists ADD COLUMN created DATETIME not null DEFAULT '" + DBFileSaveable.sqlDatePURE(new Date()) + "'";
  42.             int r = this.executeUpdate(query);
  43.             query = "ALTER TABLE picPlayLists ADD COLUMN creation_note TEXT";
  44.             this.executeUpdate(query);
  45.             query = "ALTER TABLE picPlayLists ADD COLUMN lastplayed DATETIME";
  46.             this.executeUpdate(query);
  47.             query = "update picPlayLists set creation_note='From Previous File Version'";
  48.             this.executeUpdate(query);
  49.            
  50.             p_fromVer=2;
  51.         }
  52.     }
  53.  
  54. }

2. Add Dependincies

You must add org.sqlite.JDBC or org.sqldroid.SqldroidDriver to your project

3. Add code to open and close the file in your class

Example (m_DB is declared as the DBFile override class:

  1. {
  2.     String p_argDBFile = "TestFile"
  3.     File f = new File(p_argDBFile);
  4.     if (f.exists()) {
  5.         m_DB = new ServerDataFile(p_argDBFile,false);
  6.     } else {
  7.         System.out.println("Could not find " + p_argDBFile + " so we are auto-creating it");
  8.         m_DB = new ServerDataFile(p_argDBFile,true);
  9.     }
  10. };
  11.  
  12. if (null==m_DB) {
  13.     throw new Exception("No Settings - must exit");
  14. };

Some where later

  1.     m_DB.close();

4. Create a saveable object

Create a class that extends DBFileSaveable:

  1. public class Node extends DBFileSaveable {
  2.  
  3.     public Node(int i, DBFile dbFile) throws SQLException, DBFileException {
  4.         super(i, dbFile);
  5.         Load(i,dbFile);
  6.     }
  7.  
  8.     public Node() {
  9.         super();
  10.     }
  11.  
  12.  
  13.     @Override
  14.     protected void DeleteDerived(DBFile p_File, int p_id) throws SQLException,
  15.             DBFileException {
  16.         String query = "delete from pathReplacements where id=" + p_id;
  17.         int ret = p_File.executeUpdate(query);                            
  18.     }
  19.  
  20.     @Override
  21.     protected void SaveDerivedINSERT(DBFile p_File, int p_id)
  22.             throws SQLException, DBFileException {
  23.         String query;
  24.         int ret;
  25.         query = "insert into pathReplacements (id,name,replacewith) VALUES (";
  26.         query += p_id + ",";
  27.         query +=  sqliteString(m_Name) + ",";
  28.         query +=  sqliteString(m_ReplaceWith);
  29.         query +=")";
  30.         try {
  31.             ret = p_File.executeUpdate(query);
  32.         } catch (Exception e) {
  33.             throw new DBFileException("Error Inserting Record query was: (" + query + ") error was " + e.toString());
  34.         }
  35.         if (ret!=1) {
  36.             throw new DBFileException("Trying to save but updated " + ret + " rows (should be 1) - INSERT");
  37.         };         
  38.     }
  39.  
  40.     @Override
  41.     protected void SaveDerivedUPDATE(DBFile p_File, int p_id)
  42.             throws SQLException, DBFileException {
  43.         String query;
  44.         int ret;
  45.         query = "update pathReplacements set ";
  46.         query += "name=" + sqliteString(m_Name);
  47.         query += ",replacewith=" + sqliteString(m_ReplaceWith);
  48.         query += " where id=" + p_id;
  49.         ret = p_File.executeUpdate(query);
  50.         if (1!=ret) {
  51.             throw new DBFileException("Trying to save but updated " + ret + " rows (should be 1) - UPDATE");
  52.         };          
  53.     }
  54.  
  55.     @Override
  56.     protected void LoadDerived(DBFile p_File, int p_id) throws SQLException,
  57.             DBFileException {
  58.         String query = "select name, replacewith from pathReplacements where id=" + p_id;
  59.         ResultSet rs = p_File.executeQuery(query);
  60.         int c=0;
  61.         while (rs.next()) {
  62.             c++;
  63.             m_Name = rs.getString("name");
  64.             m_ReplaceWith = rs.getString("replacewith");
  65.         };
  66.         rs.close();
  67.         rs = null;
  68.         if (c!=1) {
  69.             throw new DBFileException("ERROR: could not find file id " + p_id + " (returned " + c + " records");
  70.         };      
  71.     }
  72. }

5. Create object

You can now simply create nodes:

  1. Node a = new Node();
  2. a.Save(mainfile);

6. Manager Object

Example Manager Object:

  1. public class NodeManager {
  2.     private MainNodeFile m_mainFile = null;
  3.    
  4.     private Map<String, Node> m_nameNodeMap = new HashMap<String, Node>();
  5.     private Map<Integer, Node> m_IDNodeMap = new HashMap<Integer, Node>();
  6.    
  7.     public NodeManager(MainNodeFile p_mainFile) {
  8.         m_mainFile = p_mainFile;
  9.     }
  10.    
  11.     public void addNode(Node p_node) throws Exception {
  12.         if (p_node==null) return;
  13.         m_nameNodeMap.put(p_node.getM_name(), p_node);
  14.         m_IDNodeMap.put(p_node.getDb_id(), p_node);
  15.  
  16.     }
  17.    
  18.     public Node getNode(String p_name) throws Exception {
  19.         Node ret = m_nameNodeMap.get(p_name);
  20.         if (ret!=null) return ret;
  21.        
  22.         //Not already loaded so load from database
  23.         String query = "select id from nodes where nodeName=" + DBFileSaveable.sqliteString(p_name);
  24.         ResultSet rs = m_mainFile.executeQuery(query);
  25.        
  26.         int id = -1;
  27.         int c=0;
  28.         while (rs.next()) {
  29.             c++;
  30.             id = rs.getInt("id");
  31.         };
  32.         rs.close();
  33.         rs = null;
  34.         if (c!=1) {
  35.             return null;
  36.         };      
  37.         ret = new Node(id, m_mainFile);
  38.         if (ret!=null) {
  39.             addNode(ret);
  40.         }
  41.         return ret;
  42.     }
  43.    
  44. }

Tags

RJM Article Type
Work Notes