Spring MVC 学习笔记2 数据库之JDBC

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

<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>

<context:property-placeholder location="jdbc.properties"/>

<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  <property name="dataSource" ref="dataSource"/>
</bean>

<context:component-scan base-package="io.github.josephpei.DAO" />
<context:component-scan base-package="io.github.josephpei.Service" />

<!--
1.
<jee:jndi-lookup id="dataSource" jndi-name="jdbc/jpetstore"/>
<bean id="txManager" class="org.springframework.transaction.jta.JtaTransactionManager" />

2.
#this is the service object that we want to make transactional
<bean id="fooService" class="x.y.service.DefaultFooService"/>

#the transactional semantics
<tx:advice id="txAdvice" transaction-manager="txManager">
  <tx:attributes>
    <tx:method name="get*" read-only="true"/>
    <tx:method name="*"/>
  </tx:attributes>
</tx:advice>

#ensure that the above transactional advice runs for any execution of an operation defined by the FooService
<aop:config>
  <aop:pointcut id="fooServiceOperation" expression="execution(* x.y.service.FooService.*(..))"/>
  <aop:advisor advice-ref="txAdvice" pointcut-ref="fooServiceOperation"/>
</aop:config>

3.
#enable the configuration of transactional behavior based on annotations
<tx:annotation-driven transaction-manager="txManager"/>
-->

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

public class User implements Serializable{
    private static final long serialVersionUID = 1L;

    private int userId;
    private String userName;
    private String password;
    private String lastIp;
    private Date lastVisit;

    public User() {

    }
    // getter and setter...
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

@Repository
public class JdbcUserDao implements UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Integer getMatchCount(String userName, String password) {
        String sql = "SELECT count(*) FROM user_tbl WHERE user_name=? and password=?";
        return jdbcTemplate.queryForObject(sql, Integer.class, userName, password);
    }

    public User findUserByName(final String userName) {
        String sql = "select user_id, user_name from user_tbl where user_name=?";
        return jdbcTemplate.queryForObject(sql, new Object[] {userName},
                new RowMapper<User>() {
                    @Override
                    public User mapRow(ResultSet resultSet, int i) throws SQLException {
                        User user = new User();
                        user.setUserId(resultSet.getInt("user_id"));
                        user.setUserName(resultSet.getString("user_name"));
                        return user;
                    }
                });

    }

    public void updateLoginInfo(User user) {
        String sql = "update user_tbl set last_visit=?, last_ip=? where user_id=?";
        jdbcTemplate.update(sql, user.getLastVisit(), user.getLastIp());
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

@Service
public class UserService {

    @Autowired
    private JdbcUserDao jdbcUserDao;

    public boolean hasMatchUser(String userName, String password) {
        Integer count = jdbcUserDao.getMatchCount(userName, password);
        return count > 0;
    }

    public User findUserByName(String userName) {
        return jdbcUserDao.findUserByName(userName);
    }

    public void loginSuccess(User user) {
        jdbcUserDao.updateLoginInfo(user);
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"file:src/main/webapp/WEB-INF/spitter-servlet.xml"})
public class TestUserService {
    @Autowired
    private UserService userService;

    @Test
    public void hasMatchUser() {
        boolean b1 = userService.hasMatchUser("admin", "123456");
        boolean b2 = userService.hasMatchUser("admin", "1111");
        Assert.assertTrue(b1);
        Assert.assertFalse(b2);
    }

    @Test
    public void findUserByName() {
        User user = userService.findUserByName("admin");
        Assert.assertEquals(user.getUserName(), "admin");
    }
}