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 + "]";

    }

}

User.java

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();

}

UserDao.java

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);

    }

}

UserDaoImpl.java

　　根据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);

    }

}

JdbcTest.java

　　保存用户

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);

    }

}

JdbcTest2.java

二、使用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);

    }

}

JdbcTest3.java

<?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>

applicationContext.xml

巴特西

JavaWeb_(Spring框架)Spring与JDBC

最新文章

热门文章