Home Tags Posts tagged with "数据库"

数据库

0 76

MySQL 数据类型选择

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。


数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC),以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值,并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展,MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

img

无论在什么数据库中,数值和字符串这两种数据字段类型都用的最多,并且往往量级非常大,在这种背景下,选择的字段类型的优劣对性能影响非常大,因此我们有必要熟知上表中各种类型的使用及差别。

一、TINYINT

  1. 表示范围 :(-128,127) or (0,255)

  2. 占用字节:1字节

  3. 【强制】表达是与否概念的字段,必须使用is_xxx的方式命名,数据类型是unsigned tinyint

  4. 最小显示长度(TINYINT(1) 和 TINYINT(2)的区别)

    TINYINT(m)

 m表示的是显示数据宽度,不同中数据类型它的数据宽度也是有差别的。数据宽度和数据类型的取值范围彼此之间是相互独立的,没有任何联系。 bigint(m)默认宽度为20,如果创建时表中设置了zerofill关键字(默认是用0填充的)。zerofill含义是:往表中插入的数值比定义的长度小的时候,会在数 值前进行补值。 img

  1. TINYINT和ENUM的区别

  TINYINT(1)或ENUM(’真’,’假’)?
        用ENUM枚举当存储只有2个值时只占用一个位的宽度,0或1,但会花更多的时间去寻找枚举查询的开始。
        用TINYINT(1)默认就会占用4个位的宽度(0000)
        得出结论:比如要存储一个介于0-9之间的值,为了查询获取这个值,建议用TINYINT(1)会更快,但如果你是为了大量记录枚举(“真”,“假”),那么用ENUM( ‘true’ , ‘false’) 搜索会更快。一般的,咱们如果存的是纯数字的话,建议用tinyint,如果是字符串,且是固定长度的,建议用char,而enum的枚举字段,使用的使用需要慎重考虑,避免带来不必要的麻烦

       注:TINYINT(1)在使用Mybatis代码生成器时,会自动转为Boolean类型!!!

二、INT或INTEGER

  1. 表示范围 :(-2 147 483 648,2 147 483 647) or (0,4 294 967 295)

  2. 占用字节:4字节

  3. The keyword INT is a synonym for INTEGER

  4. 如果不需要存取负值,最好加上unsigned

三、BIGINT

1.Mysql里有个数据类型bigint在java转换成实体对象时,处理不当容易出现以下异常:

java.lang.ClassCastException: java.lang.Long cannot be cast to java.math.BigInteger

只需要注意以下情况,就可避免此类异常:当数据库中该属性添加unsigned,则在对象中对应的属性类型应该为BigInteger; ​ 当数据库中该属性未添加unsigned,则在对象中对应的属性类型应该为Long。可以成功映射为Long的表用的是BIGINT(20),但是出问题的表使用的是BIGINT(20) UNSIGNED。如果不是无符号类型,BIGINT(20)的取值范围为-9223372036854775808~9223372036854775807。与Java.lang.Long的取值范围完全一致,mybatis会将其映射为Long;而BIGINT(20) UNSIGNED的取值范围是0 ~ 18446744073709551615,其中一半的数据超出了Long的取值范围,Mybatis将其映射为BigInteger。mysql数据库字段bigint使用 – 一心二念 – 博客园 (cnblogs.com)

2.存储手机号码用bigint(11)还是varchar(11)

空间:BIGINT——8字节CHAR——11字节
效率:BIGINT效率更高
使用:32位某些应用中bigint太大会溢出,要进行各种转换,麻烦
结论:使用char(11)更好

3.用 BIGINT 做主键,而不是 INT;

在真实业务场景中,整型类型最常见的就是在业务中用来表示某件物品的数量。例如上述表的销售数量,或电商中的库存数量、购买次数等。在业务中,整型类型的另一个常见且重要的使用用法是作为表的主键,即用来唯一标识一行数据。

整型结合属性 auto_increment,可以实现自增功能,但在表结构设计时用自增做主键,希望你特别要注意以下两点,若不注意,可能会对业务造成灾难性的打击:

用 BIGINT 做主键,而不是 INT;自增值并不持久化,可能会有回溯现象(MySQL 8.0 版本前)。 从表 1 可以发现,INT 的范围最大在 42 亿的级别,在真实的互联网业务场景的应用中,很容易达到最大值。例如一些流水表、日志表,每天 1000W 数据量,420 天后,INT 类型的上限即可达到。可以看到,当达到 INT 上限后,再次进行自增插入时,会报重复错误,MySQL 数据库并不会自动将其重置为 1。

第二个特别要注意的问题是,MySQL 8.0 版本前,自增不持久化,自增值可能会存在回溯问题!(23条消息) MySQL-数字类型自增是真的坑_Five在努力的博客-CSDN博客

