SpringMVC篇二、使用JdbcTemplate进行数据库操作

2019-11-14 15:11:21

字体：大中小

来源：转载

供稿：网友

上一篇只是一个简单的SPRing MVC框架，接下来添加一些跟数据库的交互。

一、添加jdbc相关配置

在maven中添加相关依赖后，配置数据库访问参数及数据源。数据库参数使用配置文件，代码如下：

jdbc.properties

 1 jdbc.driver=com.MySQL.jdbc.Driver 2 jdbc.url=jdbc:mysql://localhost:3306/sampledb 3 jdbc.username=root 4 jdbc.passWord=123456 5 jdbc.initialSize=5 6 jdbc.maxActive=10 7 jdbc.minIdle=5 8 jdbc.maxIdle=10 9 jdbc.timeBetweenEvictionRunsMillis=360000010 jdbc.minEvictableIdleTimeMillis=360000011 jdbc.testOnBorrow=true12 jdbc.validationQuery=SELECT 1 FROM DUAL

在app-servlet.xml中添加数据源配置和jdbcTemplate配置：

<bean id="jdbcConfig" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">    <property name="location">        <value>classpath:jdbc.properties</value>    </property></bean><bean id="databasesource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">    <property name="driverClassName" value="${jdbc.driver}"/>    <property name="url" value="${jdbc.url}"/>    <property name="username" value="${jdbc.username}"/>    <property name="password" value="${jdbc.password}"/>    <property name="maxActive" value="${jdbc.maxActive}"/>    <property name="initialSize" value="${jdbc.initialSize}"/>    <property name="maxIdle" value="${jdbc.maxIdle}"/>    <property name="minIdle" value="${jdbc.minIdle}"/>    <property name="timeBetweenEvictionRunsMillis"     value="${jdbc.timeBetweenEvictionRunsMillis}"/>    <property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}"/>    <property name="testOnBorrow" value="${jdbc.testOnBorrow}"/>    <property name="validationQuery" value="${jdbc.validationQuery}"/></bean><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">    <property name="dataSource" ref="databasesource"/></bean>

二、实现一个使用用户名为super，密码为123456的用户登录过程

2.1 添加User实体类

User.java代码如下：

 1 public class User { 2     private Long id; 3     private String username; 4     private String email; 5     private String password; 6     private Long credit; 7     private Date lastVisitTime; 8     private String lastVisitip; 9     //setter、getter10 }

2.2 实现对数据库的访问操作

新建UserDao.java，在UserDao类上添加@Repository注解

首先在该类中装配JdbcTemplate，使用@Autowired注解

@Autowiredprivate JdbcTemplate jdbcTemplate;

然后在里面添加一个根据用户名和密码查询用户的方法：

 1 public User findUser(final String username, final String password) { 2         String sql = "select id,user_name, credit from t_user_info where user_name = ? and password = ?"; 3         User user = null; 4         try { 5             RowMapper<User> rm = ParameterizedBeanPropertyRowMapper.newInstance(User.class); 6             user = (User) jdbcTemplate.queryForObject(sql, new Object[]{username, password}, rm); 7         }catch (Exception e){ 8             e.printStackTrace(); 9         }10         return user;11     }

2.3 添加UserService.java类，代码如下：

 1 @Service("userService") 2 public class UserService { 3     @Autowired 4     UserDao userDao; 5     public User findUser(String username,String password){ 6         return userDao.findUser(username,password); 7     } 8  9     public int findUserCount(String username,String password){10         return userDao.findUserCount(username,password);11     }12 13     public boolean insertUser(User user){14         return userDao.insertUser(user);15     }16 }

2.4 修改UserController.java的userIndex方法：

 1 @RequestMapping(value = "/index.html", method = RequestMethod.POST) 2     public ModelAndView userIndex(String username, String password) { 3         if (StringUtil.isEmpty(username) || StringUtil.isEmpty(password)) { 4             logger.error("用户名或密码为空"); 5             ModelAndView modelAndView = new ModelAndView("/index"); 6             modelAndView.addObject("error", "用户名或密码为空！"); 7             return modelAndView; 8         } 9 10 //        int count = userService.findUserCount(username,password);11         User user = userService.findUser(username, password);12         if (user == null) {13             logger.info("用户名或密码错误");14             ModelAndView modelAndView = new ModelAndView("/index");15             modelAndView.addObject("username", username);16             modelAndView.addObject("error", "用户：" + username + "不存在或用户密码错误！");17             return modelAndView;18         }19         ModelAndView mav = new ModelAndView("success");20         mav.addObject("username", username);21         mav.addObject("password", password);22         logger.info("username : " + username + ", password : " + password);23         return mav;24     }