[Java] JDBC 프로그래밍

간단한 JDBC 프로그램으로 오라클과 티베로에 접속 해서 데이터를 조회 하는 샘플 소스를 공유 합니다. 

 

JDBC 샘플 소스 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;


public class JDBC_ConnectionTest {

	public static final String TIBERO_JDBC_CLASS_NAME = "com.tmax.tibero.jdbc.TbDriver";
	public static final String ORACLE_JDBC_CLASS_NAME = "oracle.jdbc.driver.OracleDriver";

	public static void main(String args[]) {

		String DB_USER = "tibero";
		String DB_PASSWORD = "tmax";
		String TB_DB_CONNECTION = "jdbc:tibero:thin:@127.0.0.1:17000:tibero";
		String ORA_DB_CONNECTION = "jdbc:oracle:thin:@127.0.0.1:1521:orclcdb";
		
		String DB_TYPE ="oracle";

		try {

			Properties prop = new Properties();
			prop.put("user", DB_USER);
			prop.put("password", DB_PASSWORD);
			Connection conn = null;
			
			if(DB_TYPE.equals("tibero")){
				Class.forName(TIBERO_JDBC_CLASS_NAME);
				conn = DriverManager.getConnection(TB_DB_CONNECTION, prop);
			}else{
				Class.forName(ORACLE_JDBC_CLASS_NAME);
				conn = DriverManager.getConnection(ORA_DB_CONNECTION, prop);
			}
							
			Statement stmt = conn.createStatement();
			
			try {
				stmt.executeQuery("drop table book");
	
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
			String table =" create table book     \n"  
						+ " (book_id number primary key  \n " 
						+ " ,title varchar(200)  \n " 
						+ " ,category varchar(200)  \n " 
						+ " ,price number    \n " 
						+ " ,insert_date date default current_timestamp \n " 
						+ " )  \n " ;

			stmt.executeQuery(table);
			
			ResultSet rs = null;
			stmt.executeUpdate("insert into book( book_id, title,category,price) values(1,'JDBC program','IT BOOK',20000)");

			rs = stmt.executeQuery("select * From book");
			
			System.out.println("==================== 단건조회 =====================");
			while(rs.next()){
				
				System.out.println(String.format("book_id:[%d],title:[%s],category:[%s],price:[%d],insert_date:[%s]"
						,rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5)));
			}
			System.out.println("=============================================");

			/*Table batch insert*/
			PreparedStatement psmt = conn.prepareStatement("insert into book( book_id, title,category,price) values(?,?,?,?)");

			for (int i = 2; i <= 50; i++) {

				psmt.setInt(1, i);
				psmt.setString(2, "JDBC program_" + i);
				psmt.setString(3, "IT BOOK_" + i);
				psmt.setInt(4, 20000 + (i * 100));

				psmt.addBatch();
			}

			psmt.executeBatch();		
			
			
			System.out.println("==================== batch insert 조회 =====================");
			rs = stmt.executeQuery("select * From book");
			while(rs.next()){
				
				System.out.println(String.format("book_id:[%d],title:[%s],category:[%s],price:[%d],insert_date:[%s]"
						,rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5)));
			}
			System.out.println("=============================================");
			
			rs.close();
			stmt.close();
			psmt.cancel();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}


}

jdbc 드라이버 네임 설정

• 티베로와 오라클 드라이버 명을 설정합니다. .

public static final String TIBERO_JDBC_CLASS_NAME = "com.tmax.tibero.jdbc.TbDriver";
public static final String ORACLE_JDBC_CLASS_NAME = "oracle.jdbc.driver.OracleDriver";

jdbc URL 설정 

• 티베로와 오라클 URL 을 설정합니다. 

String DB_USER = "tibero";
String DB_PASSWORD = "tmax";
String TB_DB_CONNECTION = "jdbc:tibero:thin:@127.0.0.1:17000:tibero";
String ORA_DB_CONNECTION = "jdbc:oracle:thin:@127.0.0.1:1521:orclcdb";

jdbc Connection 생성 

