Posts

Showing posts from December, 2017

Task 16 - Javafx Program to Create, Retrieve, Update and Delete records with Database

Image
Your database name must be cuonline and table name users.  Must create users table using below schema: CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) DEFAULT NULL, `password` varchar(45) DEFAULT NULL, `city` varchar(100) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `subjects` varchar(255) DEFAULT NULL, `gender` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 See the above given figure and try to understand what the application intend to do. Implement at least following features: Develop above layout and form. When user fill the form and press Add User button, add new user record in users table as per information filled in the form. Once user is added, update the list shown at lift side. When user click the "Update" button, show a form with an text input field for user ID and a "Show Update Form" button, whe

Task 15 - Java Database Connectivity with MySQL - Assignment 3, Section A

Image
Assume ‘infosys’ database contains 3 tables i.e. products, product_categories and sales. You can  download the SQL file  to create above database, tables and to insert some records for testing purpose. Make a java class having following static methods (class name shall be your registration number and name): static void updateProductNameAndStatus(int productID, String newName, boolean newStatus) static int getAverageSalePrice(int productID) static ArrayList<String> getProductsNames(int categoryID, String searchQueryString)

Task 14 - Java Database Connectivity with MySQL - Assignment 3, Section B

Image
Assume ‘infosys’ database contains 3 tables i.e. products, product_categories and sales. Column name and data type of each column is shown in below diagram: You can download the SQL file  to create above database, tables and to insert some records for testing purpose. Make a java class having following static methods (class name shall be your registration number and name): static void updateSalePrice(int productID, int invoiceID, double newUnitPrice) static int getTotalSalesBySalemanID(int salemanID) static ArrayList<String> getProductsNamesByInvoiceID(int invoiceID) updateSalePrice shall update the unit price in sales table for the records where invoice ID and productID (passed as argument) matches. getTotalSalesBySalemanID shall return the total sales made by the saleman whose ID is passed as argument. getProductsNamesByInvoiceID shall return the list of products’ names that were sold under the invoiceID passed as argument. Definition of main method is given on

Task 13 - Java JDBC and Basic SQL Practice Exercise

Download SQL script file . Connect MySQL Workbench with MySQL Database server. In Workbench, choose menu: File > Open SQL Script option and select the downloaded file. Run the script to create a database of two tables i.e. products and product_categories. You can open the script in Notepad++ to anaylyze, it contains SQL statements to create database, tables and few insert statement to add some seed data. Write Java application that shall provide following features: Show all categories names with products count of that category e.g. Mobiles - 2 Show list of categories (id and name only) to user to choose a category id by user input. Show product id, name, description, minimum stock level and cost of all products that belongs to the given category id. Show average cost of products the store sell. Show products order by category. Show products where minimum stock level is between 50 and 300 (inclusive). Let user search the products by entering a string and show all products who

Java JDBC CRUD Example with MySQL

Below are the Java code samples that uses Java Database Connectivity API (JDBC) to Create, Retrieve, Update and Delete records using MySQL databases. Before running below examples, you must add Connector/J i.e. MySQL Database driver in your classpath, Click Here to Download Connector/J . I have created a database named cuonline that contains a 'students' table. The SQL statement to create the table is given below: CREATE TABLE `students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, `email` varchar(45) DEFAULT NULL, `gender` varchar(45) DEFAULT NULL, `status` tinyint(4) DEFAULT NULL, `city` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1; You noticed, id column is set as 'auto-increment', so it would be automatically initialized if we do not pass its value while inserting new records. For each record, database would increment the value by one while i

Task 12 - Create Java Program with MySQL Database Connectivity

There are 3 parts of this assignment or programming task. Part 1: Understand Database Server, Client and SQL In MySQL Server, create a database "infosys" that shall have "users" table, using MySQL. The table shall have id, name, gender, phone and status field of type int, varchar, varchar, varchar and boolean/tinyint respectively. After creating the table, do following: Add 4-5 records using MySQL Workbench Retrieve all (using GUI) or selected records and columns from the table using GUI and SQL Delete records, single and multiple using GUI and SQL Update records' different fields using GUI and SQL If you have not attended database introduction or database connectivity lectures. You can watch here: Database Introduction   Database Connectivity Do not start Part 2, until you are comfortable with running CRUD queries from Java program. _____________________________ Part 2: Its continuation of Task 10 . I have also uploaded the solution on this

Java Interfaces and Exception Handling - Practice Problem with Solution

Problem Statement: Create a UserService Interface with following methods signatures. void addUser(User user) throws UserAlreadyExistException User getUser(int userID) throws UserAccountIsBlockedException void updateUser(User user) void deleteUser(int userID) void unblockUser(int userID) ArrayList getAllUsers() Define InMemoryUserService class that shall implement UserService interface. In InMemoryUserService class, use User ArrayList for storage of user objects.  The methods shall change/read this list to perform the required operations. Test all methods from UserTest class. Getting information from user input is encourged but optional. Define and properly encapsulate atleast id, name and status instance attributes in User class of type int , String and boolean . How you check whether a user account is blocked? If status attribute is false, it means user account is blocked. True represent the account is active. UserAlreadyExistException exception shall be thrown if use