java把excel表的数据导入到mysql数据库

用java把excel表的数据导入到数据库（可能有些类型还没考虑完全，抽空写的）

————————————————————————————代码下载————————————————————————————————

简单的思路

一、先获取数据库表的字段名、主键名，

二、读取excel文件的数据（注意：excel文件的数据表字段的顺序与该表的顺序一致，有空再做导出）

三、拼接sql

上代码：

一、先连接数据库

package com.cn.wjq.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class DBConnectUtil {

	private static String driver;
	
	private static String username;
	
	private static String pwd;
	
	private static String url;
	
	static {
		try {
			ClassLoader classLoader = DBConnectUtil.class.getClassLoader();
			
			InputStream resourceAsStream = classLoader.getResourceAsStream("config/props/db.properties");
			
			Properties properties=new Properties();
			
			properties.load(resourceAsStream);
			
			url = properties.getProperty("url");
			
			username = properties.getProperty("user");
			
			pwd = properties.getProperty("password");
			
			driver = properties.getProperty("driver");
			
			Class.forName(driver);
		}catch (IOException e) {

			e.printStackTrace();
		} catch (ClassNotFoundException e) {

			e.printStackTrace();
		}
	}
	
	public static Connection getConnection() throws SQLException {
		Connection conn = (Connection) DriverManager.getConnection(url, username, pwd);
		if (conn == null) {
			System.out.println("Failed to connect database...");
		} else {
			System.out.println("database connected successful...");
		}
		return conn;
	}
	
	public static void release(ResultSet rs, PreparedStatement sta, Connection conn) {

		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (sta != null) {
			try {
				sta.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println("Resource release successful...");
	}

	public static void release(PreparedStatement sta, Connection conn) {
		if (sta != null) {
			try {
				sta.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println("Resource release successful...");
	}
	
}

二、获取表的信息（当时没想好，写了个生成实体类代码（也就拼接））

package com.cn.wjq.util;

import java.io.FileOutputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.cn.wjq.model.BeanInfo;
import com.cn.wjq.model.ClassType;
import com.cn.wjq.model.ColumnInfo;

public class Generator {

	private final static Logger LOGGER = LoggerFactory.getLogger(DatabaseUtil.class);

	private static final String SQL = "SELECT * FROM ";

	public static final String packagePath = "com.cn.wjq.beam"; // 生成文件路径
	public static final String nextLine = "\r\n";
	public static final String tab = "\t";
	public static final String towtab = "\t\t";
	public static final String ent = "\r\n\t";
	public static final String pub = "public ";
	public static final String pri = "private ";
	public static final String imp = "import ";
	public static final String filePath = System.getProperty("user.dir");
	public static final String dirPath = filePath + "\\src\\";
	public static Pattern linePattern = Pattern.compile("_(\\w)");
	public static Pattern linePattern2 = Pattern.compile("^[a-z]");
	public static int createNum = 0;

	// 获取数据库下的表字段信息
	public static BeanInfo getColumnInfos(String tableName) throws SQLException {

		BeanInfo beanInfo = new BeanInfo();
		// 存入表名
		beanInfo.setTableName(tableName);
		// 存入表对应的类名
		String tableN = formChangeToLower(tableName);
		StringBuilder className = upCaseFirstWord(tableN);
		beanInfo.setClassName(className.toString());

		// 获取列的信息
		List<ColumnInfo> columnInfos = new ArrayList<>();
		// 获取列名
		List<String> columnNames = new ArrayList<>();
		// 获取列的类型种类
		List<String> columnTypes = new ArrayList<>();
		// 连接数据库
		Connection connection = DBConnectUtil.getConnection();
		PreparedStatement pStatement = null;
		String tableSql = SQL + tableName;
		try {
			pStatement = connection.prepareStatement(tableSql);
			// 结果集元数据
			ResultSetMetaData resultSetMetaData = pStatement.getMetaData();
			// 得到表的列数
			int columnCount = resultSetMetaData.getColumnCount();

			for (int i = 0; i < columnCount; i++) {
				ColumnInfo columnInfo = new ColumnInfo();
				// 获取列名
				String columnName = resultSetMetaData.getColumnName(i + 1);
				// 获取列的类型
				String columnType = resultSetMetaData.getColumnTypeName(i + 1);

				columnInfo.setColumnName(columnName);
				columnInfo.setColumnType(columnTy