问:
Should I index my foreign keys if they are already part of a unique constraint?
In Postgresql, I have a table with a unique constraint on two foreign keys like below:

```<code>CREATE TABLE project_ownerships(
  id BIGSERIAL  PRIMARY KEY,
  project_id BIGINT REFERENCES projects ON DELETE CASCADE,
  user_id BIGINT REFERENCES users ON DELETE CASCADE,
  role SMALLINT,
  CONSTRAINT project_user_unique UNIQUE (project_id, user_id)
);
</code>```

Having set a unique constraint on the two foreign keys project_id and user_id, does psql automatically create an index for each of them too? Or am I supposed to manually create an index for them still? Like below:

```<code>CREATE TABLE project_ownerships(
  id BIGSERIAL  PRIMARY KEY,
  project_id BIGINT REFERENCES projects ON DELETE CASCADE,
  user_id BIGINT REFERENCES users ON DELETE CASCADE,
  role SMALLINT,
  CONSTRAINT project_user_unique UNIQUE (project_id, user_id)
);

CREATE INDEX po_project_id_idx ON project_ownerships (project_id);
CREATE INDEX po_user_id_idx ON project_ownerships (user_id);
</code>```

I have read the text here, just wanted to make sure I understood it correctly in terms of actual implementation details. Specifically, with a composite index created-CONSTRAINT project_user_unique UNIQUE (project_id, user_id)- will Postgresql be able to execute an index scan(if deemed necessary) when I'm doing a join on project_id, user_id or both? Do I still need to separately create an index for each of the foreign keys?

答:
It is a matter of taste, but personally I don't think you need the surrogate key (id) here. (is it ever used?) Also: role is a (non reserved) keyword. Avoid using it as identifier.

For the foreign keys, an index is absolutely necessary, otherwise a CASCADEing delete or update would (internally) lead to a sequential scan for every deleted/updated user- or project- tuple.

For a junction (bridge) table like this, it is sufficient to create an index (or UNIQUE constraint) with the key elements in the reversed order. This also serves as a supporting index for the FK. [the first element(s) of a composite index can be used as if an index with only these fields existed]

The extra key field in the index can enable index-only scans (for instance: when the the_role field is not needed)

```<code>CREATE TABLE project_ownerships
  ( project_id BIGINT REFERENCES projects (id) ON DELETE CASCADE
  , user_id BIGINT REFERENCES users(id) ON DELETE CASCADE
  , the_role INTEGER
  , PRIMARY KEY  (project_id, user_id)
  , CONSTRAINT reversed_pk UNIQUE (user_id, project_id)
  );
</code>```

A small test-setup
(I need to disable sort and hashjoin, because for small tables like this these actually lead to cheaper plans ;-)

```<code>SET search_path=tmp;
SELECT version();

CREATE TABLE projects
        ( id bigserial not NULL PRIMARY KEY
        , the_name text UNIQUE
        );

CREATE TABLE users
        ( id bigserial not NULL PRIMARY KEY
        , the_name text UNIQUE
        );

CREATE TABLE project_ownerships
  ( project_id BIGINT REFERENCES projects (id) ON DELETE CASCADE
  , user_id BIGINT REFERENCES users(id) ON DELETE CASCADE
  , the_role INTEGER
  , PRIMARY KEY  (project_id, user_id)
  , CONSTRAINT reversed_pk UNIQUE (user_id, project_id)
  );

INSERT INTO projects( the_name)
SELECT 'project-' || gs::text
FROM generate_series(1,1000) gs
        ;

INSERT INTO users( the_name)
SELECT 'name_' || gs::text
FROM generate_series(1,1000) gs
        ;

INSERT INTO project_ownerships (project_id,user_id,the_role)
SELECT p.id, u.id , (random()* 100)::integer
FROM projects p
JOIN users u ON random() < .10
        ;

VACUUM ANALYZE projects,users,project_ownerships;

SET enable_hashjoin = 0;
SET enable_sort = 0;
-- SET enable_seqscan = 0;

EXPLAIN ANALYZE
SELECT p.the_name AS project_name
        , po.the_role AS the_role
FROM projects p
JOIN project_ownerships po ON po.project_id = p.id
        AND EXISTS (
        SELECT *
        FROM users u
        WHERE u.id = po.user_id
        AND u.the_name >= 'name_10'
        AND u.the_name < 'name_20'
        );

EXPLAIN ANALYZE
SELECT u.the_name AS user_name
        , po.the_role AS the_role
FROM users u
JOIN project_ownerships po ON po.user_id = u.id
        AND EXISTS (
        SELECT *
        FROM projects p
        WHERE p.id = po.project_id
        AND p.the_name >= 'project-10'
        AND p.the_name < 'project-20'
        );
</code>```

Resulting query plans:

```<code>SET
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 11.6 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Raspbian 8.3.0-6+rpi1) 8.3.0, 32-bit
(1 row)
SET
SET
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.97..4693.68 rows=11924 width=15) (actual time=0.333..153.660 rows=11157 loops=1)
   ->  Nested Loop  (cost=0.69..1204.55 rows=11924 width=12) (actual time=0.268..53.192 rows=11157 loops=1)
         ->  Index Scan using users_the_name_key on users u  (cost=0.28..7.02 rows=119 width=8) (actual time=0.126..0.317 rows=112 loops=1)
               Index Cond: ((the_name >= 'name_10'::text) AND (the_name < 'name_20'::text))
         ->  Index Scan using reversed_pk on project_ownerships po  (cost=0.42..9.06 rows=100 width=20) (actual time=0.015..0.308 rows=100 loops=112)
               Index Cond: (user_id = u.id)
   ->  Index Scan using projects_pkey on projects p  (cost=0.28..0.29 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=11157)
         Index Cond: (id = po.project_id)
 Planning Time: 6.218 ms
 Execution Time: 162.319 ms
(10 rows)

                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.97..4057.79 rows=11022 width=12) (actual time=0.084..93.584 rows=11236 loops=1)
   ->  Nested Loop  (cost=0.69..832.59 rows=11022 width=12) (actual time=0.063..25.260 rows=11236 loops=1)
         ->  Index Scan using projects_the_name_key on projects p  (cost=0.28..6.84 rows=110 width=8) (actual time=0.037..0.163 rows=112 loops=1)
               Index Cond: ((the_name >= 'project-10'::text) AND (the_name < 'project-20'::text))
         ->  Index Scan using project_ownerships_pkey on project_ownerships po  (cost=0.42..6.51 rows=100 width=20) (actual time=0.010..0.111 rows=100 loops=112)
               Index Cond: (project_id = p.id)
   ->  Index Scan using users_pkey on users u  (cost=0.28..0.29 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=11236)
         Index Cond: (id = po.user_id)
 Planning Time: 0.971 ms
 Execution Time: 99.671 ms
(10 rows)
</code>```

Body:
In Postgresql, I have a table with a unique constraint on two foreign keys like below:

CREATE TABLE project_ownerships(
  id BIGSERIAL  PRIMARY KEY,
  project_id BIGINT REFERENCES projects ON DELETE CASCADE,
  user_id BIGINT REFERENCES users ON DELETE CASCADE,
  role SMALLINT,
  CONSTRAINT project_user_unique UNIQUE (project_id, user_id)
);

Having set a unique constraint on the two foreign keys project_id and user_id, does psql automatically create an index for each of them too? Or am I supposed to manually create an index for them still? Like below:

CREATE TABLE project_ownerships(
  id BIGSERIAL  PRIMARY KEY,
  project_id BIGINT REFERENCES projects ON DELETE CASCADE,
  user_id BIGINT REFERENCES users ON DELETE CASCADE,
  role SMALLINT,
  CONSTRAINT project_user_unique UNIQUE (project_id, user_id)
);

CREATE INDEX po_project_id_idx ON project_ownerships (project_id);
CREATE INDEX po_user_id_idx ON project_ownerships (user_id);

I have read the text here, just wanted to make sure I understood it correctly in terms of actual implementation details. Specifically, with a composite index created-CONSTRAINT project_user_unique UNIQUE (project_id, user_id)- will Postgresql be able to execute an index scan(if deemed necessary) when I'm doing a join on project_id, user_id or both? Do I still need to separately create an index for each of the foreign keys?

Body:
It is a matter of taste, but personally I don't think you need the surrogate key (id) here. (is it ever used?) Also: role is a (non reserved) keyword. Avoid using it as identifier.

For the foreign keys, an index is absolutely necessary, otherwise a CASCADEing delete or update would (internally) lead to a sequential scan for every deleted/updated user- or project- tuple.

For a junction (bridge) table like this, it is sufficient to create an index (or UNIQUE constraint) with the key elements in the reversed order. This also serves as a supporting index for the FK. [the first element(s) of a composite index can be used as if an index with only these fields existed]

The extra key field in the index can enable index-only scans (for instance: when the the_role field is not needed)

CREATE TABLE project_ownerships
  ( project_id BIGINT REFERENCES projects (id) ON DELETE CASCADE
  , user_id BIGINT REFERENCES users(id) ON DELETE CASCADE
  , the_role INTEGER
  , PRIMARY KEY  (project_id, user_id)
  , CONSTRAINT reversed_pk UNIQUE (user_id, project_id)
  );

A small test-setup
(I need to disable sort and hashjoin, because for small tables like this these actually lead to cheaper plans ;-)

SET search_path=tmp;
SELECT version();

CREATE TABLE projects
        ( id bigserial not NULL PRIMARY KEY
        , the_name text UNIQUE
        );

