java操作数据库的通用的类

package cn.dao;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 * 数据库操作通用类
 * @author tip
 */
public class DBHelp {
	private Connection con;
	private PreparedStatement ps;
	private ResultSet rs;
	static Readdoc td = new Readdoc();//读取XML文件中的数据库连接参数
	static {
		try {
			Class.forName(td.getDriver());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public Connection getCon() throws SQLException {
		con = DriverManager.getConnection("jdbc:oracle:thin:@" + td.getAdd()
				+ ":1521:" + td.getDb(), td.getUname(), td.getPwd());
		return con;
	}

	public <T> List<T> findBySql(String sql, List<String> params, Class<T> c)
			throws SQLException {
		List<T> list = new ArrayList<T>();
		// 取出c的这个对象中有多少个方法
		Method[] ms = c.getMethods();
		con = this.getCon();
		try {
			ps = con.prepareStatement(sql);
			this.doParams(ps, params);
			rs = ps.executeQuery();
			// 得到db中的表的列
			ResultSetMetaData rsmd = rs.getMetaData();
			// 存储全部的列名
			String[] columnname = new String[rsmd.getColumnCount()];
			for (int i = 0; i < columnname.length; i++) {
				columnname[i] = rsmd.getColumnName(i + 1);
			}
			// id=> "set"+"I"+d=> "setId()" =>对应数据库表的字段
			while (rs.next()) {
				// new对象
				T t = c.newInstance(); // Topic t=new Topic();
				// t.setId(rs.getInt("id"));
				for (int i = 0; i < columnname.length; i++) {
					String cn = columnname[i]; // id title contents
					cn = "set" + cn.substring(0, 1).toUpperCase()
							+ cn.substring(1).toLowerCase(); // "setId"
					for (Method m : ms) {
						if (m.getName().equals(cn)) {
							if (rs.getObject(columnname[i]) != null) {
								if ("java.sql.Timestamp".equals(rs.getObject(columnname[i]).getClass().getName())) {
									m.invoke(t, rs.getString(columnname[i]));
								} else if ("java.lang.Integer".equals(rs.getObject(columnname[i]).getClass().getName())) {
									m.invoke(t, rs.getInt(columnname[i]));
								} else {
									m.invoke(t, rs.getObject(columnname[i]));
								}
							} else {
								m.invoke(t, rs.getObject(columnname[i]));
							}
							break;
						}
					}

				}
				list.add(t);
			}
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			closeAll(rs, ps, con);
		}
		return list;
	}

	// insert into xx values(?,?); params={"tip","whx"};
	public <T> int doUpdate(String sql, List<T> params) throws SQLException {
		con = this.getCon();
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			doParams(ps, params);
			i = ps.executeUpdate();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			closeAll(null, ps, con);
		}
		return i;
	}

	// 设置预编译对象参数
	public <T> void doParams(PreparedStatement pstmt, List<T> params)
			throws SQLException {
		if (pstmt != null && params != null && params.size() > 0) {
			for (int i = 0; i < params.size(); i++) {
				if (params.get(i) != null) {
					if (params.get(i).getClass().getName().equals("java.lang.String")) {
						pstmt.setString(i + 1, (String) params.get(i));
					} else if (params.get(i).getClass().getName()
							.equals("java.math.BigDecimal")) {
						pstmt.setBigDecimal(i + 1, (BigDecimal) params.get(i));
					} else if (params.get(i).getClass().getName().equals("java.sql.Timestamp")) {
						pstmt.setString(i + 1, (String) params.get(i));
					} else {
						pstmt.setObject(i + 1, params.get(i));
					}
				} else {
					pstmt.setString(i + 1, "");
				}

			}
		}
	}

	public void closeAll(ResultSet rs, PreparedStatement pstmt, Connection con) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

//下面是解析XML文件，数据库连接参数存在XML中。

package cn.dao;

import java.io.InputStream;
import java.util.Properties;

/**
 * 读取XML文件中的数据库连接参数
 * @author tip
 */
public class Readdoc {
	private final String FILENAME = "cn/dao/config.xml";
	private String driver = "";//数据库连接驱动
	private String uname = "";//用户名
	private String pwd = "";//密码
	private String db = "";//数据库
	private String add = "";//IP地址

	public String getDriver() {
		return driver;
	}

	public void setDriver(String driver) {
		this.driver = driver;
	}

	public String getUname() {
		return uname;
	}

	public void setUname(String uname) {
		this.uname = uname;
	}

	public String getPwd() {
		return pwd;
	}

	public void setPwd(String pwd) {
		this.pwd = pwd;
	}

	public String getDb() {
		return db;
	}

	public void setDb(String db) {
		this.db = db;
	}

	public String getAdd() {
		return add;
	}

	public void setAdd(String add) {
		this.add = add;
	}

	public Readdoc() {
		init(FILENAME);
	}

	public Readdoc(String fileName)  {
		init(fileName);
	}

	public void init(String fileName) {
		Properties proper = new Properties();
		InputStream fis = Readdoc.class.getClassLoader().getResourceAsStream(
				fileName);
		try {
			proper.loadFromXML(fis);
		} catch (Exception e) {
			//抛出异常时设置默认值
			proper.setProperty("loginid", "scott");
			proper.setProperty("pass", "tiger");
			proper.setProperty("database", "orcl");
			proper.setProperty("addip", "localhost");
			proper.setProperty("driver", "oracle.jdbc.driver.OracleDriver");
		}
		driver = proper.getProperty("driver");
		uname = proper.getProperty("loginid");
		pwd = proper.getProperty("pass");
		add = proper.getProperty("addip");
		db = proper.getProperty("database");
	}
}

评论

1 楼 Vincent-Yang 2014-01-23  

doUpdate不会用，接受的是List<T>那就是说只能有一种type,那怎么传多个不同type啊，例如我有datetime值如何传啊