springDao的jdbctemplate

pom文件

 

<?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>com.yujian</groupId>
<artifactId>spring-dao-demo1</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>
<!--spring连接jdbc-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>
<!--jdbc驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!--c3p0数据源,替代DBHelper的-->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.4</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>

<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.2</version>
</dependency>
</dependencies>

</project>
配置文件applicationContext.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"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/tx" xmlns:tx="http://www.springframework.org/schema/tx"
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 http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
<!--<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/school?useSSL=false"/>
<property name="user" value="root"/>
<property name="password" value="root"/>
</bean>-->
<!--<context:property-placeholder location="db.properties"/>
<context:component-scan base-package="com.yujian"/>-->
<!--<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${driverClass}"/>
<property name="jdbcUrl" value="${driverUrl}"/>
<property name="user" value="${dbName}"/>
<property name="password" value="${dbPass}"/>
</bean>-->
<!--<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>-->
<!--<bean id="studentDao" class="com.yujian.dao.StudentDao">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>-->
<!--<bean id="studentService" class="com.yujian.service.StudentService">
<property name="studentDao" ref="studentDao"/>
</bean>-->
<!--<tx:annotation-driven/>-->
</beans>
db.properties文件
driverClass=com.mysql.jdbc.Driver
driverUrl=jdbc:mysql://localhost:3306/school?useSSL=false
dbName=root
dbPass=root
如果用全注解方式,总文件为
package com.yujian.config;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.yujian.service.StudentService;
import org.springframework.beans.factory.annotation.Value;
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.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import java.beans.PropertyVetoException;

@Configuration
@PropertySource("classpath:db.properties")
@ComponentScan("com.yujian")
@EnableTransactionManagement
/*必须加入上面的EnableTransactionManagement才可以使用事务全注解拦截
* 而且下面要有*/
public class MyConfig {
@Value("${driverClass}")
private String driverClass;
@Value("${driverUrl}")
private String driverUrl;
@Value("${dbName}")
private String dbName;
@Value("${dbPass}")
private String dbPass;
@Bean
public ComboPooledDataSource dataSource(){
ComboPooledDataSource dataSource=new ComboPooledDataSource();
try {
dataSource.setPassword(dbPass);
dataSource.setUser(dbName);
dataSource.setJdbcUrl(driverUrl);
dataSource.setDriverClass(driverClass);
} catch (PropertyVetoException e) {
e.printStackTrace();
}
return dataSource;
}
@Bean
public JdbcTemplate jdbcTemplate(){
JdbcTemplate jdbcTemplate=new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource());
return jdbcTemplate;
}
@Bean
public DataSourceTransactionManager getDataSourceTransactionManager(){
DataSourceTransactionManager transactionManager=new DataSourceTransactionManager();
transactionManager.setDataSource(dataSource());
return transactionManager;
}

}
Dao层的jdbc设置
package com.yujian.dao;

import com.yujian.model.ClassInfo;
import com.yujian.model.Student;
import com.yujian.util.Page;
import lombok.Data;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;


