jdbc结合commons-dbutils-1.6.jar做素材数据处理的例子
前两个 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"); } }
