Error 1 could not prepare statement 1 misuse of aggregate count

Using a Where clause on a count can sometimes produce the error misuse of aggregate: COUNT()
I have not checked other providers like SQL Server yet

Code

This is a much simplified version to reproduce the issue

public class Program
{
    public static async Task Main(string[] args)
    {
        await using var connection = new SqliteConnection("Data Source=:memory:");
        connection.Open();

        var loggerFactory = new LoggerFactory();
        loggerFactory.AddProvider(new DebugLoggerProvider());

        var options = new DbContextOptionsBuilder()
            .UseSqlite(connection)
            .UseLoggerFactory(loggerFactory)
            .Options;

        await using var context = new MyDbContext(options);
        await context.Database.EnsureCreatedAsync();

        // First example which works
        var unionQuery = context.MyEntities
            .Select(x => new {x.Id, Name = "MyEntities"})
            .Union(
                context.MyEntities2
                    .Select(x => new {x.Id, Name = "MyEntities2"}));

        _ = await unionQuery
            .GroupBy(x => x.Name)
            .Select(x => new {Name = x.Key, Count = x.Count()})
            .Where(x => x.Count > 0)
            .ToListAsync();


        // Second example which breaks. Suppose I'm building the unionQuery dynamically and there happens to be
        // only one set to include. No actual union occurs but the shape of the query selector is still the same
        unionQuery = context.MyEntities
            .Select(x => new {x.Id, Name = "MyEntities"});

        // So should be able to repeat the below the same as the first example, but this is when I get the error
        _ = await unionQuery
            .GroupBy(x => x.Name)
            .Select(x => new {Name = x.Key, Count = x.Count()})
            .Where(x => x.Count > 0)
            .ToListAsync();
    }
}

public class MyDbContext : DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; }
    public DbSet<MyEntity2> MyEntities2 { get; set; }

    public MyDbContext(DbContextOptions options) : base(options)
    {
    }
}

public class MyEntity
{
    public int Id { get; set; }
}

public class MyEntity2
{
    public int Id { get; set; }
}

First example produces:

SELECT "t"."Name", COUNT(*) AS "Count"
FROM (
    SELECT "m"."Id", 'MyEntities' AS "Name"
    FROM "MyEntities" AS "m"
    UNION
    SELECT "m0"."Id", 'MyEntities2' AS "Name"
    FROM "MyEntities2" AS "m0"
) AS "t"
GROUP BY "t"."Name"
HAVING COUNT(*) > 0

Second (breaking) example produces

SELECT 'MyEntities' AS "Name", COUNT(*) AS "Count"
FROM "MyEntities" AS "m"
WHERE COUNT(*) > 0

Stack trace

Microsoft.EntityFrameworkCore.Query: Error: An exception occurred while iterating over the results of a query for context type 'EfCoreIssues.MyDbContext'.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'misuse of aggregate: COUNT()'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

Provider and version information

EF Core version: 3.1.8
Database provider: Microsoft.EntityFrameworkCore.Sqlite 3.1.8
Target framework: .NET 3.1
Operating system: Windows 10
IDE: Visual Studio 2019 16.7.2

UPDATE: Fixed. Not 100% sure why sqlfiddle.com didn’t like my syntax, but using https://dbfiddle.uk/?rdbms=sqlite_3.27 worked. I did change the last WHERE clause to be based on the id column, so that it only updates the current record, and increments instead of storing max() on records for username.

I’m trying and failing to do something similar as described here (but in SQLite):
Using count in triggers to update another table.

I assume a trigger is the best way to achieve what I want, but am open to alternate suggestions. My SQL experience is… limited, to say the least.

My goal is to have one column with an identifier, say username and another column that is a count of the times username is in the database at the time of the record insertion.

id | username | u_cnt | etc
1  |  MarkC   |   0   | ...
2  |  PeterB  |   0   | ...
3  |  MarkC   |   1   | ...

Here’s a sample of what I’ve been trying:

create trigger count_user
after insert on rec_info
begin
  update rec_info set u_cnt = ( select count(*) from rec_info where username = new.username );
end

