JDBC 基本操作

1. 简介
  • JDBC(Java DataBase Connectivity) 是有一些接口和类构成的API
  • JDBC是J2SE的一部分, 又java.sql; javax.sql包组成。
 
JDBC 基本操作
  • 应用程序层 是有我们开发人员开发的代码
  • JDBC层 - 主要是一些接口, 定义了一个规则, 定义了一些方法, 没有实现。
  • Driver - Driver就是各个产商实现JDBC接口所以开发的jar包。 Driver一般是由各个数据库产商开发的。
 
 
三层架构:
JDBC 基本操作
 
三层架构:表示层-业务逻辑层-数据访问层 
由于层是一种弱耦合结构,层与层之间的依赖是向下的,底层对于上层而言是“无知”的,改变上层的设计对于其调用的底层而言没有任何影响。如果在分层设计时,遵循了面向接口设计的思想,那么这种向下的依赖也应该是一种弱依赖关系。因而在不改变接口定义的前提下,理想的分层式架构,应该是一个支持可抽取、可替换的“抽屉”式架构。正因为如此,业务逻辑层的设计对于一个支持可扩展的架构尤为关键,因为它扮演了两个不同的角色。对于数据访问层而言,它是调用者;对于表示层而言,它却是被调用者。依赖与被依赖的关系都纠结在业务逻辑层上,如何实现依赖关系的解耦,则是除了实现业务逻辑之外留给设计师的任务。 
 
三层架构(3-tier application) 通常意义上的三层架构就是将整个业务应用划分为:表现层(UI)、业务逻辑层(BLL)、数据访问层(DAL)。区分层次的目的即为了“高内聚,低耦合”的思想。
 
  1、表现层(UI):通俗讲就是展现给用户的界面,即用户在使用一个系统的时候他的所见所得。(负责展示而已)
  2、业务逻辑层(BLL):针对具体问题的操作,也可以说是对数据层的操作,对数据业务逻辑处理。(关键在于由原始数据抽象出逻辑数据)能够提供interface\API层次上所有的功能。,“中间业务层”的实际目的是将“数据访问层”的最基础的存储逻辑组合起来,形成一种业务规则
  3、数据访问层(DAL):该层所做事务直接操作数据库,针对数据的增添、删除、修改、查找等。(关键在于粒度的把握)要保证“数据访问层”的中的函数功能的原子性!即最小性和不可再分。“数据访问层”只管负责存储或读取数据就可以了。
 
 
2. 链接数据的步骤:
  • 注册驱动(只做一次)
  • 建立连接(Connection)
  • 创建执行SQL语句(Statement, PreStatement)
  • 执行语句
  • 处理执行结果(ResultSet)
  • 释放资源
 
先把数据库驱动加载到环境变量中
2.1 注册驱动(只做一次)

一、DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

 jdbc是使用桥的模式进行连接的 在编译时需要导入对应的lib
 DriverManager就是管理数据库驱动的一个类,java.sql.Driver就是一个提供注册数据库驱动的接口,而com.microsoft.sqlserver.jdbc.SQLServerDriver()是java.sql.Driver接口的一个具体实现。

二、System.setProperty("jdbc.drivers", "com.microsoft.sqlserver.jdbc.SQLServerDriver");

 通过系统的属性设置注册驱动 如果要注册多个驱动, 则把驱动用冒号分隔,在连接时JDBC会按顺序搜索,直到找到第一个能成功连接指定URL的驱动程序。
  System.setProperty("jdbc.drivers","com.mysql.jdbc.Driver:com.oracle.jdbc.Driver"); 
 
为什么直接设置一个系统属性就可以了呢?
这是因为在DriverManager内的内部会有一个方法去获取这个值, 如果有 那么就使用这种方法, 如果没有就继续使用其他的方法 比如第一种 或者第三种。 
 
三、Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");   推荐使用这种方式
    这句代码意思是根据所提供类的名字把这个类装载到虚拟机里。 它还 new是不一样的, 第一种方式是new 会创建一个driver对象。 而这种方式只是把类装载到虚拟机并没有创建对象,是否创建何时创建要看后续代码。 
     第一种与第二种注册的方法看起来更加的直接与好理解。第三种方法是通过Class把类先装载到java的虚拟机中,并没有创建Driver类的实例。
     第二种与第三种方法可以脱离jdbc的驱动进行编译,第一种方法不可以的,它一定要有jdbc的驱动才可以通过编译,这样对我们的程序就有很多的不好之处,为程序换数据库会带来麻烦。
       我们通过Driver类的源码可以了解到,Driver类中就有一个静态的代码块,只要我们执行了Driver类中的静态代码块,并把驱动的实例放入到Drivers的一个数组列表中,我们再调用方法registerDrever就相当于又向drivers列表中放了一次driver驱动,虽然这并不影响我们程序,但是这样做实在是没有必要,还会影响程序的运行。
