MySQL Error: : ‘Access denied for user ‘root’@’localhost’

sudo mysql
-- for MySQL
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

-- for MariaDB
ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('root');

With a single query we are changing the auth_plugin to mysql_native_password and setting the root password to root and there isn’t any need to restart mysqld or start it with special privileges.

References
https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost

Configure Hibernate With MySQL in Gradle and do CRUD – JPA Persistence Way

Employee.java

import javax.persistence.*;

@Entity
@Table(name = "EMPLOYEE")
public class Employee {
    @Id
    @GeneratedValue
    @Column(name = "id")
    private int id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "salary")
    private int salary;

    public Employee() {
    }

    public Employee(String fname, String lname, int salary) {
        this.firstName = fname;
        this.lastName = lname;
        this.salary = salary;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String first_name) {
        this.firstName = first_name;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String last_name) {
        this.lastName = last_name;
    }

    public int getSalary() {
        return salary;
    }

    public void setSalary(int salary) {
        this.salary = salary;
    }
}

resources/META-INF/persistence.xml

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence

http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"

             version="2.1">

    <persistence-unit name="ir.mhdr.jpaDemo" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <class>Employee</class>

        <properties>
            <!-- Configuring JDBC properties -->
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost/testdb?useUnicode=yes&amp;characterEncoding=UTF-8&amp;useJDBCCompliantTimezoneShift=true&amp;useLegacyDatetimeCode=false&amp;serverTimezone=UTC" />
            <property name="javax.persistence.jdbc.user" value="root" />
            <property name="javax.persistence.jdbc.password" value="sampad622" />
            <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />

            <!-- Hibernate properties -->
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.format_sql" value="true" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL57Dialect" />
            <property name="hibernate.hbm2ddl.auto" value="create" />

            <!-- Configuring Connection Pool -->
            <property name="hibernate.c3p0.min_size" value="5" />
            <property name="hibernate.c3p0.max_size" value="20" />
            <property name="hibernate.c3p0.timeout" value="500" />
            <property name="hibernate.c3p0.max_statements" value="50" />
            <property name="hibernate.c3p0.idle_test_period" value="2000" />
        </properties>
    </persistence-unit>
</persistence>

EmployeeBL.java

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.query.criteria.internal.CriteriaUpdateImpl;

import javax.persistence.*;
import javax.persistence.criteria.CriteriaUpdate;
import javax.transaction.Transaction;
import java.util.Iterator;
import java.util.List;

public class EmployeeBL {

    private EntityManagerFactory factory;

    public EmployeeBL() {
        factory = Persistence.createEntityManagerFactory("ir.mhdr.jpaDemo");
    }

    public void close() {
        factory.close();
    }

    /* Method to CREATE an employee in the database */
    public void addEmployee(String fname, String lname, int salary) {
        EntityManager entityManager = factory.createEntityManager();
        EntityTransaction tx = null;

        try {
            tx = entityManager.getTransaction();
            tx.begin();

            Employee employee = new Employee(fname, lname, salary);
            entityManager.persist(employee);

            tx.commit();
        } catch (Exception ex) {

            if (tx != null) tx.rollback();
            ex.printStackTrace();

        } finally {

            entityManager.close();
        }
    }


    /* Method to  READ all the employees */
    public void listEmployees() {
        EntityManager entityManager = factory.createEntityManager();
        EntityTransaction tx = null;

        try {
            tx = entityManager.getTransaction();
            tx.begin();

            List employees = entityManager.createQuery("from Employee").getResultList();
            for (Iterator iterator =
                 employees.iterator(); iterator.hasNext(); ) {
                Employee employee = (Employee) iterator.next();
                System.out.print("First Name: " + employee.getFirstName());
                System.out.print("  Last Name: " + employee.getLastName());
                System.out.println("  Salary: " + employee.getSalary());
            }

            tx.commit();
        } catch (Exception ex) {
            if (tx != null) tx.rollback();
            ex.printStackTrace();
        } finally {

            entityManager.close();

        }
    }


    /* Method to UPDATE salary for an employee */
    public void updateEmployee(Integer EmployeeID, int salary) {

        EntityManager entityManager = factory.createEntityManager();
        EntityTransaction tx = null;

        try {
            tx = entityManager.getTransaction();
            tx.begin();

            TypedQuery<Employee> query = entityManager.createQuery("select emp from Employee emp where id=:id", Employee.class);
            query.setParameter("id", EmployeeID);
            Employee employee = query.getSingleResult();

            employee.setSalary(salary);

            entityManager.persist(employee);

            tx.commit();
        } catch (Exception ex) {
            if (tx != null) tx.rollback();
            ex.printStackTrace();
        } finally {
            entityManager.close();
        }
    }


