커뮤니티 사이트 개발 소스코드

jsp,spring,php
posted by 코딩 공부중 2022. 2. 7. 12:40

https://github.com/kimth93/soccerWorld

 

GitHub - kimth93/soccerWorld

Contribute to kimth93/soccerWorld development by creating an account on GitHub.

github.com

java와 jsp 기반의 축구커뮤니티 사이트 개발 프로젝트

크롤링과 머신러닝을 활용한 상세 기능 추가

'jsp,spring,php' 카테고리의 다른 글

jdbc)테이블 내용 삭제  (0) 2020.04.17
jdbc)테이블 내용 수정  (0) 2020.04.17
jdbc)테이블 내용 삽입 예제  (0) 2020.04.17
jdbc)테이블 조회 예제  (0) 2020.04.17
jsp)로그인 서비스 구현 - 2  (0) 2020.01.03

jdbc)테이블 내용 삭제

jsp,spring,php
posted by 코딩 공부중 2020. 4. 17. 11:42

testmember 테이블의 로우를 삭제하는 예제임

 

 

?
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
public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("드라이버 등록성공");           
        }
        catch(ClassNotFoundException e)
        {
            System.out.println("드라이버 등록실패");
            System.exit(0);
        }
        Connection con = null;
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
        String user ="system";  // 오라클 로그인 할때 아이디임.
        String pass = "oracle"; // 오라클 로그인 할때 비번디임.
        //
        try {
            con = DriverManager.getConnection(url,user,pass);
            System.out.println("오라클에 로그인(접속) 성공!!");
        }catch(SQLException e)
        {
            System.err.println("오라클에 로그인(접속) 실패 ㅜㅜ");
            System.exit(0);
        }
//
//
//
/////수정을 위한 where 조건식의 컬럼(필드)에 대응하는 이름정보 입력!
        int _flag = 0;
        int in_id = 0;
        String in_name ="";
        PreparedStatement ps = null;
        while(true) { // 이 무한 반복문은 삭제에 사용할 항목을 정합니다.
            Scanner sc = new Scanner(System.in);
            System.out.println("삭제작업에 사용할 항목을 선택하세요! : 아이디는 1. 이름은 2.입력하세요");
            int _sel = sc.nextInt();
            if (_sel == 1) {
                //Scanner sc = new Scanner(System.in);
                System.out.println("삭제 하고싶은아이디을 입력해주세요!!");
                in_id = sc.nextInt();      
                sc.nextLine(); // 초기화
                _flag = 1;
// 이하의 내용은 아이디를 이용한 조회(select) 처리로직
//---------------------------------------------
                try{
                    String sql = "select count(*) from testmember where id=?"; // 찾아서 성공하면 1나옴
                    ps = con.prepareStatement(sql);
                    ps.setInt(1, in_id);    //in_id변수에 저장한 값 넘겨주기
                    ResultSet rs = ps.executeQuery();       // ☆☆☆☆☆
                    int _count= 0;
                    while(rs.next()) {
                        _count = rs.getInt(1);
                    }
                    if(_count > 0){  // 조회의 결과가 1건 이상 있다는 뜻임
                        System.out.println("삭제 하고싶은 아이디가 있습니다!!");
                        break;
                    }else {
                        System.out.println("삭제 하고싶은 아이디가 없습니다!!");
                        continue;
                        //System.exit(0);
                    }
                 
                } catch(SQLException e) {
                    System.err.println("오라클 쿼리문 조회실패111ㅜㅜ");
                    System.exit(0);
                }
 
//---------------------------------------------
                break;
            }else if (_sel == 2) {
                System.out.println("삭제 하고싶은 이름을 입력해주세요!!");
                in_name = sc.next();  // 홍길동
                sc.nextLine(); //
                _flag = 2;  // 이름으로 수정작업한다.
//
// 이하의 내용은 이름를 이용한 조회(select) 처리로직
                try{
                    String sql = "select count(*) from testmember where name=?"; // 찾아서 성공하면 1나옴
 
                    ps = con.prepareStatement(sql);
                    ps.setString(1, in_name);   
                    ResultSet rs = ps.executeQuery();
                    int _count= 0;
                    while(rs.next()) {
                        _count = rs.getInt(1);
                    }
                 
                    if(_count > 0){  // 조회의 결과가 1건 이상 있다는 뜻임
                        System.out.println("삭제 하고싶은 이름이 있습니다!!");
                        break;
                    }else {
                        System.out.println("삭제 하고싶은 이름이 없습니다!!");
                        continue;
                        //System.exit(0);
                    }
                 
                } catch(SQLException e) {
                    System.err.println("오라클 쿼리문 조회실패111ㅜㅜ");
                    System.exit(0);
                }
                break;
            }else {
                continue;
            }
        }
    //
    //=================================================
    // 
        Scanner sc2 = new Scanner(System.in);
        String name = in_name;
        int id = in_id;
        try{
            //delete
            String sql = "";
            if (_flag == 1) {
                sql = "delete from testmember where id=?";
            } else {
                sql = "delete from testmember where name=?";
            }
            ps = con.prepareStatement(sql);
            if (_flag == 1) {
                ps.setInt(1,id);
            } else {
                ps.setString(1,name);
            }
        //
            int res = ps.executeUpdate();
            if (res > 0) {
                if(_flag ==1) {
                    System.out.println(id +"의 로우(레코드)를 삭제하였습니다. ");
                }else {
                    System.out.println(name +"의 로우(레코드)를 삭제에 실패했습니다. ");
                }
            }else {
                if(_flag ==1) {
                    System.out.println(id +"의 로우(레코드) 삭제에 실패했습니다. ");
                }else {
                    System.out.println(name +"의 로우(레코드) 삭제에 실패했습니다. ");
                }
             
            }
//
/*=========> 이곳 이후는 조회처리입니다. <========= */
//         
            sql = "select * from testmember";  
            ps = con.prepareStatement(sql);         // ☆☆☆☆☆
            ResultSet rs2 = ps.executeQuery();      // ☆☆☆☆☆
            while(rs2.next()) {
                     
                System.out.println(rs2.getInt(1)+"\t"+rs2.getString(2)+"\t"+rs2.getInt(3)+"\t"+rs2.getString(4)+"\t"+rs2.getString(5)); //다
            }
 
        } catch(SQLException e) {
            System.err.println("오라클 조회 쿼리문 실패ㅜㅜ");
            System.exit(0);
        }
    }

'jsp,spring,php' 카테고리의 다른 글

커뮤니티 사이트 개발 소스코드  (0) 2022.02.07
jdbc)테이블 내용 수정  (0) 2020.04.17
jdbc)테이블 내용 삽입 예제  (0) 2020.04.17
jdbc)테이블 조회 예제  (0) 2020.04.17
jsp)로그인 서비스 구현 - 2  (0) 2020.01.03