@Component
public class StudentDao {
//分组,列的子查询,临时表子查询没办法用orm(hibernate)做映射
/*public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
@Data这个需要在setting里的plugins搜索lombok补丁下载才可以不用写getset
}*/

//匿名内部类
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Student> findListStudent(){
String sql="select * from student s,classinfo c where c.classId=s.classId";
List<Student> list=jdbcTemplate.query(sql,rowMapper);
//可以这样写,下面代码可以多个用
List<Student> students1=jdbcTemplate.query(sql,new BeanPropertyRowMapper(Student.class));
List<Student> students=new ArrayList<Student>();
List<Map<String,Object>> rows=jdbcTemplate.queryForList(sql);
for (Map row:rows){
Student student=new Student();
student.setStuId((Integer) row.get("stuId"));
student.setStuNmae((String) row.get("stuName"));
student.setStuBirth((Date) row.get("stuBirth"));
student.setStuSex((Integer) row.get("stuSex"));
ClassInfo classInfo=new ClassInfo();
classInfo.setClassId((Integer) row.get("classId"));
classInfo.setClassName((String )row.get("className"));
student.setClassInfo(classInfo);
students.add(student);

}
return students;
}
private RowMapper<Student> rowMapper=new RowMapper<Student>() {
public Student mapRow(ResultSet rs, int i) throws SQLException {
Student student=new Student();
student.setStuId(rs.getInt("stuId"));
student.setStuNmae(rs.getString("stuName"));
student.setStuBirth(rs.getDate("stuBirth"));
student.setStuSex(rs.getInt("stuSex"));
ClassInfo classInfo=new ClassInfo();
classInfo.setClassId(rs.getInt("classId"));
classInfo.setClassName(rs.getString("className"));
student.setClassInfo(classInfo);
return student;
}
};
public Student findStudentById(int stuId){
String sql="select * from student s,classinfo c where c.classId=s.classId and s.stuId=? ";
/*Student student=jdbcTemplate.queryForObject(sql, new RowMapper<Student>() {
public Student mapRow(ResultSet rs, int i) throws SQLException {
Student student=new Student();
student.setStuId(rs.getInt("stuId"));
student.setStuNmae(rs.getString("stuName"));
student.setStuBirth(rs.getDate("stuBirth"));
student.setStuSex(rs.getInt("stuSex"));
ClassInfo classInfo=new ClassInfo();
classInfo.setClassId(rs.getInt("classId"));
classInfo.setClassName(rs.getString("className"));
student.setClassInfo(classInfo);
return student;
}
},stuId);*/
Object[]obj={stuId};
Student student=jdbcTemplate.queryForObject(sql,obj, new RowMapper<Student>() {
public Student mapRow(ResultSet rs, int i) throws SQLException {
Student student=new Student();
student.setStuId(rs.getInt("stuId"));
student.setStuNmae(rs.getString("stuName"));
student.setStuBirth(rs.getDate("stuBirth"));
student.setStuSex(rs.getInt("stuSex"));
ClassInfo classInfo=new ClassInfo();
classInfo.setClassId(rs.getInt("classId"));
classInfo.setClassName(rs.getString("className"));
student.setClassInfo(classInfo);
return student;
}
});

return student;
}

public void updateStudent(Student student){
String sql="update student set stuName=?,stuSex=?,stuBirth=?,classId=? where stuId=?";
Object[]obj={student.getStuNmae(),student.getStuSex(),student.getStuBirth(),student.getClassInfo().getClassId(),student.getStuId()};
jdbcTemplate.update(sql,obj);

}
public void insertStudent(Student student){
String sql="insert into student(stuName, stuSex, stuBirth, classId) values " +
"(?,?,?,?)";
Object[] obj={student.getStuNmae(),student.getStuSex(),student.getStuBirth(),student.getClassInfo().getClassId()};
jdbcTemplate.update(sql,obj);
}
public void deleteStudent(int stuId){
String sql="delete from student where stuId=?";
jdbcTemplate.update(sql,stuId);

}
public List<Student> listStudentBy(Student student){
String sql="select * from student,classinfo where student.classId=classinfo.classId ";
List<Object> obj=new ArrayList<Object>();
if(null!=student.getStuNmae()&&!"".equals(student.getStuNmae())){
sql+=" and stuName=? ";
obj.add(student.getStuNmae());
}
/*if(null!=student.getStuNmae()&&!"".equals(student.getStuNmae())){
sql+=" and stuName=? ";
obj.add(student.getStuNmae());
}*/
if(null!=student.getStuBirth()&&!"".equals(student.getStuBirth())){
sql+=" and stuBirth=? ";
obj.add(student.getStuBirth());
}
if(student.getStuSex()!=-1){
sql+=" and stuSex=? ";
obj.add(student.getStuSex());
}
List<Student>list=jdbcTemplate.query(sql,obj.toArray(),rowMapper);
return list;
}
public List<Student> findStuentByParam(Map<String,String> param, Page page){
String sql="select * from student ,classinfo where student.classId=classinfo.classId ";
List paramValue=new ArrayList();
//条件子句
if(null!=param&&param.size()>0) {
sql += getParamValue(param, paramValue);
}
/*String stuName=param.get("stuName");
if(stuName!=null&&!stuName.equals("")){
sql+=" and stuName like ?";
paramValue.add("%"+stuName+"%");
}
String stuSex=param.get("stuSex");
if(stuSex!=null&&!stuSex.equals("")){
sql+=" and stuSex like ?";
paramValue.add("%"+stuSex+"%");
}
String stuBirth=param.get("stuBirth");
if(stuBirth!=null&&!stuBirth.equals("")){
sql+=" and stuBith like ?";
paramValue.add("%"+stuName+"%");
}*/
//排序子句
sql+=" order by stuId ";
if(page!=null){
sql+=" limit "+(page.getPageNo()-1)*page.getPageSize()+","+page.getPageSize();
}
System.out.println("sql:"+sql+"param:->"+paramValue);
List<Student>list=jdbcTemplate.query(sql,paramValue.toArray(),rowMapper);
return list;
}
private String getParamValue(Map<String,String> param,List paramValue){
String sql="";
String stuName=param.get("stuName");
if(null!=stuName&&!"".equals(stuName)){
sql+=" and stuname like ?";
paramValue.add("%"+stuName+"%");
}
String stuSex=param.get("stuSex");
if(null!=stuSex&&!"".equals(stuSex)){
sql+=" and stusex=? ";
paramValue.add(stuSex);
}
String stuBirth=param.get("stuBirth");
if(null!=stuBirth&&!"".equals(stuBirth)){
sql+=" and stubirth like ?";
paramValue.add("%"+stuBirth+"%");
}
return sql;
}
public int findStudentRowCnt(Map<String,String> param){
String sql="select count(*) from student where 1=1";
List paramValue=new ArrayList();
if(null!=param&&param.size()>0) {
sql += getParamValue(param, paramValue);
}
return jdbcTemplate.queryForObject(sql,paramValue.toArray(),Integer.class);
}

}
测试文件
package com.yujian.test;

