使用spring jdbcTemplate完成Dao的增删改查

				
CREATE TABLE Person(
		ID int generated by default as identity (start with 1) not null,
		FIRSTNAME VARCHAR(20) NOT NULL,
		LASTNAME VARCHAR(20) NOT NULL,
		PRIMARY KEY (ID)
	);
		
			

   
package domainmodel;

public class Person {

private String firstName;
private String lastName;

public String getFirstName() {
return firstName;
}

public String getLastName() {
return lastName;
}

public void setFirstName(String firstName) {
this.firstName = firstName;
}

public void setLastName(String lastName) {
this.lastName = lastName;
}
}

package dao;

import java.util.List;

import javax.sql.DataSource;

import domainmodel.Person;

public interface IDao {

void setDataSource(DataSource ds);

void create(String firstName, String lastName);

List<Person> select(String firstname, String lastname);

List<Person> selectAll();

void deleteAll();

void delete(String firstName, String lastName);

}

				
package dao.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.ResultSetExtractor;

import domainmodel.Person;

public class PersonResultSetExtractor implements ResultSetExtractor {

	@Override
	public Object extractData(ResultSet rs) throws SQLException {
		Person person = new Person();
		person.setFirstName(rs.getString(1));
		person.setLastName(rs.getString(2));
		return person;
	}

}

			

 

 

				
package dao.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class PersonRowMapper implements RowMapper {

	@Override
	public Object mapRow(ResultSet rs, int line) throws SQLException {
		PersonResultSetExtractor extractor = new PersonResultSetExtractor();
		return extractor.extractData(rs);
	}

}

			

 

 

				
package dao;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import dao.mapper.PersonRowMapper;
import domainmodel.Person;

public class DerbyDao implements IDao {
	private DataSource dataSource;

	public void setDataSource(DataSource ds) {
		dataSource = ds;
	}

	public void create(String firstName, String lastName) {
		JdbcTemplate insert = new JdbcTemplate(dataSource);
		insert.update("INSERT INTO PERSON (FIRSTNAME, LASTNAME) VALUES(?,?)",
				new Object[] { firstName, lastName });
	}

	public List<Person> select(String firstname, String lastname) {
		JdbcTemplate select = new JdbcTemplate(dataSource);
		return select
				.query(
						"select  FIRSTNAME, LASTNAME from PERSON where FIRSTNAME = ? AND LASTNAME= ?",
						new Object[] { firstname, lastname },
						new PersonRowMapper());
	}

	public List<Person> selectAll() {
		JdbcTemplate select = new JdbcTemplate(dataSource);
		return select.query("select FIRSTNAME, LASTNAME from PERSON",
				new PersonRowMapper());
	}

	public void deleteAll() {
		JdbcTemplate delete = new JdbcTemplate(dataSource);
		delete.update("DELETE from PERSON");
	}

	public void delete(String firstName, String lastName) {
		JdbcTemplate delete = new JdbcTemplate(dataSource);
		delete.update("DELETE from PERSON where FIRSTNAME= ? AND LASTNAME = ?",
				new Object[] { firstName, lastName });
	}

}

				
package test;

import java.util.List;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

import dao.DerbyDao;
import domainmodel.Person;

public final class Main {
	private Main() {
	};

	public static void main(String[] args) {
		DerbyDao dao = new DerbyDao();
		// Initialize the datasource, could /should be done of Spring
		// configuration
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.apache.derby.jdbc.EmbeddedDriver");
		dataSource.setUrl("jdbc:derby:c:\\temp\\database\\test01;create=true");
		dataSource.setUsername("");
		dataSource.setPassword("");
		// Inject the datasource into the dao
		dao.setDataSource(dataSource);

		dao.create("Lars", "Vogel");
		dao.create("Jim", "Knopf");
		dao.create("Lars", "Man");
		dao.create("Spider", "Man");
		System.out.println("Now select and list all persons");
		List<Person> list = dao.selectAll();
		for (Person myPerson : list) {
			System.out.print(myPerson.getFirstName() + " ");
			System.out.println(myPerson.getLastName());
		}
		System.out
				.println("Now select and list all persons with have the firstname Lars and lastname Vogel");
		list = dao.select("Lars", "Vogel");
		for (Person myPerson : list) {
			System.out.print(myPerson.getFirstName() + " ");
			System.out.println(myPerson.getLastName());
		}

		// Clean-up
		dao.deleteAll();
	}
}