ssm项目，我使用了mybatis逆向工程生成xml文件，但是我要增加求某个数据库表的数据条数，如何实现？
 
首先，先看看逆向工程自动生成的其中一条语句（parameterType我改了一下，但这不重要）：
 
  <select id="selectByPrimaryKey" parameterType="com.mandy.model.Article" resultMap="BaseResultMap">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
          This element was generated on Sun Feb 09 14:19:17 CST 2020.
        -->
        select fid, title, content, label, time, userid, username, readNum,likes
        from article
        where fid = #{fid,jdbcType=INTEGER}
    </select>
 
重要的是，所有自动生成的语句，都是resultMap这个类型，返回的是BaseResultMap，我就顺势改了一下，我以为查总数据条数是这样写的：resultMap返回Integer类型
 
<!--  SELECT COUNT(*) FROM 表名称。getArticlePageCount-->
  <select id="getArticlePageCount" parameterType="java.lang.Integer" resultMap="Integer">
    select count(*)
    from article
    where userid = #{userid,jdbcType=INTEGER}
  </select>
 
但是会报错，查了一下，最后改成了resultType
 
<!--  SELECT COUNT(*) FROM 表名称。getArticlePageCount-->
  <select id="getArticlePageCount" parameterType="java.lang.Integer" resultType="Integer">
    select count(*)
    from article
    where userid = #{userid,jdbcType=INTEGER}
  </select>
 
这样就是对的，正确运行，以防以后忘记，在此记录。

mysql

select sum(table_rows) from tables where TABLE_SCHEMA = databasename
oracle

SELECT NVL(SUM(num_rows),0) FROM SYS.ALL_TABLES T WHERE T.OWNER = username
pg

pg是每一个数据库拥有一个元数据表单，所以只能一个表单一个表单的统计
SELECT sum(r.reltuples) FROM pg_class r JOIN pg_namespace n ON (r.relnamespace = n.oid) WHERE r.relkind = 'r' AND n.nspname = 'public'
kingbase

kingbasees是每一个数据库拥有一个元数据表单，所以只能一个表单一个表单的统计
SELECT sum(r.reltuples) FROM sys_class r JOIN sys_namespace n ON (r.relnamespace = n.oid) WHERE r.relkind = 'r' AND n.nspname = 'public'
hive

查询不含分区表的总条数
select FORMAT(sum(tb.PARAM_VALUE),0)  
from TBLS t   
left join DBS d  
on t.DB_ID = d.DB_ID  
left join TABLE_PARAMS tb 
on t.TBL_ID = tb.TBL_ID   
where d.NAME='testzdsun' and tb.PARAM_KEY='numRows';  

查询含有分区表的总条数
select FORMAT(sum(a.PARAM_VALUE),0)  
from TBLS t   
left join DBS d  
on t.DB_ID = d.DB_ID  
left join PARTITIONS p  
on t.TBL_ID = p.TBL_ID   
left join PARTITION_PARAMS a  
on p.PART_ID=a.PART_ID  
where d.NAME='testzdsun' and a.PARAM_KEY='numRows'; 
代码如下：（就是代码很乱，而且很多不足的地方需要修改）

package cn.com.taiji.commons.quartz;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.ResourceBundle;


import org.apache.commons.lang3.StringUtils;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;




import cn.com.taiji.commons.utils.DicTools;
import cn.com.taiji.commons.utils.JdbcTools;
import cn.com.taiji.commons.utils.SSHUtils;
import cn.com.taiji.management.dao.AdminResourceDao;
import cn.com.taiji.management.entity.AdminResource;
import cn.com.taiji.management.entity.MonitorDataSource;
import cn.com.taiji.monitor.dataresource.dao.IRawRegionDao;
import cn.com.taiji.monitor.dataresource.dao.MonitorCountDao;
import cn.com.taiji.monitor.dataresource.dao.MonitorDataSourceDao;
import cn.com.taiji.monitor.dataresource.entity.MonitorCount;


/**
* @desc
* @para
* @author 
* @version 2018年6月8日 下午3:13:51
*/
public class TagMonitorsDataJob implements BaseJob{


