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.}