20201103_27 JVM

728x90

SMALL

1.수동으로 추가

자바프로젝트에서 오른쪽클릭 -build path-add external archives

경로 : C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib

마지막꺼 선택(아무거나 괜찮지만 그래도 최신버전이니까)

package ko.or.iei;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Application {

	public static void main(String[] args) throws ClassNotFoundException {
		Class.forName("oracle.jdbc.driver.OracleDriver");//객체를JVM에 로드
		try (final Connection conn 
				= DriverManager.getConnection(  //drivermanager한테 주소달라고해서 얻어옴
						"jdbc:oracle:thin:@localhost:1521:XE",
						"system", "본인비번."
						);
				final PreparedStatement pstmt =   //얻어온 주소로 preparedstatement만듬
						conn.prepareStatement("SELECT 1 FROM dual");
				){
			final ResultSet rs =
					pstmt.executeQuery();
			rs.next();  //select절을 만족하는 그다음행을 선택
			int value =	rs.getInt(1); //첫번쨰칼럼. 자바와다르게 첫번째는 1
			System.out.println(value);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

그러면 결과적으로 1이 잘 나온다

2.Maven사용

project누르고 configure-maven어쩌구 누르면된다

mvnrepository.com/

Maven Repository: Search/Browse/Explore

Commons Basic Last Release on Nov 2, 2020

mvnrepository.com

여기서 ojdbc쳐서 호환성좋은 버전을 들어가줌

mvnrepository.com/artifact/com.oracle.ojdbc/ojdbc10_g

Maven Repository: com.oracle.ojdbc » ojdbc10_g

Oracle JDBC Driver compatible with JDK8, JDK9, and JDK11

mvnrepository.com

Maven을 카피한다음에 depencies쳐서 안에다 복사해준다

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>kr.or.iei</groupId>
  <artifactId>jdbc2</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>jdbc2</name>
  <description>뭔가있음</description>
  <build>
    <sourceDirectory>src</sourceDirectory>
    <plugins>
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.1</version>
        <configuration>
          <release>14</release>
        </configuration>
      </plugin>
    </plugins>
  </build>
  <dependencies>
  	<!-- https://mvnrepository.com/artifact/com.oracle.ojdbc/ojdbc10_g -->
<dependency>
    <groupId>com.oracle.ojdbc</groupId>
    <artifactId>ojdbc10_g</artifactId>
    <version>19.3.0.0</version>
</dependency>
  	
  </dependencies>
</project>

이렇게

뭐가뭔데요..!

JDBC객체
-DriverManager : 드라이버관리
-Connection : 특정 DB에 대한 커넥션(세션)
-DB 작업을 위한 객체
1.Statement
2.PreparedStatement
-ResultSet : 쿼리 결과를 받아서 저장하는 객체

Statement, PreparedStatement
-executeQuery : 반환을 ResultSet. DQL(SELECT)
-executeUpdate: 반환은 int. 영향받은 행 수. DML(insert, update, delete)
(쿼리분석, 컴파일, 실행. PS는 캐시를해서 속도향상)

차이점
-prepated statement는 캐시 (연속적으로 할 때 빠름, 서로다른것할때는 오히려 느릴 수도 있음)

ResultSet
-DQL 수행결과 저장
-get타입(int columnIndex);
-get타입(String columnLabel);

CRUD
Create(insert) Read(select) Update(update) Delete(delete)

Create

Create SEQUENCE seq_jdbc_example_pk;
CREATE TABLE jdbc_example(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(128) NOT NULL,
    time TIMESTAMP DEFAULT sysdate
    );
    
SELECT * FROM jdbc_example;
INSERT INTO jdbc_example VALUES(seq_jdbc_example_pk.nextval, 'chwon', DEFAULT);
COMMIT;

ALTER TABLE jdbc_example add email VARCHAR2(128);

SQL에서 실행해주고

package ko.or.iei;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Application2 {

	public static void main(String[] args) throws ClassNotFoundException {
		//1. JVM에 클래스로드(Oracle JDBC Driver)
		Class.forName("oracle.jdbc.driver.OracleDriver");
		
		//2. 드라이버 매니저로부터 커넥션 얻어옴
		try (final Connection conn 
				= DriverManager.getConnection(  //DriverManager한테 주소달라고해서 얻어옴
						"jdbc:oracle:thin:@localhost:1521:XE",
						"system", "본인비번."
						);
			)	{
			final String name = "abc";
			final String sqlFormat = "INSERT INTO jdbc_example VALUES(seq_jdbc_example_pk.nextval, '%s', DEFAULT)";
			final String sql = String.format(sqlFormat, name);
			System.out.println(sql);
			Statement statement = conn.createStatement();
			final int affectedRows = statement.executeUpdate(sql);
			System.out.println(affectedRows);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
}

package ko.or.iei;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Application2 {

	public static void main(String[] args) throws ClassNotFoundException {
		//1. JVM에 클래스로드(Oracle JDBC Driver)
		Class.forName("oracle.jdbc.driver.OracleDriver");
		
		//2. 드라이버 매니저로부터 커넥션 얻어옴
		try (final Connection conn 
				= DriverManager.getConnection(  //DriverManager한테 주소달라고해서 얻어옴
						"jdbc:oracle:thin:@localhost:1521:XE",
						"system", "본인비번."
						);
			)	{
			final String name = "abc";
			final String sql = "INSERT INTO jdbc_example VALUES(seq_jdbc_example_pk.nextval, ?, DEFAULT)";
			final PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			final int affectedRows = pstmt.executeUpdate();
			System.out.println(affectedRows);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
}

결과)

칼럼번호

String sql = "SELECT* FROM jdbc_example WHERE id = 4";
				final PreparedStatement pstmt = conn.prepareStatement(sql);
				final ResultSet rs = pstmt.executeQuery();
				rs.next();
				final int id = rs.getInt(1);
				final String name = rs.getString(2);
				final LocalDateTime localDateTime = rs.getTimestamp(3).toLocalDateTime();
				final String email = rs.getString(4);
				System.out.println(id);
				System.out.println(name);
				System.out.println(localDateTime);
				System.out.println(email);

칼럼네임

String sql = "SELECT* FROM jdbc_example WHERE id = 4";
				final PreparedStatement pstmt = conn.prepareStatement(sql);
				final ResultSet rs = pstmt.executeQuery();
				rs.next();
				final int id = rs.getInt("id");
				final String name = rs.getString("name");
				final LocalDateTime localDateTime = rs.getTimestamp("time").toLocalDateTime();
				final String email = rs.getString("email");
				System.out.println(id);
				System.out.println(name);
				System.out.println(localDateTime);
				System.out.println(email);

package ko.or.iei;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;


public class Application4 {
	
	private static final String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:XE";
	//상수지정할때는 카멜표시안하고 각단어를 밑줄로분리
	 
	private static final String DB_USER = "system";
	private static final String DB_PASS = "본인비번.";
	public static void main(String[] args) throws ClassNotFoundException {
        // 1. 클래스 로드
        Class.forName("oracle.jdbc.driver.OracleDriver");

        // 2. 데이터베이스에 접속
        try (final Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PASS)) {

            // 3. 데이터베이스 작업
            final String sql = "SELECT * FROM jdbc_example";
            final PreparedStatement pstmt = conn.prepareStatement(sql);
            final ResultSet rs = pstmt.executeQuery();

            final List<JdbcExample> list = new ArrayList<>();
            while (rs.next()) {
                final JdbcExample jdbcExample = read(rs);
                list.add(jdbcExample);
            }

            // for-each
            for (JdbcExample jdbcExample : list) {
                System.out.println(jdbcExample);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

	
	private static JdbcExample read(ResultSet rs) throws SQLException {
        final long id = rs.getLong("id");
        final String name = rs.getString("name");
        final LocalDateTime time = rs.getTimestamp("time").toLocalDateTime();
        final String email = rs.getString("email");
        return new JdbcExample(id, name, time, email);
    }

}

이거 왜 오류나는데...

package ko.or.iei;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;


public class Application4 {
	
	private static final String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:XE";
	//상수지정할때는 카멜표시안하고 각단어를 밑줄로분리
	 
	private static final String DB_USER = "system";
	private static final String DB_PASS = "본인비번";
	public static void main(String[] args) throws ClassNotFoundException {
        // 1. 클래스 로드
        Class.forName("oracle.jdbc.driver.OracleDriver");

        // 2. 데이터베이스에 접속
        try (final Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PASS)) {

           final String sql = "UPDATE jdbc_example Set name = ? Where 1 = 1";
           final PreparedStatement pstmt = conn.prepareStatement(sql);
           pstmt.setString(1, "java");
           final int affectedRows = pstmt.executeUpdate();
           System.out.println(affectedRows);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

	
	private static JdbcExample read(ResultSet rs) throws SQLException {
        final long id = rs.getLong("id");
        final String name = rs.getString("name");
        final LocalDateTime time = rs.getTimestamp("time").toLocalDateTime();
        final String email = rs.getString("email");
        return new JdbcExample(id, name, time, email);
    }

}

결과값4

728x90

LIST

저작자표시 비영리 변경금지

'JAVA > Java' 카테고리의 다른 글

20201104_27 Review (0)	2020.11.04
20201103_27 A와 B 돈인출, review (0)	2020.11.03
20201030_25 이진법, 상속, 클래스 등 (0)	2020.10.30
20201029_24 자바 해시 (0)	2020.10.29
20201028_23 자바리뷰 (0)	2020.10.28

헤맨 만큼 내 땅이다

20201103_27 JVM

'JAVA > Java' 카테고리의 다른 글

댓글

티스토리툴바

20201103_27 JVM

'JAVA > Java' 카테고리의 다른 글

관련글

댓글

티스토리툴바