Spring integration practice of distributed NoSQL database

Time:2020-12-4

background

Spring plays an important role in J2EE application development framework. It implements lightweight IOC (inversion of control) and AOP (aspect oriented) container frameworks. It can manage the life cycle of JavaBeans. It can be used alone or integrated with other frameworks, such as struts, mybatis, hibernate and other mainstream frameworks.

Spring’s IOC technology promotes low coupling, and can pass in other objects that an object depends on passively, instead of creating or finding dependent objects by itself. Aspect oriented features allow development by separating application business logic from system level services (such as auditing and transaction management). Business logic mainly performs various business functions, while system level services are responsible for other concerns, such as logs or transaction support. Spring contains and manages the configuration and life cycle of application objects. As a container, it can create a separate instance according to the business characteristics or generate a new instance every time it is needed. Spring can combine simple component configuration into complex applications, so it can be well integrated with other J2EE application development frameworks.

NoSQL database is becoming the mainstream in the technical field, so sequoiadb supports massive distributed data storage and provides common development language drivers for developers to directly operate the data in the database. In most enterprise application development process, most developers or customers tend to use standard SQL statements as the standard interface for data operation. The sequoiadb ssql suite supports standard SQL statements for various operations on the data in the database. This paper mainly introduces the integration of sequoiadb ssql suite with common J2EE development framework and the solutions to problems encountered in the development process.

Product introduction

SequoiadbIt can be used for high-performance relational database, such as high-performance relational database Type. PostgreSQL supports standard SQL. Sequoiadb ssql suite can use standard SQL statements to access sequoiadb database and complete various operations on sequoiadb database by extending PostgreSQL function. Common J2EE application persistence layer development frameworks, such as mybatis and hibernate, support PostgreSQL database, and these frameworks can integrate well with spring. Therefore, the integration process of sequoiadb ssql suite with spring and the integration process of PostgreSQL and spring are the same.

Environment construction

3.1 software configuration

Operating system: Windows 7
JDK:1.7.0_ 8064 bit, download address:

http://www.oracle.com/technetwork/java/javase/downloads/java-archive-downloads-javase7-521261.html#jdk-7u80-oth-JPR

Myeclipse:12.0.0
Sequoiadb: 1.12.5 or above
SequoiaDB SSQL:9.3.4
Dependency packages: Spring 3.2.4, mybatis 3.3.0, mybatis spring 1.1.1, mybatis Paginator 1.2.5, postgresql-9.3.jdbc 4-20131010.203348-4

This project mainly realizes querying data from sequoiadb and pagination to show the whole process of spring integration sequoiadb ssql integration.

Create a project project as shown in the following figure:

Spring integration practice of distributed NoSQL database

explain:

  1. When spring integrates sequoiadb, it is recommended to select DBCP connection pool to manage database connection

  2. For sequoiadb and sequoiadb ssql installation, please refer to the installation and connector section on the sequoiadb official website

3.2 set space and set creation and data preparation

1. Log in as the sdbadmin user, enter the / opt / sequoiadb / bin directory, and enter the SDB shell console to create the collection space and collection

Create the collection space script as follows:

var db = new Sdb();
db.dropCS('CS');

Create the collection script as follows:

db.CS.createCL('account_tx');

The execution results are as follows:

Spring integration practice of distributed NoSQL database

2. Exit the SDB shell script and execute the following command on the Linux console to import the data:

./sdbimprt --hosts=node03:11810,node02:11810 --type=csv --file=cs.csv -c CS -l account_tx --fields='account_id long,tx_id long,customer_name string,tx_time string,tx_value int,account_overage long,tx_type int,other_side_account long,tx_site string,summary string' -j 3

The execution results are as follows:

Spring integration practice of distributed NoSQL database

3. Use PostgreSQL connector to create database and external tables

The script to create the database is as follows:

bin/createdb -p 5432 cs

The script to create the external table is as follows:

create foreign table account_tx(account_id bigint,tx_id bigint,customer_name text,tx_time text,tx_value integer,account_overage bigint,tx_type integer,other_side_account bigint,tx_site text,summary text
) server sdb_server options ( collectionspace 'CS', collection 'account_tx' ) ;

The execution results are as follows:

Spring integration practice of distributed NoSQL database

4. Verify the test data in PostgreSQL

The validation test data script is as follows:

select * from account_tx limit 5;

The execution results are as follows:

Spring integration practice of distributed NoSQL database

Code display

4.1 framework building code display

1. In web.xml File initialization configuration information, such as spring listener and dispatcher, and loading spring related configuration files.

