spring_04spring整合JDBC

spring整合JDBC

spring中提供了一个可以操作数据库的对象，对象封装了jdbc技术。

JDBCTemplate JDBC模板对象

与DBUtils中的QueryRunner非常相似

1.导包

4+2
spring-test
spring-aop
junit4
c3p0连接池
JDBC驱动
spring-jdbc
spring-tx事物包

2.准备数据库

CREATE DATABASE spring;

CREATE TABLE t_user (
id int(5) NOT NULL AUTO_INCREMENT,
name varchar(10) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

3.普通方式使用JDBCTemplate

package org.spring.jdbc;

import java.beans.PropertyVetoException;

import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Demo {

@Test
public void test() throws PropertyVetoException{
//1.创建数据库连接池
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/spring");
dataSource.setUser("root");
dataSource.setPassword("123456");

//2.创建JDBC模板对象
JdbcTemplate jt = new JdbcTemplate(dataSource);
String sql = "insert into t_user values(null,'Tom')";
jt.update(sql);
}
}

4.spring方式使用JDBCTemplate

//User.java
package org.spring.jdbc;

public class User {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + "]";
}

}

//UserDao.java
package org.spring.jdbc;

import java.util.List;

public interface UserDao {
//添加
void save(User user);
//更改
void update(User user);
//删除
void delete(Integer id);
//查
List findAll();
User findById(Integer id);
int findCount();
}

//UserDaoImpl.java
package org.spring.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class UserDaoImpl implements UserDao {

private JdbcTemplate jt;

public void setJt(JdbcTemplate jt) {
this.jt = jt;
}

public JdbcTemplate getJt() {
return jt;
}

@Override
public void save(User user) {
String sql = "insert into t_user values(null,?)";
jt.update(sql, user.getName());
}

@Override
public void update(User user) {
String sql = "update t_user set name=? where id=?";
jt.update(sql, user.getName(),user.getId());
}

@Override
public void delete(Integer id) {
String sql = "delete from t_user where id=?";
jt.update(sql,id);
}

@Override
public List findAll() {
String sql = "select * from t_user";
List list = jt.query(sql, new RowMapper() {
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
return user;
}
});
return list;
}

@Override
public User findById(Integer id) {
String sql = "select * from t_user where id=?";
return jt.queryForObject(sql, new RowMapper() {
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
return user;
}
},id);
}

@Override
public int findCount() {
String sql = "select count(*) from t_user";
return jt.queryForObject(sql, Integer.class);
}

}

//applicationContext.xml

//TestSpringJdbc.java
package org.spring.jdbc;

import javax.annotation.Resource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class TestStringJdbc {

@Resource(name="userDao")
private UserDao ud;

@Resource(name="user")
private User u;

@Resource(name="user")
private User u2;

@Test
public void test1(){
u.setName("Jack");
ud.save(u);
}
@Test
public void test2(){
u2.setName("Tom");
u2.setId(2);
ud.update(u2);
}
@Test
public void test3(){
System.out.println(ud.findById(1));
System.out.println(ud.findCount());
System.out.println(ud.findAll());
}
@Test
public void test4(){
ud.delete(1);
}
}

5.扩展：让UserDaoImpl extends JDBCDaoSupport

jt ---> super.getJdbcTemplate()

用处：不需要手动准备 JDBC模板了，直接从父类的方法直接获得

6.读取properties文件配置

jdbc.driverClass=com.myslq.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/spring
jdbc.user=root
jdbc.password=123456

配置：

<context:property-placeholder location="db.properties"/>