35.Spring-jdbc支持.md

2022-10-17 14:01:59

目录

[toc]

1.JdbcTemplate类

传统的jdbc开始，需要对Connection、Stream的创建、使用和关闭一一操作。开发负责，代码耦合。在Spring中提供JdbcTemplate简化开发。配合连接池数据库的jdbc操作将和简单

1.1导入jar包

其中，tx是事务管理，接下来用。这里咱不用理会。

1.2创建Dao对象

创建Dao对象，在Dao对象中通过DataSource类来管理连接，通过JdbcTemplate类来处理sql。注意DataSource类是：javax.sql.DataSource



import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

import java.util.Map;

/**

 * Created by liyue on 2016/11/30.

 */

public class PersonDao

{

    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate()

    {

        return jdbcTemplate;

    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate)

    {

        this.jdbcTemplate = jdbcTemplate;

    }

    public void save(String sql)

    {

        System.out.println("保存开始:");

        //插入、保存和更新这一类都是update

        jdbcTemplate.update(sql);

        System.out.println("保存结束");

    }

    public void queryForList()

    {

        String sql = "SELECT * FROM role LIMIT 100;";

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

        System.out.println(list);

    }

    public void queryById(int id)

    {

        String sql = "SELECT * FROM role  WHERE r_id = " + String.valueOf(id) + ";";

        Map<String, Object> m = jdbcTemplate.queryForMap(sql);

        System.out.println(m);

    }

}

然后配置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:tx="http://www.springframework.org/schema/tx"

       xmlns:aop="http://www.springframework.org/schema/aop"

       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        http://www.springframework.org/schema/tx

       http://www.springframework.org/schema/tx/spring-tx.xsd        http://www.springframework.org/schema/aop        http://www.springframework.org/schema/aop/spring-aop.xsd">    <!--数据源：c3p0连接池-->

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">

        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>

        <property name="jdbcUrl" value="jdbc:mysql:///hi"></property>

        <property name="user" value="root"></property>

        <property name="password" value="root"></property>

        <property name="initialPoolSize" value="3"></property>

    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

        <property name="dataSource" ref="dataSource"></property>

    </bean>    <!--Dao对象-->

    <bean id="personDao" class="per.liyue.spring.jdbc_base.PersonDao">

        <property name="jdbcTemplate" ref="jdbcTemplate"></property>

    </bean>

</beans>

最后写测试调用例子

package per.liyue.spring.jdbc_base;

import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

import java.util.Map;

/**

 * Created by liyue on 2016/11/30.

 */

public class PersonDao

{

    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate()

    {

        return jdbcTemplate;

    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate)

    {

        this.jdbcTemplate = jdbcTemplate;

    }

    public void save(String sql)

    {

        System.out.println("保存开始:");

        //插入、保存和更新这一类都是update        jdbcTemplate.update(sql);

        System.out.println("保存结束");

    }

    public void queryForList()

    {

        String sql = "SELECT * FROM role LIMIT 100;";

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

        System.out.println(list);

    }

    public void queryById(int id)

    {

        String sql = "SELECT * FROM role  WHERE r_id = " + String.valueOf(id) + "; ";

        Map<String, Object> m = jdbcTemplate.queryForMap(sql);

        System.out.println(m);

    }

}

1.3将上述例子封装后

增加一个查询结果类

package per.liyue.spring.jdbc_base;

/**

 * Created by liyue on 2016/11/30.

 */

public class Role

{

    private int roleId;

    public int getRoleId()

    {

        return roleId;

    }

    public void setRoleId(int roleId)

    {

        this.roleId = roleId;

    }

    public String getRoleName()

    {

        return roleName;

    }

    public void setRoleName(String roleName)

    {

        this.roleName = roleName;

    }

    private String roleName;

    @Override

    public String toString()

    {

        return "roleId: " + roleId + " roleName: " + roleName;

    }

}

改造Dao类

package per.liyue.spring.jdbc_base;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import java.util.Map;

/**

 * Created by liyue on 2016/11/30.

 */

public class PersonDao

{

    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate()

    {

        return jdbcTemplate;

    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate)

    {

        this.jdbcTemplate = jdbcTemplate;

    }

    public void save(String sql)

    {

        System.out.println("保存开始:");        //插入、保存和更新这一类都是update

        jdbcTemplate.update(sql);

        System.out.println("保存结束");

    }

    public void queryForList()

    {

        String sql = "SELECT * FROM role LIMIT 100;";

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

        System.out.println(list);

    }

    public Role queryById(int id)

    {

        String sql = "SELECT * FROM role  WHERE r_id = " + String.valueOf(id) + ";";        /*        简单查询        Map<String, Object>m = jdbcTemplate.queryForMap(sql);        System.out.println(m);        */        /*        封装查询         */

        List<Role> list = jdbcTemplate.query(sql, new MyResult());

        return ((list != null) && (list.size() > 0)) ? list.get(0) : null;

    }

    class MyResult implements RowMapper<Role>

    {

        @Override

        public Role mapRow(ResultSet resultSet, int i) throws SQLException

        {

            Role role = new Role();

            role.setRoleId(resultSet.getInt("r_id"));

            role.setRoleName(resultSet.getString("r_name"));

            return role;

        }

    }

}

package per.liyue.spring.jdbc_base;

import org.junit.Test;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

/**

 * Created by liyue on 2016/11/30.

 */

public class App

{

    private ApplicationContext applicationContext = new ClassPathXmlApplicationContext("per/liyue/spring/jdbc_base/applicationContext.xml");

    @Test

    public void testSave()

    {

        System.out.println("开始:");

        PersonDao personDao = applicationContext.getBean("personDao", PersonDao.class);

        String sql = "INSERT INTO role(r_name) VALUE('架构')";

        personDao.save(sql);

        System.out.println("结束");

    }

    @Test

    public void find100()

    {

        System.out.println("开始:");

        PersonDao personDao = applicationContext.getBean("personDao", PersonDao.class);

        personDao.queryForList();

        System.out.println("结束");

    }

    @Test

    public void findById()

    {

        System.out.println("开始:");

        PersonDao personDao = applicationContext.getBean("personDao", PersonDao.class);

        Role role = personDao.queryById(1);

        System.out.println(role);

        System.out.println("结束");

    }

}

码农公寓

目录 [toc]

1.JdbcTemplate类

1.1导入jar包

1.2创建Dao对象

1.3将上述例子封装后

2.

3.

相关文章

目录

[toc]