CREATE TABLE users
        ( id bigserial not NULL PRIMARY KEY
        , the_name text UNIQUE
        );

CREATE TABLE project_ownerships
  ( project_id BIGINT REFERENCES projects (id) ON DELETE CASCADE
  , user_id BIGINT REFERENCES users(id) ON DELETE CASCADE
  , the_role INTEGER
  , PRIMARY KEY  (project_id, user_id)
  , CONSTRAINT reversed_pk UNIQUE (user_id, project_id)
  );

INSERT INTO projects( the_name)
SELECT 'project-' || gs::text
FROM generate_series(1,1000) gs
        ;

INSERT INTO users( the_name)
SELECT 'name_' || gs::text
FROM generate_series(1,1000) gs
        ;

INSERT INTO project_ownerships (project_id,user_id,the_role)
SELECT p.id, u.id , (random()* 100)::integer
FROM projects p
JOIN users u ON random() < .10
        ;

VACUUM ANALYZE projects,users,project_ownerships;

SET enable_hashjoin = 0;
SET enable_sort = 0;
-- SET enable_seqscan = 0;

EXPLAIN ANALYZE
SELECT p.the_name AS project_name
        , po.the_role AS the_role
FROM projects p
JOIN project_ownerships po ON po.project_id = p.id
        AND EXISTS (
        SELECT *
        FROM users u
        WHERE u.id = po.user_id
        AND u.the_name >= 'name_10'
        AND u.the_name < 'name_20'
        );

EXPLAIN ANALYZE
SELECT u.the_name AS user_name
        , po.the_role AS the_role
FROM users u
JOIN project_ownerships po ON po.user_id = u.id
        AND EXISTS (
        SELECT *
        FROM projects p
        WHERE p.id = po.project_id
        AND p.the_name >= 'project-10'
        AND p.the_name < 'project-20'
        );

Resulting query plans:

SET
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 11.6 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Raspbian 8.3.0-6+rpi1) 8.3.0, 32-bit
(1 row)
SET
SET
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.97..4693.68 rows=11924 width=15) (actual time=0.333..153.660 rows=11157 loops=1)
   ->  Nested Loop  (cost=0.69..1204.55 rows=11924 width=12) (actual time=0.268..53.192 rows=11157 loops=1)
         ->  Index Scan using users_the_name_key on users u  (cost=0.28..7.02 rows=119 width=8) (actual time=0.126..0.317 rows=112 loops=1)
               Index Cond: ((the_name >= 'name_10'::text) AND (the_name < 'name_20'::text))
         ->  Index Scan using reversed_pk on project_ownerships po  (cost=0.42..9.06 rows=100 width=20) (actual time=0.015..0.308 rows=100 loops=112)
               Index Cond: (user_id = u.id)
   ->  Index Scan using projects_pkey on projects p  (cost=0.28..0.29 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=11157)
         Index Cond: (id = po.project_id)
 Planning Time: 6.218 ms
 Execution Time: 162.319 ms
(10 rows)

                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.97..4057.79 rows=11022 width=12) (actual time=0.084..93.584 rows=11236 loops=1)
   ->  Nested Loop  (cost=0.69..832.59 rows=11022 width=12) (actual time=0.063..25.260 rows=11236 loops=1)
         ->  Index Scan using projects_the_name_key on projects p  (cost=0.28..6.84 rows=110 width=8) (actual time=0.037..0.163 rows=112 loops=1)
               Index Cond: ((the_name >= 'project-10'::text) AND (the_name < 'project-20'::text))
         ->  Index Scan using project_ownerships_pkey on project_ownerships po  (cost=0.42..6.51 rows=100 width=20) (actual time=0.010..0.111 rows=100 loops=112)
               Index Cond: (project_id = p.id)
   ->  Index Scan using users_pkey on users u  (cost=0.28..0.29 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=11236)
         Index Cond: (id = po.user_id)
 Planning Time: 0.971 ms
 Execution Time: 99.671 ms
(10 rows)


来源: stackexchange

问:
how to read xls chart in java?
I am using POI API to read a template n to create an XLS file through java.

I want to get the chart from the template. IS there any way to do so?

答:
```<code>// officetools.jar available at http://www.dancrintea.ro/xls-to-pdf/

import officetools.OfficeFile;

FileInputStream fis = new FileInputStream(new File(”test.xls”));

OfficeFile f = new OfficeFile(fis, ”localhost”, ”8100″, false);

// read a column
for (int i=0; i<=99; i++)
    System.out.println(f.getCell(1,i));
</code>```

OwnerDisplayName: Chinmay

ContentLicense: CC BY-SA 2.5

Title: how to read xls chart in java?

OwnerDisplayName: zz ww

ContentLicense: CC BY-SA 3.0


问:
NullPointerException trying retrieve @ManyToOne Parent Field - Spring Boot
i'm trying to get a field from parent entity in a @ManyToOne relationship, but it throws a NullPointerException during the Integration Test when i try to print the parent field, the code is below for both my entities including the Test.

Album Entity - Parent

```<code>
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.validation.constraints.NotBlank;

import org.hibernate.annotations.GenericGenerator;

@Entity
@Table(name = "albuns")
public class Album {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "native")
    @GenericGenerator(name = "native", strategy = "native")
    private Long codigo;

    @NotBlank(message = "Erro: Certifique-se de digitar o nome do album.")
    @Column(nullable = false)
    private String nome;

    @OneToMany(mappedBy = "album", cascade = { CascadeType.PERSIST, CascadeType.REMOVE }, fetch = FetchType.EAGER)
    private List<Imagem> imagens = new ArrayList<>();

    @Column(name = "data_criacao", nullable = false)
    private LocalDate dataCriacao;

    @Column(unique = true, nullable = false)
    private String capa;

    protected Album() {
    }

    public Album(String nome, LocalDate dataCriacao, String capa) {
        this.nome = nome;
        this.dataCriacao = dataCriacao;
        this.capa = capa;
    }

    public Long getCodigo() {
        return codigo;
    }

    public void setCodigo(Long codigo) {
        this.codigo = codigo;
    }

    public String getNome() {
        return nome;
    }

    public void setNome(String nome) {
        this.nome = nome;
    }

    public LocalDate getDataCriacao() {
        return dataCriacao;
    }

    public void setDataCriacao(LocalDate dataCriacao) {
        this.dataCriacao = dataCriacao;
    }

    public String getCapa() {
        return capa;
    }

    public void setCapa(String capa) {
        this.capa = capa;
    }

    public List<Imagem> getImagens() {
        return imagens;
    }

    public void setImagens(List<Imagem> imagens) {
        this.imagens = imagens;
    }

}
</code>```

Imagem Entity - Child

```<code>
import java.time.LocalDate;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

import org.hibernate.annotations.GenericGenerator;

@Entity
@Table(name = "imagens")
public class Imagem {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "native")
    @GenericGenerator(name = "native", strategy = "native")
    private long codigo;

    @Column(nullable = false, unique = true)
    private String url;

    @Column(name = "data_carregamento", nullable = false)
    private LocalDate dataCarregamento;

    @ManyToOne(targetEntity = Album.class, fetch = FetchType.LAZY)
    @JoinColumn(name = "cod_album")
    private Album album;

    public Imagem() {
    }

    public Imagem(String url, LocalDate dataCarregamento) {
        this.url = url;
        this.dataCarregamento = dataCarregamento;

    }

    public long getCodigo() {
        return codigo;
    }

    public void setCodigo(long codigo) {
        this.codigo = codigo;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public LocalDate getDataCarregamento() {
        return dataCarregamento;
    }

    public void setDataCarregamento(LocalDate dataCarregamento) {
        this.dataCarregamento = dataCarregamento;
    }

    public Album getAlbum() {
        return album;
    }

    public Long getAlbumCodigo() {
        return album.getCodigo();
    }

    public void setAlbum(Album album) {
        this.album = album;
    }

}
</code>```

Album Repository

```<code>
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import com.servidor.multiplatform.entidades.Album;

@Repository
public interface AlbumRepositorio extends JpaRepository<Album, Long> {

    public Album findByNome(String nome);

}
</code>```

Integration Test

```<code>
import static org.assertj.core.api.Assertions.assertThat;

import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager;
import org.springframework.test.context.junit4.SpringRunner;

import com.servidor.multiplatform.entidades.Album;
import com.servidor.multiplatform.entidades.Imagem;
import com.servidor.multiplatform.repositorios.AlbumRepositorio;

@RunWith(SpringRunner.class)
@DataJpaTest
public class TesteDeIntegracaoAlbumRepositorio {

    @Autowired
    private TestEntityManager entityManager;

    @Autowired
    private AlbumRepositorio albumRepositorio;

    @Test
    public void TesteDeInsercaoDoAlbumAsSuasImagensEPesquisaDaSuaInformacao() {

        // Album

        Album album1 = new Album("Album 1", LocalDate.now(), "Capa 1");

        // List for Test
        List<Imagem> imagensLocal = new ArrayList<>();

        // Filling the Lists with new images
        for (int i = 0; i < 4; i++) {
            Imagem imagem = new Imagem("url" + i, LocalDate.now());
            imagensLocal.add(imagem);
            album1.getImagens().add(imagem);
        }

        // Persisting the Album and the Pictures
        entityManager.persist(album1);
        entityManager.flush();

        // Getting the Album and the Pictures

        Album albumEncontrado = albumRepositorio.findByNome("Album 1");
        List<Imagem> imagensPesistidas = albumEncontrado.getImagens();

        Album album = entityManager.find(Album.class, 1L);

        System.out.println(album.getCodigo());

        for (Imagem imagem : album.getImagens()) {
            // The problem is here in this code
            System.out.println(imagem.getAlbumCodigo());
        }

        // Testing the Album
        assertThat(album1.getNome()).isEqualTo(albumEncontrado.getNome());

        // Testing the Lists
        Assert.assertEquals(imagensLocal, imagensPesistidas);

    }

}
</code>```