所以推荐使用第三种方法来注册驱动。
 第三中的好处在于能够在编译时不依赖于特定的JDBC Driver库,也就是减少了项目代码的依赖性,而且也很容易改造成从配置文件读取JDBC配置,从而可以在运行时动态更换数据库连接驱动。
 
2.2 建立链接(Connection) 
//创建连接
  String url = "jdbc:mysql://localhost:3306/jdbc";
  String user = "root";
  String password = "";
  Connection conn = DriverManager.getConnection(url, user, password);
JDBC连接字符URL格式:
 JDBC:子协议:子名称//主机名:端口/数据库名称?属性名=属性值&属性名2=属性值2&...
 如:

mysql的url: jdbc:mysql://localhost:3306/jdbc  (没有子协议)

 如果是连接到本机,且端口是默认, 那主机名和端口可以省略
  jdbc:mysql:///jdbc
User, password可以使用属性名=属性值方法告知数据库
其他参数: 

useUnicode=true&characterEncoding=GBK

 
建立连接的过程要尽量晚。
2.3 创建执行SQL的语句(Statement)
   那怎么发送sql语句, 怎么获取执行结果呢? 就是通过Statement实现 创建执行语句, 就相当于创建一个车去运送货物               

//创建SQL语句

        Statement st = conn.createStatement();
执行语句

//执行SQL语句并接受返回的数据 返回的数据是一种类似二维数组的结果

String exeSql = "SELECT CAPEX_MTRC, CAPEX_REGN FROM USER";

//建议sql不要选取表中所有的行, 只需选中需要的行就可以了

ResultSet rs = st.executeQuery(exeSql);

2.4 处理执行结果(ResultSet)

//处理执行结果
  while(rs.next()){
   System.out.println(rs.getString(CAPEX_MTRC) + "\t" + rs.getString(CAPEX_REGN ) + "\r" + 
     rs.getObject(3));
  }
rs.next() 会定位到当前的一行数据, 然后通过getObject(index)来获取指定列的值。 rs.next()每次都会移到的下一行数据
 
释放资源 执行语句会暂用系统大量资源, 所以最好在使用完后释放资源。
     //释放资源  注意释放的顺序
  rs.close();
  st.close();
  conn.close();
数据库连接Connection是非常稀缺的资源, 用完后必须马上释放, 如果Connection不能及时正确的关闭将导致系统宕机。 Connection的使用原则是尽量晚创建, 尽量早释放。 
 
对数据库进行CRUD操作

Create/update/delete 要使用Statement的executeUpdate(sql)方法 , 它返回的是被影响的行数。

 
增, 删, 改是用Statement.executeUpdate() 完成, 返回时int类型, 被影响的行数
查询用Statement.executeQuery()完成, 返回时ResultSet对象。
 
