网站上一些用户自己上传的歌曲侵权了.
编辑给我一个Excel,包含歌曲名称和歌手名称.
比如
|
最接近天堂的地方
|
张杰
|
|
最浪漫的事(改编版)
|
张杰
|
|
最美的太阳
|
张杰
|
括号里的内容需要去除之后再匹配.由于Excel的内容五花八门,我和编辑的工作量都很大,但是还存在漏杀的情况.
突然我想到借助单位已有的solr,可以简化工作量,而且匹配的精确度更高.
Excel的两列直接贴在网页上(其实就是列间用\t分隔,\n换行),然后程序解析,不断访问solr。
匹配到的歌曲,回数据库获取详细信息,以CSV形式提供下载.
编辑确认下线的歌曲,数据库update一下,就完事儿了.
由于匹配度较好,编辑确认筛选的工作轻松了很多,而且漏杀率很小.
maven依赖:
-
<dependency>
-
<artifactId>solr-solrj</artifactId>
-
<groupId>org.apache.solr</groupId>
-
<version>4.9.0</version>
-
<type>jar</type>
-
<scope>compile</scope>
-
</dependency>
-
<dependency>
-
<groupId>org.slf4j</groupId>
-
<artifactId>slf4j-simple</artifactId>
-
<version>1.7.6</version>
-
</dependency>
-
<dependency>
-
<groupId>javax.servlet</groupId>
-
<artifactId>servlet-api</artifactId>
-
<version>2.5</version>
-
<scope>provided</scope>
-
</dependency>
-
<dependency>
-
<groupId>mysql</groupId>
-
<artifactId>mysql-connector-java</artifactId>
-
<version>5.1.38</version>
-
</dependency>
核心Servlet
-
package com.vv.tools;
-
-
import java.io.IOException;
-
import java.sql.Connection;
-
import java.sql.DriverManager;
-
import java.sql.PreparedStatement;
-
import java.sql.ResultSet;
-
import java.sql.SQLException;
-
import java.util.ArrayList;
-
import java.util.Iterator;
-
import java.util.List;
-
-
import javax.servlet.ServletException;
-
import javax.servlet.http.HttpServlet;
-
import javax.servlet.http.HttpServletRequest;
-
import javax.servlet.http.HttpServletResponse;
-
-
import org.apache.solr.client.solrj.SolrServerException;
-
import org.apache.solr.client.solrj.impl.HttpSolrServer;
-
import org.apache.solr.client.solrj.response.QueryResponse;
-
import org.apache.solr.common.SolrDocument;
-
import org.apache.solr.common.SolrDocumentList;
-
import org.apache.solr.common.params.ModifiableSolrParams;
-
-
-
public class SearchServlet extends HttpServlet {
-
private static final long serialVersionUID = 1L;
-
private static String URL = "jdbc:mysql://192.168.1.138:30000/songod";
-
private static String USERNAME = "xx";
-
private static String PWD = "xx";
-
private HttpSolrServer server = null;
-
-
private HttpSolrServer getSolrServer() {
-
if (server == null) {
-
String url = "http://192.168.1.204:8983/solr/Song";
-
server = new HttpSolrServer(url);
-
server.setSoTimeout(30000);
-
server.setConnectionTimeout(10000);
-
server.setDefaultMaxConnectionsPerHost(10000);
-
server.setMaxTotalConnections(10);
-
server.setFollowRedirects(false);
-
server.setAllowCompression(true);
-
server.setMaxRetries(1);
-
}
-
return server;
-
}
-
-
protected void doGet(HttpServletRequest request, HttpServletResponse response)
-
throws ServletException, IOException {
-
response.setCharacterEncoding("GBK");
-
request.setCharacterEncoding("utf8");
-
-
String str = request.getParameter("data");
-
-
if (str != null && str != "") {
-
try {
-
List<Integer> result = searchAll(str);
-
if (result.size() == 0) {
-
response.sendRedirect("index.html");
-
}
-
-
response.setContentType("application/x-xls");
-
response.setHeader("Content-Disposition", "attachment;filename=songs.csv");
-
Class.forName("com.mysql.jdbc.Driver");
-
Connection con = DriverManager.getConnection(URL, USERNAME, PWD);
-
PreparedStatement pt = con.prepareStatement(
-
"select songid,name,singername,vocalid,authflag from sod_song_ksc where songid = ?");
-
-
for (Integer songid : result) {
-
pt.setInt(1, songid);
-
ResultSet rs = pt.executeQuery();
-
while (rs.next()) {
-
response.getWriter().print(rs.getString("songid") + ",");
-
response.getWriter().print(rs.getString("name") + ",");
-
response.getWriter().print(rs.getString("singername") + ",");
-
response.getWriter().print(rs.getString("vocalid") + ",");
-
response.getWriter().print(rs.getString("authflag") + "\n");
-
}
-
rs.close();
-
}
-
pt.close();
-
con.close();
-
response.getWriter().flush();
-
-
} catch (SolrServerException e) {
-
e.printStackTrace();
-
} catch (ClassNotFoundException e) {
-
-
e.printStackTrace();
-
} catch (SQLException e) {
-
-
e.printStackTrace();
-
}
-
}
-
}
-
-
private List<Integer> searchAll(String str) throws SolrServerException {
-
List<Integer> result = new ArrayList<Integer>(100);
-
String[] rows = str.split("\r\n");
-
for (String row : rows) {
-
String[] info = row.split("\t");
-
String name = info[0].replaceAll("[\\[(<《【]", " ").replaceAll("[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~!@#¥%……&*()——+|{}【】‘;:”“’。,、?《》]", "");
-
-
String singername = "*";
-
if (info.length > 1) {
-
if (info[1].contains("/")) {
-
String[] artists = info[1].split("/");
-
for (String artist : artists) {
-
singername = artist;
-
result.addAll(search(name, singername));
-
}
-
} else {
-
singername = info[1];
-
result.addAll(search(name, singername));
-
}
-
} else {
-
result.addAll(search(name, singername));
-
}
-
}
-
return result;
-
}
-
-
private List<Integer> search(String wdSongname, String wdSingername) throws SolrServerException {
-
List<Integer> list = new ArrayList<Integer>();
-
ModifiableSolrParams params = new ModifiableSolrParams();
-
params.set("q", "(+Song_Name:" + wdSongname + " +Song_SingerName:" + wdSingername + ") or (+Song_Name:"
-
+ wdSongname + " and +Song_Name:" + wdSingername + ")");
-
params.set("start", 0);
-
params.set("rows", 300000);
-
params.set("wt", "json");
-
-
QueryResponse qrsp = getSolrServer().query(params);
-
SolrDocumentList docs = qrsp.getResults();
-
-
Iterator it = docs.iterator();
-
while (it.hasNext()) {
-
SolrDocument doc = (SolrDocument) it.next();
-
Integer songid = (Integer) doc.getFieldValue("Song_SongID");
-
String name = (String) doc.getFieldValue("Song_Name");
-
String singername = (String) doc.getFieldValue("Song_SingerName");
-
String producer = (String) doc.getFieldValue("Song_Producer");
-
-
list.add(songid);
-
}
-
-
return list;
-
}
-
-
protected void doPost(HttpServletRequest request, HttpServletResponse response)
-
throws ServletException, IOException {
-
doGet(request, response);
-
}
-
-
}
index.html网页很简单
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>版权查询</title>
</head>
<body>
<form action="SearchServlet" method="POST">
<textarea id="data" name="data" cols=100 rows=30>小花狸版权搜索.请输入歌曲名称和歌手名称.</textarea>
<input type="submit" value="Upload" />
</form>
</body>
</html>
在网页输入excel的内容

CSV的结果:

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/29254281/viewspace-1993297/
,如需转载,请注明出处,否则将追究法律责任。