From 36f0ced4b46a1823a15d3e33e664891c92618b78 Mon Sep 17 00:00:00 2001 From: Alexander Alexeev Date: Fri, 18 Nov 2016 01:30:39 +0700 Subject: search service for full text search implementation; search bean configuration --- .../juick/configuration/SearchConfiguration.java | 54 ++++++++++++++ .../java/com/juick/service/MessagesService.java | 2 +- .../com/juick/service/MessagesServiceImpl.java | 45 +++--------- .../com/juick/service/search/SearchService.java | 14 ++++ .../service/search/SphinxSearchServiceImpl.java | 85 ++++++++++++++++++++++ juick-server/src/main/resources/juick.conf.example | 22 +++++- 6 files changed, 184 insertions(+), 38 deletions(-) create mode 100644 juick-server/src/main/java/com/juick/configuration/SearchConfiguration.java create mode 100644 juick-server/src/main/java/com/juick/service/search/SearchService.java create mode 100644 juick-server/src/main/java/com/juick/service/search/SphinxSearchServiceImpl.java diff --git a/juick-server/src/main/java/com/juick/configuration/SearchConfiguration.java b/juick-server/src/main/java/com/juick/configuration/SearchConfiguration.java new file mode 100644 index 00000000..40473433 --- /dev/null +++ b/juick-server/src/main/java/com/juick/configuration/SearchConfiguration.java @@ -0,0 +1,54 @@ +package com.juick.configuration; + +import com.juick.service.search.SearchService; +import com.juick.service.search.SphinxSearchServiceImpl; +import org.apache.commons.dbcp2.BasicDataSource; +import org.springframework.context.annotation.Bean; +import org.springframework.context.annotation.Configuration; +import org.springframework.context.annotation.PropertySource; +import org.springframework.core.env.Environment; + +import javax.annotation.Resource; +import java.util.Collections; +import java.util.List; + +/** + * Created by aalexeev on 11/18/16. + */ +@Configuration +@PropertySource(value = {"classpath:juick.conf"}) +public class SearchConfiguration { + @Resource + private Environment env; + + @Bean + public SearchService searchService() { + BasicDataSource dataSource = new BasicDataSource(); + + dataSource.setDriverClassName(env.getProperty("sphinx_driver", "com.mysql.jdbc.Driver")); + dataSource.setUrl(env.getProperty("sphinx_url", "jdbc:mysql://127.0.0.1:9306?autoReconnect=true&useUnicode=yes&characterEncoding=utf8&maxAllowedPacket=512000")); + dataSource.setUsername(env.getProperty("sphinx_user", "")); + dataSource.setPassword(env.getProperty("sphinx_password", "")); + + return new SphinxSearchServiceImpl(dataSource); + } + + @Bean + public SearchService emptySearchService() { + return new SearchService() { + @Override + public void setMaxResult(int maxResult) { + } + + @Override + public List searchInAllMessages(String searchString, int messageIdBefore) { + return Collections.emptyList(); + } + + @Override + public List searchByStringAndUser(String searchString, int userId, int messageIdBefore) { + return Collections.emptyList(); + } + }; + } +} diff --git a/juick-server/src/main/java/com/juick/service/MessagesService.java b/juick-server/src/main/java/com/juick/service/MessagesService.java index 83ab0dce..5990f408 100644 --- a/juick-server/src/main/java/com/juick/service/MessagesService.java +++ b/juick-server/src/main/java/com/juick/service/MessagesService.java @@ -58,7 +58,7 @@ public interface MessagesService { List getPhotos(int visitorUid, int before); - List getSearch(JdbcTemplate sqlSearch, String search, int before); + List getSearch(String search, int before); List getUserBlog(int uid, int privacy, int before); diff --git a/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java b/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java index 6fc71ac3..6f33bbf3 100644 --- a/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -4,6 +4,7 @@ import com.juick.Message; import com.juick.Tag; import com.juick.User; import com.juick.server.helpers.PrivacyOpts; +import com.juick.service.search.SearchService; import com.juick.util.MessageUtils; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.StringEscapeUtils; @@ -30,13 +31,17 @@ import java.util.*; @Repository public class MessagesServiceImpl extends BaseJdbcService implements MessagesService { private final UserService userService; + private final SearchService searchService; @Inject - public MessagesServiceImpl(JdbcTemplate jdbcTemplate, UserService userService) { + public MessagesServiceImpl(JdbcTemplate jdbcTemplate, UserService userService, SearchService searchService) { super(jdbcTemplate, null); Assert.notNull(userService); this.userService = userService; + + Assert.notNull(searchService); + this.searchService = searchService; } private class MessageMapper implements RowMapper { @@ -576,26 +581,14 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional(readOnly = true) @Override - public List getSearch(final JdbcTemplate sqlSearch, final String search, final int before) { - List mids; - - if (before > 0) { - mids = sqlSearch.queryForList( - "SELECT id AS message_id FROM messages WHERE MATCH(?) AND id < ? ORDER BY id DESC LIMIT 25", - Integer.class, - search, before); - } else { - mids = sqlSearch.queryForList( - "SELECT id AS message_id FROM messages WHERE MATCH(?) ORDER BY id DESC LIMIT 25", - Integer.class, - search); - } - if (mids.size() > 0) { + public List getSearch(final String search, final int before) { + List mids = searchService.getMessageIdsInAllMessages(search, before); + + if (!mids.isEmpty()) return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages WHERE message_id IN (:ids) AND privacy > 0 ORDER BY message_id DESC LIMIT 20", new MapSqlParameterSource("ids", mids), Integer.class); - } return mids; } @@ -672,23 +665,9 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional(readOnly = true) @Override public List getUserSearch(final JdbcTemplate sqlSearch, final int UID, final String search, final int privacy, final int before) { - List mids; - - if (before > 0) { - mids = sqlSearch.queryForList( - "SELECT id AS message_id FROM messages WHERE user_id=? AND MATCH(?) AND id mids = searchService.getMessageIdsByUser(search, UID, before); - if (mids.size() > 0) { + if (!mids.isEmpty()) { return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages WHERE message_id IN (:ids) AND privacy >= :privacy ORDER BY message_id DESC", new MapSqlParameterSource("ids", mids) diff --git a/juick-server/src/main/java/com/juick/service/search/SearchService.java b/juick-server/src/main/java/com/juick/service/search/SearchService.java new file mode 100644 index 00000000..21deb0b1 --- /dev/null +++ b/juick-server/src/main/java/com/juick/service/search/SearchService.java @@ -0,0 +1,14 @@ +package com.juick.service.search; + +import java.util.List; + +/** + * Created by aalexeev on 11/18/16. + */ +public interface SearchService { + void setMaxResult(int maxResult); + + List searchInAllMessages(String searchString, int messageIdBefore); + + List searchByStringAndUser(String searchString, final int userId, int messageIdBefore); +} diff --git a/juick-server/src/main/java/com/juick/service/search/SphinxSearchServiceImpl.java b/juick-server/src/main/java/com/juick/service/search/SphinxSearchServiceImpl.java new file mode 100644 index 00000000..de0a101f --- /dev/null +++ b/juick-server/src/main/java/com/juick/service/search/SphinxSearchServiceImpl.java @@ -0,0 +1,85 @@ +package com.juick.service.search; + +import org.apache.commons.lang3.StringUtils; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; +import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; +import org.springframework.util.Assert; + +import javax.sql.DataSource; +import java.util.Collections; +import java.util.List; + +/** + * Created by aalexeev on 11/18/16. + */ + +/* Note + * Don't use any spring's component annotation (such as @Repository, @Service, @Component, etc). + * This class directly used by spring's search configuration class + */ +public class SphinxSearchServiceImpl implements SearchService { + private static final int DEFAULT_MAX_RESULT = 25; + + private final NamedParameterJdbcTemplate namedParameterSearchJdbcTemplate; + + private int maxResult = DEFAULT_MAX_RESULT; + + + public SphinxSearchServiceImpl(JdbcTemplate searchJdbcTemplate) { + Assert.notNull(searchJdbcTemplate); + this.namedParameterSearchJdbcTemplate = new NamedParameterJdbcTemplate(searchJdbcTemplate); + } + + public SphinxSearchServiceImpl(DataSource searchDataSource) { + Assert.notNull(searchDataSource); + this.namedParameterSearchJdbcTemplate = new NamedParameterJdbcTemplate(searchDataSource); + } + + @Override + public List searchInAllMessages(final String searchString, final int messageIdBefore) { + if (StringUtils.isBlank(searchString)) + return Collections.emptyList(); + + MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource() + .addValue("search", searchString) + .addValue("before", messageIdBefore) + .addValue("limit", maxResult); + + return namedParameterSearchJdbcTemplate.queryForList( + "SELECT id AS message_id FROM messages WHERE MATCH(:search) " + + (messageIdBefore > 0 ? + " AND id < :before " : "") + + " ORDER BY id DESC LIMIT :limit", + sqlParameterSource, + Integer.class); + } + + @Override + public List searchByStringAndUser(final String searchString, final int userId, int messageIdBefore) { + if (StringUtils.isBlank(searchString)) + return Collections.emptyList(); + + MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource() + .addValue("search", searchString) + .addValue("userId", userId) + .addValue("before", messageIdBefore) + .addValue("limit", maxResult); + + return namedParameterSearchJdbcTemplate.queryForList( + "SELECT id AS message_id FROM messages WHERE user_id = :userId AND MATCH(:search) " + + (messageIdBefore > 0 ? + " AND id < :before " : "") + + " ORDER BY id DESC LIMIT :limit", + sqlParameterSource, + Integer.class); + } + + @Override + public void setMaxResult(int maxResult) { + if (maxResult <= 0) + throw new IllegalArgumentException("maxResult value (" + maxResult + ") must be greater then 0"); + + this.maxResult = maxResult; + } +} \ No newline at end of file diff --git a/juick-server/src/main/resources/juick.conf.example b/juick-server/src/main/resources/juick.conf.example index c9411456..945bfae4 100644 --- a/juick-server/src/main/resources/juick.conf.example +++ b/juick-server/src/main/resources/juick.conf.example @@ -1,17 +1,31 @@ -#Driver +### Main database JDBC connection settings ### +# Main database JDBC driver datasource_driver=net.sf.log4jdbc.DriverSpy !datasource_driver=com.mysql.jdbc.Driver -#Url +# Main database JDBC Url !datasource_url=jdbc:mysql://localhost:3306/juick?autoReconnect=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF8 datasource_url=jdbc:log4jdbc:mysql://localhost:3306/juick?autoReconnect=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF8 -#Username +# Main database username datasource_user= -#Password +# Main database password datasource_password= +### Sphinx search connection +# Sphinx search JDBC driver +sphinx_driver=com.mysql.jdbc.Driver + +# Sphinx search JDBC url +sphinx_url=jdbc:mysql://127.0.0.1:9306?autoReconnect=true&useUnicode=yes&characterEncoding=utf8&maxAllowedPacket=512000 + +# Sphinx search JDBC username +sphinx_user= + +# Sphinx search JDBC password +sphinx_password= + twitter_consumer_key= twitter_consumer_secret= -- cgit v1.2.3