jpa query oracle 参数int为空_Spring JPA/H2/Oracle的实现

Set data source

DataSource bean will be created automatically. Springboot 1 find Datasource class as below order.

Tomcat pool -->> HikariCP -->>  Commons DBCP -->>  Commons DBCP2

But SpringBoot 2 find DataSource class as below order. It means SpringBoot 2 use HikariCP as default, since HikariCP is the fastest connection pool.

HikariCP -->>  Tomcat pool -->>  Commons DBCP2

The automatically created bean use configuration in application.yml like this.

spring:  datasource:    driver-class-name: com.mysql.jdbc.Driver    type: com.zaxxer.hikari.HikariDataSource    username: root    password: root    url: jdbc:mysql://localhost:3306/persontest?useSSL=false&characterEncoding=utf8    hikari:      autoCommit: true      connectionTimeout: 30000      idleTimeout: 600000      maxLifetime: 1800000      maximumPoolSize: 10

Or

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xespring.datasource.username=systemspring.datasource.password=passwordspring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriverspring.datasource.dbcp2.initial-size=7spring.datasource.dbcp2.max-total=20spring.datasource.dbcp2.pool-prepared-statements=true

You also can create DataSource bean manually.

Access data in DB

You can do it by below approaches.

1. use @PersistenceContext with EntityManager, then use EntityManager access data in DB.

@Repository@Transactionalpublic class SpitterRepositoryImpl implements SpitterRepository {    @PersistenceContext    private EntityManager em;        public void addSpitter(Spitter spitter){        em.persist(spitter);    }        public Spitter getSpitterById(long id){        return em.find(Spitter.class, id)    }}

2. extend JpaRepository, use function signature, or create function with @Query

public interface JpaSpitterRepository extends JpaRepository<Spitter, Long> {    Spitter findByUsername(String username);    ListfindByBreedIn(List breed);    int countByDiscontinuedTrue();    ListfindByShippingDateBetween(Date start, Date end);        @Query("select s from Spitter s where s.email like '%gmail.com'")    ListfindAllGmailSpitters();}

3. Mix below two approaches to implement more flexible features.

public interface JpaSpitterRepository extends JpaRepository<Spitter, Long>,                        SpitterRepository {     Spitter findByUsername(String username);                                               }

Use H2 DB with SpringBoot

1. add dependency in pom.xml

<dependency>    <groupId>com.h2databasegroupId>    <artifactId>h2artifactId>dependency>

2. configure datasource and h2 in application.yml

# Database Settingsspring:  datasource:    url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE    platform: h2    username: sa    password:    driverClassName: org.h2.Driver  jpa:    database-platform: org.hibernate.dialect.H2Dialect    hibernate:      ddl-auto: update    properties:      hibernate:        show_sql: true        use_sql_comments: true        format_sql: true# H2 Settings  h2:    console:      enabled: true      path: /h2-console      settings:        trace: false        web-allow-others: false

3. add data.sql in the src/main/resources, H2 will automatically use the script to populate H2 database.

see detail here https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html

4. start the application by mvn spirng-boot:run then you can access H2 web console http://localhost:8080/h2-console , you can use it to query or modify data in H2 DB.

Pay attention, H2 console will be forbidden by Spring Security anyway. Need some specific configuration, please see Using Spring Security to implement AAA.

Use Oracle DB with SpringBoot

1. make sure pom.xml include jpa dependency.

<dependency>    <groupId>org.springframework.bootgroupId>    <artifactId>spring-boot-starter-data-jpaartifactId>dependency>

2. download oracle ojdbc jar and then add it under WEB-INF/lib and define it in our classpath.

3. configure oracle datasource in application.properties

# create and drop tables and sequences, loads import.sqlspring.jpa.hibernate.ddl-auto=create-drop# Oracle settingsspring.datasource.url=jdbc:oracle:thin:@localhost:1522:orclspring.datasource.username=HIBERNATE_TESTspring.datasource.password=HIBERNATE_TESTspring.datasource.driver.class=oracle.jdbc.driver.OracleDriver

4. implement Entity classes with @Entity, @Id, @Column, @ManyToOne, @OneToMany, below is two Entity classes.

@Entitypublic class Player {    @Id    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "player_Sequence")    @SequenceGenerator(name = "player_Sequence", sequenceName = "PLAYER_SEQ")    private Long id;    @Column(name = "name")    private String name;    @Column(name = "num")    private int num;    @Column(name = "position")    private String position;    @ManyToOne(fetch = FetchType.LAZY)        @JoinColumn(name = "team_id", nullable = false)        private Team team;    public Player() {    }        // getters/setters}

and

@Entitypublic class Team {    @Id    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "team_Sequence")    @SequenceGenerator(name = "team_Sequence", sequenceName = "TEAM_SEQ")    private Long id;    @Column(name = "name")    private String name;    @OneToMany(cascade = CascadeType.ALL,            fetch = FetchType.EAGER,            mappedBy = "team")    private List players;    public Team() {    }        // getters/setters}

5. define import.sql under src/main/resources in order to fill our tables with static data

insert into Team (id,name) values(1,'Barcelona');insert into Player (id, team_id, name, num, position) values(1,1,'Lion', 10, 'Forw');insert into Player (id, team_id, name, num, position) values(2,1,'Andr', 8, 'Midfi');insert into Player (id, team_id, name, num, position) values(3,1,'Pique', 3, 'Defen');

6. define repositories for entities.

@Repositorypublic interface PlayerRepository extends CrudRepository<Player, Long> {    ListfindByTeamId(long teamId);}

@Repositorypublic interface TeamRepository extends CrudRepository<Team, Long> {    Team findByPlayers(long playerId);}

7. then you can use repository in your service class

@Servicepublic class SoccerServiceImpl implements SoccerService {    @Autowired    private PlayerRepository playerRepository;    @Autowired    private TeamRepository teamRepository;    public List<String> getAllTeamPlayers(long teamId) {        List<String> result = new ArrayList<String>();        List players = playerRepository.findByTeamId(teamId);        for (Player player : players) {            result.add(player.getName());        }        return result;    }    public void addBarcelonaPlayer(String name, String position, int number) {        Team barcelona = teamRepository.findOne(1l);        Player newPlayer = new Player();        newPlayer.setName(name);        newPlayer.setPosition(position);        newPlayer.setNum(number);        newPlayer.setTeam(barcelona);        playerRepository.save(newPlayer);    }}

Connect on-premise Oracle DB from PCF App

1. Define JDBC Connection String like this format

jdbc:oracle:thin:DBUSER/DBPASSWORD@HOST:PORT/SERVICE_NAME

2. Creating a User Provided Service

cf create-user-provided-service SERVICE_INSTANCE_NAME -p '{"jdbcUrl": "our-refactored-jdbc-connection-string"}'cf bind-service APP_NAME SERVICE_INSTANCE_NAMEEee

3. Add the Oracle JDBC Driver and Spring Cloud Connector dependencies to your pom.xml

<dependency>    <groupId>org.springframework.bootgroupId>    <artifactId>spring-boot-starter-cloud-connectorsartifactId>    <version>2.0.5.RELEASEversion>dependency> <dependency>    <groupId>com.oraclegroupId>    <artifactId>ojdbc8artifactId>    <version>12.2.0.1.0version>dependency>

4. build and then cf push your App to PCF, it should connect to Oracle DB now.

END