solr+jdbc练习

1 创建数据库表

create  table  item
(
fid varchar(50) PRIMARY KEY,
fname varchar(50),
ftitle varchar(50),
fprice varchar(50),
fcreatetime datetime

)

2 创建java项目 

引入项目所需要的jar

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.38</version>
</dependency>
  <!-- https://mvnrepository.com/artifact/org.apache.solr/solr-solrj -->
<!-- https://mvnrepository.com/artifact/org.apache.solr/solr-solrj -->
<dependency>
    <groupId>org.apache.solr</groupId>
    <artifactId>solr-solrj</artifactId>
    <version>5.5.4</version>
</dependency>

3  创建实体类 

package entity;

import java.util.Date;

public class Item {
private  String id;
private  String name;
private  String title;
private  String price;
private  Date date;
public String getId() {
	return id;
}
public void setId(String id) {
	this.id = id;
}
public String getName() {
	return name;
}
public void setName(String name) {
	this.name = name;
}
public String getTitle() {
	return title;
}
public void setTitle(String title) {
	this.title = title;
}
public String getPrice() {
	return price;
}
public void setPrice(String price) {
	this.price = price;
}
public Date getDate() {
	return date;
}
public void setDate(Date date) {
	this.date = date;
}
@Override
public String toString() {
	return "Item [id=" + id + ", name=" + name + ", title=" + title + ", price=" + price + ", date=" + date + "]";
}
	
	
	
}

4 创建数据库连接工具类

package JDBCUtils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import java.util.Properties;








/**
 * 数据库链接
 * @Description: TODO(用一句话描述该文件做什么)  
* @author guochao 
* @date 2018年10月13日  
*
 */
public class JDBCUtils {
		
		private static  String username="";
		private static  String password="";
		private static  String url="";
		private static  String clssName="";
		private  static PreparedStatement pr=null;
		private  static  Statement  st=null; 
		
		private static  Connection conectoin=null;
	static {
		try {
			Properties pr = new Properties();
			InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("config/mysql.properties");
			pr.load(in);
			username = pr.getProperty("jdbc.username");
			 password =	pr.getProperty("jdbc.password");
			url =pr.getProperty("jdbc.url");
			clssName = pr.getProperty("jdbc.driver");
			Class.forName(clssName);
			in.close();
		
		} catch (Exception e) {
			
			e.printStackTrace();
		}
	}
	/**
	 * 获取数据库连接 
	* @Description: TODO(用一句话描述该文件做什么)  
	* @author guochao 
	* @date 2018年10月13日  
	*
	 */
	public  static  Connection getConnection() throws Exception{
		
		if(conectoin==null){
			conectoin = DriverManager.getConnection(url, username, password);	
		}
		return conectoin;
		
	}
	
	public static void closePre() throws Exception{
		if(pr!=null){
			pr.close();
		}
	}
	public static void closeSt() throws Exception{
		if(st!=null){
			st.close();
		}
	}
	public static void closeConnect() throws Exception{
		if(conectoin!=null){
			conectoin.close();
		}
	}
	
	public static PreparedStatement getPr(String sql,Object args[]) throws Exception{
		Connection connection = getConnection();
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		
		for (int i = 0; i < args.length; i++) {
			if(args[i] instanceof String){
				prepareStatement.setString(i, args[i].toString());
			}
			if(args[i] instanceof Date){
				Date date = (Date)args[i];
				prepareStatement.setDate(i, (java.sql.Date) date);
			}
		}
		return prepareStatement;
	}

	public static void main(String[] args) throws Exception {
		Connection connection = JDBCUtils.getConnection();
		Statement createStatement = connection.createStatement();
		String sql ="select  *  from  item";
		ResultSet rs = createStatement.executeQuery(sql);
		while(rs.next()){
			System.out.println(rs.getString(1));
		}
		rs.close();
		connection.close();
		createStatement.close();
		
	}
	
}

5 创建solr连接

package SolrUtils;

import java.io.IOException;


import org.apache.solr.client.solrj.impl.HttpSolrClient;
import org.apache.solr.common.SolrInputDocument;

public class SolrUtils {
protected static final String URl ="http://192.168.34.3:8080/solr/core1";
	static	HttpSolrClient solrClient =null;
	 public static HttpSolrClient getSolrClient(){
		 if(solrClient==null){
			 solrClient = new HttpSolrClient(URl);
		 }
		return solrClient;
		 
	 }
	
	 public static void main(String[] args) throws Exception, IOException {
		 HttpSolrClient solrClient2 = SolrUtils.getSolrClient();
		 SolrInputDocument document = new SolrInputDocument();
		 document.addField("id", "123456");
		 document.addField("content", "测试数据");
		 solrClient2.add(document);
		 solrClient2.commit();
	}
}

