参考视频哔哩哔哩
1、Service和Servicelmpl的概念
java中
service和servicelmpl是常见的代码组织方式
- Service是指业务逻辑的接口,定义了系统对外提供的功能。Servicelmpl是Service接口的具体实现,实现了具体的业务逻辑。
Service和Servicelmpl的好处
使用Service和Servicelmpl的方式可以带来以下好处:
-
代码分层清晰:通过将业务逻辑抽象为接口和实现类的方式,可以将不同层次的代码分离,提高代码的可读性和可维护性
-
便于扩展和测试:由于业务逻辑被封装在Service中,当需要添加新的功能时,只需要在Service接口中添加方法,并在Servicelmpl中实现即可。同时,由于业务逻辑和其他层解耦,可以方便地进行单元测试。
-
支持事务管理:在实际开发中,往往需要对一组操作进行事务管理。Service的设计可以方便地实现对一组操作的事务管理,保证数据的一致性。
2、DAO(Data Access Object Layer)
DAO层(Data Access Object Layer)是软件开发中的一种设计模式,主要用于将应用程序的业务逻辑与数据访问逻辑分离。DAO层提供了一种抽象的方式来处理数据库操作,使得数据存取和业务逻辑之间的解耦更为清晰。
2、1 DAO层的特点
- 职责明确:DAO层专注于数据的持久化与访问,不涉及业务逻辑。
- 抽象化:通过接口或类对数据访问进行封装,隐藏具体的数据访问实现细节,比如使用何种数据库、如何执行SQL语句等。
- 重用性:可以在多个地方复用相同的数据库访问代码,提高代码的一致性和可维护性。
- 易于测试:由于数据访问逻辑被封装,可以方便地使用模拟对象进行单元测试。
2、2 DAO层的组成
- DAO接口:定义了数据访问的方法,如增、删、改、查等。
- DAO实现类:实现具体的DAO接口,并包含与数据库交互的代码。
- VO(Value Object)/DTO(Data Transfer Object):用于在DAO层与其他层之间传递数据的对象。
3、相关代码的实现:
3、1 链接数据库(逻辑和界面未分离):
1、加载驱动
2、创建连接
3、sql预编译
4、执行sql语句
5、关闭连接
package DBtest;import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;public class jdbctext {private static String URL;private static String USER;private static String PASSWORD;private static String DRIVER;static {try {InputStream in = DbUutil.class.getResourceAsStream("DbUtil.properties");Properties prop = new Properties();prop.load(in);DRIVER = prop.getProperty("driver");URL = prop.getProperty("url");USER = prop.getProperty("user");PASSWORD = prop.getProperty("password");} catch (IOException e) {e.printStackTrace();}}public static Connection getConn() throws SQLException {Connection conn = null;try {conn = DriverManager.getConnection(URL, USER, PASSWORD);} catch (SQLException e) {e.printStackTrace();}return conn;}public static int update(String sql, Object... params) throws SQLException {Connection conn = getConn();PreparedStatement ps = null;try {ps = conn.prepareStatement(sql);setParams(ps, params);return ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {closeAll(conn, ps, null);}return -1;}public static void closeAll(Connection conn, PreparedStatement ps, ResultSet rs) {try {if (ps != null) {ps.close();}if (rs != null) {rs.close();}if (conn != null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}public static void setParams(PreparedStatement ps, Object... params) throws SQLException {if (params != null && params.length > 0) {for (int i = 0; i < params.length; i++) {ps.setObject(i + 1, params[i]);}}}public static List<List> queryList(String sql, Object... params) throws SQLException {Connection conn = getConn();PreparedStatement ps = null;ResultSet rs = null;List<List> lists = new ArrayList<>();try {ps = conn.prepareStatement(sql);setParams(ps, params);rs = ps.executeQuery();ResultSetMetaData metaData = rs.getMetaData();while (rs.next()) {List<Object> list = new ArrayList<>();for (int i = 0; i < metaData.getColumnCount(); i++) {list.add(rs.getObject(i + 1));}lists.add(list);}return lists;} catch (SQLException e) {e.printStackTrace();} finally {closeAll(conn, ps, rs);}return null;}public static List<Map<String, Object>> queryMap(String sql, Object... params) throws SQLException {Connection conn = getConn();PreparedStatement ps = null;ResultSet rs = null;List<Map<String, Object>> maps = new ArrayList<>();try {ps = conn.prepareStatement(sql);setParams(ps, params);rs = ps.executeQuery();ResultSetMetaData metaData = rs.getMetaData();while (rs.next()) {Map<String, Object> map = new HashMap<>();for (int i = 0; i < metaData.getColumnCount(); i++) {map.put(metaData.getColumnLabel(i + 1), rs.getObject(i + 1));}maps.add(map);}return maps;} catch (SQLException e) {e.printStackTrace();} finally {closeAll(conn, ps, rs);}return null;}public static int getPrimaryKey(String sql, Object... params) throws SQLException {Connection conn = getConn();PreparedStatement ps = null;ResultSet rs = null;try {ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);setParams(ps, params);ps.executeUpdate();rs = ps.getGeneratedKeys();if (rs.next()) {return rs.getInt(1);}} catch (SQLException e) {e.printStackTrace();} finally {closeAll(conn, ps, rs);}return -1;}
}
4、业务逻辑分离
4、1 加载配置文件
public class jdbctext {// Connection con;private static String URL;private static String USER;private static String PASSWORD;private static String DRIVER;static {try {InputStream in = DbUutil.class.getResourceAsStream("DbUtil.properties");Properties prop = new Properties();prop.load(in);DRIVER =prop.getProperty("driver");URL = prop.getProperty("url");USER = prop.getProperty("user");PASSWORD = prop.getProperty("password");} catch (IOException e) {e.printStackTrace();}}
}
配置文件DbUtil.properties
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/contact?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8
user = root
password = 123456
4、2 封装创建连接的方法
/**
1、方法名:getConn();
2、参数列表 不需要
3、返回值 Connection conn
*/
public static Connection getConn() throws SQLException {Connection conn = null;try {conn = DriverManager.getConnection(URL, USER, PASSWORD);} catch (SQLException e) {e.printStackTrace();// throw new RuntimeException(e); // Uncomment if you want to rethrow the exception}return conn;
}
4、3 通用增删改查操作
1、方法名:update()
2、参数列表:
(1)sql语句 参数
(2)sql ?的占位符,所代表的参数
3、返回值:返回增删改查的结果,成功还是失败,返回值是成功改变数据的行数。
public static int update(String sql, Object... params) throws SQLException {// 调用上面封装好的,得到conn连接Connection conn = getConn();PreparedStatement ps = null;try {ps = conn.prepareStatement(sql);// 调用通用的设置sql语句的方法,向sql中设置参数setParams(ps, params);// 执行sql语句int i = ps.executeUpdate();return i;} catch (SQLException e) {e.printStackTrace();} finally {// 调用通用的关闭方法closeAll(conn, ps, null);}return -1; // 在异常情况下返回 -1
}
4、4 封装通用的关闭的方法:
1、方法名 closeAll()
2、参数列表:
ResultSet set;PrearedlStatement ps;Connection conn
3、返回值 :不需要返回值
public static void closeAll(Connection conn, PreparedStatement ps, ResultSet rs) {// Close PreparedStatementtry {if (ps != null) {ps.close();}} catch (SQLException e) {e.printStackTrace();}// Close ResultSettry {if (rs != null) {rs.close();}} catch (SQLException e) {e.printStackTrace();}// Close Connectiontry {if (conn != null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}
}
4、5 封装通用查询方法(List)
/**
*封装通用的查询方法
*需要做数据的转储:目的是能够把查询的出的数据 在程序中别的地方使用。
*可以转储两种形式:
* List
* List
public static List<List> queryList(String sql, Object... params) throws SQLException {// Establish the connectionConnection conn = getConn();PreparedStatement ps = null;ResultSet rs = null;try {// Prepare the SQL statementps = conn.prepareStatement(sql);// Set parameters for the prepared statementsetParams(ps, params);// Execute the SQL queryrs = ps.executeQuery();// Retrieve metadata about the result setResultSetMetaData metaData = rs.getMetaData();// Create a list to hold all rows of dataList<List<Object>> lists = new ArrayList<>();// Process each row in the result setwhile (rs.next()) {// Create a list to hold data for the current rowList<Object> list = new ArrayList<>();// Populate the row list with data from the result setfor (int i = 0; i < metaData.getColumnCount(); i++) {list.add(rs.getObject(i + 1));}// Add the current row list to the main listlists.add(list);}return lists;} catch (SQLException e) {e.printStackTrace();} finally {// Ensure all resources are closedcloseAll(conn, ps, rs);}return null;
}
4、6 封装通用查询方法(Map)
/**
* 封装List<>map<String,Object>类型
* 方法的三要素:
* 1、方法名 queryMap()
* 2、参数列表 sql,Object …
* 3、返回值 如果没有查到数据 就返回NUll
*/
public static List<Map<String, Object>> queryMap(String sql, Object... params) throws SQLException {// Establish the connectionConnection conn = getConn();PreparedStatement ps = null;ResultSet rs = null;try {// Prepare the SQL statementps = conn.prepareStatement(sql);// Set parameters for the prepared statementsetParams(ps, params);// Execute the SQL queryrs = ps.executeQuery();// Create a list to hold the maps of each rowList<Map<String, Object>> maps = new ArrayList<>();// Retrieve metadata about the result setResultSetMetaData metaData = rs.getMetaData();// Process each row in the result setwhile (rs.next()) {// Create a map to hold data for the current rowMap<String, Object> map = new HashMap<>();// Populate the map with column labels and values from the result setfor (int i = 0; i < metaData.getColumnCount(); i++) {map.put(metaData.getColumnLabel(i + 1), rs.getObject(i + 1));}// Add the current row map to the main listmaps.add(map);}return maps;} catch (SQLException e) {e.printStackTrace();// Optionally throw a runtime exception if needed// throw new RuntimeException(e);} finally {// Ensure all resources are closedcloseAll(conn, ps, rs);}return null;
}
4、7 新增时返回自增主键的方法
/**
* 新增时返回自增主键的方法
* 1、方法名 getParmaryKey()
* 2、参数列表 String sql, Object …params
* 3、返回值 int
*/
public static int getPrimaryKey(String sql, Object... params) throws SQLException {// Establish the connectionConnection conn = getConn();PreparedStatement ps = null;ResultSet rs = null;try {// Prepare the SQL statement to return generated keysps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);// Set parameters for the prepared statementsetParams(ps, params);// Execute the update SQL statementps.executeUpdate();// Retrieve the generated keys (primary key)rs = ps.getGeneratedKeys();// Check if the result set has a row and return the primary keyif (rs.next()) {return rs.getInt(1);}} catch (SQLException e) {e.printStackTrace();} finally {// Ensure all resources are closedcloseAll(conn, ps, rs);}// Return -1 if no primary key was generatedreturn -1;
}
5、测试及结果:
package DBtest;import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Objects;public class DBtext {public static void main(String[] args) throws SQLException {//测试封装的增删改方法String sql ="insert into text values(?,?,null)";// String sql ="select *from text";// List<Map<String, Object>> list= jdbctext.queryMap(sql);Object[] parms = { "活着","23"};// Object[] parms=null; 这里是没传入参数// int i= jdbctext.update(sql,parms);int key= jdbctext.getParmaryKey(sql,parms);System.out.println(key);// System.out.println(list);}
}