Tools_java/tctable/TCTableTools.java

package tctable;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;

//В андроид не используется данный класс
public class TCTableTools {

    //Записать таблицу в OutputStream
    public static boolean getTCTableFromResultSet(String name, int id, ResultSet rs, OutputStream os){
        TCTable tbl=new TCTable(name,id);
        try {
            ResultSetMetaData rsmd = rs.getMetaData();
            for(int i=1;i<=rsmd.getColumnCount();i++)
            {
                TCField field = new TCField(rsmd.getColumnName(i), rsmd.getColumnTypeName(i));
                tbl.addField(field);
            }
            tbl.getHeader(os);
            while (rs.next())
            {
                for(int i=1;i<=rsmd.getColumnCount();i++)
                {
                    if(rsmd.getColumnTypeName(i).equals("geometry")) { //Геометрию не сохраняю пока не знаю как лучьше сохранять
                        tbl.fields.get(i - 1).setValue(null);
                    }else {
                        tbl.fields.get(i - 1).setValue(rs.getString(i));
                    }
                }
                tbl.getCol(os);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    //Напиши функцию: Получить ассоциативный массив название полей таблицы и их тип
    public static Map<String, String> getTableSchema(Connection connection, String tableName) {
        Map<String, String> schemaMap = new HashMap<>();
        String query = """
                SELECT c.column_name, 
                       CASE 
                           WHEN t.typtype = 'e' THEN 'enum' 
                           WHEN t.typname = 'USER-DEFINED' THEN 
                               (SELECT pt.typname FROM pg_type pt WHERE pt.oid = c.udt_name::regtype::oid) 
                           ELSE t.typname 
                       END as data_type 
                FROM information_schema.columns c 
                JOIN pg_type t ON c.udt_name = t.typname 
                WHERE c.table_schema = ? 
                AND c.table_name = ?
                """;
        try (PreparedStatement statement = connection.prepareStatement(query)) {
            String schema = Tools.beforeFirst(tableName,".");
            String table = Tools.afterLast(tableName,".");
            statement.setString(1, schema);
            statement.setString(2, table);
            try (ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    String columnName = resultSet.getString("column_name");
                    String columnType = resultSet.getString("data_type");
                    schemaMap.put(columnName, columnType);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return schemaMap;
    }

    public static String getSQLUpdate(TCTable tbl) {
        StringBuilder sql;
        sql = new StringBuilder("update " + tbl.name + " set ");
        for(int i=0;i<tbl.fields.size();i++)
        {
            if(tbl.fields.get(i).u_exists){
                if(!tbl.fields.get(i).name.equals("uid")) {
                    sql.append(tbl.fields.get(i).name);
                    switch (tbl.fields.get(i).type){
                        case TCField.BD_UTF8_1_UUID:{
                            sql.append("=main.strtouuid(?),");
                            break;
                        }
                        case TCField.BD_UTF8_4_JSONB:{
                            sql.append("=?::jsonb,");
                            break;
                        }
                        case TCField.BD_UTF8_1_TIMESTAMP:{
                            sql.append("=?::timestamp,");
                            break;
                        }
                        default:
                            sql.append("=?,");
                    }
                }
            }
        }
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(" where uid=main.strtouuid(?)");
        return sql.toString();
    }

    public static String getSQLInsert(TCTable tbl)
    {
        String sql="insert into "+tbl.name+"(";
        StringBuilder subSql1= new StringBuilder(" ");
        StringBuilder subSql2= new StringBuilder(" ");
        for(int i=0;i<tbl.fields.size();i++)
        {
            if(tbl.fields.get(i).u_exists){
                if(!tbl.fields.get(i).name.equals("uid")) {
                    subSql1.append(tbl.fields.get(i).name);
                    subSql1.append(",");
                    switch (tbl.fields.get(i).type){
                        case TCField.BD_UTF8_1_UUID:{
                            subSql2.append("main.strtouuid(?),");
                            break;
                        }
                        case TCField.BD_UTF8_4_JSONB:{
                            subSql2.append("?::jsonb,");
                            break;
                        }
                        case TCField.BD_UTF8_1_TIMESTAMP:{
                            subSql2.append("?::timestamp,");
                            break;
                        }
                        default:
                            subSql2.append("?,");
                    }
                }
            }
        }
        subSql1.append("uid");
        subSql2.append("main.strtouuid(?)");
        sql+=subSql1+")values("+subSql2+");";
        return sql;
    }

}