Spring JDBCTemplate

Spring的JdbcTemplate其实就是封装了我们一般使用的JDBC API，用法简单，但是还是留一个记录比较好！方便日后学习



application.xml


Xml代码
1.<?xml version="1.0" encoding="UTF-8"?> 
2.<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"> 
3. 
4.<beans> 
5.    <bean    id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 
6.        <property name="dataSource"> 
7.            <ref bean="dataSource"/> 
8.        </property> 
9.    </bean> 
10.      
11.    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> 
12.        <property name="driverClassName" value="com.mysql.jdbc.Driver"/> 
13.        <property name="url" value="jdbc:mysql://localhost:3306/test"/> 
14.        <property name="username" value="root"/> 
15.        <property name="password" value="root"/> 
16.    </bean> 
17.      
18.    <bean id="studentDao" class="com.vincent.dao.StudentDao"> 
19.        <property name="jdbcTemplate"> 
20.            <ref bean="jdbcTemplate"/> 
21.        </property> 
22.    </bean> 
23.</beans> 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource">
<ref bean="dataSource"/>
</property>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>
   
    <bean id="studentDao" class="com.vincent.dao.StudentDao">
    <property name="jdbcTemplate">
    <ref bean="jdbcTemplate"/>
    </property>
    </bean>
</beans> 数据源可以使用连接池或者JNDI 两种



Student.java


Java代码
1.package com.vincent.dao;  
2. 
3.import java.sql.Timestamp;  
4. 
5.public class Student {  
6. 
7.    private String student_id;  
8.    private int student_age;  
9.    private int student_score;  
10.    private byte student_sex;  
11.    private byte student_state;  
12.    private String student_info;  
13.    private Timestamp student_reg;  
14. 
15.    public String getStudent_id() {  
16.        return student_id;  
17.    }  
18. 
19.    public void setStudent_id(String student_id) {  
20.        this.student_id = student_id;  
21.    }  
22. 
23.    public int getStudent_age() {  
24.        return student_age;  
25.    }  
26. 
27.    public void setStudent_age(int student_age) {  
28.        this.student_age = student_age;  
29.    }  
30. 
31.    public int getStudent_score() {  
32.        return student_score;  
33.    }  
34. 
35.    public void setStudent_score(int student_score) {  
36.        this.student_score = student_score;  
37.    }  
38. 
39.    public byte getStudent_sex() {  
40.        return student_sex;  
41.    }  
42. 
43.    public void setStudent_sex(byte student_sex) {  
44.        this.student_sex = student_sex;  
45.    }  
46. 
47.    public byte getStudent_state() {  
48.        return student_state;  
49.    }  
50. 
51.    public void setStudent_state(byte student_state) {  
52.        this.student_state = student_state;  
53.    }  
54. 
55.    public String getStudent_info() {  
56.        return student_info;  
57.    }  
58. 
59.    public void setStudent_info(String student_info) {  
60.        this.student_info = student_info;  
61.    }  
62. 
63.    public Timestamp getStudent_reg() {  
64.        return student_reg;  
65.    }  
66. 
67.    public void setStudent_reg(Timestamp student_reg) {  
68.        this.student_reg = student_reg;  
69.    }  
70. 
71.} 
package com.vincent.dao;

import java.sql.Timestamp;

public class Student {

private String student_id;
private int student_age;
private int student_score;
private byte student_sex;
private byte student_state;
private String student_info;
private Timestamp student_reg;

public String getStudent_id() {
return student_id;
}

public void setStudent_id(String student_id) {
this.student_id = student_id;
}

public int getStudent_age() {
return student_age;
}

public void setStudent_age(int student_age) {
this.student_age = student_age;
}

public int getStudent_score() {
return student_score;
}

public void setStudent_score(int student_score) {
this.student_score = student_score;
}

public byte getStudent_sex() {
return student_sex;
}

public void setStudent_sex(byte student_sex) {
this.student_sex = student_sex;
}

public byte getStudent_state() {
return student_state;
}

public void setStudent_state(byte student_state) {
this.student_state = student_state;
}

public String getStudent_info() {
return student_info;
}

public void setStudent_info(String student_info) {
this.student_info = student_info;
}

public Timestamp getStudent_reg() {
return student_reg;
}

public void setStudent_reg(Timestamp student_reg) {
this.student_reg = student_reg;
}

}
 


IStudentDao.java


Java代码
1.package com.vincent.dao;  
2. 
3.import java.util.List;  
4. 
5.public interface IStudentDao {  
6.    public void insertStudent(Student s);  
7. 
8.    public void deleteStudent(Student s);  
9. 
10.    public void updateStudent(Student s);  
11. 
12.    public Student getStudent(String id);  
13. 
14.    public List<Student> queryStudent();  
15.} 
package com.vincent.dao;