I’ve been bashing my head against the docs at https://sqlite.org/lang_createtrigger.html#syntax_restrictions_on_update_delete_and_insert_statements_within_triggers
but I can’t seem to figure out what’s wrong with the count_user statement. Depending on where I’m testing it I’ll get one of the following
near ")": syntax error or could not prepare statement (1 incomplete input), neither of which are that useful to me.

I’ve also tried making u_cnt a generated/computed column ( https://sqlite.org/gencol.html ), but trying to declare the column with:

u_cnt int as (count(username)) stored,

Which gives me misuse of aggregate function count() and trying to add HAVING or GROUP BY led me down a rabbit hole that gave me and headache and didn’t seem to lead to progress.

sqlite> SELECT p1.domain_id, p2.domain_id, COUNT(p1.domain_id) AS d1, COUNT(p2.domain_id) AS d2
   ...> FROM PDB as p1, Interacting_PDBs as i1, PDB as p2, Interacting_PDBs as i2
   ...> WHERE p1.id = i1.PDB_first_id
   ...> AND p2.id = i2.PDB_second_id
   ...> AND i1.id = i2.id
   ...> AND d1>100
   ...> AND d2>100
   ...> ORDER BY d1, d2;
SQL error: misuse of aggregate: 
sqlite>

3 ответа

Лучший ответ

SELECT p1.domain_id, p2.domain_id, COUNT(p1.domain_id) AS d1, COUNT(p2.domain_id) AS d2
    FROM PDB as p1, Interacting_PDBs as i1, PDB as p2, Interacting_PDBs as i2
    WHERE p1.id = i1.PDB_first_id
    AND p2.id = i2.PDB_second_id
    AND i1.id = i2.id
GROUP BY p1.domain_Id, p2.domain_Id
HAVING d1 > 100 AND d2 > 100
ORDER BY d1, d2;

[SOLVED]

I am very new to Spring boot. Project is made to lookup pre-defined database using REST. When I go to the URL that should just return a customer by id, the prepared SQL statement does not seem to match a simple select * from customer where id=1.

I think my Customer and Order class are not correct, as I have added new annotations since my last attempt at this. I used Hibernate documentation user guide to try and implement my first one to many relationship in Java classes.

If there are any suggestions or critiques about anything unrelated, like structure, etc. , I would also like to hear all of it.

Had trouble posting on Stackoverflow because of formatting issues…

Thank you for your time. I very much appreciate helping a newbie MSCS student out.

Github Link

Whitelabel Error Page

This application has no explicit mapping for /error, so you are seeing this as a fallback.
Thu Dec 13 04:57:06 EST 2018
There was an unexpected error (type=Internal Server Error, status=500).
could not prepare statement; SQL [select customer0_.customer_id as customer1_0_0_, customer0_.customer_fname as customer2_0_0_, customer0_.customer_lname as customer3_0_0_, orders1_.customer_order_id as customer4_1_1_, orders1_.order_id as order_id1_1_1_, orders1_.order_id as order_id1_1_2_, orders1_.customer_order_id as customer4_1_2_, orders1_.date_ordered as date_ord2_1_2_, orders1_.date_shipped as date_shi3_1_2_ from customer customer0_ left outer join orders orders1_ on customer0_.customer_id=orders1_.customer_order_id where customer0_.customer_id=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

Console

2018-12-13 04:56:53.946  INFO 7000 --- [           main] com.jkim176.project3.common.AppConfig    : Starting AppConfig on DESKTOP-QD1E0AA with PID 7000 (C:UsersmdDocumentsworkspace-sts-3.9.4.RELEASEproject3targetclasses started by md in C:UsersmdDocumentsworkspace-sts-3.9.4.RELEASEproject3)
2018-12-13 04:56:53.950  INFO 7000 --- [           main] com.jkim176.project3.common.AppConfig    : No active profile set, falling back to default profiles: default
2018-12-13 04:56:54.531  INFO 7000 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data repositories in DEFAULT mode.
2018-12-13 04:56:54.584  INFO 7000 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 48ms. Found 2 repository interfaces.
2018-12-13 04:56:54.859  INFO 7000 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$EnhancerBySpringCGLIB$$8547384c] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2018-12-13 04:56:55.114  INFO 7000 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2018-12-13 04:56:55.132  INFO 7000 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2018-12-13 04:56:55.132  INFO 7000 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/9.0.13
2018-12-13 04:56:55.139  INFO 7000 --- [           main] o.a.catalina.core.AprLifecycleListener   : The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: [C:Program FilesJavajre1.8.0_191bin;C:WINDOWSSunJavabin;C:WINDOWSsystem32;C:WINDOWS;C:/Program Files/Java/jre1.8.0_191/bin/server;C:/Program Files/Java/jre1.8.0_191/bin;C:/Program Files/Java/jre1.8.0_191/lib/amd64;C:Program Files (x86)Common FilesOracleJavajavapath;C:Perl64sitebin;C:Perl64bin;C:ProgramDataOracleJavajavapath;C:Program Files (x86)InteliCLS Client;C:Program FilesInteliCLS Client;C:WINDOWSsystem32;C:WINDOWS;C:WINDOWSSystem32Wbem;C:WINDOWSSystem32WindowsPowerShellv1.0;C:Program Files (x86)IntelIntel(R) Management Engine ComponentsDAL;C:Program FilesIntelIntel(R) Management Engine ComponentsDAL;C:Program Files (x86)IntelIntel(R) Management Engine ComponentsIPT;C:Program FilesIntelIntel(R) Management Engine ComponentsIPT;c:Program FilesMicrosoft SQL Server110ToolsBinn;c:Program Files (x86)Microsoft SQL Server110ToolsBinnManagementStudio;c:Program Files (x86)Microsoft SQL Server110ToolsBinn;c:Program Files (x86)Microsoft SQL Server110DTSBinn;C:Program FilesMicrosoft SQL Server110DTSBinn;C:Program FilesMicrosoft SQL Server130ToolsBinn;C:Program Filesdotnet;C:Program FilesPuTTY;C:Program Files (x86)GtkSharp2.12bin;C:Program Filesnodejs;C:Gradlebin;C:Maven\apache-maven-3.5.3bin;C:WINDOWSSystem32OpenSSH;C:Program FilesIntelWiFibin;C:Program FilesCommon FilesIntelWirelessCommon;C:Program FilesMySQLMySQL Utilities 1.6;C:UsersmdAppDataLocalProgramsPythonPython37-32python.exe;C:UsersmdPycharmProjectsuntitled1venvScripts;C:PHP;C:Program FilesMongoDBServer4.0bin;C:UsersmdAppDataLocalProgramsPythonPython37-32Scripts;C:UsersmdAppDataLocalProgramsPythonPython37-32;C:UsersmdAppDataLocalProgramsPythonLauncher;C:UsersmdAppDataLocalMicrosoftWindowsApps;C:Program FilesIntelWiFibin;C:Program FilesCommon FilesIntelWirelessCommon;C:UsersmdAppDataRoamingnpm;;C:Program FilesJetBrainsPyCharm Community Edition 2018.3bin;;C:STSsts-bundlests-3.9.4.RELEASE;;.]
2018-12-13 04:56:55.237  INFO 7000 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2018-12-13 04:56:55.237  INFO 7000 --- [           main] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 1251 ms
2018-12-13 04:56:55.323  INFO 7000 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2018-12-13 04:56:55.404  INFO 7000 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2018-12-13 04:56:55.587  INFO 7000 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
	name: default
	...]
