HBase series (11) – Spring/Spring Boot + Mybatis + Phoenix integration

One, foreword

Use Spring+Mybatis to run Phoenix and other relational databases (e.g. Mysql, Oracle). The configuration is basically the same.

• Spring + Mybatis + Phoenix
• SpringBoot + Mybatis + Phoenix

Spring + Mybatis + Phoenix

2.1 Project Structure

2.2 Main Dependencies

In addition to spring-related dependencies, you need to import Phoenix-Core and the corresponding Mybatis dependency package

<! -- Mybatis dependencies -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.3.2</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.6</version>
</dependency>
<! --phoenix core-->
<dependency>
    <groupId>org.apache.phoenix</groupId>
    <artifactId>phoenix-core</artifactId>
    <version>4.14.0 - cdh5.14.2</version>
</dependency>
Copy the code

2.3 Database Configuration Files

Configure the database driver and ZooKeeper address in the database configuration file jdbc.properties

# database driven phoenix. DriverClassName = org. Apache. Phoenix. JDBC. # PhoenixDriver zookeeper address Phoenix. Url = JDBC: phoenix: 192.168.0.105:2181Copy the code

2.4 Configuring the data source and session Factory

<?xml version="1.0" encoding="UTF-8"? >
<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:tx="http://www.springframework.org/schema/tx"
       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-4.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

    <! -- Open annotation pack scan -->
    <context:component-scan base-package="com.heibaiying.*"/>

    <! -- Specify the location of the configuration file -->
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <! -- Configure data source -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <! - Phoenix configuration - >
        <property name="driverClassName" value="${phoenix.driverClassName}"/>
        <property name="url" value="${phoenix.url}"/>
    </bean>

    <! Mybatis session factory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <! Mapper file location -->
        <property name="mapperLocations" value="classpath*:/mappers/**/*.xml"/>
        <property name="configLocation" value="classpath:mybatisConfig.xml"/>
    </bean>

    <! -- Scan registered interface -->
    <! What it does: Starts a recursive search from the base package of an interface and registers them as MapPerFactoryBeans (only interfaces of at least one method are registered; , concrete classes are ignored)-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <! -- Specify session factory -->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        <! Mybatis -->
        <property name="basePackage" value="com.heibaiying.dao"/>
    </bean>

</beans>
Copy the code

2.5 Setting Mybtais Parameters

Create a new Mybtais configuration file and configure additional parameters as required. For more Settings configuration items, see the official documentation

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

      

<! -- Mybatis config file -->
<configuration>
    <settings>
        <! -- Enable hump naming -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <! SQL > select * from 'SQL';
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
</configuration>
Copy the code

2.6 Querying Interfaces

public interface PopulationDao {

    List<USPopulation> queryAll(a);

    void save(USPopulation USPopulation);

    USPopulation queryByStateAndCity(@Param("state") String state, @Param("city") String city);

    void deleteByStateAndCity(@Param("state") String state, @Param("city") String city);
}
Copy the code

      

