mysql笔记四——TEXT、LOB类型的使用和存储过程的使用

TEXT类型存储大文本文件、LOB类型存储图片

先建立一个带有text类型和lob类型的表格
CREATE TABLE note(
id INT AUTO_INCREMENT PRIMARY KEY,
node TEXT
);
create table img(
id int,
img blob
);

---

myelipse下使用
需要注意的是，文件的编码必须和数据库编码一致，否则写不进去

package cn.hncu.demo;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;


import cn.hncu.utils.ConnectFactory;

public class LobDemoTextOrImg {
    @Test //读取大文件字段
    public void readTextLob() throws SQLException, IOException{
        Connection con=ConnectFactory.getCon();//自己做的connect工具类
        Statement st=con.createStatement();
        String sql="select * from note";
        ResultSet rs=st.executeQuery(sql);
        while(rs.next()){
            InputStream in=rs.getAsciiStream(2);//通过getAsciiStream（）来获得一个流
            if(in!=null){
                BufferedReader br=new BufferedReader(new InputStreamReader(in));
                String str=null;
                while((str=br.readLine())!=null){
                    System.out.println(str);
                }
                in.close();
                br.close();
            }
        }
        con.close();
    }
    @Test //写大文件字段
    public void writeLob222() throws Exception{
        Connection con=ConnectFactory.getCon();
        String sql = "insert into note values(?,?)";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setInt(1, 4);

        InputStream in = LobDemoTextOrImg.class.getClassLoader().getResourceAsStream("JdbcDemo.abc");
        pst.setAsciiStream(2, in);
        pst.executeUpdate();
        con.close();
    }
    @Test //PreparedStatement写大文件字段,需要用到PreparedStatement的传参方式
    public void writeTextLob() throws Exception{//因为编码设置，只能写Utf-8编码的
        Connection con=ConnectFactory.getCon();//自己做的connect工具类
        String sql = "insert into note values(?,?)";
        java.sql.PreparedStatement ps=con.prepareStatement(sql);
//      InputStream in=LobDemoText.class.getClassLoader().getResourceAsStream("cn/hncu/demo/JdbcDemo.class");
        InputStream in =new FileInputStream("G:/a/JdbcDemo.java");
        ps.setInt(1, 22);
        ps.setAsciiStream(2, in);//用到ps.setAsciiStream()
        ps.executeUpdate();
        con.close();
    }
    @Test
    public void test() throws SQLException{
        Connection con=ConnectFactory.getCon();//自己做的connect工具类
        Statement st=con.createStatement();
        String sql = "  SET character_set_results=utf8;";
//      st.execute(sql);
        String sql2 = " SHOW VARIABLES WHERE variable_name LIKE 'character%';";
        ResultSet rs=st.executeQuery(sql2);
        while(rs.next()){
            System.out.println(rs.getString(1)+",,,"+rs.getString(2));
        }
    }
    @Test //写大文件字段
    /*TinyBlob 最大支持255
     Blob 最大支持65k
     MediumBlob 最大支持16M
     LongBlob 最大支持4G*/
    public void writeImgLob() throws Exception{
        Connection con=ConnectFactory.getCon();
        String sql="insert into img values(?,?)";
        PreparedStatement ps=con.prepareStatement(sql);
        ps.setInt(1, 1);
        InputStream in=LobDemoTextOrImg.class.getClassLoader().getResourceAsStream("women.jpg");
        if(in!=null){
            ps.setBinaryStream(2, in);//需要二进制写入
            ps.execute();
        }
        con.close();
    }
    @Test
    public void readImgLob() throws Exception{
        Connection con=ConnectFactory.getCon();
        String sql="select *from img";
        Statement st=con.createStatement();
        ResultSet rs=st.executeQuery(sql);
        while(rs.next()){
            rs.getInt(1);
            InputStream in=rs.getBinaryStream(2);
            if(in!=null){
                FileOutputStream fout=new FileOutputStream("G:/a/a.jpg");
                int len=0;
                byte[] buf=new byte[512];
                while((len=in.read(buf))!=-1){
                    fout.write(buf, 0, len);
                }
                in.close();
                fout.close();
            }
        }
        con.close();
    }
}

存储过程的使用

※※存储过程※※※
定义：
create procedure 过程名(参数)
begin
多条sql语句
end

调用：
call 过程名(实参)

实例1–无参的存储过程：
△小细节：要把默认的语句结束“;”号改成其它如“$$”，这样存储过程中定义的分号就不被看成是语句结束(否则会直接被提交)。最后再把“;”号还原成默认的结束符。

delimiter

$$create procedure p1() begin insert into stud values('P0010','小李',23); select * from stud; end$$
delimiter ;

call p1();

实例2–有参的存储过程：
delimiter

$$create procedure p2( in id varchar(32), in nm varchar(30), in age int ) begin insert into stud values(id,nm,age); select * from stud; end$$
delimiter ;

call p2(‘P011’,’小五’,28);

实例3–有返回值的存储过程：
delimiter

$$create procedure p3( in id varchar(32), in nm varchar(30), in age int, out num int ) begin insert into stud values(id,nm,age); select * from stud; select count(*) into num from stud; end$$
delimiter ;

CALL p3(‘P012’,’小小五’,27, @aa); /调用且用aa接收结果/

SELECT @aa; /显示用户变量aa/
系统变量名称：@@变量名
用户变量名称：@变量名

//binary
mysql查询默认是不区分大小写的如：
select * from? table_name where? a like? ‘a%’???
select * from? table_name where? a like? ‘A%’???
select * from table_name where a like ‘a%’
select * from table_name where a like ‘A%’
效果是一样的。

要让mysql查询区分大小写，可以：
select? * from? table_name where? binary? a like? ‘a%’??
select? * from? table_name where? binary? a like? ‘A%’???
select * from table_name where binary a like ‘a%’
select * from table_name where binary a like ‘A%’
也可以在建表时，加以标识?
create table table_name(
a varchar(20) binary
)

myelipese下调用

package cn.hncu.demo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import org.junit.Test;

import cn.hncu.utils.ConnectFactory;

public class storeProcsDemo {
    /*
     * 演示在java中执行存储过程
     */
    @Test//不带参数
    public void callProcedureDemo() throws SQLException{
        Connection con=ConnectFactory.getCon();
        String sql="call p1();";
        CallableStatement cst=con.prepareCall(sql);//CallableStatement类
        cst.executeQuery();
    }
    @Test//带输入参数
    public void callProcedureDemo2() throws SQLException{
        Connection con=ConnectFactory.getCon();
        String sql="call p2(?,?,?);";
        CallableStatement cst=con.prepareCall(sql);
        cst.setString(1, "P2012");
        cst.setString(2, "王建安");
        cst.setInt(3, 20);
        cst.executeQuery();
    }
    @Test//带输入参数和输出参数
    public void callProcedureDemo3() throws SQLException{
        Connection con=ConnectFactory.getCon();
        String sql="call p3(?,?,?,?);";//最后一个为输出参数
        CallableStatement cst=con.prepareCall(sql);
        cst.setString(1, "P1032");
        cst.setString(2, "Bigg");
        cst.setInt(3, 50);
        cst.registerOutParameter(4, Types.INTEGER);//获取值
        cst.execute();
        int num=cst.getInt(4);
        System.out.println(num);
    }
}