The Stack Trace

```<code>TesteDeIntegracaoAlbumRepositorio.TesteDeInsercaoDoAlbumAsSuasImagensEPesquisaDaSuaInformacao
com.servidor.multiplatform.TesteDeIntegracaoAlbumRepositorio
TesteDeInsercaoDoAlbumAsSuasImagensEPesquisaDaSuaInformacao(com.servidor.multiplatform.TesteDeIntegracaoAlbumRepositorio)
java.lang.NullPointerException

    at com.servidor.multiplatform.entidades.Imagem.getAlbumCodigo(Imagem.java:75)

    at com.servidor.multiplatform.TesteDeIntegracaoAlbumRepositorio.TesteDeInsercaoDoAlbumAsSuasImagensEPesquisaDaSuaInformacao(TesteDeIntegracaoAlbumRepositorio.java:64)

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

    at java.lang.reflect.Method.invoke(Unknown Source)

    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)

    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)

    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

    at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)

    at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)

    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)

    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)

    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)

    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)

    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)

    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)

    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)

    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)

    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)

    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)

    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)

    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)

    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)

    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)

    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)

    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)

    at org.junit.runner.JUnitCore.run(JUnitCore.java:115)

    at org.junit.vintage.engine.execution.RunnerExecutor.execute(RunnerExecutor.java:40)

    at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(Unknown Source)

    at java.util.stream.ReferencePipeline$3$1.accept(Unknown Source)

    at java.util.Iterator.forEachRemaining(Unknown Source)

    at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Unknown Source)

    at java.util.stream.AbstractPipeline.copyInto(Unknown Source)

    at java.util.stream.AbstractPipeline.wrapAndCopyInto(Unknown Source)

    at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(Unknown Source)

    at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(Unknown Source)

    at java.util.stream.AbstractPipeline.evaluate(Unknown Source)

    at java.util.stream.ReferencePipeline.forEach(Unknown Source)

    at org.junit.vintage.engine.VintageTestEngine.executeAllChildren(VintageTestEngine.java:80)

    at org.junit.vintage.engine.VintageTestEngine.execute(VintageTestEngine.java:71)

    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229)

    at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197)

    at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211)

    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191)

    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:137)

    at org.eclipse.jdt.internal.junit5.runner.JUnit5TestReference.run(JUnit5TestReference.java:89)

    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)

    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:541)

    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:763)

    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:463)

    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:209)

</code>```

答:
Imagem.album remains null because code given in unit test never writes to that field. Only one side of the relation is set via album1.getImagens().add(imagem).

```<code>//Imagem.album is not set in this loop:
for (int i = 0; i < 4; i++) {
    Imagem imagem = new Imagem("url" + i, LocalDate.now());
    imagensLocal.add(imagem);
    album1.getImagens().add(imagem);
}
</code>```

As told in specification, one should keep relationships consistent:

Note that it is the application that bears responsibility for maintaining the consistency of run-time relationships—for example, for insuring that the “one” and the “many” sides of a bidi-rectional  relationship  are  consistent  with  one  another  when  the  application  updates  therelationship at runtime

It will remain null also for albumEncontradoand album, because they are refences to same object as album1. It is so, because persistence context is not cleared in any point.

Right way to fix the issue is to keep relationships consistent by setting both sides of relationship:

```<code>for (int i = 0; i < 4; i++) {
    Imagem imagem = new Imagem("url" + i, LocalDate.now());
    imagensLocal.add(imagem);
    album1.getImagens().add(imagem);
    imagem.setAlbum(album1);
}
</code>```

NullPointerException  could also be avoided by clearing persistence context via EntityManager.clear() before querying database. That would break the assertion for list equality, because it depends from identity of elements in list. Reason is that Imagem does not implement equals. Even after adding equals it still has assumption about order of elements, which is not that deterministic.

ContentLicense: CC BY-SA 4.0

Body:
i'm trying to get a field from parent entity in a @ManyToOne relationship, but it throws a NullPointerException during the Integration Test when i try to print the parent field, the code is below for both my entities including the Test.

Album Entity - Parent

import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.validation.constraints.NotBlank;

import org.hibernate.annotations.GenericGenerator;

@Entity
@Table(name = "albuns")
public class Album {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "native")
    @GenericGenerator(name = "native", strategy = "native")
    private Long codigo;

    @NotBlank(message = "Erro: Certifique-se de digitar o nome do album.")
    @Column(nullable = false)
    private String nome;

    @OneToMany(mappedBy = "album", cascade = { CascadeType.PERSIST, CascadeType.REMOVE }, fetch = FetchType.EAGER)
    private List<Imagem> imagens = new ArrayList<>();

    @Column(name = "data_criacao", nullable = false)
    private LocalDate dataCriacao;

    @Column(unique = true, nullable = false)
    private String capa;

    protected Album() {
    }

    public Album(String nome, LocalDate dataCriacao, String capa) {
        this.nome = nome;
        this.dataCriacao = dataCriacao;
        this.capa = capa;
    }

    public Long getCodigo() {
        return codigo;
    }

    public void setCodigo(Long codigo) {
        this.codigo = codigo;
    }

    public String getNome() {
        return nome;
    }

    public void setNome(String nome) {
        this.nome = nome;
    }

    public LocalDate getDataCriacao() {
        return dataCriacao;
    }

    public void setDataCriacao(LocalDate dataCriacao) {
        this.dataCriacao = dataCriacao;
    }

    public String getCapa() {
        return capa;
    }

    public void setCapa(String capa) {
        this.capa = capa;
    }

    public List<Imagem> getImagens() {
        return imagens;
    }

    public void setImagens(List<Imagem> imagens) {
        this.imagens = imagens;
    }

}

Imagem Entity - Child

import java.time.LocalDate;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

import org.hibernate.annotations.GenericGenerator;

@Entity
@Table(name = "imagens")
public class Imagem {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "native")
    @GenericGenerator(name = "native", strategy = "native")
    private long codigo;

    @Column(nullable = false, unique = true)
    private String url;

    @Column(name = "data_carregamento", nullable = false)
    private LocalDate dataCarregamento;

    @ManyToOne(targetEntity = Album.class, fetch = FetchType.LAZY)
    @JoinColumn(name = "cod_album")
    private Album album;

    public Imagem() {
    }

    public Imagem(String url, LocalDate dataCarregamento) {
        this.url = url;
        this.dataCarregamento = dataCarregamento;

    }

    public long getCodigo() {
        return codigo;
    }

    public void setCodigo(long codigo) {
        this.codigo = codigo;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public LocalDate getDataCarregamento() {
        return dataCarregamento;
    }

    public void setDataCarregamento(LocalDate dataCarregamento) {
        this.dataCarregamento = dataCarregamento;
    }

    public Album getAlbum() {
        return album;
    }

    public Long getAlbumCodigo() {
        return album.getCodigo();
    }

    public void setAlbum(Album album) {
        this.album = album;
    }

}

Album Repository

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import com.servidor.multiplatform.entidades.Album;

@Repository
public interface AlbumRepositorio extends JpaRepository<Album, Long> {

    public Album findByNome(String nome);

}

Integration Test

import static org.assertj.core.api.Assertions.assertThat;

import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager;
import org.springframework.test.context.junit4.SpringRunner;

import com.servidor.multiplatform.entidades.Album;
import com.servidor.multiplatform.entidades.Imagem;
import com.servidor.multiplatform.repositorios.AlbumRepositorio;

@RunWith(SpringRunner.class)
@DataJpaTest
public class TesteDeIntegracaoAlbumRepositorio {

    @Autowired
    private TestEntityManager entityManager;

    @Autowired
    private AlbumRepositorio albumRepositorio;

    @Test
    public void TesteDeInsercaoDoAlbumAsSuasImagensEPesquisaDaSuaInformacao() {

        // Album

        Album album1 = new Album("Album 1", LocalDate.now(), "Capa 1");

        // List for Test
        List<Imagem> imagensLocal = new ArrayList<>();

        // Filling the Lists with new images
        for (int i = 0; i < 4; i++) {
            Imagem imagem = new Imagem("url" + i, LocalDate.now());
            imagensLocal.add(imagem);
            album1.getImagens().add(imagem);
        }

        // Persisting the Album and the Pictures
        entityManager.persist(album1);
        entityManager.flush();

        // Getting the Album and the Pictures

        Album albumEncontrado = albumRepositorio.findByNome("Album 1");
        List<Imagem> imagensPesistidas = albumEncontrado.getImagens();

        Album album = entityManager.find(Album.class, 1L);

        System.out.println(album.getCodigo());

        for (Imagem imagem : album.getImagens()) {
            // The problem is here in this code
            System.out.println(imagem.getAlbumCodigo());
        }

        // Testing the Album
        assertThat(album1.getNome()).isEqualTo(albumEncontrado.getNome());

        // Testing the Lists
        Assert.assertEquals(imagensLocal, imagensPesistidas);

    }

}

The Stack Trace

