Full acces to a database Learn programming Java



Lesson:

Access To Relational Databases


Exercise:

Full acces to a database


Objetive:

Create a program that allows the user to enter information about books and browse the existing data. It should handle the case where the data file does not exist when the program starts.


Code:

import java.util.*;
public class Main
{
	static void main(String[] args)
	{
		boolean finish = false;
		String option;
		String title, author, genere, sumary;
		SQLiteCommand cmd;

		SQLiteConnection conection = new SQLiteConnection("Data Source=ejemplo01.sqlite;Version=3;New=True;Compress=True;");
		conection.Open();

		if (!(new java.io.File("ejemplo01.sqlite")).isFile())
		{

			System.out.println("Creando la base de datos...");
		}

		String creacion = "CREATE TABLE IF NOT EXISTS books(title varchar(20)," +
		" author varchar(20),genere varchar(20), sumary varchar(20));";
		cmd = new SQLiteCommand(creacion, conection);
		cmd.ExecuteNonQuery();

		do
		{
			System.out.println();
			System.out.println("Books database");
			System.out.println();
			System.out.println("1.- Add a new Book");
			System.out.println("2.- View all Books");
			System.out.println("0.- Exit");
			System.out.println();
			System.out.print("Choose an Option: ");
			option = new Scanner(System.in).nextLine();

			switch (option)
			{
				case "0":
					finish = true;
					break;
				case "1":
					String continueOption;
					do
					{
						System.out.print("Enter the title: ");
						title = new Scanner(System.in).nextLine();

						System.out.print("Enter the author: ");
						author = new Scanner(System.in).nextLine();

						System.out.print("Enter the genere: ");
						genere = new Scanner(System.in).nextLine();

						System.out.print("Enter the sumary: ");
						sumary = new Scanner(System.in).nextLine();

						String insercion = "insert into books values ('" + title + "'," +
						" '" + author + "', '" + genere + "', '" + sumary + "');";
						cmd = new SQLiteCommand(insercion, conection);

						int cantidad = cmd.ExecuteNonQuery();
						if (cantidad < 1)
						{
							System.out.println("Insert Fails");
						}

						System.out.print("Enter another book (y/n): ");
						continueOption = new Scanner(System.in).nextLine();
					} while (continueOption.toString().toLowerCase().equals("y"));

					break;

				case "2":
					String consulta = "select * from books";
					cmd = new SQLiteCommand(consulta, conection);
					SQLiteDataReader datos = cmd.ExecuteReader();
					int rowCount = 1;
					System.out.println();
					while (datos.Read())
					{
						title = String.valueOf(datos[0]);
						author = String.valueOf(datos[1]);
						genere = String.valueOf(datos[2]);
						sumary = String.valueOf(datos[3]);

						System.out.printf("%1$s- Title: %2$s," + " Author: %3$s, Genere: %4$s, Sumary: %5$s" + "\r\n", rowCount, title, author, genere, sumary);
						rowCount++;
					}
					System.out.println();
					break;
			}
		} while (!finish);

		System.out.println("Bye!!");
		conection.Close();
	}
}



Juan A. Ripoll - Systems Tutorials and Programming Courses ©  All rights reserved.  Legal Conditions.