package com.chuckcaplan.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SQLFind {
	public static void main(String[] args) {

		//CHANGE THESE VARIABLES
		//strDBDataType can be NUMBER, VARCHAR2, etc.
		String strDBDataType = "VARCHAR2"; //change to args[0] if you would like
		//strSearchParam must have an operator and the proper single quotes,
		// like "= 'Test'", "like '%123%'" for VARCHAR2 or "= 123" for NUMBER
		String strSearchParam = "like '%ELM ST.%'"; //change to args[1] if you would like
		//make sure to put the DB driver in your classpath
		String strDBClass = "oracle.jdbc.driver.OracleDriver";
		String strDBURL = "jdbc:oracle:thin:@myserver:1521:mysid";
		String strDBUser = "myuser";
		String strDBPass = "mypass";

		try {
			//variable i will hold the number of results
			int i = 0;

			System.out.println("TABLE / COLUMN / VALUE");
			System.out.println("----------------------");

			//get the connection
			Class.forName(strDBClass);
			Connection con = DriverManager.getConnection(strDBURL, strDBUser,
					strDBPass);

			//get the resultset of each column and table for the selected data
			// type
			Statement stmt = con.createStatement();
			ResultSet rs = stmt
					.executeQuery("Select table_name, column_name from user_tab_columns where data_type = '"
							+ strDBDataType + "'");
			while (rs.next()) {
				//for each column in each table, see if the requested data is
				// there
				String table = rs.getString("table_name");
				String column = rs.getString("column_name");
				Statement s2 = con.createStatement();
				ResultSet rs2 = s2.executeQuery("Select " + column + " from "
						+ table + " where " + column + " " + strSearchParam);
				while (rs2.next()) {
					//if it is there, increment the counter and write it to the
					// screen
					i++;
					System.out.println(table + " / " + column + " / "
							+ rs2.getString(column));
				}
				//close make these vars null to save memory and stop the
				// cursor from getting too big
				rs2.close();
				rs2 = null;
				s2.close();
				s2 = null;
			}
			//close the connection
			con.close();
			System.out.println("\nResults Found: " + i);
		} catch (Exception ex) {
			//if there were any errors...
			System.out.println("Error is:" + ex.getMessage());
		}
	}
}