其实,在海量互联网架构设计过程中,为了之后更好的分布式架构扩展性,不建议使用整型类型做主键,更为推荐的是字符串类型。合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。

正例:人的年龄用unsigned tinyint(表示范围0-255,人的寿命不会超过255岁);海龟就必须是smallint,但如果是太阳的年龄,就必须是int;如果是所有恒星的年龄都加起来,那么就必须使用bigint。

四、FLOAT,DOUBLE,DECIMAL

  1. 表示范围 :较大

  2. 占用字节:4字节,8字节

  3. MySQL 中使用浮点数和定点数来表示小数。

浮点类型有两种,分别是单精度浮点数FLOAT)和双精度浮点数DOUBLE);定点类型只有一种,就是 DECIMAL。浮点类型和定点类型都可以用(M, D)来表示,其中M称为精度,表示总共的位数;D称为标度,表示小数的位数。浮点数类型的取值范围为 M(1~255)和 D(1~30,且不能大于 M-2),分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的,FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

  1. DOUBLE 实际上是以字符串的形式存放的(精准)

  2. 长度一定下,浮点数表示范围更大,缺点则是精度问题

在 MySQL 中,定点数以字符串形式存储,在对精度要求比较高的时候(如货币、科学数据),使用 DECIMAL 的类型比较好,另外两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点数时需要注意,并尽量避免做浮点数比较。

  1. 单精度float比双精度效率更高

  2. 浮点数判断相等不能用== 或者equals,应当使用

  3. 【强制】小数类型为decimal,禁止使用float和double。

说明:float和double在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不正确的结果。如果存储的数据范围超过decimal的范围,建议将数据拆成整数和小数分开存储。


日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值,当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性,将在后面描述。

img


字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。img

一、CHAR和VARCHAR

CHAR数据类型是MySQL中的固定长度的字符类型。我们经常声明CHAR类型的长度,指定我们要存储的最大字符数。例如,CHAR(20)最多可以容纳20个字符。

如果要存储的数据是固定大小,则应使用CHAR数据类型。与VARCHAR这种情况相比,您将获得更好的性能。

CHAR数据类型的长度可以是0到255之间的任何值。当您存储CHAR值时,MySQL会将其值用空格填充到您声明的长度。

当您查询CHAR值时,MySQL将删除尾随空格。

请注意,如果启用PAD_CHAR_TO_FULL_LENGTH SQL模式,MySQL将不会删除尾随空格。

区别:

  1. CHAR的长度是不可变的,而VARCHAR的长度是可变的

  2. CHAR的存取速度要比VARCHAR快得多

  3. 存储方式不同

varchar比char节省空间,但在效率上比char稍微差一些。varchar比char节省空间,是因为varchar是可变字符串,比如:用varchar(5)存储字符串“abc”,只占用3个字节的存储空间,而用char(5)存储,则占用5个字节(“abc ”)。varchar比char效率稍差,是因为,当修改varchar数据时,可能因为数据长度不同,导致数据迁移(即:多余I/O)。其中,oracle对此多余I/O描述的表达是:“行迁移”(Row Migration)。

  1. “行迁移”(Row Migration)

    *“当一行的记录初始插入时是可以存储在一个block中的,由于更新操作导致行增加了,而block的自由空间已经完全满了,这个时候就产生了行迁移。在这种情况下,oracle将会把整行数据迁移到一个新的block中,oracle会保留被迁移的行的原始指针指向新的存放行数据的block,这就意味着被迁移行的ROW ID是不会改变的。"*
    
      其中要解释一下:block是oracle中最小的数据组织与管理单位,是数据文件磁盘储存空间单位,也是**数据库I/O最小单位(****也就是说,读和写都是一个block的大小,所以如果block没满时,更新内容长度变更的varchar字段,和更新内容长度没变的varchar字段,I/O次数是一样,不存在额外消耗,只有在block满时,才会出现额外I/O,所以char和varchar性能之间的性能差异,是相当细微的,绝大多数情况下可以忽略不计,所以上文描述的“稍”差的含义)**。
    
      所以,我的开发经验是:“**用varchar代替char的效率有所下降,但不大**”。
  2. 占用空间不同

CHAR的存储方式是,一个英文字符(ASCII)占用1个字节,一个汉字占用两个字节;而VARCHAR的存储方式是,一个英文字符占用2个字节,一个汉字也占用2个字节。

varchar是可变长字符串,不预先分配存储空间(这里说的是存储时不预先分配存储空间,但是在查询需要创建临时表时,会按照设置的长度预先分配内存空间,因此这个设置长度时不可以随意,详情见下varchar的长度设计),长度不要超过5000,如果存储长度大于此值,定义字段类型为TEXT,独立出来一张表,用主键来对应,避免影响其它字段索引效率。

     6.VARCHAR类型的实际长度是它的值的实际长度+1。为什么”+1″呢?

