简单的生活,更少的期待,更多的付出。

spring data jpa的一些使用笔记

JPA全称为Java持久性API(Java Persistence API),Spring Data JPA是Spring Data中的一种JPA实现。
示例可见官方文档

JPA使用Oracle序列作为实体ID

1
@Entity
2
@Table(name="T_SPRINGJPA_USER")
3
public class User {
4
    /**
5
     * 主键序列:DEFAULT_SUQUENCE 是我在oracle数据库中创建的一个序列
6
     *           MY_SUQUENCE 是给自定义的序列随意创建一个引用名称
7
     * 指我的主键生成策略 MY_SUQUENCE 使用的是 DEFAULT_SUQUENCE 这个序列。
8
     */
9
    @SequenceGenerator(name = "MY_SUQUENCE", sequenceName = "DEFAULT_SUQUENCE")
10
    @Id
11
    @GeneratedValue(generator="MY_SUQUENCE")
12
    private Long id;
13
14
    @Column(name="USER_NAME")
15
    private String uName;
16
    
17
    //some get and set
18
}

调用存储过程

1
2
//Referencing explicitly mapped procedure with name "plus1inout" in database.
3
@Procedure("plus1inout")
4
Integer explicitlyNamedPlus1inout(Integer arg);
5
6
7
//Referencing implicitly mapped procedure with name "plus1inout" in database via procedureName alias.
8
@Procedure(procedureName = "plus1inout")
9
Integer plus1inout(Integer arg);
10
11
12
//Referencing explicitly mapped named stored procedure "User.plus1IO" in EntityManager.
13
@Procedure(name = "User.plus1IO")
14
Integer entityAnnotatedCustomNamedProcedurePlus1IO(@Param("arg") Integer arg);
15
16
17
//Referencing implicitly mapped named stored procedure "User.plus1" in EntityManager via method-name.
18
@Procedure
19
Integer plus1(@Param("arg") Integer arg);

JPA实现锁表

1
//Defining lock metadata on query methods
2
3
interface UserRepository extends Repository<User, Long> {
4
5
  // Plain query method
6
  @Lock(LockModeType.READ)
7
  List<User> findByLastname(String lastname);
8
}
9
10
11
12
//Defining lock metadata on CRUD methods
13
interface UserRepository extends Repository<User, Long> {
14
15
  // Redeclaration of a CRUD method
16
  @Lock(LockModeType.READ);
17
  List<User> findAll();
18
}

行级锁

1
import javax.persistence.LockModeType;  
2
import org.springframework.data.jpa.repository.JpaRepository;  
3
import org.springframework.data.jpa.repository.Lock;  
4
import org.springframework.data.jpa.repository.Query;  
5
import org.springframework.data.repository.query.Param;  
6
import org.springframework.stereotype.Repository;  
7
import com.xxx.xx.core.entity.UserInfo;  
8
@Repository  
9
public interface UserInfoDao extends JpaRepository<UserInfo, Long> {  
10
    @Query(value = "select j from UserInfo j where j.userName = :username ")  
11
    public UserInfo getUserForUpdate(@Param("username") String username);  
12
    @Lock(value = LockModeType.PESSIMISTIC_WRITE)  
13
    @Query(value = "select j from UserInfo j where j.id = :id ")  
14
    public void getUserByIdForUpdate(@Param("id") Long id);  
15
    @Lock(value = LockModeType.PESSIMISTIC_WRITE) // 会锁整个表
16
    @Query(value = "select j from UserInfo j where j.userName = :username ")  
17
    public void getUserByNameForUpdate(@Param("username") String username);  
18
}
19
20
21
import org.springframework.transaction.annotation.Transactional;  
22
  
23
public class UserService implements IUserService {  
24
    @Autowired  
25
    private UserInfoDao UserInfoDao;  
26
    @Transactional // 这个是需要标注的,因为Dao层有for update 的机制,那么这边就要开启事务了,否则会报错的。。。  
27
    public UserInfo getUserForUpdate(Long id) {  
28
        UserInfoDao.getUserByIdForUpdate(id);  
29
        try {  
30
            Thread.sleep(100000);  
31
        } catch (InterruptedException e) {  
32
        }  
33
        return null;  
34
    }  
35
}

需要明确的指定主键,才会执行行级锁,否则执行的为表锁。
MySQL中select * for update锁表的问题

悲观所和乐观锁问题,这里的乐观锁比较简单,jpa有提供注解@Version,加上该注解,自动实现乐观锁,byId修改的时候sql自动变成:update ... set ... where id = ? and version = ?,比较方便。

Oracle中,使用其他条件时,会在使用该数据集,如list时,锁表。形成多条锁表SQL语句。并不会直接表锁。

Oracle锁表,解锁的一些描述

锁表

增删改查,封锁粒度都为行级。
事务锁(TX)
| 语句|类型|模式|
| —–|——-|————|
|Insert|TX|排它(X)|
|Update|TX|排它(X)|
|Delete|TX|排它(X)|
|Select|TX|排它(X)|