import java.util.List;

public interface IStudentDao {
public void insertStudent(Student s);

public void deleteStudent(Student s);

public void updateStudent(Student s);

public Student getStudent(String id);

public List<Student> queryStudent();
}
StudentDao.java


Java代码
1.package com.vincent.dao;  
2. 
3.import java.sql.ResultSet;  
4.import java.sql.SQLException;  
5.import java.util.List;  
6. 
7.import org.springframework.jdbc.core.JdbcTemplate;  
8.import org.springframework.jdbc.core.RowCallbackHandler;  
9.import org.springframework.jdbc.core.RowMapper;  
10. 
11.public class StudentDao implements IStudentDao {  
12. 
13.    private JdbcTemplate jdbcTemplate;  
14. 
15.    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  
16.        this.jdbcTemplate = jdbcTemplate;  
17.    }  
18. 
19.    public void insertStudent(Student s) {  
20.        String sql = "insert into student (student_id,student_age,student_score,student_sex,student_state,student_info,student_reg)" + "values (?,?,?,?,?,?,?)";  
21.        Object[] params = new Object[] { s.getStudent_id(), s.getStudent_age(), s.getStudent_score(), s.getStudent_sex(), s.getStudent_state(),  
22.                s.getStudent_info(), s.getStudent_reg() };  
23.        this.jdbcTemplate.update(sql, params);  
24.    }  
25. 
26.    @Override 
27.    public void deleteStudent(Student s) {  
28.        String sql = "delete from student " + "where student_id=?";  
29.        Object[] params = new Object[] { s.getStudent_id() };  
30.        this.jdbcTemplate.update(sql, params);  
31.    }  
32. 
33.    @Override 
34.    public Student getStudent(String id) {  
35.        String sql = "select student_id,student_age,student_score,student_sex,student_state,student_info,student_reg from student " + "where student_id=?";  
36.        Object[] params = new Object[] { id };  
37.        final Student s = new Student();  
38.        this.jdbcTemplate.query(sql, params, new RowCallbackHandler() {  
39.            @Override 
40.            public void processRow(ResultSet rs) throws SQLException {  
41.                s.setStudent_id(rs.getString("student_id"));  
42.                s.setStudent_age(rs.getInt("student_age"));  
43.                s.setStudent_score(rs.getInt("student_score"));  
44.                s.setStudent_sex(rs.getByte("student_sex"));  
45.                s.setStudent_state(rs.getByte("student_state"));  
46.                s.setStudent_info(rs.getString("student_info"));  
47.                s.setStudent_reg(rs.getTimestamp("student_reg"));  
48.            }  
49.        });  
50.        return s;  
51.    }  
52. 
53.    @Override 
54.    public List<Student> queryStudent() {  
55. 
56.        String sql = "select student_id,student_age,student_score,student_sex,student_state,student_info,student_reg from student ";  
57. 
58.        return this.jdbcTemplate.query(sql, new RowMapper() {  
59.            @Override 
60.            public Object mapRow(ResultSet rs, int arg1) throws SQLException {  
61.                Student s = new Student();  
62.                s.setStudent_id(rs.getString("student_id"));  
63.                s.setStudent_age(rs.getInt("student_age"));  
64.                s.setStudent_score(rs.getInt("student_score"));  
65.                s.setStudent_sex(rs.getByte("student_sex"));  
66.                s.setStudent_state(rs.getByte("student_state"));  
67.                s.setStudent_info(rs.getString("student_info"));  
68.                s.setStudent_reg(rs.getTimestamp("student_reg"));  
69.                return s;  
70.            }  
71.        });  
72.    }  
73. 
74.    @Override 
75.    public void updateStudent(Student s) {  
76.        String sql = "update student set student_age=?,student_score=?,student_sex=?,student_state=?,student_info=?,student_reg=? " + "where student_id=?";  
77.        Object[] params = new Object[] { s.getStudent_age(), s.getStudent_score(), s.getStudent_sex(), s.getStudent_state(), s.getStudent_info(),  
78.                s.getStudent_reg(), s.getStudent_id() };  
79.        this.jdbcTemplate.update(sql, params);  
80.    }  
81. 
82.} 
package com.vincent.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;

public class StudentDao implements IStudentDao {

private JdbcTemplate jdbcTemplate;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}

public void insertStudent(Student s) {
String sql = "insert into student (student_id,student_age,student_score,student_sex,student_state,student_info,student_reg)" + "values (?,?,?,?,?,?,?)";
Object[] params = new Object[] { s.getStudent_id(), s.getStudent_age(), s.getStudent_score(), s.getStudent_sex(), s.getStudent_state(),
s.getStudent_info(), s.getStudent_reg() };
this.jdbcTemplate.update(sql, params);
}

