Java从excel表获取数据传入数据库

一.连接数据库

package com.javen.db;

import java.beans.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.xml.stream.events.StartElement;

public class DBhepler {
	static String updata="15";
    static Connection conn;
    static ResultSet res ;
    //连接数据库
    public void DataBase() {
    	String url= "jdbc:mysql://localhost:3306/xinxi";
    	String driver = "com.mysql.cj.jdbc.Driver";
    	String user = "root";
	    String password = "123456";
    	
        try {
                Class.forName(driver);
                conn = (Connection) DriverManager.getConnection(url+"?user="+user+"&password="+password+"&useSSL = false&autoReconnect = true&useUnicode=true&characterEncoding=utf-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC"); 
                if(!conn.isClosed())
		              System.out.println("连接数据库成功!"); 
            } catch (ClassNotFoundException e) {
                  System.err.println("装载 JDBC/ODBC 驱动程序失败。" );  
                e.printStackTrace();
            } catch (SQLException e) {
                System.err.println("无法连接数据库" ); 
                e.printStackTrace();
            }
    }
    // 查询My SQL中数据表
    public ResultSet Search(String sql, String str[]) {
        DataBase();
        try {
            PreparedStatement pst =conn.prepareStatement(sql);
            if (str != null) {
                for (int i = 0; i < str.length; i++) {
                    pst.setString(i + 1, str[i]);
                }
            }
            res = pst.executeQuery();
            System.out.println(res.getString(1)+" "+res.getString(2)+" "+res.getString(3));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;//返回单个查询对象
    }
    
    //给数据库增添删改
    //执行sql语句--增加数据
    public int AddU(String sql, String str[]) {
        int a = 0;
        DataBase();
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            if (str != null) {
                for (int i = 0; i < str.length; i++) {
                    pst.setString(i + 1, str[i]);
                }
            }
            a = pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return a;
    }

二.数据实体创建

package com.javen.entity;


    public class renyuan {
    	
        private int id;//工号
        private String name;//姓名
        private int age;//年龄
        private String sex;//性别
        private String time;//参加工作时间
        private int salary;//薪资
        private String position;//职位
    
    public renyuan() {}
    public renyuan(int id, String name, int age,String sex, String time,int salary,String position) 
    {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
        this.time = time;
        this.salary = salary;
        this.position = position;
    }
    
    public String toString() {
        return "renyuan[id=" + id + ", name=" + name + ",age=" + age + ", sex=" + sex + ", time=" + time + ",salary=" + salary + ",position=" + position + "]";
    }
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex =sex;
    }
    public String getTime() {
        return time;
    }
    public void setTime(String time) {
        this.time =time;
    }
    public int getSalary() {
        return salary;
    }
    public void setSalary(int salary) {
        this.salary = salary;
    }
    public String getPosition() {
        return position;
    }
    public void setPosition(String position) {
        this.position =position;
    }
  }
    

三.从excel 表获取数据

package com.javen.service;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;

import com.javen.db.DBhepler;
import com.javen.entity.renyuan;
import com.javen.entity.chuchai;
import com.javen.entity.shuidian;
import com.javen.entity.weather;
import com.mysql.cj.jdbc.CallableStatement;
import com.sun.beans.util.Cache;
import com.sun.javafx.collections.MappingChange.Map;
import com.sun.xml.internal.ws.wsdl.writer.document.Types;

public class Service {
 //获取人员表excel表中所有数据
    public static List<renyuan> getAllByExcel1(String file){
        List<renyuan> list=new ArrayList<renyuan>();
        try {
            Workbook rwb=Workbook.getWorkbook(new File(file));
            Sheet rs=rwb.getSheet(0);
            
            int rows=rs.getRows();//得到所有的行
            int clos=rs.getColumns();//得到所有的列
            
            System.out.println("excel 表中列数："+clos+"   行数:"+rows);
            System.out.println("获取人员表数据成功！");
            for (int i = 1; i < rows; i++) {
                for (int j = 0; j < clos; j++) {
                    //第一个是列数，第二个是行数
                    String id=rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
                    String name=rs.getCell(j++, i).getContents();
                    String age=rs.getCell(j++, i).getContents();
                    String sex=rs.getCell(j++, i).getContents();
                    String time=rs.getCell(j++, i).getContents();
                    String salary=rs.getCell(j++, i).getContents();
                    String position=rs.getCell(j++, i).getContents();                  
                    list.add(new renyuan(Integer.parseInt(id), name,Integer.parseInt(age), sex,time, Integer.parseInt(salary),position));     
                    //在控制台输出获取的数据
                    System.out.println("工号："+id+"   姓名 :"+name+"   年龄 :"+age+"   性别  :"+sex+"  参加工作时间 ："+time+"   薪资 :"+salary+"   职位  :"+position);
                }      
            }
        } catch (Exception e) {
            e.printStackTrace();
        } 
        return list;
    }

四.将数据传输到My Sql 数据库
/*

• 对数据库执行sql语句
  */
  package com.javen.excel;

import java.util.List;
import com.javen.db.DBhepler;
import com.javen.entity.renyuan;
import com.javen.service.Service;

public class TestExcel{
    public static void main(String[] args) {
        //调用getAllByExcel1函数得到excel表格中所有的数据
        List<renyuan> listExcel1=Service.getAllByExcel1("D://信息系统集成 蓝牙天气//人员表.xls");
        //连接数据库
        DBhepler db=new DBhepler();   
    
        //人员表插入数据库
        for (renyuan renyuan : listExcel1) {
        //插入SQL语句
                String sql="insert into renyuan (工号,姓名,年龄,性别,参加工作时间,薪资,职位) values(?,?,?,?,?,?,?)";
                String[] str=new String[]{renyuan.getId()+"",renyuan.getName(),renyuan.getAge()+"",renyuan.getSex(),renyuan.getTime(),renyuan.getSalary()+"",renyuan.getPosition()};
                db.AddU(sql, str);  
                System.out.println("插入人员信息成功！");
            }       
        }
    }