通用的增删改查

1. 利用xml配置实体类和数据库表名的映射关系2. 根据xml设计，用正确的数据结构映射类封装好xml信息3. 得到数据库连接前，读取xml信息，用map封装成映射数据4. 写dao时根据反射和map生成sql语句，拿到属性值测试

为了解决上一篇文中实体类的局限性：不可加字段；实体名，表名，列的字段一样，而且顺序一样上一篇反射实现dao层增删改查 本文实现结构

1. 利用xml配置实体类和数据库表名的映射关系

sql语句 create table ticket_info ( ticket_id number primary key, ticket_name varchar(30) not null, ticket_price number(10, 2) not null ) create sequence ticket_seq create table goods_info ( goods_id number primary key, goods_name varchar(30) not null, goods_price number(10, 2) not null, goods_date date not null, goods_factory varchar(50) not null ) create sequence goods_seq

xml, 映射表属性和实体类列

goods.xml

<?xml version="1.0" encoding="UTF-8"?> <class name="com.lovely.entity.Goods" table="goods_info"> <id name="gid" column="goods_id"> <sequence>goods_seq</sequence> </id> <property name="gname" column="goods_name"></property> <property name="gprice" column="goods_price"></property> <property name="gdate" column="goods_date"></property> <property name="gfactory" column="goods_factory"></property> </class> ticket.xml <?xml version="1.0" encoding="UTF-8"?> <!-- 实体类和表之间的映射关系 --> <class name="com.lovely.entity.Ticket" table="ticket_info"> <!-- 类名和表名的映射关系 --> <id name="tid" column="ticket_id"> <!-- 实体主键和表中主键列的映射关系 --> <sequence>ticket_seq</sequence> </id> <property name="tname" column="ticket_name"></property> <!-- 属性名和表名列的映射关系 --> <property name="tprice" column="ticket_price"></property> </class> goods, 和 entity的实体类 package com.lovely.entity; import java.sql.Date; public class Goods { private Integer gid; private String gname; private Double gprice; private Date gdate; private String gfactory; public Goods() {} public Integer getGid() { return gid; } public void setGid(Integer gid) { this.gid = gid; } public String getGname() { return gname; } public void setGname(String gname) { this.gname = gname; } public Double getGprice() { return gprice; } public void setGprice(Double gprice) { this.gprice = gprice; } public Date getGdate() { return gdate; } public void setGdate(Date gdate) { this.gdate = gdate; } public String getGfactory() { return gfactory; } public void setGfactory(String gfactory) { this.gfactory = gfactory; } } package com.lovely.entity; public class Ticket { private Integer tid; private String tname; private Double tprice; public Ticket() {} public Ticket(Integer tid, String tname, Double tprice) { super(); this.tid = tid; this.tname = tname; this.tprice = tprice; } public Integer getTid() { return tid; } public void setTid(Integer tid) { this.tid = tid; } public String getTname() { return tname; } public void setTname(String tname) { this.tname = tname; } public Double getTprice() { return tprice; } public void setTprice(Double tprice) { this.tprice = tprice; } @Override public String toString() { return "Ticket [tid=" + tid + ", tname=" + tname + ", tprice=" + tprice + "]\n"; } }

2. 根据xml设计，用正确的数据结构映射类封装好xml信息

