ランダムコードによるデータベースの生成とインポート

ランダムコードの生成とデータベースのインポート
データベーステーブル

create table rndcode
(
	id int auto_increment primary key,
	serialnum varchar(30),
	code varchar(30),
	price int,
	used bit,
	time DATE
);

pythonランダムコードを生成してファイルに出力
randomGenerate.py

#!/usr/bin/python
import random
import sys
import os

words = []
keys = []
unique_keys = []

for i in range(0, 10):
    words.append(str(i))

for i in range(0,26):
    words.append(str(chr(i+65)))
    words.append(str(chr(i+97)))

print(words)

def gen_key(count):
    for i in range(int(count)):
        s = ""
        for j in range(20):
            s += random.choice(words)
            if (j + 1)%4 == 0 and j != 19:
                s += '-'
        keys.append(s)

print(sys.argv[1])
gen_key(sys.argv[1])

unique_keys = list(set(keys))

if (os.path.exists("keys.txt")):
    os.remove("keys.txt")
file = open("keys.txt", mode="w")

for key in unique_keys:
    file.write(key)
    file.write("
")
file.close()

JAvaエンジニアリングランダムコードをデータベースにインポート
コードTree:

src
	database
		DBUtil.java
		ImportDB.java
	keys.txt
	randomGenerate.py
libraries
	mysql-connector-java-5.1.46.jar

DBUtil.java

package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {

	private static String CLASSFORNAME = "com.mysql.jdbc.Driver";
	private static String SERVERANDDB = "jdbc:mysql://localhost:3306/graborder?useSSL=false";
	private static String USER = "root";
	private static String PWD = "1234";
	
	static {
		try {
			Class.forName(CLASSFORNAME);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
    public static Connection getConnection() {
        Connection conn = null; 
        try {    
            conn = DriverManager.getConnection(SERVERANDDB,  USER, PWD);  
        }catch (SQLException e) {  
        	e.printStackTrace();  
        }   
        return conn;  
    }
    
    public static void release(Connection conn,Statement stmt ,ResultSet rs){  
        if (conn != null) {  
            try {  
                conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
        if (stmt != null) {  
            try {  
                stmt.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
        if(rs!=null){  
            try {  
                rs.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
    }
}

ImportDB.java

package database;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ImportDB {
	
	private static void readKeysFromfile(List keys) {
		BufferedReader br = null;
		String key = null;
		
		try {
			br = new BufferedReader(new FileReader("src/keys.txt"));
			while ((key = br.readLine()) != null) {
				keys.add(key);
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (br != null) {
					br.close();
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	private static int getPriceByIndex(List keys, int index) {
		int price = 1;
		
		int split = keys.size() / 2;
		int tmp = index / split;
		if (tmp <= 0) {
			price = 1;
		} else {
			price = 2;
		}
		return price;
	}
	private static void importKeysToDatabase(List keys) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DBUtil.getConnection();
			for (int i = 0; i < keys.size(); i++) {
				int price = getPriceByIndex(keys, i);
				String sql = "insert into rndcode(serialnum, code, price, used, time) values(?, ?, ?, ?, ?)";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, "none");
				pstmt.setString(2, keys.get(i));
				pstmt.setInt(3, price);
				pstmt.setBoolean(4, false);
				pstmt.setDate(5, null);
	            int row=pstmt.executeUpdate();  
	            if (row == 1) {  
	                System.out.println("Add code success!"); 
	            } else {
	            	System.out.println("Add code fail!");
	            }
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(conn, pstmt, null);
		}
	}
	
	private static void importKeysToDatabase() {
		List keys = new ArrayList();
		readKeysFromfile(keys);
		importKeysToDatabase(keys);
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		importKeysToDatabase();
	}

}