Spring和数据库编程

xiaoxiao2025-04-10 20

学习日志：资料来自杨开振的《JavaEE 互联网轻量级框架整合开发》

1、配置数据库资源

通过Spring内部提供的类使用第三方数据库连接池从Web服务器中通过JNDI获取数据源

一般会采用xml配置，或者以注解。主要以xml

1.1 使用简单数据库配置

** 代码清单 12-2：配置SimpleDriverDtaSource **

1.2 使用第三方数据库连接池

1.3 使用JNDI数据库连接池

2 JdbcTemplate

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean> package com.learn.ssm.chapter12; import com.learn.ssm.pojo.Role; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import java.sql.ResultSet; import java.sql.SQLException; public class Main { ApplicationContext context = new ClassPathXmlApplicationContext("com/learn/ssm/chapter12/spring-cfg.xml"); JdbcTemplate jdbcTemplate = context.getBean(JdbcTemplate.class); int id = 1; String sql = "select id,role_name,note from t_note where id = " + id; Role role = jdbcTemplate.queryForObject(sql, new RowMapper<Role>() { @Override public Role mapRow(ResultSet resultSet, int i) throws SQLException { Role result = new Role(); while (resultSet.next()) { result.setId(resultSet.getInt("id")); result.setRoleName(resultSet.getString("role_name")); result.setNote(resultSet.getString("note")); } return result; } }); }

2.1 JdbcTemplate的增删改查

package com.learn.ssm.chapter12; import com.learn.ssm.chapter12.JdbcTemplate.impl.JdbcTemplateTest; import com.learn.ssm.pojo.Role; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; import java.util.List; public class Main { public static void main(String[] args){ ApplicationContext context = new ClassPathXmlApplicationContext("com/learn/ssm/chapter12/spring-cfg.xml"); JdbcTemplate jdbcTemplate = context.getBean(JdbcTemplate.class); JdbcTemplateTest jdbcTemplateTest = new JdbcTemplateTest(); Role role = new Role(31,"role_name_100","role_note_100"); jdbcTemplateTest.insertRole(jdbcTemplate, role); List<Role> list = jdbcTemplateTest.findRole(jdbcTemplate, "role"); jdbcTemplateTest.updateRole(jdbcTemplate, role); jdbcTemplateTest.deleteRole(jdbcTemplate, 31); } }

package com.learn.ssm.chapter12.JdbcTemplate.impl; import com.learn.ssm.pojo.Role; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; public class JdbcTemplateTest { public int insertRole(JdbcTemplate jdbcTemplate, Role role){ String sql = "insert into t_role(role_name, note) values(?,?)"; Object[] params = { role.getRoleName(), role.getNote() }; return jdbcTemplate.update(sql,params); } public int deleteRole(JdbcTemplate jdbcTemplate, int id){ String sql = "delete from t_role where id = ?"; return jdbcTemplate.update(sql, id); } public int updateRole(JdbcTemplate jdbcTemplate, Role role){ String sql = "update t_role set role_name = ?, note = ? where id = ?"; Object[] params = { role.getRoleName(), role.getNote(), role.getId() }; return jdbcTemplate.update(sql, params); } public List<Role> findRole(JdbcTemplate jdbcTemplate, String roleName){ String sql = "select id, role_name, note from t_role where role_name like concat('%',?,'%')"; Object[] params = { roleName }; List<Role> list = jdbcTemplate.query(sql, params, new RowMapper<Role>() { @Override public Role mapRow(ResultSet resultSet, int i) throws SQLException { Role result = new Role(); result.setId(resultSet.getInt("id")); result.setNote(resultSet.getString("note")); result.setRoleName(resultSet.getString("role_name")); return result; } }); return list; } }

2.2 执行多条SQL