SQL注入, PreparedStatement, Statement
        在sql中包含特殊字符或sql关键字(如‘or 1 or ')时Statement将出现不可预料的结果(出现异常或查询结果不正确), 这是因为or在判断条件中 是或的关系 会导致你的整个判断是true 因为 1. 所以所有的结果都会返回  。 
可用Preparement来解决
PreparedStatement是继承于Statement. 相对于Statement的优点:
  • 没有sql注入问题
  • Statement会是数据库频繁编译SQL, 可能造成数据库缓冲区溢出。
  • 数据库和驱动可以对PreparedStatement进行优化(只有在相关联的数据库连接没有关闭的情况下有效)
注意:
rs = ps.executeQuery(); 
调用PreparedStatement executeQuery()方法的时候不要加参数, 因为PreparedStatement的对无参的这个方法有优化, 是调用它自己的方法。 如果加参数就相当于是调用父类的方法, 而父类的方法并没有任何的改动。
   String sql = "select CAPEX_MTRC, CAPEX_REGN from ESR.CAPEX where CAPEX_REGN = ?";
   ps = conn.prepareStatement(sql);
   ps.setString(1, CAPEX_REGN); //参数是String方法
   ps.executeUpdate(); // 对于update, delete insert
   ps.executeQuery(); // 对于查询
 
开发 用PreparedStatement。只要带有参数就用PreparedStatement
自己玩 且 查询只有没有任何条件的, 或者条件是固定的 可以用statement
 
注意:
    1.  传递的sql 语句, 不要使用* 去操作所有的数据, 需要那列的数据就操作那几列 

推荐使用列名: select BUD_FISC_YR_NM, BUD_PER_NM, BUD_YR_MTH_NR, ESDW_CRT_TS from CORE.BUD_RT;

不推荐使用*: select * from CORE.BUD_RT;

    2. 在获取的数据的时候一般不要使用getObject(index), 返回什么数据类型就使用对应的get方法 同时参数有使用列名而不是   推荐使用 ps.getString("name"); 
 

3.  ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

ResultSet.TYPE_SCROLL_INSENSITIVE

返回的结果集对数据库中的的数据变动是不敏感的。可以这么认为,当拿到结果集时,已经把数据库库中满足条件的所有记录都取了出来,放在缓存中,如果此时有另一个线程将数据库中的数据更改了,也不会影响这个结果集中的数据,因为它用的是缓存中的。

ResultSet.TYPE_SCROLL_SENSITIVE

与此对应,它返回的结果集是敏感的,那么是不是意味着拿到结果集后,数据库中的数据变化都会反映到结果集中呢?不是这样的,这里此时拿到的结果集只是某种条件的记录的id,当打印结果集中数据的时候,根据id再临时到数据库中取,那么对于拿到结果集后,数据库中的数据被更新了(update),肯定是会被反映到结果集上的,但是对于插入(insert)操作,由于新插入的记录的id并没有被结果集缓存,所以不会反映到结果集中,对于删除操作(delete),因为数据库中的删除操作只是对被删除的记录做一个标记,使之不被被检索到,实际的数据并没有被删除,而实际缓存的  是id的实际偏移,所以删除操作也不会被反映到结果集上。

package com.jdbc.base;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

publicclass CRUD{

publicstaticvoid main(String[] args) throws ClassNotFoundException, SQLException

{

create();

find();

delete();

System.out.println("------------------after deleted----------------------------");

find();

}

staticvoid create() throws ClassNotFoundException, SQLException{

String url = "jdbc:vertica://shr3-vrt-dev.houston.hp.com:5433/shr3_vrt_dev";

String userName = "srvc_esdw_etl_dev";

String password = "but.han.545!";

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

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

conn = DriverManager.getConnection(url, userName, password);

String sql = "insert into CORE.BUD_RT (BUD_FISC_YR_NM, BUD_PER_NM, ESDW_CRT_TS) values ('2021' ,'Current', '2016-01-23 03:08:22')";

ps = conn.prepareStatement(sql);

ps.executeUpdate();

//rs.close();

ps.close();

conn.close();

}

staticvoid find() throws ClassNotFoundException, SQLException{

String url = "jdbc:vertica://shr3-vrt-dev.houston.hp.com:5433/shr3_vrt_dev";

String userName = "srvc_esdw_etl_dev";

String password = "but.han.545!";

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

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

conn = DriverManager.getConnection(url, userName, password);

String sql = "select BUD_FISC_YR_NM, BUD_PER_NM, BUD_YR_MTH_NR, ESDW_CRT_TS from CORE.BUD_RT";

ps = conn.prepareStatement(sql);

rs = ps.executeQuery();

while(rs.next()){

System.out.println(rs.getInt("BUD_FISC_YR_NM") + "\t" + rs.getString("BUD_YR_MTH_NR") + "\t" + rs.getTimestamp("ESDW_CRT_TS"));

}

rs.close();

ps.close();

conn.close();

}

staticvoid delete() throws SQLException, ClassNotFoundException{

String url = "jdbc:vertica://shr3-vrt-dev.houston.hp.com:5433/shr3_vrt_dev";

String userName = "srvc_esdw_etl_dev";

String password = "but.han.545!";

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

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

conn = DriverManager.getConnection(url, userName, password);

String sql = "delete from CORE.BUD_RT where BUD_FISC_YR_NM in('2018', '2021')";

ps = conn.prepareStatement(sql);

intcount = ps.executeUpdate();

System.out.println(count + " records are deleted.");

//rs.close();

ps.close();

conn.close();

}

}

上面例子可以运行成功, 但是呢  它有很多问题:

1. 代码的冗余 有很多重复的代码

2. 如果连接数据库的用户名 密码改了, 上面方法都要改动

3. 注册驱动只需要注册一次就行了, 但是上面个每个方法都会注册一次。

解决方法: 使用工具类来执行一些公用的方法

 

package com.jdbc.base;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

//这个工具类,不需要继承所以定义出 final。 当然也可以不定义成final 这样别人可能会在你的代码的基础做一步的修改

//同样,也不需要创建对象,所以构造函数私有  不会创建对象

publicfinalclass JdbcUtils {

//用户名,密码 URL 也需要提取出来,这样如果密码发生了改变,只需要改这一个地方就可以了

//而且一般不需要被人来直接使用这些变量,定义出私有的保护起来而且对以后的演化会很好,

privatestatic String url = "jdbc:vertica://shr3-vrt-dev.houston.hp.com:5433/shr3_vrt_dev";

privatestatic String userName = "srvc_esdw_etl_dev";

privatestatic String password = "but.han.545!";

private JdbcUtils()  {

}

//z注册驱动只需要一次就可以了,不然数据库每次操作都注册一次会很浪费资源,所以把注册驱动放到工具类中

//静态代码块只会在类被装载到虚拟时执行一次,所以这个很适合装载驱动

//这个已经不能在在工具类中处理,需要抛出去,让上层处理

static{

try {

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

catch (ClassNotFoundException e) {

thrownew ExceptionInInitializerError(e);

}

}

/*catch (ClassNotFoundException e) {

// Could not find the driver class. Likely an issue

// with finding the .jar file.

System.out.println("Could not find the JDBC driver class.");

e.printStackTrace();

return; // Exit. Cannot do anything further.

}*/

//创建连接也可以放到工具类这样  这样以后每次获取连接的时候都通过工具类来获取

//且参数是私有的,所以放到工具来中最好

publicstatic Connection getConnection() throws SQLException{

return DriverManager.getConnection(urluserNamepassword);

}

// 释放资源也可以放到工具类中

publicstaticvoid free(ResultSet rs, Statement st, Connection conn)

{

try{

if (rs != null)

rs.close();

catch(SQLException e){

e.printStackTrace();

finally {

try{

if (st != null)

st.close();

catch(SQLException e){

e.printStackTrace();

finally {

if(conn != null)

try {

conn.close();

catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

}

使用JdbcUtils类修改CRUD代码

package com.jdbc.base;

importjava.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

publicclass CRUD {

publicstaticvoid main(String[] args) throws SQLException {

read();

}

staticvoid create() throws SQLException {

Connectionconn = null;

Statement st = null;

ResultSet rs = null;

try {

conn = JdbcUtils.getConnection();

st = conn.createStatement();

String sql = "insert into CORE.BUD_RT (BUD_FISC_YR_NM, BUD_PER_NM, ESDW_CRT_TS) values ('2021' ,'Current', '2016-01-23 03:08:22')";

intcount = st.executeUpdate(sql);

System.out.println(count + " records inserted into table.");

finally {

JdbcUtils.free(rs, st, conn);

}

}

stati cvoid delete() throws SQLException {

Connectionconn = null;

Statement st = null;

ResultSet rs = null;

try {

conn = JdbcUtils.getConnection();

st = conn.createStatement();

String sql = "delete from CORE.BUD_RT where BUD_FISC_YR_NM = 2020";

intcount = st.executeUpdate(sql);

System.out.println(count + " records deleted.");

finally {

JdbcUtils.free(rs, st, conn);

}

}

staticvoid update() throws SQLException {

Connectionconn = null;

Statement st = null;

ResultSet rs = null;

try {

conn = JdbcUtils.getConnection();

st = conn.createStatement();

String sql = "update  CORE.BUD_RT set BUD_PER_NM = 'test' where BUD_FISC_YR_NM = '2014'";

intcount = st.executeUpdate(sql);

System.out.println(count + " records updated.");

finally {

JdbcUtils.free(rs, st, conn);

}

}

staticvoid read() throws SQLException {

Connectionconn = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {

conn = JdbcUtils.getConnection();

String sql = "select BUD_FISC_YR_NM, BUD_PER_NM, BUD_YR_MTH_NR, ESDW_CRT_TS from CORE.BUD_RT";

ps = conn.prepareStatement(sql);

rs = ps.executeQuery();

while (rs.next()) {

System.out.println(rs.getString("BUD_FISC_YR_NM") + "\t" + rs.getString("BUD_PER_NM") + "\t"

+ rs.getInt("BUD_YR_MTH_NR")+ "\t" + rs.getDate("ESDW_CRT_TS"));

}

finally {

JdbcUtils.free(rs, ps, conn);

}

}

}

这个时候类看起来就好多了。 但实际上任然有很多地方需要进一步优化。

上一篇:线性代数的本质与几何意义 01. 向量是什么?(3blue1brown 咪博士 图文注解版)


下一篇:JSOUP教程,JSOUP 乱码处理,JSOUP生僻字乱码解决方案