发表于： 2019-03-02 20:21:23
1 624
今天完成的事情：
1：基于maven 使用spring框架 对jdbcTemplate crud操作的实现
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>jdbcdemo</groupId>
<artifactId>jdbcdemo</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<name>jdbcdemo</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.1.1</version>
</dependency>
</dependencies>
</project>
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>jdbcdemo</groupId>
<artifactId>jdbcdemo</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<name>jdbcdemo</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.1.1</version>
</dependency>
</dependencies>
</project>
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>jdbcdemo</groupId>
<artifactId>jdbcdemo</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<name>jdbcdemo</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.1.1</version>
</dependency>
</dependencies>
</project>
package PersonDao;
import Person.Person;
import javax.sql.DataSource;
import java.util.List;
/**
* @author 接口类。
*/
public interface PersonDao {
public void setDatasource(DataSource ds);
/**
* 插入数据的方法
*/
public void addperson(Person person);
/**
* 更新数据的方法
*/
public void updateperson(Person person);
/**
* 查询全表的方法
*/
public List<Person> allperson();
/**
* 根据id查询方法
*/
public List<Person> querypersonId(int id);
/**
* 根据id删除方法
*/
public void deletepersonId(int id);
/**
* 输出结果的方法
*/
public void displayall();
}
package PersonDaoimpl;
import Person.Person;
import PersonDao.PersonDao;
import PersonMapper.PersonMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
import java.util.List;
/**
* @author 实现类。
*/
public class PersonDaoImpl implements PersonDao {
private DataSource datasource;
private JdbcTemplate jdbcTemplateObject;
public void setDatasource(DataSource ds) {
this.datasource = ds;
this.jdbcTemplateObject = new JdbcTemplate(datasource);
}
/**
* 插入数据的方法
*/
public void addperson(Person person) {
String sql = "INSERT INTO stu (create_at,update_at,name,qq,job,start_time,college,number,daily_url,wish,brother,referee,city,review) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
jdbcTemplateObject.update(sql, person.getCreate_at(), person.getUpdate_at(), person.getName(), person.getQq(), person.getJob(), person.getStart_time(), person.getCollege(), person.getNumber(), person.getDaily_url(), person.getWish(), person.getBrother(), person.getReferee(), person.getCity(), person.getReview());
return;
}
/**
* 更新数据的方法
*/
public void updateperson(Person person) {
String sql = " UPDATE stu SET name=?,brother=?,job=?,wish=? where id = ?";
jdbcTemplateObject.update(sql, person.getName(), person.getBrother(), person.getJob(),person.getWish(),person.getId());
return;
}
/**
* 查询全部数据的方法
*/
public List<Person> allperson() {
List<Person> personList = null;
String sql = "SELECT * FROM stu ";
personList = jdbcTemplateObject.query(sql, new PersonMapper());
return personList;
}
/**
* 根据id查询数据的方法
*/
public List<Person> querypersonId(int id) {
List<Person> personList = null;
String sql = "SELECT * FROM stu WHERE id=?";
personList = jdbcTemplateObject.query(sql, new Object[]{id}, new PersonMapper());
return personList;
}
/**
* 根据id删除数据的方法
*/
public void deletepersonId(int id) {
String sql = "DELETE FROM stu WHERE id=?";
jdbcTemplateObject.update(sql, id);
return;
}
/**
* 输出结果的方法
*/
public void displayall() {
List<Person> personList = allperson();
for (Person s : personList) {
s.display();
}
}
}
package PersonMapper;
import Person.Person;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
/**
* @author 映射类。
*/
public class PersonMapper implements RowMapper<Person> {
public Person mapRow(ResultSet rs, int rownum) throws SQLException {
Person person = new Person();
person.setId(rs.getInt("id"));
person.setCreate_at(rs.getString("create_at"));
person.setUpdate_at(rs.getString("update_at"));
person.setName(rs.getString("name"));
person.setQq(rs.getInt("qq"));
person.setJob(rs.getString("job"));
person.setStart_time(rs.getString("start_time"));
person.setCollege(rs.getString("college"));
person.setNumber(rs.getInt("number"));
person.setDaily_url(rs.getString("daily_url"));
person.setWish(rs.getString("wish"));
person.setBrother(rs.getString("brother"));
person.setReferee(rs.getString("referee"));
person.setCity(rs.getString("city"));
person.setReview(rs.getString("review"));
return person;
}
}
package MainTest;
import Person.Person;
import PersonDaoimpl.PersonDaoImpl;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.List;
/**
* @author 测试类。
*/
public class MainTest {
public static void main(String[] args) {
ApplicationContext Context = new ClassPathXmlApplicationContext("Beans.xml");
PersonDaoImpl personDaoImpl = (PersonDaoImpl) Context.getBean("PersonDaoImpl");
String[] create_at = {"10355392","10344396","10455396","10895396","10336396","10378396"};
String[] update_at = {"47289530","47226537","47245537","47969537","47287837","47282637"};
String[] name = {"张天笑", "李晓", "赵一天", "金明明", "刘星", "张帆"};
int[] qq = {110323924,110372524,110363924,110387924,110382924,110359924};
String[] job = {"java","UI","WEB","Qa","js","css"};
String[] start_time ={"2015","2016","2017","2018","2019","2014"};
String[] college = {"天津南开大学","河南大学","郑州大学","浙江大学","东北大学","辽宁大学"};
int[] number = {2468,3598,0246,4589,7836,2999};
String[] daily_url = {"http://www.jnshu.com/school/28015/daily","http://www.jnshu.com/school/28015/daily","http://www.jnshu.com/school/28015/daily","http://www.jnshu.com/school/28015/daily","http://www.jnshu.com/school/28015/daily","http://www.jnshu.com/school/28015/daily"};
String[] wish = {"老大最帅","老大最帅","老大最帅","老大最帅","老大最帅","老大最帅"};
String[] brother = {"张温瑞","李潇","路一鸣","蔡卓坤","王帆","吴盈"};
String[] referee = {"知乎","知乎","知乎","知乎","知乎","知乎"};
String[] city = {"郑州","浙江","江苏","山东","辽宁","河北"};
String[] review = {"审核中","审核中","审核中","审核中","审核中","审核中"};
Person person = null;
List<Person> personList = null;
/**插入数据的方法*/
System.out.println("------------addperson---------------");
for (int i = 0; i <create_at.length; i++) {
person = new Person(create_at[i],update_at[i],name[i],qq[i],job[i],start_time[i],college[i],number[i],daily_url[i],wish[i],brother[i],referee[i],city[i],review[i]);
personDaoImpl.addperson(person);
}
personDaoImpl.displayall();
/**更新数据的方法*/
System.out.println("------------updateperson---------------");
person = new Person("王汇通","田文瑞","java","老大最丑",1);
personDaoImpl.updateperson(person);
personDaoImpl.displayall();
/**查询全表*/
System.out.println("---------allsperson-------------");
personDaoImpl.allperson();
personDaoImpl.displayall();
/**根据Id查询*/
System.out.println("------------querypersonId---------------");
personList = personDaoImpl.querypersonId(1);
for (Person s : personList) {
s.display();
}
/**根据I删除*/
System.out.println("---------deleteId-------------");
personDaoImpl.deletepersonId(55);
personDaoImpl.displayall();
}
}
2：编写junit单元测试（还没写完，目前还不能用）
package PersonDaoimpl;
import Person.Person;
import org.junit.Test;
public class PersonDaoImplTest {
PersonDaoImpl personDaoimpl = new PersonDaoImpl();
@Test
public void addperson() {
Person person = new Person();
person.setCreate_at("10352468");
person.setUpdate_at("47289539");
person.setName("张天爱");
person.setQq(1024890526);
person.setJob("java");
person.setStart_time("2018");
person.setCollege("清华大学");
person.setNumber(3759);
person.setDaily_url("http://www.jnshu.com/school/28015/daily");
person.setWish("老大最帅");
person.setBrother("文青");
person.setReferee("知乎");
person.setCity("河南");
person.setReview("审核中");
person.display();
}
@Test
public void updateperson() {
Person person = new Person();
person.setName("李雪");
person.setBrother("张xin");
person.setJob("QA");
person.setWish("老大最帅");
person.setId(58);
person.display();
}
明天计划的事情：
1：编写单元测试
2：创建并编写多条插入的方法
遇到的问题：
1：实现类SQL语句出现少个逗号的问题（害得我找了半天）
解决方法是：把sql语句复制到数据库看效果 ，调整好在复制回来
2：数据越界问题
解决方法是：把所有数据长度调成一致
收获： 以上
还有数据库效果图 ： 如下
返回列表
评论