文章目录
SQL进阶
动态查询： 根据用户给定的条件来决定执行什么样的查询。
代码
/**
* 根据用户提供的数据进行查询
* @param departments 封装查询数据的departments对象
* @return 返回查询到的departments对象容器
*/
private static List<Departments> selectDepartmentsByProperty(Departments departments) {
Connection conn = null;
Statement statement = null;
List<Departments> list = new ArrayList<>();
ResultSet resultSet = null;
try {
conn = DBUtil.getConnection();
statement = conn.createStatement();
String sql = genSql(departments);
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
Departments dept = new Departments();
dept = new Departments();
dept.setDepartmentId(resultSet.getInt(1));
dept.setDepartmentName(resultSet.getString(2));
dept.setManagerId(resultSet.getInt(3));
dept.setLocationId(resultSet.getInt(4));
list.add(dept);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(resultSet, statement, conn);
}
return list;
}
/**
* 根据departments对象生成sql查询语句
* @param departments 封装用户查询数据的departments对象
* @return 返回生成的sql查询语句
*/
private static String genSql(Departments departments) {
StringBuilder sql = new StringBuilder("select * from departments where 1=1");
if (departments.getDepartmentId() != null && departments.getDepartmentId() > 0) {
sql.append(" and department_id = ").append(departments.getDepartmentId());
}
if (departments.getDepartmentName() != null) {
sql.append(" and department_name = ").append(departments.getDepartmentName());
}
if (departments.getLocationId() != null && departments.getLocationId() != 0) {
sql.append(" and location_id = ").append(departments.getLocationId());
}
if (departments.getManagerId() != null && departments.getManagerId() != 0) {
sql.append(" and manager_id = ").append(departments.getManagerId());
}
System.out.println("sql = " + sql);
return sql.toString();
}
四: 程序的分层使用
1. 什么是应用分层
应用程序通过创建不同的包来实现项目的分层, 将项目中的代码根据功能做具体划分, 并放在不同的包下.
2. 分层的优点
- 分层结构将应用系统划分为若干层， 每一层只解决问题的一部分， 通过各层的协作
提供整体解决方案。 大的问题被分解为一系列相对独立的子问题， 局部化在每一层中， 这样
就有效的降低了单个问题的规模和复杂度， 实现了复杂系统的第一步也是最为关键的一步分
解。 - 分层结构具有良好的可扩展性， 为应用系统的演化增长提供了一个灵活的支持， 具
有良好的可扩展性。 增加新的功能时， 无须对现有的代码做修改， 业务逻辑可以得到最大限
度的重用。 - 分层架构易于维护。 在对系统进行分解后， 不同的功能被封装在不同的层中， 层与
层之间的耦合显著降低。 因此在修改某个层的代码时， 只要不涉及层与层之间的接口， 就不
会对其他层造成严重影响
3. 三层结构
三层结构就是将整个业务应用划分为: 界面层(User Interface lagyer), 业务逻辑层(business logic Layer), 数据访问层(Date access layer). 区分层次的目的即为了"高内聚, 低耦合"的思想. 在软件体系架构的设计中, 分层结构式最常见, 也是最重要的一种结构.
4. 项目分层图解
5. 分层代码
模型层代码
package pers.jssd.jdbc.pojo;
/**
* @author jssdjing@gmail.com
* @create 2019/8/8 15:20
*/
public class Departments {
private Integer departmentId;
private String departmentName;
private Integer managerId;
private Integer locationId;
public Departments() {
}
public Departments(Integer departmentId, String departmentName, Integer managerId, Integer locationId) {
this.departmentId = departmentId;
this.departmentName = departmentName;
this.managerId = managerId;
this.locationId = locationId;
}
public Integer getDepartmentId() {
return departmentId;
}
public void setDepartmentId(Integer departmentId) {
this.departmentId = departmentId;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
public Integer getManagerId() {
return managerId;
}
public void setManagerId(Integer managerId) {
this.managerId = managerId;
}
public Integer getLocationId() {
return locationId;
}
public void setLocationId(Integer locationId) {
this.locationId = locationId;
}
@Override
public String toString() {
return "Departments{" +
"departmentId=" + departmentId +
", departmentName='" + departmentName + '\'' +
", managerId=" + managerId +
", locationId=" + locationId +
'}';
}
}
工具层代码
package pers.jssd.jdbc.commons;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;
/**
* 数据库连接对象
*
* @author jssd
*/
public class DBUtil {
private static String URL;
private static String USER;
private static String PWD;
static {
ResourceBundle db = ResourceBundle.getBundle("db");
String DRIVER = db.getString("driver");
URL = db.getString("url");
USER = db.getString("user");
PWD = db.getString("pwd");
// 注册驱动
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 取得连接对象
*
* @return 返回连接对象
*/
public static Connection getConnection() {
Connection con = null;
// 取得数据库连接
try {
con = DriverManager.getConnection(URL, USER, PWD);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
/**
* 关闭资源
*
* @param closeable 实现了AutoCloseable的可关闭变量
*/
public static void close(AutoCloseable... closeable) {
for (AutoCloseable autoCloseable : closeable) {
try {
if (autoCloseable != null) {
autoCloseable.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void rollback(Connection conn) {
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
持久层代码
接口:
package pers.jssd.jdbc.dao;
import pers.jssd.jdbc.pojo.Departments;
import java.util.List;
/**
* Departments表的持久层接口
*
* @author jssdjing@gmail.com
*/
public interface DepartmentsDao {
/**
* 通过departments_name查找, 返回一个存储查找数据的容器
*
* @param DepartmentName 查找的DepartmentsName
* @return 返回存储查找数据的容器, 如果没有找到, 则容器size为0
*/
List<Departments> selectByDepartmentsName(String DepartmentName);
/**
* 向Departments表中添加数据
*
* @param departments 存储添加数据的departments表
*/
void InsertDepartments(Departments departments);
}
实现类
package pers.jssd.jdbc.dao.impl;
import pers.jssd.jdbc.commons.DBUtil;
import pers.jssd.jdbc.dao.DepartmentsDao;
import pers.jssd.jdbc.pojo.Departments;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* Departments 持久层的实现类
*
* @author jssdjing@gmail.com
*/
public class DepartmentsDaoImpl implements DepartmentsDao {
@Override
public List<Departments> selectByDepartmentsName(String departmentName) {
List<Departments> list = new ArrayList<>();
Connection conn = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
String sql = "select * from departments d where d.department_name = ?";
try {
conn = DBUtil.getConnection();
statement = conn.prepareStatement(sql);
statement.setString(1, departmentName);
resultSet = statement.executeQuery();
while (resultSet.next()) {
Departments d = new Departments();
d.setDepartmentId(resultSet.getInt(1));
d.setDepartmentName(resultSet.getString(2));
d.setManagerId(resultSet.getInt(3));
d.setLocationId(resultSet.getInt(4));
list.add(d);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(resultSet, statement, conn);
}
return list;
}
@Override
public void InsertDepartments(Departments departments) {
Connection conn = null;
PreparedStatement statement = null;
String sql = "insert into departments (department_id, department_name, manager_id, location_id) values (default, ?, ?, ?);";
try {
conn = DBUtil.getConnection();
conn.setAutoCommit(false);
statement = conn.prepareStatement(sql);
statement.setString(1, departments.getDepartmentName());
statement.setInt(2, 201);
statement.setInt(3, 1800);
statement.execute();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
DBUtil.rollback(conn);
} finally {
DBUtil.close(statement, conn);
}
}
}
服务层代码
接口:
package pers.jssd.jdbc.service;
import pers.jssd.jdbc.pojo.Departments;
/**
* @author jssdjing@gmail.com
*/
public interface DepartmentsService {
/**
* 添加一个departments
* @param departments 要添加的departments
*/
void addDepartments(Departments departments);
}
实现代码
package pers.jssd.jdbc.service.impl;
import pers.jssd.jdbc.dao.DepartmentsDao;
import pers.jssd.jdbc.dao.impl.DepartmentsDaoImpl;
import pers.jssd.jdbc.pojo.Departments;
import pers.jssd.jdbc.service.DepartmentsService;
/**
* @author jssdjing@gmail.com
*/
public class DepartmentsServiceImpl implements DepartmentsService {
@Override
public void addDepartments(Departments departments) {
DepartmentsDao departmentsDao = new DepartmentsDaoImpl();
departmentsDao.InsertDepartments(departments);
}
}
测试层代码
package pers.jssd.jdbc.test;
import pers.jssd.jdbc.pojo.Departments;
import pers.jssd.jdbc.service.DepartmentsService;
import pers.jssd.jdbc.service.impl.DepartmentsServiceImpl;
/**
* @author jssdjing@gmail.com
*/
public class TestAddDepartments {
public static void main(String[] args) {
DepartmentsService departmentsService = new DepartmentsServiceImpl();
Departments departments = new Departments(0, "开发部", 0, 0);
departmentsService.addDepartments(departments);
}
}
6. 使用BaseDao. 通用SQL模板
新建立一个接口
package pers.jssd.jdbc.dao;
import java.sql.ResultSet;
import java.util.List;
/**
* @author jssdjing@gmail.com
*/
public interface BaseDao {
/**
* 通用DML执行语句
*
* @param sql 执行的sql语言
* @param os 执行的参数数组
* @return 返回执行成功的条数, 不成功事务会回滚, 返回0条
*/
int executeUpdate(String sql, Object... os);
/**
* 通用DQL执行语句
*
* @param sql DQL查询sql
* @param clz List存储的内容class对象
* @param os 执行查询的参数数组
* @param <T> 泛型方法, 传递List存储的内容
* @return 返回执行成功后存储对象的list
*/
<T> List<T> executeQuery(String sql, Class<T> clz, Object... os);
}
实现这个接口
package pers.jssd.jdbc.dao.impl;
import org.apache.commons.beanutils.BeanUtils;
import pers.jssd.jdbc.commons.DBUtil;
import pers.jssd.jdbc.dao.BaseDao;
import pers.jssd.jdbc.pojo.Dept;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @author jssdjing@gmail.com
*/
public class BaseDaoImpl implements BaseDao {
@Override
public int executeUpdate(String sql, Object... os) {
Connection conn = null;
PreparedStatement statement = null;
int count = 0;
try {
conn = DBUtil.getConnection();
conn.setAutoCommit(false);
statement = conn.prepareStatement(sql);
// 取得statement执行的sql语句中查询了多少列
ParameterMetaData metaData = statement.getParameterMetaData();
for (int i = 0; i < metaData.getParameterCount(); i++) {
statement.setObject(i + 1, os[i]);
}
count = statement.executeUpdate();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
DBUtil.rollback(conn);
} finally {
DBUtil.close(statement, conn);
}
return count;
}
@Override
public <T> List<T> executeQuery(String sql, Class<T> clz, Object... os) {
List<T> list = new ArrayList<>();
Connection conn = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
conn = DBUtil.getConnection();
statement = conn.prepareStatement(sql);
ParameterMetaData parameterMetaData = statement.getParameterMetaData();
for (int i = 0; i < parameterMetaData.getParameterCount(); i++) {
statement.setObject(i + 1, os[i]);
}
resultSet = statement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
while (resultSet.next()) {
T t = clz.newInstance();
for (int i = 0; i < metaData.getColumnCount(); i++) {
String columnName = metaData.getColumnName(i + 1);
setValueInObject(t, columnName, resultSet.getObject(i + 1), Class.forName(metaData.getColumnClassName(i + 1)));
//BeanUtils.setProperty(t, columnName, resultSet.getObject(i + 1));
}
list.add(t);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(resultSet, statement, conn);
}
return list;
}
/**
* 和BeanUtils一样的效果, 通过反射给对象的属性符值
*/
private <T> void setValueInObject(T obj, String columnName, Object value, Class<?> columnClassType) {
Method method;
try {
method = obj.getClass().getDeclaredMethod("set" + upperFirstLetter(columnName), columnClassType);
method.invoke(obj, value);
} catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
e.printStackTrace();
}
}
/**
* 首字母大写
*/
private String upperFirstLetter(String name) {
char[] cs = name.toCharArray();
cs[0] -= 32;
return String.valueOf(cs);
}
public static void main(String[] args) {
String sql = "insert into departments (department_id, department_name, manager_id, location_id) values (default, ?, ?, ?)";
String sql2 = "select * from departments d where d.department_name like ?";
BaseDaoImpl dao = new BaseDaoImpl();
/* int i = dao.executeUpdate(sql, "调研部2", 204, 2700);
System.out.println("\"执行了: \" + i + \"次\" = " + "执行了: " + i + "次");*/
List<Dept> depts = dao.executeQuery(sql2, Dept.class, "%研发%");
for (Dept dept : depts) {
System.out.println("dept = " + dept);
}
}
}
DepartmentsDaoImol继承此类
package pers.jssd.jdbc.dao.impl;
import pers.jssd.jdbc.dao.DepartmentsDao;
import pers.jssd.jdbc.pojo.Departments;
import pers.jssd.jdbc.pojo.Dept;
import java.util.List;
/**
* Departments 持久层的实现类
*
* @author jssdjing@gmail.com
*/
public class DepartmentsDaoImpl extends BaseDaoImpl implements DepartmentsDao {
@Override
public List<Dept> selectByDepartmentsName(String departmentName) {
String sql = "select * from departments where department_name = ?";
return this.executeQuery(sql, Dept.class, departmentName);
}
@Override
public void InsertDepartments(Departments departments) {
// 插入Departments表的sql语句
String sql = "insert into departments (department_id, department_name, manager_id, location_id) values (default, ?, ?, ?);";
Object[] os = new Object[]{departments.getDepartmentName(), departments.getManagerId(), departments.getLocationId()};
int count = this.executeUpdate(sql, os);
System.out.println("插入执行成功, 共插入了" + count + "条");
}
}
实体类必须和表名一致
package pers.jssd.jdbc.pojo;
/**
* @author jssdjing@gmail.com
*/
public class Dept {
private Integer department_id;
private String department_name;
private Integer manager_id;
private Integer location_id;
public Dept() {
}
public Dept(Integer department_id, String department_name, Integer manager_id, Integer location_id) {
this.department_id = department_id;
this.department_name = department_name;
this.manager_id = manager_id;
this.location_id = location_id;
}
@Override
public String toString() {
return "Dept{" +
"department_id=" + department_id +
", department_name='" + department_name + '\'' +
", manager_id=" + manager_id +
", location_id=" + location_id +
'}';
}
public Integer getDepartment_id() {
return department_id;
}
public void setDepartment_id(Integer department_id) {
this.department_id = department_id;
}
public String getDepartment_name() {
return department_name;
}
public void setDepartment_name(String department_name) {
this.department_name = department_name;
}
public Integer getManager_id() {
return manager_id;
}
public void setManager_id(Integer manager_id) {
this.manager_id = manager_id;
}
public Integer getLocation_id() {
return location_id;
}
public void setLocation_id(Integer location_id) {
this.location_id = location_id;
}
}