这一个字节用于保存实际使用了多大的长度。


二、MySQL中的varchar长度设计

 之前看到一篇博文,讲的是在定义varchar(n)时,需要用多大就设置多大,而我当时在分析时,对数据量估算错误了,设置了varchar(5000),被leader和导师批评了一番,因此特意学习varchar的长度设计:
尽管varchar(n)是存储可变长字符串的,但是其n也不是越大越好,理论如下
当MySql在查询需要创建临时表的时候(union,order by、group by,子查询),在MySql读取数据之前,是只知道varchar的长度n,不知道实际数据的长度的,但是读取数据之前需要预分配内存空间,MySql是根据varchar(n)中的n来进行分配内存的,这样也是最合理的方式,不可能分配小于n个字符的空间,因此针对varchar(1000)设置就会预先分配1000个字符空间,很显然这个是十分不靠谱的设计。

三、varchar(50)和varchar(255)有性能上的差别么?

对于INNODB,varchar(50)varchar(255)这两者在存放方式上完全一样:1-2 byte保存长度,实际的字符串存放在另外的位置,每个字符1 byte到4 byte不定(视编码和实际存储的字符而定)。所以将一个字段从varchar(50)长度改成varchar(100)长度不会导致表的重建。但如果把长度从varchar(50)改成varchar(256)就不一样了,表示长度会需要用到2 byte或更多。既然255长度以下对INNODB都一样,而且我们平时基本上也不太会使用到MYISAM,那么是不是为了省心,我们就可以把255长度以下的字段的类型都设置成varchar(255)了呢?
非也。
因为内存表(临时表)介意。
虽然我们不会明文创建内存表,但所有的中间结果都会被数据库引擎存放在内存表(MySQL在有些查询情况下需要创建内存表)。我们可以通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内存表用来帮助完成某个操作。
而内存表会按照定义的varchar(n)的n来分配内存。以utf-8编码为例,对于varchar(255),每一行所占用的内存就是长度的2 byte + 3 * 255 byte。对于100条数据,光一个varchar字段就占约1GB内存。如果我们该用varchar(50),就可以剩下来约80%的内存空间。
除此之外,255长度也可能会对索引造成坑。MySQL在5.6版本及之前的最大长度是767 byte。但MySQL 5.5版本后开始支持4个byte的字符集utf8mb4(沙雕表情用到的字符太多,长度不够用)。255 * 4 > 767,所以索引就放不下varchar(255)长度的字段了。虽然MySQL在5.7版本后将限制改成了3072 byte,但如果是多字段的联合索引还是有可能会超过这个限制

所以我们的结论就是:在长度够用的情况下,越短越好。

四、varchar(255)和varchar(256)的区别

在varchar长度接近256时,varchar长度设置成255的好处:

1、方便InnoDB建索引,对于 MyISAM,可以对前 1000 个字节做索引,对于 InnoDB,则只有 767 字节。(来源依据)。255X3=765

2、少申请一个字节,记录字符创长度,一个8位的tinyint,可以表示的无符号数值的范围是,0-255,如果长度超过了255,需要在申请个字节

五、varchar(n)长度定义知识总结

1.在长度够用的情况下,越短越好(因为长了会导致查询生成临时表时降低性能且占用内存)。

2.在varchar长度接近256时,varchar长度设置成255更好

六、参考文章

0 38

今天在分析需求的时候,rock哥突然叫我过去,跟我讨论一个需求的建表方案。

 

我和rock负责的这个需求是对一个运营人员(BD)所拥有的门店数据的统计展示。首先我们设计了权限管理,每个BD拥有查看对应门店统计数据的权限,对应到库表是:t_BDRelatedShop_info

即 运营人员的ID和其所拥有权限的门店ID相对应;而另一张表,则是 门店统计数据表:t_shopStatistics_info

按道理来说,要展示一个BD所拥有权限的门店的统计数据,仅仅涉及到三个部分,即 Uid,ShopId,ShopStatisticsInfo,我们完全可以把这三个部分整合到一张表里,这样进行单表查询,效率最高,结构最简单,那为什么还要分两张表呢?

Rock哥告诉我,分表主要有两方面的考虑

架构层面:前文提到 t_BDRelatedShop_info 这个表是用来做权限管理的,也就是说,这个表应该是独立的;如果把 Uid,ShopId,ShopStatisticsInfo 这三部分信息冗余到一张表里,那么是                         不利于系统维护和调整的;

