2-1-2

package task;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

public class DataStorage_Mysql {
    //1、导入数据源
    private static String file="resource/空气质量数据.txt";
    
    //数据库资源
    private static String mysql="com.mysql.jdbc.Driver";
    private static String url="jdbc:mysql://localhost:3306/jdbc";
    private static String username="root";
    private static String password="123456";
    private static Connection conn=null;
    private static PreparedStatement stmt=null;
        
    //2、数据库连接
    public static Connection getConnection()  throws ClassNotFoundException, SQLException{
        Class.forName(mysql);//数据库驱动
        Connection conn=DriverManager.getConnection(url, username,password);
        return conn;
    }
    //释放
    public static void release(PreparedStatement stmt,Connection conn) {
        if(stmt!=null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt=null;
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn=null;
        }
    }
    
        
    //3、建表
    public static void createTable() throws ClassNotFoundException, SQLException{
            conn=getConnection();
            String create_table="create table if not exists air_quality(id int(3) auto_increment primary key,"
                    + "date varchar(15) not null,level varchar(12) not null,AQI varchar(6) not null,"
                    + "AQI_ranking varchar(6) not null,PM2_5 varchar(6) not null,Pm10 varchar(6) not null)";
            stmt=conn.prepareStatement("drop table if exists air_quality");
            stmt.execute();
            stmt=conn.prepareStatement(create_table);
            stmt.execute();
            release(stmt,conn);
    }
    //4、批量插入数据
        public static void putData(ArrayList<String []> list) throws ClassNotFoundException, SQLException{
            conn=getConnection();
            String insert_sql="insert into air_quality(date,level,AQI,AQI_ranking,PM2_5,Pm10)"
                    + "values(?,?,?,?,?,?)"; //遍历list,为？赋值
            stmt=conn.prepareStatement(insert_sql);
            for(String[] line:list) {
                stmt.setString(1, line[0]);
                stmt.setString(2, line[1]);
                stmt.setString(3, line[2]);
                stmt.setString(4, line[3]);
                stmt.setString(5, line[4]);
                stmt.setString(6, line[5]);
                stmt.executeUpdate();
            }
            release(stmt,conn);
        }        
        
    //5、查询数据
    public static void scanData() throws ClassNotFoundException, SQLException{
        conn=getConnection();
        String select_sql="select * from air_quality";
        stmt=conn.prepareCall(select_sql);
        ResultSet rs=stmt.executeQuery();
        while(rs.next()) {
            String id=rs.getString(1);
            String date=rs.getString(2);
            String level=rs.getString(3);
            String AQI=rs.getString(4);
            String AQI_ranking=rs.getString(5);
            String PM2_5=rs.getString(6);
            String PM10=rs.getString(7);
            System.out.println(id+"\t"+date+"\t"+level+"\t"+AQI+"\t"+AQI_ranking+"\t"
                    +PM2_5+"\t"+PM10);
        }
    }

    //读取数据
    public static ArrayList<String []> readData()throws IOException {
        ArrayList<String[]> list = new ArrayList<String[]>();
        Scanner in = new Scanner(new File(file));
        while(in.hasNext()) {
            String read = in.nextLine();
            if(read!=null&&!read.equals(null)) {
                String[] line = read.split("\t");
                for(String str : line) {
                    System.out.println(str+",");
                }
                list.add(line);
            }
        }
        in.close();
        return list;
    }
    
        //主流程控制
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        ArrayList<String[]> list = readData();
        createTable();
        putData(list);//31天*6个数据，放在list集合中，批量插入数据表
        scanData();
    }    
    
}