SpringJDBC模板的使用
spring對持久層也提供了解決方案
spring提供了很多的模板用于簡化開發(fā)
JDBC模板的簡單使用
1.導(dǎo)包
spring依賴文件+spring輔助文件+mysql相關(guān)+jdbc相關(guān)
<!-- spring依賴的核心文件 -->
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<!-- spring的輔助文件 -->
<!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
2.測試
public class Jdbcdemo1 {
@Test
public void demo1(){
//創(chuàng)建連接詞
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8");
dataSource.setUsername("root");
dataSource.setPassword("root");
//創(chuàng)建JDBC模板
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.update("insert into users values(?,?,?)","10","dff","asd");
}
}
將連接池交給spring管理
在上文的基礎(chǔ)上進(jìn)行改進(jìn)呜达，將數(shù)據(jù)庫的連接交給spring管理
1.新建xml文件
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<!-- 屬性注入 8.幾的版本 com.mysql.cj.jdbc.Driver-->
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/test"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
<!-- 配置Spring的JDBC的模板 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
2.測試
//將jdbc注入到數(shù)據(jù)中，也可以再xml文件里用bean配置 但是要有jdbcTemplate的set方法
@Resource(name="jdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Test
public void demo1(){
//update執(zhí)行增刪改
jdbcTemplate.update("insert into users values(?,?,?)","11","11","11");
}
使用開源的數(shù)據(jù)庫連接池
DBCP連接池
1.引入jar包
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-pool2 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>2.4.2</version>
</dependency>
2.配置XML文件
<!-- 配置DBCP的連接池 -->
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/test"></property>
<property name="username" value="root"></property>
<property name="password" value="ro"></property>
</bean>
C3P0連接池
1.引入jar包
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
2.在XML文件中配置
<!-- 配置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/test"></property>
<property name="user" value="root"></property>
<property name="password" value="root"></property>
</bean>
將參數(shù)配置到屬性文件
以C3P0連接池為例
1.新建一個文件(jdbc.properties) 配置
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=ro
2.文件引入到xml文件中
第一種方式（較少）
<!-- 第一種方式 -->
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:jdbc.properties"></property>
</bean>
第二種方式：
<!-- 第二種豌蟋，通過context標(biāo)簽 -->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!-- 配置c3p0連接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driverClass}"></property>
<property name="jdbcUrl" value="${jdbc.url}"></property>
<property name="user" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
對數(shù)據(jù)庫的數(shù)據(jù)進(jìn)行操作
以C3P0連接池為例
根據(jù)數(shù)據(jù)查詢
@Test
public void demo2() {
String name=jdbcTemplate.queryForObject("select name from users where id= ? ", String.class,10);
System.out.println(name);
}
查詢總數(shù)
@Test
public void demo3() {
Long count = jdbcTemplate.queryForObject("select count(*) from users", Long.class);
System.out.println(count);
}
單個查詢
在返回值是自定義實體類時愉棱，需要使用RowMapper<自定義實體類>接口
1.編寫對應(yīng)實體類
2.在測試類中
class MyRowMapper implements RowMapper<Users>{
@Override
public Users mapRow(ResultSet rs, int arg1rowNum) throws SQLException {
// TODO 自動生成的方法存根
Users user = new Users();
user.setId(rs.getString("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
return user;
}
}
3.測試
@Test
public void demo4() {
Users user = jdbcTemplate.queryForObject("select * from users where id= ?",new MyRowMapper(),10);
System.out.println(user);
}
如果不想采用上文接口的方式，可以在測試類中：
@Test
public void demo4() {
RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.calss);
Users user = jdbcTemplate.queryForObject("select * from users where id= ?",new MyRowMapper(),10);
System.out.println(user);
}
全部查詢
@Test
public void demo5() {
List<Users> list = jdbcTemplate.query("select * from users", new MyRowMapper());
for(Users user:list) {
System.out.println(user);
}