TesteDeIntegracaoAlbumRepositorio.TesteDeInsercaoDoAlbumAsSuasImagensEPesquisaDaSuaInformacao
com.servidor.multiplatform.TesteDeIntegracaoAlbumRepositorio
TesteDeInsercaoDoAlbumAsSuasImagensEPesquisaDaSuaInformacao(com.servidor.multiplatform.TesteDeIntegracaoAlbumRepositorio)
java.lang.NullPointerException

    at com.servidor.multiplatform.entidades.Imagem.getAlbumCodigo(Imagem.java:75)

    at com.servidor.multiplatform.TesteDeIntegracaoAlbumRepositorio.TesteDeInsercaoDoAlbumAsSuasImagensEPesquisaDaSuaInformacao(TesteDeIntegracaoAlbumRepositorio.java:64)

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

    at java.lang.reflect.Method.invoke(Unknown Source)

    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)

    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)

    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

    at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)

    at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)

    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)

    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)

    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)

    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)

    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)

    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)

    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)

    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)

    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)

    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)

    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)

    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)

    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)

    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)

    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)

    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)

    at org.junit.runner.JUnitCore.run(JUnitCore.java:115)

    at org.junit.vintage.engine.execution.RunnerExecutor.execute(RunnerExecutor.java:40)

    at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(Unknown Source)

    at java.util.stream.ReferencePipeline$3$1.accept(Unknown Source)

    at java.util.Iterator.forEachRemaining(Unknown Source)

    at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Unknown Source)

    at java.util.stream.AbstractPipeline.copyInto(Unknown Source)

    at java.util.stream.AbstractPipeline.wrapAndCopyInto(Unknown Source)

    at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(Unknown Source)

    at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(Unknown Source)

    at java.util.stream.AbstractPipeline.evaluate(Unknown Source)

    at java.util.stream.ReferencePipeline.forEach(Unknown Source)

    at org.junit.vintage.engine.VintageTestEngine.executeAllChildren(VintageTestEngine.java:80)

    at org.junit.vintage.engine.VintageTestEngine.execute(VintageTestEngine.java:71)

    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229)

    at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197)

    at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211)

    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191)

    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:137)

    at org.eclipse.jdt.internal.junit5.runner.JUnit5TestReference.run(JUnit5TestReference.java:89)

    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)

    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:541)

    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:763)

    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:463)

    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:209)

Body:
Imagem.album remains null because code given in unit test never writes to that field. Only one side of the relation is set via album1.getImagens().add(imagem).

//Imagem.album is not set in this loop:
for (int i = 0; i < 4; i++) {
    Imagem imagem = new Imagem("url" + i, LocalDate.now());
    imagensLocal.add(imagem);
    album1.getImagens().add(imagem);
}

As told in specification, one should keep relationships consistent:

Note that it is the application that bears responsibility for maintaining the consistency of run-time relationships—for example, for insuring that the “one” and the “many” sides of a bidi-rectional  relationship  are  consistent  with  one  another  when  the  application  updates  therelationship at runtime

It will remain null also for albumEncontradoand album, because they are refences to same object as album1. It is so, because persistence context is not cleared in any point.

Right way to fix the issue is to keep relationships consistent by setting both sides of relationship:

for (int i = 0; i < 4; i++) {
    Imagem imagem = new Imagem("url" + i, LocalDate.now());
    imagensLocal.add(imagem);
    album1.getImagens().add(imagem);
    imagem.setAlbum(album1);
}

NullPointerException  could also be avoided by clearing persistence context via EntityManager.clear() before querying database. That would break the assertion for list equality, because it depends from identity of elements in list. Reason is that Imagem does not implement equals. Even after adding equals it still has assumption about order of elements, which is not that deterministic.


问:
How do I use PDO::FETCH_CLASS with namespace?
I'm learning PHP and right now I'm displaying some articles with PDO. I have some issue using PDO::FETCH_CLASS with the usage of namespace.

my folder architecture:

```<code>class/
- Post.php
layout/
-articles/
--articles.php
public/
-index.php (contains my router)
</code>```

Post.php

```<code>namespace App\Articles;

class Post {
    private $id;
    private $name;
    private $content;
    private $date;

    public function getId()
    {
        return $this->id;
    }
    public function getName()
    {
        return $this->name;
    }
    public function getContent()
    {
        return $this->content;
    }
    public function getExcerpt()
    {
        $res = $this->content;
        return (strlen($res) > 150) ? substr($res, 0, 150) . '...' : $res;
    }
    public function getDate()
    {
        return $this->date = date('d-m-Y', $this->date);
    }
}
</code>```

data.db contains only four columns: id, name(text), content(text), created_at(int)

The part I have trouble with:

```<code>use App\Articles\Post;

$pdo = new PDO('sqlite:../data.db', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
]);
try {
    $query = $pdo->query('SELECT * FROM posts');
    $posts = $query->fetchAll(PDO::FETCH_CLASS, 'Post');
} catch (PDOException $e) {
    $error = $e->getMessage();
}
</code>```

I tried different typing instead of 'Postlike

```<code>'App\Articles\'
'App\Articles\Post'
'App\Articles\::POST'
...
</code>```

I can't manage to make it works with namepsace.
It works with a require '../class/Post.php'.

Is there a solution for this problem or should I just use the require ?

答:
PDO does not look up aliased class names or resolve the current namespace. So you have to pass it explicitely:

```<code>$query = $pdo->prepare('SELECT * FROM posts');
$query->execute();
$posts = $query->fetchAll(PDO::FETCH_CLASS, __NAMESPACE__ . '\\post');
</code>```

Using query instead of prepare will cause pdo error, I changed to prepare.

And you can't use NAMESPACE if its separate from the class, but you can use full path 'app\controller\post'.

See this answer and question https://stackoverflow.com/a/20176944/12232340

Body:
I'm learning PHP and right now I'm displaying some articles with PDO. I have some issue using PDO::FETCH_CLASS with the usage of namespace.

my folder architecture:

class/
- Post.php
layout/
-articles/
--articles.php
public/
-index.php (contains my router)

Post.php

namespace App\Articles;

class Post {
    private $id;
    private $name;
    private $content;
    private $date;

    public function getId()
    {
        return $this->id;
    }
    public function getName()
    {
        return $this->name;
    }
    public function getContent()
    {
        return $this->content;
    }
    public function getExcerpt()
    {
        $res = $this->content;
        return (strlen($res) > 150) ? substr($res, 0, 150) . '...' : $res;
    }
    public function getDate()
    {
        return $this->date = date('d-m-Y', $this->date);
    }
}

data.db contains only four columns: id, name(text), content(text), created_at(int)

The part I have trouble with:

use App\Articles\Post;

$pdo = new PDO('sqlite:../data.db', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
]);
try {
    $query = $pdo->query('SELECT * FROM posts');
    $posts = $query->fetchAll(PDO::FETCH_CLASS, 'Post');
} catch (PDOException $e) {
    $error = $e->getMessage();
}

I tried different typing instead of 'Postlike

'App\Articles\'
'App\Articles\Post'
'App\Articles\::POST'
...

I can't manage to make it works with namepsace.
It works with a require '../class/Post.php'.

Is there a solution for this problem or should I just use the require ?

Body:
PDO does not look up aliased class names or resolve the current namespace. So you have to pass it explicitely:

$query = $pdo->prepare('SELECT * FROM posts');
$query->execute();
$posts = $query->fetchAll(PDO::FETCH_CLASS, __NAMESPACE__ . '\\post');

Using query instead of prepare will cause pdo error, I changed to prepare.

And you can't use NAMESPACE if its separate from the class, but you can use full path 'app\controller\post'.

See this answer and question https://stackoverflow.com/a/20176944/12232340


问:
SQL query joining two different tables. Find online status from user's last interaction
Problem description: Finding online status of therapist users based on  last interaction in Session table. Cannot keep last interaction date on User table but it should be separated on Session table. But how can I join one row for each user with User table information and session data of Session table for user's last interaction for each record?

Description of figure 1: Listing of therapists from User table with showing their online status based on their last interaction from Session table

Description of figure2 (User): User table is the first table and email is its primary key. It need to be joined with second column, that is Session table's StartDate field as one row for each record. (Pink backgrounded StartDate column is not on User table. User table columns plus the pink backgrounded StartDate column is desired result)

The hardest part of the problem is email is basis of join field and only one in User table but has many in the Session table because it is not primary there.

Description of figure3 (Session): Email field duplicates as user interacts in the program. They are kind of logs of key presses changing personal information e.g.

I cannot created a qualified query to solve my problem so I created a query for table1 and second query in the loop that is not right way of doing the job. It caused performance issues. I need to get help joining two different queries at once.

The first query filters results based on primary key email from User table. The second query gets latest session date on from Session table within the loop.

$marker_list is the first query and $sql_online is for the second query which is nested.

