你的位置:首页 > Java教程

[Java教程]Spring MVC篇二、使用JdbcTemplate进行数据库操作


上一篇只是一个简单的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.
<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   }

 




这样就实现了登录的简单流程。