本篇博客拖延了很久，可能细节处有些许遗忘了

项目需求要支持多种数据库的操作：基本的通过sql进行增删改查、查某个库的所有表、查数据表的字段、表数据及对应类型长度，👇都有实现，涉及到mysql、oracle、postgresql、gbase、mongo五种数据库

一、先上代码

1.pom引入配置

–各位如果需要驱动安装包可以私我，应该gbase的驱动包是最难找的了

      <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.5.0</version>
      </dependency>
      <dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc8</artifactId>
        <version>21.7.0.0</version>
      </dependency>
      <dependency>
        <groupId>org.mongodb</groupId>
        <artifactId>mongo-java-driver</artifactId>
        <version>3.0.4</version>
      </dependency>
      <dependency>
        <groupId>gbase-connector-java</groupId>
        <artifactId>gbase-connector-java</artifactId>
        <version>8.3.81.53</version>
      </dependency>

2.demo代码

mysql

main方法

public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        String mysqlQueryTables = "select table_name from information_schema.tables where table_schema='test'";
        String insertToMysql = "insert into `yzh_0922_002` (yzh_0922_002_no,id,sheet_name) values( 4,null,null)";
        String mysqlTableInfo = "select * from information_schema.COLUMNS where table_name = 'aa_wy_test_a' and table_schema = 'test'";
        String mysqlTableRows = "select count(0) from " + "table_name";
        int page = 2;
        int size = 10;
        String q = page == 0 && size == 0 ? "" : " limit " + (page - 1) * size + "," + size;
        String mysqlQuerySql = "select `amount`,`id` from `" + "aa_wy_test_a" + "`" + q;
        try {
//            mysql数据库
            connection = mysqlConnection();
            insertToMysql(connection, insertToMysql);
            getDbTables(connection, mysqlQueryTables);
            getTableColumn(connection, "aa_wy_test_a");
            getTableRowsCount(connection, mysqlTableRows);
            getTableData(connection, mysqlQuerySql);
            //insert
//            String insertSql ="insert into accounts (id,name,age,salary) values( 15,\'0xx\',11,12)";
//            statement = connection.createStatement();
//            statement.executeQuery(insertSql);
            //delete
//            String delSql ="drop table if exists aaaa_missing";
//            statement.executeUpdate(delSql);
            //select
//                String sql1 ="select * from aa_wy_test_a";
//            statement = connection.createStatement();
//            ResultSet resultSet = statement.executeQuery(sql1);
//            while(resultSet.next()) {
//                System.out.println("------------" + resultSet.getString("TABLE_NAME"));
//            }
//            convertList(resultSet);
//            resultSet.close();
//
//            statement.close();
//            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

获取表中数据

    private static List<Map<String, ?>> convertList(ResultSet rs) throws SQLException {
        List<Map<String, ?>>  list = new ArrayList<>();
        while (rs.next()) {
            ResultSetMetaData md = rs.getMetaData();//获取键名
            int columnCount = md.getColumnCount();//获取列的数量
            Map<String, Object> orderedRecords = new LinkedHashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                System.out.println(md.getColumnName(i) + "---" + rs.getObject(i) + ", -------md:" + md.getColumnTypeName(i));
                orderedRecords.put(md.getColumnName(i), rs instanceof BigInteger ? ((BigInteger) rs).longValue() : rs);
            }
            list.add(orderedRecords);
        }
        return list;
    }

其余方法

//获取连接
private static Connection mysqlConnection() throws Exception {
        String mysqlUser = "";
        String mysqlPassword = "";
        String mysqlUrl = "jdbc:mysql://ip:3306/test";
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        Class.forName(driverClassName);
        return DriverManager.getConnection(mysqlUrl, mysqlUser, mysqlPassword);
    }
//insert
private static void insertToMysql(Connection connection, String sql) throws SQLException {
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        statement.close();
        connection.close();
    }
//获取库中的表
private static List<String> getDbTables(Connection connection, String sql) throws SQLException {
        List<String> list = new ArrayList<String>();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()) {
            list.add(resultSet.getString("TABLE_NAME"));
            System.out.println("------------" + resultSet.getString("TABLE_NAME"));
        }
        resultSet.close();
        statement.close();
        connection.close();
        return list;
    }
