//更新
@Transactional
@Modifying
@Query(value = "UPDATE dic SET name =?1, type = ?2, code = ?3, comment = ?4, create_time = ?5 WHERE id = ?6" , nativeQuery = true)
public int upDataManager(String name, String type, String code, String comment, String time,Integer managerId);
或saveandflush
sql添加一个用户
GRANT all on test.* to test@"%" IDENTIFIED by "123456";
flush PRIVILEGES
查询两张表
SELECT
info.id ,
info.app_id ,
info.token ,
info.encoding_aes_key ,
info.create_time ,
info.original_id ,
d.`name` AS 'wx_type'
FROM
public_users_info AS info ,
dic AS d
WHERE
info.type = d. CODE
AND d.type = 'wx_type'
AND d. CODE = 1
模糊查询
- SELECT * from public_users_info a left join public_users b on a.public_user_id=b.id left join dic c on a.type=c.`code`
where (a.id like '%1%' or a.`name` LIKE '%1%' or a.app_id like '%1%' or a.app_secret like '%1%') ORDER BY a.id
- SELECT * from public_users_info a left join public_users b on a.public_user_id=b.id
where (a.id like '%1%' or a.`name` LIKE '%1%' or a.app_id like '%1%' or a.app_secret like '%1%') ORDER BY a.id
- SELECT * from public_users_info a where (a.id like '%1%' or a.`name` LIKE '%1%' or a.app_id like '%1%' or a.app_secret like '%1%') ORDER BY a.id
坑:sql语句中的《*》很耗时,尽量不用
//分页查询
select *from table limit size*page, size
或者用pageable
分页查询