All Downloads are FREE. Search and download functionalities are using the official Maven repository.
Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
com.litongjava.table.services.DbService Maven / Gradle / Ivy
package com.litongjava.table.services;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.litongjava.db.activerecord.Db;
import com.litongjava.db.activerecord.DbPro;
import com.litongjava.db.activerecord.Record;
import com.litongjava.db.activerecord.dialect.Dialect;
import com.litongjava.db.activerecord.dialect.MysqlDialect;
import com.litongjava.db.activerecord.dialect.PostgreSqlDialect;
import com.litongjava.db.activerecord.dialect.Sqlite3Dialect;
import com.litongjava.model.page.Page;
import com.litongjava.table.model.DbTableStruct;
import com.litongjava.table.utils.MarkdownTableUtils;
/**
* @author Tong Li
* @version 1.0
* @date 2020年8月27日_下午2:55:52
* @desc
*/
public class DbService {
public Map> getAllTableColumns(DbPro dbPro) {
Map> retval = new HashMap<>();
String[] tableNames = tableNames(dbPro);
for (String name : tableNames) {
retval.put(name, getTableColumns(dbPro, name));
}
return retval;
}
public String getAllTableDataExamplesOfMarkdown(DbPro dbPro) {
StringBuilder markdown = new StringBuilder();
String[] tableNames = tableNames(dbPro);
for (int i = 0; i < tableNames.length; i++) {
String name = tableNames[i];
Page paginate = dbPro.paginate(1, 1, "select *", "from " + name);
if (paginate == null) {
continue;
}
List records = paginate.getList();
if (records == null) {
continue;
}
String markdownTable = MarkdownTableUtils.to(records);
markdown.append("**" + (i + 1) + " ." + name + "**\n");
markdown.append(markdownTable);
}
return markdown.toString();
}
/**
* 为ai提供的支持
*
* @param dbPro
* @return
*/
public String getAllTableColumnsOfMarkdown(DbPro dbPro) {
StringBuilder markdown = new StringBuilder();
String[] tableNames = tableNames(dbPro);
for (int i = 0; i < tableNames.length; i++) {
String name = tableNames[i];
List tableColumns = getTableColumns(dbPro, name);
String markdownTable = MarkdownTableUtils.to(tableColumns);
markdown.append("**" + (i + 1) + " ." + name + "**\n");
markdown.append(markdownTable);
}
return markdown.toString();
}
public List getPrimaryKey(DbPro dbPro, String tableName) {
List ret = new ArrayList<>();
List columns = getTableStruct(dbPro, tableName);
if (columns.size() < 1) {
throw new RuntimeException("columns of " + tableName + " size is 0");
}
// 遍历出主键,添加到ret中
for (DbTableStruct record : columns) {
String key = record.getKey();
if ("PRI".equals(key) || "1".equals(key)) {
DbTableStruct tableColumn = new DbTableStruct();
tableColumn.setField(record.getField());
tableColumn.setType(record.getType());
tableColumn.setKey(key);
ret.add(tableColumn);
}
}
return ret;
}
@SuppressWarnings("unused")
private String[] toArray(List list) {
int size = list.size();
String[] ret = new String[size];
for (int i = 0; i < size; i++) {
ret[i] = list.get(i);
}
return ret;
}
/**
* 查询表名 support mysql and postgresql
*/
public String[] tableNames(DbPro dbPro) {
List tables = null;
Dialect dialect = dbPro.getConfig().getDialect();
if (dialect instanceof PostgreSqlDialect) {
String sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE';";
tables = Db.find(sql);
} else {
String sql = "show tables";
tables = Db.find(sql);
}
int size = tables.size();
String[] retval = new String[size];
for (int i = 0; i < size; i++) {
retval[i] = (String) tables.get(i).getColumnValues()[0];
}
return retval;
}
public String[] tableNames() {
return tableNames(Db.use());
}
public List getTableStruct(DbPro dbPro, String tableName) {
Dialect dialect = dbPro.getConfig().getDialect();
List columns = getTableColumns(dbPro, tableName);
List ret = new ArrayList<>();
if (dialect instanceof PostgreSqlDialect) {
for (Record record : columns) {
DbTableStruct tableColumn = new DbTableStruct();
tableColumn.setField(record.getStr("field"));
tableColumn.setType(record.getStr("type"));
String key = record.getStr("key");
tableColumn.setKey(key);
ret.add(tableColumn);
}
} else if (dialect instanceof Sqlite3Dialect) {
for (Record record : columns) {
DbTableStruct tableColumn = new DbTableStruct();
tableColumn.setField(record.getStr("name"));
tableColumn.setType(record.getStr("type"));
tableColumn.setIsNull(record.getStr("notnull"));
tableColumn.setDefaultValue(record.getStr("dflt_value"));
tableColumn.setKey(record.getStr("pk"));
ret.add(tableColumn);
}
} else if (dialect instanceof MysqlDialect) {
for (Record record : columns) {
DbTableStruct tableColumn = new DbTableStruct();
tableColumn.setField(record.getStr("Field"));
tableColumn.setType(record.getStr("Type"));
tableColumn.setIsNull(record.getStr("Null"));
tableColumn.setDefaultValue(record.getStr("Default"));
tableColumn.setKey(record.getStr("Key"));
ret.add(tableColumn);
}
} else {
// 遍历出主键,添加到ret中
for (Record record : columns) {
DbTableStruct tableColumn = new DbTableStruct();
tableColumn.setField(record.getStr("field"));
tableColumn.setType(record.getStr("type"));
String key = record.getStr("key");
tableColumn.setKey(key);
ret.add(tableColumn);
}
}
return ret;
}
public List getTableColumns(DbPro dbPro, String tableName) {
String sql;
Dialect dialect = dbPro.getConfig().getDialect();
List records = null;
if (dialect instanceof PostgreSqlDialect) {
// Field,Type,Null,Key,Default,Extra
sql = "SELECT column_name as Field, data_type as Type, is_nullable as Null, " + "column_default as Default, " + "CASE WHEN column_name = ANY (ARRAY(SELECT kcu.column_name "
+ "FROM information_schema.key_column_usage AS kcu " + "JOIN information_schema.table_constraints AS tc " + "ON kcu.constraint_name = tc.constraint_name "
+ "WHERE tc.table_name = ? AND tc.constraint_type = 'PRIMARY KEY')) THEN 'PRI' ELSE '' END AS key " + "FROM information_schema.columns WHERE table_name = ? and table_schema = ?;";
records = dbPro.find(sql, tableName, tableName, "public");
// 即便将别名设置为大写,返回的依然是小写,气人
} else if (dialect instanceof Sqlite3Dialect) {
// PRAGMA table_info(tablename)
// cid,name,type,notnull,dflt_value,pk
sql = "PRAGMA table_info(" + tableName + ")";
records = dbPro.find(sql);
} else {
// Field,Type,Null,Key,Default,Extra
sql = "show columns from " + tableName;
records = dbPro.find(sql);
}
return records;
}
/**
* 增加表字段
*
* @param tableName
* @param field
* @param type
* @param comment
*/
public void addColumn(String tableName, String field, String type, String comment) {
String sql = "ALTER TABLE %s ADD COLUMN `%s` %s comment '%s';";
sql = String.format(sql, tableName, field, type, comment);
Db.update(sql);
}
}
