Spring JDBC数据库开发

针对数据库操作，Spring框架提供了JdbcTemplate类。

1.Spring JDBC的配置

创建配置文件applicationContext.xml，添加如下代码：

 　　　<!--配置数据源-->

     <bean id="dataSource"

           class="org.springframework.jdbc.datasource.DriverManagerDataSource">

         <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

         <property name="url" value="jdbc:mysql://localhost:3306/springjdbc"/>

         <property name="username" value="root"/>

         <property name="password" value="root"/>

     </bean>

     <!--配置JDBC模板-->

     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

         <property name="dataSource" ref="dataSource"/>

     </bean>

     <!--配置注入类-->

     <bean id="userDao" class="com.wxy.model.UserDaoImpl">

         <property name="jdbcTemplate" ref="jdbcTemplate"/>

     </bean>

2.创建Javabean和Dao接口

 public class User {

     private Integer id;

     private String name;

     private Integer age;

     public Integer getId() {  return id; }

     public void setId(Integer id) {

         this.id = id;

     }

     public String getName() {

         return name;

     }

     public void setName(String password) {

         this.name = password;

     }

     public Integer getAge() { return age; }

     public void setAge(Integer age) {

         this.age = age;

     }

 }

javabean

public interface UserDao {

    public void execute();

    public int addUser(User user);

    public int updateUser(User user);

    public int deleteUser(int id);

    public void query();

}

UserDao

3.Spring JdbcTemplate的常用方法

2.1 execute(String sql)执行SQL语句

2.2 update()插入、更新、删除表数据操作

2.3 query()查询操作

上述的三个数据库操作方法都放在UserDaoImpl实现类中调用，具体代码如下：

 import com.wxy.javabean.User;

 import org.springframework.dao.DataAccessException;

 import org.springframework.jdbc.core.JdbcTemplate;

 import org.springframework.jdbc.core.ResultSetExtractor;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.util.ArrayList;

 import java.util.List;

 public class UserDaoImpl implements UserDao {

     //获取JdbcTemplate实例

     private JdbcTemplate jdbcTemplate;

14     public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

15         this.jdbcTemplate = jdbcTemplate;

16     }

     //执行SQL语句的方法

     @Override

     public void execute() {

         jdbcTemplate.execute("create table account( id int primary key auto_increment,"

                 + "username varchar(50)," + "balance double)");

     }

     //添加用户

     @Override

     public int addUser(User user) {

         String sql = "insert user (id,name,age) value(?,?,?)";

         //定义数组来存储SQL语句中的参数

         Object[] obj = new Object[]{

                 user.getId(),

                 user.getName(),

                 user.getAge()

         };

         int num = this.jdbcTemplate.update(sql,obj);

         return num;

     }

     //更新用户信息

     public int updateUser(User user){

         String sql = "update user set name=?,age=? where id=?";

         Object[] params = new Object[]{

                 user.getName(),

                 user.getAge(),

                 user.getId()

         };

         int num = this.jdbcTemplate.update(sql,params);

         return num;

     }

     //删除用户

     @Override

     public int deleteUser(int id) {

         String sql = "delete from user where id =?";

         int num = jdbcTemplate.update(sql,id);

         return num;

     }

     //查询所有用户

     @Override

     public void query() {

         String listSql = "select * from user";

         List<User> list = jdbcTemplate.query(listSql, new ResultSetExtractor<List>() {

             public List<User> extractData(ResultSet rs) throws SQLException,

                     DataAccessException {

                 List<User> result = new ArrayList<User>();

                 while (rs.next()) {

                     User user = new User();

                     user.setId(rs.getInt("id"));

                     user.setName(rs.getString("name"));

                     user.setAge(rs.getInt("age"));

                     result.add(user);

                 }

                 return result;

             }

         });

         for (User user : list) {

             System.out.print("id=" + user.getId() + " ");

             System.out.print("name=" + user.getName() + " ");

             System.out.println("age=" + user.getAge() + " ");

         }

     }

 }

4.编写测试类Test

 import com.wxy.javabean.User;

 import com.wxy.model.UserDaoImpl;

 import org.junit.Test;

 import org.springframework.context.ApplicationContext;

 import org.springframework.context.support.ClassPathXmlApplicationContext;

 public class JdbcTemplateTest {

     @Test

     public void executeTest(){

         ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");

         UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");

         userDao.execute();

         System.out.println("创建成功！");

     }

     @Test

     public void addTest(){

         ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");

         UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");

         User user = new User();

         user.setId(3);

         user.setName("Tom");

         user.setAge(21);

         int num = userDao.addUser(user);

         if(num>0){

             System.out.println("成功添加"+num+"个用户");

         }else {

             System.out.println("添加用户操作失败！");

         }

     }

     @Test

     public void updateTest(){

         ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");

         UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");

         User user = new User();

         user.setId(2);

         user.setName("Tim");

         user.setAge(26);

         int num = userDao.addUser(user);

         if(num>0){

             System.out.println("成功修改"+num+"个用户");

         }else {

             System.out.println("修改用户信息失败！");

         }

     }

     @Test

     public void deleteTest(){

         ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");

         UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");

         int num = userDao.deleteUser(2);

         if(num>0){

             System.out.println("成功删除"+num+"个用户");

         }else {

             System.out.println("删除用户操作失败！");

         }

     }

     @Test

     public void query(){

         ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");

         UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");

         userDao.query();

     }

 }

巴特西

Spring JDBC数据库开发

最新文章

热门文章