Archive for the ‘ mysql ’ Category

liquibase migration on spring boot startup (SpringLiquibase)

Prerequisites:

  1. mysql database available to be used. I’m using jdbc:mysql://localhost:3306/liquibase_test
  2. I’m using Netbeans. Other IDE also can be used.

  

Steps are…

  1. Create project from “Spring initialz” with 3 dependencies:

    2. a. liquibase

    b. mysql

    c. jpa

  1. Open generated project in netbeans.
  1. Change application.properties to application.yml and paste the content below in it. 
    spring:
    datasource:
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/liquibase_test 
        username: greenhorn
        password: greenhorn
        
liquibase:
    check-change-log-location: true
    enabled: true
  1. Create and insert the below in “db.changelog-test.xml” file and place the file in src/main/resources/db/mysql/changelog (create if the directory doesn’t exist): 
    <?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
    
    <changeSet author="generated" id="tbl-1">
        <createTable tableName="test_tbl">
            <column name="tblId" type="VARBINARY(36)">
                <constraints nullable="false"/>
            </column>
            <column name="createdDate" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="updatedDate" type="datetime"/>
            <column name="fk_createdBy" type="VARBINARY(36)">
                <constraints nullable="false"/>
            </column>
            <column name="fk_updatedBy" type="VARBINARY(36)"/>
            <column name="active" type="BIT(1)"/>
            <column name="deleted" type="BIT(1)"/>
            <column name="content" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="version" type="INT"/>
        </createTable>
    </changeSet>
    
    <changeSet author="generated" id="tbl-2">
        <addPrimaryKey columnNames="tblId" constraintName="PRIMARY" tableName="test_tbl"/>
    </changeSet>
</databaseChangeLog>
  1. Create LiquibaseConfig.java file and paste the content below in it: 
    @Configuration
public class LiquibaseConfig {

    @Autowired
    private DataSource dataSource;

    @Autowired
    private ResourceLoader resourceLoader;

    @Bean
    public SpringLiquibase liquibase() throws Exception {
        //      Locate change log file
        String changelogFile = "classpath:db/mysql/changelog/db.changelog-test.xml";
        Resource resource = resourceLoader.getResource(changelogFile);

        Assert.state(resource.exists(), "Unable to find file: " + resource.getFilename());
        // Configure Liquibase
        SpringLiquibase liquibase = new SpringLiquibase();
        liquibase.setChangeLog(changelogFile);
        liquibase.setDataSource(dataSource);
        liquibase.setDropFirst(true);
        liquibase.setShouldRun(true);

        // Verbose logging
        Map<String, String> params = new HashMap<>();
        params.put("verbose", "true");
        return liquibase;
    }
}
  1. Execute the project using mvn spring-boot:run
  1. Examine the database in jdbc:mysql://localhost:3306/liquibase_test
  1. End.


NOTE
The sample spring boot maven project used for this exercise can be downloaded from:

liquibase-spring

References:

  1. programcreek SpringLiquibase



2017
07/02
CATEGORY
Information Technology
Java
maven
mysql
Spring Boot
TAGS
Leave a comment

sql file execution on spring boot startup

Prerequisites:

  1. mysql database available to be used. I’m using jdbc:mysql://localhost:3306/mybatis_test
  2. I’m using Netbeans. Other IDE also can be used.

  

Steps are…

  1. Create project from “Spring initialz” with 3 dependencies:

    2. a. mybatis

    b. mysql

    c. jpa

  1. Open generated project in netbeans.
  1. Add spring data source details in the “application.properties” file: 
    spring.datasource.url = jdbc:mysql://localhost:3306/mybatis_test
spring.datasource.username = greenhorn
spring.datasource.password = greenhorn
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
  1. Create and insert the below in “schema.sql” file and place the file in the src/main/resources directory: 
    DROP TABLE IF EXISTS `test_tbl`;

