spring JDBC的应用
原文地址:https://www.iteye.com/blog/chen106106-1574911
1:首先在类路径下面配置访问数据的一些基本信息,包括连接数据库的地址,用户,密码
jdbc.properties
jdbc.main.server=localhost:3306/test
jdbc.main.user=root
jdbc.main.password=123456
2:在spring的配置文件中配置NamedParameterJdbcTemplate,并且要注入DataSource,因为NamedParameterJdbcTemplate需要引用它来访问数据库
applicatonContext.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" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd"> <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="placeholderPrefix" value="$${" /> <property name="locations"> <list> <value>/WEB-INF/jdbc.properties</value> </list> </property> </bean> <bean name="parentDataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource" abstract="true"> <property name="maximumConnectionCount" value="40" /> <property name="minimumConnectionCount" value="2" /> <property name="simultaneousBuildThrottle" value="40" /> <property name="prototypeCount" value="2" /> <property name="trace" value="true" /> <property name="verbose" value="false" /> </bean> <bean id="mainDataSource" parent="parentDataSource"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="driverUrl"> <value>jdbc:mysql://$${jdbc.main.server}?useUnicode=true&characterEncoding=gbk&user=$${jdbc.main.user}&password=$${jdbc.main.password}&zeroDateTimeBehavior=convertToNull</value> </property> <property name="user" value="$${jdbc.main.user}"/> <property name="password" value="$${jdbc.main.password}"/> <property name="alias" value="main"/> <property name="maximumConnectionCount" value="200" /> <property name="simultaneousBuildThrottle" value="20"/> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"> <constructor-arg ref="mainDataSource" /> </bean> <bean id="corporateEventDao" class="com.example.JdbcCorporateEventDao"> <property name="jdbcTemplate" ref="jdbcTemplate" /> </bean> <context:property-placeholder location="jdbc.properties"/> </beans>
3:配置需要持久化的对象实体JAVA bean
public class Actor { private Long id; private String firstName; private String lastName; private int age; //专业 private String specialty; public String getFirstName() { return this.firstName; } public String getLastName() { return this.lastName; } public Long getId() { return this.id; } // setters omitted... }
4:定义DAO对实体对象需要的操作
public interface CorporateEventDao{ public int countOfActors(Actor exampleActor); public long addActor(Actor exampleActor); public boolean updateActor(long userId); public Actor findActorById(long userId); public List<Actor> getAllUser(); }
5:实现DAO,并且将NamedParameterJdbcTemplate注入到DAO中。
public class CorporateEventDaoImpl implements CorporateEventDao { private NamedParameterJdbcTemplate jdbcTemplate; public void setAppJdbc(NamedParameterJdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } private ParameterizedBeanPropertyRowMapper<Actor> rowMapper=ParameterizedBeanPropertyRowMapper.newInstance(Actor.class); @Override public int countOfActors(Actor exampleActor) { String sql = "select count(*) from t_actor where first_name =fastName and last_name=:lastName"; SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor); return this.jdbcTemplate.queryForInt(sql, namedParameters); } @Override public long addActor(Actor exampleActor) { // TODO Auto-generated method stub String sql = "insert into t_actor(first_name,last_name,age,specialty) values(?,?,?,?)"; SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor); return this.jdbcTemplate.update(sql, namedParameters); } @Override public boolean updateActor(long userId) { // TODO Auto-generated method stub String sql = "update t_actor set id=?"; return this.jdbcTemplate.getJdbcOperations().update(sql,userId)>0; } @Override public Actor findActorById(long userId) { // TODO Auto-generated method stub String sql = "select first_name,last_name,age,specialty from t_actor where userId=?"; return this.jdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{userId}, rowMapper); } @Override public List<Actor> getAllUser() { // TODO Auto-generated method stub String sql = "select first_name,last_name,age,specialty from t_actor"; return this.jdbcTemplate.getJdbcOperations().queryForList(sql, null, rowMapper); } public Actor findActor(String specialty, int age) { String sql = "select id, first_name, last_name from T_ACTOR" + " where specialty = ? and age = ?"; // notice the wrapping up of the argumenta in an array return (Actor) jdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[] {specialty, age}, rowMapper); } }