ajax - 在 Postgres 中检索数据库的列和表

Question

我正在开发一个 Spring MVC 项目，我需要获取所选 Postgres 数据库的表和属性，并在页面加载后立即将它们存储在数组中。

我目前已经编写了以下 ajax 调用，通过它我能够检索表格并显示在相应的数据库下。

VisualEditor.js

drop: function (e, ui) {

    var mouseTop = e.clientY;
    var mouseLeft = e.clientX;

    var dropElem = ui.draggable.attr('class');
    droppedElement = ui.helper.clone();
    ui.helper.remove();
    $(droppedElement).removeAttr("class");
    $(droppedElement).draggable({containment: "container"});
    jsPlumb.repaint(ui.helper);


    //If the dropped Element is a TABLE then->
    if (dropElem == "stream ui-draggable ui-draggable-handle") {
        var newAgent = $('<div>');
        jsPlumb.addEndpoint(newAgent,connectorProperties);
        newAgent.attr('id', i).addClass('streamdrop');
        var elemType = "table";
        $("#container").addClass("disabledbutton");
        $("#toolbox").addClass("disabledbutton");

        $('#container').append(newAgent);

        $.ajax({
            type: "post",
            url: "http://localhost:8080/controllers/tables",
            cache: false,
            success: function(response){
                if (response !== "{}"){

                var array = response.replace("{", "").replace("}", "");
                var index = array.indexOf("[") +1;
                var stringval = array.split(":");
                var stringval2 = array.substring(index,array.indexOf("]")).split(",");
                var db_names = new Array();
                var table_names = new Array();
                var column_names = new Array();

                for(var i = 0 ;i < stringval.length-1 ;i++)
                {
                    db_names[i] = eval(stringval[i]);
                    if(db_names[i]=="testdb"){
                        var StreamArray = new Array();
                    }
                    var listId = "db"+i;
                    var dropdownId ="mydrpdown"+i;
                    table_names[i] = new Array();
                    $("#lot").append(
                        "<li onclick='myFunction(\""+dropdownId+"\","+i+","+stringval2.length+")' class='list-group-item dropbtn' id='"+listId+"'> " + db_names[i] +
                         "</li>  "+
                         "<div id='" + dropdownId +"' class='dropdown-content'> " +
                         "<a onclick='setDatabase(\""+db_names[i]+"\",\""+listId+"\")'> Make This as Default Database</a>"+
                         "</div>"
                    );
                    $("#databaseID").append(
                        "<option>" + db_names[i] +"</option>"
                    );

                    for(var j=0;j < stringval2.length;j++)
                    {
                        /**
                         * Loading the Predefined Databases and Tables of the Connected DB
                         */
                        var table_id= "tableId"+i+j;
                        if( eval(stringval2[j]) != null){

                        table_names[i][j] = eval(stringval2[j]);

                        if(db_names[i]=="testdb")
                        {
                            StreamArray[j] = new Array(4);
                            StreamArray[j][0] = table_names[i][j];

                            /**
                             * table_names array values at the moment are:
                             * customer,department and students
                             * So the following ajax call should pass each table name to the listTables
                             * method in the controller and fetch the respective columns of each table
                             */

                            $.ajax({
                                type: "post",
                                url: "http://localhost:8080/controllers/listTables/{tablename}",
                                data: { tablename: table_names[i][j]} ,
                                cache: false,
                                success: function(response){
                                    if (response !== "{}"){

                                var array = response.replace("{", "").replace("}", "");
                                var index = array.indexOf("[") +1;
                                var stringval = array.split(":");
                                var stringval2 = array.substring(index,array.indexOf("]")).split(",");
                                var db_names = new Array();
                                var table_names = new Array();
                                var column_names = new Array();

                                for(var i = 0 ;i < stringval.length-1 ;i++){

                                }
                                    }
                                }
                            });

                        }

                          $("#lot").append(
                            "<li class='list-group-item'style = 'display:none' id='"+table_id+"'> "+table_names[i][j]+" </li>");
                    }   
                }

                $("#lot").append(
                    "</br>");

                array = array.slice(array.indexOf("]") +2);

                index = array.indexOf("[") +1;
                stringval2 = array.substring(index,array.indexOf("]")).split(",");
                }
            }
        },
        error: function(xhr, status, error){

              alert("Error while loading the query");
        }
    });

        $("property").show();
        $(".toolbox-titlex").show();
        $(".panel").show();

我已经编写了 2 个嵌入其中的 ajax 调用，以首先获取 table_names，然后获取 column_names。但我猜测这不是一种非常有效的方法。但我不确定如何同时获得两者。

EditorController.java

@RequestMapping(value= "/tables", method = RequestMethod.POST)
public @ResponseBody
String tables(HttpServletRequest request, HttpServletResponse response)
throws Exception {

    Map<String, List<String>> alvalues = new  HashMap<String, List<String>>();;  
    String queryString = request.getParameter("query");
//  ApplicationContext context = new ClassPathXmlApplicationContext("Spring-Module.xml");

    //CustomerDao customerDAO = (CustomerDao) context.getBean("CustomerDao");
    alvalues = customerDAO.getAllTables();
    Gson gson = new Gson();
    String element = gson.toJson(alvalues);
        System.out.println(element);

            return element;


}

@RequestMapping(value= "/listTables/{tablename}", method = RequestMethod.POST)
public @ResponseBody
String listTables(HttpServletRequest request, HttpServletResponse response,@PathVariable("tablename") String tablename)
throws Exception {

    Map<String, List<String>> alvalues = new  HashMap<String, List<String>>();;  
    String queryString = request.getParameter("query");

    alvalues = customerDAO.getAllFields(tablename);
    Gson gson = new Gson();
    String element = gson.toJson(alvalues);
        System.out.println(element);

            return element;


}

CustomerDAO.java

public Map<String, List<String>> getAllTables();
public Map<String, List<String>> getAllFields(String tablename);

jdbcCustomerDAO.java

 public Map<String, List<String>> getAllTables(){


        int k = 1;
        Map<String, List<String>> map = new HashMap<String, List<String>>();
        ArrayList<String> databases = getAllDatabse();


        String sql = "SELECT table_name FROM information_schema.tables WHERE table_schema='public'";

        Connection conn = null;

        for(int i=0;i<=databases.size();i++)
        {   
        try {
            Class.forName("org.postgresql.Driver");
            conn = DriverManager.getConnection(
               "jdbc:postgresql://localhost:5432/"+databases.get(i),"postgres", "123");

            PreparedStatement ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE, 
                    ResultSet.CONCUR_UPDATABLE);


            ResultSet rs = ps.executeQuery();
            ArrayList<String> alvalues = new ArrayList<String>();
                while(rs.next()){
                    alvalues.add(rs.getString(1));

                }

                map.put(databases.get(i), alvalues);
                rs.beforeFirst(); 



            for (Map.Entry<String, List<String>> entry : map.entrySet()) {

                String key = entry.getKey();

                List<String> values = entry.getValue();

                System.out.println("Key = " + key);

                System.out.println("Values = " + values + "n");

            } 
            conn.close();

    }
        catch(Exception e){

            System.out.println(e.getMessage());
        }
        }
        return map;
    }


 public Map<String, List<String>> getAllFields(String tablename){


        int k = 1;
        Map<String, List<String>> map = new HashMap<String, List<String>>();
        ArrayList<String> databases = getAllDatabse();


        String sql = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '"+tablename+"'";

        Connection conn = null;

        for(int i=0;i<=databases.size();i++)
        {   
        try {
            Class.forName("org.postgresql.Driver");
            conn = DriverManager.getConnection(
               "jdbc:postgresql://localhost:5432/"+databases.get(i),"postgres", "123");

            PreparedStatement ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE, 
                 ResultSet.CONCUR_UPDATABLE);


            ResultSet rs = ps.executeQuery();
            ArrayList<String> alvalues = new ArrayList<String>();
                while(rs.next()){
                    alvalues.add(rs.getString(1));

                }

                map.put(databases.get(i), alvalues);
                rs.beforeFirst(); 



            for (Map.Entry<String, List<String>> entry : map.entrySet()) {

                String key = entry.getKey();

                List<String> values = entry.getValue();

                System.out.println("Key = " + key);

                System.out.println("Values = " + values + "n");

            } 
            conn.close();

    }
        catch(Exception e){

            System.out.println(e.getMessage());
        }
        }
        return map;
    }

