批处理文件和存储过程

Statement 对象和PreparedStatement

package jdbc;

import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

import utils.JdbcUtils;
//二进制的数据存储及读取
/**
 * 创建表
 * create table t1{
 *     id int primary key,
 *     img BLOB
 * }
 * 
 * USE mysite;
CREATE TABLE t3
(
    id INT PRIMARY KEY,
    NAME VARCHAR(20),
    age INT
);
 * 
 * @author kj
 *
 */
public class BlobTest {

    @Test
    public void testSave() throws SQLException, FileNotFoundException {
        //获得连接对象
        Connection conn = JdbcUtils.getConnection();

        Statement stmt = conn.createStatement();
        //将三条语句一起放入stmt对象,一起发送到服务端执行，在DBMS中会编译成一个逻辑单元执行，速度快些
        String sql1 = "insert into t3 values(1,'富兰克林',22)";
        String sql2 = "insert into t3 values(2,'麦克',42)";
        String sql3 = "insert into t3 values(3,'崔佛',32)";
        
        stmt.addBatch(sql1);
        stmt.addBatch(sql2);
        stmt.addBatch(sql3);
        
        stmt.executeBatch();
        JdbcUtils.release(null, stmt, conn);

    }
    @Test
    public void testPrepare() throws SQLException, FileNotFoundException {
        //获得连接对象
        Connection conn = JdbcUtils.getConnection();
        PreparedStatement pstmt = conn.prepareStatement("insert into t3 values(?,?,?)");
        //指定？的值
        pstmt.setInt(1,4);
        pstmt.setString(2, "王飞1");
        pstmt.setInt(3, 56);
        pstmt.addBatch();
        
        pstmt.setInt(1, 5);
        pstmt.setString(2, "王飞2");
        pstmt.setInt(3, 58);
        pstmt.addBatch();
        
        pstmt.setInt(1, 6);
        pstmt.setString(2, "王飞3");
        pstmt.setInt(3, 59);
        pstmt.addBatch();
        
        pstmt.executeBatch();
        
        JdbcUtils.release(null, pstmt, conn);
    }

}

 插入1000条数据

    @Test
    public void testPrepareFor() throws SQLException, FileNotFoundException {
        //获得连接对象
        Connection conn = JdbcUtils.getConnection();
        PreparedStatement pstmt = conn.prepareStatement("insert into t3 values(?,?,?)");
        //指定？的值
        //执行前的时间
        System.out.println(new Date());
        for (int i = 0; i < 1000; i++) {
            pstmt.setInt(1,i);
            pstmt.setString(2, "崔佛-菲利普" + i);
            pstmt.setInt(3, i);
            //缓存
            pstmt.addBatch();
            //为了提高效率，两百条发送一次
            if(i % 200 == 0){
                //执行sql语句
                pstmt.executeBatch();
                //一定要清空缓存
                pstmt.clearBatch();
            }
        }
        //为了防止缓存中还有sql语句没有执行，应当再次执行sql语句
        pstmt.executeBatch();
        //执行前的时间
        System.out.println(new Date());
        
        JdbcUtils.release(null, pstmt, conn);
    }

---

JDBC大数据LOB的存取

大数据也称为LOF(Large Object)，LOB又可分为clob和blob，clob用于存储大文本，blob用于存储二进制数据。

在MySQL中，只有blob和Text。

Text又可分为TINYTEXT,TEXT,MEDIUMTEXT和LONGTEXT

blob又可分为TINYBLOB,BLOB,MEDIUMBLOB和LONGBLOB

---

存储过程

---------------------存储过程---

-- 无参的存储过程 --
DELIMITER ;; -- 指定结束符
CREATE PROCEDURE pro1()
BEGIN 
SELECT * FROM mysite.`t3`;
END;
;;
-- 调用
CALL pro1();;

带参存储过程

-- 带参的存储 in 表示输入参数，整型
DELIMITER ;;
CREATE PROCEDURE pro2(IN a INT)
BEGIN
    SELECT * FROM t3 WHERE id = a;
END;
;;
CALL pro2(5);

带输入参数和输出参数

-- 输出参数  输出参数b
DELIMITER ;;
CREATE PROCEDURE pro4(IN a INT, OUT b VARCHAR(20))
BEGIN
    SELECT NAME INTO b FROM t3 WHERE id = a;
END;
;;

-- 接收参数
CALL pro4(5,@name);
SELECT @name;

---

Java中调用存储过程

//演示JDBC调用存储过程
public class ProcedureTest {

    @Test
    public void callStatement() throws Exception{
        //获得连接对象
        Connection conn = JdbcUtils.getConnection();
        //创建执行存储过程的命令对象
        CallableStatement cstmt = conn.prepareCall("{call pro4(?,?)}");
        //指定? 的值
        cstmt.setInt(1, 6);
        //指定第二个问号是输出参数
        cstmt.registerOutParameter(2, Types.VARCHAR);
        //执行存储过程
        cstmt.executeUpdate();
        //获得返回值
        String name = cstmt.getString(2);
        System.out.println(name);
        
        //释放资源
        JdbcUtils.release(null, cstmt, conn);
    }
}