teratail
回答率
85.48%
質問するログイン新規登録
トップに関する質問sql文

編集履歴

質問編集履歴

1

エラー

2017/05/17 02:56

投稿

Yoshi--
Yoshi--

スコア62

test CHANGED
File without changes
test CHANGED
@@ -1,3 +1,5 @@
1
+ MutterDAO.java
2
+
1
3
  ```java
2
4
 
3
5
  package dao;
@@ -26,7 +28,9 @@
26
28
 
27
29
 
28
30
 
31
+ //全レコードを取得
32
+
29
- public List<Mutter> findAll() {
33
+ public List<Mutter> findAll() {
30
34
 
31
35
  List<Mutter> mutterList = new ArrayList<Mutter>();
32
36
 
@@ -36,19 +40,27 @@
36
40
 
37
41
 
38
42
 
43
+
44
+
45
+
46
+
47
+ // 並び替え; SELECT 列名 FROM テーブル名 ORDER BY 列名 並び順 (検索結果をTWEET_IDの大きい順DESCに並び替える)
48
+
39
- String sql = "SELECT "
49
+ String sql = "SELECT "
40
50
 
41
51
  + "TWEET_ID AS TWEET_ID,"
42
52
 
43
53
  + "USER_NAME AS USER_NAME,"
44
54
 
45
- + "TWEET.TEXT AS TEXT "
55
+ + "TWEET.TEXT AS TEXT " // AS は別名的な
46
-
56
+
47
- + "FROM TWEET INNER JOIN USER ON TWEET.USER_ID = USER.USER_ID "
57
+ + "FROM TWEET INNER JOIN USER ON TWEET.USER_ID = USER.USER_ID " //結合；選択列リスト FROM テーブルA JOIN テーブルB ON 両テーブルの結合条件
48
58
 
49
59
  + "ORDER BY TWEET_ID DESC";
50
60
 
61
+
62
+
51
- try(PreparedStatement pStmt = conn.prepareStatement(sql)){
63
+ try(PreparedStatement pStmt = conn.prepareStatement(sql)){
52
64
 
53
65
  // SELECTを実行
54
66
 
@@ -86,57 +98,99 @@
86
98
 
87
99
 
88
100
 
101
+
102
+
103
+
104
+
105
+ //つぶやきを追加
106
+
107
+ public boolean create(Mutter mutter) {
108
+
109
+ try(Connection conn = JDBCUtils.getConnection()){
110
+
111
+
112
+
113
+ // INSERT文の準備(idは自動連番なので指定しなくてよい）
114
+
115
+ String sql = "INSERT INTO TWEET (USER_ID,TEXT) VALUES(?, ?)";
116
+
117
+ try(PreparedStatement pStmt = conn.prepareStatement(sql)){
118
+
119
+ // INSERT文中の「?」に使用する値を設定しSQLを完成
120
+
121
+ pStmt.setString(1, mutter.getUserId());
122
+
123
+ pStmt.setString(2, mutter.getText());
124
+
125
+ // INSERT文を実行
126
+
127
+ int result = pStmt.executeUpdate();
128
+
129
+ if (result != 1) {
130
+
131
+ return false;
132
+
133
+ }
134
+
135
+ }
136
+
137
+ } catch (Exception e) {
138
+
139
+ throw new RuntimeException(e);
140
+
141
+ }
142
+
143
+ return true;
144
+
145
+ }
146
+
147
+
148
+
89
149
  }
90
150
 
91
151
  ```
92
152
 
93
153
 
94
154
 
95
- 上記
155
+
96
-
97
- この意味は
156
+
98
-
99
-
100
-
101
- TWEETテーブルに
157
+
102
-
103
- USERテーブルを結合していると思うのですが
158
+
104
-
105
- AS
159
+
106
-
107
- の意味がわかりません
160
+
108
-
109
-
110
-
111
- 本当に
112
-
113
- やりたいことは
114
-
115
- 結合したテーブルから
116
-
117
- TWEET_ID
118
-
119
- USER_NAME
120
-
121
- TEXT
122
-
123
- をとり
124
-
125
- TWEET_IDの大きい順に並べたいです
161
+ CreateTableDAO.java
126
-
127
-
128
-
129
- アドバイスをお願いします
130
-
131
-
132
-
133
-
134
-
135
-
136
162
 