import com.yujian.config.MyConfig;
import com.yujian.dao.StudentDao;
import com.yujian.model.ClassInfo;
import com.yujian.model.Student;
import com.yujian.service.StudentService;
import com.yujian.util.DateFormat;
import com.yujian.util.Page;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class StudentTest {
@Test
public void testFindfListStudent(){
ApplicationContext ctx=new AnnotationConfigApplicationContext(MyConfig.class);
StudentDao studentDao=(StudentDao) ctx.getBean("studentDao");
     //上面用的全注解方式,若是用配置文件用下面的
    //
ApplicationContext ctx=new ClassPathXmlApplicationContext("app*.xml");
    //
StudentDao studentDao=(StudentDao) ctx.getBean("studentDao");
 
 List<Student>list=studentDao.findListStudent();
System.out.println(list);
System.out.println(3+3.5);
}
@Test
public void testFindfStudentById(){
ApplicationContext ctx=new AnnotationConfigApplicationContext(MyConfig.class);
StudentDao studentDao=(StudentDao) ctx.getBean("studentDao");
Student student=studentDao.findStudentById(1);
System.out.println(student);
}
@Test
public void testInsertfStudentById(){
ApplicationContext ctx=new AnnotationConfigApplicationContext(MyConfig.class);
StudentDao studentDao=(StudentDao) ctx.getBean("studentDao");
Student student=new Student();
student.setStuNmae("赵四");
student.setStuSex(1);
DateFormat format=new DateFormat();
String date="1997-03-30";
student.setStuBirth(format.format(date));
ClassInfo classInfo=new ClassInfo();
classInfo.setClassId(1);
student.setClassInfo(classInfo);
studentDao.insertStudent(student);
}
@Test
public void testUpdatefStudentById(){
ApplicationContext ctx=new AnnotationConfigApplicationContext(MyConfig.class);
StudentDao studentDao=(StudentDao) ctx.getBean("studentDao");
Student student=new Student();
student.setStuNmae("刘能");
student.setStuSex(0);
DateFormat format=new DateFormat();
String date="1997-12-30";
student.setStuBirth(format.format(date));
ClassInfo classInfo=new ClassInfo();
classInfo.setClassId(2);
student.setClassInfo(classInfo);
student.setStuId(6);
studentDao.updateStudent(student);
}
@Test
public void testDeleteStudent(){
ApplicationContext ctx=new AnnotationConfigApplicationContext(MyConfig.class);
StudentDao studentDao=(StudentDao) ctx.getBean("studentDao");
studentDao.deleteStudent(7);
}
@Test
public void testListStudentBy(){
ApplicationContext ctx=new AnnotationConfigApplicationContext(MyConfig.class);
StudentDao studentDao=(StudentDao) ctx.getBean("studentDao");
Student student=new Student();
/*student.setStuNmae("刘能");
student.setStuSex(0);
DateFormat format=new DateFormat();
String date="1997-12-30";
student.setStuBirth(format.format(date));
ClassInfo classInfo=new ClassInfo();
classInfo.setClassId(2);
student.setClassInfo(classInfo);
student.setStuId(6);*/
// student.setStuNmae("李四");
// student.setStuSex(-1);
List<Student> students=studentDao.listStudentBy(student);
System.out.println(students);
}
@Test
public void testListStudent(){
ApplicationContext ctx=new AnnotationConfigApplicationContext(MyConfig.class);
StudentDao studentDao=(StudentDao) ctx.getBean("studentDao");
Map<String,String> map=new HashMap<String, String>();

/*student.setStuNmae("刘能");
student.setStuSex(0);
DateFormat format=new DateFormat();
String date="1997-12-30";
student.setStuBirth(format.format(date));
ClassInfo classInfo=new ClassInfo();
classInfo.setClassId(2);
student.setClassInfo(classInfo);
student.setStuId(6);*/
// map.put("stuName","李");
/*List<Student> students=studentDao.findStuentByParam(map,null);
System.out.println(students);*/
int row=studentDao.findStudentRowCnt(map);

System.out.println("row:"+row);
Page page=new Page();
page.setRow(row);
page.setPageSize(3);
System.out.println("pageCnt:"+page.getPageCnt());
System.out.println("pageNo1:"+page.getPageNo());
page.setPageNo(3);
System.out.println("pageNo2:"+page.getPageNo());
List<Student> list=studentDao.findStuentByParam(map,page);
System.out.println(list);
}
@Test
public void testInsertfStudentService(){
ApplicationContext ctx=new AnnotationConfigApplicationContext(MyConfig.class);
StudentService studentService=(StudentService) ctx.getBean("studentService");
Student student=new Student();
student.setStuNmae("刘芮玲34");
student.setStuSex(1);
DateFormat format=new DateFormat();
String date="1997-03-30";
student.setStuBirth(format.format(date));
ClassInfo classInfo=new ClassInfo();
classInfo.setClassId(1);
student.setClassInfo(classInfo);
studentService.insertStudent(student);
}
}






 

相关文章