//获取表字段、类型和长度
private static List<String> getTableColumn(Connection connection, String tableName) throws SQLException {
        List<String> list = new ArrayList<String>();
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        ResultSet resultSet = databaseMetaData.getColumns( null, "%", tableName, "%");
        while(resultSet.next()) {
            String columnName = resultSet.getString("COLUMN_NAME");
            String columnType = resultSet.getString("TYPE_NAME");
            int datasize = resultSet.getInt("COLUMN_SIZE");
            System.out.println(columnName+" "+columnType+" "+datasize);
        }
        resultSet.close();
        connection.close();
        return list;
    }
//获取表行数
private static long getTableRowsCount(Connection connection, String sql) throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        long rowCount = 0;
        if (resultSet.next()) {
            rowCount = resultSet.getInt(1);
        }
        System.out.println(rowCount );
        resultSet.close();
        statement.close();
        connection.close();
        return rowCount;
    }
//获取表数据
private static List<Map<String, Object>> getTableData(Connection connection, String sql) throws SQLException {
        System.out.println(sql);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        List<Map<String, Object>>  recordsList = new ArrayList<>();
        ResultSetMetaData md = resultSet.getMetaData();//获取键名
        int columnCount = md.getColumnCount();//获取列的数量
        while (resultSet.next()) {
            Map<String, Object> orderedRecords = new LinkedHashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                try {
                    orderedRecords.put(md.getColumnName(i), md.getColumnType(i) == Types.INTEGER ? resultSet.getInt(i) : resultSet.getString(i));
                } catch (Exception e) {
                    orderedRecords.put(md.getColumnName(i), resultSet.getString(i));
                }
            }
            recordsList.add(orderedRecords);
        }
        resultSet.close();
        statement.close();
        connection.close();
        return recordsList;
    }

oracle

main方法

