CREATE TABLE `users` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`userpwd` varchar(30) NOT NULL,
PRIMARY KEY (`userid`),
UNIQUE KEY `username_uk` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
2 编写实体类
package com.star.pojo;
public class Users {
private Integer userid;
private String username;
private String userpwd;
public Users() {
}
public Users(Integer userid, String username, String userpwd) {
this.userid = userid;
this.username = username;
this.userpwd = userpwd;
}
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 String getUserpwd() {
return userpwd;
}
public void setUserpwd(String userpwd) {
this.userpwd = userpwd;
}
@Override
public String toString() {
return "Users{" +
"userid=" + userid +
", username='" + username + '\'' +
", userpwd='" + userpwd + '\'' +
'}';
}
}
3? 创建db.properdies文件（连接数据库文件）
prop.driverClass=com.mysql.jdbc.Driver
prop.url=jdbc:mysql://locahost:3306/user
prop.userName=root
prop.password=zjx666888
4 编写spring.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:util="http://www.springframework.org/schema/util"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
">
<!--开启注解扫描
1 如果扫描多个包，多个包使用逗号隔开
2 扫描包上层目录-->
<context:component-scan base-package="com.star"/>
<!--开启aop注解扫描-->
<aop:aspectj-autoproxy/>
<!--配置数据源-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/ssm"/>
<property name="username" value="root"/>
<property name="password" value="zjx666888"/>
</bean>
<!--配置jdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
5 编写dao层
//编写dao层接口
package com.star.dao;
import com.star.pojo.Users;
import java.util.List;
public interface UsersDao {
Users selectUserById(Integer id);
int addUsers(Users users);
int updateUsers(Users users);
int dropUserById(Integer id);
List<Users> selectUserAll();
void batchAdd( List<Object[]> batchArgs);
void batchUpdate( List<Object[]> batchArgs);
void batchDrop( List<Object[]> batchArgs);
}
//编写dao实现类
package com.star.dao.impl;
import com.star.dao.UsersDao;
import com.star.pojo.Users;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;
@Repository
public class UsersDaoImpl implements UsersDao {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 根据id查询用户
* @param id
* @return
*/
@Override
public Users selectUserById(Integer id) {
String sql = "select *from users where userid = ?";
Users users = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Users.class), id);
return users;
}
/**
* 添加用户
* @param users
* @return
*/
@Override
public int addUsers(Users users) {
String sql = "insert into users values(?,?,?)";
Object[] obj = {null, users.getUsername(), users.getUserpwd()};
int update = jdbcTemplate.update(sql, obj);
return update;
}
/**
* 更新用户
* @param users
* @return
*/
@Override
public int updateUsers(Users users) {
String sql = "update users set username = ?,userpwd = ? where userid = ?";
Object[] args = {users.getUsername(), users.getUserpwd(), users.getUserid()};
return jdbcTemplate.update(sql, args);
}
/**
* 删除用户
* @param id
* @return
*/
@Override
public int dropUserById(Integer id) {
String sql = "delete from users where userid = ?";
return jdbcTemplate.update(sql, id);
}
/**
* 查询所有用户
* @return
*/
@Override
public List<Users> selectUserAll() {
String sql = "select *from users";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Users.class));
}
/**
* 批量添加用户
* @param list
*/
@Override
public void batchAdd(List<Object[]> list) {
String sql = "insert into users values(?,?,?)";
jdbcTemplate.batchUpdate(sql,list);
}
/**
* 批量更新用户
* @param batchArgs
*/
@Override
public void batchUpdate( List<Object[]> batchArgs) {
String sql = "update users set username = ?,userpwd = ? where userid = ?";
jdbcTemplate.batchUpdate(sql,batchArgs);
}
/**
* 批量删除用户
* @param batchArgs
*/
@Override
public void batchDrop( List<Object[]> batchArgs) {
String sql = "delete from users where userid = ? ";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
}
6 编写service层
//编写service层接口
package com.star.service;
import com.star.pojo.Users;
public interface UsersService {
void selectUserById(Integer id);
void addUsers(Users users);
void updateUsers(Users users);
void dropUserById(Integer id);
void selectUserAll();
void addUsersBatch();
void updateUsersBatch();
void dropUserBatch();
}
//编写service层实现类
package com.star.service.impl;
import com.star.dao.UserDao;
import com.star.dao.UsersDao;
import com.star.pojo.Users;
import com.star.service.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
@Service
public class UsersServiceImpl implements UsersService {
@Autowired
private UsersDao usersDao;
@Override
public void selectUserById(Integer id) {
Users users = usersDao.selectUserById(id);
System.out.println(users);
}
@Override
public void addUsers(Users users) {
int i = usersDao.addUsers(users);
System.out.println(i);
}
@Override
public void updateUsers(Users users) {
usersDao.updateUsers(users);
}
@Override
public void dropUserById(Integer id) {
usersDao.dropUserById(id);
}
@Override
public void selectUserAll() {
System.out.println(usersDao.selectUserAll());
}
@Override
public void addUsersBatch() {
List<Object[]> list = new ArrayList<>();
Object[] o1 = {null,"111","111"};
Object[] o2 = {null,"222","222"};
Object[] o3 = {null,"333","333"};
list.add(o1);
list.add(o2);
list.add(o3);
usersDao.batchAdd(list);
}
@Override
public void updateUsersBatch() {
List<Object[]> list = new ArrayList<>();
Object[] o1 = {"aaa","aaaa",2};
Object[] o2 = {"aaa","aaaa",2};
Object[] o3 = {"aaa","aaaa",2};
list.add(o1);
list.add(o2);
list.add(o3);
usersDao.batchUpdate(list);
}
@Override
public void dropUserBatch() {
List<Object[]> list = new ArrayList<>();
Object[] o1 = {2};
Object[] o2 = {3};
Object[] o3 = {4};
list.add(o1);
list.add(o2);
list.add(o3);
usersDao.batchDrop(list);
}
}
7 编写测试
@Test
public void testSpringJdbc(){
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("classpath:spring5jdbc.xml");
UsersService bean = applicationContext.getBean(UsersService.class);
//bean.selectUserById(1); //查询
//bean.addUsers(new Users(null,"lq","zjx111")); //insert
//bean.updateUsers(new Users(1,"ywjz","ywjz")); //update
//bean.dropUserById(1); //delete
//bean.selectUserAll(); selectAll
//bean.addUsersBatch(); batchAdd
//bean.updateUsersBatch();
bean.dropUserBatch();
}