业务层面:1.我们现有的权限审批系统采用的是 T+1 模式,简单来说就是 如果当前BD想要修改权限,比如新增一个可以查看的门店,这个BD提交的权限申请要等到第二天才能生效;

                     2.我们现有的业务表和数据表是分开存放的,出于数据量和安全考虑

        分表之后我们需要先从t_BDRelatedShop_info表中查出BD所拥有的门店统计数据权限的ShopIdList,然后通过ShopIdList再去t_shopStatistics_info表中查出每一个门店所对应的统计信              息,组成列表ShopStatisticsList返回;很显然,分两张表虽然可以解决上述问题,但是在性能上是有损耗的,因为要进行两次查表。

 

联表查询

这个时候有朋友会说,你傻啊,你不会做联表查询吗?

是的,我考虑过,可是Rock哥告诉我,如果建两张表,那么这两张表并不在一个库之中,MySQL的跨库Join查询做的并不怎么好,尤其是一旦BD和门店的数量一上去,Join查询的性能就更加糟糕;

 

双写实现

既然MySQL的跨库Join查询效率不高,那么我们将其改成MySQL的同库Join不就好了吗?

t_BDRelatedShop_info 权限管理表的数量级并不算大,我们可以采用双写的方式实现

即 在t_BDRelatedShop_info表所在的库中也建一个t_shopStatistics_infoB表,作为t_shopStatistics_infoA表的从表,每一次对t_shopStatistics_infoA表的操作都同步到t_shopStatistics_infoB表中,这样可以保证实时同步BD的权限;

BD申请修改权限——>t_shopStatistics_infoA表修改——>同步到t_shopStatistics_infoB表

BD申请查看门店数据统计——>t_shopStatistics_infoB表Join t_shopStatistics_info表

 

至此,该问题暂时被解决了,而这个问题让我了解了很多新东西

  1. 跨库查询和分库分表 分库分表的几种常见玩法及如何解决跨库查询等问题-阿里云开发者社区 (aliyun.com)
  2. 单个数据库实例所能承载的数据量
  3. 联表查询

基于内存保存消息

最近开发基于Bio的Socket项目的时候,想把简单的聊天室往消息队列的方向靠拢,因此在考虑怎么记录每个客户端所发送的消息(即实现聊天记录的保存功能)

客户端发送消息— 服务器端接收 转发 存储—-目标客户端

服务器接收转发我使用的是Read线程转发即可,那么存储呢?一开始的想法是,要不存储在内存中,即创建HashMap<senderName,msgList>来保存

具体如下:

<橙汁,List<“消息一”,“消息二”…>>

<小阮,List<“消息一”,”消息二”…>>

代码如下:

这样的实现好处在于:简单,明了。客户端将消息发送给服务器,服务器做转发,同时存入msgMemoryMap(全局变量);由于我的网络通信模型是使用的Bio,服务器循环监听,每来一个客户端就创建一个对应的读线程,而这些步骤也是在读线程里面完成的

也就是说 客户端A的消息存入 和客户端B的消息存入不是同一个线程实现的(当然已经使用了线程池进行优化,此处是有隐患的,如果客户端太多那么服务器就要创建很多线程),因为效率上不用太过担心

更高效的序列化

在存入msgMemoryMap之前,我将原来的Serialize序列化方式改为了Protostuff序列化,因为存入消息实在太占用内存了…

如果使用Serialize序列化,平均每条消息要占用300个字节(300B)这还得期望聊天用户发送的消息都是短消息,如果长消息那更恐怖,我计算了一下,如果同时有1000个用户在聊天,每人发送1条,也就是1*1000*300 = 300Kb!这个内存占用实在是太可怕了,因此我不得不考虑更高效的序列化方式,即Protostuff,关于Protostuff的文章会在之后写出,目前只需要知道它的序列化更高效且生成的byte数组大小更小,差不多是Serialize生成的十分之一,那么接下来直接看测试代码:

 

这样仿佛解决了写入的消息占用内存过大的问题?其实还可以进一步优化,比如再优化MessageRedis类的字段,或者先压缩再存入msgMemoryMap,等需要拿出来使用的时候再解压,也就是“时间换空间”的想法,听上去好像使用msgMemoryMap只要解决了内存占用问题就好了,其实并不然,因为内存具有掉电即失的特性。

任何实际开发的项目都不可能将数据简单的写在内存,必须要进行持久化,不然你的用户使用你的聊天室向其他人发送重要的资料和文件,等到后面他需要取查看这些消息的时候,却发现居然全丢了。持久化,欸,会做啊,我写入到数据库去不就不会丢失了嘛。

写入数据库有两种策略:

1.每来一条消息,服务器做转发后,将其写入到数据库;

2.每来一条消息,服务器将其写入到数据库后,再做转发;

