前两个 insertTitle,insertOpening 是刚开始做的针对单一素材数据解析及写入的方法，后面一个 insertMaterial 是后期封装的一个可处理各种素材导入的方法，main方法中是它的一个测试用例。

package com.binye.data;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Date;

import java.util.List;

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.handlers.ArrayListHandler;

import org.junit.Test;

public class InsertMeterial {

    private static final String driverClassName = "com.mysql.jdbc.Driver";

    private static final String url = "jdbc:mysql://192.168.17.11:3306/test?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8";    

    private static final String username = "root";

    private static final String password = "****";

    private static Connection conn;

    public static Connection getConnection() {

        if (conn == null)

        {

            try {

                Class.forName(driverClassName);

                conn = DriverManager.getConnection(url, username, password);

            } catch (ClassNotFoundException e) {

                e.printStackTrace();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        return conn;

    }    

    public static void closeConnection() {

        if (conn != null) {

            try {

                conn.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    @Test

    public void insertTitle(){

        QueryRunner query = new QueryRunner();

        String getTagValue = "SELECT id,tag_value FROM tags";

        String getTitleInfo = "SELECT `标题` FROM title";

        // 获取所有标签类型

        List<Object[]> tags = null;

        try {

            tags = query.query(getConnection(), getTagValue, new ArrayListHandler());

        } catch (SQLException e) {

            e.printStackTrace();

        }

        // 获取标题

        List<Object[]> titles = null;

        try {

            titles = query.query(getConnection(), getTitleInfo, new ArrayListHandler());

        } catch (SQLException e) {

            e.printStackTrace();

        }        

        // 准备标题插入素材表和关系表的预处理语句

        PreparedStatement pstmtForTitle = null;

        PreparedStatement pstmtForMaterialTag = null;

        PreparedStatement pstmtForInsertTest = null;

        for (Object[] result1:titles){

            String titleId = UUIDUtil.getOrigUUID();

            String title = result1[0].toString();

            // 向素材表中插入新标题

            String titleSQL = "INSERT INTO material(id,type,value,create_time,status) VALUES( ?, 6, ?, NOW(), 0)";

            try {

                pstmtForTitle = (PreparedStatement) getConnection().prepareStatement(titleSQL);

                pstmtForTitle.setString(1, titleId);

                pstmtForTitle.setString(2, title);

                pstmtForTitle.executeUpdate();

                pstmtForTitle.close();

                // 向标签与素材的关系表中插入数据

                String tagSQL = "SELECT column FROM title WHERE `标题` = titleInfo";

                // 遍历标题表中各个标签的取值情况（若等于1则插入相应关系）

                for (Object[] result2:tags){

                    String tag = result2[1].toString();

                    String getStateOfEachTag = tagSQL.replace("column", "`"+ tag +"`").replace("titleInfo", "'" + title + "'");

                    List<Object[]> states = query.query(getConnection(), getStateOfEachTag, new ArrayListHandler());;

                    if (states.get(0)[0].toString().equals("1")){

                        String materialTagSQL = "INSERT INTO material_tags(material_id,tag_id) VALUES(?,?)";

                        pstmtForMaterialTag = (PreparedStatement) getConnection().prepareStatement(materialTagSQL);

                        pstmtForMaterialTag.setString(1, titleId);

                        pstmtForMaterialTag.setString(2, result2[0].toString());

                        pstmtForMaterialTag.executeUpdate();

                        pstmtForMaterialTag.close();

                    }

                }

                // 查看插入情况

                String insertTestSQL = "select m.id,m.status,m.type,m.value,t.tag_value" +

                " from material m,material_tags mt,tags t" + " where m.id = mt.material_id and mt.tag_id=t.id and m.id=?";

                pstmtForInsertTest = (PreparedStatement) getConnection().prepareStatement(insertTestSQL);

                pstmtForInsertTest.setString(1,titleId);

                ResultSet rs = pstmtForInsertTest.executeQuery();

                int col = rs.getMetaData().getColumnCount();

                System.out.println("============================");

                // 逐行打印数据，字段之间以空格格开，所列字段依次是: 素材id,状态,素材类型,素材内容,标签

                while (rs.next()) {

                    for (int i = 1; i <= col; i++) {

                        System.out.print(rs.getString(i) + " ");

                     }

                    System.out.println("");

                }

                System.out.println("============================");

                pstmtForInsertTest.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    @Test

    public void insertOpening(){

        QueryRunner query = new QueryRunner();

        String getTagValue = "SELECT id,tag_value FROM tags";

        String getOpeningInfo = "SELECT `开场白` FROM opening";

        // 获取所有标签类型

        List<Object[]> tags = null;

        try {

            tags = query.query(getConnection(), getTagValue, new ArrayListHandler());

        } catch (SQLException e) {

            e.printStackTrace();

        }

        // 获取开场白

        List<Object[]> openings = null;

        try {

            openings = query.query(getConnection(), getOpeningInfo, new ArrayListHandler());

        } catch (SQLException e) {

            e.printStackTrace();

        }

        PreparedStatement pstmtForOpening = null;

        PreparedStatement pstmtForMaterialTag = null;

        PreparedStatement pstmtForInsertTest = null;

        for (Object[] result1:openings){

            String openingId = UUIDUtil.getOrigUUID();

            String opening = result1[0].toString();

            // 向素材表中插入新开场白

            String openingSQL = "INSERT INTO material(id,type,value,create_time,status) VALUES( ?, 5, ?, NOW(), 0)";

            try {

                pstmtForOpening = (PreparedStatement) getConnection().prepareStatement(openingSQL);

                pstmtForOpening.setString(1, openingId);

                pstmtForOpening.setString(2, opening);

                pstmtForOpening.executeUpdate();

                pstmtForOpening.close();

                // 找到相应的标签

                String tagSQL = "SELECT column FROM opening WHERE `开场白` = openingInfo";

                for (Object[] result2:tags){

                    String tag = result2[1].toString();

                    String getStateOfEachTag = tagSQL.replace("column", "`"+ tag +"`").replace("openingInfo", "'" + opening + "'");

                    List<Object[]> states = query.query(getConnection(), getStateOfEachTag, new ArrayListHandler());;

                    if (states.get(0)[0].toString().equals("1")){

                        String materialTagSQL = "INSERT INTO material_tags(material_id,tag_id) VALUES(?,?)";

                        pstmtForMaterialTag = (PreparedStatement) getConnection().prepareStatement(materialTagSQL);

                        pstmtForMaterialTag.setString(1, openingId);

                        pstmtForMaterialTag.setString(2, result2[0].toString());

                        pstmtForMaterialTag.executeUpdate();

                        pstmtForMaterialTag.close();

                    }

                }

                // 查看插入情况

                String insertTestSQL = "select m.id,m.status,m.type,m.value,t.tag_value" +

                " from material m,material_tags mt,tags t" + " where m.id = mt.material_id and mt.tag_id=t.id and m.id=?";

                pstmtForInsertTest = (PreparedStatement) getConnection().prepareStatement(insertTestSQL);

                pstmtForInsertTest.setString(1,openingId);

                ResultSet rs = pstmtForInsertTest.executeQuery();

                int col = rs.getMetaData().getColumnCount();

                System.out.println("============================");

                // 逐行打印数据，字段之间以空格格开，所列字段依次是: 素材id,状态,素材类型,素材内容,标签

                while (rs.next()) {

                    for (int i = 1; i <= col; i++) {

                        System.out.print(rs.getString(i) + " ");

                     }

                    System.out.println("");

                }

                System.out.println("============================");

                pstmtForInsertTest.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    public static enum MaterialType {

        TITLE,OPENING

    }

    public static void insertMaterial(MaterialType materialType){

        QueryRunner query = new QueryRunner();

        String getTagValue = "SELECT id,tag_value FROM tags";

        String getMaterialInfo = null;

        String materialSQL = null;

        String tagInMaterialSQL = null;

        switch (materialType) {

        case TITLE:

            getMaterialInfo = "SELECT `标题` FROM title";

            materialSQL = "INSERT INTO material(id,type,value,create_time,status) VALUES( ?, 6, ?, NOW(), 0)";

            tagInMaterialSQL = "SELECT column FROM title WHERE `标题` = materialInfo";

            break;

        case OPENING:

            getMaterialInfo = "SELECT `开场白` FROM opening";

            materialSQL = "INSERT INTO material(id,type,value,create_time,status) VALUES( ?, 5, ?, NOW(), 0)";

            tagInMaterialSQL = "SELECT column FROM opening WHERE `开场白` = materialInfo";

            break;

        default:

            break;

        }

        // 获取所有标签类型

        List<Object[]> tags = null;

        try {

            tags = query.query(getConnection(), getTagValue, new ArrayListHandler());

        } catch (SQLException e) {

            e.printStackTrace();

        }

        // 获取标题

        List<Object[]> materials = null;

        try {

            materials = query.query(getConnection(), getMaterialInfo, new ArrayListHandler());

        } catch (SQLException e) {

            e.printStackTrace();

        }        

        // 准备标题插入素材表和关系表的预处理语句

        PreparedStatement pstmtForMaterial = null;

        PreparedStatement pstmtForMaterialTag = null;

        PreparedStatement pstmtForInsertTest = null;

        for (Object[] result1:materials){

            String materialId = UUIDUtil.getOrigUUID();

            String material = result1[0].toString();

            try {

                // 向素材表中插入新标题

                pstmtForMaterial = (PreparedStatement) getConnection().prepareStatement(materialSQL);

                pstmtForMaterial.setString(1, materialId);

                pstmtForMaterial.setString(2, material);

                pstmtForMaterial.executeUpdate();

                pstmtForMaterial.close();

                // 向标签与素材的关系表中插入数据

                // 遍历标题表中各个标签的取值情况（若等于1则插入相应关系）

                for (Object[] result2:tags){

                    String tag = result2[1].toString();

                    String getStateOfEachTag = tagInMaterialSQL.replace("column", "`"+ tag +"`").replace("materialInfo", "'" + material + "'");

                    List<Object[]> states = query.query(getConnection(), getStateOfEachTag, new ArrayListHandler());;

                    if (states.get(0)[0].toString().equals("1")){

                        String materialTagSQL = "INSERT INTO material_tags(material_id,tag_id) VALUES(?,?)";

                        pstmtForMaterialTag = (PreparedStatement) getConnection().prepareStatement(materialTagSQL);

                        pstmtForMaterialTag.setString(1, materialId);

                        pstmtForMaterialTag.setString(2, result2[0].toString());

                        pstmtForMaterialTag.executeUpdate();

                        pstmtForMaterialTag.close();

                    }

                }

                // 查看插入情况

                String insertTestSQL = "select m.id,m.status,m.type,m.value,t.tag_value" +

                " from material m,material_tags mt,tags t" + " where m.id = mt.material_id and mt.tag_id=t.id and m.id=?";

                pstmtForInsertTest = (PreparedStatement) getConnection().prepareStatement(insertTestSQL);

                pstmtForInsertTest.setString(1,materialId);

                ResultSet rs = pstmtForInsertTest.executeQuery();

                int col = rs.getMetaData().getColumnCount();

                System.out.println("============================");

                // 逐行打印数据，字段之间以空格格开，所列字段依次是: 素材id,状态,素材类型,素材内容,标签

                while (rs.next()) {

                    for (int i = 1; i <= col; i++) {

                        System.out.print(rs.getString(i) + " ");

                     }

                    System.out.println("");

                }

                System.out.println("============================");

                pstmtForInsertTest.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    public static void main(String[] args) {

        System.out.println("数据插入开始");

        Long start = new Date().getTime();

        InsertMeterial.insertMaterial(MaterialType.TITLE);

        InsertMeterial.insertMaterial(MaterialType.OPENING);

        //执行结束，关闭连接

        closeConnection();

        Long end = new Date().getTime();

        System.out.println("执行时间 "+ (end - start) +" ms;"+ (end - start)/1000 +"s;"+ (end - start)/1000/60 +"m");

    }

}

最新文章

  1. 关于EF6的记录Sql语句 与 EntityFramework.Extend 的诟病
  2. 微服务实战（二）：使用API Gateway--转
  3. mysql event_scheduler运行一段时间后 自动关闭
  4. nginx下开启pathinfo模式
  5. IN()
  6. ajax分页2：jquery.pagination +JSON 动态无刷新分页
  7. HDU 3791
  8. windows平台快速安装 matplotlib
  9. Java中常用的加密方法(JDK)
  10. HW4.42
  11. 信用卡/借记卡充值p2p平台
  12. Hibernate入门之关系篇:多对一和一对多映射
  13. Get请求出现乱码的解决方案
  14. android教学大纲
  15. Linux Academy Learn Notes
  16. springMVC源码分析--AbstractControllerUrlHandlerMapping（六）
  17. linux和windows下，C/C++开发的延时函数，sleep函数
  18. [No000016B]清华maven库配置settings.xml
  19. easyui datagrid 单元格编辑（cell editing）
  20. netty 并发访问测试配置

热门文章

  1. 【SpringBoot】Web开发
  2. docker container 导入和导出
  3. 目标检测后处理之NMS（非极大值抑制算法）
  4. redis—django-redis
  5. Maven 依赖的作用域
  6. PAT乙级1042
  7. Go语言值，指针，引用类型
  8. Python MySQL 数据库连接不同方式
  9. Codevs 1574 广义斐波那契数列(矩阵乘法)
  10. ubuntu配置pip3以及scrapy