이렇게 자바에 적어 놓으면
나중에 배포 후 비밀번호 변경시
변경시마다 재배포해야하는 문제가 있다.
객체화 후 DI하여 배포하자
DB접속 주석처리
DataSource추가
세터추가
Class.~주석
커넥션 추가
package com.newlecture.web.service.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import com.newlecture.web.entity.Notice;
import com.newlecture.web.service.NoticeService;
public class JDBCNoticeService implements NoticeService{
/*
* private String url = "jdbc:oracle:thin:@localhost:1521/orclpdb"; private
* String uid = "NEWLEC"; private String pwd = "1111"; private String driver =
* "oracle.jdbc.driver.OracleDriver";
*/
private DataSource dataSource;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
public List<Notice> getList(int page, String field, String query) throws ClassNotFoundException, SQLException{
int start = 1 + (page-1)*10; // 1, 11, 21, 31, ..
int end = 10*page; // 10, 20, 30, 40...
String sql = "SELECT * FROM NOTICE_VIEW WHERE "+field+" LIKE ? AND NUM BETWEEN ? AND ?";
//Class.forName(driver);
//Connection con = DriverManager.getConnection(url,uid, pwd);
Connection con = dataSource.getConnection();
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, "%"+query+"%");
st.setInt(2, start);
st.setInt(3, end);
ResultSet rs = st.executeQuery();
List<Notice> list = new ArrayList<Notice>();
while(rs.next()){
int id = rs.getInt("ID");
String title = rs.getString("TITLE");
String writerId = rs.getString("WRITER_ID");
Date regDate = rs.getDate("REGDATE");
String content = rs.getString("CONTENT");
int hit = rs.getInt("hit");
String files = rs.getString("FILES");
Notice notice = new Notice(
id,
title,
writerId,
regDate,
content,
hit,
files
);
list.add(notice);
}
rs.close();
st.close();
con.close();
return list;
}
// Scalar
public int getCount() throws ClassNotFoundException, SQLException {
int count = 0;
String sql = "SELECT COUNT(ID) COUNT FROM NOTICE";
//Class.forName(driver);
//Connection con = DriverManager.getConnection(url,uid, pwd);
Connection con = dataSource.getConnection();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
if(rs.next())
count = rs.getInt("COUNT");
rs.close();
st.close();
con.close();
return count;
}
public int insert(Notice notice) throws SQLException, ClassNotFoundException {
String title = notice.getTitle();
String writerId = notice.getWriterId();
String content = notice.getContent();
String files = notice.getFiles();
String url = "jdbc:oracle:thin:@localhost:1521/xepdb1";
String sql = "INSERT INTO notice ( " +
" title," +
" writer_id," +
" content," +
" files" +
") VALUES (?,?,?,?)";
//Class.forName(driver);
//Connection con = DriverManager.getConnection(url,uid, pwd);
Connection con = dataSource.getConnection();
//Statement st = con.createStatement();
//st.ex....(sql)
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, title);
st.setString(2, writerId);
st.setString(3, content);
st.setString(4, files);
int result = st.executeUpdate();
st.close();
con.close();
return result;
}
public int update(Notice notice) throws SQLException, ClassNotFoundException {
String title = notice.getTitle();
String content = notice.getContent();
String files = notice.getFiles();
int id = notice.getId();
String url = "jdbc:oracle:thin:@localhost:1521/xepdb1";
String sql = "UPDATE NOTICE " +
"SET" +
" TITLE=?," +
" CONTENT=?," +
" FILES=?" +
"WHERE ID=?";
//Class.forName(driver);
//Connection con = DriverManager.getConnection(url,uid, pwd);
Connection con = dataSource.getConnection();
//Statement st = con.createStatement();
//st.ex....(sql)
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, title);
st.setString(2, content);
st.setString(3, files);
st.setInt(4, id);
int result = st.executeUpdate();
st.close();
con.close();
return result;
}
public int delete(int id) throws ClassNotFoundException, SQLException {
String url = "jdbc:oracle:thin:@localhost:1521/xepdb1";
String sql = "DELETE NOTICE WHERE ID=?";
//Class.forName(driver);
//Connection con = DriverManager.getConnection(url,uid, pwd);
Connection con = dataSource.getConnection();
//Statement st = con.createStatement();
//st.ex....(sql)
PreparedStatement st = con.prepareStatement(sql);
st.setInt(1, id);
int result = st.executeUpdate();
st.close();
con.close();
return result;
}
}
DataSource는 인터페이스인데
인터페이스는 객체화가 안되나 보다?
개념이 부족해서 이건 따로 공부가 필요할 것 같다.
<bean id="noticeService" class="com.newlecture.web.service.jdbc.JDBCNoticeService" >
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521/orclpdb"/>
<property name="username" value="NEWLEC"/>
<property name="password" value="1111"/>
</bean>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.9.RELEASE</version>
</dependency>
성공~
비밀번호나 주소가 변경될 시 여기서 수정해주면 된다.
출처:
https://www.youtube.com/watch?v=C5PXf4JWxCU&list=PLq8wAnVUcTFUHYMzoV2RoFoY2HDTKru3T&index=49
댓글