    /* Method to DELETE an employee from the records */
    public void deleteEmployee(Integer EmployeeID) {

        EntityManager entityManager = factory.createEntityManager();
        EntityTransaction tx = null;

        try {

            tx = entityManager.getTransaction();
            tx.begin();

            Employee employee = entityManager.find(Employee.class, EmployeeID);
            entityManager.remove(employee);

            tx.commit();

        } catch (Exception ex) {
            if (tx != null) tx.rollback();
            ex.printStackTrace();
        } finally {
            entityManager.close();
        }
    }
}

Main.java

public class Main {


    public static void main(String[] args)
    {
        EmployeeBL employeeBL=new EmployeeBL();
        employeeBL.addEmployee("Mahmood","Ramzani",1000);
        employeeBL.close();
    }
}

References
https://github.com/mhdr/HibernateSamples/tree/master/004_Persistent
http://docs.jboss.org/hibernate/orm/5.2/quickstart/html_single/#tutorial_jpa
https://dzone.com/articles/a-curious-java-language-feature-and-how-it-produce
https://www.sitepoint.com/5-reasons-to-use-jpa-hibernate/

Batch Insert in Hibernate

EmployeeBL.java

public class EmployeeBL {
    private SessionFactory factory;

    public EmployeeBL() {
        Configuration configuration = new Configuration();
        this.factory = configuration.configure().buildSessionFactory();
    }

    public void insertRange(List<Employee> employeeList) {
        Session session = factory.openSession();
        session.setProperty("hibernate.jdbc.batch_size", 30);
        session.setProperty("hibernate.cache.use_second_level_cache", false);

        Transaction tx = session.beginTransaction();

        int i = 0;

        for (Employee employee : employeeList) {

            session.save(employee);
            i++;

            if (i % 30 == 0) { // same as the JDBC batch size
                //flush a batch of inserts and release memory:
                session.flush();
                session.clear();
            }
        }

        tx.commit();
        session.close();
    }
}

References
https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/batch.html
https://github.com/mhdr/HibernateSamples/tree/master/003_BatchInsert

Configure Hibernate With MySQL in Gradle and do CRUD

build.gradle

group 'ir.mhdr'
version '1.0-SNAPSHOT'

apply plugin: 'java'

sourceCompatibility = 1.8
mainClassName="Main"

repositories {
    mavenCentral()
}

dependencies {

    // https://mvnrepository.com/artifact/org.hibernate/hibernate-core
    compile group: 'org.hibernate', name: 'hibernate-core', version: '5.2.10.Final'

    // https://mvnrepository.com/artifact/org.hibernate/hibernate-gradle-plugin
    compile group: 'org.hibernate', name: 'hibernate-gradle-plugin', version: '5.2.10.Final'

    // https://mvnrepository.com/artifact/mysql/mysql-connector-java
    compile group: 'mysql', name: 'mysql-connector-java', version: '6.0.6'

    // https://mvnrepository.com/artifact/org.slf4j/slf4j-api
    compile group: 'org.slf4j', name: 'slf4j-api', version: '1.7.25'

    // https://mvnrepository.com/artifact/org.slf4j/slf4j-simple
    testCompile group: 'org.slf4j', name: 'slf4j-simple', version: '1.7.25'

    testCompile group: 'junit', name: 'junit', version: '4.12'
}

Employee.java

import javax.persistence.*;

@Entity
@Table(name = "EMPLOYEE")
public class Employee {
    @Id
    @GeneratedValue
    @Column(name = "id")
    private int id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "salary")
    private int salary;

    public Employee() {
    }

    public Employee(String fname, String lname, int salary) {
        this.firstName = fname;
        this.lastName = lname;
        this.salary = salary;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String first_name) {
        this.firstName = first_name;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String last_name) {
        this.lastName = last_name;
    }

    public int getSalary() {
        return salary;
    }

    public void setSalary(int salary) {
        this.salary = salary;
    }
}

resources/hibernate.cfg.xml

<?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.hbm2ddl.auto">create</property>
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.format_sql">true</property>

        <property name="hibernate.dialect">
            org.hibernate.dialect.MySQL57Dialect
        </property>
        <property name="hibernate.connection.driver_class">
            com.mysql.cj.jdbc.Driver
        </property>

        <!-- Assume test is the database name -->
        <property name="hibernate.connection.url">
            jdbc:mysql://localhost/testdb?useUnicode=yes&amp;characterEncoding=UTF-8&amp;useJDBCCompliantTimezoneShift=true&amp;useLegacyDatetimeCode=false&amp;serverTimezone=UTC
        </property>
        <property name="hibernate.connection.username">
            root
        </property>
        <property name="hibernate.connection.password">
            sampad622
        </property>

        <!-- List of XML mapping files -->
        <mapping class="Employee"/>

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

