sss

package com.zhuyang.test;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.halamadrid.po.NewsPO;

public class DAOUtil {
// private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
// private static final String URL =
// "jdbc:mysql://mysql.sql86.cdncenter.net:3306/sq_halamadrid?setUnicode=true&characterEncoding=utf8";
// private static final String USER_NAME = "sq_halamadrid";
// private static final String PASSWORD = "realmadrid1902";

private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/MySQL";
private static final String USER_NAME = "root";
private static final String PASSWORD = "881104";
private static Connection conn;
private static PreparedStatement statement;
private static CallableStatement callableStatement;
private static ResultSet resultSet;

public static Connection getConnection() throws Exception {
ConnectionPool connPool = new ConnectionPool(DRIVER_NAME, URL,
USER_NAME, PASSWORD);
connPool.createPool();
Connection conn = connPool.getConnection();

// Class.forName(DRIVER_NAME);
// conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
return conn;
}

public static PreparedStatement getStatement(String sql) throws Exception {
statement = getConnection().prepareStatement(sql);
return statement;
}

public static CallableStatement getCallableStatement(String sql)
throws Exception {
callableStatement = getConnection().prepareCall(sql);
return callableStatement;
}

public static Object callFunction(String sql, Object[] params)
throws Exception {
Object obj = null;
CallableStatement callableStatement = getCallableStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
callableStatement.setObject(i + 1, params[i]);
}
}
ResultSet resultSet = callableStatement.executeQuery();
while (resultSet.next()) {
obj = resultSet.getObject(1);
}
return obj;
}

public static int updateData(String sql, Object[] params) throws Exception {
getConnection().setAutoCommit(false);
int rows = 0;
PreparedStatement statement = getStatement(sql);
for (int i = 0; i < params.length; i++) {
statement.setObject(i + 1, params[i]);
}
statement.execute();
return rows;
}

/**
*
* @param sql
*            sql statement
* @param params
* @param cls
*            which class should be returned
* @return
* @throws Exception
*/
public static List searchData(String sql, Object[] params, Class cls)
throws Exception {
List list = new ArrayList();
Object result = null;
PreparedStatement statement = getStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
statement.setObject(i + 1, params[i]);
}
}
resultSet = statement.executeQuery();
while (resultSet.next()) {
Field fields[] = cls.getDeclaredFields();
Object objectCopy = cls.getConstructor(new Class[] {}).newInstance(
new Object[] {});
for (Field field : fields) {
field.setAccessible(true);
String fieldName = field.getName();
Object dbValue = resultSet.getObject(fieldName);
String setMethodName = "set"
+ StringUtils.capitalise(fieldName);
Method setMethod = cls.getDeclaredMethod(setMethodName, field
.getType());
setMethod.invoke(objectCopy, new Object[] { dbValue });
}
list.add(objectCopy);
}
return list;
}

public static void close() {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

// 查询所总条数
public static int totalCount(String tableName, Object[] params) {
String sql = "select count(*)  from " + tableName;
int count = 0;
try {
statement = getConnection().prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
statement.setObject(i + 1, params[i]);
}
}
resultSet = statement.executeQuery();
if (resultSet.next()) {
count = resultSet.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return count;
}

public static void main(String[] args) throws Exception {
StringBuffer html =new StringBuffer();
System.out.println(listComments() );
List<CommentPO> listComments = DAOUtil.listComments();
List<CommentPO> quoteList=new ArrayList();
for(CommentPO comments :listComments ){
quoteList =addComment(listComments,quoteList,comments);
}
for(CommentPO po:quoteList){
System.out.println("+++++");
System.out.println("找到=="+po.getId() + "comment id"+po.getCommentid());
System.out.println("===========");
html.append("原帖  ").append(po.getUsername()).append("内容 ：").append(po.getContent());
}
System.out.println(html);
}
protected static List addComment(List<CommentPO> list, List<CommentPO> quoteList,CommentPO comments) {
if(comments.getCommentid()!=null ){
for(CommentPO comment :list ){
if(comments.getId().equals(comment.getCommentid())){
//System.out.println("找到=="+comment.getId() + "comment id"+comment.getCommentid());
quoteList.add(comment);
addComment(list, quoteList, comment);
}
}
return quoteList;

}
return quoteList;
}
public static List<CommentPO> listComments()  {
List<CommentPO> list;
try {
String sql ="select * from commenttest order by id desc";
list = DAOUtil.searchData(sql, null, CommentPO.class);
return list;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return null;
}

}