package com.company;

import java.sql.*;

/**
 * Created by philos_ofas on 25/01/2017.
 */
public class DBConnection {


	public Statement getConnection() throws SQLException, ClassNotFoundException {
		Connection conn = connectTODB();
		Statement statement = null;
		statement = conn.createStatement();
		String sql = "CREATE TABLE IF NOT EXISTS Login (id INT,"
				+ "UserName VARCHAR(100),"
				+ "Password VARCHAR(100),"
				+ "FirstName VARCHAR(50),"
				+ "LastName VARCHAR(50),"
				+ "Address VARCHAR(150),"
				+ "PostalCode Varchar (50),"
				+ "BirthDate Date);";
		statement.executeUpdate(sql);

		System.out.println("Sukurta Lenta Login");

		String insertDataSql = "INSERT INTO Login (id, UserName, Password, FirstName, LastName, Address, PostalCode, BirthDate)\n" +
				"VALUES ('1234','philos_ofas','pass','Aurimas', 'Kraulaidys', 'zygio g. Vilnius','8345', '2014-11-01');";
		statement.executeUpdate(insertDataSql);

		System.out.println("Įterpta eilute i Lenta Login");

		String SelectAll = "Select * from Login;";
		ResultSet resultSet = statement.executeQuery(SelectAll);

		while (resultSet.next()) {
			String userValue = resultSet.getString("UserName");
			String passwordValue = resultSet.getString("Password");
			String firstNameValue = resultSet.getString("FirstName");
			Date BirthDateValue = resultSet.getDate("BirthDate");
			System.out.println("Some info after Select query: UserName " + userValue + " password: "
					+ passwordValue + " BirthDate: " + BirthDateValue );
		}

		return statement;
	}


	private Connection connectTODB() throws ClassNotFoundException, SQLException {
		Connection connection = null;
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/";
		String dbName = "CarRental";
		String driver = "com.mysql.jdbc.Driver";
		String userName = "root";
		String password = "pass";
		connection = (Connection) DriverManager.getConnection(url + dbName +"?"+ "user=" +userName + "&password="+ password);
		System.err.println("Connected to database");
		return connection;
	}



}