Ekiras

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 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 In this example we will be using Enums to save a value in database using hibernate. Suppose we have a Enum named Level  and we want to save it in database using hibernate persistence.Then we can map this in the following way. Level.java This is an Enum that defines the level of difficulty of a question. package com.ekiras.enums; public enum Level { LEVEL_ONE, LEVEL_TWO, LEVEL_THREE, LEVEL_FOUR, LEVEL_FIVE; } Qusetion.java This class contains the enum as a field that will be saved to the databa...

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;

Points To Remember 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. Solution : How to ignore Foreign key constraint failure When you need to avoid foreign key constraints, you can do SET FOREIGN_KEY_CHECKS=0; When you again need to set foreign key constraints on you can, SET FOREIGN_KEY_CHECKS=1; Note : This is not recommended If you are trying to insert some data to your tables when your constrains are off, you can end up adding raw data or incorrect data to the tables due to which you application may suffer. So it is advised that you use it only when it is required with high priority and where you are sure that you are not messing up with your table relations and mappings.

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

Hibernate provides the annotation @NotFound .  This annotation can be used in cases where we have relationships between the Entities e.g. ManyToOne or OneToMany or ManyToMany etc. Syntax - @NotFound(action=NotFoundException.IGNORE) When we want to access an entity using the relation from another entity, if the value of this entity is not available then the hibernate throws an exception. We  can use @NotFound annotation to ignore these exceptions.  NotFoundException.IGNORE tells the hibernate to avoid throwing the exception if the record for the entity is not found. @Entity(name="user") @Table(name="user") public class User{ }

There are 4 types of Generation Strategies that can be used to generate Id in hibernate. AUTO   e.g. strategy = GenerationType.AUTO IDENTITY  e.g. strategy = GenerationType.IDENTITY SEQUENCE  e.g. strategy = GenerationType.SEQUENCE TABLE  e.g. strategy = GenerationType.TABLE strategy = GenerationType.AUTO This is the default Generation Strategy that is used by the hibernate. This strategy allows hibernate to decide what strategy to be used to generate the unique key for the table. Strategies used by hibernate can be different for different types of databases used. strategy = GenerationType.IDENTITY In this Generation Strategy the hibernate is going to use an identity column. Identity column is a feature that is provided in some of the databases, this is not a generic feature and is provided by a few databases only. strategy = GenerationType.SEQUENCE In this Generation Strategy, the hibernate generate unique keys by a sequence hilo. This uses a sequence ob...