spring第四天jdbctemplete

最新推荐文章于 2022-08-06 22:18:52 发布

puppycuty

最新推荐文章于 2022-08-06 22:18:52 发布

阅读量284

点赞数

分类专栏： java spring

本文链接：https://blog.csdn.net/qq_38125626/article/details/88423456

版权

java 同时被 2 个专栏收录

140 篇文章 0 订阅

订阅专栏

spring

25 篇文章 0 订阅

订阅专栏

jdbctemplete的基本用法

使用spring ioc配置jdbc

jdbc在持久层的使用方法

jdbctemplete在持久层的使用方式二（继承org.springframework.jdbc.core.support.JdbcDaoSupport）

spring中定义其他数据源

1.需要导入的jar包

2.在本地数据库创建springtest库和表people

jdbctemplete的基本用法

package cn.pro.jdbctemplete;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

//jdbctemplete的基本用法
public class JdbcTempleteDemo1 {
	public static void main(String[] args) {
		//1定义数据源 
		DriverManagerDataSource ds =new DriverManagerDataSource();
		ds.setDriverClassName("com.mysql.jdbc.Driver");
		ds.setUrl("jdbc:mysql://localhost:3306/springtest");
		ds.setUsername("root");
		ds.setPassword("123456");
		
		
		//2获取对象
		JdbcTemplate jt = new JdbcTemplate(ds);
		//或者
		
		/*JdbcTemplate jt2 = new JdbcTemplate();
		jt2.setDataSource(ds);*/
		//3执行操作
		jt.execute("insert into people (name,addr) values ('mac','jiangsu')");
		
		System.out.println("~~~~~~~~~~~~~~~~~~~~~~~");
		ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
		JdbcTemplate test1 = (JdbcTemplate) ac.getBean("jdbctemplete");
		test1.execute("insert into people (name,addr) values ('hanmei','hankou')");
	}
}

使用spring ioc配置jdbc

bean.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">

    <!-- bean definitions here -->
    <!-- 配置jdbctemplete -->
    <bean id="jdbctemplete" 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/springtest"></property>
    <property name="username" value="root"></property>
    <property name="password" value="123456"></property>
    
    </bean>
    

</beans>

创建一个javabean

package cn.pro.domain;

public class people {
	private String name;
	private String addr;
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAddr() {
		return addr;
	}
	public void setAddr(String addr) {
		this.addr = addr;
	}
	@Override
	public String toString() {
		return "people [name=" + name + ", addr=" + addr + "]";
	}

	
	
}

实现增删改查

package cn.pro.jdbctemplete;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import cn.pro.domain.people;

//jdbctemplete的基本用法
public class JdbcTempleteDemo2 {
	public static void main(String[] args) {
		
		System.out.println("~~~~~~~~~~~~~~~~~~~~~~~");
		//获取容器和数据源  
		ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
		//获取对象
		JdbcTemplate test1 = (JdbcTemplate) ac.getBean("jdbctemplete");
		//执行操作 
		//保存
		//test1.update("insert into people (name,addr) values (?,?)","fff","abc");
		
		//更新
		//test1.update("update people set addr=? where name = ?","123","fff" );
		
		//删除 
		//test1.update("delete from people where name=?","fff");
		//查询所有 
		
		//RowMapper rm = null;
		/*List<people> list = test1.query("select * from people where id>?", new BeanPropertyRowMapper<people>(people.class), 2);
		//System.out.println(list);
		for(int i=0;i<list.size();i++){
			System.out.println(list.get(i));
		}*/
		
		
		
		//查询一个
		/*List<people> list = test1.query("select * from people where id=?", new BeanPropertyRowMapper<people>(people.class), 8);
		//System.out.println(list);
			System.out.println(list.isEmpty()?"no result":list.get(0));*/
			
		//查询一行一列  聚合函数的使用 
		//queryForObject()该方法是spring3以后添加的   在spring2版本 它的方式时 queryforint(),queryforlong(),queryforshort()
		Integer t = test1.queryForObject("select count(*) from people where id>?",Integer.class,4);
		System.out.println(t);
		long l= test1.queryForObject("select count(*) from people where id>?",long.class,4);
		
		System.out.println(l);
		
		
		
	}
}

jdbc在持久层的使用方法

持久层接口

package cn.pro.dao;

import cn.pro.domain.people;

