Hibernate — Getting JSON to Java — Java to Hibernate

This page has good information about getting JSON into a database as JSON. Then I would need to sort through the JSON data directly. I want to do a one time extraction of the JSON data and then use that data to make my Java database with hibernate.

Here’s good information for a simple Hibernate tutorial.

Ideally I would store the JSON data in MongoDB, a NoSQL database that can handle JSON objects. For simplicity, I am going to stick with MySQL and just add the real name of the songs and venues to the database I already have.

So how did I turn the JSON into JAVA last time?

I can’t work on the TAD MACs because MySQL can’t be installed. So.. I’ll work on it at home. I could ssh to stu first and then use MySQL directly on the command line. I’ll need to update the allowed IP addresses that can access the database with a password though.

I still can’t install NetBeans on these computers without admin privileges so no luck.

ssh greatwmc@stu.cs.jmu.edu
mysql -h moe-audio.cr8bdnqgcx5a.us-east-1.rds.amazonaws.com -u milesgwood -p

SSH works but Netbeans isn’t allowed. Time to quit for today and go to Free Flow.

Setup Netbeans and Apache

  1. Install MySQL
  2. Install Netbeans with JDK – this bundles java with Netbeans
  3. Install Struts2 Plugins
  4. Install Apache Tomcat server (8.5.11)

Turns out I actually needed to install Apache separately. I downloaded core release .zip file, moved it to the netbeans projects folder and ran the following on all of the scripts in /bin.

chmod +x *

Then I just told Netbeans where to find the server by “Adding a server” and setting it to the home directory of the apache download.

Get schema from JSON

Use this JSON viewer to clean up the JSON. 

Here’s the show I’m working with.      JSONForMoeShowODT      JSONForMoeShow

<iframe src=”https://archive.org/embed/moe2006-02-02_urbana_CMXY.flac16” width=”500″ height=”140″ frameborder=”0″ webkitallowfullscreen=”true” mozallowfullscreen=”true” allowfullscreen></iframe>

To view my database I had to add a Security Group rule in AWS. 

mysql -h rds-mysql-10mintutorial.cr8bdnqgcx5a.us-east-1.rds.amazonaws.com -u milesgwood -p

Here is the show we are working with in the database.

SELECT * FROM shows WHERE show_url = "moe2006-02-02_urbana_CMXY.flac16" LIMIT 100;
+-----+------------+-------+----------------------------------+
| id  | date       | venue | show_url                         |
+-----+------------+-------+----------------------------------+
| 485 | 2006-02-02 | NULL  | moe2006-02-02_urbana_CMXY.flac16 |
+-----+------------+-------+----------------------------------+

Here is the data for all of the songs in the songs database.

mysql> SELECT * FROM songs WHERE show_id=485 LIMIT 100;
+------+---------+--------------------------------------------+------------+
| id   | show_id | name                                       | mtime      |
+------+---------+--------------------------------------------+------------+
| 5504 |     485 | moe2006-02-02d1t01_spine_of_a_dog.flac     | 1140106259 |
| 5505 |     485 | moe2006-02-02d3t01_fade_in.flac            | 1140121057 |
| 5506 |     485 | moe2006-02-02d2t04_biblical.flac           | 1140121006 |
| 5507 |     485 | moe2006-02-02d1t05_four.flac               | 1140109584 |
| 5508 |     485 | moe2006-02-02d2t03_meat.flac               | 1140120143 |
| 5509 |     485 | moe2006-02-02d3t05_crowd.flac              | 1140126396 |
| 5510 |     485 | moe2006-02-02d1t03_she_sends_me.flac       | 1140107389 |
| 5511 |     485 | moe2006-02-02d3t02_opium.flac              | 1140123530 |
| 5512 |     485 | moe2006-02-02d1t04_tuning.flac             | 1140107476 |
| 5513 |     485 | moe2006-02-02d1t06_wicked_awesome.flac     | 1140110682 |
| 5514 |     485 | moe2006-02-02d3t07_nebraska.flac           | 1140128035 |
| 5515 |     485 | moe2006-02-02d2t05_fade_out.flac           | 1140121034 |
| 5516 |     485 | moe2006-02-02d1t02_new_york_city.flac      | 1140106828 |
| 5517 |     485 | moe2006-02-02d1t08_bring_it_back_home.flac | 1140113367 |
| 5518 |     485 | moe2006-02-02d2t01_intro.flac              | 1140113502 |
| 5519 |     485 | moe2006-02-02d3t03_32_things.flac          | 1140125490 |
| 5520 |     485 | moe2006-02-02d2t02_tailspin.flac           | 1140114993 |
| 5521 |     485 | moe2006-02-02d3t04_new_york_city.flac      | 1140125887 |
| 5522 |     485 | moe2006-02-02d3t06_join_together.flac      | 1140127252 |
| 5523 |     485 | moe2006-02-02d1t07_captain_america.flac    | 1140112381 |
+------+---------+--------------------------------------------+------------+
20 rows in set (0.01 sec)

