±à¼ÍƼö: |
±¾ÎÄÖ÷Òª½²½âJdbcTemplateÀàÖ§³ÖµÄ»Øµ÷Àà
£¬jdbcTmeplateµÄCRUD²Ù×÷ £¬²éѯ²Ù×÷µÈÏ£Íû¶ÔÄúÄÜÓÐËù°ïÖú¡£
±¾ÎÄÀ´×ÔÓÚ²©¿ÍÔ°£¬ÓÉ»ðÁú¹ûÈí¼þDelores±à¼ÍƼö |
|
¸ÅÊö
Spring JDBC³éÏó¿ò¼Ücore°üÌṩÁËJDBCÄ£°åÀ࣬ÆäÖÐJdbcTemplateÊÇcore°üµÄºËÐÄÀ࣬ËùÒÔÆäËûÄ£°åÀà¶¼ÊÇ»ùÓÚËü·â×°Íê³ÉµÄ£¬JDBCÄ£°åÀàÊǵÚÒ»ÖÖ¹¤×÷ģʽ¡£ JdbcTemplateÀàͨ¹ýÄ£°åÉè¼ÆÄ£Ê½°ïÖúÎÒÃÇÏû³ýÁËÈß³¤µÄ´úÂ룬ֻ×öÐèÒª×öµÄÊÂÇ飨¼´¿É±ä²¿·Ö£©£¬²¢ÇÒ°ïÎÒÃÇ×öÄÄЩ¹Ì¶¨²¿·Ö£¬ÈçÁ¬½ÓµÄ´´½¨¼°¹Ø±Õ¡£
JdbcTemplateÀà¶Ô¿É±ä²¿·Ö²ÉÓûص÷½Ó¿Ú·½Ê½ÊµÏÖ£¬ÈçConnectionCallbackͨ¹ý»Øµ÷½Ó¿Ú·µ»Ø¸øÓû§Ò»¸öÁ¬½Ó£¬´Ó¶ø¿ÉÒÔʹÓøÃÁ¬½Ó×öÈκÎÊÂÇé¡¢StatementCallbackͨ¹ý»Øµ÷½Ó¿Ú·µ»Ø¸øÓû§Ò»¸öStatement£¬´Ó¶ø¿ÉÒÔʹÓøÃStatement×öÈκÎÊÂÇéµÈµÈ£¬»¹ÓÐÆäËûһЩ»Øµ÷½Ó¿Ú

