1.引入依赖:
aspectjweaver-1.6.8.jar
cglib-2.2.0.jar.zip
commons-logging-1.1.1.jar
druid-1.1.9.jar
hamcrest-core-1.3.jar
junit-4.12.jar
mysql-connector-java-5.1.8.jar
spring-beans-5.2.6.RELEASE.jar
spring-context-5.2.6.RELEASE.jar
spring-core-5.2.6.RELEASE.jar
spring-expression-5.2.6.RELEASE.jar
spring-jdbc-5.2.6.RELEASE.jar
spring-orm-5.2.6.RELEASE.jar
spring-tx-5.2.6.RELEASE.jar
2.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:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql:///user_db"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<context:component-scan base-package="com.my.jdbcTemplate"/>
</beans>
3.接口UserDao.java
package com.my.jdbcTemplate;
import java.util.List;
public interface UserDao {
void add(User user);
void batchAdd(List<Object[]> users);
void update(User user);
void batchUpdate(List<Object[]> users);
void delete(String userName);
void batchDelete(List<Object[]> userNames);
int queryCount(String name);
User queryObject(String name);
List<User> queryObjects(String name);
}
4.实现类
package com.my.jdbcTemplate;
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.List;
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void add(User user) {
String sql = "insert into t_user values(?,?,?)";
Object[] args = {user.getName(), user.getAge(), user.getHubby()};
int updateResult = jdbcTemplate.update(sql, args);
System.out.println("addResult: " + updateResult);
}
@Override
public void batchAdd(List<Object[]> users) {
String sql = "insert into t_user values(?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, users);
System.out.println("batchAddResult: " + ints.length);
}
@Override
public void update(User user) {
String sql = "update t_user set age=?,hubby=? where name=?";
Object[] args = {user.getAge(), user.getHubby(), user.getName()};
int updateResult = jdbcTemplate.update(sql, args);
System.out.println("updateResult: " + updateResult);
}
@Override
public void batchUpdate(List<Object[]> users) {
String sql = "update t_user set age=?,hubby=? where name=?";
int[] ints = jdbcTemplate.batchUpdate(sql, users);
System.out.println("batchUpdateResult: " + ints.length);
}
@Override
public void delete(String userName) {
String sql = "delete from t_user where name=?";
int updateResult = jdbcTemplate.update(sql, userName);
System.out.println("deleteResult: " + updateResult);
}
@Override
public void batchDelete(List<Object[]> userNames) {
String sql = "delete from t_user where name=?";
int[] updateResult = jdbcTemplate.batchUpdate(sql,userNames);
System.out.println("deleteResult: " + updateResult.length);
}
@Override
public int queryCount(String name) {
String sql = "select count(*) from t_user where name=?";
Integer integer = jdbcTemplate.queryForObject(sql, new String[]{name},int.class);
return integer;
}
@Override
public User queryObject(String name) {
String sql = "select * from t_user where name=?";
User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), name);
return user;
}
@Override
public List<User> queryObjects(String name) {
String sql = "select * from t_user where name=?";
List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class), name);
return users;
}
}
5.service类
package com.my.jdbcTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
@Autowired
private UserDao userDao;
public void addUser(User user) {
userDao.add(user);
}
public void batchAddUser(List<Object[]> users) {
userDao.batchAdd(users);
}
public void updateUser(User user) {
userDao.update(user);
}
public void batchUpdateUser(List<Object[]> users) {
userDao.batchUpdate(users);
}
public void deleteUser(String name) {
userDao.delete(name);
}
public void batchDeleteUser(List<Object[]> names) {
userDao.batchDelete(names);
}
public int queryUserCount(String name) {
return userDao.queryCount(name);
}
public User queryUserObject(String name) {
return userDao.queryObject(name);
}
public List<User> queryUserObjects(String name) {
return userDao.queryObjects(name);
}
}
6.实体类
package com.my.jdbcTemplate;
public class User {
private String name;
private int age;
private String hubby;
public User() {
}
public User(String name, int age, String hubby) {
this.name = name;
this.age = age;
this.hubby = hubby;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getHubby() {
return hubby;
}
public void setHubby(String hubby) {
this.hubby = hubby;
}
}
7.测试类
package com.my.jdbcTemplate;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class jdbcTemplateTest {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
UserService userService = context.getBean("userService", UserService.class);
userService.addUser(new User("Jay Chou",43,"Movie")); // 增
Object[] user1 = {"Lee Hom",40,"Music"};
Object[] user2 = {"Lee Hom",41,"Sing"};
Object[] user3 = {"Lee Hom",42,"Sleep"};
List<Object[]> list = new ArrayList<>();
list.add(user1);
list.add(user2);
list.add(user3);
userService.batchAddUser(list); // 批量增
User user4 = new User("Jay Chou",45,"Dau");
userService.updateUser(user4); // 改
Object[] user5 = {"Lee Hom",40,"Music"};
Object[] user6 = {"Jay Chou",41,"Sing"};
List<Object[]> list1 = new ArrayList<>();
list1.add(user5);
list1.add(user6);
userService.batchUpdateUser(list1); // 批量改
int res = userService.queryUserCount("Lee Hom"); // 查数量
System.out.println("queryCountResult: "+ res);
User user = userService.queryUserObject("Jay Chou"); // 查对象
System.out.println(user.getName()+"--"+user.getAge()+"--"+user.getHubby());
List<User> users = userService.queryUserObjects("Lee Hom"); // 查多个对象
System.out.println("queryObjectsResult: "+users.size());
/*userService.deleteUser("Lee Hom"); // 删
userService.deleteUser("Jay Chou"); // 删*/
Object[] name1 = {"Lee Hom"};
Object[] name2 = {"Jay Chou"};
List<Object[]> list2 = new ArrayList<>();
list2.add(name1);
list2.add(name2);
userService.batchDeleteUser(list2); // 批量删
}
}
8.输出结果
addResult: 1
batchAddResult: 3
updateResult: 1
batchUpdateResult: 2
queryCountResult: 3
Jay Chou–45–Dau
queryObjectsResult: 3
deleteResult: 2