jdbc获取数据库表结构

最新推荐文章于 2024-09-04 15:47:16 发布

KBXH

最新推荐文章于 2024-09-04 15:47:16 发布

阅读量5.8k

点赞数 2

文章标签： mysql 表结构 jdbc

本文链接：https://blog.csdn.net/u013792404/article/details/96024208

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

public static void main(String[] args) throws Exception {
		String url = "jdbc:mysql://localhost:3306/test";
		String user = "root";
		String password = "mysql";
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection(url, user, password);
		DatabaseMetaData metaData = connection.getMetaData();
        
        //获取数据库下面所有表
		ResultSet tables = metaData.getTables(null, null, "%", new String[] { "TABLE" });

		while (tables.next()) {
            // 列的个数
			int columnCount = tables.getMetaData().getColumnCount();

			List<String> colNamesList = new ArrayList<String>();
			for (int i = 1; i <= columnCount; i++) { // 获取列名称
				String columnName = tables.getMetaData().getColumnName(i);
				colNamesList.add(columnName) ;
			}
			System.out.println(colNamesList);
			
            // 根据指定列名称获取数据
			String TABLE_NAME = tables.getString("TABLE_NAME") ;
			System.out.println(TABLE_NAME);
			
			// String TABLE_CAT = tables.getString("TABLE_CAT");
			// String TABLE_SCHEM = tables.getString("TABLE_SCHEM");
			// String TABLE_NAME = tables.getString("TABLE_NAME");
			// String TABLE_TYPE = tables.getString("TABLE_TYPE");
			// String REMARKS = tables.getString("REMARKS");
			// String TYPE_CAT = tables.getString("TYPE_CAT");
			// String TYPE_SCHEM = tables.getString("TYPE_SCHEM");
			// String TYPE_NAME = tables.getString("TYPE_NAME");
			// String SELF_REFERENCING_COL_NAME =
			// tables.getString("SELF_REFERENCING_COL_NAME");
			// String REF_GENERATION = tables.getString("REF_GENERATION");

		}

	}

数据库中只有一个user表，结果输出：
[TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS, TYPE_CAT, TYPE_SCHEM, TYPE_NAME, SELF_REFERENCING_COL_NAME, REF_GENERATION]
user

        // User表  获取表的所有列
		ResultSet columns = metaData.getColumns(null, "%", "user", "%");

		// 列
		List<String> colslist = new ArrayList<String>();
		while (columns.next()) {
			// int columnCount = columns.getMetaData().getColumnCount();
			// for (int i = 1; i <= columnCount; i++) {// 获取所有列名
			// String columnName = columns.getMetaData().getColumnName(i);
			// list.add(columnName);
			// }
			
			// [TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
			// TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS,
			// NUM_PREC_RADIX, NULLABLE, REMARKS, COLUMN_DEF, SQL_DATA_TYPE,
			// SQL_DATETIME_SUB, CHAR_OCTET_LENGTH, ORDINAL_POSITION,
			// IS_NULLABLE, SCOPE_CATALOG, SCOPE_SCHEMA, SCOPE_TABLE,
			// SOURCE_DATA_TYPE, IS_AUTOINCREMENT, IS_GENERATEDCOLUMN]
			String COLUMN_NAME = columns.getString("COLUMN_NAME");
			String TYPE_NAME = columns.getString("TYPE_NAME");
			colslist.add(COLUMN_NAME + "|" + TYPE_NAME);
		}
		System.out.println(colslist);

输出：列名称|类型

[id|INT, name|VARCHAR, address|VARCHAR, phone|VARCHAR]

获取主键：

ResultSet PrimaryKeys = metaData.getPrimaryKeys(null, "%", "user");
		// 获取主键列
		List<String> PrimaryKeysist = new ArrayList<String>();
		while (PrimaryKeys.next()) {
			
			String COLUMN_NAME = PrimaryKeys.getString("COLUMN_NAME");
			PrimaryKeysist.add(COLUMN_NAME);
		}
		System.out.println(PrimaryKeysist);

输出：[id]

