Excel读写入数据库以及相关查询操作

package com.softeem.dbutils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import java.sql.Statement;
import java.util.List;
import java.util.Properties;

import org.apache.commons.dbcp2.BasicDataSource;



/**
 * DBCP数据库连接工具类
 * 依赖
 * 1> mysql驱动
 * 2> dbcp相关插件
 * @author DXY
 *
 */

public class DBUtils {

	// 连接数据库基本属性
	private static String driver;
	private static String url;
	private static String username;
	private static String password; 
	private static BasicDataSource bds;// 数据源
	// 连接池属性
	private static int initSize;
	private static int maxSize;
	private static int maxIdle;
	private static long maxWait;
	
	static{
		init();
	}
	
	public static void init(){
		try {
			// 创建数据源对象
			bds = new BasicDataSource();
			// 加载属性文件,获取属性信息
			Properties props = new Properties();
			props.load(DBUtils.class.getResourceAsStream("jdbc.properties"));
			driver = props.getProperty("driver");
			url = props.getProperty("url");
			username = props.getProperty("user");
			password = props.getProperty("password");
			
			initSize = Integer.parseInt(props.getProperty("initSize"));
			maxSize = Integer.parseInt(props.getProperty("maxSize"));
			maxIdle = Integer.parseInt(props.getProperty("maxIdle"));
			maxWait = Long.parseLong(props.getProperty("maxWait"));
			
			// 设置驱动类路径
			bds.setDriverClassName(driver);
			bds.setUrl(url);
			bds.setUsername(username);
			bds.setPassword(password);
			
			bds.setInitialSize(initSize);	// 初始连接数
			bds.setMaxTotal(maxSize);	// 最大连接数
			bds.setMaxIdle(maxIdle);		// 最大闲置连接数
			bds.setMaxWaitMillis(maxWait);// 等待获取连接的最大时间(ms)
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	// 获取连接
	public static Connection getConn(){
		if(bds == null || bds.isClosed()){
			init();
		}
		try {
			return bds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	//封装资源回收的方法
		public static void close(ResultSet rs,Statement stat,Connection conn){
		
			try {
				if(rs != null) rs.close();
				if(stat != null) stat.close();
				if(conn != null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		
	/**
	 * 通用增删改	
	 * @param conn
	 * @param sql
	 * @param objs
	 * @return
	 */
	public static boolean execUpdate(Connection conn, String sql, Object ...objs){
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			for (int i = 0; i < objs.length; i++) {
				ps.setObject(i+1, objs[i]);
			}
			int i = ps.executeUpdate();
			return i > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	// 封装查询操作
	public static <T> List<T> execQuery(String sql,CallBack<T> call, Object ...params){
		Connection conn = DBUtils.getConn();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i+1, params[i]);
			}
			ResultSet rs = ps.executeQuery();
			return call.getDatas(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	// 查询单个对象
	public static <T> T execQueryOne(String sql,CallBack<T> call, Object ...params){
		Connection conn = DBUtils.getConn();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i+1, params[i]);
			}
			ResultSet rs = ps.executeQuery();
			return call.getData(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	// JDK1.8+新特性
	public interface CallBack<T>{
		// public List<T> getDatas(ResultSet rs);
		default List<T> getDatas(ResultSet rs) {
			return null;
			}
		default T getData(ResultSet rs) {
			return null;
			}
	}
	
	// 低版本JDK 用抽象类实现
//	public static abstract Callback<T>{
//		default List<T> getDatas(ResultSet rs) {
//			return null;
//			}
//		default T getData(ResultSet rs) {
//			return null;
//			}
//	}	
	
}

package com.softeem.homework;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Pattern;

// 将String类型转换的 utils类
public class Tools {
	
	static SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");

	// -->int
	public static int getInt(String s){
		if(Pattern.matches("^\d*$",s)){
			return Integer.parseInt(s);
		}
		return 0;
	}
	
	// -->double
	public static double getDouble(String s){
		if(Pattern.matches("^\d+\.??\d+$", s)){
			return Double.parseDouble(s);
		}
		return 0.0;
	}
	
	// -->Date
	public static Date getDate(String s)
	{
		Date date = null;
		try {
			if(s != null && !"".equals(s)){
				date = sdf.parse(s);
			}
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return date;
	}
	
	// -->BigDecimal
	public static BigDecimal getBigDecimal(String s)
	{
		if(Pattern.matches("^\d+\.??\d+$", s)){
			return new BigDecimal(s);
		}
		return new BigDecimal("0.0");
	}


	//将Date类型-->String类型
	public static String getStringDate(Date d){
		if(d != null){
			return sdf.format(d);
		}
		return "";
	}

}

package com.softeem.homework;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

// Excel读取 --> 文件流 --> 数据库
public class ExcelRead {
	
	// 获取到Excel中的数据
	public List<Goods> readGoods(File file){
		List<Goods> list = new ArrayList<>();
		Goods goods = null;
		Workbook workbook = null;
		
		try {
			workbook = Workbook.getWorkbook(file);		// 创建一个工作簿
			Sheet sheet = workbook.getSheet("Sheet1"); 	// 获取指定名称的表单
			int rows = sheet.getRows();					// 获取总行数
			for(int i = 1; i < rows; i++){
				goods = new Goods();
				String s1 = sheet.getCell(0,i).getContents();
				String s2 = sheet.getCell(1,i).getContents();
				String s3 = sheet.getCell(2,i).getContents();
				String s4 = sheet.getCell(3,i).getContents();
				String s5 = sheet.getCell(4,i).getContents();
				String s6 = sheet.getCell(5,i).getContents();
				String s7 = sheet.getCell(6,i).getContents();
				
				goods.setId(Tools.getInt(s1));
				goods.setGoodsname(s2);
				goods.setPrice(Tools.getBigDecimal(s3));
				goods.setOffset(Tools.getDouble(s4));
				goods.setTime(Tools.getDate(s5));
				goods.setCounts(Tools.getInt(s6));
				goods.setCid(Tools.getInt(s7));
				
				list.add(goods);
			}
		} catch (BiffException | IOException e) {
			e.printStackTrace();
		} finally {
			if(workbook != null) workbook.close();
		}
		return list;
	}
	
	public List<Types> readTypes(File file){
		List<Types> list = new ArrayList<>();
		Types types = null;
		Workbook workbook = null;
		
		try {
			workbook = Workbook.getWorkbook(file);		// 创建一个工作簿
			Sheet sheet = workbook.getSheet("Sheet1"); 	// 获取指定名称的表单
			int rows = sheet.getRows();					// 获取总行数
			for (int i = 1; i < rows; i++) {
				types = new Types();
				String s1 = sheet.getCell(0,i).getContents();
				String s2 = sheet.getCell(1,i).getContents();
				
				types.setId(Tools.getInt(s1));
				types.setCname(s2);
				
				list.add(types);
			}
		} catch (BiffException | IOException e) {
			e.printStackTrace();
		} finally {
			if(workbook != null) workbook.close();
		}
		return list;
	}
}

package com.softeem.homework;

import java.io.File;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import com.softeem.dbutils.DBUtils;

/**
 * 批处理操作
 * 		将Excel文件中所得到的数据存到数据库中
 * @author lx
 *
 */
public class Transfer {

	// 将Excel中获取到的List<Goods> 导入到数据库中
	public void batch(){
		List<Goods> list= new ExcelRead().readGoods(new File("C:\Users\lx\Desktop\练习\商品表.xls"));
		Connection conn = DBUtils.getConn();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("insert into tb_goods(id,goodsname,price,offset,time,counts,cid) values(?,?,?,?,?,?,?)");
			for (Goods goods : list) {
				ps.setInt(1, goods.getId());
				ps.setString(2, goods.getGoodsname());
				ps.setBigDecimal(3, goods.getPrice());
				ps.setDouble(4, goods.getOffset());
				if(goods.getTime() != null){
					ps.setDate(5, new java.sql.Date(goods.getTime().getTime())); // 注:Date类型的转换
				}else{
					ps.setDate(5, new Date(0));
				}
				ps.setInt(6, goods.getCounts());
				ps.setInt(7, goods.getCid());
				ps.addBatch();
				int i = 0;
				if(i % 50 == 0){
					ps.executeBatch(); 	// 先执行一批更新
					ps.clearBatch();	// 清空缓冲区
				}
			}
			System.out.println("商品表导入成功!");
			ps.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, ps, conn);
		}
	}
	
	// 将Excel中获取到的List<Types> 导入到数据库中
	public void batch2(){
		List<Types> list = new ExcelRead().readTypes(new File("C:\Users\lx\Desktop\练习\类别表.xls"));
		Connection conn = DBUtils.getConn();
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement("insert into tb_types(id,cname) values(?,?)");
			for (Types types : list) {
				ps.setInt(1, types.getId());
				ps.setString(2, types.getCname());
				ps.addBatch();
			}
			System.out.println("类别表导入成功!");
			ps.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, ps, conn);
		}
	}
	
	public static void main(String[] args) {
		new Transfer().batch();
//		new Transfer().batch2();
	}
	
}

package com.softeem.homework;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.softeem.dbutils.DBUtils;
import com.softeem.dbutils.DBUtils.CallBack;

//2.完成商品的检索相关功能
//		1.根据分类，显示分类下所有的商品信息，按照库存量从低到高排序(提供补货依据)
//		2.模糊搜索,根据商品信息(名称或类别)
public class FindByCounts {

//	1.根据分类，显示分类下所有的商品信息，按照库存量从低到高排序(提供补货依据)
	public List<Goods> find1(int id,int currentPage,int pageSize){
		return DBUtils.execQuery("select * from tb_goods where cid = ? order by counts ASC limit ?,?", new CallBack<Goods>(){
			@Override
			public List<Goods> getDatas(ResultSet rs) {
				List<Goods> list = new ArrayList<>();
				try {
					Goods goods = null;
					while(rs.next()){
						goods = new Goods(rs.getInt("id"),rs.getString("goodsname"),rs.getBigDecimal("price"),rs.getDouble("offset"),rs.getDate("time"),rs.getInt("counts"),rs.getInt("cid"));
						list.add(goods);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return list;
			}
		}, id,(currentPage-1)*pageSize,pageSize);
	}
	
//	2.模糊搜索,根据商品信息(名称或类别)
	public List<Goods> find2(String s,int currentPage,int pageSize){
		return DBUtils.execQuery("select * from tb_goods where goodsname like ? limit ?,?", new CallBack<Goods>(){
			@Override
			public List<Goods> getDatas(ResultSet rs) {
				List<Goods> list = new ArrayList<>();
				try {
					Goods goods = null;
					while(rs.next()){
						goods = new Goods(rs.getInt("id"),rs.getString("goodsname"),rs.getBigDecimal("price"),rs.getDouble("offset"),rs.getDate("time"),rs.getInt("counts"),rs.getInt("cid"));
						list.add(goods);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return list;
			}
		},"%"+s+"%",(currentPage-1)*pageSize,pageSize);
	}
	
	public List<Types> find3(String s,int currentPage,int pageSize){
		return DBUtils.execQuery("select * from tb_types where cname like ? limit ?,?", new CallBack<Types>(){
			@Override
			public List<Types> getDatas(ResultSet rs) {
				List<Types> list = new ArrayList<>();
				try {
					Types types = null;
					while(rs.next()){
						types = new Types(rs.getInt("id"),rs.getString("cname"));
						list.add(types);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return list;
			}
		},"%"+s+"%",(currentPage-1)*pageSize,pageSize);
	}
	
	public static void main(String[] args) {
//		List<Goods> list = new FindByCounts().find1(1,1,10);
//		for (Goods goods : list) {
//			System.out.println(goods);
//		}
		
		List<Types> list2 = new FindByCounts().find3("鞋", 1, 10);
		for (Types types : list2) {
			System.out.println(types);
		}
		
		List<Goods> list = new FindByCounts().find2("鞋",1,10);
		for (Goods goods : list) {
			System.out.println(goods);
		}
	}
}

最后

以上就是呆萌蚂蚁为你收集整理的Excel读写入数据库以及相关查询操作的全部内容，希望文章能够帮你解决Excel读写入数据库以及相关查询操作所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。