Duplicate Table in Sql Server , Oracle and MySql

 SQL Server 




SELECT *
INTO NEW_TABLE_NAME
FROM OLD_TABLE;




Oracle & MySql 

CREATE TABLE NEW_TABLE_NAME AS (SELECT * FROM OLD_TABLE);


Get more from  : http://techprogramme.com/

Read More

Spring + Hibernate + MySql

In this article i'm discussing about how to use Spring and Hibernate with MySql

First create MySql Database using below script

springtutorial Database has two tables dmo_t_customer and dmo_t_item which has 1 to Many relationship.

CREATE DATABASE springtutorial;

USE `springtutorial`;

/*Table structure for table `dmo_t_customer` */

CREATE TABLE `dmo_t_customer` (
  `CUS_ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `CUS_NAME` VARCHAR(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`CUS_ID`)
) ENGINE=INNODB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


/*Table structure for table `dmo_t_item` */

CREATE TABLE `dmo_t_item` (
  `ITM_ID` BIGINT(20) UNSIGNED NOT NULL AUTO_I192NCREMENT,
  `ITM_ITEM` VARCHAR(60) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ITM_COST` DOUBLE DEFAULT '0',
  `ITM_Unit` DOUBLE DEFAULT NULL,
  `ITM_QTY` INT(11) DEFAULT NULL,
  `CUS_ID` BIGINT(20) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`ITM_ID`),
  KEY `cus_fk` (`CUS_ID`),
  CONSTRAINT `cus_fk` FOREIGN KEY (`CUS_ID`) REFERENCES `dmo_t_customer` (`CUS_ID`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


Create a new Spring MVC project in Intellij

Go to File ---> New Project ---> Select Spring MVC and Fill details as follows and click finish

After click on Finish you will get a layout like below

Now lets add required dependencies to the Maven pom.xml file. Add below lines inside of <dependencies> tag in the pom.xml

   <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.9</version>
  </dependency>

  <dependency>
            <groupId>net.sf.json-lib</groupId>
            <artifactId>json-lib</artifactId>
            <version>2.2.3</version>
            <classifier>jdk15</classifier>
 </dependency>

        <!--Hibernate dependencies-->
  <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>3.3.2.ga</version>
        </dependency>
        <dependency>
            <groupId>javax.transaction</groupId>
            <artifactId>jta</artifactId>
            <version>1.1</version>
  </dependency>
        <!--END Hibernate dependencies-->
     
 <!--Jackson is use to serialize or map Java objects to JSON and vice versa-->
   <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-jaxrs</artifactId>
            <version>1.6.3</version>
            <type>jar</type>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-core-asl</artifactId>
            <version>1.6.3</version>
            <type>jar</type>
            <scope>compile</scope>
  </dependency>

After that Go to src --> main --> webapp --> Open mvc-dispatcher-servlet.xml and change it as below.

highlighted lines are the lines which added newly to declare explicit support for annotation-driven MVC

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context              http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">

    <context:component-scan base-package="com.customer.controller"/>

    <mvc:annotation-driven/>

    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix" value="/WEB-INF/pages/"/>
        <property name="suffix" value=".jsp"/>
    </bean>

</beans>

Now lets configure Hibernate Framework to our project. to do that right click on the project name and select Add Framwork Support. 

From that select Hibernate and put a tick on Create default hibernate configuration and main class check box and make sure to put a tick on Set up library later because we already added hibernate dependency to the pom.xml file

Click ok. now you will be able to see the generated hibernate.cfg.xml file and Main.java file in "src/main/java/" location. Delete Main.java file and create a new folder called resources in "src/main/" and move hibernate.cfg.xml file to resources folder.

Now we need to create domain classes for Database Tables. Create a new package name com.customer.domains in the location "src/main/java/" and create below classes in side of that

DmoTCustomerEntity

package com.customer.domains;

import org.codehaus.jackson.annotate.JsonManagedReference;

import javax.persistence.*;
import java.util.List;

@Entity
@Table(name = "dmo_t_customer", schema = "", catalog = "springtutorial")
public class DmoTCustomerEntity {
    private long cusId;
    private String cusName;
    private List<DmoTItemEntity> dmoTItemEntityList;

    @Id
    @Column(name = "CUS_ID")
    @GeneratedValue(strategy= GenerationType.AUTO)
    public long getCusId() {
        return cusId;
    }

    public void setCusId(long cusId) {
        this.cusId = cusId;
    }

    @Basic
    @Column(name = "CUS_NAME")
    public String getCusName() {
        return cusName;
    }

    public void setCusName(String cusName) {
        this.cusName = cusName;
    }

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "dmoTCustomerEntity")
    @JsonManagedReference
    public List<DmoTItemEntity> getDmoTItemEntityList() {
        return dmoTItemEntityList;
    }

    public void setDmoTItemEntityList(List<DmoTItemEntity> dmoTItemEntityList) {
        this.dmoTItemEntityList = dmoTItemEntityList;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        DmoTCustomerEntity that = (DmoTCustomerEntity) o;

        if (cusId != that.cusId) return false;
        if (cusName != null ? !cusName.equals(that.cusName) : that.cusName != null) return false;

        return true;
    }

    @Override
    public int hashCode() {
        int result = (int) (cusId ^ (cusId >>> 32));
        result = 31 * result + (cusName != null ? cusName.hashCode() : 0);
        return result;
    }
}