2018-12-13 04:56:55.743  INFO 7000 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.3.7.Final}
2018-12-13 04:56:55.744  INFO 7000 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2018-12-13 04:56:55.848  INFO 7000 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.4.Final}
2018-12-13 04:56:55.955  INFO 7000 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.H2Dialect
2018-12-13 04:56:56.435  INFO 7000 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2018-12-13 04:56:56.800  INFO 7000 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2018-12-13 04:56:56.831  WARN 7000 --- [           main] aWebConfiguration$JpaWebMvcConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2018-12-13 04:56:56.986  INFO 7000 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2018-12-13 04:56:56.989  INFO 7000 --- [           main] com.jkim176.project3.common.AppConfig    : Started AppConfig in 3.329 seconds (JVM running for 3.986)
2018-12-13 04:57:06.507  INFO 7000 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2018-12-13 04:57:06.508  INFO 7000 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2018-12-13 04:57:06.514  INFO 7000 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 6 ms
2018-12-13 04:57:06.571  WARN 7000 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 42122, SQLState: 42S22
2018-12-13 04:57:06.571 ERROR 7000 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Column "ORDERS1_.CUSTOMER_ORDER_ID" not found; SQL statement:
select customer0_.customer_id as customer1_0_0_, customer0_.customer_fname as customer2_0_0_, customer0_.customer_lname as customer3_0_0_, orders1_.customer_order_id as customer4_1_1_, orders1_.order_id as order_id1_1_1_, orders1_.order_id as order_id1_1_2_, orders1_.customer_order_id as customer4_1_2_, orders1_.date_ordered as date_ord2_1_2_, orders1_.date_shipped as date_shi3_1_2_ from customer customer0_ left outer join orders orders1_ on customer0_.customer_id=orders1_.customer_order_id where customer0_.customer_id=? [42122-197]
2018-12-13 04:57:06.579  INFO 7000 --- [nio-8080-exec-1] o.h.e.internal.DefaultLoadEventListener  : HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: could not prepare statement