 @Autowired
 private IRawRegionDao iRawRegionDao;
 @Autowired
 private AdminResourceDao adminResourceDao;
 @Autowired
 private MonitorDataSourceDao monitorDataSourceDao;
 @Autowired
 private MonitorCountDao monitorCountDao;
 
 private static Logger log = LoggerFactory.getLogger(TagMonitorsDataJob.class);
 private static ResourceBundle rb = ResourceBundle.getBundle("application", Locale.getDefault());
 
 @Override
 public void execute(JobExecutionContext context) throws JobExecutionException {
 log.info("定时执行监控统计条数===========================");
  try {
 Map<String,Long> map0 = this.findHiveCount();
 Map<String,Long> map1 = this.getDBCounts(DicTools.SOURCE_FLAG_ORIGINAL);
 Map<String,Long> map2 = this.getDBCounts(DicTools.SOURCE_FLAG_HANDLE);
 Map<String,Long> map3 = this.getDBCounts(DicTools.SOURCE_FLAG_ANALYSIS);
 
 //保存
 getMonitorCount(map1,DicTools.SOURCE_FLAG_ORIGINAL,map0);
 getMonitorCount(map2,DicTools.SOURCE_FLAG_HANDLE,map0);
 getMonitorCount(map3,DicTools.SOURCE_FLAG_ANALYSIS,map0);
 } catch (Exception e) {
 log.error("定时统计监控总条数报错"+e);
 }
 }


 private void getMonitorCount(Map<String,Long> map,String partition,Map<String,Long> map0) {
 long count = map0.containsKey(partition) == true ?map0.get(partition) : 0;
 MonitorCount monitorCount = new MonitorCount();
 int createDate = (int)(System.currentTimeMillis()/1000);
 monitorCount.setCreateDate(createDate);
 monitorCount.setPartition(partition);
 monitorCount.setDbTotalCount(map.get("dbTotalCount")+count);
 monitorCount.setFileTotalCount(map.get("fileTotalCount"));
 monitorCount.setFileTotalSize(map.get("fileTotalSize"));
 monitorCountDao.save(monitorCount);
 }
 //统计hive
 private Map<String,Long> findHiveCount() throws Exception{
 Map<String,Long> rel = new HashMap<String,Long>();
 Map<String,Long> mapTab = new HashMap<String,Long>();
 Map<String,Long> mapPartitionTab = new HashMap<String,Long>();
 List<AdminResource> admins = adminResourceDao.findByTypeIsType(DicTools.RS_STYLE_HIVEMETADATA);
 
 if(admins != null && admins.size() > 0 ) {
 for (AdminResource admin : admins) {
 String driverName = rb.getString("mysql.driver");
 String url = "jdbc:mysql://" + admin.getIp().trim() + ":" + admin.getPort().trim() + "/"+ admin.getInstance() + "?characterEncoding=utf-8";
 //查询不含分区的表单总条数
 //String sql1 = "select FORMAT(sum(tb.PARAM_VALUE),0) " + 
 String sql1 = "select d.NAME, sum(tb.PARAM_VALUE) " + 
 "from TBLS t " + 
 "left join DBS d " + 
 "on t.DB_ID = d.DB_ID " + 
 "left join TABLE_PARAMS tb " + 
 "on t.TBL_ID = tb.TBL_ID " + 
 "where tb.PARAM_KEY='numRows' " +
 "group by d.NAME";
 //弊端假如key项目会替换
 mapTab.putAll(getHiveCounts(driverName, url, admin.getUserName(), admin.getPwd(), sql1));
 //String sql2 = "select FORMAT(sum(a.PARAM_VALUE),0) " + 
 String sql2 = "select d.NAME, sum(a.PARAM_VALUE) " + 
 "from TBLS t " + 
 "left join DBS d " + 
 "on t.DB_ID = d.DB_ID " + 
 "left join PARTITIONS p " + 
 "on t.TBL_ID = p.TBL_ID " + 
 "left join PARTITION_PARAMS a " + 
 "on p.PART_ID=a.PART_ID " + 
 "where a.PARAM_KEY='numRows' " +
 "group by d.NAME";
 mapPartitionTab.putAll(getHiveCounts(driverName, url, admin.getUserName(), admin.getPwd(),sql2));
 }
 //用map去数据库匹配然后进行累加操作
 mapTab = this.combiner(mapTab, mapPartitionTab);
 List<Object []> list = monitorDataSourceDao.getDBPartition(mapTab.keySet(),DicTools.SOFT_STYLE_HIVE);
 Map<String,String> mapPartition = new HashMap<String,String>();
 if(list != null && !list.isEmpty()) {
 for (Object[] obj : list) {
 mapPartition.put(obj[0]==null?"":obj[0].toString(), obj[1]==null?"":obj[1].toString());
 }
 }
 for (String key : mapPartition.keySet()) {
 if(mapTab.containsKey(key)) {
 if(rel.containsKey(mapPartition.get(key))) {
 rel.put(mapPartition.get(key),rel.get(key) + mapTab.get(key));
 }else {
 rel.put(mapPartition.get(key), mapTab.get(key));
 }
 }
 }
 }
 return rel;
 }
 