I am just now realizing that the mtime is modification time. It’s not the playtime. So what values from the JSON do I actually want? I am going to make a new post where I sort through the JSON for the show. I figured out that the mtime is how Archive knows the order of the songs. So I do need mtime.

mysql> SELECT * FROM songs WHERE show_id=485 ORDER BY mtime LIMIT 100;

The main piece I’m missing now is the method of getting the show identifiers. I think I initially got them from a JSON search on archive.

Design database tables

root > files

  • name – file name “VERY NEEDED”
  • mtime – upload order
  • source – confirms that it is original recording “original”
  • album – SHOW name
  • title – SONG TITILE
  • format – String of the type of file
  • length – it doesn’t have a song length if it isn’t a song

The file named showfile_meta.xml has the info on the location, uploader, and other good stuff for a next iteration.

“name”: “moe2006-02-02_urbana_CMXY.flac16_meta.xml“,

The file named showfile_files.xml has all of this same info in XML form.

name”: “moe2006-02-02_urbana_CMXY.flac16_files.xml”,

root> metadata

  • identifier – the showfile or url for the show
  • coverage – show location
  • description – the order of songs
  • date – date it was performed
  • uploader – the kind soul who uploaded it – email
  • adder – the username of the uploader
  • title – title for the show
  • venue – venue

root > reviews

  • reviewbody
  • reviewtitle
  • reviewer
  • stars
  • createdate
  • reviewdate

The shows database is missing some info and has a lot of shows that are bad data. I am going to drop all of the shows and re-add them again. To add them, I use the archive advanced search function and download a CSV file with all of the needed data. 3,364 recordings.

TRUNCATE TABLE shows;
TRUNCATE TABLE songs;
ALTER TABLE shows ADD show_name VARCHAR(200);
ALTER TABLE shows DROP COLUMN date;
ALTER TABLE shows ADD date VARCHAR(30);

Copy the CSV file to a computer that can access mysql.

scp search_new.csv greatwmc@stu.cs.jmu.edu:

LOAD DATA LOCAL INFILE 'search_new.csv'
INTO TABLE shows
FIELDS TERMINATED BY ','
IGNORE 1 ROWS
(@col1,@col2)
SET id=NULL, venue=NULL, date=NULL, show_url=@col1, show_name=@col2;

At this point I am still trying to update the shows. My csv columns have errors. I got 172 errors but the data looks good.

This @col1 syntax put quotes around the values in the database.

To fix it I am running

UPDATE `shows` 
SET `show_url` = TRIM(BOTH '"' FROM `show_url`);
UPDATE `shows` 
SET `show_name` = TRIM(BOTH '"' FROM `show_name`);

ALTER TABLE shows ADD score INT DEFAULT 0;
ALTER TABLE songs ADD score INT DEFAULT 0;

Populating the songs database

  1. Get all of the show identifiers
  2. Use the identifiers to make a GET request for the JSON document for the show
  3. Parse the document for show metadata
  4. Update the show with venue and date
  5. Parse the document for song data
  6. Add the songs to the database

I need to add columns to the database for these new data pieces

TRUNCATE TABLE songs;
ALTER TABLE songs 
ADD COLUMN source VARCHAR(20),
ADD COLUMN format VARCHAR(20),
ADD COLUMN length VARCHAR(15),
ADD COLUMN title VARCHAR(250),
ADD COLUMN album VARCHAR(250),
ADD COLUMN track VARCHAR(30),
ADD COLUMN external_identifier VARCHAR(40);

ALTER TABLE songs DROP COLUMN title;

ALTER TABLE songs DROP COLUMN album;

ALTER TABLE songs 
ADD COLUMN title VARCHAR(250),
ADD COLUMN album VARCHAR(250);

 

Now I can add the data. With autocommit as true I got 80 records in 19 seconds.

With autocommit set to false I get faster transactions.

Describe tables MySQL

Automatically generate POJO from MySQL – Reverse Engineering

Once the JSON has been turned into useful database tables, I can then use those tables to automatically generate the Hibernate classes.

Tutorial in Netbeans.

  1. Create Hibernate configuration file with connection settings and correct Driver (hibernate.cfg.xml)
    1. Add the MySQL driver, jdbc URL, username, and password to the Session Factory settings
    2. Hibernate Config
  2. Hibernate Reverse Engineering Wizard (hibernate.reveng.xml)
    1. Both files are put into the default package in source
  3. Hibernate Mapping Files and POJOs from database
    1. This generates all of the Hibernate files in a new package which I called hibernate

