一、首先创建数据库连接
public void create() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
this.connection = java.sql.DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ry?useUnicode=true&characterEncoding=utf8", "root", "123456");
} catch (Exception e) {
e.printStackTrace();
}
}
在spring boot 中也可以写成配置类的形式:
新建一个类:(我这里用了lombox ,没用的要自己加上get和set方法,lombox的注解@Data @NoArgsConstructor)
@Component
@ConfigurationProperties(prefix="dbconnect")
@PropertySource(value= {"classpath:application.yml"})
@Data
@NoArgsConstructor
public class DbConnect implements Serializable {
private static final long serialVersionUID = 4349552945449301902L;
/**
* 数据库连接地址
*/
private String url;
/**
* 用户名
*/
private String userName;
/**
* 密码
*/
private String password;
/**
* 连接驱动
*/
private String driverClassName;
}
在application中配置:
dbconnect:
# 配置数据库连接信息
driverClassName: com.mysql.jdbc.Driver
userName: root
password: 123456
url: jdbc:mysql://127.0.0.1:3306/ry?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true
然后创建连接:
@Component
public class ConnectUtil {
@Autowired
private DbConnection dbConnection;
public void createConnection() {
try {
Class.forName(dbConnection.getDriverClassName()).newInstance();
this.connection = java.sql.DriverManager.getConnection(customConnection.getUrl(), customConnection.getUserName(), customConnection.getPassword());
} catch (Exception e) {
e.printStackTrace();
}
}
}
创建连接后写调用方法:
public void getTableData() {
//创建数据库连接
// create();
createConnection();
try {
DatabaseMetaData dbMetaData = connection.getMetaData();
ResultSet resultSet = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});
while (resultSet.next()) {// ///TABLE_TYPE/REMARKS
// System.out.println("表名:" + resultSet.getString("TABLE_NAME"));
// System.out.println("表类型:" + resultSet.getString("TABLE_TYPE"));
// System.out.println("表所属数据库:" + resultSet.getString("TABLE_CAT"));
// System.out.println("表备注:" + getRemark(resultSet.getString("TABLE_NAME")));
String tableName = resultSet.getString("TABLE_NAME");
String tableType = resultSet.getString("TABLE_TYPE");
String tableCat = resultSet.getString("TABLE_CAT");
String tableRemark = getRemark(resultSet.getString("TABLE_NAME"));
ResultSet rs = dbMetaData.getColumns(null, "%", resultSet.getString("TABLE_NAME"), "%");
while (rs.next()) {
// System.out.println("字段名:"+rs.getString("COLUMN_NAME")+"\t字段注释:"+rs.getString("REMARKS")+"\t字段数据类型:"+rs.getString("TYPE_NAME"));
String columnName = rs.getString("COLUMN_NAME");
String columnRemark = rs.getString("REMARKS");
String columnType = rs.getString("TYPE_NAME");
}
}
} catch (SQLException e) {
try {
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}
//关闭连接
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 传入表名,返回注释信息
*/
private String getRemark(String tableName) {
String sql = "SHOW CREATE TABLE " + tableName;
PreparedStatement ps = null;
String comment = null;
try {
ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if (rs != null && rs.next()) {
String createDDL = rs.getString(2);
comment = parse(createDDL);
}
} catch (Exception e) {
e.printStackTrace();
}
return comment;
}
/**
* 传入建表语句,返回表注释信息
*/
public static String parse(String all) {
String comment = null;
int index = all.indexOf("COMMENT='");
if (index < 0) {
return "";
}
comment = all.substring(index + 9);
comment = comment.substring(0, comment.length() - 1);
return comment;
}