简易的对象映射框架
- 在使用jdbc的时候,往往都需要对数据库进行一些列的增删改查操作,但是每次使用jdbc的时候,难免不能避免使用jdbc的六大步骤:
- 1,加载驱动
- 2,获取链接
- 3,根据连接获取发送并执行SQL语句的statement对象
- 4,执行SQL语句
- 5,处理结果
- 6,关闭资源
- 因此,我们可以将CRUD中的公共部分拿出来,做一个简易的封装
public class DBUtils {
private static final String DRIVER_CLASS="com.mysql.jdbc.Driver";
private static final String URL="jdbc:mysql://127.0.0.1:3306/mydb2";
private static final String USER="root";
private static String PASSWORD= "123456";
static {
try {
Class.forName(DRIVER_CLASS);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static synchronized Connection getConn() {
try {
return DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(ResultSet rs, Statement stat, Connection conn) {
try {
if (rs != null)
rs.close();
if (stat != null)
stat.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
- 因为无论是执行 添加,修改,删除,还是查询,都会涉及到:加载驱动,获取链接以及关闭资源,所有我们将这三个步骤进行一个简易的封装,而且,在添加,修改,删除这几个步骤中,仅仅就是SQL语句的差别,所以,我们也可以进行简易的封装:
public static boolean exeUpdate(String sql, Object... params) {
Connection conn = getConn();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
return ps.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(null, ps, conn);
}
return false;
}
- 这个是对insert,delete,update的封装,在执行这些操作的时候,我们只需要传入一个SQL语句,以SQL语句中需要的参数就可以进行增,删,改操作,并返回一个Boolean类型的结果,相比于没有封装前,代码量减少了可不是一点点。
- 然后就是DQL操作的封装了,查询的操作封装相对于前面的DML来说要复杂一些,因为我们在进行查询操作之后,会返回一个结果集,这个时候,我们就需要处理这个结果集。
- 1,封装查询一条数据,并返回一个java对象(前提是必须要有一个javabean)
在封装查询语句之前,将执行SQL语句之后获得的结果集为一个map集合,我们先通过反射定义一个方法将这个map集合转化为一个java对象:
**
* 将Map集合转换为一个确定的类型
*
* @param <T>
* @param map
* @param t
* @return
*/
private static <T> T mapToBean(Map<String, Object> map, Class<T> t) {
try {
T obj = t.newInstance();
map.forEach((k, v) -> {
try {
Field field = t.getDeclaredField(k);
field.setAccessible(true);
field.set(obj, v);
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
});
return obj;
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return null;
}
public static <T> T queryOne(Class<T> t, String sql, Object... params) {
List<Map<String, Object>> list = queryMaps(sql, params);
if (list.size() > 0) {
Map<String, Object> map = list.get(0);
return mapToBean(map, t);
}
return null;
}
- 2,封装查询多条语句的查询语句,并返回一个Javabean的List集合,在这之前,我们先要将查询语句返回的结果集变为一个map集合的List集合,听起来有点绕口,通俗的来讲就是:将查询语句返回的多条数据用一个List集合装起来,而这个list集合中的元素是多个Map集合:
public static List<Map<String, Object>> queryMaps(String sql, Object... params) {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
Connection conn = getConn();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
String key = rsmd.getColumnName(i);
String label = rsmd.getColumnLabel(i);
Object value = rs.getObject(label);
if (Objects.nonNull(value)) {
map.put(key, value);
}
}
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, ps, conn);
}
return list;
}
- 然后在封装成为查询多条数据的方法:
- 大致思路就是:先查询多条数据,将它用一个List集合装起来,list集合中的元素是Map集合,然后在遍历,将每一个map集合通过mapToBean方法转化为一个Javabean,最后放回的结果就是一个List集合,集合中的元素就是一个个Javabean。
public static <T> List<T> queryList(Class<T> t, String sql, Object... params) {
List<T> list = new ArrayList<T>();
List<Map<String, Object>> maps = queryMaps(sql, params);
maps.forEach(m -> {
T obj = mapToBean(m, t);
list.add(obj);
});
return list;
}
- 3,多表联合查询方法的封装,因为多表联合查询的结果对应的Javabean在查询之前是不确定的,所以我们查询之后也就只能返回一个List集合,集合中的元素是一个个map集合,如果需要将其转换为一个Javabean,那么就需要自行先创建一个对应的Java类。
public static List<Map<String, Object>> queryMaps(String sql, Object... params) {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
Connection conn = getConn();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
String key = rsmd.getColumnName(i);
String label = rsmd.getColumnLabel(i);
Object value = rs.getObject(label);
if (Objects.nonNull(value)) {
map.put(key, value);
}
}
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, ps, conn);
}
return list;
}
以上就是对jdbc中CRUD的简易封装了,不过还没有涉及到数据库连接池以及jdbc事务的内容,所以不需要借助第三方的jar包的导入以及配置文件的书写,但是对于一些平常的jdbc需求已经是可以满足了,那么下面我将完整的ORM封装书写下来:
package com.softeem.DButils;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Properties;
import com.alibaba.druid.pool.DruidDataSource;
public class DButils {
private static String DRIVER_CLASS;
private static String URL;
private static String USER;
private static String PASSWORD;
private static int MAX_ACTIVE;
private static long MAX_WAIT;
private static int INIT_SIZE;
private static DruidDataSource ds;
static {
try {
InputStream is = new FileInputStream(new File("C:\\Users\\黄小宝\\Desktop\\jdbc.txt"));
Properties p = new Properties();
System.out.println(is);
p.load(is);
DRIVER_CLASS = p.getProperty("driver");
URL = p.getProperty("url");
USER = p.getProperty("user");
PASSWORD = p.getProperty("password");
MAX_ACTIVE = Integer.parseInt(p.getProperty("pool.maxActive"));
INIT_SIZE = Integer.parseInt(p.getProperty("pool.initSize"));
MAX_WAIT = Long.parseLong(p.getProperty("pool.maxWait"));
init();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void init() {
ds = new DruidDataSource();
ds.setDriverClassName(DRIVER_CLASS);
ds.setUrl(URL);
ds.setUsername(USER);
ds.setPassword(PASSWORD);
ds.setMaxActive(MAX_ACTIVE);
ds.setInitialSize(INIT_SIZE);
ds.setMaxWait(MAX_WAIT);
}
public static synchronized Connection getConn() {
try {
if(ds == null || ds.isClosed()) {
init();
}
return ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(ResultSet rs, Statement stat, Connection conn) {
try {
if (rs != null)
rs.close();
if (stat != null)
stat.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static boolean exeUpdate(Connection conn,String sql, Object... params) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
return ps.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(null, ps, null);
}
return false;
}
public static boolean exeUpdate(String sql, Object... params) {
Connection conn = getConn();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
return ps.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(null, ps, conn);
}
return false;
}
public static <T> T queryOne(Class<T> t, String sql, Object... params) {
List<Map<String, Object>> list = queryMaps(sql, params);
if (list.size() > 0) {
Map<String, Object> map = list.get(0);
return mapToBean(map, t);
}
return null;
}
public static <T> List<T> queryList(Class<T> t, String sql, Object... params) {
List<T> list = new ArrayList<T>();
List<Map<String, Object>> maps = queryMaps(sql, params);
maps.forEach(m -> {
T obj = mapToBean(m, t);
list.add(obj);
});
return list;
}
private static <T> T mapToBean(Map<String, Object> map, Class<T> t) {
try {
T obj = t.newInstance();
map.forEach((k, v) -> {
try {
Field field = t.getDeclaredField(k);
field.setAccessible(true);
field.set(obj, v);
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
});
return obj;
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return null;
}
public static List<Map<String, Object>> queryMaps(String sql, Object... params) {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
Connection conn = getConn();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
String key = rsmd.getColumnName(i);
String label = rsmd.getColumnLabel(i);
Object value = rs.getObject(label);
if (Objects.nonNull(value)) {
map.put(key, value);
}
}
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, ps, conn);
}
return list;
}
public static int queryCount(String sql, Object... params) {
Connection conn = getConn();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
rs = ps.executeQuery();
if(rs.next()) {
return rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(rs, ps, conn);
}
return 0;
}
}
- 在使用的时候也是非常简单的:例如我给大家做一下简单的测试:
package com.softeem.day804.Utils;
import java.util.List;
import com.softeem.day804.VO.Emp;
public class Test {
public static void main(String[] args) {
String sql = "select * from emp";
List<Emp> list = DBUtils.queryList(Emp.class, sql);
list.forEach(l->System.out.println(l));
}
}
Emp [eno=2, ename=阿卡丽, job=经理, hiredate=2017-04-01 00:00:00.0, age=31, sex=女, sal=9800.0, dno=0]
Emp [eno=3, ename=露露, job=普通员工, hiredate=2020-03-22 00:00:00.0, age=22, sex=女, sal=2200.0, dno=40]
Emp [eno=5, ename=阿木木, job=普通员工, hiredate=2017-11-22 00:00:00.0, age=27, sex=男, sal=5000.0, dno=40]
Emp [eno=6, ename=阿德, job=普通员工, hiredate=2018-08-05 00:00:00.0, age=21, sex=男, sal=3160.0, dno=30]
Emp [eno=7, ename=艾希, job=经理, hiredate=2015-03-03 00:00:00.0, age=29, sex=女, sal=10500.0, dno=0]
Emp [eno=8, ename=易大师, job=经理, hiredate=2016-09-10 00:00:00.0, age=35, sex=男, sal=8900.0, dno=30]
Emp [eno=9, ename=甄姬, job=普通员工, hiredate=2017-09-09 00:00:00.0, age=27, sex=女, sal=9900.0, dno=0]
Emp [eno=10, ename=后裔, job=普通员工, hiredate=2020-01-01 00:00:00.0, age=30, sex=男, sal=8400.0, dno=0]
Emp [eno=11, ename=猪八戒, job=普通员工, hiredate=2020-07-22 00:00:00.0, age=45, sex=男, sal=3200.0, dno=0]
Emp [eno=12, ename=廉颇, job=普通员工, hiredate=2018-07-06 00:00:00.0, age=55, sex=男, sal=6800.0, dno=40]
Emp [eno=13, ename=李青, job=普通员工, hiredate=2019-07-22 00:00:00.0, age=36, sex=男, sal=6900.0, dno=50]
Emp [eno=14, ename=李白, job=经理, hiredate=2016-01-01 00:00:00.0, age=36, sex=男, sal=16600.0, dno=40]
Emp [eno=15, ename=赵信, job=经理, hiredate=2018-09-11 00:00:00.0, age=38, sex=男, sal=7500.0, dno=50]
Emp [eno=16, ename=安妮, job=普通员工, hiredate=2019-10-11 00:00:00.0, age=18, sex=女, sal=3900.0, dno=60]
Emp [eno=17, ename=提莫, job=普通员工, hiredate=2019-10-12 00:00:00.0, age=19, sex=男, sal=5600.0, dno=60]
Emp [eno=18, ename=韦鲁斯, job=经理, hiredate=2018-05-10 00:00:00.0, age=30, sex=男, sal=7800.0, dno=60]
Emp [eno=19, ename=德莱文, job=普通员工, hiredate=2018-09-11 00:00:00.0, age=35, sex=男, sal=9000.0, dno=50]
Emp [eno=20, ename=艾维利亚, job=普通员工, hiredate=2017-06-30 00:00:00.0, age=26, sex=女, sal=2200.0, dno=30]
Emp [eno=123, ename=黄国志, job=null, hiredate=null, age=0, sex=null, sal=0.0, dno=0]