å 为工ä½éè¦ï¼æ以å¦ä¹ ä¸ä¸JdbcTemplateçç¨æ³ã
JdbcTemplateçåºæ¬ç¨æ³å¯ä»¥åèæä¹åçä¸ç¯å客 https://blog.csdn.net/cwr452829537/article/details/84562951
1ãåå¤å·¥ä½
è¿éæ们å åå¤ä¸å¼ ç¨æ·è¡¨
CREATE TABLE `t_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`gender` int(3) NOT NULL,
`phone` varchar(50) NOT NULL,
`note` text,
`status` tinyint(4) NOT NULL,
`created_ts` datetime NOT NULL,
`last_modified_ts` datetime DEFAULT NULL,
`creator` bigint(20) NOT NULL,
`editor` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4;
INSERT INTO t_user VALUES(1, 'å¼ ä¸', 2, '17565485695', 'å¦ç', 1, '2018-12-12 00:00:00.000', NULL, 1001, NULL);
INSERT INTO t_user VALUES(2, 'æå', 1, '13654865859', 'ä¸çæ', 1, '2018-12-12 00:00:00.000', NULL, 1001, NULL);
INSERT INTO t_user VALUES(3, 'çäº', 1, '13546652858', 'çé¿', 1, '2018-12-12 00:00:00.000', NULL, 1001, NULL);
INSERT INTO t_user VALUES(4, 'èµµå
', 2, '15856548565', NULL, 1, '2018-12-12 00:00:00.000', NULL, 1001, NULL);
INSERT INTO t_user VALUES(5, 'Tom', 1, '19846515616', 'student', 1, '2018-12-12 00:00:00.000', NULL, 1001, NULL);
INSERT INTO t_user VALUES(6, 'R', 1, '13654585654', 'Super', 1, '2018-12-12 00:00:00.000', NULL, 1001, NULL);
2ãæ°æ®æ¥è¯¢
æ¥è¯¢å个åºæ¬æ°æ®ç±»å(å¦String,Integer,Longç)åå个对象å¯ä»¥ä½¿ç¨jdbcTemplate.queryForObject()æ¹æ³ï¼æ¥è¯¢åºæ¬æ°æ®ç±»åå表å¯ä»¥ä½¿ç¨jdbcTemplate.queryForList()æ¹æ³ï¼æ¥è¯¢å¯¹è±¡å表å¯ä»¥ä½¿ç¨jdbcTemplate.query()æè jdbcTemplate.queryForList()
/**
* åå§åJdbcTemplate
* @return
*/
public JdbcTemplate init() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/java");
dataSource.setUsername("root");
dataSource.setPassword("root");
return new JdbcTemplate(dataSource);
}
ï¼ä¸ï¼æ¥è¯¢åºæ¬æ°æ®ç±»å(String,Integer,Longç)
     æ¥è¯¢å个(å¦æ¥è¯¢æå®æ§å«çç¨æ·æ°é)
     public <T> T queryForObject(String sql, Object[] args, Class<T> requiredType)
     åæ°åå«æ¯sqlï¼sqlåæ°æ°ç»ï¼è¿åæ°æ®ç±»å(è¿éæ们就è¿åInteger)   Â
/**
* æ¥è¯¢æå®æ§å«ç¨æ·æ°é
* @param gender æ§å«ï¼1ï¼ç·ï¼2ï¼å¥³
* @return
*/
@Override
public Integer countUsers(Integer gender) {
// åå§åJdbcTemplate
JdbcTemplate jdbcTemplate = init();
StringBuilder sql = new StringBuilder();
sql.append(" SELECT ")
.append(" COUNT(*) ")
.append(" FROM ")
.append(" t_user ")
.append(" WHERE gender = ?");
try {
return jdbcTemplate.queryForObject(sql.toString(), new Object[]{gender}, Integer.class);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
 æ¥è¯¢å表(å¦æ¥è¯¢æå®æ§å«çç¨æ·idéå)
 public <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType)
 åæ°åå«æ¯sqlï¼sqlåæ°æ°ç»ï¼è¿åæ°æ®ç±»å(è¿éæ们就è¿åInteger)   Â
/**
* æ¥è¯¢æå®æ§å«çç¨æ·idéå
* @param gender æ§å«
* @return
*/
@Override
public List<Integer> listIds(Integer gender) {
JdbcTemplate jdbcTemplate = init();
StringBuilder sql = new StringBuilder();
sql.append(" SELECT ")
.append(" id ")
.append(" FROM ")
.append(" t_user ")
.append(" WHERE gender = ?");
try {
return jdbcTemplate.queryForList(sql.toString(), new Object[]{gender}, Integer.class);
} catch (Exception e) {
return null;
}
}
ï¼äºï¼æ¥è¯¢å¯¹è±¡
    æ¥è¯¢å个(å¦æ¥è¯¢æå®idçç¨æ·ä¿¡æ¯)
    public <T> T queryForObject(String sql, @Nullable Object[] args, RowMapper<T> rowMapper)
    åæ°åå«æ¯sqlï¼sqlåæ°æ°ç»(å¯ä¸ºç©º)ï¼å¯¹è±¡æ å°å ³ç³»
/**
* æ¥è¯¢å个ç¨æ·(éè¿jdbctemplateæ¥è¯¢)
*
* @param id ç¨æ·id
* @return
*/
@Override
public UserEntity getOneByJdbc(Long id) {
UserEntity userEntity;
JdbcTemplate jdbcTemplate = init();
StringBuilder sql = new StringBuilder();
sql.append(" SELECT ")
.append(" id,name,gender,phone,note ")
.append(" FROM ")
.append(" t_user ")
.append(" WHERE ")
.append(" id = ? ");
try {
userEntity = jdbcTemplate.queryForObject(sql.toString(), new Object[]{id}, (rs, rowNum) -> {
UserEntity temp = new UserEntity();
temp.setId(rs.getLong("id"));
temp.setName(rs.getString("name"));
temp.setGender(rs.getInt("gender"));
temp.setPhone(rs.getString("phone"));
temp.setNote(rs.getString("note"));
return temp;
});
} catch (Exception e) {
return null;
}
return userEntity;
}
 æ¥è¯¢å表(æ¥è¯¢ææç¨æ·å表ï¼å«å页)
 public <T> List<T> query(String sql, @Nullable Object[] args, RowMapper<T> rowMapper)
 åæ°åå«æ¯sqlï¼sqlåæ°æ°ç»(å¯ä¸ºç©º)ï¼å¯¹è±¡æ å°å ³ç³»
/**
* æ¥è¯¢ç¨æ·å表
*
* @param pageable
* @return
*/
@Override
public Page<UserEntity> listUsersByJdbc(Pageable pageable) {
JdbcTemplate jdbcTemplate = init();
StringBuilder sql = new StringBuilder();
sql.append(" SELECT ")
.append(" id,name,gender,phone,note ")
.append(" FROM ")
.append(" t_user ")
.append(" LIMIT ?,? ");
try {
List<UserEntity> list = jdbcTemplate.query(sql.toString(),
new Object[]{pageable.getPageNumber(), pageable.getPageSize()},
new BeanPropertyRowMapper<>(UserEntity.class)
);
return new PageImpl<>(list, pageable, list.size());
} catch (Exception e) {
return null;
}
}
æ»ç»
- jdbcTemplate.queryForObject()æ¹æ³å¦ææ¥è¯¢ç»æ为空æè ä¸å¯ä¸çæ¶åä¼æåºå¼å¸¸ï¼å¯ä»¥åæä¸æ ·try...catchèµ·æ¥è¿ånullæè æåºç©ºå¼å¸¸ã
Â
Â
Â
Â