Spring+JDBC-1
Spring 通过配置文件 操作数据库
一、文件结构
二、java代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package entity; 2 3 /** 4 * 该类为实体类,与dept表对应 类---表 对象---记录 成员变量---字段 5 * 6 * ORM:对象关系型数据映射 mybatis hibernate 7 * 8 */ 9 public class Entity { 10 private int id; 11 private String name; 12 private String sex; 13 14 public Entity() { 15 super(); 16 } 17 18 public Entity(int id, String name, String sex) { 19 super(); 20 this.id = id; 21 this.name = name; 22 this.sex = sex; 23 } 24 25 public int getId() { 26 return id; 27 } 28 29 public void setId(int id) { 30 this.id = id; 31 } 32 33 public String getName() { 34 return name; 35 } 36 37 public void setName(String name) { 38 this.name = name; 39 } 40 41 public String getSex() { 42 return sex; 43 } 44 45 public void setSex(String sex) { 46 this.sex = sex; 47 } 48 49 @Override 50 public String toString() { 51 return "Entity [id=" + id + ", name=" + name + ", sex=" + sex + "]"; 52 } 53 54 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package inter; 2 3 import java.util.List; 4 5 import entity.Entity; 6 7 public interface DaoInter { 8 public int insertDept(); 9 10 public List<Entity> selectDeptAll(); 11 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package impl; 2 3 import java.sql.ResultSet; 4 import java.sql.SQLException; 5 import java.util.List; 6 7 import org.springframework.jdbc.core.JdbcTemplate; 8 import org.springframework.jdbc.core.RowMapper; 9 import org.springframework.jdbc.datasource.DataSourceTransactionManager; 10 import org.springframework.jdbc.datasource.DriverManagerDataSource; 11 import org.springframework.transaction.TransactionDefinition; 12 import org.springframework.transaction.TransactionStatus; 13 import org.springframework.transaction.support.DefaultTransactionDefinition; 14 15 import entity.Entity; 16 import inter.DaoInter; 17 18 /** 19 * dao层实现类 20 * 21 * @author Administrator 22 */ 23 public class DaoImpl implements DaoInter { 24 25 public JdbcTemplate jdbcTemplate; 26 public int insertDept() { 27 //1.创建事务管理器 28 DataSourceTransactionManager transactionManager =new DataSourceTransactionManager(); 29 transactionManager.setDataSource(jdbcTemplate.getDataSource()); 30 //2.创建一个事务 31 DefaultTransactionDefinition dtd=new DefaultTransactionDefinition(); 32 //3. 获取事务状态 33 TransactionStatus status=transactionManager.getTransaction(dtd); 34 //4设定事务级别及属性 35 //隔离 36 dtd.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED); 37 //传播 38 dtd.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); 39 //只读 40 dtd.setReadOnly(false); 41 //超时 42 dtd.setTimeout(-1); 43 //执行sql 44 int flag=jdbcTemplate.update("INSERT INTO person2 VALUES(?,?,?)", 300,"红绸军","男"); 45 System.out.println(flag); 46 //提交事务 47 transactionManager.commit(status); 48 return flag; 49 } 50 51 public JdbcTemplate getJdbcTemplate() { 52 return jdbcTemplate; 53 } 54 55 public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 56 this.jdbcTemplate = jdbcTemplate; 57 } 58 59 60 public List<Entity> selectDeptAll() { 61 String sql="select * from person2"; 62 List<Entity> list=jdbcTemplate.query(sql, new RowMapper() { 63 public Object mapRow(ResultSet rs, int rowNum) throws SQLException { 64 Entity en=new Entity(); 65 en.setId(rs.getInt("id")); 66 en.setName(rs.getString("name")); 67 en.setSex(rs.getString("sex")); 68 return en; 69 } 70 }); 71 return list; 72 } 73 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package test; 2 3 import java.util.List; 4 5 import org.springframework.context.ApplicationContext; 6 import org.springframework.context.support.ClassPathXmlApplicationContext; 7 8 import entity.Entity; 9 import impl.DaoImpl; 10 import inter.DaoInter; 11 12 public class TestJDBC { 13 public static void main(String[] args) { 14 // 加载ioc容器 15 ApplicationContext context = 16 new ClassPathXmlApplicationContext("applicationContext.xml"); 17 DaoInter dao=(DaoImpl)context.getBean("DaoImpl"); 18 //dao.insertDept(); 19 List<Entity> list=dao.selectDeptAll(); 20 for(Entity d:list) { 21 System.out.println(d); 22 } 23 } 24 }
三、配置文件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 <?xml version="1.0" encoding="UTF-8"?> 2 <beans xmlns="http://www.springframework.org/schema/beans" 3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p" 4 xmlns:context="http://www.springframework.org/schema/context" 5 xmlns:aop="http://www.springframework.org/schema/aop" 6 xsi:schemaLocation="http://www.springframework.org/schema/beans 7 http://www.springframework.org/schema/beans/spring-beans-4.3.xsd 8 http://www.springframework.org/schema/context 9 http://www.springframework.org/schema/context/spring-context-4.3.xsd 10 http://www.springframework.org/schema/aop 11 http://www.springframework.org/schema/aop/spring-aop-4.3.xsd"> 12 13 <!-- 加载数据库配置文件 --> 14 <context:property-placeholder location="classpath:db.properties"></context:property-placeholder> 15 <!-- 创建数据源 --> 16 <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" 17 init-method="init" destroy-method="close"> 18 <!-- 配置数据库连接基本信息 --> 19 <property name="driverClassName" value="${driverClass}" /> 20 <property name="url" value="${url}" /> 21 <property name="username" value="${name}" /> 22 <property name="password" value="${password}" /> 23 <!-- ******配置数据库连接池相关信息******* --> 24 <!-- 配置初始化大小、最小、最大 --> 25 <property name="initialSize" value="5" /> 26 <property name="minIdle" value="2" /> 27 <property name="maxActive" value="10" /> 28 <!-- 配置获取连接等待超时的时间 --> 29 <property name="maxWait" value="10000" /> 30 <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 --> 31 <property name="timeBetweenEvictionRunsMillis" value="60000" /> 32 <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 --> 33 <property name="minEvictableIdleTimeMillis" value="300000" /> 34 <property name="testWhileIdle" value="true" /> 35 <!-- 这里建议配置为TRUE,防止取到的连接不可用 --> 36 <property name="testOnBorrow" value="true" /> 37 <property name="testOnReturn" value="false" /> 38 <!-- 打开PSCache,并且指定每个连接上PSCache的大小 --> 39 <property name="poolPreparedStatements" value="true" /> 40 <property name="maxPoolPreparedStatementPerConnectionSize" 41 value="20" /> 42 <!-- 这里配置提交方式,默认就是TRUE,可以不用配置 --> 43 <property name="defaultAutoCommit" value="true" /> 44 <!-- 验证连接有效与否的SQL,不同的数据配置不同 --> 45 <property name="validationQuery" value="select 1" /> 46 </bean> 47 <!-- 声明JdbcTemplate --> 48 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 49 <property name="dataSource" ref="dataSource"></property> 50 </bean> 51 52 <!-- dao层 --> 53 <bean id="DaoImpl" class="impl.DaoImpl" p:jdbcTemplate-ref="jdbcTemplate"/> 54 </beans>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 <project xmlns="http://maven.apache.org/POM/4.0.0" 2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 3 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> 4 <modelVersion>4.0.0</modelVersion> 5 <groupId>spring</groupId> 6 <artifactId>spring_JDBC</artifactId> 7 <version>0.0.1-SNAPSHOT</version> 8 <dependencies> 9 <!-- druid数据源 --> 10 <dependency> 11 <groupId>com.alibaba</groupId> 12 <artifactId>druid</artifactId> 13 <version>1.1.8</version> 14 </dependency> 15 <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> 16 <dependency> 17 <groupId>mysql</groupId> 18 <artifactId>mysql-connector-java</artifactId> 19 <version>8.0.11</version> 20 </dependency> 21 22 <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx --> 23 <dependency> 24 <groupId>org.springframework</groupId> 25 <artifactId>spring-tx</artifactId> 26 <version>4.3.2.RELEASE</version> 27 </dependency> 28 <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc --> 29 <dependency> 30 <groupId>org.springframework</groupId> 31 <artifactId>spring-jdbc</artifactId> 32 <version>4.3.2.RELEASE</version> 33 </dependency> 34 <!-- https://mvnrepository.com/artifact/org.springframework/spring-core --> 35 <dependency> 36 <groupId>org.springframework</groupId> 37 <artifactId>spring-core</artifactId> 38 <version>4.3.2.RELEASE</version> 39 </dependency> 40 <dependency> 41 <groupId>org.springframework</groupId> 42 <artifactId>spring-context</artifactId> 43 <version>4.3.2.RELEASE</version> 44 </dependency> 45 <dependency> 46 <groupId>org.springframework</groupId> 47 <artifactId>spring-beans</artifactId> 48 <version>4.3.2.RELEASE</version> 49 </dependency> 50 <dependency> 51 <groupId>org.springframework</groupId> 52 <artifactId>spring-expression</artifactId> 53 <version>4.3.2.RELEASE</version> 54 </dependency> 55 </dependencies> 56 </project>