November 22, 2017
HomeDevelopmentSpring MVC + Hibernate with AngularJSDefine Database Schema and Hibernate Configuration No ratings yet.

Define Database Schema and Hibernate Configuration

By  Spring MVC + Hibernate with AngularJS  0 Comments

Our example will demonstrate three different table relationships:

  • One-to-One
  • One-to-Many
  • Many-to-Many

You can download the schema.sql here, and run it with MySQL client.

/* create database */

CREATE DATABASE itblogs DEFAULT CHARACTER SET utf8;

 

USE itblogs;

 

/* One-to-One relationship */

/* SHOP – ADDRESS */

CREATE TABLE ADDRESS(

id BIGINT NOT NULL AUTO_INCREMENT,

building VARCHAR(50) NOT NULL,

street VARCHAR(50) NOT NULL,

city  VARCHAR(50) NOT NULL,

country  VARCHAR(50) NOT NULL,

PRIMARY KEY (id)

);

 

CREATE TABLE SHOP (

id BIGINT NOT NULL AUTO_INCREMENT,

name VARCHAR(50) NOT NULL,

address_id BIGINT NOT NULL,

telephone VARCHAR(50) NOT NULL,

fax VARCHAR(50) NOT NULL,

email VARCHAR(50) NOT NULL,

status  TINYINT(1),

PRIMARY KEY (id),

CONSTRAINT shop_address FOREIGN KEY (address_id) REFERENCES ADDRESS (id)

);

 

/* One-to-Many relationship */

/* CUSTOMER – ORDERS */

 

CREATE TABLE CUSTOMER (

id BIGINT NOT NULL AUTO_INCREMENT,

firstname VARCHAR(50) NOT NULL,

lastname VARCHAR(50) NOT NULL,

telephone VARCHAR(50) NOT NULL,

email VARCHAR(50) NOT NULL,

age TINYINT UNSIGNED,

gender char(1),

PRIMARY KEY (id)

);

 

CREATE TABLE ORDERS (

id BIGINT NOT NULL AUTO_INCREMENT,

customer_id BIGINT,

order_date TIMESTAMP NOT NULL,

price DOUBLE,

status  TINYINT(1),

CONSTRAINT customer_orders FOREIGN KEY (customer_id) REFERENCES CUSTOMER(id),

PRIMARY KEY (id)

);

 

/* Many-to-Many relationship */

/* ORDER – ORDER_PRODUCT – PRODUCT */

CREATE TABLE PRODUCT (

id BIGINT NOT NULL AUTO_INCREMENT,

name VARCHAR(50) NOT NULL,

details VARCHAR(255) NOT NULL,

price DOUBLE,

status  TINYINT(1),

PRIMARY KEY (id)

);

 

CREATE TABLE ORDER_PRODUCT (

order_id BIGINT NOT NULL,

product_id BIGINT NOT NULL,

PRIMARY KEY (order_id, product_id),

FOREIGN KEY (order_id) REFERENCES ORDERS (id),

FOREIGN KEY (product_id) REFERENCES PRODUCT (id)

);

 

INSERT INTO ADDRESS(building,street,city,country) VALUES (‘NY building’,’NY street’,’NewYork’,’USA’);

INSERT INTO ADDRESS(building,street,city,country) VALUES (‘HK A building’,’HK First street’,’Hong Kong’,’China’);

INSERT INTO ADDRESS(building,street,city,country) VALUES (‘HK B building’,’HK Second street’,’Hong Kong’,’China’);

INSERT INTO ADDRESS(building,street,city,country) VALUES (‘SY A building’,’SY First street’,’Sydney’,’Australia’);

INSERT INTO ADDRESS(building,street,city,country) VALUES (‘SY B building’,’SY Second street’,’Sydney’,’Australia’);

 

INSERT INTO SHOP(name,address_id,telephone,fax,email,status) VALUES (‘NewYork Shop’,1,'(ny) 1111 1111′,'(ny) 2222 2222′,’newyork@itblogs.com’,1);

INSERT INTO SHOP(name,address_id,telephone,fax,email,status) VALUES (‘Hong Kong Shop 1′,2,'(hk) 1111 1112′,'(hk) 2222 2223′,’hongkong_1@itblogs.com’,1);

INSERT INTO SHOP(name,address_id,telephone,fax,email,status) VALUES (‘Hong Kong Shop 2′,3,'(hk) 1111 1113′,'(hk) 2222 2224′,’hongkong_2@itblogs.com’,1);

