JDBC操纵数据库
在各种ORM框架盛行的时代，想必我们很多都已经忘了如何在不借助框架的基础上，使用原生的JDBC来和数据库打交道了，对于初级程序员来说，使用原生的JDBC编码可以使我们更加全面的了解框架底层下的操作逻辑，在原生编码中我们也需要自己去封装一些工具类，使用一些设计模式来简化我们操纵数据库的步骤，在这一步步封装的过程中可以提高我们的编程思想，所以了解原生JDBC操控数据库也是很有必有得，接下来就让我们从一份最原始的JDBC到自己封装的工具类及模板来实现的全步骤，话不多说直接上代码!
JDBC 初体验
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
String sql = "select count(0) as total from n_student where name=?";
//1,注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2,获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/noone_test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC","root","root");
//3,获取语句会话对象,这里我们获取的是预编译处理会话，可以防止SQL注入
ps = conn.prepareStatement(sql);
//给占位符? 赋值，setObject() 第一个参数为占位符位置，第二个参数为实际传入的值
ps.setObject(1, "小虎");
//执行语句得到返回值
rs = ps.executeQuery();
int total = 0;
if (rs.next()) {
total = rs.getInt("total");
}
System.out.println(total);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (rs!=null){rs.close();}
if (ps!=null){ps.close();}
if (conn!=null){conn.close();}
} catch (Exception e) {
e.printStackTrace();
}
}
}
JDBC 1.0
下面我们要使用原生JDBC模拟真实开发场景，我们现在要对学生表进行增删改查操作
学生表
@Data
public class Student {
private Integer age;
private String name;
public Student(Integer age, String name) {
this.age = age;
this.name = name;
}
}
Dao
/**
* 传统操控jdbc接口方式
*/
public class StudentDao1 {
public void save(Student stu) {
String sql = "insert into n_student(age,name) values(?,?) ";
Connection conn = null;
PreparedStatement ps = null;
try {
//加载数据库驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接
conn = DriverManager.getConnection("jdbc:mysql://192.168.232.128:3306/noone_test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC", "root", "root");
//创建语句对象
ps = conn.prepareStatement(sql);
//给占位符赋值
ps.setObject(1, stu.getAge());
ps.setObject(2, stu.getName());
ps.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (ps != null) {
ps.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
public void delete(Student stu) {
String sql = "delete from n_student where name =?";
Connection conn = null;
PreparedStatement ps = null;
try {
//加载数据库驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接
conn = DriverManager.getConnection("jdbc:mysql://192.168.232.128:3306/noone_test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC", "root", "root");
//创建语句对象
ps = conn.prepareStatement(sql);
//给占位符赋值
ps.setObject(1, stu.getName());
ps.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (ps != null) {
ps.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
public void selectOne(Student stu) {
String sql = "select * from n_student where name =?";
Connection conn = null;
PreparedStatement ps = null;
try {
//加载数据库驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接
conn = DriverManager.getConnection("jdbc:mysql://192.168.232.128:3306/noone_test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC", "root", "root");
//创建语句对象
ps = conn.prepareStatement(sql);
//给占位符赋值
ps.setObject(1, stu.getName());
ps.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (ps != null) {
ps.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
从上述代码中我们发现，dao中有着大量的冗余代码，不利于开发，也不利于维护，所以我们可以将这些重复的代码抽取成一个工具类JdbcUtils
package com.xzq.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static Properties prop;
public JdbcUtils() {}
static {
try {
//1,加载注册驱动
ClassLoader loader = Thread.currentThread().getContextClassLoader();
InputStream is = loader.getResourceAsStream("db.properties");
prop = new Properties();
prop.load(is);
Class.forName(prop.getProperty("driverClassName"));
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() {
Connection conn = null;
try {
conn=DriverManager.getConnection(prop.getProperty("url"),prop.getProperty("username"),prop.getProperty("password"));
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return conn;
}
//关闭资源
public static void closedResource(PreparedStatement ps,Connection conn) {
try {
if (ps != null) {
ps.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//关闭资源
public static void closedResource(ResultSet rs,PreparedStatement ps, Connection conn) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (ps != null) {
ps.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
使用我们的jdbcUtils使用再来对dao进行改造
JDBC 2.0
package com.xzq.dao;
import com.xzq.domain.Student;
import com.xzq.util.JdbcUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* 传统操控jdbc接口方式
*/
public class StudentDao2 {
public void save(Student stu) {
String sql = "insert into n_student(age,name) values(?,?) ";
Connection conn = JdbcUtils.getConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
ps.setObject(1, stu.getAge());
ps.setObject(2, stu.getName());
ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.closedResource(ps,conn);
}
}
public void delete(Student stu) {
String sql = "delete from n_student where name =?";
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
ps=conn.prepareStatement(sql);
ps.executeUpdate();
ps.setObject(1, stu.getName());
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public Student selectOne(Student stu) {
String sql = "select * from n_student where name =?";
Connection conn = null;
PreparedStatement ps = null;
Student db_stu = null;
try {
conn=JdbcUtils.getConnection();
ps=conn.prepareStatement(sql);
ps.setObject(1, stu.getName());
ResultSet rs = ps.executeQuery();
if (rs.next()) {
int age = rs.getInt("age");
String name = rs.getString("name");
db_stu = new Student(age, name);
}
JdbcUtils.closedResource(rs,ps,conn);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return db_stu;
}
public List<Student> selectAll() {
String sql = "select*from n_student";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
ArrayList<Student> list = new ArrayList<>();
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
int age = rs.getInt("age");
String name = rs.getString("name");
Student student = new Student(age,name);
list.add(student);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return list;
}
}
这样看起来我们的代码确实简介了许多，但仔细观察，可以发现代码中还是有很多冗余代码，例如获取连接，关闭连接，构建返回值，DML中不一样的只有SQL语句和参数，
我们可以创建一个模板类JdbcTemplate，创建一个DML和DQL模板来进行重构
模板类
package com.xzq.template;
import com.xzq.domain.Student;
import com.xzq.util.IRowMapper;
import com.xzq.util.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/*
模板类
*/
public class JdbcTemplate {
//DQL查询统一模板
public static List query( String sql, Object... params) {
List<Student> list = new ArrayList<>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps=conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
Object param = params[i];
ps.setObject(i+1, param);
}
rs=ps.executeQuery();
while (rs.next()) {
int age = rs.getInt("age");
String name = rs.getString("name");
Student student = new Student(age, name);
list.add(student);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closedResource(rs,ps,conn);
}
return list;
}
//DML操作统一模板
public static void DMLOperator(String sql, Object... params) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
ps=conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
Object param = params[i];
ps.setObject(i, param);
}
ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.closedResource(ps,conn);
}
}
}
这样的重复代码基本就处理好了，但是有一个很严重的问题就是，这个程序的DQL操作中只能处理Student类和n_student表相关数据，无法处理其他数据，不同表有不同列，不同处理结果集的代码也就不一样，处理结果集的操作只有调用方Dao知道，所以我们不应该把处理结果的方法放在模板方法中
应该由每个DAO自己来处理，因此我们可以创建一个IRowMapper接口处理结果集
IRowMapper接口
package com.xzq.util;
import java.sql.ResultSet;
import java.util.List;
public interface IRowMapper {
//处理结果集
List mapping(ResultSet rs) throws Exception;
}
实现类自己去实现IRowMapper接口的 mapping方法，想要处理什么类型数据在里面定义即可
学生结果集处理类
public class StudentMapping implements IRowMapper {
@Override
public List mapping(ResultSet rs) throws Exception {
ArrayList<Object> list = new ArrayList<>();
while (rs.next()) {
int age = rs.getInt("age");
String name = rs.getString("name");
Student student = new Student(age, name);
list.add(student);
}
return list;
}
}
模板类，通过高层调用传来的IRowMapper接口实现来进行结果集映射
//DQL查询统一模板
public static List query(IRowMapper rm, String sql, Object... params) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps=conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
Object param = params[i];
ps.setObject(i+1, param);
}
rs=ps.executeQuery();
return rm.mapping(rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closedResource(rs,ps,conn);
}
return null;
}
Dao
public Student selectOne(Student stu) {
String sql = "select * from n_student where name =?";
Object params[] = new Object[]{stu.getName()};
List<Student> list = JdbcTemplate.query( new StudentMapping(),sql, params);
return list.get(0);
}
public List<Student> selectAll() {
String sql = "select*from n_student";
return JdbcTemplate.query( new StudentMapping(),sql);
}
看起来似乎没什么毛病了，但是如果这时我们想要查询学生数量，就只能通过泛型来解决了
IRowMapper接口
//指定泛型接口
public interface IRowMapper<T> {
//出来结果集
T mapping(ResultSet rs) throws Exception;
}
JdbcTemplate模板类
//DQL查询统一模板 <T> T 第一个<T>表明这是一个泛型方法，第二个T才是指定返回值为泛型
public static <T> T query(IRowMapper<T> rm, String sql, Object... params) {
List<Student> list = new ArrayList<>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps=conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
Object param = params[i];
ps.setObject(i+1, param);
}
rs=ps.executeQuery();
return rm.mapping(rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closedResource(rs,ps,conn);
}
return null;
}
JDBC 3.0
经过上面的各种抽象，3.0版本也基本差不多了，看一下现在的客户端调用代码
public class StudentDao3 {
public void save(Student stu) {
String sql = "insert into n_student(age,name) values(?,?) ";
Object params[] = new Object[]{stu.getAge(), stu.getName()};
JdbcTemplate.DMLOperator(sql,params);
}
public void delete(Student stu) {
String sql = "delete from n_student where name =?";
Object[] params = new Object[]{stu.getName()};
JdbcTemplate.DMLOperator(sql, params);
}
public Student selectOne(Student stu) {
String sql = "select * from n_student where name =?";
Object params[] = new Object[]{stu.getName()};
List<Student> list = JdbcTemplate.query( new StudentMapping(),sql, params);
return list.get(0);
}
public List<Student> selectAll() {
String sql = "select*from n_student";
return JdbcTemplate.query( new StudentMapping(),sql);
}
public Long getCount() {
String sql = "select count(0) as total from n_student";
Long total = JdbcTemplate.query(new IRowMapper<Long>() {
@Override
public Long mapping(ResultSet rs) throws Exception {
long count = 0L;
if (rs.next()) {
count= rs.getLong("total");
}
return count;
}
}, sql);
return total;
}
}
扫一扫
1352
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
