Can't read column value by column label in Java

There is an SQL query which is executed successfully via command line client on Linux

SELECT  ct.id,  ct.name,  ct.description,  ct.email_folder,  ct.email_address,  ct.boilerplates_folder,  bf.name AS boilerplates_folder_name,  ct.document_registry,  ct.project_template,  pt.name AS project_template_name,  ct.custom_parties,  ct.case_variables,  ct.remuneration_type,  rt.name AS remuneration_type_name,  ct.remuneration_amount FROM zimbra.subs_case_template ct   LEFT JOIN subs_boilerplates_folder bf ON ct.boilerplates_folder = bf.id  LEFT JOIN subs_project_template pt ON ct.project_template = pt.id  LEFT JOIN subs_remuneration_type rt ON ct.remuneration_type = rt.id  WHERE ct.id = 11; 

output

+----+-----------+-----------------------+--------------+---------------+---------------------+--------------------------+-------------------+------------------+------------------------+----------------+----------------+-------------------+------------------------+---------------------+ | id | name      | description           | email_folder | email_address | boilerplates_folder | boilerplates_folder_name | document_registry | project_template | project_template_name  | custom_parties | case_variables | remuneration_type | remuneration_type_name | remuneration_amount | +----+-----------+-----------------------+--------------+---------------+---------------------+--------------------------+-------------------+------------------+------------------------+----------------+----------------+-------------------+------------------------+---------------------+ | 11 | Template8 | Template8 description |            1 |             1 |                   4 | /Folder/Four             |                 1 |                4 | Project_template_4.mpt |              0 |              0 |              NULL | NULL                   |                NULL | +----+-----------+-----------------------+--------------+---------------+---------------------+--------------------------+-------------------+------------------+------------------------+----------------+----------------+-------------------+------------------------+---------------------+ 1 row in set (0.00 sec) 

but when the same SQL query is executed with use of JDBC following error is thrown:

java.sql.SQLException: Column 'boilerplates_folder_name' not found 

from this line of code

rs.getString("boilerplates_folder_name"); 

the same happens with a column labeled as remuneration_type_name

When I'm trying to access remuneration_type_name column via its index with following code

rs.getString(14); 

next error is thrown

java.sql.SQLException: Column Index out of range, 14 > 12. 

12 is a number of columns queried from subs_case_template table, but with 3 joined fields it must be 15, right?

Here is what ResultSet documentation says:

/**  * Retrieves the value of the designated column in the current row  * of this <code>ResultSet</code> object as  * a <code>String</code> in the Java programming language.  *  * @param columnLabel the label for the column specified with the SQL AS clause.  If the SQL AS clause was not specified, then the label is the name of the column  * @return the column value; if the value is SQL <code>NULL</code>, the  * value returned is <code>null</code>  * @exception SQLException if the columnLabel is not valid;  * if a database access error occurs or this method is  *            called on a closed result set  */ String getString(String columnLabel) throws SQLException; 

exactly this line

@param columnLabel the label for the column specified with the SQL AS clause.  If the SQL AS clause was not specified, then the label is the name of the column 

So it looks like it must work.

SQL schema

CREATE TABLE `subs_boilerplates_folder` (   `id` smallint UNSIGNED AUTO_INCREMENT,   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  CREATE TABLE `subs_project_template` (   `id` smallint UNSIGNED AUTO_INCREMENT,   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  CREATE TABLE `subs_remuneration_type` (   `id` tinyint UNSIGNED AUTO_INCREMENT,   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  CREATE TABLE `subs_case_template` (   `id` int(11) UNSIGNED AUTO_INCREMENT,   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,   `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,   `email_folder` bool DEFAULT '0',   `email_address` bool DEFAULT '0',   `boilerplates_folder` smallint UNSIGNED DEFAULT NULL,   `document_registry` bool DEFAULT '1',   `project_template` smallint UNSIGNED DEFAULT NULL,   `custom_parties` bool DEFAULT '0',   `case_variables` bool DEFAULT'0',   `remuneration_type` tinyint UNSIGNED DEFAULT NULL,   `remuneration_amount` int DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `boilerplates_folder` (`boilerplates_folder`),   KEY `project_template` (`project_template`),   KEY `remuneration_type` (`remuneration_type`),   CONSTRAINT `subs_case_template_ibfk_1` FOREIGN KEY (`boilerplates_folder`) REFERENCES `subs_boilerplates_folder` (`id`),   CONSTRAINT `subs_case_template_ibfk_2` FOREIGN KEY (`project_template`) REFERENCES `subs_project_template` (`id`),   CONSTRAINT `subs_case_template_ibfk_3` FOREIGN KEY (`remuneration_type`) REFERENCES `subs_remuneration_type` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Test data

INSERT INTO subs_boilerplates_folder VALUES (1, '/Folder/One'); INSERT INTO subs_boilerplates_folder VALUES (2, '/Folder/Two'); INSERT INTO subs_boilerplates_folder VALUES (3, '/Folder/Three'); INSERT INTO subs_boilerplates_folder VALUES (4, '/Folder/Four');  INSERT INTO subs_project_template (name) VALUES   ('Project_template_1.mpt'),('Project_template_2.mpt'),('Project_template_3.mpt'),('Project_template_4.mpt');  INSERT INTO subs_remuneration_type(name) VALUES   ('Dayly'),('Weekly'),('Monthly without base fee or cap'),('Monthly with a base fee or cap'),('Yearly');  INSERT INTO subs_case_template VALUES (11, 'Template8', 'Template8 description', 1, 1, 4, '/Folder/Four', 1, 4, 'Project_template_4.mpt', 0, 0, NULL, NULL, NULL); 

Java

$ java -version openjdk version "1.8.0-internal" OpenJDK Runtime Environment (build 1.8.0-internal-build_2014_07_29_16_56-b00) OpenJDK 64-Bit Server VM (build 25.40-b01, mixed mode) 

MariaDB

MariaDB [zimbra]> status -------------- /opt/zimbra/mariadb/bin/mysql  Ver 15.1 Distrib 10.0.15-MariaDB, for Linux (x86_64) using readline 5.1 

Replay

Category: java Time: 2016-07-28 Views: 0

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.303 (s). 12 q(s)