Using Spring Data for database views without an ID

A database view is a great way to prepare complicated data structures right in the database and present it in a more convenient way. We usually use database views to map our technical driven database structure (with a lot of tables and foreign keys) to a more business driven structure. This means that we might join tables, concatenate values or just rename the columns, because a particular name suits better to the current business context.

To map those views to Java entities, we simply use Spring data:

However, the example above wouldn’t work. Spring would complain that the @Id annotation is missing and would throw a org.hibernate.AnnotationException: No identifier specified for entity exception.

From a business perspective, this is bad. The id of a company is its company id, the id of a person is its person id. But both are missing in the view, which has not unique identifier.

We could solve this problem by a simple trick: we invent an unique identifier – our current row number!

We can now use the row number in our Spring Data entity:

Best regards,
Thomas

ORA-28001: the password has expired

Today I came about a very annoying exception. After my development setup was running smoothly for the last six months, my application was getting database errors today. I know that I didn’t break something, so the problem had to be somewhere else – and it was: ORA-28001: the password has expired

If you install an Oracle database on a Windows system, the default password policy will make all passwords expire after exactly six months! Great. So here’s how to fix that:

Best regards,
Thomas

3 ways of installing Oracle XE 11g on Ubuntu

During the last few days I struggled with installing Oracle XE 11g on an Ubuntu VM. And with “the last few days” I mean “the last few weeks”. Sad, but true. Here is what I learned about installing Oracle XE 11g on Ubuntu. But please note that I am not a Linux specialist nor an Oracle specialist – it was my first try. And as you will see, I try to make my life as easy as possible. But let’s start with the very basics.

About Oracle XE 11g

Oracle Database Express Edition 11g (or just Oracle XE) is the free version of Oracles 11g database. It was released in 2011 and is the second free version of Oracle’s database. The first free version was Oracles XE 10g, which was released in 2005. The latest paid version of Oracle’s database is 12c, which was released in 2013.

What is really confusing is the fact that the release date of the paid versions is different from the release date of the free versions. So don’ be confused, the latest free database from Oracle is 11g.

Version Paid Free
Version 9i: 2001
Version 10g: 2003 2005
Version 11g: 2007 2011
Version 12c: 2013

By the way, the i, g and c in the database names stand for internet, grid and cloud.

You can download Oracle XE from the link below – and this is where the pain begins. First of all you need an Oracle account, but this is free and easy. Then you have the choice between two packages:

  • One package for Windows which only runs on a 32-bit machine as Oracle says. But don’t worry, it also runs on a 64-bit machine (like mine) and although the unzipped installation folder is called DISK1 there is no DISK2 or something. Just run the setup.exe.
  • One package for Linux which is meant to run on a 64-bit machine as Oracle says. And here is the first pain: The package is only available as RPM so you first have to run alien on it to convert it for Ubuntu.

#1 – Installing Oracle XE by hand

b7e

My first approach was to install Oracle XE by hand. Although this seems to be the straightforward solution, it was the most painful one. You have to convert the RPM package to a DEP package, create a chkconfig script, create some mystic kernel parameters in a 60-oracle.conf, set your swap space to 2GB or more, create some more folders, install the DEB package, configure the database, export some environment variables like ORACLE_HOME (are you still with me?), reload that changes and then start the Oracle service and connect via SQLPlus. Easy, isn’t it? That’s why Oracle is making so much money with consulting 😀

And how to know all this steps? Well, use Google, because the best descriptions I found are not from Oracle. To be precise, I found no official description how to setup the Oracle XE database. Here is what I found on blogs and forums. Just choose one that suits you best. They are all really good, thanks to the people who wrote this!

#2 – Installing Oracle XE with Vagrant and Puppet

Vagrant is a free tool to create Linux VMs automatically. This means you can run Vagrant with a simple configuration file (called Vagrantfile) which describes a Linux VM (e.g. how many CPUs it should have or which image should be installed). Vagrant will create a VM according to this configuration and start it. This gives you the ability to create the same machine with the exact same configuration over and over again.

Puppet is a tool to orchestrate Linux machines. This means Puppet can automatically install programs, create folders or write files. You can download and use it for free. It perfectly integrates into Vagrant. So you can create a VM with Vagrant and as soon as it is ready it can be orchestrated by Puppet.

