Method of connecting SQLServer database with JdbcTemplate of String in Java

I haven't written an article for a long time. On the one hand, I am busy in recent months and don't have much time. On the other hand, I have serious procrastination recently, and my idea of writing an article is always delayed. Today, find a small case to write 1, and fight against laziness 1.

First of all, let's talk about the background. We generally use mybatis or hibernate development framework to connect and operate databases. Recently, we have done research and development of GIS simulation products. According to the needs, we need to save three parts of data: 1. Business data, the amount of data is relatively small; 2. GIS data requires spatial relations; 3. IoT data has a large amount of data, which can reach more than one million per day in our self-test development stage. According to the above data characteristics, we use the traditional MySQL database, spatial database PostgreSQL, TD engine time series database, and do the dynamic switching of spring boot multi-data sources in the project. Today's focus is not on the implementation of multiple data sources, which will be introduced in another article later; In the process of research and development, we need to connect with other systems, connect SQLServer to pull data, and the project itself has made multiple data sources. If we continue to add data sources, it will increase the difficulty of the system, so we use jdbc to connect with external data sources. Look at the code below.

1. Introducing jar-dependent packages

The project uses Spring Boot. After creating the project, introduce the following dependencies:

<dependencies>
        
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <scope>runtime</scope>
        </dependency>

    </dependencies>

2. Utils development

1. Create entity classes to implement mapRow (ResultSet rs, int rowNum) methods of org. springframework. jdbc. core. RowMappe interfaces.

package com.johan.handler.task.iotSync.bean;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author: Johan
 * @date: 2021/10/18
 * @desc: IOT Information 
 */
@Data
public class IotDG implements RowMapper {
    /**
     *  Label name 
     */
    private String tagName;

    /**
     *  Label description 
     */
    private String tagDesc;

    /**
     *  Label value 
     */
    private Double tagVal;

    /**
     *  Label unit 
     */
    private String tagUnit;

    /**
     *  Type, 0  Stress, 1  Flow 
     */
    private String type;


    @Override
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        IotDG iotDG = new IotDG();
        iotDG.setTagName(rs.getString("TagName"));
        iotDG.setTagDesc(rs.getString("TagDesc"));
        iotDG.setTagVal(rs.getDouble("Value"));
        iotDG.setTagUnit(rs.getString("TagUnit"));
        iotDG.setType(rs.getString("Type"));

        return iotDG;
    }
}

2. Connect the database and read the table data

package com.johan.handler.task.iotSync.iotConvert;

import com.johan.handler.task.iotSync.bean.IotDG;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.sql.*;
import java.util.List;

/**
 * @author johan
 * @Description SQL server
 * @time 2021/10/18 18:26
 */
public class JDBCUtils {

    private static JdbcTemplate jdbcTemplate;
    static {
        String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String url ="jdbc:sqlserver://10.25.23.172:1433;databaseName=SCADA_Data_3040";// Connection address 
        String user ="SLSL";// Users 
        String password ="Admin@3040";// Password 

        DriverManagerDataSource dataSource=new DriverManagerDataSource();
        dataSource.setUrl(url);
        dataSource.setDriverClassName(driver);
        dataSource.setUsername(user);
        dataSource.setPassword(password);

        jdbcTemplate=new JdbcTemplate(dataSource);
    }


    public static List<IotDG> listAll(int type){
        String sql = "SELECT * FROM RealData where Type=" + type;

//        System.out.println(iotDGList);
        return jdbcTemplate.query(sql,new IotDG());
    }


}

3. Test

Method is static and can be called directly.

package com.johan.domain.iot;

import com.johan.handler.task.iotSync.bean.IotDG;
import com.johan.handler.task.iotSync.iotConvert.JDBCUtils;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

/**
 * @author johan
 * @time 2021/10/22 17:57
 */
@SpringBootTest
@Slf4j
public class IotDomainTest {

    @Test
    public void jdbcTest(){
        List<IotDG> iotDGList = JDBCUtils.listAll(0);
        System.out.println(iotDGList);
    }

}

Not only SQLServer, but also MySQL and Oracle, which are commonly used, can be connected with JdbcTemplate.