我们来分析一下这两种策略,假设是 用户 橙汁 发送给 小阮的一条消息 ;

          策略1 :

           服务器先转发,那挺好,小阮可以立刻收到橙汁所发送的消息,然后橙汁的这条消息写入数据库,完美保存;橙汁第二条消息来的时候重复这个逻辑,因为橙汁发送消息中间是有间隔的,也就是不可能一直不停的发(假设消息有意义),那么这个间隔时间足够橙汁将第一条消息写入数据库了,到此,消息既转发了又保存了,服务器完成一次操作的时间为(T转发 + T存入)。

           似乎万事大吉?其实不然,现在都追求高可用,高并发,高可靠,我们的系统也不能落下。很显然目前的策略没有满足高并发和高可用,因为如果在服务器收到消息并转发后,断电了怎么办?消息并没有被写入数据库,如果小阮收到了消息,比如是 “明天一起去吃饭吧” 然后橙汁把这事忘了,第二天小阮来算账,说 “你昨天说的今天一起吃饭啊”;橙汁说:“噢 是吗? 我看看聊天记录”,结果消息记录居然真的没有!橙汁确实说了这句话,也就是说我们的系统会出现很多很多比这更复杂的问题,那么怎么解决呢?

                 策略2:

                  服务器先写入,再转发,如果写入失败则重试(或者其他策略),直到成功后再转发;这样的话,如果小阮收到了这条消息,一定和数据库中 的是一样的;同样的,如果写入数据库之后断电了,消息没被转发怎么办?只需要标记消息是否转发成功,如果没有的话就重新转发,可以从数据库去获取,不怕数据丢了,当然这里设计还可以详细展开。

分析了两种策略,如果是更追求消息一致性的话,优先选择策略2,当然策略2只是一个简版(悄咪咪的告诉你,这两个策略借鉴了Redis和Mysql的设计思路)

在上述策略的基础上继续思考,写入数据库是可以实现,但是不可忽略数据库的压力

数据库OS:对啊,你想的倒是好,每个线程都写数据库,如果采用策略2还需要从我身上拉取信息,线程压力小了,我数据库的压力大了喂,快找点人替我分担

 

是的,为了缓解数据库的压力,同时又可以完成我们的持久化功能,同时操作起来还快

缓存中间件:你他妈直接报我身份证得了

 

因此,我们可以采用Redis 来实现我们的需求—在转发消息的同时持久化保存消息

一样的:

1.每来一条消息,服务器做转发后,将其写入到Redis;

2.每来一条消息,服务器将其写入到Redis后,再做转发;

            策略1:服务器转发后,写入到Redis,Redis操作起来更快,对其上述写入数据库的策略1来说,就是T保存(保存需要的时间)更短了。如果转发完成后,断电了,没有写入到Redis,这里也会有问题,先按下不表

            策略2:先写入Redis,再转发,因为T保存的时间更短,写入Redis后转发失败的可能性就更小了,同样的,可以在接收消息的客户端设置一个接收反馈,接收到了反馈,没接收到的话这样服务器也知道,可以重发;

Redis的持久化功能保证了即使Redis所在的服务器机器掉电了,也不会丢失太多数据,丢失的数量取决了我们所采用的Redis持久化策略,比如是使用AOF还是使用RDB,其参数设置,以及是否做了Redis集群等等。

好啦,先介绍到这里啦~

0 100

Structured Query Language 结构化查询语言

为啥不叫非结构化查询语言呢?NoSQL

为啥不叫结构化操作语言呢?Operate

为啥不叫结构化查询操作呢?

因为这个是针对结构化数据的,查询

 

那么什么是结构化数据呢?一摞书,一摞笔记本,一捆钢笔。这是半理想状态。

书随意摆放,笔记本随意摆放,钢笔夹在书里,笔记本里,放在笔筒里,这是最不理想的状态

一摞按照书名首字母排序摆放在书架的书,一摞按照科目(语文、英语、化学…)顺序摆放的笔记本,一捆按照品牌顺序摆放的钢笔。这是最理想的状态。显然这样的状态是结构化,有规律,按照一定规则安排、处理的。(最直观的是图书馆及图书管理学)如果不是实际物品,而是抽象的概念,那么这个结构化就是现在的关系型数据库(DataBase)

查询结构化数据的语言就是结构化语言。查询化学这个笔记本,只需要定位到笔记本存放的位置(笔记本统一放置在一起,一个地方,甚至是一摞这种状态),第三个就是了。

而select * from notebook where notebook_name =“化学”就是比较合理的查询语句,翻译过来就是从笔记本中找出名字是化学的那个东西(*不管是什么,都直接拿过来给我就可以)

继而形成了SQL。

作者:LazyYoun
链接:https://www.zhihu.com/question/349924681/answer/851943256

一、什么是JDBC?

通俗点讲,J 就是java,DB就是database,C 就是connectivity,中文意思就是java数据库连接,简单点说吧,就是通过java语言去操作数据库。原来我们操作数据库是在控制台使用sql语句来操作数据库,JDBC使用java语言向数据库发送sql语句。

