发表于： 2019-11-10 21:17:30
2 1154
今天拖师兄们的福算是完成了jdbctemplate连接数据库的部分。
添加dependencies
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.2.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.2.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.2.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>5.2.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.2.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.2.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.1.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
</dependency>
student.java
package Spring;
//实体类
public class Student {
private int id ;
private String name ;
private long create_at ;
private long update_at ;
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 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;}
}
studentimpl.java
package Spring;
import com.sofiaJdbcTemplatedemo.App;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class StudentImpl implements StudentDao {
//查找全部
@Override
public List<Student> findAll() {
ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) app.getBean("jdbcTemplate");
String sql = "select * from student";
final List<Student> listAllStudent = new ArrayList<>();
jdbcTemplate.query(sql, new RowCallbackHandler() {
@Override
public void processRow(ResultSet resultSet) throws SQLException {
Student student = new Student();
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setCreate_at(resultSet.getInt("create_at"));
student.setUpdate_at(resultSet.getInt("update_at"));
listAllStudent.add(student);
}
}
);
return listAllStudent;
}
@Override
public boolean delete(int id) {
ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) app.getBean("jdbcTemplate");
String sql = "delete from student where id=?";
int result= jdbcTemplate.update(sql, id);
System.out.println("success");
if(result > 0) {
return true;
}
return false;
}
@Override
public int insert(final Student student) {
ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) app.getBean("jdbcTemplate");
final String sql = "insert into student (name, create_at,update_at) VALUES(?,?,?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1,student.getName());
ps.setLong(2,student.getCreate_at());
ps.setLong(3,student.getUpdate_at());
return ps;
}
},keyHolder);
return keyHolder.getKey().byteValue();
}
@Override
public Student findByID(int id) {
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
String sql = "select * from student where id=?";
final Student student = new Student();
jdbcTemplate.query(sql, new RowMapper<Object>() {
@Override
public Object mapRow(ResultSet resultSet, int i) throws SQLException {
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setCreate_at(resultSet.getInt("create_at"));
student.setUpdate_at(resultSet.getInt("update_at"));//这里原本拼写错误
return student;
}
}, id
);
return student;
}
@Override
public boolean update(Student student) {
ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) app.getBean("jdbcTemplate");
String sql = "update student set name = ? where id= ?";
int result = jdbcTemplate.update(sql, student.getName(),student.getId());
if(result > 0) {
return true;
}
return false;
}
}
studenttest.java
package com.sofiaJdbcTemplatedemo;
import Spring.Student;
import Spring.StudentDao;
import Spring.StudentImpl;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class StudentTest {
static StudentDao studentDao = new StudentImpl();
public static void main(String[] args) {
//ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicantionContext.xml");
Student student = new Student();
student.setId(2);//改成自增
student.setName("rueben");
student.setCreate_at(20191110);
student.setUpdate_at(20191110);
System.out.println(studentDao.findAll());
}
}
applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
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">
<!--使用context命名空间，通过spring扫描指定包及其所有子包下所有bean的实现类，进行注解解析-->
<context:component-scan base-package="edu.njxz.demo"/>
<!-- 配置数据源-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<!-- Mysql数据库驱动-->
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<!-- 连接数据库的url-->
<!-- 连接数据库的用户名-->
<property name="username" value="root"></property>
<!-- 连接数据库的密码-->
<property name="password" value="Root"></property>
</bean>
<!-- 配置Jdbc模板-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
studentdao.java
package Spring;
//创建Dao接口
import java.util.List;
public interface StudentDao {
List<Student> findAll();
boolean delete(int id);
int insert(Student student);
Student findByID(int id);
boolean update(Student student);
}
程序报错后来师兄帮忙解决了，原因一是因为我student.setUpdate_at(resultSet.getInt("uddate_at"));这里拼写错误了
二是因为数据库保持连接的时间过长，数据库回收了连接，而系统的缓冲池不知道，继续使用被回收的连接所致的。重启电脑后得到解决。
明天计划学习mybatis连接数据库。
今天的收获就是有师兄真好，嘻嘻。
返回列表
评论