Pre:Create schema:vicky_schema;
1.Create two tables;
CREATE TABLE CONTACT (
ID INT NOT NULL AUTO_INCREMENT, FIRST_NAME VARCHAR(60) NOT NULL
, LAST_NAME VARCHAR(40) NOT NULL
, BIRTH_DATE DATE
, UNIQUE UQ_CONTACT_1 (FIRST_NAME, LAST_NAME)
, PRIMARY KEY (ID)
);
CREATE TABLE CONTACT_TEL_DETAIL (
ID INT NOT NULL AUTO_INCREMENT
, CONTACT_ID INT NOT NULL
, TEL_TYPE VARCHAR(20) NOT NULL
, TEL_NUMBER VARCHAR(20) NOT NULL
, UNIQUE UQ_CONTACT_TEL_DETAIL_1 (CONTACT_ID, TEL_TYPE)
, PRIMARY KEY (ID)
, CONSTRAINT FK_CONTACT_TEL_DETAIL_1 FOREIGN KEY (CONTACT_ID)
REFERENCES CONTACT (ID)
);
2.Contact.java
package com.feifan.jdbcsample.contact.dao.contact;
import java.io.Serializable;
import java.sql.Date;
import java.util.List;
import org.apache.log4j.Logger;
public class Contact implements Serializable{
/**
* @param args
*/
private Long contactId;
private String firstName;
private String lastName;
private Date birthDate;
private List<ContactDetail> contactDetail;
public Long getContactId() {
return contactId;
}
public void setContactId(Long contactId) {
this.contactId = contactId;
}
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 Date getBirthDate() {
return birthDate;
}
public void setBirthDate(Date birthDate) {
this.birthDate = birthDate;
}
public List<ContactDetail> getContactDetail() {
return contactDetail;
}
public void setContactDetail(List<ContactDetail> contactDetail) {
this.contactDetail = contactDetail;
}
public String toString() {
return "Contact Id: " + contactId + " First name: " + firstName + " Last name: " +
lastName + " Birth date: " + birthDate;
}
}
3.ContactDetail.java
package com.feifan.jdbcsample.contact.dao.contact;
import java.io.Serializable;
public class ContactDetail implements Serializable {
private Long contactDetailId;
private Long contactId;
private String telephoneType;
private String telephoneNumber;
public Long getContactDetailId() {
return contactDetailId;
}
public void setContactDetailId(Long contactDetailId) {
this.contactDetailId = contactDetailId;
}
public Long getContactId() {
return contactId;
}
public void setContactId(Long contactId) {
this.contactId = contactId;
}
public String getTelephoneType() {
return telephoneType;
}
public void setTelephoneType(String telephoneType) {
this.telephoneType = telephoneType;
}
public String getTelephoneNumber() {
return telephoneNumber;
}
public void setTelephoneNumber(String telephoneNumber) {
this.telephoneNumber = telephoneNumber;
}
public String toString() {
return "Contact Detail Id: " + contactDetailId + "Contact Id: " + contactId +
"Telephone type: " + telephoneType + "Telephone number: " + telephoneNumber;
}
}
4.ContactDao.java
package com.feifan.jdbcsample.contact.dao.contact;
import java.util.List;
public interface ContactDao {
public List<Contact> findAllContact();
public List<Contact> findByFirstName(String firstName);
public void insert(Contact contact);
public void update(Contact contact);
public void delete(Contact contact);
}
5.ContactDaoImpl.java
package com.feifan.jdbcsample.contact.daoImpl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import com.feifan.jdbcsample.contact.dao.contact.Contact;
import com.feifan.jdbcsample.contact.dao.contact.ContactDao;
public class ContactDaoImpl implements ContactDao {
private static Logger logger = Logger.getLogger(ContactDaoImpl.class.getPackage().getName());
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException ex) {
logger.error("Can not find mysql jdbc driver,please install driver! " + ex.getMessage());
}
}
private Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://localhost:3306/vicky_schema",
"root","pass");
}
private void closeConnection(Connection connection) {
if (connection == null) return;
try {
connection.close();
}
catch(SQLException ex) {
logger.error("Can not close the connection! " + ex.getMessage() );
}
}
@Override
public List<Contact> findAllContact() {
// TODO Auto-generated method stub
List<Contact> contactList = new ArrayList<Contact>();
Connection connection = null;
String QUREY_SQL = "SELECT * FROM CONTACT";
try {
connection = getConnection();
PreparedStatement statement = connection.prepareStatement(QUREY_SQL);
ResultSet resultSet = statement.executeQuery();
while(resultSet.next()) {
Contact contact = new Contact();
contact.setContactId(resultSet.getLong("ID"));
contact.setFirstName(resultSet.getString("FIRST_NAME"));
contact.setLastName(resultSet.getString("LAST_NAME"));
contact.setBirthDate(resultSet.getDate("BIRTH_DATE"));
contactList.add(contact);
}
} catch (SQLException ex) {
logger.error("The error occurs when execute query method" + ex.getMessage());
}
finally {
closeConnection(connection);
}
return contactList;
}
@Override
public List<Contact> findByFirstName(String firstName) {
// TODO Auto-generated method stub
return null;
}
@Override
public void insert(Contact contact) {
// TODO Auto-generated method stub
}
@Override
public void update(Contact contact) {
// TODO Auto-generated method stub
}
@Override
public void delete(Contact contact) {
// TODO Auto-generated method stub
}
}
6.ContactDaoSample1.java
package com.feifan.jdbcsample.contact.main;
import java.util.ArrayList;
import java.util.List;
import com.feifan.jdbcsample.contact.dao.contact.Contact;
import com.feifan.jdbcsample.contact.daoImpl.ContactDaoImpl;
import org.apache.log4j.Logger;
public class ContactDaoSample1 {
/**
* @param args
*/
private static Logger logger = Logger.getLogger(ContactDaoSample1.class.getPackage().getName());
public static void main(String[] args) {
// TODO Auto-generated method stub
List<Contact> res = new ArrayList<Contact>();
ContactDaoImpl contactList = new ContactDaoImpl();
res = contactList.findAllContact();
for(Contact value : res) {
logger.debug(value);
}
}
}