jdbc)테이블 내용 수정

jsp,spring,php
posted by 코딩 공부중 2020. 4. 17. 11:40

testmember의 로우 내용을 수정하는 예제

?
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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
<code class="hljs php"><span class="hljs-keyword">public</span> <span class="hljs-built_in">static</span> <span class="hljs-keyword">void</span> select(<span class="hljs-keyword">String</span> url, <span class="hljs-keyword">String</span> user, <span class="hljs-keyword">String</span> pass, <span class="hljs-keyword">int</span> id) {
        PreparedStatement ps = <span class="hljs-literal">null</span>;
        <span class="hljs-keyword">try</span>{
            Connection con = DriverManager.getConnection(url,user,pass);
            <span class="hljs-keyword">String</span> sql = <span class="hljs-string">"select * from testmember where id=?"</span>; <span class="hljs-comment">// 찾아서 성공하면 1나옴</span>
            ps = con.prepareStatement(sql);
            ps.setInt(<span class="hljs-number">1</span>, id);   
            ResultSet rs = ps.executeQuery();
            System.out.println(<span class="hljs-string">"수정하고자 하는 "</span>+id+<span class="hljs-string">"의 정보입니다."</span>);
            <span class="hljs-keyword">while</span>(rs.next()) {
                System.out.println(rs.getInt(<span class="hljs-number">1</span>)+<span class="hljs-string">"\t"</span>+rs.getString(<span class="hljs-number">2</span>)+<span class="hljs-string">"\t"</span>+rs.getInt(<span class="hljs-number">3</span>)+<span class="hljs-string">"\t"</span>+rs.getString(<span class="hljs-number">4</span>)+<span class="hljs-string">"\t"</span>+rs.getString(<span class="hljs-number">5</span>));
            }
             
        } <span class="hljs-keyword">catch</span>(SQLException e) {
            System.err.println(<span class="hljs-string">"오라클 쿼리문 조회실패111ㅜㅜ"</span>);
            System.<span class="hljs-keyword">exit</span>(<span class="hljs-number">0</span>);
        }
    }
    <span class="hljs-keyword">public</span> <span class="hljs-built_in">static</span> <span class="hljs-keyword">void</span> select(<span class="hljs-keyword">String</span> url, <span class="hljs-keyword">String</span> user, <span class="hljs-keyword">String</span> pass, <span class="hljs-keyword">String</span> name) {
        PreparedStatement ps = <span class="hljs-literal">null</span>;
         
        <span class="hljs-keyword">try</span>{
            Connection con = DriverManager.getConnection(url,user,pass);
            <span class="hljs-keyword">String</span> sql = <span class="hljs-string">"select * from testmember where name=?"</span>; <span class="hljs-comment">// 찾아서 성공하면 1나옴</span>
            ps = con.prepareStatement(sql);
            ps.setString(<span class="hljs-number">1</span>, name);   
            ResultSet rs = ps.executeQuery();
            System.out.println(<span class="hljs-string">"수정하고자 하는 "</span>+name+<span class="hljs-string">" 학생의 정보입니다."</span>);
            <span class="hljs-keyword">while</span>(rs.next()) {
                System.out.println(rs.getInt(<span class="hljs-number">1</span>)+<span class="hljs-string">"\t"</span>+rs.getString(<span class="hljs-number">2</span>)+<span class="hljs-string">"\t"</span>+rs.getInt(<span class="hljs-number">3</span>)+<span class="hljs-string">"\t"</span>+rs.getString(<span class="hljs-number">4</span>)+<span class="hljs-string">"\t"</span>+rs.getString(<span class="hljs-number">5</span>));
            }
             
        } <span class="hljs-keyword">catch</span>(SQLException e) {
            System.err.println(<span class="hljs-string">"오라클 쿼리문 조회실패111ㅜㅜ"</span>);
            System.<span class="hljs-keyword">exit</span>(<span class="hljs-number">0</span>);
        }
    }
     
    <span class="hljs-keyword">public</span> <span class="hljs-built_in">static</span> <span class="hljs-keyword">void</span> update(<span class="hljs-keyword">String</span> url, <span class="hljs-keyword">String</span> user, <span class="hljs-keyword">String</span> pass, <span class="hljs-keyword">int</span> id) {
        Scanner sc = <span class="hljs-keyword">new</span> Scanner(System.in);
        <span class="hljs-keyword">try</span> {
            Connection conn = DriverManager.getConnection(url,user,pass);
            PreparedStatement ps = <span class="hljs-literal">null</span>;
             
            System.out.println(<span class="hljs-string">"수정할 월급을 입력해주세요"</span>);
            <span class="hljs-keyword">int</span> salary = sc.nextInt();
            System.out.println(<span class="hljs-string">"수정할 이메일 입력해주세요"</span>);
            <span class="hljs-keyword">String</span> email = sc.next();
            System.out.println(<span class="hljs-string">"수정할 전화번호를 입력해주세요"</span>);
            <span class="hljs-keyword">String</span> tel = sc.next();
             
            <span class="hljs-keyword">String</span> sql = <span class="hljs-string">"update testmember set salary=?, email=?, tel=? where hakbun=?"</span>;
            ps = conn.prepareStatement(sql);
             
            ps.setDouble(<span class="hljs-number">1</span>,salary);
            ps.setString(<span class="hljs-number">2</span>, email);
            ps.setString(<span class="hljs-number">3</span>, tel);
            ps.setInt(<span class="hljs-number">4</span>, id);
             
            <span class="hljs-keyword">int</span> res = ps.executeUpdate();
             
            <span class="hljs-keyword">if</span> (res > <span class="hljs-number">0</span>) {
                System.out.println(<span class="hljs-string">"아이디 "</span> + id +<span class="hljs-string">"의 로우(레코드)를 수정하였습니다. "</span>);
            }<span class="hljs-keyword">else</span> {
                System.out.println(<span class="hljs-string">"아이디 "</span> + id +<span class="hljs-string">"의 로우(레코드)수정에 실패했습니다. "</span>);
            }
             
        } <span class="hljs-keyword">catch</span> (SQLException e) {
            System.err.println(<span class="hljs-string">"오라클 쿼리문 조회실패ㅜㅜ"</span>);
            System.<span class="hljs-keyword">exit</span>(<span class="hljs-number">0</span>);
        }
    }
     
    <span class="hljs-keyword">public</span> <span class="hljs-built_in">static</span> <span class="hljs-keyword">void</span> update(<span class="hljs-keyword">String</span> url, <span class="hljs-keyword">String</span> user, <span class="hljs-keyword">String</span> pass, <span class="hljs-keyword">String</span> name) {
        Scanner sc = <span class="hljs-keyword">new</span> Scanner(System.in);
        <span class="hljs-keyword">try</span> {
            Connection conn = DriverManager.getConnection(url,user,pass);
            PreparedStatement ps = <span class="hljs-literal">null</span>;
             
            System.out.println(<span class="hljs-string">"수정할 월급을 입력해주세요"</span>);
            <span class="hljs-keyword">int</span> salary = sc.nextInt();
            System.out.println(<span class="hljs-string">"수정할 이메일 입력해주세요"</span>);
            <span class="hljs-keyword">String</span> email = sc.next();
            System.out.println(<span class="hljs-string">"수정할 전화번호를 입력해주세요"</span>);
            <span class="hljs-keyword">String</span> tel = sc.next();
             
             
            <span class="hljs-keyword">String</span> sql = <span class="hljs-string">"update testmember set salary=?, email=?, tel=? where name=?"</span>;
            ps = conn.prepareStatement(sql);
             
            ps.setDouble(<span class="hljs-number">1</span>,salary);
            ps.setString(<span class="hljs-number">2</span>, email);
            ps.setString(<span class="hljs-number">3</span>, tel);
            ps.setString(<span class="hljs-number">4</span>, name);
             
            <span class="hljs-keyword">int</span> res = ps.executeUpdate();
             
            <span class="hljs-keyword">if</span> (res > <span class="hljs-number">0</span>) {
                System.out.println(<span class="hljs-string">"이름 "</span> + name +<span class="hljs-string">"학생 의 로우(레코드)를 수정하였습니다. "</span>);
            }<span class="hljs-keyword">else</span> {
                System.out.println(<span class="hljs-string">"이름 "</span> + name +<span class="hljs-string">"학생 의 로우(레코드) 수정에 실패했습니다. "</span>);
            }
             
        } <span class="hljs-keyword">catch</span> (SQLException e) {
            System.err.println(<span class="hljs-string">"오라클 쿼리문 조회실패ㅜㅜ"</span>);
            System.<span class="hljs-keyword">exit</span>(<span class="hljs-number">0</span>);
        }
    }
     
    <span class="hljs-keyword">public</span> <span class="hljs-built_in">static</span> <span class="hljs-keyword">int</span> flagIn() {
        Scanner sc = <span class="hljs-keyword">new</span> Scanner(System.in);
        System.out.println(<span class="hljs-string">"수정에 사용할 기준을 선택해주세요! (아이디는  1, 이름은 2를 입력해주세요~)"</span>);
        <span class="hljs-keyword">int</span> flag = sc.nextInt();
        <span class="hljs-keyword">return</span> flag;
    }
 
    <span class="hljs-keyword">public</span> <span class="hljs-built_in">static</span> <span class="hljs-keyword">void</span> main(<span class="hljs-keyword">String</span>[] args) {
         
         
        Scanner sc = <span class="hljs-keyword">new</span> Scanner(System.in);
         
        <span class="hljs-keyword">try</span> {
            <span class="hljs-keyword">Class</span>.forName(<span class="hljs-string">"oracle.jdbc.driver.OracleDriver"</span>);
            System.out.println(<span class="hljs-string">"드라이버 등록성공"</span>);          
        }
        <span class="hljs-keyword">catch</span>(ClassNotFoundException e)
        {
            System.out.println(<span class="hljs-string">"드라이버 등록실패"</span>);
        }
         
        Connection con = <span class="hljs-literal">null</span>;
         
        <span class="hljs-keyword">String</span> url = <span class="hljs-string">"jdbc:oracle:thin:@127.0.0.1:1521:xe"</span>;
        <span class="hljs-keyword">String</span> user =<span class="hljs-string">""</span>;
        <span class="hljs-keyword">String</span> pass = <span class="hljs-string">""</span>;
         
        <span class="hljs-keyword">try</span> {
            con = DriverManager.getConnection(url,user,pass);
            System.out.println(<span class="hljs-string">"오라클에 로그인(접속) 성공!!"</span>);
        }<span class="hljs-keyword">catch</span>(SQLException e)
        {
            System.err.println(<span class="hljs-string">"오라클에 로그인(접속) 실패 ㅜㅜ"</span>);
            System.<span class="hljs-keyword">exit</span>(<span class="hljs-number">0</span>);
        }
         
         
         
        PreparedStatement ps = <span class="hljs-literal">null</span>;
        <span class="hljs-keyword">String</span> sql =<span class="hljs-string">""</span>;
        <span class="hljs-keyword">int</span> flag = <span class="hljs-number">0</span>;
        <span class="hljs-keyword">boolean</span> check = <span class="hljs-literal">true</span>;
         
        <span class="hljs-keyword">while</span>(<span class="hljs-literal">true</span>) {
            flag = flagIn(); <span class="hljs-comment">//원하는 로직 입력하기</span>
            <span class="hljs-keyword">if</span>(flag == <span class="hljs-number">1</span>) {
                <span class="hljs-keyword">while</span>(check) {
                    System.out.println(<span class="hljs-string">"수정하고자 하는 직원의 아이디를 입력해주세요~"</span>);
                    <span class="hljs-keyword">int</span> id = sc.nextInt();
                    <span class="hljs-comment">//select로 체크</span>
                    <span class="hljs-keyword">try</span> {
                        sql = <span class="hljs-string">"select id from testmember"</span>;
                        ps = con.prepareStatement(sql);        
                        ResultSet rs = ps.executeQuery();
                         
                        <span class="hljs-keyword">while</span>(rs.next()) {
                            <span class="hljs-keyword">if</span>(rs.getInt(<span class="hljs-number">1</span>)==id) {
                                select(url,user,pass,id);
                                update(url, user, pass, id);        <span class="hljs-comment">//수정완료</span>
                                check = <span class="hljs-literal">false</span>;
                                <span class="hljs-keyword">break</span>;
                            }
                        }
                        <span class="hljs-keyword">if</span>(check) {
                            System.out.println(<span class="hljs-string">"아이디가 존재하지 않습니다..."</span>);   <span class="hljs-comment">//수정실패</span>
                        }
                    }
                    <span class="hljs-keyword">catch</span>(SQLException e)
                    {
                        System.err.println(<span class="hljs-string">"오라클 쿼리문 조회실패ㅜㅜ"</span>);
                        System.<span class="hljs-keyword">exit</span>(<span class="hljs-number">0</span>);
                    }
                }
            <span class="hljs-keyword">break</span>;
 
            }<span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span>(flag == <span class="hljs-number">2</span>) {
                <span class="hljs-keyword">while</span>(check) {
                    System.out.println(<span class="hljs-string">"수정하고자 하는 학생의 이름을 입력해주세요~"</span>);
                    <span class="hljs-keyword">String</span> name = sc.next();
                    <span class="hljs-keyword">try</span> {
                        sql = <span class="hljs-string">"select name from testmember "</span>;
                        ps = con.prepareStatement(sql);        
                        ResultSet rs = ps.executeQuery();
                         
                        <span class="hljs-keyword">while</span>(rs.next()) {
                            <span class="hljs-keyword">if</span>(rs.getString(<span class="hljs-number">1</span>).equals(name)) {
                                select(url,user,pass,name);
                                update(url, user, pass, name);  <span class="hljs-comment">//수정완료</span>
                                check = <span class="hljs-literal">false</span>;
                                <span class="hljs-keyword">break</span>;
                            }
                        }
                        <span class="hljs-keyword">if</span>(check) {
                            System.out.println(<span class="hljs-string">"이름이 존재하지 않습니다..."</span>);    <span class="hljs-comment">//수정실패</span>
                        }
                    }
                    <span class="hljs-keyword">catch</span>(SQLException e)
                    {
                        System.err.println(<span class="hljs-string">"오라클 쿼리문 조회실패ㅜㅜ"</span>);
                        System.<span class="hljs-keyword">exit</span>(<span class="hljs-number">0</span>);
                    }
                }
                <span class="hljs-keyword">break</span>;
                 
            }<span class="hljs-keyword">else</span> {
                System.out.println(<span class="hljs-string">"아이디는 1, 이름은 2입니다. 다시 입력해주세요..."</span>);
            }
        }
    }
 
