作者:刘伟亮 | 来源:互联网 | 2023-06-24 12:26
大对象Lob字段的处理 Lob实际上有2种类型,分别是用于存储大文本的字段Clob和存储二进制大量数据的字段Blob。不同的数据库处理方式略有区别,大字段一般都是采用流的方式进行处理
由于大字段类型存储的数据较多,一般针对lob字段会采用分表存储的方式实现需求:存储学生信息,要求存储学生的照片
将学生照片存储在文件系统中,实际数据中只存储照片的路径 如果图片要求加密,存储在文件系统中则不合理 可以利用DBMS提供的压缩加密功能将数据存储在数据表 如果具体存储?
一个表中存储相关的基本信息,非大字段数据 一个表中存储大字段数据 Clob Clob在MySQL种的数据类型 text最大存放255的字符text最大存放65535个字符 mediumtext最大存放16M的字符 longtext最大存放4G的字符 表的创建
-- 学生的基本信息 create table tb_student( id bigint primary key auto_increment, name varchar(20) ); -- 学生表对应的大字段数据 create table tb_memo( id bigint -- 具体实现中将其定义为外键,并且支持级联删除 memo longtext );-- 为了测试方便,所以将两个表合在一起 create table tb_student( id bigint primary key auto_increment, name varchar(20), memo longtext );
使用JDBC向表中插入数据
try ( Connection conn = DriverManager . getConnection ( "jdbc: mysql: / serverTimezone= UTC", " root", " 123456 ") ; Reader r = new BufferedReader ( new FileReader ( "memo.txt" ) ) ; ) { PreparedStatement ps = conn. prepareStatement ( "insert into tb_studentvalues ( null , ? , ? ) ") ; ps. setString ( 1 , "张三" ) ; ps. setClob ( 2 , r) ; int len = ps. executeUpdate ( ) ; if ( len > 0 ) System . out. println ( "插入成功!" ) ; else System . out. println ( "插入失败!" ) ; }
如何判断是否支持不用加载驱动
使用JDBC从表中读取大对象类型字段
PreparedStatement ps = conn. prepareStatement ( "select * from tb_student where id=?" ) ; ps. setLong ( 1 , 1 ) ; ResultSet rs = ps. executeQuery ( ) ; if ( rs. next ( ) ) { Long id = rs. getLong ( "id" ) ; String name = rs. getString ( "name" ) ; Clob clob = rs. getClob ( "memo" ) ; Reader r = clob. getCharacterStream ( ) ; System . out. println ( id+ "\t" + name) ; char [ ] buffer= new char [ 8192 ] ; int len= 0 ; while ( ( len= r. read ( buffer) ) > 0 ) { System . out. print ( new String ( buffer, 0 , len) ) ; 14 } r. close ( ) ; 16 }
Blob Blob是二进制大字段类型,一般用于存储图形、声音、视频等二进制序列数据。MySQL针对二进制大对象提供了4种数据类型
tinyblob上限255 blob上限65535 mediumblob上限16M longblob上限4G 表定义:针对大对象类型的字段必须分表存储
alter table tb_student add image longblob;
使用JDBC插入二进制大对象数据
InputStream is = new BufferedInputStream ( new FileInputStream ( "pic/xiaopang.jpg" ) ) ; PreparedStatement ps= conn. prepareStatement ( "update tb_student set image=? where id=?" ) ; ps. setBlob ( 1 , is) ; ps. setLong ( 2 , 1 ) ; int len= ps. executeUpdate ( ) ;
使用JDBC获取二进制大对象数据
PreparedStatement ps = conn. prepareStatement ( "select * from tb_student where id=?" ) ; ps. setLong ( 1 , 1 ) ; ResultSet rs = ps. executeQuery ( ) ; if ( rs. next ( ) ) { Long id = rs. getLong ( "id" ) ; String name = rs. getString ( "name" ) ; String memo = rs. getString ( "memo" ) ; System . out. println ( id + "\t" + name + "\t" + memo) ; Blob image = rs. getBlob ( "image" ) ; InputStream is = image. getBinaryStream ( ) ; OutputStream os = new FileOutputStream ( "c:\\" + name + ".jpg" ) ; byte [ ] buffer = new byte [ 8192 ] ; int len = 0 ; while ( ( len= is. read ( buffer) ) > 0 ) os. write ( buffer, 0 , len) ; os. close ( ) ; is. close ( ) ; 18 }
数据库设计 数据库设计是指对于一个给定的应用环境,构建最优的数据库模型,建立数据库及其应用系统,使之能 够有效地存储数据,满足各种用户的信息要求和处理要求
常见的数据库设计方法:
试凑法:凭借的是开发经验 规范化法:3NF和反范式 CAD计算机辅助设计 按照规范设计可以将数据库的设计过程分为6个阶段:系统需求分析、概念结构设计、逻辑结构设计、物 理结构设计、数据库实施、数据库运行与维护。
需求分析:全面了解设计的存储需求,保证数据的安全性和完整性。里程碑是需求分析文档 概念设计:根据需求了解各个需要存储的实体以及实体之间的关系,里程碑是ER图 逻辑结构设计:设计数据的逻辑存储结构,根据数据实体之间的逻辑关系解决数据冗余和数据维护 异常,里程碑是模式定义 物理结构设计:根据具体所使用的数据库特点进行表结构设计,里程碑是建库建表的SQL语句 维护优化:根据实际情况,对索引、存储结构等相关方面进行优化 用户管理系统 需求:针对一个注册用户信息进行CRUD操作,需要记录存储用户的编号、用户名称、用户口令和注册 时间。
概念设计
E-R图就是实体关系图,其中主要包括实体E矩形;实体的描述信息【属性】,椭圆和不定向线连接实体;实体和实体之间存在一定的关联关系,可以使用菱形进行连接,允许给关系上添加额外属性。
实体之间的关系有3种:
一对一关系,1:1,例如一个人只能有一个身份证,一个身份证只能属于一个人 一对多关系,1:m或者m:1,例如一个商品只能属于一个类目,一个类目可以包含多个商品 多对多关系,n:m,例如一个学生可以选修多门课程,一个课程可以被多个学生选修 可以使用在线工具或者单机版软件进行ER图的绘制,例如processon在线工具或者单机版Sybase公司的
PowerDesigner【推荐】和Microsoft的visio,需要注意PowerDesigner绘制的不是标准ER图
逻辑结构设计
依据是经过技术评审后的ER图,目前基本使用的都是关系型数据库,所以都是采用关系模型。这里的具 体操作就是将ER图转换为关系模型。
将每个实体转换为一个关系,实体的属性就是关系的属性,实体的标识字就是关系的关键字【不是 主键,主键是数据库的概念】 模式定义:用户(用户编号,用户名称,用户口令、注册时间) 实体之间关系的转换 物理结构设计
使用范式NF减少数据冗余,如果不满足对应等级的范式进行分表处理生成SQL语句
create table if not exists tb_users( id bigint primary key auto_increment comment '用户编号', username varchar(20) not null unique comment '用户名称', -- unique唯一性约束,不允许重复 password varchar(20) not null comment '用户口令', create_time timestamp default current_timestamp -- 从MySQL5.6开始允许最多存储6位小数的秒,current_timestamp是系统函数,用于获取插入数据时的具体时间,没有设置,所以只 精确到秒 )engine=innodb default charset utf8;