https://www.journaldev.com/17053/spring-jdbctemplate-example

今天完成的事情：任务1 的 17 项

17. 编写DAO，分别JdbcTemplate和Mybatis连接数据库，注意使用JDBCTemplate的时候分离Interface和Imple，使用Mybatis的时候注意理解为什么不需要Impl，注意遵守命名规范。

1，首先是没怎么注意过的JdbcTemplate的连接。既然连都连了，做一套CRUD操作。

运行我的程序前，我先在mysql中手动建立了一张表，这张表和我java程序中的实体类的属性一一对应。具体表字段，看之前的日报。

2，设置database.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/xz_task1
dbuser=root
dbpassword=

以上设置根据自己用户名，密码，driver,连接数据库url填写

3，做好jar包的管理，pom.xml，根据自己的情况填写尤其是dependency。以下方式防止硬编码：

<properties>
   <spring.framework>4.3.0.RELEASE</spring.framework>
</properties>

JdbcTemplate class的使用就是使用Spring，需要加入Spring的相关依赖。

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-core</artifactId>
    <version>${spring.framework}</version>
</dependency>

等等

4，按照要求，Impl分离，所以项目结构是：

5，代码展示

package com.JdbcTemplate.spring.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
@ComponentScan("com.JdbcTemplate.spring")
@PropertySource("classpath:database.properties")
public class AppConfig {

   @Autowired
   Environment environment;

   private final String URL = "url";
   private final String USER = "dbuser";
   private final String DRIVER = "driver";
   private final String PASSWORD = "dbpassword";

   @Bean
   DataSource dataSource() {
      DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
      driverManagerDataSource.setUrl(environment.getProperty(URL));
      driverManagerDataSource.setUsername(environment.getProperty(USER));
      driverManagerDataSource.setPassword(environment.getProperty(PASSWORD));
      driverManagerDataSource.setDriverClassName(environment.getProperty(DRIVER));
      return driverManagerDataSource;
   }
}

package com.JdbcTemplate;

import com.JdbcTemplate.model.Student;
import com.JdbcTemplate.spring.config.AppConfig;
import com.JdbcTemplate.spring.dao.StudentDAO;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

public class Main {
   public static void main(String[] args) {
      AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext(AppConfig.class);

      StudentDAO studentDAO = context.getBean(StudentDAO.class);

      System.out.println("List of person is:");

      for (Student p : studentDAO.getAllPersons()) {
         System.out.println(p);
      }

      System.out.println("\nCreating person: Sergey");
      Student student = new Student(2L, 1234L, 3456L, "Sergey",
            "35673","","","",
            "","","","","");
      System.out.println(student);
      studentDAO.createStudent(student);
      System.out.println("\nList of person is:");
      for (Student p : studentDAO.getAllPersons()) {
         System.out.println(p);
      }

      System.out.println("\nGet person with ID 2");

      Student studentById = studentDAO.getStudentById(2L);
      System.out.println(studentById);

      System.out.println("\nDeleting person with ID 2");
      studentDAO.deleteStudent(studentById);

      System.out.println("\nCreating person: Tom");
      Student student4 = new Student(4L, 123468L, 345618L, "Tom",
            "3567311","CS","2010","UNL",
            "hoho","th","Dr. Who","Working with Linus",
            "Nicole");
      System.out.println(student4);
      studentDAO.createStudent(student4);
      System.out.println("\nList of person is:");
      for (Student p : studentDAO.getAllPersons()) {
         System.out.println(p);
      }

      System.out.println("\nUpdate person with ID 4");

      Student pperson = studentDAO.getStudentById(4L);
      pperson.setName("CHANGED");
      studentDAO.updateStudent(pperson);

      System.out.println("\nList of person is:");
      for (Student p : studentDAO.getAllPersons()) {
         System.out.println(p);
      }

      context.close();
   }
}

package com.JdbcTemplate.model;

public class Student {
   private Long id;
   private Long create_at;
   private Long update_at;
   private String name;
   private String qq_number;
   private String major;
   private String estimated_time_enrollment;
   private String school;
   private String student_id;
   private String log_link;
   private String mentor;
   private String wishes;
   private String recommended_from;


   public Student() {
   }

   public Student(Long id, Long create_at, Long update_at, String name, String qq_number,
               String major, String estimated_time_enrollment, String school,
               String student_id, String log_link, String mentor, String wishes, String recommended_from) {
      this.id = id;
      this.create_at = create_at;
      this.update_at = update_at;
      this.name = name;
      this.qq_number = qq_number;
      this.major = major;
      this.estimated_time_enrollment = estimated_time_enrollment;
      this.school = school;
      this.student_id = student_id;
      this.log_link = log_link;
      this.mentor = mentor;
      this.wishes = wishes;
      this.recommended_from = recommended_from;
   }

   public Long getId() {
      return id;
   }

   public void setId(Long id) {
      this.id = id;
   }

   public Long getCreate_at() {
      return create_at;
   }

   public void setCreate_at(Long create_at) {
      this.create_at = create_at;
   }

   public Long getUpdate_at() {
      return update_at;
   }

   public void setUpdate_at(Long update_at) {
      this.update_at = update_at;
   }

   public String getName() {
      return name;
   }

   public void setName(String name) {
      this.name = name;
   }

   public String getQq_number() {
      return qq_number;
   }

   public void setQq_number(String qq_number) {
      this.qq_number = qq_number;
   }

   public String getMajor() {
      return major;
   }

   public void setMajor(String major) {
      this.major = major;
   }

   public String getEstimated_time_enrollment() {
      return estimated_time_enrollment;
   }

