org.apache.commons.dbutils.handlers.BeanHandler Java Examples

@Override
	public T query(String sql, Object... args) {
		
		Connection connection = null;
		
		try {
//			connection = ConnectionContext.getInstance().get();
			connection = JDBCUtils.getConnection();
			return queryRunner.query(connection, sql, new BeanHandler<>(clazz), args);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(connection);
		}
		
		return null;
	}
 

public static void main(String[] args) throws SQLException {

		final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
		final String driver = "org.h2.Driver";
		final String usr = "sa";
		final String pwd = "";

		QueryRunner run = new QueryRunner();

		DbUtils.loadDriver(driver);
		Connection conn = DriverManager.getConnection(url, usr, pwd);
		// -----------------------------------------------------------------------------------
		ResultSetHandler<Employee> resultHandler = new BeanHandler<Employee>(
				Employee.class);

		try {
			Employee emp = run.query(conn,
					"SELECT * FROM employee WHERE employeename=?",
					resultHandler, "Jose");
			System.out.println(emp.getEmployeeId());
		} finally {
			DbUtils.close(conn);
		}

	}
 

@Override
public <T> T queryEntity(Class<T> entityClass, String sql, Object... params) {
    T result;
    try {
        Map<String, String> columnMap = EntityHelper.getColumnMap(entityClass);
        if (MapUtil.isNotEmpty(columnMap)) {
            result = queryRunner.query(sql, new BeanHandler<T>(entityClass, new BasicRowProcessor(new BeanProcessor(columnMap))), params);
        } else {
            result = queryRunner.query(sql, new BeanHandler<T>(entityClass), params);
        }
    } catch (SQLException e) {
        logger.error("查询出错！", e);
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return result;
}
 

@Override
public <T> T queryEntity(Class<T> entityClass, String sql, Object... params) {
    T result;
    try {
        Map<String, String> columnMap = EntityHelper.getColumnMap(entityClass);
        if (MapUtil.isNotEmpty(columnMap)) {
            result = queryRunner.query(sql, new BeanHandler<T>(entityClass, new BasicRowProcessor(new BeanProcessor(columnMap))), params);
        } else {
            result = queryRunner.query(sql, new BeanHandler<T>(entityClass), params);
        }
    } catch (SQLException e) {
        logger.error("查询出错！", e);
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return result;
}
 

public void updateState(Admin admin){
	try {
		//?在DButils中表示一个占位符
		String sql = "select * from public.admin where email=?";
		admin = JdbcUtils.getQueryRuner().query(sql,
				new BeanHandler<Admin>(Admin.class), admin.getEmail());
		if(admin != null){
			admin.setState(true);
			//将结果写回数据库
			JdbcUtils.getQueryRuner().update(
   					"update public.admin set state=? where email=?",admin.isState(),admin.getEmail());
		}else {
			return;
		}
		
		
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
}
 

@Override
public Admin findByUUID(String string) {
	Admin admin = null;
	try {
		//?在DButils中表示一个占位符
		String sql = "select * from public.admin where id=?";
		admin =  JdbcUtils.getQueryRuner().query(sql,
				new BeanHandler<Admin>(Admin.class),string);    //根据UUID去查找返回数据库中相应的admin对象
		if (admin != null) {
			return admin;
		}else{
			return null;
		}
		
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
}
 

public boolean getUserState(Admin admin){
	try {
		//?在DButils中表示一个占位符
		String sql = "select * from public.admin where email=? and pwd=?";
		admin = JdbcUtils.getQueryRuner().query(sql,
				new BeanHandler<Admin>(Admin.class), admin.getEmail(),  //admin.getUserName()获取其用户名
				admin.getPwd());  //admin.getPwd()获取其密码
		if(admin != null){
			return admin.isState();
		}else {
			return false;
		}
		
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
}
 

public String findPwd(Admin admin){
		try {
			//?在DButils中表示一个占位符
			String sql = "select * from public.admin where email=?";
System.out.println("进入到数据库之后收到的邮箱参数为:"+admin.getEmail());
			admin =  JdbcUtils.getQueryRuner().query(sql,
					new BeanHandler<Admin>(Admin.class), admin.getEmail()
				);  
			if (admin != null) {
				return admin.getPwd();
			}else{
				return null;
			}
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}	
		
	}
 

public String getUUID(Admin admin){
	String sql = "select * from public.admin where email=?";   //根据用户的email查找到数据库中存放的整个对象
	try {
		//执行该SQL语句并返回一个admin对象
		admin =  JdbcUtils.getQueryRuner().query(sql,
				new BeanHandler<Admin>(Admin.class), admin.getEmail() //根据email查找数据库中是否存在该用户
			); 
		if (admin != null) {
			return admin.getId();
		}else{
			return null;
		}
		
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
}
 

public String  findByEmail(Admin admin) {
	try {
		//?在DButils中表示一个占位符
		String sql = "select * from public.admin where email=?";
		//执行该SQL语句并返回一个admin对象
		admin =  JdbcUtils.getQueryRuner().query(sql,
				new BeanHandler<Admin>(Admin.class), admin.getEmail() //根据email查找数据库中是否存在该用户
			); 
		if (admin != null) {
			return admin.getEmail();
		}else{
			return null;
		}
		
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
}
 

@Override
public ProjectGlobal getProjectGlobal(final String projectId,final String column) {
    return process(new Handler<ProjectGlobal>() {
        @Override
        public ProjectGlobal handle(Connection connection, QueryRunner qr) throws SQLException {
            ProjectGlobal pg = qr.query(connection, "select "+column+" from " + TableNames.PROJECT_GLOBAL + " where projectId=?", new BeanHandler<>(ProjectGlobal.class), projectId);
            if (pg == null) {
                //会有并发问题
                pg = generateProjectGlobal(projectId);
                SQLBuildResult sbr = SqlUtils.generateInsertSQL(pg);
                if (qr.update(connection, sbr.getSql(), sbr.getParams()) == 0) {
                    throw new SystemErrorException("创建project_global失败");
                }
            }
            return pg;
        }
    });
}
 

@Override
public ProjectGlobal getProjectGlobal(final String projectId) {
    return process(new Handler<ProjectGlobal>() {
        @Override
        public ProjectGlobal handle(Connection connection, QueryRunner qr) throws SQLException {
            ProjectGlobal pg = qr.query(connection, "select * from " + TableNames.PROJECT_GLOBAL + " where projectId=?", new BeanHandler<>(ProjectGlobal.class), projectId);
            if (pg == null) {
                //会有并发问题
                pg = generateProjectGlobal(projectId);
                SQLBuildResult sbr = SqlUtils.generateInsertSQL(pg);
                if (qr.update(connection, sbr.getSql(), sbr.getParams()) == 0) {
                    throw new SystemErrorException("创建project_global失败");
                }
            }
            return pg;
        }
    });
}
 

@SuppressWarnings("unchecked")
protected BaseDao() {
	queryRunner = new QueryRunner();
	Type superClass = getClass().getGenericSuperclass();
	if (superClass instanceof ParameterizedType) {
		ParameterizedType parameterizedType = (ParameterizedType) superClass;
		Type[] typeArgs = parameterizedType.getActualTypeArguments();
		if (typeArgs != null && typeArgs.length > 0) {
			if (typeArgs[0] instanceof Class) {
				clazz = (Class<T>) typeArgs[0];
			}
		}
	}
	beanHandler = new BeanHandler<>(clazz);
	beanListHandler = new BeanListHandler<>(clazz);
}
 

/**
 * 查询一条数据,可搭配page,limit,order,group,having使用
 * 
 * @return T 返回javabean
 * @throws Exception if has error
 */
@SuppressWarnings("unchecked") 
public <T> T find() throws Exception{
	this.limit(1);
	try {
		if (buildSql_Select()) {
			BeanHandler<T> beanHandler= new BeanHandler<T>((Class<T>) this.beanClass,new BasicRowProcessor(new BeanProcessor(columnToPropertyOverrides)));
			T bean = new QueryRunner().query(conn, sql,beanHandler, param_where);
			D.autoCloseConn(conn);
			return bean;
		}
	} catch (Exception e) {
		D.closeConn(conn);
		throw e;
	}
	return null;
}
 

@Override
public User getUserByThirdId(final String thirdId) {
    return process(new Handler<User>() {
        @Override
        public User handle(Connection connection, QueryRunner qr) throws SQLException {
            String sql = "select * from " + TableNames.USER + " where id = (select userid from " + TableNames.USER_THIRD + " where id=?)";
            return qr.query(connection, sql, new BeanHandler<>(User.class), thirdId);
        }
    });
}
 

@Override
public User login(final String email, final String password) {
    return process(new Handler<User>() {
        @Override
        public User handle(Connection connection, QueryRunner qr) throws SQLException {
            String sql = "select * from user where email=? and password=?";
            return qr.query(connection, sql, new BeanHandler<>(User.class), email, password);
        }
    });
}
 

@Override
public <T> T getById(final Class<T> clazz, final String id) {
    return process(new Handler<T>() {
        @Override
        public T handle(Connection connection, QueryRunner qr) throws SQLException {
            String sql = "select * from " + SqlUtils.getTableName(clazz) + " where id = ?";
            return qr.query(connection, sql, new BeanHandler<>(clazz), id);
        }
    });
}
 

@Override
public DbJob getJob(int jobId)
{
    try {
        return queryRunner.query("select * from jobs where id=?", new BeanHandler<>(DbJob.class, processor), jobId);
    }
    catch (Exception e) {
        throw throwsException(e);
    }
}
 

public NewsDetail getNewsDetail(String newsId) {
	sql = "SELECT newsId, username, title, content, image, releaseTime FROM news a, user b WHERE a.userId = b.userId AND newsId = ?;";
	try {
		return queryRunner.query(sql, new BeanHandler<NewsDetail>(NewsDetail.class), newsId);
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
}
 

public User login(String username, String password) {
	sql = "SELECT userId, username, `password`, sex, height, weight, registerTime, `status` FROM `user` WHERE username = ? AND `password` = ?";
	try {
		return queryRunner.query(sql, new BeanHandler<User>(User.class), username, password);
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
}
 

@Override
public User findUser(String username, String password) throws SQLException {
	String sql = "select id,username,password from user where username=? and password =?";
	Object[] params = { username, password };
	User user = qr.query(sql, new BeanHandler<User>(User.class), params);
	return user;
}
 

public static void main(String[] args) {
	Connection conn = null;

	final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
	final String driver = "org.h2.Driver";
	final String usr = "sa";
	final String pwd = "";

	User user = null;

	try {
		// Loading the Driver using DbUtils static method
		DbUtils.loadDriver(driver);
		conn = DriverManager.getConnection(url, usr, pwd);
		QueryRunner query = new QueryRunner();
		user = (User) query.query(conn,
				"select * from user where userId=3", new BeanHandler<User>(
						User.class));

		System.out.println("User Object::  " + user.getUserId() + "\t"
				+ user.getFirstName() + "\t" + user.getLastName() + "\t"
				+ user.getEmailId());

	} catch (SQLException se) {
		se.printStackTrace();
	} finally {
		DbUtils.closeQuietly(conn);
	}
}
 

public Account findAccountById(Integer accountId) {
    try {
        return runner.query("select *from count where id = ?", new BeanHandler<Account>(Account.class), accountId);
    } catch (Exception e) {
        throw new RuntimeException();
    }
}
 

/**
 * 将查询结果转换成Bean返回。
 * 
 * @param ds JDBC连接池
 */
public void queryBean(DataSource ds, int userId) {
    String sql = "select userId, userName, gender, age from student where userId=?";
    
    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<Student> handler = new BeanHandler<Student>(Student.class);
    Student result = null;
    try {
        result = run.query(sql, handler, userId);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }
    
    System.out.println(result);
}
 

protected RepositoryDao() {
    super();
    beanHandler = new BeanHandler<>(getEntityClass(), new BasicRowProcessor(new RepositoryBeanProcessor()));
    beanListHandler = new BeanListHandler<>(getEntityClass(), new BasicRowProcessor(new RepositoryBeanProcessor()));
}
 

@Override
public <T> T query(BeanQueryParam<T> param, BeanHandler<T> handler) {
    return query(param.sqlStatement(), handler);
}
 

/**
 * 单个bean query
 *
 * @param handler bean处理器, 通过{@link Queries#beanHandler(Class)}获取
 * @param <T>     bean class type
 * @return bean
 * @see Queries#beanHandler(Class)
 */
<T> T query(BeanQueryParam<T> param, BeanHandler<T> handler);