JDBC
2023. 12. 14. 23:08ㆍDaily Codig Reminder
- 오라클 데이터베이스 연동을 위한 4가지 정보를 저장
String driver =”oracle.jdbc.drtiver.OracleDriver”;
String url =”jdbc:oracle:thin:@localhost:1521:xe”;
String userid =”scott”;
String passwd =”tiger”;
2. 드라이버 로딩
Class.forName(driver);
3. Connection 맺기
Connection con = DriverManager.getConnection (url, userid, passwd);
4. SQL문 작성
String query = “SELECT deptno, dname, loc form dept”;
또는
String query = “DELETE FROM dept WHERE deptno =40”;
5. PrepareStatement 생성
PrepareStatement pstmt = con.prepareStatement(query);
6. SQL 문 ㅈㄴ송 및 결과값 얻기
-DML 요청 (INSERT, DELETE, UPDATE)인 경우 코드
int n = pstmt.executeUpdate();
-SELECT 요청
Result rs = pstmt.executeQuery();
rs.next() 및 rs.getint (컬럼명) ,rs.getString (컬럼멸) 이용하여 데이터 추출 자
7. 자원 반납
rs.close(); stmt.close(); con.close();
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class StatementTest {
public static void main(String[] args) {
//1. db연동에 필요한 4가지 정보
String driver="oracle.jdbc.driver.OracleDriver";//드라이버 클래스명
String url ="jdbc:oracle:thin:@localhost:1521:xe"; ///접속정보 및 sid
String userid="scott";//계정 정보
String passwd ="tiger";
Connection con = null;//db접속객체
Statement stmt = null; //sql실행, 결과 받는 객체
ResultSet rs = null; //select 결과 저장 객체
//2. 드라이버 로딩
try {
Class.forName(driver);
//System.out.println("driver 로딩 성공");
//3. connection (db접속) 얻기
con =DriverManager.getConnection(url, userid, passwd);//db 접속 후 connection에 리턴
//System.out.println(con);
//4.String으로 sql 작성 및 Statement 작성
String sql= "select * from dept"; // ; 필요없음
stmt = con.createStatement();//sql실행할 객체 생성
//5. sql 실행 후 결과 얻기
rs=stmt.executeQuery(sql); //select - executequery , dml은 excuteupdate
//System.out.println(rs);
//6. 결과 출력
//6-1. 헤더부분 출력
ResultSetMetaData mrs =rs.getMetaData();//컬럼명 리턴
int colcount = mrs.getColumnCount();
System.out.println(colcount);
for (int i = 1; i <= colcount; i++) { // i =1, <= 주의
System.out.print(mrs.getColumnName(i)+"\t");
}System.out.println();
System.out.println("==========");
while (rs.next()) {
// int deptno = rs.getInt("deptno");
// String dname = rs.getString("dname");
// String loc = rs.getString("loc");
int deptno = rs.getInt(1);
String dname = rs.getString(2);
String loc = rs.getString(3);
System.out.println(deptno +"\t"+dname+"\t"+loc);
}
} catch (ClassNotFoundException e) {
System.out.println("driver 로딩 실패");
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if (rs!=null)rs.close();
if (stmt!=null)stmt.close();
if (con!=null)con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}//end main
}//end class
public class StatementTest {
public static void main(String[] args) {
//1. db연동에 필요한 4가지 정보
String driver="oracle.jdbc.driver.OracleDriver";//드라이버 클래스명
String url ="jdbc:oracle:thin:@localhost:1521:xe"; ///접속정보 및 sid
String userid="scott";//계정 정보
String passwd ="tiger";
Connection con = null;//db접속객체
Statement stmt = null; //sql실행, 결과 받는 객체
ResultSet rs = null; //select 결과 저장 객체
//2. 드라이버 로딩
try {
Class.forName(driver);
//System.out.println("driver 로딩 성공");
//3. connection (db접속) 얻기
con =DriverManager.getConnection(url, userid, passwd);//db 접속 후 connection에 리턴
//System.out.println(con);
//4.String으로 sql 작성 및 Statement 작성
//int num =10;
// String sql= "select deptno , dname , loc from dept where deptno="+num; // ; 필요없음
String name ="SALES";
String sql= "select deptno x , dname , loc from dept where dname='"+name+"'"; // ; 필요없음
System.out.println(sql);
stmt = con.createStatement();//sql실행할 객체 생성
//5. sql 실행 후 결과 얻기
rs=stmt.executeQuery(sql); //select - executequery , dml은 excuteupdate
//System.out.println(rs);
//6. 결과 출력
//6-1. 헤더부분 출력
ResultSetMetaData mrs =rs.getMetaData();//컬럼명 리턴
int colcount = mrs.getColumnCount();
System.out.println(colcount);
for (int i = 1; i <= colcount; i++) { // i =1, <= 주의
System.out.print(mrs.getColumnName(i)+"\t");
}System.out.println();
System.out.println("==========");
while (rs.next()) {
int deptno = rs.getInt("x");
String dname = rs.getString("dname");
String loc = rs.getString("loc");
// int deptno = rs.getInt(1);
// String dname = rs.getString(2);
// String loc = rs.getString(3);
System.out.println(deptno +"\t"+dname+"\t"+loc);
}
} catch (ClassNotFoundException e) {
System.out.println("driver 로딩 실패");
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if (rs!=null)rs.close();
if (stmt!=null)stmt.close();
if (con!=null)con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}//end main
}//end class
import java.sql.SQLException;
import java.util.ArrayList;
import com.dto.Dept;
import com.exception.RecordNotFoundException;
import com.service.OracleService;
import oracle.net.aso.e;
public class OralceMain {
public static void main(String[] args) {
OracleService service= new OracleService();
// Dept xx= new Dept(99, "영업","서울");
// int num= service.insert(xx);
// System.out.println("in main num = "+ num );
// int num2= service.delete(99);
// System.out.println("in main num = "+ num2 );
//
Dept xx2= new Dept(13, "제경", "부산");
try {
int num3=service.update(xx2);
System.out.println("in main update num = "+ num3 );
} catch (RecordNotFoundException e1) {
// TODO Auto-generated catch block
System.out.println("catch: "+ e1.getMessage());
e1.printStackTrace();
}
// System.out.println("in main delete num = "+ num2 );
try {
ArrayList<Dept> list= service.select();//전체 select
for (Dept dept : list) {
System.out.println(dept);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package com.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.dto.Dept;
import com.exception.RecordNotFoundException;
public class OracleDAO {
// 1. 4가지 정보
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String userid = "scott";
String passwd = "tiger";
public OracleDAO() {
super();
try {
Class.forName(driver);
System.out.println("드리아버 로딩 성공");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public ArrayList<Dept> select() throws SQLException {
ArrayList<Dept> list = new ArrayList<Dept>();
Connection con= null;
PreparedStatement pstmt= null;
ResultSet rs= null;
con= DriverManager.getConnection(url, userid, passwd);
String sql="select deptno x, dname, loc from dept";
pstmt= con.prepareStatement(sql);
rs= pstmt.executeQuery();
while (rs.next()) {
int deptno = rs.getInt("x");
String dname = rs.getString(2);
String loc = rs.getString("loc");
//System.out.println(deptno + "\t" + dname + "\t" + loc);
Dept dto = new Dept(deptno, dname, loc);
list.add(dto);
}
if(rs!= null) rs.close();
if(pstmt!= null) pstmt.close();
if(con != null) con.close();
return list;//service
}
public int insert(Dept xx) {
Connection con= null;
PreparedStatement pstmt= null;
int num=0;
try {
con= DriverManager.getConnection(url, userid, passwd);
String sql="insert into dept(deptno, dname, loc)"
+"values (?,?,?)";
pstmt= con.prepareStatement(sql);
pstmt.setInt(1, xx.getDeptno());
pstmt.setString(2, xx.getDname());
pstmt.setString(3, xx.getLoc());
num= pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return num;
}
public int update(Dept xx) throws RecordNotFoundException {
Connection con= null;
PreparedStatement pstmt= null;
int num=0;
try {
con= DriverManager.getConnection(url, userid, passwd);
String sql="update dept set dname=? , loc=? where deptno=?";
pstmt= con.prepareStatement(sql);
pstmt.setString(1, xx.getDname());
pstmt.setString(2, xx.getLoc());
pstmt.setInt(3, xx.getDeptno());
num= pstmt.executeUpdate();
System.out.println("실행 결과"+ num);
if(num ==0 ) {
throw new RecordNotFoundException(xx.getDeptno()+"번 부서가 없음");
}
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
// 7. 자원반납
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return num;
}
public int delete(int i) {
Connection con= null;
PreparedStatement pstmt= null;
int num=0;
try {
con= DriverManager.getConnection(url, userid, passwd);
String sql="delete from dept where deptno= ?";
pstmt= con.prepareStatement(sql);
pstmt.setInt(1, i);
num= pstmt.executeUpdate();
}catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return num;/////////////////////////////
}
}
드리아버 로딩 성공 실행
결과1
in main update num = 1
Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK]
Dept [deptno=20, dname=RESEARCH, loc=DALLAS]
Dept [deptno=30, dname=SALES, loc=CHICAGO]
Dept [deptno=40, dname=OPERATIONS, loc=BOSTON]
Dept [deptno=13, dname=제경, loc=부산]
import java.sql.SQLException;
import java.util.ArrayList;
import com.dto.Dept;
import com.exception.RecordNotFoundException;
import com.service.OracleTXService;
public class OralceTXMain {
public static void main(String[] args) {
OracleTXService service= new OracleTXService();
//2번클릭
int deptno = 99;
String dname="개발";
String loc ="제주";
Dept xx =new Dept(deptno, dname, loc);
service.insert(xx);
//실행 갯수 출력
//1번클릭
try {
ArrayList<Dept> list= service.select();
for (Dept dept : list) {
System.out.println(dept);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//4번 클릭
//service.delete(99);
//3번 클릭
Dept xx2 =new Dept(99, "경제", "서울");
try {
service.update(xx2);
} catch (RecordNotFoundException e1) {
System.out.println(e1.getMessage());
}
//1번클릭
try {
ArrayList<Dept> list= service.select();
for (Dept dept : list) {
System.out.println(dept);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package com.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.dto.Dept;
import com.exception.RecordNotFoundException;
public class OracleTxDAO {
// 1. 4가지 정보
public OracleTxDAO() {
}
public ArrayList<Dept> select(Connection con) throws SQLException {
ArrayList<Dept> list = new ArrayList<Dept>();
PreparedStatement pstmt= null;
ResultSet rs= null;
String sql="select deptno x, dname, loc from dept";
pstmt= con.prepareStatement(sql);
rs= pstmt.executeQuery();
while (rs.next()) {
int deptno = rs.getInt("x");
String dname = rs.getString(2);
String loc = rs.getString("loc");
//System.out.println(deptno + "\t" + dname + "\t" + loc);
Dept dto = new Dept(deptno, dname, loc);
list.add(dto);
}
if(rs!= null) rs.close();
if(pstmt!= null) pstmt.close();
return list;
}
public void insert(Connection con, Dept xx) {
PreparedStatement pstmt= null;
try {
String sql="insert into dept(deptno, dname, loc)"
+"values (?,?,?)";
pstmt= con.prepareStatement(sql);
pstmt.setInt(1, xx.getDeptno());
pstmt.setString(2, xx.getDname());
pstmt.setString(3, xx.getLoc());
int num= pstmt.executeUpdate();
System.out.println("실행 결과"+ num);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
// 7. 자원반납
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void update(Connection con, Dept xx) throws RecordNotFoundException {
PreparedStatement pstmt= null;
try {
String sql="update dept set dname=? , loc=? where deptno=?";
pstmt= con.prepareStatement(sql);
pstmt.setString(1, xx.getDname());
pstmt.setString(2, xx.getLoc());
pstmt.setInt(3, xx.getDeptno());
int num= pstmt.executeUpdate();
System.out.println("실행 결과"+ num);
if(num ==0 ) {
throw new RecordNotFoundException(xx.getDeptno()+"레코드가 없음");
}
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
// 7. 자원반납
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void delete(Connection con, int i) {
PreparedStatement pstmt= null;
try {
String sql="delete from dept where deptno= ?";
pstmt= con.prepareStatement(sql);
pstmt.setInt(1, i);
int num= pstmt.executeUpdate();
System.out.println("실행 결과"+ num);
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
// 7. 자원반납
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
package com.service;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import com.dao.OracleTxDAO;
import com.dto.Dept;
import com.exception.RecordNotFoundException;
public class OracleTXService {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String userid = "scott";
String passwd = "tiger";
OracleTxDAO dao;
public OracleTXService() {
super();
// TODO Auto-generated constructor stub
dao= new OracleTxDAO();///////////////
try {
Class.forName(driver);
System.out.println("드라이버 로딩 성공");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public ArrayList<Dept> select() throws SQLException{
Connection con= null;
ArrayList<Dept> list= null;
try {//con닫기
con= DriverManager.getConnection(url, userid, passwd);
list = dao.select(con);
}finally {
if(con!= null) con.close();
}
return list;
}
public void insert(Dept xx) {
Connection con= null;
try {//con닫기
con= DriverManager.getConnection(url, userid, passwd);
dao.insert(con, xx);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(con!= null) con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void update(Dept xx) throws RecordNotFoundException {
Connection con= null;
try {//con닫기
con= DriverManager.getConnection(url, userid, passwd);
dao.update(con, xx);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(con!= null) con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void delete(int i) {
Connection con= null;
try {//con닫기
con= DriverManager.getConnection(url, userid, passwd);
dao.delete(con, i);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(con!= null) con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
드라이버 로딩 성공
실행 결과1
Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK]
Dept [deptno=20, dname=RESEARCH, loc=DALLAS]
Dept [deptno=30, dname=SALES, loc=CHICAGO]
Dept [deptno=40, dname=OPERATIONS, loc=BOSTON]
Dept [deptno=13, dname=제경, loc=부산]
Dept [deptno=99, dname=개발, loc=제주]
실행 결과1
Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK]
Dept [deptno=20, dname=RESEARCH, loc=DALLAS]
Dept [deptno=30, dname=SALES, loc=CHICAGO]
Dept [deptno=40, dname=OPERATIONS, loc=BOSTON]
Dept [deptno=13, dname=제경, loc=부산]
Dept [deptno=99, dname=경제, loc=서울]
'Daily Codig Reminder' 카테고리의 다른 글
| My Batis (0) | 2023.12.19 |
|---|---|
| JDBC 2 (0) | 2023.12.19 |
| 자바 IO (0) | 2023.12.14 |
| 리스트, 맵 (0) | 2023.12.12 |
| 예외처리 와 제네릭컬렉션 (1) | 2023.12.11 |