数据库代码:select b.*,e.uid,e.ip,e.place,e.ctime,count(distinct e.uid) from edm.edm_open_56 e left join (select u.id,u.email from e_toemail u ) b on e.uid = b.id运行数据库代码的结果:
id email uid ip place ctime count(distinct e.uid)
267 [email protected] 267 192.168.4.200 - LAN 1340011706 1
Java代码:public List<EmailOpenReturnInfoVO> findOpenInfo(Long taskId) {
List<EmailOpenReturnInfoVO> emailOpenReturnInfoVOs = new ArrayList<EmailOpenReturnInfoVO>();
String sql = "select b.*,e.uid,e.ip,e.place,e.ctime,count(distinct e.uid) from edm.edm_open_56 e left join (select u.id,u.email from e_toemail u ) b on e.uid = b.id";
PreparedStatement ps = null;
ResultSet rs = null;
EmailOpenReturnInfoVO vo = null;
Session session = null;
try {
session = this.sessionFactory.openSession();
ps = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection().prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
vo = new EmailOpenReturnInfoVO();
vo.setEmial(rs.getString("email"));
vo.setIp(rs.getString("ip"));
vo.setPlace(rs.getString("place"));
vo.setTime(new Date(rs.getInt("ctime")*1000L));
emailOpenReturnInfoVOs.add(vo);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(null != session)
session.close();
}
return emailOpenReturnInfoVOs;
}运行最后的结果:id = null ,email = null ;也就是e_toemail表中的所有数据都为null,但是第一张表中的数据能正常获取,求解啊。急急急有能在线请教的吗?可以联系我的Q: 526673470
id email uid ip place ctime count(distinct e.uid)
267 [email protected] 267 192.168.4.200 - LAN 1340011706 1
Java代码:public List<EmailOpenReturnInfoVO> findOpenInfo(Long taskId) {
List<EmailOpenReturnInfoVO> emailOpenReturnInfoVOs = new ArrayList<EmailOpenReturnInfoVO>();
String sql = "select b.*,e.uid,e.ip,e.place,e.ctime,count(distinct e.uid) from edm.edm_open_56 e left join (select u.id,u.email from e_toemail u ) b on e.uid = b.id";
PreparedStatement ps = null;
ResultSet rs = null;
EmailOpenReturnInfoVO vo = null;
Session session = null;
try {
session = this.sessionFactory.openSession();
ps = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection().prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
vo = new EmailOpenReturnInfoVO();
vo.setEmial(rs.getString("email"));
vo.setIp(rs.getString("ip"));
vo.setPlace(rs.getString("place"));
vo.setTime(new Date(rs.getInt("ctime")*1000L));
emailOpenReturnInfoVOs.add(vo);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(null != session)
session.close();
}
return emailOpenReturnInfoVOs;
}运行最后的结果:id = null ,email = null ;也就是e_toemail表中的所有数据都为null,但是第一张表中的数据能正常获取,求解啊。急急急有能在线请教的吗?可以联系我的Q: 526673470
解决方案 »
- 【紧急】已经加上mail.jar包 但是总是说找不到com/sun/mail/util/LineInputStream
- 表单与图片信息同步展现
- =======================java web 选择一个文件夹上传
- 关于Cglib实现AOP有个大疑惑
- spring jpa 延迟加载的问题
- org.hibernate.exception.GenericJDBCException: could not execute query怎么解决
- 我愿意有偿学习spring + ibatis,希望好心人帮助。
- 55555帮帮忙~~~~
- Struts高手、java高手请进,技术难题谁解决了有100分拿。
- 网站运行一段时间就卡的要死
- java ftp删除文件夹
- 弱弱的问下urlrewriter的问题
在vo打个断点、对比下
你在取出的数据的email字段后面加个别名 比如 a.email as email
然后再看看
count是聚合函数,如果select中除了count以外还有其它非聚合的字段存在,比如LZ的b.*,e.uid等,那么后面没有group by的话应该就是非法的
原来是:select u.id,u.email from e_toemail u 中,应该是:select u.id,u.email from edm.e_toemail u 当然,如果不加,在数据库中运行是完全没有问题的。但是在java中用spring注解一个数据源中跨数据库得必须加上。
select u.uid, count(u.ip) from xxx
看看能执行不?
我测试的语句:select u.uid, count(u.ip) from edm.edm_open_56 u结果:267 5谢谢,能通过。