package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class JDBCUtil {
//싱글톤 패턴 : 인스턴스의 생성을 제한하여 하나의 인스턴스만 사용하는 디자인 패턴
//인스턴스(객체)만 하나만 생성을 한다. ->
//현재 있는 클래스에 객체를 생성하고 다른 클래스가 객체가 필요하면 빌려주게 된다.
//
/*
*
*
*
*
*/
//private 을 붙여서 다른 클래스에서 생성자를 호출할 수 없다.
private JDBCUtil (){
}
//다른 클래스에서 생성자를 호출 할 수 없으니까,
//인스턴스를 보관할 변수
private static JDBCUtil instance;
//인스턴스를 빌려주는 메서드
public static JDBCUtil getInstance(){
if (instance == null){
instance = new JDBCUtil ();
}
return instance;
}
private String url = "jdbc:oracle:thin:@localhost:1521:xe";
private String user = "Aurora";
private String password = "java";
private Connection con = null ;
private PreparedStatement ps = null ;
private ResultSet rs = null ;
/*메소드 종류
*
* Map<String, Object> selectOne(String sql ) //물음표가 없는 경우
* Map<String, Object> selectOne(String sql , List<Object> param) //물음표가 있는경우 List<Object> param 물음표에 담을것 List 는 Arraylist 의
* List<Map<String, Object>> selectList(String sql ) //물음표가 없는 경우
* List<Map<String, Object>> selectList(String sql , List<Object> param ) //물음표가 있는 경우
* int update(String sql) //물음표가 있는경우 -> return 값은
* int update(String sql, List<Object> param) //물음표가 없는경우
*/
public Map<String, Object> selectOne(String sql){
Map<String, Object> row = null;
try {
con = DriverManager.getConnection(url, user, password);
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
while(rs.next()){
row = new HashMap<>();
for(int i = 1; i <= columnCount; i++){
String key = md.getColumnName(i);
Object value = rs.getObject(i);
row.put(key, value);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(rs != null) try { rs.close(); } catch(Exception e) {}
if(ps != null) try { ps.close(); } catch(Exception e) {}
if(con != null) try { con.close(); } catch(Exception e) {}
}
return row;
}
public Map<String, Object> selectOne(String sql, List<Object> param){
Map<String, Object> row = null;
try {
con = DriverManager.getConnection(url, user, password);
ps = con.prepareStatement(sql);
for(int i = 0; i < param.size(); i++){
ps.setObject(i + 1, param.get(i));
}
rs = ps.executeQuery();
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
while(rs.next()){
row = new HashMap<>();
for(int i = 1; i <= columnCount; i++){
String key = md.getColumnName(i);
Object value = rs.getObject(i);
row.put(key, value);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(rs != null) try { rs.close(); } catch(Exception e) {}
if(ps != null) try { ps.close(); } catch(Exception e) {}
if(con != null) try { con.close(); } catch(Exception e) {}
}
return row;
}
public List<Map<String, Object>> selectList(String sql){
List<Map<String, Object>> list = new ArrayList<>();
try {
con = DriverManager.getConnection(url, user, password);
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
while(rs.next()){
Map<String, Object> row = new HashMap<>();
for(int i = 1; i <= columnCount; i++){
String key = md.getColumnName(i);
Object value = rs.getObject(i);
row.put(key, value);
}
list.add(row);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(rs != null) try { rs.close(); } catch(Exception e) {}
if(ps != null) try { ps.close(); } catch(Exception e) {}
if(con != null) try { con.close(); } catch(Exception e) {}
}
return list;
}
public List<Map<String, Object>> selectList(String sql, List<Object> param){
List<Map<String, Object>> list = new ArrayList<>();
try {
con = DriverManager.getConnection(url, user, password);
ps = con.prepareStatement(sql);
for(int i = 0; i < param.size(); i++){
ps.setObject(i + 1, param.get(i));
}
rs = ps.executeQuery();
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
while(rs.next()){
Map<String, Object> row = new HashMap<>();
for(int i = 1; i <= columnCount; i++){
String key = md.getColumnName(i);
Object value = rs.getObject(i);
row.put(key, value);
}
list.add(row);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(rs != null) try { rs.close(); } catch(Exception e) {}
if(ps != null) try { ps.close(); } catch(Exception e) {}
if(con != null) try { con.close(); } catch(Exception e) {}
}
return list;
}
public int update(String sql){
int result = 0;
try {
con = DriverManager.getConnection(url, user, password);
ps = con.prepareStatement(sql);
result = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(rs != null) try { rs.close(); } catch(Exception e) {}
if(ps != null) try { ps.close(); } catch(Exception e) {}
if(con != null) try { con.close(); } catch(Exception e) {}
}
return result;
}
public int update(String sql, List<Object> param){
int result = 0;
try {
con = DriverManager.getConnection(url, user, password);
ps = con.prepareStatement(sql);
for(int i = 0; i < param.size(); i++){
ps.setObject(i + 1, param.get(i));
}
result = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(rs != null) try { rs.close(); } catch(Exception e) {}
if(ps != null) try { ps.close(); } catch(Exception e) {}
if(con != null) try { con.close(); } catch(Exception e) {}
}
return result;
}
}
댓글
댓글 쓰기