The really great thing about Vagrant and Puppet is that such scripts can be exchanged. This means that you can write a setup of scripts for something (e.g. to install Oracle XE) and share it with other people. And this is what those to guys did:

  • Matthew Baldwin wrote a complete Puppet (and Vagrant) setup to install Oracle XE on CentOS (a “RPM-Linux“). You find it here on GitHub.
  • Hilverd Reker wrote a complete Puppet (and Vagrant) setup to install Oracle XE on Ubuntu 12.04 (a “DEB-Linux“). You find it here on GitHub.

Both projects work the same:

  1. Install Vagrant, VirtualBox and Puppet
  2. Checkout the GitHub repository or download it as a ZIP-file
  3. Download the Oracle XE installation files and put it in some folder which you can read in the README.md of the projects
  4. Go to the root folder of the project and run vagrant up. This will download a Linux image, install it to a VM and do all the rest (including installing Oracle XE). Note that this will take some time depending on your internet connection!
  5. Now you can call vagrant ssh and you got a running Linux VM with Oracle XE!

Both projects work really well and install an Oracle XE instance in a couple of minutes.

#3 – Installing Oracle XE with Docker

homepage-docker-logo

This one is the nicest way to install Oracle XE. Docker is an application container for Linux. It is based on LXC and gives you the ability to package complete application including their dependencies to a self-containing file (called an image). These images can be exchanged and run on every Linux machine where Docker is installed! Awesome!

Docker images are also shared around the community on https://index.docker.io. And this is where this two guys come into play:

  • Wei-Ming Wu made a Docker image containing Oracle XE. You can find it here.
  • Alexei Ledenev extended Wei-Ming Wu’s image to also use the Oracle web console (APEX). You can find it here.

Both projects work the same:

  1. Install Docker on your Linux machine. You can find instructions for that at http://docs.docker.io/en/latest/installation/ubuntulinux. But it is nothing more then this:
  2. Pull the image to your machine:
  3. Run the image:
  4. That’s it. Absolutely simple.

The great thing is that those images are not as big as a virtual machine. They only contain the actual application and its environment. But they are packed in a way that they can be executed everywhere right out of the box but are still isolated. You can also make changes to the images and create another image containing your changed system.

Resources

Best regards,
Thomas

Media Night Winter Semester 2013/2014

opinion-trends-poster

During the last summer semester, two friends of mine and I made a student project called TechTrends. TechTrends was a web application that let you search for articles and trends in the field of computer science. Based on posts from Reddit and Hackernews, it provided an intelligent search on a growing number of articles and blogs.

During this winter semester I continued the project and implemented a sentiment analysis for TechTrends. Based on the existing infrastructure such as our database and our crawler, I add an automated categorization of articles according to their comments on Hackernews and Reddit.

You can find the old and stable version of our project under http://techtrends.mi.hdm-stuttgart.de/. The up-to-date development version is available under http://opiniontrends.mi.hdm-stuttgart.de/.

media_night_ws13

I will present the project at the Media Night at our university next week. It’s open for everybody and for free. It will start around 6 pm, but you can come whenever you want to, there is no schedule. Every project has its own booth, where it is presented and where you can ask question and get in touch with the people behind it.

You can find the program and information about all projects on http://www.hdm-stuttgart.de/medianight.

What? – Media Night Winter 2013
When? – 16th January 2014, from 6 pm to 10 pm
Where? – Hochschule der Medien, Nobelstraße 10, 70569 Stuttgart

Best regards,
Thomas

A GPS diary in four weeks with Play!

During the last four weeks I did another small project for one of my university courses this semester. The course was about ORM modelling and abstractions for databases. We did some exercies with Hibernate and discussed diefferent aspects about ORM modelling. At the end of the course, every student made a small web-project using a framework and ORM tool of his choice.

Most of us used Play! and ebeans to make their application – me too. I didn’t have any experience with Play! but it was very pleasent to work with. Togehter with Foundation CSS prototyping was extremly fast. And ebeans also did a great job. During the whole project I didn’t write any line of SQL – nice!

Online demo

You can see a demo on http://tad.mi.hdm-stuttgart.de/. Just log-in as tad@tad.de with 123456 as your password to see a demo-account with some data.

Presentation

My presentation about TAD: http://tuhrig.de/wp-content/uploads/tad/presentation.html

Code

The code is available on my university’s own Git repository, but it is public:

