トッカンソフトウェア

JavaからJDBCでPostgreSQLのメタデータを取得

テーブル一覧とビュー一覧

				
select * from pg_tables
select * from pg_views

			

テーブル項目と主キー

				
package posgre;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {

	public static void main(String[] args) throws Exception {
		Connection conn = null;

		try {

			// クラスのロード
			Class.forName("org.postgresql.Driver");

			// コネクションの取得
			conn = DriverManager.getConnection(
				"jdbc:postgresql://localhost:5432/postgres", "postgres", "ps");

			columns(conn);
			keys(conn);

		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	private static void columns(Connection conn) throws SQLException {

		DatabaseMetaData dmd = conn.getMetaData();

		String tableName = "test";
		String schema = "public";

		try (ResultSet rs = dmd.getColumns(null, schema, tableName, null)) {

			while (rs.next()) {

				System.out.println(rs.getString("COLUMN_NAME"));
				System.out.println(rs.getString("TYPE_NAME"));
				System.out.println(rs.getString("COLUMN_SIZE"));
			}
		}
	}

	private static void keys(Connection conn) throws SQLException {

		DatabaseMetaData dmd = conn.getMetaData();

		String tableName = "test";
		String schema = "public";

		try (ResultSet rs = dmd.getPrimaryKeys(null, schema, tableName)) {

			while (rs.next()) {

				System.out.println(rs.getString("COLUMN_NAME"));
				System.out.println(rs.getString("PK_NAME"));
			}
		}
	}
}


			

メタデータからDelete文とINSERT文を作成するサンプル

				
package posgre;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class CreateTestData {
	private static final String SCHEMA = "public";
	private static final String OWNER = "postgres";

	public static void main(String[] args) throws Exception {
		Connection conn = null;

		try {

			// クラスのロード
			Class.forName("org.postgresql.Driver");

			// コネクションの取得
			conn = DriverManager.getConnection(
				"jdbc:postgresql://localhost:5432/postgres", "postgres", "ps");

			CreateTestData createTestData = new CreateTestData();
			createTestData.execute(conn);

		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	private void execute(Connection conn) throws SQLException {
		List<String> tableNames = getAllTableNames(conn);

		for (String tableName : tableNames) {
			TableData tableData = new TableData(conn, tableName);

			CreateSQL createSQL = new CreateSQL(tableData);

			if (createSQL.isPKeyExists()) {
				output(createSQL.delete());
				output(createSQL.insert());
			} else {
				output("主キーが未設定:" + tableName);
			}
		}

	}

	private void output(String str) {
		System.out.println(str);
	}

	private List<String> getAllTableNames(Connection conn) throws SQLException {

		ArrayList<String> ret = new ArrayList<>();

		Statement stmt = null;
		ResultSet rs = null;

		try {

			stmt = conn.createStatement();

			String sql = "select tablename from pg_tables where schemaname = '";
			sql += SCHEMA;
			sql += "' and tableowner = '";
			sql += OWNER;
			sql += "'";

			rs = stmt.executeQuery(sql);

			while (rs.next()) {

				ret.add(rs.getString("tablename"));

			}

		} finally {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		}

		return ret;
	}

	class TableData {

		String tableName;
		List<String> names = new ArrayList<>();
		List<String> types = new ArrayList<>();
		List<Integer> sizes = new ArrayList<>();
		List<String> keys = new ArrayList<>();

		public TableData(Connection conn, String tableName) throws SQLException {
			this.tableName = tableName;

			DatabaseMetaData dmd = conn.getMetaData();

			extractKey(dmd);
			extractCol(dmd);
		}

		private void extractKey(DatabaseMetaData dmd) throws SQLException {

			try (ResultSet rs = dmd.getPrimaryKeys(null, SCHEMA, tableName)) {

				while (rs.next()) {

					keys.add(rs.getString("COLUMN_NAME"));
				}
			}

		}

		private void extractCol(DatabaseMetaData dmd) throws SQLException {

			try (ResultSet rs = dmd.getColumns(null, SCHEMA, tableName, null)) {

				while (rs.next()) {

					names.add(rs.getString("COLUMN_NAME"));
					types.add(rs.getString("TYPE_NAME"));
					sizes.add(rs.getInt("COLUMN_SIZE"));
				}

			}
		}

		public String getTableName() {
			return tableName;
		}

		public List<String> getNames() {
			return names;
		}

		public List<String> getTypes() {
			return types;
		}

		public List<String> getKeys() {
			return keys;
		}

		public List<Integer> getSizes() {
			return sizes;
		}

	}

	class CreateSQL {
		String tableName;
		List<String> names;
		List<String> keys;
		Map<String, Column> mapCol = new HashMap<>();

		public CreateSQL(TableData tableData) {

			tableName = tableData.getTableName();
			names = tableData.getNames();
			keys = tableData.getKeys();
			List<String> types = tableData.getTypes();
			List<Integer> sizes = tableData.getSizes();

			for (int i = 0; i < names.size(); i++) {
				String name = names.get(i);
				String type = types.get(i);
				int size = sizes.get(i);
				mapCol.put(name, new Column(name, type, size));
			}
		}

		public boolean isPKeyExists() {

			return keys.size() > 0;
		}

		public String delete() {

			String where = keys.stream().map(key -> key + "=" + mapCol.get(key).val())
					.collect(Collectors.joining(" AND "));

			String sql = "DELETE FROM " + tableName;
			sql += " WHERE ";

			sql += where;
			return sql;
		}

		public String insert() {
			String cols = names.stream().collect(Collectors.joining(","));
			String vals = names.stream().map(name -> mapCol.get(name).val()).collect(Collectors.joining(","));

			String sql = "INSERT INTO " + tableName + "(";
			sql += cols;
			sql += ") VALUES(";
			sql += vals;
			sql += ")";

			return sql;
		}

	}

	class Column {

		String value;

		public Column(String name, String type, int size) {

			Map<String, String> map = changeMap();
			if (map.containsKey(name)) {
				value = map.get(name);
				return;
			}

			List<String> typeStrs = Arrays.asList("varchar", "bpchar", "text");
			List<String> typeNums = Arrays.asList("int", "long", "float", "numeric", "numeric");

			if (typeStrs.contains(type)) {
				if (name.length() > size) {
					value = "'" + name.substring(0, size) + "'";

				} else {
					value = "'" + name + "'";
				}
				return;
			}

			if (typeNums.stream().filter(typeNum -> type.startsWith(typeNum)).count() > 0) {

				value = "" + name.length();

				return;
			}

			if (type.startsWith("date")) {
				value = "TO_DATE('2023/01/01', 'YYYY/MM/DD')";

				return;
			}

			if (type.startsWith("timestamp")) {
				value = "TO_TIMESTAMP('2023/01/01 11:22:33', 'YYYY/MM/DD HH24:MI:SS')";

				return;
			}

			value = name;
		}

		public String val() {

			return value;
		}

		private Map<String, String> changeMap() {
			Map<String, String> ret = new HashMap<>();

			return ret;
		}

	}
}