通过java连接并处理数据库的脚本代码样例

目录

 

1.测试主类

2.涉及的ExcelmportUtils 工具类

---

1.测试主类

package com.example.demo.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.example.demo.entity.CmsLife;

/**
 * @author hanzl
 * @date 2020/6/17 5:01 下午
 */
public class MySQLCmsLifeConnection {
  public static void main(String [] args) throws Exception{
    String url="jdbc:mysql://****";
    String user="user";
    String password="aaa";
    PreparedStatement stmt = null;
    PreparedStatement stmtQuery = null;

    try {
      //1. 加载驱动
      Class.forName("com.mysql.cj.jdbc.Driver");
      //2. 获取连接
      Connection conn= DriverManager.getConnection(url,user,password);
      // 执行查询
      System.out.println(" 实例化Statement对...");
      String sql = "insert into t_car_life (f_id, f_type,f_resouce_id,f_sort,f_status,f_publish_time,f_create_by,f_create_time,f_yn) "
          + "values(?,?,?,?,?,?,?,?,1)";

      //增加insert
      String filePath = "/Users/mac/Desktop/cmsLife.xlsx";
      File file=new File(filePath);
      InputStream inputStream=new FileInputStream(file);
      List<CmsLife> list= ExcelmportUtils.encapsulationJaveBean(inputStream,new CmsLife(),"YYYY-MM-dd HH:mm:ss");

      int index=0;
      for(CmsLife cs:list){
        index++;
        System.out.println(cs.getId());
        System.out.println(sql);

        String sqlQury = "select f_id  as id from t_car_life_resource where f_url = ?";

        // 2. 获取PreparedStatement
        stmtQuery = conn.prepareStatement(sqlQury);
        // 3. 设置查询参数
        stmtQuery.setString (1, cs.getJumpUrl());
        // 4. 执行查询sql
        ResultSet resultSet = stmtQuery.executeQuery();
        // 5.结果集解析
        Integer f_resouce_id=0;
        if (resultSet.next()) {
          Integer id = resultSet.getInt("id");
          f_resouce_id=id;
          System.out.println(id);
        }

        stmt = conn.prepareStatement(sql);
        stmt.setInt(1,cs.getId());
        stmt.setInt(2,cs.getType());
        stmt.setInt(3,f_resouce_id);
        stmt.setInt(4,cs.getSort());
        stmt.setInt(5,cs.getStatus());
        stmt.setDate(6,new Date(cs.getPublishTime().getTime()));
        stmt.setString(7,cs.getCreateUser());
        stmt.setDate(8,new Date(cs.getCreateTime().getTime()));
        System.out.println(cs.getPublishTime());
        //stmt.setDate(7,new Date(cs.getPublishTime().getTime()));
        stmt.executeUpdate();
        System.out.println("当前执行===i="+index+"title="+cs.getJumpUrl());
      }

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

    System.out.println("连接成功");


}
}

2.涉及的ExcelmportUtils 工具类

package com.example.demo.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.example.demo.entity.CmsRelease;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author hanzl
 * @date 2020/4/8 3:53 下午 excel 导入
 */
public class ExcelmportUtils {

  public static void main (String args[]) throws  Exception {
    String filePath = "/Users/mac/Desktop/export_result.xlsx";
    String filePath2 = "/Users/mac/Desktop/模板.xls";
    File file=new File(filePath);
    InputStream inputStream=new FileInputStream(file);
   List<CmsRelease> list=encapsulationJaveBean(inputStream,new CmsRelease(),"YYYY-MM-dd");
   System.out.println(list.size());
  }

  public static  <T>List<T> encapsulationJaveBean(InputStream inputStream,T t,String dateString)throws IOException{
    List<T> tList=new ArrayList<>();
    //根据指定的文件输入流导入Excel从而产生Workbook对象
    XSSFWorkbook wb0 = new XSSFWorkbook(inputStream);
    //    Workbook wb0 = new HSSFWorkbook(fileIn);
    //获取Excel文档中的第一个表单
    Sheet sht0 = wb0.getSheetAt(0);
    //对Sheet中的每一行进行迭代
    for (Row r : sht0) {
      //如果当前行的行号（从0开始）未达到2（第三行）则从新循环
      if(r.getRowNum()<1){
        continue;
      }
      Class clazz = t.getClass();
      Field[] fieldArray = clazz.getDeclaredFields();
      try {
        Object obj = clazz.getConstructor().newInstance();//产生新的对象--》Student stu = new Student();
        for (int i =0 ;i<fieldArray.length;i++) {
          Field f=fieldArray[i];
          f = clazz.getDeclaredField(f.getName().toString());
          f.setAccessible(true);//暴力反射，解除私有限定
          if (f.getType().equals(String.class)){
            f.set(obj, getCellValue(r.getCell(i)));
          }else if (f.getType().equals(Integer.class)){
            f.set(obj, Integer.valueOf(getCellValue(r.getCell(i))));
          }else if (f.getType().equals(Long.class.getName())){
            f.set(obj,Long.valueOf(getCellValue(r.getCell(i))));
          }else if (f.getType().equals(Float.class)){
            f.set(obj, Float.valueOf(getCellValue(r.getCell(i))));
          }else if (f.getType().equals(Double.class)){
            f.set(obj, Double.valueOf(getCellValue(r.getCell(i))));
          }else if (f.getType().equals(Byte.class)){
            f.set(obj, Byte.valueOf(getCellValue(r.getCell(i))));
          }else if (f.getType().equals(Boolean.class)){
            f.set(obj, Boolean.valueOf(getCellValue(r.getCell(i))));
          }else if (f.getType().equals(Date.class)){
            if(!getCellValue(r.getCell(i)).equals("(null)")){
              System.out.println(getCellValue(r.getCell(i)));
              SimpleDateFormat sdf = new SimpleDateFormat(dateString);
            Date date=null;
            try {
              date=sdf.parse(getCellValue(r.getCell(i)));
            } catch (Exception e) {
              e.printStackTrace();
            }
            f.set(obj, date);
            }
          }else {
            f.set(obj, getCellValue(r.getCell(i)));
          }
        }
        tList.add((T)obj);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return  tList;
  }
  /**
   * 功能:获取单元格的值
   */
  private static String getCellValue(Cell cell) {
    Object result = "";
    if (cell != null) {
      switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
          result = cell.getStringCellValue();
          break;
        case Cell.CELL_TYPE_NUMERIC:
          if (DateUtil.isCellDateFormatted(cell)) {
            Date theDate = cell.getDateCellValue();
            SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            result = dff.format(theDate);
          } else {
            DecimalFormat df = new DecimalFormat("0");
            result = df.format(cell.getNumericCellValue());
          }
          break;
        case Cell.CELL_TYPE_BOOLEAN:
          result = cell.getBooleanCellValue();
          break;
        case Cell.CELL_TYPE_FORMULA:
          result = cell.getCellFormula();
          break;
        case Cell.CELL_TYPE_ERROR:
          result = cell.getErrorCellValue();
          break;
        case Cell.CELL_TYPE_BLANK:
          break;
        default:
          break;
      }
    }
    return result.toString();
  }
}