public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        String oracleSqlQueryTables = "select table_name from user_tables";
        String oracleTableRows = "select count(0) from " + "PSI1";
        int page = 2;
        int size = 10;
        String oracleQ = page == 0 && size == 0 ? "" : " where ROWNUM BETWEEN " + (page - 1) * size  + " AND " + page * size;
        String ol = "select ID from (select t.*,rownum r from PSI1 t ) where r between " + ((page - 1) * size + 1) + " and " + page * size;
        String oracleQuerySql = page == 0 && size == 0 ? "select * from PSI1" : ol;
        try {
            oracle数据库
            connection = oracleConnection();
            getDbTables(connection, oracleSqlQueryTables);
            getTableColumn(connection, "TEST");
            getTableRowsCount(connection, oracleTableRows);
            getTableData(connection, oracleQuerySql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

其余方法

//获取连接
    private static Connection oracleConnection() throws Exception {
        String oracleUser = "";
        String oraclePassword = "";
        String oracleUrl = "jdbc:oracle:thin:@ip:port:xe";
        String driverClassName = "oracle.jdbc.driver.OracleDriver";
        Class.forName(driverClassName);
        return DriverManager.getConnection(oracleUrl, oracleUser, oraclePassword);
    }
//下面四个方法和上面mysql调用的是同一个方法，就不赘述了
getDbTables(connection, oracleSqlQueryTables);
getTableColumn(connection, "TEST");
getTableRowsCount(connection, oracleTableRows);
getTableData(connection, oracleQuerySql);

postgresql

main方法

public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        int page = 2;
        int size = 10;
        String l = page == 0 && size == 0 ? "" : " limit " + size + " offset " + (page - 1) * size;
        String postQuerySql = "select id,age from accounts" + l;
        String postTableRows = "select count(0) from " + "accounts";
        try {
//            postGre数据库
            connection = postConnection();
            getPostTables(connection);
            getTableColumn(connection, "basic_info");
            getTableRowsCount(connection, postTableRows);
            getTableData(connection, postQuerySql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

其余方法

//获取连接
    private static Connection postConnection() throws Exception {
        String postGreUser = "";
        String postGrePassword = "";
        String postGreUrl = "jdbc:postgresql://ip:port/test";
        String driverClassName = "org.postgresql.Driver";
        Class.forName(driverClassName);
        return DriverManager.getConnection(postGreUrl, postGreUser, postGrePassword);
    }
//获取数据库中所有表名
    private static List<String> getPostTables(Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        List<String> list = new ArrayList<String>();
        ResultSet resultSet = metaData.getTables(null, null, "%", null);
        while (resultSet.next()) {
            String tableName = resultSet.getString("TABLE_NAME");
            String tableType = resultSet.getString("TABLE_TYPE");
            if (judgeContainsStr(tableName, false) && null != tableType && tableType.equals("TABLE")) {
                System.out.println("postGre-tableName:" + tableName + ", tableType:" + tableType);
                list.add(tableName);
            }
        }
        return list;
    }
//下面三个方法和上面mysql调用的是同一个方法，就不赘述了
            getTableColumn(connection, "basic_info");
            getTableRowsCount(connection, postTableRows);
            getTableData(connection, postQuerySql);

gbase

main方法

public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        String gbaseQueryTables = "select table_name from information_schema.tables where table_schema='test'";
        String gbaseTableRows = "select count(0) from " + "mytable";
        String gbaseQuerySql = "select * from " + "mytable" + q;
        String gbaseQuerySql1 ="select `col2`,`col1` from `" + "mytable" + "`" + q;
        try {
//            gbase数据库
            connection = gbaseConnection();
            getDbTables(connection, gbaseQueryTables);
            getTableColumn(connection, "mytable");
            getTableRowsCount(connection, gbaseTableRows);
            getTableData(connection, gbaseQuerySql1);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

其余方法

    private static Connection gbaseConnection() throws Exception {
        String gbaseUser = "";
        String gbasePassword = "";
        String gbaseUrl = "jdbc:gbase://ip:port/test";
        String driverClassName = "com.gbase.jdbc.Driver";
        Class.forName(driverClassName);
        return DriverManager.getConnection(gbaseUrl, gbaseUser, gbasePassword);
    }
//下面四个方法和上面mysql调用的是同一个方法，就不赘述了
            getDbTables(connection, gbaseQueryTables);
            getTableColumn(connection, "mytable");
            getTableRowsCount(connection, gbaseTableRows);
            getTableData(connection, gbaseQuerySql1);

mongo

main方法

public static void main(String[] args) {
        try {
//            mongo数据库
            MongoClient mongoClient = mongo();
            getMongoTables(mongoClient, "dmtestdb");
            getMongoTableColumn(mongoClient, "dmtestdb", "dmtestcoll");
            getMongoTableColumn1(mongoClient, "dmtestdb", "dmtestcoll");
            getMongoTableRowsCount(mongoClient, "dmtestdb", "dmtestdb");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

其他方法

    private static MongoClient mongo() {
        try {
            ServerAddress serverAddress = new ServerAddress(ip,port);
            List<ServerAddress> addrs = new ArrayList<ServerAddress>();
            addrs.add(serverAddress);
            //MongoCredential.createScramSha1Credential()三个参数分别为 用户名 数据库名称 密码
            MongoCredential credential = MongoCredential.createScramSha1Credential("dmtest", "dmtestdb", "dmlab".toCharArray());
            List<MongoCredential> credentials = new ArrayList<MongoCredential>();
            credentials.add(credential);
            System.out.println("Connect to database successfully");
            //通过连接认证获取MongoDB连接
            return new MongoClient(addrs,credentials);
        } catch (Exception e) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            return null;
        }
    }
    public static List<String> getMongoTables(MongoClient mongoClient, String databaseName) {
        List<String> tableCounts = new ArrayList<>();
        MongoDatabase mongoDatabase = mongoClient.getDatabase(databaseName);
        for (String collectionName : mongoDatabase.listCollectionNames()) {
            tableCounts.add(collectionName);
        }
        mongoClient.close();
        return tableCounts;
    }
    private static List<String> getMongoTableColumn(MongoClient mongoClient, String databaseName, String tableName) throws SQLException {
        MongoDatabase mongoDatabase = mongoClient.getDatabase(databaseName);
        MongoCollection<Document> mongoCollection = mongoDatabase.getCollection(tableName);
//查询集合全部数据        FindIterable<Document> cursor = mongoCollection.find();
//分页查询        FindIterable<Document> cursor = mongoCollection.find().skip(1).limit(2);
//分页查询        mongoCollection.find().skip("跳过").limit("查询几条")
//        mongoCollection.find(eq("status", "A")).skip("跳过").limit("查询几条")
//查询具体的字段                .projection(fields(include("item", "status"), excludeId()));
        FindIterable<Document> cursor = mongoCollection.find().skip(1).limit(2).projection(fields(include("name", "age", "_id"), excludeId()));
        HashMap<String, String> keyAndType = new HashMap<>();
        for (Map.Entry<String, Object> entry : cursor.first().entrySet()) {
            if (entry.getValue() instanceof Double) {
                keyAndType.put(entry.getKey(), "Double");
            } else if (entry.getValue() instanceof Integer) {
                keyAndType.put(entry.getKey(), "Integer");
            } else {
                keyAndType.put(entry.getKey(), "String");
            }
        }
        MongoCursor<Document> mongoCursor = cursor.iterator();
        List<CollectionRecords.CollectionRecord> records = Lists.newArrayList();
        while (mongoCursor.hasNext()) {
            Document loopDocument = mongoCursor.next();
            CollectionRecords.CollectionRecord collectionRecord = new CollectionRecords.CollectionRecord();
            for (Map.Entry<String, Object> entry : loopDocument.entrySet()) {
                CollectionRecords.CollectionField collectionField = new CollectionRecords.CollectionField();
                collectionField.setKey(entry.getKey());
                collectionField.setValue(entry.getValue().toString());
                collectionField.setType(keyAndType.get(entry.getKey()));
                collectionRecord.add(collectionField);
                records.add(collectionRecord);
            }
        }
//            [{"record":[{"key":"name","value":"\"bob\"","type":"[]uint8"},{"key":"age","value":"42","type":"[]uint8"},{"key":"status","value":"\"A\"","type":"[]uint8"},{"key":"ObjectId","value":"62f328222522c7b329120af3","type":"bson.ObjectId"}]},{"record":[{"key":"status","value":"\"A\"","type":"[]uint8"},{"key":"ObjectId","value":"62f328332522c7b329120afb","type":"bson.ObjectId"},{"key":"name","value":"\"ahn\"","type":"[]uint8"},{"key":"age","value":"22","type":"[]uint8"}]},{"record":[{"key":"age","value":"34","type":"[]uint8"},{"key":"status","value":"\"D\"","type":"[]uint8"},{"key":"ObjectId","value":"62f3283d2522c7b329120b14","type":"bson.ObjectId"},{"key":"name","value":"\"xi\"","type":"[]uint8"}]}]
        System.out.println("records: " + new Gson().toJson(records));
        mongoCursor.close();
        mongoClient.close();
        return null;
    }
    private static List<String> getMongoTableColumn1(MongoClient mongoClient, String databaseName, String tableName) throws SQLException {
        MongoDatabase mongoDatabase = mongoClient.getDatabase(databaseName);
        ListCollectionsIterable<Document> collectionsIterable = mongoDatabase.listCollections();
        Document firstDocument = collectionsIterable.first();
        if (firstDocument != null) {
            System.out.println("first collection >>>" + firstDocument.toJson());
        }
        /** collectionsIterable.iterator() 获取 MongoCursor<Document> 游标
         * 不用担心空指针异常*/
        MongoCursor<Document> mongoCursor = collectionsIterable.iterator();
        while (mongoCursor.hasNext()) {
            Document loopDocument = mongoCursor.next();
//            collectionList.add(loopDocument);
        }
        /**关闭游标*/
        mongoClient.close();
        return null;
    }
    private static long getMongoTableRowsCount(MongoClient mongoClient, String databaseName, String tableName) throws SQLException {
        long rowCount = 0;
        MongoDatabase mongoDatabase = mongoClient.getDatabase(databaseName);
        MongoCollection<Document> mongoCollection = mongoDatabase.getCollection(tableName);
        rowCount = mongoCollection.count();
        mongoClient.close();
        System.out.println("getMongoTableRowsCount-rowCount:" + rowCount);
        return rowCount;
    }

代码较多，但应该是比较细节了，这里就不做过多的讲解了

Java连接各种数据库操作（mysql、oracle、postgresql、gbase、mongo）

一、先上代码

1.pom引入配置

2.demo代码

mysql

postgresql

gbase

mongo

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Java连接各种数据库操作（mysql、oracle、postgresql、gbase、mongo）

一、先上代码

1.pom引入配置

2.demo代码

mysql

postgresql

gbase

mongo

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像