解析JSON将数据存入sqlserver数据库中

最新推荐文章于 2024-02-21 22:28:03 发布

原创最新推荐文章于 2024-02-21 22:28:03 发布 · 1.6w 阅读

5 ·

CC 4.0 BY-SA版权

Sql Server 数据库同时被 2 个专栏收录

6 篇文章

订阅专栏

java

5 篇文章

订阅专栏

本文介绍了一种方法，通过Java处理JSON数据并将其存储到SQL Server数据库中。在处理过程中，可能会遇到如字段长度溢出、特殊字符问题等挑战，作者提供了一个SQLManager工具类用于数据库操作，并编写了findBug函数来定位存储时可能出现的问题。

<span style="font-family: Arial, Helvetica, sans-serif;"></span><pre code_snippet_id="588026" snippet_file_name="blog_20150124_1_2028981" name="code" class="java">JSON格式还是这篇博客中的数据： http://blog.youkuaiyun.com/lushuner/article/details/43085463

存入数据库的时候总是有各种各样的意外数据，要么字段长度溢出，要么字符串含有单引号，要么JSON格式不对，比较头疼的事情是用解析类解析出的结果要比用存入数据库的类真实存入到数据库的中结果数要多，意思就是有的JSON格式中的properties不符合要求存不进去，要从1000条的properties中人工去找哪条没存进去是不太现实的，就写了个findBug函数去找。这里对数据库的操作都用了一个SQLManager的工具类。

<pre code_snippet_id="588026" snippet_file_name="blog_20150124_4_680100" name="code" class="java">SqlManager代码：

public class SqlManager {

