SpringJdbcTemplate实现CRUD操作代码
第一步:创建一个Java Project
导入相应的Spring的jar包
加入日志文件log4j.properties,便于查看信息(不加也行)
1 ### direct log messages to stdout ### 2 log4j.appender.stdout=org.apache.log4j.ConsoleAppender 3 log4j.appender.stdout.Target=System.err 4 log4j.appender.stdout.layout=org.apache.log4j.PatternLayout 5 log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n 6 7 ### direct messages to file mylog.log ### 8 log4j.appender.file=org.apache.log4j.FileAppender 9 log4j.appender.file.File=c:\mylog.log 10 log4j.appender.file.layout=org.apache.log4j.PatternLayout 11 log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n 12 13 ### set log levels - for more verbose logging change 'info' to 'debug' ### 14 15 log4j.rootLogger=info, stdout
第二步:创建pojo类
1 package com.cn.pojo; 2 3 public class User { 4 5 private Integer id; 6 private String uname; 7 private String upsw; 8 private String usex; 9 public Integer getId() { 10 return id; 11 } 12 public void setId(Integer id) { 13 this.id = id; 14 } 15 public String getUname() { 16 return uname; 17 } 18 public void setUname(String uname) { 19 this.uname = uname; 20 } 21 public String getUpsw() { 22 return upsw; 23 } 24 public void setUpsw(String upsw) { 25 this.upsw = upsw; 26 } 27 public String getUsex() { 28 return usex; 29 } 30 public void setUsex(String usex) { 31 this.usex = usex; 32 } 33 @Override 34 public String toString() { 35 return "User [id=" + id + ", uname=" + uname + ", upsw=" + upsw + ", usex=" + usex + "]"; 36 } 37 38 }
第三步:编写工具类
1 package com.cn.utils; 2 3 import org.springframework.jdbc.core.JdbcTemplate; 4 import org.springframework.jdbc.datasource.DriverManagerDataSource; 5 6 public class JdbcUtils { 7 8 public static JdbcTemplate getTemplate() { 9 // 设置数据库信息 10 DriverManagerDataSource dataSource = new DriverManagerDataSource(); 11 dataSource.setDriverClassName("com.mysql.jdbc.Driver"); 12 dataSource.setUrl("jdbc:mysql:///mydb?characterEncoding=utf-8"); 13 dataSource.setUsername("root"); 14 dataSource.setPassword("dielianhua"); 15 // 创建jdbcTemplate对象,设置数据源 16 JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); 17 return jdbcTemplate; 18 } 19 20 }
第四步:测试springJdbcTemplate功能
注意:springJdbcTemplate中的增删改都是调用update方法,只有查询需要创建一个实现RowMapper接口的类
1 package com.cn.jdbcTemplate; 2 3 import java.util.List; 4 5 import org.junit.Test; 6 7 import com.cn.pojo.MyRowMapper; 8 import com.cn.pojo.User; 9 import com.cn.utils.JdbcUtils; 10 11 public class TestJdbc { 12 13 @Test 14 public void add() { 15 //调用jdbcTemplate对象里面的方法实现操作 16 //创建sql语句 17 String sql = "insert into user value(?,?,?,?)"; 18 int rows = JdbcUtils.getTemplate().update(sql, "0002", "亮亮", "3344", "男"); 19 System.out.println(rows); 20 } 21 @Test 22 public void deleteById() { 23 String sql = "delete from user where id=?"; 24 int rows = JdbcUtils.getTemplate().update(sql, "0001"); 25 System.out.println(rows); 26 } 27 @Test 28 public void updateById() { 29 String sql = "update user set upsw=? where id=?"; 30 int rows = JdbcUtils.getTemplate().update(sql, "7788", "0001"); 31 System.out.println(rows); 32 } 33 @Test 34 public void findAll() { 35 String sql = "select * from user"; 36 List<User> count = JdbcUtils.getTemplate().query(sql, new MyRowMapper()); 37 System.out.println(count); 38 } 39 @Test 40 public void findById() { 41 String sql = "select * from user where id=?"; 42 User count = JdbcUtils.getTemplate().queryForObject(sql, new MyRowMapper(), "0001"); 43 System.out.println(count); 44 } 45 }
1 package com.cn.pojo; 2 3 import java.sql.ResultSet; 4 import java.sql.SQLException; 5 6 import org.springframework.jdbc.core.RowMapper; 7 8 import com.cn.pojo.User; 9 10 public class MyRowMapper implements RowMapper<User>{ 11 12 @Override 13 public User mapRow(ResultSet rs, int num) throws SQLException { 14 //1从结果集里面把数据得到 15 Integer id = rs.getInt("id"); 16 String uname = rs.getString("uname"); 17 String upsw = rs.getString("upsw"); 18 String usex = rs.getString("usex"); 19 //2 把得到数据封装到对象里面 20 User user = new User(); 21 user.setId(id); 22 user.setUname(uname); 23 user.setUpsw(upsw); 24 user.setUsex(usex); 25 return user; 26 } 27 28 }
需要做的一步----》数据库设计:创建数据库mydb 创建表user