IncorrectResultSetColumnCountException；
該報錯是映射結果集（ResultSet），所映射column(列)count(數目)錯誤，例如；
假設，你使用的映射方法所映射的是單列，但是你sql語句查詢出結果是多列，解析執行映射結果集，那這不就報錯飚紅了麼；
下面給出一個例子；
抓住Dao層就是一頓狂改，然後service業務層。。。
error；
public Products selectById(int id) {
String sql = "select * from products where pid =?";//單行單列的查詢使用queryForObject
// 通過id展示商品是使用 Templated query 是一行多列，sql 會和方法沖突
Products products = template.queryForObject(sql, Products.class, id);
return products;
}
更改以後；
dao層；
public List<Products> selectById(int id) {
String sql = "select * from products where pid =?";//單行單列的查詢 通過id展示商品是使用Templated query
List<Products> list = template.query(sql, new BeanPropertyRowMapper<>(Products.class), id);
return list;
}
service層；
//findById
public Products findById(int id) {
ProductsDao productsDao = new ProductsDao();
List<Products> list = productsDao.selectById(id);
Products products = null;//可能集合為空，資料庫中沒這id
try {
products = list.get(0);
} catch (Exception e) {
return null;
}
return products;
}
ok啦；
代碼附上；
本人使用的JDK9， MYSQL8，druid資料庫連接配接池&Template ； 完成一個基礎小案例；
bean類不在提供；表結構附上；
說明，bean類中局部使用參數數目不同的構造函數，可根據工具類和dao層檢視（比如addPro 增加商品時候，主鍵自增null,無需設定主鍵，構造中也無需主鍵id的）；
CREATE TABLE `products` (
`pid` int(11) NOT NULL AUTO_INCREMENT,
`pname` varchar(30) DEFAULT NULL,
`price` int(11) DEFAULT NULL,
`category` varchar(30) DEFAULT NULL,
PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
utils工具類；
package com.company.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
/**
* @auther SyntacticSugar
* @data 2018/9/16 0016下午 6:00
*/
public class ProductsUtils {
private static Connection conn = null;
private static DataSource source = null;
private static PreparedStatement statement = null;
private static ResultSet set = null;
private static JdbcTemplate template = null;
/**
* 擷取資源
*
* @return
*/
public static DataSource getDataSource() {
Properties p = new Properties();
try {
p.load(new FileReader("config/druid.properties"));
source = DruidDataSourceFactory.createDataSource(p);
conn = source.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
return source;
}
public static JdbcTemplate getJdbcTemplate(DataSource source) {
try {
template = new JdbcTemplate(source);
} catch (Exception e) {
e.printStackTrace();
}
return template;
}
//釋放資源
public static void release(ResultSet set, PreparedStatement statement, Connection conn) {
try {
if (set != null) {
set.close();
}
if (statement != null) {
statement.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
dao層；
package com.company.dao;
import com.company.bean.Products;
import com.company.utils.ProductsUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
/**
* @auther SyntacticSugar
* @data 2018/9/16 0016下午 5:59
*/
public class ProductsDao {
//擷取資源
private static DataSource source = ProductsUtils.getDataSource();
//擷取 template
private static JdbcTemplate template = ProductsUtils.getJdbcTemplate(source);
/**
* 通過 id 查詢商品，供service層使用
* @param id
* @return
*/
public List<Products> selectById(int id) {
String sql = "select * from products where pid =?";//單行單列的查詢 通過id展示商品是使用Templated query
List<Products> list = template.query(sql, new BeanPropertyRowMapper<>(Products.class), id);
return list;
}
/**
* 查詢ALL 商品
*
* @return
*/
public List<Products> selectAll() {
String sql = "select * from products ";
List<Products> list = template.query(sql, new BeanPropertyRowMapper<Products>(Products.class));
return list;
}
/**
* 更新商品
*/
public int updateProduct(Products products) {
String sql = "update products set pid=?,pname=?,price=?,category=?";
int update = template.update(sql, products.getPid(),products.getPname(),products.getPrice(),products.getCategory());
return update;
}
/**
* 删除商品
*/
public int deleteProductById(int id) {
String sql = "delete from products where pid=?";
int delete = template.update(sql, id);
return delete;
}
/**
添加商品
*/
public int addProduct(Products products) {
String sql = "insert into products values(null,?,?,?) ;";
int insert = template.update(sql,products.getPname(),products.getPrice(),products.getCategory());
return insert;
}
}
service層；
package com.company.service;
import com.company.bean.Products;
import com.company.dao.ProductsDao;
import java.util.List;
/**
* @auther SyntacticSugar
* @data 2018/9/16 0016下午 11:07
*/
public class ProductsService {
//添加商品
public int addProd(Products products) {
ProductsDao productsDao = new ProductsDao();
int addCount = productsDao.addProduct(products);
return addCount;
}
//删除商品
public int deleteProd(int id) {
ProductsDao productsDao = new ProductsDao();
int deleteCount = productsDao.deleteProductById(id);
return deleteCount;
}
//更新商品
public int updateProd(Products products) {
ProductsDao productsDao = new ProductsDao();
int updateCount= productsDao.updateProduct(products);
return updateCount;
}
//findALL
public List<Products> findALL() {
ProductsDao productsDao = new ProductsDao();
List<Products> list = productsDao.selectAll();
return list;
}
//findById
public Products findById(int id) {
ProductsDao productsDao = new ProductsDao();
List<Products> list = productsDao.selectById(id);
Products products = null;//可能集合為空，資料庫中沒這id
try {
products = list.get(0);
} catch (Exception e) {
return null;
}
return products;
}
}
web(UI層)；
package com.company.UserView;
import com.company.bean.Products;
import com.company.service.ProductsService;
import java.util.List;
import java.util.Scanner;
/**
* @auther SyntacticSugar
* @data 2018/9/16 0016下午 9:11
*/
public class ProductMain {
private static Scanner sc = new Scanner(System.in);
private static ProductsService productsService = new ProductsService();
public static void main(String[] args) {
while (true) {
System.out.println("請輸入編号");
System.out.println("1:添加商品\t2:删除商品\t3:更新商品\t4:查詢所有商品\t5:根據id查詢商品\t6:退出系統");
//
String s = sc.nextLine();
int i = 0;
try {
i = Integer.parseInt(s);
} catch (NumberFormatException e) {
System.out.println("輸入錯誤");
}
switch (i) {
case 1:
add();
break;
case 2:
deleteById();
break;
case 3:
updatePro();
break;
case 4:
seeAll();
break;
case 5:
seeById();
break;
case 6:
System.exit(0);
default:
System.out.println("輸入錯誤");
break;
}
}
}
private static void add() {
//鍵盤錄入
sop("請輸入pname");
String pname = sc.nextLine();
sop("請輸入price");
String pr = sc.nextLine();
int price = Integer.parseInt(pr);
sop("請輸入category");
String category = sc.nextLine();
Products products = new Products(pname, price, category);
int i = productsService.addProd(products);//調用service層的方法
showResult(i);
}
private static void deleteById() {
//鍵盤錄入
sop("請輸入要删除的id");
String idStr = sc.nextLine();
int id = Integer.parseInt(idStr);
int i = productsService.deleteProd(id);//調用service層方法
showResult(i);
}
private static void updatePro() {
//鍵盤錄入
sop("請輸入要更新的id");
String idStr = sc.nextLine();
int id = Integer.parseInt(idStr);
sop("請輸入更新的pname");
String pname = sc.nextLine();
sop("請輸入更新的price");
String pr = sc.nextLine();
int price = Integer.parseInt(pr);
sop("請輸入更新的category");
String category = sc.nextLine();
Products products = new Products(id, pname, price, category);
int i = productsService.updateProd(products);//調用service層方法
showResult(i);
}
private static void seeAll() {
List<Products> list = productsService.findALL(); //service層是findALL方法
for (Products products : list) {
sop(products);
}
}
private static void seeById() {
//鍵盤錄入
sop("請輸入要檢視商品的id");
String idStr = sc.nextLine();
int id = Integer.parseInt(idStr);
Products productsbyId = productsService.findById(id);
sop(productsbyId);
}
public static void sop(Object obj) {
System.out.println(obj);
}
/**
* 對傳回的應影響數進行判斷
*
* @param i
*/
private static void showResult(int i) {
if (i != 0) {
sop("操作成功");
} else {
sop("操作失敗");
}
}
}
運作測試；
檢視資料庫 ，瞅一瞅；
不一 一測試了，ok