public interface IPeopleDao {
	//根据id查询账户
	people findPeopleById(Integer id);
	
	
	//根据名字查询账户  
	people findPeopleByName(String name);
	
	//更新账户  
	void updatePeople(people p);
	
	
	
}

持久层实现类

package cn.pro.dao.impl;

import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;

import cn.pro.dao.IPeopleDao;
import cn.pro.domain.people;

public class IPeopleDaoImpl implements IPeopleDao{
	
	private JdbcTemplate jdbc;
	
	public void setJdbc(JdbcTemplate jdbc) {
		this.jdbc = jdbc;
	}
 
	public people findPeopleById(Integer id) {
		// TODO Auto-generated method stub   
		List<people> list = jdbc.query("select * from people where id=?", new BeanPropertyRowMapper<people>(people.class),id);
		return (list.isEmpty()?null:list.get(0));
	
	 
	}

	public people findPeopleByName(String name) {
		
		List<people> list = jdbc.query("select * from people where name=?", new BeanPropertyRowMapper<people>(people.class),name);
		if(list.isEmpty()){
			return null;//没有这个账号
		}
		if(list.size()>1){
			throw  new RuntimeException("结果不唯一");
		}
		return list.get(0);
	}

	public void updatePeople(people p) {
		// TODO Auto-generated method stub
		
		jdbc.update("update people set addr=? where name=?",p.getAddr(),p.getName());
		
	}

}

bean.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">

    <!-- bean definitions here -->
    <!-- 配置jdbctemplete -->
    <bean id="jdbctemplete" 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/springtest"></property>
    <property name="username" value="root"></property>
    <property name="password" value="123456"></property>
    
    </bean>
    
    <!-- 配置dao -->
    <bean id="iPeopleDao" class="cn.pro.dao.impl.IPeopleDaoImpl">
    <property name="jdbc" ref="jdbctemplete"></property>
    </bean>

</beans>

测试类

package cn.pro.jdbctemplete;


import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import cn.pro.dao.IPeopleDao;
import cn.pro.dao.impl.IPeopleDaoImpl;
import cn.pro.domain.people;

//jdbctemplete在dao中的用法
public class JdbcTempleteDemo3 {
	public static void main(String[] args) {
		
		//获取容器和数据源  
		ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
		//获取容器
		IPeopleDao p = (IPeopleDaoImpl)ac.getBean("iPeopleDao");
		
		people p1= p.findPeopleById(8);
		System.out.println(p1);
		
	}
		
		
		
}

jdbctemplete在持久层的使用方式二（继承org.springframework.jdbc.core.support.JdbcDaoSupport）

package cn.pro.dao.impl;

import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import cn.pro.dao.IPeopleDao;
import cn.pro.domain.people;

public class IPeopleDaoImpl2 extends JdbcDaoSupport implements IPeopleDao{
 
	public people findPeopleById(Integer id) {
		// TODO Auto-generated method stub   
		List<people> list = getJdbcTemplate().query("select * from people where id=?", new BeanPropertyRowMapper<people>(people.class),id);
		return (list.isEmpty()?null:list.get(0));
	
	 
	}

	public people findPeopleByName(String name) {
		
		List<people> list = getJdbcTemplate().query("select * from people where name=?", new BeanPropertyRowMapper<people>(people.class),name);
		if(list.isEmpty()){
			return null;//没有这个账号
		}
		if(list.size()>1){
			throw  new RuntimeException("结果不唯一");
		}
		return list.get(0);
	}

	public void updatePeople(people p) {
		// TODO Auto-generated method stub
		
		getJdbcTemplate().update("update people set addr=? where name=?",p.getAddr(),p.getName());
		
	}

}

org.springframework.jdbc.core.support.JdbcDaoSupport

/*
 * Copyright 2002-2012 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.springframework.jdbc.core.support;

import java.sql.Connection;
import javax.sql.DataSource;

import org.springframework.dao.support.DaoSupport;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.SQLExceptionTranslator;

/**
 * Convenient super class for JDBC-based data access objects.
 *
 * <p>Requires a {@link javax.sql.DataSource} to be set, providing a
 * {@link org.springframework.jdbc.core.JdbcTemplate} based on it to
 * subclasses through the {@link #getJdbcTemplate()} method.
 *
 * <p>This base class is mainly intended for JdbcTemplate usage but can
 * also be used when working with a Connection directly or when using
 * {@code org.springframework.jdbc.object} operation objects.
 *
 * @author Juergen Hoeller
 * @since 28.07.2003
 * @see #setDataSource
 * @see #getJdbcTemplate
 * @see org.springframework.jdbc.core.JdbcTemplate
 */
