Ekiras

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  JDBC Mysql JPA MVC Gradle Create Basic CRUD for Person Entity with JPA and Mysql Download from GitHub 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 rep...

Suppose you have a dump of more than one databases in a single dump file, then you can restore one or all the databases. 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. For more specific restoration of databases from a dump file you can try mydumpsplitter.  

Points To Remember You can take dump of Mysql using mysqldump  in windows, linux or ubuntu operating systems. 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 ta...

Points To Remember 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.   Example : Insert Values from one table to another. 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;

Why this question is important ? 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. Example  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 larges...

Points To Remember  Download  Mysql Java Connector  by clicking on the link. Put the above downloaded jar file in the lib  folder of your project. Code : Mysql JDBC connection in Java 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 JavaMysqlC...

Syntax : SHOW CREATE TABLE tablename When To Use ? 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.  Example 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...