目录
1. 传统的jdbc和JdbcTemplate的对比
JDBC(Java DataBase Connectivity,java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。JDBC连接数据库的步骤如下:
driverClassName=com.mysql.jdbc.Driver
jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
username=root
password=123456
1. 打开数据库连接, 2. 预编译并执行SQL语句,3. 遍历查询结果(如果需要的话), 4. 处理抛出的任何异常,5. 处理事务,6. 关闭数据库连接。这些过程中存在大量的try...catch...finally语句,造成了代码的泛滥,导致代码的可读性下降。
JDBCTemplate就是Spring对JDBC的封装,通俗点说就是Spring对jdbc的封装的模板。使用spring的注入功能,可以把DataSource注册到JdbcTemplate之中,相比较于传统的JDBC,大大简化了数据库的操作。
SpringIOC容器将管理数据库连接的数据源当作普通Java Bean一样管理,然后将数据源注入封装类JdbcTemplate中,JdbcTemplate的dataSource属性就是注入配置的数据源。
2. 数据源的配置
常用的几种实现方式有:
1、从JNDI获得DataSource(在服务器配置文件中配置)
2、第三方的连接池获得DataSource,Spring在第三方依赖包中包含了两个数据源的实现类包,其一是Apache的DBCP,其二是 C3P0,在xml中分别对这两个jar进行配置,都可获得数据源DataSource。
使用dbcp需要导入两个相关jar包: commons-dbcp-1.2.1.jar;commons-pool-1.3.jar
数据库jar:mysql-connector-java-5.1.7-bin.jar
事物相关jar:spring-tx-4.2.4.RELEASE.jar
commons-logging.jar
spring-beans-4.2.4.RELEASE.jar
spring-context-4.2.4.RELEASE.jar
spring-core-4.2.4.RELEASE.jar
spring-expression-4.2.4.RELEASE.jar
spring-aop-4.2.4.RELEASE.jar
spring-aspects-4.2.4.RELEASE.jar
spring-web-4.2.4.RELEASE.jar
spring-webmvc-4.2.4.RELEASE.jar
配置web.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" version="3.1">
<display-name>JavaWeb-Day06</display-name>
<welcome-file-list>
<welcome-file>index.do</welcome-file>
</welcome-file-list>
<!-- 配置前端控制器 -->
<servlet>
<servlet-name>springmvc</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:config/applicationContext.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup><!-- 当启动的时候加载 -->
</servlet>
<servlet-mapping>
<servlet-name>springmvc</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
<!-- 在web.xml配置编码处理的过滤器 ,解决中文乱码问题 -->
<filter>
<filter-name>encodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>encodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<session-config>
<session-timeout>30</session-timeout>
</session-config>
</web-app>
配置jdbc.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/javaweb_day6
username=root
password=123456
maxActive=1000
配置applicationContext.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:context="http://www.springframework.org/schema/context"
xmlns:util="http://www.springframework.org/schema/util"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.3.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util-4.3.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd">
<!-- 扫描注解 -->
<context:component-scan
base-package="com.chtw.*"></context:component-scan>
<!-- 用于扫描RequestMapping -->
<mvc:annotation-driven />
<!-- 视图解析器 -->
<bean id=""
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/jsp/" />
<property name="suffix" value=".jsp" />
</bean>
<!-- 引入本地文件 -->
<util:properties id="jdbc" location="classpath:config/jdbc.properties"/>
<!-- 配置dbcp数据源;close()方法关闭数据源,以便Spring容器关闭时,数据源能够正常关闭-->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="#{jdbc.driver}"/>
<property name="url" value="#{jdbc.url}"/>
<property name="username" value="#{jdbc.username}"/>
<property name="password" value="#{jdbc.password}"/>
<property name="maxActive" value="#{jdbc.maxActive}"/>
</bean>
</beans>
C3P0配置数据源,在applicationContext.xml中配置
<util:properties id="prop" location="classpath:jdbc.properties"/>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="#{jdbc.driver}"/>
<property name="jdbcUrl" value="#{jdbc.url}"/>
<property name="user" value="#{jdbc.username}"/>
<property name="password" value="#{jdbc.password}"/>
</bean>
3、使用DriverManagerDataSource获得DataSource。(没有使用数据库连接池,一般使用)
3. 使用jdbcTemplate实现数据库CURD操作
实例化jdbcTemplate:在applicationcOntext.xml添加配置
<!-- 配置jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
jdbcTemplate的常见方法介绍:
1、queryForObject() 查询单个数据
2、query() 查询多个数据,返回一个list
3、update、batchUpdate方法:update方法用于执行新增、修改、删除等语句;batchUpdate方法用于执行批处理相关语句;
4、execute方法: 可以用于执行任何SQL语句,一般用于执行DDL语句
创建一个实体类实体类User
package com.chtw.entity;
public class User {
private int userId;
private String userName;
private String password;
private String phoneNumber;
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getPhoneNumber() {
return phoneNumber;
}
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
}
创建一个映射类UserMapper,用于将实体类和数据库的字段一一对应
package com.chtw.mapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Controller;
import com.chtw.entity.User;
@Controller
public class UserMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int i) throws SQLException {
User user = new User();
user.setUserId(rs.getInt("userId"));
user.setUserName(rs.getString("userName"));
user.setPassword(rs.getString("password"));
user.setPhoneNumber(rs.getString("phoneNumber"));
//rs.close();
System.out.println("i="+i);
return user;
}
}
书写DAO层,放我们的一些方法
package com.chtw.dao;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import com.chtw.entity.User;
import com.chtw.mapper.UserMapper;
@Repository //DAO的注解类
public class UserDAO {
@Autowired
private JdbcTemplate jt;
@Autowired
private UserMapper userMapper;
public List<User> getAll(){
String sql = "select * from user";
Object[] obj = {};
//参数(sql语句,?参数,映射类)
List<User> uList = jt.query(sql,obj,userMapper);//可执行任何SQL
return uList;
}
public User getOne() {
String sql = "select * from user where userId =?";
Object[] obj = {1001};
User user = jt.queryForObject(sql, obj, userMapper);
return user;
}
public int delete(int userId) {
String sql = "delete from user where userId = ?";
Object[] obj = {1001};
int count = jt.update(sql,obj);
return count;
}
}
创建一个数据库javaweb_day6
create database javaweb_day6;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`userId` int(11) NOT NULL AUTO_INCREMENT,
`userName` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`phoneNumber` varchar(255) DEFAULT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('1001', 'Alice', '123456', '123');
INSERT INTO `user` VALUES ('1002', 'Bob', '654321', '132');
INSERT INTO `user` VALUES ('1003', 'Clover', 'abcdef', '345');
写一个测试接口IndexController
package com.chtw.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.chtw.dao.UserDAO;
import com.chtw.entity.User;
@Controller
public class IndexController {
@Autowired
private UserDAO userDAO;
@RequestMapping("/index")
@ResponseBody
public List<User> index(Model model) {
List<User> uList = userDAO.getAll();
model.addAttribute("uList",uList);
for(User user:uList) {
System.out.println(user.getUserId()+"--"+user.getUserName());
}
return uList;
}
}
启动服务器,访问localhost/你的项目名/index.do
数据库数据
本人联系方式2329095893,欢迎各位进行学习讨论
欢迎关注熊熊出没ING公众号,不定时跟新Java、python、信息安全等相关知识哦。