public Role getRoleByConnectionCallBack(JdbcTemplate jdbcTemplate, int id){ Role role = null; role = jdbcTemplate.execute(new ConnectionCallback<Role>() { @Override public Role doInConnection(Connection connection) throws SQLException, DataAccessException { Role result = null; String sql = "select id, role_name, note from t_role where id = ?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,id); ResultSet rs = ps.executeQuery(); while(rs.next()){ result = new Role(); result.setId(rs.getInt("id")); result.setRoleName(rs.getString("role_name")); result.setNote(rs.getString("note")); } return result; } }); return role; } public Role getRoleByStatementCallBack(JdbcTemplate jdbcTemplate, int id){ Role role = null; role = jdbcTemplate.execute(new StatementCallback<Role>() { @Override public Role doInStatement(Statement statement) throws SQLException, DataAccessException { Role result = null; String sql = "select id, role_name, note from t_role where id = "+ id; ResultSet rs = statement.executeQuery(sql); while(rs.next()){ result = new Role(rs.getInt("id"),rs.getString("role_name"),rs.getString("note")); } return result; } }); return role; }

3 Myabtis-Spring 项目

配置Mybatis项目需要这么几步：

配置数据源配置SqlSessionFactory可以选择的配置有SqlSessionTemplate，在同时配置SqlSessionTemplate和SqlSessionFactory，优先采用SqlSessionTemplate配置Mapper，可以配置单个Mapper，也可以通过扫描的方法生成Mapper事务管理

3.1 配置SqlSesionFactoryBean

从Mybtais的介绍中，可以知道SqlSessionFactory是产生SqlSession的基础。

这里配置了SqlSessionFactoryBean，但是只配置了数据源，然后引入一个Mybtais的配置文件。

<?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="useGeneratedKeys" value="true"/> <setting name="defaultExecutorType" value="REUSE"/> <setting name="lazyLoadingEnabled" value="true"/> <setting name="defaultStatementTimeout" value="25000"/> </settings> <typeAliases> <typeAlias type="com.learn.ssm.pojo.Role" alias="role"/> </typeAliases> <mappers> <mapper resource="com/ssm/learn/chapter12/mapper/RoleMapper.xml"/> </mappers> </configuration> <?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: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/aop http://www.springframework.org/schema/aop/spring-aop.xsd"> <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"> <property name="username" value="root"/> <property name="password" value=""/> <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/ssm"/> <property name="maxIdle" value="5"/> <property name="maxWaitMillis" value="1000"/> <property name="maxTotal" value="255"/> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> <property name="configLocation" value="classpath:com/learn/ssm/chapter12/sqlMapConfig.xml"/> </bean> </beans>

3.2 配置SqlSessionTemplate组件

配置好了SqlSessionTemplate就可以使用了，比如：

package com.learn.ssm.chapter12; import com.learn.ssm.pojo.Role; import org.mybatis.spring.SqlSessionTemplate; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; public class Main { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("com/learn/ssm/chapter12/spring-cfg.xml"); SqlSessionTemplate sqlSessionTemplate = context.getBean(SqlSessionTemplate.class); Role role = new Role(); role.setRoleName("role_name_sqlSessionTemplate"); role.setNote("role_note_sqlSessionTemplate"); sqlSessionTemplate.insert("com.learn.ssm.chapter12.mapper.RoleMapper.insertRole",role); int id = role.getId(); Role role1 = sqlSessionTemplate.selectOne("com.learn.ssm.chapter12.mapper.RoleMapper.getRole",id); role.setNote("update_sqlSessionTemplate"); sqlSessionTemplate.update("com.learn.ssm.chapter12.mapper.RoleMapper.updateRole",role); sqlSessionTemplate.delete("com.learn.ssm.chapter12.mapper.RoleMapper.deleteRole",id); } }

3.3 配置MapperFactoryBean

这里可以看到MapperFactoryBean存在3个属性可以配置，分别是mapperInterface、sqlSessionTemplate和SqlSessionFactory，其中：

mapperInterfact 是映射器的接口如果同时配置sqlsessionTemplate和sqlSessionFactory，那么则会启动sqlSessionTemplate，当我们配置这样的一个Bean，就可以通过下面的代码去获取映射器: RoleMapper roleMapper = context.getBean(RoleMapper.class);

3.4 配置MapperScannerConfigurer

这是一个通过扫描的形式进行配置Mapper的类，如果一个个去配置Mapper，显然工作量大，并且导致配置泛滥，有了它只需要给予一些简单的配置，它就能生成大量mapper，从而减少工作量。对于MapperScannerConfigurer主要配置有以下几个:

basePackage,指定让Spring自动扫描什么包，它会逐层深入扫描，如果遇到多个包可以使用半角逗号分隔annotationClass，表示如果类被这个注解表示的时候，才进行扫描。对于开发而言，建议使用这个方式进行注册对应的Mapper。在Spring中往往使用注解@Repository表示数据访问层（DAO，Data Access Object），所以本书的例子也是以此方式为主进行介绍的。SqlSessionFactoryBeanName,指定在Spring中定义SqlSessionFactory的Bean名称。如果sqlSessionTemplateBeanName被定义，则失效markerInterface，指定实现了什么接口就认为它是mapper 在Spring配置之前要给Mpaper一个注解，在Spring中往往采用@Repository表示DAO层。 package com.learn.ssm.chapter12.mapper; import com.learn.ssm.pojo.Role; import org.springframework.stereotype.Repository; @Repository public interface RoleMapper { public int insertRole(Role role); public int deleteRole(int id); public int updateRole(Role role); public Role getRole(int id); }

@Repository标志了这是一个DAO层，我们还要告诉Spring扫描哪个包，这样就可能扫出对应的Mapper到Spring IoC容器中。

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.learn.ssm.chapter12.mapper"/> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/> <property name="annotationClass" value="org.springframework.stereotype.Repository"/> </bean> package com.learn.ssm.chapter12.mapper; import com.learn.ssm.chapter12.base.BaseMapper; import com.learn.ssm.pojo.Role; import org.springframework.stereotype.Repository; @Repository public interface RoleMapper { public int insertRole(Role role); public int deleteRole(int id); public int updateRole(Role role); public Role getRole(int id); }

3.5 测试

package com.learn.ssm.chapter12; import com.learn.ssm.chapter12.mapper.RoleMapper; import com.learn.ssm.pojo.Role; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; public class Main { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("com/learn/ssm/chapter12/spring-cfg.xml"); RoleMapper roleMapper = context.getBean(RoleMapper.class); Role role = new Role(); role.setRoleName("role_name_mapper"); role.setNote("note_mapper"); roleMapper.insertRole(role); int id = role.getId(); roleMapper.getRole(id); role.setNote("note_mapper_update"); roleMapper.updateRole(role); roleMapper.deleteRole(id); } }

小结

最新回复(0)