</code>

'jsp,spring,php' 카테고리의 다른 글

커뮤니티 사이트 개발 소스코드  (0) 2022.02.07
jdbc)테이블 내용 삭제  (0) 2020.04.17
jdbc)테이블 내용 삽입 예제  (0) 2020.04.17
jdbc)테이블 조회 예제  (0) 2020.04.17
jsp)로그인 서비스 구현 - 2  (0) 2020.01.03

jdbc)테이블 내용 삽입 예제

jsp,spring,php
posted by 코딩 공부중 2020. 4. 17. 11:38

DB에 저장된 testmember테이블에 로우를 삽입하는 예제

 

 

?
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
public static boolean check(String url, String user, String pass, int id){
         
        boolean flag = true;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
     
        try {
            con = DriverManager.getConnection(url,user,pass);
        //System.out.println("오라클에 로그인(접속) 성공!!");
        }catch(SQLException e)
        {
            //System.err.println("오라클에 로그인(접속) 실패 ㅜㅜ");
            System.exit(0);
        }
     
        try {
            String sql = "select id from testmember";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()) {
                if(rs.getInt(1)==id) {
                    return false;
                }else {
                    flag = true;
                }
            }
        }
        catch(SQLException e) {
            System.err.println("오라클 쿼리문 조회실패!!");
            System.exit(0);
        }
        return flag;
    }
 
 
    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("드라이버 등록성공");           
        }
        catch(ClassNotFoundException e)
        {
            System.out.println("드라이버 등록실패");
            System.exit(0);
        }
        Connection con = null;
     
         
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
        String user ="";  // 오라클 로그인 할때 아이디임.
        String pass = ""; // 오라클 로그인 할때 비번디임.
