Java Spring5学习之JdbcTemplate详解

Java Spring5学习之JdbcTemplate详解

一、JdbcTemplate

Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作

二、实战

2.1 引入依赖

mysql

mysql-connector-java

8.0.24

org.springframework

spring-jdbc

5.3.6

org.springframework

spring-orm

5.3.6

org.springframework

spring-tx

5.3.6

2.2 配置连接池

location="classpath:jdbc.properties" />

class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">

2.3 配置JdbcTemplate 对象，注入 DataSource

class="org.springframework.jdbc.core.JdbcTemplate">

2.4 扫描注解

base-package="cn.zj.aop.an">

2.5 创建 service 类，创建 dao 类，在 dao 注入 jdbcTemplate 对象

@Repository

public class UserDaoImpl implements UserDao {

//注入 JdbcTemplate

@Autowired

private JdbcTemplate jdbcTemplate;

}

@Service

public class UserService {

// 注入 dao

@Autowired

private UserDao userDao;

}

三、操作(CRUD)

实体类

public class User {

private String userId;

private String username;

private String ustatus;

@Override

public String toString() {

return "User [userId=" + userId + ", username=" + username + ", ustatus=" + ustatus + "]";

}

public String getUserId() {

return userId;

}

public void setUserId(String userId) {

this.userId = userId;

}

public String getUsername() {

return username;

}

public void setUsername(String username) {

this.username = username;

}

public String getUstatus() {

return ustatus;

}

public void setUstatus(String ustatus) {

this.ustatus = ustatus;

}

}

3.1 添加

service

//添加

public void addUser(User user) {

userDao.add(user);

}

dao

@Override

public void add(User user) {

// 1 创建 sql 语句

String sql = "insert into t_user values(?,?,?)";

// 2 调用方法实现

Object[] args = { user.getUserId(), user.getUsername(), user.getUstatus() };

int update = jdbcTemplate.update(sql, args);

System.out.println(update);

}

测试

@Test

public void test1() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

User user = new User();

user.setUserId("1");

user.setUsername("java");

user.setUstatus("a");

userService.addUser(user);

}

结果

3.2 修改

service

//修改

public void updateUser(User user) {

userDao.updateUser(user);

}

dao

@Override

public void updateUser(User user) {

// TODO Auto-generated method stub

String sql = "update t_user set username=?,ustatus=? where userId=?";

// 2 调用方法实现

Object[] args = { user.getUsername(), user.getUstatus() ,user.getUserId()};

int update = jdbcTemplate.update(sql, args);

System.out.println(update);

}

测试

@Test

public void test2() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

User user = new User();

user.setUserId("1");

user.setUsername("javaScrip");

user.setUstatus("abc");

userService.updateUser(user);

}

3.3 删除

// 删除

public void deleteUser(String id) {

userDao.deleteUser(id);

}

@Override

public void deleteUser(String id) {

String sql="delete from t_user where userId=?";

int update=jdbcTemplate.update(sql, id);

System.out.println(update);

}

@Test

public void test3() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

userService.deleteUser("1");

}

四、查询

4.1 查询总记录数 jdbcTemplate.queryForObject

@Test

public void test4() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

int count = userService.selectUserCount();

System.out.println("数据库中共有记录:"+count);

}

//查询记录数

public int selectUserCount() {

return userDao.selectCount();

}

@Override

public int selectCount() {

String sql = "select count(0) from t_user";

Integer count = jdbcTemplate.queryForObject(sql, Integer.class);

return count;

}

4.2 查询返回对象

@Test

public void test5() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

User user=userService.findUserInfo("1");

System.out.println(user);

}

//查询对象

public User findUserInfo(String id) {

// TODO Auto-generated method stub

return userDao.findUserInfo(id);

}

@Override

public User findUserInfo(String id) {

String sql = "select userId,username,ustatus from t_user where userId=?";

User user = jdbcTemplate.queryForObject(sql, new

BeanPropertyRowMapper(User. class),id);

return user;

}

4.3 查询返回集合

@Test

public void test6() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

