nal Logger logger = LoggerFactory.getLogger(JdbcService.class);
@Resource
private JdbcTemplate jdbcTemplate ;
/**
* 添加数据
*/
public int addData (User user){
return jdbcTemplate.update(
"INSERT INTO `tb_user` (`user_name`, `email`, `phone`, `create_time`, `update_time`) VALUES (?, ?, ?, ?, ?)",
user.getUserName(),user.getEmail(),user.getPhone(),user.getCreateTime(),user.getUpdateTime());
}
/**
* 查询全部
*/
public List<User> queryAll (){
return jdbcTemplate.query("SELECT * FROM tb_user WHERE state=1",new BeanPropertyRowMapper<>(User.class));
}
/**
* 修改字段
*/
public int updateName (Integer id,String name){
return jdbcTemplate.update("UPDATE `tb_user` SET `user_name` = ? WHERE `id` = ?",name,id);
}
/**
* 主键删除
*/
public int deleteId (Integer id){
return jdbcTemplate.update("DELETE FROM `tb_user` WHERE `id` = ?",id);
}
}
五、MybatisPlus框架
1、配置管理
1.1 配置类
在配置类中，添加MapperScan注解用来扫描和注册MyBatis框架的mapper接口，以及设置PaginationInnerInterceptor分页拦截器；
@Configuration
@MapperScan("com.boot.jdbc.mapper")
public class MybatisConfig {
/**
* 分页
*/
@Bean
public MybatisPlusInterceptor paginationInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
1.2 配置文件
在日志中输出mybatis框架解析的SQL语句，方便在测试的时候快速发现问题；
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
2、Mapper
Mapper接口编写数据库操作方法，Mapper.xml文件中定义数据库执行的SQL语句，在mybatis-plus组件中提供很多单表操作的默认方法实现，也可以自定义方法；
2.1 Mapper接口
public interface UserMapper extends BaseMapper<User> {
/**
* 自定义分页
*/
IPage<UserModel> queryUserPage(@Param("page") IPage<User> page);
}
2.2 Mapper文件
<mapper namespace="com.boot.jdbc.mapper.UserMapper">
<select id="queryUserPage" resultType="com.boot.jdbc.entity.UserModel">
SELECT
tb1.id userId,
tb1.user_name userName,
tb1.email,
tb1.phone,
tb1.create_time createTime,
tb1.update_time updateTime,
tb1.state,
tb2.school,
tb2.city_name cityName
FROM tb_user tb1
LEFT JOIN tb_user_extd tb2 ON tb1.id = tb2.user_id
WHERE tb1.state='1'
ORDER BY tb1.id DESC
</select>
</mapper>
3、单元测试
编写UserMapper接口测试，很多默认实现的方法参考BaseMapper接口即可，或者参考IService接口和ServiceImpl实现类，提供了更加丰富的扩展方法；
public class UserMapperTest {
@Resource
private UserMapper userMapper ;
@Test
public void testInsert (){
List<User> userBatch = Arrays.asList(
new User(null,"Zhang三","Zhang@qq.com","18623459687",new Date(),new Date(),1));
userBatch.forEach(userMapper::insert);
}
@Test
public void testUpdate (){
User user = userMapper.selectById(1);
user.setState(2);
userMapper.updateById(user);
}
@Test
public void testDelete (){
userMapper.deleteById(7);
}
@Test
public void testQuery (){
List<User> userColumnsList = new LambdaQueryChainWrapper<>(userMapper)
.select(User::getUserName,User::getPhone,User::getEmail)
.like(User::getPhone,"189").orderByDesc(User::getId).last("limit 2").list();
userColumnsList.forEach(System.out::println);
}
@Test
public void testP