git clone https://version.mi.hdm-stuttgart.de/git/TAD

Best regards,
Thomas Uhrig

Gelesen: Oracle 9i fĂŒr Einsteiger

Oracle ist eines der weltweit grĂ¶ĂŸten Softwareunternehmen und MarktfĂŒhrer im Bereich der Datenbanken. In den meisten InformatikstudiengĂ€ngen kommt daher einmal der Zeitpunkt, sich mit der Oracle Datenbank nĂ€her auseinander zu setzen.

Oracle 9i fĂŒr Einsteiger - BuchcoverWelche Prozesse laufen im Datenbankenbetrieb ab? Was ist ein Redo-Log? Wie geht Oracle mit Transaktionen um? Was ist PL/SQL?
All diesen Fragen versucht “Oracle 9i fĂŒr Einsteiger” (Hanser Verlage 2002, ISBN 3-446-21921-8) auf den Grund zugehen. Das handliche Einsteigerwerk von M. Abbey, M. Corey und I. Abramson erscheint dabei als autorisierte deutsche Ausgabe der Oracle Press.

Anfangen bei der Geschichte der Datenbank, ĂŒber typische “Objekte” und Terminologien, bis hin zu Datawarehousing, deckt dieses Buch auf knapp 500 Seiten ein breites Basiswissen ab. FĂŒr eine Datenbanken 1, 2 oder sogar 3 Vorlesung allemal genĂŒgen Stoff. Das Buch ist dabei relativ verstĂ€ndlich Geschrieben und liest sich fast durchweg angenehm. Es werden Code-Beispiele gegeben, sowie Kontrollfragen zu jedem Kapitel. Ein zuerst guter Eindruck also.

Das Problem an dieser “deutschen Exklusivausgabe” – wie es auf dem Buchdeckel zu lesen ist – ist die mehr als nachlĂ€ssige Übersetzung:

Seite 219:

Wie viele Zeilen besitzt die Dual-Tabelle?

A. Keine

B. Keine

C. …

Seite 339:

Die DAzugehörige Antwort findet sich in Anhang A.

Seite 368:

Bei Oracle 9i versteht man unter einer Fat Table unserer Meinung nach eine Tabelle mit mehr als zwei Dutzend Zeilen. Derzeit kann eine Tabelle maximal 1000 Spalten enthalten…

Solche Fehler sind zwar verzeihlich (kaum einer wird an eine Fat Table denken, wenn er eine Tabelle mit 24 EintrĂ€gen vor sich hat), aber spĂ€testens beim vierten, fĂŒnften oder sechsten stören sie erheblich. Ähnlich verhĂ€lt es sich mit den zahlreichen Formatierungsfehlern der Code-Beispiele (falsches Highlighting, verwirrendes EinrĂŒcken und wahllose Klammern). Wenn dann noch Kapitel 2 bevorsteht, in dem ĂŒber mehrere Seiten hinweg erklĂ€rt wird was eine Newsgroup, ein Forum oder eine Kundenhotline ist, steigt langsam Ärger auf:

Stellen Sie sicher, dass der Support Service die korrekten Kontaktinformationen der Person erhĂ€lt […]. Falls Sie zeitweise unter einer anderen Telefonnummer oder E-Mail-Adresse zu erreichen sind, brauchen Sie sich hinterher nicht beschweren, dass Sie nicht kontaktiert wurden.

[…] Also – bleiben Sie mit Ihren Erwartungen auf dem Boden und haben sie einfach Geduld.

Bedenkt man, dass dieses Buch als Neuerscheinung rund 70 € gekostet hat (ich habe gĂŒnstig bei eBay erworben), ist es eine Frechheit. Zumal auf dem Buchcover groß und viel versprechend das Oracle Logo und die Worte “Official Authorized” prangen.

Fazit: Hat man die Möglichkeit dieses Buch gĂŒnstig (vielleicht gebraucht bei eBay oder Amazon) zu erstehen, ist es eine lohnende Investition. Es gibt eine schnelle und Angenehme EinfĂŒhrung in das Thema Oracle – vielleicht gerade wegen den zahlreichen Tipp- und Schreibfehlern. FĂŒr den enormen Originalpreis aber, ist dieses Buch mehr als Ă€rgerlich.

Beste GrĂŒĂŸe, Thomas Uhrig.