여러종류의 데이타베이스 테스트 소스

출처 :http://www.howto.pe.kr/zboard/zboard.php?id=lecture&page=2&sn1=&divpage=1&sn=off&ss=on&sc=on&keyword=java&select_arrange=vote&desc=asc&no=161

 

[ 여러종류의 데이타베이스 테스트 소스 ]


/*
ODBC 시스템 설정:
제어판->ODBC->시스템 DSN->추가->Driver do Microsoft Access (*.mdb)->마침->
데이타 원본이름: url의 연결 DB명 "testDB"->만들기->testDB.mdb->확인->확인->확인
*/
importjava.sql.*;

public class Test
{
        public static void main ( String args [] )
        {
                String driver = "sun.jdbc.odbc.JdbcOdbcDriver";//ODBC
                //String driver = "oracle.jdbc.driver.OracleDriver";//Oracle
                String url = "jdbc:odbc:testDB";//ODBC. testDB ODBC 연결명
                //String url = "jdbc:oracle:thin:@192.168.1.103:1521:ORCL";//Oracle thin. @ip 혹은 리스너명:포트:SID
                //String url = "jdbc:oracle:oci8:@";//Oracle oci
                //String url="jdbc:msql://203.253.23.3:1114/mydb";//miniSQL
                //String url = "jdbc:postgres95:mydb";//Postgres
                String id = "scott";
                String password = "tiger";

                try//드라이버 로드
                {
                        Class.forName ( driver );
                }           
                catch ( Exception e ) { System.out.println ( "1: Driver Fail..." ); return; }
        
                Connection con = null;
                try//디비 접속
                {
                        con = DriverManager.getConnection ( url, id, password );
                        
                        DatabaseMetaData dbMeta = con.getMetaData ( );//데이타베이스 정보 추출
                        
                        System.out.println ( "ProductName: " + dbMeta.getDatabaseProductName ( ) );
                        System.out.println ( "ProductVersion: " + dbMeta.getDatabaseProductVersion ( ) );
                        System.out.println ( "DriverName: " + dbMeta.getDriverName ( ) );
                        System.out.println ( "DriverVersion: " + dbMeta.getDriverVersion ( ) );
                        System.out.println ( "URL: " + dbMeta.getURL ( ) );
                        System.out.println ( );
                }           
                catch ( Exception e ) { System.out.println ( "2: Connect Fail..." ); return; }

                Statement sm = null;    
                try//쿼리 수행
                {
                        sm = con.createStatement ( );
                        
                        sm.executeUpdate ( "drop table test" );//테이블 삭제. 테이블이 없으면 다음부터
                        
                        sm.executeUpdate ( "create table test ( user varchar (20), password varchar (20) ) ");//테이블 생성

                        sm.executeUpdate ( "insert into test values ( 'yuksamgu', '6390' )" );//데이타 입력
                        
                        sm.executeUpdate ( "update test set password='639' where user='yuksamgu'" );//데이타 변경
                        
                        ResultSet rs = sm.executeQuery ( "select * from test" );//데이타 추출
                        
                        ResultSetMetaData rsMeta = rs.getMetaData ( );//필드 정보 추출
                        
                        int columnType = 0; String columnTypeName = "", columnLabel = "";
                        for ( int i = 1; i <= rsMeta.getColumnCount ( ); i++ )//i=1과 <=에 주의
                        {
                                columnType = rsMeta.getColumnType ( i );
                                columnTypeName = rsMeta.getColumnTypeName ( i );
                                columnLabel = rsMeta.getColumnLabel ( i );//필드 라벨명
                                
                                System.out.print ( columnLabel + "(" + columnType + "," + columnTypeName + ")" + "\t" );
                        }
        
                        System.out.println ( );
                        
                        String field = "";
                        while ( rs.next ( ) )//레코드 위치 증가
                        {
                                for ( int i = 1; i <= rsMeta.getColumnCount ( ); i++ )
                                {
                                        field = format ( rs, rsMeta.getColumnType ( i ), i );
                                        
                                        //field = rs.getString ( i );//i 번째 필드 추출
                                        
                                        System.out.print ( field + "\t\t" );
                                }
                                
                                System.out.println ( );
                        }
                }
                catch ( Exception e ) { System.out.println ( "3: SQL Fail..." ); return; }
                        
                try//닫기
                {                
                        sm.close ( );
                        con.close ( );
                }           
                catch ( Exception e ) { System.out.println ( "4: Close Fail..." ); return; }
        }

        private static String format ( ResultSet rs, int dataType, int col ) throws SQLException
        {
                switch ( dataType )
                {
                        case Types.DATE:
                                java.sql.Date date = rs.getDate(col);
                                return date.toString();
                        case Types.TIME:
                                java.sql.Time time = rs.getTime(col);
                                return time.toString();
                        case Types.TIMESTAMP:
                                java.sql.Timestamp timestamp = rs.getTimestamp(col);
                                return timestamp.toString();
                        case Types.CHAR:
                        case Types.VARCHAR:
                        case Types.LONGVARCHAR:
                                String str = rs.getString(col);
                                return str;
                        case Types.NUMERIC:
                        case Types.DECIMAL:
                                java.math.BigDecimal numeric = rs.getBigDecimal(col, 10);
                                return numeric.toString();
                        case Types.BIT:
                                boolean bit = rs.getBoolean(col);
                                return String.valueOf ( new Boolean ( bit ) );
                        case Types.TINYINT:
                                byte tinyint = rs.getByte(col);
                                return String.valueOf ( new Integer ( tinyint ) );
                        case Types.SMALLINT:
                                short smallint = rs.getShort(col);
                                return String.valueOf ( new Integer ( smallint ) );
                        case Types.INTEGER:
                                int integer = rs.getInt(col);
                                return String.valueOf ( new Integer ( integer ) );
                        case Types.BIGINT:
                                long bigint = rs.getLong(col);
                                return String.valueOf ( new Long ( bigint ) );
                        case Types.REAL:
                                float real = rs.getFloat(col);
                                return String.valueOf ( new Float ( real ) );
                        case Types.FLOAT:
                        case Types.DOUBLE:
                                double longreal = rs.getDouble(col);
                                return String.valueOf ( new Double ( longreal ) );
                        case Types.BINARY:
                        case Types.VARBINARY:
                        case Types.LONGVARBINARY:
                                byte[] binary = rs.getBytes(col);
                                return new String ( binary, 0 );
                        default:
                                return "";
                }
        }
}

/*
결과:
ProductName: ACCESS
ProductVersion: 04.00.0000
DriverName: JDBC-ODBC Bridge (ODBCJT32.DLL)
DriverVersion: 2.0001 (04.00.4403)
URL: jdbc:odbc:testDB

user(12,VARCHAR)        password(12,VARCHAR)
yuksamgu                    639
*/