SpringMVC 与oracle数据库的交互

川长思鸟来 2023-10-17 08:05 8阅读 0赞

**一、使用 JdbcTemplate连接数据库**

Spring 框架中 org.springframework.jdbc.core  包提供了 JDBC 模板类，其中JdbcTemplate 是 core 这个包的核心类，其他模板类都是基于它封装完成的。JdbcTemplate 类主要提供以下四类方法：

（1）execute 方法

（2）update 方法及 batchUpdate 方法

（3）query 方法及 queryForXXX 方法

注意：JdbcTemplate 的 query 方法，支持传入一个 Callback 接口：

(3-1)ResultSetExtractor：用于结果集数据提取，用户需实现方法 extractData 方法来处理结果集

\*\*(3-2) RowCallbackHandler：用于处理 ResultSet 的每一行结果，用户需实现方法processRow 方法来完成处理，在该回调方法中无需执行 rs.next()

(3-3)RowMapper：用于将结果集每行数据转换为需要的类型，用户需实现方法 mapRow 方法来完成将每行数据转换为相应的类型

（4）call 方法

**二、使用 Mybatis框架连接数据库**

（1）Mybatis 优势：

• 简单易学

• Mybatis  不 会对应用程序或者数据库的现有设计强加任何影响

• 提 供  xml  标 签，支持编写动 态  sql

• Mybatis  实 现 了  DAO  接 口 与  xml  映 射文件的绑定

**（2）引入 Mybatis依赖包：**

**![Center][]**

在pom.xml文件中配置：

<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-jdbc</artifactId>
    			<version>4.3.1.RELEASE</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.springframework.ws</groupId>
    			<artifactId>spring-oxm</artifactId>
    			<version>1.5.9</version>
    		</dependency>
    		
    
    		
    		<dependency>
    			<groupId>org.mybatis</groupId>
    			<artifactId>mybatis</artifactId>
    			<version>3.2.8</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.mybatis</groupId>
    			<artifactId>mybatis-spring</artifactId>
    			<version>1.2.2</version>
    		</dependency>
    
    		
    
    		<dependency>
    			<groupId>com.oracle</groupId>
    			<artifactId>ojdbc14</artifactId>
    			<version>10.2.0.4.0</version>
    		</dependency>
    
    
    		
    		<dependency>
    			<groupId>org.apache.tomcat</groupId>
    			<artifactId>tomcat-jdbc</artifactId>
    			<version>8.0.32</version>
    			<scope>runtime</scope>
    		</dependency>

**（3）在src/main/resources下配置文件**

**![Center 1][]**

jdbc.properties文件

jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://127.0.0.1:3306/test_db?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&allowMultiQueries=true
    jdbc.username=root
    jdbc.password=root
    
    jdbc.driver.ora=oracle.jdbc.driver.OracleDriver
    jdbc.url.ora=jdbc:oracle:thin:@127.0.0.1:1521:HLX
    jdbc.username.ora=scott
    jdbc.password.ora=hsx
    
    #connection pool settings
    jdbc.pool.maxIdle=20
    jdbc.pool.maxActive=190

applicationContext.xml 中配置 dataSource

<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
            
            <property name="driverClassName" value="${jdbc.driver.ora}" />
            <property name="url" value="${jdbc.url.ora}" />
            <property name="username" value="${jdbc.username.ora}" />
            <property name="password" value="${jdbc.password.ora}" />
    
            
            <property name="maxActive" value="${jdbc.pool.maxActive}" />
            <property name="maxIdle" value="${jdbc.pool.maxIdle}" />
            <property name="minIdle" value="0" />
            <property name="defaultAutoCommit" value="false" />
        </bean>
        
        
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        	<property name="dataSource" ref="dataSource"></property>
        </bean>
    
        
        <beans profile="production">
            <context:property-placeholder ignore-unresolvable="true" file-encoding="utf-8" 
                location="classpath:config.properties,classpath:jdbc.properties" />
        </beans>

applicationContext.xml 中配置 sqlSessionFactory和 sqlSessionTemplate,还有其他的；