//
        try {
            con = DriverManager.getConnection(url,user,pass);
            System.out.println("오라클에 로그인(접속) 성공!!");
        }catch(SQLException e)
        {
            System.err.println("오라클에 로그인(접속) 실패 ㅜㅜ");
            System.exit(0);
        }
        PreparedStatement ps = null;
     
    //삽입하기
     
        Scanner sc = new Scanner(System.in);
        System.out.println("회원정보를 입력합니다. 아이디를 입력해주세요!(정수만!)");
        int id = sc.nextInt();
        System.out.println("이름을 입력해주세요!!");
        String name = sc.next();
        System.out.println("월급을 입력해주세요");
        int salary = sc.nextInt();
        System.out.println("이메일 주소를 입력해주세요");
        String email = sc.next();
        System.out.println("전화번호를 입력해주세요");
        String tel = sc.next();
         
     
        try {
            while(true) {
                boolean check = check(url,user, pass,id);
                if(check) {
                //insert 수행
                    String sql = "insert into testmember values(?,?,?,?,?)";
                    ps = con.prepareStatement(sql);
                 
                    ps.setInt(1,id);        //(1,3)은 1은 컬럼의 순서
                    ps.setString(2, name);
                    ps.setInt(3, salary);
                    ps.setString(4, email);
                    ps.setString(5, tel);
                //
                    int res = ps.executeUpdate();
                 
                    if(res>0) {
                        System.out.println("아이디는 "+id + " 이름은 "+ name +"인 직원의 정보를 등록했습니다.");
                        break;
                    }else {
                        System.out.println("정보 등록에 실패했습니다...");
                        break;
                    }
                }else {
                    System.out.println("이미 존재하는 아이디 입니다. 다시 입력해주세요(정수만!)");
                    id = sc.nextInt();
                }
            }
        }
        catch(SQLException e)
        {
            System.err.println("오라클 쿼리문 조회실패ㅜㅜ");
            System.exit(0);
        }
 
    }

'jsp,spring,php' 카테고리의 다른 글

jdbc)테이블 내용 삭제  (0) 2020.04.17
jdbc)테이블 내용 수정  (0) 2020.04.17
jdbc)테이블 조회 예제  (0) 2020.04.17
jsp)로그인 서비스 구현 - 2  (0) 2020.01.03
jsp)로그인 서비스 구현 - 1  (0) 2020.01.03

jdbc)테이블 조회 예제

jsp,spring,php
posted by 코딩 공부중 2020. 4. 17. 11:36

DB안에 저장된 testmember 테이블의 로우를 조회하는 예제