 private static Connection conn=null;
private static PreparedStatement ps=null;
private static CallableStatement cs=null;
private static ResultSet rs=null;
public static void openConnection(){
String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String dbURL = "jdbc:sqlserver://localhost:1433;DatabaseName=json";
String userName = "sa";
String userPwd = "123";
try{
Class.forName(driverName);
conn = DriverManager.getConnection(dbURL, userName,userPwd);
System.out.println("连接数据库成功");
}
catch (Exception e){
e.printStackTrace();
System.out.print("连接失败");
}
}
public static void closeConnection(){
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
 public static boolean executeQuery(String sql,String []parameters){
  try{
  ps=conn.prepareStatement(sql);
  if(parameters!=null && !parameters.equals("")){
  for(int i=0; i<parameters.length; i++){
  ps.setString(i+1, parameters[i]);
  }
  }
 rs=ps.executeQuery(); 
 if(rs.next())
 return true;
 else
 return false;
  }catch(Exception e){
 e.printStackTrace();
 throw new RuntimeException(e.getMessage());
  }finally{
  }
//  System.out.println(url+username+password);
  }
public static void update(String sql, String[] parmeters) {


try {
ps = conn.prepareStatement(sql);
if (parmeters != null) {
for (int i = 0; i < parmeters.length; i++) {
ps.setString(i + 1, parmeters[i]);
}
}
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} 
}

public static void main(String[] args) {
long date = Date.parse("November 11, 2014");
System.out.println(new SimpleDateFormat().format("November 11, 2014"));


String sql="insert into android (ID,UpdatedTime,ContentRating,Description,RatingCount,AppSize,CategoryUrl,AppVersion) values ('com.hyperbeard.muertitos5','"+Date.parse("November 11, 2014")+"','Everyone','Muertitos (The Little Dead): A Matching Puz',106,41,'\"http\"s://play.google.com/store/apps/category/GAME_PUZZLE','1.1')";
String [] params={};
SqlManager sm = new SqlManager();
sm.openConnection();
sm.update(sql, params);
sm.closeConnection();
System.out.println("插入结束！");

}
}

<span style="white-space:pre"></span><pre code_snippet_id="588026" snippet_file_name="blog_20150124_6_5757721" name="code" class="java">解析JSON并存入数据库的StoreAndroid类代码：

public class StoreAndroid {public String readFile(String fileName) throws Exception {BufferedReader bf = new BufferedReader(new FileReader(fileName));String content="";String s="";while(content != null){content = bf.readLine();if(content == null)break;s=s+content.trim();}bf.close();return s;}

<span style="white-space:pre">	</span>public static void store(String fileIn) throws Exception{
		//String string = ;
		//System.out.println(string.substring(89000, 89900));
		JSONArray apps = new JSONArray(new AndroidResolve().readFile(fileIn));
		System.out.println(apps.length());

		SqlManager sm = new SqlManager();
		sm.openConnection();

		for(int i=0;i<apps.length();i++){
			System.out.println("APP\t "+i +"---------------------------------------------------------------------------------------");
			String sql = "insert into android (";
			String sql2 = ") values ('";
			String [] params={};
			List<String> screenshots = new ArrayList<String>();
			
			JSONObject app = (JSONObject) apps.get(i);
			JSONObject properties = (JSONObject) app.get("Properties");
				
				JSONArray ID = properties.getJSONArray("ID");
				String id = ID.getString(0);
				System.out.println("ID   :   "+id);
				sql = sql + "ID";
				sql2 = sql2 + id+"','";
				
/*				System.out.println("APP "+String.valueOf(i+1)+":");
				System.out.println("\tID : "+ID.get(0));
*/	
				try {
					JSONArray UpdatedTime = properties.getJSONArray("UpdatedTime");
		        	String updatedDate = new SimpleDateFormat("yyyyMMdd").format(new  SimpleDateFormat("MMMM d,yyyy",Locale.ENGLISH).parse((String) UpdatedTime.get(0)));
					sql = sql + ",UpdatedTime";
					sql2 = sql2 +updatedDate +"','";
				} catch (Exception e1) {
					e1.printStackTrace();
				}
				try {
					JSONArray ContentRating = properties.getJSONArray("ContentRating");
					sql = sql + ",ContentRating";
					sql2 = sql2 + ContentRating.get(0)+"','";
				} catch (Exception e2) {
					// TODO Auto-generated catch block
					e2.printStackTrace();
				}

				try {
					JSONArray UpdatedDateByCrawler = properties.getJSONArray("UpdatedDateByCrawler");
					sql = sql + ",UpdatedDateByCrawler";
					sql2 = sql2 + UpdatedDateByCrawler.get(0)+"','";
				} catch (Exception e2) {
					// TODO Auto-generated catch block
					e2.printStackTrace();
				}

				try {
					JSONArray Description = properties.getJSONArray("Description");
					sql = sql + ",Description";
					sql2 = sql2 + Description.get(0).toString().replaceAll("'", "''")+"','";
				} catch (Exception e1) {
					e1.printStackTrace();
				}

				try {
					JSONArray ScreenshotUrls = properties.getJSONArray("ScreenshotUrls");
					for(int j = 0; j < ScreenshotUrls.length(); j++){
						String sqlscr = "insert into android_screen values('"+id+"','"+ScreenshotUrls.get(j).toString().replaceAll("'","''")+"')";
						screenshots.add(sqlscr);
						//sm.update(sqlscr, params);
						//System.out.println(sqlscr);
					}
				} catch (Exception e) {
					//e.printStackTrace();
				}
				
				try {
					JSONArray RatingCount = properties.getJSONArray("RatingCount");
					sql = sql + ",RatingCount";
					sql2 = sql2 + RatingCount.get(0).toString().replaceAll(",", "")+"','";
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					JSONArray OsVersion = properties.getJSONArray("OsVersion");
					sql = sql + ",OsVersion";
					sql2 = sql2 + OsVersion.get(0)+"','";
				} catch (Exception e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}

				try {
					JSONArray AppSize = properties.getJSONArray("AppSize");
					sql = sql + ",AppSize";
					String appsize = AppSize.get(0).toString();
					if(appsize.charAt(appsize.length()-1)=='M'){
						appsize = appsize.substring(0, appsize.length()-1);
					}else if(appsize.charAt(appsize.length()-1)=='k'){
						float size = (float) (Float.parseFloat( appsize.substring(0, appsize.length()-1).replaceAll(",", ""))/1000.0);
						appsize = String.valueOf(size);						
					}else{
						appsize = "0.0";
					}
					sql2 = sql2 + appsize+"','";
				} catch (Exception e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}

				try {
					JSONArray NumDownloads = properties.getJSONArray("NumDownloads");
					String []ss = NumDownloads.get(0).toString().replaceAll(",", "").split("-");
				
					sql = sql + ",NumDownloadsBegin";
					sql2 = sql2 + ss[0].trim()+"','";					
					sql = sql + ",NumDownloadsEnd";
					sql2 = sql2 + ss[1].trim()+"','";					
					
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					JSONArray Price = properties.getJSONArray("Price");
					String price =null;
					if( "0".equals(Price.get(0)) ){
						price="0";
					}else{
						price = Price.get(0).toString().substring(1);
					}
					sql = sql + ",Price";
					sql2 = sql2 + price +"','";
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					JSONArray RatingScore = properties.getJSONArray("RatingScore");
					sql = sql + ",RatingScore";
					sql2 = sql2 + RatingScore.get(0)+"','";
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					JSONArray Category = properties.getJSONArray("Category");
					sql = sql + ",Category";
					sql2 = sql2 + Category.get(0)+"','";
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					JSONArray AcquiredDateByCrawler = properties.getJSONArray("AcquiredDateByCrawler");
					sql = sql + ",AcquiredDateByCrawler";
					sql2 = sql2 + AcquiredDateByCrawler.get(0)+"','";
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					JSONArray PublisherUrl = properties.getJSONArray("PublisherUrl");
					sql = sql + ",PublisherUrl";
					sql2 = sql2 + PublisherUrl.get(0).toString().replaceAll("'", "''")+"','";
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					JSONArray Title = properties.getJSONArray("Title");
					sql = sql + ",Title";
					sql2 = sql2 + Title.get(0).toString().replaceAll("'", "''")+"','";
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					JSONArray Url = properties.getJSONArray("Url");
					sql = sql + ",Url";
					sql2 = sql2 + Url.get(0).toString().replaceAll("'", "''")+"','";
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					JSONArray Publisher = properties.getJSONArray("Publisher");
					sql = sql + ",Publisher";
					sql2 = sql2 + Publisher.get(0).toString().replaceAll("'", "''")+"','";
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					JSONArray CategoryUrl = properties.getJSONArray("CategoryUrl");
					sql = sql + ",CategoryUrl";
					sql2 = sql2 + CategoryUrl.get(0).toString().replaceAll("'", "''")+"','";
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					JSONArray AppVersion = properties.getJSONArray("AppVersion");
					sql = sql + ",AppVersion";
					sql2 = sql2 + AppVersion.get(0)+"','";
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				try {
					JSONArray IconUrl = properties.getJSONArray("IconUrl");
					sql = sql + ",IconUrl";
					sql2 = sql2 + IconUrl.get(0).toString().replaceAll("'", "''")+"')";
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				sm.update(sql+sql2, params);
				System.out.println(sql+sql2);
		
				if(screenshots.isEmpty())
					continue;
				else{
					for(String sqlscr : screenshots){
						sm.update(sqlscr, params);
					}
				}
		}
		sm.closeConnection();
	
	}
	public static void main(String[] args){
		try {
			store("E:\\JsonResolve\\temp2.txt");
			System.out.println("Done");			
		} catch (Exception e) {
			e.printStackTrace();
		}	
	}
}

<pre code_snippet_id="588026" snippet_file_name="blog_20150124_9_1443237" name="code" class="java">其中找bug用到的findBug函数：

public static void findBug(String fileIn) throws Exception{
//String string = ;
//System.out.println(string.substring(89000, 89900));
JSONArray apps = new JSONArray(new IosResolve().readFile(fileIn));
System.out.println(apps.length());
SqlManager sm = new SqlManager();
sm.openConnection();
for(int i=0;i<apps.length();i++){
// System.out.println("APP\t "+i +"---------------------------------------------------------------------------------------");
String sql = "select * from ios where ID=?";
JSONObject app = (JSONObject) apps.get(i);
JSONObject properties = (JSONObject) app.get("Properties");
JSONArray ID = properties.getJSONArray("ID");
String id = ID.getString(0);
//System.out.println("\t\t\t\t\t\t\t\tID   :   "+id);
String[] params = {id};
if(!sm.executeQuery(sql, params)){
System.out.println("APP\t "+i +"---------------------------------------------------------------------------------------");
System.out.println("ID  :  "+id);
System.out.println("BUG is here!!");
}
}
sm.closeConnection();
}