练习题——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}@Overridepublic 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;}@Overridepublic String toString() {return "Data [recommend=" + recommend + "]";}}