How to enable Hibernate SQL Logging in Spring Boot using application.properties

You can enable hibernate sql logging level to Debug. This will print the sql queries fired by hibernate.

logging.level.org.hibernate.SQL=DEBUG

Your complete SQL configuration may look like following

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://localhost/ekiras
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
logging.level.org.hibernate.SQL=DEBUG

This is a sample Spring Boot Application that uses 

1. JDBC
2. Mysql
3. JPA
4. MVC
5. Gradle

Create Basic CRUD for Person Entity with JPA and Mysql

Project Structure

build.gradle

View Maven Dependencies (pom.xml) here.

buildscript {
    ext {
        springBootVersion = '1.2.5.RELEASE'
    }
    repositories {
        maven { url "http://repo.spring.io/libs-milestone" }
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}") 
        classpath("io.spring.gradle:dependency-management-plugin:0.5.1.RELEASE")
    }
}

apply plugin: 'java'
apply plugin: 'eclipse-wtp'
apply plugin: 'idea'
apply plugin: 'spring-boot' 
apply plugin: 'io.spring.dependency-management' 
apply plugin: 'war'


war {
    baseName = 'springboot'
    version = '0.0.1-SNAPSHOT'
}
sourceCompatibility = 1.7
targetCompatibility = 1.7

repositories {
    mavenCentral()
    maven { url "http://repo.spring.io/libs-milestone" }
}

configurations {
    providedRuntime
}

dependencies {
    compile("org.springframework.boot:spring-boot-starter-data-jpa")
    compile("javax.servlet:jstl:1.2")
    runtime("mysql:mysql-connector-java")
    compile("org.springframework.boot:spring-boot-starter-jdbc")
    compile ("org.springframework.boot:spring-boot-starter-web")
    providedRuntime("org.springframework.boot:spring-boot-starter-tomcat")
    providedRuntime 'org.apache.tomcat.embed:tomcat-embed-jasper'
}


eclipse {
    classpath {
         containers.remove('org.eclipse.jdt.launching.JRE_CONTAINER')
         containers 'org.eclipse.jdt.launching.JRE_CONTAINER/org.eclipse.jdt.internal.debug.ui.launcher.StandardVMType/JavaSE-1.7'
    }
}

task wrapper(type: Wrapper) {
    gradleVersion = '2.3'
}

AppConfig.java

package com.ekiras.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.web.servlet.config.annotation.DefaultServletHandlerConfigurer;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;
import org.springframework.web.servlet.view.InternalResourceViewResolver;

/**
 * Created by ekansh on 12/7/15.
 */
@Configuration
public class AppConfiguration extends WebMvcConfigurerAdapter{

    @Override
    public void configureDefaultServletHandling(DefaultServletHandlerConfigurer configurer) {
        configurer.enable();
    }

    @Bean
    public InternalResourceViewResolver viewResolver() {
        InternalResourceViewResolver resolver = new InternalResourceViewResolver();
        resolver.setViewClass(org.springframework.web.servlet.view.JstlView.class);
        resolver.setPrefix("/WEB-INF/views/");
        resolver.setSuffix(".jsp");
        return resolver;
    }

}

Person.java

package com.ekiras.domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * Created by ekansh on 12/7/15.
 */
@Entity
public class Person {

    @Override
    public String toString(){
        return this.name;
    }

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "email", nullable = false, unique = true)
    private String email;

    @Column(name = "password")
    private String password;

    @Column(name = "mobile")
    private String mobile;

    private transient String confirmPassword;

    // Getters and Setters
}

PersonRepository.java

package com.ekiras.repository;

import com.ekiras.domain.Person;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

/**
 * Created by ekansh on 14/7/15.
 */
@Repository
public interface PersonRepository extends CrudRepository<Person,Long> {

}

PersonService.java

package com.ekiras.service;

import com.ekiras.dao.PersonDAO;
import com.ekiras.domain.Person;
import com.ekiras.repository.PersonRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Created by ekansh on 13/7/15.
 */
@Service
public class PersonService {

    @Autowired
    private PersonDAO personDAO;

    @Autowired
    private PersonRepository personRepository;

    public Object findAll(){
        return personRepository.findAll();
    }

    public Person findById(Long id){
        return personRepository.findOne(id);
    }

    public Person save(Person person){
        return personRepository.save(person);
    }

}

PersonController.java

package com.ekiras.controller;

import com.ekiras.domain.Person;
import com.ekiras.service.PersonService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

/**
 * Created by ekansh on 12/7/15.
 */

@Controller
@RequestMapping(value = {"","/person"})
public class PersonController {

    @Autowired
    private PersonService personService;


    @RequestMapping(value = {"/","index"})
    public String index(Model model){
        System.out.println(personService.findAll());
        model.addAttribute("persons", personService.findAll());
        return "person/index";
    }

    @RequestMapping(value = "create")
    public String create(){
        return "person/create";
    }

    @RequestMapping(value = "save", method = RequestMethod.POST)
    public String save(Person person){
        personService.save(person);
        return "redirect:index";
    }

    @RequestMapping(value = "edit/{id}")
    public String edit(@PathVariable Long id, Model model){
        model.addAttribute("person",personService.findById(id));
        return "person/edit";
    }


    @RequestMapping(value = "update",method = RequestMethod.POST)
    public String update(Person person){
        personService.save(person);
        return "redirect:index";
    }


    @RequestMapping(value = "/test")
    public String test(){
        return " hello world";
    }


}

With all the above configurations your code is ready to do the CRUD on Person Entity.

Run the following command to run the project.

gradle bootRun

mysql -u root -p --one-database DBNAME < DUMPFILE.SQL

You should take care of a few things when you take the dump.

