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

• 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

• Enums are special java classes that are used to declare constants and methods.
• Enums have all the variables declared as public static final.
• Enums variables can be compared using == operator.
• End of a enum can be declared by a semi colon, but this is optional.
• There are two ways to use Enums 
• ORDINAL (saves the enum values in integer format starting from 0.)
• STRING (saves the enum value in String, takes the value of the field itself. )

Use Enums in Hibernate

package com.ekiras.enums;

public enum Level {

 LEVEL_ONE,
 LEVEL_TWO,
 LEVEL_THREE,
 LEVEL_FOUR,
 LEVEL_FIVE;
}

package com.ekiras.domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import com.ekiras.enums.Level;

@Entity
@Table(name="question")
public class Question {
  
        @Id
        @Column(name="id")
        @GeneratedValue(strategy=GenerationType.AUTO)
        private Long id;

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

        @Column(name="level")
        @Enumerated(EnumType.ORDINAL)
        private Level status = Level.LEVEL_ONE;
  
        // GETTERS and SETTERS

}

        
 @Column(name="level")
 @Enumerated(EnumType.STRING)
 private Level status = Level.LEVEL_ONE;

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

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

Taking dump of a table

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

Taking dump of a remote database

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

Taking dump of a remote database table

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

• 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. 

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

insert into person select * from person_old;

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

• A foreign key is a key that is a primary key in some another and is used to maintain a relationship with this table.
• Foreign key constraint failure occurs when we are trying to assign a value to a foreign key, that does not exist in the other table of which it is primary key.

SET FOREIGN_KEY_CHECKS=0;

SET FOREIGN_KEY_CHECKS=1;

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

• 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 ;

• 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");
     }
}

• 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. 

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 |

@Entity(name="user")
@Table(name="user")
public class User{
}

1. AUTO  e.g. strategy = GenerationType.AUTO
2. IDENTITY e.g. strategy = GenerationType.IDENTITY
3. SEQUENCE e.g. strategy = GenerationType.SEQUENCE
4. TABLE e.g. strategy = GenerationType.TABLE