```<code><?php

$latitude = filter_input(INPUT_GET, "latitude", FILTER_SANITIZE_SPECIAL_CHARS);
$longitude = filter_input(INPUT_GET, "longitude", FILTER_SANITIZE_SPECIAL_CHARS);
$start = filter_input(INPUT_GET, "start", FILTER_SANITIZE_SPECIAL_CHARS);
$email = filter_input(INPUT_GET, "email", FILTER_SANITIZE_EMAIL);

include_once "./connect-db.php";

if ($start == 'undefined') {
    $start = 0;
} else {
    $start = $start * 10;
}
$distance = 100;
//First query: lists therapists from User table
$marker_list = "SELECT `User`.`uid`, `User`.`latitude`, `User`.`longitude`, `User`.`name`, `User`.`phone_verified`, `User`.`email`, ( 3959 * acos(cos(radians(" . $latitude . ")) * cos(radians(latitude)) * cos(radians(longitude) - radians(" . $longitude . ")) + sin(radians(" . $latitude . ")) * sin(radians(latitude ))) ) AS `distance` FROM `massage`.`User` WHERE rol= 'terapist' AND list_on_maps= '1' AND email_valid='1' HAVING distance < $distance ORDER BY distance LIMIT $start, 7";

if ($results = mysqli_query($conn, $marker_list)) {
     $sizeofquery = mysqli_num_rows($results);
     $counter = 0;
     $maps = array();
     while ($row = mysqli_fetch_row($results)) {
         $maps[$counter] = new stdClass();
         $maps[$counter]->index = $row[0];
         $maps[$counter]->latlng = new stdClass();
         $maps[$counter]->latlng->latitude = floatval($row[1]);
         $maps[$counter]->latlng->longitude = floatval($row[2]);
         $maps[$counter]->title = $row[3];
         $maps[$counter]->phone_verified = $row[4];
//Second query gets their user interaction from Session table
         $sql_online= "SELECT `StartDate` FROM `massage`.`Session` WHERE `email`='$row[5]' ORDER BY `Session`.`StartDate` DESC LIMIT 1";
         $result_nested= mysqli_query($conn, $sql_online);
         $row_online= mysqli_fetch_array($result_nested);
         $delta= (strtotime("now") - strtotime($row_online['StartDate']));
         if ($delta < 300 ) {
             $maps[$counter]->online = "y";
         } else if ( $delta < 600){
             $maps[$counter]->online = "t";
         } else {
             $maps[$counter]->online = "g";
         }
         $counter++;
    }
    $maps->counter = $row['counter'];
    $mapsJSON = json_encode($maps);
}
//Third Query: Inserts last interaction as listing therapists as on many pages. It is not an important part of the question.
$sql_session = "INSERT INTO `massage`.`Session` (`StartDate`, `email`, `Interaction`) VALUES (current_timestamp(), '$email', 'dolistmaps');";
mysqli_query($conn, $sql_session);
header('Content-type:application/json;charset=utf-8');
echo $mapsJSON;
mysqli_free_result($results);
mysqli_close($conn);
</code>```

So please help me creating a single query which accomplishes joining two different tables on the common field  email on both tables.

答:
You can use MAX to find the latest StartDate from Session for each email in a derived table and JOIN that to the user table to get the results you need:

```<code>SELECT `User`.`uid`,
       `User`.`latitude`,
       `User`.`longitude`,
       `User`.`name`,
       `User`.`phone_verified`,
       `User`.`email`,
       ( 3959 * acos(cos(radians(" . $latitude . ")) * cos(radians(latitude)) * cos(radians(longitude) - radians(" . $longitude . ")) + sin(radians(" . $latitude . ")) * sin(radians(latitude ))) ) AS `distance`,
       s.StartDate
FROM `massage`.`User`
JOIN (
    SELECT email, MAX(StartDate) AS StartDate
    FROM `massage.Session`
    GROUP BY email
) s ON s.email = `User`.email
WHERE rol= 'terapist' AND list_on_maps= '1' AND email_valid='1'
HAVING distance < $distance
ORDER BY distance
LIMIT $start, 7
</code>```

Body:
Problem description: Finding online status of therapist users based on  last interaction in Session table. Cannot keep last interaction date on User table but it should be separated on Session table. But how can I join one row for each user with User table information and session data of Session table for user's last interaction for each record?

Description of figure 1: Listing of therapists from User table with showing their online status based on their last interaction from Session table

Description of figure2 (User): User table is the first table and email is its primary key. It need to be joined with second column, that is Session table's StartDate field as one row for each record. (Pink backgrounded StartDate column is not on User table. User table columns plus the pink backgrounded StartDate column is desired result)

The hardest part of the problem is email is basis of join field and only one in User table but has many in the Session table because it is not primary there.

Description of figure3 (Session): Email field duplicates as user interacts in the program. They are kind of logs of key presses changing personal information e.g.

I cannot created a qualified query to solve my problem so I created a query for table1 and second query in the loop that is not right way of doing the job. It caused performance issues. I need to get help joining two different queries at once.

The first query filters results based on primary key email from User table. The second query gets latest session date on from Session table within the loop.

$marker_list is the first query and $sql_online is for the second query which is nested.

<?php

$latitude = filter_input(INPUT_GET, "latitude", FILTER_SANITIZE_SPECIAL_CHARS);
$longitude = filter_input(INPUT_GET, "longitude", FILTER_SANITIZE_SPECIAL_CHARS);
$start = filter_input(INPUT_GET, "start", FILTER_SANITIZE_SPECIAL_CHARS);
$email = filter_input(INPUT_GET, "email", FILTER_SANITIZE_EMAIL);

include_once "./connect-db.php";

if ($start == 'undefined') {
    $start = 0;
} else {
    $start = $start * 10;
}
$distance = 100;
//First query: lists therapists from User table
$marker_list = "SELECT `User`.`uid`, `User`.`latitude`, `User`.`longitude`, `User`.`name`, `User`.`phone_verified`, `User`.`email`, ( 3959 * acos(cos(radians(" . $latitude . ")) * cos(radians(latitude)) * cos(radians(longitude) - radians(" . $longitude . ")) + sin(radians(" . $latitude . ")) * sin(radians(latitude ))) ) AS `distance` FROM `massage`.`User` WHERE rol= 'terapist' AND list_on_maps= '1' AND email_valid='1' HAVING distance < $distance ORDER BY distance LIMIT $start, 7";

if ($results = mysqli_query($conn, $marker_list)) {
     $sizeofquery = mysqli_num_rows($results);
     $counter = 0;
     $maps = array();
     while ($row = mysqli_fetch_row($results)) {
         $maps[$counter] = new stdClass();
         $maps[$counter]->index = $row[0];
         $maps[$counter]->latlng = new stdClass();
         $maps[$counter]->latlng->latitude = floatval($row[1]);
         $maps[$counter]->latlng->longitude = floatval($row[2]);
         $maps[$counter]->title = $row[3];
         $maps[$counter]->phone_verified = $row[4];
//Second query gets their user interaction from Session table
         $sql_online= "SELECT `StartDate` FROM `massage`.`Session` WHERE `email`='$row[5]' ORDER BY `Session`.`StartDate` DESC LIMIT 1";
         $result_nested= mysqli_query($conn, $sql_online);
         $row_online= mysqli_fetch_array($result_nested);
         $delta= (strtotime("now") - strtotime($row_online['StartDate']));
         if ($delta < 300 ) {
             $maps[$counter]->online = "y";
         } else if ( $delta < 600){
             $maps[$counter]->online = "t";
         } else {
             $maps[$counter]->online = "g";
         }
         $counter++;
    }
    $maps->counter = $row['counter'];
    $mapsJSON = json_encode($maps);
}
//Third Query: Inserts last interaction as listing therapists as on many pages. It is not an important part of the question.
$sql_session = "INSERT INTO `massage`.`Session` (`StartDate`, `email`, `Interaction`) VALUES (current_timestamp(), '$email', 'dolistmaps');";
mysqli_query($conn, $sql_session);
header('Content-type:application/json;charset=utf-8');
echo $mapsJSON;
mysqli_free_result($results);
mysqli_close($conn);

So please help me creating a single query which accomplishes joining two different tables on the common field  email on both tables.

Body:
You can use MAX to find the latest StartDate from Session for each email in a derived table and JOIN that to the user table to get the results you need:

SELECT `User`.`uid`,
       `User`.`latitude`,
       `User`.`longitude`,
       `User`.`name`,
       `User`.`phone_verified`,
       `User`.`email`,
       ( 3959 * acos(cos(radians(" . $latitude . ")) * cos(radians(latitude)) * cos(radians(longitude) - radians(" . $longitude . ")) + sin(radians(" . $latitude . ")) * sin(radians(latitude ))) ) AS `distance`,
       s.StartDate
FROM `massage`.`User`
JOIN (
    SELECT email, MAX(StartDate) AS StartDate
    FROM `massage.Session`
    GROUP BY email
) s ON s.email = `User`.email
WHERE rol= 'terapist' AND list_on_maps= '1' AND email_valid='1'
HAVING distance < $distance
ORDER BY distance
LIMIT $start, 7


问:
Encoding an integer in 7-bit format of C# BinaryReader.ReadString
C#'s BinaryReader has a function that according to MSDN, reads an integer encoded as "seven bit integer", and then reads a string with the length of this integer.

