2008. 7. 26. 16:10
JDBC PreparedStatement를 이용한 데이터 조회 예제
2008. 7. 26. 16:10 in 3. Implementation/DATABASE
1. DataInsert_Prepared.java
01 import java.sql.*;
02
03 public class DataInsert_Prepared{
04 Connection con = null;
05 PreparedStatement ptmt = null;
06
07 public static void main (String args []) {
08 DataInsert_Prepared di = new DataInsert_Prepared();
09 try{
10 di.connect();
11 di.insert();
12 di.close();
13 }catch(SQLException e){
14 e.printStackTrace();
15 }finally{
16 try{
17 if(di.ptmt != null) di.ptmt.close();
18 if(di.con != null) di.con.close();
19 }catch(SQLException e){}
20 }
21 }
22
23 public void connect()throws SQLException {
24 try{
25 Class.forName("oracle.jdbc.driver.OracleDriver");
26 con = DriverManager.getConnection
27 ("jdbc:oracle:thin:@127.0.0.1:1521:ora9",
28 "jdbc00","jdbc00");
29 System.out.println("데이터베이스 연결되었습니다.");
30 }catch(ClassNotFoundException e){
31 System.out.println("JDBC Driver load fail!!!");
32 }
33 }
34
35 public void close()throws SQLException {
36 con.close();
37 System.out.println("데이터베이스 연결해제되었습니다.");
38 }
39
40 public void insert()throws SQLException {
41 String query = "insert into emp values(?,?,?,?,?)";
42 ptmt = con.prepareStatement (query);
43
44 int id = 8;
45 String name = "박삼성";
46 double bonus = 40000.00;
47 Date in_date = new Date(System.currentTimeMillis());
48 String dept = "인사부";
49
50 ptmt.setInt(1,id);
51 ptmt.setString(2,name);
52 ptmt.setDouble(3,bonus);
53 ptmt.setDate(4,in_date);
54 ptmt.setString(5,dept);
55 int rowcount = ptmt.executeUpdate();
56
57 id++;
58 name = "이자바";
59 dept = "총무부";
60
61 ptmt.setInt(1,id);
62 ptmt.setString(2,name);
63 ptmt.setDouble(3,bonus);
64 ptmt.setDate(4,in_date);
65 ptmt.setString(5,dept);
66 rowcount += ptmt.executeUpdate();
67
68 System.out.println
69 ("emp 테이블에" + rowcount +
70 " 개의 데이터가 삽입되었습니다.");
71 ptmt.close();
72 }
73 }
02
03 public class DataInsert_Prepared{
04 Connection con = null;
05 PreparedStatement ptmt = null;
06
07 public static void main (String args []) {
08 DataInsert_Prepared di = new DataInsert_Prepared();
09 try{
10 di.connect();
11 di.insert();
12 di.close();
13 }catch(SQLException e){
14 e.printStackTrace();
15 }finally{
16 try{
17 if(di.ptmt != null) di.ptmt.close();
18 if(di.con != null) di.con.close();
19 }catch(SQLException e){}
20 }
21 }
22
23 public void connect()throws SQLException {
24 try{
25 Class.forName("oracle.jdbc.driver.OracleDriver");
26 con = DriverManager.getConnection
27 ("jdbc:oracle:thin:@127.0.0.1:1521:ora9",
28 "jdbc00","jdbc00");
29 System.out.println("데이터베이스 연결되었습니다.");
30 }catch(ClassNotFoundException e){
31 System.out.println("JDBC Driver load fail!!!");
32 }
33 }
34
35 public void close()throws SQLException {
36 con.close();
37 System.out.println("데이터베이스 연결해제되었습니다.");
38 }
39
40 public void insert()throws SQLException {
41 String query = "insert into emp values(?,?,?,?,?)";
42 ptmt = con.prepareStatement (query);
43
44 int id = 8;
45 String name = "박삼성";
46 double bonus = 40000.00;
47 Date in_date = new Date(System.currentTimeMillis());
48 String dept = "인사부";
49
50 ptmt.setInt(1,id);
51 ptmt.setString(2,name);
52 ptmt.setDouble(3,bonus);
53 ptmt.setDate(4,in_date);
54 ptmt.setString(5,dept);
55 int rowcount = ptmt.executeUpdate();
56
57 id++;
58 name = "이자바";
59 dept = "총무부";
60
61 ptmt.setInt(1,id);
62 ptmt.setString(2,name);
63 ptmt.setDouble(3,bonus);
64 ptmt.setDate(4,in_date);
65 ptmt.setString(5,dept);
66 rowcount += ptmt.executeUpdate();
67
68 System.out.println
69 ("emp 테이블에" + rowcount +
70 " 개의 데이터가 삽입되었습니다.");
71 ptmt.close();
72 }
73 }
2. DataSelect_Prepared.java
01 import java.sql.*;
02
03 public class DataSelect_Prepared{
04 Connection con = null;
05 PreparedStatement ptmt = null;
06
07 public static void main (String args []) {
08 DataSelect_Prepared dsp = new DataSelect_Prepared();
09 if(args.length < 2){
10 System.out.println
11 ("emp 테이블에서 조회할 id 값을 입력하세요.\n"
12 + "5번에서 8번까지의 id를 가진 데이터를 조회하려면\n"
13 + "java DataSelect_Prepared 5 8 을 입력하세요.");
14 return;
15 }
16 int start = Integer.parseInt(args[0]);
17 int end = Integer.parseInt(args[1]);
18
19 try{
20 dsp.connect();
21 dsp.select(start, end);
22 dsp.close();
23 }catch(SQLException e){
24 e.printStackTrace();
25 }finally{
26 try{
27 if(dsp.ptmt != null) dsp.ptmt.close();
28 if(dsp.con != null) dsp.con.close();
29 }catch(SQLException e){}
30 }
31 }
32
33 public void connect() throws SQLException {
34 try{
35 Class.forName("oracle.jdbc.driver.OracleDriver");
36 con = DriverManager.getConnection
37 ("jdbc:oracle:thin:@127.0.0.1:1521:ora9",
38 "jdbc00","jdbc00");
39 System.out.println("데이터베이스 연결되었습니다.");
40 }catch(ClassNotFoundException e){
41 System.out.println("JDBC Driver load fail!!!");
42 }
43 }
44
45 public void close() throws SQLException {
46 con.close();
47 System.out.println("\n데이터베이스 연결해제되었습니다.");
48 }
49
50 public void select(int start, int end) throws SQLException {
51 String query =
52 "select * from emp where id >= ? and id <= ?";
53 ptmt = con.prepareStatement (query);
54
55 ptmt.setInt(1, start);
56 ptmt.setInt(2, end);
57
58 ResultSet rs = ptmt.executeQuery();
59 System.out.println("조회 결과는 다음과 같습니다.\n");
60 while(rs.next()){
61 int id = rs.getInt("id");
62 String result = "ID : " + id + "\t";
63 String name = "이름 : " + rs.getString(2) + "\t";
64 result += name;
65 double bonus = rs.getDouble(3);
66 result += "BONUS : " + bonus + "\t";
67 Date inDate = rs.getDate(4);
68 result += "입사일 : " + inDate + "\t";
69 String dept = "부서 : " + rs.getString(5) + "\t";
70 result += dept;
71 System.out.println(result);
72 }
73 ptmt.close();
74 }
75 }
02
03 public class DataSelect_Prepared{
04 Connection con = null;
05 PreparedStatement ptmt = null;
06
07 public static void main (String args []) {
08 DataSelect_Prepared dsp = new DataSelect_Prepared();
09 if(args.length < 2){
10 System.out.println
11 ("emp 테이블에서 조회할 id 값을 입력하세요.\n"
12 + "5번에서 8번까지의 id를 가진 데이터를 조회하려면\n"
13 + "java DataSelect_Prepared 5 8 을 입력하세요.");
14 return;
15 }
16 int start = Integer.parseInt(args[0]);
17 int end = Integer.parseInt(args[1]);
18
19 try{
20 dsp.connect();
21 dsp.select(start, end);
22 dsp.close();
23 }catch(SQLException e){
24 e.printStackTrace();
25 }finally{
26 try{
27 if(dsp.ptmt != null) dsp.ptmt.close();
28 if(dsp.con != null) dsp.con.close();
29 }catch(SQLException e){}
30 }
31 }
32
33 public void connect() throws SQLException {
34 try{
35 Class.forName("oracle.jdbc.driver.OracleDriver");
36 con = DriverManager.getConnection
37 ("jdbc:oracle:thin:@127.0.0.1:1521:ora9",
38 "jdbc00","jdbc00");
39 System.out.println("데이터베이스 연결되었습니다.");
40 }catch(ClassNotFoundException e){
41 System.out.println("JDBC Driver load fail!!!");
42 }
43 }
44
45 public void close() throws SQLException {
46 con.close();
47 System.out.println("\n데이터베이스 연결해제되었습니다.");
48 }
49
50 public void select(int start, int end) throws SQLException {
51 String query =
52 "select * from emp where id >= ? and id <= ?";
53 ptmt = con.prepareStatement (query);
54
55 ptmt.setInt(1, start);
56 ptmt.setInt(2, end);
57
58 ResultSet rs = ptmt.executeQuery();
59 System.out.println("조회 결과는 다음과 같습니다.\n");
60 while(rs.next()){
61 int id = rs.getInt("id");
62 String result = "ID : " + id + "\t";
63 String name = "이름 : " + rs.getString(2) + "\t";
64 result += name;
65 double bonus = rs.getDouble(3);
66 result += "BONUS : " + bonus + "\t";
67 Date inDate = rs.getDate(4);
68 result += "입사일 : " + inDate + "\t";
69 String dept = "부서 : " + rs.getString(5) + "\t";
70 result += dept;
71 System.out.println(result);
72 }
73 ptmt.close();
74 }
75 }
3. ImgTableCreate.java
01 import java.sql.*;
02
03 public class ImgTableCreate{
04 Connection con = null;
05 Statement stmt = null;
06
07 public static void main (String args []) {
08 ImgTableCreate tc = new ImgTableCreate();
09 tc.connect();
10 tc.createTable();
11 tc.close();
12 }
13
14 public void connect(){
15 try{
16 Class.forName("oracle.jdbc.driver.OracleDriver");
17 con = DriverManager.getConnection
18 ("jdbc:oracle:thin:@127.0.0.1:1521:ora9",
19 "jdbc00","jdbc00");
20 System.out.println("데이터베이스 연결되었습니다.");
21 }catch(ClassNotFoundException e){
22 System.out.println("JDBC Driver load fail!!!");
23 }catch(SQLException e){
24 System.out.println("Connection fail!!!");
25 e.printStackTrace();
26 }
27 }
28
29 public void close(){
30 try{
31 con.close();
32 System.out.println
33 ("데이터베이스 연결 해제되었습니다.");
34 }catch(SQLException e){
35 System.out.println("Connection fail!!!");
36 e.printStackTrace();
37 }finally{
38 try{
39 if(con != null) con.close();
40 }catch(SQLException e){}
41 }
42 }
43
44 public void createTable(){
45 try{
46 stmt = con.createStatement ();
47 String query = "CREATE TABLE img ";
48 query += "(name VARCHAR2(30) , ";
49 query += " contents RAW(2000))" ;
50 int rowcount = stmt.executeUpdate(query);
51 System.out.println
52 ("img 테이블 생성이 완료되었습니다.");
53 stmt.close();
54 }catch(SQLException e){
55 e.printStackTrace();
56 }finally {
57 try{
58 if(stmt != null) stmt.close();
59 }catch(SQLException e){}
60 }
61 }
62 }
02
03 public class ImgTableCreate{
04 Connection con = null;
05 Statement stmt = null;
06
07 public static void main (String args []) {
08 ImgTableCreate tc = new ImgTableCreate();
09 tc.connect();
10 tc.createTable();
11 tc.close();
12 }
13
14 public void connect(){
15 try{
16 Class.forName("oracle.jdbc.driver.OracleDriver");
17 con = DriverManager.getConnection
18 ("jdbc:oracle:thin:@127.0.0.1:1521:ora9",
19 "jdbc00","jdbc00");
20 System.out.println("데이터베이스 연결되었습니다.");
21 }catch(ClassNotFoundException e){
22 System.out.println("JDBC Driver load fail!!!");
23 }catch(SQLException e){
24 System.out.println("Connection fail!!!");
25 e.printStackTrace();
26 }
27 }
28
29 public void close(){
30 try{
31 con.close();
32 System.out.println
33 ("데이터베이스 연결 해제되었습니다.");
34 }catch(SQLException e){
35 System.out.println("Connection fail!!!");
36 e.printStackTrace();
37 }finally{
38 try{
39 if(con != null) con.close();
40 }catch(SQLException e){}
41 }
42 }
43
44 public void createTable(){
45 try{
46 stmt = con.createStatement ();
47 String query = "CREATE TABLE img ";
48 query += "(name VARCHAR2(30) , ";
49 query += " contents RAW(2000))" ;
50 int rowcount = stmt.executeUpdate(query);
51 System.out.println
52 ("img 테이블 생성이 완료되었습니다.");
53 stmt.close();
54 }catch(SQLException e){
55 e.printStackTrace();
56 }finally {
57 try{
58 if(stmt != null) stmt.close();
59 }catch(SQLException e){}
60 }
61 }
62 }
4. ImgTableInsert.java
01 import java.sql.*;
02 import java.io.*;
03
04 public class ImgTableInsert {
05 public static void main (String args []) {
06 Connection conn=null;
07 PreparedStatement pstmt=null;
08 try{
09 Class.forName ("oracle.jdbc.driver.OracleDriver");
10 }catch(ClassNotFoundException e){}
11
12 try{
13 conn = DriverManager.getConnection
14 ("jdbc:oracle:thin:@127.0.0.1:1521:ora9",
15 "jdbc00","jdbc00");
16 pstmt = conn.prepareStatement
17 ("insert into img values (?,?)");
18 File file = new File("anyboy.gif");
19 int size = (int) file.length();
20 FileInputStream fin = new FileInputStream(file);
21 byte [] contents = new byte[size];
22 int total;
23 while((total = fin.read(contents)) != -1){
24 }
25 pstmt.setString(1,file.getName());
26 pstmt.setBytes(2,contents);
27 int row = pstmt.executeUpdate();
28 System.out.println
29 (row + " 개의 데이터가 삽입되었습니다");
30 }catch(Exception e){
31 e.printStackTrace();
32 }finally{
33 if(pstmt != null) pstmt.close();
34 if(conn != null) conn.close();
35 }
36 }
37 }
02 import java.io.*;
03
04 public class ImgTableInsert {
05 public static void main (String args []) {
06 Connection conn=null;
07 PreparedStatement pstmt=null;
08 try{
09 Class.forName ("oracle.jdbc.driver.OracleDriver");
10 }catch(ClassNotFoundException e){}
11
12 try{
13 conn = DriverManager.getConnection
14 ("jdbc:oracle:thin:@127.0.0.1:1521:ora9",
15 "jdbc00","jdbc00");
16 pstmt = conn.prepareStatement
17 ("insert into img values (?,?)");
18 File file = new File("anyboy.gif");
19 int size = (int) file.length();
20 FileInputStream fin = new FileInputStream(file);
21 byte [] contents = new byte[size];
22 int total;
23 while((total = fin.read(contents)) != -1){
24 }
25 pstmt.setString(1,file.getName());
26 pstmt.setBytes(2,contents);
27 int row = pstmt.executeUpdate();
28 System.out.println
29 (row + " 개의 데이터가 삽입되었습니다");
30 }catch(Exception e){
31 e.printStackTrace();
32 }finally{
33 if(pstmt != null) pstmt.close();
34 if(conn != null) conn.close();
35 }
36 }
37 }
5. ImgTableSelect.java
01 import java.sql.*;
02 import java.io.*;
03
04 public class ImgTableSelect {
05 public static void main (String args []) {
06 Connection conn = null;
07 PreparedStatement pstmt = null;
08 FileOutputStream fout = null;
09 byte [] db = null;
10
11
12 try{
13 Class.forName ("oracle.jdbc.driver.OracleDriver");
14 }catch(ClassNotFoundException e){}
15
16 try{
17 conn = DriverManager.getConnection
18 ("jdbc:oracle:thin:@127.0.0.1:1521:ora9",
19 "jdbc00","jdbc00");
20 pstmt = conn.prepareStatement
21 ("select contents from img where name = ?");
22 pstmt.setString(1,"anyboy.gif");
23
24 int leng = (int)(new File("anyboy.gif").length());
25 db = new byte[leng];
26
27 ResultSet rs = pstmt.executeQuery();
28 while(rs.next()){
29 db = rs.getBytes(1);
30 }
31
32 fout = new FileOutputStream("anyboy.gif.db");
33 fout.write(db);
34 System.out.println
35 ("anyboy.gif.db 라는 파일에 저장했습니다\n");
36 }catch(Exception e){
37 e.printStackTrace();
38 }finally{
39 if(pstmt != null) pstmt.close();
40 if(conn != null) conn.close();
41 }
42 }
43 }
02 import java.io.*;
03
04 public class ImgTableSelect {
05 public static void main (String args []) {
06 Connection conn = null;
07 PreparedStatement pstmt = null;
08 FileOutputStream fout = null;
09 byte [] db = null;
10
11
12 try{
13 Class.forName ("oracle.jdbc.driver.OracleDriver");
14 }catch(ClassNotFoundException e){}
15
16 try{
17 conn = DriverManager.getConnection
18 ("jdbc:oracle:thin:@127.0.0.1:1521:ora9",
19 "jdbc00","jdbc00");
20 pstmt = conn.prepareStatement
21 ("select contents from img where name = ?");
22 pstmt.setString(1,"anyboy.gif");
23
24 int leng = (int)(new File("anyboy.gif").length());
25 db = new byte[leng];
26
27 ResultSet rs = pstmt.executeQuery();
28 while(rs.next()){
29 db = rs.getBytes(1);
30 }
31
32 fout = new FileOutputStream("anyboy.gif.db");
33 fout.write(db);
34 System.out.println
35 ("anyboy.gif.db 라는 파일에 저장했습니다\n");
36 }catch(Exception e){
37 e.printStackTrace();
38 }finally{
39 if(pstmt != null) pstmt.close();
40 if(conn != null) conn.close();
41 }
42 }
43 }
Rss Feed