查询Oracle 数据库的home

1. Controller 调用的接口

@RestController
@RequestMapping("/admin/dbconnect")
public class PostgressDBConnect {

    @Autowired
    PostgressDBConnectService postgressDBConnectService;

    @GetMapping("/getSchema")
    public void getSchema(){
        int sequence =1;
        //obtain srcval by JDBC
        List<String> srcval =this.postgressDBConnectService.getConnection(sequence);

        //obtain the schemas
        ArrayList<String> schemas =this.postgressDBConnectService.getSchema("localhost","3000","abc","123456","user");

        //obtain the tables
        ArrayList<String> tables =this.postgressDBConnectService.getTables(schemas);
    }
}

1. Service Interface

@Service
public class PostgressDBConnectServiceImpl implements PostgressDBConnectService {

    @Autowired
    ConnectionUtils connectionUtils;

    @Override
    public  List<String> getConnection(int sequence) {
        List<String> list =new ArrayList<>();
        String query =null;
        query ="select src_val from adminx.fealter_table1 where sequence =?";

        try {
            Connection connection = this.connectionUtils.getConnection();
            PreparedStatement pstm = connection.prepareStatement(query);

            pstm.setInt(1,sequence);

            ResultSet rs = pstm.executeQuery();
            while (rs.next()){
                list.add(rs.getString(1));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        return list;
    }

    @Override
    public ArrayList<String> getSchema(String host,String port,String service,String pass,String username) {
        ArrayList<String> schemas =new ArrayList<>();
        String query ="select distinct(owner) from all_objects where object_type in ('TABLE','VIEW') and upper(owner) not like '%SYS%' and upper(owner) not like '%XDB%'";

        try {
            Connection connection = this.connectionUtils.connectToOracle(host, port, service, pass, username);
            PreparedStatement pstm = connection.prepareStatement(query);
            ResultSet rs = pstm.executeQuery();
            while (rs.next()){
                schemas.add(rs.getString(1));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return schemas;
    }

    @Override
    public ArrayList<String> getTables(ArrayList<String> schemas) {
        ArrayList<String> tables =new ArrayList<>();
        String ifview ="";
        String query ="select distinct object_name,object_type from all_objects where  object_type in ('TABLE','VIEW') and owner=?";

        try {
            Connection connection = this.connectionUtils.connectToOracle("host", "port", "service", "pass", "username");
            PreparedStatement pstm = connection.prepareStatement(query);
            pstm.setString(1,schemas.get(1));


            ResultSet rs = pstm.executeQuery();
            while (rs.next()){
                tables.add(rs.getString(1));
                if (rs.getString(2).equalsIgnoreCase("VIEW"))
                    ifview ="Y";
                else
                    ifview="N";
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return tables;
    }
}

1. Service 实现类

@Service
public class PostgressDBConnectServiceImpl implements PostgressDBConnectService {

    @Autowired
    ConnectionUtils connectionUtils;

    @Override
    public  List<String> getConnection(int sequence) {
        List<String> list =new ArrayList<>();
        String query =null;
        query ="select src_val from adminx.fealter_table1 where sequence =?";

        try {
            Connection connection = this.connectionUtils.getConnection();
            PreparedStatement pstm = connection.prepareStatement(query);

            pstm.setInt(1,sequence);

            ResultSet rs = pstm.executeQuery();
            while (rs.next()){
                list.add(rs.getString(1));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        return list;
    }

    @Override
    public ArrayList<String> getSchema(String host,String port,String service,String pass,String username) {
        ArrayList<String> schemas =new ArrayList<>();
        String query ="select distinct(owner) from all_objects where object_type in ('TABLE','VIEW') and upper(owner) not like '%SYS%' and upper(owner) not like '%XDB%'";

        try {
            Connection connection = this.connectionUtils.connectToOracle(host, port, service, pass, username);
            PreparedStatement pstm = connection.prepareStatement(query);
            ResultSet rs = pstm.executeQuery();
            while (rs.next()){
                schemas.add(rs.getString(1));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return schemas;
    }

    @Override
    public ArrayList<String> getTables(ArrayList<String> schemas) {
        ArrayList<String> tables =new ArrayList<>();
        String ifview ="";
        String query ="select distinct object_name,object_type from all_objects where  object_type in ('TABLE','VIEW') and owner=?";

        try {
            Connection connection = this.connectionUtils.connectToOracle("host", "port", "service", "pass", "username");
            PreparedStatement pstm = connection.prepareStatement(query);
            pstm.setString(1,schemas.get(1));


            ResultSet rs = pstm.executeQuery();
            while (rs.next()){
                tables.add(rs.getString(1));
                if (rs.getString(2).equalsIgnoreCase("VIEW"))
                    ifview ="Y";
                else
                    ifview="N";
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return tables;
    }
}

1. connect Util

@Component
@Transactional
public class ConnectionUtils {

    @Autowired
    private DataSource dataSource;

    String connectionUrl=null;
    Connection connection =null;


    public Connection getConnection() throws SQLException {
        return this.dataSource.getConnection();
    }

    public Connection connectToOracle(String host,String port,String service,String pass,String username){
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");

            connectionUrl = "jdbc:oracle:thin:@//" +host +":" +port + "/" + service +"";

            connection = DriverManager.getConnection(connectionUrl,username,pass);

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

}