ÀÚ¹Ù·Î ¸¸µç SQLPLUS
D19_SQLPLUS.java
//---------------------------------------------------------------------------------------------------
import java.sql.Statement;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
/*
2. SQLPLUS ÇÁ·Î±×·¥À» Á¦ÀÛÇغ¸ÀÚ
(±â´É:Å×À̺í»ý¼º, »èÁ¦, ÇÁ·Î½ÃÁ® ¼öÇà, select, delete, update....)
*/
public class D19_SQLPLUS {
static Statement stmt;
static Connection con;
static ResultSet rs;
static ResultSetMetaData rsmd;
static BufferedReader br;
static boolean exitCheck;
public static void main(String[] args) {
String id=null;
String pwd=null;
int chanceToLogin=0;
while(true){
br = new BufferedReader(
new InputStreamReader(System.in));
//»ç¿ëÀÚ ·Î±×ÀÎ
try{
//3¹ø ÀÌ»ó ·Î±×Àο¡ ½ÇÆÐÇá¸é ÇÁ·Î±×·¥ Á¾·á
if(chanceToLogin==3){
System.out.println("ÇÁ·Î±×·¥À» Á¾·áÇÕ´Ï´Ù.");
break;
}
System.out.print("»ç¿ëÀÚ¸í ÀÔ·Â : ");
id = br.readLine();
System.out.print("¾ÏÈ£ ÀÔ·Â : ");
pwd = br.readLine();
//ÀÔ·ÂµÈ id¿Í password·Î connection °´Ã¼ »ý¼º
DBConnetion dbc = new DBConnetion(id, pwd);
con = dbc.getCon();
//sqlÄõ¸®¸¦ ÀԷ¹޴ ºÎºÐÀ» ó¸®ÇÏ´Â input()¸Þ¼Òµå È£Ãâ
input();
}catch(IOException ioe){
System.out.println(ioe.getMessage());
}catch(SQLException se){
//DBConnetion()°´Ã¼ »ý¼º½Ã ID³ª PWD°¡ Ʋ¸®¸é while·çÇÁ óÀ½À¸·Î À̵¿
System.out.print(se.getMessage());
System.out.println("¾ÆÀ̵ð ¶Ç´Â ºñ¹Ð¹øÈ£°¡ Ʋ¸³´Ï´Ù.!");
System.out.println();
chanceToLogin++;
continue;
}catch(Exception e){
System.out.println(e.getMessage());
}finally{
try{
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(con != null) con.close();
if(exitCheck){
System.exit(0);
}
}catch(SQLException se){
System.out.println(se.getMessage());
}
}
}
}
public static void input()throws IOException, Exception{
br = new BufferedReader(
new InputStreamReader(System.in));
String inQuery=""; //ÀԷ¹ÞÀº Äõ¸®
String[] splitedQuery=null;//°ø¹éÀ» ±âÁØÀ¸·Î ºÐÇÒÇÑ Äõ¸®
String query = null; //½ÇÁ¦·Î ½ÇÇàÇÒ Äõ¸®
String tempQuery = null;//Enter°¡ ÀԷµDZâ±îÁöÀÇ ÀԷ°ª
String startString = null;//Äõ¸®ÀÇ Ã¹¹ø° ¹®ÀÚ¿
int lineNum=2;
boolean flag=false;
System.out.print("SQL> ");
while(true){
tempQuery = br.readLine();
inQuery += tempQuery;
if(inQuery.equals("exit")){
//System.out.println("ÇÁ·Î±×·¥À» Á¾·áÇÕ´Ï´Ù.");
exitCheck = true;
throw new Exception("ÇÁ·Î±×·¥À» Á¾·áÇÕ´Ï´Ù.");
//System.exit(0);
}
//ÀԷ°ª ¾øÀÌ enter°¡ µé¾î¿À¸é SQL> ÇÁ·ÒÇÁÆ®·Î ºüÁ®³ª¿È
if(tempQuery.equals("")){
lineNum=2;
inQuery = "";
System.out.print("SQL> ");
continue;
}
//°ø¹éÀ» ±âÁØÀ¸·Î ÀԷ¹ÞÀº Äõ¸®¹®ÀÚ¿À» ÀÚ¸¥´Ù.
splitedQuery = inQuery.split(" ");
for(int i=0; i<splitedQuery.length; i++){
if(splitedQuery[i].trim().equals("begin")){
procedure(inQuery, lineNum);
flag = true;
break;
}
}
//ÇÁ·Î½ÃÀúÀÇ °æ¿ì ¹Ù·Î À§ÀÇ for¿¡¼ ó¸® ÇßÀ¸¹Ç·ÎwhileÀÇ Ã³À½À¸·Î À̵¿
if(flag){
flag=false;
lineNum=2;
inQuery = "";
System.out.print("SQL> ");
continue;
}
if(!inQuery.endsWith(";")){
System.out.print(" "+lineNum+" ");
inQuery += " ";
lineNum++;
continue;
}
//°ø¹éÀ» ±âÁØÀ¸·Î ÀԷ¹ÞÀº Äõ¸®¹®ÀÚ¿À» ÀÚ¸¥´Ù.
splitedQuery = inQuery.split(" ");
//ù¹®ÀÚ·Î " "(°ø¹é)À» ³Ö¾î¼ ½ÃÀÛ¹®ÀÚ´Â À妽º[1]ÀÌ µÈ´Ù.
startString = splitedQuery[0];
//³¡¹®ÀÚÀÎ ';'¸¦ °ø¹éÀ¸·Î replace
query = inQuery.replace(';',' ');
if(startString.equals("select")){
exeQuery(query);
}else if(startString.equals("update") || startString.equals("delete") ||
startString.equals("insert")){
exeUpdate(query);
}else{
exe(query);
}
//´ÙÀ½ Äõ¸® ÀÔ·ÂÀ» À§ÇÑ º¯¼ö ¼ÂÆÃ
lineNum=2;
inQuery = "";
System.out.print("SQL> ");
}
}
public static void exeQuery(String query){
int count=0;//¼öÇàÇÑ ¿ÀÇ ¼ö¸¦ ¼¼´Â º¯¼ö
try{
stmt = con.createStatement();
rs = stmt.executeQuery(query);
rsmd = rs.getMetaData();
//Ä÷³ÀÇ ¼ö¸¦ ¾Ë¾Æ¿Â´Ù.
int colNum = rsmd.getColumnCount();
//Ä÷³ÀÇ ¼ö ¸¸Å ·çÇÁ¸¦ µ¹¸é¼ Ä÷³¸íÀ» Ãâ·ÂÇÑ´Ù.
for(int i=1; i<=colNum; i++){
String colName = rsmd.getColumnName(i);
System.out.print(colName +"\t");
}
System.out.println();
System.out.println("=============================");
//Ä÷³ÀÇ ³»¿ëÀ» °¢ Ä÷³ÀÇ º¯¼öÇü¿¡ µû¶ó ¾ò¾î¿À°í, À̸¦ Ãâ·ÂÇÑ´Ù.
while(rs.next()){
for(int i=1; i<=colNum; i++){
if(rsmd.getColumnTypeName(i).equals("NUMBER")){
System.out.print(rs.getInt(i)+"\t");
}else if(rsmd.getColumnTypeName(i).equals("VARCHAR2")){
System.out.print(rs.getString(i)+"\t");
}else{
System.out.print(rs.getObject(i)+"\t");
}
}
System.out.println();
count++;
}
System.out.println(count+"°³ÀÇ ÇàÀÌ ¼±Åà µÇ¾ú½À´Ï´Ù.");
}catch(SQLException se){
System.out.println(se.getMessage());
}
}
public static void exeUpdate(String query){
try{
stmt = con.createStatement();
int num = stmt.executeUpdate(query);
System.out.println(num+"°³ÀÇ ÇàÀÌ ¼öÇà µÇ¾ú½À´Ï´Ù.");
}catch(SQLException se){
System.out.println(se.getMessage());
}
}
public static void exe(String query){
try{
stmt = con.createStatement();
stmt.execute(query);
System.out.println("SQL ±¸¹®ÀÌ ¼öÇàµÇ¾ú½À´Ï´Ù.");
}catch(SQLException se){
System.out.println(se.getMessage());
}
}
public static void procedure(String query, int lineNum)throws IOException{
br = new BufferedReader(
new InputStreamReader(System.in));
String tempQuery =null;
do{
System.out.print(" "+lineNum+" ");
tempQuery = br.readLine();
if(tempQuery.trim().equals("/")){
try{
query += tempQuery+"\n";
stmt = con.createStatement();
stmt.execute(query);
System.out.println("ÇÁ·Î½ÃÀú°¡ »ý¼ºµÇ¾ú½À´Ï´Ù.");
break;
}catch(SQLException se){
System.out.println(se.getMessage());
break;
}
}
lineNum++;
query += tempQuery;
}while(true);
}
}
//---------------------------------------------------------------------------------------------------
DBConnection.java
//---------------------------------------------------------------------------------------------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
class DBConnetion{
private String id;
private String pwd;
private String url="jdbc:oracle:thin:@localhost:1521:orcl";
private String driver="oracle.jdbc.driver.OracleDriver";
private Connection con;
//SQLExceptionÀº È£ÃâºÎ¿¡¼ ó¸®Çϵµ·Ï throws ÇÑ´Ù.
public DBConnetion(String id, String pwd) throws SQLException{
this.id = id;
this.pwd = pwd;
try{
Class.forName(driver);
con = DriverManager.getConnection(url, id, pwd);
System.out.println(id+"´Ô ¾È³çÇϼ¼¿ä~^^");
}catch(ClassNotFoundException ce){
System.out.println(ce.getMessage());
}
}
public Connection getCon(){
return con;
}
}
//---------------------------------------------------------------------------------------------------
Ãâó: https://invincure.tistory.com/entry/ÀÚ¹Ù·Î-¸¸µç-SQLPLUS?category=299957 [Do you want it? Come get it.]