 public Map<String,Long> combiner(Map<String,Long> map1,Map<String,Long> map2) {
 for(String key : map1.keySet()) {
 if(map2.containsKey(key)) {
 map1.put(key, map1.get(key)+map2.get(key));
 }
 }
 return map1;
 }
 
 public Map<String,Long> getDBCounts(String partition) throws Exception {
 Map<String,Long> map = new HashMap<String,Long>();
 long dbTotalCount = 0;
 long fileTotalCount = 0;
 long fileTotalSize = 0;//以kb为单位
 
 List<Object[]> list = iRawRegionDao.getJobMonitorCounts(partition);
 List<MonitorDataSource> monitorDataSources = monitorDataSourceDao.getBySoftTypeAndSourceFlag
 (DicTools.SOFT_STYLE_POSTGRESQL,Integer.parseInt(partition));
 
 //postgresql统计
 if(monitorDataSources != null && monitorDataSources.size() > 0) {
 for (MonitorDataSource monitorDataSource : monitorDataSources) {
 String driverName = rb.getString("postgresql.driver");
 // "jdbc:sqlserver://192.168.58.93:1433;DatabaseName=test1",
 String url = "jdbc:postgresql://"+monitorDataSource.getIp()+":"+monitorDataSource.getPort()+"/"+monitorDataSource.getDataInstance();
 
 String sql = "SELECT sum(r.reltuples) "+
 "FROM pg_class r JOIN pg_namespace n ON (r.relnamespace = n.oid) "+  
 "WHERE r.relkind = 'r' AND n.nspname = 'public'";
 dbTotalCount += getCounts(driverName, url, monitorDataSource.getDbUsername(), monitorDataSource.getDbPassword(), sql); 
 }
 }
 
 //kingbase统计
 List<MonitorDataSource> kingbaseDB = monitorDataSourceDao.getBySoftTypeAndSourceFlag
 (DicTools.SOFT_STYLE_KINGBASE,Integer.parseInt(partition));
 if(kingbaseDB != null && kingbaseDB.size() > 0) {
 for (MonitorDataSource monitorDataSource : kingbaseDB) {
 String driverName = rb.getString("kingbase.driver");
 // "jdbc:sqlserver://192.168.58.93:1433;DatabaseName=test1",
 String url = "jdbc:kingbase://"+monitorDataSource.getIp()+":"+monitorDataSource.getPort()+"/"+monitorDataSource.getDataInstance();
 String sql = "SELECT sum(r.reltuples) "+
 "FROM sys_class r JOIN sys_namespace n ON (r.relnamespace = n.oid) "+  
 "WHERE r.relkind = 'r' AND n.nspname = 'public'";
 dbTotalCount += getCounts(driverName, url, monitorDataSource.getDbUsername(), monitorDataSource.getDbPassword(), sql); 
 }
 }
 
 //oracle mysql nfs 统计
 if(list != null && list.size() > 0) {
 //0 admin_id 1:软件类型 2:实例
 for (Object[] obj : list) {
 String admin_id = obj[0] == null? "" : obj[0].toString();
 String softType = obj[1] == null? "" : obj[1].toString();
 String instance = obj[2] == null? "" : obj[2].toString();
 if(DicTools.SOFT_STYLE_HIVE.equals(softType) || DicTools.SOFT_STYLE_POSTGRESQL.equals(softType) || 
 (DicTools.SOFT_STYLE_NFS.equals(softType) && (DicTools.SOURCE_FLAG_HANDLE.equals(partition) || DicTools.SOURCE_FLAG_HANDLE.equals(partition)))) {
 continue;
 }
 //查询adminResource
 AdminResource admin = adminResourceDao.findOne(admin_id);
 if(admin == null) {
 continue;
 }
 //每一个case中进行 数据库/文件系统 连接并返回各个数据库/文件系统的条数/大小
 switch (softType) {
 case DicTools.SOFT_STYLE_ORACLE:
 String driverName = rb.getString("oracle.driver");
 // jdbc:oracle:thin:@192.168.58.111:1521/orcl
 String url = "jdbc:oracle:thin:@" + admin.getIp().trim() + ":" + admin.getPort().trim() + "/"+ admin.getInstance();
 String sql = "SELECT NVL(SUM(num_rows),0) FROM SYS.ALL_TABLES T WHERE T.OWNER in ("+instance.toUpperCase()+")";
 dbTotalCount += getCounts(driverName, url, admin.getUserName(), admin.getPwd(), sql);
 break;
 case DicTools.SOFT_STYLE_MYSQL:
 driverName = rb.getString("mysql.driver");
 // jdbc:mysql://192.168.71.57:3306/zyzx?characterEncoding=utf-8
 //url = "jdbc:mysql://" + admin.getIp().trim() + ":" + admin.getPort().trim() + "/"+ admin.getInstance() + "?characterEncoding=utf-8";
 url = "jdbc:mysql://" + admin.getIp().trim() + ":" + admin.getPort().trim() + "/information_schema"+ "?characterEncoding=utf-8";
 sql = "select sum(table_rows) from tables where TABLE_SCHEMA in ("+instance+")";
 dbTotalCount += getCounts(driverName, url, admin.getUserName(), admin.getPwd(), sql);
 break;
 /*case DicTools.SOFT_STYLE_SQLSERVER:
 driverName = rb.getString("sqlserver.driver");
 // "jdbc:sqlserver://192.168.58.93:1433;DatabaseName=test1",
 url = "jdbc:sqlserver://" + admin.getIp().trim() + ":" + admin.getPort().trim()+ ":DatabaseName=" + admin.getInstance();
 sql = "";
 incNum += getCounts(driverName, url, admin.getUserName(), admin.getPwd(), sql);
 break;
 case DicTools.SOFT_STYLE_POSTGRESQL:
 driverName = rb.getString("postgresql.driver");
 // "jdbc:sqlserver://192.168.58.93:1433;DatabaseName=test1",
 url = "jdbc:postgresql://"+admin.getIp()+":"+admin.getPort()+"/"+admin.getInstance();
 sql = "";
 dbTotalCount += getCounts(driverName, url, admin.getUserName(), admin.getPwd(), sql);
 break;
 case DicTools.SOFT_STYLE_HIVE:
 // "jdbc:hive2://192.168.58.110:10000/testzdsun",
 //url = "jdbc:hive2://" + admin.getIp().trim() + ":" + admin.getPort().trim() + "/"+ admin.getInstance();
 break;*/
 case DicTools.SOFT_STYLE_NFS:
 int port = StringUtils.isNotBlank(admin.getPort())?Integer.parseInt(admin.getPort()):22;
 String commond = "ls -lR "+admin.getInstance()+"| grep \"^-\" | wc -l";
 String ls = SSHUtils.execCommandByJSch(admin.getIp(), port,admin.getUserName(), admin.getPwd(),commond);
 fileTotalCount += Long.valueOf(ls.replaceAll("\n", ""));
 String fileSize = SSHUtils.execCommandByJSch(admin.getIp(), port,admin.getUserName(), admin.getPwd(), 
 "du -sm "+admin.getInstance()+" |awk '{print $1}'");
 fileTotalSize += Long.valueOf(fileSize.replaceAll("\n", ""));
 break;
 /*case DicTools.SOFT_STYLE_HDFS:
 break;*/
 default:
 log.error("该数据库类型或者文件类型("+softType+")不支持！");
 break;
 //throw new Exception("该数据库类型或者文件类型不支持！");
 }
 }
 }
 
 map.put("dbTotalCount", dbTotalCount);
 map.put("fileTotalCount", fileTotalCount);
 map.put("fileTotalSize", fileTotalSize);
 return map;
 }
 
