目录
一.JDBC访问
1. Pom依赖
可以根据自己的mysql版本修改
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
2. Java代码
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
public class DBUtils {
public static void main(String[] args) throws Exception {
//查询语句
String sql = "select * from tb";
//1、解析配置
String url = "jdbc:mysql://192.168.57.188:3306/mytest";
String username = "root";
String password = "123456";
Class.forName("com.mysql.jdbc.Driver");
//2、获取connection连接
Connection conn = DriverManager.getConnection(url, username, password);
//3、获取PreparedStatement
PreparedStatement preparedStatement = conn.prepareStatement(sql);//支出传递问号,防止sql注入
//4、获取字段信息
ResultSet resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int fieldCount = metaData.getColumnCount();//字段数量
ArrayList<String> fields = new ArrayList<>(fieldCount);
for (int i = 1; i <= fieldCount; i++) {
fields.add(metaData.getColumnName(i));
}
//5、从结果集获取结果数据
while (resultSet.next()) {
HashMap<String, Object> line = new HashMap();
for (String field : fields) {
line.put(field, resultSet.getObject(field));
}
System.out.println(line);//打印一行信息
}
resultSet.close();
preparedStatement.close();
conn.close();
}
}
3. 表数据与运行结果
二. c3p0查询
1. pom依赖
<!--数据库驱动与连接池-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.27</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
2. JAVA代码
package com.digital.web.back.util;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;
import java.sql.*;
import java.util.*;
/**
* 数据库工具类
*/
public class DBUtil {
private static Logger logger = LoggerFactory.getLogger(DBUtil.class);//日志对象
private static ComboPooledDataSource pool;//连接池
/**
* 初始化连接池
*
* @throws Exception
*/
public static synchronized void init() {
if (pool != null) return;
try {
pool = new ComboPooledDataSource();
Resource resource = new ClassPathResource("application.properties");
Properties props = PropertiesLoaderUtils.loadProperties(resource);
pool.setDriverClass(props.getProperty("mysql.driver"));
pool.setJdbcUrl(props.getProperty("mysql.jdbc"));
pool.setUser(props.getProperty("mysql.user"));
pool.setPassword(props.getProperty("mysql.pwd"));
pool.setMaxPoolSize(Integer.valueOf(props.getProperty("mysql.initialPoolSize", "1")));
pool.setInitialPoolSize(Integer.valueOf(props.getProperty("mysql.maxPoolSize", "10")));
pool.setMinPoolSize(Integer.valueOf(props.getProperty("mysql.minPoolSize", "1")));
logger.info("初始化数据库完成");
} catch (Exception e) {
logger.error("初始化数据库连接池失败", e);
}
}
/**
* 获取数据库连接
*
* @return
*/
public static Connection getConn() {
try {
Connection conn = pool.getConnection();
logger.debug("获取数据库连接");
return conn;
} catch (SQLException e) {
logger.error("获取数据库连接异常,检查数据库运行是否正常", e);
pool.close();
pool = null;
init();//重新初始化
return null;
}
}
/**
* sql查询获取结果存储到 List<Map<String,Object>> 中
*
* @param uuid 唯一标识
* @param sql 查询sql语句
* @param conn 数据库连接
* @return
*/
public static List<Map<String, Object>> select(String uuid, String sql, Connection conn) {
List<Map<String, Object>> result = new LinkedList<>();
PreparedStatement preparedStatement = null;//支出传递问号,防止sql注入
ResultSet resultSet = null;
try {
preparedStatement = conn.prepareStatement(sql);//获取PreparedStatement
resultSet = preparedStatement.executeQuery();//获取查询结果的字段信息
ResultSetMetaData metaData = resultSet.getMetaData();
int fieldCount = metaData.getColumnCount();//字段数量
List<String> fields = new ArrayList<>(fieldCount);
for (int i = 1; i <= fieldCount; i++) {
fields.add(metaData.getColumnName(i));
}
//5、从结果集获取结果数据
while (resultSet.next()) {
Map<String, Object> line = new HashMap();
for (String field : fields) {
line.put(field, resultSet.getObject(field));
}
result.add(line);
}
} catch (SQLException e) {
logger.error(ObjectUtil.merge(uuid, "查询异常,查询sql为:", sql), e);
} finally {
ResourceUtil.close(resultSet);
ResourceUtil.close(preparedStatement);
}
return result;
}
public static void insert(Connection conn) {
}
/**
* 批量导入
*
* @param uuid 唯一标识
* @param conn 数据库连接
* @param tableName 表名称
* @param data 导入的数据
*/
public static void insertBatch(String uuid, Connection conn, String tableName, List<Map<String, Object>> data) throws Exception {
PreparedStatement ps = null;
if (data.size() < 1 || data.get(0).size() < 1) return;
Map<String, Object> first = data.get(0);
Set<String> keySet = first.keySet();
Object[] keyArray = keySet.toArray();
StringBuilder values = new StringBuilder();
StringBuilder keys = new StringBuilder();
values.append("?");
keys.append(keyArray[0]);
if (keyArray.length > 1) {
for (int i = 1; i < keyArray.length; i++) {
values.append(",?");
keys.append(ObjectUtil.merge(",", keyArray[i]));
}
}
String sql = ObjectUtil.merge("insert into ", tableName, "(", keys.toString(), ")", " value(", values.toString(), ")");//定义sql的格式
try {
conn.setAutoCommit(false);//关闭自动提交
ps = conn.prepareStatement(sql);
for (Map<String, Object> line : data) {
for (int i = 0; i < keyArray.length; i++) {
ps.setObject(i + 1, line.get(keyArray[i]));
}
ps.addBatch(); //将sql语句保存起来,先不执行
}
ps.executeBatch();//执行批处理中所有的sql语句
ps.clearBatch();//清空批处理
conn.commit();
logger.info(ObjectUtil.merge(uuid, "批量插入成功"));
} catch (Exception e) {
try {
conn.rollback();
conn.setAutoCommit(true);//开启自动提交
} catch (SQLException ex) {
logger.error(ObjectUtil.merge(uuid, "批量插入失败后回滚异常", ex));
}
conn.setAutoCommit(true);//开启自动提交
throw new Exception(e);
} finally {
ResourceUtil.close(ps);
}
}
/**
* 插入
*
* @param uuid 唯一标识
* @param conn 数据库连接
* @param tableName 表名称
* @param data 导入的数据
*/
public static void insert(String uuid, Connection conn, String tableName, Map<String, Object> data) throws Exception {
PreparedStatement ps = null;
if (data.size() < 1 || data.size() < 1) return;
Set<String> keySet = data.keySet();
Object[] keyArray = keySet.toArray();
StringBuilder values = new StringBuilder();
StringBuilder keys = new StringBuilder();
values.append("?");
keys.append(keyArray[0]);
if (keyArray.length > 1) {
for (int i = 1; i < keyArray.length; i++) {
values.append(",?");
keys.append(ObjectUtil.merge(",", keyArray[i]));
}
}
String sql = ObjectUtil.merge("insert into ", tableName, "(", keys.toString(), ")", " value(", values.toString(), ")");//定义sql的格式
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < keyArray.length; i++) {
ps.setObject(i + 1, data.get(keyArray[i]));
}
ps.execute();
logger.info(ObjectUtil.merge(uuid, "单次插入成功"));
} catch (Exception e) {
throw new Exception(e);
} finally {
ResourceUtil.close(ps);
}
}
/**
* 更新与删除
*
* @param uuid
* @param conn
* @param sql
*/
public static void update(String uuid, Connection conn, String sql) throws Exception {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
int i = ps.executeUpdate();
logger.info(ObjectUtil.merge(uuid, "更新完成,更新数据量: ", i));
} catch (Exception e) {
throw new Exception(e);
} finally {
ResourceUtil.close(ps);
}
}
public static void main(String[] args) throws Exception {
DBUtil.init();
Connection conn = DBUtil.getConn();
List<Map<String, Object>> data = new LinkedList<>();
HashMap<String, Object> line = new HashMap<>();
data.add(line);
line.put("id", 13);
line.put("update_time", "2023-04-01 00:01:03");
line.put("crop_name", 3);
line.put("create_time", "2023-04-01 00:01:03");
line.put("update_user", 5);
HashMap<String, Object> line1 = new HashMap<>();
data.add(line1);
line1.put("id", 64);
line1.put("update_time", "2023-04-01 00:01:03");
line1.put("crop_name", 8);
line1.put("create_time", "2023-04-01 00:01:03");
line1.put("update_user", 10);
DBUtil.insertBatch("asdadsdfs ", conn, "crop_copy", data);//批量插入
ResourceUtil.close(conn);
}
}
mysql 滑动窗口函数的运用
根据指定的时间窗口滑动
select
userid, -- 编码
money, -- 余额
sum(money) over (order by str_to_date(insert_date,'%Y-%m-%d %H:%i:%S') range between interval 20 MINUTE preceding and current row) moneySum, -- 滑动开窗函数,向前跨20分钟计算总数 滑动粒度为20分钟 汇总窗口函数sum
insert_date -- 操作时间
from tmp