Spring的数据库开发

步骤

1.创建User类

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor //有参
@NoArgsConstructor //无参
public class User {
     private int id;
     private String username;
     private String password;
}

2.UserDao和UserImpl

mport com.sty.pojo.User;

import java.util.List;

public interface UserDao {
    void addUser(User user);
    void deleteUser(int id);
    int updateUser(User user);
    User queryUserByID(int id);
    List<User> queryUser();
}

import com.sty.pojo.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.util.List;

public class UserDaoImpl implements UserDao {
    private JdbcTemplate jdbcTemplate;

    //为了Spring 注入
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    //添加用户
    public void addUser(User user) {

        String sql = "insert into user(username,password) values (?,?)";

       Object[] obj= new Object[]{
                user.getUsername(),
               user.getPassword()
        };
           jdbcTemplate.update(sql,obj);

    }

    //删除用户信息
    public void deleteUser(int id) {
        String sql ="delete from user where id=?";
        jdbcTemplate.update(sql,id);
    }


    //修改用户信息
    public  int updateUser(User user) {
        String sql ="update user set username=?,password=? where id=?";

        Object[] obj =new Object[]{
              user.getUsername(),
                user.getPassword(),
                user.getId()
        };

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


    //查询用户
    public User queryUserByID(int id) {
        String sql="select * from day10.user where id=?";

       RowMapper<User> RowMapper = new BeanPropertyRowMapper<User>(User.class);

        User user = jdbcTemplate.queryForObject(sql, RowMapper,id);
        return user;
    }

    public List<User> queryUser() {

        String sql = "select * from user";
        RowMapper<User> mapper = new BeanPropertyRowMapper<User>(User.class);

        List<User> list = jdbcTemplate.query(sql,mapper);
        return list;
    }
}

3.applicationContext.xml

<?xml version="1.0" encoding="UTF8"?>
<!--suppress ALL -->
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd">

    <!--1.配置数据源-->
    <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/day10"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>

    <!--2.配置JDBC模板-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!--将jdbcTemplate注入userDao实例中-->

  <bean id="userDao" class="com.sty.dao.UserDaoImpl">
      <property name="jdbcTemplate" ref="jdbcTemplate"/>
  </bean>
</beans>

4.测试

mport com.sty.dao.UserDao;
import com.sty.pojo.User;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

public class TestUSer {
   @Test
    public  void QueryTest(){

       ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
       UserDao userDao = (UserDao) context.getBean("userDao");

       User user = userDao.queryUserByID(1);
       System.out.println(user);
   }


   @Test
   public void addTest(){

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

    UserDao user = (UserDao) context.getBean("userDao");

      User user1 = new User();
      user1.setUsername("麻子");
      user1.setPassword("123546");

      user.addUser(user1);
   }


   @Test
    public void updateTest(){

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

       UserDao user = (UserDao) context.getBean("userDao");


       User user1 = new User();
       user1.setUsername("李四喜");
       user1.setPassword("123546456");
       user1.setId(1);

       int i = user.updateUser(user1);
       if(i>0){
           System.out.println("修改成功");
       }else{
           System.out.println("修改失败");
       }

   }


   @Test
    public void queryTest(){

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

       UserDao user = (UserDao) context.getBean("userDao");
       List<User> list = user.queryUser();
       for (User user1 : list) {
           System.out.println(user1);
       }

   }


}

5.注意出现下面的错误信息，可以把applicationContext.xml中的UTF-8,改为UTF8

org.springframework.beans.factory.xml.XmlBeanDefinitionStoreException: Line 8 in XML document from class path resource [applicationContext.xml] is invalid; nested exception is org.xml.sax.SAXParseException; lineNumber: 8; columnNumber: 11; 2 字节的 UTF-8 序列的字节 2 无效。

6.依赖