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 }