关于把JSP新闻内容（String）存入数据库的CLob字段

首先说明情况：我的News实体类里面的newsContent是String类型的，用富文本编辑器编辑以后要存入数据库的Clob字段中

代码进行了简单的分层，JdbcUtil全部代码贴在了最下面。

Dao的实现类（部分）：

1 public void update(News news) {
2     String sql = "update NEWS set NEWS_TITLE = ?, NEWS_CONTENT = ?, NEWS_DATE = to_date(?,'yyyy-mm-dd'), ADMIN_ID = ? where NEWS_ID = ?";
3     Reader clobcontent = new StringReader(news.getNewsContent());
4     jdbcUtil.execute(sql, new Object[]{news.getNewsTitle(),clobcontent,news.getNewsDate(),news.getAdminId(),news.getNewsId()});
5 }

JdbcUtil工具类（部分）：

/**
 * 执行不带结果集的sql
 */
public void execute(String sql,Object[] objs){
    System.out.println(sql);
    createStatment(sql);
    try {
        if (objs != null){
            for (int i = 1; i <= objs.length; i++){
                
                //对java.util.Date的特列处理：转java.sql.Date
                Object obj = objs[i - 1];
                
                if(obj != null && obj instanceof java.util.Date){
                    obj = new java.sql.Timestamp(((java.util.Date)obj).getTime());
                }
                if(obj != null && obj instanceof java.io.Reader){
                    preparedStatement.setClob(i, (java.io.Reader) obj);
                }else{
                    preparedStatement.setObject(i,obj);
                }
                
            }
        }
    int num = preparedStatement.executeUpdate();
    System.out.println("受影响的行数为：" + num);
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        close();
    }
}

