package test;

import java.sql.*;

import java.text.SimpleDateFormat;

import java.util.*;

import java.util.Date;

public class JDBCTest {

	public static void main(String[] args) {

		// 驱动程序名

		String driver = "com.mysql.jdbc.Driver";

		// URL指向要访问的数据库名

		String url = "jdbc:mysql://172.33.0.190:3306/report";

		// MySQL配置时的用户名

		String user = "root";

		// MySQL配置时的密码

		String password = "123456";

		try {

			Class.forName(driver);

			Connection conn = DriverManager.getConnection(url, user, password);

			if (!conn.isClosed())

				System.out.println("Succeeded connecting to the Database!");

			Statement statement = conn.createStatement();

//			String showTables = "show tables";

//

//			ResultSet rs = statement.executeQuery(showTables);

//

//			System.out.println("-----------------");

			ResultSet rs=null;

			List tableLists = new ArrayList();

			//tableLists.add("rpt_ci_cinema_summary_dt");

			//tableLists.add("bm_ykfp_cinema_film_hall_ticket_dt");

			//tableLists.add("rpt_fm_cinema_film_ticket_dt");

			//tableLists.add("rpt_ci_cinema_hall_ticket_dt");

			//tableLists.add("rpt_ci_cinema_payment_ticket_dt");

			//tableLists.add("rpt_shop_cinema_goods_trade_dt");

			//tableLists.add("rpt_shop_cinema_goods_chanl_trade_dt");

			//tableLists.add("rpt_shop_cinema_goods_item_trade_dt");

			//tableLists.add("rpt_shop_cinema_goods_combo_item_trade_dt");

			//tableLists.add("rpt_fm_cinema_film_summary_dt");

			tableLists.add("rpt_memb_cinema_membcard_addmoney_detail");

			System.out.println(tableLists);

			/*while (rs.next()) {

				String name = rs.getString(1);

				if (name.startsWith("rpt") && name.endsWith("dt") && name.indexOf("_tr_") == -1)

					tableLists.add(name);

			}*/

			// list2.stream().forEach(key -> System.out.println(key));

			tableLists.stream().forEach(System.out::println);

			System.out.println("-----------------");

			List dateLists = new ArrayList();

			Date d1 = new SimpleDateFormat("yyyyMMdd").parse("20150101");//定义起始日期

			  Date d2 = new SimpleDateFormat("yyyyMMdd").parse("20160801");//定义结束日期

			  Calendar dd = Calendar.getInstance();//定义日期实例

			  dd.setTime(d1);//设置日期起始时间

			  while(dd.getTime().before(d2)){//判断是否到结束日期

			  SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");

			  java.lang.String str = sdf.format(dd.getTime());

			  System.out.println(str);//输出日期结果

			  dd.add(Calendar.DATE, 1);//进行当前日期加1

			  dateLists.add(str);

			  }

			/*for (int i = 20160101; i < 20160132; i++) {

				dateLists.add(String.valueOf(i));

			}*/

			System.out.println("-----------------" + dateLists.size());

			dateLists.stream().forEach(System.out::println);

			for (int k = 0; k <dateLists.size(); k++) {

			  String dateStr=(String)dateLists.get(k);

			  System.out.println("----------" + dateStr);

				for (int j = 0; j < tableLists.size(); j++) {

					String tableName = (String) tableLists.get(j);

					String descTable = "desc " + tableName;

					rs = statement.executeQuery(descTable);

					List columnNameList = new ArrayList();

					while (rs.next()) {

						String name = rs.getString(1);

						if (name != null)

							columnNameList.add(name);

					}

					System.out.println("-----------------");

					// String sqlSelect = "select * from

					// "+"rpt_ci_cinema_chanl_new_trade_dt";

					// String sqlInsert = "insert into student (Name,Sex,Age)

					// values(?,?,?)";

					String insertTable = "replace into" + " " + tableName + " " + "( ";

					int columnNameSize = columnNameList.size();

					for (int i = 0; i < columnNameSize; i++) {

						insertTable += columnNameList.get(i);

						if (i != columnNameSize - 1)

							insertTable += ",";

					}

					insertTable += " )";

					// ******Need change the date "20160502" to dateStr

					insertTable += " select '"+ dateStr +"' AS BIZDATE,";

					for (int i = 1; i < columnNameSize; i++) {

						insertTable += columnNameList.get(i);

						if (i != columnNameSize - 1)

							insertTable += ",";

					}

					insertTable += " from " + tableName + " where biz_date='20160601' "+";";

					System.out.println("" + insertTable);

					statement.execute(insertTable);

					System.out.println("--####Finished execute insert into table : " + tableName);

				}

			}

			rs.close();

			conn.close();

		} catch (ClassNotFoundException e) {

			System.out.println("Sorry,can`t find the Driver!");

			e.printStackTrace();

		} catch (SQLException e) {

			e.printStackTrace();

		} catch (Exception e) {

			e.printStackTrace();

		} finally{

         System.out.println("********************All Completed*****************");

		}

	}

}