Spring JDBC Support with jdbc configuration in code directly

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 stubList<Contact> res = new ArrayList<Contact>();ContactDaoImpl contactList = new ContactDaoImpl();res = contactList.findAllContact();for(Contact value : res) {logger.debug(value);}}}