List list=userService.findAllUser();

System.out.println(list);

}

//查询返回集合

public List findAllUser(){

return userDao.findAllUser();

}

@Override

public List findAllUser() {

String sql = "select userId,username,ustatus from t_user";

List list = jdbcTemplate.query(sql, new

BeanPropertyRowMapper(User. class));

return list;

}

五、批量操作 jdbcTemplate.batchUpdate

5.1 添加

@Test

public void test7() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

List

Object[] o1 = { "11", "易语言", "中文"};

Object[] o2 = { "12", "c++", "cc"};

Object[] o3 = { "13", "MySQL", "数据库"};

list.add(o1);

list.add(o2);

list.add(o3);

userService.batchAdd(list);

}

//批量添加

public void batchAdd(List

userDao.batchAdd(list);

}

@Override

public void batchAdd(List

String sql = "insert into t_user values(?,?,?)";

int[] ints = jdbcTemplate.batchUpdate(sql, list);

System.out.println(ints);

}

5.2 修改

@Test

public void test8() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

List

Object[] o1 = { "易语言易", "中文语言","11"};

Object[] o2 = { "c++c", "ccccc","12"};

Object[] o3 = {"MySQL+ORACle", "数据库数据", "13"};

list.add(o1);

list.add(o2);

list.add(o3);

userService.batchUpdate(list);

}

//批量修改

public void batchUpdate(List

userDao.batchUpdate(list);

}

@Override

public void batchUpdate(List

String sql = "update t_user set username=?,ustatus=? where userId=?";

int[] ints = jdbcTemplate.batchUpdate(sql, list);

System.out.println(ints);

}

5.3 删除

@Test

public void test9() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

List

Object[] o1 = { "11"};

Object[] o2 = { "12"};

Object[] o3 = { "13"};

list.add(o1);

list.add(o2);

list.add(o3);

userService.batchDelete(list);

}

//批量删除

public void batchDelete(List

userDao.batchDelete(list);

}

@Override

public void batchDelete(List

String sql = "delete from t_user where userId=?";

int[] ints = jdbcTemplate.batchUpdate(sql, list);

System.out.println(ints);

http://}

location="classpath:jdbc.properties" />

class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">

class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">

2.3 配置JdbcTemplate 对象，注入 DataSource

class="org.springframework.jdbc.core.JdbcTemplate">

class="org.springframework.jdbc.core.JdbcTemplate">

2.4 扫描注解

base-package="cn.zj.aop.an">

base-package="cn.zj.aop.an">

2.5 创建 service 类，创建 dao 类，在 dao 注入 jdbcTemplate 对象

@Repository

public class UserDaoImpl implements UserDao {

//注入 JdbcTemplate

@Autowired

private JdbcTemplate jdbcTemplate;

}

@Service

public class UserService {

// 注入 dao

@Autowired

private UserDao userDao;

}

三、操作(CRUD)

实体类

public class User {

private String userId;

private String username;

private String ustatus;

@Override

public String toString() {

return "User [userId=" + userId + ", username=" + username + ", ustatus=" + ustatus + "]";

}

public String getUserId() {

return userId;

}

public void setUserId(String userId) {

this.userId = userId;

}

public String getUsername() {

return username;

}

public void setUsername(String username) {

this.username = username;

}

public String getUstatus() {

return ustatus;

}

public void setUstatus(String ustatus) {

this.ustatus = ustatus;

}

}

3.1 添加

service

//添加

public void addUser(User user) {

userDao.add(user);

}

dao

@Override

public void add(User user) {

// 1 创建 sql 语句

String sql = "insert into t_user values(?,?,?)";

// 2 调用方法实现

Object[] args = { user.getUserId(), user.getUsername(), user.getUstatus() };

int update = jdbcTemplate.update(sql, args);

System.out.println(update);

}

测试

@Test

public void test1() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

User user = new User();

user.setUserId("1");

user.setUsername("java");

user.setUstatus("a");

userService.addUser(user);

}

结果

3.2 修改

service

//修改

public void updateUser(User user) {

userDao.updateUser(user);

}

