MutterDAO.java
java
1package dao; 2 3import java.sql.Connection; 4import java.sql.PreparedStatement; 5import java.sql.ResultSet; 6import java.util.ArrayList; 7import java.util.List; 8 9import model.Mutter; 10import util.JDBCUtils; 11 12public class MutterDAO { 13 14 //全レコードを取得 15 public List<Mutter> findAll() { 16 List<Mutter> mutterList = new ArrayList<Mutter>(); 17 18 try(Connection conn = JDBCUtils.getConnection()){ 19 20 21 22 // 並び替え; SELECT 列名 FROM テーブル名 ORDER BY 列名 並び順 (検索結果をTWEET_IDの大きい順DESCに並び替える) 23 String sql = "SELECT " 24 + "TWEET_ID AS TWEET_ID," 25 + "USER_NAME AS USER_NAME," 26 + "TWEET.TEXT AS TEXT " // AS は別名的な 27 + "FROM TWEET INNER JOIN USER ON TWEET.USER_ID = USER.USER_ID " //結合;選択列リスト FROM テーブルA JOIN テーブルB ON 両テーブルの結合条件 28 + "ORDER BY TWEET_ID DESC"; 29 30 try(PreparedStatement pStmt = conn.prepareStatement(sql)){ 31 // SELECTを実行 32 try(ResultSet rs = pStmt.executeQuery()){ 33 // SELECT文の結果をArrayListに格納 34 while (rs.next()) { 35 int tweetId = rs.getInt("TWEET_ID"); 36 String userName = rs.getString("USER_NAME"); 37 String text = rs.getString("TEXT"); 38 Mutter mutter = new Mutter(tweetId, userName, text); 39 mutterList.add(mutter); 40 } 41 } 42 } 43 } catch (Exception e) { 44 throw new RuntimeException(e); 45 } 46 return mutterList; 47 } 48 49 50 51//つぶやきを追加 52 public boolean create(Mutter mutter) { 53 try(Connection conn = JDBCUtils.getConnection()){ 54 55 // INSERT文の準備(idは自動連番なので指定しなくてよい) 56 String sql = "INSERT INTO TWEET (USER_ID,TEXT) VALUES(?, ?)"; 57 try(PreparedStatement pStmt = conn.prepareStatement(sql)){ 58 // INSERT文中の「?」に使用する値を設定しSQLを完成 59 pStmt.setString(1, mutter.getUserId()); 60 pStmt.setString(2, mutter.getText()); 61 // INSERT文を実行 62 int result = pStmt.executeUpdate(); 63 if (result != 1) { 64 return false; 65 } 66 } 67 } catch (Exception e) { 68 throw new RuntimeException(e); 69 } 70 return true; 71 } 72 73}
CreateTableDAO.java
java
1 package dao; 2 3import java.sql.Connection; 4import java.sql.Statement; 5 6import util.JDBCUtils; 7 8public class CreateTableDAO { 9 10 public void createTable(){ 11 try(Connection conn = JDBCUtils.getConnection()){ //データベースに接続、自動的にクローズ 12 13 //Userテーブル作成 14 try (Statement stmt = conn.createStatement()){ 15 String tableUser = "CREATE TABLE USER(USER_ID int NOT NULL AUTO_INCREMENT," 16 + "NICK_NAME varchar(100) NOT NULL," 17 + "E_MAIL varchar(100) NOT NULL UNIQUE," 18 + "PASS varchar(100) NOT NULL," 19 + "USER_NAME varchar(100) NOT NULL UNIQUE," 20 + "PRIMARY KEY (USER_ID))"; 21 stmt.executeUpdate(tableUser); 22 } 23 24 //User1作成 25 try (Statement stmt = conn.createStatement()){ 26 String User = "INSERT INTO USER (NICK_NAME, E_MAIL, PASS, USER_NAME) VALUES ('竹内','take@gmail.com', '1111', 'take')"; 27 28 stmt.executeUpdate(User); 29 } 30 31 //User2作成 32 try (Statement stmt = conn.createStatement()){ 33 String User = "INSERT INTO USER (NICK_NAME, E_MAIL, PASS, USER_NAME) VALUES ('吉田','yyy@gmail.com', '2222', 'yoshida')"; 34 stmt.executeUpdate(User); 35 } 36 37 38 39 40 //Tweetテーブル作成 41 try (Statement stmt = conn.createStatement()){ 42 String tableTweet = "CREATE TABLE TWEET(TWEET_ID int NOT NULL AUTO_INCREMENT," 43 + "USER_ID int NOT NULL AUTO_INCREMENT," 44 + "TEXT varchar(255) NOT NULL," 45 + "PRIMARY KEY (TWEET_ID)," 46 + "FOREIGN KEY (USER_ID) REFERENCES USER (USER_ID))"; // 外部キー FOREIGN KEY (参照元列名) REFERENCES 参照先テーブル名 (参照先列名) 47 stmt.executeUpdate(tableTweet); 48 } 49 50 //tweet作成 51 try (Statement stmt = conn.createStatement()){ 52 String tweet = "INSERT INTO TWEET (TEXT) VALUES ('初めまして!!')"; 53 stmt.executeUpdate(tweet); 54 } 55 56 //Followテーブル作成 57 try (Statement stmt = conn.createStatement()){ 58 String tableFollow = "CREATE TABLE FOLLOW(USER_ID int NOT NULL AUTO_INCREMENT," 59 + "FOLLOW_ID int NOT NULL," 60 + "PRIMARY KEY (USER_ID, FOLLOW_ID)," 61 + "FOREIGN KEY (USER_ID) REFERENCES USER (USER_ID)," 62 + "FOREIGN KEY (FOLLOW_ID) REFERENCES USER (USER_ID))"; 63 stmt.executeUpdate(tableFollow); 64 } 65 66 //Favoriteテーブル作成 67 try (Statement stmt = conn.createStatement()){ 68 String tableFavorite = "CREATE TABLE FAVORITE(USER_ID int NOT NULL AUTO_INCREMENT, " 69 + "TWEET_ID int NOT NULL AUTO_INCREMENT," 70 + "PRIMARY KEY (USER_ID, TWEET_ID), " 71 + "FOREIGN KEY (USER_ID) REFERENCES USER (USER_ID), " 72 + "FOREIGN KEY (TWEET_ID) REFERENCES TWEET (TWEET_ID))"; 73 stmt.executeUpdate(tableFavorite); 74 } 75 76 System.out.println("USER,TWEET, FOLLOW, FAVORITEテーブルを作成しました。"); 77 78 }catch(Exception e){ 79 throw new RuntimeException(e); 80 } 81 System.out.println("完了"); 82 } 83 84} 85
UserDAO.java
java
1package dao; 2 3import java.sql.Connection; 4import java.sql.PreparedStatement; 5import java.sql.ResultSet; 6 7import model.User; 8import util.JDBCUtils; 9 10public class UserDAO { 11 12 public User getUser(String userName, String pass) { //User型 13 User user = null; //初期化 14 15 //データベースに接続 16 try (Connection conn = JDBCUtils.getConnection()) { 17 18 // USERテーブルからUSER_NAME,PASSを検索 19 String sql = "SELECT * FROM USER WHERE USER_NAME=? AND PASS=?"; 20 try (PreparedStatement pStmt = conn.prepareStatement(sql)) { 21 22 // 文中の「?」に使用する値を設定しSQLを完成 23 pStmt.setString(1, userName); //postで送られたuserNameがデータベース上にあるか検索 24 pStmt.setString(2, pass); 25 26 // SQLを実行してResultSetを受け取る 27 try (ResultSet rs = pStmt.executeQuery()) { 28 29 // ResultSetから結果を取得 30 if (rs.next()) { //値が一つしか存在しないのでif文 31 //nameにデータベース上の検索したUSER_NAMEを代入 32 int userId = rs.getInt("USER_ID"); 33 String nickName = rs.getString("NICK_NAME"); 34 String eMail = rs.getString("E_MAIL"); 35 String pa = rs.getString("PASS"); 36 String name = rs.getString("USER_NAME"); 37 System.out.println("<検索結果> 該当あり ユーザー名:" + name + "パスワード:" + pa); 38 39 // TODO(ユーザー情報)の生成 40 user = new User(userId, nickName, eMail, pa, name);// 取得した値を詰める 41 }else{ 42 System.out.println("<検索結果> 該当なし"); 43 } 44 } 45 } 46 } catch (Exception e) { 47 throw new RuntimeException(e); 48 } 49 50 return user; //値を詰めたuserを返す(なかったら初期のnullが返る) 51 } 52 53 54 public boolean createUser(User user) { 55 try (Connection conn = JDBCUtils.getConnection()) { 56 57 // INSERT文の準備(userIdは自動連番なので指定しなくてよい) 58 String sql = "INSERT INTO USER (NICK_NAME,E_MAIL,PASS,USER_NAME)VALUES(?, ?, ?, ?)"; 59 try (PreparedStatement pStmt = conn.prepareStatement(sql)) { 60 // INSERT文中の「?」に使用する値を設定しSQLを完成 61 pStmt.setString(1, user.getNickName()); 62 pStmt.setString(2, user.getEMail()); 63 pStmt.setString(3, user.getPass()); 64 pStmt.setString(4, user.getUserName()); 65 // INSERT文を実行 66 int result = pStmt.executeUpdate(); 67 if (result != 1) { 68 return false; 69 } 70 } 71 } catch (Exception e) { 72 throw new RuntimeException(e); 73 } 74 return true; 75 } 76 77} 78
こんな感じのエラーが出ていまいます
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/05/16 06:03
2017/05/16 06:49
2017/05/16 07:12
2017/05/16 07:50
2017/05/16 08:18
2017/05/17 02:57
2017/05/17 03:45
2017/05/17 03:58
2017/05/17 08:25
2017/05/18 02:14
2017/05/18 03:04