   public void setEstimated_time_enrollment(String estimated_time_enrollment) {
      this.estimated_time_enrollment = estimated_time_enrollment;
   }

   public String getSchool() {
      return school;
   }

   public void setSchool(String school) {
      this.school = school;
   }

   public String getStudent_id() {
      return student_id;
   }

   public void setStudent_id(String student_id) {
      this.student_id = student_id;
   }

   public String getLog_link() {
      return log_link;
   }

   public void setLog_link(String log_link) {
      this.log_link = log_link;
   }

   public String getMentor() {
      return mentor;
   }

   public void setMentor(String mentor) {
      this.mentor = mentor;
   }

   public String getWishes() {
      return wishes;
   }

   public void setWishes(String wishes) {
      this.wishes = wishes;
   }

   public String getRecommended_from() {
      return recommended_from;
   }

   public void setRecommended_from(String recommended_from) {
      this.recommended_from = recommended_from;
   }

   @Override
   public String toString() {
      return "Person{" + "id=" + id + ", name=" + name
            + '\'' + '}';
   }
}

package com.JdbcTemplate.model;

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class StudentMapper implements RowMapper<Student> {

   public Student mapRow(ResultSet resultSet, int i) throws SQLException {

      Student student = new Student();
      student.setId(resultSet.getLong("id"));
      student.setCreate_at(resultSet.getLong("create_at"));
      student.setUpdate_at(resultSet.getLong("update_at"));
      student.setName(resultSet.getString("name"));
      student.setQq_number(resultSet.getString("qq_number"));
      student.setMajor(resultSet.getString("major"));
      student.setEstimated_time_enrollment(resultSet.getString("estimated_time_enrollment"));
      student.setSchool(resultSet.getString("school"));
      student.setStudent_id(resultSet.getString("student_id"));
      student.setLog_link(resultSet.getString("log_link"));
      student.setMentor(resultSet.getString("mentor"));
      student.setWishes(resultSet.getString("wishes"));
      student.setRecommended_from(resultSet.getString("recommended_from"));
      return student;
   }
}

package com.JdbcTemplate.spring.dao;

import com.JdbcTemplate.model.Student;
import com.JdbcTemplate.model.StudentMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.util.List;

@Component
public class StudentDAOImpl implements StudentDAO {

   JdbcTemplate jdbcTemplate;

   private final String SQL_FIND_STUDENT = "select * from students where id = ?";
   private final String SQL_DELETE_STUDENT = "delete from students where id = ?";
   private final String SQL_UPDATE_STUDENT = "update students set name  = ? where id = ?";
   private final String SQL_GET_ALL = "select * from students";
   private final String SQL_INSERT_STUDENT =
         "insert into students(id, create_at, update_at, name, qq_number, major," +
               "estimated_time_enrollment, school, student_id, log_link, mentor, " +
               "wishes, recommended_from) " +
               "values(?,?,?,?,?,?,?,?,?,?,?,?,?)";

   @Autowired
   public StudentDAOImpl(DataSource dataSource) {
      jdbcTemplate = new JdbcTemplate(dataSource);
   }

   public Student getStudentById(Long id) {
      return jdbcTemplate.queryForObject(SQL_FIND_STUDENT, new Object[] { id }, new StudentMapper());
   }

   public List<Student> getAllPersons() {
      return jdbcTemplate.query(SQL_GET_ALL, new StudentMapper());
   }

   public boolean deleteStudent(Student student) {
      return jdbcTemplate.update(SQL_DELETE_STUDENT, student.getId()) > 0;
   }

   public boolean updateStudent(Student student) {
      return jdbcTemplate.update(SQL_UPDATE_STUDENT, student.getName(),
            student.getId()) > 0;
   }

   public boolean createStudent(Student student) {
      return jdbcTemplate.update(SQL_INSERT_STUDENT, student.getId(), student.getCreate_at(), student.getUpdate_at(),
            student.getName(), student.getQq_number(), student.getMajor(), student.getEstimated_time_enrollment(),
            student.getSchool(), student.getStudent_id(), student.getLog_link(), student.getMentor(),
            student.getWishes(), student.getRecommended_from()) > 0;
   }
}

package com.JdbcTemplate.spring.dao;

import com.JdbcTemplate.model.Student;

import java.util.List;

public interface StudentDAO {
   Student getStudentById(Long id);

   List<Student> getAllPersons();

   boolean deleteStudent(Student student);

   boolean updateStudent(Student student);

   boolean createStudent(Student student);
}

6，结果如下：List of person is:

Person{id=1, name=燕小鱼'}

Creating person: Sergey

Person{id=2, name=Sergey'}

List of person is:

Person{id=1, name=燕小鱼'}

Person{id=2, name=Sergey'}

Get person with ID 2

Person{id=2, name=Sergey'}

Deleting person with ID 2

Creating person: Tom

Person{id=4, name=Tom'}

List of person is:

Person{id=1, name=燕小鱼'}

Person{id=4, name=Tom'}

Update person with ID 4

List of person is:

Person{id=1, name=燕小鱼'}

Person{id=4, name=CHANGED'}

Nov 04, 2020 10:32:30 PM org.springframework.context.annotation.AnnotationConfigApplicationContext doClose

INFO: Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@179d3b25: startup date [Wed Nov 04 22:32:27 CST 2020]; root of context hierarchy

Process finished with exit code 0

今天问题：IDEA IntelliJ Ultimate版本，好用，稳定。考虑获取方式。

今天的编码是为了展示JdbcTemplate，有的地方还可以写不那么死。

今天的收获：复习了根据报错，引入jar包的操作。Eclipse和IntelliJ IDEA项目互转。最终代码以提交到git为准。

明天的计划：继续完成task 1。