1、简单粗暴，直接在类中创建连接池使用
1 package com.xiaostudy;
2
3 import org.apache.commons.dbcp.BasicDataSource;
4 import org.springframework.jdbc.core.JdbcTemplate;
5
6 /**
7 * @desc 测试类
8 *
9 * @author xiaostudy
10 *
11 */
12 public class Test {
13
14 public static void main(String[] args) {
15 //创建连接池
16 BasicDataSource dataSource = new BasicDataSource();
17 dataSource.setDriverClassName("com.mysql.jdbc.Driver");
18 dataSource.setUrl("jdbc:mysql://localhost:3306/user");
19 dataSource.setUsername("root");
20 dataSource.setPassword("123456");
21 //创建模板
22 /*JdbcTemplate jdbcTemplate = new JdbcTemplate();
23 jdbcTemplate.setDataSource(dataSource);*/
24 JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
25
26 //添加
27 jdbcTemplate.update("insert into spring_user(name, password) values(?, ?);", "xiaostudy", "123456");
28 //修改
29 jdbcTemplate.update("update spring_user set name=?,password=? where id=?;", "xiaostudy", "123456", "2");
30 //删除
31 jdbcTemplate.update("delete from spring_user where name=? and password=?;", "xiaostudy", "123456");
32
33 }
34
35 }
2、较第一种，就是把业务分开
2.1、domain类User.java
1 package com.xiaostudy;
2
3 /**
4 * @desc domain类
5 * @author xiaostudy
6 *
7 */
8 public class User {
9
10 private Integer id;
11 private String name;
12 private String password;
13
14 public Integer getId() {
15 return id;
16 }
17
18 public void setId(Integer id) {
19 this.id = id;
20 }
21
22 public String getName() {
23 return name;
24 }
25
26 public void setName(String name) {
27 this.name = name;
28 }
29
30 public String getPassword() {
31 return password;
32 }
33
34 public void setPassword(String password) {
35 this.password = password;
36 }
37
38 @Override
39 public String toString() {
40 return "User [id=" + id + ", name=" + name + ", password=" + password + "]";
41 }
42
43 }
2.2、dao类UserDao.java
1 package com.xiaostudy;
2
3 import org.apache.commons.dbcp.BasicDataSource;
4 import org.springframework.jdbc.core.JdbcTemplate;
5
6
7 /**
8 * @desc Dao类
9 * @author xiaostudy
10 *
11 */
12 public class UserDao {
13
14 /**
15 * @desc 获取模板的方法
16 * @return JdbcTemplate 返回类型
17 */
18 public JdbcTemplate getJdbcTemplate() {
19 // 创建连接池
20 BasicDataSource dataSource = new BasicDataSource();
21 dataSource.setDriverClassName("com.mysql.jdbc.Driver");
22 dataSource.setUrl("jdbc:mysql://localhost:3306/user");
23 dataSource.setUsername("root");
24 dataSource.setPassword("123456");
25
26 // 创建模板
27 JdbcTemplate jdbcTemplate = new JdbcTemplate();
28 jdbcTemplate.setDataSource(dataSource);
29 return jdbcTemplate;
30 }
31
32 /**
33 * @desc 添加用户
34 * @param user 参数
35 * @return int 返回类型
36 */
37 public int insertUser(User user) {
38 JdbcTemplate jdbcTemplate = getJdbcTemplate();
39 return jdbcTemplate.update("insert into spring_user(name, password) values(?, ?);", user.getName(),
40 user.getPassword());
41 }
42
43 /**
44 * @desc 修改用户
45 * @param user 参数
46 * @param id 参数
47 * @return int 返回类型
48 */
49 public int updateUser(User user, int id) {
50 JdbcTemplate jdbcTemplate = getJdbcTemplate();
51 return jdbcTemplate.update("update spring_user set name=?,password=? where id=?;", user.getName(),
52 user.getPassword(), id);
53 }
54
55 /**
56 * @desc 删除用户
57 * @param user 参数
58 * @return int 返回类型
59 */
60 public int deleteUser(User user) {
61 JdbcTemplate jdbcTemplate = getJdbcTemplate();
62 return jdbcTemplate.update("delete from spring_user where name=? and password=?;", user.getName(),
63 user.getPassword());
64 }
65 }
2.3、测试类Test.java
1 package com.xiaostudy;
2
3 /**
4 * @desc 测试类
5 * @author xiaostudy
6 *
7 */
8 public class Test {
9
10 public static void main(String[] args) {
11 User user = new User();
12 user.setName("xiaostudy");
13 user.setPassword("123456");
14 UserDao userDao = new UserDao();
15 // userDao.insertUser(user);
16 // userDao.updateUser(user, 1);
17 userDao.deleteUser(user);
18
19 }
20
21 }
3、较第二种，接入spring中
3.1、domain类User.java
1 package com.xiaostudy;
2
3 /**
4 * @desc damain类
5 * @author xiaostudy
6 *
7 */
8 public class User {
9
10 private Integer id;
11 private String name;
12 private String password;
13
14 public Integer getId() {
15 return id;
16 }
17
18 public void setId(Integer id) {
19 this.id = id;
20 }
21
22 public String getName() {
23 return name;
24 }
25
26 public void setName(String name) {
27 this.name = name;
28 }
29
30 public String getPassword() {
31 return password;
32 }
33
34 public void setPassword(String password) {
35 this.password = password;
36 }
37
38 @Override
39 public String toString() {
40 return "User [id=" + id + ", name=" + name + ", password=" + password + "]";
41 }
42
43 }
3.2、dao类UserDao.java
1 package com.xiaostudy;
2
3 import org.springframework.context.ApplicationContext;
4 import org.springframework.context.support.ClassPathXmlApplicationContext;
5 import org.springframework.jdbc.core.JdbcTemplate;
6
7 /**
8 * @desc dao类
9 * @author xiaostudy
10 *
11 */
12 public class UserDao {
13
14 /**
15 * @desc 获取模板的方法
16 * @return JdbcTemplate 返回类型
17 */
18 public JdbcTemplate getJdbcTemplate() {
19 //从spring容器中获取连接池对象
20 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
21 return applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
22 }
23
24 /**
25 * @desc 添加用户
26 * @param user 参数
27 * @return int 返回类型
28 */
29 public int insertUser(User user) {
30 JdbcTemplate jdbcTemplate = getJdbcTemplate();
31 return jdbcTemplate.update("insert into spring_user(name, password) values(?, ?);", user.getName(), user.getPassword());
32 }
33
34 /**
35 * @desc 修改用户
36 * @param user 参数
37 * @param id 参数
38 * @return int 返回类型
39 */
40 public int updateUser(User user, int id) {
41 JdbcTemplate jdbcTemplate = getJdbcTemplate();
42 return jdbcTemplate.update("update spring_user set name=?,password=? where id=?;", user.getName(), user.getPassword(), id);
43 }
44
45 /**
46 * @desc 删除用户
47 * @param user 参数
48 * @return int 返回类型
49 */
50 public int deleteUser(User user) {
51 JdbcTemplate jdbcTemplate = getJdbcTemplate();
52 return jdbcTemplate.update("delete from spring_user where name=? and password=?;", user.getName(), user.getPassword());
53 }
54 }
3.3、spring配置文件applicationContext.xml
1 <?xml version="1.0" encoding="UTF-8"?>
2 <beans xmlns="http://www.springframework.org/schema/beans"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 xmlns:aop="http://www.springframework.org/schema/aop"
5 xmlns:context="http://www.springframework.org/schema/context"
6 xsi:schemaLocation="http://www.springframework.org/schema/beans
7 http://www.springframework.org/schema/beans/spring-beans.xsd
8 http://www.springframework.org/schema/aop
9 http://www.springframework.org/schema/aop/spring-aop.xsd
10 http://www.springframework.org/schema/context
11 http://www.springframework.org/schema/context/spring-context.xsd">
12 <!-- 将domain类添加到容器中 -->
13 <bean id="user" class="com.xiaostudy.User"></bean>
14 <!-- 将dao类添加到容器中 -->
15 <bean id="userDao" class="com.xiaostudy.UserDao"></bean>
16 <!-- 将连接池添加到容器中 -->
17 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
18 <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
19 <property name="url" value="jdbc:mysql://localhost:3306/user"></property>
20 <property name="username" value="root"></property>
21 <property name="password" value="123456"></property>
22 </bean>
23 <!-- 将模板添加到容器中 -->
24 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
25 <property name="dataSource" ref="dataSource"></property>
26 </bean>
27 </beans>
3.4、测试类Test.java
1 package com.xiaostudy;
2
3 import org.springframework.context.ApplicationContext;
4 import org.springframework.context.support.ClassPathXmlApplicationContext;
5
6 /**
7 * @desc 测试类
8 * @author xiaostudy
9 *
10 */
11 public class Test {
12
13 public static void main(String[] args) {
14 //获取spring容器
15 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
16 //从容器中获取domain对象
17 User user = applicationContext.getBean("user", User.class);
18 user.setName("huangwu");
19 user.setPassword("123456");
20 //从容器中获取dao对象
21 UserDao userDao = applicationContext.getBean("userDao", UserDao.class);
22 //userDao.insertUser(user);
23 userDao.updateUser(user, 2);
24 //userDao.deleteUser(user);
25
26 }
27
28 }
4、较第三种，把在spring配置文件中的连接池信息提取到一个配置文件中
4.1、domain类User.java
1 package com.xiaostudy;
2
3 /**
4 * @domain类
5 * @author xiaostudy
6 *
7 */
8 public class User {
9
10 private Integer id;
11 private String name;
12 private String password;
13
14 public Integer getId() {
15 return id;
16 }
17
18 public void setId(Integer id) {
19 this.id = id;
20 }
21
22 public String getName() {
23 return name;
24 }
25
26 public void setName(String name) {
27 this.name = name;
28 }
29
30 public String getPassword() {
31 return password;
32 }
33
34 public void setPassword(String password) {
35 this.password = password;
36 }
37
38 @Override
39 public String toString() {
40 return "User [id=" + id + ", name=" + name + ", password=" + password + "]";
41 }
42
43 }
4.2、dao类UserDao.java
1 package com.xiaostudy;
2
3 import org.springframework.context.ApplicationContext;
4 import org.springframework.context.support.ClassPathXmlApplicationContext;
5 import org.springframework.jdbc.core.JdbcTemplate;
6
7 /**
8 * @desc dao类
9 * @author xiaostudy
10 *
11 */
12 public class UserDao {
13
14 /**
15 * @desc 获取模板的方法
16 * @return JdbcTemplate 返回类型
17 */
18 public JdbcTemplate getJdbcTemplate() {
19 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
20 return applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
21 }
22
23 /**
24 * @desc 添加用户
25 * @param user 参数
26 * @return int 返回类型
27 */
28 public int insertUser(User user) {
29 JdbcTemplate jdbcTemplate = getJdbcTemplate();
30 return jdbcTemplate.update("insert into spring_user(name, password) values(?, ?);", user.getName(), user.getPassword());
31 }
32
33 /**
34 * @desc 修改用户
35 * @param user 参数
36 * @param id 参数
37 * @return int 返回类型
38 */
39 public int updateUser(User user, int id) {
40 JdbcTemplate jdbcTemplate = getJdbcTemplate();
41 return jdbcTemplate.update("update spring_user set name=?,password=? where id=?;", user.getName(), user.getPassword(), id);
42 }
43
44 /**
45 * @desc 删除用户
46 * @param user 参数
47 * @return int 返回类型
48 */
49 public int deleteUser(User user) {
50 JdbcTemplate jdbcTemplate = getJdbcTemplate();
51 return jdbcTemplate.update("delete from spring_user where name=? and password=?;", user.getName(), user.getPassword());
52 }
53 }
4.3、连接池的配置文件dataSource.properties
1 jdbc.driverClassName=com.mysql.jdbc.Driver
2 jdbc.url=jdbc:mysql://localhost:3306/user
3 jdbc.username=root
4 jdbc.password=123456
4.4、spring配置文件applicationContext.xml
1 <?xml version="1.0" encoding="UTF-8"?>
2 <beans xmlns="http://www.springframework.org/schema/beans"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 xmlns:aop="http://www.springframework.org/schema/aop"
5 xmlns:context="http://www.springframework.org/schema/context"
6 xsi:schemaLocation="http://www.springframework.org/schema/beans
7 http://www.springframework.org/schema/beans/spring-beans.xsd
8 http://www.springframework.org/schema/aop
9 http://www.springframework.org/schema/aop/spring-aop.xsd
10 http://www.springframework.org/schema/context
11 http://www.springframework.org/schema/context/spring-context.xsd">
12 <!-- 将domain类添加到容器中 -->
13 <bean id="user" class="com.xiaostudy.User"></bean>
14 <!-- 将dao类添加到容器中 -->
15 <bean id="userDao" class="com.xiaostudy.UserDao"></bean>
16 <!-- 将连接池的配置文件添加到容器中 -->
17 <context:property-placeholder location="classpath:dataSource.properties"/>
18 <!-- 将连接池添加到容器中 -->
19 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
20 <property name="driverClassName" value="${jdbc.driverClassName}"></property>
21 <property name="url" value="${jdbc.url}"></property>
22 <property name="username" value="${jdbc.username}"></property>
23 <property name="password" value="${jdbc.password}"></property>
24 </bean>
25 <!-- 将模板添加到容器中 -->
26 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
27 <property name="dataSource" ref="dataSource"></property>
28 </bean>
29 </beans>
4.5、测试类Test.java
1 package com.xiaostudy;
2
3 import org.springframework.context.ApplicationContext;
4 import org.springframework.context.support.ClassPathXmlApplicationContext;
5
6 /**
7 * @desc 测试类
8 *
9 * @author xiaostudy
10 *
11 */
12 public class Test {
13
14 public static void main(String[] args) {
15 //获取spring容器
16 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
17 //从容器中获取domain对象
18 User user = applicationContext.getBean("user", User.class);
19 user.setName("lisi");
20 user.setPassword("123456");
21 //从容器中获取dao对象
22 UserDao userDao = applicationContext.getBean("userDao", UserDao.class);
23 //userDao.insertUser(user);
24 userDao.updateUser(user, 2);
25 //userDao.deleteUser(user);
26
27 }
28
29 }
5、较DBCP，用C3P0连接池，需要改变的就是连接池的包要改变，和连接池配置的名称要改
6、较第五种，把模板添加到容器改成让dao去添加模板
6.1、domain类User.java
1 package com.xiaostudy;
2
3 /**
4 * @desc domain类
5 * @author xiaostudy
6 *
7 */
8 public class User {
9
10 private Integer id;
11 private String name;
12 private String password;
13
14 public Integer getId() {
15 return id;
16 }
17
18 public void setId(Integer id) {
19 this.id = id;
20 }
21
22 public String getName() {
23 return name;
24 }
25
26 public void setName(String name) {
27 this.name = name;
28 }
29
30 public String getPassword() {
31 return password;
32 }
33
34 public void setPassword(String password) {
35 this.password = password;
36 }
37
38 @Override
39 public String toString() {
40 return "User [id=" + id + ", name=" + name + ", password=" + password + "]";
41 }
42
43 }
6.2、dao类UserDao.java
1 package com.xiaostudy;
2
3 import org.springframework.jdbc.core.JdbcTemplate;
4 import org.springframework.jdbc.core.support.JdbcDaoSupport;
5
6 /**
7 * @desc dao类
8 * @author xiaostudy
9 *
10 */
11 public class UserDao extends JdbcDaoSupport {
12
13 /**
14 * @desc 添加用户
15 * @param user 参数
16 * @return int 返回类型
17 */
18 public int insertUser(User user) {
19 //从继承的父类中获取模板
20 JdbcTemplate jdbcTemplate = getJdbcTemplate();
21 return jdbcTemplate.update("insert into spring_user(name, password) values(?, ?);", user.getName(), user.getPassword());
22 }
23
24 /**
25 * @desc 修改用户
26 * @param user 参数
27 * @param id 参数
28 * @return int 返回类型
29 */
30 public int updateUser(User user, int id) {
31 JdbcTemplate jdbcTemplate = getJdbcTemplate();
32 return jdbcTemplate.update("update spring_user set name=?,password=? where id=?;", user.getName(), user.getPassword(), id);
33 }
34
35 /**
36 * @desc 删除用户
37 * @param user 参数
38 * @return int 返回类型
39 */
40 public int deleteUser(User user) {
41 JdbcTemplate jdbcTemplate = getJdbcTemplate();
42 return jdbcTemplate.update("delete from spring_user where name=? and password=?;", user.getName(), user.getPassword());
43 }
44 }
6.3、连接池的配置文件dataSource.properties
1 jdbc.driverClassName=com.mysql.jdbc.Driver
2 jdbc.url=jdbc:mysql://localhost:3306/user
3 jdbc.username=root
4 jdbc.password=123456
6.4、spring配置文件applicationContext.xml
1 <?xml version="1.0" encoding="UTF-8"?>
2 <beans xmlns="http://www.springframework.org/schema/beans"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 xmlns:aop="http://www.springframework.org/schema/aop"
5 xmlns:context="http://www.springframework.org/schema/context"
6 xsi:schemaLocation="http://www.springframework.org/schema/beans
7 http://www.springframework.org/schema/beans/spring-beans.xsd
8 http://www.springframework.org/schema/aop
9 http://www.springframework.org/schema/aop/spring-aop.xsd
10 http://www.springframework.org/schema/context
11 http://www.springframework.org/schema/context/spring-context.xsd">
12 <!-- 将domain类添加到容器中 -->
13 <bean id="user" class="com.xiaostudy.User"></bean>
14 <!-- 将dao类添加到容器中 -->
15 <bean id="userDao" class="com.xiaostudy.UserDao">
16 <!-- 将连接池给dao，让dao去注入 -->
17 <property name="dataSource" ref="dataSource"></property>
18 </bean>
19 <!-- 将连接池的配置文件添加到容器中 -->
20 <context:property-placeholder location="classpath:dataSource.properties"/>
21 <!-- 将连接池添加到容器中 -->
22 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
23 <property name="driverClass" value="${jdbc.driverClass}"></property>
24 <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
25 <property name="user" value="${jdbc.user}"></property>
26 <property name="password" value="${jdbc.password}"></property>
27 </bean>
28 </beans>
6.5测试类Test.java
1 package com.xiaostudy;
2
3 import org.springframework.context.ApplicationContext;
4 import org.springframework.context.support.ClassPathXmlApplicationContext;
5
6 /**
7 * @desc 测试类
8 * @author xiaostudy
9 *
10 */
11 public class Test {
12
13 public static void main(String[] args) {
14 //获取spring容器
15 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
16 //从容器中获取domain对象
17 User user = applicationContext.getBean("user", User.class);
18 user.setName("sssss");
19 user.setPassword("123456");
20 //从容器中获取dao对象
21 UserDao userDao = applicationContext.getBean("userDao", UserDao.class);
22 //userDao.insertUser(user);
23 userDao.updateUser(user, 2);
24 //userDao.deleteUser(user);
25
26 }
27
28 }
