Hibernate One to one

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>HibernateOneToOne</groupId>

  <artifactId>HibernateOneToOne</artifactId>

  <version>0.0.1-SNAPSHOT</version>

  <build>

    <sourceDirectory>src</sourceDirectory>

    <plugins>

      <plugin>

        <artifactId>maven-compiler-plugin</artifactId>

        <version>3.5.1</version>

        <configuration>

          <source>1.8</source>

          <target>1.8</target>

        </configuration>

      </plugin>

    </plugins>

  </build>

  <dependencies>

  <dependency>

  <groupId>org.hibernate</groupId>

  <artifactId>hibernate-core</artifactId>

  <version>4.3.5.Final</version>

  </dependency>

  <dependency>

  <groupId>mysql</groupId>

  <artifactId>mysql-connector-java</artifactId>

  <version>5.0.5</version>

  </dependency>

  </dependencies>

</project>

<?xml version="1.0" encoding="utf-8"?>

<!DOCTYPE hibernate-configuration SYSTEM 

"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

   <session-factory>

   <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

   <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>

   <!-- Assume test is the database name -->

   <property name="hibernate.connection.url">jdbc:mysql://localhost/telcoma</property>

   <property name="hibernate.connection.username">root</property>

   <property name="hibernate.connection.password"></property>

   <property name="show_sql">true</property>

<property name="hbm2ddl.auto">update</property>

<mapping class="com.telcoma.Employee"/>

<mapping class="com.telcoma.Address"/>

</session-factory>

</hibernate-configuration>

package com.telcoma;

import javax.persistence.CascadeType;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

import javax.persistence.OneToOne;

import javax.persistence.Table;

@Entity

@Table(name="employee")

