一个可同时登陆,操作不同数据库的小工具类。可方便地进行简单的数据库操作。
代码如下:
package cn.kai.util;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
*SQL实用工具类
*/
public class SQLTool {
public static void main(String[] args) {
Connection conn = getConnection();
proccess(conn);
}
/** 根据用户输入得到连接 */
public static Connection getConnection() {
BufferedReader in = new BufferedReader(
new InputStreamReader(
System.in));
Connection conn = null;
System.out.println("请输入要进入的数据库名称,MySQL,Oracle,DB2,SQL Server或Sybase");
try {
String databaseName = in.readLine();
while(databaseName == null || databaseName == "" || databaseName.length() == 0) {
System.out.println("请输入要进入的数据库名称,MySQL或Oracle");
databaseName = in.readLine();
}
if(databaseName.matches("^((exit)|(bye))$")) {
System.out.println("bye,client");
System.exit(1);
}
//加载驱动
if(databaseName.toLowerCase().equals("mysql")) {
Class.forName("com.mysql.jdbc.Driver");
}else if(databaseName.toLowerCase().equals("oracle")) {
Class.forName("oracle.jdbc.driver.OracleDriver");
}else if(databaseName.toLowerCase().equals("DB2")) {
Class.forName("com.ibm.db2.jdbc.app.DB2Driver");
}else if(databaseName.toLowerCase().equals("SQL Server")) {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
}else if(databaseName.toLowerCase().equals("Sybase")) {
Class.forName("com.sybase.jdbc3.jdbc.SybDriver");
}
System.out.println("请输入登录的url:");
String url = in.readLine();
System.out.println("请输入登录用户名:");
String userName = in.readLine();
System.out.println("请输入登录密码:");
String password = in.readLine();
conn = DriverManager.getConnection(url,userName,password);
} catch (IOException e) {
System.out.println("IO异常");
System.exit(1);
} catch (ClassNotFoundException e) {
System.out.println("加载驱动失败!请检查驱动包是否放在正确的地方");
System.exit(1);
} catch (SQLException e) {
System.out.println("登录失败!请检查登录信息是否正确");
System.exit(1);
}
return conn;
}
public static void proccess(Connection conn) {
try {
conn.setAutoCommit(false);
} catch (SQLException e1) {
e1.printStackTrace();
}
BufferedReader in = new BufferedReader(
new InputStreamReader(
System.in));
int line = 1;
String lineStr = "";
StringBuffer sb = new StringBuffer();
while(true) {
//显示行号
System.out.print((line == 1 ? "" : line) + "sql-->");
try {
lineStr = in.readLine();
if(lineStr.endsWith(";")) {
sb.append(lineStr.substring(0, lineStr.length() - 1));
if(sb.toString().matches("^((exit)|(bye)|(quit))$")) {
//退出程序
proccessQuit();
}else if(sb.toString().matches("^((commit)|(rollback))$")) {
//处理事务
proccessTransaction(conn,sb.toString());
}else {
//执行SQL语句
proccessSQL(conn,sb.toString());
}
line = 1;
sb = new StringBuffer();
}else {
line++;
sb.append(lineStr + " ");
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
*执行SQL
*/
private static void proccessSQL(Connection conn,String sql) {
try {
Statement stmt = conn.createStatement();
ResultSet rs = null;
boolean flag = stmt.execute(sql);
if(flag) { //查询操作
rs = stmt.getResultSet(); //获得结果集
System.out.println(JDBCUtil.getMetaData(rs).toString()); //打印结果。该方法代码见结尾。
}else { //是更新操作
int c = stmt.getUpdateCount();
System.out.println("更新成功---" + c);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
// e.printStackTrace();
}
}
/**
*处理事务,本程序只支持commit和rollback
*/
private static void proccessTransaction(Connection conn,String command) {
try {
if("commit".equals(command)) {
conn.commit();
}else if("rollback".equals(command)) {
conn.rollback();
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
public static void proccessQuit() {
System.out.println("bye bye");
System.exit(1);
}
}
程序中用到了我的JDBC工具类中的一个格式化打印ResultSet中结果集的方法,具体代码如下:
public static StringBuffer getMetaData(ResultSet rs){
StringBuffer sb=new StringBuffer();
if(rs==null)
return sb;
try{
ResultSetMetaData meta=rs.getMetaData();
int col=meta.getColumnCount();//字段个数
int sumMaxLength=0;//所有字段的总长
for(int i=1;i<=col;i++){//字段名的显示
sb.append(meta.getColumnName(i));
for(int j=meta.getColumnName(i).length();j<meta.getColumnDisplaySize(i);j++)
sb.append(" ");
sumMaxLength+=meta.getColumnDisplaySize(i);
}
sb.append("/n");
for(int i=1;i<=sumMaxLength;i++)//分隔线的显示
sb.append("-");
sb.append("/n");
String s="";
while(rs.next()){ //字段值的显示
for(int i=1;i<=col;i++){
s=rs.getString(i);
sb.append(s);
int j=0;
for(j=s.length();j<meta.getColumnDisplaySize(i);j++)
sb.append(" "); //长度不足的用空格补上
}
sb.append("/n");
}
}catch(Exception e){
e.printStackTrace();
}finally{
return sb;
}
}