Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
- import java.io.BufferedReader;
- import java.io.FileNotFoundException;
- import java.io.FileReader;
- import java.io.IOException;
- import java.math.BigInteger;
- import java.sql.*;
- import java.sql.Date;
- import java.text.DateFormat;
- import java.text.ParseException;
- import java.text.SimpleDateFormat;
- import java.util.*;
- import java.util.concurrent.ThreadLocalRandom;
- /**
- * A Java MySQL PreparedStatement INSERT example.
- * Demonstrates the use of a SQL INSERT statement against a
- * MySQL database, called from a Java program, using a
- * Java PreparedStatement.
- *
- * Created by Alvin Alexander, http://devdaily.com
- */
- public class Insert
- {
- static ArrayList<ArrayList<String>> arrays = new ArrayList<>(20);
- static ArrayList<String> id;
- static ArrayList<String> firstName;
- static ArrayList<String> lastName;
- static ArrayList<String> email;
- static ArrayList<String> date;
- static ArrayList<String> company;
- static ArrayList<String> phone;
- static ArrayList<String> hour;
- static ArrayList<String> pesel;
- static ArrayList<String> nr;
- static ArrayList<String> street;
- static ArrayList<String> postal;
- static ArrayList<String> city;
- static ArrayList<String> country;
- static ArrayList<String> oplacone;
- static ArrayList<String> osobaFiz;
- static ArrayList<String> nip;
- static ArrayList<String> cena;
- static ArrayList<String> nazwaKwatery;
- static ArrayList<String> nazwaFunkcji;
- public Insert() {
- arrays.add(id = new ArrayList<>(500));
- arrays.add(firstName = new ArrayList<>(500));
- arrays.add(lastName = new ArrayList<>(500));
- arrays.add(email = new ArrayList<>(500));
- arrays.add(date = new ArrayList<>(500));
- arrays.add(company = new ArrayList<>(500));
- arrays.add(phone = new ArrayList<>(500));
- arrays.add(hour = new ArrayList<>(500));
- arrays.add(pesel = new ArrayList<>(500));
- arrays.add(nr = new ArrayList<>(500));
- arrays.add(street = new ArrayList<>(500));
- arrays.add(postal = new ArrayList<>(500));
- arrays.add(city = new ArrayList<>(500));
- arrays.add(country = new ArrayList<>(500));
- arrays.add(oplacone = new ArrayList<>(500));
- arrays.add(osobaFiz = new ArrayList<>(500));
- arrays.add(nip = new ArrayList<>(500));
- arrays.add(cena = new ArrayList<>(500));
- arrays.add(nazwaKwatery = new ArrayList<>(500));
- arrays.add(nazwaFunkcji = new ArrayList<>(500));
- }
- public static void main(String[] args) {
- Insert insert = new Insert();
- insert.readCSV("DATA.csv");
- Scanner keyboard = new Scanner(System.in);
- System.out.println("Database name:");
- String dbName = keyboard.nextLine();
- System.out.println("Username:");
- String userName = keyboard.nextLine();
- System.out.println("Password:");
- String password = keyboard.nextLine();
- System.out.println("Number of rows:");
- int rows = keyboard.nextInt();
- System.out.println("Please wait, this may take several minutes...");
- insertIntoTable(dbName, userName, password, rows);
- }
- public void readCSV(String csvFile) {
- BufferedReader br = null;
- String line = "";
- String cvsSplitBy = ";";
- try {
- br = new BufferedReader(new FileReader(csvFile));
- while ((line = br.readLine()) != null) {
- String[] country = line.split(cvsSplitBy);
- for (int i = 0; i < country.length; i++) {
- arrays.get(i).add(country[i]);
- }
- }
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- } finally {
- if (br != null) {
- try {
- br.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
- }
- public static void insertIntoTable(String dbName, String userName, String password, int rows) {
- try {
- String myDriver = "com.mysql.jdbc.Driver";
- String myUrl = "jdbc:mysql://localhost/"+dbName+"?autoReconnect=true&useSSL=false";
- Class.forName(myDriver);
- Connection conn = DriverManager.getConnection(myUrl, userName, password);
- for (int i = 0; i < rows; i++) {
- insertZakladyPogrzebowe(conn);
- insertZmarli(conn);
- insertFunkcje(conn);
- insertZleceniodawcy(conn);
- insertCenniki(conn);
- insertAdresy(conn);
- insertPogrzeby(conn);
- insertAktyZgonu(conn);
- insertPracownicy(conn);
- insertWlasciciele(conn);
- insertRodzajeKwater(conn);
- insertZlecenia(conn);
- insertKwatery(conn);
- insertZleconoNa(conn);
- insertOplaty(conn);
- insertOplacono(conn);
- }
- conn.close();
- }
- catch (Exception e) {
- System.err.println("Got an exception!");
- System.err.println(e);
- }
- }
- public static void insertZakladyPogrzebowe(Connection conn) throws SQLException {
- boolean ex = true;
- BigInteger nipNr = new BigInteger(getRandomNumber(10));
- BigInteger phoneNr = new BigInteger(getRandomNumber(9));
- while (ex) {
- try {
- ex = false;
- String query = " insert into zakladypogrzebowe(NIP, NazwaZakladu, Telefon, OsobaKontaktowaImie, OsobaKontaktowaNazwisko)"
- + " values (?, ?, ?, ?, ?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- preparedStmt.setString(1, String.valueOf(nipNr));
- preparedStmt.setString(2, company.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(3, String.valueOf(phoneNr));
- preparedStmt.setString(4, firstName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(5, lastName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.execute();
- }
- catch (MySQLIntegrityConstraintViolationException e) {
- System.err.println(e.getMessage());
- System.err.println("dalej zakłady");
- ex = true;
- nipNr = nipNr.add(new BigInteger("1"));
- }
- }
- }
- public static void insertZmarli(Connection conn) throws SQLException, ParseException {
- String query = " insert into zmarli(Imie, DrugieImie, Nazwisko, NazwiskoPanienskie, DrugieNazwisko, DataUrodzenia, DataZgonu, PESEL)"
- + " values (?, ?, ?, ?, ?, ?, ?, ?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- DateFormat format = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH);
- preparedStmt.setString(1, firstName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(2, firstName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(3, lastName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(4, lastName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(5, lastName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setDate(6, new Date(format.parse(date.get(ThreadLocalRandom.current().nextInt(0, 500))).getTime()));
- preparedStmt.setDate(7, new Date(format.parse(date.get(ThreadLocalRandom.current().nextInt(0, 500))).getTime()));
- preparedStmt.setString(8, pesel.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.execute();
- }
- public static void insertFunkcje(Connection conn) throws SQLException {
- String query = " insert into funkcje(Nazwa)"
- + " values (?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- preparedStmt.setString(1, nazwaFunkcji.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.execute();
- }
- public static void insertZleceniodawcy(Connection conn) throws SQLException, ParseException {
- String query = " insert into zleceniodawcy(Imie, Nazwisko, OsobaFizyczna, Nazwa, NIP, Telefon, Email)"
- + " values (?, ?, ?, ?, ?, ?, ?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- BigInteger phoneNr = new BigInteger(getRandomNumber(9));
- preparedStmt.setString(1, firstName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(2, lastName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setBoolean(3, Boolean.valueOf(osobaFiz.get(ThreadLocalRandom.current().nextInt(0, 500))));
- preparedStmt.setString(4, company.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(5, nip.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(6, String.valueOf(phoneNr));
- preparedStmt.setString(7, email.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.execute();
- }
- public static void insertCenniki(Connection conn) throws SQLException {
- String query = " insert into cenniki(CenaZa10, CenaZa20, CenaZaWieczyste)"
- + " values (?, ?, ?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- preparedStmt.setDouble(1, Double.parseDouble(cena.get(ThreadLocalRandom.current().nextInt(0, 500))));
- preparedStmt.setDouble(2, Double.parseDouble(cena.get(ThreadLocalRandom.current().nextInt(0, 500))));
- preparedStmt.setDouble(3, Double.parseDouble(cena.get(ThreadLocalRandom.current().nextInt(0, 500))));
- preparedStmt.execute();
- }
- public static void insertAdresy(Connection conn) throws SQLException {
- String query = " insert into adresy(Ulica, NrBudynku, NrLokalu, KodPocztowy, Miejscowosc, Kraj)"
- + " values (?, ?, ?, ?, ?, ?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- preparedStmt.setString(1, street.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(2, nr.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(3, nr.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(4, postal.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(5, city.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(6, country.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.execute();
- }
- public static void insertPogrzeby(Connection conn) throws SQLException, ParseException {
- String query = " insert into pogrzeby(DzienPogrzebu, GodzinaPogrzebu, ZmarlyID, NIPZakladu)"
- + " values (?,?,?,?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- DateFormat format = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH);
- preparedStmt.setDate(1, new Date(format.parse(date.get(ThreadLocalRandom.current().nextInt(0, 500))).getTime()));
- preparedStmt.setTime(2, java.sql.Time.valueOf(hour.get(ThreadLocalRandom.current().nextInt(0, 500))+":00"));
- preparedStmt.setInt(3, Integer.parseInt(getRandomID(conn, "zmarli", "ZmarlyID")));
- preparedStmt.setString(4, getRandomID(conn, "zakladypogrzebowe", "NIP"));
- preparedStmt.execute();
- }
- public static void insertPracownicy(Connection conn) throws SQLException {
- String query = " insert into pracownicy(Imie, Nazwisko, DrugieImie, DrugieNazwisko, PESEL, Telefon, Email, FunkcjaID)"
- + " values (?,?,?,?,?,?,?,?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- BigInteger phoneNr = new BigInteger(getRandomNumber(9));
- preparedStmt.setString(1, firstName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(2, lastName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(3, firstName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(4, lastName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(5, pesel.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(6, String.valueOf(phoneNr));
- preparedStmt.setString(7, email.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setInt(8, Integer.parseInt(getRandomID(conn, "funkcje", "FunkcjaID")));
- preparedStmt.execute();
- }
- public static void insertWlasciciele(Connection conn) throws SQLException {
- String query = " insert into wlasciciele(Imie, DrugieImie, NazwiskoWlasciciela, DrugieNazwisko, Telefon, Email, AdresID)"
- + " values (?,?,?,?,?,?,?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- BigInteger phoneNr = new BigInteger(getRandomNumber(9));
- preparedStmt.setString(1, firstName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(2, firstName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(3, lastName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(4, lastName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(5, String.valueOf(phoneNr));
- preparedStmt.setString(6, email.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setInt(7, Integer.parseInt(getRandomID(conn, "adresy", "AdresID")));
- preparedStmt.execute();
- }
- public static void insertRodzajeKwater(Connection conn) throws SQLException {
- String query = " insert into rodzajekwater(Nazwa, CennikID)"
- + " values (?,?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- preparedStmt.setString(1, nazwaKwatery.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setInt(2, Integer.parseInt(getRandomID(conn, "cenniki", "idCennik")));
- preparedStmt.execute();
- }
- public static void insertZlecenia(Connection conn) throws SQLException, ParseException {
- String query = " insert into zlecenia(DataRozpoczecia, DataZakonczenia, Oplacone, PracownikID, UslugaID, ZleceniodawcaID)"
- + " values (?,?,?,?,?,?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- DateFormat format = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH);
- preparedStmt.setDate(1, new Date(format.parse(date.get(ThreadLocalRandom.current().nextInt(0, 500))).getTime()));
- preparedStmt.setDate(2, new Date(format.parse(date.get(ThreadLocalRandom.current().nextInt(0, 500))).getTime()));
- preparedStmt.setBoolean(3, Boolean.valueOf(oplacone.get(ThreadLocalRandom.current().nextInt(0, 500))));
- preparedStmt.setInt(4, Integer.parseInt(getRandomID(conn, "pracownicy", "PracownikID")));
- preparedStmt.setInt(5, 1);
- preparedStmt.setInt(6, Integer.parseInt(getRandomID(conn, "zleceniodawcy", "ZleceniodawcaID")));
- preparedStmt.execute();
- }
- public static void insertAktyZgonu(Connection conn) throws SQLException, ParseException {
- boolean ex = true;
- BigInteger num = new BigInteger(getRandomNumber(6));
- while (ex) {
- try {
- ex = false;
- String query = " insert into aktyzgonu(NrAktu, ZmarlyID, DataWystawienia, GodzinaZgonu, ImieOjca, NazwiskoOjca, ImieMatki, NazwiskoMatki)"
- + " values (?, ?, ?, ?, ?, ?, ?,?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- DateFormat format = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH);
- preparedStmt.setString(1, String.valueOf(num));
- preparedStmt.setInt(2, Integer.parseInt(getRandomID(conn, "zmarli", "ZmarlyID")));
- preparedStmt.setDate(3, new Date(format.parse(date.get(ThreadLocalRandom.current().nextInt(0, 500))).getTime()));
- preparedStmt.setTime(4, java.sql.Time.valueOf(hour.get(ThreadLocalRandom.current().nextInt(0, 500)) + ":00"));
- preparedStmt.setString(5, firstName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(6, lastName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(7, firstName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.setString(8, lastName.get(ThreadLocalRandom.current().nextInt(0, 500)));
- preparedStmt.execute();
- }
- catch (MySQLIntegrityConstraintViolationException e) {
- System.err.println(e.getMessage());
- System.err.println("dalej akty");
- ex = true;
- System.out.println(num);
- num = num.add(new BigInteger("1"));
- System.out.println(num);
- }
- }
- }
- public static void insertKwatery(Connection conn) throws SQLException {
- boolean ex = true;
- int sekNr = ThreadLocalRandom.current().nextInt(1, 999);
- int kwatNr = ThreadLocalRandom.current().nextInt(1, 999);
- while (ex) {
- try {
- ex = false;
- String query = " insert into kwatery(NrSektora, NrKwatery, RodzajID)"
- + " values (?,?,?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- preparedStmt.setInt(1, sekNr);
- preparedStmt.setInt(2, kwatNr);
- preparedStmt.setInt(3, Integer.parseInt(getRandomID(conn, "rodzajekwater", "idRodzajKwat")));
- preparedStmt.execute();
- } catch (MySQLIntegrityConstraintViolationException e) {
- System.err.println(e.getMessage());
- System.err.println("dalej kwatery");
- ex = true;
- kwatNr++;
- }
- }
- }
- public static void insertZleconoNa(Connection conn) throws SQLException {
- String query = " insert into zleconona(NrSektora, NrKwatery)"
- + " values (?,?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- String[] ids = getRandomDoubleID(conn, "kwatery", "NrSektora", "NrKwatery");
- preparedStmt.setInt(1, Integer.parseInt(ids[0]));
- preparedStmt.setInt(2, Integer.parseInt(ids[1]));
- preparedStmt.execute();
- }
- public static void insertOplaty(Connection conn) throws SQLException, ParseException {
- String query = " insert into oplaty(DzienWplaty, DataObowiazywania, WlascicielID, CennikID, NrSektora, NrKwatery)"
- + " values (?,?,?,?,?,?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- String[] ids = getRandomDoubleID(conn, "kwatery", "NrSektora", "NrKwatery");
- DateFormat format = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH);
- preparedStmt.setDate(1, new Date(format.parse(date.get(ThreadLocalRandom.current().nextInt(0, 500))).getTime()));
- preparedStmt.setDate(2, new Date(format.parse(date.get(ThreadLocalRandom.current().nextInt(0, 500))).getTime()));
- preparedStmt.setInt(3, Integer.parseInt(getRandomID(conn, "wlasciciele", "WlascicielID")));
- preparedStmt.setInt(4, Integer.parseInt(getRandomID(conn, "cenniki", "idCennik")));
- preparedStmt.setInt(5, Integer.parseInt(ids[0]));
- preparedStmt.setInt(6, Integer.parseInt(ids[1]));
- preparedStmt.execute();
- }
- public static void insertOplacono(Connection conn) throws SQLException {
- String query = " insert into oplacono(ZmarlyID, OplataID)"
- + " values (?,?)";
- PreparedStatement preparedStmt = conn.prepareStatement(query);
- preparedStmt.setInt(1, Integer.parseInt(getRandomID(conn, "zmarli", "ZmarlyID")));
- preparedStmt.setInt(2, Integer.parseInt(getRandomID(conn, "oplaty", "OplataID")));
- preparedStmt.execute();
- }
- public static ArrayList<String> getID(Connection con, String tablename, String idname) {
- ArrayList<String> resArray = new ArrayList<>();
- try {
- //Class.forName("com.mysql.jdbc.Driver").newInstance();
- //Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mydb", "root", "qqqq");
- Statement st = con.createStatement();
- String sql = ("SELECT * FROM " + tablename + " ORDER BY " + idname + ";");
- ResultSet rs = st.executeQuery(sql);
- while (rs.next()) {
- String id = rs.getString(idname);
- resArray.add(id);
- }
- //con.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return resArray;
- }
- public static String[] getRandomDoubleID(Connection con, String tablename, String idname1, String idname2) {
- ArrayList<String> resArray1 = new ArrayList<>();
- ArrayList<String> resArray2 = new ArrayList<>();
- try {
- //Class.forName("com.mysql.jdbc.Driver").newInstance();
- //Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mydb", "root", "qqqq");
- Statement st = con.createStatement();
- String sql = ("SELECT * FROM " + tablename + " ORDER BY " + idname1 + ";");
- ResultSet rs = st.executeQuery(sql);
- while (rs.next()) {
- String id1 = rs.getString(idname1);
- String id2 = rs.getString(idname2);
- resArray1.add(id1);
- resArray2.add(id2);
- }
- // con.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- String[] doubleRes = new String[2];
- int random = ThreadLocalRandom.current().nextInt(0, resArray1.size());
- doubleRes[0] = resArray1.get(random);
- doubleRes[1] = resArray2.get(random);
- return doubleRes;
- }
- public static String getRandomNumber(int digCount) {
- Random rnd = new Random();
- StringBuilder sb = new StringBuilder(digCount);
- for(int i=0; i < digCount; i++)
- sb.append((char)('0' + rnd.nextInt(10)));
- return sb.toString();
- }
- public static String getRandomID(Connection con, String tablename, String idname) {
- ArrayList<String> ids = getID(con, tablename, idname);
- return ids.get(ThreadLocalRandom.current().nextInt(0, ids.size()));
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement