Using PostgreSQL, hibernate, spring, and Java to implement nosql in SQL databases

As we all know, relational data types have always been one of SQL's criticism. With the increasing variety of data, the conversion from various data types to relational data types has plagued most SQL database users. However, the advantages of SQL databases are undeniable. If you can have both the fish and the bear's paw-implementing nosql Data Types in the SQL database, isn't everything a wonderful change ?!

It is said that everything in the world has to lose, and in the world of 0 and 1, this is more obvious. While having the advantages of some columns, the schema-free nosql storage does not offer much. The main advantage of the nosql movement is to give people a variety of choices on the data persistence layer. Using nosql, we do not need to convert all data into relational data models. The biggest challenge today is not that of selecting persistent data models in each domain system and closely integrating subsequent models. There are many ways to solve this problem. The general method is polyglot peristence. Next, let's take a look at how to closely integrate common SQL models with key-value nosql models through Java, spring, hibernate, and PostgreSQL.

This article involves a simple network application that uses conventional SQL and PostgreSQL hstore-type key-value pairs. The idea is to mix nosql into SQL. The advantage of this method is that SQL and nosql data can be stored in the same data storage.

This example will cover the Java, spring, and hibernate server technologies. Of course, it can also be implemented through rails, Django, and other technologies. In order to add hstore support to hibernate, the article "store PostgreSQL hstore type key/value pairs in a single database row through hibernate" was specially queried. Although the encoding content will not be discussed here, you can use GitHub Repo for my demo project to get everything you want.

The demo application uses Maven to define dependencies. Jetty is embedded through a simple Ole Java application. Spring is configured through the main, web, and database sections in Java config.

The Client technology will use jquery and bootstrap, while the client and server will be strictly separated through the restful JSON service. The entire client is stored in a simple Ole HTML file. Communication between the client and JSON is implemented through jquery/ajax, which is declared in spring MVC controller.

Let's get back to implementing nosql in SQL. Although the "contacts" stored by this application has only one name attribute, it does not prevent it from having "contacts methods" (such as phone numbers and email addresses ). "Contact methods" is a very good non-mode key-Value Pair column because it avoids tedious alternative options: put the information into a separate table or try to create a class containing all possible "contact methods. Let's take a look at the simple contact class:

1. Package com. jamesward. model;
2.  
3. Import net. backtothefront. hstoreusertype;
4. Import org. hibernate. Annotations. type;
5. Import org. hibernate. Annotations. typedef;
6.  
7. Import javax. Persistence. column;
8. Import javax. Persistence. entity;
9. Import javax. Persistence. generatedvalue;
10. Import javax. Persistence. ID;
11. Import java. util. hashmap;
12. Import java. util. Map;
13.  
14. @ Entity
15. @ Typedef (name = "hstore", typeclass = hstoreusertype. Class)
16. Public class contact {
17.  
18. @ ID
19. @ Generatedvalue
20. Public integer ID;
21.  
22. @ Column (nullable = false)
23. Public string name;
24.  
25. @ Type (type = "hstore ")
26. @ Column (columndefinition = "hstore ")
27. Public Map <string, string> contactmethods = new hashmap <string, string> ();
28.  
29. }

If you are familiar with hibernate/JPA, the above will be very familiar to you. The strange thing to attract you is the contactmethods object modification-Map <string, string>, and the PostgreSQL hostore data type. For normal operation, the hostore data type must be defined and the columndefinition set. Thanks again to Jakub gluszecki for its integration of hstorehelper and hostoreusertyper. Otherwise, this will not be possible.

Because it is just a simple hibernate/JPA, the rest is relatively simple. The following is the contactservice class for basic query and modification:

1. Package com. jamesward. Service;
2.  
3. Import com. jamesward. model. contact;
4. Import org. springframework. stereotype. Service;
5. Import org. springframework. transaction. annotation. Transactional;
6.  
7. Import javax. Persistence. entitymanager;
8. Import javax. Persistence. persistencecontext;
9. Import javax. Persistence. Criteria. criteriaquery;
10.  
11. Import java. util. List;
12.  
13. @ Service
14. @ Transactional
15. Public class contactserviceimpl implements contactservice {
16.  
17. @ Persistencecontext
18. Entitymanager em;
19.  
20. @ Override
21. Public void addcontact (contact ){
22. Em. persist (contact );
23. }
24.  
25. @ Override
26. Public list <contact> getallcontacts (){
27. Criteriaquery <contact> C = em. getcriteriabuilder (). createquery (contact. Class );
28. C. From (contact. Class );
29. Return em. createquery (c). getresultlist ();
30. }
32. Public contact getcontact (integer ID ){
33. Return em. Find (contact. Class, ID );
34. }
35.  
36. @ Override
37. Public void addcontactmethod (integer contactid, string name, string value ){
38. Contact contact = getcontact (contactid );
39. Contact. contactmethods. Put (name, value );
40. }
42. }

After you understand its working mechanism, we will start to try the demonstration on Heroku.

If you want to run this application locally or on Heroku, you must extract the source code and create the spring_hibernate_hstore_demo directory:

1. $ Git clone https://github.com/jamesward/spring_hibernate_hstore_demo.git
2. $ CD spring_hibernate_hstore_demo

What you need to do to run locally:

1. Create a connection to pssql to allow PostgreSQL database support for hstore:

1. $ Psql-u username-w-H localhost Database

2. Enable hstore:

1. => Create extension hstore;
2. => \ Q

3. Create an application (Maven-dependent installation ):

1. $ MVN package

4. Set the database_url environment variable to point to your PostgreSQL Server:

1. $ Export database_url = Postgres: // username: password @ localhost/databasename

5. Run the application:

1. $ Java-CP target/classes: Target/dependency/* COM. jamesward. webapp

6. Try again.

Perfect ?! Now, try again to run on the cloud through Heroku. Follow these steps:

1. Install hero toolbelt

2. log on to Heroku:

1. $ Heroku Login

3. Create a new application:

1. $ Heroku create

4. Add Heroku Postgres:

1. $ Heroku Addons: Add Heroku-PostgreSQL: Dev

5. Notify Heroku to complete the database_usl settings based on the database you just added (manually reset your_heroku_postgesql_color_url ):

1. $ Heroku PG: Promote your_heroku_postgresql_color_url

6. Open the Psql connection to the database:

1. $ Heroku PG: Psql

7. Let hstore support your database:

1. => Create extension hstore;
2. => \ Q

8. Deploy the application:

1. $ Git push Heroku master

9. View applications on the cloud:

1. $ Heroku open

Here we have completed all the demonstrations of using PostgreSQL, hibernate, spring, and Java to implement nosql in SQL databases, and the integration of nosql and SQL will be more perfect under brainstorming! (The original compilation/Zhong Hao Bao research/editor is from James Ward)