137
163
  ```java
138
164
 
165
+ package dao;
166
+
167
+
168
+
169
+ import java.sql.Connection;
170
+
171
+ import java.sql.Statement;
172
+
173
+
174
+
175
+ import util.JDBCUtils;
176
+
177
+
178
+
179
+ public class CreateTableDAO {
180
+
181
+
182
+
183
+ public void createTable(){
184
+
185
+ try(Connection conn = JDBCUtils.getConnection()){ //データベースに接続、自動的にクローズ
186
+
187
+
188
+
189
+ //Userテーブル作成
190
+
191
+ try (Statement stmt = conn.createStatement()){
192
+
139
- "CREATE TABLE USER(USER_ID int NOT NULL AUTO_INCREMENT,"
193
+ String tableUser = "CREATE TABLE USER(USER_ID int NOT NULL AUTO_INCREMENT,"
140
194
 
141
195
  + "NICK_NAME varchar(100) NOT NULL,"
142
196
 
@@ -148,18 +202,312 @@
148
202
 
149
203
  + "PRIMARY KEY (USER_ID))";
150
204
 
205
+ stmt.executeUpdate(tableUser);
206
+
207
+ }
208
+
209
+
210
+
211
+ //User1作成
212
+
213
+ try (Statement stmt = conn.createStatement()){
214
+
215
+ String User = "INSERT INTO USER (NICK_NAME, E_MAIL, PASS, USER_NAME) VALUES ('竹内','take@gmail.com', '1111', 'take')";
216
+
217
+
218
+
219
+ stmt.executeUpdate(User);
220
+
221
+ }
222
+
223
+
224
+
225
+ //User2作成
226
+
227
+ try (Statement stmt = conn.createStatement()){
228
+
229
+ String User = "INSERT INTO USER (NICK_NAME, E_MAIL, PASS, USER_NAME) VALUES ('吉田','yyy@gmail.com', '2222', 'yoshida')";
230
+
231
+ stmt.executeUpdate(User);
232
+
233
+ }
234
+
235
+
236
+
237
+
238
+
239
+
240
+
241
+
242
+
243
+ //Tweetテーブル作成
244
+
245
+ try (Statement stmt = conn.createStatement()){
246
+
247
+ String tableTweet = "CREATE TABLE TWEET(TWEET_ID int NOT NULL AUTO_INCREMENT,"
248
+
249
+ + "USER_ID int NOT NULL AUTO_INCREMENT,"
250
+
251
+ + "TEXT varchar(255) NOT NULL,"
252
+
253
+ + "PRIMARY KEY (TWEET_ID),"
254
+
255
+ + "FOREIGN KEY (USER_ID) REFERENCES USER (USER_ID))"; // 外部キー FOREIGN KEY (参照元列名) REFERENCES 参照先テーブル名 (参照先列名)
256
+
257
+ stmt.executeUpdate(tableTweet);
258
+
259
+ }
260
+
261
+
262
+
263
+ //tweet作成
264
+
265
+ try (Statement stmt = conn.createStatement()){
266
+
267
+ String tweet = "INSERT INTO TWEET (TEXT) VALUES ('初めまして！！')";
268
+
269
+ stmt.executeUpdate(tweet);
270
+
271
+ }
272
+
273
+
274
+
275
+ //Followテーブル作成
276
+
277
+ try (Statement stmt = conn.createStatement()){
278
+
279
+ String tableFollow = "CREATE TABLE FOLLOW(USER_ID int NOT NULL AUTO_INCREMENT,"
280
+
281
+ + "FOLLOW_ID int NOT NULL,"
282
+
283
+ + "PRIMARY KEY (USER_ID, FOLLOW_ID),"
284
+
285
+ + "FOREIGN KEY (USER_ID) REFERENCES USER (USER_ID),"
286
+
287
+ + "FOREIGN KEY (FOLLOW_ID) REFERENCES USER (USER_ID))";
288
+
289
+ stmt.executeUpdate(tableFollow);
290
+
291
+ }
292
+
293
+
294
+
295
+ //Favoriteテーブル作成
296
+
297
+ try (Statement stmt = conn.createStatement()){
298
+
299
+ String tableFavorite = "CREATE TABLE FAVORITE(USER_ID int NOT NULL AUTO_INCREMENT, "
300
+
301
+ + "TWEET_ID int NOT NULL AUTO_INCREMENT,"
302
+
303
+ + "PRIMARY KEY (USER_ID, TWEET_ID), "
304
+
305
+ + "FOREIGN KEY (USER_ID) REFERENCES USER (USER_ID), "
306
+
307
+ + "FOREIGN KEY (TWEET_ID) REFERENCES TWEET (TWEET_ID))";
308
+
309
+ stmt.executeUpdate(tableFavorite);
310
+
311
+ }
312
+
313
+
314
+
315
+ System.out.println("USER,TWEET, FOLLOW, FAVORITEテーブルを作成しました。");
316
+
317
+
318
+
319
+ }catch(Exception e){
320
+
321
+ throw new RuntimeException(e);
322
+
323
+ }
324
+
325
+ System.out.println("完了");
326
+
327
+ }
328
+
329
+
330
+
331
+ }
332
+
333
+
334
+
151
335
  ```
152
336
 
