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

public class MariaDbJdbcDriverTest {
	private static final String URL = "jdbc:mariadb://localhost:3306/db1";
	private static final String USER = "testuser";
	private static final String PWD = "password";

	public static Connection setUp() {
		Connection connection = null;
		
		try {
			DriverManager.registerDriver(new org.mariadb.jdbc.Driver()); 
			connection = DriverManager.getConnection(URL, USER, PWD); 
			connection.setAutoCommit(false); 
		}
		catch (Exception e) {
		}
		
		return connection;
	}

	public static void tearDown(Connection connection) {
		try {
			connection.commit(); connection.close(); }
		catch (Exception e) {
		}
	}

	public static void testStatementGetTime(Connection connection) {
		final String sql = "SELECT id, time_test FROM time_test WHERE id=1;";
		Statement statement;
		ResultSet resultSet;
		
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);

			while (resultSet.next()) {
				System.out.println("ID: " + resultSet.getInt(1) + ", Time: " + resultSet.getTime(2)); 
			}
			
			resultSet.close();
			statement.close();
		}
		catch (Exception e) {
		}
	}
	
	public static void testStatementGetString(Connection connection) {
		final String sql = "SELECT id, time_test FROM time_test WHERE id=1;";
		Statement statement;
		ResultSet resultSet;
		
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);

			while (resultSet.next()) {
				System.out.println("ID: " + resultSet.getInt(1) + ", Time: " + resultSet.getString(2)); 
			}
			
			resultSet.close();
			statement.close();
		}
		catch (Exception e) {
		}
	}

	public static void testPreparedStatementGetTime(Connection connection) {
		final String sql = "SELECT id, time_test FROM time_test WHERE id=?;";
		PreparedStatement preparedStatement;
		ResultSet resultSet;
	
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, 1);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				System.out.println("ID: " + resultSet.getInt(1) + ", Time: " + resultSet.getTime(2)); 
			}
			
			resultSet.close();
			preparedStatement.close();
		}
		catch (Exception e) {
		}
	}
	
	public static void testPreparedStatementGetString(Connection connection) {
		final String sql = "SELECT id, time_test FROM time_test WHERE id=?;";
		PreparedStatement preparedStatement;
		ResultSet resultSet;
	
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, 1);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				System.out.println("ID: " + resultSet.getInt(1) + ", Time: " + resultSet.getString(2)); 
			}
			
			resultSet.close();
			preparedStatement.close();
		}
		catch (Exception e) {
		}
	}
	
	public static void main(String[] args) {
		Connection connection;
		
		connection = setUp();
		
		System.out.println("Testing Statement with getTime()");
		testStatementGetTime(connection);
		
		System.out.println("Testing PreparedStatement with getTime()");
		testPreparedStatementGetTime(connection);
		
			System.out.println("Testing Statement with getString()");
		testStatementGetString(connection);
		
		System.out.println("Testing PreparedStatement with getString()");
		testPreparedStatementGetString(connection);
		
		tearDown(connection);
		
	}
}