《MySQL系列-开发相关》MySQL新建数据库表并存储2010年到2030年的日期

MySQL新建数据库表并存储2010年到2030年的日期

一、建表

CREATE TABLE `datetime` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `datetime` varchar(30) DEFAULT NULL,
  `weekday` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7401 DEFAULT CHARSET=latin1;

二、代码导入数据

POM依赖

			<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

java代码

import java.sql.*;

public class MySQL {
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接对象
        String url = "jdbc:mysql://ip:port/zxy";
        String user = "root";
        String password = "********";
        Connection connection = DriverManager.getConnection(url, user, password);

        return connection;
    }
    public static void close(Statement statement, Connection connection) throws SQLException {
        if (statement != null && connection != null){
            statement.close();
            connection.close();
        }
    }

    /**
     * 测试
     * @param args
     */
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //获得connection
        Connection connection = MySQL.getConnection();

            String sql = "insert into zxy.datetime (datetime) select DATE_ADD(date_format('2010-01-01','%Y-%m-%d'),INTERVAL ? DAY) from dual;";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            int i = 0;
            for (i = 0; i < 7400; i++) {
                preparedStatement.setInt(1, i);
                preparedStatement.executeUpdate();
            }


        //关闭连接
        MySQL.close(preparedStatement,connection);
    }
}

三、查看效果

select * from zxy.datetime d order by id
第三个字段weekday暂时没用到