SQL構文エラーが治らない

package
1
2import java.sql.Connection;
3import java.sql.PreparedStatement;
4import java.sql.ResultSet;
5import java.util.ArrayList;
6
7public class Model {
8	private String q;
9	private String c;
10	private ArrayList<TitleListResult> results;
11
12	public void execute() {
13		System.out.println("Hello");
14
15		try {
16			Connection conn = ConnectUtilMy.connectDatabase();
17
18			// 住所の検索
19			String sql = "SELECT title_name,years_publication,performance_income,production_cost,public_evaluation,screening_time,genre_name,original_name,cast_name,directed_name "
20					+ "FROM movie "
21					+ "inner join original_list on movie.original_number=original_list.original_number "
22					+ "inner join genre_list on movie.genre_number=genre_list.genre_number "
23					+ "inner join directed_list on movie.directed_number=directed_list.directed_number "
24					+ "inner join cast_list on movie.cast_number=cast_list.cast_number";
25
26			PreparedStatement pstmt;
27			if (c.equals("全て")) {
28				sql += "where title_name like ? or directed_name like ? or cast_name like ?";
29				pstmt = conn.prepareStatement(sql);
30				pstmt.setString(1, q + "%");
31				pstmt.setString(2, q + "%");
32				pstmt.setString(3, q + "%");
33
34			} else if (c.equals("タイトル名")) {
35				sql += "where title_name like ?";
36				pstmt = conn.prepareStatement(sql);
37				pstmt.setString(1, q + "%");
38
39			} else if (c.equals("監督名")) {
40				sql += "where directed_name like ?";
41				pstmt = conn.prepareStatement(sql);
42				pstmt.setString(1, q + "%");
43
44			} else if (c.equals("主演名")) {
45				sql += "where cast_name like ?";
46				pstmt = conn.prepareStatement(sql);
47				pstmt.setString(1, q + "%");
48
49			} else {
50				throw new RuntimeException("選択が不正です");
51
52			}
53			pstmt.setMaxRows(50);
54			ResultSet rs = pstmt.executeQuery();
55
56			// 検索結果の取得
57			results = new ArrayList<TitleListResult>();
58			
59			while (rs.next()) {
60				TitleListResult result = new TitleListResult();
61				result.Set_movie_name(rs.getString("title_name"));
62				result.Set_years_publication(rs.getString("years_publication"));
63				result.Set_performance_income(rs.getDouble("performance_income"));
64				result.Set_Production_cost(rs.getDouble("production_cost"));
65				result.Set_Public_evaluation(rs.getDouble("public_evaluation"));
66				result.Set_Screening_time(rs.getString("screening_time"));
67				result.Set_Genre_name(rs.getString("genre_number"));
68				result.Set_original_name(rs.getString("original_name"));
69				result.Set_cast_name(rs.getString("cast_name"));
70				result.Set_directed_name(rs.getString("directed_name"));
71				results.add(result);
72			}
73
74			// 切断
75			rs.close();
76			pstmt.close();
77			conn.close();
78		} catch (Exception ex) {
79			ex.printStackTrace();
80		}
81	}
82
83	public ArrayList<TitleListResult> getResults() {
84		return results;
85	}
86
87	public void setQ(String q) {
88		this.q = q;
89	}
90
91	public void setC(String c) {
92		this.c = c;
93	}
94}
95

こちらのModel.javaファイルを実行するとトムキャットに

Hello
java.sql.SQLSyntaxErrorException: SQL構文エラーです。バージョンに対応するマニュアルを参照して正しい構文を確認してください。 : 'title_name like 'ア%'' 付近 1 行目
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
        at movie.Model.execute(Model.java:55)
        at movie.Controller.doPost(Controller.java:33)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.base/java.lang.Thread.run(Thread.java:832)

このようなエラーが出てしまいます。

前方一致の検索なのですがsql構文をどうしたらうまく通るでしょうか？

中身のcreate tableです

create table movie(
movie_number int,
title_name varchar(400) not null,
years_publication char(4) not null,
performance_income varchar(50) not null,
production_cost varchar(50) not null,
public_evaluation double not null,
screening_time varchar(10) not null,
genre_number int not null,
directed_number int not null,
original_number varchar(400) not null,
cast_number int not null,
constraint MV primary key(movie_number),
constraint FK_1 foreign key(genre_number) references genre_list(genre_number),
 
constraint FK_2 foreign key(directed_number) references  directed_list(directed_number),
 
constraint FK_3 foreign key(original_number) references original_list(original_number),
constraint FK_4 foreign key(cast_number) references cast_list(cast_number));






create table genre_list(
genre_name varchar(400) not null ,
genre_number int auto_increment ,
constraint GN primary key(genre_number));



create table directed_list(
directed_name varchar(400) not null ,
directed_number int auto_increment ,
constraint DI primary key(directed_number));

create table original_list(
original_name varchar(400) not null ,
original_number int auto_increment ,
constraint OI primary key(original_number));



create table prodction_list(
prodction_name varchar(400) not null ,
prodction_number int  auto_increment,
constraint PR primary key(prodction_number));






create table cast_list(
cast_name varchar(400) not null ,
cast_number int  auto_increment,
constraint MA primary key(cast_number));

検索画面はこのようなイメージです

Orlofsky

2020/08/22 04:43

エラーメッセージはキャプチャではなく、https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。

Orlofsky

2020/08/22 04:44

質問に各テーブルの定義情報として CREATE TABLEを https://teratail.com/help/question-tips#questionTips3-7 の [コード] で追記してください。

行動規範の内容に同意します

回答2件

ベストアンサー

ざっと見たところ、

SQL
1// 住所の検索
2String sql = "SELECT title_name,years_publication,performance_income,production_cost,public_evaluation,screening_time,genre_name,original_name,cast_name,directed_name "
3    + "FROM movie "
4    + "inner join original_list on movie.original_number=original_list.original_number "
5    + "inner join genre_list on movie.genre_number=genre_list.genre_number "
6    + "inner join directed_list on movie.directed_number=directed_list.directed_number "
7    + "inner join cast_list on movie.cast_number=cast_list.cast_number";

一番最後の行にスペースが入っていないので、その次に追加される文字列がくっついてしまっておかしな状況になっているんじゃないでしょうか。
一度文字列sqlをログに出力するなどして、ちゃんとSQLとして成立するものになっているか確認されてみてはどうでしょうか。

投稿2020/08/22 05:01

編集2020/08/22 05:04

KoichiSugiyama

総合スコア3041

kaibasira

2020/08/22 05:12

ありがとうございます！エラーが違う文章になりました。

m.ts10806

2020/08/22 08:10

それ、解決したと言えるんだろうか。

行動規範の内容に同意します

cast_list.cast_number の後ろに空白がないので、where がエラーになっているのでは？

SQL
1                    + "inner join cast_list on movie.cast_number=cast_list.cast_number";
2                                   ↓
3                    + "inner join cast_list on movie.cast_number=cast_list.cast_number ";