Äú¿ÉÒÔ¾èÖú£¬Ö§³ÖÎÒÃǵĹ«ÒæÊÂÒµ¡£

1Ôª 10Ôª 50Ôª





ÈÏÖ¤Â룺  ÑéÖ¤Âë,¿´²»Çå³þ?Çëµã»÷Ë¢ÐÂÑéÖ¤Âë ±ØÌî



  ÇóÖª ÎÄÕ ÎÄ¿â Lib ÊÓÆµ iPerson ¿Î³Ì ÈÏÖ¤ ×Éѯ ¹¤¾ß ½²×ù Model Center   Code  
»áÔ±   
   
 
     
   
 ¶©ÔÄ
  ¾èÖú
JdbcTemplateÍêȫѧϰ
 
×÷ÕߣºÏÂÒ»Õ¾ÌìÁÁÁË
  2020  次浏览      27
 2020-3-24
 
±à¼­ÍƼö:
±¾ÎÄÖ÷Òª½²½â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¡±£¨´óСд¶¼ÐУ©£¬ÀíÓÉͬÉÏ£»

 
   
2020 ´Îä¯ÀÀ       27
Ïà¹ØÎÄÕÂ

Java΢·þÎñÐÂÉú´úÖ®Nacos
ÉîÈëÀí½âJavaÖеÄÈÝÆ÷
JavaÈÝÆ÷Ïê½â
Java´úÂëÖÊÁ¿¼ì²é¹¤¾ß¼°Ê¹Óð¸Àý
Ïà¹ØÎĵµ

JavaÐÔÄÜÓÅ»¯
Spring¿ò¼Ü
SSM¿ò¼Ü¼òµ¥¼òÉÜ
´ÓÁ㿪ʼѧjava±à³Ì¾­µä
Ïà¹Ø¿Î³Ì

¸ßÐÔÄÜJava±à³ÌÓëϵͳÐÔÄÜÓÅ»¯
JavaEE¼Ü¹¹¡¢ Éè¼ÆÄ£Ê½¼°ÐÔÄܵ÷ÓÅ
Java±à³Ì»ù´¡µ½Ó¦Óÿª·¢
JAVAÐéÄâ»úÔ­ÀíÆÊÎö