Simple Example, I Need Help!
I'm working on a very simple application using Eclipse and MySQL here it is:
http://img.photobucket.com/albums/v335/shlumph/table.jpg
It's pretty self-explanitory. You enter a person's first and last name, press submit, and their name flops onto the table. I made this naively, and do not know how to make use of reflection and beans, if that's even what I need.
I tried googling on how to make use of reflection and beans when dealing with database connectivity, but there's just so much different kinds of information, it's like trying to find a needle in a haystack.
If someone could help point me in a direction to go, post some in depth tutorials, or even point out a good book that I can get on amazon, that would be excellent!
Just incase if you care what I have done naively, here is the database structure and code:
I have a database named addressbook with a table called names. Inside of names there are the following columns:
first_name varchar(20)
last_name varchar(20)
publicclass exampleextends Composite{
private Label firstNameLbl =null;
private Text firstNameTxt =null;
private Label lastNameLbl =null;
private Text lastNameTxt =null;
private Composite tableComposite =null;
private Button submitButton =null;
private Table nameTable =null;
private Connection con =null;
public example(Composite parent,int style){
super(parent, style);
initialize();
}//example()
privatevoid initialize(){
GridData gridData1 =new GridData();
gridData1.widthHint = 75;
GridLayout gridLayout =new GridLayout();
gridLayout.numColumns = 2;
firstNameLbl =new Label(this, SWT.NONE);
firstNameLbl.setText("First Name");
firstNameTxt =new Text(this, SWT.BORDER);
lastNameLbl =new Label(this, SWT.NONE);
lastNameLbl.setText("Last Name");
lastNameTxt =new Text(this, SWT.BORDER);
this.setLayout(gridLayout);
Label filler1 =new Label(this, SWT.NONE);
submitButton =new Button(this, SWT.NONE);
submitButton.setText("Submit");
submitButton.setLayoutData(gridData1);
submitButton
.addSelectionListener(new org.eclipse.swt.events.SelectionAdapter(){
publicvoid widgetSelected(org.eclipse.swt.events.SelectionEvent e){
getConnection();
try{
String firstName = firstNameTxt.getText();
String lastName = lastNameTxt.getText();
String sql ="INSERT names VALUES ('" + firstName +"', '" + lastName +"')";
Statement stmt = con.createStatement();
stmt.executeUpdate(sql);
//Update the table
nameTable.removeAll();
addItems();
//Close connections
con.close();
stmt.close();
}catch (SQLException e2){
e2.printStackTrace();
}
}//widgetSelected()
});
createTableComposite();
this.setSize(new Point(241, 220));
}//initialize()
/**
* This method initializes tableComposite
*
*/
privatevoid createTableComposite(){
GridData gridData2 =new GridData();
gridData2.widthHint = 200;
gridData2.heightHint = 100;
GridData gridData =new GridData();
gridData.horizontalSpan = 2;
tableComposite =new Composite(this, SWT.NONE);
tableComposite.setLayout(new GridLayout());
tableComposite.setLayoutData(gridData);
nameTable =new Table(tableComposite, SWT.BORDER);
nameTable.setHeaderVisible(true);
nameTable.setLayoutData(gridData2);
nameTable.setLinesVisible(true);
TableColumn firstNameCol =new TableColumn(nameTable, SWT.LEFT);
firstNameCol.setText("First Name");
firstNameCol.setWidth(100);
TableColumn lastNameCol =new TableColumn(nameTable, SWT.LEFT);
lastNameCol.setText("Last Name");
lastNameCol.setWidth(100);
getConnection();
addItems();
}//createTableComposite()
/**
* This gets the MySQL Connection
*/
publicvoid getConnection(){
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql:///addressbook","root","password");
}catch(Exception e){
System.err.println("Exception: " + e.getMessage());
}
}//getConnection()
/**
* This adds items to the address table
*/
privatevoid addItems(){
try{
String sql ="SELECT first_name, last_name FROM names";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
String first_name = rs.getString(1);
String last_name = rs.getString(2);
String[] row =new String[]{first_name, last_name};
TableItem item1 =new TableItem(nameTable, 0);
item1.setText(row);
}
}catch (SQLException e){
e.printStackTrace();
}
}//addItems()
}
Message was edited by:
shlumph
[8560 byte] By [
shlumpha] at [2007-11-27 10:36:13]
# 1
It's pretty much a big mess. Hasn't anybody told you about decomposition or layering? You've got UI and database stuff all mingled into one class. Ever thought about breaking the problem up into smaller pieces?
I don't see why you need reflection.
What does Eclipse have to do with your application? That's just an IDE.
Two columns in your table? No primary key? What's up with that? Bad design.
Start with a Person class: firstName, lastName, and id would be its private data members.
Then write a PersonDao that does CRUD operations with the database:
package persistence;
public interface PersonDao
{
Person find(Long id);
List<Person> find();
public void save(Person p);
public void update(Person p);
public delete(Person p);
}
Write an implementation of that interface and get it working perfectly. Then worry about the UI stuff.
%
# 2
> It's pretty much a big mess. Hasn't anybody told you
> about decomposition or layering?
Welcome back duffymo. :D
# 3
"It's pretty much a big mess. Hasn't anybody told you about decomposition or layering? You've got UI and database stuff all mingled into one class. Ever thought about breaking the problem up into smaller pieces?"
That's where you come in =)
Ok, so I made a Person class, with the three fields, and also updated my MySQL table for the primary key id.
I have some questions...
What is the package persistence for? What is supposed to be in that package?
Could you give an example of the implementation of one of the methods in the PersonDao interface?
# 4
> "It's pretty much a big mess. Hasn't anybody told you
> about decomposition or layering? You've got UI and
> database stuff all mingled into one class. Ever
> thought about breaking the problem up into smaller
> pieces?"
>
> That's where you come in =)
Consider it done.
> Ok, so I made a Person class, with the three fields,
> and also updated my MySQL table for the primary key
> id.
Better. What type did you make the id column? (Hint: Should be an identity or auto increment column. I can't remember which MySQL supports.)
> I have some questions...
>
> What is the package persistence for? What is
> supposed to be in that package?
All the interfaces and classes that pertain to persistence. Packages are a good way to partition a problem, too. Ever wonder why the Java API uses them?
> Could you give an example of the implementation of
> one of the methods in the PersonDao interface?
Yes, but that's where you come in. Try writing one and we'll see how you do.
%
# 5
Make sure you manage your resources properly - in a finally block. This utility class will help:
package persistence;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DatabaseUtils
{
public void close(Connection c)
{
try
{
if (c != null)
{
c.close();
}
}
catch (SQLException e)
{
//log.error(e); // Log with log4j or Commons logging
}
}
public void close(Statement s)
{
try
{
if (s != null)
{
s.close();
}
}
catch (SQLException e)
{
//log.error(e); // Log with log4j or Commons logging
}
}
public void close(ResultSet rs)
{
try
{
if (rs != null)
{
rs.close();
}
}
catch (SQLException e)
{
//log.error(e); // Log with log4j or Commons logging
}
}
public void rollback(Connection c)
{
try
{
if (c != null)
{
c.rollback();
}
}
catch (SQLException e)
{
//log.error(e); // Log with log4j or Commons logging
}
}
}
%
# 6
Yes, the id column is auto-increment and not null. I kind of went on a rampage without reading your latter post and added some more stuff.
I added in other columns: street, city, state, phone and email to my database. As well as fields/setters/getters to my Person class.
I think I went on a different, but similar route then what you mentioned with the database utility. I tried making a MySQL generator interface/implementation along with a PersonDao interface/implementation, like you mentioned.
Can you give me some more suggestions, I'm sure this is done noobly/poorly.
Here is my PersonDao interface:
import java.util.*;
public interface PersonDao {
public Person find(Long id);
public List<Person> find();
public List<Person> search(String fieldName, String search);
public void save(Person p);
public void update(Person p);
public void delete(Person p);
}
To give you an example, here is my implementation for methods delete, and find:
public void delete(Person p) {
mysql.delete("names", "id", p.getID().toString());
}
public Person find(Long id) {
Person p = new Person();
ResultSet rs = mysql.find("names", "id", id.toString());
try {
while (rs.next()) {
String firstName = rs.getString(1);
String lastName = rs.getString(2);
String street = rs.getString(4);
String city = rs.getString(5);
String state = rs.getString(6);
String zipCode = rs.getString(7);
String phone = rs.getString(8);
String email = rs.getString(9);
p.setID(id);
p.setFirstName(firstName);
p.setLastName(lastName);
p.setStreet(street);
p.setCity(city);
p.setState(state);
p.setZipCode(zipCode);
p.setPhone(phone);
p.setEmail(email);;
}
} catch (SQLException e) {
e.printStackTrace();
}
mysql.closeConnection();
return p;
}// find()
Here is my MySqlGenerator interface:
public interface MySqlGenerator {
public ResultSet selectAll(String table);
public ResultSet find(String table, String pkColumn, String pkValue);
public ResultSet search(String table, String column, String search);
public List<Object> select(String table, String column);
public void update(String table, String column, String pkColumn, String pkValue, String newValue);
public void save(String table, String column, String values);
public void delete(String table, String pkColumn, String pkValue);
public void closeConnection();
}
And to give you an example, here is the implementation for delete and find:
public void delete(String table, String pkColumn, String pkValue) {
getConnection();
try {
Statement stmt = con.createStatement();
String sql = "DELETE FROM " +table+ " WHERE " +pkColumn+ "=" + pkValue;
stmt.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeConnection();
}
}
public ResultSet find(String table, String pkColumn, String pkValue) {
getConnection();
ResultSet rs = null;
try {
Statement stmt = con.createStatement();
String sql = "SELECT * FROM" +table+ " WHERE " +pkColumn+ "=" + pkValue;
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
So theoretically, if this was done well (which I'm sure it's not), I could put PersonDao, PersonDaoImpl, MySqlGenerator, MySqlGeneratorImpl, all into a package named persistence?
Thanks for your help thus far, I really want to learn how to do this the right way.
# 7
> Yes, the id column is auto-increment and not null. I
> kind of went on a rampage without reading your latter
> post and added some more stuff.
So tell me why I bothered?
> I added in other columns: street, city, state, phone
> and email to my database. As well as
> fields/setters/getters to my Person class.
What does adding more columns do when you can't even insert what you had? Seems foolish. Do the easy thing first, then expand.
> I think I went on a different, but similar route then
> what you mentioned with the database utility.
Mine's the right way. 8)
> I
> tried making a MySQL generator
> interface/implementation along with a PersonDao
> interface/implementation, like you mentioned.
>
> Can you give me some more suggestions, I'm sure this
> is done noobly/poorly.
I like that find idiom for method names. Why did you copy the interface I gave you and add "search"? The idea would be to add find methods that have different parameters. In your case, I'd have a find that takes two String arguments.
It's not just being picky. Consistency matters. If you published this, a user would be likely to think "Why two find methods and a search? Did two people write this?" Pick one and stick to it.
It's great, except for that search method. After all, it's what I gave you.
>
> To give you an example, here is my implementation for
> methods delete, and find:
> > public void delete(Person p) {
> mysql.delete("names", "id", p.getID().toString());
> }
>
> public Person find(Long id) {
>
> Person p = new Person();
> ResultSet rs = mysql.find("names", "id",
> ", id.toString());
> try {
> while (rs.next()) {
> String firstName = rs.getString(1);
> String lastName = rs.getString(2);
> String street = rs.getString(4);
> String city = rs.getString(5);
> String state = rs.getString(6);
> String zipCode = rs.getString(7);
> String phone = rs.getString(8);
> String email = rs.getString(9);
>
> p.setID(id);
> p.setFirstName(firstName);
> p.setLastName(lastName);
> p.setStreet(street);
> p.setCity(city);
> p.setState(state);
> p.setZipCode(zipCode);
> p.setPhone(phone);
> p.setEmail(email);;
> }
> } catch (SQLException e) {
> e.printStackTrace();
> }
> mysql.closeConnection();
> return p;
> }// find()
>
>
> Here is my MySqlGenerator interface:
> > public interface MySqlGenerator {
>
> public ResultSet selectAll(String table);
>
> public ResultSet find(String table, String pkColumn,
> , String pkValue);
>
> public ResultSet search(String table, String column,
> , String search);
>
> public List<Object> select(String table, String
> g column);
>
> public void update(String table, String column,
> , String pkColumn, String pkValue, String newValue);
>
> public void save(String table, String column, String
> g values);
>
> public void delete(String table, String pkColumn,
> , String pkValue);
>
> public void closeConnection();
>
> }
>
>
> And to give you an example, here is the
> implementation for delete and find:
> > public void delete(String table, String pkColumn,
> , String pkValue) {
> getConnection();
> try {
> Statement stmt = con.createStatement();
> String sql = "DELETE FROM " +table+ " WHERE "
> E " +pkColumn+ "=" + pkValue;
>
> stmt.executeUpdate(sql);
>
> } catch (SQLException e) {
> e.printStackTrace();
> } finally {
> closeConnection();
> }
> }
>
> public ResultSet find(String table, String pkColumn,
> , String pkValue) {
> getConnection();
> ResultSet rs = null;
> try {
> Statement stmt = con.createStatement();
>
> String sql = "SELECT * FROM" +table+ " WHERE "
> E " +pkColumn+ "=" + pkValue;
> rs = stmt.executeQuery(sql);
>
> } catch (SQLException e) {
> e.printStackTrace();
> }
> return rs;
> }
>
>
> So theoretically, if this was done well (which I'm
> sure it's not), I could put PersonDao, PersonDaoImpl,
> MySqlGenerator, MySqlGeneratorImpl, all into a
> package named persistence?
I don't see what that MySqlGenerator is all about.
No, I wouldn't recommend doing it this way. Put it all the in the PersonDaoImpl. Better yet, create a subpackage "jdbc" under persistence and put the PersonDaoImpl in there. JDBC is one way to implement this interface. There are lots of others. But this is what you should do to start.
NEVER return a ResultSet from a method that way. Load the contents into an object or data structure and close it inside the method that created it. You're leaking a database cursor that way, AND you're exposing SQL stuff to clients. Keep all the persistence artifacts inside the persistence package. Don't let them leak out that way.
Nope, sorry, I don't think this is it.
%
# 8
Ok, I made another effort. I got rid of the MySqlGenerator, and added in the DatabaseUtil you posted... hopefully I'm using it correctly.
This is what my PersonDao interface looks like now:
public interface PersonDao {
public List<Person> find();
public List<Person> search(String fieldName, String search);
public void save(Person p);
public void update(Person p);
public void delete(Person p);
}
Here is an example, methods search and delete:
public List<Person> search(String column, String search) {
List<Person> people = new ArrayList<Person>();
getConnection();
ResultSet rs = null;
try {
Statement stmt = con.createStatement();
String sql = "SELECT * FROM names WHERE " + column + " LIKE '%" + search + "%'";
rs = stmt.executeQuery(sql);
while (rs.next()) {
Person p = new Person();
Long id = rs.getLong(3);
String firstName = rs.getString(1);
String lastName = rs.getString(2);
String street = rs.getString(4);
String city = rs.getString(5);
String state = rs.getString(6);
String zipCode = rs.getString(7);
String phone = rs.getString(8);
String email = rs.getString(9);
p.setID(id);
p.setFirstName(firstName);
p.setLastName(lastName);
p.setStreet(street);
p.setCity(city);
p.setState(state);
p.setZipCode(zipCode);
p.setPhone(phone);
p.setEmail(email);
people.add(p);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
dbUtil.close(stmt);
dbUtil.close(rs);
dbUtil.close(con);
}
return people;
}
public void delete(Person p) {
getConnection();
try {
Statement stmt = con.createStatement();
String sql = "DELETE FROM names WHERE id=" + p.getId();
stmt.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
dbUtil.close(stmt);
dbUtil.close(con);
}
}
Is this starting to look better now, duffymo? Is there anything else I can improve? Thanks again!
# 9
Better, but still not there. If it's working for you, go for it.
My preference would be to use Spring, but that's beyond you right now.
Does this code even compile? Have you run this?
I ask because you declare Statement inside a try block and close it in a finally - after it's out of scope. Doesn't the compiler complain?
Use PreparedStatement and bind variables. It's safer - can't have SQL injection attacks that way.
%
# 10
>Does this code even compile? Have you run this?
Sure does. I'm moving the Statement declarations outside of the try blocks, thanks for pointing that out. It wasn't complaining becuase I had a Statement declaration way up top for some reason.
I'll switch over to using PreparedStatement anyways, SQL injection was going to be one of my next questions. Thanks for pointing that out.
>My preference would be to use Spring, but that's beyond you right now.
Well, I get the impression that you are one hell of a programmer, so your preference is what I want to learn. I have no idea what Spring is, but that will be what I'll research next.
Thanks duff
Message was edited by:
shlumph
# 11
> >Does this code even compile? Have you run this?
> Sure does. I'm moving the Statement declarations
> outside of the try blocks, thanks for pointing that
> out. It wasn't complaining becuase I had a Statement
> declaration way up top for some reason.
Best to have Statement and ResultSet in the narrowest scope possible. Create and close them in method scope.
> I'll switch over to using PreparedStatement anyways,
> SQL injection was going to be one of my next
> questions. Thanks for pointing that out.
>
> >My preference would be to use Spring, but that's
> beyond you right now.
It's up to you. Might be a good thing to look at if you think you're ready.
Writing these DAOs will become mighty easy with Spring.
I'd recommend "Pro Spring" by Rob Harrop. Great young guy - very smart.
> Well, I get the impression that you are one hell of a programmer,
No, far from it. Not the best here, not the best I know. Just a guy with opinions, that's all.
> so your preference is what I want to
> learn. I have no idea what Spring is, but that will
> be what I'll research next.
Good idea. You'll find it at http://www.springframework.org
> Thanks duff
You're very welcome. Good luck. Come back if you have Spring questions.
%
PS - I'll try to get through a straight JDBC example on my own. If I do, I'll post it.
