哪位大哥能帮在下写一个完整的JDBC数据库操作类？过年了，帮帮忙，我实在是没米了。。。

/*
* Created on 2005-7-6
*
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*
* 项目名称：
* 起始日期：2005-05-10
* 项目负责人：
*
* 创建者：
*
* 描述：数据库操作对象
*/
package oaclass.db;
import java.sql.*;import  javax.naming.InitialContext;
import  javax.naming.Context;
import  javax.sql.DataSource;
import java.io.*;
import java.util.Properties;
import oaclass.util.*;
import java.util.ArrayList;
public class DBManagerBeans
{
private static String JNDINAME;

private Connection con=null;
private Statement smt=null;
private PreparedStatement psmt=null;
private CallableStatement csmt=null;
private ResultSet rs=null;
private static Context   ctx; //UPdate_20060915
private static DataSource ds; //UPdate_20060915
private PrintWriter log;

// UPdate_20060915 采用建立一次JNDI不用每次都去查找了
public static void initDBPool(){
try{
ctx=new InitialContext();
ds=(DataSource)ctx.lookup("java:comp/env/jdbc/"+JNDINAME);
}catch(Exception ex){
SystemUtil.out("DataSource:"+ex.getMessage());
SystemUtil.out(ex);
}

}

public DBManagerBeans()
{
init();
}

public DBManagerBeans(String poolname){
setJNDINAME(poolname);
}

public void setJNDINAME(String poolname){
JNDINAME=poolname;
}
public String getJNDINAME(){
return JNDINAME;
}

public void init()
{
try{
//ctx=new InitialContext();
//ds=(DataSource)ctx.lookup("java:comp/env/jdbc/"+JNDINAME);
con=ds.getConnection();
smt=this.CreateNewStatement();//con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
}
catch(Exception ex){
SystemUtil.out("DataSource:"+ex.getMessage());
SystemUtil.out(ex);
}
}

//设置事务
public void setAutoCommit(boolean commit) throws SQLException{
con.setAutoCommit(commit);
}

public void commit() throws SQLException{
con.commit();
}

//事务退回
public void rollback() throws SQLException{
con.rollback();
}
//设置连接对象
public void setConnection(Connection con)
{
if(con!=null)
   this.con=con;
}

//读取连接对象
public Connection getConnection()
{
return this.con;
}

//设置执行命令对象
public void setStatement(Statement smt)
{
if(smt!=null)
  this.smt=smt;
}

//读取执行命令对象
public Statement getStatement()
{
return this.smt;
}


//创建一个新的与上个对象无关的执行命令对象
public Statement CreateNewStatement()
{
Statement smt_temp=null;

try
{
smt_temp=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
}catch(SQLException e)
{
SystemUtil.out("DBManagerBeans:CreateNewStatement()");
SystemUtil.out(e);
}

return smt_temp;

}

//执行查询命令并返回结果
public ResultSet DBQuery(String str) throws SQLException
{
if(str==null)
   return null;
rs=smt.executeQuery(str);

return rs;
}

//执行添加、删除、更新操作
public int DBUpdate(String str) throws SQLException
{
int n=0;
if(str!=null)
   n=smt.executeUpdate(str);
return n;
}

//设置预设执行命令对象
public void setPreparedStatement(PreparedStatement psmt)
{
if(psmt!=null)
   this.psmt=psmt;
}

public void prepareStatement(String sql)
throws SQLException
{
psmt = con.prepareStatement(sql);
}


//读取预设执行命令对象
public PreparedStatement getPreparedStatement()
{
return psmt;
}

public PreparedStatement preparedStatement(String sql) throws SQLException{
   psmt= con.prepareStatement(sql);

   return psmt;
   }

//创建一个新的预设执行命令对象
public PreparedStatement CreateNewPreparedStatement(String str) throws SQLException
{
PreparedStatement psmt_temp=null;
if(str!=null)
   psmt_temp=con.prepareStatement(str,ResultSet.CONCUR_UPDATABLE,ResultSet.TYPE_SCROLL_INSENSITIVE);

return psmt_temp;
}

//设置存储过程对象
public void setCallableStatement(CallableStatement csmt)
{
if(csmt!=null)
   this.csmt=csmt;
}

//读取存储程对象
public CallableStatement getCallableStatement()
{
return csmt;
}

//建立一个新的存储过程对象
public CallableStatement CreateNewCallableStatement() throws SQLException
{
return csmt;
}

//将本对象中的链接对象放回连接池
public void freeConnection()
{
// System.out.println(con+" Free");

try{

if(rs!=null){
rs.close();
rs=null;
}

if(smt!=null){
smt.close();
smt=null;
}

if(psmt!=null){
psmt.close();
psmt=null;
}
if(con!=null){
con.close();
//2006-09-15关掉
con=null;
} }catch(SQLException e){

SystemUtil.out("DBManagerBeans:freeConnection()");
SystemUtil.out(e);
} }

}
package mliwng.persistent;import java.sql.Connection;
import java.sql.DriverManager;import javax.sql.DataSource;public class ConnectionManager { public static ThreadLocal local = new ThreadLocal();
private static DataSource dataSource;
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/test";
private static final String UID = "root";
private static final String PWD = "mliwng";

public static void openConnection()
{
  Connection conn = (Connection)local.get();

  try
  {
   if(conn == null)
   {
    Class.forName(DRIVER);
    conn = DriverManager.getConnection(URL,UID,PWD);
    local.set(conn);
   }
  }catch(Exception e)
  {
   e.printStackTrace();
  }
}

public static void closeConnection()
{
  Connection conn = (Connection)local.get();
  try
  {
   if(conn != null)
   {
    conn.close();
   }
  }catch(Exception e)
  {
   e.printStackTrace();
  }finally
  {
   local.set(null);
  }
} public static DataSource getDataSource() {
  return dataSource;
} public static void setDataSource(DataSource dataSource) {
  ConnectionManager.dataSource = dataSource;
}
}package mliwng.persistent;import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;public class PreparedStatementManager { public static PreparedStatement getSelectPstmt(Class c)
{
  PreparedStatement pstmt = null;
  String sql = SqlManager.createSelect(c);
  if(ConnectionManager.local.get()== null)
  {
   System.out.println("local == null");
  }
  try
  {
   pstmt = ((Connection)ConnectionManager.local.get()).prepareStatement(sql);
  }catch(Exception e)
  {
   e.printStackTrace();
  }

  return pstmt;
}

public static PreparedStatement getSelectByKeyPstmt(Class c,String key,Object object)
{
  PreparedStatement pstmt = null;
  String sql = SqlManager.createSelectById(c, key);

  try
  {
   pstmt = ((Connection)ConnectionManager.local.get()).prepareStatement(sql);
   pstmt.setObject(1, object);
  }catch(Exception e)
  {
   e.printStackTrace();
  }

  return pstmt;
} public static PreparedStatement getInsertPstmt(Object object)
{
  PreparedStatement pstmt = null;
  String sql = SqlManager.createInsert(object);

  try
  {
   pstmt = ((Connection)ConnectionManager.local.get()).prepareStatement(sql);
   setParameter(pstmt,object);
  }catch(Exception e)
  {
   e.printStackTrace();
  }

  return pstmt;
}

public static PreparedStatement getUpdatePstmt(Object object,String key)
{
  PreparedStatement pstmt = null;
  String sql = SqlManager.createUpdate(object, key);

  try
  {
   pstmt = ((Connection)ConnectionManager.local.get()).prepareStatement(sql);
   int j = setParameter(pstmt,object);
   Field field = object.getClass().getDeclaredField(key);
   field.setAccessible(true);
   System.out.println(j);
   pstmt.setObject(j+1, field.get(object));

  }catch(Exception e)
  {
   e.printStackTrace();
  }

  return pstmt;
}

public static PreparedStatement getDeletePstmt(Class c,String key,Object object)
{
  PreparedStatement pstmt = null;
  String sql = SqlManager.createDelete(c,key);
  try
  {
   pstmt = ((Connection)ConnectionManager.local.get()).prepareStatement(sql);
   pstmt.setObject(1,object);
  }catch(Exception e)
  {
   e.printStackTrace();
  }

  return pstmt;
} private static int setParameter(PreparedStatement pstmt,Object object)
{
  Class c = object.getClass();
  Field[] fields = c.getDeclaredFields();

  int j = 0;
  for(int i = 0;i<fields.length;i++)
  {
   if(!isNull(fields[i],object))
   {
    j ++;
    try
    {
     pstmt.setObject(j, fields[i].get(object));
    }catch(Exception e)
    {
     e.printStackTrace();
    }
   }
  }

  return j;

}

private static boolean isNull(Field field,Object obj)
{
  boolean flag = true;

  field.setAccessible(true);
  try
  {
   if(field.get(obj) == null)
   {
    flag = true;
   }else
   {
    flag = false;
   }
  }catch(Exception e)
  {
   e.printStackTrace();
  }

  return flag;
}
}
package mliwng.persistent;import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;public class SessionManager { private Connection conn;

public SessionManager()
{
  conn = (Connection)ConnectionManager.local.get();
}

public List loadAll(Class c) throws Exception
{
  ResultSet rs = PreparedStatementManager.getSelectPstmt(c).executeQuery();

  return this.getList(rs, c);
}

public List loadByKey(Class c,String key,Object object) throws Exception
{
  ResultSet rs = PreparedStatementManager.getSelectByKeyPstmt(c, key,object).executeQuery();

  return this.getList(rs, c);
}

public int save(Object object,String key) throws Exception
{
  int i = 0;
  i = PreparedStatementManager.getInsertPstmt(object).executeUpdate();
  return i;
}

public int updateByKey(Object object,String key) throws Exception
{
  int i = 0;
  i = PreparedStatementManager.getUpdatePstmt(object, key).executeUpdate();
  return i;
}

public int deleteByKey(Class c,String key,Object arg) throws Exception
{
  int i = 0;
  i = PreparedStatementManager.getDeletePstmt(c,key,arg).executeUpdate();
  return i;
}

private List getList(ResultSet rs,Class c) throws Exception
{
  List list = new ArrayList();
  Object object = null;
  Field[] fields = c.getDeclaredFields();

  while(rs.next())
  {
   object = c.newInstance();
   for(int i = 0;i<fields.length;i++)
   {
    fields[i].setAccessible(true);
    fields[i].set(object, rs.getObject(fields[i].getName()));
   }

   list.add(object);
  }

  return list;
}
}package mliwng.persistent;import java.lang.reflect.Field;public class SqlManager { public static String createSelect(Class c)
{
  StringBuffer buffer = new StringBuffer("SELECT * FROM ");
  buffer.append(c.getSimpleName());

  return buffer.toString();
}

public static String createSelectById(Class c , String key)
{
  String sql = createSelect(c);
  StringBuffer buffer = new StringBuffer(sql);
  buffer.append(" WHERE ");
  buffer.append(key);
  buffer.append(" = ?");

  return buffer.toString();
}

public static String createUpdate(Object obj,String key)
{
  Field[] fields = obj.getClass().getDeclaredFields();
  StringBuffer buffer = new StringBuffer("UPDATE ");
  buffer.append(obj.getClass().getSimpleName());
  buffer.append(" SET ");
  for(int i = 0;i < fields.length;i ++)
  {
   if(!isNull(fields[i],obj))
   {
    buffer.append(fields[i].getName());
    buffer.append( " = ? ,");
   }
  }
  buffer = buffer.deleteCharAt(buffer.length() - 1);

  buffer.append(" WHERE ");
  buffer.append(key);
  buffer.append(" = ?");


  System.out.println(buffer.toString());
  return buffer.toString();
}

public static String createInsert(Object obj)
{

  StringBuffer buffer = new StringBuffer("INSERT INTO ");
  buffer.append(obj.getClass().getSimpleName());
  buffer.append("(");
  Field[] fields = obj.getClass().getDeclaredFields();
  StringBuffer bufferValue = new StringBuffer();
  for(int i = 0;i < fields.length;i ++)
  {
   if(!isNull(fields[i],obj))
   {
    buffer.append(fields[i].getName());
    buffer.append(",");
    bufferValue.append("?,");
   }
  }
  buffer = buffer.deleteCharAt(buffer.length() - 1);
  buffer.append(")");
  bufferValue = bufferValue.deleteCharAt(bufferValue.length() - 1);
  buffer.append(" VALUES(");
  buffer.append(bufferValue);
  buffer.append(")");

  return buffer.toString();
}

private static boolean isNull(Field field,Object obj)
{
  boolean flag = true;

  field.setAccessible(true);
  try
  {
   if(field.get(obj) == null)
   {
    flag = true;
   }else
   {
    flag = false;
   }
  }catch(Exception e)
  {
   e.printStackTrace();
  }

  return flag;
}

public static String createDelete(Class c,String key)
{
  StringBuffer buffer = new StringBuffer("DELETE FROM ");
  buffer.append(c.getSimpleName());
  buffer.append(" WHERE ");
  buffer.append(key);
  buffer.append(" = ?");
  return buffer.toString();
}
}package mliwng.persistent;import java.sql.Connection;public class TransactionManager {

public static void beginTransaction()
{
  Connection conn = (Connection)ConnectionManager.local.get();
  try
  {
   conn.setAutoCommit(false);
  }catch(Exception e)
  {
   e.printStackTrace();
  }
}

public static void commit()
{
  Connection conn = (Connection)ConnectionManager.local.get();
  try
  {
   conn.commit();
  }catch(Exception e)
  {
   e.printStackTrace();
  }
}

public static void rollback()
{
  Connection conn = (Connection)ConnectionManager.local.get();
  try
  {
   conn.rollback();
  }catch(Exception e)
  {
   e.printStackTrace();
  }
}
} 这个是测试用的
package test;
public class User
{
private Integer id;
private String userName;
private String userPassword;

public Integer getId() {
  return id;
}
public void setId(Integer id) {
  this.id = id;
}
public String getUserName() {
  return userName;
}
public void setUserName(String userName) {
  this.userName = userName;
}
public String getUserPassword() {
  return userPassword;
}
public void setUserPassword(String userPassword) {
  this.userPassword = userPassword;
}
}
package test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;import mliwng.persistent.ConnectionManager;
import mliwng.persistent.SessionManager;
import mliwng.persistent.TransactionManager;public class UserDao{ public List list() throws Exception
{
  return new SessionManager().loadAll(User.class);
}

public void insert(User user) throws Exception
{
  new SessionManager().save(user, "id");
}

public void update(User user) throws Exception
{
  new SessionManager().updateByKey(user, "id");
}

public void delete(int id) throws Exception
{
  new SessionManager().deleteByKey(User.class,"id",id);
}

public List select() throws Exception
{
  return new SessionManager().loadByKey(User.class, "userName","mliwng");
} public static void main(String[] args)
{
  UserDao dao = new UserDao();

  User user = new User();
  user.setUserName("11111");
  user.setId(1);

  User user1 = new User();
  user1.setUserName("cccc");
  user1.setUserPassword("cccc");

  User user2 = new User();
  user2.setUserName("bbbb");
  user2.setUserPassword("bbb");

  ConnectionManager.openConnection();    //打开连接
  TransactionManager.beginTransaction(); //开启事务
  try
  {
   dao.insert(user2); //调用UserDao.insert()方法保存User2对象
   TransactionManager.commit();  提交事务
  }catch(Exception e)
  {
   e.printStackTrace();
   TransactionManager.rollback();   //出错时回滚
  }finally
  {
   ConnectionManager.closeConnection();
  }
}
}

调试易

哪位大哥能帮在下写一个完整的JDBC数据库操作类？过年了，帮帮忙，我实在是没米了。。。

解决方案 »