SpringJDBC模板的使用
spring对持久层也提供了解决方案
spring提供了很多的模板用于简化开发
JDBC模板的简单使用
1.导包
spring依赖文件+spring辅助文件+mysql相关+jdbc相关
<!-- 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(){
//创建连接词
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");
//创建JDBC模板
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.update("insert into users values(?,?,?)","10","dff","asd");
}
}
将连接池交给spring管理
在上文的基础上进行改进，将数据库的连接交给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注入到数据中，也可以再xml文件里用bean配置 但是要有jdbcTemplate的set方法
@Resource(name="jdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Test
public void demo1(){
//update执行增删改
jdbcTemplate.update("insert into users values(?,?,?)","11","11","11");
}
使用开源的数据库连接池
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>
将参数配置到属性文件
以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标签 -->
<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>
对数据库的数据进行操作
以C3P0连接池为例
根据数据查询
@Test
public void demo2() {
String name=jdbcTemplate.queryForObject("select name from users where id= ? ", String.class,10);
System.out.println(name);
}
查询总数
@Test
public void demo3() {
Long count = jdbcTemplate.queryForObject("select count(*) from users", Long.class);
System.out.println(count);
}
单个查询
在返回值是自定义实体类时，需要使用RowMapper<自定义实体类>接口
1.编写对应实体类
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);
}
网友评论