首页 > 代码库 > springJDBC01 利用springJDBC操作数据库
springJDBC01 利用springJDBC操作数据库
1 什么是springJDBC
spring通过抽象JDBC访问并一致的API来简化JDBC编程的工作量。我们只需要声明SQL、调用合适的SpringJDBC框架API、处理结果集即可。事务由Spring管理，并将JDBC受查异常转换为Spring一致的非受查异常，从而简化开发。
2 前期准备
2.1 jar包
spring-jdbc : springjdbc的包
mysql ： MySQL的驱动包
dbcp ：数据库连接池
spring-webmvc : springmvc框架包
annotation ：@resource需要用到的包，该包在Tomcat中有，如果是web项目而且运行环境是Tomcat的话就不需要导入这个包了
junit ： 单元测试包
2.2 数据库（使用mysql数据库5.4）
1 # 创建用户表 2 CREATE TABLE t_user ( 3 user_id INT AUTO_INCREMENT PRIMARY KEY, 4 user_name VARCHAR (30), 5 credits INT, 6 password VARCHAR (32), 7 last_visit DATETIME, 8 last_ip VARCHAR(23) 9 ) ENGINE = InnoDB; 10 11 # 查询t_user表的结构 12 DESC t_user; 13 14 # 创建用户登录日志表 15 CREATE TABLE t_login_log ( 16 login_log_id INT AUTO_INCREMENT PRIMARY KEY, 17 user_id INT, 18 ip VARCHAR (23), 19 login_datetime DATETIME 20 ) ENGINE = InnoDB; 21 22 #查询 t_login_log 表的结构 23 DESC t_login_log; 24 25 INSERT INTO t_user 26 (user_name, password) 27 VALUES 28 ("wys", "182838" ); 29 30 SELECT * FROM t_user;
3 环境搭建（使用的是eclipse）
3.1 利用maven导入相关jar包
1 <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"> 2 <modelVersion>4.0.0</modelVersion> 3 <groupId>cn.xiangxu</groupId> 4 <artifactId>baobaotao</artifactId> 5 <version>0.0.1-SNAPSHOT</version> 6 <packaging>war</packaging> 7 <dependencies> 8 <dependency> 9 <groupId>org.springframework</groupId> 10 <artifactId>spring-webmvc</artifactId> 11 <version>3.2.8.RELEASE</version> 12 </dependency> 13 <dependency> 14 <groupId>mysql</groupId> 15 <artifactId>mysql-connector-java</artifactId> 16 <version>5.1.37</version> 17 </dependency> 18 <dependency> 19 <groupId>org.springframework</groupId> 20 <artifactId>spring-jdbc</artifactId> 21 <version>3.2.8.RELEASE</version> 22 </dependency> 23 <dependency> 24 <groupId>commons-dbcp</groupId> 25 <artifactId>commons-dbcp</artifactId> 26 <version>1.4</version> 27 </dependency> 28 <dependency> 29 <groupId>javax.annotation</groupId> 30 <artifactId>javax.annotation-api</artifactId> 31 <version>1.2</version> 32 </dependency> 33 <dependency> 34 <groupId>junit</groupId> 35 <artifactId>junit</artifactId> 36 <version>4.12</version> 37 </dependency> 38 </dependencies> 39 </project>
3.2 创建properties文件，用于存放数据库相关信息
1 driverClassName=com.mysql.jdbc.Driver 2 url=jdbc:mysql://127.0.0.1:3306/sampledb 3 username=root 4 password=182838 5 maxActive=10 6 maxWait=3000
3.3 创建spring配置文件
3.3.1 配置properties文件的bean
3.3.2 配置数据库连接池
3.3.3 配置jdbcTemplate
3.3.4 配置组件扫描
1 <?xml version="1.0" encoding="UTF-8"?> 2 <beans xmlns="http://www.springframework.org/schema/beans" 3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" 4 xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee" 5 xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" 6 xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:util="http://www.springframework.org/schema/util" 7 xmlns:jpa="http://www.springframework.org/schema/data/jpa" 8 xsi:schemaLocation=" 9 http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd 10 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd 11 http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd 12 http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd 13 http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd 14 http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd 15 http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd 16 http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd 17 http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd"> 18 19 <!-- 读取mysql.properties文件 --> 20 <util:properties id="mysql" location="classpath:config/mysql.properties"/> 21 22 <!-- 配置连接池 --> 23 <bean id="ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> 24 <property name="driverClassName" value="#{mysql.driverClassName}" /> 25 <property name="url" value="#{mysql.url}" /> 26 <property name="username" value="#{mysql.username}" /> 27 <property name="password" value="#{mysql.password}" /> 28 </bean> 29 30 <!-- 配置jdbcTemplate --> 31 <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate"> 32 <property name="dataSource" ref="ds"></property> 33 </bean> 34 35 <!-- 组件扫描 --> 36 <context:component-scan base-package="com.baobaotao"></context:component-scan> 37 38 </beans>
注意：我们不用配置spirng的主控制器，因为我们只是操作持久层；虽然我们用到了spring容器，但是我们可以通过编写代码来启动容器
3.4 项目结构图
4 代码实现数据库操作
4.1 编写实体类
1 package com.baobaotao.entity; 2 3 import java.io.Serializable; 4 import java.util.Date; 5 6 public class User implements Serializable { 7 8 private static final long serialVersionUID = -3573627859368072117L; 9 10 private Integer userId; 11 private String userName; 12 private Integer credits; 13 private String password; 14 private Date lastVisit; 15 private String lastIp; 16 17 public User() { 18 super(); 19 // TODO Auto-generated constructor stub 20 } 21 22 public User(Integer userId, String userName, Integer credits, String password, Date lastVisit, String lastIp) { 23 super(); 24 this.userId = userId; 25 this.userName = userName; 26 this.credits = credits; 27 this.password = password; 28 this.lastVisit = lastVisit; 29 this.lastIp = lastIp; 30 } 31 32 @Override 33 public int hashCode() { 34 final int prime = 31; 35 int result = 1; 36 result = prime * result + ((lastIp == null) ? 0 : lastIp.hashCode()); 37 return result; 38 } 39 @Override 40 public boolean equals(Object obj) { 41 if (this == obj) 42 return true; 43 if (obj == null) 44 return false; 45 if (getClass() != obj.getClass()) 46 return false; 47 User other = (User) obj; 48 if (lastIp == null) { 49 if (other.lastIp != null) 50 return false; 51 } else if (!lastIp.equals(other.lastIp)) 52 return false; 53 return true; 54 } 55 public Integer getUserId() { 56 return userId; 57 } 58 public void setUserId(Integer userId) { 59 this.userId = userId; 60 } 61 public String getUserName() { 62 return userName; 63 } 64 public void setUserName(String userName) { 65 this.userName = userName; 66 } 67 public Integer getCredits() { 68 return credits; 69 } 70 public void setCredits(Integer credits) { 71 this.credits = credits; 72 } 73 public String getPassword() { 74 return password; 75 } 76 public void setPassword(String password) { 77 this.password = password; 78 } 79 public Date getLastVisit() { 80 return lastVisit; 81 } 82 public void setLastVisit(Date lastVisit) { 83 this.lastVisit = lastVisit; 84 } 85 public String getLastIp() { 86 return lastIp; 87 } 88 public void setLastIp(String lastIp) { 89 this.lastIp = lastIp; 90 } 91 92 @Override 93 public String toString() { 94 return "User [userId=" + userId + ", userName=" + userName + ", credits=" + credits + ", password=" + password 95 + ", lastVisit=" + lastVisit + ", lastIp=" + lastIp + "]"; 96 } 97 98 }
1 package com.baobaotao.entity; 2 3 import java.io.Serializable; 4 import java.util.Date; 5 6 public class LoginLog implements Serializable { 7 8 private static final long serialVersionUID = 5176708814959439551L; 9 10 private Integer loginLogId; 11 private String userId; 12 private String ip; 13 private Date loginDatetime; 14 @Override 15 public int hashCode() { 16 final int prime = 31; 17 int result = 1; 18 result = prime * result + ((ip == null) ? 0 : ip.hashCode()); 19 result = prime * result + ((loginDatetime == null) ? 0 : loginDatetime.hashCode()); 20 result = prime * result + ((loginLogId == null) ? 0 : loginLogId.hashCode()); 21 result = prime * result + ((userId == null) ? 0 : userId.hashCode()); 22 return result; 23 } 24 @Override 25 public boolean equals(Object obj) { 26 if (this == obj) 27 return true; 28 if (obj == null) 29 return false; 30 if (getClass() != obj.getClass()) 31 return false; 32 LoginLog other = (LoginLog) obj; 33 if (ip == null) { 34 if (other.ip != null) 35 return false; 36 } else if (!ip.equals(other.ip)) 37 return false; 38 if (loginDatetime == null) { 39 if (other.loginDatetime != null) 40 return false; 41 } else if (!loginDatetime.equals(other.loginDatetime)) 42 return false; 43 if (loginLogId == null) { 44 if (other.loginLogId != null) 45 return false; 46 } else if (!loginLogId.equals(other.loginLogId)) 47 return false; 48 if (userId == null) { 49 if (other.userId != null) 50 return false; 51 } else if (!userId.equals(other.userId)) 52 return false; 53 return true; 54 } 55 public Integer getLoginLogId() { 56 return loginLogId; 57 } 58 public void setLoginLogId(Integer loginLogId) { 59 this.loginLogId = loginLogId; 60 } 61 public String getUserId() { 62 return userId; 63 } 64 public void setUserId(String userId) { 65 this.userId = userId; 66 } 67 public String getIp() { 68 return ip; 69 } 70 public void setIp(String ip) { 71 this.ip = ip; 72 } 73 public Date getLoginDatetime() { 74 return loginDatetime; 75 } 76 public void setLoginDatetime(Date loginDatetime) { 77 this.loginDatetime = loginDatetime; 78 } 79 public LoginLog() { 80 super(); 81 // TODO Auto-generated constructor stub 82 } 83 public LoginLog(Integer loginLogId, String userId, String ip, Date loginDatetime) { 84 super(); 85 this.loginLogId = loginLogId; 86 this.userId = userId; 87 this.ip = ip; 88 this.loginDatetime = loginDatetime; 89 } 90 @Override 91 public String toString() { 92 return "LoginLog [loginLogId=" + loginLogId + ", userId=" + userId + ", ip=" + ip + ", loginDatetime=" 93 + loginDatetime + "]"; 94 } 95 96 }
4.2 编写UserDao接口
1 package com.baobaotao.dao; 2 3 import java.util.List; 4 5 import com.baobaotao.entity.User; 6 7 public interface UserDao { 8 /** 9 * 向用户表中添加记录 10 * @param user 用户表实体对象 11 */ 12 public void insert(User user); 13 14 /** 15 * 查询所有用户数据 16 * @return 由查询到记录组成的集合 17 */ 18 public List<User> findAll(); 19 20 }
4.3 编写UserDao接口的实现类UserDaoImpl
1 package com.baobaotao.dao; 2 3 import java.sql.ResultSet; 4 import java.sql.SQLException; 5 import java.util.List; 6 7 import javax.annotation.Resource; 8 9 import org.springframework.jdbc.core.JdbcTemplate; 10 import org.springframework.jdbc.core.RowMapper; 11 import org.springframework.stereotype.Repository; 12 13 import com.baobaotao.entity.User; 14 15 @Repository("userDao") 16 public class UserDaoImpl implements UserDao { 17 18 @Resource(name="jt") 19 private JdbcTemplate jt; 20 21 public void insert(User user) { 22 23 String sql = "INSERT INTO t_user " + 24 "(user_name, password) " + 25 "VALUES " + 26 "(?, ?) "; 27 Object [] args = {user.getUserName(), user.getPassword()}; 28 Integer num = jt.update(sql, args); 29 if(num > 0) { 30 System.out.println("插入数据成功"); 31 } else { 32 System.out.println("插入数据失败"); 33 } 34 35 } 36 37 public List<User> findAll() { 38 String sql = "SELECT * FROM t_user "; 39 List<User> users = jt.query(sql, new UserRowMapper()); 40 return users; 41 } 42 43 /** 44 * 结果集处理：经每一条查询记录转变成一个实体对象 45 * @author 三少 46 * 47 */ 48 class UserRowMapper implements RowMapper<User> { 49 50 public User mapRow(ResultSet rs, int rowNum) throws SQLException { 51 User user = new User(); 52 user.setUserId(rs.getInt("user_id")); 53 user.setUserName(rs.getString("user_name")); 54 user.setCredits(rs.getInt("credits")); 55 user.setPassword(rs.getString("password")); 56 user.setLastVisit(rs.getDate("last_visit")); 57 user.setLastIp(rs.getString("last_ip")); 58 return user; 59 } 60 } 61 62 }
使用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 编写测试方法
1 package testDao; 2 3 import java.util.List; 4 5 import org.junit.Before; 6 import org.junit.Test; 7 import org.springframework.context.ApplicationContext; 8 import org.springframework.context.support.ClassPathXmlApplicationContext; 9 10 import com.baobaotao.dao.UserDao; 11 import com.baobaotao.entity.User; 12 13 public class TestDao { 14 15 private ApplicationContext ac; 16 private UserDao userDao; 17 18 @Before 19 public void init() { 20 ac = new ClassPathXmlApplicationContext("config/spring_mysql.xml"); // 启动容器 21 System.out.println(ac); 22 23 userDao = ac.getBean("userDao", UserDao.class); // 利用容器创建对象 24 System.out.println(userDao); 25 } 26 27 /** 28 * 测试插入数据 29 */ 30 @Test 31 public void test01() { 32 User user = new User(); 33 user.setUserName("wym"); 34 user.setPassword("111"); 35 36 userDao.insert(user); 37 } 38 39 /** 40 * 测试查询所有数据 41 */ 42 @Test 43 public void test02() { 44 List<User> users = userDao.findAll(); 45 for(User user : users) { 46 System.out.println(user); 47 } 48 } 49 50 51 }
springJDBC01 利用springJDBC操作数据库
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。