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 }

springJDBC的几种方法

1、简单粗暴，直接在类中创建连接池使用

2、较第一种，就是把业务分开

2.1、domain类User.java

2.2、dao类UserDao.java

2.3、测试类Test.java

3、较第二种，接入spring中

3.1、domain类User.java

3.2、dao类UserDao.java

3.3、spring配置文件applicationContext.xml

3.4、测试类Test.java

4、较第三种，把在spring配置文件中的连接池信息提取到一个配置文件中

4.1、domain类User.java

4.2、dao类UserDao.java

4.3、连接池的配置文件dataSource.properties

4.4、spring配置文件applicationContext.xml

4.5、测试类Test.java

5、较DBCP，用C3P0连接池，需要改变的就是连接池的包要改变，和连接池配置的名称要改

6、较第五种，把模板添加到容器改成让dao去添加模板

6.1、domain类User.java

6.2、dao类UserDao.java

6.3、连接池的配置文件dataSource.properties

6.4、spring配置文件applicationContext.xml

6.5测试类Test.java