二、JDBC的原理

以前有一个叫SUN的公司,他们公司的数据库工程师想开发一个操作全世界数据库的接口和规范的实现,一开工结果有点难度啊,原因是个大数据库服务器差异性太大了,于是SUN公司的负责人把这些数据库工程师叫到一起开了一个会,讨论的结果是我们不去做具体的实现了,我们定义一个接口规范,让个大数据库厂商去实现,于是各大数据库厂商按照SUN公司的规范提供了一套操作自己数据库的API,也就是JDBC接口的驱动实现类。

注:JDBC是定义的接口规范,JDBC接口的驱动实现类是由各大厂商自己提供的

三、JDBC详解

JDBC的全称是Java数据库连接(Java Database connect),它是一套用于执行SQL语句的Java API。应用程序可通过这套API连接到关系数据库,并使用SQL语句来完成对数据库中数据的查询、更新和删除等操作。应用程序使用JDBC访问数据库的方式如下图所示。

从上图可以看出,应用程序使用JDBC访问特定的数据库时,需要与不同的数据库驱动进行连接。由于不同数据库厂商提供的数据库驱动不同,因此,为了使应用程序与数据库真正建立连接,JDBC不仅需要提供访问数据库的API,还需要封装与各种数据库服务器通信的细节。为了帮助大家更好地理解应用程序如何通过JDBC访问数据库,下面通过一张图来描述JDBC的具体实现细节,如下图

preview

从上图中可以看出,JDBC的实现包括三部分。

(1)JDBC驱动管理器:负责注册特定的JDBC驱动器,主要通过java.sql. Driver Manager类实现。

(2)JDBC驱动器API由Sun公司负责制定,其中最主要的接口是java.sql. Driver接口。

(3)JDBC驱动器:它是一种数据库驱动,由数据库厂商创建,也称为JDBC驱动程序JDBC驱动器实现了JDBC驱动器API,负责与特定的数据库连接,以及处理通信细节。

2、JDBC常用API

在开发JDBC程序前,首先了解一下JDBC常用的API。JDBC API主要位于java.sql包中,该包定义了一系列访问数据库的接口和类,具体如下。

1. Driver接口

Driver接口是所有JDBC驱动程序必须实现的接口,该接口专门提供给数据库厂商使用。在编写JDBC程序时,必须要把指定数据库驱动程序或类库加载到项目的classpath中。
2. DriverManager类

Driver Manager类用于加载JDBC驱动并且创建与数据库的连接。在Driver Manager类中,定义了两个比较重要的静态方法。如表所示:

registerDriver(Driver driver) 

该方法用于向 DriverManager中注册给定的JDBC驱动程程序

getConnection(String url,String user,String pwd)

该方法用于建立和数据库的连接,并返回表示连接的 Connection对象

3、Connection接口

Connection接口代表Java程序和数据库的连接,在Connection接口中,定义了一系列方法,具体如表所示。

getMetaData()
该方法用于返回表示数据库的元数据的 DatabaseMetaData对象

createStatement()
用于创建一个Statement对象来将SQL语句发送到数据库

prepareStatement(String sql)
用于创建一个PreparedStatement对象来将参数化的SQL语句发送到数据库

prepareCall(String sql)
用于创建一个CallableStatement对象来调用数据库存储过程

4、Statement接口

Statement接口用于向数据库发送SQL语句,在Statement接口中,提供了三个执行SQL语句的方法,具体如表所示。

execute(String sql)

用于执行各种SQL语句,该方法返回一个boolean类型的值,如果为true,表示所执行的SQL语句具备查询结果,可通过Statement的getResultSet方法获得查询结果。

executeUpdate(String sql)

用于执行SQL中的Insert、update和delete语句。该方法返回一个int类型的值,表示数据库中受该SQL语句影响的记录的数目。

executeQuery(String sql)

用于执行SQL中的select语句,该方法返回一个表示查询结果的ResultSet对象

5. PreparedStatement接口

PreparedStatement是Statement的子接口,用于执行预编译的SQL语句。在PreparedStatement接口中,提供了一些基本操作的方法,具体如表下所示。

executeUpdate()

在此PreparedStatement对象中执行SQL语句,该语句必须是个DML语句或者是无返回内容的SQL语句,比如DDL语句。

executeQuery()

在此PreparedStatement对象中执行SQL查询,该方法返回的ResultSet对象

setInt(int parameterIndex, int x)

将指定参数设置为给定的int值

setFloat(int parameterIndex, float x)

指定参数设置为给定的float值

setString(int parameterIndex, String x)

将指定参数设置为给定的String值

setDate(int parameterIndex, Date x)

将指定参数设置为给定的Date值

addBatch()