6 配置solr字段和实体类之间的映射修改/solrhome/core1/conf/managed-schema 添加如下内容重启（tomcat）

<fieldType name="text_ik" class="solr.TextField">
        <analyzer class="org.wltea.analyzer.lucene.IKAnalyzer"></analyzer>
</fieldType>
 <field name="item_title" type="text_ik" indexed="true" stored="true"/>
 <field name="item_name" type="text_ik" indexed="true" stored="true"/>
 <field name="item_price" type="text_ik" indexed="true" stored="true"/>
 <field name="item_date" type="text_ik" indexed="true" stored="false"/>

7  小练习  在数据库中插入1000条数据 装入solr中

package SolrTest;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.solr.client.solrj.SolrQuery;
import org.apache.solr.client.solrj.SolrServerException;
import org.apache.solr.client.solrj.impl.HttpSolrClient;
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.SolrInputDocument;
import org.junit.Test;

import JDBCUtils.JDBCUtils;
import SolrUtils.SolrUtils;
import entity.Item;

public class TestSolr {
	@Test
	public  void addToMysql(){
		try {
			Connection connection = JDBCUtils.getConnection();
			String sql ="insert into item(fid,fname,ftitle,fprice,fcreatetime)values(?,?,?,?,now())";
			PreparedStatement pr = connection.prepareStatement(sql);
			for (int i = 0; i < 1000; i++) {
				pr.setString(1,String.valueOf(System.currentTimeMillis()));
				pr.setString(2,"华为手机"+i);
				pr.setString(3,"拿回来玩"+i*1100);
				pr.setString(4,"1000"+200*i);
				pr.executeUpdate();
			}
			
			pr.close();
		} catch (Exception e) {
			
			e.printStackTrace();
		}
	}
	
//  将查出来的数据存入solr
	
	
	public static  void addToSolr(List<Item> list) throws Exception, IOException{
		HttpSolrClient solrClient = SolrUtils.getSolrClient();
		SolrInputDocument document=null;
		List<SolrInputDocument> solrList =new ArrayList<SolrInputDocument>();
		for (Item item : list) {
			document = new SolrInputDocument();
			document.addField("id", item.getId());
			document.addField("item_name", item.getName());
			document.addField("item_title", item.getTitle());
			document.addField("item_price", item.getPrice());
			document.addField("item_date", item.getDate());
			solrList.add(document);
		}
		solrClient.add(solrList);
		solrClient.commit();
		solrClient.close();
		
	}
	
	
	public static  List<Item> getFromSql(Map<String,String> map) throws Exception{
		Connection connection = JDBCUtils.getConnection();
		StringBuffer buffer = new StringBuffer();
		int count =1;
		buffer.append("select fid,fname,ftitle,fprice,fcreatetime from  item where 1=1");
		if(map.containsKey("id")){
			buffer.append("  and fid =?");
		}
		if(map.containsKey("key")){
			buffer.append("  and ( fname like ? or ftitle like ? or fprice like ?)");
		}
		PreparedStatement pr = connection.prepareStatement(buffer.toString());
		if(map.containsKey("id")){
			pr.setString(count, map.get("id"));
			count++;
		}
		if(map.containsKey("key")){
			buffer.append("  and ( fname like ? or ftitle like ? or fprice like ?)");
			pr.setString(count, "%"+map.get("key")+"%");
			count++;
			pr.setString(count, "%"+map.get("key")+"%");
			count++;
			pr.setString(count, "%"+map.get("key")+"%");
		}
		ResultSet rs = pr.executeQuery();
		List<Item> list = new ArrayList<Item>();
		Item item =null;
		while(rs.next()){
			item = new Item();
			item.setId(rs.getString("fid"));
			item.setName(rs.getString("fname"));
			item.setTitle(rs.getString("ftitle"));
			
			item.setPrice(rs.getString("fprice"));
			item.setDate(rs.getDate("fcreatetime"));
			list.add(item);
		}
		
		return list;
		
		
		
	}
	
	@Test
	public  void queryFromSolr() throws Exception, IOException{
		HttpSolrClient solrClient = SolrUtils.getSolrClient();
		SolrQuery query = new SolrQuery();
		query.setQuery("*:*");
		QueryResponse response = solrClient.query(query);
		SolrDocumentList results = response.getResults();
		for (SolrDocument solrDocument : results) {
			System.out.println(solrDocument.toString());
		}
		
	}
	
	
	
	
	
	
	
	public static void main(String[] args) throws Exception {
		Map<String,String> map = new HashMap<String,String>();
//		map.put("id", "1539432171309");
		map.put("key", "华为手机49");
		List<Item> list = getFromSql(map);
		for (Item item : list) {
			System.out.println(item);
		}
		addToSolr(list);
		
		
	}
	
}