<mapper namespace="com.heibaiying.dao.PopulationDao">


    <select id="queryAll" resultType="com.heibaiying.bean.USPopulation">
        SELECT * FROM us_population
    </select>

    <insert id="save">
        UPSERT INTO us_population VALUES( #{state}, #{city}, #{population} )
    </insert>

    <select id="queryByStateAndCity" resultType="com.heibaiying.bean.USPopulation">
        SELECT * FROM us_population WHERE state=#{state} AND city = #{city}
    </select>

    <delete id="deleteByStateAndCity">
        DELETE FROM us_population WHERE state=#{state} AND city = #{city}
    </delete>

</mapper>
Copy the code

2.7 Unit Tests

@RunWith(SpringRunner.class)
@ContextConfiguration({"classpath:springApplication.xml"})
public class PopulationDaoTest {

    @Autowired
    private PopulationDao populationDao;

    @Test
    public void queryAll(a) {
        List<USPopulation> USPopulationList = populationDao.queryAll();
        if(USPopulationList ! =null) {
            for (USPopulation USPopulation : USPopulationList) {
                System.out.println(USPopulation.getCity() + ""+ USPopulation.getPopulation()); }}}@Test
    public void save(a) {
        populationDao.save(new USPopulation("TX"."Dallas".66666));
        USPopulation usPopulation = populationDao.queryByStateAndCity("TX"."Dallas");
        System.out.println(usPopulation);
    }

    @Test
    public void update(a) {
        populationDao.save(new USPopulation("TX"."Dallas".99999));
        USPopulation usPopulation = populationDao.queryByStateAndCity("TX"."Dallas");
        System.out.println(usPopulation);
    }


    @Test
    public void delete(a) {
        populationDao.deleteByStateAndCity("TX"."Dallas");
        USPopulation usPopulation = populationDao.queryByStateAndCity("TX"."Dallas"); System.out.println(usPopulation); }}Copy the code

SpringBoot + Mybatis + Phoenix

3.1 Project Structure

3.2 Main Dependencies

<! -- Mybatis 1.3.x (1.3.1) for more information about spring-boot and mybatis, see <a href="http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/">-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>
<! --phoenix core-->
<dependency>
    <groupId>org.apache.phoenix</groupId>
    <artifactId>phoenix-core</artifactId>
    <version>4.14.0 - cdh5.14.2</version>
</dependency>
<dependency>
Copy the code

Spring Boot and Mybatis version mapping:

MyBatis – Spring – the Boot – the Starter edition	MyBatis – Spring version	The Spring version of the Boot
1.3.x (1.3.1)	1.3 the or who	1.5 the or who
1.2.x (1.2.1)	1.3 the or who	1.4 the or who
1.1.x (1.1.1)	1.3 the or who	1.3 the or who
1.0.x (1.0.2)	1.2 the or who	1.3 the or who

3.3 Configuring a Data Source

Yml. Spring Boot 2.x uses Hikari as the default database connection pool. Hikari is currently the best connection pool on the Java platform and performs better than Druid.

spring:
  datasource:
    # zookeeper address
    url: JDBC: phoenix: 192.168.0.105:2181
    driver-class-name: org.apache.phoenix.jdbc.PhoenixDriver

    The following connection pool configuration is not necessary if you do not want to configure a special database connection pool configuration
    2 # spring - the boot default X using high-performance Hikari as connection pool More configuration can refer to https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      The minimum number of free connections maintained in the pool
      minimum-idle: 10
      # Maximum number of connections in the pool, both idle and active
      maximum-pool-size: 20
      # This property controls the default automatic commit behavior for connections returned from the pool. The default is true
      auto-commit: true
      # Maximum idle time allowed
      idle-timeout: 30000
      # This property represents the user-defined name of the connection pool, which is mainly displayed in the logging and JMX administrative console to identify the pool and pool configuration. Default value: Automatically generated
      pool-name: custom-hikari
      # This property controls the maximum lifetime of a connection in the pool, with a value of 0 indicating infinite lifetime, default 1800000 meaning 30 minutes
      max-lifetime: 1800000
      The database connection timeout period is 30 seconds
      connection-timeout: 30000
      Mysql > select 1 from dual; mysql > select 1 from dual
      connection-test-query: SELECT 1

# mybatis
mybatis:
  configuration:
    Enable print SQL statement debugging
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
Copy the code

3.4 Creating a Query Interface

Above Spring+Mybatis we use XML to write SQL, in order to reflect Mybatis support a variety of ways, here we use annotations to write SQL.

@Mapper
public interface PopulationDao {

    @Select("SELECT * from us_population")
    List<USPopulation> queryAll(a);

    @Insert("UPSERT INTO us_population VALUES( #{state}, #{city}, #{population} )")
    void save(USPopulation USPopulation);

    @Select("SELECT * FROM us_population WHERE state=#{state} AND city = #{city}")
    USPopulation queryByStateAndCity(String state, String city);


    @Delete("DELETE FROM us_population WHERE state=#{state} AND city = #{city}")
    void deleteByStateAndCity(String state, String city);
}
Copy the code

3.5 Unit Tests

@RunWith(SpringRunner.class)
@SpringBootTest
public class PopulationTest {

    @Autowired
    private PopulationDao populationDao;

    @Test
    public void queryAll(a) {
        List<USPopulation> USPopulationList = populationDao.queryAll();
        if(USPopulationList ! =null) {
            for (USPopulation USPopulation : USPopulationList) {
                System.out.println(USPopulation.getCity() + ""+ USPopulation.getPopulation()); }}}@Test
    public void save(a) {
        populationDao.save(new USPopulation("TX"."Dallas".66666));
        USPopulation usPopulation = populationDao.queryByStateAndCity("TX"."Dallas");
        System.out.println(usPopulation);
    }

    @Test
    public void update(a) {
        populationDao.save(new USPopulation("TX"."Dallas".99999));
        USPopulation usPopulation = populationDao.queryByStateAndCity("TX"."Dallas");
        System.out.println(usPopulation);
    }


    @Test
    public void delete(a) {
        populationDao.deleteByStateAndCity("TX"."Dallas");
        USPopulation usPopulation = populationDao.queryByStateAndCity("TX"."Dallas"); System.out.println(usPopulation); }}Copy the code

Add: construct a predicate sentence

The building statements of the test tables involved in the unit tests above are as follows:

CREATE TABLE IF NOT EXISTS us_population (
      state CHAR(2) NOT NULL,
      city VARCHAR NOT NULL,
      population BIGINT
      CONSTRAINT my_pk PRIMARY KEY (state, city));
      
-- Test data
UPSERT INTO us_population VALUES('NY','New York',8143197);
UPSERT INTO us_population VALUES('CA','Los Angeles',3844829);
UPSERT INTO us_population VALUES('IL','Chicago',2842518);
UPSERT INTO us_population VALUES('TX','Houston',2016582);
UPSERT INTO us_population VALUES('PA','Philadelphia',1463281);
UPSERT INTO us_population VALUES('AZ','Phoenix',1461575);
UPSERT INTO us_population VALUES('TX','San Antonio',1256509);
UPSERT INTO us_population VALUES('CA','San Diego',1255540);
UPSERT INTO us_population VALUES('CA','San Jose',912332);
Copy the code

See the GitHub Open Source Project: Getting Started with Big Data for more articles in the big Data series