OracleConn
package oracleTest01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class OracleConn {
public static void main(String[] args) throws SQLException {
//DataSource
String url ="jdbc:oracle:thin:@127.0.0.1:1521:xe";
String user="nowon";
String password="1234";
Connection con=DriverManager.getConnection(url, user, password);
System.out.println("DB접속완료!" +con);
//1.회원가입 : email, pass, name
Scanner in=new Scanner(System.in);
System.out.println("이메일을 입력하시오.");
String email=in.nextLine();
System.out.println("비밀번호을 입력하시오.");
String pass=in.nextLine();
System.out.println("이름을 입력하시오.");
String name=in.nextLine();
String sql="insert into member "//공백을 주거나 괄호사용
+ "values(seq_mem.nextval, ?,?,?, sysdate)";//문자열 결합 형식시 들어가야할 데이터값에는 ?를 넣는다.
PreparedStatement pstmt=con.prepareStatement(sql);
//?에 대응하는 데이터 셋팅
pstmt.setString(1, email);
pstmt.setString(2, pass);
pstmt.setString(3, name);
//query가 완성되어서 이제 실행
int n=pstmt.executeUpdate();
System.out.println(n+"개의 회원정보를 삽입하였습니다.");
in.close();
con.close();
}
}
DataSourceConfiguration
package oracleTest01;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.util.Properties;
public class DataSourceConfiguration {
private static Properties dataSource=new Properties();
private DataSourceConfiguration() {}
public static Properties getDataSource() throws IOException {
String fileName="dataSource.properties";
Reader reader = new FileReader(fileName);
dataSource.load(reader);
return dataSource;
}
}
dataSource.properties
url =jdbc:oracle:thin:@127.0.0.1:1521:xe
user=nowon
pass=1234
OracleSelect
package oracleTest01;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.Properties;
import java.util.Scanner;
public class OracleSelect {
public static void main(String[] args) throws IOException, SQLException {
/*
Scanner in= new Scanner(System.in);
System.out.println("조회할 사람의 이름은?");
String pName = "%"+in.nextLine()+"%";
Properties dataSource = DataSourceConfiguration.getDataSource();
String url=dataSource.getProperty("url");
String user=dataSource.getProperty("user");
String password=dataSource.getProperty("pass");
Connection con=DriverManager.getConnection(url, user, password);
String sql = "select * from member where name like ?";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, pName);
//DML(CRUD) : 성향이 다른 명령어 : select -> epdlxjrkqt rmeofh skajwlsms qusrud
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
//long mno=rs.getLong(1); -비추 컬럼인덱스를 이용해서 데이터를 읽어오기ㅣ
long mno=rs.getLong("mno");//숫자보다는 컬럼이릉을 통해 데이터 읽어오기
String email = rs.getString("email");
String pass = rs.getString("pass");
String name = rs.getString("name");
//Timpstamp _created _date=rs.getTimestamp("created_date");
LocalDateTime created_date=rs.getTimestamp("created_date").toLocalDateTime();
System.out.print(mno + ",");
System.out.print(email + ",");
System.out.print(pass + ",");
System.out.print(name + ",");
System.out.println(created_date);
}
*/
Properties dataSource = DataSourceConfiguration.getDataSource();
String url=dataSource.getProperty("url");
String user=dataSource.getProperty("user");
String password=dataSource.getProperty("pass");
Connection con=DriverManager.getConnection(url, user, password);
Scanner in= new Scanner(System.in);
System.out.println("조회할 게시글의 번호은?");
long in_bno = in.nextLong();
//b.CREATED_DATE 현재 데이터 테이블이 같은 이름으로 두개 존재하기 때문에 구분해주어야한다.
String sql = "select bno, title, content, read_count, b.CREATED_DATE , email "
+ "from board b , member m "
+ "where bno=? and b.mno=m.mno ";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setLong(1, in_bno);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
long bno=rs.getLong("bno");
String title = rs.getString("title");
String content = rs.getString("content");
int readCount = rs.getInt("read_count");
LocalDateTime createdDate=rs.getTimestamp("CREATED_DATE").toLocalDateTime();
String writer= rs.getString("email");
BoardDTO board=new BoardDTO(bno, title, content, readCount, createdDate, writer);
System.out.println(board);
System.out.print(bno);
System.out.print(title + ",");
System.out.print(content + ",");
System.out.print(readCount);
System.out.println(createdDate);
System.out.println(writer);
}
con.close();
}
}
BoardDTO
package oracleTest01;
import java.time.LocalDateTime;
public class BoardDTO {
@Override
public String toString() {
return "Board [bno=" + bno + ", title=" + title + ", content=" + content + ", readCount=" + readCount
+ ", createdDate=" + createdDate + ", writer=" + writer + "]";
}
private long bno;
private String title;
private String content;
private int readCount;
private LocalDateTime createdDate;
private long mno;
private String writer;
public BoardDTO(long bno, String title, String content, int readCount, LocalDateTime createdDate, String writer) {
this.bno = bno;
this.title = title;
this.content = content;
this.readCount = readCount;
this.createdDate = createdDate;
this.mno = mno;
this.writer = writer;
}
public long getBno() {
return bno;
}
public void setBno(long bno) {
this.bno = bno;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public int getReadCount() {
return readCount;
}
public void setReadCount(int readCount) {
this.readCount = readCount;
}
public LocalDateTime getCreatedDate() {
return createdDate;
}
public void setCreatedDate(LocalDateTime createdDate) {
this.createdDate = createdDate;
}
public long getMno() {
return mno;
}
public void setMno(long mno) {
this.mno = mno;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
}