Spring³ýÁËÌṩJdbcTemplateºËÐÄÀ࣬»¹ÌṩÁË»ùÓÚJdbcTemplateʵÏÖµÄNamedParameterJdbcTemplateÀàÓÃÓÚÖ§³ÖÃüÃû²ÎÊý°ó¶¨¡¢ SimpleJdbcTemplateÀàÓÃÓÚÖ§³ÖJava5+µÄ¿É±ä²ÎÊý¼°×Ô¶¯×°Ïä²ðÏäµÈÌØÐÔ¡£
JdbcTemplateÀàÖ§³ÖµÄ»Øµ÷Àࣺ
Ô¤±àÒëÓï¾ä¼°´æ´¢¹ý³Ì´´½¨»Øµ÷£ºÓÃÓÚ¸ù¾ÝJdbcTemplateÌṩµÄÁ¬½Ó´´½¨ÏàÓ¦µÄÓï¾ä£»
PreparedStatementCreator£ºÍ¨¹ý»Øµ÷»ñÈ¡JdbcTemplateÌṩµÄConnection£¬ÓÉÓû§Ê¹ÓøÃConncetion´´½¨Ïà¹ØµÄPreparedStatement£»
CallableStatementCreator£ºÍ¨¹ý»Øµ÷»ñÈ¡JdbcTemplateÌṩµÄConnection£¬ÓÉÓû§Ê¹ÓøÃConncetion´´½¨Ïà¹ØµÄCallableStatement£»
Ô¤±àÒëÓï¾äÉèÖµ»Øµ÷£ºÓÃÓÚ¸øÔ¤±àÒëÓï¾äÏàÓ¦²ÎÊýÉèÖµ£»
PreparedStatementSetter£ºÍ¨¹ý»Øµ÷»ñÈ¡JdbcTemplateÌṩµÄPreparedStatement£¬ÓÉÓû§À´¶ÔÏàÓ¦µÄÔ¤±àÒëÓï¾äÏàÓ¦²ÎÊýÉèÖµ£»
BatchPreparedStatementSetter£º£»ÀàËÆÓÚPreparedStatementSetter£¬µ«ÓÃÓÚÅú´¦Àí£¬ÐèÒªÖ¸¶¨Åú´¦Àí´óС£»
×Ô¶¨Ò幦Äܻص÷£ºÌṩ¸øÓû§Ò»¸öÀ©Õ¹µã£¬Óû§¿ÉÒÔÔÚÖ¸¶¨ÀàÐ͵ÄÀ©Õ¹µãÖ´ÐÐÈκÎÊýÁ¿ÐèÒªµÄ²Ù×÷£»
ConnectionCallback£ºÍ¨¹ý»Øµ÷»ñÈ¡JdbcTemplateÌṩµÄConnection£¬Óû§¿ÉÔÚ¸ÃConnectionÖ´ÐÐÈκÎÊýÁ¿µÄ²Ù×÷£»
StatementCallback£ºÍ¨¹ý»Øµ÷»ñÈ¡JdbcTemplateÌṩµÄStatement£¬Óû§¿ÉÒÔÔÚ¸ÃStatementÖ´ÐÐÈκÎÊýÁ¿µÄ²Ù×÷£»
PreparedStatementCallback£ºÍ¨¹ý»Øµ÷»ñÈ¡JdbcTemplateÌṩµÄPreparedStatement£¬Óû§¿ÉÒÔÔÚ¸ÃPreparedStatementÖ´ÐÐÈκÎÊýÁ¿µÄ²Ù×÷£»
CallableStatementCallback£ºÍ¨¹ý»Øµ÷»ñÈ¡JdbcTemplateÌṩµÄCallableStatement£¬Óû§¿ÉÒÔÔÚ¸ÃCallableStatementÖ´ÐÐÈκÎÊýÁ¿µÄ²Ù×÷£»
½á¹û¼¯´¦Àí»Øµ÷£ºÍ¨¹ý»Øµ÷´¦ÀíResultSet»ò½«ResultSetת»»ÎªÐèÒªµÄÐÎʽ£»
RowMapper£ºÓÃÓÚ½«½á¹û¼¯Ã¿ÐÐÊý¾Ýת»»ÎªÐèÒªµÄÀàÐÍ£¬Óû§ÐèʵÏÖ·½·¨mapRow(ResultSet rs, int rowNum)À´Íê³É½«Ã¿ÐÐÊý¾Ýת»»ÎªÏàÓ¦µÄÀàÐÍ¡£
RowCallbackHandler£ºÓÃÓÚ´¦ÀíResultSetµÄÿһÐнá¹û£¬Óû§ÐèʵÏÖ·½·¨processRow(ResultSet rs)À´Íê³É´¦Àí£¬Ôڸûص÷·½·¨ÖÐÎÞÐèÖ´ÐÐrs.next()£¬¸Ã²Ù×÷ÓÉJdbcTemplateÀ´Ö´ÐУ¬Óû§Ö»Ðè°´ÐлñÈ¡Êý¾ÝÈ»ºó´¦Àí¼´¿É¡£
ResultSetExtractor£ºÓÃÓÚ½á¹û¼¯Êý¾ÝÌáÈ¡£¬Óû§ÐèʵÏÖ·½·¨extractData(ResultSet rs)À´´¦Àí½á¹û¼¯£¬Óû§±ØÐë´¦ÀíÕû¸ö½á¹û¼¯£»
ÏÂÃæÏêϸ½²½âjdbcTmeplateµÄCRUD²Ù×÷£º
£¨Ò»£©Ôö¼Ó¡¢É¾³ý¡¢Ð޸IJÙ×÷£º
1£©Ôö¼Ó¡¢¸üС¢É¾³ý£¨Ò»ÌõsqlÓï¾ä£©£¨sql¹Ì¶¨£¬²»ÐèÒª²ÎÊý£©£º
(a) int update(final String sql)
ÆäÖÐsql²ÎÊýΪÐèÒª´«ÈëµÄ²åÈësqlÓï¾ä¡£
(b)int update(PreparedStatementCreator psc)
public void test()
{
jdbcTemplate.update (new PreparedStatementCreator()
{
@Override
public PreparedStatement createPreparedStatement(Connection
conn)
throws SQLException {
return conn.prepareStatement ("insert into
test(name) values('name1')");
}
});
} |
(c)Èç¹ûÐèÒª·µ»ØÐ²åÈëÊý¾ÝµÄÖ÷¼ü£¬²ÉÓÃÈçÏ·½·¨£¨Ê¹ÓÃKeyHolder keyholder=new GeneratedKeyHolder();»ñµÃÖ÷¼ü£¬jdbcTemplateºÍNamedParameterJdbcTemplate¶¼¿ÉÒÔͨ¹ý´Ë·½·¨»ñµÃÖ÷¼ü£©£º
int update(PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
public void test()
{
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update (new PreparedStatementCreator()
{
@Override
public PreparedStatement createPreparedStatement(Connection
conn)
throws SQLException {
return conn.prepareStatement ("insert into
test(name) values('name1')");
}
},keyHolder);
int i = keyHolder.getKey().intValue(); //Õâ¾ÍÊǸղåÈëµÄÊý¾ÝµÄÖ÷¼ü
} |
2£©Ôö¼Ó¡¢¸üС¢É¾³ý£¨Ò»ÌõsqlÓï¾ä£©£¨sqlÐèҪעÈë²ÎÊýÌî³ä¡®£¿¡¯£©£º ¡¡(a)int update(String sql, PreparedStatementSetter pss)
public void test()
{
String sql = "insert into test (name) values
(?)";
//·µ»ØµÄÊǸüеÄÐÐÊý
int count = jdbcTemplate.update (sql, new PreparedStatementSetter(){
@Override
public void setValues (PreparedStatement pstmt)
throws SQLException {
pstmt.setObject(1, "name4");
}
});
} |
(b)int update(String sql, Object[] args, int[] argTypes)
ÆäÖвÎÊýº¬Ò壺 sql:Ô¤´¦ÀísqlÓï¾ä£» args:sqlÐèҪעÈëµÄ²ÎÊý£» argTypes:ÐèҪעÈëµÄsql²ÎÊýµÄJDBCÀàÐÍ£¨java.sql.TypesÖÐÀ´»ñÈ¡ÀàÐ͵ij£Á¿£©£»
public void test()
{
String sql = "insert into test (name,age,create_date)
values (?,?,?)";
Date now = new Date(System. currentTimeMillis());
//·µ»ØµÄÊǸüеÄÐÐÊý
int count = jdbcTemplate.update (sql, new Object[]{"СÃ÷",14,now},
new int[]{Types.VARCHAR,Types. INTEGER,Types.DATE});
} |
(c)int update(String sql, Object... args)
ÆäʵÄÚ²¿»¹Êǵ÷Ó÷½·¨aʵÏֵģ¬JdbcTemplateÌṩÕâÖÖ¸ü¼òµ¥µÄ·½Ê½¡°update(String sql, Object... args)¡±À´ÊµÏÖÉèÖµ£¬ËùÒÔÖ»Òªµ±Ê¹ÓøÃÖÖ·½Ê½²»Âú×ãÐèÇóʱ²ÅӦʹÓÃPreparedStatementSetter£¨ÉÏÃæ·½·¨a£©¡£
public void test()
{
String sql = "insert into test (name,age,create_date)
values (?,?,?)";
Date now = new Date (System.currentTimeMillis());
//·µ»ØµÄÊǸüеÄÐÐÊý
int count = jdbcTemplate.update (sql, "СÃ÷",
14, now);
} |
public void test()
{
String sql = "insert into test (name,age,create_date)
values (?,?,?)";
Date now = new Date (System.currentTimeMillis());
//·µ»ØµÄÊǸüеÄÐÐÊý
int count = jdbcTemplate.update (sql, new Object[]{"СÃ÷",14,now});
} |
ÕâÁ½ÖÖʵ¼ÊÉϵ÷ÓõͼÊǸ÷½·¨£¬Óɴ˿ɼûObject...argsʵ¼ÊÉϾÍÊǿɱäµÄÊý×飬¶øÊý×鳤¶ÈÊǹ̶¨µÄ£¬±ØÐëÏȶ¨ÒåÒ»¸öÊý×飬¶øObject...argsÔÚ´«µÝʱ²ÎÊý¿ÉÒÔÈÎÒ⣬ËùÒÔÒ²¿ÉÒÔ´«µÝÒ»¸ö¹Ì¶¨µÄObjectÊý×é¡£
(d)int update(PreparedStatementCreator psc)
ʹÓø÷½·¨¿ÉÒÔ×Ô¼ºÊ¹ÓÃÔʼjdbc·½Ê½¸øÔ¤±àÒësql×¢Èë²ÎÊý£¬À´½øÐÐÔö¼Ó¡¢É¾³ý¡¢¸üвÙ×÷£º
public void test(final
Customer customer)
{//²ÎÊýÒ²ÊǾֲ¿±äÁ¿£¬Ò²±ØÐëÓÃfinalÐÞÊΣ¬ ÄÚ²¿ÀàÖвÅÄÜ·ÃÎÊ£¨È«¾Ö±äÁ¿²»Óã©
//·½·¨¾Ö²¿±ØÐëÊÇfinalµÄ£¬ÄäÃûÄÚ²¿ÀàÖвÅÄÜÒýÓÃ
final String sql = "insert into test (name,age,create_date)
values (?,?,?)";
Date now = new Date (System.currentTimeMillis());
//·µ»ØµÄÊǸüеÄÐÐÊý
int count = jdbcTemplate.update (new PreparedStatementCreator()
{
@Override
public PreparedStatement createPreparedStatement(Connection
conn)
throws SQLException {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, customer.getName());
ps.setInt(2, customer.getAge());
ps.setDate(3, customer.getCreateDate());
return ps;
}
});
} |
Èç¹ûÐèÒª·µ»Ø²åÈëµÄÖ÷¼ü£¬Ö»ÄÜÓô˷½·¨£¬Ôö¼ÓKeyHolder²ÎÊý£º
public void test(final
Customer customer)
{//²ÎÊýÒ²ÊǾֲ¿±äÁ¿£¬Ò²±ØÐëÓÃfinalÐÞÊΣ¬ ÄÚ²¿ÀàÖвÅÄÜ·ÃÎÊ£¨È«¾Ö±äÁ¿²»Óã©
KeyHolder keyHolder = new GeneratedKeyHolder();
//·½·¨¾Ö²¿±ØÐëÊÇfinalµÄ£¬ ÄäÃûÄÚ²¿ÀàÖвÅÄÜÒýÓÃ
final String sql = "insert into test (name,age,create_date)
values (?,?,?)";
Date now = new Date (System.currentTimeMillis());
//·µ»ØµÄÊǸüеÄÐÐÊý
int count = jdbcTemplate.update (new PreparedStatementCreator()
{
@Override
public PreparedStatement createPreparedStatement(Connection
conn)
throws SQLException {
PreparedStatement ps = conn.prepareStatement (sql,Statement.RETURN_GENERATED_KEYS);
//ÓеÄÊý¾Ý¿â°æ±¾²»Ò»Ñù£¬ ÐèÒªÌí¼ÓµÚ¶þ¸ö²ÎÊý£¬²»È»»á±¨´í£»
ps.setString(1, customer.getName());
ps.setInt(2, customer.getAge());
ps.setDate(3, customer.getCreateDate());
return ps;
}
},keyHolder);
int i = keyHolder.getKey().intValue(); //Õâ¾ÍÊǸղåÈëµÄÊý¾ÝµÄÖ÷¼ü
} |
3£©ÅúÁ¿Ôö¼Ó¡¢É¾³ý¡¢¸üÐÂÊý¾Ý£¨¶àÌõsqlÓï¾ä£© £¨a£©ÅúÁ¿Ö´ÐжàÌõsql£¨¹Ì¶¨µÄsql£¬²»ÐèҪעÈë²ÎÊý£¬µ«ÊÇsql¸ñʽ²»¹Ì¶¨£© int[] batchUpdate(final String[] sql)
²ÎÊýÊÇÒ»¸öStringÊý×飬´æ·Å¶àÌõsqlÓï¾ä£»·µ»ØÖµÊÇintÊý×飬¼´Ã¿Ìõsql¸üÐÂÓ°ÏìµÄÐÐÊý¡£ ¡¡£¨b£©ÅúÁ¿Ö´ÐжàÌõsql£¨Ô¤´¦Àísql£¬ÐèҪעÈë²ÎÊý£© int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss)
²ÎÊýsql£ºÒ»ÌõÔ¤´¦Àísql£¨Èç¹ûÊÇÅúÁ¿´¦ÀíÔ¤´¦Àísql£¬ÄÇôsqlµÄ¸ñʽ¾ÍÊǹ̶¨µÄ£¬Ö»Ìî³ä²ÎÊý¶øÒÑ£©£»µÚ¶þ¸ö²ÎÊý¾ÍÊǻص÷Àà£¬Ç°ÃæÓÐͳһ½éÉܻص÷Àà¡£
¾ÙÁ½¸öÀý×Ó£¬Ò»¸ö¸üУ¬Ò»¸ö²åÈ룺
ÅúÁ¿²åÈ룺
public void test(final List<Customer> customer)
{//²ÎÊýÒ²ÊǾֲ¿±äÁ¿£¬ Ò²±ØÐëÓÃfinalÐÞÊΣ¬ÄÚ²¿ÀàÖвÅÄÜ·ÃÎÊ £¨È«¾Ö±äÁ¿²»Óã©
String sql = "insert into test(name,age,create_date)
values (?,?,?)";
//·µ»ØµÄÊǸüеÄÐÐÊý
int[] count = jdbcTemplate.batchUpdate
(sql, new BatchPreparedStatementSetter(){
@Override
public void setValues (PreparedStatement ps, int
i)
throws SQLException {
//×¢Èë²ÎÊýÖµ
ps.setString(1, customer.get(i).getName());
ps.setInt(2, customer.get(i).getAge());
ps.setDate (3, customer.get(i).getCreateDate());
}
@Override
public int getBatchSize() {
//ÅúÁ¿Ö´ÐеÄÊýÁ¿
return customer.size();
}
});
} |
ÅúÁ¿¸üУº
public void test(final List<Customer> customer)
{//²ÎÊýÒ²ÊǾֲ¿±äÁ¿£¬Ò²±ØÐëÓÃfinalÐÞÊΣ¬ ÄÚ²¿ÀàÖвÅÄÜ·ÃÎÊ£¨È«¾Ö±äÁ¿²»Óã©
String sql = "update test set name = ?,age
= ? where id = ?";
//·µ»ØµÄÊǸüеÄÐÐÊý
int[] count = jdbcTemplate.batchUpdate
(sql, new BatchPreparedStatementSetter(){
@Override
public void setValues (PreparedStatement ps, int
i)
throws SQLException {
//×¢Èë²ÎÊýÖµ
ps.setString(1, customer.get(i).getName());
ps.setInt(2, customer.get(i).getAge());
ps.setInt(3, customer.get(i).getId());
}
@Override
public int getBatchSize() {
//ÅúÁ¿Ö´ÐеÄÊýÁ¿
return customer.size();
}
});
} |
¡¡£¨c£©ÅúÁ¿´¦Àí¶àÌõÔ¤´¦ÀísqlÓï¾ä»¹ÓÐÏÂÃæ¼¸ÖÖ¼òµ¥·½·¨£¨²ÎÊýºÍÇ°ÃæÀàËÆ£¬ÕâÀï¾Í²»Ïê½â£©£º
int[] batchUpdate(String sql, List< Object[]> batchArgs)£»
int[] batchUpdate(String sql, List< Object[]> batchArgs, int[] argTypes)£»
Ç°Ãæ½²ÁËÔö¼Ó¡¢É¾³ý¡¢¸üвÙ×÷£¬Õâ½Ú½²Ò»Ï²éѯ¡£
²éѯ²Ù×÷£º
(Ò»)²éѯһ¸öÖµ£¨²»ÐèҪעÈë²ÎÊý£©
queryForObject(String sql, Class< T > requiredType)£»
×¢Ò⣺²ÎÊýrequiredTypeÖ»ÄÜÊÇString£¬IntegerÕâÖÖÀàÐÍ£¬²»ÄÜÊÇ×Ô¶¨ÒåµÄʵÌåÀàÐÍ£¬Ö»ÄÜ·µ»ØÒ»¸öÖµ£¬²»ÄÜÓ³Éä¶ÔÏó£¨Ó³Éä¶ÔÏóÏÂÃæ»á˵£©£» ¡¡sql:Ô¤´¦Àísql£»requiredType£º²éѯµ¥Áнá¹ûµÄÀàÐÍ£»
public void test()
{
String sql = "select count(*) from test";
int count = jdbcTemplate.queryForObject (sql, Integer.class);
} |
£¨¶þ£©²éѯһ¸öÖµ£¨Ê¹ÓÃÔ¤´¦Àísql£¬ÐèҪעÈë²ÎÊý£©
queryForObject(String sql, Object[] args, Class< T > requiredType)£»
public void test(Integer
id) {
String sql = "select name from test where
id = ?";
String name = jdbcTemplate.queryForObject (sql,
new Object[]{id}, String.class);
} |
»¹ÓÐÈçÏ·½Ê½£ºqueryForObject(String sql, Object[] args, int[] argTypes, Class< T > requiredType)£»
£¨Èý£©²éѯµ¥ÐмǼ£¬×ª»»³ÉÒ»¸ö¶ÔÏ󣨹̶¨sql£¬²»ÐèÒª²ÎÊý£©
< T > T queryForObject(String sql, RowMapper< T > rowMapper)
public void test()
{
String sql = "select name,age from test where
id = 10";
Customer customer = jdbcTemplate.queryForObject (sql,
new RowMapper<Customer>() {
@Override
public Customer mapRow (ResultSet rs, int i)
throws SQLException {
Customer c = new Customer();
c.setName(rs.getString("name"));
c.setAge(rs.getInt("age"));
return c;
}
});
} |
£¨ËÄ£©²éѯµ¥ÐмǼ£¬×ª»»³ÉÒ»¸ö¶ÔÏó£¨Ô¤´¦Àísql£¬ÐèҪעÈë²ÎÊý£©
< T > T queryForObject(String sql, Object[] args, RowMapper< T > rowMapper)
public void test(Integer
id)
{//²ÎÊýÒ²ÊǾֲ¿±äÁ¿£¬Ò²±ØÐëÓÃfinalÐÞÊΣ¬ ÄÚ²¿ÀàÖвÅÄÜ·ÃÎÊ£¨È«¾Ö±äÁ¿²»Óã©
String sql = "select name, age from test where
id = ?";
Customer customer = jdbcTemplate. queryForObject(sql,
new Object[]{id}, new RowMapper<Customer>()
{
@Override
public Customer mapRow (ResultSet rs, int paramInt)
throws SQLException {
Customer c = new Customer();
c.setName(rs.getString("name"));
c.setAge(rs.getInt("age"));
return c;
}
});
} |
Ò²¿ÉÒÔʹÓÃÈçÏ·½Ê½£º£¨1£©< T > T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper< T > rowMapper); £¨2£©< T > T queryForObject(String sql, RowMapper< T > rowMapper, Object... args);
£¨Î壩²éѯÊý¾Ý¿âÖÐÒ»ÁжàÐÐÊý¾Ý£¬¼´²éѯÊý¾Ý¿âÖе¥ÁÐÊý¾Ý´æÈëÒ»¸ölistÖУ¬·½Ê½ÈçÏ£º£¨¹Ì¶¨sql£¬Ã»²ÎÊý£© £¨a£©List< Map< String, Object >> queryForList(String sql)
Õâ¸ö·½·¨·â×°³Émap·ÅÈëlistÖУ¬key:ÁÐÃû£¨OracleÊý¾Ý¿âsqlÖ´Ðнá¹ûÁÐÃûĬÈÏΪ´óд£¬ÐèҪСдÓÃasÈ¡±ðÃû£¬±ðÃûÓÃË«ÒýºÅ£© value:ÁеÄÖµ
public void test()
{//Èç¹ûOracleÓÃÕâ¸ösql²éѯ£¬
·µ»ØµÄÁÐÃû¾ÍÊÇNAME(´óдµÄ)£¬ ¶ÔÓ¦MapÀïÃæµÄkey¾ÍÊÇNAME
String sql = "select name from test where
id > 0";
//Èç¹ûÓÃÕâ¸ösql²éѯ£¬·µ»ØµÄÁÐÃû ¾ÍÊÇname(СдµÄ)£¬¶ÔÓ¦MapÀïÃæµÄkey¾ÍÊÇname
String sql2 = "select name as \"name\"
from test where id > 0";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql); //ÕâÀïÓõÄÊǵÚÒ»¸ösql
} |
£¨b£©< T > List< T > queryForList(String sql, Class< T > elementType)
Õâ¸ö·½·¨¾ÍÊÇÖ±½Ó½«µ¥ÀàÐÍÊý¾Ý´æÈëListÖС£ ×¢Ò⣺Õâ¸öTËäÈ»ÊÇ·ºÐÍ£¬µ«ÊÇÖ»Ö§³ÖInteger.class String.class ÕâÖÖµ¥Êý¾ÝÀàÐ͵ģ¬×Ô¼º¶¨ÒåµÄBean²»Ö§³Ö¡££¨ËùÒÔÓÃÀ´²éѯµ¥ÁÐÊý¾Ý£©
public void test()
{
String sql = "select name from test where
id > 0";
List<String> list = jdbcTemplate. queryForList(sql,
String.class);
} |
£¨Áù£©²éѯÊý¾Ý¿âÖÐÒ»ÁжàÐÐÊý¾Ý£¬¼´²éѯÊý¾Ý¿âÖе¥ÁÐÊý¾Ý´æÈëÒ»¸ölistÖУ¬·½Ê½ÈçÏ£º£¨Ô¤´¦Àísql£¬ÐèҪעÈë²ÎÊý£© £¨a£©< T > List< T > queryForList(String sql, Object[] args, Class< T > elementType) ×¢Ò⣺Õâ¸öTËäÈ»ÊÇ·ºÐÍ£¬µ«ÊÇÖ»Ö§³ÖInteger.class String.class ÕâÖÖµ¥Êý¾ÝÀàÐ͵ģ¬×Ô¼º¶¨ÒåµÄBean²»Ö§³Ö¡££¨ËùÒÔÓÃÀ´²éѯµ¥ÁÐÊý¾Ý,Í¬Ç°ÃæÒ»¸öÒâ˼£¬ºóÃæ²»ÔÙ½âÊÍ£©
public void test(Integer
id) {
String sql = "select name from test where
id > ?";
List<String> list = jdbcTemplate. queryForList(sql,
new Object[]{id}, String.class);
} |
¡¡ »¹ÓÐÈçÏ·½Ê½ÊµÏÖ£º£¨1£©< T > List< T > queryForList(String sql, Object[] args, int[] argTypes, Class< T > elementType)£» £¨2£©< T > List< T > queryForList(String sql, Class< T > elementType, Object... args)£» £¨b£©List< Map< String, Object >> queryForList(String sql, Object... args) ¡¡·â×°³Émap´æÈëList,ºÍ֮ǰһÑù£¬Òª×¢ÒâOracleÊý¾Ý¿â·µ»ØµÄÁÐÃûĬÈÏÊÇ´óдµÄ£¬Èç¹ûÐèÒª£¬ÓñðÃû±äСд¡£
public void test(Integer
id) {
String sql = "select name from test where
id > ?";
List<Map<String, Object>> list = jdbcTemplate.queryForList (sql,
new Object[]{id});
} |
¡¡ »¹ÓÐÒ»ÖÖ·½Ê½ÊµÏÖ£ºList< Map< String, Object >> queryForList(String sql, Object[] args, int[] argTypes)£»
£¨Æß£©²éѯ¶àÌõÊý¾Ý£¨¹Ì¶¨sql£¬Ã»ÓвÎÊý£© £¨a£©< T > List< T > query(String sql, RowMapper< T > rowMapper) ¡¡Ã¿ÌõÊý¾ÝÓ³ÉäΪjava¶ÔÏ󣬷ÅÈëListÖС£
public void test()
{
String sql = "select name, age from test where
id > 10";
List<Customer> list = jdbcTemplate. query(sql,
new RowMapper<Customer>() {
@Override
public Customer mapRow (ResultSet rs, int rowNum)
throws SQLException {
//ÕâÀï±ØÐënew¶ÔÏ󣬲»ÄÜÔÚ·½·¨Íânew£¬ È»ºóÓÃͬһ¸ö£¬ÒòΪÊÇÒ»¸öList£¬ ²éѯ³öÀ´¶à¸ö¶ÔÏóÓ³É䣬
//±ØÐ뱣֤ÿһ´Îµ÷Óö¼Ê¹ÓÃÒ»¸öеġ£
//Èç¹û²»new£¬¶øÊÇʹÓÃͬһ¸ö¶ÔÏó£¬ »áµ¼Ö´æÈëµ½ListÖеͼÊÇÒ»ÑùµÄ¶ÔÏó £¨¶¼ÊÇ×îºóÒ»¸ö¶ÔÏ󣩡£
Customer customer = new Customer();
customer.setName(rs.getString("name"));
customer.setAge(rs.getInt("age"));
return customer;
}
});
} |
¸Ã·½·¨Ò²¿ÉÒÔ°ÑÿһÐÐÊý¾Ýת»»Îª×Ô¶¨Òåkey-valueµÄmap¶ÔÏó·ÅÈëlistÖУ¬ÈçÏ£º£¨ËùÒÔ˵ʹÓûص÷Àà±È¼òµ¥·½·¨¸üÇ¿´ó£¬ÀïÃæÂß¼×Ô¼º°´ÐèÇóд£©
public void test()
{
String sql = "select name,age from test where
id > 10";
List<Map<Integer,Object>> list = jdbcTemplate.query
(sql, new RowMapper<Map<Integer,Object>>(){
@Override
public Map<Integer,Object> mapRow(ResultSet
rs, int rowNum)
throws SQLException {
Map<Integer, Object> map = new HashMap<Integer,
Object>();
map.put(rowNum, rs.getString("name"));
map.put(rowNum, rs.getInt("age"));
return map;
}
});
} |
mapÖеÄkey£¬valueÀàÐ͸ù¾ÝÐèÒª×Ô¶¨Ò壬·Ç³£·½±ã¡£ÏñÕâÖÖʵÏÖRowMapper< T >½Ó¿ÚµÄÄäÃûÀ࣬T¿ÉÒÔΪMap£¬Ò²¿ÉÒÔΪ×Ô¶¨ÒåµÄ¶ÔÏóÀàÐÍ£¬ÈçÉÏÁ½ÖÖ£¬¸ù¾ÝÐèҪѡÔñ¡£ £¨b£© void query(String sql, RowCallbackHandler rch) ¡¡×¢Ò⣺Èç¹ûʹÓÃRowCallbackHandler »Øµ÷À࣬Õâ¸ö·½·¨ÊÇûÓзµ»ØÖµµÄ£¬¶øÊÇÔڻص÷ÀàÖн«½á¹û·ÅÈëÔ¤Ïȶ¨ÒåµÄListÖУ¬Ó÷¨ÈçÏ£º
public void test()
{
String sql = "select name, age from test where
id > 10";
//¾Ö²¿±äÁ¿£¬±ØÐëÓÃfinalÐÞÊΣ¬ ÄÚ²¿ÀàÖвÅÄÜ·ÃÎÊ£¨È«¾Ö±äÁ¿²»Óã©
final List<Customer> list = new ArrayList<Customer>();
jdbcTemplate.query (sql, new RowCallbackHandler()
{
@Override
public void processRow(ResultSet rs) throws SQLException
{
Customer customer = new Customer();
customer.setName(rs.getString("name"));
customer.setAge(rs.getInt("age"));
list.add(customer);
}
});
} |
µ±È»£¬ÕâÖÖ·½Ê½Ò²¿ÉÒÔת»»Îªmap£¬´æÈëlistÖУ¬ºÍÉÏÃæa·½Ê½Ò»Ñù£¬ÕâÀï¾Í²»Ïê½âÁË¡£ £¨c£©< T > T query(final String sql, final ResultSetExtractor< T > rse)
¡¡ResultSetExtractorʹÓûص÷·½·¨extractData(ResultSet
rs)Ìṩ¸øÓû§Õû¸ö½á¹û¼¯£¬ÈÃÓû§¾ö¶¨ÈçºÎ´¦Àí¸Ã½á¹û¼¯
public void test()
{
String sql = "select name,age from test where
id > 10";
List<Customer> list = jdbcTemplate.query (sql,
new ResultSetExtractor<List<Customer>>()
{
@Override
public List<Customer> extractData(ResultSet
rs)
throws SQLException, DataAccessException {
List<Customer> result = new ArrayList<Customer>();
while(rs.next()) {
Customer customer = new Customer();
customer.setName(rs.getString("name"));
customer.setAge(rs.getInt("age"));
result.add(customer);
}
return result;
}
});
} |
ͬÑùÒ²¿ÉÒÔת»»Îªmap¶ÔÏó·ÅÈëlistÖУ¬ÈçÏ£º
public void test()
{
String sql = "select name,age from test where
id > 10";
List<Map<String, Integer>> list
= jdbcTemplate.query (sql, new ResultSetExtractor <List<Map<String,
Integer>>>() {
@Override
public List<Map<String, Integer>>
extractData(ResultSet rs)
throws SQLException, DataAccessException {
List<Map<String, Integer>> result
= new ArrayList<Map<String, Integer>>();
while(rs.next()) {
Map<String, Integer> map = new HashMap<String,
Integer>();
map.put(rs.getString("name"), rs.getInt("age"));
result.add(map);
}
return result;
}
});
} |
£¨d£©< T > List< T > query(PreparedStatementCreator psc, RowMapper< T > rowMapper)
public void test()
{//¾Ö²¿±äÁ¿£¬
±ØÐëÓÃfinalÐÞÊΣ¬ÄÚ²¿ÀàÖвÅÄÜ·ÃÎÊ £¨È«¾Ö±äÁ¿²»Óã©
final String sql = "select name, age from
test where id > 10";
List<Customer> list = jdbcTemplate.query (new
PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection
conn)
throws SQLException {
PreparedStatement ps = conn.prepareStatement(sql);
//Èç¹ûsqlÊÇÔ¤´¦ÀíµÄ£¬ÐèÒª´«Èë²ÎÊý£¬ ¿ÉÒÔÔÚÕâÀïдjdbc´úÂë´«È룬ºóÃæ¾Í²»ÁоÙÕâÖÖ·½·¨ÁË
return ps;
}
}, new RowMapper<Customer>() {
@Override
public Customer mapRow(ResultSet rs, int rowNum)
throws SQLException {
Customer customer = new Customer();
customer.setAge(rs.getInt("age"));
customer.setName(rs.getString("name"));
return customer;
}
});
} |
¿ÉÒÔ½«RowMapper»»³ÉResultSetExtractor»òÕßRowCallbackHandler»Øµ÷À࣬ºÍÇ°ÃæÒ»Ñù£¬Òò´Ë»¹ÓÐÏÂÃæÁ½ÖÖ·½·¨£º £¨1£©void query(PreparedStatementCreator psc, RowCallbackHandler rch)£» £¨2£©< T > T query(PreparedStatementCreator psc, ResultSetExtractor< T > rse)£»
£¨°Ë£©²éѯ¶àÌõÊý¾Ý£¨Ô¤´¦Àísql£¬ÐèÒª´«Èë²ÎÊý£© £¨a£©< T > List< T > query(String sql, PreparedStatementSetter pss, RowMapper< T > rowMapper)
public void test(final
Integer id)
{//²ÎÊýÒ²ÊǾֲ¿±äÁ¿£¬Ò²±ØÐëÓÃfinalÐÞÊΣ¬ ÄÚ²¿ÀàÖвÅÄÜ·ÃÎÊ£¨È«¾Ö±äÁ¿²»Óã©
String sql = "select name, age from test where
id > ?";
List<Customer> list = jdbcTemplate. query(sql,
new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps)
throws SQLException {
ps.setInt(1, id);
}
}, new RowMapper<Customer>() {
@Override
public Customer mapRow (ResultSet rs, int rowNum)
throws SQLException {
Customer customer = new Customer();
customer.setName(rs.getString("name"));
customer.setAge(rs.getInt("age"));
return customer;
}
});
} |
ÓÃRowMapper»Øµ÷À໹ÓÐÈýÖÖ·½·¨£º ¡¡£¨1£©< T > List< T > query(String sql, Object[] args, int[] argTypes, RowMapper< T > rowMapper)£» ¡¡£¨2£©< T > List< T > query(String sql, Object[] args, RowMapper< T > rowMapper)£» ¡¡£¨3£©< T> List< T > query(String sql, RowMapper< T > rowMapper, Object... args)£»
¶øÈç¹û°Ñ»Øµ÷Àà»»³ÉResultSetExtractor»òÕßRowCallbackHandler»Øµ÷À࣬ÓÖÓаËÖÖ·½·¨£¨ÏñÉÏÃæÒ»Ñù¸÷ÓÐËÄÖÖ£©£¬ÕâÀï¾Í²»¾Ù³öÀ´ÁË£¬ºÍÇ°ÃæÓ÷¨Ò»Ö¡£
ʹÓÃSpringµÄJdbcTemplate»òÕßNamedParameterJdbcTemplate²éѯÊý¾Ý¿â£¬»ñÈ¡½á¹û£¬Êý¾Ý¿â±í×ֶκÍʵÌåÀà×Ô¶¯¶ÔÓ¦£¬¿ÉÒÔʹÓÃBeanPropertyRowMapper¡£
×¢Ò⣺ ¡¡×Ô¶¯°ó¶¨£¬ÐèÒªÁÐÃû³ÆºÍJavaʵÌåÀàÃû×ÖÒ»Ö£¬È磺ÊôÐÔÃû ¡°userName¡± ¿ÉÒÔÆ¥ÅäÊý¾Ý¿âÖеÄÁÐ×ֶΣ¨ÕâÀï˵µÄÁÐ×Ö¶ÎÊÇsqlÖ´Ðнá¹ûµÄÁÐÃû£¬Ò²¾ÍÊÇÈç¹ûÓбðÃû¾ÍÓñðÃû£¬£¨OracleĬÈÏÁÐÃû´óд£©£© "USERNAME" »ò ¡°user_name¡±¡£ÕâÑù£¬ÎÒÃǾͲ»ÐèÒªÒ»¸ö¸öÊÖ¶¯°ó¶¨ÁË£¬´ó´óÌá¸ßÁË¿ª·¢Ð§ÂÊ¡£
ÏÂÃæ½²½âBeanPropertyRowMapperÓ÷¨£º
BeanPropertyRowMapper< T > implements RowMapper< T >Õâ¸öÀàÊÇʵÏÖÁËRowMapper½Ó¿ÚµÄ£¬ËùÒÔ֮ǰ½²µÄ²éѯÖУ¬ÄÜÓõ½RowMapper»Øµ÷ÀàµÄ¶¼¿ÉÒÔÓÃBeanPropertyRowMapperÀ´½«½á¹ûÖ±½ÓÓ³ÉäΪʵÌåÀà¡£
public List<UserEntity>
findUser(UserEntity user) {
List<UserEntity> userList = jdbcTemplate.query (SEL_BY_USERNAME_PWD,
new Object[] { user.getUserName(), user.getPwd()
},
new BeanPropertyRowMapper<UserEntity> (UserEntity.class));
return userList;
} |
ÕýÈçÉÏÃæ£¬Ö±½ÓÀûÓÃBeanPropertyRowMapperµÄ¹¹Ôì·½·¨´«µÝÒ»¸öÐèÒªÓ³ÉäµÄÀàµÄclass¶ÔÏó½øÈ¥¼´¿ÉʵÏÖ£¬µ±È»±ØÐëÂú×ã֮ǰ˵µÄÒªÇó£º
1.ÊôÐÔÃû¡°userName¡±Òª°´ÈçϹæÔòÆ¥Åäsql½á¹ûÁУº½á¹ûÁÐÒªÊÇ"user_name"(´óСд¶¼ÐÐ)£¬ÒòΪBeanPropertyRowMapperµÄÖлὫ½á¹ûÁж¼×ªÎªÐ¡Ð´È¥ºÍ¶ÔÏóÖÐsetÊôÐÔ¶ÔÓ¦£»
2.ÊôÐÔÃû¡°user¡±Õâ¶ÔÓ¦½á¹ûÁÐΪ¡°USER¡±£¨´óСд¶¼ÐУ©£¬ÀíÓÉͬÉÏ£»
|