INSERT INTO SHOP(name,address_id,telephone,fax,email,status) VALUES (‘Sydney Shop 1′,4,'(AU) 1111 1114′,'(AU) 2222 2225′,’sydney_1@itblogs.com’,1);

INSERT INTO SHOP(name,address_id,telephone,fax,email,status) VALUES (‘Sydney Shop 2′,5,'(AU) 1111 1115′,'(AU) 2222 2226′,’sydney_2@itblogs.com’,1);

 

INSERT INTO CUSTOMER(firstname,lastname,telephone,email,age,gender) VALUES (‘Peter’,’Pan’,’1111 1111′,’peterpan@itblogs.com’,100,’M’);

INSERT INTO CUSTOMER(firstname,lastname,telephone,email,age,gender) VALUES (‘David’,’Pan’,’1111 1222′,’davidpan@itblogs.com’,60,’M’);

INSERT INTO CUSTOMER(firstname,lastname,telephone,email,age,gender) VALUES (‘Mary’,’Pan’,’1111 1333′,’marypan@itblogs.com’,20,’F’);

 

INSERT INTO ORDERS(customer_id,order_date,price,status) VALUES (1,’2015-01-01 00:00:01′,’1000′,1);

INSERT INTO ORDERS(customer_id,order_date,price,status) VALUES (1,’2015-05-01 10:00:01′,’2000′,1);

INSERT INTO ORDERS(customer_id,order_date,price,status) VALUES (2,’2015-01-10 00:00:01′,’500′,1);

INSERT INTO ORDERS(customer_id,order_date,price,status) VALUES (2,’2015-03-05 12:00:01′,’3000′,1);

INSERT INTO ORDERS(customer_id,order_date,price,status) VALUES (2,’2015-03-06 14:00:01′,’300′,1);

INSERT INTO ORDERS(customer_id,order_date,price,status) VALUES (2,’2015-10-22 09:00:01′,’2500′,1);

INSERT INTO ORDERS(customer_id,order_date,price,status) VALUES (3,’2015-02-15 13:30:01′,’4300′,1);

INSERT INTO ORDERS(customer_id,order_date,price,status) VALUES (3,’2015-06-08 15:00:01′,’800′,1);

INSERT INTO ORDERS(customer_id,order_date,price,status) VALUES (3,’2015-12-22 16:30:01′,’10500′,1);

 

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘product_1′,’product_1_details’,100,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘product_2′,’product_2_details’,110,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘product_3′,’product_3_details’,120,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘product_4′,’product_4_details’,130,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘product_5′,’product_5_details’,140,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘product_6′,’product_6_details’,150,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘product_7′,’product_7_details’,160,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘product_8′,’product_8_details’,170,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘product_9′,’product_9_details’,180,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘product_10′,’product_10_details’,190,1);

 

commit;

 

After running successfully, you should find the new tables under itblogs database as:

 

Then, let’s create a Hibernate configuration file called HibernateConfiguration.java under src/main/java/com/itblogs/config/ as:

package com.itblogs.config;

import java.util.Properties;

import javax.sql.DataSource;

import org.hibernate.SessionFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.ComponentScan;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.PropertySource;

import org.springframework.core.env.Environment;

import org.apache.commons.dbcp2.BasicDataSource;

import org.springframework.orm.hibernate4.HibernateTransactionManager;

import org.springframework.orm.hibernate4.LocalSessionFactoryBean;

import org.springframework.transaction.annotation.EnableTransactionManagement;

/**

 * @author      Kelvin Hui

 * @see              it-blogs.com

 * @since            01-Jan-2016

 */

@Configuration

@ComponentScan({ “com.itblogs.config” })

@EnableTransactionManagement

@PropertySource(value = { “classpath:application.properties” })

public class HibernateConfiguration {

    private static final String CONNECTION_DRIVERCLASSNAME           = “connection.driverClassName”;

    private static final String CONNECTION_URL                             = “connection.url”;

    private static final String CONNECTION_USERNAME                  = “connection.username”;

    private static final String CONNECTION_PASSWORD                  = “connection.password”;

    private static final String DBCP_INITIALSIZE                           = “dbcp.initialSize”;

    private static final String DBCP_MAXTOTAL                              = “dbcp.maxTotal”;