application . properties

spring.jpa.hibernate.ddl-auto=none

schema.sql

CREATE TABLE CUSTOMER(
	CUSTOMER_ID BIGINT AUTO_INCREMENT PRIMARY KEY,
	CUSTOMER_FNAME VARCHAR(30) NOT NULL,
	CUSTOMER_LNAME VARCHAR(30) NOT NULL
);

CREATE TABLE ORDERS(
	ORDER_ID BIGINT AUTO_INCREMENT PRIMARY KEY,
	ORDER_CUSTOMER_ID BIGINT NOT NULL,
	DATE_ORDERED DATE NOT NULL,
	DATE_SHIPPED DATE,
	FOREIGN KEY (ORDER_CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID)
);

data.sql

INSERT INTO CUSTOMER(CUSTOMER_FNAME, CUSTOMER_LNAME) VALUES('Gerald', 'McClellan');
INSERT INTO CUSTOMER(CUSTOMER_FNAME, CUSTOMER_LNAME) VALUES('Joe', 'Frazier');
INSERT INTO CUSTOMER(CUSTOMER_FNAME, CUSTOMER_LNAME) VALUES('Orlando', 'Canizales');
INSERT INTO CUSTOMER(CUSTOMER_FNAME, CUSTOMER_LNAME) VALUES('Julian', 'Jackson');
INSERT INTO CUSTOMER(CUSTOMER_FNAME, CUSTOMER_LNAME) VALUES('Ray', 'Leonard');
INSERT INTO CUSTOMER(CUSTOMER_FNAME, CUSTOMER_LNAME) VALUES('Tommy', 'Hearns');
INSERT INTO CUSTOMER(CUSTOMER_FNAME, CUSTOMER_LNAME) VALUES('Roberto', 'Duran');
INSERT INTO CUSTOMER(CUSTOMER_FNAME, CUSTOMER_LNAME) VALUES('Marvin', 'Hagler');
INSERT INTO CUSTOMER(CUSTOMER_FNAME, CUSTOMER_LNAME) VALUES('Mike', 'McCallum');
INSERT INTO CUSTOMER(CUSTOMER_FNAME, CUSTOMER_LNAME) VALUES('James', 'Toney');

