SpringJDBC起步
1.首先在IDEA中新建一个web项目。
然后给自己的项目起一个名字即可,并放入相应目录,我这里项目名称为“springJDBC”.
2.整理项目目录结构
用模板搭建的项目目录可能不适合我们的需求,需要进行微调。
3.引入依赖项
springJDBC项目需要引入一些常见的依赖项。在项目的pom.xml文件加入依赖。注意放在标签内部。
<!-- jdbc包-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.10.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.2.10.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
4.jdbc配置
db.properties文件:
mysql.className = com.mysql.cj.jdbc.Driver
mysql.url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT
mysql.userName = root
mysql.password = root
spring.xml文件中:
<!--组件扫描器-->
<context:component-scan base-package="org.example">
</context:component-scan>
<!--读取配置文件-->
<context:property-placeholder location="db.properties"/>
<!--配置数据源-->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<!--注入数据库连接的必须的字符串-->
<property name="driverClassName" value="${mysql.className}"/>
<property name="url" value="${mysql.url}"/>
<property name="username" value="${mysql.userName}"/>
<property name="password" value="${mysql.password}"/>
</bean>
<!--配置JdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--将连接数据库时使用的数据源对象,注入到JDBCTemplate对象中-->
<property name="dataSource" ref="dataSource"/>
</bean>
5.建立Java类
po:实体类
dao:持久化层 与
service:业务逻辑层
contoller:控制层
User.java
package org.example.po;
public class User {
private Integer id;
private String name;
private String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", password='" + password + '\'' +
'}';
}
}
UserDao.java:
package org.example.dao;
import org.example.po.User;
import java.util.List;
public interface UserDao {
void addUser(User user);
void deleteUser(int i);
void updateUser(User user);
List<User> selectAll();
User selectById(int id);
User selsctByName(String name);
}
UserDaoImpl.java:
package org.example.dao;
import org.example.po.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public class UserDaoImpl implements UserDao {
//直接和sql语句有关
@Autowired
JdbcTemplate jdbcTemplate ;
@Override
public void addUser(User user) {
String sql = "insert into user(name,password) values(?,?)";
jdbcTemplate.update(sql,user.getName(),user.getPassword());
}
@Override
public void deleteUser(int i) {
String sql = "delete from user where id=?";
jdbcTemplate.update(sql,i);
}
@Override
public void updateUser(User user) {
String sql = "update user set name = ? , password=? where id=?";
jdbcTemplate.update(sql,user.getName(),user.getPassword(),user.getId());
}
@Override
public List<User> selectAll() {
String sql = "select id,name,password from user order by id desc";
RowMapper<User> rowMapper =new BeanPropertyRowMapper<>(User.class);
List<User> list = jdbcTemplate.query(sql,rowMapper);
return list;
}
@Override
public User selectById(int id) {
String sql = "select id,name,password from user where id=?";
RowMapper<User> rowMapper =new BeanPropertyRowMapper<>(User.class);
User user = jdbcTemplate.queryForObject(sql,rowMapper,id);
return user;
}
@Override
public User selsctByName(String name) {
String sql = "select id,password from user where name=?";
RowMapper<User> rowMapper =new BeanPropertyRowMapper<>(User.class);
User user = jdbcTemplate.queryForObject(sql,rowMapper,name);
return user;
}
}
UserService.java:
package org.example.service;
import org.example.po.User;
import java.util.List;
public interface UserService {
void addUser(User user);
void deleteUser(int i);
void updateUser(User user);
List<User> selectAll();
User selectById(int id);
User selsctByName(String name);
}
UserServiceImpl:
package org.example.service;
import org.example.dao.UserDao;
import org.example.po.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
@Override
public void addUser(User user) {
userDao.addUser(user);
}
@Override
public void deleteUser(int i) {
userDao.deleteUser(i);
}
@Override
public void updateUser(User user) {
userDao.updateUser(user);
}
@Override
public List<User> selectAll() {
return userDao.selectAll();
}
@Override
public User selectById(int id) {
return userDao.selectById(id);
}
@Override
public User selsctByName(String name) {
return userDao.selsctByName(name);
}
}
UserController.java
package org.example.controller;
import org.example.po.User;
import org.example.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import java.util.List;
@Controller
public class UserController {
@Autowired
private UserService userService;
public void addUser(User user){
userService.addUser(user);
}
public void deleteUser(int i){
userService.deleteUser(i);
}
public void updateUser(User user){
userService.updateUser(user);
}
public List<User> selectAll(){
return userService.selectAll();
}
public User selectById(int id){
return userService.selectById(id);
}
public User selsctByName(String name){
return userService.selsctByName(name);
}
}
6.编写单元测试类
JDBCTest.java:
package org.example;
import org.example.controller.UserController;
import org.example.po.User;
import org.junit.Before;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.List;
public class JDBCTest {
ApplicationContext ac;
@Before
public void before(){
ac = new ClassPathXmlApplicationContext("spring.xml");
}
@Test
public void testAdd(){
User user = new User();
user.setName("孙八");
user.setPassword("123456");
UserController userController = ac.getBean(UserController.class);
userController.addUser(user);
}
@Test
public void testDelete(){
UserController userController = ac.getBean(UserController.class);
userController.deleteUser(3);
}
@Test
public void testSeleceAll(){
UserController userController = ac.getBean(UserController.class);
List<User> list = userController.selectAll();
for (User user : list) {
System.out.println(user);
}
}
@Test
public void testSelectById(){
UserController userController = ac.getBean(UserController.class);
int id=3;
User user = userController.selectById(id);
System.out.println(user);
}
@Test
public void testSelectByName(){
UserController userController = ac.getBean(UserController.class);
String name="张三";
User user = userController.selsctByName(name);
System.out.println(user);
}
}
7.运行
测试结果: