spring----jdbcTemplate
一、jdbcTemplate
spring用于提供操作jdbc的工具类，类似DButils
依赖连接池 DataSources (数据源)
1.1配置dbcp连接池
1、创建表
2、导入jar包
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>2.6.2</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.6.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.4</version>
</dependency>
User
public class User {
private int id;
private String username;
private String password;
public User(String username, String password) {
this.username = username;
this.password = password;
}
public User() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
UserDaoImpl
@Repository("userDaoImpl")
public class UserDaoImpl {
@Resource(name = "jdbcTemplate")
private JdbcTemplate jdbcTemplate;
public void updata(User user){
jdbcTemplate.update("update t_user set username=?,password=? where id=?",user.getUsername(),user
.getPassword(),user.getId());
}
}
xml
<?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:aop="http://www.springframework.org/schema/aop"
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
http://www.springframework.org/schema/aop
https://www.springframework.org/schema/aop/spring-aop.xsd">
<context:component-scan base-package="com.test"></context:component-scan>
<bean id="basicDataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/javas1"></property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="basicDataSource"></property>
</bean>
</beans>
test
public class Test{
public static void main(String[] args) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
// //创建数据源(连接池),dbcp
// BasicDataSource basicDataSource = new BasicDataSource();
// basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
// basicDataSource.setUrl("jdbc:mysql://localhost:3306/javas1");
// basicDataSource.setUsername("root");
// basicDataSource.setPassword("2014(zy)");
//
// //创建模板
// JdbcTemplate jdbcTemplate = new JdbcTemplate();
// jdbcTemplate.setDataSource(basicDataSource);
//
// //通过api操作
// jdbcTemplate.update("INSERT INTO t_user(username,PASSWORD) VALUES(\"aa\",\"aa\")");
// //jdbcTemplate.update("INSERT INTO t_user(username,PASSWORD) VALUES(?,?)","cc","cc");
//将上面的代码使用xml配置
ClassPathXmlApplicationContext classPathXmlApplicationContext = new ClassPathXmlApplicationContext("spring-context.xml");
UserDaoImpl userDaoImpl = (UserDaoImpl) classPathXmlApplicationContext.getBean("userDaoImpl");
User user = new User();
user.setId(3);
user.setUsername("zzz");
user.setPassword("zzz");
userDaoImpl.updata(user);
}
}
1.2配置 c3p0连接池
修改xml中的配置，其他的不变，就可以直接测试了
<?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:aop="http://www.springframework.org/schema/aop"
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
http://www.springframework.org/schema/aop
https://www.springframework.org/schema/aop/spring-aop.xsd">
<context:component-scan base-package="com.test"></context:component-scan>
<bean id="basicDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/javas1"></property>
<property name="user" value="root"></property>
<property name="password" value="123456"></property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="basicDataSource"></property>
</bean>
</beans>
补充
由于每一个dao都要写
@Resource(name = "jdbcTemplate") private JdbcTemplate jdbcTemplate;
所以spring采取一个措施，所有的dao可以继承一个父类，然后，在配置文件中，将 jdbcTemplate 注入到父类当中
UserDaoImpl
public class UserDaoImpl extends JdbcDaoSupport{
public List<User> selectall(){
System.out.println("test");
List<User> query = this.getJdbcTemplate().query("select * from t_user", BeanPropertyRowMapper.newInstance(User.class));
return query;
}
}
xml，通过xml 将 jdbcTemplate注入进去，取代之前的注解方式 [JdbcDaoSupport中有 setJdbcTemplate 方法，所以可以通过xml注入]
<bean id="userDaoImpl" class="com.test.UserDaoImpl">
<property name="jdbcTemplate" ref="JdbcTemplate"></property>
</bean>
1.3 jdbcTemplate API
查询所有：List<User> userList = jdbcTemplate.query("select * from t_user", BeanPropertyRowMapper.newInstance(User.class));
查询一个：User user = jdbcTemplate.queryForObject("select * from t_user where id=?", BeanPropertyRowMapper.newInstance(User.class), 5);
1.4 配置 properties
目的：在xml中配置的固定的数据，存放到properties文件中，方便以后的修改
在资源目录下创建一个properties文件，这里写jdbcInfo.properties
jdbc.driverClass=com.mysql.jdbc.Driver jdbc.jdbcUrl=jdbc:mysql://localhost:3306/javas1 jdbc.user=root jdbc.password=123456
xml
<?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:aop="http://www.springframework.org/schema/aop"
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
http://www.springframework.org/schema/aop
https://www.springframework.org/schema/aop/spring-aop.xsd">
<context:component-scan base-package="com.test"></context:component-scan>
<!-- 加载配置文件-->
<context:property-placeholder location="jdbcinfo.properties"></context:property-placeholder>
<!-- 如果配置文件没有放在资源目录,下面这种写法就可以找到,classpath:表示定位到src目录下-->
<!-- <context:property-placeholder location="classpath:com.test.xx.properties"></context:property-placeholder>-->
<bean id="basicDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driverClass}"></property>
<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
<property name="user" value="${jdbc.user}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<bean id="JdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="basicDataSource"></property>
</bean>
<bean id="userDaoImpl" class="com.test.UserDaoImpl">
<property name="jdbcTemplate" ref="JdbcTemplate"></property>
</bean>
</beans>