    private static final String DBCP_MAXIDLE                               = “dbcp.maxIdle”;

    private static final String HIBERNATE_DIALECT                          = “hibernate.dialect”;

    private static final String HIBERNATE_SHOW_SQL                         = “hibernate.show_sql”;

    private static final String HIBERNATE_FORMAT_SQL                 = “hibernate.format_sql”;

    private static final String PACKAGES_TO_SCAN                           = “com.itblogs.model”;

    private static final int DBCP_INITIAL_SIZE                             = 10;

    private static final int DBCP_MAX_TOTAL                                      = 50;

    private static final int DBCP_MAX_IDLE                                       = 10;

       @Autowired

    private Environment environment;

    @Bean

    public LocalSessionFactoryBean sessionFactory() {

        LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();

        sessionFactory.setDataSource(dataSource());

        sessionFactory.setPackagesToScan(new String[] { PACKAGES_TO_SCAN });

        Properties properties = new Properties();

        properties.put(HIBERNATE_DIALECT, environment.getRequiredProperty( HIBERNATE_DIALECT ));

        properties.put(HIBERNATE_SHOW_SQL, environment.getRequiredProperty( HIBERNATE_SHOW_SQL ));

        properties.put(HIBERNATE_FORMAT_SQL, environment.getRequiredProperty(HIBERNATE_FORMAT_SQL));

        sessionFactory.setHibernateProperties(properties);

        return sessionFactory;

     }

    @Bean

    public DataSource dataSource() {

       BasicDataSource dataSource = new BasicDataSource();

        dataSource.setDriverClassName(environment.getRequiredProperty( CONNECTION_DRIVERCLASSNAME ));

        dataSource.setUrl(environment.getRequiredProperty( CONNECTION_URL ));

        dataSource.setUsername(environment.getRequiredProperty( CONNECTION_USERNAME ));

        dataSource.setPassword(environment.getRequiredProperty( CONNECTION_PASSWORD ));

        int initialSize = DBCP_INITIAL_SIZE;

        int maxTotal = DBCP_MAX_TOTAL;

        int maxIdle = DBCP_MAX_IDLE;

        try { initialSize = Integer.parseInt( environment.getRequiredProperty( DBCP_INITIALSIZE ) ); } catch(Exception ie) { }

        try { maxTotal = Integer.parseInt( environment.getRequiredProperty( DBCP_MAXTOTAL ) ); } catch(Exception ie) { }

        try { maxIdle = Integer.parseInt( environment.getRequiredProperty( DBCP_MAXIDLE ) ); } catch(Exception ie) { }

        dataSource.setInitialSize(initialSize);

        dataSource.setMaxTotal(maxTotal);

        dataSource.setMaxIdle(maxIdle);

        return dataSource;

    }

    @Bean

    @Autowired

    public HibernateTransactionManager transactionManager(SessionFactory sessionFactory) {

       HibernateTransactionManager txManager = new HibernateTransactionManager();

       txManager.setSessionFactory(sessionFactory);

       return txManager;

    }

}

 

And create a configuration file called “application.properties” under src/main/resources/ as:

connection.driverClassName = com.mysql.jdbc.Driver

connection.url = jdbc:mysql://localhost:3306/itblogs

connection.username = root

connection.password = root

dbcp.initialSize = 10

dbcp.maxTotal = 50

dbcp.maxIdle = 10

hibernate.dialect = org.hibernate.dialect.MySQLDialect

hibernate.show_sql = true

hibernate.format_sql = true

 

It is using to store the JDBC database connection parameters, the HibernateConfiguration.java will read this file and configure the DB connection when application startup. You may need to update the connection.username and connection.password for your own database connection.

connection.driverClassName Database connection JDBC driver class name
connection.url Connection URL for your database
connection.username Username to connect your DB
connection.password Password to connect your DB
dbcp.initialSize DBCP : Initial size of the DB connection pool
dbcp.maxTotal DBCP : Max number of DB connection(s)
dbcp.maxIdle DBCP : Max number of Idle DB connection(s)

It must be less than dbcp.maxTotal
hibernate.dialect Define the Hibernate SQL dialect of the database
hibernate.show_sql Whether to show the SQL to log/console
hibernate.format_sql Whether to well-format the output SQL

 

Next chapter, it will show you how to create the model classes.

Please rate this

Tags:, , , , , ,

Related Posts

About Author

Kelvin Hui