<context:component-scan base-package="com.hlx" use-default-filters="true">
            <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" />
        </context:component-scan>
    
    
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource" />
            
            <property name="typeAliasesPackage" value="com.hlx.entity;" /> 
            
            <property name="mapperLocations" value="classpath*:/mybatis/*Mapper.xml" />
            <property name="configLocation" value="classpath:/mybatis/config.xml"/>
        </bean>
        
        <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
           <constructor-arg index="0" ref="sqlSessionFactory" />
           <constructor-arg index="1" value="BATCH" />
        </bean>
    
        
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.hlx.dao" />
        </bean>
    
        
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource"></property>
        </bean>
        
        <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" />

config.xml配置文件（ Mybatis  全 局的环境配置）

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
    
      
    	<settings>
           
           <setting name="cacheEnabled" value="true" />
    		<setting name="lazyLoadingEnabled" value="false" />
    		<setting name="useColumnLabel" value="true" />
    		<setting name="useGeneratedKeys" value="true" />
    		<setting name="defaultExecutorType" value="SIMPLE" />
    		<setting name="localCacheScope" value="STATEMENT"/>
    	</settings>
    </configuration>

userinfoMapper.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
    <mapper namespace="com.hlx.dao.UserinfoDao">
    
    	
    	<cache eviction="FIFO" flushInterval="60000" size="500" readOnly="true"></cache>
    
      
    	
    	
    	
    	<insert id="save" parameterType="Userinfo">
    	 
    	 <selectKey  resultType="java.lang.Integer" keyProperty="id" order="AFTER">
    	   select seq_uid.currval as id from dual
    	 </selectKey>
    	  insert into userinfos(id,uname, upass) values(seq_uid.nextval,#{uname}, #{upass})
    	</insert>
    
    	<select id="selectById" parameterType="int" resultType="Userinfo"
    		useCache="false">
    		select * from userinfos where id=#{id}
    	</select>
    
        
    	<delete id="delUserinfo" parameterType="int" flushCache="true">
    		DELETE from userinfos where id=#{id}
    	</delete>
    
    	<select id="all" resultType="Userinfo">
    		select * from userinfos
    	</select>
    
    </mapper>

(4)Entity实体类省略；Dao接口

package com.hlx.dao;
    
    import java.util.List;
    
    
    import com.hlx.entity.Userinfo;
    
    public interface UserinfoDao {
    
    	void save(Userinfo user);
    	Userinfo selectById(Integer id);
    	void delUserinfo(Integer id); // 删除用户
    	List<Userinfo> all(); // 查询所有的数据
    
    }

(5)Service接口

public interface UserinfoService {
    	void addUserinfo(String name, String pwd); // 添加用户
    
    	void delUserinfo(Integer id); // 删除用户
    
    	Userinfo getUserinfoById(Integer id); // 根据用户ID来查询数据
    
    	List<Userinfo> all(); // 查询所有的数据
    	
    	
    	//使用MyBatis------------------------------------------
    	void addUserinfoMyBatis(String name, String pwd); // 添加用户
    
    	void delUserinfoMyBatis(Integer id); // 删除用户
    
    	Userinfo getUserinfoByIdMyBatis(Integer id); // 根据用户ID来查询数据
    
    	List<Userinfo> allMyBatis(); // 查询所有的数据
    	//------------------------------------------
    }

(6)实现Service接口类

/**
     * 实现业务
     * 
     * @author Administrator
     * 
     */
    @Service
    public class UserinfoServiceImpl implements UserinfoService {
    
    	// 注入jdbcTemplate
    	@Resource
    	JdbcTemplate jdbcTemplate;
    
    	/**
    	 * 添加，事务管理
    	 */
    	@Transactional
    	@Override
    	public void addUserinfo(final String name, final String pwd) {
    		//支持seq_uid.nextval （oracle自动序列化）
    		final String sql = "insert into userinfos(id,uname,upass) values(seq_uid.nextval,?,?)";
    		
    		// 保存生成后的PK  ===>Mysql支持 (oracle不支持)
    		//KeyHolder key = new GeneratedKeyHolder();
    
    		jdbcTemplate.update(new PreparedStatementCreator() {
    			@Override
    			public PreparedStatement createPreparedStatement(Connection arg0)
    					throws SQLException {
    
    				PreparedStatement ps = arg0.prepareStatement(sql);
    				ps.setString(1, name);
    				ps.setString(2, pwd);
    				return ps;
    			}
    		});
    
    		// 输出key
    		//System.out.println("key=" + key.getKey().intValue());
    	}
    	
    	/**
    	 * 删除，事务管理
    	 */
    	@Transactional
    	@Override
    	public void delUserinfo(Integer id) {
    		// TODO Auto-generated method stub
    		String sql="delete from userinfos where id=?";
    		jdbcTemplate.update(sql, id);
    
    	}
    
    	@Override
    	public Userinfo getUserinfoById(Integer id) {
    		// TODO Auto-generated method stub
    		String sql="select * from userinfos where id=?";
    		
    		//实体对象
    		final Userinfo user= new Userinfo();
    		
    		//查询
    		jdbcTemplate.query(sql, new Object[]{id}, new RowCallbackHandler() {
    			
    			@Override
    			public void processRow(ResultSet rs) throws SQLException {
    				// TODO Auto-generated method stub
    				user.setUname(rs.getString("uname"));
    				user.setUpass(rs.getString("upass"));
    			}
    			
    		
    		});
    		
    		//设置ID
    		user.setId(id);
    		
    		return user;
    	}
    
    	@Override
    	public List<Userinfo> all() {
    		// TODO Auto-generated method stub
    		String sql ="select  * from userinfos";
    		
    		final List<Userinfo> lists = new ArrayList<Userinfo>();
    		
    		jdbcTemplate.query(sql, new RowCallbackHandler() {
    			
    			@Override
    			public void processRow(ResultSet rs) throws SQLException {
    				// TODO Auto-generated method stub
    				Userinfo user= new Userinfo();
    				user.setUname(rs.getString("uname"));
    				user.setUpass(rs.getString("upass"));
    				user.setId(rs.getInt("id"));
    				
    				//添加到集合中
    				lists.add(user);
    			}
    		});
    		
    		return lists;
    	}
    /mybatis///
    	//注入接口
    	@Resource
    	UserinfoDao userinfoDao;
    	
    	
    	@Transactional
    	@Override
    	public void addUserinfoMyBatis(String name, String pwd) {
    		// TODO Auto-generated method stub
    		Userinfo user=new Userinfo();
    		user.setUname(name);
    		user.setUpass(pwd);
    		userinfoDao.save(user);
    	}
    	
    	@Transactional
    	@Override
    	public void delUserinfoMyBatis(Integer id) {
    		// TODO Auto-generated method stub
    		userinfoDao.delUserinfo(id);
    	}
    
    	@Override
    	public Userinfo getUserinfoByIdMyBatis(Integer id) {
    		// TODO Auto-generated method stub
    		return userinfoDao.selectById(id);
    	}
    
    	@Override
    	public List<Userinfo> allMyBatis() {
    		// TODO Auto-generated method stub
    		return userinfoDao.all();
    	}
    }

整个工程

![Center 2][]

**效果(一）：JdbcTemplate连接数据库**

**![Center 3][]**

**![Center 4][]**

**![Center 5][]**

**![Center 6][]**

**效果（二）： Mybatis连接数据库**

**![Center 7][]**

**![Center 8][]**

**![Center 9][]**

**![Center 10][]**

**总结：**

**1、使用SpringMVC+ JdbcTemplate操作Oracle数据库CRUD**

**2、使用SpringMVC+ Mybatis操作Oracle数据库CRUD**

[Center]: https://img-blog.csdn.net/20160725103513233?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 1]: https://img-blog.csdn.net/20160725103916797?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 2]: https://img-blog.csdn.net/20160725105227506?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 3]: https://img-blog.csdn.net/20160725105342662?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 4]: https://img-blog.csdn.net/20160725105353368?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 5]: https://img-blog.csdn.net/20160725105408478?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 6]: https://img-blog.csdn.net/20160725105425756?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 7]: https://img-blog.csdn.net/20160725105524741?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 8]: https://img-blog.csdn.net/20160725105538590?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 9]: https://img-blog.csdn.net/20160725105551288?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 10]: https://img-blog.csdn.net/20160725105604882?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center