Spring Boot学习篇(四)

1 BLOB(二进制大类型)

1.1 创建tb_blob表,其sql语句如下所示

CREATE TABLE tb_blob(id number primary key,fname VARCHAR2(50) NOT NULL,f blob
)

1.2 在entity包下面创建TbBlob实体类

package com.zlz.entity;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import javax.persistence.Id;
import javax.persistence.Table;@AllArgsConstructor
@NoArgsConstructor
@Data
//@Table(name="tb_blob") 制定查询时的表名
public class TbBlob {@Idprivate Integer id;private String fname;private byte[] f;//数据库blob对应java中的字节数组
}

1.3 在com包.zlz包.mapper包下创建TbBlobMapper接口

package com.zlz.mapper;import com.zlz.entity.TbBlob;
import tk.mybatis.mapper.common.Mapper;public interface TbBlobMapper extends Mapper {int add(TbBlob b);
}

1.4 在mappers文件夹下创建TbBlobMapper.xml

1.4.1 创建后的目录结构如下所示

1.4.2 TbBlobMapper.xml内容如下

insert into tb_blob values(#{id},#{fname},#{f,typeHandler=org.apache.ibatis.type.BlobTypeHandler})

1.5 两种使用场景

1.5.1 将图片写入到数据库中

a 示例代码

@Autowired
TbBlobMapper tbBlobMapper;
//写进去的方法
@Test
public void blobTest() throws Exception {InputStream inputStream=new FileInputStream("C:\\Users\\SSS翱翔万里\\Pictures\\Camera Roll\\1.jpg");byte[] bs=new byte[inputStream.available()];inputStream.read(bs);TbBlob tbBlob = new TbBlob(10,"a.png",bs);tbBlobMapper.add(tbBlob);
}

b.控制台运行截图

c 数据库内容截图1

c.数据库内容截图2(点击了小圆点后出现的图片)

1.5.2 将图片从数据库从读出到指定目录中

a 示例代码

@Test
public void blobRead() throws Exception {TbBlob tbBlob = tbBlobMapper.selectByPrimaryKey(10);OutputStream os = new FileOutputStream("d:\\"+tbBlob.getFname());os.write(tbBlob.getF());os.close();
}

b.控制台运行截图

c 最终效果图

2 CLOB(文本类型)

2.1 创建tb_clob表,其sql语句如下所示

CREATE TABLE tb_clob(id number primary key,fname VARCHAR2(50) NOT NULL,f clob
)

2.2 在entity包下面创建TbClob实体类

package com.zlz.entity;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import javax.persistence.Id;@AllArgsConstructor
@NoArgsConstructor
@Data
//@Table(name="tb_lob") 制定查询时的表名
public class TbClob {@Idprivate Integer id;private String fname;private String f;//数据库clob对应java中String
}

2.3 在com包.zlz包.mapper包下创建TbClobMapper接口

package com.zlz.mapper;import com.zlz.entity.TbBlob;
import com.zlz.entity.TbClob;
import tk.mybatis.mapper.common.Mapper;public interface TbClobMapper extends Mapper {}

2.4 两种使用场景

2.4.1 将txt文本写入到数据库中

a 示例代码

@AutowiredTbClobMapper tbClobMapper;@Testpublic void clob() throws Exception {//clob存储的是大量的文本信息InputStream inputStream=new FileInputStream("d:\\clobTest.txt");//将字节流转换成字符流Reader reader=new InputStreamReader(inputStream);BufferedReader bufferedReader=new BufferedReader(reader);//因为大量拼接,所以用StringBuilderStringBuilder stringBuffer = new StringBuilder();String s=null;while((s=bufferedReader.readLine())!=null){stringBuffer.append(s+"\r\n");}TbClob tbClob = new TbClob(10,"test.txt",stringBuffer.toString());tbClobMapper.insertSelective(tbClob);}

b.控制台运行截图

c 数据库内容截图1

c.数据库内容截图2(点击了小圆点后出现的文本内容)

2.4.2 将文本内容从数据库读出到指定目录中

a 示例代码

@AutowiredTbClobMapper tbClobMapper;
@Testpublic void clobRead() throws Exception {//需要把信息查询到才行TbClob tbClob = tbClobMapper.selectByPrimaryKey(10);OutputStream os=new FileOutputStream("d:\\"+tbClob.getFname());//字节输出流转换成字符输出流 OutputStreamWriterOutputStreamWriter osw= new OutputStreamWriter(os);osw.write(tbClob.getF());osw.close();}

b.控制台运行截图

c 生成的文件的位置

d.生成文件的内容

3.调用存储过程

3.1 存储过程的出参为String类型

3.1.1 创立存储过程zz

-- 转账的存储过程
CREATE or replace procedure zz
(zh1 number,zh2 in number,money number,jg out varchar2)
asvzh1sal number;--用于接收转出账户的余额vcount number:=0;-- 累加受影响行数
beginselect sal into vzh1sal from emp where empno=zh1;if vzh1sal

package com.zlz.mapper;
import tk.mybatis.mapper.common.Mapper;import java.util.Map;public interface TbBlobMapper extends Mapper {//存储过程,要拿到out参数,必须使用map集合void zz(Map map);
}

call zz(#{zh1},#{zh2},#{money},#{jg,mode=OUT,jdbcType=VARCHAR})

@AutowiredTbBlobMapper tbBlobMapper;
@Test
public void t1(){Map map=new HashMap();map.put("zh1", 7499);map.put("zh2", 1111);map.put("money", 100);map.put("jg", null);tbBlobMapper.zz(map);//拿到出参并打印System.out.println(map.get("jg"));
}

@AutowiredTbBlobMapper tbBlobMapper; 
@Testpublic void t1(){Map map=new HashMap();map.put("zh1", 7369);map.put("zh2", 7499);map.put("money", 100);map.put("jg", null);tbBlobMapper.zz(map);//拿到出参并打印System.out.println(map.get("jg"));}

create or replace procedure p3
(losal number,hisal number,jg out sys_refcursor)
as
beginopen jg for select * from emp where salbetween losal and hisal;
end;

package com.zlz.mapper;import com.zlz.entity.Emp;
import tk.mybatis.mapper.common.Mapper;import java.util.Map;public interface EmpMapper extends Mapper {//无论出参是什么类型,要想拿到出参的值,都得是map集合void p3(Map map);
}

call p3(#{a},#{b},#{c,mode=OUT,jdbcType=CURSOR,resultMap=myemp,javaType=ResultSet})

@Test
public void p3(){Map map=new HashMap<>();map.put("a", 2000);map.put("b", 3000);//这句话是一定要的map.put("c", null);em.p3(map);//拿到结果List list = (List) map.get("c");for (Emp emp : list) {System.out.println(emp);}
}