Using Hibernate generated POJOs

Now that I’ve generated all of these POJOs from my table design, how do i actually use them?

All of the CRUD operations are handled by the Session, Transaction, and Query interfaces. Lets look at the interfaces I’ll use.

  • Session – lightweight and created for almost every tx
  • SessionFactory – created once and used for the entire life of the application
    • It can cache SQL statements if second-level cache is desired – look into this later
  • Configuration is setup
  • Transaction – keeps Hibernate portable by abstracting the Trasaction type -COBRA, JDBC, etc.
  • Query has shortcuts like find() – and is lightweight

Here is how you create a brand new object and save it.

Session session = getSessionFactory().openSession();
Transaction tx = session.beginTransaction();
Message message = new Message("Hello World");
session.save(message);
tx.commit();
session.close();

This session object is not a HTML session object.

Session newSession = getSessionFactory().openSession();
Transaction newTransaction = newSession.beginTransaction();
List messages =
      newSession.find("from Message as m order by m.text asc");
System.out.println( messages.size() + " message(s) found:" );
for ( Iterator iter = messages.iterator(); iter.hasNext(); ) {
   Message message = (Message) iter.next();
   System.out.println( message.getText() );
}
newTransaction.commit();
newSession.close()

Hibernate uses HQL to get data from the database. Here we get the Objects we want in a List and can iterate through them. This will be really useful for getting the Songs data in a list format.

Session session = getSessionFactory().openSession();
Transaction tx = session.beginTransaction();
// 1 is the generated id of the first message
   Message message =
(Message) session.load( Message.class, new Long(1) ); //This gets the Message with ID 1
message.setText("Greetings Earthling");
Message nextMessage = new Message("Take me to your leader (please)");
message.setNextMessage( nextMessage );
tx.commit();
session.close();

This grabs the message from the database with ID 1 and updates it using automatic dirty checking. This prevents us from needing to explicitly update an object inside of a transaction. So I can grab a Song object and update it while I am creating a new comment for that song as well. Dirty checking only works if there is a reference from an object that already exists in the database. The nextMessage field gets created only because there is a reference to it in Message. This is called a cascading save and prevents an explicit call to save().

The documentation included in the most recent release has very different instructions. I am using an older version of Hibernate as it is included with NetBeans. It is 4.3.x.

For the one time setup I found the included Events tutorial helpful

private SessionFactory sessionFactory;

@Override
 protected void setUp() throws Exception {
 // A SessionFactory is set up once for an application!
 final StandardServiceRegistry registry = new StandardServiceRegistryBuilder()
 .configure() // configures settings from hibernate.cfg.xml
 .build();
 try {
 sessionFactory = new MetadataSources( registry ).buildMetadata().buildSessionFactory();
 }
 catch (Exception e) {
 // The registry would be destroyed by the SessionFactory, but we had trouble building the SessionFactory
 // so destroy it manually.
 StandardServiceRegistryBuilder.destroy( registry );
 }
 }

@Override
 protected void tearDown() throws Exception {
 if ( sessionFactory != null ) {
 sessionFactory.close();
 }
 }

adsf

Notes on Front End

I put the html into the back end last time. I don’t want to do that this time. I want to keep it in the front end.

Archive put up some good info on how to use their embedded players.

Autoplay – “https://archive.org/embed/jj2008-06-14.mk4&autoplay=1″

Playlist  –    “https://archive.org/embed/jj2008-06-14.mk4&playlist=1″

Specific single audio file – “https://archive.org/embed/jj2008-06-14.mk4/jj2008-06-14d2t04.flac

Youtube Videos

<iframe width="420" height="315"
    src="https://www.youtube.com/embed/XGSy3_Czz8k&autoplay=1">
</iframe>


<iframe width="560" height="315" src="https://www.youtube.com/embed/Tt4j5t1YA-E" frameborder="0" allowfullscreen></iframe>

So I want to update the front end. Here is what the backend is currently returning.

<li class='talk-bubble tri-left round border left-top botmsg'>
<iframe src='https://archive.org/embed/moe1996-11-16.flac16/moe1996-11-16d1t06.flac&autoplay=1'  
width='500' height='140' frameborder='0' webkitallowfullscreen='true' mozallowfullscreen='true' 
allowfullscreen></iframe>
<br>Show URL: moe1996-11-16.flac16
<br>Show Date: null   
<br>Filename: moe1996-11-16d1t06.flac</li>

I want to simply return the show with all of the songs in there. It’s probably best to return all of the information and sort through it on the view side. First I want to update the version I am using so the documentation applies. To use the latest Hibernate I need to use Maven. I’m going to learn Maven now.