【MySQL数据库】代码生成器的数据库操作

1、得到一个连接下全部的数据库

Class.forName(databaseVo.getDiver_name());
//Connection connection = DriverManager.getConnection("jdbc:mysql://localhost","root","5516");
Connection connection = DriverManager.getConnection(databaseVo.getDatabase_url(), databaseVo.getDatabase_name(), databaseVo.getDatabase_password());
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("show databases");
while (rs.next()) {
	//showdatabases只得到一列数据，所以1就是这一行唯一的数据，为数据库名
    String databaseName = rs.getString(1);
    System.out.println(databaseName);
}

2、得到一个数据库下的全部表

Class.forName(databaseVo.getDiver_name());
Connection connection = DriverManager.getConnection(databaseVo.getDatabase_url(), databaseVo.getDatabase_name(), databaseVo.getDatabase_password());
DatabaseMetaData dbMetaData = connection.getMetaData();
ResultSet rs = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});
while (rs.next()) {
	System.out.println("表名：" + rs.getString("TABLE_NAME") + "<br/>" +
	"表类型：" + rs.getString("TABLE_TYPE") + "<br/>" +
	"表所属数据库：" + rs.getString("TABLE_CAT") + "<br/>" +
	"表所属用户名：" + rs.getString("TABLE_SCHEM") + "<br/>" +
	"表备注：" + rs.getString("REMARKS") + "<br/>");
}

3、得到一个表的全部信息

连接成功某一个数据库之后，可以使用这个操作：statement .executeQuery(“describe tname”) 可以得到表的字段信息。
如果没连接某一个固定数据库，则如下

Class.forName(databaseVo.getDiver_name());
Connection connection = DriverManager.getConnection(databaseVo.getDatabase_url(), 
Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
sql = getSql(databaseVo.getDatabase_name(), tableName);
ResultSet rs = statement.executeQuery(sql);
rs.first();
int var4 = rs.getRow();
if (var4 <= 0) {
    throw new Exception("该表不存在或者表中没有字段");
}
while (true) {
	ColumnVo columnVo = new ColumnVo();
	columnVo.setFieldName(FieldUtil.toCamelCase(rs.getString(1).toLowerCase()));
	columnVo.setFieldDbName(rs.getString(1).toUpperCase());
	columnVo.setPrecision(isBlank(rs.getString(4)));
	columnVo.setScale(isBlank(rs.getString(5)));
	columnVo.setCharmaxLength(isBlank(rs.getString(6)));
	columnVo.setNullable(getNullAble(rs.getString(7)));
	columnVo.setFieldType(getDataStructure(rs.getString(2).toLowerCase(), columnVo.getPrecision(), columnVo.getScale()));
	columnVo.setFieldDbType(FieldUtil.toCamelCase(rs.getString(2).toLowerCase()));
	columnVo.setFiledComment(StringUtils.isBlank(rs.getString(3)) ? columnVo.getFieldName() : rs.getString(3));
	res.add(columnVo);
	log.debug("columnt.getFieldName() -------------" + columnVo.getFieldName());
	if (!rs.next()) {
		log.debug("读取表成功");
	break;
	}
}


//主要sql语句拼接，后面的方法为将字符串"tableName"变为"'tableName'"返回，否则sql语句错误
private static String getSql(String databaseName, String tableName) {
	return MessageFormat.format("select column_name,data_type,column_comment,numeric_precision,numeric_scale,character_maximum_length,is_nullable nullable from information_schema.columns where table_name = {0} and table_schema = {1} order by ORDINAL_POSITION", FieldUtil.fieldValue(tableName), FieldUtil.fieldValue(databaseName));
}