This is the official tutorial from Oracle. http://docs.oracle.com/javase/tutoria
ID: 3935685 • Letter: T
Question
This is the official tutorial from Oracle.
http://docs.oracle.com/javase/tutorial/jdbc/
Here is a good tutorial for getting started with SQLite.
http://www.tutorialspoint.com/sqlite/sqlite_java.htm
Chapter 34 in the Liang text. He uses MySQL. Getting started with SQLite might be a little easier, but he does a good job of defining the issues in not too many pages.
For this assignment you can use SQLite OR MySQL.
There are numerous videos in YouTube that demonstrate how to do this. Some are better than others. When you find one that is helpful, post a link to it on the discussion board.
We have been working with the front-end (GUI), and the middle (creating and manipulating collections of objects), and now we will add on the back end. The persistent storage of data in your applications. This exercise is to get you comfortable with connecting to a DB, adding, deleting, retrieving data. I encourage you to play with this one, do more than the minimum.
SQLite is a very small database. It is included by default in Android and iOS. It is surprisingly powerful for such a small footprint. It can be frustrating to see what’s going on – what is in the DB, did the query work correctly? MySQL is often called a community database. It belongs to Oracle, but they allow anyone to use it for free. The recent versions of the MySQL workbench that allows you to see what’s going on in your database are really very nice – starting to look like the Access front end.
Create a connection to a relational database using SQLite or MySQL.
Create a single database table to hold information.
Let’s make a simple class called Person for this exercise.
Person
firstName (String)
lastName(String)
age (int)
ssn (long)
creditCard (long)
Note that once you have the DB created, you don’t want to do this again every time you run your test program. The easiest way to deal with this – for this assignment, is to comment out the code that creates the DB creation and the table creation while you experiment with the following.
(Aside: I choose ssn and credit card as fields here so that you might think about the persistent storage of sensitive data. There are some pretty strict laws governing the storage of some data. Please don’t use any actual social security numbers or credit card numbers in this exercise.)
Demonstrate the insertion of a record into the database Insert several records.
Write a method called insertPerson(Person person) that adds a person object to your database. Create another object of type Person, and demonstrate calling your method, passing the object to the method.
Demonstrate the retrieval of information from the database. Use SQL Select statements, to retrieve a particular Person from the database.
Write a method called selectPerson that returns a Person object. This method retrieves the data for a Person from the database. We also need to pass a parameter to identify what person. You can use ‘name’ if you like, or if you find it easier to use the database generated ID that’s fine too.
This method returns the object that represents that person. This will require that you extract the data that is returned from the database, and call the Person constructor. (Later you will understand that that this is the data-exchange between the relational database and the business layer. )
Write a method called findAllPeople that returns an ArrayList<Person> of objects containing all the people in the database. Demonstrate that it is working correctly.
Write a method called deletePerson that removes a person from the database. The parameters will be first name and last name. Print out on the console the data from the record that is being deleted. Use your findAllPeople method to verify that that person has been removed from the database. Consider what this method should return. Suppose the person is not found, should the method return that information somehow?
Deliverables.
Include all of your code. And the output from the console that verifies that it’s working.
Add something to the discussion board that might be helpful to your classmates.
======================================================================
Addendum:
One of the students in a previous semester wrote this description of getting started with SQLite… It’s sort of help with using the tutorial that helps with using SQLite.
SQLite is just a database for storing data (like MySQL, Microsoft SQL Server, Access, etc.) The way SQLite works is by creating a ".db" file in a directory that you specify, and that is where all of the database records created in your program are stored. (There is no requirement to use the .db extension, it’s a convention.)
The benefit to using a database like SQLite is that the memory is non-volatile, so any records you add to the database are physically written to your hard-drive, and will remain there after the program execution completes... unlike storing data in RAM (when you use collections, hashmaps, etc), which is cleared every time the program runs. Another huge benefit, which is outside the scope of this assignment, would be that multiple users can access the same database simultaneously.
But the exercise here is to be able to create a database, add some tables for storing data, then establish a connection to the database, and be able to insert data into it, retrieve data from it, modify existing records in it, or delete records in it. And the tutorial that Dr. A linked in the assignment covers how to do every single one of those tasks:
http://www.tutorialspoint.com/sqlite/sqlite_java.htm
As for how to set it up, I created the following directory on my PC:
C:JavaSQLite JDBC
Then downloaded the file named "sqlite-jdbc4-3.8.2-SNAPSHOT.jar" from the link in the tutorial:
https://bitbucket.org/xerial/sqlite-jdbc/downloads
And saved that file to the directory I created above. Then it's just a matter of setting up Eclipse to include that file in your project's Build Path.
To do so:
Right click on your Java Project > Properties > Java Build Path > Libraries > Add External JARs... then browse to that file.
Once you've done that, then you should be able to copy and paste the methods from the Tutorial link above, and modify them as needed for your specific program. The methods for connection to the database, and manipulating the data should reside in the "datalayer" package of your project.
Hopefully that helps!
Explanation / Answer
import java.sql.*;
import java.util.ArrayList;
public class Main {
public static void main(String[] args) {
Person person = new Person("Barrack","Obama", 12, 5136112,12345);
Person person1 = new Person("Name1", "LastName6", 13, 5136113,12346);
Person person2 = new Person("Name2", "LastName7", 14, 5136114,12347);
Person person3 = new Person("Name3", "LastName8", 15, 5136115,12348);
Person person4 = new Person("Name4", "LastName9", 16, 5136116,12349);
Person person5 = new Person("Name5", "LastName10", 17, 5136117,12350);
createTable();
insertPerson(person);
insertPerson(person1);
insertPerson(person2);
insertPerson(person3);
insertPerson(person4);
insertPerson(person5);
System.out.println(selectPerson(4));
for(Person p: findAllPeople()){
System.out.println(p);
}
deletePerson(4);
for(Person p: findAllPeople()){
System.out.println(p);
}
}
public static Connection deletePerson(int id){
Connection connection = null;
Statement stmt = null;
try {
connection = getConnection();
connection.setAutoCommit(false);
stmt = connection.createStatement();
String sql = "DELETE from MARK where ID=" + id + ";";
stmt.executeUpdate(sql);
connection.commit();
connection.close();
System.out.println("(Deleted Person "+ id + ") done successfully");
} catch ( Exception e ) {
System.out.println(e);
connection = null;
}
return connection;
}
public static ArrayList<Person> findAllPeople(){
Connection connection = null;
Statement statement = null;
ArrayList<Person> person = new ArrayList<>();
try {
connection = getConnection();
connection.setAutoCommit(false);
statement = connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM MARK;");
while ( rs.next() ) {
person.add(new Person(rs.getString("firstname"), rs.getString("lastname"), rs.getInt("age"), rs.getInt("creditcard"),rs.getInt("ssn")));
}
rs.close();
statement.close();
connection.close();
System.out.println("(Find All People) done successfully");
} catch ( Exception e ) {
System.out.println(e);
person = null;
}
return person;
}
public static Person selectPerson(int id){
Connection connection = null;
Statement statement = null;
Person person = new Person();
try {
connection = getConnection();
connection.setAutoCommit(false);
statement = connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM MARK where ID=" + id + ";");
while ( rs.next() ) {
person.setFirstName(rs.getString("firstname"));
person.setLastName(rs.getString("lastname"));
person.setAge(rs.getInt("age"));
person.setCreditCard(rs.getInt("creditcard"));
person.setSsn(rs.getInt("ssn"));
}
rs.close();
statement.close();
connection.close();
System.out.println("(Select Person " + id + ") done successfully");
} catch ( Exception e ) {
System.out.println(e);
person = null;
}
return person;
}
public static Connection insertPerson(Person person){
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
connection.setAutoCommit(false);
statement = connection.createStatement();
String sql = "INSERT INTO MARK (FIRSTNAME,LASTNAME,AGE,CREDITCARD,SSN) " +
"VALUES ("+
"'" + person.getFirstName() + "'," +
"'" + person.getLastName() + "'," +
person.getAge() + "," +
person.getCreditCard() + "," +
person.getSsn() + " );";
statement.executeUpdate(sql);
statement.close();
connection.commit();
connection.close();
System.out.println("(Insert Person " + person.getFirstName() + " " + person.getLastName() + ") done successfully");
} catch ( Exception e ) {
System.out.println(e);
connection = null;
}
return connection;
}
public static Connection createTable(){
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
statement = connection.createStatement();
String sql = "CREATE TABLE MARK " +
"(ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
"FIRSTNAME CHAR(30) NOT NULL, " +
"LASTNAME CHAR(30) NOT NULL, " +
"AGE INT , " +
"CREDITCARD BIGINT , " +
"SSN BIGINT ) ";
statement.executeUpdate(sql);
statement.close();
connection.close();
System.out.println("Table created successfully");
}catch (Exception e){
System.out.println(e);
connection = null;
}
return connection;
}
public static Connection getConnection(){
Connection connection = null;
try{
Class.forName("org.sqlite.JDBC");
connection = DriverManager.getConnection("jdbc:sqlite:MyDatabase.db");
System.out.println("Opened database successfully");
}catch (Exception e){
System.out.println(e);
connection = null;
}
return connection;
}
}
Person.java
import java.math.BigInteger;
public class Person {
private long ssn;
private String firstName;
private String lastName;
private int age;
private long creditCard;
public Person(String firstName, String lastName, int age, long creditCard, long ssn) {
this.ssn = ssn;
this.firstName = firstName;
this.lastName = lastName;
this.age = age;
this.creditCard = creditCard;
}
public Person() {
}
public long getSsn() {
return ssn;
}
public void setSsn(long ssn) {
this.ssn = ssn;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public long getCreditCard() {
return creditCard;
}
public void setCreditCard(long creditCard) {
this.creditCard = creditCard;
}
//Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Person)) return false;
Person person = (Person) o;
return getSsn() == person.getSsn();
}
//Override
public int hashCode() {
return (int) (getSsn() ^ (getSsn() >>> 32));
}
//Override
public String toString() {
return "Person{" +
"firstName='" + firstName + ''' +
", lastName='" + lastName + ''' +
", age=" + age +
", creditCard=" + creditCard +
", ssn=" + ssn +
'}';
}
}
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.