SERVER에 CONTEXT.XML
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <?xml version="1.0" encoding="UTF-8"?> <Context> --> <WatchedResource>WEB-INF/web.xml</WatchedResource> <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource> <!-- dbcp 설정 (dataBase connection pool) --> <Resource name="myDB" auth="Container" driverClassName="com.mysql.jdbc.Driver" maxTotal="50" maxIdle="50" maxWaitMillis="-1" url="jdbc:mysql://localhost:3306/java" username="java" password="java1234" type="javax.sql.DataSource" /> </Context> | cs |
BOOK DAO
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 | package book; // jdbc import import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; public class BookDAO { // db접속 public Connection dbConn() { DataSource ds=null; Connection conn=null; try { //context.xml을 분석하는 객체 Context ctx=new InitialContext(); //context.xml의 resource 태그 검색 ds =(DataSource)ctx.lookup("java:comp/env/myDB"); conn=ds.getConnection(); //커넥션을 할당받음 } catch (Exception e) { e.printStackTrace(); } return conn; } // db접속 /*public Connection dbConn() { Connection conn = null; // db접속 객체 try { // mysql jdbc driver 로딩 Class.forName("com.mysql.jdbc.Driver"); // db연결 문자열 but 이방법은 보안에 취약하다. .. String url = "jdbc:mysql://localhost:3306/java"; String id = "java"; // mysql 접속아이디 String pwd = "java1234"; // mysql 접속 비번 // db 접속 conn = DriverManager.getConnection(url, id, pwd); System.out.println("db접속 성공"); } catch (Exception e) { // db관련작업은 반드시 익셉션 처리 System.out.println("db접속 실패"); e.printStackTrace(); } return conn; }*/ // 북리스트 public ArrayList<bookDTO> bookList() { ArrayList<bookDTO> list = new ArrayList<bookDTO>(); Connection conn = null; // DB접속 객체 PreparedStatement pstmt = null; // SQL실행객체 ResultSet rs = null; // 결과셋 처리 객체 try { conn = dbConn(); // db연결 키 String sql = "select * from book_table"; pstmt = conn.prepareStatement(sql); // sql을 실행시키는 객체 만들어짐 rs = pstmt.executeQuery(); // 실행 후 결과 값이 rs에 넘어옴 while (rs.next()) { // 결과셋.next(); 다음 레코드가 있으면 true bookDTO dto = new bookDTO(); dto.setId(rs.getInt("id")); dto.setTitle(rs.getString("title")); dto.setAuthor(rs.getString("author")); dto.setPrice(rs.getInt("price")); dto.setQty(rs.getInt("qty")); // ArrayList에 추가 list.add(dto); } } catch (Exception e) { e.printStackTrace(); } finally { // 오픈한 역순으로 닫기작업 실행 // resultset= > statement=> connection try { if (rs != null) { rs.close(); } } catch (Exception e2) { e2.printStackTrace(); } try { if (pstmt != null) { pstmt.close(); } } catch (Exception e2) { e2.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (Exception e2) { e2.printStackTrace(); } } return list; } //삽입 public void bookInsert(bookDTO dto) { Connection conn = null; // DB접속 객체 PreparedStatement pstmt = null; // SQL실행객체 try{ conn=dbConn(); String sql="insert into book_table(title, author, price, qty)"+" values(?,?,?,?)"; //sql 실행객체 생성 pstmt=conn.prepareStatement(sql); //? 에 입력될 값 매핑 pstmt.setString(1, dto.getTitle()); pstmt.setString(2, dto.getAuthor()); pstmt.setInt(3, dto.getPrice()); pstmt.setInt(4, dto.getQty()); //executeQuery() select 명령어 //executeUpdate select 이외 명령어 pstmt.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally{ //리소스 정리작업 try { if (pstmt != null) { pstmt.close(); } } catch (Exception e2) { e2.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (Exception e2) { e2.printStackTrace(); } } } } | cs |
출처: http://abc1211.tistory.com/82?category=922165 [길위의 개발자]