The main contents of this section are as follows:
1, Reflection writing universal insert
when using reflection to complete common database operations, we generally need to formulate some conventions. It is assumed that our database meets the following conventions during design and development:
1. The database table name and entity name meet the conversion rules between underline and hump naming.
2. Database field and entity attribute names meet the conversion rules of underline and hump naming.
3. Each table has a field called id, which is the self incremented primary key of the table.
4. The entity does not need to have an id value when adding, but it is required when modifying.
after meeting the above rules, we can write a general insertion method. This method receives an Object type of data and a database connection. The method will automatically splice SQL and insert data into the corresponding data table according to the corresponding data type.
CREATE TABLE `resources` ( `id` int(11) NOT NULL AUTO_INCREMENT, `create_time` datetime(6) DEFAULT NULL, `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `order_number` int(11) DEFAULT NULL, `update_time` datetime(6) DEFAULT NULL, `url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ); insert into `resources`(`id`,`create_time`,`name`,`order_number`,`update_time`,`url`) values (1,'2021-08-26 16:46:56.017000','User add',1,'2021-08-26 16:46:56.017000','/user/add'); insert into `resources`(`id`,`create_time`,`name`,`order_number`,`update_time`,`url`) values (2,'2021-08-26 16:47:20.476000','User modification',2,'2021-08-26 16:47:20.476000','/user/update'); insert into `resources`(`id`,`create_time`,`name`,`order_number`,`update_time`,`url`) values (3,'2021-08-26 16:47:32.253000','User query',3,'2021-08-26 16:47:32.253000','/user/*'); insert into `resources`(`id`,`create_time`,`name`,`order_number`,`update_time`,`url`) values (4,'2021-08-26 16:47:43.113000','User delete',4,'2021-08-26 16:47:43.113000','/user/delete/*'); CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `create_time` datetime(6) DEFAULT NULL, `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `remark` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `update_time` datetime(6) DEFAULT NULL, PRIMARY KEY (`id`) ); insert into `role`(`id`,`create_time`,`name`,`remark`,`update_time`) values (1,'2021-08-26 16:40:16.035000','Super administrator','Have full permissions','2021-08-26 16:40:16.036000'); insert into `role`(`id`,`create_time`,`name`,`remark`,`update_time`) values (2,'2021-08-26 16:40:40.854000','Ordinary users','Just look','2021-08-26 16:40:40.854000');
Entities: Role
public class Role {
private Integer id;
private Date createTime;
private String name;
private String remark;
private Date updateTime;
}
Entity: Resources
public class Resources {
private Integer id;
private Date createTime;
private String name;
private Integer orderNumber;
private Date updateTime;
private String url;
}
String tool class: StringUtil
public class StringUtil {
/**
* Hump turn
* @param camelCaseName Hump string
* @return Underscore string
*/
public static String underscoreName(String camelCaseName) {
StringBuffer result = new StringBuffer();
if (camelCaseName != null && camelCaseName.length() > 0) {
result.append(camelCaseName.substring(0, 1).toLowerCase());
//Get the first letter and turn it directly to lowercase
for (int i = 1; i < camelCaseName.length(); i++) {
char ch = camelCaseName.charAt(i);
//Take the character at position i
if (Character.isUpperCase(ch)) {//Determine whether the character is a capital letter
result.append("_");//If uppercase, underline is spliced
result.append(Character.toLowerCase(ch));
//Turn yourself into lowercase and add result
} else {
result.append(ch);
}
}
}
return result.toString();
}
}
code:
public static void main(String[] args) throws IllegalAccessException, SQLException {
Connection connection = DataSource.getConnection();
Role role = new Role();
role.setName("tourist");
role.setRemark("tourist");
role.setCreateTime(new Date());
role.setUpdateTime(new Date());
boolean ok = save(connection, role);
}
private static boolean save(Connection connection, Object object) throws IllegalAccessException, SQLException {
Class<?> clazz = object.getClass();
StringBuffer stringBuffer = new StringBuffer("insert into `" + StringUtil.underscoreName(clazz.getSimpleName()) + "`(");
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
if (!field.getName().equals("id")) {
stringBuffer.append("`" + StringUtil.underscoreName(field.getName()) + "`,");
}
}
stringBuffer.deleteCharAt(stringBuffer.length() - 1);//Delete last comma
stringBuffer.append(") values (");
for (Field field : fields) {
if (!field.getName().equals("id")) {
stringBuffer.append("?,");
}
}
stringBuffer.deleteCharAt(stringBuffer.length() - 1);//Delete last comma
stringBuffer.append(")");
System.out.println(stringBuffer);
PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
for (int i = 0; i < fields.length; i++) {
if (!fields[i].getName().equals("id")) {
fields[i].setAccessible(true);
Object value = fields[i].get(object);
System.out.println(i + ":" + value);
statement.setObject(i, value);
}
}
int i = statement.executeUpdate();
statement.close();
return i == 1;
}
2, Reflection writing general modification
reflection modifies all attributes according to the id.
public static void main(String[] args) throws IllegalAccessException, SQLException, NoSuchFieldException {
Connection connection = DataSource.getConnection();
Role role = new Role();
role.setId(1);
role.setName("tourist");
role.setRemark("tourist");
role.setCreateTime(new Date());
role.setUpdateTime(new Date());
boolean ok = update(connection, role);
}
private static boolean update(Connection connection, Object object) throws IllegalAccessException, SQLException, NoSuchFieldException {
Class<?> clazz = object.getClass();
StringBuffer stringBuffer = new StringBuffer("update `" + StringUtil.underscoreName(clazz.getSimpleName()) + "` set ");
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
if (!field.getName().equals("id")) {
stringBuffer.append("`" + StringUtil.underscoreName(field.getName()) + "`=?,");
}
}
stringBuffer.deleteCharAt(stringBuffer.length() - 1);//Delete last comma
stringBuffer.append(" where id = ?");
System.out.println(stringBuffer);
PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
for (int i = 0; i < fields.length; i++) {
if (!fields[i].getName().equals("id")) {
fields[i].setAccessible(true);
Object value = fields[i].get(object);
System.out.println(i + ":" + value);
statement.setObject(i, value);
}
}
Field id = clazz.getDeclaredField("id");
id.setAccessible(true);
statement.setObject(fields.length, id.get(object));
int i = statement.executeUpdate();
statement.close();
return i == 1;
}
3, Write generic queries using reflection
reflection obtains all data.
public static void main(String[] args) throws IllegalAccessException, SQLException, InstantiationException {
Connection connection = DataSource.getConnection();
List<Role> list = select(connection, Role.class);
System.out.println(list);
}
private static <T> List<T> select(Connection connection, Class<T> clazz) throws IllegalAccessException, SQLException, InstantiationException {
StringBuffer stringBuffer = new StringBuffer("select ");
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
stringBuffer.append("`" + StringUtil.underscoreName(field.getName()) + "`,");
}
stringBuffer.deleteCharAt(stringBuffer.length() - 1);//Delete last comma
stringBuffer.append(" from " + StringUtil.underscoreName(clazz.getSimpleName()));
System.out.println(stringBuffer);
PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
ResultSet resultSet = statement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();//ResultSet Metadata
List<T> list = new ArrayList<>();
while (resultSet.next()) {
T t = clazz.newInstance();//Call parameterless construction to create an object
for (Field field : fields) {
for (int i = 0; i < metaData.getColumnCount(); i++) {
String columnName = metaData.getColumnName(i + 1);
if (StringUtil.underscoreName(field.getName()).equals(columnName)) {
field.setAccessible(true);
field.set(t, resultSet.getObject(columnName));
}
}
}
list.add(t);
}
resultSet.close();
statement.close();
return list;
}
practice:
1. Complete the six common operations of the database according to the above case.
Role role = findById(connection, Role.class, 2);//Get data with id 2 boolean ok = deletedById(connection, Role.class, 2);//Delete data with id 2 List<Role> list = page(connection, Role.class, 1, 10);//Query 10 data on page 1 long count = count(connection, Role.class);//Returns the total number of entries in the database
2. When querying, some people think that they can write more general, and pass in the where condition and the parameters in the condition together to complete the corresponding query effect. For example:
List = select (connection, role. Class, "where name =? And create_time <?", new Object [] {"test", new Date()});
Follow the method prompts to complete the method.
Reference code:
private static long count(Connection connection, Class<?> clazz) throws SQLException {
StringBuffer stringBuffer = new StringBuffer("select count(0) from " + StringUtil.underscoreName(clazz.getSimpleName()));
PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
System.out.println(stringBuffer);
ResultSet resultSet = statement.executeQuery();
long count = 0;
if (resultSet.next()) {
count = resultSet.getLong(1);
}
resultSet.close();
statement.close();
return count;
}
private static <T> List<T> page(Connection connection, Class<T> clazz, int page, int size) throws SQLException, IllegalAccessException, InstantiationException {
StringBuffer stringBuffer = new StringBuffer("select ");
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
stringBuffer.append("`" + StringUtil.underscoreName(field.getName()) + "`,");
}
stringBuffer.deleteCharAt(stringBuffer.length() - 1);//Delete last comma
stringBuffer.append(" from " + StringUtil.underscoreName(clazz.getSimpleName()) + " limit " + (page - 1) * size + "," + size);
System.out.println(stringBuffer);
PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
ResultSet resultSet = statement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();//ResultSet Metadata
List<T> list = new ArrayList<>();
while (resultSet.next()) {
T t = clazz.newInstance();//Call parameterless construction to create an object
for (Field field : fields) {
for (int i = 0; i < metaData.getColumnCount(); i++) {
String columnName = metaData.getColumnName(i + 1);
if (StringUtil.underscoreName(field.getName()).equals(columnName)) {
field.setAccessible(true);
field.set(t, resultSet.getObject(columnName));
}
}
}
list.add(t);
}
resultSet.close();
statement.close();
return list;
}
private static boolean deletedById(Connection connection, Class<?> clazz, int id) throws SQLException {
String sql = "delete from `" + StringUtil.underscoreName(clazz.getSimpleName()) + "` where id=" + id;
PreparedStatement statement = connection.prepareStatement(sql);
System.out.println(sql);
int i = statement.executeUpdate();
statement.close();
return i == 1;
}
private static <T> T findById(Connection connection, Class<T> clazz, int id) throws IllegalAccessException, SQLException, InstantiationException {
StringBuffer stringBuffer = new StringBuffer("select ");
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
stringBuffer.append("`" + StringUtil.underscoreName(field.getName()) + "`,");
}
stringBuffer.deleteCharAt(stringBuffer.length() - 1);//Delete last comma
stringBuffer.append(" from " + StringUtil.underscoreName(clazz.getSimpleName()) + " where id =" + id);
System.out.println(stringBuffer);
PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
ResultSet resultSet = statement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();//ResultSet Metadata
T t = null;
if (resultSet.next()) {
t = clazz.newInstance();//Call parameterless construction to create an object
for (Field field : fields) {
for (int i = 0; i < metaData.getColumnCount(); i++) {
String columnName = metaData.getColumnName(i + 1);
if (StringUtil.underscoreName(field.getName()).equals(columnName)) {
field.setAccessible(true);
field.set(t, resultSet.getObject(columnName));
}
}
}
}
resultSet.close();
statement.close();
return t;
}
Question 2:
private static <T> List<T> select(Connection connection, Class<T> clazz, String whereSql, Object[] objects) throws SQLException, IllegalAccessException, InstantiationException {
StringBuffer stringBuffer = new StringBuffer("select ");
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
stringBuffer.append("`" + StringUtil.underscoreName(field.getName()) + "`,");
}
stringBuffer.deleteCharAt(stringBuffer.length() - 1);//Delete last comma
stringBuffer.append(" from " + StringUtil.underscoreName(clazz.getSimpleName()) + " " + whereSql);
System.out.println(stringBuffer);
PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
for (int i = 0; i < objects.length; i++) {
System.out.println(i + ":" + objects[i]);
statement.setObject(i + 1, objects[i]);
}
ResultSet resultSet = statement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();//ResultSet Metadata
List<T> list = new ArrayList<>();
while (resultSet.next()) {
T t = clazz.newInstance();//Call parameterless construction to create an object
for (Field field : fields) {
for (int i = 0; i < metaData.getColumnCount(); i++) {
String columnName = metaData.getColumnName(i + 1);
if (StringUtil.underscoreName(field.getName()).equals(columnName)) {
field.setAccessible(true);
field.set(t, resultSet.getObject(columnName));
}
}
}
list.add(t);
}
resultSet.close();
statement.close();
return list;
}
4, Stream recursive tree encapsulation
tree node data encapsulation using recursion is usually divided into encapsulation in memory after one query and encapsulation of child nodes after multiple queries.
The data are as follows:
CREATE TABLE `category` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'classification id', `name` char(50) DEFAULT NULL COMMENT 'Classification name', `parent_id` bigint(20) DEFAULT NULL COMMENT 'Parent classification id', `level` int(11) DEFAULT NULL COMMENT 'Hierarchy', `show_status` tinyint(4) DEFAULT '1' COMMENT 'Show[0-No display, 1 display]', `sort_number` int(11) DEFAULT '0' COMMENT 'sort', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1433 DEFAULT CHARSET=utf8mb4 COMMENT='Three level classification of commodities'; INSERT INTO `category` VALUES (1,'Books, audio-visual, electronic books and periodicals',0,1,1,0); INSERT INTO `category` VALUES (2,'mobile phone',0,1,1,0); INSERT INTO `category` VALUES (22,'Electronic books and periodicals',1,2,1,2); INSERT INTO `category` VALUES (23,'Audio visual',1,2,1,6); INSERT INTO `category` VALUES (24,'English original',1,2,1,1); INSERT INTO `category` VALUES (25,'literature',1,2,1,12); INSERT INTO `category` VALUES (34,'Mobile communication',2,2,1,11); INSERT INTO `category` VALUES (35,'Operator',2,2,1,7); INSERT INTO `category` VALUES (36,'Mobile Accessories',2,2,1,2); INSERT INTO `category` VALUES (165,'e-book',22,3,1,102); INSERT INTO `category` VALUES (166,'Network originality',22,3,1,153); INSERT INTO `category` VALUES (167,'Digital magazine',22,3,1,129); INSERT INTO `category` VALUES (168,'Multimedia books',22,3,1,17); INSERT INTO `category` VALUES (169,'music',23,3,1,32); INSERT INTO `category` VALUES (170,'Movies',23,3,1,108); INSERT INTO `category` VALUES (171,'Educational audio-visual',23,3,1,106); INSERT INTO `category` VALUES (172,'a juvenile',24,3,1,35); INSERT INTO `category` VALUES (173,'Business investment',24,3,1,27); INSERT INTO `category` VALUES (174,'English learning and examination',24,3,1,29); INSERT INTO `category` VALUES (175,'literature',24,3,1,64); INSERT INTO `category` VALUES (176,'biography',24,3,1,58); INSERT INTO `category` VALUES (177,'Self-Improvement',24,3,1,98); INSERT INTO `category` VALUES (178,'novel',25,3,1,141); INSERT INTO `category` VALUES (179,'literature',25,3,1,55); INSERT INTO `category` VALUES (180,'Youth Literature',25,3,1,26); INSERT INTO `category` VALUES (181,'biography',25,3,1,149); INSERT INTO `category` VALUES (182,'Art',25,3,1,122); INSERT INTO `category` VALUES (225,'mobile phone',34,3,1,201); INSERT INTO `category` VALUES (226,'walkie-talkie',34,3,1,103); INSERT INTO `category` VALUES (227,'Contract phone',35,3,1,134); INSERT INTO `category` VALUES (228,'Number selection Center',35,3,1,137); INSERT INTO `category` VALUES (229,'Broadband installation',35,3,1,52); INSERT INTO `category` VALUES (230,'Set meal',35,3,1,77); INSERT INTO `category` VALUES (231,'portable source',36,3,1,0); INSERT INTO `category` VALUES (232,'Battery/portable source',36,3,1,1); INSERT INTO `category` VALUES (233,'Bluetooth headset',36,3,1,2); INSERT INTO `category` VALUES (234,'Charger/data line',36,3,1,10); INSERT INTO `category` VALUES (235,'Apple perimeter',36,3,1,42); INSERT INTO `category` VALUES (236,'Mobile phone earphone',36,3,1,184); INSERT INTO `category` VALUES (237,'cellphone screen protector',36,3,1,86); INSERT INTO `category` VALUES (238,'Mobile phone memory card',36,3,1,113); INSERT INTO `category` VALUES (239,'Charger',36,3,1,69); INSERT INTO `category` VALUES (240,'data line',36,3,1,7); INSERT INTO `category` VALUES (241,'Mobile phone protective cover',36,3,1,65); INSERT INTO `category` VALUES (242,'Vehicle accessories',36,3,1,64); INSERT INTO `category` VALUES (243,'iPhone parts',36,3,1,127); INSERT INTO `category` VALUES (244,'Mobile phone battery',36,3,1,198); INSERT INTO `category` VALUES (245,'Creative accessories',36,3,1,123); INSERT INTO `category` VALUES (246,'Portable/Wireless audio',36,3,1,18); INSERT INTO `category` VALUES (247,'Mobile phone accessories',36,3,1,213); INSERT INTO `category` VALUES (248,'Photo accessories',36,3,1,22); INSERT INTO `category` VALUES (249,'Mobile phone bracket',36,3,1,217);
Sream code is as follows:
public List<Category> queryTree() {
List<Category> categoryAll = baseDao.selectAll();
Category category = new Category();
category.setCatId((long) 0);
List<Category> list = findSon(category, categoryAll);
return list;
}
private List<Category> findSon(Category category, List<Category> categoryList) {
return categoryList
.stream()
.filter(c ->
c.getParentCid() == category.getId()
)
.map(c -> {
c.setChildren(findSon(c, categoryList));
return c;
})
.sorted(Comparator.comparingInt(Category::getSort)).collect(Collectors.toList());
}