但我无法成功获取列名。

控制台日志

According to this, the table_names are successfully retrieved but the column names are empty.

 INFO : com.postgres.controllers.HomeController - Welcome home! The client locale is en_US.
 postgres
 postgreside
 testdb
 Key = postgres
 Values = []n
 Key = postgreside
 Values = [constraints, users, notes, session, projects, databases, tables, columns, index, checksconstraint, additionalproperties, foreignref, primaryref, referenceproperties, department, person]n
 Key = postgres
 Values = []n
 Key = testdb
 Values = [customer, department, students]n
 Key = postgreside
 Values = [constraints, users, notes, session, projects, databases, tables, columns, index, checksconstraint, additionalproperties, foreignref, primaryref, referenceproperties, department, person]n
 Key = postgres
 Values = []n
 Index: 3, Size: 3
 {"testdb":["customer","department","students"],"postgreside":["constraints","users","notes","session","projects","databases","tables","columns","index","checksconstraint","additionalproperties","foreignref","primaryref","referenceproperties","department","person"],"postgres":[]}
 postgres
 postgreside
 testdb
 postgres
 postgreside
 testdb
 postgres
 postgreside
 testdb
 Key = postgres
 Values = []n
 Key = postgres
 Values = []n
 Key = postgres
 Values = []n
 Key = postgreside
 Values = []n
 Key = postgres
 Values = []n
 Key = postgreside
 Values = []n
 Key = postgres
 Values = []n
 Key = postgreside
 Values = []n
 Key = postgres
 Values = []n
 Key = testdb
 Values = []n
 Key = postgreside
 Values = []n
 Key = postgres
 Values = []n
 Index: 3, Size: 3
 {"testdb":[],"postgreside":[],"postgres":[]}
 Key = testdb
 Values = []n
 Key = postgreside
 Values = []n
 Key = postgres
 Values = []n
 Index: 3, Size: 3
 {"testdb":[],"postgreside":[],"postgres":[]}
 Key = testdb
 Values = []n
 Key = postgreside
 Values = []n
 Key = postgres
 Values = []n
 Index: 3, Size: 3
 {"testdb":[],"postgreside":[],"postgres":[]}

我想要一个方法来检索表名及其属性/列并将其存储在 StreamArray 中，这样我就不需要在每次需要引用时都访问数据库到相关数据。

对于如何获取列名称并将其存储在我在 VisualEditor.js 下创建的 StreamArray 中的任何建议，我们将不胜感激。

Answer 1

一般情况下，你可以处理多个数据库，多个schema，每个表名可以出现在多个schema中。在您的情况下，您可能需要排除更多模式。或者您可能决定只有模式“public”是相关的。 (要小心这样的决定。)

select table_catalog, table_schema, table_name, column_name
from information_schema.columns
where table_catalog = 'your_db_name'
  and table_schema <> 'information_schema'
  and table_schema <> 'pg_catalog'
order by table_catalog, table_schema, table_name, column_name;