¸®´ª½º °ü·Ã °Á¶õ |
---|
Á¦¸ñ | JDBC, mySQL #5 1999/01/03 (16:26) |
À̸§ | ±èÈ¿¿ø |
¹øÈ£ | 89 |
Á¶È¸ | 450 |
º»¹® |
----------------------------------------------------------------------------- JAVA, JDBC¿Í MySQLÀ» ÀÌ¿ëÇÑ À¥°ú DBÀÇ ¿¬µ¿ 5. (Àúµµ ¾ÆÁ÷ °øºÎ ÁßÀ̶ó Ʋ¸° ºÎºÐÀÌ ¸¹À» °Ì´Ï´Ù. Ȥ½Ã ¹ß°ßÇϽŠºÐ²²¼´Â ¾Ë·ÁÁֽʽÿÀ. °¨»çÇÕ´Ï´Ù.) ¾È³çÇϽʴϱî.... InoS ÇãÁ¤¼öÀÔ´Ï´Ù...... 1) MySQLÀÇ »ç¿ë¹ý°ú SQL ¹®¹ý °øºÎ 2) JDBC 3) - i. JDBC DriverÀÇ ¼³Ä¡¿Í ¼³Á¤ - ii. JDBC API <= ¿À´Ã...... ÀÌÁ¦ ÀÌ °ÁÂÀÇ ¸¶Áö¸·¿¡ µé¾î ¼¹±º¿©..... ¸¹Àº µµ¿òÀº µÆ³ª¿©? À½... ÀúµÎ °Á¸¦ óÀ½ ¿Ã·Áº¸´Â °Å°í, ¶Ç ¾ÆÁ÷ ÀúµÎ Àß ¸ð¸£´Â ³»¿ëÀ» °Á·ΠÇÏ·Á´Ï, Èûµå´Â±º¿©. ÇÏÁö¸¸, JDBC¿¡ °üÇÑ ÀÚ·á´Â ±¹³»¿¡¼ ±¸Çϱâ Èûµç ½ÇÁ¤ÀÔ´Ï´Ù. Ã¥ÀÌ ¸î±ÇÀÖ±ä ÇÏÁö¸¸, ´ëºÎºÐ ¹ø¿ªº»ÀÌÁö¿©. ÇÁ·Î±×·¡¹ÖÀ» ¹è¿ì¸é¼ Ç×»ó ´À³¢´Â °Çµ¥, ¿ì¸®³ª¶ó°¡ ³Ê¹« µÚÃÄÀú ÀÖ°í, ¶Ç, »õ·Î¿î ±â¼úÀ» ´ëºÎºÐ ¿Ü±¹¿¡¼ µé¾î¿Â´Ù´Â °Ì´Ï´Ù. ¸ÕÀú ¿ì¸® ³ª¶ó¿¡¼ °³¹ßÀ» ÇÏ°í, ¶Ç Çѱ¹¸»·Î µÈ Ã¥À» ÀÐÀ¸·Á ¼¼°èÀεéÀÌ Çѱ¹¸»À» ¹è¿ì°í, À¯ÇÐÀ» ¿À´Â ¼¼»ó. ¼ÒÇÁÆ®¿þ¾î ´ë±¹À» ¸¸µå´Â °Ô, ¿ì¸®µéÀÇ ¼Ò¸ÁÀº ¾Æ´Ò±î¿©... ¾ÕÀÇ °ÁµéÀ» ÀÐ¾î ¿À¸é¼, µå¶óÀ̹öµµ Á¦´ë·Î ¼³Ä¡ÇϽðí, Å×½ºÆ®µµ Çغ¸¼Ì°ÚÁ®? ÀÌ ¹Ù·Î ¾ÕÀÇ °Á´ ²À ÀÐÀ¸½Ã±â ¹Ù¶ø´Ï´Ù. ¾È ÀÐ°í ³Ñ¾î ¿À½Å ºÐÀº µÚ·Î °¡½Ã±æ.... ¶Ç jdk 1.1 ÀÌ»óÀ» ¾²¼Å¾ß ÇÕ´Ï´Ù. ÀÌÀü ¹öÀüÀº JDBC API°¡ Æ÷ÇÔÀÌ ¾ÈµÇ¾î ÀÖ½À´Ï´Ù. ±×·³ ÀÌÁ¦ ½ÃÀÛÇսôÙ. 1. Áغñ üÁ¶. ¿ì¼± °øºÎ¸¦ À§ÇØ, µ¥ÀÌŸ º£À̽º¸¦ Çϳª ¸¸µéÁ®.... mysqladmin -p create [DB À̸§] Àú¹ø °Á¿¡¼ Æнº¿öµå¸¦ ¸¸µé¾úÀ¸¸é, -p ¿É¼ÇÀ» ºÎÄ¡½Ã±â ¹Ù¶ø´Ï´Ù. ±×·³ ¾ÏÈ£ ¹¯´Â ȸéÀÌ ³ª¿É´Ï´Ù. Àú´Â ±×³É test DB¸¦ ¾²±â·Î Çß¾¹´Ï´Ù. ±×·³ mysql -p test ¶ó°í ÇÁ·ÒÇÁÆ®¿¡¼ Ä¡¸é, test DB¸¦ »ç¿ëÇÒ ¼ö ÀÖ°ÚÁ®... mysql> À§Ã³·³ º¸ÀÌÁ®? ±×·³ Å×À̺íÀ» Çϳª ¸¸µì½Ã´Ù. ¾Æ¹«°Å³ª... Àü beeper tableÀ» ¸¸µé²²¿©. mysql> create table beeper (name char(20), number char(20)) ; beeper Å×À̺íÀ» ¸¸µé¾úÀ¸´Ï, ·¹Äڵ带 Ãß°¡ÇսôÙ. µÉ ¼ö Àִµ¥·Î, Ä£±¸µéÀÇ À̸§°ú »ß»ß¸¦ ¸¹ÀÌ ÀÔ·ÂÇϼ¼¿©. ¿¬½Àµµ ÇÏ°í, ¾ÕÀ¸·Î ÇÒ °øºÎ¸¦ À§ÇØ. mysql> insert into beeper values('ÇãÁ¤¼ö', '015-8409-1531') ; À§Ã³·³ Çؼ insert ÇÏ´Â°Ç ¾ÕÀÇ °Á¿¡¼ ¹è¿ü½À´Ï´Ù. ±â¾ïÀÌ ¾È³ª¸é ¾ÕÀ¸·Î ´Ù½Ã °¡½Ã±æ... ÀÌÁ¦ ÀÔ·ÂÇÑ °ÍÀ» º¼±î¿©? mysql> select * from beeper ; 3 rows in set (0.00 sec) +--------+---------------+ | name | number | +--------+---------------+ | ÇãÁ¤¼ö | 015-8409-1531 | | ÀåÇö¼º | 015-8631-8867 | | Áøûȣ | 012-297-7975 | +--------+---------------+ Èì. Àü 3¸íÀ» ÀÔ·ÂÇß±º¿©. Àå³ »ß»ß´Â Ä¡Áö ¸¿½Ã´Ù. ÀÌÁ¦ ºñ·Î¼Ò ¸ðµç Áغñ°¡ µÆ±º¿©. 2. JDBC API¸¦ ¹è¿ìÀÚ. ÀÌÁ¦ ÀÌ °ÁÂÀÇ °¡Àå Áß¿äÇÑ ºÎºÐ¿¡ ¿ÍÀÖ½À´Ï´Ù. JDBC API¸¦ ¹è¿ì±â À§ÇØ ÀÌ ¾ÕÀÇ ¸¹Àº °Á°¡ ÀÖ¾ú½À´Ï´Ù. ÀÚ. ½ÃÀÛÇØ º¼±î¿©... java appletÀ̳ª, applicationÀÌ JDBC¸¦ ÀÌ¿ëÇØ DB¸¦ ÀÌ¿ëÇϱâ À§Çؼ ¹¹°¡ ¸ÕÀú ÇÊ¿äÇÒ±î¿ä? ¼³°è¸¦ Çغ¾½Ã´Ù. À½. 1) ¸ÕÀú JDBC Driver¸¦ loadÇØ¾ß ÇÏ°Ú°í, 2) ConnectionÀ» ÇÏ°í, 3) Áú¹®À» ÇÒ Statement¸¦ ¸¸µé°í, 4) Áú¹®À» ÇÏ°í, 5) °á°ú°ª(ResultSet)À» ¹Þ¾Æ ¿Í¼ 6) °á°ú¸¦ °¡°ø(Process)ÇÏ¸é µÉ °ÍÀÔ´Ï´Ù. ÀÌ·± ½ÄÀ¸·Î ÇÁ·Î±×·¥À» Â¥¸é µË´Ï´Ù. ¹¹ ¸»·Î´Â ¾î·ÆÁö ¾ÊÁ®? ½ÇÁ¦·Îµµ ¾î·ÆÁö ¾Ê½À´Ï´Ù. JDBC APIÁ¤µµ¸¸ ¸î°³ ¾Ë¾Æ µÎ¸é ½±Á®.... ¸ÕÀú Driver¸¦ loadÇØ º¾½Ã´Ù. 3. DriverÀÇ load Driver¸¦ loadÇϱâ À§Çؼ´Â Class.forName()À» ÀÌ¿ëÇÕ´Ï´Ù. ¿¹¸¦ º¼±î¿©? java.sql.* ; public class test_driver { public static void main(String [] argv) { try { Class.forName("gwe.sql.gweMysqlDriver") ; } catch(ClassNotFoundException e){System.out.println(e.getMessage()) ;} } } À§ÀÇ Äڵ带 ÀúÀåÇÏ°í, ½ÇÇàÇØ º¾½Ã´Ù. ¹°·Ð ¿¡·¯°¡ ¾È ³ª¾ß Á¦´ë·Î µÈ °Ì´Ï´Ù. À§¿¡¼ ¸»ÇÑ °Í ó·³ Class.forName()À» ÀÌ¿ëÇؼ load¸¦ ÇÕ´Ï´Ù. ±× ¾ÈÀÇ ÀÎÀÚ´Â µå¶óÀ̹öÀÔ´Ï´Ù. Class.forName()Àº ClassNotFoundExceptionÀ» throwÇÕ´Ï´Ù. 4. Connection ÇÏÀÚ ÀÌÁ¦ µå¶óÀ̹öµµ ·Îµå°¡ µÇ´Ï, ¿¬°áÀ» ÇؾßÇÏ°ÚÁö¿©..... ´ÙÀ½°ú °°Àº ¼Ò½º¸¦ º¼±î¿©? import java.sql.* ; public class test { public static void main(String [] argv) { Connection con ; try { Class.forName("gwe.sql.gweMysqlDriver") ; } catch(ClassNotFoundException e) {System.out.println(e.getMessage());} try { String url = "jdbc:mysql://localhost:3333/test"; // *Áß¿ä* ¿¬°áÀ» ÇÑ´Ù. con = DriverManager.getConnection(url,"ID","PASSWORD"); System.out.println("OK") ; } catch(SQLException e){System.out.println(e.getMessage());} } } Àü¿¡ º¸¿©µå·È´ø ¼Ò½º¶û °°Àº °ÍÀÌÁö¿©? ÀÌÁ¦ ¼³¸íÀ» Çغ¾½Ã´Ù. ¿¬°áÀº DriverManagerÀÇ Class MethodÀÎ getConnectionÀ» ÀÌ¿ëÇؼ ÇÕ´Ï´Ù. ÀÌ°ÍÀº ConnectionÀ» ¸®ÅÏÇÏÁ®. ´ÙÀ½À» º¾½Ã´Ù. java.sql.DriverManager interface ¼±¾ð public class DriverManager extends Object DriverManager Ŭ¶ó½º´Â JDBC µå¶óÀ̹ö¸¦ manageÇÏ´Â ¼ºñ½º¸¦ Á¦°øÇÕ´Ï´Ù. º¸Åë Connect¸¦ Çϱâ À§ÇØ ¸¹ÀÌ ¾¹´Ï´Ù.. ¼ö¸¹Àº Method°¡ ÀÖÁö¸¸, ´ÙÀ½À» Á¦ÀÏ ¸¹ÀÌ ¾¹´Ï´Ù. public static synchronized Connection getConnection( String URL,String user , String passwd) throws SQLExcetion ÀÌ ÇÔ¼ö´Â ÁÖ¾îÁø URL°ú user, passwd·Î ¿¬°áÀ» ÇÏ°Ô µÈ´Ù. ¸®ÅÏ °ªÀº Connection ÀÔ´Ï´Ù. À§ÀÇ ¿¹Á¦¿¡¼µµ ´ÙÀ½°ú °°ÀÌ ÇßÁ®? con = DriverManager.getConnection(url,"ID","PASSWORD"); ¿©±â¼ conÀº ConnectionÀÇ Instance¿´½À´Ï´Ù. ÀÌÁ¦ Connection interface¿¡ ´ëÇØ ¾Ë¾Æ º¾½Ã´Ù. java.sql.Connection public interface Connection extends Object A Connection represents a session with a named database. Nomally, within a Connection SQL statements are executed and results are returned.(¸¶¶¥È÷ Çؼ®À» Çϱâ Èûµé±º¿©). Connection interface¾ÈÀÇ MethodµéÀº ´ÙÀ½°ú °°½À´Ï´Ù. (¸®ÅÏ °ªµéÀ» Àß ºÁµÎ¼¼¿©) public abstract Statement createStatement() throws SQLException ÀÌ ¸Þ¼Òµå´Â SQL query¸¦ ¼öÇàÇϱâ À§ÇÑ statement¸¦ ¸®ÅÏÇÑ´Ù. statement ¸¦ ÀÌ¿ëÇØ query¸¦ ÇÑ´Ù. public void close() throws SQLException ConnectionÀ» close ÇÑ´Ù. pubic abstract DatabaseMetaData getMetaData() throws SQLException À§ ÀÌ¿Ü¿¡µµ ¸¹Àº ¸Þ¼ÒµéÀÌ ÀÖÁö¸¸, ¸¹ÀÌ ¾È¾²ÀÔ´Ï´Ù. °ü½ÉÀÖÀ¸½Å ºÐµé²²¼´Â Á÷Á¢ http://www.javasoft.com¿¡ °¡½Ã´øÁö. Ã¥À» ã¾Æ º¸½Ã±â ¹Ù¶ø´Ï´Ù. JDBC APIÀÇ ·¹ÆÛ·±½º°¡ Àִ åÀÌ ÀÖ´õ±º¿©.... ¾Æ¹«Æ° ÀÌ·¸°Ô Çؼ, ¿¬°áÀ» ÇÏ°í, ¾ÆÁÖ Áß¿äÇÑ Connection ÀÇ instance¸¦ ¾ò¾ú½À´Ï´Ù. ÀÌÁ¦, query¸¦ Çϱâ À§ÇØ, Statement¸¦ ¸¸µé¾î º¾½Ã´Ù. 5. query¸¦ ¼öÇàÇϱâ À§ÇØ Statement¸¦ ¸¸µéÀÚ. Àü¿¡ select * from user ; µîÀ» ÀÌ¿ëÇؼ query¸¦ ÇßÁö¿©..... JDBC¸¦ ÀÌ¿ëÇÏ´Â °Íµµ ´Ù java appletÀ̳ª application¿¡¼ ÀÌ·± °ÍµéÀ» ¼öÇàÇϱâ À§Çؼ ÀÔ´Ï´Ù. ÀÌ·± °ÍÀ» java applet µî¿¡¼ Çϱâ À§ÇØ, Statement°¡ ÇÊ¿äÇÕ´Ï´Ù. ´ÙÀ½ ¼Ò½º¸¦ º¸µµ·Ï ÇսôÙ. import java.sql.* ; public class test { public static void main(String [] argv) { Connection con ; Statement stmt ;//Ãß°¡µÈ ºÎºÐ try { Class.forName("gwe.sql.gweMysqlDriver") ; } catch(ClassNotFoundException e) {System.out.println(e.getMessage());} try { String url = "jdbc:mysql://localhost:3333/test"; con = DriverManager.getConnection(url,"ID","PASSWORD"); stmt = con.createStatement() ;//¿©±âµµ Ãß°¡ µÇ¾ú±º¿©. } catch(SQLException e){System.out.println(e.getMessage());} } } ÀÌÁ¦ Statement interface¿¡ ´ëÇØ ¾Ë¾Æ º¾½Ã´Ù. java.sql.Statement public interface Statement StatementÀÇ instance´Â SQL statementµîÀ» (select, insert µî) À» ¼öÇàÇÏ°í, ±× °á°ú·Î, ResultSetÀ» ¾ò´Â´Ù. ¿ª½Ã ¸¹Àº ¸Þ¼ÒµåµéÀÌ ÀÖÁö¸¸, ¸¹ÀÌ ¾²ÀÌ´Â ´ÙÀ½°ú °°Àº °Íµé¸¸ º¾½Ã´Ù. (¸¶Âù°¡Áö·Î return °ªÀÌ ¹ºÁö Àß º¸½Ê½Ã¿À) public abstract ResultSet executeQuery(String sql) throws SQLException º¸Åë, select¸¦ ¼öÇàÇÒ¶§ ¸¹ÀÌ ¾²ÀÔ´Ï´Ù. public abstract int executeUpdate(String sql) throws SQLException ÀÌ°Ç, update³ª insert, deleteµî, ·¹Äڵ带 º¯°æÇÏ´Â SQL statement¸¦ ¼öÇàÇÒ¶§ ¾²ÀÔ´Ï´Ù. public abstract void close() throws SQLException Á¾·á ÀÌ Á¤µµ¸¸ ¾Ë¾Æ³öµµ ¹¹ ¾µ¸¸ ÇÏ°Ú±º¿©.... Áß¿äÇÑ°Ç ¸®ÅÏ Å¸ÀÔÀÌ ¹ºÁö¸¦ Àß ¾Ë¾ÆµÖ¶ó´Â °Í. 6. query¸¦ ÇÏ°í °á°ú°ª(ResultSet)À» ¾ò¾î ¿Í¼ Ãâ·ÂÀ» Çغ¸ÀÚ. ÀÌ °ÁÂÀÇ ¸¶Áö¸·¿¡ ¿À¼Ë±º¿©..... Çѹø query¸¦ ³Ñ±â°í °á°ú¸¦ ¾ò¾î ¿Í º¸°Ú½À´Ï´Ù. ¸ÕÀú Statement¿¡¼ executeQueryÀÇ ¸®ÅÏ Å¸ÀÔÀÎ ResultSet¿¡ ´ëÇØ ¾Ë¾Æ º¾½Ã´Ù. java.sql.ResultSet public interface ResultSet extends Object ResultSetÀº Statement¸¦ executeÇÔÀ¸·Î½á ¾ò¾îÁø °á°ú¿¡ access ÇÒ ¼ö ÀÖµµ·Ï ÇÑ´Ù. ¿©·¯ ÇàÀÌ °á°ú·Î ³Ñ°Ü Á³À»¶§´Â next()¸¦ ÀÌ¿ëÇؼ ´ÙÀ½ ·¹ÄÚµå·Î À̵¿ÇÒ ¼ö ÀÖ´Ù. °¢ Ä÷³ÀÇ °ªÀº index¿Í Ä÷³ÀÇ À̸§À» °¡Áö°í Á¢±ÙÀ» ÇÒ ¼öÀִµ¥, index ¸¦ °¡Áö°í Á¢±ÙÀ» ÇÏ´Â °ÍÀÌ ´õ ÀϹÝÀûÀÌ´Ù. ¿©±â¿¡µµ ¸¹Àº methodµéÀÌ ÀÖÁö¸¸, ´ÙÀ½°ú °°ÀÌ ¸¹ÀÌ ¾²ÀÌ´Â °Í¿¡ ´ëÇؼ¸¸ ¾Ë¾Æ º¾½Ã´Ù. (¿ª½Ã ¸®ÅÏ°ªÀ» Àß ºÁµÎ¼¼¿©) public abstract boolean next() throws SQLException ´ÙÀ½ ·¹ÄÚµå·Î À̵¿À» ÇÑ´Ù. ¸¸¾à ·¹Äڵ尡 ¾øÀ¸¸é, false¸¦ ¸®ÅÏÇÑ´Ù. while()À» ÀÌ¿ëÇØ ¸ðµç, ·¹Äڵ带 °Ë»öÇÒ ¼ö ÀÖ´Ù. public abstract String getString(int columnIndex) throws SQLException ÀÎÀÚ´Â Ä÷³ À妽ºÀÔ´Ï´Ù. ±× ·¹ÄÚµåÀÇ Ä÷³°ªÀ» StringÀ¸·Î ¸®ÅÏÇÏÁ® ÀÚ¼¼ÇÑ °ÍÀº ´ÙÀ½¿¡ ¿¹¿¡¼ º¾½Ã´Ù. public abstract byte getByte(int columnIndex) throws SQLException byte¸¦ ¾ò¾î ¿É´Ï´Ù. public abstract int getInt(int columnIndex) throws SQLExceotion int¸¦ ¾ò¾î ¿É´Ï´Ù. ÀÌ°Íµé ¸»°íµµ ¸¹ÀÌ ÀÖ½À´Ï´Ù. °¢ ÀÚ·áÇü º°·Î getXXX()°¡ ÀÖ¾¹´Ï´Ù. ¶Ç ÀÎÀÚ¿¡ µû¶ù getXXX(String columnName) µµ Àִµ¥, Ä÷³ À̸§º¸´Ù´Â Ä÷³ index·Î accessÇÏ´Â°Ô ´õ ÁÁÀ¸¹Ç·Î, ¼Ò°³´Â ¾ÈÇÕ´Ï´Ù. ÀÌ Á¤µµ¸é, ´Ù Çß±º¿©. ÀÌÁ¨ ¿¹Á¦¸¦ º¾½Ã´Ù. ¾Æ±î ¿ì¸®°¡ ¸¸µç test DBÀÇ beeper table ¤¿¡¼ °á°ú¸¦ ¾ò¾î ¿Í¼, Ãâ·ÂÀ» ÇÕ´Ï´Ù. ================================================================= import java.sql.* ; public class test { public static void main(String [] argv) { Connection con ; Statement stmt ; ResultSet rs ; String RESULT = "\n" ; try { Class.forName("gwe.sql.gweMysqlDriver") ; } catch(ClassNotFoundException e) {System.out.println(e.getMessage());} try { String url = "jdbc:mysql://localhost:3333/test"; con = DriverManager.getConnection(url,"root","hjsksh"); stmt = con.createStatement() ; rs = stmt.executeQuery("select * from beeper" ) ; while(rs.next()) { RESULT += rs.getString(1) + " | " + rs.getString(2) ; RESULT += "\n" ; } System.out.println(RESULT) ; } catch(SQLException e){System.out.println(e.getMessage());} } } ================================================================== ¾ó¸¶ ±æÁö ¾ÊÀº ¼Ò½ºÀ̳ª, ¾Æ±î ÀÔ·ÂÇÑ test DB¿¡¼ beeper tableÀÇ ¸ðµç ³»¿ëÀ» Àß º¸¿©ÁÖ´Â ±º¿©. ±×´ÙÁö ¾î·ÆÁö´Â ¾È½À´Ï´Ù. Ȥ½Ã ¾î·Æ´Ù¸é, °³ÀÎÀûÀ¸·Î Áú¹®À» ¹ÞÀ»Å×´Ï ¸ÞÀÏÀ» ÁֽʽÿÀ. ÀÌ»ó Áö±Ý±îÁö 5¹øÀÇ °Á·Î, ª°Ô³ª¸¶ JDBC¿¡ ´ëÇØ ¾Ë¾Æ ºÃ½À´Ï´Ù. java application¸¸À» Â¥ºÃÀ¸³ª, appletÀ¸·Î ¸¸µé¸é, WEB°ú DB¸¦ ¿¬µ¿Çϱ⵵ ½±¿ï ²®´Ï´Ù. |