java 根据表名和关键字全文检索

java 根据表名和关键字全文检索：
MySQL根据表名获取列名的sql为：
select COLUMN_NAME from information_schema.COLUMNS where table_name =’tableName”
Oracle根据表名获取列名的sql为：
select COLUMN_NAME from all_tab_columns where table_name =’tableName’

---

一、数据库连接工具类

public class JDBCUtil{
 /**
     * 获取数据库连接
     * @return 数据库连接
     */
  public static getConn(){
    Connection conn = null;
    Class.forName("com.mysql.jdbc.Driver");
    try{
     conn=DriverManager.getConnection("jdbc:mysql://ip:port/yourDatabaseName","userName","password")

    }catch(Exception e){
         e.printStackTrace();
      }
 retrun conn;
  }
  /**
     * 根据表名获取表的记录数目
     * @param conn 数据源链接
     * @param tableName 表名
     * @return 数目
     */
    public static int getCount(Connection conn, String tableName) {
        int total = 0;
        String sql = "select count(1) from " + tableName;
        PreparedStatement statement;
        try {
            statement = conn.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }
}

二、根据表名和关键字全文检索

public JSONArray query(String tableName, String keyWord) {
                Connection conn = JDBCUtil.getConn();
        ResultSet result = null;
        PreparedStatement pre = null;
        try {
            //根据数据库类型查询表的所有列名
             String dataType = conn .getMetaData().getDatabaseProductName();
             String columnSql = "";
             switch (dataType) {
            case "MySQL":
                columnSql = "select COLUMN_NAME from information_schema.COLUMNS where table_name ='"+tableName+"'";
                break;

            case "Oracle":
                columnSql = "select COLUMN_NAME from all_tab_columns where table_name ='"+tableName+"'";
            default:
                break;
            }
             pre = conn .prepareStatement(columnSql);
             result = pre.executeQuery();
             //拼接sql
             StringBuilder sql = new StringBuilder("select * from "+tableName+" t where ");
             while (result.next()) {
                int i=1;
                sql.append(sql.append("t."+result.getString(i))+" like '%"+keyWord+"%' or ");
            }
            sql = sql.delete(sql.length()-3, sql.length());//去掉sql末尾的or
            pre = con.prepareStatement(sql.toString());
            result = pre.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultSetToJsonArry(pre, result);
    }

/**
     * 将ResultSet结果集转成JSONArray
     * 
     * @param pre
     * @param rs
     * @return
     */
  public JSONArray resultSetToJsonArry(PreparedStatement pre, ResultSet result) {

        if (result == null)
            return null;

        JSONArray array = new JSONArray();
        try {
            ResultSetMetaData rm= result.getMetaData();// 得到结果集(rs)的结构信息，比如字段数、字段名等

            int columnCount = rm.getColumnCount(); // 返回此 ResultSet 对象中的列数

            JSONObject json = null;
            while (result.next()) {
                json = new JSONObject();
                for (int i = 1; i <= columnCount; i++) {
                    if(!"".equals(result.getObject(i))){
                        if(result.getObject(i) instanceof Date){
                            json.put(rm.getColumnName(i), sdf.format(result.getObject(i)));
                        }else{
                            json.put(rm.getColumnName(i), result.getObject(i));
                        }
                    }else{
                        json.put(rm.getColumnName(i), "");
                    }


                }
                array.put(json);
            }
        } catch (SQLException | JSONException e) {
            e.printStackTrace();
        } finally {
            close(result);
            close(pre);
            close(con);
        }
        return array;
    }