springJDBC01 利用springJDBC操作数据库
1 什么是springJDBC
spring通过抽象JDBC访问并一致的API来简化JDBC编程的工作量。我们只需要声明SQL、调用合适的SpringJDBC框架API、处理结果集即可。事务由Spring管理,并将JDBC受查异常转换为Spring一致的非受查异常,从而简化开发。
利用传统的jdbc操作数据库的步骤:获取连接→创建Statement→执行数据操作→获取结果→关闭Statement→关闭结果集→关闭连接;而Spring JDBC通过一个模板类org.springframework. jdbc.core.JdbcTemplate封装了样板式的代码,用户通过模板类就可以轻松地完成大部分数据访问的操作。
2 前期准备
2.1 jar包
spring-jdbc : springjdbc的包
mysql : MySQL的驱动包
dbcp :数据库连接池
spring-webmvc : springmvc框架包
annotation :@resource需要用到的包,该包在Tomcat中有,如果是web项目而且运行环境是Tomcat的话就不需要导入这个包了
junit : 单元测试包
2.2 数据库(使用mysql数据库5.4)
# 创建用户表
CREATE TABLE t_user (
user_id INT AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR (30),
credits INT,
password VARCHAR (32),
last_visit DATETIME,
last_ip VARCHAR(23)
) ENGINE = InnoDB; # 查询t_user表的结构
DESC t_user; # 创建用户登录日志表
CREATE TABLE t_login_log (
login_log_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
ip VARCHAR (23),
login_datetime DATETIME
) ENGINE = InnoDB; #查询 t_login_log 表的结构
DESC t_login_log; INSERT INTO t_user
(user_name, password)
VALUES
("wys", "182838" ); SELECT * FROM t_user;
相关表
3 环境搭建(使用的是eclipse)
3.1 利用maven导入相关jar包
<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>cn.xiangxu</groupId>
<artifactId>baobaotao</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>3.2.8.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.37</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>3.2.8.RELEASE</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>javax.annotation</groupId>
<artifactId>javax.annotation-api</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
</project>
pom.xml
3.2 创建properties文件,用于存放数据库相关信息
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/sampledb
username=root
password=182838
maxActive=10
maxWait=3000
mysql.properties
3.3 创建spring配置文件
3.3.1 配置properties文件的bean
3.3.2 配置数据库连接池
3.3.3 配置jdbcTemplate
3.3.4 配置组件扫描
<?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:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:util="http://www.springframework.org/schema/util"
xmlns:jpa="http://www.springframework.org/schema/data/jpa"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd"> <!-- 读取mysql.properties文件 -->
<util:properties id="mysql" location="classpath:config/mysql.properties"/> <!-- 配置连接池 -->
<bean id="ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="#{mysql.driverClassName}" />
<property name="url" value="#{mysql.url}" />
<property name="username" value="#{mysql.username}" />
<property name="password" value="#{mysql.password}" />
</bean> <!-- 配置jdbcTemplate -->
<bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="ds"></property>
</bean> <!-- 组件扫描 -->
<context:component-scan base-package="com.baobaotao"></context:component-scan> </beans>
spring_mysql.xml
注意:我们不用配置spirng的主控制器,因为我们只是操作持久层;虽然我们用到了spring容器,但是我们可以通过编写代码来启动容器
3.4 项目结构图
4 代码实现数据库操作
4.1 编写实体类
package com.baobaotao.entity; import java.io.Serializable;
import java.util.Date; public class User implements Serializable { private static final long serialVersionUID = -3573627859368072117L; private Integer userId;
private String userName;
private Integer credits;
private String password;
private Date lastVisit;
private String lastIp; public User() {
super();
// TODO Auto-generated constructor stub
} public User(Integer userId, String userName, Integer credits, String password, Date lastVisit, String lastIp) {
super();
this.userId = userId;
this.userName = userName;
this.credits = credits;
this.password = password;
this.lastVisit = lastVisit;
this.lastIp = lastIp;
} @Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((lastIp == null) ? 0 : lastIp.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
User other = (User) obj;
if (lastIp == null) {
if (other.lastIp != null)
return false;
} else if (!lastIp.equals(other.lastIp))
return false;
return true;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public Integer getCredits() {
return credits;
}
public void setCredits(Integer credits) {
this.credits = credits;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getLastVisit() {
return lastVisit;
}
public void setLastVisit(Date lastVisit) {
this.lastVisit = lastVisit;
}
public String getLastIp() {
return lastIp;
}
public void setLastIp(String lastIp) {
this.lastIp = lastIp;
} @Override
public String toString() {
return "User [userId=" + userId + ", userName=" + userName + ", credits=" + credits + ", password=" + password
+ ", lastVisit=" + lastVisit + ", lastIp=" + lastIp + "]";
} }
User.java
package com.baobaotao.entity; import java.io.Serializable;
import java.util.Date; public class LoginLog implements Serializable { private static final long serialVersionUID = 5176708814959439551L; private Integer loginLogId;
private String userId;
private String ip;
private Date loginDatetime;
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((ip == null) ? 0 : ip.hashCode());
result = prime * result + ((loginDatetime == null) ? 0 : loginDatetime.hashCode());
result = prime * result + ((loginLogId == null) ? 0 : loginLogId.hashCode());
result = prime * result + ((userId == null) ? 0 : userId.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
LoginLog other = (LoginLog) obj;
if (ip == null) {
if (other.ip != null)
return false;
} else if (!ip.equals(other.ip))
return false;
if (loginDatetime == null) {
if (other.loginDatetime != null)
return false;
} else if (!loginDatetime.equals(other.loginDatetime))
return false;
if (loginLogId == null) {
if (other.loginLogId != null)
return false;
} else if (!loginLogId.equals(other.loginLogId))
return false;
if (userId == null) {
if (other.userId != null)
return false;
} else if (!userId.equals(other.userId))
return false;
return true;
}
public Integer getLoginLogId() {
return loginLogId;
}
public void setLoginLogId(Integer loginLogId) {
this.loginLogId = loginLogId;
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getIp() {
return ip;
}
public void setIp(String ip) {
this.ip = ip;
}
public Date getLoginDatetime() {
return loginDatetime;
}
public void setLoginDatetime(Date loginDatetime) {
this.loginDatetime = loginDatetime;
}
public LoginLog() {
super();
// TODO Auto-generated constructor stub
}
public LoginLog(Integer loginLogId, String userId, String ip, Date loginDatetime) {
super();
this.loginLogId = loginLogId;
this.userId = userId;
this.ip = ip;
this.loginDatetime = loginDatetime;
}
@Override
public String toString() {
return "LoginLog [loginLogId=" + loginLogId + ", userId=" + userId + ", ip=" + ip + ", loginDatetime="
+ loginDatetime + "]";
} }
LoginLog.java
4.2 编写UserDao接口
package com.baobaotao.dao; import java.util.List; import com.baobaotao.entity.User; public interface UserDao {
/**
* 向用户表中添加记录
* @param user 用户表实体对象
*/
public void insert(User user); /**
* 查询所有用户数据
* @return 由查询到记录组成的集合
*/
public List<User> findAll(); }
UserDao.java
4.3 编写UserDao接口的实现类UserDaoImpl
package com.baobaotao.dao; import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List; import javax.annotation.Resource; import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository; import com.baobaotao.entity.User; @Repository("userDao")
public class UserDaoImpl implements UserDao { @Resource(name="jt")
private JdbcTemplate jt; public void insert(User user) { String sql = "INSERT INTO t_user " +
"(user_name, password) " +
"VALUES " +
"(?, ?) ";
Object [] args = {user.getUserName(), user.getPassword()};
Integer num = jt.update(sql, args);
if(num > 0) {
System.out.println("插入数据成功");
} else {
System.out.println("插入数据失败");
} } public List<User> findAll() {
String sql = "SELECT * FROM t_user ";
List<User> users = jt.query(sql, new UserRowMapper());
return users;
} /**
* 结果集处理:经每一条查询记录转变成一个实体对象
* @author 三少
*
*/
class UserRowMapper implements RowMapper<User> { public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setUserId(rs.getInt("user_id"));
user.setUserName(rs.getString("user_name"));
user.setCredits(rs.getInt("credits"));
user.setPassword(rs.getString("password"));
user.setLastVisit(rs.getDate("last_visit"));
user.setLastIp(rs.getString("last_ip"));
return user;
}
} }
UserDaoImpl
使用springJDBC操作数据库时,程序员只需要编写相关的sql语句,待定参数可以用?代替,然后调用JdbcTemplate类的相关方法来执行sql语句就行啦。
JdbcTemplate类中的主要方法
execute方法:可以用于执行任何SQL语句,一般用于执行DDL语句;
update方法及batchUpdate方法:update方法用于执行新增、修改、删除等语句;
batchUpdate方法用于执行批处理相关语句;
query方法及queryForXXX方法:用于执行查询相关语句
call方法:用于执行存储过程、函数相关语句。
注意:
使用query方法时有三个参数
参数1 sql语句字符串
参数2 未知参数组
参数3 查询结果处理(就是讲每一条查询记录变成一个实体对象,三少的一般做法是编写一个实现了RowMapper接口的内部类,然后创建一个该内部类对象来作为参数3)
4.4 项目结构图
5 测试类
5.1 测试前需要启动spring容器,因为我们的代码中使用到了spring容器的功能
5.2 编写测试方法
package testDao; import java.util.List; import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext; import com.baobaotao.dao.UserDao;
import com.baobaotao.entity.User; public class TestDao { private ApplicationContext ac;
private UserDao userDao; @Before
public void init() {
ac = new ClassPathXmlApplicationContext("config/spring_mysql.xml"); // 启动容器
System.out.println(ac); userDao = ac.getBean("userDao", UserDao.class); // 利用容器创建对象
System.out.println(userDao);
} /**
* 测试插入数据
*/
@Test
public void test01() {
User user = new User();
user.setUserName("wym");
user.setPassword("111"); userDao.insert(user);
} /**
* 测试查询所有数据
*/
@Test
public void test02() {
List<User> users = userDao.findAll();
for(User user : users) {
System.out.println(user);
}
} }
测试类
最新文章
- Learn ZYNC (6)
- JQuery基础总结上
- JSON字符串——后台解析系列
- backbone调试
- IMP数据到指定的表空间
- Shell_Shell调用SQLPlus简介(案例)
- imageNamed 与 imageWithContentsOfFile的区别
- Dagger2 scope
- jquery cookie 用法
- 常用的PL/SQL开发原则
- 生产环境下JAVA进程高CPU占用故障排查
- VS2008/MFC —常用控件使用总结 转载
- No http handler was found for request type &#39;GET&#39;
- IM应用中的优化
- mybatis 打印SQL语句
- java 字符串大小比较
- SqlServer 字段拼接
- Chapter_4_JAVA作业
- 算法练习:求字符串的最长重复子串(Java实现)
- Windows下用PIP安装scipy出现no lapack/blas resources found