DmoTItemEntity


package com.customer.domains;

import org.codehaus.jackson.annotate.JsonBackReference;

import javax.persistence.*;

@Entity
@Table(name = "dmo_t_item", schema = "", catalog = "springtutorial")
public class DmoTItemEntity {
    private long itmId;
    private String itmItem;
    private Double itmCost;
    private Double itmUnit;
    private Integer itmQty;
    private DmoTCustomerEntity dmoTCustomerEntity;

    @Id
    @Column(name = "ITM_ID")
    @GeneratedValue(strategy= GenerationType.AUTO)
    public long getItmId() {
        return itmId;
    }

    public void setItmId(long itmId) {
        this.itmId = itmId;
    }

    @Basic
    @Column(name = "ITM_ITEM")
    public String getItmItem() {
        return itmItem;
    }

    public void setItmItem(String itmItem) {
        this.itmItem = itmItem;
    }

    @Basic
    @Column(name = "ITM_COST")
    public Double getItmCost() {
        return itmCost;
    }

    public void setItmCost(Double itmCost) {
        this.itmCost = itmCost;
    }

    @Basic
    @Column(name = "ITM_Unit")
    public Double getItmUnit() {
        return itmUnit;
    }

    public void setItmUnit(Double itmUnit) {
        this.itmUnit = itmUnit;
    }

    @Basic
    @Column(name = "ITM_QTY")
    public Integer getItmQty() {
        return itmQty;
    }

    public void setItmQty(Integer itmQty) {
        this.itmQty = itmQty;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "CUS_ID", nullable = false)
    @JsonBackReference
    public DmoTCustomerEntity getDmoTCustomerEntity() {
        return dmoTCustomerEntity;
    }

    public void setDmoTCustomerEntity(DmoTCustomerEntity dmoTCustomerEntity) {
        this.dmoTCustomerEntity = dmoTCustomerEntity;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        DmoTItemEntity that = (DmoTItemEntity) o;

        if (itmId != that.itmId) return false;
        if (itmCost != null ? !itmCost.equals(that.itmCost) : that.itmCost != null) return false;
        if (itmItem != null ? !itmItem.equals(that.itmItem) : that.itmItem != null) return false;
        if (itmQty != null ? !itmQty.equals(that.itmQty) : that.itmQty != null) return false;
        if (itmUnit != null ? !itmUnit.equals(that.itmUnit) : that.itmUnit != null) return false;

        return true;
    }

    @Override
    public int hashCode() {
        int result = (int) (itmId ^ (itmId >>> 32));
        result = 31 * result + (itmItem != null ? itmItem.hashCode() : 0);
        result = 31 * result + (itmCost != null ? itmCost.hashCode() : 0);
        result = 31 * result + (itmUnit != null ? itmUnit.hashCode() : 0);
        result = 31 * result + (itmQty != null ? itmQty.hashCode() : 0);
        return result;
    }
}

