遍历数据库查询信息

有时候我们得到了系统的一些信息，但是不知道是在那里的，数据库跟踪也难以找到，这时候可以遍历数据库来定位，下面是一个遍历数据库的参考代码

package com.ppjre.tools.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
 * 遍历数据库查找指定的信息 
 * @author pengjiren
 *
 */
public class SearchInfoDBImp implements SearchInfoFromDB {

	private String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
	private String user = "sa";
	private String password = "";
	private String dataname = "DB_"; // default
	private String url = "jdbc:microsoft:sqlserver://192.168.0.101:1433;DatabaseName="+ "";
	private String conn_str = "";
	private List<String> tableList = new ArrayList<String>(); // 数据库表集合

	public SearchInfoDBImp(String DBName) { //指定数据库名称
		this.dataname = DBName;
		this.conn_str = url + this.dataname;
		Connection conn1 = newConnection();
		int DBTableCounts = 0; // 计数
		try {
			DatabaseMetaData dmd = conn1.getMetaData();
			System.out.println("驱动名称:" + dmd.getDriverName());
			System.out.println("驱动版本:" + dmd.getDriverVersion());
			System.out.println("版本:"
					+ dmd.getDatabaseProductVersion().toString());
			System.out.println("名称:" + dmd.getDatabaseProductName().toString());
			System.out.println("用户:" + dmd.getUserName());
			// //
			ResultSet tableRSSET = dmd.getTables(null, "%", null,
					new String[] { "TABLE" });
			while (tableRSSET.next()) {
				String tablesName = tableRSSET.getString("TABLE_NAME");
				if (tablesName.indexOf("Mast", 0) >= 0) { // mast 开头的不需要
					// 去掉不需要的表
				} else if (tablesName.indexOf("Bill", 0) >= 0) {
					// 去掉不需要的表
				} else {
					tableList.add(tablesName);
					System.out.println(tableRSSET.getString("TABLE_NAME"));
					DBTableCounts++;
				}
			}
			tableRSSET.close();
			dmd = null;
			System.out.println("共有：" + DBTableCounts + "个表。");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.Close(conn1);
		}

	}

	/**
	 * --制定查询
	 */
	@Override
	public String goSearch(String info) {
		List<String> sqlList = new ArrayList<String>(); // 查询语句集合
		Connection conn1 = newConnection();
		DatabaseMetaData dmd;
		Statement stmt;
		try {
			// /// 封装SQL语句
			dmd = conn1.getMetaData();
			for (String sTableName : tableList) {
				// ---
				ResultSet columnRSSET = dmd.getColumns(null, null, sTableName,
						null);
				StringBuffer ssql = new StringBuffer("select top 1 2 from "
						+ sTableName + " where 1=2 ");
				while (columnRSSET.next()) {
					String ColName = columnRSSET.getString("COLUMN_NAME")
							.trim();
					if (ColName.toUpperCase().trim().equals("NAME")) {
						continue; // 去掉 NAME字段
					}
					if (ColName.toUpperCase().trim().equals("REM")) {
						continue; // 去掉 REM字段
					}
					int DATA_TYPE_INT = columnRSSET.getInt("DATA_TYPE");// DATA_TYPE:
																		// 1-char，
																		// 4是int，12是varchar，93--datetime
					if (DATA_TYPE_INT == 12)
						ssql.append(" or " + ColName + "='" + info + "'");
				}
				columnRSSET.close();
				sqlList.add(ssql.toString());
				// ---
			} // for end
				// //查询
			stmt = conn1.createStatement();
			int countTwo = 0; // 计数
			for (String ssql : sqlList) {
				countTwo++;
				if (countTwo < 1000) { // 限制表数
					ResultSet rSET = stmt.executeQuery(ssql.toString());
					if (rSET.next()) {
						System.out.println("--->" + ssql); // 打印有结果的语句
						rSET.close();
					}
				}
			} // for end;
			System.out.println("=====完成compedt=====");
			stmt.close();
			// /
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dmd = null;
			stmt = null;
			this.Close(conn1);
		}
		return null;
	}

	/* -------------------------------------------------------------- */

	/**
	 * 创建新连接
	 * 
	 * @return
	 */
	private Connection newConnection() {
		Connection con = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(conn_str, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.out.println("sorry can't find db driver!");
		} catch (SQLException e1) {
			e1.printStackTrace();
			System.out.println("sorry can't create Connection!");
		}
		return con;
	}

	private void Close(Connection conn) {
		try {
			if (conn.isClosed()) {
				return;
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	/* -------------------------------------------------------------- */
}