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     }