Is there a clear documentation for the seven bit integer format (I have a rough understanding that the MSB or the LSB marks whether there are more bytes to read, and the rest bits are the data, but I'll be glad for something more exact).

Even better, is there a C implementation for reading and writing numbers in this format?

答:
I had to explore this 7-bit format also. In one of my projects I pack some data into files using C#'s BinaryWriter and then unpack it again with BinaryReader, which works nicely.

Later I needed to implement a reader for this project's packed files for Java, too. Java has a class named DataInputStream (in java.io package), which has some similar methods. Unfortunately DataInputStream's data interpretation is very different than C#'s.

To solve my problem I ported C#'s BinaryReader to Java myself by writing a class that extends java.io.DataInputStream. Here is the method I wrote, which does exactly the same as C#'s BinaryReader.readString():

```<code>public String csReadString() throws IOException {
    int stringLength = 0;
    boolean stringLengthParsed = false;
    int step = 0;
    while(!stringLengthParsed) {
        byte part = csReadByte();
        stringLengthParsed = (((int)part >> 7) == 0);
        int partCutter = part & 127;
        part = (byte)partCutter;
        int toAdd = (int)part << (step*7);
        stringLength += toAdd;
        step++;
    }
    char[] chars = new char[stringLength];
    for(int i = 0; i < stringLength; i++) {
        chars[i] = csReadChar();
    }
    return new String(chars);
}
</code>```

Body:
I had to explore this 7-bit format also. In one of my projects I pack some data into files using C#'s BinaryWriter and then unpack it again with BinaryReader, which works nicely.

Later I needed to implement a reader for this project's packed files for Java, too. Java has a class named DataInputStream (in java.io package), which has some similar methods. Unfortunately DataInputStream's data interpretation is very different than C#'s.

To solve my problem I ported C#'s BinaryReader to Java myself by writing a class that extends java.io.DataInputStream. Here is the method I wrote, which does exactly the same as C#'s BinaryReader.readString():

public String csReadString() throws IOException {
    int stringLength = 0;
    boolean stringLengthParsed = false;
    int step = 0;
    while(!stringLengthParsed) {
        byte part = csReadByte();
        stringLengthParsed = (((int)part >> 7) == 0);
        int partCutter = part & 127;
        part = (byte)partCutter;
        int toAdd = (int)part << (step*7);
        stringLength += toAdd;
        step++;
    }
    char[] chars = new char[stringLength];
    for(int i = 0; i < stringLength; i++) {
        chars[i] = csReadChar();
    }
    return new String(chars);
}


问:
I have a problem when I install angular on terminal macOs
I would like to install angular but when I type the command these error messages appear. Could someone help me? Thank you in advance. enter image description here</p>

答:
Just use sudo

```<code>sudo npm install -g @angular/cli
</code>```

Body:
Just use sudo

sudo npm install -g @angular/cli


问:
How do I count the NaN values in a column in pandas DataFrame?
I want to find the number of NaN in each column of my data.

答:
The below will print all the Nan columns in descending order.

```<code>df.isnull().sum().sort_values(ascending = False)
</code>```

or

The below will print first 15 Nan columns in descending order.

```<code>df.isnull().sum().sort_values(ascending = False).head(15)
</code>```

Body:
The below will print all the Nan columns in descending order.

df.isnull().sum().sort_values(ascending = False)

or

The below will print first 15 Nan columns in descending order.

df.isnull().sum().sort_values(ascending = False).head(15)


问:
affdex.cpp:9:31: fatal error: pybind11/pybind11.h: No such file or directory #include <pybind11/pybind11.h>
I can import pybind11 in Python and it is in the include path. However, I get the following error following the instructions here:

```<code>(venv)[jalal@ivcgpu1 affdex-sdk-py]$ python
Python 2.7.5 (default, Aug  4 2017, 00:39:18)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-16)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pybind11
>>> pybind11.get_include()
'/home/grad3/jalal/venv/include/site/python2.7'
>>> pybind11.get_include(True)
'/home/grad3/jalal/venv/include/site/python2.7'
>>> quit()
(venv)[jalal@ivcgpu1 affdex-sdk-py]$ c++ -O3 -shared -std=c++11 -fPIC -Wl,--no-as-needed -I$AFFDEX_SDK_HOME/include -I$HOME/venv/include/site/python2.7 `python-config --cflags --ldflags --libs` -Wl,--no-as-needed -laffdex-native -L$AFFDEX_SDK_HOME/lib affdex.cpp -o affdex.so
affdex.cpp:9:31: fatal error: pybind11/pybind11.h: No such file or directory
 #include <pybind11/pybind11.h>
                               ^
compilation terminated.
(venv)[jalal@ivcgpu1 affdex-sdk-py]$
</code>```

How can I fix this error?

UPDATE:

```<code>(venv)[jalal@ivcgpu1 affdex-sdk-py]$ echo $HOME
/home/grad3/jalal
(venv)[jalal@ivcgpu1 affdex-sdk-py]$ echo $HOME/venv/include/site/python2.7
/home/grad3/jalal/venv/include/site/python2.7
</code>```

答:
I saw the following error message when trying to install scipy for pypy:

```<code>  scipy/fft/_pocketfft/pypocketfft.cxx:15:31: fatal error: pybind11/pybind11.h: No such file or directory
   #include <pybind11/pybind11.h>
                                 ^
  compilation terminated.
  scipy/fft/_pocketfft/pypocketfft.cxx:15:31: fatal error: pybind11/pybind11.h: No such file or directory
   #include <pybind11/pybind11.h>
</code>```

The fix was:

```<code>$ python -m pip install pybind11
</code>```

Body:
I can import pybind11 in Python and it is in the include path. However, I get the following error following the instructions here:

(venv)[jalal@ivcgpu1 affdex-sdk-py]$ python
Python 2.7.5 (default, Aug  4 2017, 00:39:18)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-16)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pybind11
>>> pybind11.get_include()
'/home/grad3/jalal/venv/include/site/python2.7'
>>> pybind11.get_include(True)
'/home/grad3/jalal/venv/include/site/python2.7'
>>> quit()
(venv)[jalal@ivcgpu1 affdex-sdk-py]$ c++ -O3 -shared -std=c++11 -fPIC -Wl,--no-as-needed -I$AFFDEX_SDK_HOME/include -I$HOME/venv/include/site/python2.7 `python-config --cflags --ldflags --libs` -Wl,--no-as-needed -laffdex-native -L$AFFDEX_SDK_HOME/lib affdex.cpp -o affdex.so
affdex.cpp:9:31: fatal error: pybind11/pybind11.h: No such file or directory
 #include <pybind11/pybind11.h>
                               ^
compilation terminated.
(venv)[jalal@ivcgpu1 affdex-sdk-py]$

How can I fix this error?

UPDATE:

(venv)[jalal@ivcgpu1 affdex-sdk-py]$ echo $HOME
/home/grad3/jalal
(venv)[jalal@ivcgpu1 affdex-sdk-py]$ echo $HOME/venv/include/site/python2.7
/home/grad3/jalal/venv/include/site/python2.7

Body:
I saw the following error message when trying to install scipy for pypy:

  scipy/fft/_pocketfft/pypocketfft.cxx:15:31: fatal error: pybind11/pybind11.h: No such file or directory
   #include <pybind11/pybind11.h>
                                 ^
  compilation terminated.
  scipy/fft/_pocketfft/pypocketfft.cxx:15:31: fatal error: pybind11/pybind11.h: No such file or directory
   #include <pybind11/pybind11.h>

The fix was:

$ python -m pip install pybind11


问:
Path.Combine for URLs?
Path.Combine is handy, but is there a similar function in the .NET framework for URLs?

I'm looking for syntax like this:

```<code>Url.Combine("http://MyUrl.com/", "/Images/Image.jpg")
</code>```

which would return:

"http://MyUrl.com/Images/Image.jpg"

答:
Recently Combine method was added to Energy.Core package, so you might want to use it to join URL parts.

```<code>    string url;
    url = Energy.Base.Url.Combine("https://www.youtube.com", "watch?v=NHCgbs3TcYg");
    Console.WriteLine(url);
    url = Energy.Base.Url.Combine("https://www.youtube.com", "watch?v=NHCgbs3TcYg", "t=150");
    Console.WriteLine(url);
</code>```

Additionally it will recognize parameter part, so it will work as you might expect (joining path with slash and parameters with ampersand).

https://www.youtube.com/watch?v=NHCgbs3TcYg

https://www.youtube.com/watch?v=NHCgbs3TcYg&t=150

Documentation for Energy.Base.Url class

Package on NuGet gallery

Code sample

OwnerDisplayName: Brian MacKay

Title: Path.Combine for URLs?

Body:
Path.Combine is handy, but is there a similar function in the .NET framework for URLs?

I'm looking for syntax like this:

Url.Combine("http://MyUrl.com/", "/Images/Image.jpg")

which would return:

"http://MyUrl.com/Images/Image.jpg"

LastEditorDisplayName: Ryan

Body:
Recently Combine method was added to Energy.Core package, so you might want to use it to join URL parts.

    string url;
    url = Energy.Base.Url.Combine("https://www.youtube.com", "watch?v=NHCgbs3TcYg");
    Console.WriteLine(url);
    url = Energy.Base.Url.Combine("https://www.youtube.com", "watch?v=NHCgbs3TcYg", "t=150");
    Console.WriteLine(url);

Additionally it will recognize parameter part, so it will work as you might expect (joining path with slash and parameters with ampersand).

https://www.youtube.com/watch?v=NHCgbs3TcYg

https://www.youtube.com/watch?v=NHCgbs3TcYg&t=150

Documentation for Energy.Base.Url class

Package on NuGet gallery

Code sample


Title: coding variable values into classes using R

Body:
I have a set of data in which I need to code values of certain variables (numeric) into 3 classes.

My data set is similar to this but has 60 more variables:

anim <- c(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)
wt <- c(181,179,180.5,201,201.5,245,246.4,189.3,301,354,369,205,199,394,231.3)
data <- data.frame(anim,wt)

> data
   anim    wt
1     1 181.0
2     2 179.0
3     3 180.5
4     4 201.0
5     5 201.5
6     6 245.0
7     7 246.4
8     8 189.3
9     9 301.0
10   10 354.0
11   11 369.0
12   12 205.0
13   13 199.0
14   14 394.0
15   15 231.3

