- Controller 调用的接口
@RestController
@RequestMapping("/admin/dbconnect")
public class PostgressDBConnect {
@Autowired
PostgressDBConnectService postgressDBConnectService;
@GetMapping("/getSchema")
public void getSchema(){
int sequence =1;
List<String> srcval =this.postgressDBConnectService.getConnection(sequence);
ArrayList<String> schemas =this.postgressDBConnectService.getSchema("localhost","3000","abc","123456","user");
ArrayList<String> tables =this.postgressDBConnectService.getTables(schemas);
}
}
- 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;
}
}
- 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;
}
}
- 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;
}
}