练习题——Gson解析、利用JDBC连接数据库进行数据的存取及查询等的综合

/**
 *对数据的查询类
 */
package com.liuzhonglin.exercise;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//3.在另外一个类当中读取数据库的结果。
//规则：用户输入id，就能得到关于这个id的产品名和产品信息。
//    可以循环输入，如果用户输入exit，就退出查询系统。
public class ExerciseDemo01 {
	public static void main(String[] args) {
		BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
		try {
			Class.forName("org.sqlite.JDBC");
			Connection conn = DriverManager.getConnection("jdbc:sqlite:/"+"e:/test.db");
			Statement stmt = conn.createStatement();
			
			while(true){
				System.out.println("请输入id：");
				String str = br.readLine();
				if(str.equals("exit")){
					System.out.println("退出系统");
					stmt.close();
					conn.close();
					return;
				}
				String sql = "select * from aa where id="+str;
				ResultSet rs = stmt.executeQuery(sql);
				while(rs.next()){
					System.out.println("产品编号:"+str+",产品名称："+rs.getString("company")+"\n产品详情："+rs.getString("summary")+"\n");
				}
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

<pre name="code" class="java">package com.liuzhonglin.exercise;
//http://218.244.149.129:9010/api/recommend.php
//
//1.通过接口获取网络数据，并解析
//2.把解析之后的结果当中的属性：id   company  summary  作为表当中的一条存入到数据库当中。
//3.在另外一个类当中读取数据库的结果。
//    规则：用户输入id，就能得到关于这个id的产品名和产品信息。
//	      可以循环输入，如果用户输入exit，就退出查询系统。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.google.gson.Gson;

public class ExerciseDemo {
	public static void main(String[] args) {
		//解析数据
		String path = "http://218.244.149.129:9010/api/recommend.php";
		String str = HttpUtils.getByStringokHttp(path);
		Data data = new Gson().fromJson(str, Data.class);
		System.out.println(data);
		//利用JDBC访问数据库，将解析出来的数据存入到数据库中
		try {
			Class.forName("org.sqlite.JDBC");
			Connection conn = DriverManager.getConnection("jdbc:sqlite:/" + "e:/test.db");
			Statement stmt = conn.createStatement();
			String sql = "create table if not exists aa(id integer,company String,summary String)";
			stmt.executeUpdate(sql);
			String sql1 = "insert into aa(id,company,summary) values (?,?,?)";
			PreparedStatement ps = conn.prepareStatement(sql1);
			ps.executeUpdate();
			List<Company> list = data.getRecommend();
			for (Company c : list) {
				ps.setInt(1, c.getId());
				ps.setString(2, c.getCompany());
				ps.setString(3, c.getSummary());
				ps.executeUpdate();
			}
			//将数据存入数据库中的第二种方法
			// int count=data.getRecommend().size();
			// for (int i = 0; i < count; i++) {
			// ps.setInt(1,data.getRecommend().get(i).getId());
			// ps.setString(2, data.getRecommend().get(i).getCompany());
			// ps.setString(3, data.getRecommend().get(i).getSummary());
			// ps.executeUpdate();
			// }
			//在网数据库中存数据时候不用ResultSet，ResultSet结果集一般在从数据库中读取数据的时候用
			// ResultSet rs = stmt.executeQuery(sql1);
			// while(rs.next()){
			// }
			stmt.close();
			conn.close();
			ps.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

/*
 * 工具类
 */
package com.liuzhonglin.exercise;

import java.io.IOException;

import okhttp3.OkHttpClient;
import okhttp3.Request;
import okhttp3.Response;

public class HttpUtils {
	public static String getByStringokHttp(String path){
		String result = null;
		OkHttpClient client = new OkHttpClient();
		Request request = new Request.Builder().url(path).get().build();
		try {
			Response response = client.newCall(request).execute();
			if(response.isSuccessful()){
				result = response.body().string();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return result;
	}
}

/*
 * Bean类
 */
package com.liuzhonglin.exercise;

public class Company {
	private int id;
	private String company;
	private String summary;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getCompany() {
		return company;
	}
	public void setCompany(String company) {
		this.company = company;
	}
	public String getSummary() {
		return summary;
	}
	public void setSummary(String summary) {
		this.summary = summary;
	}
	public Company(int id, String company, String summary) {
		super();
		this.id = id;
		this.company = company;
		this.summary = summary;
	}
	public Company() {
		super();
		// TODO Auto-generated constructor stub
	}
	@Override
	public String toString() {
		return "Company [id=" + id + ", company=" + company + ", summary=" + summary + "]";
	}
}

package com.liuzhonglin.exercise;

import java.util.List;

public class Data {
	List<Company>recommend;

	public List<Company> getRecommend() {
		return recommend;
	}

	public void setRecommend(List<Company> recommend) {
		this.recommend = recommend;
	}

	@Override
	public String toString() {
		return "Data [recommend=" + recommend + "]";
	}
	
}