将一组参数添加到此PreparedStatement对象的批处理命令中

setCharacterStream(parameterIndex, reader, length)

将指定的输入流写入数据库的文本字段

setBinaryStream(parameterIndex, x, length)

将二进制的输入流数据写入到二进制字段中

需要注意的是,上表中的setDate()方法可以设置日期内容,但参数Date的类型是java.sq.Date,而不是java.util.Date。

6、CallableStatement接口

CallableStatement是PreparedStatement的子接口,用于执行SQL存储过程。在Callablestatement按接口中,提供了一些基本操作的方法,具体下表所示:

registerOutParameter(int parameterIndex,int sqlType)

按顺序位置将OUT参数注册为SQL类型。其中,parameterIndex表示顺序位置,sqlType表示SQL类型

setNull(String parameter Name, int sqlType)

将指定参数设置为SQL类型的NULL

setString(String parameterName, String x)

查询最后一个读取的OUT参数是否为SQL类型的NULL

wasNull()

查询最后一个读取的OUT参数是否为SQL类型的NULL

getlnt(int parameterIndex)

以Java语言中int值的形式获取指定的数据库中INTEGER类型参数的值

需要注意的是,由于 CallableStatement接口继承PreparedStatement,PreparedStatement接口又继承了 Statement,因此CallableStatement接口中除了拥有自己特有的方法,也同时拥有了这两个父接口中的方法。

7、ResultSet接口

ResultSet接口表示 select查询语句得到的结果集,该结果集封装在一个逻辑表格中。在 ResultSet接口内部有一个指向表格数据行的游标,ResultSet对象初始化时,游标在表格的第一行之前。下表中列举了ResultSet接口中的常用方法。

getString(int columnIndex)

用于获取指定字段的String类型的值,参数columnIndex代表字段的索引
getString(String columnName)

用于获取指定字段的String类型的值,参数column Name代表字段的名称
getInt(int columnIndex)

用于获取指定字段的int类型的值,参数columnIndex代表字段的索引

getInt(String columnName)

用于获取指定字段的int类型的值,参数columnName代表字段的名称

getDate(int columnIndex)

用于获取指定字段的Date类型的值,参数columnIndex代表字段的索引
getDate(String columnName)

用于获取指定字段的Date类型的值,参数column Name代表字段的名称
next()

将游标从当前位置向下移一行
absolute(int row)

将游标移动到此Resultset对象的指定行
afterLast()

将游标移动到此ResultSet对象的末尾,即最后一行之后
beforeFirst()

将游标移动到此Resultset对象的开头,即第一行之前
previous()
将游标移动到此ResultSet对象的上一行
last()

将游标移动到此ResultSet对象的最

从上表中可以看出,ResultSet接口中定义了大量的getXxx()方法,采用哪种getXxx()方法取决于字段的数据类型。程序既可以通过字段的名称来获取指定数据,也可以通过字段的索引来获取指定的数据,字段的索引是从1开始编号的。

四、JDBC实现步骤

DBC编程大致按照以下几个步骤进行。

(1) 加载并注册数据库驱动,具体方式如下。

DriverManager.registerDriver(Driver driver);

(2) 通过Driver Manager获取数据库连接,具体方式如下。

Connection conn= DriverManager.getConnection(String url, String user, String pass);
从上述方式可以看出,getConnection()方法中有三个参数,它们分别表示数据库url、登录数据库的用户名和密码。数据库山通常遵循如下形式的写法。

jdbc:subprotocol:subname

上面的URL写法中jdbc部分是固定的,subprotocol指定链接达到特定数据库的驱动程序,而subname部分则很不固定,也没有什么规律,不同数据库的形式可能存在较大差异,一Mysql数据库为例,其形式如下:

jdbc:mysql://hostname:port/databasename

(3)通过Connection对象获取Statement对象。Connection创建Statement的方式有如下三种。

① createStatement(): 创建基本的Statement对象

② prepareStatement(): 创建PreparedStatement对象。

③ preparCall(): 创建CallableStatement对象。

以创建基本的Statement对象为例,具体方式如下。

Statement stmt=conn.createStatement();

(4)使用Statement执行SQL语句。所有的Statement都有如下三种方法来执行语句。

①execute():可以执行任何SQL语句。

②executeQuery():通常执行查询语句,执行后返回代表结果集的Resultset对象。

③executeUpdate():主要用于执行DML和DDL语句。执行DML语句,如INSERT、UPDATE或 DELETE时,返回受SQL语句影响的行数,执行DDL语句返回0。

以executeQuer()方法为例,具体方式如下。

//执行SQL语句,获取结果集ResulSet
ResultSet rs=stmt.executQuery(sql);