Now open hibernate.cfg.xml file and change it as below. change hibernate.connection.url,

hibernate.connection.username, hibernate.connection.password based on your database information. 

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD//EN"
        "http://hibernate.sourceforge.net/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 students is the database name -->
        <property name="hibernate.connection.url">
            jdbc:mysql://192.168.8.86:3306/springtutorial
        </property>
        <property name="hibernate.connection.username">
            allroot
        </property>
        <property name="hibernate.connection.password">
            123
        </property>
        <property name="show_sql">true</property>

        <mapping class="com.customer.domains.DmoTCustomerEntity"/>
        <mapping class="com.customer.domains.DmoTItemEntity"/>

  </session-factory>
</hibernate-configuration>

Create another two class in the com.customer.controller package as HibernateUtil and JsonResponse

HibernateUtil.java

package com.customer.controller;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;

/**
 * Created by janithag on 1/16/17.
 */
public class HibernateUtil {

    private static final SessionFactory sessionFactory = buildSessionFactory();

    private static SessionFactory buildSessionFactory() {
        try {
            // Create the SessionFactory from hibernate.cfg.xml
            return new AnnotationConfiguration().configure().buildSessionFactory();
        }
        catch (Throwable ex) {
            // Make sure you log the exception, as it might be swallowed
            System.err.println("Initial SessionFactory creation failed." + ex);
            throw new ExceptionInInitializerError(ex);
        }
    }

    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    public static void shutdown() {
        // Close caches and connection pools
        getSessionFactory().close();
    }


}

JsonResponse.java

package com.customer.controller;

import java.util.List;


public class JsonResponse  {

    private List<?> dataCollection;
    private String serverMessage;

    public String getServerMessage() {
        return serverMessage;
    }

    public void setServerMessage(String serverMessage) {
        this.serverMessage = serverMessage;
    }

    public List<?> getDataCollection() {
        return dataCollection;
    }

    public void setDataCollection(List<?> dataCollection) {
        this.dataCollection = dataCollection;
    }

}

Now delete both HelloController.java(src/main/java/com.customer.controller/HelloController.java) and hello.jsp(src/main/webapp/WEB-INF/pages/hello.jsp) files from the project.

Right click on the pages folder in the webapp and go to new and click on Create JSP and name it as customerPage.

Change customerPage.jsp as below

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
    <script>

        function saveDetails()
        {
            $.post('saveDetails.do?cusName='+$("#cusName").val()+'&itemName='+$("#itemName").val()+'&itemCost='+$("#itemCost").val()+'&itemUnit='+$("#itemUnit").val()+'&itemQty='+$("#itemQty").val(), function(response) {

                alert(response.serverMessage);
            });
        }

        function showDetails()
        {
            $.getJSON("showDetails",function(data) {

                var detailsList = "";
                var itemList = "";
                var dataCollection=[];
                dataCollection = data.dataCollection
                for(var i=0;i<dataCollection.length;i++)
                {
                    var details = dataCollection[i];
                    var cusID = details['cusId'];
                    var cusName = details['cusName'];

                    var itemDetails = details['dmoTItemEntityList'];
                    for(var x = 0; x<itemDetails.length; x++)
                    {
                        var items = itemDetails[x];
                        var itmCost = items['itmCost'];
                        var itmItem = items['itmItem'];
                        var itmQty = items['itmQty'];
                        var itmUnit = items['itmUnit'];

                        itemList += "itmCost: "+itmCost+" || itmItem: "+itmItem+" || itmQty: "+itmQty+" || itmUnit: "+itmUnit +"\n";
                    }

                    detailsList += "cusID: "+cusID+" || cusName: "+cusName+"\n Items -: \n"+itemList +"\n";
                    itemList = "";
                }

                alert(detailsList);

            });
        }

    </script>

    <title>Customer Page</title>
</head>
<body>

<label for="cusName">Customer Name :</label><br>
<input type="text" name="cusName" id="cusName"><br><br>
<label for="itemName">Item Name :</label><br>
<input type="text" name="itemName" id="itemName"><br><br>
<label for="itemCost">Item Cost :</label><br>
<input type="text" name="itemCost" id="itemCost"><br><br>
<label for="itemUnit">Item Unit :</label><br>
<input type="text" name="itemUnit" id="itemUnit"><br><br>
<label for="itemQty">Item Quantity :</label><br>
<input type="text" name="itemQty" id="itemQty"><br><br>