 //返回条数信息
 private long getCounts(String driverName,String url,String user,String pwd,String sql) throws Exception{
 Connection conn = null;
 Statement st = null;
 ResultSet rs = null;
 long rel = 0;
 try {
 //注册驱动
 JdbcTools.registerDriver(driverName);
 //获取连接
 conn = JdbcTools.getConnection(url,user,pwd);
 //创建包边
 st = conn.createStatement();
 //执行查询
 rs = st.executeQuery(sql);
 while(rs.next()) {
 rel = Long.valueOf(rs.getString(1).replaceAll("\\D", ""));
 }
 } catch (Exception e) {
 log.error("统计数据库条数报错"+e);
 throw new Exception("统计数据库条数报错！");
 }finally {
 JdbcTools.release(conn, st, rs);
 }
 return rel;
 }
 
 
 //返回条数信息
 private Map<String,Long> getHiveCounts(String driverName,String url,String user,String pwd,String sql) throws Exception{
 Connection conn = null;
 Statement st = null;
 ResultSet rs = null;
 Map<String,Long> map = new HashMap<String,Long>();
 try {
 //注册驱动
 JdbcTools.registerDriver(driverName);
 //获取连接
 conn = JdbcTools.getConnection(url,user,pwd);
 //创建包边
 st = conn.createStatement();
 //执行查询
 rs = st.executeQuery(sql);
 while(rs.next()) {
 String key = rs.getString(1);
 long value = Long.valueOf(rs.getString(2).replaceAll(",", ""));
 map.put(key, value);
 }
 } catch (Exception e) {
 log.error("统计数据库条数报错"+e);
 throw new Exception("统计数据库条数报错！");
 }finally {
 JdbcTools.release(conn, st, rs);
 }
 return map;
 }
}

