Issue with foreign key relations and JSON

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Issue with foreign key relations and JSON

rean
Hi guys.

I have a project that uses extjs-spring-ibatis . My problem is that I seem to get my data fine in JSON format when using a table that has no foreign key relations using ibatis as my ORM .But I only get back data from the table  that has the foreign key relation if I try and get back data from a mysql table with foreign key relations.I have a simple example below
The output of my query using the tail command  is as follows:

2012-01-12 15:42:49,966 DEBUG - {pstm-100016} Executing Statement:           select classes_id, classes_location, classes_name, classes_form, classes_size,       students_registration_number, teachers_registration_number     from Classes

And I do get ALL the results as follows:

2012-01-12 15:42:49,968 DEBUG - {rset-100017} Result: [1, Quisque., FRM, FRM1, 25, U1P 3R8, O9O 3G4]

However when I try to get the output in JSON format, all I get are the final two columns as follows:
 
012-01-12 15:42:49,979 DEBUG - {"count":1,"results":[{"teachersRegistrationNumer":"O9O 3G4","studentsRegistrationNumber":"U1P 3R8"}],"success":true}

This table is as follows
| Classes | CREATE TABLE `Classes` (
  `classes_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `classes_location` varchar(200) DEFAULT NULL,
  `classes_name` varchar(200) DEFAULT NULL,
  `classes_form` varchar(200) DEFAULT NULL,
  `classes_size` int(11) DEFAULT NULL,
  `students_registration_number` varchar(200) DEFAULT NULL,
  `teachers_registration_number` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`classes_id`),
  UNIQUE KEY `classes_id_UNIQUE` (`classes_id`),
  KEY `fk_classes_students1` (`students_registration_number`),
  KEY `fk_classes_teachers1` (`teachers_registration_numer`),
  CONSTRAINT `fk_classes_students1` FOREIGN KEY (`students_registration_number`) REFERENCES `Students` (`students_registration_number`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_classes_teachers1` FOREIGN KEY (`teachers_registration_number`) REFERENCES `Teachers` (`teachers_registration_numer`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1 |

In this case the teachers_registration_number and student_registration_number columns are constrained and they are the only ones I get data in JSON format from.

However if I try and use a table that does not have any foreign key relations I get all my data back  fromIbatis and in JSON format for example


| Teachers | CREATE TABLE `Teachers` (
  `teachers_id` int(11) NOT NULL AUTO_INCREMENT,
  `teachers_first_name` varchar(200) DEFAULT NULL,
  `teachers_surname` varchar(200) DEFAULT NULL,
  `teachers_designation` varchar(200) DEFAULT NULL,
  `teachers_registration_number` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`teachers_registration_numer`),
  UNIQUE KEY `teachers_id_UNIQUE` (`teachers_id`),
  UNIQUE KEY `teachers_registration_numer_UNIQUE` (`teachers_registration_numer`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1 |



What can I do to get back all my data from a table that has foreign key relations in ibatis?
Any help would be appreciated






Loading...