Top

Spring 结合 JDBC、C3P0 进行 MySQL 数据库操作



首先导入必要的包

pom.xml
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
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>

<!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>

<!-- https://mvnrepository.com/artifact/commons-pool/commons-pool -->
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.6</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>

Bean类如下

User.java
1
2
3
4
5
6
7
8
9
public class User {
private String id;
private String username;
private String password;
private int level;
private String avatar;

//省略构造、get/setter、tostring()..
}

数据库字段及内容如下

id username password level avatar
6 CrazyKid3 96e79218965eb72c92a549dd5a330112 0 6.jpg
8 crazykid33 96e79218965eb72c92a549dd5a330112 1 8.jpg
9 test 96e79218965eb72c92a549dd5a330112 0 9.jpg
10 test123 e10adc3949ba59abbe56e057f20f883e 0 10.jpg

Spring 的配置文件如下

dataaccess_config.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?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">

<!-- 声明 user 和 userdao -->
<bean id="user" class="net.crazykid.bean.jdbc.bean.User"/>
<bean id="userDao" class="net.crazykid.bean.jdbc.dao.UserDao">
<property name="dataSource" ref="dataSource"/>
</bean>

<!--声明一个数据源-->
<!--引入配置文件-->
<context:property-placeholder location="classpath:db.properties"/>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driverClassName}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>

</beans>

db.properties 文件省略..

Dao类如下

UserDao.java
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
44
45
46
47
48
49
50
51
public class UserDao extends JdbcDaoSupport {

//查询所有数据
public List selectAll() {
String sql = "select * from user";
List users = getJdbcTemplate().queryForList(sql);
return users;
}

//查询一张表总的记录数
public int selectCount() {
String sql = "select count(*) from user";
int rowCount = getJdbcTemplate().queryForObject(sql ,Integer.class);
return rowCount;
}

//查询时绑定参数
public int selectCountById(int id) {
String sql = "select count(*) from user where id=?";
int rowCount = getJdbcTemplate().queryForObject(sql, Integer.class, id);
return rowCount;
}

//查询一个字符串
public String selectUsernameById(int id) {
String sql = "select username from user where id=?";
String username = getJdbcTemplate().queryForObject(sql, String.class, id);
return username;
}

//查询数据并且将数据封装成一个自定义类型对象
public User selectUserById(int id) {
String sql = "select * from user where id=?";
User user = getJdbcTemplate().queryForObject(sql, new UserRowMapper(), id);
return user;
}

//查询数据并且将数据封装成一个自定义类型对象,将这些对象保存到一个List集合中
public List<User> selectAllUser() {
String sql = "select * from user";
List<User> list = getJdbcTemplate().query(sql, new UserRowMapper());
return list;
}

//插入数据
public void InsertUser(User user) {
String sql = "insert into user (id, username, password, level, avatar) values (?, ?, ?, ?, ?)";
getJdbcTemplate().update(sql, user.getId(),user.getUsername(),user.getPassword(),user.getLevel(),user.getAvatar());
}
//删除、修改都是差不多的...就不写了
}

笔记:在 Spring 配置文件里,声明该 Dao 的时候通过<property>标签注入了数据源 dataSource,也就是应该要通过类里的方法 setDataSource 注入数据源,而该方法就在继承的 JdbcDaoSupport 这个类里面:

1
2
3
4
5
6
public final void setDataSource(DataSource dataSource) {
if (this.jdbcTemplate == null || dataSource != this.jdbcTemplate.getDataSource()) {
this.jdbcTemplate = this.createJdbcTemplate(dataSource);
this.initTemplateConfig();
}
}

这个方法是 final 的,因此不能也无需重写到 Dao 类里,它会把传进去的 dataSource 转换为 JdbcTemplate ,在操作数据库的时候,使用 getJdbcTemplate() 获得 jdbcTemplate 对象,然后执行相应的方法。

在将查询结果封装成一个自定义类型对象的时候,需要写一个 Mapper

UserRowMapper.java
1
2
3
4
5
6
7
8
9
10
11
12
13
//T代表返回的类型
public final class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int i) throws SQLException {
User user = new User();
user.setId(rs.getString("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setLevel(rs.getInt("level"));
user.setAvatar(rs.getString("avatar"));
return user;
}
}

测试类

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
44
45
46
47
48
49
50
51
public class testDataAccess {
ClassPathXmlApplicationContext context;
UserDao userDao;

@Before
public void init(){
context = new ClassPathXmlApplicationContext("dataaccess/dataaccess_config.xml");
userDao = (UserDao) context.getBean("userDao");
}

@Test
public void testSelectAll() {
userDao.selectAll();
}

@Test
public void testSelectCount() {
int count = userDao.selectCount();
System.out.println(count);
}

@Test
public void testSelectById() {
int count = userDao.selectCountById(8);
System.out.println(count);
}

@Test
public void testSelectUsernameById() {
String username = userDao.selectUsernameById(8);
System.out.println(username);
}

@Test
public void testSelectUserById() {
User user = userDao.selectUserById(8);
System.out.println(user);
}

@Test
public void testSelectAllUser() {
List<User> list = userDao.selectAllUser();
System.out.println(list);
}

@Test
public void testInsertUser() {
User user = new User("23333", "testinsert", "123", 0, null);
userDao.InsertUser(user);
}
}

通过注解方式注入dataSource

那么如何通过注解的方式注入 dataSource 呢?首先需要在 Spring 的配置文件里手动声明 jdbcTemplate

1
2
3
4
5
<!-- 声明 dataSource 略.. -->

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg ref="dataSource"/>
</bean>

在 userDao 里,可以把继承的 JdbcDaoSupport 类 去掉,然后自己去声明 JdbcTemplate 和 getter 方法

UserDao.java
1
2
3
4
5
6
7
8
9
10
11
@Component
public class UserDao{
@Autowired
private JdbcTemplate jdbcTemplate;

public JdbcTemplate getJdbcTemplate() {
return this.jdbcTemplate;
}

//各种方法...
}

 发表评论

文明评论,请勿灌水。为防止恶意评论,请登录账号后发表评论