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<User> 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<User> findAll() {
            String sql = "select * from t_user";
            List<User> list = jt.query(sql, new RowMapper<User>() {
                @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<User>() {
                @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
    <?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"
        xmlns:aop="http://www.springframework.org/schema/aop"
        xmlns:p="http://www.springframework.org/schema/p"
        xmlns:jdbc="http://www.springframework.org/schema/jdbc"
        xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd
            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-4.0.xsd
            http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd">

        <!-- 将连接池放入spring容器 -->
        <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="driverClass" value="com.myslq.jdbc.Driver"></property>
            <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/spring"></property>
            <property name="user" value="root"></property>
            <property name="password" value="123456"></property>
        </bean>
        <!-- 将JDBC模板放入spring容器 -->
        <bean name="jt" class="org.springframework.jdbc.core.JdbcTemplate">
            <property name="dataSource" ref="dataSource"></property>
        </bean>
        <!-- 将UserDao放入spring容器 -->
        <bean name="userDao" class="org.spring.jdbc.UserDaoImpl">
            <property name="jt" ref="jt"></property>
        </bean>
        <!-- 将User放入spring容器中 -->
        <bean name="user" class="org.spring.jdbc.User" scope="prototype"></bean>
    </beans>




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

    配置：

    <!-- 指定spring读取db.properties文件的配置信息 -->
    <context:property-placeholder location="db.properties"/>
    <!-- 将连接池放入spring容器 -->
    <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClass}"></property>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
        <property name="user" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

转载于:https://www.cnblogs.com/nm666/p/8296482.html