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

}