Error 1 could not prepare statement 1 near syntax error

IF EXISTS (SELECT * FROM customers) THEN
    update customers set CustomerName= "David" where ConcactName="Jason";
Else
    -- insert clause;

2 ответа

update customers
    set CustomerName = 'David'
    where ContactName = 'Jason';

UPDATE customers 
SET CustomerName = 'David' 
WHERE ConcactName = 'Jason'
  AND EXISTS ( SELECT NULL
               FROM `Table` );

Использую H2, Spring и Hibernate. Запрос предельно простой «select v from LegalEntity as v». Т.е. выбрать все данные из таблицы. И вот еще схема таблицы если надо, тоже очень простая:

create table if not exists LegalEntity (
  id int primary key auto_increment,
  version int not null,
  name varchar(50) not null,
  inn varchar(50) not null
);

Но падает вот такая ошибка:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [select legalentit0_.id as id1_2_, legalentit0_.inn as inn2_2_, legalentit0_.name as name3_2_, legalentit0_.version as version4_2_ from legal_entity legalentit0_]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

В чем проблема?

P.S. Странный какой-то оно SQL генерирует… Это вроде я пишу HQL (еще не разобрался толком). Ну в общем вызов такой:

em.createQuery("select v from LegalEntity as v", LegalEntity.class).getResultList();

Синтаксическая ошибка при попытке ввода даты и времени

CREATE TABLE Voo_Pac 
(
     codReserva INT NOT NULL PRIMARY KEY,
     DataCont DATE,
     HoraCont TIME
);

INSERT INTO Voo_Pac (codReserva, DataCont, HoraCont)
VALUES (1), (15-08-2019), (12:13:52);

Ответы
2

INSERT INTO Voo_Pac (codReserva, DataCont, HoraCont)
VALUES (1, '2019-08-15', '12:13:52');

INSERT INTO Voo_Pac (codReserva, DataCont, HoraCont)
VALUES 1, '15-08-2019', '12:13:52';

Другие вопросы по теме

В настоящее время я создаю приложение phonegap.

Одна страница содержит форму, которая позволяет зарегистрированному пользователю обновлять базовые настройки. На этой странице у меня есть кнопка «Обновить», которая вызывает функцию JS, которая обновляет базу данных с измененными настройками. Однако, похоже, я не могу получить даже самое основное обновление.

Heres (очень упрощенный) пример.

function updateSettings(tx) {
    tx.executeSql('UPDATE USERS SET (username) VALUES ("edit") WHERE id = 1');
}

db.transaction(updateSettings, errorCB);

Запуск этого (и почти любого другого обновления ive попытался) дает мне эту ошибку:

Error processing SQL: could not prepare statement (1 near "(": syntax error)

Несколько более сложный запрос, который я изначально пытался запустить, был следующим:

tx.executeSql('UPDATE USERS SET (username, password, userId, defaultHandler, autoSync, updateCT, updateHistorical, updateFavorite, signedIn) VALUES ("'+ username +'", "'+ password +'", "plUserId", "Default Handle", "'+ sync +'", "' + updateCaseTypes + '", "' + updateHistoricalCases + '", "' + updateFavoriteCases +'", "1") WHERE id = 1');

Может ли кто-нибудь указать мне в правильном направлении?

Спасибо заранее

[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);
	}
}