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

本篇博客拖延了很久，可能细节处有些许遗忘了
项目需求要支持多种数据库的操作：基本的通过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;
    }

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