推荐一个优秀的Java的DbDao

最新推荐文章于 2023-07-05 16:34:29 发布

weixin_34341229

最新推荐文章于 2023-07-05 16:34:29 发布

阅读量334

点赞数

文章标签： java 数据库 python

原文链接：https://my.oschina.net/hengbao666/blog/1551912

版权

2019独角兽企业重金招聘Python工程师标准>>>

背景：当我们需要在某一些地方不试用框架进行dao层操作的时候，我们可以使用下面的简易dao层。

DbDao.java:

package com.x.y;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DbDao {

	 private Connection connect; 
	 private PreparedStatement pstmt;
	 private ResultSet resultSet;
       
	 public DbDao(Connection connect){
      this.connect=connect;
     }
	 public boolean AddSome(String sql, List<Object>params)throws  SQLException {  
         boolean flag = false;  
         int result = -1;  
         pstmt = connect.prepareStatement(sql);
		 int index = 1;  
         if(params != null && !params.isEmpty()){  
             for(int i=0; i<params.size(); i++){  
                 pstmt.setObject(index++, params.get(i));  
             }  
         }
         result = pstmt.executeUpdate();  
        
         flag = result > 0 ? true : false;  
         return flag;  
     } 
	
     public boolean updateByKeyWord(String sql, List<Object>params)throws  SQLException {  
         boolean flag = false;  
         int result = -1;  
         pstmt = connect.prepareStatement(sql);
		 int index = 1;  
         if(params != null && !params.isEmpty()){  
             for(int i=0; i<params.size(); i++){  
                 pstmt.setObject(index++, params.get(i));  
             }  
         }
         result = pstmt.executeUpdate();  
        
         flag = result > 0 ? true : false;  
         return flag;  
     }  
     public boolean updateBySql(String sql) throws SQLException {
    	 boolean falg = false;
    	 int a=0;
    	   
			pstmt = connect.prepareStatement(sql);
			a=pstmt.executeUpdate();
	
		    if (a>=1) {
			   falg=true;
		    }
    	 return falg;
	}
     
  
   public boolean deleteBySql(String sql)throws SQLException{
	   boolean falg = false;
	   int a=0;
	   pstmt = connect.prepareStatement(sql);
	   falg=pstmt.execute();
	   return falg;
	 	
	   }
      public ResultSet getMessage(String sql)throws SQLException{
	 	
	 		Statement statement=connect.createStatement();
	 		resultSet=statement.executeQuery(sql);
	 		
	        return resultSet;
	 	
	   }
   
          public <T> T findOne(String sql, List<Object> params,  
             Class<T> cls )throws Exception{  
         T resultObject = null;  
         int index = 1;  
         pstmt = connect.prepareStatement(sql);  
         if(params != null && !params.isEmpty()){  
             for(int i = 0; i<params.size(); i++){  
                 pstmt.setObject(index++, params.get(i));  
             }  
         }  
         resultSet = pstmt.executeQuery();  
         ResultSetMetaData metaData  = resultSet.getMetaData();  
         int cols_len = metaData.getColumnCount();  
         while(resultSet.next()){  
             //通过反射机制创建一个实例  
        	
             resultObject = cls.newInstance();  
             for(int i = 0; i<cols_len; i++){  
                 String cols_name = metaData.getColumnName(i+1);  
                 Object cols_value = resultSet.getObject(cols_name);  
                 if(cols_value == null){  
                     cols_value = "";  
                 }  
                 Field field = cls.getDeclaredField(cols_name);  
                 field.setAccessible(true); //打开javabean的访问权限  
                 field.set(resultObject, cols_value);  
             }  
         }  
         if (resultObject==null) {
			System.out.println("ss");
		}
         return resultObject;  
   
     }  
     
     public <T> List<T> findMore(String sql, List<Object> params,  
             Class<T> cls )throws Exception {  
         List<T> list = new ArrayList<T>();  
         int index = 1;  
         pstmt = connect.prepareStatement(sql);  
         if(params != null && !params.isEmpty()){  
             for(int i = 0; i<params.size(); i++){  
                 pstmt.setObject(index++, params.get(i));  
             }  
         }  
         resultSet = pstmt.executeQuery();  
         ResultSetMetaData metaData  = resultSet.getMetaData();  
         int cols_len = metaData.getColumnCount();  
         while(resultSet.next()){  
             //通过反射机制创建一个实例  
             T resultObject = cls.newInstance();  
             for(int i = 0; i<cols_len; i++){  
                 String cols_name = metaData.getColumnName(i+1);  
                 Object cols_value = resultSet.getObject(cols_name);  
                 if(cols_value == null){  
                     cols_value = "";  
                 }  
                 Field field = cls.getDeclaredField(cols_name);  
                 field.setAccessible(true); //打开javabean的访问权限  
                 field.set(resultObject, cols_value);  
             }  
             list.add(resultObject);  
         }  
         return list;  
     }  
     public <T> List<T> findAll(String sql,   
             Class<T> cls )throws Exception {  
         List<T> list = new ArrayList<T>();  
         
         pstmt = connect.prepareStatement(sql);  
        
         resultSet = pstmt.executeQuery();  
         ResultSetMetaData metaData  = resultSet.getMetaData();  
         int cols_len = metaData.getColumnCount();  
         while(resultSet.next()){  
             //通过反射机制创建一个实例  
             T resultObject = cls.newInstance();  
             for(int i = 0; i<cols_len; i++){  
                 String cols_name = metaData.getColumnName(i+1);  
                 Object cols_value = resultSet.getObject(cols_name);  
                 if(cols_value == null){  
                     cols_value = "";  
                 }  
                 Field field = cls.getDeclaredField(cols_name);  
                 field.setAccessible(true); //打开javabean的访问权限  
                 field.set(resultObject, cols_value);  
             }  
             list.add(resultObject);  
         }  
         return list;  
     }  
   
     /** 
      * 释放数据库连接 
      */  
     public void releaseconn(){
     	if(resultSet!=null){
     		try {
 				resultSet.close();
 			} catch (SQLException e) {
 				// TODO Auto-generated catch block
 				e.printStackTrace();
 			}
     	}
     	if(pstmt!=null){
     		try {
 				pstmt.close();
 			} catch (SQLException e) {
 				// TODO Auto-generated catch block
 				e.printStackTrace();
 			}
     	}
     	if (connect!=null) {
			try {
				connect.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
    
}
}

C3P0Util.java

package com.x.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * 数据库工具类
 * @author liang
 *
 */
public class C3P0Util {
	static ComboPooledDataSource cpds=null;
	static{
		//这里有个优点，写好配置文件，想换数据库，简单
		//cpds = new ComboPooledDataSource("oracle");//这是oracle数据库
		cpds = new ComboPooledDataSource("mysql");//这是mysql数据库
	}
	/**
	 * 获得数据库连接
	 * @return   Connection
	 */
	public static Connection getConnection(){
		try {
			return cpds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 数据库关闭操作
	 * @param conn  
	 * @param st    
	 * @param pst
	 * @param rs
	 */
	public static void close(Connection conn,PreparedStatement pst,ResultSet rs){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(pst!=null){
			try {
				pst.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 测试DBUtil类
	 * @param args
	 */
	public static void main(String[] args) {
		Connection conn=getConnection();
		System.out.println(conn.getClass().getName());
		close(conn,null,null);
	}
}

BaseDao.java

package com.x.base;

import java.sql.Connection;
import com.keshe.db.C3P0Util;
import com.keshe.db.DbDao;

public class BaseDao {
	private Connection connect;
	public DbDao dao; 
	public BaseDao() {
		   connect=C3P0Util.getConnection();
		   
		   dao=new DbDao(connect);
	}
	
	public void closeDb(){
		
			dao.releaseconn();
		
	}
	
	public static void main(String[] args) {
		BaseDao dao=new BaseDao();
		Connection connection=dao.connect;
		dao.closeDb();
		System.out.println("ok");
	}
	
}

如何使用？

首先，写一个UserDao.java：

package com.x.dao;

import java.util.List;
import com.x.bean.User;

public interface UserDao  {

	public User login(String username,String password)throws Exception;
	
	public User adminLogin(String username,String password)throws Exception;
	public User getUserInfo(String uid,int state)throws Exception;
	public boolean addUser(User user) throws Exception;
	public boolean updateUser(User user) throws Exception;
	public boolean delateUser(String id) throws Exception;
	public List<User> findAll() throws Exception;
	public List<User> findAllLing() throws Exception;
	public List<User> findAllotTast(int bid,int zhi) throws Exception;
	public User getOne(String uid)throws Exception;
	public void closeDB()throws Exception;
}

然后，实现这个接口，并继承BaseDao

package com.x.dao.impl;

import java.util.ArrayList;
import java.util.List;
import com.x.base.BaseDao;
import com.x.bean.User;
import com.x.dao.UserDao;

public class UserDaoImpl extends BaseDao implements UserDao {

	@Override
	public User login(String username, String password) throws Exception {
		 List<Object> parms=new ArrayList<Object>();
			parms.add(username);
			parms.add(password);
			
			String sql="select * from user where gonghao=? and password =?";
			User user=new User();
			return dao.findOne(sql, parms, user.getClass());
	}

	@Override
	public void closeDB() throws Exception {
		closeDb();
		
	}

	@Override
	public boolean addUser(User user) throws Exception {
		 List<Object> parms=new ArrayList<Object>();
			parms.add(user.getGonghao());
			parms.add(user.getPassword());
			parms.add(user.getName());
			parms.add(user.getBid());
			parms.add(user.getZhi());
			parms.add(user.getPhone());
			parms.add(user.getRuzhi());
			String sql="insert into user(gonghao,password,name,bid,zhi,phone,ruzhi) values(?,?,?,?,?,?,?)";
		    
		return dao.AddSome(sql, parms);
	}

	@Override
	public boolean updateUser(User user) throws Exception {
		 List<Object> parms=new ArrayList<Object>();
			parms.add(user.getGonghao());
			parms.add(user.getPassword());
			parms.add(user.getName());
			parms.add(user.getBid());
			parms.add(user.getZhi());
			parms.add(user.getId());
			String sql="update user set gonghao=?,password=?,name=?,bid=?,zhi=? where id=?";
	      return dao.updateByKeyWord(sql, parms);
	}

	@Override
	public boolean delateUser(String id) throws Exception {
		String sql="delete from user where id='"+id+"'";
		return dao.deleteBySql(sql);
	}

	@Override
	public User getUserInfo(String uid, int state) throws Exception {
		List<Object> parms=new ArrayList<Object>();
		parms.add(uid);

		String sql="select u*.,b.bname,b.bleader   from `user` u,bumen b where b.id=u.bid  and u.gonghao=?  ";
		User user=new User();
		return dao.findOne(sql, parms, user.getClass());
		
	}

	@Override
	public List<User> findAll() throws Exception {
		// TODO Auto-generated method stub
		String sql="select * from user";
		User user=new User();
		return (List<User>) dao.findAll(sql,user.getClass());
	}

	@Override
	public User adminLogin(String username, String password) throws Exception {
		List<Object> parms=new ArrayList<Object>();
		parms.add(username);
		parms.add(password);
		
		String sql="select * from user where gonghao=? and password =? and zhi=4";
		User user=new User();
		return dao.findOne(sql, parms, user.getClass());
		
	}

	@Override
	public List<User> findAllLing() throws Exception {
		
		
		
		String sql="select * from user where zhi=2";
		User user=new User();
		return (List<User>) dao.findAll(sql, user.getClass());
	}

	@Override
	public List<User> findAllotTast(int bid, int zhi) throws Exception {
		String sql;
		List<Object> parms=new ArrayList<Object>();
		if(zhi!=1){
			sql = "select * from user where bid=? and zhi=?";
			parms.add(bid+"");
		}else{
			sql = "select * from user where   zhi=?";
		}
	
		
		parms.add(zhi+1+"");
		User user = new User();
		return (List<User>)dao.findMore(sql, parms, user.getClass());
	}

	@Override
	public User getOne(String uid) throws Exception {
		List<Object> params=new ArrayList<Object>();
		params.add(uid);
		String sql="select * from user where id=?";
		User user=new User();
		return dao.findOne(sql, params, user.getClass());
		
	}

}

转载于:https://my.oschina.net/hengbao666/blog/1551912