jdbc

前言

由于目前市面上存在的多种数据库，为了使Java编写的程序不依赖具体的数据库，Java提供了专门用于操作数据库的API，就是JDBC(Java Data Base Connectivity)Java数据库连接。使用JDBC的应用程序和数据库连接后，就可以使用JDBC提供的API操作数据库。

基本步骤：

• 与一个数据库建立连接
• 向连接的数据库发送SQL语句
• 处理SQL语句返回的结果

连接数据库

在开发中常用的连接方式是加载JDBC-数据库驱动(连接器)，即JDBC调用本地的JDBC-数据库驱动和相应数据库建立连接。连接需要经历两个步骤：

1. 加载JDBC-数据库驱动。
2. 和指定的数据库建立连接。

如果JDK扩展没用JDBC-MySQL驱动，可登录MySQL官网下载，选择JDBC Driver for MySQL，例如下载的是mysql-connector-java-5.1.40.zip，解压后复制到JDK扩展目录中，例如E:\jdk1.8\jre\lib\ext。之后便开始加载JDBC-MySQL数据库驱动，负责加载驱动的代码为:

try {//MySQL驱动被封装在Driver类中 Class.forName("com.mysql.jdbc.Driver"); //oracle Class.forName("oracle.jdbc.driver.OracleDriver"); //加载SQLServer驱动 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //加载Derby驱动 Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); }catch(Exception e){ }

之后第三步开始连接数据库，java.sql包中的DriverManager类有两个用于建立连接的类方法：

• Connection getConnection(String ,String ,String)
• ConnectiongetConnection(String)

上述两个方法都有可能返回SQLException异常，DriverManager类调用上述方法可以和数据库建立连接，可以返回一个Connection对象。注意，连接时MySQL数据库服务器启动，默认端口为3306。

Connection con; String url = "jdbc:mysql://127.0.0.1:3306/users?user=root&password=123456&useSSL=true"; //String url = "jdbc:mysql://127.0.0.1:3306/users?useSSL=true&characterEncoding=utf-8"; //String sqlserver_url="jdbc:sqlserver://localhost:1433;DatabaseName=users"; //String derby="jdbc:derby:users;create=true";创建并连接users数据库 //String user = "root"; //String password = "123456"; try{//连接MySQL数据库users con = DriverManager.getConnection(url); //con = DriverManager.getConnection(url,user,password); }catch(SQLException e){ System.out.println(e); }

需要注意的是，如果连接数据库的表中有汉字，那么建立连接时要多传一个参数characterEncoding，取值utf-8或gb2312。

查询操作

建立连接后就可以发送SQL语句了，和数据库进行交互，常见操作有查询、修改和更新等。步骤具体如下：

1. 向数据库发送查询语句

   使用statement声明一个SQL语句对象，然后让连接对象con调用方法creatStatement()创建这个SQL语句对象。

   try{ Statement sql = con.createStatement(); }catch(SQLException e){ System.out.println(e); }

2. 处理查询结果

   有了SQL语句对象后，这个对象就可以调用相应的方法实现对数据库的查询和修改，并将查询结果存放在一个ResultSet类声明的对象中，即查询结果将返回一个ResultSet对象，对象由按字段组织的数据行构成。

   ResultSet rs1 = sql.executeQuery("SELECT * FROM users"); ResultSet rs2 = sql.executeQuery("SELECT name,height FROM users");

   结果集rs1的列数和users的列数相同，结果集rs2列数只有2列。ResultSet对象一次只能看到一个数据行，使用next()移到下一行，获得一行数据后，ResultSet对象可以使用getXxx方法获得字段值，将位置索引或列名传递给getXxx方法即可。无论字段为何种属性，总可以使用getString(int columnIndex)或getString(String columnName)方法返回字段的串表示。

3. 关闭连接

   ResultSet对象和数据库连接对象实现了紧密的绑定，则一旦连接对象被关闭，ResultSet对象中的数据立刻消失。

   con.close(); //关闭连接将无法获取数据

顺序查询

ResultSet对象一次只能看到一个数据行，使用next()方法移到下一个数据行，next()最初查询位置，即游标位置，在第一行前面。next()向下移动游标，移动成功返回true，否则返回false。

rs = sql.executeQuery("SELECT * FROM users"); while(rs.next()){ String number = rs.getString(1); String name = rs.getString(2); Date date = rs.getDate(3); System.out.println(number); System.out.println(name); System.out.println(date); } con.close();

控制游标

调用next()方法使游标向下移动，若需要在结果集中上下移动、显示某一条记录或随机显示若干记录，必须返回一个可滚动的结果集。为了得到一个可滚动的结果集可使用如下方法：

Statement st = con.createStatement(int type,int concurrency);

type的取值决定滚动方式：

• ResultSet.TYPE_FORWORD_ONLY：结果集的游标只能向下滚动。
• ResultSet.TYPE_SCROLL_INSENSITIVE：结果集的游标可以上下移动，当数据库变化时，当前结果集不变。
• ResultSet.TYPE_SCROLL_SENSITIVE：返回可滚动的结果集，当数据库变化时，当前结果集同步改变。

concurrency取值决定是否可以用结果集更新数据库：

• ResultSet.CONCUR_READ_ONLY：只读，不能用结果集更新数据库中的表。
• ResultSet.CONCUR_UPDATABLE：能用结果集更新数据库中的表。

滚动查询经常会用到一些实例方法，大家可自行查询了解和使用。例如public void last()移到最后一行，public int getRow()返回当前行号。

条件和排序查询

需要条件进行查询时，可在SQL语句中添加条件。

• where子语句

  select 字段 from 表 where 条件

1. 字段值和固定值比较

   select name from users where name='骚年'

2. 字段值在某个范围内

   select * from users where height>1.60 and height<1.8 select * from users where weight>45 and name!='张三'

3. 使用某些特殊日期函数，如year,month,day

   select * from users where year(birthday)<2013 and month(birthday)<=10 select * from users where year(birthday) between 2000 and 2014

4. 使用某些特殊的时间函数，如hour,minute,second

   select * from timelist where second(shijian)=32

5. 使用操作符like进行模式匹配，使用%代替0给或多个字符，用下划线_代替一个字符，例如在name字段查询含‘凯’字的记录。

   select * form users where name like '%凯%'

• 排序

  用order by子语句进行排序，例如：

  select * from users order by height select * from users where name like '%凯%' order by name

增删改操作

Statement对象调用方法：

public int executeUpdate(String sqlStatement);

通过参数sqlStatement指定的方式实现对数据库中记录的更新、添加和删除操作。

1. 更新

   下述SQL语句将users表中name值为“张三”的记录的height字段的值更新为1.77：

   update 表 set 字段 = 新值 where <条件子句> update users set height = 1.77 where name='张三'

2. 添加

   以下SQL语句将users表中添加两条新的记录(记录之间用逗号分隔)：

   insert into 表(字段列表) values (对应的具体的记录) insert into 表 values (对应的具体的记录) insert into users values ('R1001','基地','2020-11-21',1.67),('R1002','包有为','2020-12-20',1.79)

3. 删除

   下述SQL语句将删除users表中的number字段值为R1002的记录：

   delete from 表名 where <条件子句> delete from users where number='R1002'

当返回结果集后，没用立即输出结果集的记录，而是接着执行了更新语句，那么结果集就不能输出记录，如果要返回结果集就必须重新返回结果集。

使用预处理语句

Java提供了更高效的数据库处理机制，就是PreparedStatement预处理语句对象。它的目的是减轻数据库SQL解释器的负担，事先将SQL语句解释为数据库底层的内部命令，然后让数据库去执行这个命令，从而提高了访问数据库的速度。

连接对象调用preparedStatement(String sql)方法对参数sql指定的SQL语句进行预编译处理，生成数据库底层的内部命令，并将该命令封装在PreparedStatement对象中，那么该对象调用下列方法都可以使得该命令被数据库执行。

• ResultSet executeQuery()
• boolean execute()
• int executeUpdate()

只要编译好了PreparedStatement对象，那么该对象可以随时执行上述方法。

使用通配符?来代替字段的值，只要在预处理语句之前再设置通配符所代表的具体值即可。

String str = "select * from users where height < ? and name= ?"; PreparedStatement sql = con.preparedStatement(str);

在sql对象执行之前，必须调用相应的方法设置通配符?代表的具体值，例如：

sql.setFloat(1,1.76f); sql.setString(2,"哦哦");

通配符按照它们在预处理语句中从左到右依次出现的顺序分别为第1、2···n个通配符，使用通配符可以随时改变SQL语句中的条件。常用方法有以下几个：

void setDate(int paraIndex,Date x); void setDouble(int paraIndex,double x); void setFloat(int paraIndex,float x); void setInt(int paraIndex,int x); void setLong(int paraIndex,long x); void setString(int paraIndex,String x);

通用查询

编写一个类，用户将数据库名、SQL语句传递给该类对象，那么该对象就用一个二维数组返回查询结果。为了编写通用查询，需要知道数据库的列的名字，特别是表的列数(字段的个数)，简单的方法就是使用返回到程序的结果集获取相关信息。结果集ResultSet对象调用getMetaData()方法返回一个ResultSetMetaData对象(结果集的元数据对象)。

ResultSetMetaData metaData = rs.getMetaData();

之后metaData调用getColumnCount()方法就可以返回结果集中的列的数目，调用getColumnName(int i)就可以返回结果集中的第i列的名字。

int columnCount = metaData.getColumnCount(); String columnName = metaData.getColumnName(i);

事务

事务由一组SQL语句组成，所谓事务处理，是指应用程序保证事务中的SQL语句要么全部执行要么一个都不执行。事务处理是保证数据库中数据完整性与一致性的重要机制。应用程序与数据库建立连接后，可能使用多条SQL语句操作数据库中的一个或多个表。例如管理资金转账的应用程序，保证转账双方资金安全合理，则SQL语句都执行或都不执行。

JDBC处理事务的步骤：

1. 用setAutoCommit(boolean b)方法关闭自动提交模式

   关闭自动提交就是关闭SQL语句的即可生效性，连接对象的提交模式是自动提交模式，连接对象产生的Statement或PreparedStatement对象对数据库提交的任何SQL语句都会立即生效，而我们的要求是让一组提交的SQL语句同步生效。就是归纳为不能立刻生效，不能不生效！

   连接对象con首先调用setAutoCommit(boolean autoCommit)方法，参数取值false即可关闭。注意，先关闭再获取Statement对象。

   sql = con.createStatement();

2. 用commit()方法处理事务

   关闭后，连接对象产生的Statement对象对数据库提交的任何一条SQL语句都不会立刻生效，这样Statement对象可以提交多条SQL语句，这些SQL语句就是一个事务，事务中的SQL语句不会立刻生效。连接对象调用commit()方法就是试图让事务中的SQL语句全部生效。

3. 用rollback()方法处理事务失败

   处理事务失败就是撤销事务所做的操作。事务中任何一个SQL语句未能生效，就抛出SQLException异常，在处理该异常时，必须让连接对象con调用rollback()方法，作用是撤销事务中成功执行的SQL语句对数据库所做的更新、插入和删除操作，让数据库中的数据恢复为之前的状态。

事务的四大特点（ACID）

• Atomicity（原子性）：表示一个事务内的所有操作是一个整体，要么全部成功，要么全失败；
• Consistency（一致性）：表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前的状态；
• Isolation(隔离性）：事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
• Durability(持久性）：持久性事务完成之后，它对于系统的影响是永久性的。

事务隔离级别从低到高：

• 读取未提交（Read Uncommitted)
• 读取已提交（Read Committed)
• 可重复读（Repeatable Read)
• 序列化（serializable)