jdbc 단건 입력 후 조회

jdbc batch insert 후 조회 

PreparedStatement addBatch() 를 이용해 여러건을 한번에 입력후 조회 합니다. 

 

psmt.addBatch()

jdbc connection 종료

테스트 결과 

• jdbc 컴파일 및 실행 
• 윈도우와 리눅스 컴파일시 구분자가 다르다 리눅스 ':' , 윈도우=>';'

#윈도우
javac -classpath .;tibero6-jdbc.jar;ojdbc8.jar JDBC_ConnectionTest.java 
java  -classpath .;tibero6-jdbc.jar;ojdbc8.jar JDBC_ConnectionTest 

#리눅스
javac -classpath .:tibero6-jdbc.jar:ojdbc8.jar JDBC_ConnectionTest.java 
java  -classpath .:tibero6-jdbc.jar:ojdbc8.jar JDBC_ConnectionTest

• 실행결과

==================== 단건조회 =====================
book_id:[1],title:[JDBC program],category:[IT BOOK],price:[20000],insert_date:[2022-04-20 20:48:04.0]
=============================================
==================== batch insert 조회 =====================
book_id:[1],title:[JDBC program],category:[IT BOOK],price:[20000],insert_date:[2022-04-20 20:48:04.0]
book_id:[2],title:[JDBC program_2],category:[IT BOOK_2],price:[20200],insert_date:[2022-04-20 20:48:04.0]
book_id:[3],title:[JDBC program_3],category:[IT BOOK_3],price:[20300],insert_date:[2022-04-20 20:48:04.0]
book_id:[4],title:[JDBC program_4],category:[IT BOOK_4],price:[20400],insert_date:[2022-04-20 20:48:04.0]
book_id:[5],title:[JDBC program_5],category:[IT BOOK_5],price:[20500],insert_date:[2022-04-20 20:48:04.0]
book_id:[6],title:[JDBC program_6],category:[IT BOOK_6],price:[20600],insert_date:[2022-04-20 20:48:04.0]
book_id:[7],title:[JDBC program_7],category:[IT BOOK_7],price:[20700],insert_date:[2022-04-20 20:48:04.0]
book_id:[8],title:[JDBC program_8],category:[IT BOOK_8],price:[20800],insert_date:[2022-04-20 20:48:04.0]
book_id:[9],title:[JDBC program_9],category:[IT BOOK_9],price:[20900],insert_date:[2022-04-20 20:48:04.0]
book_id:[10],title:[JDBC program_10],category:[IT BOOK_10],price:[21000],insert_date:[2022-04-20 20:48:04.0]
book_id:[11],title:[JDBC program_11],category:[IT BOOK_11],price:[21100],insert_date:[2022-04-20 20:48:04.0]
book_id:[12],title:[JDBC program_12],category:[IT BOOK_12],price:[21200],insert_date:[2022-04-20 20:48:04.0]
book_id:[13],title:[JDBC program_13],category:[IT BOOK_13],price:[21300],insert_date:[2022-04-20 20:48:04.0]
book_id:[14],title:[JDBC program_14],category:[IT BOOK_14],price:[21400],insert_date:[2022-04-20 20:48:04.0]
book_id:[15],title:[JDBC program_15],category:[IT BOOK_15],price:[21500],insert_date:[2022-04-20 20:48:04.0]
book_id:[16],title:[JDBC program_16],category:[IT BOOK_16],price:[21600],insert_date:[2022-04-20 20:48:04.0]
book_id:[17],title:[JDBC program_17],category:[IT BOOK_17],price:[21700],insert_date:[2022-04-20 20:48:04.0]
book_id:[18],title:[JDBC program_18],category:[IT BOOK_18],price:[21800],insert_date:[2022-04-20 20:48:04.0]
book_id:[19],title:[JDBC program_19],category:[IT BOOK_19],price:[21900],insert_date:[2022-04-20 20:48:04.0]
book_id:[20],title:[JDBC program_20],category:[IT BOOK_20],price:[22000],insert_date:[2022-04-20 20:48:04.0]
=============================================