Spring JDBC数据库开发

针对数据库操作，Spring框架提供了JdbcTemplate类。

1.Spring JDBC的配置

创建配置文件applicationContext.xml，添加如下代码：

　　　<!--配置数据源-->
    <bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/springjdbc"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>
    <!--配置JDBC模板-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!--配置注入类-->
    <bean id="userDao" class="com.wxy.model.UserDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>

2.创建Javabean和Dao接口

public class User {
    private Integer id;
    private String name;
    private Integer age;

    public Integer getId() {  return id; }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String password) {
        this.name = password;
    }

    public Integer getAge() { return age; }

    public void setAge(Integer age) {
        this.age = age;
    }
}

public interface UserDao {
    public void execute();
    public int addUser(User user);
    public int updateUser(User user);
    public int deleteUser(int id);
    public void query();
}

3.Spring JdbcTemplate的常用方法

2.1 execute(String sql)执行SQL语句

2.2 update()插入、更新、删除表数据操作

2.3 query()查询操作

上述的三个数据库操作方法都放在UserDaoImpl实现类中调用，具体代码如下：

import com.wxy.javabean.User;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

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

public class UserDaoImpl implements UserDao {
    //获取JdbcTemplate实例
    private JdbcTemplate jdbcTemplate;
14     public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
15         this.jdbcTemplate = jdbcTemplate;
16     }
    //执行SQL语句的方法
    @Override
    public void execute() {
        jdbcTemplate.execute("create table account( id int primary key auto_increment,"
                + "username varchar(50)," + "balance double)");
    }
    //添加用户
    @Override
    public int addUser(User user) {
        String sql = "insert user (id,name,age) value(?,?,?)";
        //定义数组来存储SQL语句中的参数
        Object[] obj = new Object[]{
                user.getId(),
                user.getName(),
                user.getAge()
        };
        int num = this.jdbcTemplate.update(sql,obj);
        return num;
    }
    //更新用户信息
    public int updateUser(User user){
        String sql = "update user set name=?,age=? where id=?";
        Object[] params = new Object[]{
                user.getName(),
                user.getAge(),
                user.getId()
        };
        int num = this.jdbcTemplate.update(sql,params);
        return num;
    }
    //删除用户
    @Override
    public int deleteUser(int id) {
        String sql = "delete from user where id =?";
        int num = jdbcTemplate.update(sql,id);
        return num;
    }
    //查询所有用户
    @Override
    public void query() {
        String listSql = "select * from user";
        List<User> list = jdbcTemplate.query(listSql, new ResultSetExtractor<List>() {
            public List<User> extractData(ResultSet rs) throws SQLException,
                    DataAccessException {
                List<User> result = new ArrayList<User>();
                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setName(rs.getString("name"));
                    user.setAge(rs.getInt("age"));
                    result.add(user);
                }
                return result;
            }
        });

        for (User user : list) {
            System.out.print("id=" + user.getId() + " ");
            System.out.print("name=" + user.getName() + " ");
            System.out.println("age=" + user.getAge() + " ");
        }
    }
}

4.编写测试类Test

import com.wxy.javabean.User;
import com.wxy.model.UserDaoImpl;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class JdbcTemplateTest {
    @Test
    public void executeTest(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");
        userDao.execute();
        System.out.println("创建成功！");
    }
    @Test
    public void addTest(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");
        User user = new User();
        user.setId(3);
        user.setName("Tom");
        user.setAge(21);
        int num = userDao.addUser(user);
        if(num>0){
            System.out.println("成功添加"+num+"个用户");
        }else {
            System.out.println("添加用户操作失败！");
        }
    }
    @Test
    public void updateTest(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");
        User user = new User();
        user.setId(2);
        user.setName("Tim");
        user.setAge(26);
        int num = userDao.addUser(user);
        if(num>0){
            System.out.println("成功修改"+num+"个用户");
        }else {
            System.out.println("修改用户信息失败！");
        }
    }
    @Test
    public void deleteTest(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");
        int num = userDao.deleteUser(2);
        if(num>0){
            System.out.println("成功删除"+num+"个用户");
        }else {
            System.out.println("删除用户操作失败！");
        }
    }
    @Test
    public void query(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");
        userDao.query();
    }
}