public interface UserRepository extends JpaRepository<UserEntity, Long> {
UserEntity findByAccountAndPassword(String account, String password);
@Query(value = "select * from user u where u.account like ?1", nativeQuery = true)
List<UserEntity> userlist(String account);
List<UserEntity> findByAccountContaining(String account);
//多表查询可以不用实体返回
@Query(value = "select t.*,u.family,u.frirends,u.job,u.school from mydb.user t left join mydb.userinfo u on t.id=u.id", nativeQuery = true)
List<Map<String,Object>> selecttwo();
}
@Service("UserService")
public class UserService implements UserInterface {
@Autowired
private UserRepository userRepository;
//查
public UserEntity GetEntityByPassword(String account, String password) {
return userRepository.findByAccountAndPassword(account, password);
}
//增
//@Transactional(rollbackOn = Exception.class)//开启事务,报错回滚
public boolean Register(UserEntity userEntity) {
boolean res = false;
if (!userEntity.getAccount().isEmpty() && !userEntity.getPassword().isEmpty()) {
String pass = DigestUtils.md5DigestAsHex(userEntity.getPassword().getBytes());
userEntity.setPassword(pass);
userEntity.setId(BaseController.getUUID());
userRepository.save(userEntity);
res = true;
}
return res;
}
//查
public List<UserEntity> userlist(String account){
System.out.println("这里在读数据库");
return userRepository.userlist(account);
}
//增
public UserEntity Adduser(UserEntity userEntity){
userRepository.save(userEntity);
return userEntity;
}
//分页查
public List<UserEntity> pagelist(Integer page, Integer size){
Sort sort=new Sort(new Sort.Order(Sort.Direction.ASC,"account"));
//构造分页对象
Pageable pageable =new PageRequest(page,size,sort);
Page<UserEntity> pageusers= userRepository.findAll(pageable);
List<UserEntity> pagelist=pageusers.getContent();
long total=pageusers.getTotalElements();
return pagelist;
}
//多表查
public List<Map<String,Object>> selecttwo(){
return userRepository.selecttwo();
}
}
@Mapper
public interface UserMaper {
public List<UserEntity> Sel();
public UserEntity Getbyid(String id);
public List<UserEntity> Getbyword(String word);
public void insertdata(UserEntity userEntity);
public void updatedata(UserEntity userEntity);
public List<Viewentity> selecttwo();
public List<Map<String,Object>> selecttwobymap();
}
xml代码
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.demo.mapper.UserMaper">
<select id="Sel" resultType="UserEntity">
select * from user
</select>
<select id="Getbyid" resultType="UserEntity">
select * from user where id=#{id}
</select>
<!--传入string类型的参数时用_parameter代替参数名-->
<select id="Getbyword" resultType="UserEntity">
select * from user
<where>/*where放这里可以保证特殊条件如第一个条件不满足第二个条件时将and 自动去掉,不过我们之前会在where后面加1=1这样就不会错了。*/
/*相当于case when default*/
<choose>
<when test="_parameter=='haha'">
account like #{_parameter}
</when>
<when test="_parameter=='长庚'">
and name=#{_parameter}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>
<insert id="insertdata">
insert user (id,name,address,phone,account)values (#{id},#{name},#{address},#{phone},#{account})
</insert>
<update id="updatedata">
update user
<set>
name=#{name},address=#{address},phone=#{phone},account=#{account}
</set>
where id=#{id}
</update>
<select id="selecttwo" resultType="Viewentity">
select t.*,u.family,u.frirends,u.job,u.school from mydb.user t left join mydb.userinfo u on t.id=u.id
</select>
<select id="selecttwobymap" resultType="Map">
select t.*,u.family,u.frirends,u.job,u.school from mydb.user t left join mydb.userinfo u on t.id=u.id
</select>
</mapper>
控制器代码
@RestController
@Slf4j
public class TestController {
@Autowired
private UserInterface userInterface;
@Autowired
private UserMaper userMaper;
@RequestMapping("get/testcache")
public List<UserEntity> findone(String account) {
if (account == null) {
account = "haha";
}
return userInterface.userlist(account);
}
@RequestMapping("get/adduser")
public UserEntity Adduser() {
UserEntity userEntity = new UserEntity();
userEntity.setId("1234");
userEntity.setAccount("haha");
userInterface.Adduser(userEntity);
return userEntity;
}
@RequestMapping("get/getcachelist")
public List<UserEntity> findcachelist(String account) {
Jedis jedis = new Jedis("localhost", 6379);
jedis.connect();
Set<String> value = jedis.keys("users");
jedis.disconnect();
return userInterface.userlist(account);
}
@RequestMapping("get/savestring")
@Cacheable("user_string")
public String savestring() {
String value = "dfsdfhesrwefsvdsdvivkdfifasndfhfi";
return value;
}
/*测试使用mybatis连接数据
* mybatis持久化框架
* */
@RequestMapping("get/mybatisdata")
public List<UserEntity> get() {
return userMaper.Sel();
}
@RequestMapping("get/mybatisuser")
public UserEntity getbyid(String id) {
return userMaper.Getbyid(id);
}
@RequestMapping("get/getbyword")
public List<UserEntity> getbyword(String word) {
return userMaper.Getbyword(word);
}
@RequestMapping("get/insert")
public String insertdata() {
UserEntity user=new UserEntity();
user.setId(UUID.randomUUID().toString());
user.setAccount("ceshibatis");
user.setAddress("奥尔良");
user.setName("新宿");
user.setPhone("13778790989");
userMaper.insertdata(user);
return "保存成功!";
}
@RequestMapping("get/update")
public String updatedata(String id) {
UserEntity user=new UserEntity();
user.setId(id);
user.setAccount("update");
user.setAddress("update奥尔良");
user.setName("update新宿");
user.setPhone("13778790989");
userMaper.updatedata(user);
return "更新成功!";
}
@RequestMapping("get/gettwo")
public List<Viewentity> gettwo() {
return userMaper.selecttwo();
}
@RequestMapping("get/gettwobymap")
public List<Map<String,Object>> gettwobymap() {
return userMaper.selecttwobymap();
}
@RequestMapping("get/gettwobyjpa")
public List<Map<String,Object>> gettwobyjpa() {
return userInterface.selecttwo();
}
}