• Create a database by that name in mysql if it does not exist.
• Switch to the database you want to restore, this will help restore the database properly.
• You need to be outside mysql environment to run this command.

Points To Remember

1. You can take dump of Mysql using mysqldump in windows, linux or ubuntu operating systems.
2. You should reach to the location of mysql/bin in a windows platform to use mysqldump, you can use from any location in ubuntu and linux.

Taking dump of whole database

When you want to take the dump of the whole file, you can simply do it with the following command

mysqldump -u db_username -p database_name > path/dump.sql

Here the database_name is your database name and path is absolute path where you want to create dump.

Taking dump of a table

When you want to take the dump of a single table you can do this by the following command.

mysqldump -u db_username -p database_name tablename > path/dump.sql

Taking dump of a remote database

When you want to take a dump of a remote user you can do this by the following command.

mysqldump -u <db_username> -h <db_host> -p database_name > path/dump.sql

Taking dump of a remote database table

When you want to take the dump of a single table from a remote database you can use the following command

mysqldump -u <db_username> -h <db_host> -p database_name table_name > path/dump.s

How to restore the database from a dump file.

• We can use subqueries in databases like Mysql and avoid multiple queries.
• Syntax - INSERT INTO table1 SELECT * FROM table2;
• We do not write VALUES in the query. 

Suppose we have two table person and person_old with columns

• id int primary key
• name varchar
• age number
• contact varchar
• description text

And we want to copy all the details of the table person_old to table person. Then we must write our query as. This will insert all the rows of the person_old table to the new table person.

insert into person select * from person_old;

Also if we want to add only a few columns of the table person_old to the table person.Suppose we want to add only name, age and contact to the table person from table person_old where age is less than 25, then we can query as follows.

insert into person(name,age,contact) select name,age,contact from person_old  where age < 25;

This is a most frequently asked question in an interview these days. This question tests the thinking of a candidate and how much the candidate knows about the database. This question  cannot be done without the use of a sub query and most of the people are not comfortable writing the sub-queries. So if you are not very comfortable with database queries you might get tricked in this question.
Suppose we have a table with id's from 1 to 1000 and we need to find the second largest id from this table.
Let's say we have table

create table test(id int not null primary_key, name varchar(50));

So we can get the second largest value of id in the following ways.

• SELECT max(id) FROM test WHERE id!=(SELECT MAX(id) FROM test GROUP BY id) GROUP BY id;

• SELECT MIN(id) FROM test (SELECT id FROM test ORDER BY id DESC LIMIT 2) t ;

In Query 1 : SELECT max(id) FROM test WHERE id!=(SELECT MAX(id) FROM test GROUP BY id) GROUP BY id;
We first find the largest id from the table "test" by the sub query "(SELECT MAX(id) FROM test GROUP BY id)", in our case this will give 1000 .Now we select the max "id" from table test where id is not equal to this id, thus it will give 999 as the max id.

In Query 2 : SELECT MIN(id) FROM test (SELECT id FROM test ORDER BY id DESC LIMIT 2) t ;
We first find the number of records in decreasing order that we have to find, suppose we have to find the second largest value then we will find 2 records, if we need to find the third largest value then we need to find the three largest records.SELECT id FROM test ORDER BY id DESC LIMIT 2, gives us 2 records for id = 1000 and 999. Now we select the id with the minimum value, i.e id =999, which is minimum between 1000 and 999.

• Download Mysql Java Connector by clicking on the link.
• Put the above downloaded jar file in the lib folder of your project.

import java.sql.Connection;
import java.sql.DriverManager;


public class JavaMysqlConnection {

 
    private String host = "localhost:3306"; // For running on localhost
    private String db = "databaseName";
    private String userName = "userName";
    private String password = "password";
     
    public Connection getConnection() {
       try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn =  DriverManager.getConnection("jdbc:mysql://"+ host +"/"+db,userName, password);
            return conn;
       }catch(Exception e){
             e.printStackTrace();
       }
       return null;
    }
}

   

public class Test{

      public static void main(String[] argv) {
         Connection conn =  new JavaMysqlConnection().getConnection();
         if(conn == null)
             System.out.println("Connection was not established");
         else
             System.out.println("Connection is successfully established");
     }
}

This is a very generic code that you can use in both your standalone projects and web based projects. Whenever you need to get a connection to database, you just need to create an object of class JavaMysqlConnection and call its getConnection() method.

• You already have a table in the database and you want to view how this table was created.
• You want to check all the datatypes, default value, nullability of the columns.
• You want to see all the constraints and keys that are applicable to this table.
• This command can be used to recreate the table as it is with all constraints and keys set. 

Suppose we have two tables Institute and User  and we want to check the schema of the tables or in simple terms how these tables were created, then we will use this command SHOW CREATE TABLE user, where user is the table name whose schema we want to check.
The above command will give the following output.

user  | CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `city` varchar(255) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `dob` datetime DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `firstName` varchar(30) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `is_enabled` tinyint(1) NOT NULL,
  `is_expired` tinyint(1) DEFAULT NULL,
  `lastName` varchar(30) DEFAULT NULL,
  `last_updated` datetime NOT NULL,
  `mobile` bigint(20) DEFAULT NULL,
  `pwd` varchar(255) NOT NULL,
  `token` varchar(255) DEFAULT NULL,
  `user_role` int(11) NOT NULL,
  `institute_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_52d8b7773f334f29885182c5fad` (`email`),
  KEY `FK_d87d9b2cd99f4f999b750d83637` (`institute_id`),
  CONSTRAINT `FK_d87d9b2cd99f4f999b750d83637` FOREIGN KEY (`institute_id`) REFERENCES `institute` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |