015. Spring NamedParameterJdbcTemplate访问数据库

1、创建Java项目：File -> New -> Java Project

2、项目结构

3、必要jar包

4、创建数据库，使用MySQL数据库

SET NAMES GBK;CREATE DATABASE IF NOT EXISTS `db_spring`;USE `db_spring`;DROP TABLE IF EXISTS `tb_user`;CREATE TABLE `tb_user`(    `id` INTEGER PRIMARY KEY NOT NULL,    `name` VARCHAR(16) NOT NULL,    `pswd` VARCHAR(32) NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `tb_user`(`id`, `name`, `pswd`) VALUES(146, '吴腾辉', '105032014146'), (148, '吴钟灯', '105032014148');

5、创建UserModel实体类UserModel.java

package com.spring.model;public class UserModel {    private int id;    private String name;    private String pswd;    public UserModel() {        super();    }    public UserModel(int id, String name, String pswd) {        super();        this.id = id;        this.name = name;        this.pswd = pswd;    }    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 getPswd() {        return pswd;    }    public void setPswd(String pswd) {        this.pswd = pswd;    }    @Override    public String toString() {        return "UserModel [id=" + id + ", name=" + name + ", pswd=" + pswd + "]";    }}

6、创建UserDao接口UserDao.java

package com.spring.dao;import java.util.List;import com.spring.model.UserModel;public interface UserDao {    public UserModel getUser(int id);    public List<UserModel> getUsers();    public int insertUser(UserModel user);    public int updateUser(UserModel user);    public int deleteUser(int id);}

7、创建UserDaoImp实现类UserDaoImp.java

package com.spring.dao.imp;import java.sql.ResultSet;import java.sql.SQLException;import java.util.LinkedList;import java.util.List;import org.springframework.jdbc.core.RowCallbackHandler;import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;import com.spring.dao.UserDao;import com.spring.model.UserModel;public class UserDaoImp implements UserDao{    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;    public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;    }    @Override    public UserModel getUser(int id) {        String sql = "select id, name, pswd from tb_user where id=:id";        MapSqlParameterSource msps = new MapSqlParameterSource();        msps.addValue("id", id);        UserModel user = new UserModel();        namedParameterJdbcTemplate.query(sql, msps, new RowCallbackHandler(){            @Override            public void processRow(ResultSet rs) throws SQLException {                user.setId(rs.getInt("id"));                user.setName(rs.getString("name"));                user.setPswd(rs.getString("pswd"));            }        });        return user;    }    @Override    public List<UserModel> getUsers() {        List<UserModel> userList = new LinkedList<UserModel>();        String sql = "select id, name, pswd from tb_user";        namedParameterJdbcTemplate.query(sql, new RowCallbackHandler(){            UserModel user = null;            @Override            public void processRow(ResultSet rs) throws SQLException {                user = new UserModel();                user.setId(rs.getInt("id"));                user.setName(rs.getString("name"));                user.setPswd(rs.getString("pswd"));                userList.add(user);            }        });        return userList;    }    @Override    public int insertUser(UserModel user) {        String sql = "insert into tb_user(id, name, pswd) values(:id, :name, :pswd)";        MapSqlParameterSource msps = new MapSqlParameterSource();        msps.addValue("id", user.getId());        msps.addValue("name", user.getName());        msps.addValue("pswd", user.getPswd());        return namedParameterJdbcTemplate.update(sql, msps);    }    @Override    public int updateUser(UserModel user) {        String sql = "update tb_user set name=:name, pswd=:pswd where id=:id";        MapSqlParameterSource msps = new MapSqlParameterSource();        msps.addValue("name", user.getName());        msps.addValue("pswd", user.getPswd());        msps.addValue("id", user.getId());        return namedParameterJdbcTemplate.update(sql, msps);    }    @Override    public int deleteUser(int id) {        String sql = "delete from tb_user where id=:id";        MapSqlParameterSource msps = new MapSqlParameterSource();        msps.addValue("id", id);        return namedParameterJdbcTemplate.update(sql, msps);    }}