通常的DML操作(SELECT…FOR UPDATE、INSERT、UPDATE、DELETE),在表级获得的只是意向锁(RS或RX),其真正的封锁粒度还是在行级;另外,Oracle数据库的一个显著特点是,在缺省情况下,单纯地读数据(SELECT)并不加锁,Oracle通过回滚段(Rollback segment)来保证用户不读”脏”数据。这些都提高了系统的并发程度。
由于意向锁及数据行上锁标志位的引入,减小了Oracle维护行级锁的开销,这些技术的应用使Oracle能够高效地处理高度并发的事务请求。
锁表粒度Oracle 数据封锁机制

解锁

oracle 进程锁死 解锁
su - oracle

telnet 到服务器(用户;密码都是 oracle)运行下面的命令:
sqlplus system/manager as sysdba
再执行下面的Sql代码
select sess.sid,sess.serial#,lo.oracle_username,lo.os_user_name,ao.object_name,lo.locked_mode from v$locked_object lo,dba_objects ao,v$session sess where ao.object_id = lo.object_id and lo.session_id = sess.sid;
杀掉锁表进程:
如有記錄則表示有lock,記錄下SID 和 serial# ,將記錄的ID替換下面的738,1429,即可解除LOCK
alter system kill session ‘738,1429’;

select distinct t2.username,
‘alter system kill session ‘’’ || t2.sid || ‘,’ ||
t2.serial# || ‘’’’ || ‘;’,
t3.object_name 被锁表名,
t4.spid 进程号,
t2.osuser os用户名,
t2.program 程序名
from v$locked_object t1
inner join v$session t2
on t1.session_id = t2.sid
inner join dba_objects t3
on t1.object_id = t3.object_id
inner join v$process t4
on t2.paddr = t4.addr;

常用JPA方法

查询

1、查询所有数据 findAll()

2、分页查询 findAll(new PageRequest(0, 2))

3、根据id查询 findOne()

4、根据实体类属性查询: findByProperty (type Property); 例如:findByAge(int age);

5、排序: findAll(sort )

1
Sort sort = new Sort(Sort.Direction.DESC, "age").and (new Sort(Sort.Direction.DESC, "id"));

6、条件查询 and/or/findByAgeLessThan/LessThanEqual 等,

例如: `findByUsernameAndPassword(String username , String password)`

7、总数 查询 count() 或者 根据某个属性的值查询总数countByAge(int age);

8、是否存在某个id exists()

修改,删除,新增

  1. 新增:直接使用 save(T) 方法
  2. 删除: delete() 或者 deleteByProperty 例如:deleteByAge(int age) ;
  3. 更新:
    1
    @Modifying 
    2
    @Query("update Customer u set u.age = ?1 where u.id = ?2")
    3
    int update(int age1 , long id);

官网其他示例,方法名内支持的关键字

方法名内支持的关键字

KeywordSampleJPQL snippet
AndfindByLastnameAndFirstname… where x.lastname = ?1 and x.firstname = ?2
OrfindByLastnameOrFirstname… where x.lastname = ?1 or x.firstname = ?2
Is,EqualsfindByFirstname,findByFirstnameIs,findByFirstnameEquals… where x.firstname = 1?
BetweenfindByStartDateBetween… where x.startDate between 1? and ?2
LessThanfindByAgeLessThan… where x.age &lt; ?1
LessThanEqualfindByAgeLessThanEqual… where x.age &#8656; ?1
GreaterThanfindByAgeGreaterThan… where x.age &gt; ?1
GreaterThanEqualfindByAgeGreaterThanEqual… where x.age &gt;= ?1
AfterfindByStartDateAfter… where x.startDate &gt; ?1
BeforefindByStartDateBefore… where x.startDate &lt; ?1
IsNullfindByAgeIsNull… where x.age is null
IsNotNull,NotNullfindByAge(Is)NotNull… where x.age not null
LikefindByFirstnameLike… where x.firstname like ?1
NotLikefindByFirstnameNotLike… where x.firstname not like ?1
StartingWithfindByFirstnameStartingWith… where x.firstname like ?1 (parameter bound with appended %)
EndingWithfindByFirstnameEndingWith… where x.firstname like ?1 (parameter bound with prepended %)
ContainingfindByFirstnameContaining… where x.firstname like ?1 (parameter bound wrapped in %)
OrderByfindByAgeOrderByLastnameDesc… where x.age = ?1 order by x.lastname desc
NotfindByLastnameNot… where x.lastname &lt;&gt; ?1
InfindByAgeIn(Collection&lt;Age&gt; ages)… where x.age in ?1
NotInfindByAgeNotIn(Collection&lt;Age&gt; age)… where x.age not in ?1
TruefindByActiveTrue()… where x.active = true
FalsefindByActiveFalse()… where x.active = false
IgnoreCasefindByFirstnameIgnoreCase… where UPPER(x.firstame) = UPPER(?1)
-------------本文结束感谢您的阅读-------------
谢谢大爷打赏,常来玩啊