当前位置：网站首页>Native JDBC operation database
Native JDBC operation database
2022-08-09 07:37:00 【Cocoxzq000】
JDBC操纵数据库
在各种ORM框架盛行的时代,Presumably many of us have forgotten how to do it without using a framework,使用原生的JDBCcome to deal with the database,对于初级程序员来说,使用原生的JDBCCoding can give us a more comprehensive understanding of the underlying operation logic of the framework,In native code, we also need to encapsulate some tools,Use some design patterns to simplify our steps in manipulating the database,In this step-by-step encapsulation process, our programming ideas can be improved,So understand the originalJDBCManipulating the database is also a must,Next, let us start with a most originalJDBCFull steps to implement your own encapsulated tool classes and templates,话不多说直接上代码!
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,Get statement session object,Here we get the precompiled processing session,可以防止SQL注入
ps = conn.prepareStatement(sql);
//给占位符? 赋值,setObject() The first parameter is the placeholder position,The second parameter to the actual incoming values
ps.setObject(1, "小虎");
//Execute the statement to get the return value
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
Next we will use the nativeJDBC模拟真实开发场景,We are now going to add, delete, modify, and query the student table.
学生表
@Data
public class Student {
private Integer age;
private String name;
public Student(Integer age, String name) {
this.age = age;
this.name = name;
}
}
Dao
/** * The traditional controljdbc接口方式 */
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();
}
}
}
}
}
We found from the above code,daoThere is a lot of redundant code in,不利于开发,也不利于维护,So we can extract these repeated codes into a tool classJdbcUtils
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();
}
}
}
}
}
使用我们的jdbcUtilsuse againdao进行改造
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;
/** * The traditional controljdbc接口方式 */
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;
}
}
It looks like our code does introduce a lot,但仔细观察,It can be found that there is still a lot of redundant code in the code,e.g. get connection,关闭连接,构建返回值,DMLThe only difference isSQL语句和参数,
We can create a template classJdbcTemplate,创建一个DML和DQLTemplate to refactor
模板类
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;
}
//DMLOperation Unified Template
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);
}
}
}
This kind of repetitive code is basically dealt with,But there is a very serious problem,这个程序的DQL操作中只能处理Student类和n_student表相关数据,Cannot process other data,Different tables have different columns,The code for different processing result sets is also different,Operations that process result sets only have the callerDao知道,So we shouldn't put the method that handles the result in the template method
应该由每个DAO自己来处理,因此我们可以创建一个IRowMapperThe interface handles the result set
IRowMapper接口
package com.xzq.util;
import java.sql.ResultSet;
import java.util.List;
public interface IRowMapper {
//处理结果集
List mapping(ResultSet rs) throws Exception;
}
实现类自己去实现IRowMapper接口的 mapping方法,What type of data you want to process can be defined in it
Student result set processing class
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;
}
}
模板类,Passed through a high-level callIRowMapperInterface implementation for result set mapping
//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);
}
Seems to be alright,But if we want to query the number of students at this time,It can only be solved by generics
IRowMapper接口
//Specify a generic interface
public interface IRowMapper<T> {
//out result set
T mapping(ResultSet rs) throws Exception;
}
JdbcTemplate模板类
//DQL查询统一模板 <T> T 第一个<T>表明这是一个泛型方法,第二个Tis to specify that the return value is a generic
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
After various abstractions above,3.0The version is almost the same,Now look at the client calling code
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;
}
}
总结
通过原生的JDBC编写,在1.0Version we found registered driver,获取连接,Too much redundant code to close resources,So we choose to encapsulate the tool classJdbcUtilsto abstract2.0版本,但是在2.0In the version, we found that there is still a lot of redundant code,We still have to code on the client side(Dao层)Mid-to-face contact connection,会话对象,返回结果等操作,其实对于DML只有SQLStatements and parameters are different,其他都相同,在DQL中只有SQL语句,参数,The code to handle the result set is different,So we can create a template for classDQL和DML创建模板,After creating the template,DQLIn the template with the processing result set operation,But our abstract template class is not quite a universal template,I don't want to manipulate the student table anymore,want to do to the teacherCRUD,Am I still rewriting a template?？其实,Processing of result sets,What type does the high-level call understand?,因此,Operations that process result sets should not be placed in templates,Instead, it abstracts an interface that handles result sets,Pass its concrete implementation when calling the template class.Now the template class doesn't matter what type of return set,But it doesn't work when I want to query the number,At this point is on the template classList返回类型,Compatible nowList,LongYou may need to query other types of fields in the future.,We can't write the return type to death,Who should decide the return type?？There should be a top the caller is the client（Dao）来决定,Hence the need for generics 了,The interface where we will handle the result setIRowMapperConverted into a generic interface,Specify the generic type at client invocation,In the template class into a generic method,In this way, you only need to specify the type of the returned result when the client calls it,After this step-by-step operation,Finally got the final version,Actually, it's not too convenient,Can we abstract when encapsulating the result set?？In fact, it can be solved by reflection,在讲到ORMFramework to say again.边栏推荐
- 数据库索引原理
- 力扣208，实现Trie(前缀树)
- LeetCode：876. 链表的中间结点————简单
- 【sqlite3】sqlite3.OperationalError: table addresses has 7 columns but 6 values were supplied
- MVN 中配置flyway mysq
- composer 内存不足够
- 【报错】Root Cause com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
- Rsync常见错误
- 学习小笔记---机器学习
- Apache POI
猜你喜欢
【机器学习】降维代码练习
虚拟机网卡报错：Bringing up interface eth0: Error: No suitable device found: no device found for connection
SiamFC：用于目标跟踪的全卷积孪生网络 fully-convolutional siamese networks for object tracking
Colors that Tkinter can choose from
用tensorflow.keras模块化搭建神经网络模型
redis学习笔记
重要消息丨.NET Core 3.1 将于今年12月13日结束支持
RK3568商显版开源鸿蒙板卡产品解决方案
jmeter并发数量以及压力机的一些限制
【nuxt】服务器部署步骤
随机推荐
975. 奇偶跳 有序集合
Lottie系列三 ：原理分析
Learning Notes---Machine Learning
Change Jupyter Notebook default open directory
字节也开始缩招了...
JSONObject遍历的时候顺序不一致，导致数据对应出错
训练好的深度学习模型，多种部署方式
Difference Constraint - Graph Theory
排序第四节——归并排序（附有自己的视频讲解）
学习小笔记---机器学习
01 自然语言处理NLP介绍
类和结构体
unity第一课
分布式理论
Kotlin Coroutines - Exception Handling
洛谷P1110 报表统计 multiset stl好题
Use tensorflow.keras to build a neural network model modularly
力扣208，实现Trie(前缀树)
Better Scroll Y上下滚动无法上拉滚动解决办法
搭载开源鸿蒙系统的嵌入式XM-RK3568工业互联方案