?
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
<code class="hljs php"><span class="hljs-keyword">public</span> <span class="hljs-built_in">static</span> <span class="hljs-keyword">void</span> main(<span class="hljs-keyword">String</span>[] args) {
        <span class="hljs-keyword">try</span> {
            <span class="hljs-keyword">Class</span>.forName(<span class="hljs-string">"oracle.jdbc.driver.OracleDriver"</span>);
            System.out.println(<span class="hljs-string">"드라이버 등록 성공"</span>);
        }<span class="hljs-keyword">catch</span>(ClassNotFoundException e)
        {
            System.out.println(<span class="hljs-string">"드라이버 등록 실패"</span>);
         
        }
         
        Connection conn = <span class="hljs-literal">null</span>;
        <span class="hljs-keyword">String</span> url = <span class="hljs-string">"jdbc:oracle:thin:@127.0.0.1:1521:xe"</span>;
        <span class="hljs-keyword">String</span> user = <span class="hljs-string">""</span>; DB계정 입력
        <span class="hljs-keyword">String</span> passwd = <span class="hljs-string">""</span>;<span class="hljs-comment">//비밀번호 입력</span>
         
        <span class="hljs-keyword">try</span> {
            conn = DriverManager.getConnection(url,user,passwd);
            System.out.println(<span class="hljs-string">"오라클 연결에 성공하였습니다."</span>);
             
        }<span class="hljs-keyword">catch</span>(SQLException e)
        {
            System.out.println(<span class="hljs-string">"오라클 연결에 실패했습니다 ㅜ"</span>);
            System.<span class="hljs-keyword">exit</span>(<span class="hljs-number">0</span>);     
        }
         
        PreparedStatement ps = <span class="hljs-literal">null</span>;
        <span class="hljs-keyword">String</span> sql = <span class="hljs-string">""</span>;
        ResultSet rs = <span class="hljs-literal">null</span>;
         
        <span class="hljs-keyword">try</span> {
 
            sql = <span class="hljs-string">"select * from testMember"</span>;
            ps = conn.prepareStatement(sql);   
            rs = ps.executeQuery();
 
             
            <span class="hljs-keyword">while</span>(rs.next()) {
                 
                <span class="hljs-keyword">int</span> id = rs.getInt(<span class="hljs-string">"id"</span>);
                <span class="hljs-keyword">String</span> name = rs.getString(<span class="hljs-string">"name"</span>);
                <span class="hljs-keyword">int</span> salary = rs.getInt(<span class="hljs-string">"salary"</span>);
                <span class="hljs-keyword">String</span> email = rs.getString(<span class="hljs-string">"email"</span>);
                <span class="hljs-keyword">String</span> tel = rs.getString(<span class="hljs-string">"tel"</span>);
                 
                System.out.println(id+<span class="hljs-string">"\t"</span>+name+<span class="hljs-string">"\t"</span>+salary+<span class="hljs-string">"\t"</span>+email+<span class="hljs-string">"\t"</span>+tel+<span class="hljs-string">"\t"</span>);
            }
        }
        <span class="hljs-keyword">catch</span>(SQLException e)
        {
            System.err.println(<span class="hljs-string">"오라클 쿼리문 조회 실패"</span>);
        }
    }
 
</code>

'jsp,spring,php' 카테고리의 다른 글

jdbc)테이블 내용 수정  (0) 2020.04.17
jdbc)테이블 내용 삽입 예제  (0) 2020.04.17
jsp)로그인 서비스 구현 - 2  (0) 2020.01.03
jsp)로그인 서비스 구현 - 1  (0) 2020.01.03
(php,mysql)서울시 도서관 검색  (0) 2019.02.11

jsp)로그인 서비스 구현 - 2

jsp,spring,php
posted by 코딩 공부중 2020. 1. 3. 17:19

servlet 구성

1.로그인 처리

?
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
<code class="hljs"><span class="hljs-meta">@WebServlet</span>(<span class="hljs-string">"/login"</span>)
public <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">LoginServlet</span> <span class="hljs-keyword">extends</span> <span class="hljs-title">HttpServlet</span> </span>{
    <span class="hljs-keyword">private</span> static <span class="hljs-keyword">final</span> long serialVersionUID = <span class="hljs-number">1</span>L;
        
   
    <span class="hljs-keyword">protected</span> void doPost(<span class="hljs-type">HttpServletRequest</span> request, <span class="hljs-type">HttpServletResponse</span> response) <span class="hljs-keyword">throws</span> <span class="hljs-type">ServletException</span>, <span class="hljs-type">IOException</span> {
        <span class="hljs-type">String</span> id = request.getParameter(<span class="hljs-string">"id"</span>);
        <span class="hljs-type">String</span> password = request.getParameter(<span class="hljs-string">"password"</span>);
         
        <span class="hljs-type">MemberService</span> service = <span class="hljs-keyword">new</span> <span class="hljs-type">MemberService</span>();
        boolean loginFlag = service.login(id, password);
        <span class="hljs-type">HttpSession</span> session = request.getSession();
        <span class="hljs-keyword">if</span>(loginFlag) {
            <span class="hljs-comment">//아이디 패스워드가 동일한 경우</span>
            <span class="hljs-comment">//상태정보유지</span>
            <span class="hljs-comment">//쿠키를 이용한 상태정보 유지</span>
<span class="hljs-comment">//           Cookie cookie = new Cookie("login", id);</span>
<span class="hljs-comment">//           cookie.setPath("/");</span>
<span class="hljs-comment">//           cookie.setMaxAge(-1);</span>
<span class="hljs-comment">//           </span>
<span class="hljs-comment">//           </span>
<span class="hljs-comment">//           response.addCookie(cookie);</span>
             
            <span class="hljs-comment">//세션을 이용한 상태정보 유지</span>
             
            session.setAttribute(<span class="hljs-string">"login"</span>, id);
             
             
            <span class="hljs-comment">//로그인이 성공했을때 가야할 페이지로 리다이렉트</span>
            response.sendRedirect(<span class="hljs-string">"guestbookListForm.jsp"</span>);
             
             
        }<span class="hljs-keyword">else</span> {
            <span class="hljs-comment">//입력한 아이디 패스워드가 다를 경우</span>
            session.setAttribute(<span class="hljs-string">"msg"</span>,<span class="hljs-string">"login정보가 다릅니다. 다시 로그인하세요"</span>);
            response.sendRedirect(<span class="hljs-string">"loginForm.jsp"</span>);
        }
    }
 
}
</code>

2.회원삭제 처리

 

3.회원리스트 처리

 

4.회원가입

 

5.

'jsp,spring,php' 카테고리의 다른 글

jdbc)테이블 내용 삽입 예제  (0) 2020.04.17
jdbc)테이블 조회 예제  (0) 2020.04.17
jsp)로그인 서비스 구현 - 1  (0) 2020.01.03
(php,mysql)서울시 도서관 검색  (0) 2019.02.11
php 가위바위보 사이트  (0) 2019.01.25

jsp)로그인 서비스 구현 - 1

jsp,spring,php
posted by 코딩 공부중 2020. 1. 3. 17:13

1.회원 관리 서비스