I need to code values of the variable "wt" up into 3 classes: (wt >= 179 & wt < 200) = 1; (wt >= 200 & wt < 300) = 2; (wt > 300) = 3

which should give me this

> data2
   anim    wt SWT
1     1 181.0   1
2     2 179.0   1
3     3 180.5   1
4     4 201.0   2
5     5 201.5   2
6     6 245.0   2
7     7 246.4   2
8     8 189.3   1
9     9 301.0   3
10   10 354.0   3
11   11 369.0   3
12   12 205.0   2
13   13 199.0   1
14   14 394.0   3
15   15 231.3   2

Body:
You can try cut

anim <- c(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)
wt <-c(181,179,180.5,201,201.5,245,246.4,
189.3,301,354,369,205,199,394,231.3)
data <- data.frame(anim,wt)

EDIT: fixed group - right = FALSE, got rid of split example.

group = cut(data$wt, c(178, 200, 300, Inf), right=FALSE)

data$swt = as.numeric(group)
data
   anim    wt swt
1     1 181.0   1
2     2 179.0   1
3     3 180.5   1
4     4 201.0   2
5     5 201.5   2
6     6 245.0   2
7     7 246.4   2
8     8 189.3   1
9     9 301.0   3
10   10 354.0   3
11   11 369.0   3
12   12 205.0   2
13   13 199.0   1
14   14 394.0   3
15   15 231.3   2
>


问:
Best way to use query params in Spring Boot
I have my JPA repository in place in code which is having multiple findByColumn method for fast lookup.

and few findBy clause with multiple columns like findByColumn1AndColumn2AndColumn3 and many more like or combination of multiple columns like mentioned.

My question is currently I expect my client to use just one endpoint for all the select/fetch operation which expect the RequestParam for each column and if he does not enter any expected column value on the request endpoint I set default as "NA" and I use if clause to identified all the incoming fields params and then call the respected repository endpoint.

I problem is now due to adding multiple new columns in the same endpoint request my if clause if become craze and I am confident enough code will not be user friendly for other team member to read.

Can anyone suggestion how to achieve this dynamic query part on the spring boot app.

My Controller Code snippet look like this:

```<code>@GetMapping("/getdata")
public ResponseEntity<Page<PojoTbl>> getDataFromDB(
        @PageableDefault(page = 0, size = 20) @SortDefault.SortDefaults({
                @SortDefault(sort = "id", direction = Sort.Direction.DESC) }) Pageable pageable,
        @RequestParam(value = "col1", required = false, defaultValue = "NA") String col1,
        @RequestParam(value = "col2", required = false, defaultValue = "NA") String col2,
        @RequestParam(value = "col3", required = false, defaultValue = "NA") String col3,
        @RequestParam(value = "col4", required = false, defaultValue = "NA") String col4,
        @RequestParam(value = "col5", required = false, defaultValue = "NA") String col5,
        @RequestParam(value = "col6", required = false, defaultValue = "NA") String col6,
        @RequestParam(value = "col7", required = false, defaultValue = "NA") String col7,
        @RequestParam(value = "col8", required = false, defaultValue = "NA") String col8,
        @RequestParam(value = "id", required = false) Long id) {

    Page<PojoTbl> selectedRecords = myService.findDataFromDB(col1,
            col2, col3, col4, col5, col6, col7, id,
            col8, pageable);

    return ResponseEntity.ok(selectedRecords);

}
</code>```

Back-end service layer for the same look like this:

```<code>public Page<PojoTbl> findDataFromDB(String col1, String col2,
        String col3, String col4, String col5, String col6,
        String col7, Long id, String col8, Pageable pageable) {

    Page<PojoTbl> selectedRecords = null;

    if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findAll(pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && !col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol8IgnoreCase(col8, pageable);

    } else if (!col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol1IgnoreCase(col1, pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && !col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol5IgnoreCase(col5, pageable);

    } else if (col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol2IgnoreCase(col2, pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && !col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol6IgnoreCase(col6, pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && !col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol7IgnoreCase(col7, pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && !Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findById(id, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol1Andcol2IgnoreCase(col1, col2,
                pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && !col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol1Andcol2Andcol5IgnoreCase(col1,
                col2, col5, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && !col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol1Andcol2Andcol6IgnoreCase(col1,
                col2, col6, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && !col6.equals("NA") && !col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository
                .findBycol1Andcol2Andcol6Andcol7IgnoreCase(col1, col2,
                        col6, col7, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && !col3.equals("NA")
            && !col4.equals("NA") && !col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && !col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository
                .findBycol1Andcol2Andcol3Andcol4Andcol6IgnoreCase(
                        col1, col2, col3, col4, col6, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && !col3.equals("NA")
            && !col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && !col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository
                .findBycol1Andcol2Andcol3Andcol4Andcol5IgnoreCase(
                        col1, col2, col3, col4, col5, pageable);

    }
    return selectedRecords;

}
</code>```

See the complexity i have created in the service layer, this will going to create problem in future release as code looks messy due to many check in single if statement.

Hope someone can guide me on the best practices which can reduce the code complexity here.

答:
Though you can use specification as you were suggested in comments, you can try a custom query with @Query annotation.

```<code>public interface PersonRepository extends JpaRepository<PojoTbl, Integer> {

    @Query("select p from PojoTbj p where " +
        "(p.col1 like ?1 or ?1 is null) and " +
        "(p.col2 like ?2 or ?2 is null) and " +
        "(p.col3 like ?3 or ?3 is null) and " +
        "(p.id = ?4 or id is null)"
    )
    Page<PojoTbl> findByParams(String param1, String param2, String param3, Long id, Pageable pageable)

}
</code>```

Using conditions like ?1 is null allows you to pass null for params you don't want to use in the query. So you don't need "NA" value anymore.

```<code>public ResponseEntity<Page<PojoTbl>> getDataFromDB(
        @PageableDefault(page = 0, size = 20) @SortDefault.SortDefaults({
                @SortDefault(sort = "id", direction = Sort.Direction.DESC) }) Pageable pageable,
        @RequestParam(required = false) String col1,
        @RequestParam(required = false) String col2,
        @RequestParam(required = false) String col3,
        @RequestParam(value = "id", required = false, defaultValue=null) Long id) {

    Page<PojoTbl> selectedRecords = myService.findDataFromDB(col1,
            col2, col3, id, pageable);

    return ResponseEntity.ok(selectedRecords);

}
</code>```

@Query accepts any valid JPQL query. That means you can use any JPQL functions with it. For example if you need to ignore case you can use upper function:

```<code>@Query("select p from PojoTbj p where " +
    "(upper(p.col1) like upper(?1) or ?1 is null))
</code>```

Body:
I have my JPA repository in place in code which is having multiple findByColumn method for fast lookup.

and few findBy clause with multiple columns like findByColumn1AndColumn2AndColumn3 and many more like or combination of multiple columns like mentioned.

My question is currently I expect my client to use just one endpoint for all the select/fetch operation which expect the RequestParam for each column and if he does not enter any expected column value on the request endpoint I set default as "NA" and I use if clause to identified all the incoming fields params and then call the respected repository endpoint.

I problem is now due to adding multiple new columns in the same endpoint request my if clause if become craze and I am confident enough code will not be user friendly for other team member to read.

Can anyone suggestion how to achieve this dynamic query part on the spring boot app.

My Controller Code snippet look like this:

@GetMapping("/getdata")
public ResponseEntity<Page<PojoTbl>> getDataFromDB(
        @PageableDefault(page = 0, size = 20) @SortDefault.SortDefaults({
                @SortDefault(sort = "id", direction = Sort.Direction.DESC) }) Pageable pageable,
        @RequestParam(value = "col1", required = false, defaultValue = "NA") String col1,
        @RequestParam(value = "col2", required = false, defaultValue = "NA") String col2,
        @RequestParam(value = "col3", required = false, defaultValue = "NA") String col3,
        @RequestParam(value = "col4", required = false, defaultValue = "NA") String col4,
        @RequestParam(value = "col5", required = false, defaultValue = "NA") String col5,
        @RequestParam(value = "col6", required = false, defaultValue = "NA") String col6,
        @RequestParam(value = "col7", required = false, defaultValue = "NA") String col7,
        @RequestParam(value = "col8", required = false, defaultValue = "NA") String col8,
        @RequestParam(value = "id", required = false) Long id) {

    Page<PojoTbl> selectedRecords = myService.findDataFromDB(col1,
            col2, col3, col4, col5, col6, col7, id,
            col8, pageable);

    return ResponseEntity.ok(selectedRecords);

}

Back-end service layer for the same look like this:

public Page<PojoTbl> findDataFromDB(String col1, String col2,
        String col3, String col4, String col5, String col6,
        String col7, Long id, String col8, Pageable pageable) {

    Page<PojoTbl> selectedRecords = null;

    if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findAll(pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && !col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol8IgnoreCase(col8, pageable);

    } else if (!col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol1IgnoreCase(col1, pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && !col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol5IgnoreCase(col5, pageable);

    } else if (col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol2IgnoreCase(col2, pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && !col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol6IgnoreCase(col6, pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && !col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol7IgnoreCase(col7, pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && !Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findById(id, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol1Andcol2IgnoreCase(col1, col2,
                pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && !col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol1Andcol2Andcol5IgnoreCase(col1,
                col2, col5, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && !col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol1Andcol2Andcol6IgnoreCase(col1,
                col2, col6, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && !col6.equals("NA") && !col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository
                .findBycol1Andcol2Andcol6Andcol7IgnoreCase(col1, col2,
                        col6, col7, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && !col3.equals("NA")
            && !col4.equals("NA") && !col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && !col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository
                .findBycol1Andcol2Andcol3Andcol4Andcol6IgnoreCase(
                        col1, col2, col3, col4, col6, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && !col3.equals("NA")
            && !col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && !col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository
                .findBycol1Andcol2Andcol3Andcol4Andcol5IgnoreCase(
                        col1, col2, col3, col4, col5, pageable);

    }
    return selectedRecords;

}

See the complexity i have created in the service layer, this will going to create problem in future release as code looks messy due to many check in single if statement.

Hope someone can guide me on the best practices which can reduce the code complexity here.

Body:
Though you can use specification as you were suggested in comments, you can try a custom query with @Query annotation.

public interface PersonRepository extends JpaRepository<PojoTbl, Integer> {

    @Query("select p from PojoTbj p where " +
        "(p.col1 like ?1 or ?1 is null) and " +
        "(p.col2 like ?2 or ?2 is null) and " +
        "(p.col3 like ?3 or ?3 is null) and " +
        "(p.id = ?4 or id is null)"
    )
    Page<PojoTbl> findByParams(String param1, String param2, String param3, Long id, Pageable pageable)

}

Using conditions like ?1 is null allows you to pass null for params you don't want to use in the query. So you don't need "NA" value anymore.

public ResponseEntity<Page<PojoTbl>> getDataFromDB(
        @PageableDefault(page = 0, size = 20) @SortDefault.SortDefaults({
                @SortDefault(sort = "id", direction = Sort.Direction.DESC) }) Pageable pageable,
        @RequestParam(required = false) String col1,
        @RequestParam(required = false) String col2,
        @RequestParam(required = false) String col3,
        @RequestParam(value = "id", required = false, defaultValue=null) Long id) {

    Page<PojoTbl> selectedRecords = myService.findDataFromDB(col1,
            col2, col3, id, pageable);

    return ResponseEntity.ok(selectedRecords);

}

@Query accepts any valid JPQL query. That means you can use any JPQL functions with it. For example if you need to ignore case you can use upper function:

@Query("select p from PojoTbj p where " +
    "(upper(p.col1) like upper(?1) or ?1 is null))


问:
should I always use one of pip install editable or setup.py develop in my project?
When exactly should I use either pip install -e or python setup.py develop in my project? is it always necessary to do one of them? can't I just work with the scripts themselves as they are?

related to
"pip install --editable ./" vs "python setup.py develop"

答:
You have to use them only when you need to import something from a sibling folder or any other import problems.

Take a look at this thread


答:
By your repository methods I understand you are doing basic findBy for the fields that are available.

Take a look at Query by Example (QBE): documentation

Essentially you are filling all the present fields to the entity and ask for an Example type back.

Although this approach helps the dynamic nature of your case it comes with some limitations as are described in the documentation link above.

Title: Best way to use query params in Spring Boot


问:
How to change item color in javascript?
How can I change the item color when being clicked in Javascript? Not allowed to change in HTML nor CSS.

Tried this but not working:

```<code>$("#image1").click(
        function(){
            $(this).css({color:'white'});
        },
        function(){
            $(this).css({color:'black'});
        }
    );
</code>```

However, it does work when I tried the alert function:

```<code>$("#image1").click(
        function(){
            alert("Hello");
        }
    );
</code>```

HTML (item is a dot created in css, 20px):

```<code><div id="funded_courses">
            <span id="image1" class="dot"></span>
            <span id="image2" class="dot"></span>
            <span id="image3" class="dot"></span>
            <span id="image4" class="dot"></span>
</div>
</code>```

答:
```<code>$("#image1").click(function(){
    var default_color = "black";
    $(".dot").each(function( index ) {
        $(this).css("color": default_color);
    });
    $(this).css("color":"white");
});
</code>```

Title: How to change item color in javascript?

Body:
How can I change the item color when being clicked in Javascript? Not allowed to change in HTML nor CSS.

Tried this but not working:

$("#image1").click(
        function(){
            $(this).css({color:'white'});
        },
        function(){
            $(this).css({color:'black'});
        }
    );

However, it does work when I tried the alert function:

$("#image1").click(
        function(){
            alert("Hello");
        }
    );

HTML (item is a dot created in css, 20px):


问:
Lisp changes function to lambda expression when stored in function cell
In this post, I ask tangentially why when I declare in SBCL

```<code>(defun a (&rest x)
    x)
</code>```

and then check what the function cell holds

```<code>(describe 'a)
COMMON-LISP-USER::A
  [symbol]

A names a compiled function:
  Lambda-list: (&REST X)
  Derived type: (FUNCTION * (VALUES LIST &OPTIONAL))
  Source form:
    (LAMBDA (&REST X) (BLOCK A X))
</code>```

I see this particular breakdown of the original function. Could someone explain what this output means? I'm especially confused by the last line

```<code> Source form:
        (LAMBDA (&REST X) (BLOCK A X))
</code>```

This is mysterious because for some reason not clear to me Lisp has transformed the original function into a lambda expression. It would also be nice to know the details of how a function broken down like this is then called. This example is SBCL. In Elisp

```<code>(symbol-function 'a)
</code>```

gives

```<code>(lambda (&rest x) x)
</code>```

again, bizarre. As I said in the other post, this is easier to understand in Scheme -- but that created confusion in the answers. So once more I ask, Why has Lisp taken a normal function declaration and seemingly stored it as a lambda expression?

答:
DEFUN is a defining macro. Macros transform code.

In Common Lisp:

```<code>(defun foo (a)
  (+ a 42))
</code>```

Above is a definition form, but it will be transformed by DEFUN into some other code.

The effect is similar to

```<code>(setf (symbol-function 'foo)
      (lambda (a)
        (block foo
          (+ a 42))))
</code>```

Above sets the function cell of the symbol FOO to a function. The BLOCK construct is added by SBCL, since in Common Lisp named functions defined by DEFUN create a BLOCK with the same name as the function name. This block name can then be used by RETURN-FROM to enable a non-local return from a specific function.

Additionally DEFUN does implementation specific things. Implementations also record development information: the source code, the location of the definition, etc.

Scheme has DEFINE:

```<code>(define (foo a)
  (+ a 10))
</code>```

This will set FOO to a function object.

Body:
In this post, I ask tangentially why when I declare in SBCL

(defun a (&rest x)
    x)

and then check what the function cell holds

(describe 'a)
COMMON-LISP-USER::A
  [symbol]

A names a compiled function:
  Lambda-list: (&REST X)
  Derived type: (FUNCTION * (VALUES LIST &OPTIONAL))
  Source form:
    (LAMBDA (&REST X) (BLOCK A X))

I see this particular breakdown of the original function. Could someone explain what this output means? I'm especially confused by the last line

 Source form:
        (LAMBDA (&REST X) (BLOCK A X))

This is mysterious because for some reason not clear to me Lisp has transformed the original function into a lambda expression. It would also be nice to know the details of how a function broken down like this is then called. This example is SBCL. In Elisp

(symbol-function 'a)

gives

(lambda (&rest x) x)

again, bizarre. As I said in the other post, this is easier to understand in Scheme -- but that created confusion in the answers. So once more I ask, Why has Lisp taken a normal function declaration and seemingly stored it as a lambda expression?

Body:
DEFUN is a defining macro. Macros transform code.

In Common Lisp:

(defun foo (a)
  (+ a 42))

Above is a definition form, but it will be transformed by DEFUN into some other code.

The effect is similar to

(setf (symbol-function 'foo)
      (lambda (a)
        (block foo
          (+ a 42))))

Above sets the function cell of the symbol FOO to a function. The BLOCK construct is added by SBCL, since in Common Lisp named functions defined by DEFUN create a BLOCK with the same name as the function name. This block name can then be used by RETURN-FROM to enable a non-local return from a specific function.

Additionally DEFUN does implementation specific things. Implementations also record development information: the source code, the location of the definition, etc.

Scheme has DEFINE:

(define (foo a)
  (+ a 10))

This will set FOO to a function object.


答:
Access is denied since the node_modules folder is owned by root. Changing permissions of this folder should resolve the error.

First check who owns this directory and then change permissions accordingly.

Cmd to check owner:

```<code>ls -la /usr/local/lib/node_modules
</code>```

Then, change user using the below cmd:

```<code>sudo chown -R [owner]:[owner] /usr/local/lib/node_modules
</code>```

EDIT:

Using sudo to install the package will also resolve the error but changing permissions of the Directory will help in the long run, so you don't run into the same error while installing a different package.

Title: I have a problem when I install angular on terminal macOs

Body: I would like to install angular but when I type the command these error messages appear. Could someone help me? Thank you in advance. enter image description here

Body:
Access is denied since the node_modules folder is owned by root. Changing permissions of this folder should resolve the error.

First check who owns this directory and then change permissions accordingly.

Cmd to check owner:

ls -la /usr/local/lib/node_modules

Then, change user using the below cmd:

sudo chown -R [owner]:[owner] /usr/local/lib/node_modules

EDIT:

Using sudo to install the package will also resolve the error but changing permissions of the Directory will help in the long run, so you don't run into the same error while installing a different package.