CREATE TABLE `test_tbl` (
	  `testId` varbinary(36) NOT NULL,
	  `fk_createdBy` varbinary(36) NOT NULL,
	  `fk_updatedBy` varbinary(36) DEFAULT NULL,
	  `createdDate` datetime NOT NULL,
	  `updatedDate` datetime DEFAULT NULL,
	  `active` bit(1) DEFAULT NULL,
	  `deleted` bit(1) DEFAULT NULL,
	  `customReferenceNo` varchar(100) DEFAULT NULL,
	  PRIMARY KEY (`testId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  1. Add the below codes to the Main java class: 
        public class MybatisSpringApplication implements CommandLineRunner {
    ...

    @Autowired
    private DataSource datasource;

    @Override
    public void run(String... args) throws Exception {
        String script = "C:\\Users\\greenhorn\\Desktop\\mybatis-spring\\src\\main\\resources\\schema.sql";

        // Approch 1: using native way to create instance of Connection
        ScriptRunner scriptRunner = new ScriptRunner(
                DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis_test", "greenhorn", "greenhorn"));

        // Approch 2: using spring boot injected DataSource to get the connection
        //ScriptRunner scriptRunner = new ScriptRunner(datasource.getConnection());
        scriptRunner.runScript(new BufferedReader(new FileReader(script)));
    }
    ...
  1. Execute the project using mvn spring-boot:run
  1. Examine the database in jdbc:mysql://localhost:3306/mybatis_test
  1. End.


NOTE
The sample spring boot maven project used for this exercise can be downloaded from:

mybatis-spring

2017
07/01
CATEGORY
Information Technology
Java
maven
mysql
Spring Boot
TAGS
Leave a comment

Upper case table names in Mysql which comes with xampp



Below are the general steps taken in the video above.(Not the exact steps)

Check the default variable value in Mysql first.

  1. Open xampp control panel. Start mysql.
  1. Open Shell from xampp control panel.
  1. Login to mysql database.
  1. Execute the below command to get the default value from mysql server.
    2. show variables where variable_name = "lower_case_table_names";
    The above will return a result similar to below:
    +------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.37 sec)
  1. In order to make the table names upper case, we need to change the value of variable lower_case_table_names from 1 to 2.

Change variable value.

  1. Open my.ini from xampp control panel.
  1. Find for [mysqld] text in the file.
  1. Add the text below on next line after text [mysqld] and save the file.
    2. lower_case_table_names=2
    After applying the changes above, the portion of configuration will look similar like below:
    # The MySQL server
[mysqld]
lower_case_table_names=2
  1. Restart mysql from xampp control panel.

Try creating table with Upper case name.

Below are the sequence of sql used to create database and table:

    create database case1;
use case1;
create table Mapple(idOne varchar(1));
show tables;
show columns from Mapple;
select * from mapple;
drop database case1;
    Reference or sites showed in the video:

  1. change-table-name-to-upper-case
2016
05/04
CATEGORY
Information Technology
mysql
TAGS
Leave a comment

Maven liquibase skeleton project

Part 1 and Part 2 in a playlist


General steps taken in the video above.(Not the exact steps)

  1. Start mysql and create database sample_one.
  1. Create new maven java application. I named it maven_liquibase_skeleton.
  1. Add mysql and liquibase dependency to pom.xml file. (The below was taken from Maven liquibase configuration with some my own changes)
    2. <dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
    </dependency>
</dependencies>
  1. Add liquibase plugin to pom.xml file. (The below was taken from Maven liquibase configuration with some my own changes)
    2. <build>
	<plugins>
		<plugin>
			<groupId>org.liquibase</groupId>
			<artifactId>liquibase-maven-plugin</artifactId>
			<version>3.5.0</version>
			<configuration>                  
				<propertyFile>src/main/resources/liquibase/sample_one_database_config.properties</propertyFile>
				<logging>debug</logging>
				<dropFirst>true</dropFirst>
			</configuration>                
			<executions>
				<execution>
					<phase>process-resources</phase>                                                                  
					<goals>
						<goal>update</goal>
					</goals>
				</execution>
			</executions>
		</plugin> 	
	</plugins>
</build>
  1. Create sample_one_database_config.properties on directory, {project_source_directory}/src/main/resources/liquibase.
  1. Add the below content into the file sample_one_database_config.properties in path .
    2. driver: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/sample_one
username: greenhorn
password: greenhorn
changeLogFile: src/main/resources/liquibase/sample_one_database.changelog.xml
  1. Create the changeLogFile file as mentioned in the above content. Create sample_one_database.changelog.xml on directory, {project_source_directory}/src/main/resources/liquibase.
  1. Add the below content into the file sample_one_database.changelog.xml.(The below was taken from Liquibase home page with some my own changes)
    2. <?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <preConditions>
        <runningAs username="liquibase"/>
    </preConditions>

    <changeSet id="1" author="nvoxland">
        <createTable tableName="person">
            <column name="id" type="int" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="firstname" type="varchar(50)"/>
            <column name="lastname" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="state" type="char(2)"/>
        </createTable>
    </changeSet>

    <changeSet id="2" author="nvoxland">
        <addColumn tableName="person">
            <column name="username" type="varchar(8)"/>
        </addColumn>
    </changeSet>
    <changeSet id="3" author="nvoxland">
        <addLookupTable
            existingTableName="person" existingColumnName="state"
            newTableName="state" newColumnName="id" newColumnDataType="char(2)"/>
    </changeSet>

    <!--external files-->
    <include file="src/main/resources/liquibase/sample_one_database.default-data.xml"/>

</databaseChangeLog>
  1. Create the file mentioned by the <include file> tag in the above content. Create sample_one_database.default-data.xml on directory, {project_source_directory}/src/main/resources/liquibase.
  1. Add the below content into the file sample_one_database.default-data.xml.
    2. <?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
    
    <changeSet id="1" author="bob">
        <createTable tableName="ResetPassword">
            <column name="Username" type="varchar(30)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="Email" type="varchar(150)">
                <constraints nullable="false"/>
            </column>
            <column name="active" type="boolean" defaultValueBoolean="true"/>
        </createTable>
    </changeSet>
</databaseChangeLog>
  1. Clean and Build the project from Netbeans.


NOTE
The sample maven project used for this exercise can be downloaded from:

maven_liquibase_skeleton

    Reference or sites showed in the video:

  1. http://www.liquibase.org/
  2. Maven liquibase configuration
  3. Maven update plugin
2016
05/04
CATEGORY
Information Technology
maven
mysql
TAGS

Leave a comment

DAO on Plain JPA

Part 1

Part 2

General steps taken in the video above.(Not the exact steps)

  1. Create Dao interface in package org.entity.dao with below definition:
    2. public interface Dao {
	public boolean insert(T t);
	public boolean update(T t);
	public boolean delete(T t);
}

  1. Create StudentDaoJpaImpl class in package org.entity.dao.impl with the below content:
    2. /**
 * @PersistenceUnit has a unitName attribute. Its value is optional;
 * however, it can be used to inject another entityManagerFactory bean
 * defined in the container.
 */
@PersistenceUnit
private EntityManagerFactory entityManagerFactory;

@Override
public boolean insert(Student t) {
	EntityManager entityManager = entityManagerFactory
			.createEntityManager();
	EntityTransaction transaction = entityManager.getTransaction();
	transaction.begin();

	entityManager.persist(t);

	transaction.commit();
	entityManager.close();

	return true;
}

  1. Added StudentDaoJpaImpl as spring managed bean in DBConfiguration class:
    2. @Bean
public StudentDaoJpaImpl studentDao() {
	StudentDaoJpaImpl dao = new StudentDaoJpaImpl();
	return dao;
}

  1. Change main method to use the spring managed bean to persist Student object:
    2. 	
public static void main(String[] args) {
	ApplicationContext applicationContext = new AnnotationConfigApplicationContext(
			DBConfiguration.class);
	StudentDaoJpaImpl dao = applicationContext.getBean(StudentDaoJpaImpl.class);
	Student student = new Student();
	student.setFirstName("Grey");
	student.setLastName("Joy");
	dao.insert(student);
}

  1. Run main method.


NOTE
The sample maven project used for this exercise can be downloaded from:

jpa_spring_2

2016
04/23
CATEGORY
Information Technology
Java
maven
mysql
TAGS



Leave a comment

Spring + JPA + Hibernate + mysql in Netbeans 8.1

Part 1

Part 2

General steps taken in the video above.(Not the exact steps)

  1. Add spring framework dependencies as below into the pom.xml: 
    2. <!-- spring framework -->
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-orm</artifactId>
	<version>4.0.5.RELEASE</version>
</dependency>
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-context</artifactId>
	<version>4.0.5.RELEASE</version>
</dependency>

  1. Moved class Student and Book into package org.entity. Just to keep separation clear.

  1. Add class DBConfiguration into package org.entity.config. Below is the class content: 
    2. import java.util.HashMap;
import java.util.Map;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import javax.sql.DataSource;
import org.hibernate.jpa.HibernatePersistenceProvider;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;

@Configuration
public class DBConfiguration {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/jpa_2");
        dataSource.setUsername("greenhorn");
        dataSource.setPassword("greenhorn");
        return dataSource;
    }

    private Map jpaProperties() {
        Map jpaPropertiesMap = new HashMap();
        jpaPropertiesMap.put("hibernate.dialect", "org.hibernate.dialect.MySQLDialect");
        jpaPropertiesMap.put("hibernate.hbm2ddl.auto", "create");
        return jpaPropertiesMap;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
        factoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
        factoryBean.setDataSource(dataSource());
        factoryBean.setPackagesToScan("org.entity");
        factoryBean.setJpaPropertyMap(jpaProperties());
        return factoryBean;
    }

}

  1. Change Main class as below: 
    2. import org.entity.Student;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import org.entity.config.DBConfiguration;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

public class Main {

    public static void main(String[] args) {
        ApplicationContext applicationContext
                = new AnnotationConfigApplicationContext(DBConfiguration.class);
        EntityManagerFactory entityManagerFactory
                = applicationContext.getBean(EntityManagerFactory.class);
        EntityManager entityManager = entityManagerFactory.createEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();
        Student student = new Student();
        student.setFirstName("Jambu");
        student.setLastName("Jack");
        entityManager.persist(student);
        transaction.commit();
        entityManager.close();
    }
}

  1. Delete META-INF directory with its content, persistence.xml.

  1. Run Main class from Netbeans 8.1


NOTE
The sample maven project used for this exercise can be downloaded from

jpa1_spring

2016
04/23
CATEGORY
Information Technology
Java
maven
mysql
TAGS



Leave a comment

JPA using Hibernate and mysql in Netbeans 8.1

Part 1

Part 2

General steps taken in the video above.(Not the exact steps)

  1. Create database jpa_1:
    2. create database jpa_1;

  1. Configure Netbeans 8.1 to have the database in services:
    2. Database: jpa_1
    url: jdbc:mysql://localhost:3306/jpa_1
    username: greenhorn

  1. Create a maven java application in Netbeans 8.1.

  1. Add maven dependencies into pom.xml:

    2. <dependencies>

    <!– for JPA, use hibernate-entitymanager instead of hibernate-core –>
       <dependency>
          <groupId>org.hibernate</groupId>
          <artifactId>hibernate-entitymanager</artifactId>
          <version>5.0.6.Final</version>
       </dependency>

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

       <dependency>
          <groupId>javax.transaction</groupId>
          <artifactId>jta</artifactId>
          <version>1.1</version>
       </dependency>

    </dependencies>

  1. Add class, Book.java from Netbeans “New” right click option. Includes the below instance variable.
    2. private String name;

  1. Add class, Student.java from Netbeans “New” right click option. Includes the below instance variables.

    2. private String firstName;

    private String lastName;

    @OneToMany(cascade = CascadeType.ALL)
    @JoinColumn(name = “student_id”)
    private Set<Book> books = new HashSet<Book>();

  1. Generate getter and setter methods for the instance variables in Student and Book class from Netbeans.

  1. Add class, Main.java from Netbeans “New” right click option. Includes the below main method.

    2. public static void main(String[] args) {
         EntityManagerFactory entityManagerFactory =
         Persistence.createEntityManagerFactory(“abc”);
         EntityManager entityManager = entityManagerFactory.createEntityManager();
         entityManagerFactory.close();
    }

  1. Clean the pom file. It should look similar to the below:

    2. <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>org.greenhorn</groupId>
        <artifactId>jpa1</artifactId>
        <version>1.0-SNAPSHOT</version>
        <packaging>jar</packaging>
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <maven.compiler.source>1.8</maven.compiler.source>
            <maven.compiler.target>1.8</maven.compiler.target>
        </properties>

        <dependencies>

            <!-- for JPA, use hibernate-entitymanager instead of hibernate-core -->
            <dependency>
                <groupId>org.hibernate</groupId>
                <artifactId>hibernate-entitymanager</artifactId>
                <version>4.3.1.Final</version>
            </dependency>

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

            <dependency>
                <groupId>javax.transaction</groupId>
                <artifactId>jta</artifactId>
                <version>1.1</version>
            </dependency>
        </dependencies>
    </project>

  1. Run Main class from Netbeans 8.1


NOTE
The sample maven project used for this exercise can be downloaded from

jpa1

2016
04/22
CATEGORY
Information Technology
Java
maven
mysql
TAGS


Leave a comment

Connecting remote mysql database using ssh tunnel


The command used to create remote ssh tunnel(sample):

ssh -L 3306:localhost:3306 greenhorn@192.168.1.254

Command used to connect to server using ssh:

ssh greenhorn@192.168.1.254.com

Command to connect to database using user “greenhorn”:

mysql -u greenhorn -p

Command to use a database in particular. In this case I’m using “greenhornDb”:

use greenhornDb;

To show all the tables in the selected database I used command:

show tables;

WARNING:
If you have local mysql running on the same port 3360. You need to stop it first.

Reference:
access-your-mysql-server-remotely-over-ssh

2015
10/06
CATEGORY
Information Technology
mysql
TAGS

1 comment

How to change password of mysql user with existing password

Changing the existing user “greenhorn” password.

  1. Login to mysql using root user(your root user could be different from the one below):
    2. mysql -u root -p

  1. Change user password which uses “localhost” host.:
    2. set password for ‘greenhorn’@’localhost’= password(‘test1234’);

  1. Change user password which uses “%” host.:
    2. set password for ‘greenhorn’@’%’= password(‘test1234’);

2015
09/08
CATEGORY
mysql
Leave a comment

Creating enum type column in existing mysql table

The script of the video above is similar to below:

  1. Creating a database called dev_testing.
    2. create database dev_testing;
  1. Connect using netbeans.
  1. Create a table called tbl_20150819.

    2. – With one column id int auto_increment.

    – With one column name varchar(30),

    – primary key(id).

    CREATE TABLE tbl_20150819(

      id int(10) NOT NULL auto_increment,

      name varchar(30) default NULL,

      PRIMARY KEY  (id)

    )
  1. Insert 2 data.
    2. insert into tbl_20150819 (name) values(‘fry’);

    insert into tbl_20150819 (name) values(‘leela’);
  1. Select * from tbl_20150819
  1. Alter table add enum column currency type enum.

    2. – enum values(AUD,NZD);

    ALTER TABLE tbl_20150819 ADD currency ENUM(‘AUD’, ‘NZD’);
  1. Remove column currency. Add again column currency with default value AUD.
    2. Alter table tbl_20150819 drop column currency;

    ALTER TABLE tbl_20150819 ADD currency ENUM(‘AUD’, ‘NZD’) Default ‘AUD’;
  1. Insert 1 new data with currency value AUD.
    2. insert into tbl_20150819 (name, currency) values(‘bender’,’AUD’);
  1. Insert 1 new data with currency value NZD.
    2. insert into tbl_20150819 (name, currency) values(‘amy’,’NZD’);
  1. select all from tbl_20150819 where currency=’AUD’.
    2. select * from tbl_20150819 where currency=’AUD’;
  1. Select all from table 20150819_tbl where currency=’AUD’.
    2. select * from tbl_20150819 where currency=’NZD’;

12. Done.

 

2015
08/20
CATEGORY
Information Technology
mysql
Leave a comment