批处理Batch

• 灵活指定SQL语句中的变量
  PreparedStatement
• 对存储过程进行调用
  CallableStatement
• 运用事务处理
  Transaction
• 批处理
  Batch
  对于大量的批处理，建议使用Statement，因为PreparedStatement的预编译空间有限，当数据量特别大时，会发生异常。

CLOB文本大对象操作

• CLOB（Character Large Object)
  用于存储大量的文本数据-大字段有些特殊，不同数据库处理的方式不一样，大字段的操作常常是以流的方式来处理的。而非一般的字段，一次即可读出数据。
• Mysql中相关类型：
  TINYTEXT最大长度为255(2^8-1)字符的TEXT列。
  TEXT[（M)]最大长度为65535(2^16-1)字符的TEXT列。
  MEDIUMTEXT最大长度为16,777,215(2^24-1)字符的TEXT列。
  LONGTEXT最大长度为4,294,967,295或4GB(2^32-1)字符的TEXT列。

BLOB二进制大对象的使用

• BLOB(Binary Large Object) 用于存储大量的二进制数据
  大字段有些特殊，不同数据库处理的方式不一样，大字段的操作常常是以流的方式来处理的。而非一般的字段，一次即可读出数据。
• Mysql中相关类型：
  TINYBLOB最大长度为255(2^8-1)字节的BLOB列。
  BLOB[（M)]最大长度为65535(2^16-1)字节的BLOB列。
  MEDIUMBLOB最大长度为16,777,215(2^24-1)字节的BLOB列。
  LONGBLOB最大长度为4,294,967,295或4GB(2^32-1)字节的BLOB列。