<button type="button" onclick="saveDetails()">Save</button><br><br>
<button type="button" onclick="showDetails()">Show Details</button>

</body>
</html>

Now right click on the package com.customer.controller and create a new java class as CustomerController. change it as below

CustomerController.java

package com.customer.controller;

import com.customer.domains.DmoTCustomerEntity;

import com.customer.domains.DmoTItemEntity;

import org.hibernate.Criteria;

import org.hibernate.Session;

import org.springframework.stereotype.Controller;

import org.springframework.ui.ModelMap;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;

import java.util.List;

@Controller

public class CustomerController {

    @RequestMapping(value = "/customerPage",method = RequestMethod.GET)

    public String printWelcome(ModelMap model) {

        return "customerPage";

    }

    @RequestMapping(value = "/saveDetails", method = RequestMethod.POST)

    public @ResponseBody

    JsonResponse saveDetails(@RequestParam("cusName") String cusName,@RequestParam("itemName") String itemName,@RequestParam("itemCost") String itemCost,@RequestParam("itemUnit") String itemUnit,@RequestParam("itemQty") String itemQty,HttpServletRequest request) {

        JsonResponse jsonResponse = new JsonResponse();

        Session session = HibernateUtil.getSessionFactory().openSession();

        session.beginTransaction();

        DmoTCustomerEntity dmoTCustomerEntity = new DmoTCustomerEntity();

        dmoTCustomerEntity.setCusName(cusName);

        session.save(dmoTCustomerEntity);

        DmoTItemEntity dmoTItemEntity = new DmoTItemEntity();

        dmoTItemEntity.setItmItem(itemName);

        dmoTItemEntity.setItmCost(Double.parseDouble(itemCost));

        dmoTItemEntity.setItmQty(Integer.parseInt(itemQty));

        dmoTItemEntity.setItmUnit(Double.parseDouble(itemUnit));

        dmoTItemEntity.setDmoTCustomerEntity(dmoTCustomerEntity);

        session.save(dmoTItemEntity);

        DmoTItemEntity dmoTItemEntitySecond = new DmoTItemEntity();

        dmoTItemEntitySecond.setItmItem("Item Second");

        dmoTItemEntitySecond.setItmCost(Double.parseDouble(itemCost));

        dmoTItemEntitySecond.setItmQty(Integer.parseInt(itemQty));

        dmoTItemEntitySecond.setItmUnit(Double.parseDouble(itemUnit));

        dmoTItemEntitySecond.setDmoTCustomerEntity(dmoTCustomerEntity);

        session.save(dmoTItemEntitySecond);

        session.getTransaction().commit();

        jsonResponse.setServerMessage("Successfully Saved");

        return jsonResponse;

    }

    @RequestMapping(value = "/showDetails", method = RequestMethod.GET)

    public @ResponseBody

    JsonResponse showDetails(HttpServletRequest request) {

        JsonResponse jsonResponse = new JsonResponse();

        Session session = HibernateUtil.getSessionFactory().openSession();

        session.beginTransaction();

        Criteria criteria = session.createCriteria(DmoTCustomerEntity.class);

        List<DmoTCustomerEntity> dmoTCustomerEntity = criteria.list();

        jsonResponse.setDataCollection(dmoTCustomerEntity);

        return jsonResponse;

    }

}

Now project hierarchy should be like below 

To run this project first we need to configure Maven and JBoss in the intellij idea. To do that follow below instructions.

Click on the marked drop down arrow and select Edit Configurations.

 In the left side of panel click on the + sign and add Maven. Change the maven properties as below
 

Click apply.

Again click on the + sign and select JBoss Server and click on Local. For Application Sever give your JBoss home location and change other options as below

Click on the Deployment tab of JBoss module and click on + sign and add war file as a artifact

Now first build system from Maven and Run it from JBoss. You will get final result as below

Download Source Code from here

Read More