jdbcTemplate的Dao层封装

  1 package com.easyrail.base.dao;
  2 
  3 import java.io.Serializable;
  4 import java.lang.reflect.Field;
  5 import java.lang.reflect.ParameterizedType;
  6 import java.sql.Types;
  7 import java.util.LinkedHashMap;
  8 import java.util.List;
  9 import java.util.Map;
 10 
 11 import org.springframework.jdbc.core.BeanPropertyRowMapper;
 12 import org.springframework.jdbc.core.JdbcTemplate;
 13 import org.springframework.jdbc.core.RowMapper;
 14 
 15 public abstract class BaseDaoImpl<T>{
 16     /** 设置一些操作的常量 */
 17     public static final String SQL_INSERT = "insert";
 18     public static final String SQL_UPDATE = "update";
 19     public static final String SQL_DELETE = "delete";
 20 
 21     private JdbcTemplate jdbcTemplate;
 22 
 23     public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
 24         this.jdbcTemplate = jdbcTemplate;
 25     }
 26 
 27     private Class<T> entityClass;
 28 
 29     @SuppressWarnings("unchecked")
 30     public BaseDaoImpl() {
 31         ParameterizedType type = (ParameterizedType) getClass().getGenericSuperclass();
 32         entityClass = (Class<T>) type.getActualTypeArguments()[0];
 33         System.out.println("Dao实现类是：" + entityClass.getName());
 34     }
 35 
 36     public void save(T entity) {
 37         String sql = this.makeSql(SQL_INSERT);
 38         Object[] args = this.setArgs(entity, SQL_INSERT);
 39         int[] argTypes = this.setArgTypes(entity, SQL_INSERT);
 40         jdbcTemplate.update(sql.toString(), args, argTypes);
 41     }
 42 
 43     public void update(T entity) {
 44         String sql = this.makeSql(SQL_UPDATE);
 45         Object[] args = this.setArgs(entity, SQL_UPDATE);
 46         int[] argTypes = this.setArgTypes(entity, SQL_UPDATE);
 47         jdbcTemplate.update(sql, args, argTypes);
 48     }
 49 
 50     public void delete(T entity) {
 51         String sql = this.makeSql(SQL_DELETE);
 52         Object[] args = this.setArgs(entity, SQL_DELETE);
 53         int[] argTypes = this.setArgTypes(entity, SQL_DELETE);
 54         jdbcTemplate.update(sql, args, argTypes);
 55     }
 56 
 57     public void delete(Serializable id) {
 58         String sql = " DELETE FROM " + entityClass.getSimpleName() + " WHERE id=?";
 59         jdbcTemplate.update(sql, id);
 60     }
 61 
 62     public void deleteAll() {
 63         String sql = " TRUNCATE TABLE " + entityClass.getSimpleName();
 64         jdbcTemplate.execute(sql);
 65     }
 66 
 67     public T findById(Serializable id) {
 68         String sql = "SELECT * FROM " + entityClass.getSimpleName() + " WHERE id=?";
 69         RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
 70         return jdbcTemplate.query(sql, rowMapper, id).get(0);
 71     }
 72 
 73     public List<T> findAll() {
 74         String sql = "SELECT * FROM " + entityClass.getSimpleName();
 75         RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
 76         return jdbcTemplate.query(sql, rowMapper);
 77     }
 78 
 79     // 组装SQL
 80     private String makeSql(String sqlFlag) {
 81         StringBuffer sql = new StringBuffer();
 82         Field[] fields = entityClass.getDeclaredFields();
 83         if (sqlFlag.equals(SQL_INSERT)) {
 84             sql.append(" INSERT INTO " + entityClass.getSimpleName());
 85             sql.append("(");
 86             for (int i = 0; fields != null && i < fields.length; i++) {
 87                 fields[i].setAccessible(true); // 暴力反射
 88                 String column = fields[i].getName();
 89                 sql.append(column).append(",");
 90             }
 91             sql = sql.deleteCharAt(sql.length() - 1);
 92             sql.append(") VALUES (");
 93             for (int i = 0; fields != null && i < fields.length; i++) {
 94                 sql.append("?,");
 95             }
 96             sql = sql.deleteCharAt(sql.length() - 1);
 97             sql.append(")");
 98         } else if (sqlFlag.equals(SQL_UPDATE)) {
 99             sql.append(" UPDATE " + entityClass.getSimpleName() + " SET ");
100             for (int i = 0; fields != null && i < fields.length; i++) {
101                 fields[i].setAccessible(true); // 暴力反射
102                 String column = fields[i].getName();
103                 if (column.equals("id")) { // id 代表主键
104                     continue;
105                 }
106                 sql.append(column).append("=").append("?,");
107             }
108             sql = sql.deleteCharAt(sql.length() - 1);
109             sql.append(" WHERE id=?");
110         } else if (sqlFlag.equals(SQL_DELETE)) {
111             sql.append(" DELETE FROM " + entityClass.getSimpleName() + " WHERE id=?");
112         }
113         System.out.println("SQL=" + sql);
114         return sql.toString();
115 
116     }
117 
118     // 设置参数
119     private Object[] setArgs(T entity, String sqlFlag) {
120         Field[] fields = entityClass.getDeclaredFields();
121         if (sqlFlag.equals(SQL_INSERT)) {
122             Object[] args = new Object[fields.length];
123             for (int i = 0; args != null && i < args.length; i++) {
124                 try {
125                     fields[i].setAccessible(true); // 暴力反射
126                     args[i] = fields[i].get(entity);
127                 } catch (Exception e) {
128                     e.printStackTrace();
129                 }
130             }
131             return args;
132         } else if (sqlFlag.equals(SQL_UPDATE)) {
133             Object[] tempArr = new Object[fields.length];
134             for (int i = 0; tempArr != null && i < tempArr.length; i++) {
135                 try {
136                     fields[i].setAccessible(true); // 暴力反射
137                     tempArr[i] = fields[i].get(entity);
138                 } catch (Exception e) {
139                     e.printStackTrace();
140                 }
141             }
142             Object[] args = new Object[fields.length];
143             System.arraycopy(tempArr, 1, args, 0, tempArr.length - 1); // 数组拷贝
144             args[args.length - 1] = tempArr[0];
145             return args;
146         } else if (sqlFlag.equals(SQL_DELETE)) {
147             Object[] args = new Object[1]; // 长度是1
148             fields[0].setAccessible(true); // 暴力反射
149             try {
150                 args[0] = fields[0].get(entity);
151             } catch (Exception e) {
152                 e.printStackTrace();
153             }
154             return args;
155         }
156         return null;
157 
158     }
159 
160     // 设置参数类型（写的不全，只是一些常用的）
161     private int[] setArgTypes(T entity, String sqlFlag) {
162         Field[] fields = entityClass.getDeclaredFields();
163         if (sqlFlag.equals(SQL_INSERT)) {
164             int[] argTypes = new int[fields.length];
165             try {
166                 for (int i = 0; argTypes != null && i < argTypes.length; i++) {
167                     fields[i].setAccessible(true); // 暴力反射
168                     if (fields[i].get(entity).getClass().getName().equals("java.lang.String")) {
169                         argTypes[i] = Types.VARCHAR;
170                     } else if (fields[i].get(entity).getClass().getName().equals("java.lang.Double")) {
171                         argTypes[i] = Types.DECIMAL;
172                     } else if (fields[i].get(entity).getClass().getName().equals("java.lang.Integer")) {
173                         argTypes[i] = Types.INTEGER;
174                     } else if (fields[i].get(entity).getClass().getName().equals("java.util.Date")) {
175                         argTypes[i] = Types.DATE;
176                     }
177                 }
178             } catch (Exception e) {
179                 e.printStackTrace();
180             }
181             return argTypes;
182         } else if (sqlFlag.equals(SQL_UPDATE)) {
183             int[] tempArgTypes = new int[fields.length];
184             int[] argTypes = new int[fields.length];
185             try {
186                 for (int i = 0; tempArgTypes != null && i < tempArgTypes.length; i++) {
187                     fields[i].setAccessible(true); // 暴力反射
188                     if (fields[i].get(entity).getClass().getName().equals("java.lang.String")) {
189                         tempArgTypes[i] = Types.VARCHAR;
190                     } else if (fields[i].get(entity).getClass().getName().equals("java.lang.Double")) {
191                         tempArgTypes[i] = Types.DECIMAL;
192                     } else if (fields[i].get(entity).getClass().getName().equals("java.lang.Integer")) {
193                         tempArgTypes[i] = Types.INTEGER;
194                     } else if (fields[i].get(entity).getClass().getName().equals("java.util.Date")) {
195                         tempArgTypes[i] = Types.DATE;
196                     }
197                 }
198                 System.arraycopy(tempArgTypes, 1, argTypes, 0, tempArgTypes.length - 1); // 数组拷贝
199                 argTypes[argTypes.length - 1] = tempArgTypes[0];
200 
201             } catch (Exception e) {
202                 e.printStackTrace();
203             }
204             return argTypes;
205 
206         } else if (sqlFlag.equals(SQL_DELETE)) {
207             int[] argTypes = new int[1]; // 长度是1
208             try {
209                 fields[0].setAccessible(true); // 暴力反射
210                 if (fields[0].get(entity).getClass().getName().equals("java.lang.String")) {
211                     argTypes[0] = Types.VARCHAR;
212                 } else if (fields[0].get(entity).getClass().getName().equals("java.lang.Integer")) {
213                     argTypes[0] = Types.INTEGER;
214                 }
215 
216             } catch (Exception e) {
217                 e.printStackTrace();
218             }
219             return argTypes;
220         }
221         return null;
222     }
223 
224     private List<T> find(int pageNo, int pageSize, Map<String, String> where, LinkedHashMap<String, String> orderby) {
225         // where 与 order by 要写在select * from table 的后面，而不是where rownum<=? )
226         // where rn>=?的后面
227         StringBuffer sql = new StringBuffer(
228                 " SELECT * FROM (SELECT t.*,ROWNUM rn FROM (SELECT * FROM " + entityClass.getSimpleName());
229         if (where != null && where.size() > 0) {
230             sql.append(" WHERE "); // 注意不是where
231             for (Map.Entry<String, String> me : where.entrySet()) {
232                 String columnName = me.getKey();
233                 String columnValue = me.getValue();
234                 sql.append(columnName).append(" ").append(columnValue).append(" AND "); // 没有考虑or的情况
235             }
236             int endIndex = sql.lastIndexOf("AND");
237             if (endIndex > 0) {
238                 sql = new StringBuffer(sql.substring(0, endIndex));
239             }
240         }
241         if (orderby != null && orderby.size() > 0) {
242             sql.append(" ORDER BY ");
243             for (Map.Entry<String, String> me : orderby.entrySet()) {
244                 String columnName = me.getKey();
245                 String columnValue = me.getValue();
246                 sql.append(columnName).append(" ").append(columnValue).append(",");
247             }
248             sql = sql.deleteCharAt(sql.length() - 1);
249         }
250         sql.append(" ) t WHERE ROWNUM<=? ) WHERE rn>=? ");
251         System.out.println("SQL=" + sql);
252         Object[] args = { pageNo * pageSize, (pageNo - 1) * pageSize + 1 };
253         RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
254         return jdbcTemplate.query(sql.toString(), args, rowMapper);
255     }
256 
257     private int count(Map<String, String> where) {
258         StringBuffer sql = new StringBuffer(" SELECT COUNT(*) FROM " + entityClass.getSimpleName());
259         if (where != null && where.size() > 0) {
260             sql.append(" WHERE ");
261             for (Map.Entry<String, String> me : where.entrySet()) {
262                 String columnName = me.getKey();
263                 String columnValue = me.getValue();
264                 sql.append(columnName).append(" ").append(columnValue).append(" AND "); // 没有考虑or的情况
265             }
266             int endIndex = sql.lastIndexOf("AND");
267             if (endIndex > 0) {
268                 sql = new StringBuffer(sql.substring(0, endIndex));
269             }
270         }
271         System.out.println("SQL=" + sql);
272         return jdbcTemplate.queryForInt(sql.toString());
273     }
274 }