@Override
public void deleteStudent(Student s) {
String sql = "delete from student " + "where student_id=?";
Object[] params = new Object[] { s.getStudent_id() };
this.jdbcTemplate.update(sql, params);
}

@Override
public Student getStudent(String id) {
String sql = "select student_id,student_age,student_score,student_sex,student_state,student_info,student_reg from student " + "where student_id=?";
Object[] params = new Object[] { id };
final Student s = new Student();
this.jdbcTemplate.query(sql, params, new RowCallbackHandler() {
@Override
public void processRow(ResultSet rs) throws SQLException {
s.setStudent_id(rs.getString("student_id"));
s.setStudent_age(rs.getInt("student_age"));
s.setStudent_score(rs.getInt("student_score"));
s.setStudent_sex(rs.getByte("student_sex"));
s.setStudent_state(rs.getByte("student_state"));
s.setStudent_info(rs.getString("student_info"));
s.setStudent_reg(rs.getTimestamp("student_reg"));
}
});
return s;
}

@Override
public List<Student> queryStudent() {

String sql = "select student_id,student_age,student_score,student_sex,student_state,student_info,student_reg from student ";

return this.jdbcTemplate.query(sql, new RowMapper() {
@Override
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
Student s = new Student();
s.setStudent_id(rs.getString("student_id"));
s.setStudent_age(rs.getInt("student_age"));
s.setStudent_score(rs.getInt("student_score"));
s.setStudent_sex(rs.getByte("student_sex"));
s.setStudent_state(rs.getByte("student_state"));
s.setStudent_info(rs.getString("student_info"));
s.setStudent_reg(rs.getTimestamp("student_reg"));
return s;
}
});
}

@Override
public void updateStudent(Student s) {
String sql = "update student set student_age=?,student_score=?,student_sex=?,student_state=?,student_info=?,student_reg=? " + "where student_id=?";
Object[] params = new Object[] { s.getStudent_age(), s.getStudent_score(), s.getStudent_sex(), s.getStudent_state(), s.getStudent_info(),
s.getStudent_reg(), s.getStudent_id() };
this.jdbcTemplate.update(sql, params);
}

}
 
Test.java


Java代码
1.import java.sql.Timestamp;  
2. 
3.import org.springframework.context.support.ClassPathXmlApplicationContext;  
4. 
5.import com.vincent.dao.Student;  
6.import com.vincent.dao.StudentDao;  
7. 
8. 
9.public class Test {  
10.    public static void main(String[] args) {  
11.//      add();  
12.//      modify();  
13.//      delete();  
14.//      getStudent();  
15.//      queryStudent();  
16.    }  
17. 
18.    public static void queryStudent(){  
19.        ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");  
20.        StudentDao sd = (StudentDao)ctx.getBean("studentDao");  
21.        System.out.println(sd.queryStudent().size());  
22.    }  
23.      
24.    public static void getStudent(){  
25.        ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");  
26.        StudentDao sd = (StudentDao)ctx.getBean("studentDao");  
27.        Student s = sd.getStudent("1248682629406");  
28.        System.out.println(s.getStudent_score()+"   "+s.getStudent_id());  
29.    }  
30.      
31.    public static void delete(){  
32.        Student s = new Student();  
33.        s.setStudent_id("1248684313140");  
34.        ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");  
35.        StudentDao sd = (StudentDao)ctx.getBean("studentDao");  
36.        sd.deleteStudent(s);  
37.    }  
38.      
39.    public static void modify(){  
40.        Student s = new Student();  
41.        s.setStudent_id("1248684313140");  
42.        s.setStudent_age(10);  
43.        s.setStudent_info("not info");  
44.        s.setStudent_reg(new Timestamp(System.currentTimeMillis()));  
45.        s.setStudent_score(80);  
46.        s.setStudent_sex(new Byte("1"));  
47.        s.setStudent_state(new Byte("1"));  
48.        ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");  
49.        StudentDao sd = (StudentDao)ctx.getBean("studentDao");  
50.        sd.updateStudent(s);  
51.    }  
52.      
53.    public static void add(){  
54.        Student s = new Student();  
55.        s.setStudent_id(System.currentTimeMillis()+"");  
56.        s.setStudent_age(10);  
57.        s.setStudent_info("not info");  
58.        s.setStudent_reg(new Timestamp(System.currentTimeMillis()));  
59.        s.setStudent_score(80);  
60.        s.setStudent_sex(new Byte("1"));  
61.        s.setStudent_state(new Byte("1"));  
62.        ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");  
63.        StudentDao sd = (StudentDao)ctx.getBean("studentDao");  
64.        sd.insertStudent(s);  
65.    }  
66.}