------------------------------ JDBC | ------------------------------ # JDBCÊÇÓÉһЩ½Ó¿Ú¹¹³ÉµÄAPI(Java Data Base Connectivity,javaÊý¾Ý¿âÁ¬½Ó); # Á¬½ÓÊý¾Ý¿âµÄ²½Öè ¢Ù×¢²áÇý¶¯(Ö»×öÒ»´Î) ¢Ú½¨Á¢Á¬½Ó(Connection) ¢Û´´½¨Ö´ÐÐSQLÓï¾ä(Statement) ¢ÜÖ´ÐÐÓï¾ä ¢Ý´¦ÀíÖ´Ðнá¹û(ResultSet) ¢ÞÊÍ·Å×ÊÔ´ # MySql¡ª¡ªurl¸ñʽ * JDBC:×ÓЭÒé:×ÓÃû³Æ//Ö÷»úÃû:¶Ë¿Ú/Êý¾Ý¿âÃû?ÊôÐÔÃû=ÊôÐÔÖµ& * ±¾»úÁ¬½Ó:"jdbc:mysql://www.cqsic.com:3366/wuyedb?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true" ------------------------------ JDBC-API | ------------------------------ DriverManager # ¾²Ì¬·½·¨ Connection getConnection(String url, String user, String password) ; * ¸ù¾ÝURLÓû§ÃÜÂë,»ñÈ¡Ò»¸öÁ¬½Ó Connection # ´ú±íÒ»¸öÊý¾Ý¿âÁ¬½Ó # ʵÀý·½·¨ Statement createStatement(); * ´´½¨Ò»¸öStatement¶ÔÏó PreparedStatement prepareStatement(String sql); * ¸ù¾ÝSQL³ö´´½¨Ò»¸öPreparedStatement¶ÔÏó CallableStatement prepareCall(String sql); * ´´½¨Ò»¸öCallableStatement¶ÔÏóÀ´µ÷ÓÃÊý¾Ý¿â´æ´¢¹ý³Ì setAutoCommit(boolean transaction); * Èç¹û¸ÃֵΪ false Ôò¿ªÆôÊÖ¶¯ÊÂÎñ,ÐèÒªÊÖ¶¯Ö´ÐÐ commit ²Å»áÌá½» commit(); * Ìá½»ÊÂÎñ rollback(); * »Ø¹öÊÂÎñ Statement # ÊÇÒ»¸ö½Ó¿Ú,¿ÉÒÔʹÓÃËüÀ´Ö´ÐÐSQLÓï¾ä # ʵÀý·½·¨ boolean execute(String sql) ; * Ö´ÐÐSQL ResultSet executeQuery(String sql); * Ö´Ðиø¶¨µÄ SQL ¼ìË÷ Óï¾ä£¬¸ÃÓï¾ä·µ»Øµ¥¸ö ResultSet ¶ÔÏó¡£ int executeUpdate(String sql) * Ö´Ðиø¶¨ SQL Óï¾ä£¬¸ÃÓï¾ä¿ÉÄÜΪ INSERT¡¢UPDATE »ò DELETE Óï¾ä£¬»òÕß²»·µ»ØÈκÎÄÚÈÝµÄ SQL Óï¾ä£¨Èç SQL DDL Óï¾ä£©¡£ * ·µ»ØÊÜÓ°ÏìµÄÐÐÊý |-PreparedStatement # Ô¤±àÒëSQLµÄ,statement,¿ÉÒÔ·ÀÖ¹SQL×¢Èë # ʵÀý·½·¨ setString(int index, String param); * ¸øµÚÒ»¸öÎʺŸ³Öµ * ÓÐN¶àÖØÔØ(setXxx),¿ÉÒÔ¸³Öµ²»Í¬µÄÊý¾ÝÀàÐÍ * ×¢Òâ'?'ºÅµÄ½Ç±êÊÇ´Ó1¿ªÊ¼,¶ø²»ÊÇ0 boolean execute() * Ö´ÐÐSQLÓï¾ä¸ÃÓï¾ä¿ÉÒÔÊÇÈκÎÖÖÀàµÄ SQL Óï¾ä¡£ ResultSet executeQuery(); * Ö´ÐмìË÷,²¢·µ»Ø¸Ã²éѯÉú³ÉµÄ ResultSet ¶ÔÏó¡£ int executeUpdate() * Ö´ÐвåÈë»òÕßÐÞ¸ÄÓï¾ä |-CallableStatement # µ÷Óô洢¹ý³ÌµÄ statement ResultSet # SQLÖ´Ðнá¹û¼¯ # ʵÀý·½·¨ first() * ½«¹â±êÒƶ¯µ½´Ë ResultSet ¶ÔÏóµÄµÚÒ»ÐС£ next(); * Ö¸ÕëÒƵ½ÏÂÒ»ÐÐ,Èç¹ûÓÐÏÂÒ»Ðзµ»Ø true,·´Ö®·µ»Ø false previous(); * Ö¸ÕëÒƵ½ÉÏÒ»ÐÐ getObject(int columnIndex); * »ñÈ¡Ö¸¶¨ index ÁеÄÊý¾Ý,ÒÔObjectÐÎʽ·µ»Ø getObject(String columnLabel); * »ñÈ¡Ö¸¶¨ ÁÐÃû³Æ µÄÊý¾Ý,ÒÔObjectÐÎʽ·µ»Ø getString(String columnName); * »ñÈ¡Ö¸¶¨×ֶεÄStringÀàÐÍÖµ getString(int columnIndex); * »ñÈ¡Ö¸¶¨Ë÷ÒýµÄStringÀàÐÍÖµ * ÓдóÁ¿µÄ getXxx();´æÔÚ,°üº¬ÁËJAVA¸÷ÖÖ»ù±¾Êý¾ÝÀàÐ͵ķµ»ØÖµ Blob # ¶þ½øÖÆÊý¾Ý¶ÔÏó # ¹¹Ôì Blob blob = new SeriaBlob(byte[] bytes); # ʵÀý·½·¨ InputStream blob.getBinaryStream(); * »ñÈ¡¸Ã Blob ¶ÔÏóÖеĶÁÈ¡Á÷ ------------------------------ JDBC-²Ù×÷¶þ½øÖÆÊý¾Ý | ------------------------------ # "±ê×¼SQL"ÖÐÌṩÁËÈçÏÂÀàÐÍÀ´±£´æ´óµÄ¶þ½øÖÆÊý¾ÝÀàÐÍ tinyblob 256B -- ¶þ½øÖÆ(×Ö½Ú) blob 64k mediumblob 16M longblob 4G # "±ê×¼SQL"ÖÐÌṩÁËÈçÏÂÀàÐÍÀ´±£´æ´óµÄÎı¾Êý¾ÝÀàÐÍ tinyclob 256B clob 645KB mediumclob 16MB longclob 4G # "MYSQL"Öж¨ÒåµÄ´óÊý¾Ý×Ö·ûÀàÐÍ tinytext 256B text 64KB mediumtext 16MB longtext 4G * Ò»°ã,ÍùMYSQLÖдæÈë¹ý´óÊý¾ÝµÄʱºòÐèÒªÔÚÔÚmy.iniÖÐ×îºóÌí¼ÓÅäÖà * max.allowed packet=10485760 * ±íʾÉèÖÃMYSQLµÄÊý¾Ý´æ´¢´óС # JAVA²Ù×÷¶þ½øÖÆÊý¾Ý * Ö÷ÒªÊÇ Blob blob = new SeriaBlob(byte[] bytes); ¶ÔÏó * ´æÈë public static void set()throws Exception { Connection conn = JDBCUtils.getConnection(); String sql = "insert into tab_bin values(?,?,?)"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, 1); //Êý¾Ý¿âID×ֶΠpstmt.setString(2, "²âÊÔ.mp3"); //Ãû³Æ /** * ÐèÒªµÃµ½Blob¶ÔÏó * 1,ÓÐÎļþ,Ä¿±êÊÇBlob * 3,°ÑÎļþ±ä³Ébyte[]Êý×é * */ File f = new File("D:\\Demo.mp3"); BufferedInputStream in = new BufferedInputStream(new FileInputStream(f)); byte[] bytes = new byte[in.available()];//´´½¨Îļþ´óСµÄ×Ö½ÚÊý×é in.read(bytes); Blob blob = new SerialBlob(bytes); pstmt.setBlob(3, blob);//¶þ½øÖÆÎļþ pstmt.executeUpdate(); } * ¶ÁÈ¡ public static void get()throws Exception { //»ñµÃÁ¬½Ó¶ÔÏó Connection conn = JDBCUtils.getConnection(); String sql = "select * from tab_bin"; PreparedStatement pstmt = conn.prepareStatement(sql); //pstmtÖ´ÐвéѯµÃµ½ResultSet ResultSet rs = pstmt.executeQuery(); //»ñÈ¡rsÖÐÃûΪDateµÄÁÐÊý¾Ý if(rs.next()) { Blob blob = rs.getBlob("data"); /** * °Ñ blob±ä»ØÓ²ÅÌÉϵÄÎļþ * */ InputStream in = blob.getBinaryStream(); BufferedOutputStream bufr = new BufferedOutputStream(new FileOutputStream(new File("C:\\test.mp3"))); byte[] b = new byte[1024]; int len = 0; while((len = in.read(b)) != -1) { bufr.write(b, 0, len); bufr.flush(); } } }