连接mysql操作的util方法

1.

package cn.hls.xjx.util;

import cn.hls.xjx.modules.resources.entity.RecordInfo;
import cn.hls.xjx.modules.resources.entity.Res;
import com.alibaba.dubbo.common.utils.CollectionUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class updateInfoUtil {

    private static String driverName = "com.mysql.cj.jdbc.Driver";
    private static String dbURL = "jdbc:mysql://*********:3306/xjx1?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&autoReconnectForPools=true";
    private static String userName = "test";
    private static String userPwd = "**********";

	public static void main(String[] args) {
        getUpdateInfoRes();

	}

    public static Connection getConnection() {
        try {
            Class.forName(driverName);
            Connection dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
            return dbConn;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
    *description:获取到单机版家教 升级信息和资源关联表
    *@author: fuxb
    *@date: 2021/7/27 20:34
    @param
    *@return: java.util.List<cn.hls.xjx.modules.resources.entity.RecordInfo>
    */
    public static List<Res> getUpdateInfoRes() {
        Connection connection = getConnection();
        PreparedStatement pps = null;
        String sql = "select * from update_info_res r  ";
        List<Res> list = new ArrayList<>();
        List<String> resourceIDList = new ArrayList<>();
        try {
            pps = connection.prepareStatement(sql);
            ResultSet rs = pps.executeQuery();
            while(rs.next()) {
                Res info = new Res();
                info.setResourceID(rs.getString("ResourceID"));
                info.setTitle(rs.getString("Title"));
                list.add(info);
                resourceIDList.add(info.getResourceID());
                System.out.println(info);
            }
            String resourceIDSql = convertListToString(resourceIDList);
            if(list.size() > 0){
                pps = connection.prepareStatement("delete from res_test where ResourceID in ("+resourceIDSql+")");
                pps.executeUpdate();
                for(int i=0;i<list.size();i++){
                    String insertSql = "insert into res_test values ('"+
                            list.get(i).getResourceID()+"','"+
                            list.get(i).getTitle()+"','"+
                            list.get(i).getKPID()+"',"+
                            list.get(i).getKPRevDegree()+",'"+
                            list.get(i).getVCPID()+"','"+
                            list.get(i).getEditionID()+"',"+
                            list.get(i).getModuleID()+",'"+
                            list.get(i).getModuleName()+"','"+
                            list.get(i).getCreator()+"','"+
                            list.get(i).getCreateDate()+"','"+
                            list.get(i).getAdaptor()+"','"+
                            list.get(i).getAdaptDate()+"','"+
                            list.get(i).getDescription()+"','"+
                            list.get(i).getPublisher()+"','"+
                            list.get(i).getContributor()+"','"+
                            list.get(i).getType()+"','"+
                            list.get(i).getMediaMaterial()+"',"+
                            list.get(i).getFormat()+","+
                            list.get(i).getDifficulty()+",'"+
                            list.get(i).getIdentifier()+"','"+
                            list.get(i).getParentResource()+"','"+
                            list.get(i).getRights()+"','"+
                            list.get(i).getVersion()+"','"+
                            list.get(i).getSpecialProperty1()+"','"+
                            list.get(i).getSpecialProperty2()+"','"+
                            list.get(i).getSource()+"','"+
                            list.get(i).getFlag()+"','"+
                            list.get(i).getSpecialproperty3()+"','"+
                            list.get(i).getSpecialproperty4()+"',"+
                            list.get(i).getSubid()+",'"+
                            list.get(i).getCopyright()+"',"+
                            list.get(i).getSize()+",'"+
                            list.get(i).getCoverimage()+"','"+
                            list.get(i).getOrigin()+"','"+
                            list.get(i).getKeywords()+"','"+
                            list.get(i).getPixel()+"','"+
                            list.get(i).getAuthorInTroduction()+"','')";
                    String aflerInsertSql = insertSql.replace("'null'","''");
                    System.out.println(aflerInsertSql);
                    pps = connection.prepareStatement(aflerInsertSql);   //最后一个字段是 SubKPID   没有到暂时置为空
                    pps.executeUpdate();
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            System.out.println("同步资源数据时报错：");
            e.printStackTrace();
        }
        close(pps, connection);
        return list;
    }

    /**
     * 将List<String>集合 转化为String
     * 如{"aaa","bbb"} To 'aaa','bbb'
     */
    public static String convertListToString(List<String> strlist) {
        StringBuffer sb = new StringBuffer();
        if (CollectionUtils.isNotEmpty(strlist)) {
            for (int i = 0; i < strlist.size(); i++) {
                if (i == 0) {
                    sb.append("'").append(strlist.get(i)).append("'");
                } else {
                    sb.append(",").append("'").append(strlist.get(i)).append("'");
                }
            }
        }
        return sb.toString();
    }

        public static List<RecordInfo> getProList() {
        Connection connection = getConnection();
        PreparedStatement pps = null;
        String sql = "select * from recordinfo r,user u where RI_At_szProblemID like '__JX%' and RI_At_szProblemID not in (select RI_At_szProblemID from recordinfo_jx) and r.RI_At_Creator = u.id and u.schoolId = '272440'";
        List<RecordInfo> list = new ArrayList<RecordInfo>();
        try {
            pps = connection.prepareStatement(sql);
            ResultSet rs = pps.executeQuery();
            while(rs.next()) {
                RecordInfo info = new RecordInfo();
                info.setPI_szAnswer(rs.getString("PI_szAnswer"));
                info.setRI_At_AbilityGrade(rs.getInt("RI_At_AbilityGrade"));
                info.setRI_At_AuditDate(rs.getString("RI_At_AuditDate"));
                info.setRI_At_AuditFlag(rs.getInt("RI_At_AuditFlag")); //倒库 添加flag为1
                info.setRI_At_Auditor(rs.getString("RI_At_Auditor"));
                info.setRI_At_bHaveAnalysis(rs.getBoolean("RI_At_bHaveAnalysis"));
                info.setRI_At_bHaveDetail(rs.getBoolean("RI_At_bHaveDetail"));
                info.setRI_At_bHaveHint(rs.getBoolean("RI_At_bHaveHint"));
                info.setRI_At_CreateDate(rs.getString("RI_At_CreateDate"));
                info.setRI_At_Creator(rs.getString("RI_At_Creator"));
                info.setRI_At_haveVideo(rs.getString("RI_At_haveVideo"));
                info.setRI_At_iDifCoef(rs.getInt("RI_At_iDifCoef"));
                info.setRI_At_iEachScore(rs.getInt("RI_At_iEachScore"));
                info.setRI_At_iEditonID(rs.getString("RI_At_iEditonID"));
                info.setRI_At_iRequireTime(rs.getInt("RI_At_iRequireTime"));
                info.setRI_At_iScore(rs.getInt("RI_At_iScore"));
                info.setRI_At_iSubject(rs.getString("RI_At_iSubject"));
                info.setRI_At_LTID(rs.getInt("RI_At_LTID"));
                info.setRI_At_LTNo(rs.getInt("RI_At_LTNo"));
                info.setRI_At_LTTBID(rs.getString("RI_At_LTTBID"));
                info.setRI_At_LTType(rs.getString("RI_At_LTType"));
                info.setRI_At_NoInPaper(rs.getString("RI_At_NoInPaper"));
                info.setRI_At_PaperID(rs.getString("RI_At_PaperID"));
                info.setRI_At_Prop1(rs.getString("RI_At_Prop1"));
                info.setRI_At_Prop2(rs.getString("RI_At_Prop2"));
                info.setRI_At_Prop3(rs.getString("RI_At_Prop3"));
                info.setRI_At_Provenance(rs.getString("RI_At_Provenance"));
                info.setRI_At_ScoreInPaper(rs.getString("RI_At_ScoreInPaper"));
                info.setRI_At_szCity(rs.getString("RI_At_szCity"));
                info.setRI_At_szCounty(rs.getString("RI_At_szCounty"));
                info.setRI_At_szKnowPointID(rs.getString("RI_At_szKnowPointID"));
                info.setRI_At_szKnowPointID(rs.getString("RI_At_szKnowPointID"));
                info.setRI_At_szKPID(rs.getString("RI_At_szKPID"));
                info.setRI_At_szKPIDZ(rs.getString("RI_At_szKPIDZ"));
                info.setRI_At_szKPName(rs.getString("RI_At_szKPName"));
                info.setRI_At_szNoInHomework(rs.getString("RI_At_szNoInHomework"));
                info.setRI_At_szNoInZD(rs.getInt("RI_At_szNoInZD"));
                info.setRI_At_szProblemID(rs.getString("RI_At_szProblemID"));
                info.setRI_At_szProbStyle(rs.getString("RI_At_szProbStyle"));
                info.setRI_At_szProvince(rs.getString("RI_At_szProvince"));
                info.setRI_At_szSchool(rs.getString("RI_At_szSchool"));
                info.setRI_At_szTBID(rs.getString("RI_At_szTBID"));
                info.setRI_At_szVCPID(rs.getString("RI_At_szVCPID"));
                info.setRI_At_szYear(rs.getString("RI_At_szYear"));
                info.setRI_At_szZDID(rs.getString("RI_At_szZDID"));
                info.setRI_At_UpdateDate(rs.getString("RI_At_UpdateDate"));
                info.setRI_Si_bIsDone(rs.getBoolean("RI_Si_bIsDone"));
                info.setRI_Si_bIsRight(rs.getBoolean("RI_Si_bIsRight"));
                info.setRI_Si_iErrorTimes(rs.getInt("RI_Si_iErrorTimes"));
                info.setRI_Si_iErrorTimes(rs.getInt("RI_Si_iErrorTimes"));
                info.setRI_Si_iLastCostTime(rs.getInt("RI_Si_iLastCostTime"));
                info.setRI_Si_iLastDoneTime(rs.getDate("RI_Si_iLastDoneTime"));
                info.setSubid(rs.getInt("subid"));
                list.add(info);
                System.out.println(info);
            }
            pps = connection.prepareStatement("insert into recordinfo_jx(RI_At_szProblemID) select RI_At_szProblemID from recordinfo r,user u where RI_At_szProblemID like '__JX%' and RI_At_szProblemID not in (select RI_At_szProblemID from recordinfo_jx) and r.RI_At_Creator = u.id and u.schoolId = '272440'");
            pps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        close(pps, connection);
        return list;
    }

    public static void close(PreparedStatement pps,Connection con) {
        if(pps != null) {
            try {
                pps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}