使用Spring JdbcTemplate连接主流数据库
数据库连接依赖配置
在Spring Boot项目中配置数据库驱动依赖：
<dependencies>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
数据映射实体实现
创建数据实体类并实现RowMapper接口：
package com.example.entity;
import lombok.Data;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
@Data
public class SensorData implements RowMapper<SensorData> {
private String identifier;
private String description;
private Double measurement;
private String unit;
private String category;
@Override
public SensorData mapRow(ResultSet rs, int rowNum) throws SQLException {
SensorData data = new SensorData();
data.setIdentifier(rs.getString("TagName"));
data.setDescription(rs.getString("TagDesc"));
data.setMeasurement(rs.getDouble("Value"));
data.setUnit(rs.getString("TagUnit"));
data.setCategory(rs.getString("Type"));
return data;
}
}
数据库连接工具类
实现JdbcTemplate数据库连接操作：
package com.example.util;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import java.util.List;
public class DatabaseConnector {
private static JdbcTemplate template;
static {
DriverManagerDataSource source = new DriverManagerDataSource();
source.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
source.setUrl("jdbc:sqlserver://10.25.23.172:1433;databaseName=SCADA_Data_3040");
source.setUsername("SLSL");
source.setPassword("Admin@3040");
template = new JdbcTemplate(source);
}
public static List<SensorData> fetchDataByCategory(int category) {
String query = "SELECT * FROM RealData WHERE Type = ?";
return template.query(query, new SensorData(), category);
}
}
功能测试验证
测试数据库连接和数据获取功能：
package com.example.test;
import com.example.util.DatabaseConnector;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class ConnectionTest {
@Test
public void testDatabaseConnection() {
List<SensorData> dataList = DatabaseConnector.fetchDataByCategory(0);
System.out.println("Fetched records: " + dataList.size());
}
}
该方法同样适用于MySQL、PostgreSQL、Oracle等常见数据库连接，只需调整对应的驱动类和连接参数即可。
