时序数据库QuestDB和TimescaleDB对比
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
/**
* @Describe 获取数据库的连接
* @auther mofei
* @date 2020年11月3日下午6:25:54
*/
public static Connection getConnection() throws Exception{
Properties properties = new Properties();
properties.setProperty("user", "admin");
properties.setProperty("password", "quest");
properties.setProperty("sslmode", "disable");
properties.setProperty("maxActive", "150");
//设置连接大超时时间300s
properties.setProperty("connectTimeout", "600");
properties.setProperty("socketTimeout", "600");
properties.setProperty("rewriteBatchedStatements", "true");
final Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:8812/qdb", properties);
connection.setAutoCommit(false);
return connection;
}
//增删改资源的关闭
public static void closeReource(Connection con, PreparedStatement ps){
try {
if(ps != null)
ps.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
if(con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//查询资源的关闭
public static void closeResource(Connection con, PreparedStatement ps, ResultSet rs){
try {
if(con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static String resultSetToJson(ResultSet rs) throws SQLException
{
// json数组
JSONArray array = new JSONArray();
// 获取列数
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// 遍历ResultSet中的每条数据
while (rs.next()) {
JSONObject jsonObj = new JSONObject();
// 遍历每一列
for (int i = 1; i <= columnCount; i++) {
String columnName =metaData.getColumnLabel(i);
String value = rs.getString(columnName);
jsonObj.put(columnName, value);
}
array.add(jsonObj);
}
return array.toString();
}
}@RequestMapping("/insert/{userid}")
public void insertData(@PathVariable(name ="userid") String userid) throws Exception {
System.out.println("开始执行:"+new SimpleDateFormat("yyyy/MM/dd-HH:mm:ss:SSS").format(new Date()));
long start = System.currentTimeMillis();
Connection conn= JDBCUtils.getConnection();
conn.setAutoCommit(false);
String sql= "insert into tracepoint(tp_guid, pda_id,user_id,x,y,lng,lat,angle,speed,reporttime,type) " +
"values(?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement ps= conn.prepareStatement(sql);
for(int i=-480;i<;i++){
for(int index = ; index < 5000; index++) {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");//设置日期格式
Calendar c = Calendar.getInstance();
c.add(Calendar.DATE, i);
Date end = c.getTime();
String reportTime=df.format(end);
ps.setString(1, "3");
ps.setString(2, "C2940FE3AE644CB6A6080D6572EDAEFF");
ps.setString(3, "S104U6693374828FA3D158B69189F0"+userid);
ps.setDouble(4, 527768.577594348);
ps.setDouble(5, 4629185.61306494);
ps.setDouble(6, 123.33410580);
ps.setDouble(7, 41.79738038);
ps.setDouble(8, 73);
ps.setDouble(9, 6.05581);
ps.setString(10, reportTime);
ps.setString(11, "GPS");
ps.addBatch();
}
}
int[] inserted1 = ps.executeBatch();
conn.commit();
JDBCUtils.closeReource(conn,ps);
long end = System.currentTimeMillis();
System.out.println("执行结束:"+new SimpleDateFormat("yyyy/MM/dd-HH:mm:ss:SSS").format(new Date()));
System.out.println("总耗时:" + (end - start) + " ms");
}
@RequestMapping("/queryquest")
public int queryData() throws Exception {
String res=null;
long start = System.currentTimeMillis();
Connection conn= JDBCUtils.getConnection();
PreparedStatement ps = conn.prepareStatement(" SELECT * from 'tracepoint' where user_id='S104U6693374828FA3D158B69189F018' and reporttime>='2022-03-24' and reporttime<'2022-03-25';") ;
ResultSet rs = ps.executeQuery();
int rowCount = ;
while(rs.next()) {
rowCount++;
}
JDBCUtils.closeResource(conn,ps,rs);
long end = System.currentTimeMillis();
System.out.println("QuestDb查询结果总行数："+rowCount+",查询结果总耗时:" + (end - start) + " ms");
// res= JDBCUtils.resultSetToJson(rs);
// JDBCUtils.closeResource(conn,ps,rs);
return rowCount;
}import lombok.var;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
@RestController
public class TimeScaleDbController {
public static Connection initConn(){
Connection conn=null;
String connUrl = "jdbc:postgresql://localhost:5435/example?user=postgres&password=jjgis123.0";
try {
conn = DriverManager.getConnection(connUrl);
} catch (SQLException throwable) {
throwable.printStackTrace();
}
return conn;
}
@RequestMapping("/createschema")
private static void createSchema() throws SQLException {
Connection conn=initConn();
try (var stmt = conn.createStatement()) {
stmt.execute("CREATE TABLE sensors ( id SERIAL PRIMARY KEY, type TEXT NOT NULL, location TEXT NOT NULL)");
}
try (var stmt = conn.createStatement()) {
stmt.execute("CREATE TABLE sensor_data ( time TIMESTAMPTZ NOT NULL, sensor_id INTEGER REFERENCES sensors (id), value DOUBLE PRECISION)");
}
try (var stmt = conn.createStatement()) {
stmt.execute("SELECT create_hypertable('sensor_data', 'time')");
}
}
@RequestMapping("/insertdata")
private static void insertData() throws SQLException {
System.out.println(new SimpleDateFormat("yyyy/MM/dd-HH:mm:ss:SSS").format(new Date()));
for(int i=-480;i<;i++){
Connection conn=initConn();
conn.setAutoCommit(false);
String sql= "insert into tracepoint(tp_guid, pda_id,user_id,x,y,lng,lat,angle,speed,reporttime,positiontype) values(?,?,?,?,?,?,?,?,?,to_timestamp(?,'yyyy-mm-dd hh24:mi:ss.us'),?)";
PreparedStatement ps= conn.prepareStatement(sql);
for(int index = ; index < 5000; index++) {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");//设置日期格式
Calendar c = Calendar.getInstance();
c.add(Calendar.DATE, i);
Date end = c.getTime();
String reportTime=df.format(end);
ps.setString(1, "3");
ps.setString(2, "C2940FE3AE644CB6A6080D6572EDAEFF");
ps.setString(3, "S174U6693374828FA3D158B69189F08E");
ps.setDouble(4, 527768.577594348);
ps.setDouble(5, 4629185.61306494);
ps.setDouble(6, 123.33410580);
ps.setDouble(7, 41.79738038);
ps.setDouble(8, 73);
ps.setDouble(9, 6.05581);
ps.setString(10, reportTime);
ps.setString(11, "GPS");
ps.addBatch();
}
int[] inserted1 = ps.executeBatch();
conn.commit();
conn.close();
ps.close();
System.out.println(new SimpleDateFormat("yyyy/MM/dd-HH:mm:ss:SSS").format(new Date()));
}
System.out.println("执行结束");
}
@RequestMapping("/querytimescale")
public int queryData() throws Exception {
String res=null;
long start = System.currentTimeMillis();
Connection conn=initConn();
PreparedStatement ps = conn.prepareStatement(" select * FROM public.tracepoint where user_id='S174U6693374828FA3D158B69189F08E' and reporttime >='2022-03-01'::timestamp and reporttime <'2022-03-02'::timestamp;") ;
ResultSet rs = ps.executeQuery();
int rowCount = 0;
while(rs.next()) {
rowCount++;
}
conn.close();
ps.close();
rs.close();
long end = System.currentTimeMillis();
System.out.println("TimescaleDb查询结果总行数："+rowCount+",查询结果总耗时:" + (end - start) + " ms");
// res= JDBCUtils.resultSetToJson(rs);
// JDBCUtils.closeResource(conn,ps,rs);
return rowCount;
}
}相关文章