利用jdbcTemplate和QueryRenner实现spring连接数据库

利用jdbcTemplate和QueryRenner实现spring连接数据库

1.入门的jdbcTemplate连接数据库

package com.itheima.jdbctemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
/**
 * JdbcTemplate的最基本用法
 */
public class JdbcTemplateDemo1 {

    public static void main(String[] args) {
        //准备数据源：spring的内置数据源
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/eesy");
        ds.setUsername("root");
        ds.setPassword("1234");

        //1.创建JdbcTemplate对象
        JdbcTemplate jt = new JdbcTemplate();
        //给jt设置数据源
        jt.setDataSource(ds);
        //2.执行操作
        jt.execute("insert into account(name,money)values('ccc',1000)");
    }
}

2.基于xml配置的jdbcTemplate

1.xml中jdbc的配置
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
	<!-- 配置数据源-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/spring"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123456"></property>
    </bean>
2.主函数测试
	package com.itheima.jdbctemplate;

	import org.springframework.context.ApplicationContext;
	import org.springframework.context.support.ClassPathXmlApplicationContext;
	import org.springframework.jdbc.core.JdbcTemplate;
	
	/**
	 * JdbcTemplate的最基本用法
	 */
	public class JdbcTemplateDemo2 {
	
	    public static void main(String[] args) {
	        //1.获取容器
	        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
	        //2.获取对象
	        JdbcTemplate jt = ac.getBean("jdbcTemplate",JdbcTemplate.class);
	        //3.执行操作
	        jt.execute("insert into account(name,money)values('ddd',2222)");
	    }
	}

3.dao层继承JdbcDaoSupport类实现的jdbcTemplate

package com.itheima.dao.impl;
import com.itheima.dao.IAccountDao;
import com.itheima.domain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.util.List;

/**
 * 账户的持久层实现类.
 * 注：jdbcDaoSupport中内置成员jdbcTemplate和set注入dataSource。须在xml中注入dataSource
 */
public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao {


    public Account findAccountById(Integer accountId) {
        List<Account> accounts = super.getJdbcTemplate().query("select * from account where id = ?",new BeanPropertyRowMapper<Account>(Account.class),accountId);
        return accounts.isEmpty()?null:accounts.get(0);
    }


    public Account findAccountByName(String accountName) {
        List<Account> accounts = super.getJdbcTemplate().query("select * from account where name = ?",new BeanPropertyRowMapper<Account>(Account.class),accountName);
        if(accounts.isEmpty()){
            return null;
        }
        if(accounts.size()>1){
            throw new RuntimeException("结果集不唯一");
        }
        return accounts.get(0);
    }


    public void updateAccount(Account account) {
        super.getJdbcTemplate().update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
    }
}

4.基于注解和xml的QueryRunner

1.xml中配置
	<bean id="runner" class="org.apache.commons.dbutils.QueryRunner" scope="prototype">
	</bean>
	<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.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>
	<!--配置Connection的工具类 ConnectionUtils-->
    <bean id="connectionUtils" class="com.itheima.utils.ConnectUtils">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
2.创建工具类
	public class ConnectUtils1 {
	    private DataSource dataSource;
	    public void setDataSource(DataSource dataSource) {
	        this.dataSource = dataSource;
	    }
	    public Connection getConnection(){
	        Connection conn = null;
	        try{
	            conn = dataSource.getConnection();
	        //4.返回当前线程的连接
	            return conn;
	        }catch (Exception e){
	            throw new RuntimeException(e);
	        }
	    }
	}
3.创建dao的实现类（set注入）
	public class AccountDaoImpl1 implements IAccountDao {
	    private QueryRunner runner;
	    private ConnectUtils connectUtils;
	
	    public void setConnectUtils(ConnectUtils connectUtils) {
	        this.connectUtils = connectUtils;
	    }
	
	    public void setRunner(QueryRunner runner) {
	        this.runner = runner;
	    }
	
	    public List<Account> findAllAccount() {
	        try {
	            return runner.query(connectUtils.getConnection(),"select * from account",new BeanListHandler<Account>(Account.class));
	        } catch (Exception e) {
	            throw new RuntimeException(e);
	        }
	    }
	}
4.创建测试类进行测试。
	@RunWith(SpringJUnit4ClassRunner.class)
	@ContextConfiguration(locations = "classpath:bean.xml")
	public class AccountServiceImplTest {
		@Autowired
	    private IAccountDao accountDao1;
	    @Test
	    public void testAccountDao1(){
	        List<Account> accounts = accountDao1.findAllAccount();
	        for (Account account : accounts) {
	            System.out.println(account);
	        }
	    }
	}