背景
有时候我们需要手工执行一些 SQL，用得最多的是select，尤其是分页处理。平时如果用框架多的话，那就不知道如何手工执行，及分页面处理。
手工执行 SQL语句
代码如下：
PreparedStatement preparedStatementFieldSQL = connection.prepareStatement(tableFieldsSql);
ResultSet rsFieldSQL = preparedStatementFieldSQL.executeQuery();
这这里的第一步需要先创建连接，如下代码：
String connUrl = "jdbc:mysql://localhost:3308/db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Hongkong&allowPublicKeyRetrieval=true";
String userName = "usname";
String password = "password";
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection(connUrl, userName, password);
分页如何处理
利用limit 语句进行分页，第一个字段为开始的下标（从 0 开始，不是页码， (pageNo - 1) * pageSize），第二个为 pageSize （每页数量）
select * from tablename order by menu_id limit 1, 20
总条数
总条数可以使用count * (去掉limit 部分)
select count(*) as total from tablename order by menu_id
完成代码如下
https://gist.github.com/aborn/619d315e36bab6b2503d32ca1c790e66
package com.zhonghuitech.web.controller.tool;
import com.alibaba.fastjson2.JSONObject;
import lombok.Data;
import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @author aborn (jiangguobao)
* @date 2024/09/11 18:01
*/
public class DBExecuteUtils {
@Data
public static class TableField implements Serializable {
private static final long serialVersionUID = -2854828353278355999L;
/**
* 字段名
*/
private String fieldName;
/**
* 字段类型
*/
private String fieldType;
/**
* 字段说明
*/
private String fieldComment;
/**
* 主键 0：否 1：是
*/
private boolean primaryPk;
/**
* 索引标识
*/
private String key;
private int keyIdx;
/**
* 扩展信息
*/
private String extra;
/**
* 是否可为 null
*/
private boolean nullable;
/**
* 默认值
*/
private String defVal;
/**
* 最大长度，当为 varchar时有用
*/
private String maxLen;
/**
* 字符集
* utf8mb4_0900_ai_ci
*/
private String collationName;
/**
* 字符编码
* utf8mb4
*/
private String charSetName;
public String getFieldType() {
return "varchar".equals(this.fieldType) ? this.fieldType + "(" + this.maxLen + ")" : this.fieldType;
}
public boolean isIntType() {
return "bigint".equals(this.fieldType) || "int".equals(this.fieldType);
}
public boolean isDoubleType() {
return "double".equals(this.fieldType);
}
public boolean isStringType() {
return "varchar".equals(this.fieldType) || "char".equals(this.fieldType) || "longtext".equals(this.fieldType) || "text".equals(this.fieldType) || "json".equals(this.fieldType);
}
public boolean isDatetimetype() {
return "datetime".equals(this.fieldType);
}
}
public static void main(String[] args) throws SQLException, ClassNotFoundException {
try {
String connUrl = "jdbc:mysql://localhost:3308/db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Hongkong&allowPublicKeyRetrieval=true";
String userName = "usname";
String password = "password";
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection(connUrl, userName, password);
tablePageListDynamic(connection);
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
public static String buildCountSQL(String querySQL) {
String str = querySQL.replaceFirst("\\*", "count(*) as total");
return str.substring(0, str.indexOf("limit"));
}
public static void tablePageListDynamic(Connection connection) {
List<JSONObject> dataList = new ArrayList<>();
int total = 0;
try {
String tableName = "sys_menu";
// 查询数据有哪些字段，保存到 fieldList 中
List<TableField> fieldList = new ArrayList<>();
String tableFieldsSql = String.format("select column_name, data_type, column_comment, column_key, COLUMN_DEFAULT, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, EXTRA, CHARACTER_SET_NAME, COLLATION_NAME from information_schema.columns "
+ "where table_name = '%s' and table_schema = (select database()) order by ordinal_position", tableName);
PreparedStatement preparedStatementFieldSQL = connection.prepareStatement(tableFieldsSql);
ResultSet rsFieldSQL = preparedStatementFieldSQL.executeQuery();
while (rsFieldSQL.next()) {
TableField field = new TableField();
field.setFieldName(rsFieldSQL.getString("column_name"));
String fieldType = rsFieldSQL.getString("data_type");
if (fieldType.contains(" ")) {
fieldType = fieldType.substring(0, fieldType.indexOf(" "));
}
field.setFieldType(fieldType);
field.setFieldComment(rsFieldSQL.getString("column_comment"));
String key = rsFieldSQL.getString("column_key");
field.setPrimaryPk("PRI".equalsIgnoreCase(key));
field.setKey(key);
field.setExtra(rsFieldSQL.getString("EXTRA"));
field.setNullable("YES".equalsIgnoreCase(rsFieldSQL.getString("IS_NULLABLE")));
field.setDefVal(rsFieldSQL.getString("COLUMN_DEFAULT"));
field.setMaxLen(rsFieldSQL.getString("CHARACTER_MAXIMUM_LENGTH"));
field.setCharSetName(rsFieldSQL.getString("CHARACTER_SET_NAME"));
field.setCollationName(rsFieldSQL.getString("COLLATION_NAME"));
fieldList.add(field);
}
// 分页查询数据，查询第 1 页，每页 20个，并将数据保存到 JSON list中去
String querySQL = String.format("select * from %s order by menu_id limit 1, 20", tableName);
PreparedStatement preparedStatement = connection.prepareStatement(querySQL);
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
JSONObject dataItem = new JSONObject();
for (TableField field : fieldList) {
if (field.isIntType()) {
dataItem.put(field.getFieldName(), rs.getInt(field.getFieldName()));
} else if (field.isDoubleType()) {
dataItem.put(field.getFieldName(), rs.getDouble(field.getFieldName()));
} else if (field.isDatetimetype()) {
String strRes = rs.getString(field.getFieldName());
if (org.apache.commons.lang3.StringUtils.isNotBlank(strRes)) {
// dataItem.put(field.getFieldName(), new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(strRes)); // 转成时间类型
dataItem.put(field.getFieldName(), strRes);
}
} else {
dataItem.put(field.getFieldName(), rs.getString(field.getFieldName()));
}
}
dataList.add(dataItem);
}
// 计算总条目
PreparedStatement pps = connection.prepareStatement(buildCountSQL(querySQL));
ResultSet resultPPS = pps.executeQuery();
while (resultPPS.next()) {
total = resultPPS.getInt("total");
}
System.out.println(total);
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
执行结果
image.png
网友评论