JDBC非万能模板 & arraylist泛型数组 及 初始化 & 时间测试 & 通配符Insert

Moni.java

• 有好多注意的地方，看注释

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Scanner;
//20km/h - 60km/h   108s之内(20km/h跑600m)肯定会拍到一次，30s(60km/h跑500m)之内不会拍到第二次，5min/300s内最多有10次出镜机会
// 600m的距离至少需要36s, 500米距离最多需要90s
// 20万辆车有 200万次记录
class Direction {
    int x_offset;
    int y_offset;
    public Direction(int x, int y) {
        x_offset = x;
        y_offset = y;
    }
}
class Position {
    int x, y; // 长40个卡口间距500，宽40个卡口间距600的一个矩形
    int time;
}
class Record { // 用于卡口记录的类
    int car_id;
    int x, y;
    public Record(int id, int x, int y){
        this.car_id = id;
        this.x = x;
        this.y = y;
    }
    public void print() {
        System.out.println(car_id + " " + x + " " + y);
        System.out.println("hello");
    }
}
class Car {
    int num;
    String str;
    int totalTime; // 每辆车的5分钟内计时时间,跟Position中的time好像重复了
    int idx; // 记录汽车5分钟内的当前的第几次，查询的时候用
    Position[] pos;
    public Car(int num, String str, int totalTime){
        this.num = num;
        this.str = str;
        this.totalTime = totalTime;
        pos = new Position[15]; // 注意一定要再申请一下，否则会报错
    }
    public void setPos(int idx, int x, int y, int time) {
        pos[idx] = new Position(); // 注意一定要再申请一下，否则会报错
        pos[idx].x = x;
        pos[idx].y = y;
        totalTime += time;
        pos[idx].time = totalTime;
    }
}
public class Moni {
    static String str; //车牌号
    static Scanner cin;
    static BufferedInputStream bis;
    static Direction[] directions = new Direction[4];
    static int tmp; // 生成时间随机数的变量
    static ArrayList[] records = new ArrayList[300]; // 将卡口的记录数据按照时间顺序放进ArrayList，只记录300s内数据
                                                     // ArrayList 不支持泛型数组
    public static void main(String[] args) {
        // 定义方向
        directions[0] = new Direction(0, 1); // 这里的赋值放到main中，不要放到静态区
        directions[1] = new Direction(0, -1);
        directions[2] = new Direction(-1, 0);
        directions[3] = new Direction(1, 0); // 按照 上下左右 的顺序排列
        // 生成车辆
        Car[] car = new Car[200000];
        // 卡口的Arraylist初始化
        for(int i = 0; i < 300; i++) {
            records[i] = new ArrayList<Record>();
        }
        // 从car.txt 中读取数据
        try {
            bis = new BufferedInputStream(new FileInputStream("car.txt")); // 放到和src同级的位置
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        cin = new Scanner(bis);
        long startTime=System.currentTimeMillis();   //获取开始时间
        // 对每辆车进行初始化，第一次是生成，以后是从数据库中读取
        for(int i = 0; i < 200000; i++) {
            str = cin.nextLine();
            if (str.endsWith("0")) {
                str = str.substring(0, 7);
            } else {
                str = str.substring(8);
            }
            car[i] = new Car(i, str, 0); // 后面申请对象时，totalTime会根据数据库中查询得到的进行
            // 生成车辆第一次出现的信息
            tmp = (int) (Math.random() * 78 + 30); // 以后的5分钟车出现的第一次时间就不一定是30-108之间了，要根据上一次的totaltime来算
            car[i].pos[0] = new Position(); // 注意一定要先申请一下，否则会报错
            car[i].setPos(0, (int) (Math.random() * 40), (int) (Math.random() * 40), tmp);
//            System.out.println(car[i].pos[0].x);
        }
        // 对每辆车进行5分钟内的模拟
        for(int i = 0; i < 200; i++) {
            int dir; //下一步的方向
            int idx = 1; // 第几次被拍到
            while(true){
                tmp = (int) (Math.random() * 78 + 30);
                if(tmp < 36) dir = (int) (Math.random() * 2 + 2);
                else if (tmp > 90) dir = (int) (Math.random() * 2);
                else dir = (int) (Math.random() * 4); // 600m的距离至少需要36s, 500米距离最多需要90s
                int dirx = car[i].pos[idx - 1].x + directions[dir].x_offset;
                int diry = car[i].pos[idx - 1].y + directions[dir].y_offset;
                // 判断是否车会越界
                if(dirx < 0 || dirx >= 40) dirx = car[i].pos[idx - 1].x - directions[dir].x_offset;
                if(diry < 0 || diry >= 40) diry = car[i].pos[idx - 1].y - directions[dir].y_offset;
//              System.out.print(i + " " + car[i].totalTime + " " + tmp);
                car[i].setPos(idx, dirx, diry, tmp);
//              System.out.println(car[i].num + " " + car[i].pos[idx].x + " " + car[i].pos[idx].y + " " + car[i].pos[idx].time);
//              System.out.println(" " + car[i].totalTime);
                car[i].idx = idx; // 记录当前5分钟内是第几次出现，方便查询时候用
                idx ++;
                // 把下一个5分钟的首次出现也记录进去了
                if(car[i].totalTime >= 300) break; // 300s 归类为下一个5分钟的0s

                // 将数据记录到卡口的ArrayList中
                records[car[i].totalTime].add(new Record(i, dirx, diry));
            }
        }
        // 进行MySql插入
        int id = 0;
        record_dataDAO rDao = new record_dataDAO();
        record_data rd = new record_data();
        for(int i = 0; i < 300; i++) {
            for(int j = 0; j < records[i].size(); j++) {
                Record record = (Record) records[i].get(j);
                rd.setId(id);
                rd.setTime(i); // 这里只是插入前5分钟的，所以用i，如果是后面的要加上一个基数
                rd.setCar_str(car[record.car_id].str);
                rd.setPos_x(record.x);
                rd.setPos_y(record.y);
                rDao.insert1(rd);
                id++;
            }
            System.out.println("第" + id + "行 已经instert");
        }
        //rDao.deleteAll(); // 清空数据库
        long endTime=System.currentTimeMillis(); //获取结束时间
        System.out.println("程序运行时间： " + (endTime - startTime) + "ms");
    }
}

BaseConection.java

• 连接数据库的类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class BaseConnection {
    public static Connection getConnection(){ //涓轰簡渚夸簬璋冪敤,鍙互鐢ㄧ被鐨勫璞＄洿鎺ヨ皟鐢�
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver"); //导入驱动包
            conn = DriverManager.
            getConnection("jdbc:mysql://localhost:3306/mydb", "root", "");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    public static void closeRes(ResultSet rs,PreparedStatement ps,Connection conn){
        try {
            if(rs!=null){
                rs.close();
            }
            if(ps!=null){
                ps.close();
            }
            if(conn!=null){
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void closeRes(PreparedStatement ps,Connection conn){
        try {
            if(ps!=null){
                ps.close();
            }
            if(conn!=null){
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        Connection conn =BaseConnection.getConnection();
        System.out.println(conn);
    }
}

record_data.java

• 实体类，对应于Mysql中的表

public class record_data {
    int id;
    int time;
    String car_str;
    int pos_x;
    int pos_y;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getTime() {
        return time;
    }
    public void setTime(int time) {
        this.time = time;
    }
    public String getCar_str() {
        return car_str;
    }
    public void setCar_str(String car_str) {
        this.car_str = car_str;
    }
    public int getPos_x() {
        return pos_x;
    }
    public void setPos_x(int pos_x) {
        this.pos_x = pos_x;
    }
    public int getPos_y() {
        return pos_y;
    }
    public void setPos_y(int pos_y) {
        this.pos_y = pos_y;
    }
}

record_dataDAO.java

• Dao类，每个实体对应一个

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
public class record_dataDAO {
    public ArrayList<record_data> getList() {
        ArrayList<record_data> ar = new ArrayList<record_data>();
        Connection conn = BaseConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select * from record_data";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                record_data ne = new record_data();
                ne.setId(rs.getInt("id"));
                ne.setTime(rs.getInt("time"));
                ne.setCar_str(rs.getString("car_str"));
                ne.setPos_x(rs.getInt("pos_x"));
                ne.setPos_y(rs.getInt("pos_y"));
                ar.add(ne);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseConnection.closeRes(rs, ps, conn);
        }
        return ar;
    }
    public ArrayList<record_data> getListAll() {
        ArrayList<record_data> ar = new ArrayList<record_data>();
        Connection conn = BaseConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select * from record_data ";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                record_data ne = new record_data();
                ne.setId(rs.getInt("id"));
                ne.setTime(rs.getInt("time"));
                ne.setCar_str(rs.getString("car_str"));
                ne.setPos_x(rs.getInt("pos_x"));
                ne.setPos_y(rs.getInt("pos_y"));
                ar.add(ne);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseConnection.closeRes(rs, ps, conn);
        }
        return ar;

    }
    public void insert(record_data ne) { // id自增的Insert
        Connection conn = BaseConnection.getConnection();
        PreparedStatement ps = null;
        String sql = "insert into record_data (time,car_str,pos_x,pos_y) " + "values (" + ne.getTime() + ",'"
                + ne.getCar_str() + "'," + ne.getPos_x() + "," + ne.getPos_y() + ")";
        try {
            ps = conn.prepareStatement(sql);
            int a = ps.executeUpdate();
            if (a > 0) {
                System.out.println("Yes");
            } else {
                System.out.println("Wrong");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseConnection.closeRes(ps, conn);
        }
    }
    public boolean insert1(record_data ne) { // 自己设置id的Instert，mysql上勾选AI也可以
        boolean b = false;
        Connection conn = BaseConnection.getConnection();
        PreparedStatement ps = null;
        String sql = "insert into record_data (id,time,car_str,pos_x,pos_y) " + "values (?,?,?,?,?)";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, ne.getId());
            ps.setInt(2, ne.getTime());
            ps.setString(3, ne.getCar_str());
            ps.setInt(4, ne.getPos_x());
            ps.setLong(5, ne.getPos_y());
            int a = ps.executeUpdate();
            if (a > 0) {
                b = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseConnection.closeRes(ps, conn);
        }
        return b;
    }
    public boolean update(record_data ne) {
        boolean b = false;
        Connection conn = BaseConnection.getConnection();
        PreparedStatement ps = null;
        String sql = "update record_data set time = ?,car_str = ?,pos_x = ?,pos_y = ? " + " where id = ? ";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, ne.getTime());
            ps.setString(2, ne.getCar_str());
            ps.setInt(3, ne.getPos_x());
            ps.setInt(4, ne.getPos_y());
            ps.setInt(5, ne.getId());
            int a = ps.executeUpdate();
            if (a > 0) {
                b = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseConnection.closeRes(ps, conn);
        }
        return b;
    }
    public boolean delete(int id) {
        boolean b = false;
        Connection conn = BaseConnection.getConnection();
        PreparedStatement ps = null;
        String sql = "delete from record_data where id = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            int a = ps.executeUpdate();
            if (a > 0) {
                b = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseConnection.closeRes(ps, conn);
        }
        return b;
    }
    public void deleteAll() {
        Connection conn = BaseConnection.getConnection();
        PreparedStatement ps = null;
        String sql = "delete from record_data";
        try {
            ps = conn.prepareStatement(sql);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseConnection.closeRes(ps, conn);
        }
    }
    // Test
    public static void main(String[] args) {
        record_data ne = new record_data();
        record_dataDAO neda = new record_dataDAO();
        ne.setTime(100);
        ne.setCar_str("A100034");
        ne.setPos_x(20);
        ne.setPos_y(30);
        ne.setId(1);
        // neda.insert1(ne); // 自己定义id的插入
        // neda.insert(ne); // id 自增的插入
        // neda.update(ne);
        // neda.delete(2);
        ArrayList<record_data> ar = new record_dataDAO().getListAll();
        for (record_data net : ar) {
            System.out.println(net.getId() + " " + net.getTime() + " " + net.getCar_str() + " " + net.getPos_x() + " "
                    + net.getPos_y());
        }
    }
}