Derby和SQL请求问题：如何确定默认模式？ [英] Derby and SQL requests issue: how is the default schema determined?

问题描述

我正在尝试在学校项目中使用Derby，但是我遇到了一些问题。
我的数据库被命名为theatreDB，derby 10.2.1，JDBC 3.0.0

I'm trying to use Derby in my school project but i have some issues. my DB is named theaterDB, derby 10.2.1, JDBC 3.0.0

我通过Java发出的每个SQL请求都不会成功...我可以不明白为什么。
例如，请求：
SELECT * FROM User 返回一个异常：

Each SQL request i make through java does not succeed... I can't understand why. For example, the request: SELECT * FROM User returns an exception:

java.sql.SQLSyntaxErrorException: Schema 'ADMIN' does not exist

另一个无效的SQL：

ALTER table TheaterDB。投影添加索引（fk_Projection_Movie（Movie_id））;

语法错误：在第1行第45列遇到。

这里是java：

public Connection getConnection()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
            System.out.println("Connection to: jdbc:derby:C:\\Users\\acouty\\theaterDB;create=true");
            // DriverManager.get
            return DriverManager.getConnection("jdbc:derby:C:\\Users\\acouty\\theaterDB;create=true", "admin", "");
        }
        catch (final Exception e)
        {
            e.printStackTrace();
            return null;
        }
    }
public List<User> listUsers()
    {
        final ArrayList<User> users = new ArrayList<User>();
        System.out.println("List user request");
        try
        {
            final Connection connection = getConnection();
            final String query = "SELECT * FROM User";
            System.out.println("query is : " + query);
            final ResultSet rs = connection.createStatement().executeQuery(query);
            while (rs.next())
            {
                System.out.println("salut");
            }
            return null;
        }
        catch (final Exception e)
        {
            e.printStackTrace();
        }
        return null;
    }

这是.sql：

CREATE TABLE theaterDB."Users" 
(
    id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    name VARCHAR(45) NOT NULL,
    lastname VARCHAR(45) NOT NULL ,
    email VARCHAR(45) NOT NULL ,
    adress VARCHAR(45) NOT NULL ,
    city VARCHAR(45) NOT NULL ,
    zip VARCHAR(45) NOT NULL ,
    login VARCHAR(45) NOT NULL ,
    password VARCHAR(45) NOT NULL ,
    admin SMALLINT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

CREATE  TABLE theaterDB."Movie"
(
  id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
  title VARCHAR(45) NOT NULL ,
  resume VARCHAR(500) ,
  genre VARCHAR(60)  ,
  grade INT  ,
  review_pub VARCHAR(200) ,
  review_gen VARCHAR(200)  ,
  poster VARCHAR(100)  ,
  duration INT  ,
  release_date VARCHAR(45)  ,
  PRIMARY KEY (id) 
);

CREATE  TABLE theaterDB."Projection" (
  id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
  date DATE ,
  length INT ,
  Movie_id INT NOT NULL ,
  price DECIMAL(10,0) ,
  location VARCHAR(45) NOT NULL ,
  place_nbr INT NOT NULL ,
  PRIMARY KEY (id)
);

ALTER TABLE theaterDB."Projection" 
    ADD FOREIGN KEY(Movie_id) 
    REFERENCES theaterDB."Movie" (id) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;

CREATE TABLE  theaterDB."command" 
(
  id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
  Projection_id INT NOT NULL ,
  User_id INT NOT NULL ,
  paid SMALLINT NOT NULL ,
  PRIMARY KEY (id)
 );

 ALTER TABLE theaterDB."command" 
    ADD FOREIGN KEY(Projection_id) 
    REFERENCES theaterDB."Projection" (id) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;

 ALTER TABLE theaterDB."command" 
    ADD FOREIGN KEY(User_id) 
    REFERENCES theaterDB."Users" (id) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;

谢谢

推荐答案

我必须更改executeQuery才能执行，并将Users更改为 THEATERDB。 USERS

以下是工作请求：

SELECT * FROM "THEATERDB"."Users"


insert into "THEATERDB"."Users"(NAME, LASTNAME, EMAIL, ADRESS, CITY, ZIP, LOGIN, PASSWORD, ADMIN) values ('salut', 'salut', 'salut', 'salut', 'salut', 'salut', 'salut', 'salut', 1)

感谢您的帮助：）。

这篇关于Derby和SQL请求问题：如何确定默认模式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！