ManageEmployee.java

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

import java.util.Iterator;
import java.util.List;

public class ManageEmployee {

    private SessionFactory factory;

    public ManageEmployee() {
        factory = new Configuration().configure().buildSessionFactory();
    }

    /* Method to CREATE an employee in the database */
    public Integer addEmployee(String fname, String lname, int salary) {

        Session session = factory.openSession();
        Transaction tx = null;
        Integer employeeID = null;

        try {
            tx = session.beginTransaction();
            Employee employee = new Employee(fname, lname, salary);
            employeeID = (Integer) session.save(employee);
            tx.commit();
        } catch (HibernateException e) {

            if (tx != null) tx.rollback();
            e.printStackTrace();

        } finally {

            session.close();

        }

        return employeeID;
    }


    /* Method to  READ all the employees */
    public void listEmployees() {
        Session session = factory.openSession();
        Transaction tx = null;

        try {
            tx = session.beginTransaction();
            List employees = session.createQuery("FROM Employee").list();
            for (Iterator iterator =
                 employees.iterator(); iterator.hasNext(); ) {
                Employee employee = (Employee) iterator.next();
                System.out.print("First Name: " + employee.getFirstName());
                System.out.print("  Last Name: " + employee.getLastName());
                System.out.println("  Salary: " + employee.getSalary());
            }
            tx.commit();
        } catch (HibernateException e) {

            if (tx != null) tx.rollback();
            e.printStackTrace();
        } finally {

            session.close();

        }
    }


    /* Method to UPDATE salary for an employee */
    public void updateEmployee(Integer EmployeeID, int salary) {
        Session session = factory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            Employee employee =
                    (Employee) session.get(Employee.class, EmployeeID);
            employee.setSalary(salary);
            session.update(employee);
            tx.commit();
        } catch (HibernateException e) {
            if (tx != null) tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }


    /* Method to DELETE an employee from the records */
    public void deleteEmployee(Integer EmployeeID) {
        Session session = factory.openSession();
        Transaction tx = null;

        try {

            tx = session.beginTransaction();
            Employee employee =
                    (Employee) session.get(Employee.class, EmployeeID);
            session.delete(employee);
            tx.commit();

        } catch (HibernateException e) {
            if (tx != null) tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }
}

Main.java

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class Main {

    public static void main(String[] args) {

        ManageEmployee ME = new ManageEmployee();

      /* Add few employee records in database */
        Integer empID1 = ME.addEmployee("Zara", "Ali", 1000);
        Integer empID2 = ME.addEmployee("Daisy", "Das", 5000);
        Integer empID3 = ME.addEmployee("John", "Paul", 10000);

      /* List down all the employees */
        ME.listEmployees();

      /* Update employee's records */
        ME.updateEmployee(empID1, 5000);

      /* Delete an employee from the database */
        ME.deleteEmployee(empID2);

      /* List down new list of the employees */
        ME.listEmployees();
    }
}

References
https://github.com/mhdr/HibernateSamples/tree/master/002_CRUD

Create an Index using JPA

@Entity
@Table(name = "region",
       indexes = {@Index(name = "my_index_name",  columnList="iso_code", unique = true),
                  @Index(name = "my_index_name2", columnList="name",     unique = false)})
public class Region{

    @Column(name = "iso_code", nullable = false)
    private String isoCode;

