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

￡神魔★判官ぃ 2022-11-20 09:50 160阅读 0赞

**目录**

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();
      }
    }