2-1-16

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_sql="create table if not exists rksl(id int(3) primary key auto_increment,"
                + "gj varchar(20) not null,szz varchar(20) not null,"
                + "rk varchar(255) not null,zb varchar(100) not null)";
        stmt=conn.prepareStatement("drop table if exists rksl");
        stmt.execute();
        stmt=conn.prepareStatement(create_sql);
        stmt.execute();
        stmt.close();
        conn.close();
    }
    //4、批量插入数据
    public static void putData(ArrayList<String []> list) throws ClassNotFoundException, SQLException{
        conn=getConnection();
        String insert_sql="insert into rksl(gj,szz,rk,zb)"
                + "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.executeUpdate();
        }
        stmt.close();
        conn.close();
    }                    
        
    //5、查询数据
    public static void scanData() throws ClassNotFoundException, SQLException{
        conn=getConnection();
        String select_sql="select * from rksl";
        stmt=conn.prepareStatement(select_sql);
        ResultSet rs=stmt.executeQuery();
        while(rs.next()) {
            String gj=rs.getString(1);
            String szz=rs.getString(2);
            String rk=rs.getString(3);
            String zb=rs.getString(4);
            System.out.println(gj+"\t"+szz+"\t"+rk+"\t"+zb);
        }
        rs.close();
        stmt.close();
        conn.close();
    }
    //读取数据
    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);
        scanData();
    }    
    
}