spring-jdbc模板连接数据库（增删改查）

学生类Student，定义事务类接口IStudentService 以及实现的接口StudentImpl ，StudentImpl中有dao的方法，定义dao层及实现的接口StudentDaoImpl插入sql语句控制表单,spring配置文件applcation.xml,测试类mytest

mysql创建表单，加入mysql jar包以及  spring-jdbc-4.3.6.RELEASE.jar   spring-tx-4.3.6.RELEASE.jar。 掌握sql语句

package com.abc.beans;

public class Student {


private Integer id;
  private String name;
  private int age;
  
  
  public Student() {
super();
// TODO Auto-generated constructor stub
}
  
public Student(String name, int age) {
super();
this.name = name;
this.age = age;
}


public void setId(Integer id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setAge(int age) {
this.age = age;

}

public Integer getId() {
return id;
}
public String getName() {
return name;
}
public int getAge() {
return age;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
}

}

package com.abc.service;


import java.util.List;


import com.abc.beans.Student;


public interface IStudentService {
void saveStudent(Student student);
void removeStudentById(int id);
void modifyStudent(Student student);
 
String findStudentNameById(int id) ;
List<String> findAllstudentNames();
 
Student findStudentById(int id) ;
List<Student> findAllStudents();
}

package com.abc.service;


import java.util.List;


import com.abc.beans.Student;
import com.abc.dao.IStudentDao;


public class StduentsImpl implements IStudentService {

                                                                                                      ps:由于要用到spring容器，在此创建属性使其连接到Dao层，dao层方法与IStudentService方法一样，只是改变名字
private IStudentDao dao;
                                                                                                     只不过dao层要链接数据库，用到sql语句，实现曾删改查。

public void setDao(IStudentDao dao) {
this.dao = dao;
}


public void saveStudent(Student student) {
dao.insertStudent(student);


}


public void removeStudentById(int id) {
dao.deleteStudentById(id);


}


public void modifyStudent(Student student) {
dao.updateStudent(student);


}


public String findStudentNameById(int id) {

return dao.selectStudentNameById(id);
}
public List<String> findAllstudentNames() {
// TODO Auto-generated method stub
return dao.selectAllstudentNames();
}



public Student findStudentById(int id) {
return dao.selectStudentById(id);
}


public List<Student> findAllStudents() {
return  dao.selectAllStudents();


}

}

package com.abc.dao;


import java.util.List;


import com.abc.beans.Student;


public interface IStudentDao {


void insertStudent(Student student);
void deleteStudentById(int id);
void updateStudent(Student student);
 
String selectStudentNameById(int id) ;
List<String> selectAllstudentNames();
 
Student selectStudentById(int id) ;
List<Student> selectAllStudents();

}

package com.abc.dao;


import java.util.List;


import org.springframework.jdbc.core.support.JdbcDaoSupport;


import com.abc.beans.Student;


public class StudentDaoImpl extends JdbcDaoSupport implements IStudentDao {
                                                                             既然要用jdbc模板，自然要用他的方法，所以继承jdbcDaoSupport   alt+?可以提示单词。

                                                                              此接口要链接他的源码，查看他的方法及其属性，因为spring容器要创建他们而不是StudentDaoImpl（继承）。

                                                                              

@Override
public void insertStudent(Student student) {
   String sql = "insert into student(name,age) values(?,?)";//sql语句
this.getJdbcTemplate().update(sql, student.getName(),student.getAge());

                                                                                                                                 
}


@Override
public void deleteStudentById(int id) {
String sql = "delete from student where id =?";
this.getJdbcTemplate().update(sql, id);


}


@Override
public void updateStudent(Student student) {
String sql ="update student set name=?,age=? where id=?  ";
this.getJdbcTemplate().update(sql, student.getName(),student.getAge(),student.getId());


}


@Override
public String selectStudentNameById(int id) {
/* 通过id查询名字     args为出租参数。getJdbcTemplate().queryForObject方法
         因为返回值是string，方法里填的是 String.class
*/
String sql="select name from student where id=?";
Object[] args ={id};
return this.getJdbcTemplate().queryForObject(sql,args,String.class);
}


public List<String> selectAllstudentNames() {
//查询所有学生   调用.getJdbcTemplate().queryForList   ,集合
String sql="select name from student ";

return this.getJdbcTemplate().queryForList(sql,String.class);
}



@Override
public Student selectStudentById(int id) {
//通过id查询学生，返回值为封装了student
//定义StudentRowMap())方法继承的RowMapper
String sql="select id,name,age from student where id=?";
Object[] args ={id};
return this.getJdbcTemplate().queryForObject(sql, args,new StudentRowMap());
}


@Override
public List<Student> selectAllStudents() {
String sql="select id,name,age from student  ";

return this.getJdbcTemplate().query(sql, new StudentRowMap());
}


}

<?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:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="
        http://www.springframework.org/schema/beans 
        http://www.springframework.org/schema/beans/spring-beans.xsd">


<!-- 注册DataSource数据源四大 -->
<bean id="myDataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql:///test" />
<property name="username" value="root" />
<property name="password" value="111" />
</bean>
       <!-- 注册jdbcTemplate -->


<bean id="myjdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="myDataSource" />
</bean>
     <!-- 注册Dao -->
<bean id="studentDao" class="com.abc.dao.StudentDaoImpl">
<property name="jdbcTemplate" ref="myjdbcTemplate" />


</bean>
     <!-- 注册 Service-->
<bean id="studentService" class="com.abc.service.StduentsImpl">
<property name="dao" ref="studentDao" />

</bean>

</beans>


编译过程其实是个倒叙的注册顺序  Service-dao-jdbcTenmplate-jdbcResource,    ps：所有class填的是实现类路径。。

package com.abc.test;


import java.util.List;


import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;


import com.abc.beans.Student;
import com.abc.service.IStudentService;


public class Mytest {


private IStudentService service;


@Before
public void before() {
String config = "applicationContext.xml";
ApplicationContext ac = new ClassPathXmlApplicationContext(config);
service = (IStudentService) ac.getBean("studentService");
}


@Test
public void test01() {


Student student = new Student("ppp", 23);
service.saveStudent(student);
}


@Test
public void test02() {
service.removeStudentById(2);
}


@Test
public void test03() {
Student student = new Student("zzz", 11);
student.setId(1);
service.modifyStudent(student);
}


@Test
public void test04() {
String name = service.findStudentNameById(5);
System.out.println(name);
}


@Test
public void test05() {
List<String> names = service.findAllstudentNames();
for (String name : names) {
System.out.println(name);
}
}


@Test
public void test06() {
Student student = service.findStudentById(3);
System.out.println(student);
}


@Test
public void test07() {
List<Student> students = service.findAllStudents();
for (Student student : students) {
System.out.println(student);
}
}
}


Student [id=29, name=ppp, age=23]
Student [id=30, name=ppp, age=23]
Student [id=31, name=ppp, age=23]
Student [id=32, name=ppp, age=23]
Student [id=33, name=ppp, age=23]
Student [id=34, name=ppp, age=23]
Student [id=35, name=ppp, age=23]
Student [id=36, name=ppp, age=23]
Student [id=37, name=ppp, age=23]
Student [id=38, name=ppp, age=23]
Student [id=39, name=ppp, age=23]