JDBC（Java DataBase Connectivity，Java数据库连接）是Java程序访问数据库的基本方式，是一种用于执行SQL语句的Java API，这个API由java.sql.*，javax.sql.*包中的一些类和接口组成。它为数据库开发人员提供了一个标准的API，使它们能够用纯Java API来编写数据库应用程序。
JDBC为多种关系型数据库提供了统一访问的方式，它主要包含一些通用的接口类。真正的数据库访问操作实现是由各自数据库厂商提供的。通常把厂商提供的特定于数据库的访问API称为数据库JDBC驱动程序。
以MySQL数据库为例：
① 加载驱动（pom文件中配置MySQL驱动程序，通过Class加载驱动类）；
② 创建连接；
MySQL数据连接的URL参数格式如下：
jdbc:mysql://服务器地址:端口/数据库名?参数名1=参数值&参数名2=参数值;
③ 准备sql语句；
④ 创建命令；
⑤ 执行命令；
⑥ 返回结果处理；
⑦ 关闭结果；
⑧ 关闭命令；
⑨ 关闭连接。
- 准备JDBC开发案例：
-- MySQL dump 10.13 Distrib 5.7.21, for Win64 (x86_64)
--
-- Host: localhost Database: memo
-- ------------------------------------------------------
-- Server version 5.7.21-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `memo`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `memo` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `memo`;
--
-- Table structure for table `memo_group`
--
DROP TABLE IF EXISTS `memo_group`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `memo_group` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '便签组编号',
`name` varchar(8) NOT NULL COMMENT '便签组名称',
`created_time` datetime NOT NULL COMMENT '创建时间',
`modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=667 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `memo_group`
--
LOCK TABLES `memo_group` WRITE;
/*!40000 ALTER TABLE `memo_group` DISABLE KEYS */;
INSERT INTO `memo_group` VALUES (1,'Java','2019-06-29 08:10:00','2019-06-29 02:20:53'),(2,'C++','2019-05-11 10:10:00','2019-06-29 02:20:53'),(3,'default','2019-05-17 23:00:00','2019-06-29 02:20:53'),(4,'个人笔记','2019-06-29 10:56:46','2019-06-29 02:56:46'),(666,'测试组','2019-06-29 12:13:19','2019-06-29 04:13:19');
/*!40000 ALTER TABLE `memo_group` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `memo_info`
--
DROP TABLE IF EXISTS `memo_info`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `memo_info` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '便签编号',
`group_id` int(11) NOT NULL COMMENT '便签组编号',
`title` varchar(32) NOT NULL COMMENT '便签标题',
`content` varchar(1024) NOT NULL DEFAULT '' COMMENT '便签内容',
`is_protected` char(1) NOT NULL DEFAULT '0' COMMENT '是否私密，0：公开 1：私密',
`background` enum('WHITE','RED','BLUE','GREEN') DEFAULT 'WHITE' COMMENT '背景颜色',
`is_remind` char(1) DEFAULT '0' COMMENT '是否提醒，0：不提醒 1：提醒',
`remind_time` datetime DEFAULT NULL COMMENT '提醒时间',
`created_time` datetime NOT NULL COMMENT '创建时间',
`modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=556 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `memo_info`
--
LOCK TABLES `memo_info` WRITE;
/*!40000 ALTER TABLE `memo_info` DISABLE KEYS */;
INSERT INTO `memo_info` VALUES (1,1,'欢迎使用','下面是使用手册','0','WHITE','0',NULL,'2019-06-24 19:08:42','2019-06-24 11:08:42'),(555,666,'今天好日子','~~~~','0','WHITE','0',NULL,'2019-06-29 12:13:19','2019-06-29 04:13:19');
/*!40000 ALTER TABLE `memo_info` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `memo_share`
--
DROP TABLE IF EXISTS `memo_share`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `memo_share` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '便签分享编号',
`info_id` int(11) DEFAULT NULL COMMENT '便签编号',
`mark` varchar(32) NOT NULL DEFAULT '' COMMENT '分享备注',
`share_time` datetime NOT NULL COMMENT '分享时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `memo_share`
--
LOCK TABLES `memo_share` WRITE;
/*!40000 ALTER TABLE `memo_share` DISABLE KEYS */;
INSERT INTO `memo_share` VALUES (1,1,'特别有意思的便签APP','2019-06-24 19:08:43');
/*!40000 ALTER TABLE `memo_share` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2019-06-29 14:36:50
- 下来就开始演示JDBC开发流程（以查询和删除操作为例，其他操作类似）：
package com.xxx;
import java.sql.*;
import java.time.LocalDateTime;
/**
* Description: 演示JDBC开发流程（查询）
* Author: admin
* Create: 2019-07-02 20:35
*/
public class Case4 {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:4406/memo?useSSL=false";
connection = DriverManager.getConnection(url, "root", "jhj52000");
String sql = "select id,name,created_time,modify_time from memo_group";
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
LocalDateTime created_time = resultSet.getTimestamp("created_time").toLocalDateTime();
LocalDateTime modify_time = resultSet.getTimestamp("modify_time").toLocalDateTime();
System.out.println(id + " " + name + " " + " " + created_time + " " + modify_time);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
我们可以看到跟刚才数据库里查询出来的数据是一致的，说明确实达到了利用java代码操控数据库的目的。
package com.xxx;
import java.sql.*;
/**
* Description: 演示JDBC开发流程（删除）
* Author: admin
* Create: 2019-06-30 12:33
*/
public class JDBCCase2 {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接
String url = "jdbc:mysql://127.0.0.1:4406/memo?useSSL=false";
connection = DriverManager.getConnection(url, "root", "jhj52000");
//创建命令 *
String sql = "delete from memo_group where id = 666";
statement = connection.createStatement();
//执行命令 *
int effect = statement.executeUpdate(sql);
//返回处理结果 *
if (effect == 1) {
System.out.println("删除成功" + " " + effect);
}else {
System.out.println("删除失败" + " " + effect);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
再次查询数据库，发现确实少了一条id=666的记录。
现在问题是：总结上述两份代码发现有关加载驱动、创建连接、创建命令、关闭资源这些代码都是相同的，唯一不同的就是创建sql语句、执行命令和处理结果这些地方。那么，既然这样的话，我们就可以使用模板设计模式，将相同的功能的代码创建成一个模板类。
package com.xxx;
import java.sql.*;
import java.time.LocalDateTime;
/**
* Description: 模板设计模式
* Author: admin
* Create: 2019-07-02 8:00
*/
public abstract class AbstractJDBCTemplate {
private Connection connection;
private Statement statement;
private ResultSet resultSet;
//模板执行流程
public final void execute(String sql) {
try {
this.loadDriver();
this.createConnection();
this.createStatement();
//sql通过方法传入
if (sql.trim().toUpperCase().startsWith("SELECT")) {
this.callQuery(sql);
this.handlerResult(this.resultSet);
} else {
Integer effect = callUpdate(sql);
this.handlerResult(effect);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
//1.加载驱动
private void loadDriver() throws ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
}
//2.创建连接
private void createConnection() throws SQLException {
String url = "jdbc:mysql://127.0.0.1:4406/memo?useSSL=false";
this.connection = DriverManager.getConnection(url, "root", "jhj52000");
}
//3.准备sql
//public abstract String getSql();
//4.创建命令
private void createStatement() throws SQLException {
this.statement=this.connection.createStatement();
}
//5.执行命令
//public abstract void callStatement();
private void callQuery(String sql) throws SQLException {
this.resultSet = statement.executeQuery(sql);
}
private Integer callUpdate(String sql) throws SQLException {
return this.statement.executeUpdate(sql);
}
//6.处理结果（ResuletSet、Integer）
public abstract void handlerResult(ResultSet resultSet) throws SQLException;
public abstract void handlerResult(Integer value);
//7.关闭资源
public void close() {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
AbstractJDBCTemplate template = new JDBCTemplate1();
template.execute("select id, name, created_time, modify_time from memo_group");
template.execute("insert into memo_group(name,created_time) values ('JDBC组','2019-07-04 11:00:00')");
}
}
class JDBCTemplate1 extends AbstractJDBCTemplate {
@Override
public void handlerResult(ResultSet resultSet) throws SQLException {
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
LocalDateTime created_time = resultSet.getTimestamp("created_time").toLocalDateTime();
LocalDateTime modify_time = resultSet.getTimestamp("modify_time").toLocalDateTime();
System.out.println(id + " " + name + " " + created_time + " " + modify_time);
}
}
@Override
public void handlerResult(Integer value) {
System.out.println("更新结果" + " " + value);
}
}
查看数据库，发现多了一条id=667的组。
package com.xxx;
import java.sql.*;
/**
* Description: 模板设计模式 + 函数接口
* Author: admin
* Create: 2019-07-02 10:06
*/
public class JDBCTemplate {
private Connection connection;
private Statement statement;
private ResultSet resultSet;
public final <P, R> R execute(String sql, Handler<P, R> handler) {
R result = null;
try {
this.loadDriver();
this.createConnection();
this.createStatement();
if (sql.trim().toUpperCase().startsWith("SELECT")) {
this.callQuery(sql);
result = handler.handle((P) this.resultSet);
}else {
Integer value = this.callUpdate(sql);
result = handler.handle((P) value);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
private void loadDriver() throws ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
}
private void createConnection() throws SQLException {
String url = "jdbc:mysql://127.0.0.1:4406/memo?useSSL=false";
this.connection= DriverManager.getConnection(url, "root", "jhj52000");
}
private void createStatement() throws SQLException {
this.statement = this.connection.createStatement();
}
private void callQuery(String sql) throws SQLException {
this.resultSet = this.statement.executeQuery(sql);
}
private Integer callUpdate(String sql) throws SQLException {
return this.statement.executeUpdate(sql);
}
private void close() {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
@FunctionalInterface
interface Handler<P, R> {
R handle(P p) throws SQLException;
}
package com.xxx;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Description: 测试类
* Author: admin
* Create: 2019-07-02 20:36
*/
public class TestJdbcTemplate {
public static void main(String[] args) {
JDBCTemplate template = new JDBCTemplate();
String sql = "select id, name from memo_group";
String result = template.execute(sql, new Handler<ResultSet, String>() {
@Override
public String handle(ResultSet resultSet) throws SQLException {
StringBuilder sb = new StringBuilder();
while (resultSet.next()) {
sb.append(resultSet.getInt("id")).append(" ")
.append(resultSet.getString("name")).append("\n");
}
return sb.toString();
}
});
System.out.println(result);
sql = "select id, title, content from memo_info";
result = template.execute(sql, new Handler<ResultSet, String>() {
@Override
public String handle(ResultSet resultSet) throws SQLException {
StringBuilder sb = new StringBuilder();
while (resultSet.next()) {
sb.append(resultSet.getInt("id")).append(" ")
.append(resultSet.getString("title")).append(" ")
.append(resultSet.getString("content")).append("\n");
}
return sb.toString();
}
});
System.out.println(result);
sql = "select count(*) as total from memo_group";
Integer count = template.execute(sql, (Handler<ResultSet, Integer>) resultSet -> {
Integer totalRow = 0;
while (resultSet.next()) {
totalRow = resultSet.getInt("total");
}
return totalRow;
});
System.out.println("Memo_group一共有" + count + "条记录");
sql = "delete from memo_group where id=1";
result = template.execute(sql, (Handler<Integer, String>) integer -> {
if (integer == 1) {
return "删除成功";
}else {
return "删除失败";
}
});
System.out.println(result);
}
}
扫一扫
571
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