public abstract class JdbcDaoSupport extends DaoSupport {

	private JdbcTemplate jdbcTemplate;


	/**
	 * Set the JDBC DataSource to be used by this DAO.
	 */
	public final void setDataSource(DataSource dataSource) {
		if (this.jdbcTemplate == null || dataSource != this.jdbcTemplate.getDataSource()) {
			this.jdbcTemplate = createJdbcTemplate(dataSource);
			initTemplateConfig();
		}
	}

	/**
	 * Create a JdbcTemplate for the given DataSource.
	 * Only invoked if populating the DAO with a DataSource reference!
	 * <p>Can be overridden in subclasses to provide a JdbcTemplate instance
	 * with different configuration, or a custom JdbcTemplate subclass.
	 * @param dataSource the JDBC DataSource to create a JdbcTemplate for
	 * @return the new JdbcTemplate instance
	 * @see #setDataSource
	 */
	protected JdbcTemplate createJdbcTemplate(DataSource dataSource) {
		return new JdbcTemplate(dataSource);
	}

	/**
	 * Return the JDBC DataSource used by this DAO.
	 */
	public final DataSource getDataSource() {
		return (this.jdbcTemplate != null ? this.jdbcTemplate.getDataSource() : null);
	}

	/**
	 * Set the JdbcTemplate for this DAO explicitly,
	 * as an alternative to specifying a DataSource.
	 */
	public final void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
		initTemplateConfig();
	}

	/**
	 * Return the JdbcTemplate for this DAO,
	 * pre-initialized with the DataSource or set explicitly.
	 */
	public final JdbcTemplate getJdbcTemplate() {
	  return this.jdbcTemplate;
	}

	/**
	 * Initialize the template-based configuration of this DAO.
	 * Called after a new JdbcTemplate has been set, either directly
	 * or through a DataSource.
	 * <p>This implementation is empty. Subclasses may override this
	 * to configure further objects based on the JdbcTemplate.
	 * @see #getJdbcTemplate()
	 */
	protected void initTemplateConfig() {
	}

	@Override
	protected void checkDaoConfig() {
		if (this.jdbcTemplate == null) {
			throw new IllegalArgumentException("'dataSource' or 'jdbcTemplate' is required");
		}
	}


	/**
	 * Return the SQLExceptionTranslator of this DAO's JdbcTemplate,
	 * for translating SQLExceptions in custom JDBC access code.
	 * @see org.springframework.jdbc.core.JdbcTemplate#getExceptionTranslator()
	 */
	protected final SQLExceptionTranslator getExceptionTranslator() {
		return getJdbcTemplate().getExceptionTranslator();
	}

	/**
	 * Get a JDBC Connection, either from the current transaction or a new one.
	 * @return the JDBC Connection
	 * @throws CannotGetJdbcConnectionException if the attempt to get a Connection failed
	 * @see org.springframework.jdbc.datasource.DataSourceUtils#getConnection(javax.sql.DataSource)
	 */
	protected final Connection getConnection() throws CannotGetJdbcConnectionException {
		return DataSourceUtils.getConnection(getDataSource());
	}

	/**
	 * Close the given JDBC Connection, created via this DAO's DataSource,
	 * if it isn't bound to the thread.
	 * @param con Connection to close
	 * @see org.springframework.jdbc.datasource.DataSourceUtils#releaseConnection
	 */
	protected final void releaseConnection(Connection con) {
		DataSourceUtils.releaseConnection(con, getDataSource());
	}

}

此种方式配合xml比较简单，第一种方式适合注解比较简单。

spring中定义其他数据源

dbcp数据源导包

C3P0数据源导包

    <!-- 定义spring内置数据源 
    <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/springtest"></property>
    <property name="username" value="root"></property>
    <property name="password" value="123456"></property>
   
    </bean> -->
    
    <!-- 定义dbcp数据源 
    <bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
    
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://localhost:3306/springtest"></property>
    <property name="username" value="root"></property>
    <property name="password" value="123456"></property>
    
    </bean>-->
    
        <!-- 定义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://localhost:3306/springtest"></property>
    <property name="user" value="root"></property>
    <property name="password" value="123456"></property>
    
    </bean>