JdbcUtil（全部）：

  1 import java.lang.reflect.Field;
  2 import java.lang.reflect.Method;
  3 import java.math.BigDecimal;
  4 import java.sql.Connection;
  5 import java.sql.DriverManager;
  6 import java.sql.PreparedStatement;
  7 import java.sql.ResultSet;
  8 import java.sql.ResultSetMetaData;
  9 import java.sql.SQLException;
 10 import java.sql.Statement;
 11 import java.util.ArrayList;
 12 import java.util.Date;
 13 import java.util.List;
 14 
 15 /**
 16  * 
 17  */
 18 public class JdbcUtil {
 19     
 20     //连接
 21     Connection conn = null;
 22     
 23     //statement对象用于执行sql语句
 24     PreparedStatement preparedStatement = null;
 25 
 26     //结果集
 27     ResultSet rs = null;
 28     
 29     /**
 30      * 连接
 31      */
 32     public Connection connect(){
 33         try {
 34             Class.forName(JdbcProperties.getValue("driverClassName"));
 35             conn = DriverManager.getConnection(JdbcProperties.getValue("url"), JdbcProperties.getValue("username"), JdbcProperties.getValue("password"));
 36         } catch (Exception e) {
 37             e.printStackTrace();
 38         }
 39         return conn;
 40     }
 41     
 42     /**
 43      * 创建statement
 44      */
 45     public PreparedStatement createStatment(String sql){
 46         //连接数据库
 47         connect();
 48         try {
 49             preparedStatement = conn.prepareStatement(sql);
 50         } catch (SQLException e) {
 51             e.printStackTrace();
 52         }
 53         return preparedStatement;
 54     }
 55     
 56     /**
 57      * 执行带结果集的sql
 58      */
 59     
 60     public <T> List<T> executeQuery(Class<T> cla,String sql,Object[] objs){
 61         System.out.println(sql);
 62         //创建statement
 63         createStatment(sql);
 64         List<T> list = null;
 65         try {
 66             if (objs != null){
 67                 for (int i = 1; i <= objs.length; i++){
 68                     preparedStatement.setObject(i,objs[i - 1]);
 69                 }
 70             }
 71             rs = preparedStatement.executeQuery();
 72             list = toList(cla, rs);
 73         } catch (SQLException e) {
 74             e.printStackTrace();
 75             list = new ArrayList<T>();
 76         }finally{
 77             close();
 78         }
 79         return list;
 80     }
 81     
 82     /**
 83      * 执行带结果集的sql,带分页的
 84      */
 85     
 86     public <T> List<T> executeQuery(Class<T> cla,String sql,Object[] objs,Pager pager){
 87         //创建statement
 88         String countSql = pager.getCountSql(sql);
 89         System.out.println(countSql);
 90         createStatment(countSql);
 91         
 92         Integer count = 0;
 93         try {
 94             if (objs != null){
 95                 for (int i = 1; i <= objs.length; i++){
 96                     preparedStatement.setObject(i,objs[i - 1]);
 97                 }
 98             }
 99             rs = preparedStatement.executeQuery();
100             if(rs.next()){
101                 count = rs.getInt(1);
102             }
103             System.out.println("count:" + count);
104         } catch (SQLException e) {
105         }finally{
106             try {
107                 rs.close();
108                 preparedStatement.close();
109             } catch (SQLException e) {
110                 e.printStackTrace();
111             }
112         }
113         pager.setTotalCount(count);
114         
115         //处理翻页sql
116         sql = pager.getPageSql(sql);
117         System.out.println(sql);
118         createStatment(sql);
119         List<T> list = null;
120         try {
121             if (objs != null){
122                 for (int i = 1; i <= objs.length; i++){
123                     preparedStatement.setObject(i,objs[i - 1]);
124                 }
125             }
126             rs = preparedStatement.executeQuery();
127             list = toList(cla, rs);
128         } catch (SQLException e) {
129             e.printStackTrace();
130             list = new ArrayList<T>();
131         }finally{
132             close();
133         }
134         return list;
135     }
136     
137     
138     /**
139      * 执行不带结果集的sql
140      */
141     public void execute(String sql,Object[] objs){
142         System.out.println(sql);
143         createStatment(sql);
144         try {
145             if (objs != null){
146                 for (int i = 1; i <= objs.length; i++){
147                     
148                     //对java.util.Date的特列处理：转java.sql.Date
149                     Object obj = objs[i - 1];
150                     
151                     if(obj != null && obj instanceof java.util.Date){
152                         obj = new java.sql.Timestamp(((java.util.Date)obj).getTime());
153                     }
154                     if(obj != null && obj instanceof java.io.Reader){
155                         preparedStatement.setClob(i, (java.io.Reader) obj);
156                     }else{
157                         preparedStatement.setObject(i,obj);
158                     }
159                     
160                 }
161             }
162         int num = preparedStatement.executeUpdate();
163         System.out.println("受影响的行数为：" + num);
164         } catch (SQLException e) {
165             e.printStackTrace();
166         }finally{
167             close();
168         }
169     }
170     
171     /**
172      * 执行insert语句，执行成功，返回表的主键
173      */
174     public int insert(String sql,String idSql,Object[] objs){
175         System.out.println(sql);
176 
177         int id = 0;
178         
179         //连接数据库
180         connect();
181         try {
182             preparedStatement = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
183         } catch (SQLException e) {
184             e.printStackTrace();
185         }
186         try {
187             if (objs != null){
188                 for (int i = 1; i <= objs.length; i++){
189                     
190                     //对java.util.Date的特列处理：转java.sql.Date
191                     Object obj = objs[i - 1];
192                     
193                     if(obj != null && obj instanceof java.util.Date){
194                         //obj = new java.sql.Date(((java.util.Date)obj).getTime());
195                         obj = new java.sql.Timestamp(((java.util.Date)obj).getTime());
196                     }
197                     
198                     preparedStatement.setObject(i,obj);
199                 }
200             }
201         int num = preparedStatement.executeUpdate();
202         
203         ResultSet rs = preparedStatement.getGeneratedKeys();
204         String rowId = "";
205         if(rs.next()){
206             rowId = rs.getString(1);
207         }
208         rs.close();
209         preparedStatement.close();
210         
211          //select id from tablename where rowId = ?
212         preparedStatement = conn.prepareStatement(idSql);
213         preparedStatement.setString(1,rowId);
214         
215         rs = preparedStatement.executeQuery();
216         
217         if(rs.next()){
218             id = rs.getInt(1);
219         }
220         
221         System.out.println("受影响的行数为：" + num);
222         } catch (SQLException e) {
223             e.printStackTrace();
224         }finally{
225             close();
226         }
227         
228         return id;
229     }
230     
231     /**
232      * 关闭所有对象
233      */
234     public void close(){
235         try {
236             if (rs != null){
237                 rs.close();
238             }
239             if (preparedStatement != null){
240                 preparedStatement.close();
241             }
242             if (conn != null){
243                 conn.close();
244             }
245         } catch (SQLException e) {
246             e.printStackTrace();
247         }
248     }
249     
250     
251     /**
252      * 将结果集转化为对象
253      */
254     @SuppressWarnings("unchecked")
255     public static <T> List<T> toList(Class<T> cla,ResultSet rs){
256         
257         List<T> list = new ArrayList<T>();
258         
259         //1.获取结果集里面所有的列的列名
260         try {
261             while (rs.next()){
262             //5.反射java对象。
263             Object obj = cla.newInstance();
264             
265             ResultSetMetaData rsmd = rs.getMetaData();
266             int count = rsmd.getColumnCount();
267             for (int i = 1; i <= count; i++){
268                 //取列名
269                 String columnName = rsmd.getColumnName(i).toLowerCase();
270                 
271                 //处理外键的情况
272                 if (columnName.indexOf(".") != -1){
273                     //列名称用.分隔
274                     String[] fields = columnName.split("[.]");
275                     //第一个位置是外键对象名称
276                     String fieldName = fields[0];
277                     //第二个位置是外键对象当中的字段名称
278                     String foreignFieldName = fields[1];
279                     
280                     //去下划线，并且下划线后面的首字母变大写
281                     int _position = foreignFieldName.indexOf("_");
282                     if(_position != -1){
283                         String[] foreignFieldNames = foreignFieldName.split("[_]");
284                         
285                         String preChar = foreignFieldNames[1].substring(0, 1).toUpperCase();
286                         
287                         String otherName = foreignFieldNames[1].substring(1);
288                         foreignFieldName = foreignFieldNames[0] + preChar + otherName;
289                     }
290                     //取到外键对象这个字段
291                     Field field = cla.getDeclaredField(fieldName);
292                     //临时名称，用于拼接getter and setter
293                     String tempName = String.valueOf(fieldName.charAt(0)).toUpperCase()  + fieldName.substring(1);
294                     //获得getter
295                     Method getMethod = cla.getMethod("get" + tempName );
296                     //执行getter，获得外键对象
297                     Object foreign = getMethod.invoke(obj);
298                     //如果外键对象为空，则new一个实例，通过setter set到当前对象中
299                     if (foreign == null){
300                         foreign = field.getType().newInstance();
301                         Method setMethod = cla.getMethod("set" + tempName, field.getType());
302                         setMethod.invoke(obj, foreign);
303                     }
304                     //外键对象当中的临时字段名称
305                     String foreignTempName = String.valueOf(foreignFieldName.charAt(0)).toUpperCase()  + foreignFieldName.substring(1);; 
306                     //获得外键对象当中的字段
307                     
308                     Field foreignField = field.getType().getDeclaredField(foreignFieldName);
309                     //取外键对象当中字段的setter
310                     Method foreignSetMethod = field.getType().getMethod("set" + foreignTempName, foreignField.getType());
311                     
312                     //setType_name()
313                     
314                     //获取字段的值
315                     Object value = null;
316                     Class<?> javaType = foreignField.getType();
317                     if (javaType.equals(int.class) || javaType.equals(Integer.class) ){
318                         value = rs.getInt(columnName);
319                     }else if (javaType.equals(String.class)){
320                         value = rs.getString(columnName);
321                     }else if (javaType.equals(byte.class) || javaType.equals(Byte.class)){
322                         value = rs.getByte(columnName);
323                     }else if (javaType.equals(short.class) || javaType.equals(Short.class)){
324                         value = rs.getShort(columnName);
325                     }else if (javaType.equals(long.class) || javaType.equals(Long.class)){
326                         value = rs.getLong(columnName);
327                     }else if (javaType.equals(float.class) || javaType.equals(Float.class)){
328                         value = rs.getFloat(columnName);
329                     }else if (javaType.equals(double.class) || javaType.equals(Double.class)){
330                         value = rs.getDouble(columnName);
331                     }else if (javaType.equals(BigDecimal.class)){
332                         value = rs.getBigDecimal(columnName);
333                     }else if (javaType.equals(Date.class)){
334                         //value = rs.getDate(columnName);
335                         value = rs.getTimestamp(columnName);
336                     }else if (javaType.equals(boolean.class) || javaType.equals(Boolean.class)){
337                         value = rs.getBoolean(columnName);
338                     }else if (javaType.equals(Object.class)){
339                         value = rs.getObject(columnName);
340                     }else{
341                         System.out.println("未识别的列类型：" + javaType);
342                     }
343                     //执行setter
344                     foreignSetMethod.invoke(foreign, value);
345                 }
346                 
347                 //2.将列名转化为java对象中的属性名
348                 int position = columnName.indexOf("_");
349                 
350                 //属性名
351                 String fieldName;
352                 if (position != -1){
353                     //取下划线后面的首字母
354                     char firstChar = columnName.charAt(position + 1);
355                     //将_x格式转化成X
356                     //给属性名赋值，属性名等于去掉下划后，将下划线后面的首字母大写
357                     fieldName = columnName.replace("_" + firstChar, String.valueOf(firstChar).toUpperCase());
358                 }else{
359                     //给属性名赋值，属性名等于列名
360                     fieldName = columnName;
361                 }
362                 
363                 //3.根据java属性名找属性名的java类型
364                 //找到对应的字段
365                 Field field = null;
366                 try {
367                     field = cla.getDeclaredField(fieldName);
368                 } catch (NoSuchFieldException e) {
369                     //e.printStackTrace();
370                 }
371                 if (field != null){
372                     //取字段的java类型
373                     Class<?> javaType = field.getType();
374                     //根据java类型和set方法名称找到对应的set方法
375                     String methodName = "set" + String.valueOf(fieldName.charAt(0)).toUpperCase() + fieldName.substring(1);
376                     Method setMethod = cla.getDeclaredMethod(methodName, javaType);
377                     
378                     //4.从结果集取值。（根据属性名的java类型执行对应的rs.getXX方法。）
379                     Object value = null;
380                     if (javaType.equals(int.class) || javaType.equals(Integer.class) ){
381                         value = rs.getInt(columnName);
382                     }else if (javaType.equals(String.class)){
383                         value = rs.getString(columnName);
384                     }else if (javaType.equals(byte.class) || javaType.equals(Byte.class)){
385                         value = rs.getByte(columnName);
386                     }else if (javaType.equals(short.class) || javaType.equals(Short.class)){
387                         value = rs.getShort(columnName);
388                     }else if (javaType.equals(long.class) || javaType.equals(Long.class)){
389                         value = rs.getLong(columnName);
390                     }else if (javaType.equals(float.class) || javaType.equals(Float.class)){
391                         value = rs.getFloat(columnName);
392                     }else if (javaType.equals(double.class) || javaType.equals(Double.class)){
393                         value = rs.getDouble(columnName);
394                     }else if (javaType.equals(BigDecimal.class)){
395                         value = rs.getBigDecimal(columnName);
396                     }else if (javaType.equals(Date.class)){
397                         //value = rs.getDate(columnName);
398                         value = rs.getTimestamp(columnName);
399                     }else if (javaType.equals(boolean.class) || javaType.equals(Boolean.class)){
400                         value = rs.getBoolean(columnName);
401                     }else if (javaType.equals(Object.class)){
402                         value = rs.getObject(columnName);
403                     }else{
404                         System.out.println("未识别的列类型：" + javaType);
405                     }
406                     //6.将结果集取到的值set到反射的对象中。    
407                     setMethod.invoke(obj, value);
408                 }
409                 
410             }
411             list.add((T)obj);
412             }
413             
414         } catch (Exception e) {
415             e.printStackTrace();
416         }
417         return list;
418     }
419     
420     
421     public static void main(String[] args) {
422         JdbcUtil jdbcUtil = new JdbcUtil();
423         jdbcUtil.executeQuery(Object.class, "select * from Student", null);
424     }
425 }
View Code
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/yt975548/p/6168788.html