Re: TINYINT column returning BigInteger instead of Integer

[rana gurram]

Hi Todd,

Thanks for your response.

Here is the definition of the table.

'PAT_MEDICAL_RECORD',
'CREATE TABLE `PAT_MEDICAL_RECORD` (\n `PAT_RECORD_ID` bigint(20) NOT NULL AUTO_INCREMENT,\n
`PATIENT_ID` bigint(20) DEFAULT NULL,\n
`DOCUMENT_CATEGORY` varchar(45) DEFAULT NULL,\n `DOCUMENT_TYPE` varchar(45) DEFAULT NULL,\n `
LOCATION` varchar(45) DEFAULT NULL,\n `DOCTOR_ID` int(11) DEFAULT NULL,\n `DATE_OF_SERVICE` date DEFAULT NULL,\n `CREATED_DATE` datetime DEFAULT NULL,\n `FILE_NAME` varchar(200) DEFAULT NULL,\n `DM_DOCUMENT_ID` varchar(150) DEFAULT NULL,\n `OFFICE_ID` int(11) DEFAULT NULL,\n `RECORD_STATUS` tinyint(1) NOT NULL DEFAULT \'0\',\n `PAT_ORDER_CAT_ID` int(11) DEFAULT NULL,\n `DOCUMENT_CATEGORY_LEGACY` varchar(45) DEFAULT NULL,\n `DOCUMENT_TYPE_LEGACY` varchar(45) DEFAULT NULL,\n `CREATED_BY` varchar(45) DEFAULT NULL,\n PRIMARY KEY (`PAT_RECORD_ID`),\n KEY `IDX_PAT_MED_REC_DATE_OF_SERVICE` (`DATE_OF_SERVICE`),\n KEY `IDX_PAT_MED_REC_DOC_TYPE` (`DOCUMENT_TYPE`),\n KEY `IDX_PAT_MED_REC_DOC_CAT` (`DOCUMENT_CATEGORY`),\n KEY `PATIENT_ID` (`PATIENT_ID`)\n) ENGINE=InnoDB AUTO_INCREMENT=5213683 DEFAULT CHARSET=utf8'


You can see RECORD_STATUS` tinyint(1) NOT NULL DEFAULT \'0\' which is impacted...

The Index that is added is in table called USER. There is a LEFT Out Join between USER table and PAT_MEDICAL_RECORD.


I am sending you Query also with Hibernate code.


StringBuilder sBuild = new StringBuilder();
try
{
sBuild.append(" select IFNULL(user.user_name,'') as userName, ")
.append(" IFNULL(patmedicalrecords.document_category,null) as docCat,")
.append(" IFNULL(patmedicalrecords.document_type,null) as docType,")
.append(" date_format(patmedicalrecords.date_of_service,'%m/%d/%Y') as DOS,")
.append(" IFNULL(patmedicalrecords.dm_document_id,null) as dmDocId,")
.append(" IFNULL(patmedicalrecords.pat_record_id,null) as patRecId,")
.append(" IFNULL(patmedicalrecords.patient_id,null) as patientId,")
.append(" IFNULL(patmedicalrecords.record_status,0) as recStatus,")
.append(" IFNULL(patmedicalrecords.doctor_id,0) as docId,")
.append(" IFNULL(patmedicalrecords.location,'') as location")
.append(" from PAT_MEDICAL_RECORD as patmedicalrecords ")
.append(" left join USERS as user on patmedicalrecords.doctor_id = user.doctor_id ")
.append(" where patmedicalrecords.patient_id ="+patientId)
.append(type)
.append(" order by patmedicalrecords.date_of_service desc");


Query qry = getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sBuild.toString());
qry.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return qry.list();



The connector version we are using is mysql-connector-java 5.1.16