接上一节老代码
准备/sql
数据表
实体类
@Data
public class Lifavo implements Serializable {
private static final long serialVersionUID = -6561420029011157773L;
/**
* 用户id
*/
private Integer uid;
/**
* 歌单id
*/
private Integer listid;
/**
* 标签id
*/
private Integer tid;
/**
* 收藏时间
*/
private Date ftime;
}
sql
select * from listfavorite inner join listtag on listtag.listid=listfavorite.listid
可以看到是有5列 但是实体类中 4 个属性 同名的待会setf方法会覆盖 因为链接条件是同值
思考
select *
并不是一个好的做法
使用给定的列替换
获取实体类属性
Class clazz = Lifavo.class;
//获取属性
Field[] declaredFields = clazz.getDeclaredFields();
for (Field declaredField : declaredFields) {
System.out.print(declaredField.getName() + "\t");
}
System.out.println();
//获取注解
Annotation[] annotations = clazz.getAnnotations();
//获取set方法
List<Method> setMethod = Arrays.stream(clazz.getDeclaredMethods()).filter(e -> e.getName().contains("set"))
.collect(Collectors.toList());
代码
核心
while (resultSet.next()) {
//创建新对象 反射的方式
Object o = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取每一列的值
Object object = resultSet.getObject(columons.get(i));
//System.out.println(object.getClass());
//根据列名 invoke 去为属性赋值
String columnName = metaData.getColumnName(i + 1);
// 根据列名获取方法 --> 如果数据库中属性名和实体类中属性名不同呢? 比如 tb_uid(数据库) uid(实体类)
// (截断 忽略tb_ 或许是一种方法) 当然肯定不会这么简单 要做映射
Method method = methodMap.get(columnName.toLowerCase());
Object invoke = method.invoke(o, object);
//System.out.println("执行"+method.getName()+"成功");
}
oList.add(o);
}
整体
public class Collection5 {
private static final Logger log = LoggerFactory.getLogger(Collection5.class);
public static void main(String[] args) throws IOException, SQLException {
Properties properties = new Properties();
//加载配置信息
properties.load(new FileInputStream("month2/src/main/resources/jdbc.properties"));
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String sql = "select * from listfavorite inner join listtag on listtag.listid=listfavorite.listid";
select(url, user, password, Lifavo.class, sql);
}
private static void select(String url, String user, String password, Class clazz, String sql) throws SQLException {
try (
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
) {
ResultSet resultSet = statement.executeQuery(sql);
ResultSetMetaData metaData = resultSet.getMetaData();
//这个结果集的列数
int columnCount = metaData.getColumnCount();
//用于存储列名
List<String> columons = new ArrayList<>();
//1 获取列名 获取这张表中所有的列名 此处基于一张表, 联表查询的思路也是大同小异
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
columons.add(columnName);
}
System.out.println(columons);
//获取setter方法
Map<String, Method> methodMap = getSetterMethodMap(clazz);
//使用反射生成
List oList = getObjectList(clazz, resultSet, columnCount, columons, metaData, methodMap);
oList.forEach(System.out::println);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
}
}
private static List getObjectList(Class clazz, ResultSet resultSet, int columnCount, List<String> columons, ResultSetMetaData metaData, Map<String, Method> methodMap) throws SQLException, InstantiationException, IllegalAccessException, InvocationTargetException {
//3 从数据库读到的结果集中循环读取 使用invoke调用方法设置属性 然后将新对象缓存
List oList = new ArrayList<>();
while (resultSet.next()) {
//创建新对象 反射的方式
Object o = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取每一列的值
Object object = resultSet.getObject(columons.get(i));
//System.out.println(object.getClass());
//根据列名 invoke 去为属性赋值
String columnName = metaData.getColumnName(i + 1);
// 根据列名获取方法 --> 如果数据库中属性名和实体类中属性名不同呢? 比如 tb_uid(数据库) uid(实体类)
// (截断 忽略tb_ 或许是一种方法) 当然肯定不会这么简单 要做映射
Method method = methodMap.get(columnName.toLowerCase());
Object invoke = method.invoke(o, object);
//System.out.println("执行"+method.getName()+"成功");
}
oList.add(o);
}
return oList;
}
private static Map<String, Method> getSetterMethodMap(Class clazz) {
// 2 获取方法映射 map<方法名,方法> 从传入的 clzz 也就是字节码获取所有的setter 方法
// 获取了set方法
List<Method> setMethod = Arrays.stream(clazz.getDeclaredMethods()).filter(e -> e.getName().contains("set"))
.collect(Collectors.toList());
Map<String, Method> methodMap = new HashMap<>();
setMethod.forEach(e -> methodMap.put(e.getName().substring(3).toLowerCase(), e));
return methodMap;
}
}
优化
public class Util<T> {
private static Properties properties;
private static String url;
private static String user;
private static String password;
private static Connection connection;
private static Statement statement;
public Util(String propertiespath) throws IOException, SQLException {
properties = new Properties();
properties.load(new FileInputStream("month2/src/main/resources/jdbc.properties"));
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
}
public List<T> select(Class clazz, String sql) throws SQLException {
try {
ResultSet resultSet = statement.executeQuery(sql);
ResultSetMetaData metaData = resultSet.getMetaData();
//这个结果集的列数
//用于存储列名
List<String> columons = getClazzField(clazz);
int columnCount = columons.size();
//获取setter方法
Map<String, Method> methodMap = getSetterMethodMap(clazz);
//使用反射生成
List oList = getObjectList(clazz, resultSet, columnCount, columons, metaData, methodMap);
return oList;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
// 扫描类的属性
private static List<String> getClazzField(Class clazz) {
Field[] declaredFields = clazz.getDeclaredFields();
List<String> fieldList = Arrays.stream(declaredFields)
.filter(e -> !Objects.equals(e.getName(), "serialVersionUID"))
.map(e -> e.getName())
.collect(Collectors.toList());
return fieldList;
}
private List<T> getObjectList(Class clazz, ResultSet resultSet, int columnCount, List<String> columons, ResultSetMetaData metaData, Map<String, Method> methodMap) throws SQLException, InstantiationException, IllegalAccessException, InvocationTargetException {
//3 从数据库读到的结果集中循环读取 使用invoke调用方法设置属性 然后将新对象缓存
List oList = new ArrayList<>();
while (resultSet.next()) {
//创建新对象 反射的方式
T o = (T) clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取每一列的值
Object object = resultSet.getObject(columons.get(i));
//根据列名 invoke 去为属性赋值
String columnName = metaData.getColumnName(i + 1);
// 根据列名获取方法 --> 如果数据库中属性名和实体类中属性名不同呢? 比如 tb_uid(数据库) uid(实体类)
// (截断 忽略tb_ 或许是一种方法) 当然肯定不会这么简单 要做映射
if (methodMap.keySet().contains(columnName)) {
Method method = methodMap.get(columnName.toLowerCase());
Object invoke = method.invoke(o, object);
}
}
oList.add(o);
}
return oList;
}
private Map<String, Method> getSetterMethodMap(Class clazz) {
// 2 获取方法映射 map<方法名,方法> 从传入的 clzz 也就是字节码获取所有的setter 方法
// 获取了set方法
List<Method> setMethod = Arrays.stream(clazz.getDeclaredMethods()).filter(e -> e.getName().contains("set"))
.collect(Collectors.toList());
Map<String, Method> methodMap = new HashMap<>();
setMethod.forEach(e -> methodMap.put(e.getName().substring(3).toLowerCase(), e));
return methodMap;
}
}
表
- 根据type查询,但是只要good中的数据
sql
String sql = "select * from good inner join type on type.tid=good.tid where typename = '" + type+"'"
List select = util.select(Good.class, sql);