第一种方式：将指针移动到最后一位，获取该位置上的行数。
 
String sql = "select * from users";    //1、获取所有行的数据
con = super.getConnection();
int total;
try{
    ps = con.prepareStatement(sql); 
    rs = ps.executeQuery(sql);
    rs.last();    //2、将rs的指标移动到最后一位
    total = rs.getRow();    //3、获取当前数据的行数并赋值给total
}catch(Exception e) {
    throw new RuntimeException(e);
}finally{
    super.closeAll(rs, ps, con);
}
return total;
 
第二种方式：遍历累加
 
String sql = "select * from users";     //1、获取所有数据
con = super.getConnection();
int count = 0;
try{
    ps = con.prepareStatement(sql);
    rs = ps.executeQuery(sql);
    while(rs.next()){
        ++count;    //2、遍历累加
    }
}catch(Exception e) {
    throw new RuntimeException(e);
}finally{
    super.closeAll(rs, ps, con);
}
return count ;
 
第三种方式
 
String sql = "select count(*) from users";
con = super.getConnection();
int count = 0;
try{
    ps = con.prepareStatement(sql);
    rs = ps.executeQuery(sql);
    rs.next();
    count = rs.getInt(1);
}catch(Exception e) {
    throw new RuntimeException(e);
}finally{
    super.closeAll(rs, ps, con);
}
return count ;
 
参考：https://blog.csdn.net/w541826816/article/details/24433669
 
作者：人笨不由我

<?php
$serverName = "";
$username = "root";//linux数据库名称
$password = "XXXXXXX";//数据库密码
$dbname = "XXXXXXX";//自己创建的数据库名
// 创建连接
$conn = new mysqli($serverName,$username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}	 
$program_char = "utf8" ;
mysqli_set_charset( $conn , $program_char );  //输出中文格式 否则乱码

 $sql = "SELECT COUNT(*) FROM accesscontrol"; //获取某一张表的所有数据
 $zyvalue = $conn->query($sql);  
			while ($rownumval=$zyvalue->fetch_assoc()) {   //输出每一行数据 
			 var_dump($rownumval['COUNT(*)']); //获取数据库总条数
			} 
?>