?
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
<code class="hljs"><span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">MemberService</span> </span>{
    MemberDAO dao = <span class="hljs-keyword">new</span> MemberDAO();
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">boolean</span> <span class="hljs-title">login</span><span class="hljs-params">(String id, String password)</span> </span>{
        <span class="hljs-keyword">boolean</span> loginFlag = <span class="hljs-keyword">false</span>;
        Member member = dao.getMember(id);
        <span class="hljs-keyword">if</span>(member != <span class="hljs-keyword">null</span> && member.getPassword().equals(password)) {
            loginFlag = <span class="hljs-keyword">true</span>;
        }
        <span class="hljs-keyword">return</span> loginFlag;
    }
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">boolean</span> <span class="hljs-title">memberJoin</span><span class="hljs-params">(Member member)</span> </span>{
        <span class="hljs-comment">//Member 데이터를 얻어와서 회원가입 하기 위해</span>
        <span class="hljs-comment">//필요한 로직이 존재한다면 여기에서 수행함</span>
         
        <span class="hljs-comment">//DB에 저장할 정보가 있다면 저장</span>
        <span class="hljs-keyword">boolean</span> resultFlag = dao.addMember(member);
        <span class="hljs-keyword">return</span> resultFlag;
    }
     
    <span class="hljs-function"><span class="hljs-keyword">public</span> List <span class="hljs-title">getMemberList</span><span class="hljs-params">()</span> </span>{
        <span class="hljs-keyword">return</span> dao.getMemberList();
    }
 
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">deleteMember</span><span class="hljs-params">(String id)</span> </span>{
        <span class="hljs-comment">//삭제하기 전에 수행할 로직이 있다면 여기서 수행</span>
        dao.deleteMember(id);
    }
     
    <span class="hljs-function"><span class="hljs-keyword">public</span> Member <span class="hljs-title">getMember</span><span class="hljs-params">(String id)</span> </span>{
        <span class="hljs-keyword">return</span> dao.getMember(id);
    }
     
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">updateMember</span><span class="hljs-params">(Member member)</span> </span>{
        dao.updateMember(member);
    }
}
</code>

2. 회원관리 db 쿼리문

?
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
<code class="hljs"><span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">MemberDAO</span> </span>{
 
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">boolean</span> <span class="hljs-title">addMember</span><span class="hljs-params">(Member member)</span> </span>{
        <span class="hljs-keyword">boolean</span> resultFlag = <span class="hljs-keyword">false</span>;
        Connection conn = <span class="hljs-keyword">null</span>;
        PreparedStatement ps = <span class="hljs-keyword">null</span>;
         
        <span class="hljs-keyword">try</span> {
            conn = DButil.getConnection();
            String sql = <span class="hljs-string">"insert into member(id,name,password,email) values(?,?,?,?)"</span>;
            ps = conn.prepareStatement(sql);
            ps.setString(<span class="hljs-number">1</span>, member.getId());
            ps.setString(<span class="hljs-number">2</span>, member.getName());
            ps.setString(<span class="hljs-number">3</span>, member.getPassword());
            ps.setString(<span class="hljs-number">4</span>, member.getEmail());
             
            <span class="hljs-keyword">int</span> resultCount = ps.executeUpdate();
         
            <span class="hljs-keyword">if</span>(resultCount == <span class="hljs-number">1</span>)
                resultFlag = <span class="hljs-keyword">true</span>;
             
        }<span class="hljs-keyword">catch</span> (Exception e) {
            e.printStackTrace();
             
        }<span class="hljs-keyword">finally</span> {
 
            DButil.close(conn, ps);
        }
        <span class="hljs-keyword">return</span> resultFlag;
    }
 
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">updateMember</span><span class="hljs-params">(Member member)</span> </span>{
        <span class="hljs-keyword">int</span> resultCount = <span class="hljs-number">0</span>;
         
        Connection conn = <span class="hljs-keyword">null</span>;
        PreparedStatement ps = <span class="hljs-keyword">null</span>;
         
        <span class="hljs-keyword">try</span> {
            conn = DButil.getConnection();
            String sql = <span class="hljs-string">"update member set name=?, password=?, email=? where id=? "</span>;
            ps = conn.prepareStatement(sql);
 
            ps.setString(<span class="hljs-number">1</span>, member.getName());
            ps.setString(<span class="hljs-number">2</span>, member.getPassword());
            ps.setString(<span class="hljs-number">3</span>, member.getEmail());
            ps.setString(<span class="hljs-number">4</span>, member.getId());
             
            resultCount = ps.executeUpdate();
             
        }<span class="hljs-keyword">catch</span> (Exception e) {
            e.printStackTrace();
             
        }<span class="hljs-keyword">finally</span> {
 
            DButil.close(conn, ps);
        }
         
        <span class="hljs-keyword">return</span> resultCount;
    }
     
    <span class="hljs-comment">//����</span>
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">deleteMember</span><span class="hljs-params">(String id)</span> </span>{
        <span class="hljs-keyword">int</span> resultCount=<span class="hljs-number">0</span>;
         
        Connection conn = <span class="hljs-keyword">null</span>;
        PreparedStatement ps = <span class="hljs-keyword">null</span>;
         
        <span class="hljs-keyword">try</span> {
            conn = DButil.getConnection();
            String sql = <span class="hljs-string">"delete from member where id = ?"</span>;
            ps = conn.prepareStatement(sql);
            ps.setString(<span class="hljs-number">1</span>,id);
             
            resultCount = ps.executeUpdate();
             
        }<span class="hljs-keyword">catch</span> (Exception e) {
            e.printStackTrace();
             
        }<span class="hljs-keyword">finally</span> {
             
            DButil.close(conn, ps);
        }
        <span class="hljs-keyword">return</span> resultCount;
    }
 
    <span class="hljs-function"><span class="hljs-keyword">public</span> Member <span class="hljs-title">getMember</span><span class="hljs-params">(String id)</span> </span>{
        Member member = <span class="hljs-keyword">null</span>;
        Connection conn = <span class="hljs-keyword">null</span>;
        PreparedStatement ps = <span class="hljs-keyword">null</span>;
        ResultSet rs = <span class="hljs-keyword">null</span>;
        <span class="hljs-keyword">try</span> {
            conn = DButil.getConnection();
            String sql = <span class="hljs-string">"select id,name,password,email from member where id =? "</span>;
            ps = conn.prepareStatement(sql);
            ps.setString(<span class="hljs-number">1</span>, id);
            rs = ps.executeQuery();
             
            <span class="hljs-keyword">if</span>(rs.next()) {
                member = <span class="hljs-keyword">new</span> Member();
                member.setId(rs.getString(<span class="hljs-number">1</span>));
                member.setName(rs.getString(<span class="hljs-number">2</span>));
                member.setPassword(rs.getString(<span class="hljs-number">3</span>));
                member.setEmail(rs.getString(<span class="hljs-number">4</span>));
            }
        }<span class="hljs-keyword">catch</span> (Exception e) {
            e.printStackTrace();
        }<span class="hljs-keyword">finally</span> {
            DButil.close(conn,ps,rs);
        }
         
        <span class="hljs-keyword">return</span> member;
         
    }
 
    <span class="hljs-function"><span class="hljs-keyword">public</span> List <span class="hljs-title">getMemberList</span><span class="hljs-params">()</span> </span>{
        List memberList = <span class="hljs-keyword">new</span> ArrayList<>();
         
        Connection conn = <span class="hljs-keyword">null</span>;
        PreparedStatement ps = <span class="hljs-keyword">null</span>;
        ResultSet rs = <span class="hljs-keyword">null</span>;
        <span class="hljs-keyword">try</span> {
            conn = DButil.getConnection();
            String sql = <span class="hljs-string">"select id,name,password,email from member"</span>;
            ps = conn.prepareStatement(sql);
 
            rs = ps.executeQuery();
             
            <span class="hljs-keyword">while</span>(rs.next()) {
                Member member = <span class="hljs-keyword">new</span> Member();
                member.setId(rs.getString(<span class="hljs-number">1</span>));
                member.setName(rs.getString(<span class="hljs-number">2</span>));
                member.setPassword(rs.getString(<span class="hljs-number">3</span>));
                member.setEmail(rs.getString(<span class="hljs-number">4</span>));
                 
                memberList.add(member);
            }
        }<span class="hljs-keyword">catch</span> (Exception e) {
            e.printStackTrace();
        }<span class="hljs-keyword">finally</span> {
            DButil.close(conn,ps,rs);
        }
         
        <span class="hljs-keyword">return</span> memberList;
    }
     
     
}
</code>