INSERT INTO ORDERS(ORDER_CUSTOMER_ID, DATE_ORDERED, DATE_SHIPPED) VALUES(1, '2000-01-01', '2000-01-02');
INSERT INTO ORDERS(ORDER_CUSTOMER_ID, DATE_ORDERED, DATE_SHIPPED) VALUES(1, '2000-02-01', '2000-02-02');
INSERT INTO ORDERS(ORDER_CUSTOMER_ID, DATE_ORDERED, DATE_SHIPPED) VALUES(1, '2000-03-01', '2000-03-02');
INSERT INTO ORDERS(ORDER_CUSTOMER_ID, DATE_ORDERED, DATE_SHIPPED) VALUES(1, '2000-04-01', '2000-04-02');
INSERT INTO ORDERS(ORDER_CUSTOMER_ID, DATE_ORDERED, DATE_SHIPPED) VALUES(1, '2000-05-01', '2000-05-02');
INSERT INTO ORDERS(ORDER_CUSTOMER_ID, DATE_ORDERED, DATE_SHIPPED) VALUES(1, '2000-06-01', '2000-06-02');
INSERT INTO ORDERS(ORDER_CUSTOMER_ID, DATE_ORDERED, DATE_SHIPPED) VALUES(1, '2000-07-01', '2000-07-02');
INSERT INTO ORDERS(ORDER_CUSTOMER_ID, DATE_ORDERED, DATE_SHIPPED) VALUES(1, '2000-08-01', '2000-08-02');
INSERT INTO ORDERS(ORDER_CUSTOMER_ID, DATE_ORDERED, DATE_SHIPPED) VALUES(1, '2000-09-01', '2000-09-02');
INSERT INTO ORDERS(ORDER_CUSTOMER_ID, DATE_ORDERED, DATE_SHIPPED) VALUES(1, '2000-10-01', '2000-10-02');
INSERT INTO ORDERS(ORDER_CUSTOMER_ID, DATE_ORDERED, DATE_SHIPPED) VALUES(1, '2000-11-01', '2000-11-02');
INSERT INTO ORDERS(ORDER_CUSTOMER_ID, DATE_ORDERED, DATE_SHIPPED) VALUES(1, '2000-12-01', '2000-12-02');

Customer.java

@Entity
@Table(name="CUSTOMER")
public class Customer {

	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	@Column(name="CUSTOMER_ID")
	private long id;
	
	@Column(name="CUSTOMER_FNAME")
	private String firstName;
	
	@Column(name="CUSTOMER_LNAME")
	private String lastName;
	
	@OneToMany(mappedBy="customer",
				fetch=FetchType.EAGER,
				cascade=CascadeType.ALL)
	private List<Order> orders = new ArrayList<>();
	
	protected Customer() {}

        //omitted setters/getters
	
	public void addOrder(Order order) {
		orders.add(order);
		order.setCustomer(this);
	}
	
	public void removeOrder(Order order) {
		orders.remove(order);
		order.setCustomer(null);
	}
}

Order.java

@Entity
@Table(name="ORDERS")
public class Order {

	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	@Column(name="ORDER_ID")
	private long id;
	
	@Temporal(TemporalType.DATE)
	@Column(name="DATE_ORDERED")
	private Date dateOrdered;
	
	@Temporal(TemporalType.DATE)
	@Column(name="DATE_SHIPPED")
	private Date dateShipped;
	
	@ManyToOne
	@JoinColumn(name="CUSTOMER_ORDER_ID")
	private Customer customer;
	
	protected Order() {}

	// omitted setters/getters
}

OrderService.java

public interface OrderService {
	Customer findCustomer(long id);
	List<Order> findOrderByCustomer(long id);
}

OrderServiceImpl.java

@Service
public class OrderServiceImpl implements OrderService {
	
	private final CustomerRepository customerRepository;
	private final OrderRepository orderRepository;
	
	@Autowired
	public OrderServiceImpl(CustomerRepository customerRepository, OrderRepository orderRepository) {
		this.customerRepository = customerRepository;
		this.orderRepository = orderRepository;
	}
	
	public Customer findCustomer(long id) {
		Optional<Customer> optionalCustomer = this.customerRepository.findById(id);
		Customer customer;
		if(optionalCustomer.isPresent()) {
			customer = optionalCustomer.get();
		}
		else {
			customer = null;
		}
		return customer;
	}
	
	public List<Order> findOrderByCustomer(long id) {
		List<Order> orders = new ArrayList<>();
		Iterable<Order> it = this.orderRepository.findAll();
		if(it != null) {
			it.forEach(e -> {
				if(e.getCustomer().getId() == id) {
					orders.add(e);
				}
			});
		}
		
		return orders;
	}
}

OrderServiceController.java

@RestController
@RequestMapping(value="/api")
public class OrderServiceController {

	@Autowired
	private OrderService orderService;
	
	@RequestMapping(method=RequestMethod.GET, value="/customer/{id}")
	public Customer getCustomer(@PathVariable("id") long id) {
		return this.orderService.findCustomer(id);
	}
	
	@RequestMapping(method=RequestMethod.GET, value="/orders/{id}")
	public List<Order> getOrderByCustomer(@PathVariable("id") long id) {
		return this.orderService.findOrderByCustomer(id);
	}
}