Create class objects from query results in java

Question

I'm wasted hours trying to find a solution for this but i couldn't. I got 3 classes:

Database, Table and Column :

public class Database {
    private List<Table> table;
    private String name; ...

public class Table {
    private String name;
    private List<Column> column; ...


public class Column {
    private String columnName;
    private String dataType;
    private int data_length; ...

The following query is giving me a information which i want to use to make make new objects.

SELECT TABLE_NAME, COLUMN_NAAM, DATA_TYPE, DATA_LENGTH FROM USER_TAB_COLUMNS;

OUTPUT:

TABLE_NAME                     COLUMN_NAME                    DATA_TYPE  DATA_LENGTH
------------------------------ ------------------------------ --------- -----------
DONATIE                        PROJECTID                      NUMBER     22
DONATIE                        KLANTID                        NUMBER     22
DONATIE                        DONATIEDATUM                   DATE       7
DONATIE                        BEDRAG                         NUMBER     22
DONATIE                        DONATIEID                      NUMBER     22
KLANT                          ROL                            VARCHAR2   10
KLANT                          WACHTWOORD                     VARCHAR2   30
KLANT                          GEBRUIKERSNAAM                 VARCHAR2   30
KLANT                          TELEFOONNUMMER                 NUMBER     22
KLANT                          EMAIL                          VARCHAR2   30
KLANT                          WOONPLAATS                     VARCHAR2   15
KLANT                          POSTCODE                       VARCHAR2   6
KLANT                          HUISNUMMER                     VARCHAR2   5
KLANT                          ADRES                          VARCHAR2   30
KLANT                          GESLACHT                       VARCHAR2   5

I'm using the following code to get execute the query:

    public Database getTargetDbStructure(String username, String password, String url, String dbName) {
        Database db = new Database();
        db.setName(dbName);
        List<Table> tableStructureList = new ArrayList<Table>();
        try (Connection con = super.getConnectionTargetDb(username, password, url)) {
            String query = "SELECT DISTINCT(TABLE_NAME) FROM USER_TAB_COLUMNS";
            PreparedStatement pstmt = con.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();


            while (rs.next()){
                String query2 = "SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ?";
                PreparedStatement pstmt2 = con.prepareStatement(query2);
                String tableName = rs.getString("TABLE_NAME");
                pstmt2.setString(1, tableName);
                ResultSet rs2 = pstmt.executeQuery();
                Table t = new Table();
                t.setName(tableName);

                System.out.println("1");
                while (rs2.next()){
                    System.out.println("2");
                    String column_name = rs2.getString("COLUMN_NAAM");
                    String data_type = rs2.getString("DATA_TYPE");
                    int data_length = rs2.getInt("DATA_LENGTH");
                    Column column = new Column();
                    column.setColumnName(column_name);
                    column.setDataType(data_type);
                    column.setData_length(data_length);
                    t.addColumn(column);
                }
                db.addTable(t);
                tableStructureList.add(t);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return db;
    }

Well, java is new for me i'm trying to learn as you can see. The code that should execute the query isnt working. There must be a much better way instead of running a resultset within a resultset. I tried to make new table objects and add them directly to the table list but the problem is that i'll have for every table object only one column. I want to archieve the following structure:

DATABASE
    DONATIE
        PROJECTID
        KLANTID
        DONATIEDATUM
        BEDRAGDONATIEID
    KLANT
        ROL
        WACHTWOORD
        GEBRUIKERSNAAM
        ETC..
    TABLE3
        ETC...

So one simple Database object should contain those stuff. Can anyone help me around please. Thanks in advance

Thanks Azro, i saw that there are only 3 options: HTML, CSS and js. Where can i choose java? — J. Adam
– J. Adam, Commented Dec 12, 2018 at 20:32
You don't need, as Java needs a jvm to run, you can't run it here so just show it as code, HTML/CSS/JS can be run in a web browser that's why you can here too — azro
– azro, Commented Dec 12, 2018 at 20:33

Joakim Danielson · Accepted Answer · 2018-12-13 06:45:05Z

1

I would not have two ResultSet objects open at the same time as you observes yourself, I see 2 alternative solutions here.

Keep the two queries you have but loop through the first one completely, adding all tables to the tableStructureList and then when you are done with the first query you can move on to the second one but instead loop over the tableStructureList to get the table to use.
The second alternative is to merge the two queries into one and loop over the result set comparing the table name for the current row with the previous one and if it is not the same then create a new Table object and add the column from the row to that table and then continue with that until the table name changes again. For this to work you need to modify your second query so it includes TABLE_NAMEin the SELECT clause and that you sort it on TABLE_NAME so SELECT TABLE_NAME, ...(same as before)... ORDER BY TABLE_NAME

edited Dec 13, 2018 at 6:45

answered Dec 12, 2018 at 21:29

Joakim Danielson

53.1k5 gold badges36 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create class objects from query results in java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related