Lukáš Šálek Lukáš Šálek - 4 months ago 18
Java Question

I can not get values from table. Relationship one to many

Hi i have a problem with get many records from db
this is my main table

@DatabaseTable(tableName = "MyTrips")
public class MyTripTable {

@DatabaseField(generatedId = true)
private long id;

@DatabaseField
private long timestamp;


@DatabaseField(foreign = true, foreignAutoRefresh = true )
private ForeignCollection<PortTable> mPorts;

public MyTripTable() {}

public ForeignCollection<PortTable> getPorts() {
return mPorts;
}

public long getId() {
return id;
}
}


and here is second table

@DatabaseTable(tableName = "Ports")
public class PortTable {

@DatabaseField(generatedId = true)
private long id;

@DatabaseField
private long timestamp;

@DatabaseField(foreign = true, foreignAutoRefresh = true )
private MyTripTable myTripTable;

public PortTable() {}
public void setTripAssigned(MyTripTable myTripTable){
this.myTripTable = myTripTable;
}
}


And i need one
MyTrips
have more
Ports

I am doing this by this example but doesn't work

problem is here:

private void strawberry(){
DatabaseHelper helper = OpenHelperManager.getHelper(getApplicationContext(), DatabaseHelper.class);
RuntimeExceptionDao<MyTripTable, Long> mTrip = helper.getTripRuntimeExcaptionDao();
RuntimeExceptionDao<PortTable, Long> mPort = helper.getPortRuntimeExcaptionDao();

long time1 = Calendar.getInstance().getTimeInMillis();

//crate data
MyTripTable myFirstTrip = new MyTripTable(153315, "Praha - Gothenburg", "Big fat woman",
time1,time1,time1,time1,1,5,6 );
mTrip.create(myFirstTrip);

PortTable port1 = new PortTable("Praha",
"Praha is the capital and largest city of the Czech Republic. ",
50.078047,
14.427172);
port1.setTripAssigned(myFirstTrip);
PortTable port2 = new PortTable("Praha",
"Praha is the capital and largest city of the Czech Republic. ",
50.078047,
14.427172);
port2.setTripAssigned(myFirstTrip);
PortTable port3 = new PortTable("Praha",
"Praha is the capital and largest city of the Czech Republic. ",
50.078047,
14.427172);
port3.setTripAssigned(myFirstTrip);
PortTable port4 = new PortTable("Praha",
"Praha is the capital and largest city of the Czech Republic. ",
50.078047,
14.427172);
port4.setTripAssigned(myFirstTrip);

mPort.create(port1);
mPort.create(port2);
mPort.create(port3);
mPort.create(port4);


//Get data
Log.e("LSA", "----------------------------------TRIPS");
List<MyTripTable> alltrips = mTrip.queryForAll();
Log.e("LSA", "TRIPS" + alltrips.toString());
for (MyTripTable trip : alltrips){
Log.e("LSA", "Trip: " + trip.toString());
}

Log.e("LSA", "----------------------------------PORTS");
List<PortTable> allPorts = mPort.queryForAll();
Log.e("LSA", "PORTS" + allPorts.toString());
for (PortTable port : allPorts){
Log.e("LSA", "port: " + port.toString());
}

Log.e("LSA", "----------------------------------PORTs assigned");

MyTripTable mOneTrip = mTrip.queryForId(myFirstTrip.getId());
ForeignCollection<PortTable> mPortsForOneTrip = mOneTrip.getPorts();

Log.e("LSA", "ports inside trip: " + mPortsForOneTrip);
}


this part always return null

ForeignCollection<PortTable> mPortsForOneTrip = mOneTrip.getPorts();


Can you help me? Where is a mistake?
Thank you for the response!

Answer

Basically your main method should be giving you below error on running the above code . I am not sure why is it running for you in the first place.

Exception in thread "main" java.sql.SQLException: Field 'mPorts' in class interface com.j256.ormlite.dao.ForeignCollection' should use the @ForeignCollectionField annotation not foreign=true