337
+
338
+
339
+ UserDAO.java
340
+
153
341
  ```java
154
342
 
343
+ package dao;
344
+
345
+
346
+
347
+ import java.sql.Connection;
348
+
349
+ import java.sql.PreparedStatement;
350
+
351
+ import java.sql.ResultSet;
352
+
353
+
354
+
355
+ import model.User;
356
+
357
+ import util.JDBCUtils;
358
+
359
+
360
+
361
+ public class UserDAO {
362
+
363
+
364
+
155
- "CREATE TABLE TWEET(TWEET_ID int NOT NULL AUTO_INCREMENT,"
365
+ public User getUser(String userName, String pass) { //User型
366
+
156
-
367
+ User user = null; //初期化
368
+
369
+
370
+
371
+ //データベースに接続
372
+
157
- + "USER_ID int NOT NULL AUTO_INCREMENT,"
373
+ try (Connection conn = JDBCUtils.getConnection()) {
158
-
159
- + "TEXT varchar(255) NOT NULL,"
374
+
160
-
375
+
376
+
161
- + "PRIMARY KEY (TWEET_ID),"
377
+ // USERテーブルからUSER_NAME,PASSを検索
162
-
378
+
163
- + "FOREIGN KEY (USER_ID) REFERENCES USER (USER_ID))"
379
+ String sql = "SELECT * FROM USER WHERE USER_NAME=? AND PASS=?";
380
+
381
+ try (PreparedStatement pStmt = conn.prepareStatement(sql)) {
382
+
383
+
384
+
385
+ // 文中の「?」に使用する値を設定しSQLを完成
386
+
387
+ pStmt.setString(1, userName); //postで送られたuserNameがデータベース上にあるか検索
388
+
389
+ pStmt.setString(2, pass);
390
+
391
+
392
+
393
+ // SQLを実行してResultSetを受け取る
394
+
395
+ try (ResultSet rs = pStmt.executeQuery()) {
396
+
397
+
398
+
399
+ // ResultSetから結果を取得
400
+
401
+ if (rs.next()) { //値が一つしか存在しないのでif文
402
+
403
+ //nameにデータベース上の検索したUSER_NAMEを代入
404
+
405
+ int userId = rs.getInt("USER_ID");
406
+
407
+ String nickName = rs.getString("NICK_NAME");
408
+
409
+ String eMail = rs.getString("E_MAIL");
410
+
411
+ String pa = rs.getString("PASS");
412
+
413
+ String name = rs.getString("USER_NAME");
414
+
415
+ System.out.println("＜検索結果＞ 該当あり ユーザー名:" + name + "パスワード:" + pa);
416
+
417
+
418
+
419
+ // TODO（ユーザー情報）の生成
420
+
421
+ user = new User(userId, nickName, eMail, pa, name);// 取得した値を詰める
422
+
423
+ }else{
424
+
425
+ System.out.println("＜検索結果＞ 該当なし");
426
+
427
+ }
428
+
429
+ }
430
+
431
+ }
432
+
433
+ } catch (Exception e) {
434
+
435
+ throw new RuntimeException(e);
436
+
437
+ }
438
+
439
+
440
+
441
+ return user; //値を詰めたuserを返す(なかったら初期のnullが返る)
442
+
443
+ }
444
+
445
+
446
+
447
+
448
+
449
+ public boolean createUser(User user) {
450
+
451
+ try (Connection conn = JDBCUtils.getConnection()) {
452
+
453
+
454
+
455
+ // INSERT文の準備(userIdは自動連番なので指定しなくてよい）
456
+
457
+ String sql = "INSERT INTO USER (NICK_NAME,E_MAIL,PASS,USER_NAME)VALUES(?, ?, ?, ?)";
458
+
459
+ try (PreparedStatement pStmt = conn.prepareStatement(sql)) {
460
+
461
+ // INSERT文中の「?」に使用する値を設定しSQLを完成
462
+
463
+ pStmt.setString(1, user.getNickName());
464
+
465
+ pStmt.setString(2, user.getEMail());
466
+
467
+ pStmt.setString(3, user.getPass());
468
+
469
+ pStmt.setString(4, user.getUserName());
470
+
471
+ // INSERT文を実行
472
+
473
+ int result = pStmt.executeUpdate();
474
+
475
+ if (result != 1) {
476
+
477
+ return false;
478
+
479
+ }
480
+
481
+ }
482
+
483
+ } catch (Exception e) {
484
+
485
+ throw new RuntimeException(e);
486
+
487
+ }
488
+
489
+ return true;
490
+
491
+ }
492
+
493
+
494
+
495
+ }
496
+
497
+
164
498
 
165
499
  ```
500
+
501
+
502
+
503
+
504
+
505
+ ![イメージ説明](948f5cd847d895b80b4a7ce2489547b4.png)
506
+
507
+
508
+
509
+
510
+
511
+
512
+
513
+ こんな感じのエラーが出ていまいます