(请先看文末)
1、建表如图:
2、创建实体类:
package com.entity;
import java.io.Serializable;
public class MyUser implements Serializable{
private String userid;
private String username;
private String password;
private String realname;
public MyUser(String userid, String username, String password,
String realname) {
super();
this.userid = userid;
this.username = username;
this.password = password;
this.realname = realname;
}
public MyUser() {
super();
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getUserid() {
return userid;
}
public void setUserid(String userid) {
this.userid = userid;
}
public String getRealname() {
return realname;
}
public void setRealname(String realname) {
this.realname = realname;
}
@Override
public String toString() {
return "MyUser [userid=" + userid + ", username=" + username
+ ", password=" + password + ", realname=" + realname + "]";
}
}
2、Dao层(JDBCUtil工具类在后面)
package com.dao;
import com.entity.MyUser;
public interface UserDao {
public Boolean insertexcel (MyUser user);
}
package com.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.dao.UserDao;
import com.entity.MyUser;
import com.util.JDBCUtil;
public class UserDaoImpl implements UserDao {
@Override
public Boolean insertexcel(MyUser user) {
// TODO Auto-generated method stub
Connection conn = null;
PreparedStatement pstm = null;
Boolean a = false;
try{
//获取连接
conn = JDBCUtil.getConn();
//书写SQL
String sql = "insert into t_user(userid,username,password,realname) values(?,?,?,?)";
//创建PreparedStatement执行SQL
pstm = conn.prepareStatement(sql);
pstm.setString(1,user.getUserid());
pstm.setString(2,user.getUsername());
pstm.setString(3,user.getPassword());
pstm.setString(4,user.getRealname());
pstm.executeUpdate();
a=true;
return a;
}catch(Exception e){
e.printStackTrace();
throw new RuntimeException("异常");
}finally{
JDBCUtil.close(null, pstm, null);
}
}
}
3、Service层
package com.service;
import java.io.File;
import java.util.List;
import com.entity.MyUser;
public interface UserService {
public List addCustomerAssign(File file);
}
package com.service.impl;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import com.dao.UserDao;
import com.dao.impl.UserDaoImpl;
import com.entity.MyUser;
import com.service.UserService;
import com.util.JDBCUtil;
public class UserServiceImpl implements UserService {
public List addCustomerAssign(File file)
{
List ls=new ArrayList();
jxl.Workbook rwb = null;
try{
InputStream is = new FileInputStream(file);
rwb = Workbook.getWorkbook(is);
Sheet rs = rwb.getSheet(0);
int rsRows = rs.getRows();
for(int i=1;i <rsRows;i++){//如第一行为属性项则从第二行开始取数据(int i=0 ;i <rsRows;i++)
String cell1=rs.getCell(0,i).getContents()+" ";
String cell2=rs.getCell(1,i).getContents()+" ";
String cell3=rs.getCell(2,i).getContents()+" ";
if(cell1!=null&&!cell1.equals(" ")&&cell2!=null&&!cell3.equals(" "))
{
MyUser ms=new MyUser();
ms.setUserid(rs.getCell(0,i).getContents());
ms.setUsername(rs.getCell(1,i).getContents());
ms.setPassword(rs.getCell(2,i).getContents());
ms.setRealname(rs.getCell(3,i).getContents());
ls.add(ms);
}
}
}catch(Exception e){
e.printStackTrace();
}
finally{
rwb.close();
}
return ls;
}
}
4、Servlet层(即Controller层)
package com.servlet;
import java.io.File;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.dao.UserDao;
import com.dao.impl.Into;
import com.dao.impl.UserDaoImpl;
import com.entity.MyUser;
import com.service.UserService;
import com.service.impl.UserServiceImpl;
import com.util.excel;
public class daoru extends HttpServlet{
public void doGet(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException {
doPost(request , response);
}
protected void doPost(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
UserDao dao = new UserDaoImpl();
UserService service = new UserServiceImpl();
Into in = new Into();
excel ex=new excel();
String path=new String(request.getParameter("excel").getBytes("ISO8859-1"),"UTF-8");//红色字体改为:gbk即可解决问题
path = "D:\\"+path;
System.out.println("获取的excel: "+path);
File file=new File(path);
List ls=service.addCustomerAssign(file);
Iterator iter=ls.iterator();
while(iter.hasNext())
{
MyUser ms=(MyUser)iter.next();
if(dao.insertexcel(ms))
System.out.println("成功");
else
System.out.println("失败");
}
}
}
5、web.xml配置
<servlet>
<servlet-name>daoru</servlet-name>
<servlet-class>com.servlet.daoru</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>daoru</servlet-name>
<url-pattern>/daoru</url-pattern>
</servlet-mapping>
6、页面代码
<body>
<form action="/web04_jdbc/daoru" method="post">
选择文件:<input type="file" name="excel" />
<input type="submit" value="导入" />
</form>
</body>
7、数据库连接配置文件 jdbc.properties
ps:此文件放在src根目录下,文件中不可有空格
jdbc.driverClassName=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:xe
jdbc.username=hr
jdbc.password=hr
8、JDBCUtil工具类
package com.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtil {
private static Properties prop = new Properties();
// 静态初始化代码块:这里面的代码只在类加载的时候执行一次
static {
// 1.加载驱动:将指定类的字节码文件加载到虚拟机中,只需要执行一次就可以
InputStream in = null;
try {
// 读取配置文件
in = JDBCUtil.class.getResourceAsStream("/jdbc.properties");
// 解析保存到Properties中
prop.load(in);
Class.forName(prop.getProperty("jdbc.driverClassName"));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
// 创建一个ThreadLocal对象: 将connection绑定到当前线程
private static ThreadLocal<Connection> tol = new ThreadLocal<Connection>();
// 创建连接,并返回给调用者
public static Connection getConn() {
try {
Connection conn = tol.get();
if(conn==null){// 说明是当前线程是第一次调用,还没有绑定连接到当前线程
// 2.创建连接
conn = DriverManager.getConnection(
prop.getProperty("jdbc.url"), prop.getProperty("jdbc.username")
, prop.getProperty("jdbc.password"));
// 将创建好的连接绑定到当前线程
tol.set(conn);
}
return conn;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}
}
// 释放资源
public static void close(ResultSet rs, Statement stm, Connection conn) {
try {
//手动避免空指针异常
if (rs != null) {
rs.close();
}
if (stm != null) {
stm.close();
}
if (conn != null) {
conn.close();
// 从当前线程移除
tol.remove();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close(Statement stm, Connection conn) {
try {
//手动避免空指针异常
if (stm != null) {
stm.close();
}
if (conn != null) {
conn.close();
// 从当前线程移除
tol.remove();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
注意:
1、此案例中,导入的excel文件后缀名只能是.xls 若为.xlsx则需另存为.xls(注意是另存,直接修改后缀名不起作用)否则无法导入成功。
2、要导入excel文件必须放在D盘根目录下。(若想导入其它目录下的文件,可修改Servlet层代码中的path。)
3、需在lib下导入两个jar包:
存在的问题:
1、本案例从前端只能获取到excel文件名,并不能获取到文件所在的全路径,所以需要在Servlet层代码中补全path。
2、只能导入.xls文件,不能导入.xlsx文件。
如果有哪位大佬知道解决以上两个问题的方式,,,,表面:不告诉我也没事。 实际:求求你教教我吧!!!!!求求你了!!!!
2021-05-26修改:
以上问题已解决,可参考此文章servlet+poi导入excel文件