关闭

Spring JDBC整合

标签: SpringSpringJDBC整合
249人阅读 评论(0) 收藏 举报
分类:
1、首先导入包:

2、然后建立数据库表
CREATE DATABASE day36;

USE day36;

CREATE TABLE t_user(
    id INT PRIMARY KEY,
    NAME VARCHAR(20),
    gender CHAR(2)
);


3、在编写实体
package star.july.entity;
public class User {
          private int id;
          private String name;
          private String gender;
          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;
          }
          public String getGender() {
                   return gender;
          }
          public void setGender(String gender) {
                   this.gender = gender;
          }
          @Override
          public String toString() {
                   return "User [id=" + id + ", name=" + name + ", gender=" + gender + "]";
          }
          
}
4、编写dao类
package star.july.dao;
import java.util.List;
import star.july.entity.User;
public interface IUserDao {
    public void save(User user);
    public void update(User user);
    public void delete(int id);
    public List<User> queryAll();
    public List<User> queryPages(int curPage,int PageSize);
    public User queryById(int id);
    public int queryCount();
}


dao类实现接口
package star.july.dao;
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 star.july.entity.User;
public class UserDaoImpl implements IUserDao{
    //用于接收jdbcTemplate对象
    private JdbcTemplate jdbcTemplate;
    
    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    //添加
    public void save(User user) {
        jdbcTemplate.update("insert into t_user(id,name,gender) values(?,?,?)",
                user.getId(),
                user.getName(),
                user.getGender()
                );
    }
    //修改
    public void update(User user) {
        jdbcTemplate.update("update t_user set name=?,gender=? where id=?",
                user.getName(),
                user.getGender(),
                user.getId()
                );
        
    }
    //删除
    public void delete(int id) {
        jdbcTemplate.update("delete from t_user where id=?",id);
        
    }
    //查询所有数据
    public List<User> queryAll() {
        return jdbcTemplate.query("select * from t_user",new RowMapper(
                ) {
            public Object mapRow(ResultSet rs, int index)
                    throws SQLException {
                User u = new User();
                u.setId(rs.getInt("id"));
                u.setName(rs.getString("name"));
                u.setGender(rs.getString("gender"));
                return u;
            }
                }
                );
    }
    
    
    //分页查询
    public List<User> queryPages(int curPage,int pageSize) {
            return (List<User>)jdbcTemplate.query("select * from t_user limit ? , ?", new RowMapper(){
                public Object mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                    User u = new User();
                    u.setId(rs.getInt("id"));
                    u.setName(rs.getString("name"));
                    u.setGender(rs.getString("gender"));
                    return u;
                }
            },(curPage-1)*pageSize,pageSize);
    }
    public User queryById(int id) {
        return jdbcTemplate.queryForObject("select * from t_user where id = ?",new RowMapper(){
            public Object mapRow(ResultSet rs, int index) throws SQLException {
                User u = new User();
                u.setId(rs.getInt("id"));
                u.setName(rs.getString("name"));
                u.setGender(rs.getString("gender"));
                return u;
            }
        },id);
    }
    //查询总计录数
    public int queryCount() {
        return jdbcTemplate.queryForObject("select count(*) from t_user", Long.class).intValue();
    }
    
    
}

5、编写applicationContext.xml
<?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.xsd">
          
          <!--  1、创建连接池对象 -->
          <bean id="dataSourceID" class="com.mchange.v2.c3p0.ComboPooledDataSource">
                   <!-- 注入参数 -->
                   <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/day36"></property>
                   <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
                   <property name="user" value="root"></property>
                   <property name="password" value="root"></property>
          </bean>
          
          <!-- 2、创建spring提供的JdbcTemplate模块对象 -->
          <bean id="jdbcTemplateID" class="org.springframework.jdbc.core.JdbcTemplate">
                   <!-- 注入连接池对象 -->
                   <property name="dataSource" ref="dataSourceID"></property>
          </bean>
          
          <!-- 3、创建dao对象 -->
          <bean id="userDaoID" class="star.july.dao.UserDaoImpl">
                   <!-- 注入jdbcTemplate对象 -->
                   <property name="jdbcTemplate" ref="jdbcTemplateID"></property>
          </bean>
          
          
       
</beans>


6、测试
package star.july.test;
import java.util.List;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import star.july.dao.IUserDao;
import star.july.dao.UserDaoImpl;
import star.july.entity.User;
public class Demo {
    //添加
    @Test
    public void test(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");
        IUserDao userDao = (IUserDao)ac.getBean("userDaoID");
        User u = new User();
        u.setId(3);
        u.setName("徐渭熊");
        u.setGender("");
        userDao.save(u);
    }
    
    
    //修改
    @Test
    public void test2(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");
        IUserDao userDao = (IUserDao)ac.getBean("userDaoID");
        User u = new User();
        u.setId(1);
        u.setName("徐奇");
        u.setGender("");
        userDao.update(u);
    }
    //删除
    @Test
    public void test3(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");
        IUserDao userDao = (IUserDao)ac.getBean("userDaoID");
        userDao.delete(2);
    }
    //查询所有对象
    @Test
    public void test4(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");
        IUserDao userDao = (IUserDao)ac.getBean("userDaoID");
        List<User> list = userDao.queryAll();
        for(User u : list){
            System.out.println(u);
        }
    }
    //根据id查找对象
    @Test
    public void test5(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");
        IUserDao userDao = (IUserDao)ac.getBean("userDaoID");
        User u = userDao.queryById(2);
        System.out.println(u);
        }
    //查找总计录数
    @Test
    public void test6(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");
        IUserDao userDao = (IUserDao)ac.getBean("userDaoID");
        int count = userDao.queryCount();
        System.out.println(count);
    }
    
    //分页查询
    @Test
    public void test7(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");
        IUserDao userDao = (IUserDao)ac.getBean("userDaoID");
        List<User> list = userDao.queryPages(2, 2);
        for(User user : list ){
            System.out.println(user);
        }
    }
}





抽取数据可的参数到properties文件,首先要配置context命名空间(红色部分)
再配置全局读取:  <context:property-placeholder location="classpath:db.properties"/>
最后用表达式获取值(黑色加粗部分)

<?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:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">
          <context:property-placeholder location="classpath:db.properties"/>
          
          <!--  1、创建连接池对象 -->
          <bean id="dataSourceID" class="com.mchange.v2.c3p0.ComboPooledDataSource">
                   <!-- 注入参数 -->
                   <property name="jdbcUrl" value="${jdbcUrl}"></property>
                   <property name="driverClass" value="${driverClass}"></property>
                   <property name="user" value="${user}"></property>
                   <property name="password" value="${password}"></property>
          </bean>
          
          <!-- 2、创建spring提供的JdbcTemplate模块对象 -->
          <bean id="jdbcTemplateID" class="org.springframework.jdbc.core.JdbcTemplate">
                   <!-- 注入连接池对象 -->
                   <property name="dataSource" ref="dataSourceID"></property>
          </bean>
          
          <!-- 3、创建dao对象 -->
          <bean id="userDaoID" class="star.july.dao.UserDaoImpl">
                   <!-- 注入jdbcTemplate对象 -->
                   <property name="jdbcTemplate" ref="jdbcTemplateID"></property>
          </bean>
       
</beans>



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:71620次
    • 积分:1965
    • 等级:
    • 排名:千里之外
    • 原创:121篇
    • 转载:1篇
    • 译文:0篇
    • 评论:27条
    文章分类
    最新评论