主键id package com.lovely.base; public class MapperId { // 实体id 映射 数据库id private String idName; private String idColumn; private String seqName; public String getIdName() { return idName; } public void setIdName(String idName) { this.idName = idName; } public String getIdColumn() { return idColumn; } public void setIdColumn(String idColumn) { this.idColumn = idColumn; } public String getSeqName() { return seqName; } public void setSeqName(String seqName) { this.seqName = seqName; } @Override public String toString() { return "MapperId [idName=" + idName + ", idColumn=" + idColumn + ", seqName=" + seqName + "]"; } } MapperData 映射数据类 package com.lovely.base; import java.util.LinkedHashMap; public class MapperData { // 映射文件类 // 实体全类名 private String className; // 表名 private String tableName; private MapperId mapperId; // 存储除主键外 实体属性 -> 数据表列 相关信息 private LinkedHashMap<String, String> properties = new LinkedHashMap<String, String>(); public String getClassName() { return className; } public void setClassName(String className) { this.className = className; } public String getTableName() { return tableName; } public void setTableName(String tableName) { this.tableName = tableName; } public MapperId getMapperId() { return mapperId; } public void setMapperId(MapperId mapperId) { this.mapperId = mapperId; } public LinkedHashMap<String, String> getProperties() { return properties; } public void setProperties(LinkedHashMap<String, String> properties) { this.properties = properties; } @Override public String toString() { return "MapperData [className=" + className + ", tableName=" + tableName + ", mapperId=" + mapperId + ", properties=" + properties + "]\n"; } }

3. 得到数据库连接前，读取xml信息，用map封装成映射数据

实体属性名和表的列名一样，也封装在map里面了 package com.lovely.dao; import java.io.File; import java.lang.reflect.Field; import java.net.URL; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; import org.dom4j.Document; import org.dom4j.Element; import org.dom4j.io.SAXReader; import com.lovely.base.MapperData; import com.lovely.base.MapperId; public class BaseDao { static { try { Class.forName("oracle.jdbc.OracleDriver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * key: 全路径类名 * value: 类和表的映射关系 MapperData */ public static HashMap<String, MapperData> map = new HashMap<String, MapperData>(); static { // 静态块解析xml映射文件 try { Class<?> baseDaoClass = Class.forName("com.lovely.dao.BaseDao"); // 得到xml路径 URL url = baseDaoClass.getResource("/com/lovely/mapper"); String path = url.getFile(); File file = new File(path); // 拿到所有xml文件 File[] files = file.listFiles(); for (int i = 0; i < files.length; i++) { // 解析xml一个文件 // System.out.println(files[i].getName()); SAXReader reader = new SAXReader(); Document doc = reader.read(files[i]); // 拿到 class结点 Element root = doc.getRootElement(); MapperData mapperData = new MapperData(); // 保存实体类名 mapperData.setClassName(root.attributeValue("name")); // 保存数据表名 mapperData.setTableName(root.attributeValue("table")); MapperId mapperId = new MapperId(); // 主键结点映射关系 Element primaryKey = root.element("id"); // 实体类主键 mapperId.setIdName(primaryKey.attributeValue("name")); // 表的主键 mapperId.setIdColumn(primaryKey.attributeValue("column")); // 序列名称 mapperId.setSeqName(primaryKey.elementText("sequence")); // 保存主键结点映射关系 mapperData.setMapperId(mapperId); @SuppressWarnings("unchecked") // 所有 实体属性-表名结点映射关系 List<Element> property = root.elements("property"); LinkedHashMap<String, String> lhm = new LinkedHashMap<String, String>(); for (Element field : property) { lhm.put(field.attributeValue("name"), field.attributeValue("column")); } mapperData.setProperties(lhm); // 把实体类 与 xml中的映射数据一一对应 map.put(root.attributeValue("name"), mapperData); } } catch (Exception e) { e.printStackTrace(); } } static { // 加载没有配置文件的实体类 try { Class<?> c = BaseDao.class; // mapper下的路径 String path = c.getResource("/com/lovely/entity").getFile(); File file = new File(path); File[] files = file.listFiles(); for (int i = 0; i < files.length; i++) { String fileName = files[i].getName(); String className = "com.lovely.entity." + fileName.substring(0, fileName.indexOf(".")); // 没有映射文件的解析 对于数据库表名，字段，顺序 和 实体的类名 字段 顺序一样。 if (!map.containsKey(className)) { // 实体类的类型描述 Class<?> cc = Class.forName(className); MapperData value = new MapperData(); // 设置实体类名和表名 value.setClassName(cc.getName()); value.setTableName(cc.getSimpleName()); Field[] fields = cc.getDeclaredFields(); Field.setAccessible(fields, true); // 拿到主键 String primaryKeyName = fields[0].getName(); MapperId mapperId = new MapperId(); mapperId.setIdName(primaryKeyName); mapperId.setIdColumn(primaryKeyName); mapperId.setSeqName("seq_" + cc.getSimpleName()); // 属性 主键列名 序列名 value.setMapperId(mapperId); LinkedHashMap<String, String> properties = new LinkedHashMap<String, String>(); // 设置除主键意外的属性 for (int j = 1; j < fields.length; j++) { properties.put(fields[j].getName(), fields[j].getName()); } value.setProperties(properties); map.put(className, value); } } } catch(Exception e) { e.printStackTrace(); } } public static void main(String[] args) { System.out.println(map); } public static Connection getConn() { Connection conn = null; String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; try { conn = DriverManager.getConnection(url, "scott", "scott"); } catch (SQLException e) { e.printStackTrace(); } return conn; } public static void closeAll(Connection conn, PreparedStatement ps, ResultSet rs) { try { if (rs != null) rs.close(); if (ps != null) ps.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }

4. 写dao时根据反射和map生成sql语句，拿到属性值

curd实现 package com.lovely.dao; import java.lang.reflect.Field; import java.sql.*; import java.util.ArrayList; import java.util.Collection; import java.util.LinkedHashMap; import java.util.List; import java.util.Map.Entry; import java.util.Set; import com.lovely.base.MapperData; import com.lovely.base.MapperId; /** * * @author echo lovely * * 万能增删改查 * */ public class CommonDao { public int save(Object entity) { int count = -1; Class<?> c = entity.getClass(); MapperData mapperData = BaseDao.map.get(c.getName()); StringBuffer sql = new StringBuffer(); sql.append("insert into "); sql.append(mapperData.getTableName() + " values ("); // insert into tableName values (seq_table.nextval, ?, ?, ?...) sql.append(mapperData.getMapperId().getSeqName() + ".nextval"); LinkedHashMap<String,String> properties = mapperData.getProperties(); Set<String> keySet = properties.keySet(); // 除主键外的 实体属性名 for (int i = 0; i < keySet.size(); i++) { sql.append(", ?"); } sql.append(")"); System.out.println(sql); Connection conn = BaseDao.getConn(); PreparedStatement ps = null; try { ps = conn.prepareStatement(sql.toString()); // 设置参数的值 // 取实体列 int index = 1; for (String entityColumn : keySet) { // 反射根据属性名称拿值 Field field = c.getDeclaredField(entityColumn); field.setAccessible(true); ps.setObject(index, field.get(entity)); index ++; } count = ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally { BaseDao.closeAll(conn, ps, null); } return count; } public List<Object> queryAll(Class<?> c) { List<Object> list = new ArrayList<Object>(); // 拿到 实体类 与 表的列 映射关系 MapperData mapperData = BaseDao.map.get(c.getName()); String sql = "select * from " + mapperData.getTableName(); Connection conn = BaseDao.getConn(); PreparedStatement ps = null; ResultSet rs = null; LinkedHashMap<String, String> properties = mapperData.getProperties(); try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); // 表主键列名 String primaryKeyName = mapperData.getMapperId().getIdColumn(); String idName = mapperData.getMapperId().getIdName(); while (rs.next()) { Object entity = c.newInstance(); Object primaryKeyValue = rs.getObject(primaryKeyName); // 主键属性 Field primaryFiled = c.getDeclaredField(idName); if (primaryFiled.getType() == Integer.class) primaryKeyValue = rs.getInt(primaryKeyName); primaryFiled.setAccessible(true); primaryFiled.set(entity, primaryKeyValue); Set<Entry<String,String>> entrySet = properties.entrySet(); for (Entry<String, String> entry : entrySet) { // 属性名称 String filedName = entry.getKey(); // 表的列名 String columnName = entry.getValue(); Object attributValue = rs.getObject(columnName); // System.out.println(filedName + "\t" + columnName + "..."); // 除主键外的属性对象 Field f = c.getDeclaredField(filedName); if (f.getType() == Double.class) { attributValue = rs.getDouble(columnName); } else if (f.getType() == java.sql.Timestamp.class) { attributValue = rs.getTimestamp(columnName); } f.setAccessible(true); f.set(entity, attributValue); } list.add(entity); } } catch (Exception e) { e.printStackTrace(); } finally { BaseDao.closeAll(conn, ps, rs); } return list; } public Object queryOne(Object obj) { Object entity = null; Class<?> c = obj.getClass(); MapperData mapperData = BaseDao.map.get(c.getName()); String sql = "select * from " + mapperData.getTableName() + " where " + mapperData.getMapperId().getIdColumn() + " = ?"; System.out.println(sql); Connection conn = BaseDao.getConn(); PreparedStatement ps = null; ResultSet rs = null; try { MapperId mapperId = mapperData.getMapperId(); ps = conn.prepareStatement(sql); // 拿到主键属性对象 Field field = c.getDeclaredField(mapperId.getIdName()); field.setAccessible(true); ps.setObject(1, field.get(obj)); rs = ps.executeQuery(); LinkedHashMap<String,String> properties = mapperData.getProperties(); Set<Entry<String, String>> entrySet = properties.entrySet(); if (rs.next()) { // 记得反射创建对象... entity = c.newInstance(); Field idFiled = c.getDeclaredField(mapperId.getIdName()); idFiled.setAccessible(true); // 取到主键值 Object idColumn = rs.getObject(mapperId.getIdColumn()); System.out.println(idColumn + "\t" + mapperId.getIdName() + "\t" + mapperId.getIdColumn()); if (idFiled.getType() == Integer.class) { idColumn = rs.getInt(mapperId.getIdColumn()); } idFiled.set(entity, idColumn); for (Entry<String, String> entry : entrySet) { Field f1 = c.getDeclaredField(entry.getKey()); f1.setAccessible(true); Object value = rs.getObject(entry.getValue()); if (f1.getType() == Double.class) value = rs.getDouble(entry.getValue()); else if (f1.getType() == Timestamp.class) { value = rs.getTimestamp(entry.getValue()); } f1.set(entity, value); } } } catch (Exception e) { e.printStackTrace(); } finally { BaseDao.closeAll(conn, ps, rs); } return entity; } public int update(Object entity) { int count = -1; Class<?> c = entity.getClass(); StringBuffer sql = new StringBuffer(); // 根据类的全路径 拿到MapperData MapperData mapperData = BaseDao.map.get(c.getName()); // update tabName set * = ?, * = ?, * = ? .... where id = ? sql.append("update " + mapperData.getTableName() + " set "); LinkedHashMap<String,String> properties = mapperData.getProperties(); // 除了主键外的所有属性集合 Set<String> keySet = properties.keySet(); // 数据库中表 列的集合 Collection<String> cloumnNames = properties.values(); int cloumnSize = cloumnNames.size(); int index = 0; for (String cloumnName : cloumnNames) { if (index < cloumnSize - 1) sql.append(cloumnName + " = ?, "); else sql.append(cloumnName + " = ?"); index ++; } sql.append(" where " + mapperData.getMapperId().getIdColumn() + " = ?"); System.out.println(sql); Connection conn = BaseDao.getConn(); PreparedStatement ps = null; try { ps = conn.prepareStatement(sql.toString()); int paramIndex = 1; for (String fieldAttribute : keySet) { // 映射的键 -> 实体属性对象 Field field = c.getDeclaredField(fieldAttribute); field.setAccessible(true); Object obj = field.get(entity); ps.setObject(paramIndex, obj); paramIndex ++; } // 主键属性 Field field = c.getDeclaredField(mapperData.getMapperId().getIdName()); field.setAccessible(true); ps.setObject(paramIndex, field.get(entity)); count = ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally { BaseDao.closeAll(conn, ps, null); } return count; } public int delete(Object obj) { int count = -1; Class<?> c = obj.getClass(); MapperData mapperData = BaseDao.map.get(c.getName()); MapperId mapperId = mapperData.getMapperId(); String sql = "delete from " + mapperData.getTableName() + " where " + mapperId.getIdColumn() + " = ?"; Connection conn = BaseDao.getConn(); PreparedStatement ps = null; try { ps = conn.prepareStatement(sql); // 反射拿到实体类 主键属性对象 Field field = c.getDeclaredField(mapperId.getIdName()); field.setAccessible(true); // 根据属性对象 取到 该对象的属性值 ps.setObject(1, field.get(obj)); count = ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally { BaseDao.closeAll(conn, ps, null); } return count; } }

测试

一句代码搞定查询。。。 package com.lovely.test; import com.lovely.dao.CommonDao; import com.lovely.entity.Student; import com.lovely.entity.Ticket; public class Test1 { public static void main(String[] args) { CommonDao dao = new CommonDao(); // 学生类的结构和表的结构一样 System.out.println(dao.queryAll(Studdent.getClass())); // 配置了xml映射关系的 System.out.println(dao.queryAll(Ticket.class)); } } [Student [sid=46, sname=jack, sgender=male, sbirth=2020-07-02 20:24:01.0, saddress=null, sinfo=null] ] [Ticket [tid=2, tname=花木兰, tprice=33.2] , Ticket [tid=3, tname=阿凡达2, tprice=50.5] ]