java mysql控制台_在Java控制台模拟dos命令下操作MySQL

一、导入mysql的驱动：“com.mysql.jdbc.Driver”, 必须将mysql的驱动 包(jar文件)导入到本工程中。

Class.forName("com.mysql.jdbc.Driver");

二、由DriverManager驱动的管理类对象去获得Connection，在此之前需要先导入mysql的驱动包(或者驱动类)。

Connection conn = DriverManager.getConnection(url, userName, userPassword);

三、由Connection对象，获得执行增删改的sql语句的状态对象Statement

Statement stmt = conn.createStatement();

四、执行增删改查。

主要代码：

1、获得连接对象

/**

*

* @return：返回获得的连接对象

* @throws SQLException

* @throws ClassNotFoundException

*/

public static Connection getConn(String database, String user,

String password) throws ClassNotFoundException

{

Class.forName("com.mysql.jdbc.Driver");

Connection conn = null;

try

{

conn = DriverManager.getConnection(url + database, user, password);

System.out.println("Database changed");

} catch (SQLException e)

{

System.out.println(e.getMessage());

}

return conn;

}

2、执行查询语句返回查询结果

public static ArrayList queryDB(String sql)

throws ClassNotFoundException

{

ArrayList list = null;

try

{

list = new ArrayList();

ResultSet result = statement.executeQuery(sql);

ResultSetMetaData rsd = result.getMetaData();

num = rsd.getColumnCount();

String[] columnName = new String[num];

for (int i = 1; i <= num; i++)

{

columnName[i - 1] = rsd.getColumnName(i);

queryLen[i - 1] = Math.max(queryLen[i - 1],

columnName[i - 1].length());

}

list.add(columnName);

while (result.next())

{

int index = 0;

String[] string = new String[num];

for (int i = 1; i <= num; i++)

{

string[i - 1] = String.valueOf(result.getObject(i));

queryLen[index] = Math.max(queryLen[index],

string[i - 1].length());

index++;

}

list.add(string);

}

} catch (SQLException e)

{

System.out.println(e.getMessage());

}

return list;

}

3、格式化输出

public static void main(String[] args) throws SQLException,

ClassNotFoundException, IOException

{

BufferedReader br = new BufferedReader(new InputStreamReader(System.in));

Connection conn = null;

while (true)

{

System.out.print(">");

String command = br.readLine();

if (command.equals("mysql -u root -p"))

{

System.out.print("Enter password:");

String password = br.readLine();

System.out.print("mysql>");

String database = br.readLine();

if (database.split(" ")[0].equals("use"))

{

conn = getConn(database.split(" ")[1], "root", password);

if (conn == null)

continue;

}

break;

}

}

statement = conn.createStatement();

while (true)

{

System.out.print("\nmysql>");

String sql = br.readLine();

sql = sql.replace(";", "");

if (sql.trim().equals("quit"))

{

break;

} else if (sql.trim().startsWith("select"))

{

ArrayList list = queryDB(sql);

for (int i = 0; i < list.size(); i++)

{

if (0 == i || 1 == i)

{

System.out.print("|");

for (int j = 0; j < list.get(i).length; j++)

{

for (int a = 0; a < queryLen[j]; a++)

{

System.out.print("-");

}

System.out.print("|");

}

System.out.println();

}

for (int j = 0; j < list.get(i).length; j++)

{

System.out.print("|" + list.get(i)[j]);

for (int k = list.get(i)[j].length(); k < queryLen[j]; k++)

{

System.out.print(" ");

}

}

System.out.println("|");

if (i == list.size() - 1)

{

System.out.print("|");

for (int j = 0; j < list.get(i).length; j++)

{

for (int a = 0; a < queryLen[j]; a++)

{

System.out.print("-");

}

System.out.print("|");

}

System.out.println();

}

}

} else

{

updateDB(sql);

}

}

}

运行测试：