3.member 구성

?
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
<code class="hljs"><span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Member</span> </span>{
     
    <span class="hljs-keyword">private</span> String id;
    <span class="hljs-keyword">private</span> String name;
    <span class="hljs-keyword">private</span> String password;
    <span class="hljs-keyword">private</span> String email;
     
    <span class="hljs-function"><span class="hljs-keyword">public</span> String <span class="hljs-title">getId</span><span class="hljs-params">()</span> </span>{
        <span class="hljs-keyword">return</span> id;
    }
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setId</span><span class="hljs-params">(String id)</span> </span>{
        <span class="hljs-keyword">this</span>.id = id;
    }
    <span class="hljs-function"><span class="hljs-keyword">public</span> String <span class="hljs-title">getName</span><span class="hljs-params">()</span> </span>{
        <span class="hljs-keyword">return</span> name;
    }
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setName</span><span class="hljs-params">(String name)</span> </span>{
        <span class="hljs-keyword">this</span>.name = name;
    }
    <span class="hljs-function"><span class="hljs-keyword">public</span> String <span class="hljs-title">getPassword</span><span class="hljs-params">()</span> </span>{
        <span class="hljs-keyword">return</span> password;
    }
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setPassword</span><span class="hljs-params">(String password)</span> </span>{
        <span class="hljs-keyword">this</span>.password = password;
    }
    <span class="hljs-function"><span class="hljs-keyword">public</span> String <span class="hljs-title">getEmail</span><span class="hljs-params">()</span> </span>{
        <span class="hljs-keyword">return</span> email;
    }
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setEmail</span><span class="hljs-params">(String email)</span> </span>{
        <span class="hljs-keyword">this</span>.email = email;
    }
    <span class="hljs-meta">@Override</span>
    <span class="hljs-function"><span class="hljs-keyword">public</span> String <span class="hljs-title">toString</span><span class="hljs-params">()</span> </span>{
        <span class="hljs-keyword">return</span> <span class="hljs-string">"Member [id="</span> + id + <span class="hljs-string">", name="</span> + name + <span class="hljs-string">", password="</span> + password + <span class="hljs-string">", email="</span> + email + <span class="hljs-string">"]"</span>;
    }
 
}
</code>

'jsp,spring,php' 카테고리의 다른 글

jdbc)테이블 내용 삽입 예제  (0) 2020.04.17
jdbc)테이블 조회 예제  (0) 2020.04.17
jsp)로그인 서비스 구현 - 2  (0) 2020.01.03
(php,mysql)서울시 도서관 검색  (0) 2019.02.11
php 가위바위보 사이트  (0) 2019.01.25

(php,mysql)서울시 도서관 검색

jsp,spring,php
posted by 코딩 공부중 2019. 2. 11. 19:49

공공데이터 api와 데이터베이스 그리고 php와 html을 활용해 만든 서울시 도서관검색 웹페이지 입니다.


?
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
<pre>   //html 사용을 위한 코드
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8" %>   //jsp로 글자입력
<%@ page import="java.sql.*" %>   //jsp사용
<%@ page info = "test2.jsp" contentType="text/html; charset=utf-8" %>
<% request.setCharacterEncoding("utf-8");%>
 
<form name="form1" method="post" action="test2.jsp">    //action과 post메소드를 사용하여 체크박스 정보를 넘겨줌
<font size="8">서울시 도서관 검색</font><br>
client(id) :    <input type="text" name="id">       //id입력을 위한 text 타입
검색어  :       <input type="text" name="search">  <input type="submit" value="검색">    //검색어 입력창
<input type="checkbox" name="table" value="name">도서관 명
<input type="checkbox" name="table" value="gu">구
<input type="checkbox" name="table" value="addr">주소
<input type="checkbox" name="table" value="closed">휴관일
<input type="checkbox" name="table" value="phone">전화번호
<input type="checkbox" name="table" value="gpsx">좌표x
<input type="checkbox" name="table" value="gpsy">좌표y    //여기까지 체크박스 입력창
</form>
 