    @Column(name = "name", nullable = false)
    private String name;

} 

or

@Entity
@Table(name    = "company__activity", 
       indexes = {@Index(name = "i_company_activity", columnList = "activity_id,company_id")})
public class CompanyActivity{

References
http://stackoverflow.com/questions/3405229/specifying-an-index-non-unique-key-using-jpa

Store the result of query into a variable in MySQL

INSERT INTO users (first_name, last_name, password, user_name)
VALUES (N'ادمین',
        N'ادمین',
        N'c7ad44cbad762a5da0a452f9e854fdc1e0e7a52a38015f23f3eab1d80b931dd472634dfac71cd34ebc35d16ab7fb8a90c81f975113d6c7538dc69dd8de9077ec',
        N'admin');

INSERT INTO permissions (id, name) VALUES (1, N'ادمین');

SET @insertedId := (SELECT id
                    FROM users
                    WHERE user_name = N'admin');

INSERT INTO user_permissions (permission_id, user_id)
VALUES (1, @insertedId);

How to Configure Spring Session with JDBC for MySQL

build.gradle

buildscript {
	ext {
		springBootVersion = '1.5.2.RELEASE'
	}
	repositories {
		mavenCentral()
	}
	dependencies {
		classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
	}
}

apply plugin: 'java'
apply plugin: 'application'
apply plugin: 'org.springframework.boot'

jar {
	baseName = 'logbook-server'
	version = '0.0.1-SNAPSHOT'
}

sourceCompatibility = 1.8

repositories {
	mavenCentral()
}


dependencies {
	compile('org.springframework.boot:spring-boot-starter-data-jpa')
	compile('org.springframework.session:spring-session')
	compile('org.springframework.boot:spring-boot-starter-thymeleaf')
	compile('org.springframework.boot:spring-boot-starter-web')
	compile('org.springframework.boot:spring-boot-starter-web-services')
	compile('org.springframework.boot:spring-boot-starter-websocket')
	runtime('org.springframework.boot:spring-boot-devtools')
	runtime('mysql:mysql-connector-java')
	compileOnly('org.springframework.boot:spring-boot-configuration-processor')
	compileOnly('org.projectlombok:lombok')
	testCompile('org.springframework.boot:spring-boot-starter-test')

	// https://mvnrepository.com/artifact/joda-time/joda-time
	compile group: 'joda-time', name: 'joda-time', version: '2.9.7'

	// https://mvnrepository.com/artifact/net.time4j/time4j-parent
	compile group: 'net.time4j', name: 'time4j-parent', version: '4.25'

	// https://mvnrepository.com/artifact/org.apache.commons/commons-lang3
	compile group: 'org.apache.commons', name: 'commons-lang3', version: '3.5'

	// https://mvnrepository.com/artifact/org.bouncycastle/bcprov-jdk15on
	compile group: 'org.bouncycastle', name: 'bcprov-jdk15on', version: '1.56'

	// https://mvnrepository.com/artifact/com.google.code.gson/gson
	compile group: 'com.google.code.gson', name: 'gson', version: '2.8.0'

	// https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-tomcat
	compile group: 'org.springframework.boot', name: 'spring-boot-starter-tomcat'

	// https://mvnrepository.com/artifact/commons-io/commons-io
	compile group: 'commons-io', name: 'commons-io', version: '2.5'
}

application.properties

server.port=13602
spring.session.store-type=jdbc
server.compression.enabled=true
server.use-forward-headers=true
server.compression.mime-types=application/json,application/xml,text/html,text/xml,text/plain,text/css,application/javascript
logging.file=logbook-server.log
spring.datasource.url=jdbc:mysql://localhost/logbook
spring.datasource.username=root
spring.datasource.password=12345
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

# Number of ms to wait before throwing an exception if no connection is available.
spring.datasource.tomcat.max-wait=10000

# Maximum number of active connections that can be allocated from this pool at the same time.
spring.datasource.tomcat.max-active=50

# Validate the connection before borrowing it from the pool.
spring.datasource.tomcat.test-on-borrow=true

spring.jpa.hibernate.ddl-auto=create

HttpSessionConfig.java

@Configuration
@EnableJdbcHttpSession
public class HttpSessionConfig {

}

then execute this script on MySQL

CREATE TABLE SPRING_SESSION (
        SESSION_ID CHAR(36),
        CREATION_TIME BIGINT NOT NULL,
        LAST_ACCESS_TIME BIGINT NOT NULL,
        MAX_INACTIVE_INTERVAL INT NOT NULL,
        PRINCIPAL_NAME VARCHAR(100),
        CONSTRAINT SPRING_SESSION_PK PRIMARY KEY (SESSION_ID)
) ENGINE=InnoDB;

CREATE INDEX SPRING_SESSION_IX1 ON SPRING_SESSION (LAST_ACCESS_TIME);

CREATE TABLE SPRING_SESSION_ATTRIBUTES (
        SESSION_ID CHAR(36),
        ATTRIBUTE_NAME VARCHAR(200),
        ATTRIBUTE_BYTES BLOB,
        CONSTRAINT SPRING_SESSION_ATTRIBUTES_PK PRIMARY KEY (SESSION_ID, ATTRIBUTE_NAME),
        CONSTRAINT SPRING_SESSION_ATTRIBUTES_FK FOREIGN KEY (SESSION_ID) REFERENCES SPRING_SESSION(SESSION_ID) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX SPRING_SESSION_ATTRIBUTES_IX1 ON SPRING_SESSION_ATTRIBUTES (SESSION_ID);

References
http://docs.spring.io/spring-session/docs/current/reference/html5/#api-jdbcoperationssessionrepository-storage
http://stackoverflow.com/questions/37398905/spring-session-with-jdbc-configuration-table-test-spring-session-doesnt-exis