目标
有一个学生表,属性有id/name/age,id自增,通过spring-jdbc的方式查询所有数据,添加数据。
项目结构
Maven依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.4.RELEASE</version>
</dependency>
代码编写
建立对象模型
Student.java
package com.dotleo.entity;
/**
* Student 实体类
* @author LiuFei
* @create 2017-11-05 21:35
*/
public class Student {
private int id; //主键
private String name; //姓名
private int age; //年龄
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
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;
}
}
spring配置文件 - beans.xml 创建
此文件为最终版,可能由于部分类还没创建报错,暂时不用管
beans.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/spring_data" />
<property name="username" value="root" />
<property name="password" value="liufei" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="studentDAO" class="com.dotleo.dao.StudentDAOImpl">
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
</beans>
DAO开发
StudentDAO.java
package com.dotleo.dao;
import com.dotleo.entity.Student;
import java.util.List;
/**
* 学生类DAO
* @author LiuFei
* @create 2017-11-05 21:38
*/
public interface StudentDAO {
/**
* 查询所有学生
* @return 所有学生
*/
public List<Student> query();
/**
* 添加一个学生
* @param student 学生
*/
public int save(Student student);
}
StudentDAOImpl.java
package com.dotleo.dao;
import com.dotleo.entity.Student;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* Student类DAO实现类,通过最原始的jdbc方式操作
* @author LiuFei
* @create 2017-11-05 21:41
*/
public class StudentDAOImpl implements StudentDAO{
private JdbcTemplate jdbcTemplate;
public List<Student> query() {
final List<Student> students = new ArrayList<Student>();
String sql = "select id, name , age from student";
jdbcTemplate.query(sql, new RowCallbackHandler() {
public void processRow(ResultSet resultSet) throws SQLException {
Student student = new Student();
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setAge(resultSet.getInt("age"));
students.add(student);
}
});
return students;
}
public int save(Student student) {
int result = 0;
String sql = "insert into student (name, age) values (?, ?)";
result = jdbcTemplate.update(sql, student.getName(), student.getAge());
return result;
}
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
}
测试类创建
需要一定有junit基础
DataSourceTest.java
package com.dotleo;
import com.dotleo.dao.StudentDAO;
import com.dotleo.entity.Student;
import junit.framework.Assert;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
import java.util.List;
/**
* @author LiuFei
* @create 2017-11-05 22:51
*/
public class DataSourceTest {
ApplicationContext ctx = null;
@Before
public void setup() {
ctx = new ClassPathXmlApplicationContext("beans.xml");
}
@After
public void tearDown() {
ctx = null;
}
@Test
public void testDataSource() {
DataSource dataSource = (DataSource) ctx.getBean("dataSource");
Assert.assertNotNull(dataSource);
}
@Test
public void testJdbcTemplate() {
JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
Assert.assertNotNull(jdbcTemplate);
}
@Test
/**
* 查询方法测试
*/
public void testQuery() {
StudentDAO studentDAO = (StudentDAO) ctx.getBean("studentDAO");
List<Student> studentList = studentDAO.query();
for (Student student : studentList
) {
System.out.println("id: " + student.getId()
+ "name: " + student.getName()
+ "age: " + student.getAge());
}
}
@Test
/**
* 添加方法测试
*/
public void testSave() {
StudentDAO studentDAO = (StudentDAO) ctx.getBean("studentDAO");
Student student = new Student();
student.setName("maliu");
student.setAge(23);
int result = studentDAO.save(student);
System.out.println(result);
}
}