querydsl | Unified Queries for Java | Object-Relational Mapping library

use diesel::{ExpressionMethods, QueryDsl, QueryResult, RunQueryDsl};
use diesel::dsl::count;
use rocket::serde::json::Json;
use rust_wheel::config::db::config;

pub fn channel_fav_count(req_channel_id: &i64) -> i32 {
    use crate::model::diesel::dolphin::dolphin_schema::article_favorites::dsl::*;
    let connection = config::establish_connection();
    let query = article_favorites
        .filter(channel_id.eq(req_channel_id));
    let query_result = query.select(count(id)).first(&connection);
    return query_result.unwrap_or(0);
}

error[E0277]: the trait bound `i32: FromSql<BigInt, Pg>` is not satisfied
    --> src/service/app/cruise/article/article_fav_service.rs:11:48
     |
11   |     let query_result = query.select(count(id)).first(&connection);
     |                                                ^^^^^ the trait `FromSql<BigInt, Pg>` is not implemented for `i32`

private List<Visit> findVisitByParameters(CustomSearchCommand visit){
List<Visit> allVisits = repository.findAll();
List<Visit> visitList = allVisits.stream()
        .filter(v -> v.getDate().isAfter(visit.getDateFrom()))
        .filter(v -> v.getDate().isBefore(visit.getDateTo()))
        .filter(v -> v.getVeterinarian().getAnimalType().equals(visit.getAnimalType()))
        .filter(v -> v.getVeterinarian().getMedicalSpecialization().equals(visit.getMedicalSpecialization()))
        .collect(Collectors.toList());
if(visitList.isEmpty()) throw new InvalidParameterException("There are not any visits in this time period with" +
        " this animal type and medical Specialization");
return visitList;

package com.test.admintool.userauth.services;

import java.text.SimpleDateFormat;
import java.util.*;

import com.test.admintool.engine.common.AdminToolFunction;
import com.test.admintool.engine.common.AuditTrailMessage;
import com.test.admintool.engine.common.CopyUtil;
import com.test.admintool.engine.service.AdminService;
import com.test.admintool.engine.service.AuditTrailEventService;
import com.test.admintool.engine.utils.SecurityUtil;
import com.test.admintool.userauth.entity.User;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.GenericTypeResolver;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.transaction.annotation.Transactional;

import com.test.admintool.engine.AdminEntity;
import com.test.admintool.engine.exception.EntityNotFoundException;
import com.test.admintool.engine.repository.AdminRepository;
import com.querydsl.core.types.Predicate;

import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.Table;

@Slf4j
public abstract class AdminServiceImpl<T extends AdminEntity<T>> implements AdminService<T> {

    private static final String AUDIT_ACTION_CREATE = "CREATE";
    private static final String AUDIT_ACTION_UPDATE = "UPDATE";
    private static final String AUDIT_ACTION_DELETE = "DELETE";
    private final AdminRepository<T> repository;

    private final AuditTrailEventService auditTrailEventService;

    protected AdminServiceImpl(AdminRepository<T> repository, AuditTrailEventService auditTrailEventService) {
        this.repository = repository;
        this.auditTrailEventService = auditTrailEventService;
    }

    public T get(Long id) throws EntityNotFoundException {
        return repository.findById(id)
                .orElseThrow(() -> new EntityNotFoundException(String.format("Product %s not found", id)));
    }

    @Transactional
    public T update(T updated) throws EntityNotFoundException {
        T dbDomain = get(updated.getId());
        T updatedData = repository.save(updated);
        produceAuditMessageUpdate(dbDomain, updatedData, AUDIT_ACTION_UPDATE);
        return updatedData;
    }
}

#[macro_use]
extern crate diesel;

use diesel::result::Error;

use actix_web::web;
use diesel::{PgConnection, QueryDsl, RunQueryDsl};
use r2d2::{Pool, PooledConnection};
use r2d2_diesel::ConnectionManager;
use schema::tests::dsl::*;

pub mod schema;

pub type MyDBConnectionManager = ConnectionManager<PgConnection>;
pub type MyPool = Pool<MyDBConnectionManager>;
pub type MyDBConnection = PooledConnection<MyDBConnectionManager>;

struct S { }

impl S {
    async fn download_and_store_object(pool: web::Data<MyPool>) -> Result<(), Error>
    {
        let conn_ = pool.get().expect("couldn't get db connection from pool");
        let conn = &conn_;

        let v_data_size: Option<i64> = web::block(move || -> Result<_, Error> {
            Ok(tests.select(b).get_result::<Option<i64>>(&**conn)?)
        }).await.unwrap();

        Ok(())

    }
}

#[actix_web::main]
async fn main() -> std::io::Result<()> {
    Ok(())
}

`NonNull<pq_sys::pg_conn>` cannot be shared between threads safely
within `PooledConnection<ConnectionManager<PgConnection>>`, the trait `Sync` is not implemented for `NonNull<pq_sys::pg_conn>`
required because of the requirements on the impl of `std::marker::Send` for `&PooledConnection<ConnectionManager<PgConnection>>`
required because it appears within the type `[closure@src/main.rs:26:51: 28:10]`rustcE0277

table! {
    tests (seq) {
        seq -> Int4,
        a -> Nullable<Bytea>,
        b -> Nullable<Int8>,
    }
}

o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Warning Code: 0, SQLState: null
o.h.engine.jdbc.spi.SqlExceptionHelper   : Connection.isValid does not support non-zero timeouts, timeout value 5 has been ignored

spring:
  datasource:
    driver-class-name: org.firebirdsql.jdbc.FBDriver
    url: jdbc:firebirdsql://localhost:3050/C:\\path\\to\\my\\database.fdb?encoding=win1252
    username: myuser
    password: mypassword
    hikari:
      connection-timeout: 1000
      login-timeout: 1000
      minimum-idle: 10

  jpa:
    database-platform: org.hibernate.dialect.FirebirdDialect
    hibernate:
      ddl-auto: validate

BooleanBuilder builder = new BooleanBuilder();

builder.and(Expressions.booleanTemplate("function('array_length', {0})", qX.arr)
                .castToNum(Integer.class).gt(0));

    @EqualsAndHashCode(callSuper = true)
    @Entity
    @Table
    @Data
    @NoArgsConstructor
    @TypeDefs({
            @TypeDef(name = "list-array", typeClass = ListArrayType.class)
    })
    public class X extends BaseClass {
    
        // private fields
    
        @Type(type = "list-array")
        @Column(name = "arr", columnDefinition = "bigint[]")
        @ElementCollection
        @OrderColumn
        private List<Long> arr;
    }

> <dependency>
>      <groupId>com.querydsl</groupId>
>      <artifactId>querydsl-apt</artifactId>
>      <version>${querydsl.version}</version> 
> </dependency> 
> <dependency>
>      <groupId>com.querydsl</groupId>
>      <artifactId>querydsl-jpa</artifactId>
>      <version>${querydsl.version}</version> 
> </dependency>
> 
> <plugin>
>      <groupId>com.mysema.maven</groupId>
>      <artifactId>apt-maven-plugin</artifactId>
>      <version>1.1.3</version>
>      <executions>
>          <execution>
>              <phase>generate-sources</phase>
>              <goals>
>                  <goal>process</goal>
>              </goals>
>              <configuration>
>                  <outputDirectory>target/generated-sources/java</outputDirectory>      
> 
>                      <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
>              </configuration>
>           </execution>
>      </executions> 
></plugin>

Stream.of(
    Pair.of(QPatient.patient.name.any().given, Optional.ofNullable(given)),
    Pair.of(QPatient.patient.name.any().family, Optional.ofNullable(family))
)
.filter(pair -> Objects.nonNull(pair.getValue()));

BooleanBuilder booleanBuilder = new BooleanBuilder();
booleanBuilder.and(booleanExpression1).and(booleanExpression2)...

use diesel::prelude::*;
use diesel::query_dsl::methods::LoadQuery;
use diesel::query_builder::{QueryFragment, Query, AstPass};
use diesel::pg::Pg;
use diesel::sql_types::BigInt;
use diesel::QueryId;

pub trait PaginateForQueryFragment: Sized {
    fn paginate(self, page: i64) -> Paginated<Self>;
}

impl<T> PaginateForQueryFragment for T
    where T: QueryFragment<Pg>{
    fn paginate(self, page: i64) -> Paginated<Self> {
        Paginated {
            query: self,
            per_page: 10,
            page,
            is_sub_query: true,
        }
    }
}

#[derive(Debug, Clone, Copy, QueryId)]
pub struct Paginated<T> {
    query: T,
    page: i64,
    per_page: i64,
    is_sub_query: bool
}

impl<T> Paginated<T> {
    pub fn per_page(self, per_page: i64) -> Self {
        Paginated { per_page, ..self }
    }

    
    pub fn load_and_count_pages<U>(self, conn: &PgConnection) -> QueryResult<(Vec<U>, i64)>
        where
            Self: LoadQuery<PgConnection, (U, i64)>,
    {
        let per_page = self.per_page;
        let results = self.load::<(U, i64)>(conn)?;
        let total = results.get(0).map(|x| x.1).unwrap_or(0);
        let records = results.into_iter().map(|x| x.0).collect();
        let total_pages = (total as f64 / per_page as f64).ceil() as i64;
        Ok((records, total_pages))
    }
}

impl<T: Query> Query for Paginated<T> {
    type SqlType = (T::SqlType, BigInt);
}

impl<T> RunQueryDsl<PgConnection> for Paginated<T> {}


impl<T> QueryFragment<Pg> for Paginated<T>
    where
        T: QueryFragment<Pg>,
{
    fn walk_ast(&self, mut out: AstPass<Pg>) -> QueryResult<()> {
        out.push_sql("SELECT *, COUNT(*) OVER () FROM ");
        if self.is_sub_query {
            out.push_sql("(");
        }
        self.query.walk_ast(out.reborrow())?;
        if self.is_sub_query {
            out.push_sql(")");
        }
        out.push_sql(" t LIMIT ");
        out.push_bind_param::<BigInt, _>(&self.per_page)?;
        out.push_sql(" OFFSET ");
        let offset = (self.page - 1) * self.per_page;
        out.push_bind_param::<BigInt, _>(&offset)?;
        Ok(())
    }
}


#[derive(Debug, Clone, Copy, QueryId)]
pub struct QuerySourceToQueryFragment<T> {
    query_source: T,
}

impl<FC, T> QueryFragment<Pg> for QuerySourceToQueryFragment<T>
    where
        FC: QueryFragment<Pg>,
        T: QuerySource<FromClause=FC>,
{
    fn walk_ast(&self, mut out: AstPass<Pg>) -> QueryResult<()> {
        self.query_source.from_clause().walk_ast(out.reborrow())?;
        Ok(())
    }
}

pub trait PaginateForQuerySource: Sized {
    fn paginate(self, page: i64) -> Paginated<QuerySourceToQueryFragment<Self>>;
}

impl<T> PaginateForQuerySource for T
    where T: QuerySource {
    fn paginate(self, page: i64) -> Paginated<QuerySourceToQueryFragment<Self>> {
        Paginated {
            query: QuerySourceToQueryFragment {query_source: self},
            per_page: 10,
            page,
            is_sub_query: false, 
        }
    }
}

#[cfg(test)]
mod test {

    use std::env;
    use diesel::{Connection, ExpressionMethods, PgConnection, QueryDsl, RunQueryDsl};
    use rust_wheel::common::query::pagination::PaginateForQuerySource;
    use crate::model::diesel::rhythm::rhythm_schema::favorites::dsl::favorites;
    use crate::model::diesel::rhythm::rhythm_schema::favorites::like_status;
    use crate::models::Favorites;

    #[test]
    fn page_test(){
        use crate::model::diesel::rhythm::rhythm_schema::favorites::dsl::*;
        use rust_wheel::common::query::pagination::{PaginateForQueryFragment, PaginateForQuerySource};
        let conn = establish_music_connection();
        let query = favorites
            .filter(like_status.eq(1))
            .paginate(1)
            .per_page(10)
            .load::<Favorites>(&conn)
            .expect("query fav failed");

        println!("{:?}", 1);
    }

    pub fn establish_music_connection() -> PgConnection {
        let database_url = std::env::var("MUSIC_DATABASE_URL").expect("MUSIC_DATABASE_URL must be set");
        PgConnection::establish(&database_url).expect(&format!("Error connecting to {}", database_url))
    }
}

error[E0277]: the trait bound `(i64, std::option::Option<i64>, i64, i64, i64, std::string::String, i32, i32, i64, i32, std::option::Option<i32>, std::option::Option<i32>): Queryable<((BigInt, diesel::sql_types::Nullable<BigInt>, BigInt, BigInt, BigInt, Text, Integer, Integer, BigInt, Integer, diesel::sql_types::Nullable<Integer>, diesel::sql_types::Nullable<Integer>), BigInt), Pg>` is not satisfied
    --> src/test/app/music/fav/fav_music.rs:21:14
     |
21   |             .load::<Favorites>(&conn)
     |              ^^^^ the trait `Queryable<((BigInt, diesel::sql_types::Nullable<BigInt>, BigInt, BigInt, BigInt, Text, Integer, Integer, BigInt, Integer, diesel::sql_types::Nullable<Integer>, diesel::sql_types::Nullable<Integer>), BigInt), Pg>` is not implemented for `(i64, std::option::Option<i64>, i64, i64, i64, std::string::String, i32, i32, i64, i32, std::option::Option<i32>, std::option::Option<i32>)`
     |
     = help: the following implementations were found:
               <(A, B, C, D, E, F, G, H, I, J, K, L) as Queryable<(SA, SB, SC, SD, SE, SF, SG, SH, SI, SJ, SK, SL), __DB>>
               <(A, B, C, D, E, F, G, H, I, J, K, L) as Queryable<diesel::sql_types::Record<(SA, SB, SC, SD, SE, SF, SG, SH, SI, SJ, SK, SL)>, Pg>>
note: required because of the requirements on the impl of `Queryable<((BigInt, diesel::sql_types::Nullable<BigInt>, BigInt, BigInt, BigInt, Text, Integer, Integer, BigInt, Integer, diesel::sql_types::Nullable<Integer>, diesel::sql_types::Nullable<Integer>), BigInt), Pg>` for `Favorites`
    --> src/models.rs:14:22
     |
14   | #[derive( Serialize, Queryable, Deserialize,Default)]
     |                      ^^^^^^^^^
15   | // #[table_name = "favorites"]
16   | pub struct Favorites {
     |            ^^^^^^^^^
     = note: required because of the requirements on the impl of `LoadQuery<PgConnection, Favorites>` for `Paginated<diesel::query_builder::SelectStatement<rhythm_schema::favorites::table, query_builder::select_clause::DefaultSelectClause, query_builder::distinct_clause::NoDistinctClause, query_builder::where_clause::WhereClause<diesel::expression::operators::Eq<rhythm_schema::favorites::columns::like_status, diesel::expression::bound::Bound<Integer, i32>>>>>`
note: required by a bound in `load`
    --> /Users/dolphin/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-1.4.8/src/query_dsl/mod.rs:1238:15
     |
1238 |         Self: LoadQuery<Conn, U>,
     |               ^^^^^^^^^^^^^^^^^^ required by this bound in `load`
     = note: this error originates in the derive macro `Queryable` (in Nightly builds, run with -Z macro-backtrace for more info)

#[derive( Serialize, Queryable, Deserialize,Default)]
// #[table_name = "favorites"]
pub struct Favorites {
    pub id: i64,
    pub song_id: Option<i64>,
    pub created_time: i64,
    pub updated_time: i64,
    pub user_id: i64,
    pub source_id: String,
    pub like_status: i32,
    pub source: i32,
    pub playlist_id: i64,
    pub play_count: i32,
    pub fetched_download_url: Option<i32>,
    pub downloaded: Option<i32>
}

-- Drop table

-- DROP TABLE public.favorites;

CREATE TABLE public.favorites (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    song_id int8 NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    user_id int8 NOT NULL,
    source_id varchar NOT NULL,
    like_status int4 NOT NULL,
    "source" int4 NOT NULL,
    playlist_id int8 NOT NULL,
    play_count int4 NOT NULL DEFAULT 1,
    fetched_download_url int4 NULL DEFAULT 0,
    downloaded int4 NULL DEFAULT 0,
    CONSTRAINT favorites_id_seq_pk PRIMARY KEY (id),
    CONSTRAINT unique_idx UNIQUE (source_id, user_id)
);
CREATE UNIQUE INDEX fav_uniq_idx ON public.favorites USING btree (source_id, user_id);

#[cfg(test)]
mod test {

    use std::env;
    use diesel::{Connection, ExpressionMethods, PgConnection, QueryDsl, RunQueryDsl};
    use rust_wheel::common::query::pagination::PaginateForQuerySource;
    use crate::model::diesel::rhythm::rhythm_schema::favorites::dsl::favorites;
    use crate::model::diesel::rhythm::rhythm_schema::favorites::like_status;
    use crate::models::Favorites;

    #[test]
    fn page_test(){
        use crate::model::diesel::rhythm::rhythm_schema::favorites::dsl::*;
        use rust_wheel::common::query::pagination::{PaginateForQueryFragment, PaginateForQuerySource};
        let conn = establish_music_connection();
        let query = favorites
            .filter(like_status.eq(1))
            .paginate(1)
            .per_page(10)
            .load::<Favorites>(&conn)
            .expect("query fav failed");

        println!("{:?}", 1);
    }

    pub fn establish_music_connection() -> PgConnection {
        let database_url = std::env::var("MUSIC_DATABASE_URL").expect("MUSIC_DATABASE_URL must be set");
        PgConnection::establish(&database_url).expect(&format!("Error connecting to {}", database_url))
    }
}

error: cannot find attribute `table_name` in this scope
  --> src/models.rs:15:3
   |
15 | #[table_name = "favorites"]
   |   ^^^^^^^^^^

use rocket::serde::Serialize;
use serde::Deserialize;
use crate::model::diesel::dolphin::dolphin_schema::dashboard;
use crate::model::diesel::rhythm::rhythm_schema::favorites;

#[derive(Insertable, Serialize, Queryable, Deserialize,Default)]
#[table_name = "dashboard"]
pub struct Dashboard {
    pub id: i32,
    pub app_count: i32,
    pub user_count: i32
}

#[derive(Serialize, Queryable, Deserialize,Default)]
#[table_name = "favorites"]
pub struct Favorites {
    pub id: i64,
    pub song_id: String,
    pub created_time: i64
}