编写一个程序,用"show -d 库名",显示出中所有表,用"show -t 表名",显示一个表中各个字段的信息,
用"show -i 表名" 显示出一个表中各个字段名和相应的数据.下面程序均以mysql为例:
1:首先编用"show -d 库名",显示出中所有表的程序,如下:
package cn.itcast;
import static java.lang.System.out;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;
public class Client {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
ResultSetMetaData rsmd = null;
String url = "jdbc:mysql://localhost:3306/";
out.println("要查看数据库信息,请使用:show -d databaseName");
Scanner scaner = new Scanner(System.in);
String command = scaner.nextLine();
String prefix = command.substring(0, command.lastIndexOf("-d") + 2)
.replaceAll(" ", "");
String databaseName = command.substring(command.lastIndexOf("-d") +
2)
.trim();
if (!prefix.equals("show-d")) {
out.println("错误的命令!");
return;
}
try {
Class.forName("com.mysql.jdbc.Driver");
try {
con = DriverManager.getConnection(url +databaseName , "root", "");
} catch (Exception e) {
out.println("Exception:" + e.getMessage());
}
if (con != null) {
// 数据库中表的信息
DatabaseMetaData dsmd = con.getMetaData();
rs = dsmd.getTables(null, null, null, null);
out.printf("%-30s%-15s%-15s%-15s/n","表名称","表类别","表类型","表模式");
while (rs.next()) {
out.printf("%-30s",rs.getString("TABLE_NAME"));
out.printf ("%-15s", rs.getString("TABLE_CAT"));
out.printf ("%-15s",rs.getString("TABLE_TYPE"));
out.printf ("%-15s",rs.getString("TABLE_SCHEM"));
out.println();
}
}
}catch(Exception e){
e.printStackTrace();
}
}}
运行时输入show -d mysql,显示库中表的信息如下:
表名称 表类别 表类型 表模式
columns_priv mysql TABLE null
db mysql TABLE null
func mysql TABLE null
help_category mysql TABLE null
help_keyword mysql TABLE null
help_relation mysql TABLE null
help_topic mysql TABLE null
host mysql TABLE null
proc mysql TABLE null
procs_priv mysql TABLE null
tables_priv mysql TABLE null
time_zone mysql TABLE null
time_zone_leap_second mysql TABLE null
time_zone_name mysql TABLE null
time_zone_transition mysql TABLE null
time_zone_transition_type mysql TABLE null
user mysql TABLE null
下面我就不再用show 命令了,直接用sql语句显示表的相关信息了,读者感兴趣可以自己按照上面同样的方法用"show -t 表名",显示一个表中各个字段的信息,用"show -i 表名" 显示出一个表中各个字段名和相应的数据如下
2:显示表的各个字段信息
package cn.itcast;
import static java.lang.System.out;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;
public class Client {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
ResultSetMetaData rsmd = null;
String url = "jdbc:mysql://localhost:3306/";
try {
Class.forName("com.mysql.jdbc.Driver");
try {
con = DriverManager.getConnection(url +"itcast" , "root", "");
} catch (Exception e) {
out.println("Exception:" + e.getMessage());
}
if (con != null) {
Scanner scaner = new Scanner(System.in);
String sql= scaner.nextLine();
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
rsmd = rs.getMetaData();
//列的信息
out.printf("%-20s%-15s%-10s%-15s/n", "Field", "Type", "Null",
"Extra");
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
out.printf("%-20s", rsmd.getColumnName(i));
out.printf("%-15s", rsmd.getColumnTypeName(i) + "("
+ rsmd.getColumnDisplaySize(i) + ")");
out.printf("%-10s", ((rsmd.isNullable(i) == 1) ? "YES"
: "NO"));
out.printf("%-15s/n",
rsmd.isAutoIncrement(i) ? "auto_increment" : "");
}
}
}catch(Exception e){
e.printStackTrace();
}
}
}
输入select * from student显示表的字段信息如下
Field Type Null Extra
id INTEGER(11) NO auto_increment
name VARCHAR(20) YES
sex CHAR(1) YES
salary INTEGER(11) YES
3:显示出一个表中各个字段名和相应的数据
package cn.itcast;
import static java.lang.System.out;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;
public class Client {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
ResultSetMetaData rsmd = null;
String url = "jdbc:mysql://localhost:3306/";
try {
Class.forName("com.mysql.jdbc.Driver");
try {
con = DriverManager.getConnection(url +"itcast" , "root", "");
} catch (Exception e) {
out.println("Exception:" + e.getMessage());
}
if (con != null) {
Scanner scaner = new Scanner(System.in);
String sql= scaner.nextLine();
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
rsmd = rs.getMetaData();
// 表中数据的信息
for (int i = 1; i <= rsmd.getColumnCount(); i++){
out.printf("%-15s", rsmd.getColumnName(i));
}
out.println();
while(rs.next()){
for (int i = 1; i <= rsmd.getColumnCount(); i++){
out.printf("%-15s", rs.getObject(i).toString());
}
out.println();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
输入select * from student;后显示表的字段名和数据信息如下:
id name sex salary
1 mxz m 1000
2 wdl m 2000
6 mxz m 300
4 txd f 900
5 jl f 500
7 xxx f 800
8 zxx f 1850
9 ttt m 7000
10 xxx m 700