Here is a good tutorial for getting started with SQLite. http://www.tutorialspoi
ID: 3756449 • Letter: H
Question
Here is a good tutorial for getting started with SQLite.
http://www.tutorialspoint.com/sqlite/sqlite_java.htm (Links to an external site.)Links to an external site.
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.
1. 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. )
2. Write a method called findAllPeople that returns an ArrayList of objects containing all the people in the database. Demonstrate that it is working correctly.
3. 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?
Explanation / Answer
Please find the Java code below"-
1. JDBC_MySQL.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
public class JDBC_MySQL {
private ArrayList<Person> personList = new ArrayList<Person>();
private Person personObj;
public ArrayList<Person> findAllPeople() {
try {
personList.clear();
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:808/gopal", "gmalaker", "password");
// here gopal is database name, gmalaker is username and password is
// "password"
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from PERSON");
while (rs.next()) {
personList
.add(new Person(rs.getInt("PERSON_ID"), rs.getString("FIRST_NAME"), rs.getString("LAST_NAME")));
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
return personList;
}
public void deletePerson(String firstName, String lastName) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:808/gopal", "gmalaker", "password");
String deleteQuery = "DELETE FROM PERSON WHERE FIRST_NAME = ? AND LAST_NAME = ?";
PreparedStatement prest = con.prepareStatement(deleteQuery);
prest.setString(1, firstName);
prest.setString(1, lastName);
int deleteResult = prest.executeUpdate(); // no of rows impacted
if (deleteResult == 0) {
System.out.println("No such record found!!!");
}
con.commit();
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
public Person selectPerson(int personID) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:808/gopal", "gmalaker", "password");
String selectQuery = "SELECT FIRST_NAME, LAST_NAME FROM PERSON WHERE PERSON_ID = ?";
PreparedStatement prest = con.prepareStatement(selectQuery);
prest.setInt(1, personID);
ResultSet rs = prest.executeQuery();
if (rs == null){
System.out.println("No such record Found!!!");
}else{
while(rs.next()){
personObj = new Person(personID, rs.getString("FIRST_NAME"), rs.getString("LAST_NAME"));
}
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
return personObj;
}
}
class Person {
private int personID;
private String firstName;
private String lastName;
public Person(int personID, String firstName, String lastName) {
super();
this.personID = personID;
this.firstName = firstName;
this.lastName = lastName;
}
public int getPersonID() {
return personID;
}
public void setPersonID(int personID) {
this.personID = personID;
}
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;
}
}
Please let me know in case of any clarifications required. Thanks!
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.