JavaWeb_(Spring框架)Spring与JDBC

 

 

一、用Spring中的JdbcTemplate操作数据库

　　在MySQL中准备一个user表，表中增加一条假数据

　　

 

　　用Spring中的JdbcTemplate操作数据库，在JdbcTemplate中实现增删改查操作

    //增
    void saveUser(User u);
    
    //删
    void deleteUserById(Integer id);
    
    //改
    void updateUser(User u);
    
    //查
    //根据id查找用户
    User selectUserById(Integer id);
    
    //查找全部用户List
    List<User> selectAllUser();
    
    //查找用户数量
    Integer selectUserCount();

 

 

package com.Gary.bean;

public class User {

    private Integer u_id;
    private String u_username;
    private String u_password;
    
    
    public Integer getU_id() {
        return u_id;
    }
    public void setU_id(Integer u_id) {
        this.u_id = u_id;
    }
    public String getU_username() {
        return u_username;
    }
    public void setU_username(String u_username) {
        this.u_username = u_username;
    }
    public String getU_password() {
        return u_password;
    }
    public void setU_password(String u_password) {
        this.u_password = u_password;
    }
    @Override
    public String toString() {
        return "User [u_id=" + u_id + ", u_username=" + u_username + ", u_password=" + u_password + "]";
    }
    
    
    
}

 

package com.Gary.dao;

import java.util.List;

import com.Gary.bean.User;

public interface UserDao {

    //增
    void saveUser(User u);
    
    //删
    void deleteUserById(Integer id);
    
    //改
    void updateUser(User u);
    
    //查
    //根据id查找用户
    User selectUserById(Integer id);
    
    //查找全部用户List
    List<User> selectAllUser();
    
    //查找用户数量
    Integer selectUserCount();
    
}

 

package com.Gary.dao;

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

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

import com.Gary.bean.User;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class UserDaoImpl implements UserDao {

    JdbcTemplate jt = new JdbcTemplate(dataSource);

    private static ComboPooledDataSource dataSource;
    
    static {
        //配置c3p0
        try {
            //使用c3p0链接数据库
            dataSource = new ComboPooledDataSource();
            dataSource.setDriverClass("com.mysql.jdbc.Driver");
            dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/ssm_spring");
            dataSource.setUser("root");
            dataSource.setPassword("123456");
        } catch (PropertyVetoException e) {
        
            e.printStackTrace();
        }
    }


    //保存用户
    @Override
    public void saveUser(User u) {
        String sql = "insert into user values(null,?,?)";
        jt.update(sql,u.getU_username(),u.getU_password());
        
    }

    //根据id删除用户
    @Override
    public void deleteUserById(Integer id) {
        String sql = "delete from user where u_id = ?";
        jt.update(sql,id);
    }

    //更新用户
    @Override
    public void updateUser(User u) {
        String sql= "update user set u_username =? , u_password = ?where u_id =?";
        jt.update(sql,u.getU_username(),u.getU_password(),u.getU_id());
    }
    
    //根据id查询一个用户
    @Override
    public User selectUserById(Integer id) {
        String sql = "select * from user where u_id = ?";
        User user = jt.queryForObject(sql, new RowMapper<User>() {

            @Override
            public User mapRow(ResultSet rs, int index) throws SQLException {
                User u = new User();
                u.setU_id(rs.getInt("u_id"));
                u.setU_username(rs.getString("u_username"));
                u.setU_password(rs.getString("u_password"));
                
                return u;
            }
            
        },id);

        return user;
    }

    //查询用户列表
    @Override
    public List<User> selectAllUser() {
        String sql= "select * from user";
        List<User> list = jt.query(sql,  new RowMapper<User>() {

            @Override
            public User mapRow(ResultSet rs, int index) throws SQLException {
                User u = new User();
                u.setU_id(rs.getInt("u_id"));
                u.setU_username(rs.getString("u_username"));
                u.setU_password(rs.getString("u_password"));
                
                return u;
            }
            
        });
        
        return list;
    }

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

}

 

　　根据id查找用户

package com.Gary.test;

import org.junit.Test;

import com.Gary.bean.User;
import com.Gary.dao.UserDao;
import com.Gary.dao.UserDaoImpl;

public class JdbcTest {

    @Test
    public void Test1() {
        UserDao dao = new UserDaoImpl();
        //根据id查询用户
        User u = dao.selectUserById(1);
        System.out.println(u);
        
    }
    
}

 

　　保存用户

package com.Gary.test;

import org.junit.Test;

import com.Gary.bean.User;
import com.Gary.dao.UserDao;
import com.Gary.dao.UserDaoImpl;

public class JdbcTest2 {

    @Test
    public void Test2() {
        UserDao dao = new UserDaoImpl();
        //添加一个新的数据
        User u = new User();
        u.setU_username("newGary");
        u.setU_password("123123");
        dao.saveUser(u);
        
    }
    
}

 

 

二、使用Spring容器来管理JdbcTemplate

 

package com.Gary.test;

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;

import com.Gary.bean.User;
import com.Gary.dao.UserDao;

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

    @Resource(name = "userDao")
    private UserDao ud;
    
    @Test
    public void Test3() {
        //添加一个新的数据
        User u = new User();
        u.setU_username("666sGary");
        u.setU_password("666");
        ud.saveUser(u);
        
    }
    
}

 

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
    
    <!-- 依赖关系 dao -> jdbcTemplate -> dataSource -->

    <!-- 配置 dataSource -->
    <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/ssm_spring"/>
        <property name="user" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    
    <!-- jdbcTemplate  -->
    <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    <!-- dao -->
    <bean name="userDao" class="com.Gary.dao.UserDaoImpl">
        <property name="jt" ref="jdbcTemplate"/>
    </bean>
    
</beans>