一提到JDBC,第一想法都是繁琐,确实如此。
JDBC(Java DataBase Connectivity,Java数据库连接)技术,是一种用于执行SQL语句的Java API.
JDBC优势:
Java语言访问数据库操作完全面向抽象接口编程
开发数据库应用不用限定在特定数据库厂商的API
程序的可移植性大大增强
但是不得不说写JDBC程序是一件很繁琐但该用的时候不得不写的事。
接下来我将详细的探讨和数据库连接的过程以及实现的功能。
既然jdbc是和数据库连接的技术,那首先我们得准备一个数据库环境,在此,我创建一个便签应用的数据库以及表,如图所示:
其次,需要一个数据库驱动包,将他添加到idea工程项目自己创建的文件目录下,如图所示:
则在java中的具体操作:
1.加载数据库的jdbc驱动
2.创建连接
Connection connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/memo?
user=…&password=…”);
3.创建命令
4.准备SQL语句
5.执行SqL语句
6.处理结果
7.关闭结果
8.关闭命令
9.关闭连接
在我反反复复写过程序后,发现直接这样写太冗杂,于是发现在这些操作中有些是不变的,每次改变的只有其中一些。
发现:
不变的:
1.加载驱动
2.创建连接
3.创建命令
4.关闭结果
5.关闭命令
6.关闭连接
变的:
1.SQL
2.执行命令
3.处理结果
结果有两种返回情况,若是执行查询,返回结果集:resultSet
若是执行删除,插入等更新操作,返回值是int型
既然有一部分是不变的,因此我将其优化为模板设计模式:
源代码如下:
public abstract class AbstractIdbcTemplate {
public <T> T execute(String sql) {
this.loadDriver();
this.createConnection();
this.createStatement();
T t;
if (sql.trim().toUpperCase().startsWith("SELECT")) { //首先去掉空格,因为在数据库中有空格也是对的
this.resultSet = this.executeQuery(sql);
//处理结果
t = this.handlerResult(resultSet);
} else {
int effect = this.executeUpdate(sql);
t = this.handlerResult(effect);
}
this.close();
return t;
}
private Connection connection;
private Statement statement;
private ResultSet resultSet;
//1.加载驱动
private void loadDriver() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//2.创建连接
private void createConnection(){
String url="jdbc:mysql://127.0.0.1:3306/memo";
try {
this.connection= DriverManager.getConnection(url,"root","19913017AB");
} catch (SQLException e) {
e.printStackTrace();
}
}
//3.创建命令
private void createStatement() {
try {
this.statement=connection.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
}
private int executeUpdate(String sql) {
try {
return this.statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
private ResultSet executeQuery(String sql) {
try {
return this.statement.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//处理结果集
public abstract <T> T handlerResult(ResultSet resultSet) ;
public abstract <T> T handlerResult(int effect);
//关闭资源
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();
}
}
}
观察以上程序,发现有一点不好,处理结果一开始就给定了类型,那如果我想在使用时再实现处理结果,怎么办呢?
因此我做了如下改进:
定义了一个函数接口:interface Handler<P,R>
R就表示处理的结果
源代码:
package com.zh.jdbc;
import java.sql.*;
import java.util.logging.Handler;
public class JdbcTemplate {
public <P,R> R execute(String sql, Handler<P,R> handler) {
this.loadDriver();
this.createConnection();
this.createStatement();
R t;
if(sql.trim().toUpperCase().startsWith("SELECT")) { //首先去掉空格,因为在数据库中有空格也是对的
this.resultSet = this.executeQuery(sql);
//处理结果
t = handler.handler((P)this.resultSet);
} else{
Integer effect = this.executeUpdate(sql);
t = handler.handler((P)effect);
}
this.close();
return t;
}
private Connection connection;
private Statement statement;
private ResultSet resultSet;
private void loadDriver() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private void createConnection() {
String url="jdbc:mysql://127.0.0.1:3306/memo";
try {
this.connection= DriverManager.getConnection(url,"root","19913017AB");
} catch (SQLException e) {
e.printStackTrace();
}
}
//3.创建命令
private void createStatement() {
try {
this.statement=connection.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
}
private int executeUpdate(String sql) {
try {
return this.statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
private ResultSet executeQuery(String sql) {
try {
return this.statement.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
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();
}
}
}
interface Handler<P,R>{
R handler(P p);
}
}
因为是函数接口,在测试时我们可以用Lambda表达式
测试代码如下:
public class TestJdbcTemplate {
public static void main(String[] args) {
JdbcTemplate jdbcTemplate=new JdbcTemplate();
//使用Lambda表达式
String rs=jdbcTemplate.execute("select );id,name,created_time,modify_time from memo_group",(JdbcTemplate.Handler<ResultSet,String>)resultSet->{
StringBuilder sb=new StringBuilder();
try {
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
Timestamp created_time = resultSet.getTimestamp("created_time");
Timestamp modify_time = resultSet.getTimestamp("modify_time");
sb.append(id + "" + name + "" + created_time + "" + modify_time);
sb.append("\n");
}
}catch(SQLException e){
e.printStackTrace();}
return sb.toString();
});
System.out.println(rs);
}
}
以上就是JDBC使用步骤。
注:preparedStatment,Connection等资源实现了AutoCloseable接口,即它会自动关闭,所以使用try-with-resourcesd的方式。
Statement对象:
Statement对象主要是将SQL语句发送到数据库中。JDBC API中主要提供了三种Statement对象。
Statement:用于执行不带参数的简单SQL语句
PreparedStatement:
用于执行带或者不带参数的SQL语句
SQL语句会预编译在数据库系统
执行速度快于Statement对象
CallableStatement:
用于执行数据库存储过程的调用
常用PreparedStatement对象,它会阻止常见的SQL注入攻击
JDBC实现创建,查询,删除等功能:
此处我以便签组为例:
1.创建便签组:
public static boolean createMemoGroup(MemoGroup memoGroup) {
try {
Class.forName("com.mysql.jdbc.Driver");
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/memo", "root", "19913017AB");
PreparedStatement statement = connection.prepareStatement("insert into memo_group (name, created_time) values(?,?)")
) {
statement.setString(1, memoGroup.getName());
statement.setTimestamp(2, Timestamp.valueOf(memoGroup.getCreatedTime()));
int effect = statement.executeUpdate();
return effect == 1;
}
} catch (SQLException e) {
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return false;
}
2.查询
public static List<MemoGroup> queryMemoGroup(Integer id, String name) {
List<MemoGroup> list = new ArrayList<>();
try {
Class.forName("com.mysql.jdbc.Driver");
try(Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/memo","root","19913017AB");
PreparedStatement statement = connection.prepareStatement("select id, name, created_time, modify_time from memo_group " +
"where name =? or id =?")
)
{
statement.setString(1, name);
statement.setInt(2, id);
try (ResultSet resultSet = statement.executeQuery()) {
while (resultSet.next()) {
MemoGroup memoGroup = new MemoGroup();
memoGroup.setId(resultSet.getInt("id"));
memoGroup.setName(resultSet.getString("name"));
memoGroup.setCreatedTime(resultSet.getTimestamp("created_time").toLocalDateTime());
memoGroup.setModifyTime(resultSet.getTimestamp("modify_time").toLocalDateTime());
list.add(memoGroup);
}
}
}
} catch (SQLException e) {
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return list;
}
3.更新
public static boolean updateMemoGroup(MemoGroup memoGroup) {
try {
Class.forName("com.mysql.jdbc.Driver");
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/memo", "root", "19913017AB");
PreparedStatement statement = connection.prepareStatement("update memo_group set name=(?) where id=(?)")
) {
statement.setString(1, memoGroup.getName());
statement.setInt(2,memoGroup.getId());
int effect = statement.executeUpdate();
return effect == 1;
}
}catch (SQLException e) {
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return false;
}
4.删除:
public static Integer deleteMemoGroupByName(String name){
int i=0;
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/memo", "root", "19913017AB");
PreparedStatement statement = connection.prepareStatement("delete from memo_group where name ='" + name + "'");
i= statement.executeUpdate();
System.out.println("result:"+ i);
connection.close();
statement.close();
} catch (SQLException e) {
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return i;
}
5.创建便签:
public static boolean createMemoInfo(MemoGroup memoGroup,MemoInfo memoInfo){
Connection connection=null;
try{
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/memo";
String user="root";
String password="19913017AB";
connection= DriverManager.getConnection(url,user,password);
//事务控制
connection.setAutoCommit(false); //设为false,程序员手动控制
//事务要么成功,要么失败
String memoGroupInsertSql="insert into memo_group(id,name,created_time)values (?,?,?)";
String memoInfoInsertSql="insert into memo_info(id,group_id,title,content,created_time)values (?,?,?,?,?)";
int effect1=-1;
int effect2=-1;
try(PreparedStatement preparedStatement=connection.prepareStatement(memoGroupInsertSql)){
preparedStatement.setInt(1,memoGroup.getId());
preparedStatement.setString(2,memoGroup.getName());
preparedStatement.setTimestamp(3, Timestamp.valueOf(memoGroup.getCreatedTime()));
effect1=preparedStatement.executeUpdate();
}
try(PreparedStatement statement=connection.prepareStatement(memoInfoInsertSql)){
statement.setInt(1,memoInfo.getId());
statement.setInt(2,memoInfo.getGroupId());
statement.setString(3,memoInfo.getTitle());
statement.setString(4,memoInfo.getContent());
statement.setTimestamp(5,Timestamp.valueOf(memoInfo.getCreatedTime()));
effect2=statement.executeUpdate();
}
//正常执行完成后,根据结果确定是否提交或回滚
if(effect1==1&&effect2==1){
connection.commit();
return true;
}else{
connection.rollback();
return false;
}
} catch (SQLException e) {
e.printStackTrace();
//执行失败,应该回滚
if(connection!=null){
try{
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}catch (ClassNotFoundException e){
e.printStackTrace();
}finally{
if(connection!=null){
try{
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return false;
}
闲暇时的总结,共勉,大家可以一起探讨问题呀。