java 数据操作,java操作数据库的通用的种

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 List findBySql(String sql, List params, Class c)

throws SQLException {

List list = new ArrayList();

// 取出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 int doUpdate(String sql, List 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 void doParams(PreparedStatement pstmt, List 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");

}

}