The above error says in your class MyTripTable.java , you have marked @DatabaseField(foreign = true, foreignAutoRefresh = true ) on a collection,while according to docs you should use the annotation @ForeignCollectionField. I am posting the running code .

Code for MyTripTable.java

@DatabaseTable(tableName = "MyTrips")
public class MyTripTable {

    @DatabaseField(generatedId = true)
    private long id;

    @DatabaseField
    private long timestamp;

//    @DatabaseField(foreignAutoRefresh = true)
    @ForeignCollectionField(eager = false)
    private ForeignCollection<PortTable> mPorts;

    public MyTripTable() {
    }

    public ForeignCollection<PortTable> getPorts() {
        return mPorts;
    }

    public long getId() {
        return id;
    }

    public long getTimestamp() {
        return timestamp;
    }

    public void setId(long id) {
        this.id = id;
    }

    public void setTimestamp(long timestamp) {
        this.timestamp = timestamp;
    }
}

Code for Port Table.java

@DatabaseTable(tableName = "Ports")
public class PortTable {

    /** The id. */
    @DatabaseField(generatedId = true)
    private long id;

    /** The timestamp. */
    @DatabaseField
    private long timestamp;

    /** The my trip table. */
    @DatabaseField(foreign = true, foreignAutoRefresh = true)
    private MyTripTable myTripTable;

    /**
     * Instantiates a new port table.
     */
    public PortTable() {
    }

    /**
     * Sets the trip assigned.
     *
     * @param myTripTable the new trip assigned
     */
    public void setTripAssigned(MyTripTable myTripTable) {
        this.myTripTable = myTripTable;
    }

    /**
     * Gets the id.
     *
     * @return the id
     */
    public long getId() {
        return id;
    }

    /**
     * Gets the timestamp.
     *
     * @return the timestamp
     */
    public long getTimestamp() {
        return timestamp;
    }

    /**
     * Sets the id.
     *
     * @param id the new id
     */
    public void setId(long id) {
        this.id = id;
    }

    /**
     * Sets the timestamp.
     *
     * @param timestamp the new timestamp
     */
    public void setTimestamp(long timestamp) {
        this.timestamp = timestamp;
    }
}

Below shows the execution of above code....

 public static void main(String[] args) throws SQLException {
        String databaseUrl = "jdbc:mysql://localhost:3306/YOUR_DATABASE";

        ConnectionSource connectionSource = new JdbcConnectionSource(databaseUrl, "root", "root");
        // instantiate the dao
        Dao<MyTripTable, Long> tripDao = DaoManager.createDao(connectionSource, MyTripTable.class);
        Dao<PortTable, Long> portDao = DaoManager.createDao(connectionSource, PortTable.class);

        // if you need to create the 'accounts' table make this call
        TableUtils.createTable(connectionSource, MyTripTable.class);
        TableUtils.createTable(connectionSource, PortTable.class);

        long time1 = Calendar.getInstance().getTimeInMillis();

        // crate data
        MyTripTable myFirstTrip = new MyTripTable();
        myFirstTrip.setTimestamp(time1);

        tripDao.create(myFirstTrip);

        PortTable port1 = new PortTable();
        port1.setTimestamp(time1);
        port1.setTripAssigned(myFirstTrip);

        PortTable port2 = new PortTable();
        port2.setTimestamp(time1);
        port2.setTripAssigned(myFirstTrip);

        PortTable port3 = new PortTable();
        port3.setTimestamp(time1);
        port3.setTripAssigned(myFirstTrip);

        portDao.create(port1);
        portDao.create(port2);
        portDao.create(port3);

        List<MyTripTable> alltrips = tripDao.queryForAll();
        for (MyTripTable trip : alltrips) {
            System.out.println(trip);
        }

        List<PortTable> allPorts = portDao.queryForAll();
        for (PortTable port : allPorts) {
            System.out.println(port);
        }

        MyTripTable mOneTrip = tripDao.queryForId(myFirstTrip.getId());
        ForeignCollection<PortTable> mPortsForOneTrip = mOneTrip.getPorts();
        System.out.println(mPortsForOneTrip);

    }

I tested the above code and it was running fine. Please let me know if you face any further issues.