文章目录
- Spring框架之JdbcTemplate增删改查 *** 作
- 前言
- 一、JDBCTemplate简单介绍
- 二、使用步骤
- 1.引入所需的依赖
- 2.连接数据库的 *** 作
- 2.1、jdbc.properties文件
- 2.2、连接数据库
- 2.2、SpringConfig 用于合并写的配置文件
- 3.实体类
- 4.数据库 *** 作的代码
- 4.1、dao层
- StudyDao类
- 实现StudyDao类
- 4.2、service
- StudyService 类
- 实现StudyService 类
- 测试
- 总结
前言
提前言:本文记录自己在学习spring框架中JdbcTemplate的增删改查 *** 作。如有缺点望大家多多指教。
提示:以下是本篇文章正文内容,下面案例可供参考
大家可以看看这个,我就不多加介绍了。
https://blog.csdn.net/qq_22075913/article/details/108196005
代码如下(示例):
<dependency>
<groupId>mysqlgroupId>
<artifactId>mysql-connector-javaartifactId>
<version>5.1.49version>
dependency>
<dependency>
<groupId>c3p0groupId>
<artifactId>c3p0artifactId>
<version>0.9.1.2version>
dependency>
<dependency>
<groupId>com.alibabagroupId>
<artifactId>druidartifactId>
<version>1.1.10version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-jdbcartifactId>
<version>5.0.5.RELEASEversion>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-testartifactId>
<version>5.0.5.RELEASEversion>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-txartifactId>
<version>5.0.5.RELEASEversion>
dependency>
2.连接数据库的 *** 作
2.1、jdbc.properties文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/account
jdbc.username=root
jdbc.password=123456
2.2、连接数据库
@PropertySource("classpath:jdbc.properties")
public class mysqlConfig {
@Value("${jdbc.url}")
private String url;
@Value("${jdbc.driver}")
private String driver;
@Value("${jdbc.username}")
private String username;
@Value("${jdbc.password}")
private String password;
@Bean("dataSource")
public ComboPooledDataSource getSource() throws Exception {
//创建数据源
ComboPooledDataSource source = new ComboPooledDataSource();
//设置数据库连接参数
source.setJdbcUrl(url);
source.setDriverClass(driver);
source.setUser(username);
source.setPassword(password);
return source;
}
```
/**
* 将数据源给到jdbcTemplate
* @param dataSource
* @return
*/
@Bean("jdbcTemplate")
public JdbcTemplate jdbcTemplate(ComboPooledDataSource dataSource){
return new JdbcTemplate(dataSource);
}
/**
* 配置事务
* @param dataSource
* @return
*/
@Bean("dataSourceTransactionManager")
public DataSourceTransactionManager dataSourceTransactionManager(ComboPooledDataSource dataSource){
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager(dataSource);
return dataSourceTransactionManager;
}
2.2、SpringConfig 用于合并写的配置文件
@Configuration
@ComponentScan("com.qiu")
@Import(mysqlConfig.class)
@EnableTransactionManagement //开启注解事务
public class SpringConfig {
}
3.实体类
代码如下(示例):
public class Study {
private int id;
private String name;
private Integer chinese;
private Integer math;
private Integer english;
private Integer sno;
public Study() {
}
public Study(int id, String name, Integer chinese, Integer math, Integer english, Integer sno) {
this.id = id;
this.name = name;
this.chinese = chinese;
this.math = math;
this.english = english;
this.sno = sno;
}
该处的get,set,toString方法请自己添加哦。
4.数据库 *** 作的代码 4.1、dao层 StudyDao类
public interface StudyDao {
/**
* 添加学生成绩信息
* @param study
*/
void addStudy(Study study);
/**
* 根据id删除学生信息
* @param id
* @return
*/
boolean delete(Long id);
/**
* 根据id查询学生信息
* @param id
* @return
*/
Study selectStudy(Long id);
/**
* 查询所有学生信息
* @return
*/
List<Study> findAllStudy();
/**
* 批量添加学生成绩
* @param bathStudy
*/
void bathAddStudy(List<Object[]> bathStudy);
/**
* 修改学生成绩
* @param study
*/
void updateStudy(Study study);
/**
* 批量删除
* @param batchStudy
*/
void bathDeleteStudy(List<Object[]> batchStudy);
/**
* 批量修改学生成绩
* @param batchUpdate
*/
void batchUpdateStudy(List<Object[]> batchUpdate);
}
实现StudyDao类
@Repository("studyDao")
public class StudyDaoImpl implements StudyDao {
//注入JdbcTemplate
@Resource(description = "jdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Override
public void addStudy(Study study) {
//编写sql 方法一
String sql = "insert into study(name,chinese,math,english,sno) values (?,?,?,?,?)";
//sql参数
Object[] objs ={study.getName(),study.getChinese(),study.getMath(),study.getEnglish(),study.getSno()};
//执行语句
int update = jdbcTemplate.update(sql,objs);
//方法二
// jdbcTemplate.update("insert into study(name,chinese) values (?,?)",study.getName(),study.getChinese());
System.out.println(update);
}
@Override
public boolean delete(Long id) {
//编写sql
String sql = "delete from study where id = ?";
int update = jdbcTemplate.update(sql, id);
System.out.println(update);
return true;
}
@Override
public Study selectStudy(Long id) {
//编写sql
String sql = "select * from study where id = ?";
//执行语句
Study study = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Study>(Study.class),id);
return study;
}
@Override
public List<Study> findAllStudy() {
//编写sql
String sql = "select * from study";
//执行语句
List<Study> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Study>(Study.class));
return query;
}
@Override
public void bathAddStudy(List<Object[]> bathStudy) {
//编写sql
String sql = "insert into study(name,chinese,math,english,sno) values (?,?,?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, bathStudy);
System.out.println(Arrays.toString(ints));
}
@Override
public void updateStudy(Study study) {
String sql = "update study set name = ?,chinese = ?,math = ?,english = ?,sno = ? where id = ?";
Object[] objects = {study.getName(),study.getChinese(),study.getMath(),study.getEnglish(),study.getSno(),study.getId()};
int update = jdbcTemplate.update(sql, objects);
System.out.println(update);
}
@Override
public void bathDeleteStudy(List<Object[]> batchStudy) {
String sql = "delete from study where id = ?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchStudy);
System.out.println(Arrays.toString(ints));
}
@Override
public void batchUpdateStudy(List<Object[]> batchUpdate) {
String sql = "update study set name = ?,chinese = ?,math = ?,english = ?,sno = ? where id = ?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchUpdate);
System.out.println(Arrays.toString(ints));
}
}
4.2、service
StudyService 类
public interface StudyService {
/**
* 添加学生成绩
* @param study
* @return
*/
boolean addStudy(Study study);
/**
* 根据id查询学生信息
* @param id
* @return
*/
Study selectStudy(Long id);
/**
* 查询所有学生成绩
* @return
*/
List<Study> findAllStudy();
/**
* 批量添加学生成绩
* @param studies
*/
void batchAddStudy(List<Study> studies);
/**
* 根据id删除学生成绩
* @param id
* @return
*/
boolean deleteStudy(Long id);
/**
* 修改学生成绩信息
* @param study
*/
void updateStudy(Study study);
/**
* 批量删除学生成绩信息
* @param objects
*/
void batchDeleteStudy(List<Object[]> objects);
/**
* 批量修改学生成绩信息
* @param batchUpdates
*/
void batchUpdateStudy(List<Object[]> batchUpdates);
}
实现StudyService 类
@Service
public class StudyServiceImpl implements StudyService{
@Resource
private StudyDao studyDao;
@Override
@Transactional
public boolean addStudy(Study study) {
studyDao.addStudy(study);
// int[] arr = new int[3];
// arr[4] = 0;
return false;
}
@Override
public Study selectStudy(Long id) {
Study study = studyDao.selectStudy(id);
return study;
}
@Override
public List<Study> findAllStudy() {
List<Study> allStudy = studyDao.findAllStudy();
return allStudy;
}
@Override
public void batchAddStudy(List<Study> studies) {
List<Object[]> objectsList = new ArrayList<Object[]>();
for (Study study:studies){
//将学生列表中的数据提取出来
Object[] objects = {study.getName(),study.getChinese(),study.getMath(),study.getEnglish(),study.getSno()};
objectsList.add(objects);
}
//将数据添加到数据库
studyDao.bathAddStudy(objectsList);
}
@Override
public boolean deleteStudy(Long id) {
boolean delete = studyDao.delete(id);
return delete;
}
@Override
public void updateStudy(Study study) {
studyDao.updateStudy(study);
}
@Override
public void batchDeleteStudy(List<Object[]> objects) {
studyDao.bathDeleteStudy(objects);
}
@Override
public void batchUpdateStudy(List<Object[]> batchUpdates) {
studyDao.batchUpdateStudy(batchUpdates);
}
}
测试
/**
* spring集成junit
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {SpringConfig.class})
public class SpringJunitTest {
@Autowired
private StudyService studyService;
@Test
public void testAddStudy(){
Study study = new Study();
study.setChinese(88);
study.setEnglish(90);
study.setMath(88);
study.setName("七");
study.setSno(20);
studyService.addStudy(study);
}
@Test
public void testSelectStudy(){
Study study = studyService.selectStudy(1l);
System.out.println(study);
}
@Test
public void testFindAllStudy(){
List<Study> allStudy = studyService.findAllStudy();
System.out.println(allStudy);
}
@Test
public void testBatchAddStudy(){
ArrayList<Study> list = new ArrayList<>();
for (int i = 0;i < 10;i++){
Study study = new Study();
study.setChinese(88);
study.setEnglish(90);
study.setMath(88);
study.setName("七七九九");
study.setSno(20);
list.add(study);
}
studyService.batchAddStudy(list);
}
@Test
public void testDelete(){
boolean b = studyService.deleteStudy(226l);
System.out.println(b);
}
@Test
public void testUpdate(){
Study study = new Study();
study.setId(48);
study.setName("九九");
study.setMath(60);
study.setEnglish(90);
study.setChinese(99);
study.setSno(202099);
studyService.updateStudy(study);
}
@Test
public void testBatchDeleteStudy(){
ArrayList<Object[]> list = new ArrayList<>();
// Object[] os = {"239"};
// Object[] os2 = {"240"};
// list.add(os);
// list.add(os2);
for (int i = 241;i<=247;i++){
Object[] o = {i};
list.add(o);
}
studyService.batchDeleteStudy(list);
}
@Test
public void testBatchUpdateStudy(){
ArrayList<Object[]> list = new ArrayList<>();
Object[] o1 = {"九九","44","44","44","23232","16"};
Object[] o2 = {"九九","44","44","44","23232","17"};
list.add(o1);
list.add(o2);
studyService.batchUpdateStudy(list);
}
}
总结
有问题的大家可以多多指教。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)