dao

@Override

public void updateUser(User user) {

// TODO Auto-generated method stub

String sql = "update t_user set username=?,ustatus=? where userId=?";

// 2 调用方法实现

Object[] args = { user.getUsername(), user.getUstatus() ,user.getUserId()};

int update = jdbcTemplate.update(sql, args);

System.out.println(update);

}

测试

@Test

public void test2() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

User user = new User();

user.setUserId("1");

user.setUsername("javaScrip");

user.setUstatus("abc");

userService.updateUser(user);

}

3.3 删除

// 删除

public void deleteUser(String id) {

userDao.deleteUser(id);

}

@Override

public void deleteUser(String id) {

String sql="delete from t_user where userId=?";

int update=jdbcTemplate.update(sql, id);

System.out.println(update);

}

@Test

public void test3() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

userService.deleteUser("1");

}

四、查询

4.1 查询总记录数 jdbcTemplate.queryForObject

@Test

public void test4() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

int count = userService.selectUserCount();

System.out.println("数据库中共有记录:"+count);

}

//查询记录数

public int selectUserCount() {

return userDao.selectCount();

}

@Override

public int selectCount() {

String sql = "select count(0) from t_user";

Integer count = jdbcTemplate.queryForObject(sql, Integer.class);

return count;

}

4.2 查询返回对象

@Test

public void test5() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

User user=userService.findUserInfo("1");

System.out.println(user);

}

//查询对象

public User findUserInfo(String id) {

// TODO Auto-generated method stub

return userDao.findUserInfo(id);

}

@Override

public User findUserInfo(String id) {

String sql = "select userId,username,ustatus from t_user where userId=?";

User user = jdbcTemplate.queryForObject(sql, new

BeanPropertyRowMapper(User. class),id);

return user;

}

4.3 查询返回集合

@Test

public void test6() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

List list=userService.findAllUser();

System.out.println(list);

}

//查询返回集合

public List findAllUser(){

return userDao.findAllUser();

}

@Override

public List findAllUser() {

String sql = "select userId,username,ustatus from t_user";

List list = jdbcTemplate.query(sql, new

BeanPropertyRowMapper(User. class));

return list;

}

五、批量操作 jdbcTemplate.batchUpdate

5.1 添加

@Test

public void test7() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

List

Object[] o1 = { "11", "易语言", "中文"};

Object[] o2 = { "12", "c++", "cc"};

Object[] o3 = { "13", "MySQL", "数据库"};

list.add(o1);

list.add(o2);

list.add(o3);

userService.batchAdd(list);

}

//批量添加

public void batchAdd(List

userDao.batchAdd(list);

}

@Override

public void batchAdd(List

String sql = "insert into t_user values(?,?,?)";

int[] ints = jdbcTemplate.batchUpdate(sql, list);

System.out.println(ints);

}

5.2 修改

@Test

public void test8() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

List

Object[] o1 = { "易语言易", "中文语言","11"};

Object[] o2 = { "c++c", "ccccc","12"};

Object[] o3 = {"MySQL+ORACle", "数据库数据", "13"};

list.add(o1);

list.add(o2);

list.add(o3);

userService.batchUpdate(list);

}

//批量修改

public void batchUpdate(List

userDao.batchUpdate(list);

}

@Override

public void batchUpdate(List

String sql = "update t_user set username=?,ustatus=? where userId=?";

int[] ints = jdbcTemplate.batchUpdate(sql, list);

System.out.println(ints);

}

5.3 删除

@Test

public void test9() {

ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

UserService userService = context.getBean("userService", UserService.class);

List

Object[] o1 = { "11"};

Object[] o2 = { "12"};

Object[] o3 = { "13"};

list.add(o1);

list.add(o2);

list.add(o3);

userService.batchDelete(list);

}

//批量删除

public void batchDelete(List

userDao.batchDelete(list);

}

@Override

public void batchDelete(List

String sql = "delete from t_user where userId=?";

int[] ints = jdbcTemplate.batchUpdate(sql, list);

System.out.println(ints);

http://}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。