import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Test {
	public static void main(String[] args) throws Exception {
		String url = "jdbc:mysql://localhost:3306/test";
		String user = "root";
		String password = "mysql";
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection(url, user, password);
		DatabaseMetaData metaData = connection.getMetaData();

		// 获取所有表名
		// ResultSet tables = metaData.getTables(null, null, "%", new String[] {
		// "TABLE" });
		// List<String> tableList = new ArrayList<String>();
		// while (tables.next()) {
		// int columnCount = tables.getMetaData().getColumnCount();
		// for (int i = 1; i <= columnCount ; i++) {//获取所有列名
		// String columnName = tables.getMetaData().getColumnName(i);
		// System.out.print(columnName + "\t");
		// }
		// TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS TYPE_CAT
		// TYPE_SCHEM TYPE_NAME SELF_REFERENCING_COL_NAME REF_GENERATION
		// String TABLE_NAME = tables.getString("TABLE_NAME");
		// tableList.add(TABLE_NAME);
		//
		// }

		Map<String, List<String>> map = new HashMap<String, List<String>>();
		// User表
		String tableName = "user" ;
		ResultSet columns = metaData.getColumns(null, "%", tableName , "%");

		// 列
		List<String> colslist = new ArrayList<String>();
		while (columns.next()) {
			String COLUMN_NAME = columns.getString("COLUMN_NAME");
			String TYPE_NAME = columns.getString("TYPE_NAME");
			colslist.add(COLUMN_NAME + "|" + TYPE_NAME);
		}
		System.out.println(colslist);

		ResultSet PrimaryKeys = metaData.getPrimaryKeys(null, "%", "user");
		// 获取主键列
		List<String> PrimaryKeysist = new ArrayList<String>();
		while (PrimaryKeys.next()) {
			String COLUMN_NAME = PrimaryKeys.getString("COLUMN_NAME");
			PrimaryKeysist.add(COLUMN_NAME);
		}
		System.out.println(PrimaryKeysist);
		
		startGenerate(tableName,colslist,PrimaryKeysist) ;

	}
	
	private static void startGenerate(String tableName, List<String> colslist, List<String> primaryKeysist) {
		String basePath = "D:/workspace/" ;
		String projectName = "GenerateTest" ;
		String packageName = "com.icloud.manage" ;
		generatorFolder(basePath,projectName,packageName) ; //生成项目基本目录
		
		String packagePath = basePath + projectName + "/src/main/java" + "/" +  packageName.replaceAll("\\.", "\\/") ;
		
		String className = generateClassName(tableName);
		String classNameFile = packagePath + "/domain/" + className + ".java" ;
		
		generateDomain(classNameFile,colslist,primaryKeysist) ;
	}
	
	//TODO
	private static void generateDomain(String classNameFile, List<String> colslist, List<String> primaryKeysist) {
		
		
	}
	
	
	
	private static String generateClassName(String tableName) {
		String[] split = tableName.split("[_-]");
		StringBuffer buf = new StringBuffer() ;
		for (String str : split) {
			if(str.length()>1){
				buf.append(str.substring(0, 1).toUpperCase() + str.substring(1)) ;
			} else if(str.length() == 1){
				buf.append(str.toUpperCase());
			}
		}
		return buf.toString() ;
	}

	//生成项目基本目录
	private static void generatorFolder(String basePath,String projectName,String packageName) {
		String packageBasePath = basePath + projectName + "/src/main/java"  ;
		String resourcesPath = basePath + projectName + "/src/main/resources"  ;
		String testPackagePath = basePath + projectName + "/src/test/java"  ;
		String testResourcesPath = basePath + projectName + "/src/test/resources"  ;
		String packagePath = packageBasePath + "/" +  packageName.replaceAll("\\.", "\\/") ;
		generatePath(packageBasePath) ;
		generatePath(resourcesPath) ;
		generatePath(testPackagePath) ;
		generatePath(testResourcesPath) ;
		generatePath(packagePath) ;
		
		String domainPath = packagePath + "/domain" ;
		String controllerPath = packagePath + "/controller" ;
		String servicePath = packagePath + "/service" ;
		String mapperPath = packagePath + "/mapper" ;
		generatePath(domainPath) ;
		generatePath(controllerPath) ;
		generatePath(mapperPath) ;
		generatePath(servicePath) ;
		
	}
	
	private static void generatePath(String path) {
		File file = new File(path) ;
		if(!file.getParentFile().exists()){
			file.mkdirs() ;
		}else{
			file.mkdir()	;
		}
	}
	
	
	
	
	
	
	
	@org.junit.Test
	public void test(){
		System.out.println(generateClassName("user-role_id"));
	}
}