Spring-JDBC 数据访问

添加.jar包

<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.0.8.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.0.8.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.2</version>
</dependency>

创建连接数据库

直接在java文件写入数据库信息

 //连接数据库测试
    @Test
    public void test1(){
        //Combo 二合一 Pooled 汇集 Source资源
        //得到资源
        ComboPooledDataSource db=new ComboPooledDataSource();

        try {
            //加载驱动
            db.setDriverClass("com.mysql.jdbc.Driver");
            //获取连接
            db.setJdbcUrl("jdbc:mysql://localhost:3306/fdk");
            //获取用户名
            db.setUser("root");
            //获取密码
            db.setPassword("123");
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }

        //spring中操作数据库的工具类:JdbcTemplate
        //Template 模板,模板关联db
        JdbcTemplate temp=new JdbcTemplate(db);

        //使用模板操作数据库
        int k=temp.update("insert into address(address) values ('哈哈')");
        System.out.println("返回值为"+k);
    }

创建.properties文件导入数据库信息
对数据库进行操作

步骤

1. 创建service对象和dao对象
2. XXX.xml文件 加载properties文件信息 创建数据源,连接数据库
3. XXX.xml文件创建连接数据库的模板.
4. dao 层得到模板
   {
   i.继承JdbcDaoSupport类
   ii.调用setTemp创建模板
   }
5. 关联数据结果

执行:

1. 在dao层类上添加注解@Repository
   在service层类上添加注解@Service(“uservice”)

<!--获取配置文件信息-->
        <context:property-placeholder location="db.properties"/>
        <!--创建数据源,连接数据库-->
        <bean id="db" class="com.mchange.v2.c3p0.ComboPooledDataSource">
                <property name="driverClass" value="${driver}"/>
                <property name="password" value="${upass}"/>
                <property name="jdbcUrl" value="${url}"/>
                <property name="user" value="${uname}"/>
        </bean>

3. 创建Temp,并扫描注解

<!--core 核心 把得到的数据库资源给Temp-->
        <!--temp 在层的setTemp被使用 setTemp参数命不需要保持一致-->
        <bean id="temp" class="org.springframework.jdbc.core.JdbcTemplate">
                <property name="dataSource" ref="db"/>
        </bean>

	<!--扫描,注解-->
        <context:component-scan base-package="com">						</context:component-scan>

4. 得到连接数据库db的模板

 //得到连接数据库db的模板
    @Autowired
    public void setTemp(JdbcTemplate t){
        super.setJdbcTemplate(t);
    }

5. 增删改查

    public int insertadd(Address add) {
        String  sql="insert into address(address) values (?)";
        int k=getJdbcTemplate().update(sql,add.getAddress());
        return k;
    }

    public int deleteAdd(int aid) {
        String sql="delete from address where addressid=?";
        int k=getJdbcTemplate().update(sql,aid);
        return k;
    }

    public int updateAdd(Address add) {
        String sql="update address set address=? where addressid=?";
        int k=getJdbcTemplate().update(sql,add.getAddress(),add.getAddressid());
        return k;
    }

    public Address findbyid(int aid) {
        String sql="select * from address where addressid=?";
        Address add=getJdbcTemplate().queryForObject(sql, new Object[]{aid}, new RowMapper<Address>() {
            public Address mapRow(ResultSet resultSet, int i) throws SQLException {
                //调用chuliresult方法对查询到的结果集进行处理
                return chuliresult(resultSet);
            }
        });
        return add;
    }
    private  Address chuliresult(ResultSet resultSet){
        Address add=new Address();
        try {
            add.setAddressid(resultSet.getInt("addressid"));
            add.setAddress(resultSet.getString("address"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  add;
    }

    public List<Address> findall() {
        String sql="select * from address";
        List<Address> list=getJdbcTemplate().query(sql, new RowMapper<Address>() {
            public Address mapRow(ResultSet resultSet, int i) throws SQLException {
                System.out.println(resultSet+".....................");
                return chuliresult(resultSet);
            }
        });
        return list;
    }