public class Employee {

@Id

@GeneratedValue

@Column(name="empId")

private int empId;

@Column(name="name")

private String name;

@OneToOne(cascade=CascadeType.ALL)

private Address address;

public int getEmpId() {

return empId;

}

public void setEmpId(int empId) {

this.empId = empId;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public Address getAddress() {

return address;

}

public void setAddress(Address address) {

this.address = address;

}

}

package com.telcoma;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

import javax.persistence.Table;

@Entity

@Table(name="address")

public class Address {

@Id

@GeneratedValue

@Column(name="id")

private int id;

@Column(name="city")

private String city;

@Column(name="country")

private String country;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getCity() {

return city;

}

public void setCity(String city) {

this.city = city;

}

public String getCountry() {

return country;

}

public void setCountry(String country) {

this.country = country;

}

}

package com.telcoma;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.Transaction;

import org.hibernate.cfg.Configuration;

public class MainClass {

public static void main(String[] args) {

Configuration configuration =new Configuration();

configuration.configure("hibernate.cfg.xml");

SessionFactory sessionFactory=configuration.buildSessionFactory();

Session session=sessionFactory.openSession();

Transaction transaction =session.beginTransaction();

Address address1= new Address();

address1.setCity("Chandigarh");

address1.setCountry("india");

Address address2= new Address();

address2.setCity("dehli");

address2.setCountry("India");

Employee employee =new Employee();

employee.setName("telcoma");

employee.setAddress(address1);

Employee employee2 =new Employee();

employee2.setName("test");

employee2.setAddress(address2);

session.persist(employee);

session.persist(employee2);

transaction.commit();

session.close();

sessionFactory.close();

}

}

Jun 17, 2017 9:43:56 AM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>

INFO: HCANN000001: Hibernate Commons Annotations {4.0.4.Final}

Jun 17, 2017 9:43:56 AM org.hibernate.Version logVersion

INFO: HHH000412: Hibernate Core {4.3.5.Final}

Jun 17, 2017 9:43:56 AM org.hibernate.cfg.Environment <clinit>

INFO: HHH000206: hibernate.properties not found

Jun 17, 2017 9:43:56 AM org.hibernate.cfg.Environment buildBytecodeProvider

INFO: HHH000021: Bytecode provider name : javassist

Jun 17, 2017 9:43:56 AM org.hibernate.cfg.Configuration configure

INFO: HHH000043: Configuring from resource: hibernate.cfg.xml

Jun 17, 2017 9:43:56 AM org.hibernate.cfg.Configuration getConfigurationInputStream

INFO: HHH000040: Configuration resource: hibernate.cfg.xml

Jun 17, 2017 9:43:56 AM org.hibernate.cfg.Configuration doConfigure

INFO: HHH000041: Configured SessionFactory: null

Jun 17, 2017 9:43:57 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure

WARN: HHH000402: Using Hibernate built-in connection pool (not for production use!)

Jun 17, 2017 9:43:57 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator

INFO: HHH000401: using driver [com.mysql.jdbc.Driver] at URL [jdbc:mysql://localhost/telcoma]

Jun 17, 2017 9:43:57 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator

INFO: HHH000046: Connection properties: {user=root, password=****}

Jun 17, 2017 9:43:57 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator

INFO: HHH000006: Autocommit mode: false

Jun 17, 2017 9:43:57 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure

INFO: HHH000115: Hibernate connection pool size: 20 (min=1)

Jun 17, 2017 9:43:57 AM org.hibernate.dialect.Dialect <init>

INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect

Jun 17, 2017 9:43:57 AM org.hibernate.engine.jdbc.internal.LobCreatorBuilder useContextualLobCreation

INFO: HHH000423: Disabling contextual LOB creation as JDBC driver reported JDBC version [3] less than 4

Jun 17, 2017 9:43:57 AM org.hibernate.engine.transaction.internal.TransactionFactoryInitiator initiateService

INFO: HHH000399: Using default transaction strategy (direct JDBC transactions)

Jun 17, 2017 9:43:57 AM org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory <init>

INFO: HHH000397: Using ASTQueryTranslatorFactory

Jun 17, 2017 9:43:58 AM org.hibernate.tool.hbm2ddl.SchemaUpdate execute

INFO: HHH000228: Running hbm2ddl schema update

Jun 17, 2017 9:43:58 AM org.hibernate.tool.hbm2ddl.SchemaUpdate execute

INFO: HHH000102: Fetching database metadata

Jun 17, 2017 9:43:58 AM org.hibernate.tool.hbm2ddl.SchemaUpdate execute

INFO: HHH000396: Updating schema

Jun 17, 2017 9:43:58 AM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata

INFO: HHH000262: Table not found: address

Jun 17, 2017 9:43:58 AM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata

INFO: HHH000262: Table not found: employee

Jun 17, 2017 9:43:58 AM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata

INFO: HHH000262: Table not found: address

Jun 17, 2017 9:43:58 AM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata

INFO: HHH000262: Table not found: employee

Jun 17, 2017 9:43:58 AM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata

INFO: HHH000262: Table not found: address

Jun 17, 2017 9:43:58 AM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata

INFO: HHH000262: Table not found: employee

Jun 17, 2017 9:43:58 AM org.hibernate.tool.hbm2ddl.SchemaUpdate execute

INFO: HHH000232: Schema update complete

Hibernate: insert into address (city, country) values (?, ?)

Hibernate: insert into employee (address_id, name) values (?, ?)

Hibernate: insert into address (city, country) values (?, ?)

Hibernate: insert into employee (address_id, name) values (?, ?)

Jun 17, 2017 9:43:58 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl stop

INFO: HHH000030: Cleaning up connection pool [jdbc:mysql://localhost/telcoma]

Hibernate Many To Many Mapping Relations using Annotation

Create Java Project:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>HibernateManyToMany</groupId>
<artifactId>HibernateManyToMany</artifactId>
<version>0.0.1-SNAPSHOT</version>
<build>
<sourceDirectory>src</sourceDirectory>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.2.2.Final</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
</dependencies>
</project>

hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8"?>

<!DOCTYPE hibernate-configuration PUBLIC

"-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>

    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/hohisa</property>

    <property name="hibernate.connection.username">root</property>

    <property name="hibernate.connection.password">root</property>

    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

    <property name="hibernate.show_sql">true</property>

    <property name="hbm2ddl.auto">update</property>

    <mapping class="com.hohisa.Student"/>

    <mapping class="com.hohisa.Course"/>

</session-factory>

</hibernate-configuration>

Student.java

package com.hohisa;
import java.util.Set;
import javax.persistence.*;
@Entity
@Table(name = "student")
public class Student {
@Id
@GeneratedValue
@Column(name = "studentId")
private int studentId;
@Column(name = "studentName")
private String studentName;
@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name = "student_course", joinColumns = { @JoinColumn(name = "studentId") }, inverseJoinColumns = { @JoinColumn(name = "courseId") })
private Set<Course> courses;
public int getStudentId() {
return studentId;
}
public void setStudentId(int studentId) {
this.studentId = studentId;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public Set<Course> getCourses() {
return courses;
}
public void setCourses(Set<Course> courses) {
this.courses = courses;
}
}

Course.java

package com.hohisa;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "course")
public class Course {
@Id
@GeneratedValue
@Column(name = "courseId")
private int courseId;
@Column(name = "courseName")
private String courseName;
public int getCourseId() {
return courseId;
}
public void setCourseId(int courseId) {
this.courseId = courseId;
}
public String getCourseName() {
return courseName;
}
public void setCourseName(String courseName) {
this.courseName = courseName;
}
}

Main.java

package com.hohisa;
import java.util.*;
import org.hibernate.*;
import org.hibernate.cfg.Configuration;
public class MainClass {
public static void main(String[] args) {
Configuration configuration = new Configuration();
configuration.configure("hibernate.cfg.xml");
SessionFactory sessionFactory = configuration.buildSessionFactory();
Session session = sessionFactory.openSession();
Transaction transaction =session.beginTransaction();
Course course1 =new Course();
course1.setCourseName("Computers");
Course course2 =new Course();
course2.setCourseName("Computers");
Set<Course> courses=new HashSet<Course>();
courses.add(course1);
courses.add(course2);
Student student1=new Student();
student1.setStudentName("Anil");
student1.setCourses(courses);
Student student2=new Student();
student2.setStudentName("davy");
student2.setCourses(courses);
session.persist(student1);
session.persist(student2);
transaction.commit();
session.close();
sessionFactory.close();
}
}

Console OutPut

Database Output

Hibernate Named Query

Add Employee.java file

package com.akt;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

import javax.persistence.NamedQueries;

import javax.persistence.NamedQuery;

import javax.persistence.Table;

@NamedQueries(

@NamedQuery(

name="findEmployeeByName",

query="from Employee e where e.name=:name"

)

)

@Entity

@Table(name="employee")

public class Employee {

@Id

@GeneratedValue

@Column(name="empId")

private int empId;

@Column(name="name")

private String name;

@Column(name="username")

private String username;

@Column(name="email")

private String email;

@Column(name="salary")

private int salary;

public int getSalary() {

return salary;

}

public void setSalary(int salary) {

this.salary = salary;

}

public int getEmpId() {

return empId;

}

public void setEmpId(int empId) {

this.empId = empId;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getUsername() {

return username;

}

public void setUsername(String username) {

this.username = username;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public String toString(){return empId+" "+name+" "+salary+" "+username;}  

}

MainClass.java

package com.akt;

import java.util.Iterator;

import java.util.List;

import javax.persistence.TypedQuery;

import org.hibernate.Query;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.Transaction;

import org.hibernate.cfg.Configuration;

public class MainClass {

public static void main(String[] args) {

Configuration configuration = new Configuration();

configuration.configure("hibernate.cfg.xml");

SessionFactory sessionFactory = configuration.buildSessionFactory();

Session session = sessionFactory.openSession();

/*Transaction transaction = session.getTransaction();

transaction.begin();

Employee employee =new Employee();

employee.setUsername("abc");

employee.setEmail("abc@gmail.com");

employee.setSalary(5000);

employee.setName("abc");

session.persist(employee);

transaction.commit();*/

@SuppressWarnings("unchecked")

TypedQuery<Employee> query =session.getNamedQuery("findEmployeeByName");

query.setParameter("name", "anil");

List<Employee> employees=query.getResultList();

   Iterator<Employee> itr=employees.iterator();  

    while(itr.hasNext()){  

   Employee e=itr.next();  

   System.out.println(e);  

    }  

session.close();

sessionFactory.close();

}

}

Hibernate One To Many Relation example using Annotation

In Addition to Previous simple example:
hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8"?>

<!DOCTYPE hibernate-configuration PUBLIC

"-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>

    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/masterjee</property>

    <property name="hibernate.connection.username">root</property>

    <property name="hibernate.connection.password">root</property>

    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

    <property name="hibernate.show_sql">true</property>

    <property name="hbm2ddl.auto">update</property>

    <mapping class="com.akt.User"/>

    <mapping class="com.akt.Role"/>

</session-factory>

</hibernate-configuration>

User.java

package com.akt;

import java.util.List;

import javax.persistence.CascadeType;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

import javax.persistence.JoinColumn;

import javax.persistence.OneToMany;

import javax.persistence.Table;

@Entity

@Table(name = "user")

public class User {

@Id

@GeneratedValue

@Column(name = "userId", unique = true, nullable = false)

private int userId;

@Column(name = "name")

private String name;

@Column(name = "email")

private String email;

@Column(name = "password")

private String password;

@Column(name = "age")

private int age;

@Column(name = "salary")

private long salary;

@OneToMany(cascade = CascadeType.ALL)

@JoinColumn(name = "userId")

private List<Role> roles;

public List<Role> getRoles() {

return roles;

}

public void setRoles(List<Role> roles) {

this.roles = roles;

}

public int getUserId() {

return userId;

}

public void setUserId(int userId) {

this.userId = userId;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

public long getSalary() {

return salary;

}

public void setSalary(long salary) {

this.salary = salary;

}

}

Add new class Role.java

package com.akt;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

import javax.persistence.ManyToOne;

import javax.persistence.Table;

@Entity

@Table(name = "role")

public class Role {

@Id

@GeneratedValue

@Column(name = "roleId", unique = true, nullable = false)

private int roleId;

@Column(name = "roleName")

private String roleName;

@ManyToOne

private User user;

public User getUser() {

return user;

}

public void setUser(User user) {

this.user = user;

}

public int getRoleId() {

return roleId;

}

public void setRoleId(int roleId) {

this.roleId = roleId;

}

public String getRoleName() {

return roleName;

}

public void setRoleName(String roleName) {

this.roleName = roleName;

}

}

MainClass.java

package com.akt;

import java.util.ArrayList;

import java.util.List;

import org.hibernate.*;

import org.hibernate.cfg.Configuration;

public class MainClass {

public static void main(String[] args) {

Configuration configuration = new Configuration();

configuration.configure("hibernate.cfg.xml");

SessionFactory sessionFactory = configuration.buildSessionFactory();

Session session = sessionFactory.openSession();

Transaction transaction = session.getTransaction();

transaction.begin();

User user = new User();

user.setName("Anil");

user.setEmail("anil.thakur0909@gmail.com");

user.setPassword("test123");

user.setSalary(1500);

user.setAge(28);

Role role1 = new Role();

role1.setRoleName("Developer");

Role role2 = new Role();

role2.setRoleName("Developer");

List<Role> roles = new ArrayList<Role>();

roles.add(role1);

roles.add(role2);

user.setRoles(roles);

session.persist(user);

transaction.commit();

session.close();

sessionFactory.close();

}

}

Console Output

Mysql Database Output

Hibernate Simple Example Using Annotation

Create Maven Project:Create java project , convert that to maven bases project

Add Dependency:
pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>HibernateDemo</groupId>
<artifactId>HibernateDemo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<build>
<sourceDirectory>src</sourceDirectory>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.2.2.Final</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
</dependencies>
</project>

Add configuration file at class path:
hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8"?>

<!DOCTYPE hibernate-configuration PUBLIC

"-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>

    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/masterjee</property>

    <property name="hibernate.connection.username">root</property>

    <property name="hibernate.connection.password">root</property>

    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

    <property name="hibernate.show_sql">true</property>

    <property name="hbm2ddl.auto">update</property>

    <mapping class="com.akt.User"/>

</session-factory>

</hibernate-configuration>

Add your Entity class
User.java

package com.akt;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

import javax.persistence.Table;

@Entity

@Table(name="user")

public class User {

@Id

@GeneratedValue

@Column(name="userId")

private int userId;

@Column(name="name")

private String name;

@Column(name="email")

private String email;

@Column(name="password")

private String password;

@Column(name="age")

private int age;

@Column(name="salary")

private long salary;

public int getUserId() {

return userId;

}

public void setUserId(int userId) {

this.userId = userId;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

public long getSalary() {

return salary;

}

public void setSalary(long salary) {

this.salary = salary;

}

}

Add MainClass.java

package com.akt;

import org.hibernate.*;

import org.hibernate.cfg.Configuration;

public class MainClass {

public static void main(String[] args) {

Configuration configuration = new Configuration();

configuration.configure("hibernate.cfg.xml");

SessionFactory sessionFactory = configuration.buildSessionFactory();

Session session = sessionFactory.openSession();

Transaction transaction = session.getTransaction();

transaction.begin();

User user = new User();

user.setName("Anil");

user.setEmail("anil.thakur0909@gmail.com");

user.setPassword("test123");

user.setSalary(1500);

user.setAge(28);

session.persist(user);

transaction.commit();

session.close();

sessionFactory.close();

}

}

Console Output:

Mysql Output