The specific configuration information is as follows:

    <?xml version="1.0" encoding="UTF-8"?>
    <web-app xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web=http://java.sun.com/xml/ns/javaee xsi:schemaLocation=http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd id="WebApp_ID" version="2.5">
        <display-name>csWeb</display-name>
        <welcome-file-list>
            <welcome-file>index.jsp</welcome-file>
        </welcome-file-list>
        <context-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>classpath:applicationContext-*.xml</param-value>
        </context-param>
        <listener>
            <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
        </listener>
        <servlet>
            <servlet-name>springmvc</servlet-name>
            <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
            <init-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>classpath:springmvc.xml</param-value>
            </init-param>
        </servlet>
        <servlet-mapping>
            <servlet-name>springmvc</servlet-name>
            <url-pattern>*.do</url-pattern>
        </servlet-mapping>
    </web-app>

2. Database connection information in jdbc.properties The specific information is as follows:

    jdbc.driver=org.postgresql.Driver
    jdbc.url=jdbc:postgresql://192.168.1.48:5432/cs
    jdbc.username=sdbadmin
    jdbc.password=

3. Data operation and transaction control configuration information are in ApplicationContext- dao.xml And ApplicationContext- transaction.xml Medium.

applicationContext- dao.xml The details are as follows:

    <beans xmlns=http://www.springframework.org/schema/beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:mvc=http://www.springframework.org/schema/mvc xmlns:context=http://www.springframework.org/schema/context xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx=http://www.springframework.org/schema/tx xsi:schemaLocation="http://www.springframework.org/schema/beans  http://www.springframework.org/schema/beans/spring-beans-3.2.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd ">
        <context:property-placeholder location="classpath:jdbc.properties" />
        <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${jdbc.driver}" />
    <property name="url" value="${jdbc.url}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
    <property name="maxActive" value="30" />
    <property name="maxIdle" value="5" />
    </bean>
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource" />
            <property name="mapperLocations">
                <array>
                    <value>classpath:mapper/*.xml</value>
                </array>
            </property>
            <property name="configLocation" value="classpath:mybatic-config.xml" />
        </bean>
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com/sequoiadb/cs/mapper"></property>
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
        </bean>
    </beans>

applicationContext- transaction.xml The details are as follows:

    <beans xmlns=http://www.springframework.org/schema/beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:mvc=http://www.springframework.org/schema/mvc xmlns:context=http://www.springframework.org/schema/context xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx=http://www.springframework.org/schema/tx xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd ">
        <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate" scope="prototype">
        <constructor-arg index="0" ref="sqlSessionFactory" />
        </bean>
        <aop:aspectj-autoproxy />
        <aop:config>
            <aop:pointcut id="appService" expression="execution(* com.sequoiadb.cs.service..*Service*.*(..))" />
            <aop:advisor advice-ref="txAdvice" pointcut-ref="appService" />
        </aop:config>
        <tx:advice id="txAdvice" transaction-manager="transactionManager">
            <tx:attributes>
                <tx:method name="select*" read-only="true" />
                <tx:method name="find*" read-only="true" />
                <tx:method name="get*" read-only="true" />
                <tx:method name="*" />
            </tx:attributes>
        </tx:advice>
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource" />
        </bean>
    </beans>

4. The configuration information of spring MVC is in springmvc.xml The details are as follows:

    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns=http://www.springframework.org/schema/beans xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
        xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.springframework.org/schema/beans  http://www.springframework.org/schema/beans/spring-beans-3.2.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.2.xsd">
        <context:component-scan base-package="com.sequoiadb.cs.service" />
        <context:component-scan base-package="com.sequoiadb.cs.controller" />
        <mvc:annotation-driven conversion-service="conversionService"></mvc:annotation-driven>
        <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
            <property name="prefix" value="/jsp/" />
            <property name="suffix" value=".jsp" />
        </bean>
        <bean id="conversionService"    class="org.springframework.format.support.FormattingConversionServiceFactoryBean">
            <property name="converters">
                <list>
                    <bean class="com.sequoiadb.cs.controller.converter.CustomDateConverter" />
                </list>
            </property>
        </bean>
    </beans>

5. Mybatis framework is used for data operation. Mybatis framework and spring integrate information in mybatic- config.xml The details are as follows:

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <typeAliases>
            <typeAlias type="com.sequoiadb.cs.entity.AccountTx" alias="AccountTx" />
        </typeAliases>
        <plugins>
            <plugin interceptor="com.github.pagehelper.PageHelper">
                <property name="dialect" value="postgresql" />
    <property name="offsetAsPageNum" value="true" />
                <property name="rowBoundsWithCount" value="true" />
                <property name="pageSizeZero" value="true" />
                <property name="reasonable" value="true" />
                <property name="params" value="pageNum=start;pageSize=limit;" />
                <property name="supportMethodsArguments" value="true" />
                <property name="returnPageInfo" value="check" />
            </plugin>
        </plugins>
    </configuration>

4.2 business implementation code display

Spring integrates sequoiadb ssql, adopts MVC design pattern, and adds external table account in model layer_ TX information is encapsulated into entity class, Dao layer adopts mybatis framework operation, service layer completes specific business logic, controller layer completes view response and various user action business.

1. Model layer account_ TX corresponds to the entity class in the accounttx class. The details are as follows:

    package com.sequoiadb.cs.entity;
    
    import java.io.Serializable;
    
    public class AccountTx implements Serializable {
    
        private String account_id;
        private String tx_id;
        private String customer_name;
        private String tx_time;
        private int tx_value;
        private long account_overage;
        private int tx_type;
        private String other_side_account;
        private String tx_site;
        private String summary;
    
        public String getAccount_id() {
            return account_id;
        }
    
        public void setAccount_id(String account_id) {
            this.account_id = account_id;
        }
    
        public String getTx_id() {
            return tx_id;
        }
    
        public void setTx_id(String tx_id) {
            this.tx_id = tx_id;
        }
    
        public String getCustomer_name() {
            return customer_name;
        }
    
        public void setCustomer_name(String customer_name) {
            this.customer_name = customer_name;
        }
    
        public String getTx_time() {
            return tx_time;
        }
    
        public void setTx_time(String tx_time) {
            this.tx_time = tx_time;
        }
    
        public int getTx_value() {
            return tx_value;
        }
    
        public void setTx_value(int tx_value) {
            this.tx_value = tx_value;
        }
    
        public long getAccount_overage() {
            return account_overage;
        }
    
        public void setAccount_overage(long account_overage) {
            this.account_overage = account_overage;
        }
    
        public int getTx_type() {
            return tx_type;
        }
    
        public void setTx_type(int tx_type) {
            this.tx_type = tx_type;
        }
    
        public String getOther_side_account() {
            return other_side_account;
        }
    
        public void setOther_side_account(String other_side_account) {
            this.other_side_account = other_side_account;
        }
    
        public String getTx_site() {
            return tx_site;
        }
    
        public void setTx_site(String tx_site) {
            this.tx_site = tx_site;
        }
    
        public String getSummary() {
            return summary;
        }
    
        public void setSummary(String summary) {
            this.summary = summary;
        }
    }

2. The data operation is in the accounttxmapper class, and the configuration information is in the AccountTxMapper.xml in

The details of the accounttxmapper class are as follows:

package com.sequoiadb.cs.mapper;
    import java.util.List;
    import java.util.Map;
    
    import com.sequoiadb.cs.entity.AccountTx;
    
    public interface AccountTxMapper {
        Public list < accounttx > queryall (map) throws exception; // queries all
    }

AccountTxMapper.xml The details are as follows:

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.sequoiadb.cs.mapper.AccountTxMapper">
        <resultMap id="BaseResultMap" type="com.sequoiadb.cs.entity.AccountTx">
            <id column="tx_id" property="tx_id" jdbcType="BIGINT" />
            <result column="account_id" property="account_id" jdbcType="BIGINT" />
            <result column="customer_name" property="customer_name" jdbcType="VARCHAR" />
            <result column="tx_time" property="tx_time" jdbcType="VARCHAR" />
            <result column="tx_value" property="tx_value" jdbcType="INTEGER" />
            <result column="account_overage" property="account_overage" jdbcType="BIGINT" />
            <result column="tx_type" property="tx_type" jdbcType="INTEGER" />
            <result column="other_side_account" property="other_side_account" jdbcType="BIGINT" />
            <result column="tx_site" property="tx_site" jdbcType="VARCHAR" />
            <result column="summary" property="summary" jdbcType="VARCHAR" />
        </resultMap>
        <select id="queryAll" resultType="com.sequoiadb.cs.entity.AccountTx" parameterType="java.util.Map"> 
            select * from account_tx where 1=1
            <if test="tx_id != null" >
                and tx_id = #{tx_id,jdbcType=BIGINT}
              </if>
              <if test="account_id != null" >
                and account_id = #{account_id,jdbcType=BIGINT} order by tx_time desc
              </if>
        </select>
    </mapper>

3. The service completes the specific business logic, the accounttxservice class defines the specific business interface to be completed, and the accounttxserviceimpl class inherits the accounttxservice to complete the specific business operations.

The details of the accounttxservice interface are as follows:

package com.sequoiadb.cs.service;
    
    import java.util.List;
    import java.util.Map;
    
    import com.sequoiadb.cs.entity.AccountTx;
    
    public interface AccountTxService {
        Public list < accounttx > queryall (map) throws exception; // queries all
    }

The specific implementation of accounttxserviceimpl is as follows:

    package com.sequoiadb.cs.service.impl;
    
    import java.util.List;
    import java.util.Map;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.sequoiadb.cs.entity.AccountTx;
    import com.sequoiadb.cs.mapper.AccountTxMapper;
    import com.sequoiadb.cs.service.AccountTxService;
    
    @Service("accountTxService")
    public class AccountTxServiceImpl implements AccountTxService {
    
        @Autowired
        private AccountTxMapper accountTxMapper;
    
        @Override
        public List<AccountTx> queryAll(Map map) throws Exception {
            return accountTxMapper.queryAll(map);
        }
    }

4. The controller completes the view response and the implementation of various user actions. The specific implementation is in the accounttxcontroller class. The specific information is as follows:

package com.sequoiadb.cs.controller;
    
    import java.io.IOException;
    import java.io.PrintWriter;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import net.sf.json.JSONArray;
    import net.sf.json.JSONObject;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    import com.github.pagehelper.PageHelper;
    import com.github.pagehelper.PageInfo;
    import com.sequoiadb.cs.entity.AccountTx;
    import com.sequoiadb.cs.service.AccountTxService;
    import com.sequoiadb.cs.utils.JSONUtils;
    
    @Controller
    @RequestMapping("/accountTx")
    public class AccountTxController {
        @Autowired
        private AccountTxService accountTxService;
        
        @RequestMapping(value = "/query", method = { RequestMethod.POST})
        @ResponseBody
        public String queryAll(HttpServletRequest request,HttpServletResponse response) throws Exception {
            String tx_id = request.getParameter("tx_id");
            String account_id = request.getParameter("account_id");
            String sEcho = null;
            Int pagenum = 0; // start index
            Int PageSize = 0; // number of rows displayed per page
            JSONArray jsonarray = JSONArray.fromObject(request.getParameter("aoData"));
            for (int i = 0; i < jsonarray.size(); i++) {
                JSONObject obj = (JSONObject) jsonarray.get(i);
                if (obj.get("name").equals("sEcho"))
                    sEcho = obj.get("value").toString();
         
                if (obj.get("name").equals("iDisplayStart"))
                    pageNum = obj.getInt("value");
         
                if (obj.get("name").equals("iDisplayLength"))
                    pageSize = obj.getInt("value");
            }
            PageHelper.startPage(pageNum, pageSize);
            Map map = new HashMap();
            if(tx_id != null && tx_id.length() > 0){
                map.put("tx_id", Long.parseLong(tx_id));
            }
            if(account_id != null && account_id.length() > 0){
                map.put("account_id", Long.parseLong(account_id));
            }
            List<AccountTx> accountTxList = accountTxService.queryAll(map);
    
            PageInfo<AccountTx> pagehelper = new PageInfo<AccountTx>(accountTxList);
    
            int  initEcho = Integer.parseInt(sEcho)+1;
            Map dataMap = new HashMap();    
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("iTotalRecords", pagehelper.getTotal());
            jsonObject.put("sEcho",initEcho);
            jsonObject.put("iTotalDisplayRecords", pagehelper.getTotal());
            jsonObject.put("aaData", pagehelper.getList());
            JSONUtils.toJSONString(dataMap);
            return jsonObject.toString();
        }
        private void sendJsonData(HttpServletResponse response, String data)throws IOException {
            response.setContentType("text/html;charset=UTF-8");
            PrintWriter out;
            out = response.getWriter();
            out.println(data);
            out.flush();
            out.close();
        }
    }

5. This project mainly demonstrates how to query data and paginate through sequoiadb ssql from sequoiadb database. Due to the large space, the specific front-end page code is not shown here. Please refer to the attachment index.jsp The source code in the file. The specific query results are shown in the following figure:

Spring integration practice of distributed NoSQL database

summary

From the above integration process, we can see that spring’s integration process of sequoiadb ssql suite is consistent with that of spring’s integration of other databases such as PostgreSQL database. In the integration process, the configuration information of spring is determined according to the selection of persistence layer framework, such as mybatis and hibernate. After the persistence layer framework is determined, the Java driver jar package of sequoiadb needs to be added to the project.