在整数列表中搜索WHERE结果

Question

使用jdbc，我希望根据Integer id列表中包含的任何行来填充结果集。我的方法看起来像下面，但是抛出一个运行时异常，SQL不能被绑定。

@SqlQuery("SELECT Id, Name FROM table WHERE Id IN (:ids)")
Set<Model> loadNames(@Bind("ids") List<Integer> ids);

Answer 1

使用BindIn注释。

@SqlQuery("SELECT Id, Name FROM table WHERE Id IN (:ids)")
Set<Model> loadNames(@BindIn("ids") List<Integer> ids);

Answer 2

当我尝试这个时，我得到了一个异常:

@SqlUpdate("UPDATE k_product_rgstr SET sent_to_date = :dateSent WHERE id IN (:ids)")
public void updateSentToDate(@Bind("dateSent") Date dateSent, @BindIn("ids") List<Long> ids);
Exception in thread "main" org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: Unable to execute, no named parameter matches "ids" and no positional param for place 1 (which is 2 in the JDBC 'start at 1' scheme) has been set. [statement:"UPDATE k_product_rgstr SET sent_to_date = :dateSent WHERE id IN (:ids)", located:"UPDATE k_product_rgstr SET sent_to_date = :dateSent WHERE id IN (:ids)", rewritten:"UPDATE k_product_rgstr SET sent_to_date = ? WHERE id IN (?)", arguments:{ positional:{}, named:{__ids_11:19,__ids_10:18,__ids_15:23,__ids_14:22,__ids_13:21,__ids_12:20,__ids_19:27,__ids_18:26,__ids_17:25,__ids_16:24,__ids_200:212,__ids_201:213,__ids_202:215,__ids_203:216,__ids_204:217,__ids_205:218,__ids_206:219,__ids_207:220,__ids_208:221,__ids_209:222,__ids_210:223,__ids_211:224,__ids_212:225,__ids_213:226,__ids_214:227,__ids_215:228,__ids_216:229,__ids_217:230,__ids_218:12,__ids_219:36,__ids_186:198,__ids_187:199,__ids_188:200,__ids_189:201,__ids_180:192,__ids_181:193,__ids_182:194,__ids_183:195,__ids_184:196,__ids_185:197,__ids_197:209,__ids_198:210,__ids_199:211,__ids_190:202,__ids_191:203,__ids_192:204,__ids_193:205,__ids_194:206,__ids_195:207,__ids_196:208,dateSent:Thu Aug 06 13:41:57 EDT 2015,__ids_95:105,__ids_142:154,__ids_94:104,__ids_143:155,__ids_93:103,__ids_144:156,__ids_92:102,__ids_145:157,__ids_99:109,__ids_146:158,__ids_98:108,__ids_147:159,__ids_97:107,__ids_148:160,__ids_96:106,__ids_149:161,__ids_2:9,__ids_1:214,__ids_0:139,__ids_6:14,__ids_5:13,__ids_4:11,__ids_140:152,__ids_3:10,__ids_141:153,__ids_9:17,__ids_8:16,__ids_7:15,__ids_84:93,__ids_153:165,__ids_83:92,__ids_154:166,__ids_82:91,__ids_155:167,__ids_81:90,__ids_156:168,__ids_88:97,__ids_157:169,__ids_87:96,__ids_158:170,__ids_86:95,__ids_159:171,__ids_85:94,__ids_89:98,__ids_150:162,__ids_151:163,__ids_152:164,__ids_91:101,__ids_90:100,__ids_73:82,__ids_164:176,__ids_72:81,__ids_165:177,__ids_71:80,__ids_166:178,__ids_70:79,__ids_167:179,__ids_77:86,__ids_168:180,__ids_76:85,__ids_169:181,__ids_75:84,__ids_74:83,__ids_79:88,__ids_78:87,__ids_160:172,__ids_161:173,__ids_162:174,__ids_163:175,__ids_80:89,__ids_62:71,__ids_175:187,__ids_61:70,__ids_176:188,__ids_60:69,__ids_177:189,__ids_178:190,__ids_66:75,__ids_179:191,__ids_65:74,__ids_64:73,__ids_63:72,__ids_69:78,__ids_68:77,__ids_67:76,__ids_170:182,__ids_171:183,__ids_172:184,__ids_173:185,__ids_174:186,__ids_51:60,__ids_50:59,__ids_220:99,__ids_100:110,__ids_221:140,__ids_101:111,__ids_55:64,__ids_102:112,__ids_54:63,__ids_103:113,__ids_53:62,__ids_104:114,__ids_52:61,__ids_105:115,__ids_59:68,__ids_58:67,__ids_57:66,__ids_56:65,__ids_106:116,__ids_107:117,__ids_108:118,__ids_109:119,__ids_40:49,__ids_110:120,__ids_111:121,__ids_112:122,__ids_44:53,__ids_113:123,__ids_43:52,__ids_114:124,__ids_42:51,__ids_115:125,__ids_41:50,__ids_116:126,__ids_48:57,__ids_47:56,__ids_46:55,__ids_45:54,__ids_49:58,__ids_117:127,__ids_118:128,__ids_119:129,__ids_120:130,__ids_121:131,__ids_122:132,__ids_123:133,__ids_33:42,__ids_124:134,__ids_32:41,__ids_125:135,__ids_31:40,__ids_126:136,__ids_30:39,__ids_127:137,__ids_37:46,__ids_36:45,__ids_35:44,__ids_34:43,__ids_39:48,__ids_38:47,__ids_128:138,__ids_129:141,__ids_131:143,__ids_132:144,__ids_133:145,__ids_134:146,__ids_22:30,__ids_135:147,__ids_21:29,__ids_136:148,__ids_20:28,__ids_137:149,__ids_138:150,__ids_26:34,__ids_25:33,__ids_24:32,__ids_23:31,__ids_29:38,__ids_28:37,__ids_27:35,__ids_130:142,__ids_139:151}, finder:[]}]
    at org.skife.jdbi.v2.ColonPrefixNamedParamStatementRewriter$MyRewrittenStatement.bind(ColonPrefixNamedParamStatementRewriter.java:158)
    at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1318)
    at org.skife.jdbi.v2.Update.execute(Update.java:56)
    at org.skife.jdbi.v2.sqlobject.UpdateHandler$2.value(UpdateHandler.java:62)
    at org.skife.jdbi.v2.sqlobject.UpdateHandler.invoke(UpdateHandler.java:75)
    at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:175)
    at org.skife.jdbi.v2.sqlobject.SqlObject$1.intercept(SqlObject.java:75)
    at org.skife.jdbi.v2.sqlobject.CloseInternalDoNotUseThisClass$$EnhancerByCGLIB$$f3fbbd27.updateSentToDate(<generated>)

Answer 3

1. 确保用以下注释你的类:@UseStringTemplate3StatementLocator

   这将需要

   import org.skife.jdbi.v2.sqlobject.stringtemplate.UseStringTemplate3StatementLocator;

2. 使用BindIn注释

   @SqlQuery("SELECT Id, Name FROM table WHERE Id IN (<ids>)")

   Set<Model> loadNames(@BindIn("ids") List<Integer> ids);

   确保使用IN (<ids>)而不是IN (:ids)