jdbcTemplate類的入門 方式一 POM.XML <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocati ...
jdbcTemplate類的入門
方式一
POM.XML
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>spring-aop</groupId> <artifactId>spring-aop</artifactId> <version>0.0.1-SNAPSHOT</version> <dependencies> <!-- spring ioc組件需要的依賴包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>5.2.1.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>5.2.1.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>5.2.1.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-expression</artifactId> <version>5.2.1.RELEASE</version> </dependency> <!-- 基於AspectJ的aop依賴包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> <version>5.2.1.RELEASE</version> </dependency> <dependency> <groupId>aopalliance</groupId> <artifactId>aopalliance</artifactId> <version>1.0</version> </dependency> <!-- spring 事務管理和JDBC依賴包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>5.2.1.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.2.1.RELEASE</version> </dependency> <!-- spring 單元測試組件包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>5.2.1.RELEASE</version> <scope>test</scope> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <!-- MySql資料庫驅動包 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.18</version> </dependency> </dependencies> </project>
修改jdk版本
<build> <plugins> <!-- 配置Maven的JDK編譯級別 --> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.2</version> <configuration> <source>1.8</source> <target>1.8</target> <encoding>UTF-8</encoding> </configuration> </plugin> </plugins> </build>
測試單元
package com.cyb.spring.test; import org.junit.Test; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.DriverManagerDataSource; public class TestJdbcTemplate { @Test public void test() { try { //創建連接池,先使用spring框架內置的連接池 DriverManagerDataSource dataSource =new DriverManagerDataSource(); //資料庫驅動程式 //dataSource.setDriverClassName("com.mysql.jdbc.Driver"); //舊驅動程式 dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); //新驅動程式 //資料庫連接字元串 dataSource.setUrl("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai"); //賬號 dataSource.setUsername("root"); //密碼 dataSource.setPassword("root"); //創建模板類 JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource); //完成數據的添加 int res = jdbcTemplate.update("insert into s_user values (null,?,?)",22,"測試人員"); } catch (Exception e) { e.printStackTrace(); } } }
方式二
spring.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" 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"> <!-- 管理DataSource --> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <!-- set方法註入屬性,和類中的成員屬性無關,和set方法名稱有關,比如有一個屬性叫username,但是set方法:setName --> <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai"></property> <property name="username" value="root"></property> <property name="password" value="root"></property> </bean> <!-- 管理jdbcTemplate --> <bean id="template" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg name="dataSource" ref="dataSource"></constructor-arg> </bean> </beans>
單元測試
package com.cyb.spring.test; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = "classpath:spring.xml") public class TestJdbcTemplate { @Autowired private JdbcTemplate jdbcTemplate; @Test public void test() { try { int res = jdbcTemplate.update("insert into s_user values (null,?,?)",22,"測試人員"); } catch (Exception e) { e.printStackTrace(); } } }
Spring 管理第三方DataSource
常用數據源連接池
- dbcp
- c3p0
- druid(阿裡出品)
管理DBCP連接池
maven工程加入依賴
pom.xml
<dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency>
spring.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" 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"> <!-- 管理第三方DataSource --> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <!-- set方法註入屬性,和類中的成員屬性無關,和set方法名稱有關,比如有一個屬性叫username,但是set方法:setName --> <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai"></property> <property name="username" value="root"></property> <property name="password" value="root"></property> </bean> <!-- 管理jdbcTemplate --> <bean id="template" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg name="dataSource" ref="dataSource"></constructor-arg> </bean> </beans>
單元測試
package com.cyb.spring.test; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.DriverManagerDataSource; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = "classpath:spring.xml") public class TestJdbcTemplate { @Autowired private JdbcTemplate jdbcTemplate; @Test public void test() { try { int res = jdbcTemplate.update("insert into s_user values (null,?,?)",22,"測試人員22"); } catch (Exception e) { e.printStackTrace(); } } @Test public void test2() { try { //創建連接池,先使用spring框架內置的連接池 DriverManagerDataSource dataSource =new DriverManagerDataSource(); //資料庫驅動程式 //dataSource.setDriverClassName("com.mysql.jdbc.Driver"); //舊驅動程式 dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); //新驅動程式 //資料庫連接字元串 dataSource.setUrl("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai"); //賬號 dataSource.setUsername("root"); //密碼 dataSource.setPassword("root"); //創建模板類 JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource); //完成數據的添加 int res = jdbcTemplate.update("insert into s_user values (null,?,?)",22,"測試人員"); } catch (Exception e) { e.printStackTrace(); } } }
使用JdbcTemplate完成增刪改查操作
s_user.java
package com.cyb.spring.test; public class s_user { private int id; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } @Override public String toString() { return "s_user [id=" + id + ", name=" + name + ", age=" + age + "]"; } private String name; private int age; }
TestjdbcTemplate.java
查詢
package com.cyb.spring.test; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.datasource.DriverManagerDataSource; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = "classpath:spring.xml") public class TestJdbcTemplate { @Autowired private JdbcTemplate jdbcTemplate; @Test public void test() { //第一個參數:執行的sql語句 //第二個參數:結果映射處理器(RowMapper) //第三個參數:sql語句中的入參 List<s_user> queryList = jdbcTemplate.query("select * from s_user",new MyBeanMapper(),null); System.out.println(queryList); } } class MyBeanMapper implements RowMapper { public s_user mapRow(ResultSet rs, int rowNum) throws SQLException { s_user user=new s_user(); user.setId(rs.getInt("id")); user.setName(rs.getString("name")); user.setAge(rs.getInt("age")); return user; } }
插入
package com.cyb.spring.test; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.datasource.DriverManagerDataSource; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = "classpath:spring.xml") public class TestJdbcTemplate { @Autowired private JdbcTemplate jdbcTemplate; @Test public void test() { try { int res = jdbcTemplate.update("insert into s_user values (null,?,?)",22,"測試人員22"); } catch (Exception e) { e.printStackTrace(); } } }
更新
package com.cyb.spring.test; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.datasource.DriverManagerDataSource; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = "classpath:spring.xml") public class TestJdbcTemplate { @Autowired private JdbcTemplate jdbcTemplate; @Test public void test() { try { int res = jdbcTemplate.update("update s_user set name=?,age=? where id=?","測試人員",19,5); } catch (Exception e) { e.printStackTrace(); } } }
刪除
package com.cyb.spring.test; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.datasource.DriverManagerDataSource; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = "classpath:spring.xml") public class TestJdbcTemplate { @Autowired private JdbcTemplate jdbcTemplate; @Test public void test() { try { int res = jdbcTemplate.update("delete from s_user where id=?",5); } catch (Exception e) { e.printStackTrace(); } } }
Spring DAO 開發之JdbcDaoSupport
案例設計
- 編寫轉賬案例(包括業務層和持久層)
- 編寫DAO時引入JdbcDaoSupport的使用
實現
項目結構圖
資料庫表欄位
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>spring-aop</groupId> <artifactId>spring-aop</artifactId> <version>0.0.1-SNAPSHOT</version> <dependencies> <!-- spring ioc組件需要的依賴包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>5.2.1.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>5.2.1.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>5.2.1.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-expression</artifactId> <version>5.2.1.RELEASE</version> </dependency> <!-- 基於AspectJ的aop依賴包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> <version>5.2.1.RELEASE</version> </dependency> <dependency> <groupId>aopalliance</groupId> <artifactId>aopalliance</artifactId> <version>1.0</version> </dependency> <!-- spring 事務管理和JDBC依賴包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>5.2.1.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.2.1.RELEASE</version> </dependency> <!-- spring 單元測試組件包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test