jdbc编程中,需要手动获取Connection、Statement、PreparedStatement、ResultSet、SQLException,使用完毕后还需要手动释放连接。
spring框架对jdbc进行了封装,使用自己的封装的JdbcTemplate进行数据库操作。下面是个简单的例子
。
Person.java定义Person类,代码:
package com.yeetrack.springDAO;
import java.util.Date;
public class Person
{
private int id;
private String name;
private String sex;
private int age;
private Date birthday;
public int getId()
{
return id;
}
public void setId(int id)
{
this.id = id;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public String getSex()
{
return sex;
}
public void setSex(String sex)
{
this.sex = sex;
}
public int getAge()
{
return age;
}
public void setAge(int age)
{
this.age = age;
}
public Date getBirthday()
{
return birthday;
}
public void setBirthday(Date birthday)
{
this.birthday = birthday;
}
}
IPersonDAO.java DAO层接口,定义Person中的方法:
package com.yeetrack.springDAO;
import java.util.List;
/**
* person类操作接口
* @author youthflies
*
*/
public interface IPersonDAO
{
public String getPersonName(int id); //根据id获取姓名
public void addPerson(Person person); //添加person
public int getPersonCount(); //获取person数目
public List<Person> listPerson(); //返回所有person
}
PersonDaoImpl.java PersonDaoImpl类实现了IPersonDAO接口,而且继承了Spring DAO模块中的JdbcDaoSupport类。JdbcDaoSupport提供JdbcTemplate对象,封装了常用的jdbc操作,我们使用JdbcTemplate即可。
package com.yeetrack.springDAO;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
public class PersonDaoImpl extends JdbcDaoSupport implements IPersonDAO
{
/**
* 初始化方法,创建数据库表结构
*/
public void initDatabase()
{
String sql = "create table if not exists tb_person "
+ "(id int auto_increment, " + "name varchar(255), "
+ "sex varchar(10), age int , birthday timestamp, primary key(id) )";
getJdbcTemplate().execute(sql);
}
/**
* 根据id获取person名字
*/
public String getPersonName(int id)
{
// TODO Auto-generated method stub
String sql = "select name from tb_person where id = " + id;
return (String) getJdbcTemplate().queryForObject(sql, String.class);
}
/**
* 向数据库中添加记录
*/
public void addPerson(Person person)
{
// TODO Auto-generated method stub
String sql = "insert into tb_person (name, sex, age, birthday ) values (?, ?, ?, ?)";
getJdbcTemplate().update(sql, new Object[] { person.getName(), person.getSex(), person.getAge(), person.getBirthday()});
}
/**
* 获取记录总条数
*/
public int getPersonCount()
{
// TODO Auto-generated method stub
String sql = "select count(*) from tb_person";
return getJdbcTemplate().queryForInt(sql);
}
/**
* 返回所有的Person
* @return 返回Person List
*/
public List<Person> listPerson()
{
// TODO Auto-generated method stub
String sql = "select id, name, sex, age, birthday from tb_person";
@SuppressWarnings("unchecked")
List<Map<String, Object>> list =getJdbcTemplate().queryForList(sql);
List<Person> personList = new ArrayList<Person>();
for(Map<String, Object> row: list)
{
Person person = new Person();
person.setId((Integer)row.get("id"));
person.setName((String)row.get("name"));
person.setSex((String)row.get("sex"));
person.setAge((Integer)row.get("age"));
person.setBirthday((Date)row.get("birthday"));
personList.add(person);
}
return personList;
}
}
applicationContext.xml配置,我们需要配置一个数据源,并把该数据源设置到PersonDaoImpl中,PersonDaoImpl中有个方法initDataBase(),用来生成表结构,需要配置到applicationContext.xml中:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd"
default-autowire="byName">
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}"></property>
<property name="url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:jdbc.properties"></property>
</bean>
<bean id="personDao" class="com.yeetrack.springDAO.PersonDaoImpl" depends-on="dataSource" init-method="initDatabase">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
数据库配置文件,jdbc.properties
#配置数据驱动
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8
jdbc.username=root
jdbc.password=toor
下面是测试代码(需要有个test数据库),TestDAO.java
package com.yeetrack.springDAO;
import java.util.Date;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestDao
{
/**
* @param args
*/
public static void main(String[] args)
{
// TODO Auto-generated method stub
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
IPersonDAO personDao = (IPersonDAO) context.getBean("personDao");
Person person =new Person();
person.setName("youthflies");
person.setAge(22);
person.setSex("M");
person.setBirthday(new Date());
personDao.addPerson(person);
System.out.println(personDao.getPersonName(1));
System.out.println(personDao.getPersonCount());
System.out.println(personDao.listPerson());
}
}