[JAVA] 자바 MYSQL 삽입,삭제,수정,검색 [펌]

[JAVA] 자바 MYSQL 삽입,삭제,수정,검색 [펌]

 

구문객체를 던지는 3가지 방법

1. execute -> 테이블 생성, 수정,삭제 등 데이터베이스 관리 명령어 사용    create..

2. excuteUpdate -> 레코드 삽입 수정 삭제등 데이터 조작 명령어 사용    insert, update, delete..

3. excuteQuery -> 레코드 조회, 테이블 조회 등 조회 명령어 사용    selete..

 

[ ResultSet 을 통해 쿼리 결과를 가져 올 수 있다. 79번줄 참고! ]

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138

import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;   public class SqlTest {     Connection conn = null;     Statement stmt = null;     String table;       public SqlTest(Connection conn, String table) {         this.conn = conn;         this.table = table;         try {             this.stmt = conn.createStatement();         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         }     }       // 삽입     public void insert(int id, String name, int grade) {         StringBuilder sb = new StringBuilder();         String sql = sb.append("insert into " + table + " values(")                 .append(id + ",")                 .append("'" + name + "',")                 .append(grade)                 .append(");")                 .toString();         try {             stmt.executeUpdate(sql);         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         }     }       // 삭제     public void delete(int id) {         StringBuilder sb = new StringBuilder();         String sql = sb.append("delete from " + table + " where id = ")                 .append(id)                 .append(";")                 .toString();         try {             stmt.executeUpdate(sql);         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         }     }       // 수정     public void update(int id, String name, int grade) {         StringBuilder sb = new StringBuilder();         String sql = sb.append("update " + table + " set")                 .append(" name = ")                 .append("'" + name + "',")                 .append(" grade = ")                 .append(grade)                 .append(" where id = ")                 .append(id)                 .append(";").toString();         try {             stmt.executeUpdate(sql);         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         }     }       // 모든 검색     public void selectAll() {         StringBuilder sb = new StringBuilder();         String sql = sb.append("select * from " + table)                 .append(";").toString();         try {             ResultSet rs = stmt.executeQuery(sql);                          System.out.print("id");             System.out.print("\t");             System.out.print("name");             System.out.print("\t");             System.out.print("grade");             System.out.print("\n");             System.out.println("────────────────────────");                            while(rs.next()){                      System.out.print(rs.getInt("id"));                      System.out.print("\t");                      System.out.print(rs.getString("name"));                      System.out.print("\t");                      System.out.print(rs.getString("grade"));                      System.out.print("\n");                 }                      } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         }     }       // 검색     public void select(int id) {         StringBuilder sb = new StringBuilder();         String sql = sb.append("select * from " + table + " where")                 .append(" id = ")                 .append(id)                 .append(";").toString();         try {             ResultSet rs = stmt.executeQuery(sql);                          System.out.print("id");             System.out.print("\t");             System.out.print("name");             System.out.print("\t");             System.out.print("grade");             System.out.print("\n");             System.out.println("────────────────────────");                            while(rs.next()){                      System.out.print(rs.getInt("id"));                      System.out.print("\t");                      System.out.print(rs.getString("name"));                      System.out.print("\t");                      System.out.print(rs.getString("grade"));                      System.out.print("\n");                 }                      } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         }     }   }   Colored by Color Scripter

cs

 

모델을 이용한 방법

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

public class Student {     //모델 1:1 대응     private int id;     private String name;     private int grade;          public Student(int id,String name,int grade) {         this.id = id;         this.name = name;         this.grade = grade;     }          public Student(){};          public int getId() {         return id;     }     public void setId(int id) {         this.id = id;     }     public String getName() {         return name;     }     public void setName(String name) {         this.name = name;     }     public int getGrade() {         return grade;     }     public void setGrade(int grade) {         this.grade = grade;     }          @Override     public String toString() {         return "Student [id=" + id + ", name=" + name + ", grade=" + grade + "]";     } }   Colored by Color Scripter

cs

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;   // 이 클래스는 상태로써 디비와의 연결 객체를 가지고 // 연결객체를 사용해서 Student테이블에 데이터를  // insert, delect, update, select.. 하는 기능을 수행해줌   public class StudentDao {       private Connection conn;     private static final String USERNAME = "root";     private static final String PASSWORD = "mysql";     private static final String URL = "jdbc:mysql://localhost/min";       public StudentDao() {         // connection객체를 생성해서 디비에 연결해줌..         try {             Class.forName("com.mysql.jdbc.Driver");               conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);           } catch (ClassNotFoundException e) {             // TODO Auto-generated catch block             e.printStackTrace();             System.out.println("클래스 적재 실패!!");         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();             System.out.println("연결 실패!!");         }     }       public void insertStudent(Student student) {         String sql = "insert into student values(?,?,?);";         PreparedStatement pstmt = null;         try {             pstmt = conn.prepareStatement(sql);             pstmt.setInt(1, student.getId());             pstmt.setString(2, student.getName());             pstmt.setInt(3, student.getGrade());             pstmt.executeUpdate();         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         } finally {             try {                 if (pstmt != null && !pstmt.isClosed())                     pstmt.close();             } catch (SQLException e) {                 // TODO Auto-generated catch block                 e.printStackTrace();             }         }     }       public void updateStudent(Student student) {         String sql = "update student set name=?, grade=? where id = ?;";         PreparedStatement pstmt = null;         try {             pstmt = conn.prepareStatement(sql);             pstmt.setString(1, student.getName());             pstmt.setInt(2, student.getGrade());             pstmt.setInt(3, student.getId());             pstmt.executeUpdate();         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         } finally {             try {                 if (pstmt != null && !pstmt.isClosed())                     pstmt.close();             } catch (SQLException e) {                 // TODO Auto-generated catch block                 e.printStackTrace();             }         }     }       public void deleteStudent(int id) {         String sql = "delete from student where id = ?;";         PreparedStatement pstmt = null;         try {             pstmt = conn.prepareStatement(sql);             pstmt.setInt(1, id);             pstmt.executeUpdate();         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         } finally {             try {                 if (pstmt != null && !pstmt.isClosed())                     pstmt.close();             } catch (SQLException e) {                 // TODO Auto-generated catch block                 e.printStackTrace();             }         }     }       public Student selectOne(int id) {         String sql = "select * from student where id = ?;";         PreparedStatement pstmt = null;         Student re = new Student();         try {             pstmt = conn.prepareStatement(sql);             pstmt.setInt(1, id);             ResultSet rs = pstmt.executeQuery();               if (rs.next()) {                 re.setId(rs.getInt("id"));                 re.setName(rs.getString("name"));                 re.setGrade(rs.getInt("grade"));             }         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         } finally {             try {                 if (pstmt != null && !pstmt.isClosed())                     pstmt.close();             } catch (SQLException e) {                 // TODO Auto-generated catch block                 e.printStackTrace();             }         }         return re;     }       public List<Student> slectAll() {         String sql = "select * from student;";         PreparedStatement pstmt = null;           List<Student> list = new ArrayList<Student>();           try {             pstmt = conn.prepareStatement(sql);             ResultSet re = pstmt.executeQuery();               while (re.next()) {                 Student s = new Student();                 s.setId(re.getInt("id"));                 s.setName(re.getString("name"));                 s.setGrade(re.getInt("grade"));                 list.add(s);             }           } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         } finally {             try {                 if (pstmt != null && !pstmt.isClosed())                     pstmt.close();             } catch (SQLException e) {                 // TODO Auto-generated catch block                 e.printStackTrace();             }         }         return list;     } }   Colored by Color Scripter

cs

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

import java.util.List;   public class StudentDB_Test {       public static void main(String[] args) {         // TODO Auto-generated method stub         StudentDao sDao = new StudentDao();           Student a = sDao.selectOne(3);         System.out.println(a.toString());           // List<Student> a = sDao.slectAll();         // for(int i=0;i<a.size();i++){         // Student s = a.get(i);         // System.out.println(s.toString());         // }       } }   Colored by Color Scripter

cs

 

 

출처: https://sime.tistory.com/83 [심이 블로그]