8、创建UserService接口UserService.java

package com.spring.service;import java.util.List;import com.spring.model.UserModel;public interface UserService {    public UserModel getUser(int id);    public List<UserModel> getUsers();    public int insertUser(UserModel user);    public int updateUser(UserModel user);    public int deleteUser(int id);}

9、创建UserServiceImp实现类UserServiceImp.java

package com.spring.service.imp;import java.util.List;import com.spring.dao.UserDao;import com.spring.model.UserModel;import com.spring.service.UserService;public class UserServiceImp implements UserService {    private UserDao userDao;    public void setUserDao(UserDao userDao) {        this.userDao = userDao;    }    @Override    public UserModel getUser(int id) {        return userDao.getUser(id);    }    @Override    public List<UserModel> getUsers() {        return userDao.getUsers();    }    @Override    public int insertUser(UserModel user) {        return userDao.insertUser(user);    }    @Override    public int updateUser(UserModel user) {        return userDao.updateUser(user);    }    @Override    public int deleteUser(int id) {        return userDao.deleteUser(id);    }}

10、创建数据库配置文件jdbc.properties

jdbc.driverClassName=com.mysql.jdbc.Driverjdbc.url=jdbc:mysql://localhost:3306/db_springjdbc.username=rootjdbc.password=

11、创建spring配置文件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:context="http://www.springframework.org/schema/context"       xsi:schemaLocation="http://www.springframework.org/schema/beans         http://www.springframework.org/schema/beans/spring-beans.xsd        http://www.springframework.org/schema/context        http://www.springframework.org/schema/context/spring-context.xsd">    <context:property-placeholder location="jdbc.properties"/>    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">        <property name="driverClassName" value="${jdbc.driverClassName}"/>        <property name="url" value="${jdbc.url}"/>        <property name="username" value="${jdbc.username}"/>        <property name="password" value="${jdbc.password}"/>    </bean>    <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">        <constructor-arg ref="dataSource"></constructor-arg>    </bean>    <bean id="userDao" class="com.spring.dao.imp.UserDaoImp">        <property name="namedParameterJdbcTemplate" ref="namedParameterJdbcTemplate"></property>    </bean>    <bean id="userService" class="com.spring.service.imp.UserServiceImp">        <property name="userDao" ref="userDao"></property>    </bean></beans>

12、创建Spring测试类SpringUnit.java

package com.spring.junit;import java.util.List;import org.junit.After;import org.junit.Before;import org.junit.Test;import org.springframework.context.support.ClassPathXmlApplicationContext;import com.spring.model.UserModel;import com.spring.service.UserService;public class SpringUnit {    private ClassPathXmlApplicationContext ctx = null;    private UserService userService = null;    @Before    public void setUp() throws Exception {        ctx = new ClassPathXmlApplicationContext("applicationContext.xml");        userService = (UserService) ctx.getBean("userService");    }    @After    public void tearDown() throws Exception {        ctx.close();    }    @Test    public void test() {        testInsert();        testUpdate();        testDelete();        testGetUsers();    }    private void testInsert() {        userService.insertUser(new UserModel(147, "吴志祥", "105032014140"));        System.out.println(userService.getUser(147).toString());    }    private void testUpdate() {        userService.updateUser(new UserModel(147, "吴志祥", "105032014147"));        System.out.println(userService.getUser(147).toString());    }    private void testDelete() {        userService.deleteUser(147);        System.out.println(userService.getUser(147).toString());    }    private void testGetUsers() {        List<UserModel> userList = userService.getUsers();        for(UserModel user: userList) {            System.out.println(user.toString());        }    }}

13、测试结果

... 省略Spring日志信息 ...UserModel [id=147, name=吴志祥, pswd=105032014140]UserModel [id=147, name=吴志祥, pswd=105032014147]UserModel [id=0, name=null, pswd=null]UserModel [id=146, name=吴腾辉, pswd=105032014146]UserModel [id=148, name=吴钟灯, pswd=105032014148]... 省略Spring日志信息 ...