本篇文章记录学习JDBC的内容，虽然这部分最后都被封装起来，但是了解如何建立连接，执行sql语句，关闭连接很有必要。
$JDBC$概述
- $JDBC$(java Database Connectivity)是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口（一组API），定义了用来访问数据库的标准java类库(java.sql,javax.sql)使用这些类库可以以一种标准的方法方便地访问数据库资源。
- JDBC为访问不同的数据库提供了一种统一的途径。
- 有了JDBC之后，java程序访问数据库的方式如图
获取数据库连接
$Driver$接口
- java.sql.Driver接口是所有 JDBC 驱动程序需要实现的接口。这个接口是提供给数据库厂商使用的，不同数据库 厂商提供不同的实现。
- 在程序中不需要直接去访问实现了 Driver 接口的类，而是由驱动程序管理器类(java.sql.DriverManager)去调用 这些Driver实现。
- mysql的驱动：com.mysql.cj.jdbc.Driver(mysql8.0之后)
加载与注册驱动
- 加载驱动：：加载 JDBC 驱动需调用 Class 类的静态方法 forName()，向其传递要加载的 JDBC 驱动的类名
Class.forName("com.mysql.cj.jdbc.Driver")
- 注册驱动：DriverManager 类是驱动程序管理器类，负责管理驱动程序
- 使用DriverManager.registerDriver(com.mysql.jdbc.Driver)来注册驱动
- 通常不显式调用DriverManager 类的 registerDriver() 方法来注册驱动程序类的实例，因为 Driver 接口 的驱动程序类都包含了静态代码块，在这个静态代码块中，会调用 DriverManager.registerDriver() 方法 来注册自身的一个实例。下图是MySQL的Driver实现类的源码：
1
2
3
4
5
6
7
8
|
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
|
$URL$
- JDBC URL 用于标识一个被注册的驱动程序，驱动程序管理器通过这个 URL 选择正确的驱动程序，从而建立到 数据库的连接。
- JDBC URL的标准由三部分组成，各部分间用冒号分隔。
- jdbc:子协议:子名称
- 协议：JDBC URL中的协议总是jdbc
- 子协议：子协议用于标识一个数据库驱动程序
- 子名称：一种标识数据库的方法。子名称可以依不同的子协议而变化，用子名称的目的是为了定位数据库 提供足够的信息。包含主机名(对应服务端的ip地址)，端口号，数据库名
- MySQL的连接URL编写方式：
- jdbc:mysql://主机名称:mysql服务端口号/数据库名称?参数=值&参数=值
- jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC
连接方式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
| public static Connection getConnection(){
Connection conn = null;
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
try {
pros.load(is);
} catch (IOException e) {
e.printStackTrace();
}
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String driverName = pros.getProperty("driverName");
try {
Class.forName(driverName);
conn = DriverManager.getConnection(url,user,password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
|
配置文件properties
1
2
3
4
| user=root
password=123456
url=jdbc:mysql:
driverName=com.mysql.cj.jdbc.Driver
|
CRUD操作
操作和访问数据库
- 数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。其实一个数据库连 接就是一个Socket连接。
- 在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：
- Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
- PrepatedStatement：SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
- CallableStatement：用于执行 SQL 存储过程
使用$Statement$操作数据表的弊端
- 存在拼串操作，繁琐
- 存在SQL注入问题
- SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段 或命令（如
select * from tablename where username=''or true or'' and password=''),在输入or true or后sql语句结构发生了变化，变为了或的逻辑关系，不管用户名和密码是否匹配该式的返回值永远为true。
$PreparedStatement$的使用
Preparement样式为select*from tablename where username=? and password=?
SQL语句会在得到用户的输入之前先用数据库进行预编译，这样的话不管用户输入什么用户名和密码的判断始终都是并的逻辑关系，防止了SQL注入
可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象
PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句
preparedStatement使代码的可读性和可维护性提高。
PreparedStatement 能最大可能提高性能：
- DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的 编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参 数直接传入编译过的语句执行代码中就会得到执行。
- 在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意 义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
通用的增删改查操作
更新操作（增、删、改）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
public static void update(Connection conn,String sql,Object ...args){
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null,ps);
}
}
|
查询操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
| public static <T> T getInstance(Connection conn,Class<T> clazz,String sql,Object ...args){
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
if(resultSet.next()){
T t = clazz.newInstance();
for(int i=1;i<=columnCount;i++){
String columnLabel = rsmd.getColumnLabel(i);
Object value = resultSet.getObject(columnLabel);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,value);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null,ps,resultSet);
}
return null;
}
|
资源的释放
- 释放ResultSet, Statement,Connection。
- 数据库连接（Connection）是非常稀有的资源，用完后必须马上释放，如果Connection不能及时正确的关闭将 导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。
操作$Blob$类型字段
- MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。
- 插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。
- MySQL的四种BLOB类型
| 类型 |
最大大小（单位：字节） |
| TinyBlob |
255 |
| Blob |
65K |
| MediumBlob |
16M |
| LongBlob |
4G |
- 在mysql的安装目录下，可以在my.ini文件加上如下的配置参数： max_allowed_packet=16M，并重启数据库
插入$Blob$类型
1
2
3
| FileInputStream fis = new FileInputStream("kele.jpg");
ps.setBlob(4, fis);
ps.executeUpdate();
|
读取$Blob$类型
1
2
3
4
5
6
7
8
| Blob photo = resultSet.getBlob("photo");
is = photo.getBinaryStream();
fos = new FileOutputStream("mei.jpg");
byte[] cbuf = new byte[1024];
int len = 0;
while ((len = is.read(cbuf)) != -1) {
fos.write(cbuf, 0, len);
}
|
数据库事务
$JDBC$处理事务
- 事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
- 事务处理：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方 式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来； 要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。
- 数据一旦提交，就不可回滚。
- 数据什么时候意味着提交？
- 当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会 向数据库自动提交，而不能回滚。
- 关闭数据库连接，数据就会自动的提交。如果多个操作，每个操作使用的是自己单独的连接，则无法保证 事务。即同一个事务的多个操作必须在同一个连接下。
- JDBC程序中为了让多个 SQL 语句作为一个事务执行：
- 调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务。
- 在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务。
- 在出现异常时，调用rollback方法，回滚事务。
$MySql$中设置隔离级别
设置连接隔离级别
1
2
3
4
5
6
|
System.out.println(conn.getTransactionIsolation());
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
conn.setAutoCommit(false);
|
数据库连接池
- JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器 (Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现，常用连接池：
- **C3P0 **是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以。hibernate官方推荐使用
- DBCP 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因 自身存在BUG，Hibernate3已不再提供支持。
- Druid 是阿里提供的数据库连接池，是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池。
- DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接 池。
- DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速 度。
- 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个 数据源即可。
- 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数 据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。
$Druid$数据库连接池
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| private static DataSource dataSource = null;
static{
try {
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
dataSource = DruidDataSourceFactory.createDataSource(pros);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnectionOfDrud(){
Connection conn = null;
try {
conn = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}druid.properties配置文件
|
Apache-DBUtils实现CRUD操作
主要API的使用
DbUtils
- DbUtils ：提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。主要方法 如下：
- public static void closeQuietly(…): 这一类方法不仅能在Connection、Statement和ResultSet为NULL情 况下避免关闭，还能隐藏一些在程序中抛出的SQLEeception。
- public static void rollback(Connection conn)throws SQLException：允许conn为null，因为方法内部做 了判断
QueryRunner类
- 该类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少 编码量。
- QueryRunner类的主要方法：
- 更新：public int update(Connection conn, String sql, Object… params) throws SQLException:用来执行 一个更新（插入、更新或删除）操作。
- 查询：public Object query(Connection conn, String sql, ResultSetHandler rsh,Object… params) throws SQLException：执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句 的置换参数。该方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭。
ResultSetHandler接口及实现类
- 该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式。
- ResultSetHandler 接口提供了一个单独的方法：Object handle (java.sql.ResultSet rs)。
- 接口的主要实现类：
- BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
- BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
- MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
- MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List。
- ScalarHandler：查询单个值对象(查询类似于最大的，最小的，平均的，总和，个数相关的数据)
- 在使用这些接口实现类时一定要给JavaBean添加setter方法，因为其重写的hand方法内部会调用赋值。
crud测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
@Test
public void testQuery1(){
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnectionOfDrud();
String sql = "select name,email,birth from customers where id=?";
BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
try {
Customer customer = runner.query(conn, sql, handler, 28);
System.out.println(customer);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,null);
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
@Test
public void testQuery5(){
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnectionOfDrud();
String sql = "select max(birth) from customers";
ScalarHandler handler = new ScalarHandler();
try {
Date date = (Date) runner.query(conn, sql, handler);
System.out.println(date);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,null);
}
}
|
自定义ResultSetHandler实现类
如果不想使用自带的实现类，还可以自己实现ResultSetHandler类，只需重写handle方法处理结果集即可
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| ResultSetHandler<User> handler = new ResultSetHandler<User>() {
@Override
public User handle(ResultSet resultSet) throws SQLException {
if(resultSet.next()) {
int id = resultSet.getInt("id");
String username = resultSet.getString("username");
String password = resultSet.getString("password");
String email = resultSet.getString("email");
User user = new User(id, username, password, email);
return user;
}
return null;
}
};
|