(5)操作ResultSet结果集。如果执行的SQL语句是查询语句,执行结果将返回Resultset对象,该对象里保存了SQL语句查询的结果。程序可以通过操作该ResultSet对象来取出查询结果。 ResultSet对象提供的方法主要可以分为以下两类。

①next()、previous()、first()、last()、beforeFirst()、afterLast()、absolute()等移动记录指针的方法

②getXxx()获取指针指向行,特定列的值。

(6)回收数据库资源。关闭数据库连接,释放资源,包括关闭ResultSet、Statement和Connection等资源。

五、JDBC实现案例

1、搭建实验环境

CREATE DATABASE chapter01;
USE chapter01;
CREATE TABLE users(
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(40),
      password VARCHAR(40),
      email VARCHAR(60),
      birthday DATE
)CHARACTER SET utf8 COOLLATE utf8_genneral_ci;

数据库和表创建成功后,再向users表中插入三条数据,SQL语句如下所示。

INSERT INTO users(NAME,PASSWORD,email,birthday)
VALUES('zs','123456','zs@sina.com','1980-12-04');
INSERT INTO users(NAME,PASSWORD,email,birthday)
VALUES('lisi',123456,1isi@sina.com,'1981-12-04');
INSERT INTO users(NAME,PASSWORD,email,birthday)
VALUES('wangwu',123456,'wangwu@sina.com','1979-12-04');

2、导入数据库驱动

新建Java工程chapter01,将要访问的数据库驱动文件添加到classpath中。由于应用程序访问的是MySQL数据库,因此,将MySQL的数据库驱动文件mysql-connector-java-5.0.8-bin.jar添加到classpath中即可。

3、编写JDBC程序

在工程chapter01中,新建Java类Example01,该类用于读取数据库中的users表,并将结果输出,如例下面案例所示。

package cn.itcast.jdbc.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Date;
public class Example01 {
    public static void main(String[] args) throws SQLException {
        //1.注册数据库的驱动
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //2.通过 DriverManager获取数据库连接
        String url="jdbc:mysql://localhost:3306/chapter01";
        String usernames="root";
        String password="itcast";
        Connection conn=DriverManager.getConnection(url, username, password);
        //3.通过 Connection对象获取 Statement对象
        Statement stmt= conn.createStatement();
        //4.使用 Statement执行SQL语句
        String sql="select * from users";
        ResultSet rs=stmt.executeQuery(sql);
        //5、操作 ResultSet结果集
        System.out.println("id|name|password|email|birthday");
        while (rs.next()) {
            int id=rs.getInt("id");     //通过列名获取指定字段的值
            String name=rs.getString("name");
            String psw=rs.getString("password");
            String email=rs.getString("email");
            Date birthday=rs.getDate("birthday");
            System.out.println(id+"|"+name+"|"+psw+"|"+email+"|"+birthday); 
        }
        //6.回收数据库
        rs.close();
        stmt.close();
        conn.close();
    }
}

程序执行后,会讲从users表中读取到的数据打印到控制台。

在上面案例中演示了JDBC访问数据库的步骤。首先注册MySQL的数据库驱动器类,通过 DriverManager获取一个Connection对象,然后使用Connection对象创建了一个Statement对象,Statement对象能够通过executeQuery()方法执行SQL语句,并返回结果集ResultSet对象。最后,通过遍历Resultset对象便可得到最终的查询结果。需要注意的是,在实现第一个JDBC程序时,还有两个方面需要改进,具体如下。

六、加餐

1、注册驱动

在注册数据库驱动时,虽然DriverManager.registerDriver(new com. mysql.jdbc.Driver())方法可以完成,但会使数据库驱动被注册两次。这是因为Driver类的源码中,已经在静态代码块中完成了数据库驱动的注册。所以,为了避免数据库驱动被重复注册,只需要在程序中加载驱动类即可,具体加载方式如下所示。

Class.forName("com.mysqk.jdbc.Driver");

Class.forName() 方法

此方法含义是:加载参数指定的类,并且初始化它。

Java class.forname 详解 | 菜鸟教程 (runoob.com)

2、释放资源

由于数据库资源非常宝贵,数据库允许的并发访问连接数量有限,因此,当数据库资源使用完毕后,一定要记得释放资源。为了保证资源的释放,在Java程序中,应该将最终必须要执行的操作放在finally代码块中,具体方式如下。

if(rs!=null) {
    try {
        rs.close();
    }catch (SQLException e) {
        e.printStackTrace();
    }
    rs=null;
}
if(stmt!=null) {
    try {
        stmt.close();
    }catch (SQLException e) {
        e.printStackTrace();
    }
    stmt=null;
}
if(conn!=null) {
    try {
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    conn=null;
}

文章转载链接:什么是JDBC,JDBC的原理是什么 - 简书 (jianshu.com)
什么是JDBC?这篇文章告诉你 - 知乎 (zhihu.com)