<%
 String URL="jdbc:mysql://localhost/";  //데이터베이스와 연동을 위한 주소를 변수에 저장
 String USER = ""; //계정 id, APMSETUP은 기본적으로 root
 String PASS="";    //마찬가지로 APMSETUP은 기본적으로 apmsetup
 Connection conn=null;      //주소 입력을 위해 null값으로 변수 선언
 Statement stmt = null;
 ResultSet rs = null;
 ResultSet rs2 = null;
 PreparedStatement pstmt=null;
 
 String search = request.getParameter("search");    //위 html에서 받아온 검색어 및 체크박스 정보를 string변수에 저장
 String id = request.getParameter("id");
 String table = request.getParameter("table");
 String sql = "select * from library2";     //select문 사용을 위해 sql변수에 select문 저장
 String sqlid = "insert into ipid(id,search,ip) values(?, ?, ?)";//id와 ip 그리고 검색어 저장을 위한 테이블 내용 추가
 
 if(table != null && search != null){       //체크박스와 검색어가 입력이 되면 if문 실행
   if(table.equals("name")){        //각 if문에서 equals함수를 통해 일치하는지 확인
        sql += " where name like '%" + search + "%'";   //일치하면 sql에 where like 특정 단어 검색 명령어를 입력하고 거기에 html에서 받아온 검색어 변수를 넣어줌
} else if(table.equals("gu")){
    sql += " where gu like '%" + search + "%'";
}else if(table.equals("addr")){
    sql += " where addr like '%" + search + "%'";
}else if(table.equals("closed")){
    sql += " where closed like '%" + search + "%'";
}else if(table.equals("phone")){
    sql += " where phone like '%" + search + "%'";
}else if(table.equals("gpsx")){
    sql += " where gpsx like '%" + search + "%'";
}else if(table.equals("gpsy")){
    sql += " where gpsy like '%" + search + "%'";
 }
}   //if문 종료
 try {          //예외처리 함수를 이용하여 jsp와 mysql을 연동
  Class.forName("com.mysql.jdbc.Driver");             // mysql을 사용(JDBC)
  conn = DriverManager.getConnection(URL, USER, PASS); // 아이디, 비밀번호, 주소를 넣고 >연결
  stmt = conn.createStatement();
  rs = stmt.executeQuery(sql);// if문을 통해 검색한 내용이 저장된 sql은 rs변수에 저장
 
   while(rs.next()) {   //while 루프를 통해 검색이 완료된 테이블 내용을 모두 출력함
    
        out.print(rs.getString("name")+"\t");//gesString을 이용하여 검색한 내용 출력
        out.print(rs.getString("gu")+"\t");
        out.print(rs.getString("addr")+"\t");
        out.print(rs.getString("closed")+"\t");
        out.print(rs.getString("phone")+"\t");
        out.print(rs.getString("gpsx")+"\t");
        out.print(rs.getString("gpsy")+"\t");
 
     out.print("\n");
}
 
   pstmt = conn.prepareStatement(sqlid);        // prepareStatement  sql
   pstmt.setString(1,id);           //앞서 만들어 놓은 ipid테이블에 id와 검색어를 저장시킴
   pstmt.setString(2,search);
   pstmt.setString(3,request.getRemoteAddr());  //getRemoteAddr는 사용자의 ip를 추출하는 명령어
   pstmt.executeUpdate();
 
 
 }catch(SQLException e) {   //try문 종료
  e.getMessage();
 }
 finally {
  if(rs != null)try{rs.close();}catch(SQLException ex){}
  if(stmt != null)try{stmt.close();}catch(SQLException ex){}
  if(conn != null)try{conn.close();}catch(SQLException ex){}
 }
 
%></pre><br>

실행화면

처음 접속한 화면입니다. 검색어 입력창과 체크박스 란이 있고

그 밑에 현재 들어가져 있는 데이터들이 보이게끔 했습니다.

검색어 입력창입니다. 검색하고자 하는 주제를 체크박스에 체크하고 id와 검색어를 입력한 뒤

검색 버튼을 누릅니다.


검색버튼을 누르면 밑에 검색한 내용이 나오게 됩니다.

저는 LH를 검색해서 도서관명에 LH가 들어있는 모든 도서관 정보를 확인할 수 있습니다.


mysql을 통해 방금 검색한 id와 검색어 그리고 ip주소가 입력된 것을 확인할 수 있습니다.


사용한 url : http://data.seoul.go.kr/dataList/datasetView.do?infId=OA-2240&srvType=S&serviceKind=1&currentPageNo=1

출처 - 서울열린데이터광장

'jsp,spring,php' 카테고리의 다른 글

jdbc)테이블 내용 삽입 예제  (0) 2020.04.17
jdbc)테이블 조회 예제  (0) 2020.04.17
jsp)로그인 서비스 구현 - 2  (0) 2020.01.03
jsp)로그인 서비스 구현 - 1  (0) 2020.01.03
php 가위바위보 사이트  (0) 2019.01.25

php 가위바위보 사이트

jsp,spring,php
posted by 코딩 공부중 2019. 1. 25. 21:28
?
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
<div>
<center>
<img src="https://steemitimages.com/DQmYzZ3WgAA4ScWEFfrrM1K94PE3W8s5rgAS1FYq3fDMo45/image.png" width="400," height="300/">
<font color="blue" font="" size="10">
<h3>가위,바위,보 게임</h3>
</font>
<font color="green" font="" size="8">
  <form action="assg4.php" method="GET">
    입력:<input type="text" name="var_gababo"><br>
    학번:<input type="text" name="var_number"><br>  
    이름:<input type="text" name="var_name"><br>  
 <input type="submit">
  </form>
</font>
</center></div>
<font size="10" font="" color="#47C83E">
<center>
 
<!--?php
if($_GET != null){
$c=file("./kawi.dat");
$ga = explode(" ", $c[0]);
$win = $ga[1];
$lose = $ga[2];
$draw = $ga[3];
 
 echo"<br-->학번:". $_GET['var_number']. " 이름:". $_GET['var_name']."<br>";
 
 echo $_GET['var_name']."씨는 ".$_GET['var_gababo']."를 냈습니다.<br>";
 
 if($_GET['var_gababo'] == 가위 || $_GET['var_gababo'] == 바위 || $_GET['var_gababo'] == 보)
{ 
 
 $input = $_GET['var_gababo'];
 
 $gababo = array("가위" => 0, "바위" => 1, "보" => 2);
 
 $su=rand(0,2);
 
 
 
 if ($su == 0)
    $choice = "가위";
 
 if ($su == 1)
    $choice = "바위";
 
 if ($su == 2)
    $choice = "보";
 
echo "컴퓨터는 ".$choice."를 냈습니다.<br>";
 
if($su==0 && $gababo[$input] == 2)
 {
    echo"컴퓨터가 이겼습니다.<br>";
    ++$lose;
 }
 
else if($su==2 && $gababo[$input] == 0)
 {
 
    echo"당신이 이겼습니다.<br>";
 
    $win = ($c[0] + 1);
 
    ++$win;
 
 }
 
else if($su < $gababo[$input])
{
 
    echo"당신이 이겼습니다.<br>";
 
    $win = ($c[0] + 1);
 
    ++$win;
 
 
 
}
 
else if($gababo[$input] < $su)
{
    echo"컴퓨터가 이겼습니다.<br>";
 
    ++$lose;
}
 
else if($gababo[$input] == $su)
{
    echo"무승부 입니다<br>";
 
    $draw += 1;
}
 
//$num=($c[0] + 1);
 
}
 
else
    echo"잘못된 입력입니다<br>";
 
 
 
$hap = $win + $lose + $draw;
echo "총 ".$hap."번 시행중".$win."번 이기고,".$lose."번 졌으며 ".$draw."번 비겼습니다.<br>";
 
$stat = "$hap $win $lose $draw";
exec("echo \"$stat\" > ./kawi.dat");
}
?>
</center>
 
</font>


'jsp,spring,php' 카테고리의 다른 글

jdbc)테이블 내용 삽입 예제  (0) 2020.04.17
jdbc)테이블 조회 예제  (0) 2020.04.17
jsp)로그인 서비스 구현 - 2  (0) 2020.01.03
jsp)로그인 서비스 구현 - 1  (0) 2020.01.03
(php,mysql)서울시 도서관 검색  (0) 2019.02.11

티스토리툴바