spring中对jdbc的封装模板jdbcTemplate可以大大简化jdbc的crud操作
使用jdbcTemplate实现crud操作步骤:
1.导入相关jar包
除了其他相关spring的jar还需要这两个(当然除了这些jar包还要导入数据库的驱动jar包)
2.测试类(数据的增删改都可以调用update方法)
package com.lee.jdbcTemplate;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class JDBC {
@Test
public void add(){
//配置数据库连接相关操作
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql:///sun");
dataSource.setUsername("root");
dataSource.setPassword("sunshine");
//创建jdbcTemplate对象,设置数据源
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
//通过jdbcTemplate实现crud
String sql = "insert into user values(?,?,?)";
int row = jdbcTemplate.update(sql, "root","cindy","123");
System.out.println(row);
}
}
关于数据的查询操作:
@Test
public void query(){
//配置数据库连接相关操作
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql:///sun");
dataSource.setUsername("root");
dataSource.setPassword("sunshine");
//创建jdbcTemplate对象,设置数据源
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
//通过jdbcTemplate实现crud
//查询一条数据
String sql = "select count(*) from user";
int count = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(count);
}
查询数据封装成对象:
@Test
public void queryObject(){
//配置数据库连接相关操作
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql:///sun");
dataSource.setUsername("root");
dataSource.setPassword("sunshine");
//创建jdbcTemplate对象,设置数据源
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
//通过jdbcTemplate实现crud
//查询一条数据
String sql = "select * from user where name = ?";
User user = jdbcTemplate.queryForObject(sql, new myRowMapper(),"cindy");
System.out.println(user);
}
jdbc调用查询对象的方法,封装了一部分,需要自己写一个类实现RowMapper接口中的方法
class myRowMapper implements RowMapper<User>{
@Override
public User mapRow(ResultSet rs, int num) throws SQLException {
//jdbcTemplate封装到了获取到结果集这一步
User user = new User();
user.setUserid(rs.getString(1));
user.setName(rs.getString(2));
user.setPassword(rs.getString(3));
return user;
}
}
通过以上实现的类,还可以查询出多条user对象的集合
@Test
public void queryList(){
//配置数据库连接相关操作
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql:///sun");
dataSource.setUsername("root");
dataSource.setPassword("sunshine");
//创建jdbcTemplate对象,设置数据源
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
//通过jdbcTemplate实现crud
//查询一条数据
String sql = "select * from user";
List<User> list = jdbcTemplate.query(sql, new myRowMapper());
System.out.println(list);
}
jdbcTemplate结合c3p0
模拟web项目service层调用dao层
1.导入jar包
除了c3p0的jar还要一个依赖包mchange-commons
2.service层UserService.java
package com.lee.service;
import com.lee.dao.UserDao;
public class UserService {
private UserDao userDao;
public void setUserDao(UserDao userDao) {
this.userDao = userDao;
}
//调用dao层,完成数据操作
public void count(){
System.out.println(this.userDao.count());
}
}
3.dao层UserDao.java
package com.lee.dao;
import org.springframework.jdbc.core.JdbcTemplate;
public class UserDao {
//封装的jdbc模板通过spring配置文件创建
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
//数据量查询
public int count() {
String sql = "select count(*) from user";
int num = this.jdbcTemplate.queryForObject(sql, Integer.class);
return num;
}
}
4.spring配置文件
<?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" xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 配置c3p0 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql:///sun" />
<property name="user" value="root" />
<property name="password" value="sunshine" />
</bean>
<bean id="userDao" class="com.lee.dao.UserDao">
<!-- userDao通过jdbc模板操作数据库 -->
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
<bean id="userService" class="com.lee.service.UserService">
<property name="userDao" ref="userDao" />
</bean>
<!-- 配置jdbc模板 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 有参构造方法中调用了setDataSource方法 ,把c3p0的到dataSource传进去-->
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
5.测试类
ApplicationContext context = new ClassPathXmlApplicationContext("ApplicationContextC3p0.xml");
UserService userService = (UserService) context.getBean("userService");
userService.count();